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