rec10/trunk/src/dbMySQL.py

   1 #!/usr/bin/python
   2 # coding: UTF-8
   3 # Rec10 TS Recording Tools
   4 # Copyright (C) 2009 Yukikaze
   5 import MySQLdb
   6 import recdblist
   7 from decimal import Decimal
   8 class DB_MySQL:
   9     dbname = ""
  10     dbhost = ""
  11     dbusr = ""
  12     dbpasswd = ""
  13     dbport = 0
  14     def __init__(self, dbname, user, passwd, host="localhost", port=3306):
  15         self.dbname = dbname
  16         self.dbhost = host
  17         self.dbusr = user
  18         self.dbpasswd = passwd
  19         self.dbport = port
  20         try:
  21             con = MySQLdb.connect(user=user, passwd=passwd)
  22             cur = con.cursor()
  23             cur.execute('CREATE DATABASE ' + dbname + " DEFAULT CHARACTER SET utf8")
  24             cur.close()
  25             con.close()
  26         except:
  27             ""
  28         db = self.connect_db()
  29         try:
  30             db[1].execute('\
  31             CREATE TABLE timeline \
  32             (\
  33             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
  34             type VARCHAR(40),\
  35             chtxt VARCHAR(40),\
  36             title VARCHAR(100),\
  37             btime DATETIME,\
  38             etime DATETIME,\
  39             deltatime VARCHAR(5),\
  40             deltaday VARCHAR(5),\
  41             opt VARCHAR(20),\
  42             UNIQUE uni (type,chtxt,title,btime,deltaday)\
  43             )')
  44         except:
  45             ""
  46         self.close_db(db)
  47         self.new_epg_timeline("")
  48         self.new_in_timeline_log()
  49         self.new_in_auto_bayes_key()
  50         self.new_in_auto_jbk_key()
  51         self.new_in_status()
  52         self.new_in_settings()
  53         self.new_auto_timeline_bayes()
  54         self.new_auto_timeline_keyword()
  55     def connect_db(self):
  56         """
  57         dbへの接続
  58         """
  59         con = MySQLdb.connect(db=self.dbname, host=self.dbhost, port=self.dbport, user=self.dbusr, passwd=self.dbpasswd, charset="utf8")
  60         cur = con.cursor()
  61         cur.execute('set names utf8;')
  62         return [con, cur]
  63     def close_db(self, db):
  64         db[1].close()
  65         db[0].close()
  66     def new_epg_ch(self):
  67         db = self.connect_db()
  68         try:
  69             db[1].execute('drop table epg_ch')
  70         except:
  71             ""
  72         try:
  73             db[1].execute('\
  74             CREATE TABLE epg_ch \
  75             (\
  76             bctype VARCHAR(15),\
  77             ontv VARCHAR(30) PRIMARY KEY,\
  78             chtxt VARCHAR(20),\
  79             ch VARCHAR(20),\
  80             csch VARCHAR(20),\
  81             chname VARCHAR(100),\
  82             updatetime DATETIME,\
  83             status TINYINT,\
  84             isshow TINYINT\
  85             )')
  86         except:
  87             ""
  88         self.close_db(db)
  89     def add_epg_ch(self, bctype, ontv, chtxt, ch, csch, updatetime):
  90         db = self.connect_db()
  91         db[1].execute('\
  92         INSERT INTO epg_ch \
  93         VALUES (%s,%s,%s,%s,%s,"",%s,%s,%s)', \
  94                       (bctype, ontv, chtxt, ch, csch, updatetime, "1","1"))
  95         self.close_db(db)
  96     def select_by_ontv_epg_ch(self, ontv):
  97         db = self.connect_db()
  98         dbexe = db[1].execute("\
  99         SELECT bctype,ontv,chtxt,ch,csch,updatetime \
 100         FROM epg_ch \
 101         WHERE ontv = %s", \
 102                               (ontv,))
 103         ret = []
 104         dls = []
 105         if dbexe > 0:
 106             dls = db[1].fetchall()
 107         self.close_db(db)
 108         for dl in dls:
 109             r = list(dl)
 110             r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
 111             ret.append(r)
 112         return ret
 113     def select_by_chtxt_epg_ch(self, chtxt):
 114         db = self.connect_db()
 115         dbexe = db[1].execute("\
 116         SELECT bctype,ontv,chtxt,ch,csch,updatetime \
 117         FROM epg_ch \
 118         WHERE chtxt = %s", \
 119                               (chtxt,))
 120         ret = []
 121         dls = []
 122         if dbexe > 0:
 123             dls = db[1].fetchall()
 124         self.close_db(db)
 125         for dl in dls:
 126             r = list(dl)
 127             r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
 128             ret.append(r)
 129         return ret
 130     def select_by_bctype_epg_ch(self, bctype):
 131         db = self.connect_db()
 132         dbexe = db[1].execute("\
 133         SELECT bctype,ontv,chtxt,ch,csch,updatetime,status \
 134         FROM epg_ch \
 135         WHERE bctype = %s", \
 136                               (bctype,))
 137         ret = []
 138         dls = []
 139         if dbexe > 0:
 140             dls = db[1].fetchall()
 141         self.close_db(db)
 142         for dl in dls:
 143             #recdblist.printutf8(dl)
 144             r = list(dl)
 145             r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
 146             r[6] = str(r[6])
 147             ret.append(r)
 148         return ret
 149     def select_by_ch_epg_ch(self, ch):
 150         db = self.connect_db()
 151         dbexe = db[1].execute("\
 152         SELECT \
 153         bctype,ontv,chtxt,ch,csch,updatetime \
 154         FROM epg_ch \
 155         WHERE ch = %s", \
 156                               (ch,))
 157         ret = []
 158         dls = []
 159         if dbexe > 0:
 160             dls = db[1].fetchall()
 161         self.close_db(db)
 162         for dl in dls:
 163             r = list(dl)
 164             r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
 165             ret.append(r)
 166         return ret
 167     def select_all_epg_ch(self):
 168         db = self.connect_db()
 169         dbexe = db[1].execute("\
 170         SELECT bctype,ontv,chtxt,ch,csch,updatetime \
 171         FROM epg_ch \
 172         ")
 173         ret = []
 174         dls = []
 175         if dbexe > 0:
 176             dls = db[1].fetchall()
 177         self.close_db(db)
 178         for dl in dls:
 179             r = list(dl)
 180             r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
 181             ret.append(r)
 182         return ret
 183     def set_new_status(self,dhour):
 184         db = self.connect_db()
 185         dbexe = db[1].execute("UPDATE epg_ch \
 186         SET status = 1 \
 187         WHERE \
 188         ( \
 189         updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
 190         AND \
 191         status = 0 \
 192         )"\
 193         )
 194     def select_get_update_epg_ch(self, dhour):
 195         db = self.connect_db()
 196         dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
 197         WHERE (\
 198         ( \
 199         updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
 200         AND \
 201         status = 1 \
 202         ) \
 203         OR \
 204         status > 1 )\
 205         ORDER BY status DESC")
 206         ret = []
 207         #recdblist.printutf8(dbexe)
 208         if dbexe > 0:
 209             ret = db[1].fetchall()
 210         self.close_db(db)
 211         return ret
 212     def update_by_bctype_epg_ch(self, bctype):
 213         db = self.connect_db()
 214         db[1].execute("\
 215         UPDATE epg_ch \
 216         SET \
 217         updatetime=now() , \
 218         status = 1 \
 219         WHERE bctype = %s", (bctype,))
 220         self.close_db(db)
 221     def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
 222         db = self.connect_db()
 223         db[1].execute("\
 224         UPDATE epg_ch \
 225         SET \
 226         updatetime=now() , \
 227         status = 1\
 228         WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
 229         self.close_db(db)
 230     def update_status_by_bctype_epg_ch(self, bctype, status):
 231         db = self.connect_db()
 232         db[1].execute("\
 233         UPDATE epg_ch \
 234         SET \
 235         status=%s \
 236         WHERE bctype = %s", \
 237                       (status, bctype)\
 238                       )
 239         self.close_db(db)
 240     def update_chname_by_ontv_epg_ch(self,ontv,chname):
 241         db = self.connect_db()
 242         db[1].execute("\
 243         UPDATE epg_ch \
 244         SET \
 245         chname=%s \
 246         WHERE ontv = %s", \
 247                       (chname, ontv)\
 248                       )
 249         self.close_db(db)
 250     def add_auto_proc_tmp(self,type,title,chtxt):
 251         db = self.connect_db()
 252         db[1].execute('\
 253         INSERT IGNORE into auto_proc_tmp \
 254         (type,title,chtxt) \
 255         values (%s,%s,%s)',(type,title,chtxt))
 256         ##db.commit()
 257         self.close_db(db)
 258     def new_auto_proc_tmp(self):
 259         db = self.connect_db()
 260         try:
 261             db[1].execute('drop table auto_proc_tmp')
 262         except:
 263             ""
 264         try:
 265             db[1].execute('\
 266             CREATE TABLE auto_proc_tmp \
 267             (\
 268             type VARCHAR(20),\
 269             title VARCHAR(100) PRIMARY KEY,\
 270             chtxt VARCHAR(30),\
 271             UNIQUE unibayeskey(title)\
 272             )')
 273         except:
 274             ""
 275         self.close_db(db)
 276     def update_auto_proc(self):
 277         db = self.connect_db()
 278         try:
 279             db[1].execute('INSERT INTO auto_proc SELECT * FROM auto_proc_tmp')
 280         except:
 281             ""
 282         self.close_db(db)
 283     def new_auto_proc(self):
 284         db = self.connect_db()
 285         try:
 286             db[1].execute('drop table auto_proc')
 287         except:
 288             ""
 289         try:
 290             db[1].execute('\
 291             CREATE TABLE auto_proc \
 292             (\
 293             type VARCHAR(20),\
 294             title VARCHAR(100) PRIMARY KEY,\
 295             chtxt VARCHAR(30),\
 296             UNIQUE unibayeskey(title)\
 297             )')
 298         except:
 299             ""
 300         self.close_db(db)
 301     def drop_in_settings(self):
 302         db = self.connect_db()
 303         try:
 304             db[1].execute('drop table in_settings')
 305         except:
 306             ""
 307         self.close_db(db)
 308     def new_in_settings(self):
 309         db = self.connect_db()
 310         try:
 311             db[1].execute('\
 312             CREATE TABLE in_settings \
 313             (\
 314             auto_jbk TINYINT,\
 315             auto_bayes TINYINT,\
 316             auto_opt VARCHAR(20),\
 317             auto_del_tmp TINYINT\
 318             )')
 319             db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"G\",1)")
 320         except:
 321             ""
 322         self.close_db(db)
 323     def select_all_in_settings(self):
 324         db = self.connect_db()
 325         dbexe = db[1].execute("\
 326         SELECT auto_jbk,auto_bayes,auto_del_tmp,auto_opt \
 327         FROM in_settings \
 328         ")
 329         ret = []
 330         dls = []
 331         if dbexe > 0:
 332             dls = db[1].fetchall()
 333         self.close_db(db)
 334         for dl in dls:
 335             r = list(dl)
 336             r[0]=str(r[0])
 337             r[1]=str(r[1])
 338             r[2]=str(r[2])
 339             r[3]=r[3]
 340             ret.append(r)
 341         return ret
 342     def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
 343         db = self.connect_db()
 344         db[1].execute('\
 345         INSERT IGNORE into in_timeline_log \
 346         (chtxt,title,btime,etime,opt,exp,longexp,category) \
 347         values (%s,%s,%s,%s,%s,%s,%s,%s)', \
 348                       ( chtxt, title, btime, etime, opt,exp,longexp,category))
 349         ##db.commit()
 350         self.close_db(db)
 351     def del_in_timeline_log(self, title="", chtxt="", btime=""):
 352         """
 353
 354         """
 355         db = self.connect_db()
 356         db[1].execute("\
 357         DELETE FROM in_timeline_log \
 358         WHERE title = %s AND chtxt = %s AND btime = %s", \
 359                       (title, chtxt, btime))
 360         #db.commit()
 361         self.close_db(db)
 362     def new_in_timeline_log(self):
 363         db = self.connect_db()
 364         try:
 365             db[1].execute('\
 366             CREATE TABLE in_timeline_log \
 367             (\
 368             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
 369             chtxt VARCHAR(20),\
 370             title VARCHAR(100),\
 371             btime DATETIME,\
 372             etime DATETIME,\
 373             opt VARCHAR(20),\
 374             exp VARCHAR(200),\
 375             longexp TEXT,\
 376             category VARCHAR(100),\
 377             UNIQUE uni (chtxt,title,btime,category)\
 378             )')
 379         except:
 380             ""
 381         self.close_db(db)
 382     def select_chtxt_by_title_timeline_log(self,title):
 383         db = self.connect_db()
 384         dbexe = db[1].execute("\
 385         SELECT chtxt \
 386         FROM in_timeline_log \
 387         WHERE title LIKE \"%"+title+"%\"\
 388         GROUP by chtxt\
 389         ORDER by sum(1) DESC limit 1")
 390         retdb=db[1].fetchall()
 391         ret=""
 392         if ret!=None:
 393             if len(retdb)>0:
 394                 ret=retdb[0][0]
 395         self.close_db(db)
 396         return ret
 397     def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
 398         db = self.connect_db()
 399         db[1].execute('\
 400         INSERT IGNORE into timeline \
 401         (type,chtxt,title,btime,etime,deltatime,deltaday,opt) \
 402         values (%s,%s,%s,%s,%s,%s,%s,%s)', \
 403                       (type, chtxt, title, btime, etime, deltatime, deltaday, opt))
 404         ##db.commit()
 405         self.close_db(db)
 406     def del_timeline(self, type="", title="", chtxt="", btime=""):
 407         """
 408
 409         """
 410         db = self.connect_db()
 411         db[1].execute("\
 412         DELETE FROM timeline \
 413         WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
 414                       (type, title, chtxt, btime))
 415         #db.commit()
 416         self.close_db(db)
 417     def select_all_timeline(self):
 418         db = self.connect_db()
 419         recdata = []
 420         dbr = db[1].execute("\
 421         SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
 422         FROM timeline")
 423         dbl = db[1].fetchall()
 424         self.close_db(db)
 425         if dbr > 0:
 426             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 427                 ret = {}
 428                 ret['type'] = typet
 429                 ret['chtxt'] = chtxt
 430                 ret['title'] = title
 431                 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
 432                 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
 433                 ret['btime'] = btime
 434                 ret['etime'] = etime
 435                 ret['opt'] = opt
 436                 ret['deltatime'] = ""
 437                 ret['deltaday'] = ""
 438                 if deltatime == None:
 439                     deltatime = "3"
 440                 if deltaday == None:
 441                     deltaday = "7"
 442                 if typet == 'key':
 443                     ret['deltatime'] = deltatime
 444                 elif typet == 'keyevery':
 445                     ret['deltatime'] = deltatime
 446                     ret['deltaday'] = deltaday
 447                 recdata.append(ret)
 448         self.close_db(db)
 449         return recdata
 450     def select_bytime_timeline(self, dminutes):
 451         db = self.connect_db()
 452         recdatum = []
 453         #dbr=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE btime < DATE_SUB(now(),INTERVAL "+dminutes+" MINUTE ) AND btime > DATE_ADD(now(),INTERVAL "+dminutes+" MINUTE )")
 454         dbr = db[1].execute("SELECT \
 455         type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
 456         FROM timeline \
 457         WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
 458         DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
 459         dbl = db[1].fetchall()
 460         self.close_db(db)
 461         #recdblist.printutf8(dbl)
 462         if dbr > 0:
 463             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 464                 ret = {}
 465                 ret['type'] = typet
 466                 ret['chtxt'] = chtxt
 467                 ret['title'] = title
 468                 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
 469                 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
 470                 ret['btime'] = btime
 471                 ret['etime'] = etime
 472                 ret['opt'] = opt
 473                 if deltatime == None or deltatime == "":
 474                     deltatime = "3"
 475                 if deltaday == None or deltaday == "":
 476                     deltaday = "7"
 477                 if typet == recdblist.REC_KEYWORD:
 478                     ret['deltatime'] = deltatime
 479                 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
 480                     ret['deltatime'] = deltatime
 481                     ret['deltaday'] = deltaday
 482                 recdatum.append(ret)
 483         return recdatum
 484     def select_bytime_all_timeline(self,btime,etime):
 485         db = self.connect_db()
 486         recdatum = []
 487         #dbr=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE btime < DATE_SUB(now(),INTERVAL "+dminutes+" MINUTE ) AND btime > DATE_ADD(now(),INTERVAL "+dminutes+" MINUTE )")
 488         dbr = db[1].execute("SELECT \
 489         type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
 490         FROM timeline \
 491         WHERE btime > %s AND \
 492         etime < %s",(btime,etime))
 493         dbl = db[1].fetchall()
 494         self.close_db(db)
 495         #recdblist.printutf8(dbl)
 496         if dbr > 0:
 497             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 498                 ret = {}
 499                 ret['type'] = typet
 500                 ret['chtxt'] = chtxt
 501                 ret['title'] = title
 502                 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
 503                 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
 504                 ret['btime'] = btime
 505                 ret['etime'] = etime
 506                 ret['opt'] = opt
 507                 if deltatime == None or deltatime == "":
 508                     deltatime = "3"
 509                 if deltaday == None or deltaday == "":
 510                     deltaday = "7"
 511                 if typet == recdblist.REC_KEYWORD:
 512                     ret['deltatime'] = deltatime
 513                 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
 514                     ret['deltatime'] = deltatime
 515                     ret['deltaday'] = deltaday
 516                 recdatum.append(ret)
 517         return recdatum
 518     def count_schedule_timeline(self, btime, etime):
 519         """
 520         count rectasknum
 521         return [te num,bs/cs num]
 522         """
 523         db = self.connect_db()
 524         dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
 525         dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
 526         Srec = 0
 527         Trec = 0
 528         db[1].execute(dbexe, (btime, etime,btime,etime))
 529         for typet, bctypet, chtxtt, titlet in db[1].fetchall():
 530             if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
 531                 if bctypet.find("cs") > -1:
 532                     Srec = Srec + 1
 533                 elif bctypet.find("bs") > -1:
 534                     Srec = Srec + 1
 535                 elif bctypet.find("te") > -1:
 536                     Trec = Trec + 1
 537         self.close_db(db)
 538         return [Trec, Srec]
 539     def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
 540         db = self.connect_db()
 541         recdatum = []
 542         #dbr=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE btime < DATE_SUB(now(),INTERVAL "+dminutes+" MINUTE ) AND btime > DATE_ADD(now(),INTERVAL "+dminutes+" MINUTE )")
 543         dbr = db[1].execute("SELECT \
 544         type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
 545         FROM timeline \
 546         WHERE btime > %s AND \
 547         etime < %s\
 548         AND chtxt=%s ",(btime,etime,chtxt))
 549         dbl = db[1].fetchall()
 550         self.close_db(db)
 551         #recdblist.printutf8(dbl)
 552         if dbr > 0:
 553             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 554                 ret = {}
 555                 ret['type'] = typet
 556                 ret['chtxt'] = chtxt
 557                 ret['title'] = title
 558                 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
 559                 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
 560                 ret['btime'] = btime
 561                 ret['etime'] = etime
 562                 ret['opt'] = opt
 563                 if deltatime == None or deltatime == "":
 564                     deltatime = "3"
 565                 if deltaday == None or deltaday == "":
 566                     deltaday = "7"
 567                 if typet == recdblist.REC_KEYWORD:
 568                     ret['deltatime'] = deltatime
 569                 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
 570                     ret['deltatime'] = deltatime
 571                     ret['deltaday'] = deltaday
 572                 recdatum.append(ret)
 573         return recdatum
 574     def delete_old_timeline(self, dhour):
 575         db = self.connect_db()
 576         db[1].execute("\
 577         DELETE FROM timeline \
 578         WHERE \
 579         btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
 580         #db.commit()
 581         self.close_db(db)
 582     def new_in_auto_jbk_key(self):
 583         db = self.connect_db()
 584         try:
 585             db[1].execute("\
 586             CREATE TABLE in_auto_jbk_key \
 587             (\
 588             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
 589             keyword VARCHAR(200),\
 590             UNIQUE unijbk (keyword)\
 591             )")
 592         except:
 593             ""
 594         self.close_db(db)
 595     def add_in_auto_jbk_key(self,key):
 596         db = self.connect_db()
 597         db[1].execute('\
 598         INSERT IGNORE into in_auto_jbk_key \
 599         (keyword) \
 600         values (%s)', \
 601                       (key,))
 602         ##db.commit()
 603         self.close_db(db)
 604     def select_all_in_auto_jbk_key(self):
 605         db = self.connect_db()
 606         dbexe = db[1].execute("\
 607         SELECT keyword \
 608         FROM in_auto_jbk_key \
 609         ")
 610         ret = []
 611         if dbexe > 0:
 612             ret = db[1].fetchall()
 613         self.close_db(db)
 614         return ret
 615     def drop_in_status(self):
 616         db = self.connect_db()
 617         try:
 618             db[1].execute('drop table in_status')
 619         except:
 620             ""
 621         self.close_db(db)
 622     def new_in_status(self):
 623         db = self.connect_db()
 624         try:
 625             db[1].execute("\
 626             CREATE TABLE in_status \
 627             (\
 628             ts2avi TINYINT DEFAULT 0,\
 629             terec TINYINT DEFAULT 0,\
 630             bscsrec TINYINT DEFAULT 0,\
 631             b252ts TINYINT DEFAULT 0,\
 632             installed TINYINT DEFAULT 0\
 633             )")
 634             db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0)")
 635         except:
 636             ""
 637         self.close_db(db)
 638     def select_all_in_status(self):
 639         db = self.connect_db()
 640         dbexe = db[1].execute("\
 641         SELECT ts2avi,terec,bscsrec,b252ts \
 642         FROM in_status \
 643         ")
 644         ret = []
 645         dls = []
 646         if dbexe > 0:
 647             dls = db[1].fetchall()
 648         self.close_db(db)
 649         for dl in dls:
 650             r = list(dl)
 651             r[0]=str(r[0])
 652             r[1]=str(r[1])
 653             r[2]=str(r[2])
 654             r[3]=str(r[3])
 655             ret.append(r)
 656         return ret
 657
 658     def change_ts2avi_in_status(self,i):
 659         """
 660         statuをiだけ増減する
 661         iはint
 662         """
 663         db = self.connect_db()
 664         db[1].execute("\
 665         UPDATE in_status SET ts2avi=ts2avi+%s",i)
 666         self.close_db(db)
 667     def change_terec_in_status(self,i):
 668         """
 669         statuをiだけ増減する
 670         iはint
 671         """
 672         db = self.connect_db()
 673         db[1].execute("\
 674         UPDATE in_status SET terec=terec+%s",i)
 675         self.close_db(db)
 676     def change_bscsrec_in_status(self,i):
 677         """
 678         statuをiだけ増減する
 679         iはint
 680         """
 681         db = self.connect_db()
 682         db[1].execute("\
 683         UPDATE in_status SET bscsrec=bscsrec+%s",i)
 684         self.close_db(db)
 685     def change_b252ts_in_status(self,i):
 686         """
 687         statuをiだけ増減する
 688         iはint
 689         """
 690         db = self.connect_db()
 691         db[1].execute("\
 692         UPDATE in_status SET b252ts=b252ts+%s",i)
 693         self.close_db(db)
 694     def select_installed_in_status(self):
 695         db = self.connect_db()
 696         dbexe = db[1].execute("\
 697         SELECT ts2avi,terec,bscsrec,b252ts,installed \
 698         FROM in_status \
 699         ")
 700         ret = 0
 701         dls = []
 702         if dbexe > 0:
 703             dls = db[1].fetchall()
 704         self.close_db(db)
 705         for dl in dls:
 706             r = list(dl)
 707             ret=r[4]
 708         return ret
 709     def change_installed_in_status(self):
 710         """
 711         installedを設定する
 712         """
 713         db = self.connect_db()
 714         db[1].execute("\
 715         UPDATE in_status SET installed=1")
 716         self.close_db(db)
 717     def new_epg_timeline(self, bctype):
 718         db = self.connect_db()
 719         try:
 720             db[1].execute("\
 721             DELETE FROM epg_timeline \
 722             WHERE bctype = %s", \
 723                           (bctype,))
 724         except:
 725             ""
 726         try:
 727             db[1].execute("\
 728             CREATE TABLE epg_timeline \
 729             (\
 730             bctype VARCHAR(20),\
 731             channel VARCHAR(100) NOT NULL,\
 732             start VARCHAR(30),\
 733             stop  VARCHAR(30),\
 734             title VARCHAR(100),\
 735             exp VARCHAR(200),\
 736             longexp TEXT,\
 737             category VARCHAR(100),\
 738             UNIQUE unitv(bctype,channel,start,stop,title)\
 739             )")
 740         except:
 741             ""
 742         #db.commit()
 743         self.close_db(db)
 744     def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
 745         db = self.connect_db()
 746         db[1].execute('\
 747         INSERT IGNORE INTO epg_timeline \
 748         VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
 749                       (bctype, channel, start, stop, title, desc,longdesc,category))
 750         #db.commit()
 751         self.close_db(db)
 752     def add_multi_epg_timeline(self, tvlists):
 753         """
 754         tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
 755         """
 756         db = self.connect_db()
 757         db[1].executemany('\
 758         INSERT IGNORE INTO epg_timeline \
 759         (bctype,channel,start,stop,title,exp,longexp,category) \
 760         values(%s,%s,%s,%s,%s,%s,%s,%s)', \
 761                           tvlists)
 762         self.close_db(db)
 763     def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
 764         db = self.connect_db()
 765         dbexe = "\
 766         SELECT \
 767         epg_ch.chtxt,title,start,stop,exp,longexp,category \
 768         FROM epg_timeline \
 769         INNER JOIN epg_ch \
 770         WHERE epg_ch.ontv=epg_timeline.channel \
 771         AND \
 772         start >= %s \
 773         AND \
 774         start <= %s \
 775         AND \
 776         epg_ch.chtxt=%s"
 777         dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
 778         retall = []
 779         if dbcmd > 0:
 780             retall = db[1].fetchall()
 781         self.close_db(db)
 782         return retall
 783     def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
 784         db = self.connect_db()
 785         dbexe = "\
 786         SELECT \
 787         epg_ch.chtxt,title,start,stop,exp,longexp,category \
 788         FROM epg_timeline \
 789         INNER JOIN epg_ch \
 790         WHERE epg_ch.ontv=epg_timeline.channel \
 791         AND \
 792         start >= %s \
 793         AND \
 794         stop <= %s \
 795         AND \
 796         ( \
 797         ( title LIKE \'%%"+keyword+"%%\' ) \
 798         OR \
 799         ( exp LIKE \'%%"+keyword+"%%\' ) \
 800         OR \
 801         ( longexp LIKE \'%%"+keyword+"%%\' ) \
 802         )"
 803         dbcmd = db[1].execute(dbexe,(btime, etime))
 804         retall = []
 805         if dbcmd > 0:
 806             retall = db[1].fetchall()
 807         self.close_db(db)
 808         return retall
 809     def new_in_auto_bayes_key(self):
 810         db = self.connect_db()
 811         try:
 812             db[1].execute('CREATE TABLE in_auto_bayes_key \
 813             (\
 814             keychar VARCHAR(10),\
 815             chtxt VARCHAR(20),\
 816             ratio_rec DECIMAL(32,14),\
 817             ratio_all DECIMAL(32,14),\
 818             UNIQUE unibayeskey(keychar,chtxt)\
 819             )')
 820             db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
 821         except:
 822             ""
 823         self.close_db(db)
 824
 825     def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
 826         db = self.connect_db()
 827         ratio_rec=str(ratio_rec)
 828         ratio_all=str(ratio_all)
 829         db[1].execute('\
 830         INSERT IGNORE INTO in_auto_bayes_key \
 831         (keychar,chtxt,ratio_rec,ratio_all) \
 832         values (%s,%s,%s,%s)',\
 833         (key,chtxt,ratio_rec,ratio_all))
 834         self.close_db(db)
 835     def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
 836         db = self.connect_db()
 837         add_rec_num=str(add_rec_num)
 838         add_all_num=str(add_all_num)
 839         db[1].execute("\
 840         UPDATE in_auto_bayes_key SET ratio_rec=CONVERT(ratio_rec+%s,DECIMAL(32,14)),ratio_all=CONVERT(ratio_all+%s,DECIMAL(32,14)) WHERE keychar=\"NUM\" AND chtxt=%s",\
 841         (add_rec_num,add_all_num,chtxt))
 842         self.close_db(db)
 843     def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
 844         """
 845         """
 846         db = self.connect_db()
 847         db[1].execute("\
 848         UPDATE in_auto_bayes_key SET ratio_rec=%s,ratio_all=%s WHERE keychar=%s AND chtxt=%s",(str(new_ratio_rec),str(new_ratio_all),key,chtxt)\
 849         )
 850         self.close_db(db)
 851     def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
 852         beforenum=str(beforenum)
 853         newnum=str(newnum)
 854         db = self.connect_db()
 855         db[1].execute("\
 856         UPDATE in_auto_bayes_key SET ratio_all=CONVERT(ratio_all*%s/(%s+%s),DECIMAL(32,14)) WHERE chtxt=%s AND NOT (keychar=\"NUM\")",(beforenum,newnum,beforenum,chtxt)\
 857         )
 858         self.close_db(db)
 859     def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
 860         db = self.connect_db()
 861         beforenumf=beforenum
 862         beforenum=str(beforenum)
 863         db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
 864         VALUES (%s,%s,%s,%s)\
 865         ON DUPLICATE KEY UPDATE \
 866         ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
 867         self.close_db(db)
 868     def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
 869         """
 870         list={key:addnum}のリスト
 871         """
 872         beforenumf=beforenum
 873         beforenum=str(beforenum)
 874         db = self.connect_db()
 875         for i,j in list.items():
 876             retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
 877             db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
 878             VALUES (%s,%s,%s,%s)\
 879             ON DUPLICATE KEY UPDATE \
 880             ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
 881         self.close_db(db)
 882     def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
 883         beforenum=str(beforenum)
 884         newnum=str(newnum)
 885         db = self.connect_db()
 886         db[1].execute("\
 887         UPDATE in_auto_bayes_key SET ratio_rec=CONVERT(ratio_rec*%s/(%s+%s),DECIMAL(32,14)) WHERE chtxt=%s AND NOT (keychar=\"NUM\")",(beforenum,newnum,beforenum,chtxt)\
 888         )
 889         self.close_db(db)
 890     def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
 891         db = self.connect_db()
 892         beforenumf=beforenum
 893         beforenum=str(beforenum)
 894         db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
 895         VALUES (%s,%s,%s,%s)\
 896         ON DUPLICATE KEY UPDATE \
 897         ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
 898         self.close_db(db)
 899     def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
 900         beforenumf=beforenum
 901         beforenum=str(beforenum)
 902         db = self.connect_db()
 903         for i,j in list.items():
 904             retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
 905             db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
 906             VALUES (%s,%s,%s,%s)\
 907             ON DUPLICATE KEY UPDATE \
 908             ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
 909         self.close_db(db)
 910     def select_by_key_in_auto_bayes_key(self,key,chtxt):
 911         db = self.connect_db()
 912         dbexe = db[1].execute("\
 913         SELECT keychar,chtxt,ratio_rec,ratio_all \
 914         FROM in_auto_bayes_key \
 915         WHERE keychar = %s AND chtxt = %s", \
 916                               (key,chtxt))
 917         dls = []
 918         if dbexe > 0:
 919             dls = db[1].fetchall()
 920         self.close_db(db)
 921         if len(dls)>0:
 922             return dls[0]
 923         else:
 924             return dls
 925
 926     def new_auto_timeline_keyword(self):
 927         db = self.connect_db()
 928         try:
 929             db[1].execute('\
 930             CREATE TABLE auto_timeline_keyword \
 931             (\
 932             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
 933             chtxt VARCHAR(40),\
 934             title VARCHAR(100),\
 935             btime DATETIME,\
 936             etime DATETIME,\
 937             UNIQUE uni (chtxt,title,btime,etime)\
 938             )')
 939         except:
 940             ""
 941     def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
 942         db = self.connect_db()
 943         db[1].execute('\
 944         INSERT IGNORE into auto_timeline_keyword \
 945         (chtxt,title,btime,etime) \
 946         values (%s,%s,%s,%s)', \
 947                        (chtxt, title, btime, etime))
 948         ##db.commit()
 949         self.close_db(db)
 950     def delete_old_auto_timeline_keyword(self, dhour):
 951         db = self.connect_db()
 952         db[1].execute("\
 953         DELETE FROM auto_timeline_keyword \
 954         WHERE \
 955         btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
 956         self.close_db(db)
 957     def new_auto_timeline_bayes(self):
 958         db = self.connect_db()
 959         try:
 960             db[1].execute('\
 961             CREATE TABLE auto_timeline_bayes \
 962             (\
 963             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
 964             chtxt VARCHAR(40),\
 965             title VARCHAR(100),\
 966             btime DATETIME,\
 967             etime DATETIME,\
 968             point INT,\
 969             UNIQUE uni (chtxt,title,btime,etime)\
 970             )')
 971         except:
 972             ""
 973     def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
 974         db = self.connect_db()
 975         db[1].execute('\
 976         INSERT IGNORE into auto_timeline_bayes \
 977         (chtxt,title,btime,etime,point) \
 978         values (%s,%s,%s,%s,%s)', \
 979                       (chtxt, title, btime, etime,point))
 980         self.close_db(db)
 981     def delete_old_auto_timeline_bayes(self, dhour):
 982         db = self.connect_db()
 983         db[1].execute("\
 984         DELETE FROM auto_timeline_bayes \
 985         WHERE \
 986         btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
 987         self.close_db(db)