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