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\
51 counter TINYINT DEFAULT -1\
52 UNIQUE uni (type,chtxt,title,btime,deltaday)\
57 self.new_epg_timeline("")
58 self.new_in_timeline_log()
59 self.new_in_auto_bayes_key()
60 self.new_in_auto_jbk_key()
62 self.new_in_settings()
63 self.new_auto_timeline_bayes()
64 self.new_auto_timeline_keyword()
69 con = MySQLdb.connect(db=self.dbname, host=self.dbhost, port=self.dbport, user=self.dbusr, passwd=self.dbpasswd, charset="utf8")
71 cur.execute('set names utf8;')
73 def close_db(self, db):
77 db = self.connect_db()
79 db[1].execute('drop table epg_ch')
87 ontv VARCHAR(30) PRIMARY KEY,\
99 def add_epg_ch(self, bctype, ontv, chtxt, ch, csch, updatetime):
100 db = self.connect_db()
103 VALUES (%s,%s,%s,%s,%s,"",%s,%s,%s)', \
104 (bctype, ontv, chtxt, ch, csch, updatetime, "1","1"))
106 def select_by_ontv_epg_ch(self, ontv):
107 db = self.connect_db()
108 dbexe = db[1].execute("\
109 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
116 dls = db[1].fetchall()
120 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
123 def select_by_chtxt_epg_ch(self, chtxt):
124 db = self.connect_db()
125 dbexe = db[1].execute("\
126 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
133 dls = db[1].fetchall()
137 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
140 def select_by_bctype_epg_ch(self, bctype):
141 db = self.connect_db()
142 dbexe = db[1].execute("\
143 SELECT bctype,ontv,chtxt,ch,csch,updatetime,status \
145 WHERE bctype = %s", \
150 dls = db[1].fetchall()
153 #recdblist.printutf8(dl)
155 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
159 def select_by_ch_epg_ch(self, ch):
160 db = self.connect_db()
161 dbexe = db[1].execute("\
163 bctype,ontv,chtxt,ch,csch,updatetime \
170 dls = db[1].fetchall()
174 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
177 def select_all_epg_ch(self):
178 db = self.connect_db()
179 dbexe = db[1].execute("\
180 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
186 dls = db[1].fetchall()
190 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
193 def set_new_status(self,dhour):
194 db = self.connect_db()
195 dbexe = db[1].execute("UPDATE epg_ch \
199 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
204 def select_get_update_epg_ch(self, dhour):
205 db = self.connect_db()
206 dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
209 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
215 ORDER BY status DESC")
217 #recdblist.printutf8(dbexe)
219 ret = db[1].fetchall()
222 def update_by_bctype_epg_ch(self, bctype):
223 db = self.connect_db()
229 WHERE bctype = %s", (bctype,))
231 def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
232 db = self.connect_db()
238 WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
240 def update_status_by_bctype_epg_ch(self, bctype, status):
241 db = self.connect_db()
247 WHERE bctype = %s", \
251 def update_chname_by_ontv_epg_ch(self,ontv,chname):
252 db = self.connect_db()
261 def add_auto_proc_tmp(self,type,title,chtxt):
262 db = self.connect_db()
264 INSERT IGNORE into auto_proc_tmp \
266 values (%s,%s,%s)',(type,title,chtxt))
269 def new_auto_proc_tmp(self):
270 db = self.connect_db()
272 db[1].execute('drop table auto_proc_tmp')
277 CREATE TABLE auto_proc_tmp \
280 title VARCHAR(100) PRIMARY KEY,\
282 UNIQUE unibayeskey(title)\
287 def update_auto_proc(self):
288 db = self.connect_db()
290 db[1].execute('INSERT INTO auto_proc SELECT * FROM auto_proc_tmp')
294 def new_auto_proc(self):
295 db = self.connect_db()
297 db[1].execute('drop table auto_proc')
302 CREATE TABLE auto_proc \
305 title VARCHAR(100) PRIMARY KEY,\
307 UNIQUE unibayeskey(title)\
312 def add_auto_proc(self,type,title,chtxt):
313 db = self.connect_db()
315 INSERT IGNORE into auto_proc \
317 values (%s,%s,%s)',(type,title,chtxt))
320 def drop_in_settings(self):
321 db = self.connect_db()
323 db[1].execute('drop table in_settings')
327 def new_in_settings(self):
328 db = self.connect_db()
331 CREATE TABLE in_settings \
335 auto_opt VARCHAR(20),\
336 auto_del_tmp TINYINT\
338 db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"H\",1)")
342 def select_all_in_settings(self):
343 db = self.connect_db()
344 dbexe = db[1].execute("\
345 SELECT auto_jbk,auto_bayes,auto_del_tmp,auto_opt \
351 dls = db[1].fetchall()
361 def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
362 db = self.connect_db()
364 INSERT IGNORE into in_timeline_log \
365 (chtxt,title,btime,etime,opt,exp,longexp,category) \
366 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
367 ( chtxt, title, btime, etime, opt,exp,longexp,category))
370 def del_in_timeline_log(self, title="", chtxt="", btime=""):
374 db = self.connect_db()
376 DELETE FROM in_timeline_log \
377 WHERE title = %s AND chtxt = %s AND btime = %s", \
378 (title, chtxt, btime))
381 def new_in_timeline_log(self):
382 db = self.connect_db()
385 CREATE TABLE in_timeline_log \
387 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
395 category VARCHAR(100),\
396 UNIQUE uni (chtxt,title,btime,category)\
401 def select_chtxt_by_title_timeline_log(self,title):
402 db = self.connect_db()
403 dbexe = db[1].execute("\
405 FROM in_timeline_log \
406 WHERE title LIKE \"%"+title+"%\"\
408 ORDER by sum(1) DESC limit 1")
409 retdb=db[1].fetchall()
416 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,epgexp):
426 db = self.connect_db()
429 SET epgbtime=%s,epgetime=%s,epgtitle=%s,epgexp=%s \
430 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
431 (epgbtime,epgetime,epgtitle,epgexp,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
672 ret['counter'] = int(countert)
677 def delete_old_timeline(self, dhour):
678 db = self.connect_db()
680 DELETE FROM timeline \
682 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
685 def new_in_auto_jbk_key(self):
686 db = self.connect_db()
689 CREATE TABLE in_auto_jbk_key \
691 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
692 keyword VARCHAR(200),\
693 UNIQUE unijbk (keyword)\
698 def add_in_auto_jbk_key(self,key):
699 db = self.connect_db()
701 INSERT IGNORE into in_auto_jbk_key \
707 def select_all_in_auto_jbk_key(self):
708 db = self.connect_db()
709 dbexe = db[1].execute("\
711 FROM in_auto_jbk_key \
715 ret = db[1].fetchall()
718 def drop_in_status(self):
719 db = self.connect_db()
721 db[1].execute('drop table in_status')
725 def new_in_status(self):
726 db = self.connect_db()
729 CREATE TABLE in_status \
731 ts2avi TINYINT DEFAULT 0,\
732 terec TINYINT DEFAULT 0,\
733 bscsrec TINYINT DEFAULT 0,\
734 b252ts TINYINT DEFAULT 0,\
735 installed TINYINT DEFAULT 0,\
738 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0)")
742 def select_all_in_status(self):
743 db = self.connect_db()
744 dbexe = db[1].execute("\
745 SELECT ts2avi,terec,bscsrec,b252ts \
751 dls = db[1].fetchall()
761 def select_version_in_status(self):
762 db = self.connect_db()
765 dbexe = db[1].execute("\
770 dls = db[1].fetchall()
774 version=int(str(r[0]))
778 def change_version_in_status(self,version):
779 db = self.connect_db()
781 UPDATE in_status SET version=%s",str(version))
783 def change_ts2avi_in_status(self,i):
788 db = self.connect_db()
790 UPDATE in_status SET ts2avi=ts2avi+%s",i)
792 def change_terec_in_status(self,i):
797 db = self.connect_db()
799 UPDATE in_status SET terec=terec+%s",i)
801 def change_bscsrec_in_status(self,i):
806 db = self.connect_db()
808 UPDATE in_status SET bscsrec=bscsrec+%s",i)
810 def change_b252ts_in_status(self,i):
815 db = self.connect_db()
817 UPDATE in_status SET b252ts=b252ts+%s",i)
819 def select_installed_in_status(self):
820 db = self.connect_db()
821 dbexe = db[1].execute("\
822 SELECT ts2avi,terec,bscsrec,b252ts,installed \
828 dls = db[1].fetchall()
834 def change_installed_in_status(self):
838 db = self.connect_db()
840 UPDATE in_status SET installed=1")
842 def new_epg_timeline(self, bctype):
843 db = self.connect_db()
846 DELETE FROM epg_timeline \
847 WHERE bctype = %s", \
853 CREATE TABLE epg_timeline \
856 channel VARCHAR(100) NOT NULL,\
862 category VARCHAR(100),\
863 UNIQUE unitv(bctype,channel,start,stop,title)\
869 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
870 db = self.connect_db()
872 INSERT IGNORE INTO epg_timeline \
873 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
874 (bctype, channel, start, stop, title, desc,longdesc,category))
877 def add_multi_epg_timeline(self, tvlists):
879 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
881 db = self.connect_db()
883 INSERT IGNORE INTO epg_timeline \
884 (bctype,channel,start,stop,title,exp,longexp,category) \
885 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
888 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
889 db = self.connect_db()
892 epg_ch.chtxt,title,start,stop,exp,longexp,category \
895 WHERE epg_ch.ontv=epg_timeline.channel \
902 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
905 retall = db[1].fetchall()
908 def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
909 db = self.connect_db()
912 epg_ch.chtxt,title,start,stop,exp,longexp,category \
915 WHERE epg_ch.ontv=epg_timeline.channel \
922 ( title LIKE \'%%"+keyword+"%%\' ) \
924 ( exp LIKE \'%%"+keyword+"%%\' ) \
926 ( longexp LIKE \'%%"+keyword+"%%\' ) \
928 dbcmd = db[1].execute(dbexe,(btime, etime))
931 retall = db[1].fetchall()
934 def new_in_auto_bayes_key(self):
935 db = self.connect_db()
937 db[1].execute('CREATE TABLE in_auto_bayes_key \
939 keychar VARCHAR(10),\
941 ratio_rec DECIMAL(32,14),\
942 ratio_all DECIMAL(32,14),\
943 UNIQUE unibayeskey(keychar,chtxt)\
945 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
950 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
951 db = self.connect_db()
952 ratio_rec=str(ratio_rec)
953 ratio_all=str(ratio_all)
955 INSERT IGNORE INTO in_auto_bayes_key \
956 (keychar,chtxt,ratio_rec,ratio_all) \
957 values (%s,%s,%s,%s)',\
958 (key,chtxt,ratio_rec,ratio_all))
960 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
961 db = self.connect_db()
962 add_rec_num=str(add_rec_num)
963 add_all_num=str(add_all_num)
965 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",\
966 (add_rec_num,add_all_num,chtxt))
968 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
971 db = self.connect_db()
973 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)\
976 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
977 beforenum=str(beforenum)
979 db = self.connect_db()
981 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)\
984 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
985 db = self.connect_db()
987 beforenum=str(beforenum)
988 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
989 VALUES (%s,%s,%s,%s)\
990 ON DUPLICATE KEY UPDATE \
991 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
993 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
995 list={key:addnum}のリスト
998 beforenum=str(beforenum)
999 db = self.connect_db()
1000 for i,j in list.items():
1001 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
1003 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1004 VALUES (%s,%s,%s,%s)\
1005 ON DUPLICATE KEY UPDATE \
1006 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
1010 def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1011 beforenum=str(beforenum)
1013 db = self.connect_db()
1015 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)\
1018 def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1019 db = self.connect_db()
1020 beforenumf=beforenum
1021 beforenum=str(beforenum)
1022 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1023 VALUES (%s,%s,%s,%s)\
1024 ON DUPLICATE KEY UPDATE \
1025 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
1027 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
1028 beforenumf=beforenum
1029 beforenum=str(beforenum)
1030 db = self.connect_db()
1031 for i,j in list.items():
1032 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
1034 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1035 VALUES (%s,%s,%s,%s)\
1036 ON DUPLICATE KEY UPDATE \
1037 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
1041 def select_by_key_in_auto_bayes_key(self,key,chtxt):
1042 db = self.connect_db()
1043 dbexe = db[1].execute("\
1044 SELECT keychar,chtxt,ratio_rec,ratio_all \
1045 FROM in_auto_bayes_key \
1046 WHERE keychar = %s AND chtxt = %s", \
1050 dls = db[1].fetchall()
1056 def new_auto_timeline_keyword(self):
1057 db = self.connect_db()
1060 CREATE TABLE auto_timeline_keyword \
1062 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1064 title VARCHAR(100),\
1067 UNIQUE uni (chtxt,title,btime,etime)\
1071 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
1072 db = self.connect_db()
1074 INSERT IGNORE into auto_timeline_keyword \
1075 (chtxt,title,btime,etime) \
1076 values (%s,%s,%s,%s)', \
1077 (chtxt, title, btime, etime))
1080 def delete_old_auto_timeline_keyword(self, dhour):
1081 db = self.connect_db()
1083 DELETE FROM auto_timeline_keyword \
1085 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1087 def new_auto_timeline_bayes(self):
1088 db = self.connect_db()
1091 CREATE TABLE auto_timeline_bayes \
1093 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1095 title VARCHAR(100),\
1099 UNIQUE uni (chtxt,title,btime,etime)\
1103 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
1104 db = self.connect_db()
1106 INSERT IGNORE into auto_timeline_bayes \
1107 (chtxt,title,btime,etime,point) \
1108 values (%s,%s,%s,%s,%s)', \
1109 (chtxt, title, btime, etime,point))
1111 def delete_old_auto_timeline_bayes(self, dhour):
1112 db = self.connect_db()
1114 DELETE FROM auto_timeline_bayes \
1116 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1118 def update_db_to93(self):
1119 db = self.connect_db()
1120 self.drop_in_settings()
1121 self.new_in_settings()
1123 ALTER TABLE timeline ADD epgtitle VARCHAR(100),\
1124 ADD epgbtime DATETIME,\
1125 ADD epgetime DATETIME,\
1126 ADD epgduplicate TINYINT DEFAULT 0,\
1127 ADD epgchange TINYINT DEFAULT 0")
1129 ALTER TABLE in_status ADD version TINYINT")
1131 self.change_version_in_status("93")
1132 def update_db_93to94(self):
1133 db = self.connect_db()
1134 self.drop_in_settings()
1135 self.new_in_settings()
1137 ALTER TABLE timeline ADD counter TINYINT DEFAULT -1")
1139 self.change_version_in_status("94")
1140 def update_db_94to95(self):
1141 db = self.connect_db()
1142 self.drop_in_settings()
1143 self.new_in_settings()
1145 ALTER TABLE timeline ADD epgexp VARCHAR(200)")
1147 self.change_version_in_status("95")
1148 def update_db_95to96(self):
1149 db = self.connect_db()
1150 self.drop_in_settings()
1151 self.new_in_settings()
1153 self.change_version_in_status("96")