3 # Rec10 TS Recording Tools
4 # Copyright (C) 2009-2010 Yukikaze
9 from decimal import Decimal
16 def __init__(self, dbname, user, passwd, host="localhost", port=3306):
17 warnings.filterwarnings('ignore', "Data truncated for column")
21 self.dbpasswd = passwd
24 con = MySQLdb.connect(user=user, passwd=passwd)
26 cur.execute('CREATE DATABASE ' + dbname + " DEFAULT CHARACTER SET utf8")
31 db = self.connect_db()
34 CREATE TABLE timeline \
36 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
42 deltatime VARCHAR(5),\
45 epgtitle VARCHAR(100),\
48 epgduplicate TINYINT DEFAULT 0,\
49 epgchange TINYINT DEFAULT 0\
50 counter TINYINT DEFAULT -1\
51 UNIQUE uni (type,chtxt,title,btime,deltaday)\
56 self.new_epg_timeline("")
57 self.new_in_timeline_log()
58 self.new_in_auto_bayes_key()
59 self.new_in_auto_jbk_key()
61 self.new_in_settings()
62 self.new_auto_timeline_bayes()
63 self.new_auto_timeline_keyword()
68 con = MySQLdb.connect(db=self.dbname, host=self.dbhost, port=self.dbport, user=self.dbusr, passwd=self.dbpasswd, charset="utf8")
70 cur.execute('set names utf8;')
72 def close_db(self, db):
76 db = self.connect_db()
78 db[1].execute('drop table epg_ch')
86 ontv VARCHAR(30) PRIMARY KEY,\
98 def add_epg_ch(self, bctype, ontv, chtxt, ch, csch, updatetime):
99 db = self.connect_db()
102 VALUES (%s,%s,%s,%s,%s,"",%s,%s,%s)', \
103 (bctype, ontv, chtxt, ch, csch, updatetime, "1","1"))
105 def select_by_ontv_epg_ch(self, ontv):
106 db = self.connect_db()
107 dbexe = db[1].execute("\
108 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
115 dls = db[1].fetchall()
119 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
122 def select_by_chtxt_epg_ch(self, chtxt):
123 db = self.connect_db()
124 dbexe = db[1].execute("\
125 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
132 dls = db[1].fetchall()
136 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
139 def select_by_bctype_epg_ch(self, bctype):
140 db = self.connect_db()
141 dbexe = db[1].execute("\
142 SELECT bctype,ontv,chtxt,ch,csch,updatetime,status \
144 WHERE bctype = %s", \
149 dls = db[1].fetchall()
152 #recdblist.printutf8(dl)
154 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
158 def select_by_ch_epg_ch(self, ch):
159 db = self.connect_db()
160 dbexe = db[1].execute("\
162 bctype,ontv,chtxt,ch,csch,updatetime \
169 dls = db[1].fetchall()
173 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
176 def select_all_epg_ch(self):
177 db = self.connect_db()
178 dbexe = db[1].execute("\
179 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
185 dls = db[1].fetchall()
189 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
192 def set_new_status(self,dhour):
193 db = self.connect_db()
194 dbexe = db[1].execute("UPDATE epg_ch \
198 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
203 def select_get_update_epg_ch(self, dhour):
204 db = self.connect_db()
205 dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
208 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
214 ORDER BY status DESC")
216 #recdblist.printutf8(dbexe)
218 ret = db[1].fetchall()
221 def update_by_bctype_epg_ch(self, bctype):
222 db = self.connect_db()
228 WHERE bctype = %s", (bctype,))
230 def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
231 db = self.connect_db()
237 WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
239 def update_status_by_bctype_epg_ch(self, bctype, status):
240 db = self.connect_db()
246 WHERE bctype = %s", \
250 def update_chname_by_ontv_epg_ch(self,ontv,chname):
251 db = self.connect_db()
260 def add_auto_proc_tmp(self,type,title,chtxt):
261 db = self.connect_db()
263 INSERT IGNORE into auto_proc_tmp \
265 values (%s,%s,%s)',(type,title,chtxt))
268 def new_auto_proc_tmp(self):
269 db = self.connect_db()
271 db[1].execute('drop table auto_proc_tmp')
276 CREATE TABLE auto_proc_tmp \
279 title VARCHAR(100) PRIMARY KEY,\
281 UNIQUE unibayeskey(title)\
286 def update_auto_proc(self):
287 db = self.connect_db()
289 db[1].execute('INSERT INTO auto_proc SELECT * FROM auto_proc_tmp')
293 def new_auto_proc(self):
294 db = self.connect_db()
296 db[1].execute('drop table auto_proc')
301 CREATE TABLE auto_proc \
304 title VARCHAR(100) PRIMARY KEY,\
306 UNIQUE unibayeskey(title)\
311 def add_auto_proc(self,type,title,chtxt):
312 db = self.connect_db()
314 INSERT IGNORE into auto_proc \
316 values (%s,%s,%s)',(type,title,chtxt))
319 def drop_in_settings(self):
320 db = self.connect_db()
322 db[1].execute('drop table in_settings')
326 def new_in_settings(self):
327 db = self.connect_db()
330 CREATE TABLE in_settings \
334 auto_opt VARCHAR(20),\
335 auto_del_tmp TINYINT\
337 db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"H\",1)")
341 def select_all_in_settings(self):
342 db = self.connect_db()
343 dbexe = db[1].execute("\
344 SELECT auto_jbk,auto_bayes,auto_del_tmp,auto_opt \
350 dls = db[1].fetchall()
360 def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
361 db = self.connect_db()
363 INSERT IGNORE into in_timeline_log \
364 (chtxt,title,btime,etime,opt,exp,longexp,category) \
365 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
366 ( chtxt, title, btime, etime, opt,exp,longexp,category))
369 def del_in_timeline_log(self, title="", chtxt="", btime=""):
373 db = self.connect_db()
375 DELETE FROM in_timeline_log \
376 WHERE title = %s AND chtxt = %s AND btime = %s", \
377 (title, chtxt, btime))
380 def new_in_timeline_log(self):
381 db = self.connect_db()
384 CREATE TABLE in_timeline_log \
386 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
394 category VARCHAR(100),\
395 UNIQUE uni (chtxt,title,btime,category)\
400 def select_chtxt_by_title_timeline_log(self,title):
401 db = self.connect_db()
402 dbexe = db[1].execute("\
404 FROM in_timeline_log \
405 WHERE title LIKE \"%"+title+"%\"\
407 ORDER by sum(1) DESC limit 1")
408 retdb=db[1].fetchall()
415 def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt="" ,counter=-1):
416 db = self.connect_db()
418 INSERT IGNORE into timeline \
419 (type,chtxt,title,btime,etime,deltatime,deltaday,opt,counter) \
420 values (%s,%s,%s,%s,%s,%s,%s,%s,%s)', \
421 (type, chtxt, title, btime, etime, deltatime, deltaday, opt ,counter))
424 def update_epg_timeline(self,type,chtxt,title,btime,epgbtime,epgetime,epgtitle):
425 db = self.connect_db()
428 SET epgbtime=%s,epgetime=%s,epgtitle=%s \
429 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
430 (epgbtime,epgetime,epgtitle,type, chtxt, title, btime))
433 def update_status_change_timeline(self,type,chtxt,title,btime,epgchange):
434 db = self.connect_db()
438 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
439 (epgchange , type, chtxt, title, btime))
442 def update_status_dup_timeline(self,type,chtxt,title,btime,epgduplicate):
443 db = self.connect_db()
446 SET epgduplicate =%s \
447 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
448 (epgduplicate , type, chtxt, title, btime))
451 def del_timeline(self, type="", title="", chtxt="", btime=""):
455 db = self.connect_db()
457 DELETE FROM timeline \
458 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
459 (type, title, chtxt, btime))
462 def select_all_timeline(self):
463 db = self.connect_db()
465 dbr = db[1].execute("\
466 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
468 dbl = db[1].fetchall()
471 recdata = self.getdic_timeline(dbl)
473 def select_bytime_timeline(self, dminutes):
474 db = self.connect_db()
476 #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 )")
477 dbr = db[1].execute("SELECT \
478 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
480 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
481 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
482 dbl = db[1].fetchall()
484 #recdblist.printutf8(dbl)
486 recdatum = self.getdic_timeline(dbl)
488 def select_by_name_time_timeline(self,title,btime,btime2):
489 db = self.connect_db()
491 #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 )")
492 dbr = db[1].execute("SELECT \
493 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
495 WHERE btime > %s AND \
496 btime < %s AND title = %s",(btime,btime2,title))
497 dbl = db[1].fetchall()
499 #recdblist.printutf8(dbl)
501 recdatum = self.getdic_timeline(dbl)
503 def select_bytime_all_timeline(self,btime,etime):
504 db = self.connect_db()
506 #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 )")
507 dbr = db[1].execute("SELECT \
508 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
510 WHERE btime > %s AND \
511 etime < %s",(btime,etime))
512 dbl = db[1].fetchall()
514 #recdblist.printutf8(dbl)
516 recdatum = self.getdic_timeline(dbl)
518 def select_byepgtime_all_timeline(self,epgbtime,epgetime):
519 db = self.connect_db()
521 #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 )")
522 dbr = db[1].execute("SELECT \
523 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
525 WHERE epgbtime >= %s AND \
526 epgetime <= %s",(epgbtime,epgetime))
527 dbl = db[1].fetchall()
529 #recdblist.printutf8(dbl)
531 recdatum=self.getdic_timeline(dbl)
533 def select_byepgtime_over_timeline(self,epgbtime,epgetime):
534 db = self.connect_db()
536 #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 )")
537 dbr = db[1].execute("SELECT \
538 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
540 WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"\
541 ,(epgbtime,epgetime))
542 dbl = db[1].fetchall()
544 #recdblist.printutf8(dbl)
546 recdatum=self.getdic_timeline(dbl)
548 def count_schedule_timeline(self, btime, etime):
551 return [te num,bs/cs num]
553 db = self.connect_db()
554 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
555 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
558 db[1].execute(dbexe, (btime, etime,btime,etime))
560 for typet, bctypet, chtxtt, titlet in dbl:
561 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
562 if bctypet.find("cs") > -1:
564 elif bctypet.find("bs") > -1:
566 elif bctypet.find("te") > -1:
570 def search_schedule_timeline(self,btime,etime):
573 return [(type,bctype,chtxt,title,btime,etime)]
575 db = self.connect_db()
576 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"
577 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
579 db[1].execute(dbexe, (btime, etime,btime,etime))
581 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
582 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
583 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
586 def count_epgschedule_timeline(self, epgbtime, epgetime):
589 return [te num,bs/cs num]
591 db = self.connect_db()
592 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
593 dbexe = dbexe + " WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"
596 db[1].execute(dbexe, (epgbtime, epgetime))
598 for typet, bctypet, chtxtt, titlet in dbl:
599 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
600 if bctypet.find("cs") > -1:
602 elif bctypet.find("bs") > -1:
604 elif bctypet.find("te") > -1:
608 def search_epgschedule_timeline(self,epgbtime,epgetime):
611 return [(type,bctype,chtxt,title,btime,etime)]
613 db = self.connect_db()
614 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"
615 dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )"
617 db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime))
619 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
620 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
621 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
624 def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
625 db = self.connect_db()
627 #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 )")
628 dbr = db[1].execute("SELECT \
629 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
631 WHERE btime > %s AND \
633 AND chtxt=%s ",(btime,etime,chtxt))
634 dbl = db[1].fetchall()
636 #recdblist.printutf8(dbl)
638 recdatum = self.getdic_timeline(dbl)
640 def getdic_timeline(self,timelinelists):
642 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt ,epgbtimet , epgetimet ,epgtitlet ,epgduplicatet ,epgchanget ,countert in timelinelists:
647 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
648 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
653 ret['epgbtime'] = epgbtimet.strftime("%Y-%m-%d %H:%M:%S")
654 ret['epgetime'] = epgetimet.strftime("%Y-%m-%d %H:%M:%S")
656 ret['epgbtime'] = "2010-01-01 00:00:00"
657 ret['epgetime'] = "2010-01-01 00:00:00"
658 ret['epgtitle'] = epgtitlet
659 ret['epgduplicate'] = epgduplicatet
660 ret['epgchange'] = epgchanget
661 if deltatime == None or deltatime == "":
663 if deltaday == None or deltaday == "":
665 if typet == recdblist.REC_KEYWORD:
666 ret['deltatime'] = deltatime
667 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
668 ret['deltatime'] = deltatime
669 ret['deltaday'] = deltaday
670 ret['epgchange'] = int(countert)
673 def delete_old_timeline(self, dhour):
674 db = self.connect_db()
676 DELETE FROM timeline \
678 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
681 def new_in_auto_jbk_key(self):
682 db = self.connect_db()
685 CREATE TABLE in_auto_jbk_key \
687 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
688 keyword VARCHAR(200),\
689 UNIQUE unijbk (keyword)\
694 def add_in_auto_jbk_key(self,key):
695 db = self.connect_db()
697 INSERT IGNORE into in_auto_jbk_key \
703 def select_all_in_auto_jbk_key(self):
704 db = self.connect_db()
705 dbexe = db[1].execute("\
707 FROM in_auto_jbk_key \
711 ret = db[1].fetchall()
714 def drop_in_status(self):
715 db = self.connect_db()
717 db[1].execute('drop table in_status')
721 def new_in_status(self):
722 db = self.connect_db()
725 CREATE TABLE in_status \
727 ts2avi TINYINT DEFAULT 0,\
728 terec TINYINT DEFAULT 0,\
729 bscsrec TINYINT DEFAULT 0,\
730 b252ts TINYINT DEFAULT 0,\
731 installed TINYINT DEFAULT 0,\
734 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0)")
738 def select_all_in_status(self):
739 db = self.connect_db()
740 dbexe = db[1].execute("\
741 SELECT ts2avi,terec,bscsrec,b252ts \
747 dls = db[1].fetchall()
757 def select_version_in_status(self):
758 db = self.connect_db()
761 dbexe = db[1].execute("\
766 dls = db[1].fetchall()
770 version=int(str(r[0]))
774 def change_version_in_status(self,version):
775 db = self.connect_db()
777 UPDATE in_status SET version=%s",str(version))
779 def change_ts2avi_in_status(self,i):
784 db = self.connect_db()
786 UPDATE in_status SET ts2avi=ts2avi+%s",i)
788 def change_terec_in_status(self,i):
793 db = self.connect_db()
795 UPDATE in_status SET terec=terec+%s",i)
797 def change_bscsrec_in_status(self,i):
802 db = self.connect_db()
804 UPDATE in_status SET bscsrec=bscsrec+%s",i)
806 def change_b252ts_in_status(self,i):
811 db = self.connect_db()
813 UPDATE in_status SET b252ts=b252ts+%s",i)
815 def select_installed_in_status(self):
816 db = self.connect_db()
817 dbexe = db[1].execute("\
818 SELECT ts2avi,terec,bscsrec,b252ts,installed \
824 dls = db[1].fetchall()
830 def change_installed_in_status(self):
834 db = self.connect_db()
836 UPDATE in_status SET installed=1")
838 def new_epg_timeline(self, bctype):
839 db = self.connect_db()
842 DELETE FROM epg_timeline \
843 WHERE bctype = %s", \
849 CREATE TABLE epg_timeline \
852 channel VARCHAR(100) NOT NULL,\
858 category VARCHAR(100),\
859 UNIQUE unitv(bctype,channel,start,stop,title)\
865 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
866 db = self.connect_db()
868 INSERT IGNORE INTO epg_timeline \
869 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
870 (bctype, channel, start, stop, title, desc,longdesc,category))
873 def add_multi_epg_timeline(self, tvlists):
875 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
877 db = self.connect_db()
879 INSERT IGNORE INTO epg_timeline \
880 (bctype,channel,start,stop,title,exp,longexp,category) \
881 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
884 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
885 db = self.connect_db()
888 epg_ch.chtxt,title,start,stop,exp,longexp,category \
891 WHERE epg_ch.ontv=epg_timeline.channel \
898 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
901 retall = db[1].fetchall()
904 def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
905 db = self.connect_db()
908 epg_ch.chtxt,title,start,stop,exp,longexp,category \
911 WHERE epg_ch.ontv=epg_timeline.channel \
918 ( title LIKE \'%%"+keyword+"%%\' ) \
920 ( exp LIKE \'%%"+keyword+"%%\' ) \
922 ( longexp LIKE \'%%"+keyword+"%%\' ) \
924 dbcmd = db[1].execute(dbexe,(btime, etime))
927 retall = db[1].fetchall()
930 def new_in_auto_bayes_key(self):
931 db = self.connect_db()
933 db[1].execute('CREATE TABLE in_auto_bayes_key \
935 keychar VARCHAR(10),\
937 ratio_rec DECIMAL(32,14),\
938 ratio_all DECIMAL(32,14),\
939 UNIQUE unibayeskey(keychar,chtxt)\
941 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
946 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
947 db = self.connect_db()
948 ratio_rec=str(ratio_rec)
949 ratio_all=str(ratio_all)
951 INSERT IGNORE INTO in_auto_bayes_key \
952 (keychar,chtxt,ratio_rec,ratio_all) \
953 values (%s,%s,%s,%s)',\
954 (key,chtxt,ratio_rec,ratio_all))
956 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
957 db = self.connect_db()
958 add_rec_num=str(add_rec_num)
959 add_all_num=str(add_all_num)
961 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",\
962 (add_rec_num,add_all_num,chtxt))
964 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
967 db = self.connect_db()
969 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)\
972 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
973 beforenum=str(beforenum)
975 db = self.connect_db()
977 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)\
980 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
981 db = self.connect_db()
983 beforenum=str(beforenum)
984 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
985 VALUES (%s,%s,%s,%s)\
986 ON DUPLICATE KEY UPDATE \
987 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
989 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
991 list={key:addnum}のリスト
994 beforenum=str(beforenum)
995 db = self.connect_db()
996 for i,j in list.items():
997 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
999 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1000 VALUES (%s,%s,%s,%s)\
1001 ON DUPLICATE KEY UPDATE \
1002 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
1006 def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1007 beforenum=str(beforenum)
1009 db = self.connect_db()
1011 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)\
1014 def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1015 db = self.connect_db()
1016 beforenumf=beforenum
1017 beforenum=str(beforenum)
1018 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1019 VALUES (%s,%s,%s,%s)\
1020 ON DUPLICATE KEY UPDATE \
1021 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
1023 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
1024 beforenumf=beforenum
1025 beforenum=str(beforenum)
1026 db = self.connect_db()
1027 for i,j in list.items():
1028 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
1030 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1031 VALUES (%s,%s,%s,%s)\
1032 ON DUPLICATE KEY UPDATE \
1033 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
1037 def select_by_key_in_auto_bayes_key(self,key,chtxt):
1038 db = self.connect_db()
1039 dbexe = db[1].execute("\
1040 SELECT keychar,chtxt,ratio_rec,ratio_all \
1041 FROM in_auto_bayes_key \
1042 WHERE keychar = %s AND chtxt = %s", \
1046 dls = db[1].fetchall()
1052 def new_auto_timeline_keyword(self):
1053 db = self.connect_db()
1056 CREATE TABLE auto_timeline_keyword \
1058 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1060 title VARCHAR(100),\
1063 UNIQUE uni (chtxt,title,btime,etime)\
1067 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
1068 db = self.connect_db()
1070 INSERT IGNORE into auto_timeline_keyword \
1071 (chtxt,title,btime,etime) \
1072 values (%s,%s,%s,%s)', \
1073 (chtxt, title, btime, etime))
1076 def delete_old_auto_timeline_keyword(self, dhour):
1077 db = self.connect_db()
1079 DELETE FROM auto_timeline_keyword \
1081 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1083 def new_auto_timeline_bayes(self):
1084 db = self.connect_db()
1087 CREATE TABLE auto_timeline_bayes \
1089 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1091 title VARCHAR(100),\
1095 UNIQUE uni (chtxt,title,btime,etime)\
1099 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
1100 db = self.connect_db()
1102 INSERT IGNORE into auto_timeline_bayes \
1103 (chtxt,title,btime,etime,point) \
1104 values (%s,%s,%s,%s,%s)', \
1105 (chtxt, title, btime, etime,point))
1107 def delete_old_auto_timeline_bayes(self, dhour):
1108 db = self.connect_db()
1110 DELETE FROM auto_timeline_bayes \
1112 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1114 def update_db_to93(self):
1115 db = self.connect_db()
1116 self.drop_in_settings()
1117 self.new_in_settings()
1119 ALTER TABLE timeline ADD epgtitle VARCHAR(100),\
1120 ADD epgbtime DATETIME,\
1121 ADD epgetime DATETIME,\
1122 ADD epgduplicate TINYINT DEFAULT 0,\
1123 ADD epgchange TINYINT DEFAULT 0")
1125 ALTER TABLE in_status ADD version TINYINT")
1127 self.change_version_in_status("93")
1128 def update_db_93to94(self):
1129 db = self.connect_db()
1130 self.drop_in_settings()
1131 self.new_in_settings()
1133 ALTER TABLE timeline ADD counter TINYINT DEFAULT -1")
1135 self.change_version_in_status("94")