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
671 ret['counter'] = int(countert)
676 def delete_old_timeline(self, dhour):
677 db = self.connect_db()
679 DELETE FROM timeline \
681 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
684 def new_in_auto_jbk_key(self):
685 db = self.connect_db()
688 CREATE TABLE in_auto_jbk_key \
690 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
691 keyword VARCHAR(200),\
692 UNIQUE unijbk (keyword)\
697 def add_in_auto_jbk_key(self,key):
698 db = self.connect_db()
700 INSERT IGNORE into in_auto_jbk_key \
706 def select_all_in_auto_jbk_key(self):
707 db = self.connect_db()
708 dbexe = db[1].execute("\
710 FROM in_auto_jbk_key \
714 ret = db[1].fetchall()
717 def drop_in_status(self):
718 db = self.connect_db()
720 db[1].execute('drop table in_status')
724 def new_in_status(self):
725 db = self.connect_db()
728 CREATE TABLE in_status \
730 ts2avi TINYINT DEFAULT 0,\
731 terec TINYINT DEFAULT 0,\
732 bscsrec TINYINT DEFAULT 0,\
733 b252ts TINYINT DEFAULT 0,\
734 installed TINYINT DEFAULT 0,\
737 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0)")
741 def select_all_in_status(self):
742 db = self.connect_db()
743 dbexe = db[1].execute("\
744 SELECT ts2avi,terec,bscsrec,b252ts \
750 dls = db[1].fetchall()
760 def select_version_in_status(self):
761 db = self.connect_db()
764 dbexe = db[1].execute("\
769 dls = db[1].fetchall()
773 version=int(str(r[0]))
777 def change_version_in_status(self,version):
778 db = self.connect_db()
780 UPDATE in_status SET version=%s",str(version))
782 def change_ts2avi_in_status(self,i):
787 db = self.connect_db()
789 UPDATE in_status SET ts2avi=ts2avi+%s",i)
791 def change_terec_in_status(self,i):
796 db = self.connect_db()
798 UPDATE in_status SET terec=terec+%s",i)
800 def change_bscsrec_in_status(self,i):
805 db = self.connect_db()
807 UPDATE in_status SET bscsrec=bscsrec+%s",i)
809 def change_b252ts_in_status(self,i):
814 db = self.connect_db()
816 UPDATE in_status SET b252ts=b252ts+%s",i)
818 def select_installed_in_status(self):
819 db = self.connect_db()
820 dbexe = db[1].execute("\
821 SELECT ts2avi,terec,bscsrec,b252ts,installed \
827 dls = db[1].fetchall()
833 def change_installed_in_status(self):
837 db = self.connect_db()
839 UPDATE in_status SET installed=1")
841 def new_epg_timeline(self, bctype):
842 db = self.connect_db()
845 DELETE FROM epg_timeline \
846 WHERE bctype = %s", \
852 CREATE TABLE epg_timeline \
855 channel VARCHAR(100) NOT NULL,\
861 category VARCHAR(100),\
862 UNIQUE unitv(bctype,channel,start,stop,title)\
868 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
869 db = self.connect_db()
871 INSERT IGNORE INTO epg_timeline \
872 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
873 (bctype, channel, start, stop, title, desc,longdesc,category))
876 def add_multi_epg_timeline(self, tvlists):
878 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
880 db = self.connect_db()
882 INSERT IGNORE INTO epg_timeline \
883 (bctype,channel,start,stop,title,exp,longexp,category) \
884 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
887 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
888 db = self.connect_db()
891 epg_ch.chtxt,title,start,stop,exp,longexp,category \
894 WHERE epg_ch.ontv=epg_timeline.channel \
901 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
904 retall = db[1].fetchall()
907 def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
908 db = self.connect_db()
911 epg_ch.chtxt,title,start,stop,exp,longexp,category \
914 WHERE epg_ch.ontv=epg_timeline.channel \
921 ( title LIKE \'%%"+keyword+"%%\' ) \
923 ( exp LIKE \'%%"+keyword+"%%\' ) \
925 ( longexp LIKE \'%%"+keyword+"%%\' ) \
927 dbcmd = db[1].execute(dbexe,(btime, etime))
930 retall = db[1].fetchall()
933 def new_in_auto_bayes_key(self):
934 db = self.connect_db()
936 db[1].execute('CREATE TABLE in_auto_bayes_key \
938 keychar VARCHAR(10),\
940 ratio_rec DECIMAL(32,14),\
941 ratio_all DECIMAL(32,14),\
942 UNIQUE unibayeskey(keychar,chtxt)\
944 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
949 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
950 db = self.connect_db()
951 ratio_rec=str(ratio_rec)
952 ratio_all=str(ratio_all)
954 INSERT IGNORE INTO in_auto_bayes_key \
955 (keychar,chtxt,ratio_rec,ratio_all) \
956 values (%s,%s,%s,%s)',\
957 (key,chtxt,ratio_rec,ratio_all))
959 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
960 db = self.connect_db()
961 add_rec_num=str(add_rec_num)
962 add_all_num=str(add_all_num)
964 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",\
965 (add_rec_num,add_all_num,chtxt))
967 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
970 db = self.connect_db()
972 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)\
975 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
976 beforenum=str(beforenum)
978 db = self.connect_db()
980 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)\
983 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
984 db = self.connect_db()
986 beforenum=str(beforenum)
987 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
988 VALUES (%s,%s,%s,%s)\
989 ON DUPLICATE KEY UPDATE \
990 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
992 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
994 list={key:addnum}のリスト
997 beforenum=str(beforenum)
998 db = self.connect_db()
999 for i,j in list.items():
1000 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
1002 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1003 VALUES (%s,%s,%s,%s)\
1004 ON DUPLICATE KEY UPDATE \
1005 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
1009 def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1010 beforenum=str(beforenum)
1012 db = self.connect_db()
1014 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)\
1017 def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1018 db = self.connect_db()
1019 beforenumf=beforenum
1020 beforenum=str(beforenum)
1021 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1022 VALUES (%s,%s,%s,%s)\
1023 ON DUPLICATE KEY UPDATE \
1024 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
1026 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
1027 beforenumf=beforenum
1028 beforenum=str(beforenum)
1029 db = self.connect_db()
1030 for i,j in list.items():
1031 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
1033 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1034 VALUES (%s,%s,%s,%s)\
1035 ON DUPLICATE KEY UPDATE \
1036 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
1040 def select_by_key_in_auto_bayes_key(self,key,chtxt):
1041 db = self.connect_db()
1042 dbexe = db[1].execute("\
1043 SELECT keychar,chtxt,ratio_rec,ratio_all \
1044 FROM in_auto_bayes_key \
1045 WHERE keychar = %s AND chtxt = %s", \
1049 dls = db[1].fetchall()
1055 def new_auto_timeline_keyword(self):
1056 db = self.connect_db()
1059 CREATE TABLE auto_timeline_keyword \
1061 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1063 title VARCHAR(100),\
1066 UNIQUE uni (chtxt,title,btime,etime)\
1070 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
1071 db = self.connect_db()
1073 INSERT IGNORE into auto_timeline_keyword \
1074 (chtxt,title,btime,etime) \
1075 values (%s,%s,%s,%s)', \
1076 (chtxt, title, btime, etime))
1079 def delete_old_auto_timeline_keyword(self, dhour):
1080 db = self.connect_db()
1082 DELETE FROM auto_timeline_keyword \
1084 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1086 def new_auto_timeline_bayes(self):
1087 db = self.connect_db()
1090 CREATE TABLE auto_timeline_bayes \
1092 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1094 title VARCHAR(100),\
1098 UNIQUE uni (chtxt,title,btime,etime)\
1102 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
1103 db = self.connect_db()
1105 INSERT IGNORE into auto_timeline_bayes \
1106 (chtxt,title,btime,etime,point) \
1107 values (%s,%s,%s,%s,%s)', \
1108 (chtxt, title, btime, etime,point))
1110 def delete_old_auto_timeline_bayes(self, dhour):
1111 db = self.connect_db()
1113 DELETE FROM auto_timeline_bayes \
1115 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1117 def update_db_to93(self):
1118 db = self.connect_db()
1119 self.drop_in_settings()
1120 self.new_in_settings()
1122 ALTER TABLE timeline ADD epgtitle VARCHAR(100),\
1123 ADD epgbtime DATETIME,\
1124 ADD epgetime DATETIME,\
1125 ADD epgduplicate TINYINT DEFAULT 0,\
1126 ADD epgchange TINYINT DEFAULT 0")
1128 ALTER TABLE in_status ADD version TINYINT")
1130 self.change_version_in_status("93")
1131 def update_db_93to94(self):
1132 db = self.connect_db()
1133 self.drop_in_settings()
1134 self.new_in_settings()
1136 ALTER TABLE timeline ADD counter TINYINT DEFAULT -1")
1138 self.change_version_in_status("94")