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_bytime_all_timeline(self,btime,etime):
 485         db = self.connect_db()
 486         recdatum = []
 487         #dbr=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE btime < DATE_SUB(now(),INTERVAL "+dminutes+" MINUTE ) AND btime > DATE_ADD(now(),INTERVAL "+dminutes+" MINUTE )")
 488         dbr = db[1].execute("SELECT \
 489         type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
 490         FROM timeline \
 491         WHERE btime > %s AND \
 492         etime < %s",(btime,etime))
 493         dbl = db[1].fetchall()
 494         self.close_db(db)
 495         #recdblist.printutf8(dbl)
 496         if dbr > 0:
 497             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 498                 ret = {}
 499                 ret['type'] = typet
 500                 ret['chtxt'] = chtxt
 501                 ret['title'] = title
 502                 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
 503                 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
 504                 ret['btime'] = btime
 505                 ret['etime'] = etime
 506                 ret['opt'] = opt
 507                 if deltatime == None or deltatime == "":
 508                     deltatime = "3"
 509                 if deltaday == None or deltaday == "":
 510                     deltaday = "7"
 511                 if typet == recdblist.REC_KEYWORD:
 512                     ret['deltatime'] = deltatime
 513                 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
 514                     ret['deltatime'] = deltatime
 515                     ret['deltaday'] = deltaday
 516                 recdatum.append(ret)
 517         return recdatum
 518     def count_schedule_timeline(self, btime, etime):
 519         """
 520         count rectasknum
 521         return [te num,bs/cs num]
 522         """
 523         db = self.connect_db()
 524         dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
 525         dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
 526         Srec = 0
 527         Trec = 0
 528         db[1].execute(dbexe, (btime, etime,btime,etime))
 529         dbl=db[1].fetchall()
 530         for typet, bctypet, chtxtt, titlet in dbl:
 531             if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
 532                 if bctypet.find("cs") > -1:
 533                     Srec = Srec + 1
 534                 elif bctypet.find("bs") > -1:
 535                     Srec = Srec + 1
 536                 elif bctypet.find("te") > -1:
 537                     Trec = Trec + 1
 538         self.close_db(db)
 539         return [Trec, Srec]
 540     def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
 541         db = self.connect_db()
 542         recdatum = []
 543         #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 )")
 544         dbr = db[1].execute("SELECT \
 545         type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
 546         FROM timeline \
 547         WHERE btime > %s AND \
 548         etime < %s\
 549         AND chtxt=%s ",(btime,etime,chtxt))
 550         dbl = db[1].fetchall()
 551         self.close_db(db)
 552         #recdblist.printutf8(dbl)
 553         if dbr > 0:
 554             for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
 555                 ret = {}
 556                 ret['type'] = typet
 557                 ret['chtxt'] = chtxt
 558                 ret['title'] = title
 559                 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
 560                 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
 561                 ret['btime'] = btime
 562                 ret['etime'] = etime
 563                 ret['opt'] = opt
 564                 if deltatime == None or deltatime == "":
 565                     deltatime = "3"
 566                 if deltaday == None or deltaday == "":
 567                     deltaday = "7"
 568                 if typet == recdblist.REC_KEYWORD:
 569                     ret['deltatime'] = deltatime
 570                 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
 571                     ret['deltatime'] = deltatime
 572                     ret['deltaday'] = deltaday
 573                 recdatum.append(ret)
 574         return recdatum
 575     def delete_old_timeline(self, dhour):
 576         db = self.connect_db()
 577         db[1].execute("\
 578         DELETE FROM timeline \
 579         WHERE \
 580         btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
 581         #db.commit()
 582         self.close_db(db)
 583     def new_in_auto_jbk_key(self):
 584         db = self.connect_db()
 585         try:
 586             db[1].execute("\
 587             CREATE TABLE in_auto_jbk_key \
 588             (\
 589             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
 590             keyword VARCHAR(200),\
 591             UNIQUE unijbk (keyword)\
 592             )")
 593         except:
 594             ""
 595         self.close_db(db)
 596     def add_in_auto_jbk_key(self,key):
 597         db = self.connect_db()
 598         db[1].execute('\
 599         INSERT IGNORE into in_auto_jbk_key \
 600         (keyword) \
 601         values (%s)', \
 602                       (key,))
 603         ##db.commit()
 604         self.close_db(db)
 605     def select_all_in_auto_jbk_key(self):
 606         db = self.connect_db()
 607         dbexe = db[1].execute("\
 608         SELECT keyword \
 609         FROM in_auto_jbk_key \
 610         ")
 611         ret = []
 612         if dbexe > 0:
 613             ret = db[1].fetchall()
 614         self.close_db(db)
 615         return ret
 616     def drop_in_status(self):
 617         db = self.connect_db()
 618         try:
 619             db[1].execute('drop table in_status')
 620         except:
 621             ""
 622         self.close_db(db)
 623     def new_in_status(self):
 624         db = self.connect_db()
 625         try:
 626             db[1].execute("\
 627             CREATE TABLE in_status \
 628             (\
 629             ts2avi TINYINT DEFAULT 0,\
 630             terec TINYINT DEFAULT 0,\
 631             bscsrec TINYINT DEFAULT 0,\
 632             b252ts TINYINT DEFAULT 0,\
 633             installed TINYINT DEFAULT 0\
 634             )")
 635             db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0)")
 636         except:
 637             ""
 638         self.close_db(db)
 639     def select_all_in_status(self):
 640         db = self.connect_db()
 641         dbexe = db[1].execute("\
 642         SELECT ts2avi,terec,bscsrec,b252ts \
 643         FROM in_status \
 644         ")
 645         ret = []
 646         dls = []
 647         if dbexe > 0:
 648             dls = db[1].fetchall()
 649         self.close_db(db)
 650         for dl in dls:
 651             r = list(dl)
 652             r[0]=str(r[0])
 653             r[1]=str(r[1])
 654             r[2]=str(r[2])
 655             r[3]=str(r[3])
 656             ret.append(r)
 657         return ret
 658
 659     def change_ts2avi_in_status(self,i):
 660         """
 661         statuをiだけ増減する
 662         iはint
 663         """
 664         db = self.connect_db()
 665         db[1].execute("\
 666         UPDATE in_status SET ts2avi=ts2avi+%s",i)
 667         self.close_db(db)
 668     def change_terec_in_status(self,i):
 669         """
 670         statuをiだけ増減する
 671         iはint
 672         """
 673         db = self.connect_db()
 674         db[1].execute("\
 675         UPDATE in_status SET terec=terec+%s",i)
 676         self.close_db(db)
 677     def change_bscsrec_in_status(self,i):
 678         """
 679         statuをiだけ増減する
 680         iはint
 681         """
 682         db = self.connect_db()
 683         db[1].execute("\
 684         UPDATE in_status SET bscsrec=bscsrec+%s",i)
 685         self.close_db(db)
 686     def change_b252ts_in_status(self,i):
 687         """
 688         statuをiだけ増減する
 689         iはint
 690         """
 691         db = self.connect_db()
 692         db[1].execute("\
 693         UPDATE in_status SET b252ts=b252ts+%s",i)
 694         self.close_db(db)
 695     def select_installed_in_status(self):
 696         db = self.connect_db()
 697         dbexe = db[1].execute("\
 698         SELECT ts2avi,terec,bscsrec,b252ts,installed \
 699         FROM in_status \
 700         ")
 701         ret = 0
 702         dls = []
 703         if dbexe > 0:
 704             dls = db[1].fetchall()
 705         self.close_db(db)
 706         for dl in dls:
 707             r = list(dl)
 708             ret=r[4]
 709         return ret
 710     def change_installed_in_status(self):
 711         """
 712         installedを設定する
 713         """
 714         db = self.connect_db()
 715         db[1].execute("\
 716         UPDATE in_status SET installed=1")
 717         self.close_db(db)
 718     def new_epg_timeline(self, bctype):
 719         db = self.connect_db()
 720         try:
 721             db[1].execute("\
 722             DELETE FROM epg_timeline \
 723             WHERE bctype = %s", \
 724                           (bctype,))
 725         except:
 726             ""
 727         try:
 728             db[1].execute("\
 729             CREATE TABLE epg_timeline \
 730             (\
 731             bctype VARCHAR(20),\
 732             channel VARCHAR(100) NOT NULL,\
 733             start VARCHAR(30),\
 734             stop  VARCHAR(30),\
 735             title VARCHAR(100),\
 736             exp VARCHAR(200),\
 737             longexp TEXT,\
 738             category VARCHAR(100),\
 739             UNIQUE unitv(bctype,channel,start,stop,title)\
 740             )")
 741         except:
 742             ""
 743         #db.commit()
 744         self.close_db(db)
 745     def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
 746         db = self.connect_db()
 747         db[1].execute('\
 748         INSERT IGNORE INTO epg_timeline \
 749         VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
 750                       (bctype, channel, start, stop, title, desc,longdesc,category))
 751         #db.commit()
 752         self.close_db(db)
 753     def add_multi_epg_timeline(self, tvlists):
 754         """
 755         tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
 756         """
 757         db = self.connect_db()
 758         db[1].executemany('\
 759         INSERT IGNORE INTO epg_timeline \
 760         (bctype,channel,start,stop,title,exp,longexp,category) \
 761         values(%s,%s,%s,%s,%s,%s,%s,%s)', \
 762                           tvlists)
 763         self.close_db(db)
 764     def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
 765         db = self.connect_db()
 766         dbexe = "\
 767         SELECT \
 768         epg_ch.chtxt,title,start,stop,exp,longexp,category \
 769         FROM epg_timeline \
 770         INNER JOIN epg_ch \
 771         WHERE epg_ch.ontv=epg_timeline.channel \
 772         AND \
 773         start >= %s \
 774         AND \
 775         start <= %s \
 776         AND \
 777         epg_ch.chtxt=%s"
 778         dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
 779         retall = []
 780         if dbcmd > 0:
 781             retall = db[1].fetchall()
 782         self.close_db(db)
 783         return retall
 784     def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
 785         db = self.connect_db()
 786         dbexe = "\
 787         SELECT \
 788         epg_ch.chtxt,title,start,stop,exp,longexp,category \
 789         FROM epg_timeline \
 790         INNER JOIN epg_ch \
 791         WHERE epg_ch.ontv=epg_timeline.channel \
 792         AND \
 793         start >= %s \
 794         AND \
 795         stop <= %s \
 796         AND \
 797         ( \
 798         ( title LIKE \'%%"+keyword+"%%\' ) \
 799         OR \
 800         ( exp LIKE \'%%"+keyword+"%%\' ) \
 801         OR \
 802         ( longexp LIKE \'%%"+keyword+"%%\' ) \
 803         )"
 804         dbcmd = db[1].execute(dbexe,(btime, etime))
 805         retall = []
 806         if dbcmd > 0:
 807             retall = db[1].fetchall()
 808         self.close_db(db)
 809         return retall
 810     def new_in_auto_bayes_key(self):
 811         db = self.connect_db()
 812         try:
 813             db[1].execute('CREATE TABLE in_auto_bayes_key \
 814             (\
 815             keychar VARCHAR(10),\
 816             chtxt VARCHAR(20),\
 817             ratio_rec DECIMAL(32,14),\
 818             ratio_all DECIMAL(32,14),\
 819             UNIQUE unibayeskey(keychar,chtxt)\
 820             )')
 821             db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
 822         except:
 823             ""
 824         self.close_db(db)
 825
 826     def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
 827         db = self.connect_db()
 828         ratio_rec=str(ratio_rec)
 829         ratio_all=str(ratio_all)
 830         db[1].execute('\
 831         INSERT IGNORE INTO in_auto_bayes_key \
 832         (keychar,chtxt,ratio_rec,ratio_all) \
 833         values (%s,%s,%s,%s)',\
 834         (key,chtxt,ratio_rec,ratio_all))
 835         self.close_db(db)
 836     def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
 837         db = self.connect_db()
 838         add_rec_num=str(add_rec_num)
 839         add_all_num=str(add_all_num)
 840         db[1].execute("\
 841         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",\
 842         (add_rec_num,add_all_num,chtxt))
 843         self.close_db(db)
 844     def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
 845         """
 846         """
 847         db = self.connect_db()
 848         db[1].execute("\
 849         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)\
 850         )
 851         self.close_db(db)
 852     def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
 853         beforenum=str(beforenum)
 854         newnum=str(newnum)
 855         db = self.connect_db()
 856         db[1].execute("\
 857         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)\
 858         )
 859         self.close_db(db)
 860     def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
 861         db = self.connect_db()
 862         beforenumf=beforenum
 863         beforenum=str(beforenum)
 864         db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
 865         VALUES (%s,%s,%s,%s)\
 866         ON DUPLICATE KEY UPDATE \
 867         ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
 868         self.close_db(db)
 869     def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
 870         """
 871         list={key:addnum}のリスト
 872         """
 873         beforenumf=beforenum
 874         beforenum=str(beforenum)
 875         db = self.connect_db()
 876         for i,j in list.items():
 877             retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
 878             db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
 879             VALUES (%s,%s,%s,%s)\
 880             ON DUPLICATE KEY UPDATE \
 881             ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
 882         self.close_db(db)
 883     def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
 884         beforenum=str(beforenum)
 885         newnum=str(newnum)
 886         db = self.connect_db()
 887         db[1].execute("\
 888         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)\
 889         )
 890         self.close_db(db)
 891     def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
 892         db = self.connect_db()
 893         beforenumf=beforenum
 894         beforenum=str(beforenum)
 895         db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
 896         VALUES (%s,%s,%s,%s)\
 897         ON DUPLICATE KEY UPDATE \
 898         ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
 899         self.close_db(db)
 900     def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
 901         beforenumf=beforenum
 902         beforenum=str(beforenum)
 903         db = self.connect_db()
 904         for i,j in list.items():
 905             retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
 906             db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
 907             VALUES (%s,%s,%s,%s)\
 908             ON DUPLICATE KEY UPDATE \
 909             ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
 910         self.close_db(db)
 911     def select_by_key_in_auto_bayes_key(self,key,chtxt):
 912         db = self.connect_db()
 913         dbexe = db[1].execute("\
 914         SELECT keychar,chtxt,ratio_rec,ratio_all \
 915         FROM in_auto_bayes_key \
 916         WHERE keychar = %s AND chtxt = %s", \
 917                               (key,chtxt))
 918         dls = []
 919         if dbexe > 0:
 920             dls = db[1].fetchall()
 921         self.close_db(db)
 922         if len(dls)>0:
 923             return dls[0]
 924         else:
 925             return dls
 926
 927     def new_auto_timeline_keyword(self):
 928         db = self.connect_db()
 929         try:
 930             db[1].execute('\
 931             CREATE TABLE auto_timeline_keyword \
 932             (\
 933             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
 934             chtxt VARCHAR(40),\
 935             title VARCHAR(100),\
 936             btime DATETIME,\
 937             etime DATETIME,\
 938             UNIQUE uni (chtxt,title,btime,etime)\
 939             )')
 940         except:
 941             ""
 942     def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
 943         db = self.connect_db()
 944         db[1].execute('\
 945         INSERT IGNORE into auto_timeline_keyword \
 946         (chtxt,title,btime,etime) \
 947         values (%s,%s,%s,%s)', \
 948                        (chtxt, title, btime, etime))
 949         ##db.commit()
 950         self.close_db(db)
 951     def delete_old_auto_timeline_keyword(self, dhour):
 952         db = self.connect_db()
 953         db[1].execute("\
 954         DELETE FROM auto_timeline_keyword \
 955         WHERE \
 956         btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
 957         self.close_db(db)
 958     def new_auto_timeline_bayes(self):
 959         db = self.connect_db()
 960         try:
 961             db[1].execute('\
 962             CREATE TABLE auto_timeline_bayes \
 963             (\
 964             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
 965             chtxt VARCHAR(40),\
 966             title VARCHAR(100),\
 967             btime DATETIME,\
 968             etime DATETIME,\
 969             point INT,\
 970             UNIQUE uni (chtxt,title,btime,etime)\
 971             )')
 972         except:
 973             ""
 974     def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
 975         db = self.connect_db()
 976         db[1].execute('\
 977         INSERT IGNORE into auto_timeline_bayes \
 978         (chtxt,title,btime,etime,point) \
 979         values (%s,%s,%s,%s,%s)', \
 980                       (chtxt, title, btime, etime,point))
 981         self.close_db(db)
 982     def delete_old_auto_timeline_bayes(self, dhour):
 983         db = self.connect_db()
 984         db[1].execute("\
 985         DELETE FROM auto_timeline_bayes \
 986         WHERE \
 987         btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
 988         self.close_db(db)