+++ /dev/null
-#!/usr/bin/python
-# coding: UTF-8
-# Rec10 TS Recording Tools
-# Copyright (C) 2009-2012 Yukikaze
-import MySQLdb
-import recdblist
-import warnings
-import traceback
-
-from decimal import Decimal
-class DB_MySQL:
- dbname = ""
- dbhost = ""
- dbusr = ""
- 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
- self.dbpasswd = passwd
- self.dbport = port
- try:
- con = MySQLdb.connect(user=user, passwd=passwd)
- cur = con.cursor()
- cur.execute('CREATE DATABASE ' + dbname + " DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;")
- cur.close()
- con.close()
- 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('\
- CREATE TABLE timeline \
- (\
- id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
- type VARCHAR(40),\
- 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 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_in_status()
- self.new_in_settings()
- self.new_auto_timeline_bayes()
- self.new_auto_timeline_keyword()
- def connect_db(self):
- """
- dbへの接続
- """
- 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):
- db[1].close()
- db[0].close()
- def new_epg_ch(self):
- db = self.connect_db()
- try:
- db[1].execute('drop table epg_ch')
- 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),\
- chtxt VARCHAR(20) PRIMARY KEY,\
- ch VARCHAR(20),\
- csch VARCHAR(20),\
- tsid VARCHAR(20),\
- chname VARCHAR(100),\
- updatetime DATETIME,\
- 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 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, chtxt, ch, csch,tsid,updatetime,logoupdate,scanupdate):
- db = self.connect_db()
- db[1].execute('\
- 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 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,chtxt,ch,csch,tsid,updatetime,chname,status,visible,logoupdate,scanupdate \
- FROM epg_ch \
- WHERE chtxt LIKE %s", \
- (chtxt,))
- ret = []
- dls = []
- if dbexe > 0:
- dls = db[1].fetchall()
- self.close_db(db)
- 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_bctype_epg_ch(self, bctype):
- db = self.connect_db()
- dbexe = db[1].execute("\
- SELECT bctype,chtxt,ch,csch,tsid,updatetime,status,chname,status,visible,logoupdate,scanupdate \
- FROM epg_ch \
- 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_ch_epg_ch(self, ch):
- db = self.connect_db()
- dbexe = db[1].execute("\
- SELECT \
- bctype,chtxt,ch,csch,tsid,updatetime,chname,status,visible,logoupdate,scanupdate \
- FROM epg_ch \
- WHERE ch = %s", \
- (ch,))
- ret = []
- dls = []
- if dbexe > 0:
- dls = db[1].fetchall()
- self.close_db(db)
- 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_by_csch_epg_ch(self, csch):
- db = self.connect_db()
- dbexe = db[1].execute("\
- SELECT \
- bctype,chtxt,ch,csch,tsid,updatetime,chname,status,visible,logoupdate,scanupdate \
- FROM epg_ch \
- WHERE csch = %s", \
- (csch,))
- ret = []
- dls = []
- if dbexe > 0:
- dls = db[1].fetchall()
- self.close_db(db)
- 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,chtxt,ch,csch,tsid,updatetime,chname,status,visible,logoupdate,scanupdate \
- FROM epg_ch \
- ")
- ret = []
- dls = []
- if dbexe > 0:
- dls = db[1].fetchall()
- self.close_db(db)
- 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 (\
- ( \
- updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
- AND \
- status = 1 \
- ) \
- OR \
- 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 = []
- #recdblist.printutf8(dbexe)
- if dbexe > 0:
- ret = db[1].fetchall()
- self.close_db(db)
- return ret
- def update_by_bctype_epg_ch(self, bctype):
- db = self.connect_db()
- db[1].execute("\
- UPDATE epg_ch \
- SET \
- updatetime=now() , \
- 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("\
- UPDATE epg_ch \
- SET \
- updatetime=now() , \
- 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 , \
- updatetime=now() \
- WHERE bctype = %s", \
- (status, bctype)\
- )
- self.close_db(db)
- def update_logostatus_by_bctype_epg_ch(self,bctype,logostatus):
- db = self.connect_db()
- db[1].execute("\
- UPDATE epg_ch \
- SET \
- logostatus=%s , \
- logoupdate=now() \
- WHERE bctype = %s", \
- (logostatus, bctype)\
- )
- self.close_db(db)
- def add_auto_proc_tmp(self,type,title,chtxt):
- db = self.connect_db()
- db[1].execute('\
- 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 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 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('\
- INSERT IGNORE into in_timeline_log \
- (chtxt,title,btime,etime,opt,exp,longexp,category) \
- values (%s,%s,%s,%s,%s,%s,%s,%s)', \
- ( chtxt, title, btime, etime, opt,exp,longexp,category))
- ##db.commit()
- self.close_db(db)
- def del_in_timeline_log(self, title="", chtxt="", btime=""):
- """
-
- """
- db = self.connect_db()
- db[1].execute("\
- DELETE FROM in_timeline_log \
- WHERE title = %s AND chtxt = %s AND btime = %s", \
- (title, chtxt, btime))
- #db.commit()
- self.close_db(db)
- def new_in_timeline_log(self):
- db = self.connect_db()
- try:
- db[1].execute('\
- CREATE TABLE in_timeline_log \
- (\
- id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
- chtxt VARCHAR(20),\
- title VARCHAR(100),\
- btime DATETIME,\
- etime DATETIME,\
- opt VARCHAR(20),\
- exp VARCHAR(200),\
- longexp TEXT,\
- category VARCHAR(100),\
- UNIQUE uni (chtxt,title,btime,category)\
- )')
- 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 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,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=""):
- """
-
- """
- db = self.connect_db()
- db[1].execute("\
- DELETE FROM timeline \
- WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
- (type, title, chtxt, btime))
- #db.commit()
- self.close_db(db)
- def select_all_timeline(self):
- db = self.connect_db()
- recdata = []
- dbr = db[1].execute("\
- 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:
- 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 ,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)
- #recdblist.printutf8(dbl)
- if dbr > 0:
- 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()
- db[1].execute("\
- DELETE FROM timeline \
- WHERE \
- btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
- #db.commit()
- self.close_db(db)
- def new_in_auto_jbk_key(self):
- db = self.connect_db()
- try:
- db[1].execute("\
- CREATE TABLE in_auto_jbk_key \
- (\
- id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
- keyword VARCHAR(200),\
- auto TINYINT DEFAULT 0,\
- opt VARCHAR(20),\
- UNIQUE unijbk (keyword)\
- )")
- 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()
- db[1].execute('\
- INSERT IGNORE into in_auto_jbk_key \
- (keyword) \
- values (%s)', \
- (key,))
- ##db.commit()
- self.close_db(db)
- def select_all_in_auto_jbk_key(self):
- db = self.connect_db()
- dbexe = db[1].execute("\
- 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:
- db[1].execute("\
- CREATE TABLE in_status \
- (\
- ts2avi TINYINT DEFAULT 0,\
- terec TINYINT DEFAULT 0,\
- bscsrec 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,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()
- dbexe = db[1].execute("\
- SELECT ts2avi,terec,bscsrec,b252ts \
- FROM in_status \
- ")
- 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]=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だけ増減する
- iはint
- """
- db = self.connect_db()
- db[1].execute("\
- UPDATE in_status SET ts2avi=ts2avi+%s",i)
- self.close_db(db)
- def change_terec_in_status(self,i):
- """
- statuをiだけ増減する
- iはint
- """
- db = self.connect_db()
- db[1].execute("\
- UPDATE in_status SET terec=terec+%s",i)
- self.close_db(db)
- def change_bscsrec_in_status(self,i):
- """
- statuをiだけ増減する
- iはint
- """
- db = self.connect_db()
- db[1].execute("\
- UPDATE in_status SET bscsrec=bscsrec+%s",i)
- self.close_db(db)
- def change_b252ts_in_status(self,i):
- """
- statuをiだけ増減する
- iはint
- """
- db = self.connect_db()
- 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:
- db[1].execute("\
- DELETE FROM epg_timeline \
- WHERE bctype = %s", \
- (bctype,))
- 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 \
- (\
- bctype VARCHAR(20),\
- channel VARCHAR(100) NOT NULL,\
- start VARCHAR(30),\
- stop VARCHAR(30),\
- title VARCHAR(100),\
- exp VARCHAR(200),\
- longexp TEXT,\
- category VARCHAR(100),\
- UNIQUE unitv(bctype,channel,start,stop,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_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):
- db = self.connect_db()
- db[1].execute('\
- INSERT IGNORE INTO epg_timeline \
- VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
- (bctype, channel, start, stop, title, desc,longdesc,category))
- #db.commit()
- self.close_db(db)
- def add_multi_epg_timeline(self, tvlists):
- """
- tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
- """
- db = self.connect_db()
- db[1].executemany('\
- INSERT IGNORE INTO epg_timeline \
- (bctype,channel,start,stop,title,exp,longexp,category) \
- values(%s,%s,%s,%s,%s,%s,%s,%s)', \
- tvlists)
- self.close_db(db)
- def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
- db = self.connect_db()
- dbexe = "\
- SELECT \
- channel,title,start,stop,exp,longexp,category \
- FROM epg_timeline \
- WHERE start >= %s \
- AND \
- start <= %s \
- AND \
- 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_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.chtxt=epg_timeline.channel \
- AND \
- epg_ch.visible=1 \
- AND \
- start >= %s \
- AND \
- stop <= %s \
- AND \
- ( \
- ( title LIKE \'%%"+keyword+"%%\' ) \
- OR \
- ( exp LIKE \'%%"+keyword+"%%\' ) \
- OR \
- ( longexp LIKE \'%%"+keyword+"%%\' ) \
- )"
- dbcmd = db[1].execute(dbexe,(btime, etime))
- retall = []
- if dbcmd > 0:
- retall = db[1].fetchall()
- self.close_db(db)
- return retall
- def new_in_auto_bayes_key(self):
- db = self.connect_db()
- try:
- db[1].execute('CREATE TABLE in_auto_bayes_key \
- (\
- keychar VARCHAR(10),\
- chtxt VARCHAR(20),\
- ratio_rec DECIMAL(32,14),\
- ratio_all DECIMAL(32,14),\
- UNIQUE unibayeskey(keychar,chtxt)\
- )')
- db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
- 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):
- db = self.connect_db()
- ratio_rec=str(ratio_rec)
- ratio_all=str(ratio_all)
- db[1].execute('\
- INSERT IGNORE INTO in_auto_bayes_key \
- (keychar,chtxt,ratio_rec,ratio_all) \
- values (%s,%s,%s,%s)',\
- (key,chtxt,ratio_rec,ratio_all))
- self.close_db(db)
- def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
- db = self.connect_db()
- add_rec_num=str(add_rec_num)
- add_all_num=str(add_all_num)
- db[1].execute("\
- 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",\
- (add_rec_num,add_all_num,chtxt))
- self.close_db(db)
- def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
- """
- """
- db = self.connect_db()
- db[1].execute("\
- 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)\
- )
- self.close_db(db)
- def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
- beforenum=str(beforenum)
- newnum=str(newnum)
- db = self.connect_db()
- db[1].execute("\
- 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)\
- )
- self.close_db(db)
- def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
- db = self.connect_db()
- beforenumf=beforenum
- beforenum=str(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))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
- self.close_db(db)
- def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
- """
- list={key:addnum}のリスト
- """
- beforenumf=beforenum
- beforenum=str(beforenum)
- db = self.connect_db()
- for i,j in list.items():
- retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
- 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)
- newnum=str(newnum)
- db = self.connect_db()
- db[1].execute("\
- 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)\
- )
- self.close_db(db)
- def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
- db = self.connect_db()
- beforenumf=beforenum
- beforenum=str(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))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
- self.close_db(db)
- def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
- beforenumf=beforenum
- beforenum=str(beforenum)
- db = self.connect_db()
- for i,j in list.items():
- retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
- 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()
- dbexe = db[1].execute("\
- SELECT keychar,chtxt,ratio_rec,ratio_all \
- FROM in_auto_bayes_key \
- WHERE keychar = %s AND chtxt = %s", \
- (key,chtxt))
- dls = []
- if dbexe > 0:
- dls = db[1].fetchall()
- self.close_db(db)
- if len(dls)>0:
- return dls[0]
- else:
- return dls
- def new_auto_timeline_keyword(self):
- db = self.connect_db()
- try:
- db[1].execute('\
- CREATE TABLE auto_timeline_keyword \
- (\
- id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
- chtxt VARCHAR(40),\
- title VARCHAR(100),\
- btime DATETIME,\
- etime DATETIME,\
- UNIQUE uni (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_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('\
- INSERT IGNORE into auto_timeline_keyword \
- (chtxt,title,btime,etime) \
- values (%s,%s,%s,%s)', \
- (chtxt, title, btime, etime))
- ##db.commit()
- self.close_db(db)
- def delete_old_auto_timeline_keyword(self, dhour):
- db = self.connect_db()
- db[1].execute("\
- DELETE FROM auto_timeline_keyword \
- WHERE \
- btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
- self.close_db(db)
- def new_auto_timeline_bayes(self):
- db = self.connect_db()
- try:
- db[1].execute('\
- CREATE TABLE auto_timeline_bayes \
- (\
- id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
- chtxt VARCHAR(40),\
- title VARCHAR(100),\
- btime DATETIME,\
- etime DATETIME,\
- point INT,\
- UNIQUE uni (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,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()
- db[1].execute("\
- DELETE FROM auto_timeline_bayes \
- 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()#チャンネルスキャンをさせる
-
OSDN Git Service
