#!/usr/bin/python
# coding: UTF-8
# Rec10 TS Recording Tools
-# Copyright (C) 2009 Yukikaze
+# Copyright (C) 2009-2010 Yukikaze
import MySQLdb
+import recdblist
from decimal import Decimal
class DB_MySQL:
dbname = ""
CREATE TABLE timeline \
(\
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
- type VARCHAR(20),\
- chtxt VARCHAR(20),\
+ type VARCHAR(40),\
+ chtxt VARCHAR(40),\
title VARCHAR(100),\
btime DATETIME,\
etime DATETIME,\
""
self.close_db(db)
self.new_epg_timeline("")
- self.new_epg_ch()
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):
"""
dbへの接続
(\
bctype VARCHAR(15),\
ontv VARCHAR(30) PRIMARY KEY,\
- chtxt VARCHAR(15),\
+ chtxt VARCHAR(20),\
ch VARCHAR(20),\
csch VARCHAR(20),\
chname VARCHAR(100),\
updatetime DATETIME,\
- status TINYINT\
+ status TINYINT,\
+ isshow TINYINT\
)')
except:
""
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"))
+ VALUES (%s,%s,%s,%s,%s,"",%s,%s,%s)', \
+ (bctype, ontv, chtxt, ch, csch, updatetime, "1","1"))
self.close_db(db)
def select_by_ontv_epg_ch(self, ontv):
db = self.connect_db()
dls = db[1].fetchall()
self.close_db(db)
for dl in dls:
- #print dl
+ #recdblist.printutf8(dl)
r = list(dl)
r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
r[6] = str(r[6])
r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
ret.append(r)
return ret
+ 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 = []
- #print dbexe
+ #recdblist.printutf8(dbexe)
if dbexe > 0:
ret = db[1].fetchall()
self.close_db(db)
(chname, ontv)\
)
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:
+ ""
+ try:
+ db[1].execute('\
+ CREATE TABLE auto_proc_tmp \
+ (\
+ type VARCHAR(20),\
+ title VARCHAR(100) PRIMARY KEY,\
+ chtxt VARCHAR(30),\
+ UNIQUE unibayeskey(title)\
+ )')
+ except:
+ ""
+ 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:
+ ""
+ self.close_db(db)
def new_auto_proc(self):
db = self.connect_db()
try:
(\
type VARCHAR(20),\
title VARCHAR(100) PRIMARY KEY,\
+ chtxt VARCHAR(30),\
UNIQUE unibayeskey(title)\
)')
except:
""
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:
+ ""
+ 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,\"G\",1)")
+ except:
+ ""
+ 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('\
except:
""
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=""):
db = self.connect_db()
db[1].execute('\
ret = {}
ret['type'] = typet
ret['chtxt'] = chtxt
- ret['title'] = title.encode('utf-8')
+ ret['title'] = title
btime = btime.strftime("%Y-%m-%d %H:%M:%S")
etime = etime.strftime("%Y-%m-%d %H:%M:%S")
ret['btime'] = btime
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.encode('utf-8')
+ ret['title'] = title
btime = btime.strftime("%Y-%m-%d %H:%M:%S")
etime = etime.strftime("%Y-%m-%d %H:%M:%S")
ret['btime'] = btime
deltatime = "3"
if deltaday == None or deltaday == "":
deltaday = "7"
- if typet == 'key':
+ if typet == recdblist.REC_KEYWORD:
ret['deltatime'] = deltatime
- elif typet == 'keyevery':
+ elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
+ ret['deltatime'] = deltatime
+ ret['deltaday'] = deltaday
+ recdatum.append(ret)
+ 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 \
+ 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:
+ 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)
+ 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 \
+ FROM timeline \
+ WHERE btime > %s AND \
+ etime < %s",(btime,etime))
+ dbl = db[1].fetchall()
+ self.close_db(db)
+ #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)
+ 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 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 \
+ 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:
+ 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)
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:
+ ""
+ self.close_db(db)
def new_in_status(self):
db = self.connect_db()
try:
ts2avi TINYINT DEFAULT 0,\
terec TINYINT DEFAULT 0,\
bscsrec TINYINT DEFAULT 0,\
- b252ts TINYINT DEFAULT 0\
+ b252ts TINYINT DEFAULT 0,\
+ installed TINYINT DEFAULT 0\
)")
- db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0)")
+ db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0)")
except:
""
self.close_db(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_installed_in_status(self):
+ """
+ installedを設定する
+ """
+ db = self.connect_db()
+ db[1].execute("\
+ UPDATE in_status SET installed=1")
+ self.close_db(db)
def new_epg_timeline(self, bctype):
db = self.connect_db()
try:
db = self.connect_db()
dbexe = "\
SELECT \
- chdata.chtxt,title,start,stop,exp,longexp,category \
+ epg_ch.chtxt,title,start,stop,exp,longexp,category \
FROM epg_timeline \
- INNER JOIN chdata \
- WHERE chdata.ontv=epg_timeline.channel \
+ INNER JOIN epg_ch \
+ WHERE epg_ch.ontv=epg_timeline.channel \
AND \
start >= %s \
AND \
start <= %s \
AND \
- chdata.chtxt=%s"
+ epg_ch.chtxt=%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 \
- chdata.chtxt,title,start,stop,exp,longexp,category \
+ epg_ch.chtxt,title,start,stop,exp,longexp,category \
FROM epg_timeline \
- INNER JOIN chdata \
- WHERE chdata.ontv=epg_timeline.channel \
+ INNER JOIN epg_ch \
+ WHERE epg_ch.ontv=epg_timeline.channel \
AND \
start >= %s \
AND \
- start <= %s \
+ stop <= %s \
AND \
( \
( title LIKE \'%%"+keyword+"%%\' ) \
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:
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,\
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=""):
+ 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()
OSDN Git Service
