3 # Rec10 TS Recording Tools
4 # Copyright (C) 2009 Yukikaze
7 from decimal import Decimal
14 def __init__(self, dbname, user, passwd, host="localhost", port=3306):
18 self.dbpasswd = passwd
21 con = MySQLdb.connect(user=user, passwd=passwd)
23 cur.execute('CREATE DATABASE ' + dbname + " DEFAULT CHARACTER SET utf8")
28 db = self.connect_db()
31 CREATE TABLE timeline \
33 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
39 deltatime VARCHAR(5),\
42 UNIQUE uni (type,chtxt,title,btime,deltaday)\
47 self.new_epg_timeline("")
48 self.new_in_timeline_log()
49 self.new_in_auto_bayes_key()
50 self.new_in_auto_jbk_key()
53 self.new_auto_timeline_bayes()
54 self.new_auto_timeline_keyword()
59 con = MySQLdb.connect(db=self.dbname, host=self.dbhost, port=self.dbport, user=self.dbusr, passwd=self.dbpasswd, charset="utf8")
61 cur.execute('set names utf8;')
63 def close_db(self, db):
67 db = self.connect_db()
69 db[1].execute('drop table epg_ch')
77 ontv VARCHAR(30) PRIMARY KEY,\
88 def add_epg_ch(self, bctype, ontv, chtxt, ch, csch, updatetime):
89 db = self.connect_db()
92 VALUES (%s,%s,%s,%s,%s,"",%s,%s)', \
93 (bctype, ontv, chtxt, ch, csch, updatetime, "1"))
95 def select_by_ontv_epg_ch(self, ontv):
96 db = self.connect_db()
97 dbexe = db[1].execute("\
98 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
105 dls = db[1].fetchall()
109 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
112 def select_by_chtxt_epg_ch(self, chtxt):
113 db = self.connect_db()
114 dbexe = db[1].execute("\
115 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
122 dls = db[1].fetchall()
126 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
129 def select_by_bctype_epg_ch(self, bctype):
130 db = self.connect_db()
131 dbexe = db[1].execute("\
132 SELECT bctype,ontv,chtxt,ch,csch,updatetime,status \
134 WHERE bctype = %s", \
139 dls = db[1].fetchall()
142 #recdblist.printutf8(dl)
144 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
148 def select_by_ch_epg_ch(self, ch):
149 db = self.connect_db()
150 dbexe = db[1].execute("\
152 bctype,ontv,chtxt,ch,csch,updatetime \
159 dls = db[1].fetchall()
163 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
166 def select_all_epg_ch(self):
167 db = self.connect_db()
168 dbexe = db[1].execute("\
169 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
175 dls = db[1].fetchall()
179 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
182 def select_get_update_epg_ch(self, dhour):
183 db = self.connect_db()
184 dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
187 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
193 ORDER BY status DESC")
195 #recdblist.printutf8(dbexe)
197 ret = db[1].fetchall()
200 def update_by_bctype_epg_ch(self, bctype):
201 db = self.connect_db()
207 WHERE bctype = %s", (bctype,))
209 def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
210 db = self.connect_db()
216 WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
218 def update_status_by_bctype_epg_ch(self, bctype, status):
219 db = self.connect_db()
224 WHERE bctype = %s", \
228 def update_chname_by_ontv_epg_ch(self,ontv,chname):
229 db = self.connect_db()
238 def add_auto_proc(self,type,title):
239 db = self.connect_db()
241 INSERT IGNORE into auto_proc \
247 def new_auto_proc(self):
248 db = self.connect_db()
250 db[1].execute('drop table auto_proc')
255 CREATE TABLE auto_proc \
258 title VARCHAR(100) PRIMARY KEY,\
259 UNIQUE unibayeskey(title)\
264 def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
265 db = self.connect_db()
267 INSERT IGNORE into in_timeline_log \
268 (chtxt,title,btime,etime,opt,exp,longexp,category) \
269 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
270 ( chtxt, title, btime, etime, opt,exp,longexp,category))
273 def del_in_timeline_log(self, title="", chtxt="", btime=""):
277 db = self.connect_db()
279 DELETE FROM in_timeline_log \
280 WHERE title = %s AND chtxt = %s AND btime = %s", \
281 (title, chtxt, btime))
284 def new_in_timeline_log(self):
285 db = self.connect_db()
288 CREATE TABLE in_timeline_log \
290 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
298 category VARCHAR(100),\
299 UNIQUE uni (chtxt,title,btime,category)\
304 def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
305 db = self.connect_db()
307 INSERT IGNORE into timeline \
308 (type,chtxt,title,btime,etime,deltatime,deltaday,opt) \
309 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
310 (type, chtxt, title, btime, etime, deltatime, deltaday, opt))
313 def del_timeline(self, type="", title="", chtxt="", btime=""):
317 db = self.connect_db()
319 DELETE FROM timeline \
320 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
321 (type, title, chtxt, btime))
324 def select_all_timeline(self):
325 db = self.connect_db()
327 dbr = db[1].execute("\
328 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
330 dbl = db[1].fetchall()
333 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
338 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
339 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
343 ret['deltatime'] = ""
345 if deltatime == None:
350 ret['deltatime'] = deltatime
351 elif typet == 'keyevery':
352 ret['deltatime'] = deltatime
353 ret['deltaday'] = deltaday
357 def select_bytime_timeline(self, dminutes):
358 db = self.connect_db()
360 #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 )")
361 dbr = db[1].execute("SELECT \
362 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
364 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
365 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
366 dbl = db[1].fetchall()
368 #recdblist.printutf8(dbl)
370 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
375 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
376 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
380 if deltatime == None or deltatime == "":
382 if deltaday == None or deltaday == "":
384 if typet == recdblist.REC_KEYWORD:
385 ret['deltatime'] = deltatime
386 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
387 ret['deltatime'] = deltatime
388 ret['deltaday'] = deltaday
391 def delete_old_timeline(self, dhour):
392 db = self.connect_db()
394 DELETE FROM timeline \
396 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
399 def new_in_auto_jbk_key(self):
400 db = self.connect_db()
403 CREATE TABLE in_auto_jbk_key \
405 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
406 keyword VARCHAR(200),\
407 UNIQUE unijbk (keyword)\
412 def add_in_auto_jbk_key(self,key):
413 db = self.connect_db()
415 INSERT IGNORE into in_auto_jbk_key \
421 def select_all_in_auto_jbk_key(self):
422 db = self.connect_db()
423 dbexe = db[1].execute("\
425 FROM in_auto_jbk_key \
429 ret = db[1].fetchall()
432 def new_in_status(self):
433 db = self.connect_db()
436 CREATE TABLE in_status \
438 ts2avi TINYINT DEFAULT 0,\
439 terec TINYINT DEFAULT 0,\
440 bscsrec TINYINT DEFAULT 0,\
441 b252ts TINYINT DEFAULT 0\
443 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0)")
447 def select_all_in_status(self):
448 db = self.connect_db()
449 dbexe = db[1].execute("\
450 SELECT ts2avi,terec,bscsrec,b252ts \
456 dls = db[1].fetchall()
467 def change_ts2avi_in_status(self,i):
472 db = self.connect_db()
474 UPDATE in_status SET ts2avi=ts2avi+%s",i)
476 def change_terec_in_status(self,i):
481 db = self.connect_db()
483 UPDATE in_status SET terec=terec+%s",i)
485 def change_bscsrec_in_status(self,i):
490 db = self.connect_db()
492 UPDATE in_status SET bscsrec=bscsrec+%s",i)
494 def change_b252ts_in_status(self,i):
499 db = self.connect_db()
501 UPDATE in_status SET b252ts=b252ts+%s",i)
503 def new_epg_timeline(self, bctype):
504 db = self.connect_db()
507 DELETE FROM epg_timeline \
508 WHERE bctype = %s", \
514 CREATE TABLE epg_timeline \
517 channel VARCHAR(100) NOT NULL,\
523 category VARCHAR(100),\
524 UNIQUE unitv(bctype,channel,start,stop,title)\
530 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
531 db = self.connect_db()
533 INSERT IGNORE INTO epg_timeline \
534 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
535 (bctype, channel, start, stop, title, desc,longdesc,category))
538 def add_multi_epg_timeline(self, tvlists):
540 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
542 db = self.connect_db()
544 INSERT IGNORE INTO epg_timeline \
545 (bctype,channel,start,stop,title,exp,longexp,category) \
546 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
549 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
550 db = self.connect_db()
553 epg_ch.chtxt,title,start,stop,exp,longexp,category \
556 WHERE epg_ch.ontv=epg_timeline.channel \
563 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
566 retall = db[1].fetchall()
569 def select_by_time_auto_suggest_epg_timeline(self,keyword,btime,etime):
570 db = self.connect_db()
573 epg_ch.chtxt,title,start,stop,exp,longexp,category \
576 WHERE epg_ch.ontv=epg_timeline.channel \
583 ( title LIKE \'%%"+keyword+"%%\' ) \
585 ( exp LIKE \'%%"+keyword+"%%\' ) \
587 ( longexp LIKE \'%%"+keyword+"%%\' ) \
589 dbcmd = db[1].execute(dbexe,(btime, etime))
592 retall = db[1].fetchall()
595 #def new_epg_ch(self, bctype):
596 # db = self.connect_db()
598 # db[1].execute("DELETE FROM ch WHERE bctype = %s", (bctype,))
603 # CREATE TABLE epg_ch \
605 # bctype VARCHAR(20),\
606 # channel VARCHAR(20) NOT NULL,\
607 # display VARCHAR(100),\
608 # UNIQUE unich(bctype,channel)\
614 #def add_epg_ch(self, bctype, channel, display):
615 # db = self.connect_db()
616 ## db[1].execute('INSERT IGNORE INTO epg_ch VALUES (%s,%s,%s)', (bctype, channel, display))
619 #def add_multi_ch(self, chlists):
621 # chlists is (bctype,channel,display) lists
623 # db = self.connect_db()
624 # db[1].executemany('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', chlists)
626 def new_in_auto_bayes_key(self):
627 db = self.connect_db()
629 db[1].execute('CREATE TABLE in_auto_bayes_key \
631 keychar VARCHAR(10),\
633 ratio_rec DECIMAL(32,14),\
634 ratio_all DECIMAL(32,14),\
635 UNIQUE unibayeskey(keychar,chtxt)\
637 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
642 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
643 db = self.connect_db()
644 ratio_rec=str(ratio_rec)
645 ratio_all=str(ratio_all)
647 INSERT IGNORE INTO in_auto_bayes_key \
648 (keychar,chtxt,ratio_rec,ratio_all) \
649 values (%s,%s,%s,%s)',\
650 (key,chtxt,ratio_rec,ratio_all))
652 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
653 db = self.connect_db()
654 add_rec_num=str(add_rec_num)
655 add_all_num=str(add_all_num)
657 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",\
658 (add_rec_num,add_all_num,chtxt))
660 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
663 db = self.connect_db()
665 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)\
668 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
669 beforenum=str(beforenum)
671 db = self.connect_db()
673 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)\
676 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
677 db = self.connect_db()
679 beforenum=str(beforenum)
680 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
681 VALUES (%s,%s,%s,%s)\
682 ON DUPLICATE KEY UPDATE \
683 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
685 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
687 list={key:addnum}のリスト
690 beforenum=str(beforenum)
691 db = self.connect_db()
692 for i,j in list.items():
693 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
694 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
695 VALUES (%s,%s,%s,%s)\
696 ON DUPLICATE KEY UPDATE \
697 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
699 def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
700 beforenum=str(beforenum)
702 db = self.connect_db()
704 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)\
707 def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
708 db = self.connect_db()
710 beforenum=str(beforenum)
711 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
712 VALUES (%s,%s,%s,%s)\
713 ON DUPLICATE KEY UPDATE \
714 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
716 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
718 beforenum=str(beforenum)
719 db = self.connect_db()
720 for i,j in list.items():
721 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
722 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
723 VALUES (%s,%s,%s,%s)\
724 ON DUPLICATE KEY UPDATE \
725 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
727 def select_by_key_in_auto_bayes_key(self,key,chtxt):
728 db = self.connect_db()
729 dbexe = db[1].execute("\
730 SELECT keychar,chtxt,ratio_rec,ratio_all \
731 FROM in_auto_bayes_key \
732 WHERE keychar = %s AND chtxt = %s", \
736 dls = db[1].fetchall()
743 def new_auto_timeline_keyword(self):
744 db = self.connect_db()
747 CREATE TABLE auto_timeline_keyword \
749 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
754 UNIQUE uni (chtxt,title,btime,etime)\
758 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
759 db = self.connect_db()
761 INSERT IGNORE into auto_timeline_keyword \
762 (chtxt,title,btime,etime) \
763 values (%s,%s,%s,%s)', \
764 (chtxt, title, btime, etime))
767 def delete_old_auto_timeline_keyword(self, dhour):
768 db = self.connect_db()
770 DELETE FROM auto_timeline_keyword \
772 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
774 def new_auto_timeline_bayes(self):
775 db = self.connect_db()
778 CREATE TABLE auto_timeline_bayes \
780 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
785 UNIQUE uni (chtxt,title,btime,etime)\
789 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime=""):
790 db = self.connect_db()
792 INSERT IGNORE into auto_timeline_bayes \
793 (chtxt,title,btime,etime) \
794 values (%s,%s,%s,%s)', \
795 (chtxt, title, btime, etime))
797 def delete_old_auto_timeline_bayes(self, dhour):
798 db = self.connect_db()
800 DELETE FROM auto_timeline_bayes \
802 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")