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