rec10/trunk/src/dbMySQL.py

   1 #!/usr/bin/python
   2 # coding: UTF-8
   3 # Rec10 TS Recording Tools
   4 # Copyright (C) 2009-2010 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 add_auto_proc(self,type,title,chtxt):
 302         db = self.connect_db()
 303         db[1].execute('\
 304         INSERT IGNORE into auto_proc \
 305         (type,title,chtxt) \
 306         values (%s,%s,%s)',(type,title,chtxt))
 307         ##db.commit()
 308         self.close_db(db)
 309     def drop_in_settings(self):
 310         db = self.connect_db()
 311         try:
 312             db[1].execute('drop table in_settings')
 313         except:
 314             ""
 315         self.close_db(db)
 316     def new_in_settings(self):
 317         db = self.connect_db()
 318         try:
 319             db[1].execute('\
 320             CREATE TABLE in_settings \
 321             (\
 322             auto_jbk TINYINT,\
 323             auto_bayes TINYINT,\
 324             auto_opt VARCHAR(20),\
 325             auto_del_tmp TINYINT\
 326             )')
 327             db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"G\",1)")
 328         except:
 329             ""
 330         self.close_db(db)
 331     def select_all_in_settings(self):
 332         db = self.connect_db()
 333         dbexe = db[1].execute("\
 334         SELECT auto_jbk,auto_bayes,auto_del_tmp,auto_opt \
 335         FROM in_settings \
 336         ")
 337         ret = []
 338         dls = []
 339         if dbexe > 0:
 340             dls = db[1].fetchall()
 341         self.close_db(db)
 342         for dl in dls:
 343             r = list(dl)
 344             r[0]=str(r[0])
 345             r[1]=str(r[1])
 346             r[2]=str(r[2])
 347             r[3]=r[3]
 348             ret.append(r)
 349         return ret
 350     def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
 351         db = self.connect_db()
 352         db[1].execute('\
 353         INSERT IGNORE into in_timeline_log \
 354         (chtxt,title,btime,etime,opt,exp,longexp,category) \
 355         values (%s,%s,%s,%s,%s,%s,%s,%s)', \
 356                       ( chtxt, title, btime, etime, opt,exp,longexp,category))
 357         ##db.commit()
 358         self.close_db(db)
 359     def del_in_timeline_log(self, title="", chtxt="", btime=""):
 360         """
 361
 362         """
 363         db = self.connect_db()
 364         db[1].execute("\
 365         DELETE FROM in_timeline_log \
 366         WHERE title = %s AND chtxt = %s AND btime = %s", \
 367                       (title, chtxt, btime))
 368         #db.commit()
 369         self.close_db(db)
 370     def new_in_timeline_log(self):
 371         db = self.connect_db()
 372         try:
 373             db[1].execute('\
 374             CREATE TABLE in_timeline_log \
 375             (\
 376             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
 377             chtxt VARCHAR(20),\
 378             title VARCHAR(100),\
 379             btime DATETIME,\
 380             etime DATETIME,\
 381             opt VARCHAR(20),\
 382             exp VARCHAR(200),\
 383             longexp TEXT,\
 384             category VARCHAR(100),\
 385             UNIQUE uni (chtxt,title,btime,category)\
 386             )')
 387         except:
 388             ""
 389         self.close_db(db)
 390     def select_chtxt_by_title_timeline_log(self,title):
 391         db = self.connect_db()
 392         dbexe = db[1].execute("\
 393         SELECT chtxt \
 394         FROM in_timeline_log \
 395         WHERE title LIKE \"%"+title+"%\"\
 396         GROUP by chtxt\
 397         ORDER by sum(1) DESC limit 1")
 398         retdb=db[1].fetchall()
 399         ret=""
 400         if ret!=None:
 401             if len(retdb)>0:
 402                 ret=retdb[0][0]
 403         self.close_db(db)
 404         return ret
 405     def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
 406         db = self.connect_db()
 407         db[1].execute('\
 408         INSERT IGNORE into timeline \
 409         (type,chtxt,title,btime,etime,deltatime,deltaday,opt) \
 410         values (%s,%s,%s,%s,%s,%s,%s,%s)', \
 411                       (type, chtxt, title, btime, etime, deltatime, deltaday, opt))
 412         ##db.commit()
 413         self.close_db(db)
 414     def del_timeline(self, type="", title="", chtxt="", btime=""):
 415         """
 416
 417         """
 418         db = self.connect_db()
 419         db[1].execute("\
 420         DELETE FROM timeline \
 421         WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
 422                       (type, title, chtxt, btime))
 423         #db.commit()
 424         self.close_db(db)
 425     def select_all_timeline(self):
 426         db = self.connect_db()
 427         recdata = []
 428         dbr = db[1].execute("\
 429         SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
 430         FROM timeline")
 431         dbl = db[1].fetchall()
 432         self.close_db(db)
 433         if dbr > 0:
 434             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 435                 ret = {}
 436                 ret['type'] = typet
 437                 ret['chtxt'] = chtxt
 438                 ret['title'] = title
 439                 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
 440                 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
 441                 ret['btime'] = btime
 442                 ret['etime'] = etime
 443                 ret['opt'] = opt
 444                 ret['deltatime'] = ""
 445                 ret['deltaday'] = ""
 446                 if deltatime == None:
 447                     deltatime = "3"
 448                 if deltaday == None:
 449                     deltaday = "7"
 450                 if typet == 'key':
 451                     ret['deltatime'] = deltatime
 452                 elif typet == 'keyevery':
 453                     ret['deltatime'] = deltatime
 454                     ret['deltaday'] = deltaday
 455                 recdata.append(ret)
 456         self.close_db(db)
 457         return recdata
 458     def select_bytime_timeline(self, dminutes):
 459         db = self.connect_db()
 460         recdatum = []
 461         #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 )")
 462         dbr = db[1].execute("SELECT \
 463         type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
 464         FROM timeline \
 465         WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
 466         DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
 467         dbl = db[1].fetchall()
 468         self.close_db(db)
 469         #recdblist.printutf8(dbl)
 470         if dbr > 0:
 471             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 472                 ret = {}
 473                 ret['type'] = typet
 474                 ret['chtxt'] = chtxt
 475                 ret['title'] = title
 476                 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
 477                 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
 478                 ret['btime'] = btime
 479                 ret['etime'] = etime
 480                 ret['opt'] = opt
 481                 if deltatime == None or deltatime == "":
 482                     deltatime = "3"
 483                 if deltaday == None or deltaday == "":
 484                     deltaday = "7"
 485                 if typet == recdblist.REC_KEYWORD:
 486                     ret['deltatime'] = deltatime
 487                 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
 488                     ret['deltatime'] = deltatime
 489                     ret['deltaday'] = deltaday
 490                 recdatum.append(ret)
 491         return recdatum
 492     def select_by_name_time_timeline(self,title,btime,btime2):
 493         db = self.connect_db()
 494         recdatum = []
 495         #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 )")
 496         dbr = db[1].execute("SELECT \
 497         type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
 498         FROM timeline \
 499         WHERE btime > %s AND \
 500         btime < %s AND title = %s",(btime,btime2,title))
 501         dbl = db[1].fetchall()
 502         self.close_db(db)
 503         #recdblist.printutf8(dbl)
 504         if dbr > 0:
 505             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 506                 ret = {}
 507                 ret['type'] = typet
 508                 ret['chtxt'] = chtxt
 509                 ret['title'] = title
 510                 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
 511                 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
 512                 ret['btime'] = btime
 513                 ret['etime'] = etime
 514                 ret['opt'] = opt
 515                 if deltatime == None or deltatime == "":
 516                     deltatime = "3"
 517                 if deltaday == None or deltaday == "":
 518                     deltaday = "7"
 519                 if typet == recdblist.REC_KEYWORD:
 520                     ret['deltatime'] = deltatime
 521                 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
 522                     ret['deltatime'] = deltatime
 523                     ret['deltaday'] = deltaday
 524                 recdatum.append(ret)
 525         return recdatum
 526     def select_bytime_all_timeline(self,btime,etime):
 527         db = self.connect_db()
 528         recdatum = []
 529         #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 )")
 530         dbr = db[1].execute("SELECT \
 531         type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
 532         FROM timeline \
 533         WHERE btime > %s AND \
 534         etime < %s",(btime,etime))
 535         dbl = db[1].fetchall()
 536         self.close_db(db)
 537         #recdblist.printutf8(dbl)
 538         if dbr > 0:
 539             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 540                 ret = {}
 541                 ret['type'] = typet
 542                 ret['chtxt'] = chtxt
 543                 ret['title'] = title
 544                 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
 545                 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
 546                 ret['btime'] = btime
 547                 ret['etime'] = etime
 548                 ret['opt'] = opt
 549                 if deltatime == None or deltatime == "":
 550                     deltatime = "3"
 551                 if deltaday == None or deltaday == "":
 552                     deltaday = "7"
 553                 if typet == recdblist.REC_KEYWORD:
 554                     ret['deltatime'] = deltatime
 555                 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
 556                     ret['deltatime'] = deltatime
 557                     ret['deltaday'] = deltaday
 558                 recdatum.append(ret)
 559         return recdatum
 560     def count_schedule_timeline(self, btime, etime):
 561         """
 562         count rectasknum
 563         return [te num,bs/cs num]
 564         """
 565         db = self.connect_db()
 566         dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
 567         dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
 568         Srec = 0
 569         Trec = 0
 570         db[1].execute(dbexe, (btime, etime,btime,etime))
 571         dbl=db[1].fetchall()
 572         for typet, bctypet, chtxtt, titlet in dbl:
 573             if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
 574                 if bctypet.find("cs") > -1:
 575                     Srec = Srec + 1
 576                 elif bctypet.find("bs") > -1:
 577                     Srec = Srec + 1
 578                 elif bctypet.find("te") > -1:
 579                     Trec = Trec + 1
 580         self.close_db(db)
 581         return [Trec, Srec]
 582     def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
 583         db = self.connect_db()
 584         recdatum = []
 585         #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 )")
 586         dbr = db[1].execute("SELECT \
 587         type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
 588         FROM timeline \
 589         WHERE btime > %s AND \
 590         etime < %s\
 591         AND chtxt=%s ",(btime,etime,chtxt))
 592         dbl = db[1].fetchall()
 593         self.close_db(db)
 594         #recdblist.printutf8(dbl)
 595         if dbr > 0:
 596             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 597                 ret = {}
 598                 ret['type'] = typet
 599                 ret['chtxt'] = chtxt
 600                 ret['title'] = title
 601                 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
 602                 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
 603                 ret['btime'] = btime
 604                 ret['etime'] = etime
 605                 ret['opt'] = opt
 606                 if deltatime == None or deltatime == "":
 607                     deltatime = "3"
 608                 if deltaday == None or deltaday == "":
 609                     deltaday = "7"
 610                 if typet == recdblist.REC_KEYWORD:
 611                     ret['deltatime'] = deltatime
 612                 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
 613                     ret['deltatime'] = deltatime
 614                     ret['deltaday'] = deltaday
 615                 recdatum.append(ret)
 616         return recdatum
 617     def delete_old_timeline(self, dhour):
 618         db = self.connect_db()
 619         db[1].execute("\
 620         DELETE FROM timeline \
 621         WHERE \
 622         btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
 623         #db.commit()
 624         self.close_db(db)
 625     def new_in_auto_jbk_key(self):
 626         db = self.connect_db()
 627         try:
 628             db[1].execute("\
 629             CREATE TABLE in_auto_jbk_key \
 630             (\
 631             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
 632             keyword VARCHAR(200),\
 633             UNIQUE unijbk (keyword)\
 634             )")
 635         except:
 636             ""
 637         self.close_db(db)
 638     def add_in_auto_jbk_key(self,key):
 639         db = self.connect_db()
 640         db[1].execute('\
 641         INSERT IGNORE into in_auto_jbk_key \
 642         (keyword) \
 643         values (%s)', \
 644                       (key,))
 645         ##db.commit()
 646         self.close_db(db)
 647     def select_all_in_auto_jbk_key(self):
 648         db = self.connect_db()
 649         dbexe = db[1].execute("\
 650         SELECT keyword \
 651         FROM in_auto_jbk_key \
 652         ")
 653         ret = []
 654         if dbexe > 0:
 655             ret = db[1].fetchall()
 656         self.close_db(db)
 657         return ret
 658     def drop_in_status(self):
 659         db = self.connect_db()
 660         try:
 661             db[1].execute('drop table in_status')
 662         except:
 663             ""
 664         self.close_db(db)
 665     def new_in_status(self):
 666         db = self.connect_db()
 667         try:
 668             db[1].execute("\
 669             CREATE TABLE in_status \
 670             (\
 671             ts2avi TINYINT DEFAULT 0,\
 672             terec TINYINT DEFAULT 0,\
 673             bscsrec TINYINT DEFAULT 0,\
 674             b252ts TINYINT DEFAULT 0,\
 675             installed TINYINT DEFAULT 0\
 676             )")
 677             db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0)")
 678         except:
 679             ""
 680         self.close_db(db)
 681     def select_all_in_status(self):
 682         db = self.connect_db()
 683         dbexe = db[1].execute("\
 684         SELECT ts2avi,terec,bscsrec,b252ts \
 685         FROM in_status \
 686         ")
 687         ret = []
 688         dls = []
 689         if dbexe > 0:
 690             dls = db[1].fetchall()
 691         self.close_db(db)
 692         for dl in dls:
 693             r = list(dl)
 694             r[0]=str(r[0])
 695             r[1]=str(r[1])
 696             r[2]=str(r[2])
 697             r[3]=str(r[3])
 698             ret.append(r)
 699         return ret
 700
 701     def change_ts2avi_in_status(self,i):
 702         """
 703         statuをiだけ増減する
 704         iはint
 705         """
 706         db = self.connect_db()
 707         db[1].execute("\
 708         UPDATE in_status SET ts2avi=ts2avi+%s",i)
 709         self.close_db(db)
 710     def change_terec_in_status(self,i):
 711         """
 712         statuをiだけ増減する
 713         iはint
 714         """
 715         db = self.connect_db()
 716         db[1].execute("\
 717         UPDATE in_status SET terec=terec+%s",i)
 718         self.close_db(db)
 719     def change_bscsrec_in_status(self,i):
 720         """
 721         statuをiだけ増減する
 722         iはint
 723         """
 724         db = self.connect_db()
 725         db[1].execute("\
 726         UPDATE in_status SET bscsrec=bscsrec+%s",i)
 727         self.close_db(db)
 728     def change_b252ts_in_status(self,i):
 729         """
 730         statuをiだけ増減する
 731         iはint
 732         """
 733         db = self.connect_db()
 734         db[1].execute("\
 735         UPDATE in_status SET b252ts=b252ts+%s",i)
 736         self.close_db(db)
 737     def select_installed_in_status(self):
 738         db = self.connect_db()
 739         dbexe = db[1].execute("\
 740         SELECT ts2avi,terec,bscsrec,b252ts,installed \
 741         FROM in_status \
 742         ")
 743         ret = 0
 744         dls = []
 745         if dbexe > 0:
 746             dls = db[1].fetchall()
 747         self.close_db(db)
 748         for dl in dls:
 749             r = list(dl)
 750             ret=r[4]
 751         return ret
 752     def change_installed_in_status(self):
 753         """
 754         installedを設定する
 755         """
 756         db = self.connect_db()
 757         db[1].execute("\
 758         UPDATE in_status SET installed=1")
 759         self.close_db(db)
 760     def new_epg_timeline(self, bctype):
 761         db = self.connect_db()
 762         try:
 763             db[1].execute("\
 764             DELETE FROM epg_timeline \
 765             WHERE bctype = %s", \
 766                           (bctype,))
 767         except:
 768             ""
 769         try:
 770             db[1].execute("\
 771             CREATE TABLE epg_timeline \
 772             (\
 773             bctype VARCHAR(20),\
 774             channel VARCHAR(100) NOT NULL,\
 775             start VARCHAR(30),\
 776             stop  VARCHAR(30),\
 777             title VARCHAR(100),\
 778             exp VARCHAR(200),\
 779             longexp TEXT,\
 780             category VARCHAR(100),\
 781             UNIQUE unitv(bctype,channel,start,stop,title)\
 782             )")
 783         except:
 784             ""
 785         #db.commit()
 786         self.close_db(db)
 787     def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
 788         db = self.connect_db()
 789         db[1].execute('\
 790         INSERT IGNORE INTO epg_timeline \
 791         VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
 792                       (bctype, channel, start, stop, title, desc,longdesc,category))
 793         #db.commit()
 794         self.close_db(db)
 795     def add_multi_epg_timeline(self, tvlists):
 796         """
 797         tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
 798         """
 799         db = self.connect_db()
 800         db[1].executemany('\
 801         INSERT IGNORE INTO epg_timeline \
 802         (bctype,channel,start,stop,title,exp,longexp,category) \
 803         values(%s,%s,%s,%s,%s,%s,%s,%s)', \
 804                           tvlists)
 805         self.close_db(db)
 806     def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
 807         db = self.connect_db()
 808         dbexe = "\
 809         SELECT \
 810         epg_ch.chtxt,title,start,stop,exp,longexp,category \
 811         FROM epg_timeline \
 812         INNER JOIN epg_ch \
 813         WHERE epg_ch.ontv=epg_timeline.channel \
 814         AND \
 815         start >= %s \
 816         AND \
 817         start <= %s \
 818         AND \
 819         epg_ch.chtxt=%s"
 820         dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
 821         retall = []
 822         if dbcmd > 0:
 823             retall = db[1].fetchall()
 824         self.close_db(db)
 825         return retall
 826     def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
 827         db = self.connect_db()
 828         dbexe = "\
 829         SELECT \
 830         epg_ch.chtxt,title,start,stop,exp,longexp,category \
 831         FROM epg_timeline \
 832         INNER JOIN epg_ch \
 833         WHERE epg_ch.ontv=epg_timeline.channel \
 834         AND \
 835         start >= %s \
 836         AND \
 837         stop <= %s \
 838         AND \
 839         ( \
 840         ( title LIKE \'%%"+keyword+"%%\' ) \
 841         OR \
 842         ( exp LIKE \'%%"+keyword+"%%\' ) \
 843         OR \
 844         ( longexp LIKE \'%%"+keyword+"%%\' ) \
 845         )"
 846         dbcmd = db[1].execute(dbexe,(btime, etime))
 847         retall = []
 848         if dbcmd > 0:
 849             retall = db[1].fetchall()
 850         self.close_db(db)
 851         return retall
 852     def new_in_auto_bayes_key(self):
 853         db = self.connect_db()
 854         try:
 855             db[1].execute('CREATE TABLE in_auto_bayes_key \
 856             (\
 857             keychar VARCHAR(10),\
 858             chtxt VARCHAR(20),\
 859             ratio_rec DECIMAL(32,14),\
 860             ratio_all DECIMAL(32,14),\
 861             UNIQUE unibayeskey(keychar,chtxt)\
 862             )')
 863             db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
 864         except:
 865             ""
 866         self.close_db(db)
 867
 868     def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
 869         db = self.connect_db()
 870         ratio_rec=str(ratio_rec)
 871         ratio_all=str(ratio_all)
 872         db[1].execute('\
 873         INSERT IGNORE INTO in_auto_bayes_key \
 874         (keychar,chtxt,ratio_rec,ratio_all) \
 875         values (%s,%s,%s,%s)',\
 876         (key,chtxt,ratio_rec,ratio_all))
 877         self.close_db(db)
 878     def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
 879         db = self.connect_db()
 880         add_rec_num=str(add_rec_num)
 881         add_all_num=str(add_all_num)
 882         db[1].execute("\
 883         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",\
 884         (add_rec_num,add_all_num,chtxt))
 885         self.close_db(db)
 886     def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
 887         """
 888         """
 889         db = self.connect_db()
 890         db[1].execute("\
 891         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)\
 892         )
 893         self.close_db(db)
 894     def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
 895         beforenum=str(beforenum)
 896         newnum=str(newnum)
 897         db = self.connect_db()
 898         db[1].execute("\
 899         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)\
 900         )
 901         self.close_db(db)
 902     def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
 903         db = self.connect_db()
 904         beforenumf=beforenum
 905         beforenum=str(beforenum)
 906         db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
 907         VALUES (%s,%s,%s,%s)\
 908         ON DUPLICATE KEY UPDATE \
 909         ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
 910         self.close_db(db)
 911     def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
 912         """
 913         list={key:addnum}のリスト
 914         """
 915         beforenumf=beforenum
 916         beforenum=str(beforenum)
 917         db = self.connect_db()
 918         for i,j in list.items():
 919             retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
 920             try:
 921                 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
 922                 VALUES (%s,%s,%s,%s)\
 923                 ON DUPLICATE KEY UPDATE \
 924                 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
 925             except:
 926                 ""
 927         self.close_db(db)
 928     def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
 929         beforenum=str(beforenum)
 930         newnum=str(newnum)
 931         db = self.connect_db()
 932         db[1].execute("\
 933         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)\
 934         )
 935         self.close_db(db)
 936     def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
 937         db = self.connect_db()
 938         beforenumf=beforenum
 939         beforenum=str(beforenum)
 940         db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
 941         VALUES (%s,%s,%s,%s)\
 942         ON DUPLICATE KEY UPDATE \
 943         ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
 944         self.close_db(db)
 945     def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
 946         beforenumf=beforenum
 947         beforenum=str(beforenum)
 948         db = self.connect_db()
 949         for i,j in list.items():
 950             retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
 951             db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
 952             VALUES (%s,%s,%s,%s)\
 953             ON DUPLICATE KEY UPDATE \
 954             ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
 955         self.close_db(db)
 956     def select_by_key_in_auto_bayes_key(self,key,chtxt):
 957         db = self.connect_db()
 958         dbexe = db[1].execute("\
 959         SELECT keychar,chtxt,ratio_rec,ratio_all \
 960         FROM in_auto_bayes_key \
 961         WHERE keychar = %s AND chtxt = %s", \
 962                               (key,chtxt))
 963         dls = []
 964         if dbexe > 0:
 965             dls = db[1].fetchall()
 966         self.close_db(db)
 967         if len(dls)>0:
 968             return dls[0]
 969         else:
 970             return dls
 971
 972     def new_auto_timeline_keyword(self):
 973         db = self.connect_db()
 974         try:
 975             db[1].execute('\
 976             CREATE TABLE auto_timeline_keyword \
 977             (\
 978             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
 979             chtxt VARCHAR(40),\
 980             title VARCHAR(100),\
 981             btime DATETIME,\
 982             etime DATETIME,\
 983             UNIQUE uni (chtxt,title,btime,etime)\
 984             )')
 985         except:
 986             ""
 987     def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
 988         db = self.connect_db()
 989         db[1].execute('\
 990         INSERT IGNORE into auto_timeline_keyword \
 991         (chtxt,title,btime,etime) \
 992         values (%s,%s,%s,%s)', \
 993                        (chtxt, title, btime, etime))
 994         ##db.commit()
 995         self.close_db(db)
 996     def delete_old_auto_timeline_keyword(self, dhour):
 997         db = self.connect_db()
 998         db[1].execute("\
 999         DELETE FROM auto_timeline_keyword \
1000         WHERE \
1001         btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1002         self.close_db(db)
1003     def new_auto_timeline_bayes(self):
1004         db = self.connect_db()
1005         try:
1006             db[1].execute('\
1007             CREATE TABLE auto_timeline_bayes \
1008             (\
1009             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1010             chtxt VARCHAR(40),\
1011             title VARCHAR(100),\
1012             btime DATETIME,\
1013             etime DATETIME,\
1014             point INT,\
1015             UNIQUE uni (chtxt,title,btime,etime)\
1016             )')
1017         except:
1018             ""
1019     def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
1020         db = self.connect_db()
1021         db[1].execute('\
1022         INSERT IGNORE into auto_timeline_bayes \
1023         (chtxt,title,btime,etime,point) \
1024         values (%s,%s,%s,%s,%s)', \
1025                       (chtxt, title, btime, etime,point))
1026         self.close_db(db)
1027     def delete_old_auto_timeline_bayes(self, dhour):
1028         db = self.connect_db()
1029         db[1].execute("\
1030         DELETE FROM auto_timeline_bayes \
1031         WHERE \
1032         btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1033         self.close_db(db)