X-Git-Url: http://git.osdn.net/view?p=rec10%2Frec10-git.git;a=blobdiff_plain;f=rec10%2Ftrunk%2Fsrc%2FdbMySQL.py;h=cce2e869784ffa9793bb351eea8c1cf7281ddfa8;hp=fb4da9d08a9617d95705a27a71059b66432baac5;hb=12414a3ba81d93316e351ab51aa49bb71cf42f60;hpb=1438bd816cc08f3f45d3b50e590edc710f039c4a diff --git a/rec10/trunk/src/dbMySQL.py b/rec10/trunk/src/dbMySQL.py index fb4da9d..cce2e86 100644 --- a/rec10/trunk/src/dbMySQL.py +++ b/rec10/trunk/src/dbMySQL.py @@ -1,9 +1,12 @@ #!/usr/bin/python # coding: UTF-8 # Rec10 TS Recording Tools -# Copyright (C) 2009 Yukikaze +# Copyright (C) 2009-2012 Yukikaze import MySQLdb import recdblist +import warnings +import traceback + from decimal import Decimal class DB_MySQL: dbname = "" @@ -12,6 +15,7 @@ class DB_MySQL: dbpasswd = "" dbport = 0 def __init__(self, dbname, user, passwd, host="localhost", port=3306): + warnings.filterwarnings('ignore', "Data truncated for column") self.dbname = dbname self.dbhost = host self.dbusr = user @@ -20,11 +24,12 @@ class DB_MySQL: try: con = MySQLdb.connect(user=user, passwd=passwd) cur = con.cursor() - cur.execute('CREATE DATABASE ' + dbname + " DEFAULT CHARACTER SET utf8") + cur.execute('CREATE DATABASE ' + dbname + " DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;") cur.close() con.close() - except: - "" + except Exception, inst: + if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)): + recdblist.addCommonlogEX("Error", "init (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200) db = self.connect_db() try: db[1].execute('\ @@ -32,24 +37,33 @@ class DB_MySQL: (\ id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\ type VARCHAR(40),\ - chtxt VARCHAR(20),\ + chtxt VARCHAR(40),\ title VARCHAR(100),\ btime DATETIME,\ etime DATETIME,\ deltatime VARCHAR(5),\ deltaday VARCHAR(5),\ opt VARCHAR(20),\ + epgtitle VARCHAR(100),\ + epgbtime DATETIME,\ + epgetime DATETIME,\ + epgduplicate TINYINT DEFAULT 0,\ + epgchange TINYINT DEFAULT 0,\ + epgexp VARCHAR(200),\ + epgcategory VARCHAR(100),\ + counter TINYINT DEFAULT -1,\ UNIQUE uni (type,chtxt,title,btime,deltaday)\ )') - except: - "" + except Exception, inst: + if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)): + recdblist.addCommonlogEX("Error", "init (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200) self.close_db(db) self.new_epg_timeline("") self.new_in_timeline_log() self.new_in_auto_bayes_key() self.new_in_auto_jbk_key() - self.new_auto_proc() self.new_in_status() + self.new_in_settings() self.new_auto_timeline_bayes() self.new_auto_timeline_keyword() def connect_db(self): @@ -58,6 +72,10 @@ class DB_MySQL: """ con = MySQLdb.connect(db=self.dbname, host=self.dbhost, port=self.dbport, user=self.dbusr, passwd=self.dbpasswd, charset="utf8") cur = con.cursor() + try: + con.autocommit(1) + except: + "" cur.execute('set names utf8;') return [con, cur] def close_db(self, db): @@ -67,38 +85,57 @@ class DB_MySQL: db = self.connect_db() try: db[1].execute('drop table epg_ch') - except: - "" + except Exception, inst: + if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))): + recdblist.addCommonlogEX("Error", "new_epg_ch drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200) + try: db[1].execute('\ CREATE TABLE epg_ch \ (\ bctype VARCHAR(15),\ - ontv VARCHAR(30) PRIMARY KEY,\ - chtxt VARCHAR(15),\ + chtxt VARCHAR(20) PRIMARY KEY,\ ch VARCHAR(20),\ csch VARCHAR(20),\ + tsid VARCHAR(20),\ chname VARCHAR(100),\ updatetime DATETIME,\ - status TINYINT\ + status TINYINT,\ + visible TINYINT DEFAULT 1,\ + logo0 BLOB,\ + logo1 BLOB,\ + logo2 BLOB,\ + logo3 BLOB,\ + logo4 BLOB,\ + logo5 BLOB,\ + logoupdate DATETIME,\ + logostatus TINYINT DEFAULT 2,\ + scanupdate DATETIME\ )') - except: - "" + except Exception, inst: + if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)): + recdblist.addCommonlogEX("Error", "new_epg_ch (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200) + self.close_db(db) - def add_epg_ch(self, bctype, ontv, chtxt, ch, csch, updatetime): + def add_epg_ch(self, bctype, chtxt, ch, csch,tsid,updatetime,logoupdate,scanupdate): db = self.connect_db() db[1].execute('\ - INSERT INTO epg_ch \ - VALUES (%s,%s,%s,%s,%s,"",%s,%s)', \ - (bctype, ontv, chtxt, ch, csch, updatetime, "1")) + INSERT INTO epg_ch (bctype,chtxt,ch,csch,tsid,chname,updatetime,status,visible,logoupdate,scanupdate)\ + VALUES (%s,%s,%s,%s,%s,"",%s,%s,%s,%s,%s)', \ + (bctype, chtxt, ch, csch,tsid,updatetime, "1","1",logoupdate,scanupdate)) self.close_db(db) - def select_by_ontv_epg_ch(self, ontv): + def delete_all_epg_ch(self): + db = self.connect_db() + db[1].execute('\ + DROP TABLE epg_ch ') + self.close_db(db) + def select_by_chtxt_epg_ch(self, chtxt): db = self.connect_db() dbexe = db[1].execute("\ - SELECT bctype,ontv,chtxt,ch,csch,updatetime \ + SELECT bctype,chtxt,ch,csch,tsid,updatetime,chname,status,visible,logoupdate,scanupdate \ FROM epg_ch \ - WHERE ontv = %s", \ - (ontv,)) + WHERE chtxt LIKE %s", \ + (chtxt,)) ret = [] dls = [] if dbexe > 0: @@ -107,52 +144,67 @@ class DB_MySQL: for dl in dls: r = list(dl) r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S") + if r[9]!=None: + r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S") + else: + r[9]="2012-04-01 00:00:00" + r[10] = r[10].strftime("%Y-%m-%d %H:%M:%S") ret.append(r) return ret - def select_by_chtxt_epg_ch(self, chtxt): + def select_by_bctype_epg_ch(self, bctype): db = self.connect_db() dbexe = db[1].execute("\ - SELECT bctype,ontv,chtxt,ch,csch,updatetime \ + SELECT bctype,chtxt,ch,csch,tsid,updatetime,status,chname,status,visible,logoupdate,scanupdate \ FROM epg_ch \ - WHERE chtxt = %s", \ - (chtxt,)) + WHERE bctype = %s", \ + (bctype,)) ret = [] dls = [] if dbexe > 0: dls = db[1].fetchall() self.close_db(db) for dl in dls: + #recdblist.printutf8(dl) r = list(dl) r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S") + if r[10]!=None: + r[10] = r[10].strftime("%Y-%m-%d %H:%M:%S") + else: + r[10]="2011-04-01 00:00:00" + r[11] = r[11].strftime("%Y-%m-%d %H:%M:%S") + r[6] = str(r[6]) ret.append(r) + import random + ret.insert(0,ret[random.randint(0,len(ret)-1)]) return ret - def select_by_bctype_epg_ch(self, bctype): + def select_by_ch_epg_ch(self, ch): db = self.connect_db() dbexe = db[1].execute("\ - SELECT bctype,ontv,chtxt,ch,csch,updatetime,status \ + SELECT \ + bctype,chtxt,ch,csch,tsid,updatetime,chname,status,visible,logoupdate,scanupdate \ FROM epg_ch \ - WHERE bctype = %s", \ - (bctype,)) + WHERE ch = %s", \ + (ch,)) ret = [] dls = [] if dbexe > 0: dls = db[1].fetchall() self.close_db(db) for dl in dls: - #print dl r = list(dl) r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S") - r[6] = str(r[6]) + r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S") + r[10] = r[10].strftime("%Y-%m-%d %H:%M:%S") ret.append(r) return ret - def select_by_ch_epg_ch(self, ch): + def select_by_csch_epg_ch(self, csch): db = self.connect_db() dbexe = db[1].execute("\ SELECT \ - bctype,ontv,chtxt,ch,csch,updatetime \ + bctype,chtxt,ch,csch,tsid,updatetime,chname,status,visible,logoupdate,scanupdate \ FROM epg_ch \ - WHERE ch = %s", \ - (ch,)) + WHERE csch = %s", \ + (csch,)) ret = [] dls = [] if dbexe > 0: @@ -161,12 +213,14 @@ class DB_MySQL: for dl in dls: r = list(dl) r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S") + r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S") + r[10] = r[10].strftime("%Y-%m-%d %H:%M:%S") ret.append(r) return ret def select_all_epg_ch(self): db = self.connect_db() dbexe = db[1].execute("\ - SELECT bctype,ontv,chtxt,ch,csch,updatetime \ + SELECT bctype,chtxt,ch,csch,tsid,updatetime,chname,status,visible,logoupdate,scanupdate \ FROM epg_ch \ ") ret = [] @@ -177,22 +231,63 @@ class DB_MySQL: for dl in dls: r = list(dl) r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S") + r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S") + r[10] = r[10].strftime("%Y-%m-%d %H:%M:%S") ret.append(r) return ret + def change_visible_epg_ch(self,chtxt,visible): + db = self.connect_db() + db[1].execute("\ + UPDATE epg_ch SET visible=%s WHERE chtxt=%s",(visible,chtxt)) + self.close_db(db) + def change_logodata_epg_ch(self,chtxt,logonum,logodata): + db = self.connect_db() + db[1].execute("\ + UPDATE epg_ch SET logo"+str(logonum)+"=%s WHERE chtxt=%s",(logodata,chtxt)) + self.close_db(db) + def set_new_status(self,dhour): + db = self.connect_db() + dbexe = db[1].execute("UPDATE epg_ch \ + SET status = 1 \ + WHERE \ + ( \ + updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \ + AND \ + status = 0 \ + )"\ + ) def select_get_update_epg_ch(self, dhour): db = self.connect_db() dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \ - WHERE \ + WHERE (\ ( \ updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \ AND \ status = 1 \ ) \ OR \ - status > 1 \ + status > 1 )\ + ORDER BY status DESC") + ret = [] + #recdblist.printutf8(dbexe) + if dbexe > 0: + ret = db[1].fetchall() + self.close_db(db) + return ret + def select_get_updatelogo_epg_ch(self, dhour): + db = self.connect_db() + dbexe = db[1].execute("SELECT bctype,chtxt,logostatus FROM epg_ch \ + WHERE (\ + ( \ + updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \ + AND \ + logostatus = 1 \ + ) \ + OR \ + logostatus > 1 )\ ORDER BY status DESC") ret = [] - #print dbexe + #recdblist.printutf8(dbexe) if dbexe > 0: ret = db[1].fetchall() self.close_db(db) @@ -206,6 +301,7 @@ class DB_MySQL: status = 1 \ WHERE bctype = %s", (bctype,)) self.close_db(db) + def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt): db = self.connect_db() db[1].execute("\ @@ -215,52 +311,149 @@ class DB_MySQL: status = 1\ WHERE bctype = %s AND chtxt = %s", (bctype, chtxt)) self.close_db(db) + def update_chname_by_chtxt_epg_ch(self,chtxt,chname): + db = self.connect_db() + db[1].execute("\ + UPDATE epg_ch \ + SET \ + chname = %s \ + WHERE chtxt = %s", (chname,chtxt)) + self.close_db(db) def update_status_by_bctype_epg_ch(self, bctype, status): db = self.connect_db() db[1].execute("\ UPDATE epg_ch \ SET \ - status=%s \ + status=%s , \ + updatetime=now() \ WHERE bctype = %s", \ (status, bctype)\ ) self.close_db(db) - def update_chname_by_ontv_epg_ch(self,ontv,chname): + def update_logostatus_by_bctype_epg_ch(self,bctype,logostatus): db = self.connect_db() db[1].execute("\ UPDATE epg_ch \ SET \ - chname=%s \ - WHERE ontv = %s", \ - (chname, ontv)\ + logostatus=%s , \ + logoupdate=now() \ + WHERE bctype = %s", \ + (logostatus, bctype)\ ) self.close_db(db) - def add_auto_proc(self,type,title): + def add_auto_proc_tmp(self,type,title,chtxt): db = self.connect_db() db[1].execute('\ - INSERT IGNORE into auto_proc \ - (type,title) \ - values (%s,%s)', \ - ( type, title)) + INSERT IGNORE into auto_proc_tmp \ + (type,title,chtxt) \ + values (%s,%s,%s)',(type,title,chtxt)) ##db.commit() self.close_db(db) + def new_auto_proc_tmp(self): + db = self.connect_db() + try: + db[1].execute('drop table auto_proc_tmp') + except Exception, inst: + if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))): + recdblist.addCommonlogEX("Error", "new_auto_proc_tmp drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200) + + try: + db[1].execute('\ + CREATE TABLE auto_proc_tmp \ + (\ + type VARCHAR(20),\ + title VARCHAR(100) PRIMARY KEY,\ + chtxt VARCHAR(30),\ + UNIQUE unibayeskey(title)\ + )') + except Exception, inst: + if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)): + recdblist.addCommonlogEX("Error", "new_auto_proc_tmp (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200) + + self.close_db(db) + def update_auto_proc(self): + db = self.connect_db() + try: + db[1].execute('INSERT INTO auto_proc SELECT * FROM auto_proc_tmp') + except Exception, inst: + if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)): + recdblist.addCommonlogEX("Error", "update_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200) + + self.close_db(db) def new_auto_proc(self): db = self.connect_db() try: db[1].execute('drop table auto_proc') - except: - "" + except Exception, inst: + if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))): + recdblist.addCommonlogEX("Error", "new_auto_proc drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200) + try: db[1].execute('\ CREATE TABLE auto_proc \ (\ type VARCHAR(20),\ title VARCHAR(100) PRIMARY KEY,\ + chtxt VARCHAR(30),\ UNIQUE unibayeskey(title)\ )') - except: - "" + except Exception, inst: + if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)): + recdblist.addCommonlogEX("Error", "new_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200) + + self.close_db(db) + def add_auto_proc(self,type,title,chtxt): + db = self.connect_db() + db[1].execute('\ + INSERT IGNORE into auto_proc \ + (type,title,chtxt) \ + values (%s,%s,%s)',(type,title,chtxt)) + ##db.commit() + self.close_db(db) + def drop_in_settings(self): + db = self.connect_db() + try: + db[1].execute('drop table in_settings') + except Exception, inst: + if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))): + recdblist.addCommonlogEX("Error", "drop_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200) + self.close_db(db) + def new_in_settings(self): + db = self.connect_db() + try: + db[1].execute('\ + CREATE TABLE in_settings \ + (\ + auto_jbk TINYINT,\ + auto_bayes TINYINT,\ + auto_opt VARCHAR(20),\ + auto_del_tmp TINYINT\ + )') + db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"H\",1)") + except Exception, inst: + if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)): + recdblist.addCommonlogEX("Error", "new_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200) + + self.close_db(db) + def select_all_in_settings(self): + db = self.connect_db() + dbexe = db[1].execute("\ + SELECT auto_jbk,auto_bayes,auto_del_tmp,auto_opt \ + FROM in_settings \ + ") + ret = [] + dls = [] + if dbexe > 0: + dls = db[1].fetchall() self.close_db(db) + for dl in dls: + r = list(dl) + r[0]=str(r[0]) + r[1]=str(r[1]) + r[2]=str(r[2]) + r[3]=r[3] + ret.append(r) + return ret def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""): db = self.connect_db() db[1].execute('\ @@ -298,16 +491,60 @@ class DB_MySQL: category VARCHAR(100),\ UNIQUE uni (chtxt,title,btime,category)\ )') - except: - "" + except Exception, inst: + if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)): + recdblist.addCommonlogEX("Error", "new_in_timeline_log (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200) + self.close_db(db) - def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""): + def select_chtxt_by_title_timeline_log(self,title): + db = self.connect_db() + dbexe = db[1].execute("\ + SELECT chtxt \ + FROM in_timeline_log \ + WHERE title LIKE \"%"+title+"%\"\ + GROUP by chtxt\ + ORDER by sum(1) DESC limit 1") + retdb=db[1].fetchall() + ret="" + if ret!=None: + if len(retdb)>0: + ret=retdb[0][0] + self.close_db(db) + return ret + def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt="" ,counter=-1): db = self.connect_db() db[1].execute('\ INSERT IGNORE into timeline \ - (type,chtxt,title,btime,etime,deltatime,deltaday,opt) \ - values (%s,%s,%s,%s,%s,%s,%s,%s)', \ - (type, chtxt, title, btime, etime, deltatime, deltaday, opt)) + (type,chtxt,title,btime,etime,deltatime,deltaday,opt,counter) \ + values (%s,%s,%s,%s,%s,%s,%s,%s,%s)', \ + (type, chtxt, title, btime, etime, deltatime, deltaday, opt ,counter)) + ##db.commit() + self.close_db(db) + def update_epg_timeline(self,type,chtxt,title,btime,epgbtime,epgetime,epgtitle,epgexp,epgcategory): + db = self.connect_db() + db[1].execute('\ + UPDATE timeline \ + SET epgbtime=%s,epgetime=%s,epgtitle=%s,epgexp=%s,epgcategory=%s \ + WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \ + (epgbtime,epgetime,epgtitle,epgexp,epgcategory,type, chtxt, title, btime)) + ##db.commit() + self.close_db(db) + def update_status_change_timeline(self,type,chtxt,title,btime,epgchange): + db = self.connect_db() + db[1].execute('\ + UPDATE timeline \ + SET epgchange =%s \ + WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \ + (epgchange , type, chtxt, title, btime)) + ##db.commit() + self.close_db(db) + def update_status_dup_timeline(self,type,chtxt,title,btime,epgduplicate): + db = self.connect_db() + db[1].execute('\ + UPDATE timeline \ + SET epgduplicate =%s \ + WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \ + (epgduplicate , type, chtxt, title, btime)) ##db.commit() self.close_db(db) def del_timeline(self, type="", title="", chtxt="", btime=""): @@ -325,68 +562,215 @@ class DB_MySQL: db = self.connect_db() recdata = [] dbr = db[1].execute("\ - SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \ + SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\ FROM timeline") dbl = db[1].fetchall() self.close_db(db) if dbr > 0: - for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl: - ret = {} - ret['type'] = typet - ret['chtxt'] = chtxt - ret['title'] = title - btime = btime.strftime("%Y-%m-%d %H:%M:%S") - etime = etime.strftime("%Y-%m-%d %H:%M:%S") - ret['btime'] = btime - ret['etime'] = etime - ret['opt'] = opt - ret['deltatime'] = "" - ret['deltaday'] = "" - if deltatime == None: - deltatime = "3" - if deltaday == None: - deltaday = "7" - if typet == 'key': - ret['deltatime'] = deltatime - elif typet == 'keyevery': - ret['deltatime'] = deltatime - ret['deltaday'] = deltaday - recdata.append(ret) - self.close_db(db) + recdata = self.getdic_timeline(dbl) return recdata def select_bytime_timeline(self, dminutes): db = self.connect_db() recdatum = [] #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 )") dbr = db[1].execute("SELECT \ - type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \ + type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\ FROM timeline \ WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \ DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )") dbl = db[1].fetchall() self.close_db(db) - #print dbl + #recdblist.printutf8(dbl) if dbr > 0: - for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl: - ret = {} - ret['type'] = typet - ret['chtxt'] = chtxt - ret['title'] = title - btime = btime.strftime("%Y-%m-%d %H:%M:%S") - etime = etime.strftime("%Y-%m-%d %H:%M:%S") - ret['btime'] = btime - ret['etime'] = etime - ret['opt'] = opt - if deltatime == None or deltatime == "": - deltatime = "3" - if deltaday == None or deltaday == "": - deltaday = "7" - if typet == recdblist.REC_KEYWORD: - ret['deltatime'] = deltatime - elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS: - ret['deltatime'] = deltatime - ret['deltaday'] = deltaday - recdatum.append(ret) + recdatum = self.getdic_timeline(dbl) + return recdatum + def select_by_name_time_timeline(self,title,btime,btime2): + db = self.connect_db() + recdatum = [] + #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 )") + dbr = db[1].execute("SELECT \ + type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\ + FROM timeline \ + WHERE btime > %s AND \ + btime < %s AND title = %s",(btime,btime2,title)) + dbl = db[1].fetchall() + self.close_db(db) + #recdblist.printutf8(dbl) + if dbr > 0: + recdatum = self.getdic_timeline(dbl) + return recdatum + def select_bytime_all_timeline(self,btime,etime): + db = self.connect_db() + recdatum = [] + #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 )") + dbr = db[1].execute("SELECT \ + type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\ + FROM timeline \ + WHERE btime >= %s AND \ + etime <= %s",(btime,etime)) + dbl = db[1].fetchall() + self.close_db(db) + #recdblist.printutf8(dbl) + if dbr > 0: + recdatum = self.getdic_timeline(dbl) + return recdatum + def select_byepgtime_all_timeline(self,epgbtime,epgetime): + db = self.connect_db() + recdatum = [] + #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 )") + dbr = db[1].execute("SELECT \ + type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\ + FROM timeline \ + WHERE epgbtime >= %s AND \ + epgetime <= %s",(epgbtime,epgetime)) + dbl = db[1].fetchall() + self.close_db(db) + #recdblist.printutf8(dbl) + if dbr > 0: + recdatum=self.getdic_timeline(dbl) + return recdatum + def select_byepgtime_over_timeline(self,epgbtime,epgetime): + db = self.connect_db() + recdatum = [] + #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 )") + dbr = db[1].execute("SELECT \ + type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\ + FROM timeline \ + WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"\ + ,(epgbtime,epgetime)) + dbl = db[1].fetchall() + self.close_db(db) + #recdblist.printutf8(dbl) + if dbr > 0: + recdatum=self.getdic_timeline(dbl) + return recdatum + def count_schedule_timeline(self, btime, etime): + """ + count rectasknum + return [te num,bs/cs num] + """ + db = self.connect_db() + dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt" + dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )" + Srec = 0 + Trec = 0 + db[1].execute(dbexe, (btime, etime,btime,etime)) + dbl=db[1].fetchall() + for typet, bctypet, chtxtt, titlet in dbl: + if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS): + if bctypet.find("cs") > -1: + Srec = Srec + 1 + elif bctypet.find("bs") > -1: + Srec = Srec + 1 + elif bctypet.find("te") > -1: + Trec = Trec + 1 + self.close_db(db) + return [Trec, Srec] + def search_schedule_timeline(self,btime,etime): + """ + count rectasknum + return [(type,bctype,chtxt,title,btime,etime)] + """ + db = self.connect_db() + dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title,timeline.btime,timeline.etime FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt" + dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )" + ret=[] + db[1].execute(dbexe, (btime, etime,btime,etime)) + dbl=db[1].fetchall() + for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl: + if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS): + ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet]) + self.close_db(db) + return ret + def count_epgschedule_timeline(self, epgbtime, epgetime): + """ + count rectasknum + return [te num,bs/cs num] + """ + db = self.connect_db() + dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt" + dbexe = dbexe + " WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))" + Srec = 0 + Trec = 0 + db[1].execute(dbexe, (epgbtime, epgetime)) + dbl=db[1].fetchall() + for typet, bctypet, chtxtt, titlet in dbl: + if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS): + if bctypet.find("cs") > -1: + Srec = Srec + 1 + elif bctypet.find("bs") > -1: + Srec = Srec + 1 + elif bctypet.find("te") > -1: + Trec = Trec + 1 + self.close_db(db) + return [Trec, Srec] + def search_epgschedule_timeline(self,epgbtime,epgetime): + """ + count rectasknum + return [(type,bctype,chtxt,title,btime,etime)] + """ + db = self.connect_db() + dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title,timeline.btime,timeline.etime FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt" + dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )" + ret=[] + db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime)) + dbl=db[1].fetchall() + for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl: + if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS): + ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet]) + self.close_db(db) + return ret + def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt): + db = self.connect_db() + recdatum = [] + #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 )") + dbr = db[1].execute("SELECT \ + type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\ + FROM timeline \ + WHERE btime > %s AND \ + etime < %s\ + AND chtxt=%s ",(btime,etime,chtxt)) + dbl = db[1].fetchall() + self.close_db(db) + #recdblist.printutf8(dbl) + if dbr > 0: + recdatum = self.getdic_timeline(dbl) + return recdatum + def getdic_timeline(self,timelinelists): + recdatum=[] + for typet, chtxt, title, btime, etime, deltatime, deltaday, opt ,epgbtimet , epgetimet ,epgtitlet ,epgduplicatet ,epgchanget ,countert in timelinelists: + ret = {} + ret['type'] = typet + ret['chtxt'] = chtxt + ret['title'] = title + btime = btime.strftime("%Y-%m-%d %H:%M:%S") + etime = etime.strftime("%Y-%m-%d %H:%M:%S") + ret['btime'] = btime + ret['etime'] = etime + ret['opt'] = opt + try: + ret['epgbtime'] = epgbtimet.strftime("%Y-%m-%d %H:%M:%S") + ret['epgetime'] = epgetimet.strftime("%Y-%m-%d %H:%M:%S") + except: + ret['epgbtime'] = "2010-01-01 00:00:00" + ret['epgetime'] = "2010-01-01 00:00:00" + ret['epgtitle'] = epgtitlet + ret['epgduplicate'] = epgduplicatet + ret['epgchange'] = epgchanget + if deltatime == None or deltatime == "": + deltatime = "3" + if deltaday == None or deltaday == "": + deltaday = "7" + if typet == recdblist.REC_KEYWORD: + ret['deltatime'] = deltatime + elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS: + ret['deltatime'] = deltatime + ret['deltaday'] = deltaday + try: + ret['counter'] = int(countert) + except: + ret['counter']=-1 + recdatum.append(ret) return recdatum def delete_old_timeline(self, dhour): db = self.connect_db() @@ -404,10 +788,13 @@ class DB_MySQL: (\ id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\ keyword VARCHAR(200),\ + auto TINYINT DEFAULT 0,\ + opt VARCHAR(20),\ UNIQUE unijbk (keyword)\ )") - except: - "" + except Exception, inst: + if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)): + recdblist.addCommonlogEX("Error", "new_in_auto_jbk_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200) self.close_db(db) def add_in_auto_jbk_key(self,key): db = self.connect_db() @@ -421,14 +808,22 @@ class DB_MySQL: def select_all_in_auto_jbk_key(self): db = self.connect_db() dbexe = db[1].execute("\ - SELECT keyword \ + SELECT keyword,auto,opt \ FROM in_auto_jbk_key \ - ") +vim ") ret = [] if dbexe > 0: ret = db[1].fetchall() self.close_db(db) return ret + def drop_in_status(self): + db = self.connect_db() + try: + db[1].execute('drop table in_status') + except Exception, inst: + if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))): + recdblist.addCommonlogEX("Error", "drop_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200) + self.close_db(db) def new_in_status(self): db = self.connect_db() try: @@ -438,11 +833,14 @@ class DB_MySQL: ts2avi TINYINT DEFAULT 0,\ terec TINYINT DEFAULT 0,\ bscsrec TINYINT DEFAULT 0,\ - b252ts TINYINT DEFAULT 0\ + b252ts TINYINT DEFAULT 0,\ + installed TINYINT DEFAULT 0,\ + version TINYINT\ )") - db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0)") - except: - "" + db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0,0)") + except Exception, inst: + if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)): + recdblist.addCommonlogEX("Error", "new_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200) self.close_db(db) def select_all_in_status(self): db = self.connect_db() @@ -463,7 +861,29 @@ class DB_MySQL: r[3]=str(r[3]) ret.append(r) return ret - + def select_version_in_status(self): + db = self.connect_db() + version=0 + try: + dbexe = db[1].execute("\ + SELECT version \ + FROM in_status \ + ") + if dbexe > 0: + dls = db[1].fetchall() + self.close_db(db) + for dl in dls: + r = list(dl) + version=int(str(r[0])) + except Exception, inst: + if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)): + recdblist.addCommonlogEX("Error", "select_version_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200) + return version + def change_version_in_status(self,version): + db = self.connect_db() + db[1].execute("\ + UPDATE in_status SET version=%s",str(version)) + self.close_db(db) def change_ts2avi_in_status(self,i): """ statuをiだけ増減する @@ -500,6 +920,37 @@ class DB_MySQL: db[1].execute("\ UPDATE in_status SET b252ts=b252ts+%s",i) self.close_db(db) + def select_installed_in_status(self): + db = self.connect_db() + dbexe = db[1].execute("\ + SELECT ts2avi,terec,bscsrec,b252ts,installed \ + FROM in_status \ + ") + ret = 0 + dls = [] + if dbexe > 0: + dls = db[1].fetchall() + self.close_db(db) + for dl in dls: + r = list(dl) + ret=r[4] + return ret + def change_chscaned_in_status(self): + """ + installedを設定する + """ + db = self.connect_db() + db[1].execute("\ + UPDATE in_status SET installed=2") + self.close_db(db) + def change_installed_in_status(self,num=1): + """ + installedを設定する + """ + db = self.connect_db() + db[1].execute("\ + UPDATE in_status SET installed=%s",(num,)) + self.close_db(db) def new_epg_timeline(self, bctype): db = self.connect_db() try: @@ -507,8 +958,9 @@ class DB_MySQL: DELETE FROM epg_timeline \ WHERE bctype = %s", \ (bctype,)) - except: - "" + except Exception, inst: + if not ((type(inst)==MySQLdb.ProgrammingError and (inst[0]==1007 or inst[0]==1146))or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)): + recdblist.addCommonlogEX("Error", "new_epg_timeline delete (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200) try: db[1].execute("\ CREATE TABLE epg_timeline \ @@ -523,8 +975,9 @@ class DB_MySQL: category VARCHAR(100),\ UNIQUE unitv(bctype,channel,start,stop,title)\ )") - except: - "" + except Exception, inst: + if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)): + recdblist.addCommonlogEX("Error", "new_epg_timeline (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200) #db.commit() self.close_db(db) def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category): @@ -550,34 +1003,33 @@ class DB_MySQL: db = self.connect_db() dbexe = "\ SELECT \ - epg_ch.chtxt,title,start,stop,exp,longexp,category \ + channel,title,start,stop,exp,longexp,category \ FROM epg_timeline \ - INNER JOIN epg_ch \ - WHERE epg_ch.ontv=epg_timeline.channel \ - AND \ - start >= %s \ + WHERE start >= %s \ AND \ start <= %s \ AND \ - epg_ch.chtxt=%s" + channel LIKE %s" dbcmd = db[1].execute(dbexe, (btime, etime, chtxt)) retall = [] if dbcmd > 0: retall = db[1].fetchall() self.close_db(db) return retall - def select_by_time_auto_suggest_epg_timeline(self,keyword,btime,etime): + def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime): db = self.connect_db() dbexe = "\ SELECT \ epg_ch.chtxt,title,start,stop,exp,longexp,category \ FROM epg_timeline \ INNER JOIN epg_ch \ - WHERE epg_ch.ontv=epg_timeline.channel \ + WHERE epg_ch.chtxt=epg_timeline.channel \ + AND \ + epg_ch.visible=1 \ AND \ start >= %s \ AND \ - start <= %s \ + stop <= %s \ AND \ ( \ ( title LIKE \'%%"+keyword+"%%\' ) \ @@ -592,37 +1044,6 @@ class DB_MySQL: retall = db[1].fetchall() self.close_db(db) return retall - #def new_epg_ch(self, bctype): - # db = self.connect_db() - # try: - # db[1].execute("DELETE FROM ch WHERE bctype = %s", (bctype,)) - # except: - # "" - # try: - # db[1].execute('\ - # CREATE TABLE epg_ch \ - # (\ - # bctype VARCHAR(20),\ - # channel VARCHAR(20) NOT NULL,\ - # display VARCHAR(100),\ - # UNIQUE unich(bctype,channel)\ - # )') - # except: - # "" - #db.commit() - # self.close_db(db) - #def add_epg_ch(self, bctype, channel, display): - # db = self.connect_db() - ## db[1].execute('INSERT IGNORE INTO epg_ch VALUES (%s,%s,%s)', (bctype, channel, display)) - #db.commit() - # self.close_db(db) - #def add_multi_ch(self, chlists): - # """ - # chlists is (bctype,channel,display) lists - ## """ - # db = self.connect_db() - # db[1].executemany('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', chlists) - # self.close_db(db) def new_in_auto_bayes_key(self): db = self.connect_db() try: @@ -635,8 +1056,9 @@ class DB_MySQL: UNIQUE unibayeskey(keychar,chtxt)\ )') db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)') - except: - "" + except Exception, inst: + if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)): + recdblist.addCommonlogEX("Error", "new_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200) self.close_db(db) def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all): @@ -691,10 +1113,14 @@ class DB_MySQL: db = self.connect_db() for i,j in list.items(): retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum) - db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \ - VALUES (%s,%s,%s,%s)\ - ON DUPLICATE KEY UPDATE \ - ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl) + try: + db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \ + VALUES (%s,%s,%s,%s)\ + ON DUPLICATE KEY UPDATE \ + ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl) + except Exception, inst: + if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)): + recdblist.addCommonlogEX("Error", "change_multi_ratio_all_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200) self.close_db(db) def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum): beforenum=str(beforenum) @@ -719,10 +1145,14 @@ class DB_MySQL: db = self.connect_db() for i,j in list.items(): retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum) - db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \ - VALUES (%s,%s,%s,%s)\ - ON DUPLICATE KEY UPDATE \ - ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl) + try: + db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \ + VALUES (%s,%s,%s,%s)\ + ON DUPLICATE KEY UPDATE \ + ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl) + except Exception, inst: + if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)): + recdblist.addCommonlogEX("Error", "change_multi_ratio_rec_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200) self.close_db(db) def select_by_key_in_auto_bayes_key(self,key,chtxt): db = self.connect_db() @@ -739,7 +1169,6 @@ class DB_MySQL: return dls[0] else: return dls - def new_auto_timeline_keyword(self): db = self.connect_db() try: @@ -747,14 +1176,15 @@ class DB_MySQL: CREATE TABLE auto_timeline_keyword \ (\ id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\ - chtxt VARCHAR(20),\ + chtxt VARCHAR(40),\ title VARCHAR(100),\ btime DATETIME,\ etime DATETIME,\ UNIQUE uni (chtxt,title,btime,etime)\ )') - except: - "" + except Exception, inst: + if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)): + recdblist.addCommonlogEX("Error", "new_auto_timeline_keyword (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200) def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""): db = self.connect_db() db[1].execute('\ @@ -778,21 +1208,23 @@ class DB_MySQL: CREATE TABLE auto_timeline_bayes \ (\ id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\ - chtxt VARCHAR(20),\ + chtxt VARCHAR(40),\ title VARCHAR(100),\ btime DATETIME,\ etime DATETIME,\ + point INT,\ UNIQUE uni (chtxt,title,btime,etime)\ )') - except: - "" - def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime=""): + except Exception, inst: + if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)): + recdblist.addCommonlogEX("Error", "new_auto_timeline_bayes (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200) + def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""): db = self.connect_db() db[1].execute('\ INSERT IGNORE into auto_timeline_bayes \ - (chtxt,title,btime,etime) \ - values (%s,%s,%s,%s)', \ - (chtxt, title, btime, etime)) + (chtxt,title,btime,etime,point) \ + values (%s,%s,%s,%s,%s)', \ + (chtxt, title, btime, etime,point)) self.close_db(db) def delete_old_auto_timeline_bayes(self, dhour): db = self.connect_db() @@ -801,3 +1233,107 @@ class DB_MySQL: WHERE \ btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )") self.close_db(db) + def update_db_to93(self): + db = self.connect_db() + self.drop_in_settings() + self.new_in_settings() + db[1].execute("\ + ALTER TABLE timeline ADD epgtitle VARCHAR(100),\ + ADD epgbtime DATETIME,\ + ADD epgetime DATETIME,\ + ADD epgduplicate TINYINT DEFAULT 0,\ + ADD epgchange TINYINT DEFAULT 0") + db[1].execute("\ + ALTER TABLE in_status ADD version TINYINT") + self.close_db(db) + self.change_version_in_status("93") + def update_db_93to94(self): + db = self.connect_db() + self.drop_in_settings() + self.new_in_settings() + db[1].execute("\ + ALTER TABLE timeline ADD counter TINYINT DEFAULT -1") + self.close_db(db) + self.change_version_in_status("94") + def update_db_94to95(self): + db = self.connect_db() + self.drop_in_settings() + self.new_in_settings() + db[1].execute("\ + ALTER TABLE timeline ADD epgexp VARCHAR(200)") + self.close_db(db) + self.change_version_in_status("95") + def update_db_95to96(self): + db = self.connect_db() + self.drop_in_settings() + self.new_in_settings() + self.close_db(db) + self.change_version_in_status("96") + def update_db_96to98(self): + db = self.connect_db() + self.drop_in_settings() + self.new_in_settings() + self.close_db(db) + self.change_version_in_status("98") + def update_db_98to100(self): + ###ここで前のepg_chをバックアップしてchtxtの変換をする必要がある。 + self.drop_in_settings() + self.new_in_settings() + db = self.connect_db() + db[1].execute("\ + UPDATE timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt SET timeline.chtxt=CONCAT(CONCAT(epg_ch.ch,'_'),epg_ch.csch) WHERE NOT (substring(epg_ch.bctype,1,2) = 'bs' OR substring(epg_ch.bctype,1,2) = 'cs')") + db[1].execute("\ + UPDATE timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt SET timeline.chtxt=CONCAT('BS_',epg_ch.ch) WHERE substring(epg_ch.bctype,1,2) = 'bs'") + db[1].execute("\ + UPDATE timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt SET timeline.chtxt=CONCAT('CS_',epg_ch.csch) WHERE substring(epg_ch.bctype,1,2) = 'cs'") + try: + db[1].execute("\ + ALTER TABLE epg_ch DROP ontv") + except: + "" + db[1].execute("\ + ALTER TABLE epg_ch ADD logo0 BLOB,\ + ADD logo1 BLOB,\ + ADD logo2 BLOB,\ + ADD logo3 BLOB,\ + ADD logo4 BLOB,\ + ADD logo5 BLOB\ + ") + db[1].execute("\ + ALTER TABLE in_auto_jbk_key ADD auto TINYINT DEFAULT 0") + db[1].execute("\ + ALTER TABLE in_auto_jbk_key ADD opt VARCHAR(20) DEFAULT \"\"") + self.close_db(db) + self.change_installed_in_status(1)#チャンネルスキャンをさせる + self.change_version_in_status("100") + def update_db_100to101(self): + self.drop_in_settings() + self.new_in_settings() + self.new_epg_ch() + db = self.connect_db() + try: + db[1].execute("\ + ALTER TABLE timeline ADD epgcategory VARCHAR(100)\ + ") + except: + "" + self.close_db(db) + self.change_installed_in_status(1) + self.change_version_in_status("101") + #self.change_installed_in_status()#チャンネルスキャンをさせる + def update_db_101to102(sekf): + self.drop_in_settings() + self.new_in_settings() + self.new_epg_ch() + db = self.connect_db() + try: + db[1].execute("\ + ALTER TABLE epg_ch ADD tsid VARCHAR(20)\ + ") + except: + "" + self.close_db(db) + self.change_installed_in_status(1) + self.change_version_in_status("102") + #self.change_installed_in_status()#チャンネルスキャンをさせる +