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):
417 db = self.connect_db()
419 INSERT IGNORE into timeline \
420 (type,chtxt,title,btime,etime,deltatime,deltaday,opt,counter) \
421 values (%s,%s,%s,%s,%s,%s,%s,%s,%s)', \
422 (type, chtxt, title, btime, etime, deltatime, deltaday, opt ,counter))
425 def update_epg_timeline(self,type,chtxt,title,btime,epgbtime,epgetime,epgtitle):
426 db = self.connect_db()
429 SET epgbtime=%s,epgetime=%s,epgtitle=%s \
430 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
431 (epgbtime,epgetime,epgtitle,type, chtxt, title, btime))
434 def update_status_change_timeline(self,type,chtxt,title,btime,epgchange):
435 db = self.connect_db()
439 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
440 (epgchange , type, chtxt, title, btime))
443 def update_status_dup_timeline(self,type,chtxt,title,btime,epgduplicate):
444 db = self.connect_db()
447 SET epgduplicate =%s \
448 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
449 (epgduplicate , type, chtxt, title, btime))
452 def del_timeline(self, type="", title="", chtxt="", btime=""):
456 db = self.connect_db()
458 DELETE FROM timeline \
459 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
460 (type, title, chtxt, btime))
463 def select_all_timeline(self):
464 db = self.connect_db()
466 dbr = db[1].execute("\
467 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
469 dbl = db[1].fetchall()
472 recdata = self.getdic_timeline(dbl)
474 def select_bytime_timeline(self, dminutes):
475 db = self.connect_db()
477 #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 )")
478 dbr = db[1].execute("SELECT \
479 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
481 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
482 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
483 dbl = db[1].fetchall()
485 #recdblist.printutf8(dbl)
487 recdatum = self.getdic_timeline(dbl)
489 def select_by_name_time_timeline(self,title,btime,btime2):
490 db = self.connect_db()
492 #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 )")
493 dbr = db[1].execute("SELECT \
494 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
496 WHERE btime > %s AND \
497 btime < %s AND title = %s",(btime,btime2,title))
498 dbl = db[1].fetchall()
500 #recdblist.printutf8(dbl)
502 recdatum = self.getdic_timeline(dbl)
504 def select_bytime_all_timeline(self,btime,etime):
505 db = self.connect_db()
507 #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 )")
508 dbr = db[1].execute("SELECT \
509 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
511 WHERE btime > %s AND \
512 etime < %s",(btime,etime))
513 dbl = db[1].fetchall()
515 #recdblist.printutf8(dbl)
517 recdatum = self.getdic_timeline(dbl)
519 def select_byepgtime_all_timeline(self,epgbtime,epgetime):
520 db = self.connect_db()
522 #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 )")
523 dbr = db[1].execute("SELECT \
524 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
526 WHERE epgbtime >= %s AND \
527 epgetime <= %s",(epgbtime,epgetime))
528 dbl = db[1].fetchall()
530 #recdblist.printutf8(dbl)
532 recdatum=self.getdic_timeline(dbl)
534 def select_byepgtime_over_timeline(self,epgbtime,epgetime):
535 db = self.connect_db()
537 #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 )")
538 dbr = db[1].execute("SELECT \
539 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
541 WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"\
542 ,(epgbtime,epgetime))
543 dbl = db[1].fetchall()
545 #recdblist.printutf8(dbl)
547 recdatum=self.getdic_timeline(dbl)
549 def count_schedule_timeline(self, btime, etime):
552 return [te num,bs/cs num]
554 db = self.connect_db()
555 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
556 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
559 db[1].execute(dbexe, (btime, etime,btime,etime))
561 for typet, bctypet, chtxtt, titlet in dbl:
562 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
563 if bctypet.find("cs") > -1:
565 elif bctypet.find("bs") > -1:
567 elif bctypet.find("te") > -1:
571 def search_schedule_timeline(self,btime,etime):
574 return [(type,bctype,chtxt,title,btime,etime)]
576 db = self.connect_db()
577 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"
578 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
580 db[1].execute(dbexe, (btime, etime,btime,etime))
582 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
583 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
584 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
587 def count_epgschedule_timeline(self, epgbtime, epgetime):
590 return [te num,bs/cs num]
592 db = self.connect_db()
593 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
594 dbexe = dbexe + " WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"
597 db[1].execute(dbexe, (epgbtime, epgetime))
599 for typet, bctypet, chtxtt, titlet in dbl:
600 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
601 if bctypet.find("cs") > -1:
603 elif bctypet.find("bs") > -1:
605 elif bctypet.find("te") > -1:
609 def search_epgschedule_timeline(self,epgbtime,epgetime):
612 return [(type,bctype,chtxt,title,btime,etime)]
614 db = self.connect_db()
615 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"
616 dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )"
618 db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime))
620 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
621 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
622 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
625 def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
626 db = self.connect_db()
628 #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 )")
629 dbr = db[1].execute("SELECT \
630 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
632 WHERE btime > %s AND \
634 AND chtxt=%s ",(btime,etime,chtxt))
635 dbl = db[1].fetchall()
637 #recdblist.printutf8(dbl)
639 recdatum = self.getdic_timeline(dbl)
641 def getdic_timeline(self,timelinelists):
643 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt ,epgbtimet , epgetimet ,epgtitlet ,epgduplicatet ,epgchanget ,countert in timelinelists:
648 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
649 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
654 ret['epgbtime'] = epgbtimet.strftime("%Y-%m-%d %H:%M:%S")
655 ret['epgetime'] = epgetimet.strftime("%Y-%m-%d %H:%M:%S")
657 ret['epgbtime'] = "2010-01-01 00:00:00"
658 ret['epgetime'] = "2010-01-01 00:00:00"
659 ret['epgtitle'] = epgtitlet
660 ret['epgduplicate'] = epgduplicatet
661 ret['epgchange'] = epgchanget
662 if deltatime == None or deltatime == "":
664 if deltaday == None or deltaday == "":
666 if typet == recdblist.REC_KEYWORD:
667 ret['deltatime'] = deltatime
668 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
669 ret['deltatime'] = deltatime
670 ret['deltaday'] = deltaday
671 ret['epgchange'] = int(countert)
674 def delete_old_timeline(self, dhour):
675 db = self.connect_db()
677 DELETE FROM timeline \
679 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
682 def new_in_auto_jbk_key(self):
683 db = self.connect_db()
686 CREATE TABLE in_auto_jbk_key \
688 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
689 keyword VARCHAR(200),\
690 UNIQUE unijbk (keyword)\
695 def add_in_auto_jbk_key(self,key):
696 db = self.connect_db()
698 INSERT IGNORE into in_auto_jbk_key \
704 def select_all_in_auto_jbk_key(self):
705 db = self.connect_db()
706 dbexe = db[1].execute("\
708 FROM in_auto_jbk_key \
712 ret = db[1].fetchall()
715 def drop_in_status(self):
716 db = self.connect_db()
718 db[1].execute('drop table in_status')
722 def new_in_status(self):
723 db = self.connect_db()
726 CREATE TABLE in_status \
728 ts2avi TINYINT DEFAULT 0,\
729 terec TINYINT DEFAULT 0,\
730 bscsrec TINYINT DEFAULT 0,\
731 b252ts TINYINT DEFAULT 0,\
732 installed TINYINT DEFAULT 0,\
735 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0)")
739 def select_all_in_status(self):
740 db = self.connect_db()
741 dbexe = db[1].execute("\
742 SELECT ts2avi,terec,bscsrec,b252ts \
748 dls = db[1].fetchall()
758 def select_version_in_status(self):
759 db = self.connect_db()
762 dbexe = db[1].execute("\
767 dls = db[1].fetchall()
771 version=int(str(r[0]))
775 def change_version_in_status(self,version):
776 db = self.connect_db()
778 UPDATE in_status SET version=%s",str(version))
780 def change_ts2avi_in_status(self,i):
785 db = self.connect_db()
787 UPDATE in_status SET ts2avi=ts2avi+%s",i)
789 def change_terec_in_status(self,i):
794 db = self.connect_db()
796 UPDATE in_status SET terec=terec+%s",i)
798 def change_bscsrec_in_status(self,i):
803 db = self.connect_db()
805 UPDATE in_status SET bscsrec=bscsrec+%s",i)
807 def change_b252ts_in_status(self,i):
812 db = self.connect_db()
814 UPDATE in_status SET b252ts=b252ts+%s",i)
816 def select_installed_in_status(self):
817 db = self.connect_db()
818 dbexe = db[1].execute("\
819 SELECT ts2avi,terec,bscsrec,b252ts,installed \
825 dls = db[1].fetchall()
831 def change_installed_in_status(self):
835 db = self.connect_db()
837 UPDATE in_status SET installed=1")
839 def new_epg_timeline(self, bctype):
840 db = self.connect_db()
843 DELETE FROM epg_timeline \
844 WHERE bctype = %s", \
850 CREATE TABLE epg_timeline \
853 channel VARCHAR(100) NOT NULL,\
859 category VARCHAR(100),\
860 UNIQUE unitv(bctype,channel,start,stop,title)\
866 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
867 db = self.connect_db()
869 INSERT IGNORE INTO epg_timeline \
870 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
871 (bctype, channel, start, stop, title, desc,longdesc,category))
874 def add_multi_epg_timeline(self, tvlists):
876 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
878 db = self.connect_db()
880 INSERT IGNORE INTO epg_timeline \
881 (bctype,channel,start,stop,title,exp,longexp,category) \
882 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
885 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
886 db = self.connect_db()
889 epg_ch.chtxt,title,start,stop,exp,longexp,category \
892 WHERE epg_ch.ontv=epg_timeline.channel \
899 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
902 retall = db[1].fetchall()
905 def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
906 db = self.connect_db()
909 epg_ch.chtxt,title,start,stop,exp,longexp,category \
912 WHERE epg_ch.ontv=epg_timeline.channel \
919 ( title LIKE \'%%"+keyword+"%%\' ) \
921 ( exp LIKE \'%%"+keyword+"%%\' ) \
923 ( longexp LIKE \'%%"+keyword+"%%\' ) \
925 dbcmd = db[1].execute(dbexe,(btime, etime))
928 retall = db[1].fetchall()
931 def new_in_auto_bayes_key(self):
932 db = self.connect_db()
934 db[1].execute('CREATE TABLE in_auto_bayes_key \
936 keychar VARCHAR(10),\
938 ratio_rec DECIMAL(32,14),\
939 ratio_all DECIMAL(32,14),\
940 UNIQUE unibayeskey(keychar,chtxt)\
942 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
947 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
948 db = self.connect_db()
949 ratio_rec=str(ratio_rec)
950 ratio_all=str(ratio_all)
952 INSERT IGNORE INTO in_auto_bayes_key \
953 (keychar,chtxt,ratio_rec,ratio_all) \
954 values (%s,%s,%s,%s)',\
955 (key,chtxt,ratio_rec,ratio_all))
957 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
958 db = self.connect_db()
959 add_rec_num=str(add_rec_num)
960 add_all_num=str(add_all_num)
962 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",\
963 (add_rec_num,add_all_num,chtxt))
965 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
968 db = self.connect_db()
970 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)\
973 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
974 beforenum=str(beforenum)
976 db = self.connect_db()
978 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)\
981 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
982 db = self.connect_db()
984 beforenum=str(beforenum)
985 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
986 VALUES (%s,%s,%s,%s)\
987 ON DUPLICATE KEY UPDATE \
988 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
990 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
992 list={key:addnum}のリスト
995 beforenum=str(beforenum)
996 db = self.connect_db()
997 for i,j in list.items():
998 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
1000 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1001 VALUES (%s,%s,%s,%s)\
1002 ON DUPLICATE KEY UPDATE \
1003 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
1007 def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1008 beforenum=str(beforenum)
1010 db = self.connect_db()
1012 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)\
1015 def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1016 db = self.connect_db()
1017 beforenumf=beforenum
1018 beforenum=str(beforenum)
1019 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1020 VALUES (%s,%s,%s,%s)\
1021 ON DUPLICATE KEY UPDATE \
1022 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
1024 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
1025 beforenumf=beforenum
1026 beforenum=str(beforenum)
1027 db = self.connect_db()
1028 for i,j in list.items():
1029 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
1031 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1032 VALUES (%s,%s,%s,%s)\
1033 ON DUPLICATE KEY UPDATE \
1034 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
1038 def select_by_key_in_auto_bayes_key(self,key,chtxt):
1039 db = self.connect_db()
1040 dbexe = db[1].execute("\
1041 SELECT keychar,chtxt,ratio_rec,ratio_all \
1042 FROM in_auto_bayes_key \
1043 WHERE keychar = %s AND chtxt = %s", \
1047 dls = db[1].fetchall()
1053 def new_auto_timeline_keyword(self):
1054 db = self.connect_db()
1057 CREATE TABLE auto_timeline_keyword \
1059 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1061 title VARCHAR(100),\
1064 UNIQUE uni (chtxt,title,btime,etime)\
1068 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
1069 db = self.connect_db()
1071 INSERT IGNORE into auto_timeline_keyword \
1072 (chtxt,title,btime,etime) \
1073 values (%s,%s,%s,%s)', \
1074 (chtxt, title, btime, etime))
1077 def delete_old_auto_timeline_keyword(self, dhour):
1078 db = self.connect_db()
1080 DELETE FROM auto_timeline_keyword \
1082 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1084 def new_auto_timeline_bayes(self):
1085 db = self.connect_db()
1088 CREATE TABLE auto_timeline_bayes \
1090 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1092 title VARCHAR(100),\
1096 UNIQUE uni (chtxt,title,btime,etime)\
1100 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
1101 db = self.connect_db()
1103 INSERT IGNORE into auto_timeline_bayes \
1104 (chtxt,title,btime,etime,point) \
1105 values (%s,%s,%s,%s,%s)', \
1106 (chtxt, title, btime, etime,point))
1108 def delete_old_auto_timeline_bayes(self, dhour):
1109 db = self.connect_db()
1111 DELETE FROM auto_timeline_bayes \
1113 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1115 def update_db_to93(self):
1116 db = self.connect_db()
1117 self.drop_in_settings()
1118 self.new_in_settings()
1120 ALTER TABLE timeline ADD epgtitle VARCHAR(100),\
1121 ADD epgbtime DATETIME,\
1122 ADD epgetime DATETIME,\
1123 ADD epgduplicate TINYINT DEFAULT 0,\
1124 ADD epgchange TINYINT DEFAULT 0")
1126 ALTER TABLE in_status ADD version TINYINT")
1128 self.change_version_in_status("93")
1129 def update_db_93to94(self):
1130 db = self.connect_db()
1131 self.drop_in_settings()
1132 self.new_in_settings()
1134 ALTER TABLE timeline ADD counter TINYINT DEFAULT -1")
1136 self.change_version_in_status("94")