3 # Rec10 TS Recording Tools
4 # Copyright (C) 2009-2010 Yukikaze
10 from decimal import Decimal
17 def __init__(self, dbname, user, passwd, host="localhost", port=3306):
18 warnings.filterwarnings('ignore', "Data truncated for column")
22 self.dbpasswd = passwd
25 con = MySQLdb.connect(user=user, passwd=passwd)
27 cur.execute('CREATE DATABASE ' + dbname + " DEFAULT CHARACTER SET utf8")
30 except Exception, inst:
31 recdblist.Commonlogex("Error", "init (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=800)
32 db = self.connect_db()
35 CREATE TABLE timeline \
37 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
43 deltatime VARCHAR(5),\
46 epgtitle VARCHAR(100),\
49 epgduplicate TINYINT DEFAULT 0,\
50 epgchange TINYINT DEFAULT 0,\
52 counter TINYINT DEFAULT -1,\
53 UNIQUE uni (type,chtxt,title,btime,deltaday)\
55 except Exception, inst:
56 recdblist.Commonlogex("Error", "init (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=800)
58 self.new_epg_timeline("")
59 self.new_in_timeline_log()
60 self.new_in_auto_bayes_key()
61 self.new_in_auto_jbk_key()
63 self.new_in_settings()
64 self.new_auto_timeline_bayes()
65 self.new_auto_timeline_keyword()
70 con = MySQLdb.connect(db=self.dbname, host=self.dbhost, port=self.dbport, user=self.dbusr, passwd=self.dbpasswd, charset="utf8")
72 cur.execute('set names utf8;')
74 def close_db(self, db):
78 db = self.connect_db()
80 db[1].execute('drop table epg_ch')
88 chtxt VARCHAR(20) PRIMARY KEY,\
99 def add_epg_ch(self, bctype, chtxt, ch, csch, updatetime):
100 db = self.connect_db()
103 VALUES (%s,%s,%s,%s,"",%s,%s,%s)', \
104 (bctype, chtxt, ch, csch, updatetime, "1","1"))
106 def select_by_chtxt_epg_ch(self, chtxt):
107 db = self.connect_db()
108 dbexe = db[1].execute("\
109 SELECT bctype,chtxt,ch,csch,updatetime \
116 dls = db[1].fetchall()
120 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
123 def select_by_bctype_epg_ch(self, bctype):
124 db = self.connect_db()
125 dbexe = db[1].execute("\
126 SELECT bctype,chtxt,ch,csch,updatetime,status \
128 WHERE bctype = %s", \
133 dls = db[1].fetchall()
136 #recdblist.printutf8(dl)
138 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
142 def select_by_ch_epg_ch(self, ch):
143 db = self.connect_db()
144 dbexe = db[1].execute("\
146 bctype,chtxt,ch,csch,updatetime \
153 dls = db[1].fetchall()
157 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
160 def select_all_epg_ch(self):
161 db = self.connect_db()
162 dbexe = db[1].execute("\
163 SELECT bctype,chtxt,ch,csch,updatetime \
169 dls = db[1].fetchall()
173 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
176 def set_new_status(self,dhour):
177 db = self.connect_db()
178 dbexe = db[1].execute("UPDATE epg_ch \
182 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
187 def select_get_update_epg_ch(self, dhour):
188 db = self.connect_db()
189 dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
192 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
198 ORDER BY status DESC")
200 #recdblist.printutf8(dbexe)
202 ret = db[1].fetchall()
205 def update_by_bctype_epg_ch(self, bctype):
206 db = self.connect_db()
212 WHERE bctype = %s", (bctype,))
214 def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
215 db = self.connect_db()
221 WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
223 def update_status_by_bctype_epg_ch(self, bctype, status):
224 db = self.connect_db()
230 WHERE bctype = %s", \
234 def update_chname_by_chtxt_epg_ch(self,chtxt,chname):
235 db = self.connect_db()
244 def add_auto_proc_tmp(self,type,title,chtxt):
245 db = self.connect_db()
247 INSERT IGNORE into auto_proc_tmp \
249 values (%s,%s,%s)',(type,title,chtxt))
252 def new_auto_proc_tmp(self):
253 db = self.connect_db()
255 db[1].execute('drop table auto_proc_tmp')
260 CREATE TABLE auto_proc_tmp \
263 title VARCHAR(100) PRIMARY KEY,\
265 UNIQUE unibayeskey(title)\
270 def update_auto_proc(self):
271 db = self.connect_db()
273 db[1].execute('INSERT INTO auto_proc SELECT * FROM auto_proc_tmp')
277 def new_auto_proc(self):
278 db = self.connect_db()
280 db[1].execute('drop table auto_proc')
285 CREATE TABLE auto_proc \
288 title VARCHAR(100) PRIMARY KEY,\
290 UNIQUE unibayeskey(title)\
295 def add_auto_proc(self,type,title,chtxt):
296 db = self.connect_db()
298 INSERT IGNORE into auto_proc \
300 values (%s,%s,%s)',(type,title,chtxt))
303 def drop_in_settings(self):
304 db = self.connect_db()
306 db[1].execute('drop table in_settings')
310 def new_in_settings(self):
311 db = self.connect_db()
314 CREATE TABLE in_settings \
318 auto_opt VARCHAR(20),\
319 auto_del_tmp TINYINT\
321 db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"H\",1)")
325 def select_all_in_settings(self):
326 db = self.connect_db()
327 dbexe = db[1].execute("\
328 SELECT auto_jbk,auto_bayes,auto_del_tmp,auto_opt \
334 dls = db[1].fetchall()
344 def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
345 db = self.connect_db()
347 INSERT IGNORE into in_timeline_log \
348 (chtxt,title,btime,etime,opt,exp,longexp,category) \
349 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
350 ( chtxt, title, btime, etime, opt,exp,longexp,category))
353 def del_in_timeline_log(self, title="", chtxt="", btime=""):
357 db = self.connect_db()
359 DELETE FROM in_timeline_log \
360 WHERE title = %s AND chtxt = %s AND btime = %s", \
361 (title, chtxt, btime))
364 def new_in_timeline_log(self):
365 db = self.connect_db()
368 CREATE TABLE in_timeline_log \
370 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
378 category VARCHAR(100),\
379 UNIQUE uni (chtxt,title,btime,category)\
384 def select_chtxt_by_title_timeline_log(self,title):
385 db = self.connect_db()
386 dbexe = db[1].execute("\
388 FROM in_timeline_log \
389 WHERE title LIKE \"%"+title+"%\"\
391 ORDER by sum(1) DESC limit 1")
392 retdb=db[1].fetchall()
399 def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt="" ,counter=-1):
400 db = self.connect_db()
402 INSERT IGNORE into timeline \
403 (type,chtxt,title,btime,etime,deltatime,deltaday,opt,counter) \
404 values (%s,%s,%s,%s,%s,%s,%s,%s,%s)', \
405 (type, chtxt, title, btime, etime, deltatime, deltaday, opt ,counter))
408 def update_epg_timeline(self,type,chtxt,title,btime,epgbtime,epgetime,epgtitle,epgexp):
409 db = self.connect_db()
412 SET epgbtime=%s,epgetime=%s,epgtitle=%s,epgexp=%s \
413 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
414 (epgbtime,epgetime,epgtitle,epgexp,type, chtxt, title, btime))
417 def update_status_change_timeline(self,type,chtxt,title,btime,epgchange):
418 db = self.connect_db()
422 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
423 (epgchange , type, chtxt, title, btime))
426 def update_status_dup_timeline(self,type,chtxt,title,btime,epgduplicate):
427 db = self.connect_db()
430 SET epgduplicate =%s \
431 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
432 (epgduplicate , type, chtxt, title, btime))
435 def del_timeline(self, type="", title="", chtxt="", btime=""):
439 db = self.connect_db()
441 DELETE FROM timeline \
442 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
443 (type, title, chtxt, btime))
446 def select_all_timeline(self):
447 db = self.connect_db()
449 dbr = db[1].execute("\
450 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
452 dbl = db[1].fetchall()
455 recdata = self.getdic_timeline(dbl)
457 def select_bytime_timeline(self, dminutes):
458 db = self.connect_db()
460 #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 )")
461 dbr = db[1].execute("SELECT \
462 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
464 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
465 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
466 dbl = db[1].fetchall()
468 #recdblist.printutf8(dbl)
470 recdatum = self.getdic_timeline(dbl)
472 def select_by_name_time_timeline(self,title,btime,btime2):
473 db = self.connect_db()
475 #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 )")
476 dbr = db[1].execute("SELECT \
477 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
479 WHERE btime > %s AND \
480 btime < %s AND title = %s",(btime,btime2,title))
481 dbl = db[1].fetchall()
483 #recdblist.printutf8(dbl)
485 recdatum = self.getdic_timeline(dbl)
487 def select_bytime_all_timeline(self,btime,etime):
488 db = self.connect_db()
490 #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 )")
491 dbr = db[1].execute("SELECT \
492 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
494 WHERE btime > %s AND \
495 etime < %s",(btime,etime))
496 dbl = db[1].fetchall()
498 #recdblist.printutf8(dbl)
500 recdatum = self.getdic_timeline(dbl)
502 def select_byepgtime_all_timeline(self,epgbtime,epgetime):
503 db = self.connect_db()
505 #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 )")
506 dbr = db[1].execute("SELECT \
507 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
509 WHERE epgbtime >= %s AND \
510 epgetime <= %s",(epgbtime,epgetime))
511 dbl = db[1].fetchall()
513 #recdblist.printutf8(dbl)
515 recdatum=self.getdic_timeline(dbl)
517 def select_byepgtime_over_timeline(self,epgbtime,epgetime):
518 db = self.connect_db()
520 #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 )")
521 dbr = db[1].execute("SELECT \
522 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
524 WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"\
525 ,(epgbtime,epgetime))
526 dbl = db[1].fetchall()
528 #recdblist.printutf8(dbl)
530 recdatum=self.getdic_timeline(dbl)
532 def count_schedule_timeline(self, btime, etime):
535 return [te num,bs/cs num]
537 db = self.connect_db()
538 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
539 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
542 db[1].execute(dbexe, (btime, etime,btime,etime))
544 for typet, bctypet, chtxtt, titlet in dbl:
545 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
546 if bctypet.find("cs") > -1:
548 elif bctypet.find("bs") > -1:
550 elif bctypet.find("te") > -1:
554 def search_schedule_timeline(self,btime,etime):
557 return [(type,bctype,chtxt,title,btime,etime)]
559 db = self.connect_db()
560 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"
561 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
563 db[1].execute(dbexe, (btime, etime,btime,etime))
565 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
566 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
567 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
570 def count_epgschedule_timeline(self, epgbtime, epgetime):
573 return [te num,bs/cs num]
575 db = self.connect_db()
576 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
577 dbexe = dbexe + " WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"
580 db[1].execute(dbexe, (epgbtime, epgetime))
582 for typet, bctypet, chtxtt, titlet 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 if bctypet.find("cs") > -1:
586 elif bctypet.find("bs") > -1:
588 elif bctypet.find("te") > -1:
592 def search_epgschedule_timeline(self,epgbtime,epgetime):
595 return [(type,bctype,chtxt,title,btime,etime)]
597 db = self.connect_db()
598 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"
599 dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )"
601 db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime))
603 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
604 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
605 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
608 def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
609 db = self.connect_db()
611 #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 )")
612 dbr = db[1].execute("SELECT \
613 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
615 WHERE btime > %s AND \
617 AND chtxt=%s ",(btime,etime,chtxt))
618 dbl = db[1].fetchall()
620 #recdblist.printutf8(dbl)
622 recdatum = self.getdic_timeline(dbl)
624 def getdic_timeline(self,timelinelists):
626 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt ,epgbtimet , epgetimet ,epgtitlet ,epgduplicatet ,epgchanget ,countert in timelinelists:
631 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
632 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
637 ret['epgbtime'] = epgbtimet.strftime("%Y-%m-%d %H:%M:%S")
638 ret['epgetime'] = epgetimet.strftime("%Y-%m-%d %H:%M:%S")
640 ret['epgbtime'] = "2010-01-01 00:00:00"
641 ret['epgetime'] = "2010-01-01 00:00:00"
642 ret['epgtitle'] = epgtitlet
643 ret['epgduplicate'] = epgduplicatet
644 ret['epgchange'] = epgchanget
645 if deltatime == None or deltatime == "":
647 if deltaday == None or deltaday == "":
649 if typet == recdblist.REC_KEYWORD:
650 ret['deltatime'] = deltatime
651 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
652 ret['deltatime'] = deltatime
653 ret['deltaday'] = deltaday
655 ret['counter'] = int(countert)
660 def delete_old_timeline(self, dhour):
661 db = self.connect_db()
663 DELETE FROM timeline \
665 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
668 def new_in_auto_jbk_key(self):
669 db = self.connect_db()
672 CREATE TABLE in_auto_jbk_key \
674 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
675 keyword VARCHAR(200),\
676 UNIQUE unijbk (keyword)\
681 def add_in_auto_jbk_key(self,key):
682 db = self.connect_db()
684 INSERT IGNORE into in_auto_jbk_key \
690 def select_all_in_auto_jbk_key(self):
691 db = self.connect_db()
692 dbexe = db[1].execute("\
694 FROM in_auto_jbk_key \
698 ret = db[1].fetchall()
701 def drop_in_status(self):
702 db = self.connect_db()
704 db[1].execute('drop table in_status')
708 def new_in_status(self):
709 db = self.connect_db()
712 CREATE TABLE in_status \
714 ts2avi TINYINT DEFAULT 0,\
715 terec TINYINT DEFAULT 0,\
716 bscsrec TINYINT DEFAULT 0,\
717 b252ts TINYINT DEFAULT 0,\
718 installed TINYINT DEFAULT 0,\
721 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0)")
722 except Exception, inst:
723 recdblist.Commonlogex("Error", "new_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=800)
725 def select_all_in_status(self):
726 db = self.connect_db()
727 dbexe = db[1].execute("\
728 SELECT ts2avi,terec,bscsrec,b252ts \
734 dls = db[1].fetchall()
744 def select_version_in_status(self):
745 db = self.connect_db()
748 dbexe = db[1].execute("\
753 dls = db[1].fetchall()
757 version=int(str(r[0]))
761 def change_version_in_status(self,version):
762 db = self.connect_db()
764 UPDATE in_status SET version=%s",str(version))
766 def change_ts2avi_in_status(self,i):
771 db = self.connect_db()
773 UPDATE in_status SET ts2avi=ts2avi+%s",i)
775 def change_terec_in_status(self,i):
780 db = self.connect_db()
782 UPDATE in_status SET terec=terec+%s",i)
784 def change_bscsrec_in_status(self,i):
789 db = self.connect_db()
791 UPDATE in_status SET bscsrec=bscsrec+%s",i)
793 def change_b252ts_in_status(self,i):
798 db = self.connect_db()
800 UPDATE in_status SET b252ts=b252ts+%s",i)
802 def select_installed_in_status(self):
803 db = self.connect_db()
804 dbexe = db[1].execute("\
805 SELECT ts2avi,terec,bscsrec,b252ts,installed \
811 dls = db[1].fetchall()
817 def change_installed_in_status(self):
821 db = self.connect_db()
823 UPDATE in_status SET installed=1")
825 def change_chscaned_in_status(self):
829 db = self.connect_db()
831 UPDATE in_status SET installed=2")
833 def new_epg_timeline(self, bctype):
834 db = self.connect_db()
837 DELETE FROM epg_timeline \
838 WHERE bctype = %s", \
840 except Exception, inst:
841 recdblist.Commonlogex("Error", "new_epg_timeline (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=800)
844 CREATE TABLE epg_timeline \
847 channel VARCHAR(100) NOT NULL,\
853 category VARCHAR(100),\
854 UNIQUE unitv(bctype,channel,start,stop,title)\
860 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
861 db = self.connect_db()
863 INSERT IGNORE INTO epg_timeline \
864 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
865 (bctype, channel, start, stop, title, desc,longdesc,category))
868 def add_multi_epg_timeline(self, tvlists):
870 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
872 db = self.connect_db()
874 INSERT IGNORE INTO epg_timeline \
875 (bctype,channel,start,stop,title,exp,longexp,category) \
876 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
879 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
880 db = self.connect_db()
883 epg_ch.chtxt,title,start,stop,exp,longexp,category \
886 WHERE epg_ch.chtxt=epg_timeline.channel \
893 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
896 retall = db[1].fetchall()
899 def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
900 db = self.connect_db()
903 epg_ch.chtxt,title,start,stop,exp,longexp,category \
906 WHERE epg_ch.chtxt=epg_timeline.channel \
913 ( title LIKE \'%%"+keyword+"%%\' ) \
915 ( exp LIKE \'%%"+keyword+"%%\' ) \
917 ( longexp LIKE \'%%"+keyword+"%%\' ) \
919 dbcmd = db[1].execute(dbexe,(btime, etime))
922 retall = db[1].fetchall()
925 def new_in_auto_bayes_key(self):
926 db = self.connect_db()
928 db[1].execute('CREATE TABLE in_auto_bayes_key \
930 keychar VARCHAR(10),\
932 ratio_rec DECIMAL(32,14),\
933 ratio_all DECIMAL(32,14),\
934 UNIQUE unibayeskey(keychar,chtxt)\
936 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
941 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
942 db = self.connect_db()
943 ratio_rec=str(ratio_rec)
944 ratio_all=str(ratio_all)
946 INSERT IGNORE INTO in_auto_bayes_key \
947 (keychar,chtxt,ratio_rec,ratio_all) \
948 values (%s,%s,%s,%s)',\
949 (key,chtxt,ratio_rec,ratio_all))
951 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
952 db = self.connect_db()
953 add_rec_num=str(add_rec_num)
954 add_all_num=str(add_all_num)
956 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",\
957 (add_rec_num,add_all_num,chtxt))
959 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
962 db = self.connect_db()
964 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)\
967 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
968 beforenum=str(beforenum)
970 db = self.connect_db()
972 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)\
975 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
976 db = self.connect_db()
978 beforenum=str(beforenum)
979 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
980 VALUES (%s,%s,%s,%s)\
981 ON DUPLICATE KEY UPDATE \
982 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
984 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
986 list={key:addnum}のリスト
989 beforenum=str(beforenum)
990 db = self.connect_db()
991 for i,j in list.items():
992 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
994 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
995 VALUES (%s,%s,%s,%s)\
996 ON DUPLICATE KEY UPDATE \
997 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
1001 def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1002 beforenum=str(beforenum)
1004 db = self.connect_db()
1006 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)\
1009 def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1010 db = self.connect_db()
1011 beforenumf=beforenum
1012 beforenum=str(beforenum)
1013 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1014 VALUES (%s,%s,%s,%s)\
1015 ON DUPLICATE KEY UPDATE \
1016 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
1018 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
1019 beforenumf=beforenum
1020 beforenum=str(beforenum)
1021 db = self.connect_db()
1022 for i,j in list.items():
1023 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
1025 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1026 VALUES (%s,%s,%s,%s)\
1027 ON DUPLICATE KEY UPDATE \
1028 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
1032 def select_by_key_in_auto_bayes_key(self,key,chtxt):
1033 db = self.connect_db()
1034 dbexe = db[1].execute("\
1035 SELECT keychar,chtxt,ratio_rec,ratio_all \
1036 FROM in_auto_bayes_key \
1037 WHERE keychar = %s AND chtxt = %s", \
1041 dls = db[1].fetchall()
1047 def new_auto_timeline_keyword(self):
1048 db = self.connect_db()
1051 CREATE TABLE auto_timeline_keyword \
1053 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1055 title VARCHAR(100),\
1058 UNIQUE uni (chtxt,title,btime,etime)\
1062 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
1063 db = self.connect_db()
1065 INSERT IGNORE into auto_timeline_keyword \
1066 (chtxt,title,btime,etime) \
1067 values (%s,%s,%s,%s)', \
1068 (chtxt, title, btime, etime))
1071 def delete_old_auto_timeline_keyword(self, dhour):
1072 db = self.connect_db()
1074 DELETE FROM auto_timeline_keyword \
1076 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1078 def new_auto_timeline_bayes(self):
1079 db = self.connect_db()
1082 CREATE TABLE auto_timeline_bayes \
1084 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1086 title VARCHAR(100),\
1090 UNIQUE uni (chtxt,title,btime,etime)\
1094 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
1095 db = self.connect_db()
1097 INSERT IGNORE into auto_timeline_bayes \
1098 (chtxt,title,btime,etime,point) \
1099 values (%s,%s,%s,%s,%s)', \
1100 (chtxt, title, btime, etime,point))
1102 def delete_old_auto_timeline_bayes(self, dhour):
1103 db = self.connect_db()
1105 DELETE FROM auto_timeline_bayes \
1107 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1109 def update_db_to93(self):
1110 db = self.connect_db()
1111 self.drop_in_settings()
1112 self.new_in_settings()
1114 ALTER TABLE timeline ADD epgtitle VARCHAR(100),\
1115 ADD epgbtime DATETIME,\
1116 ADD epgetime DATETIME,\
1117 ADD epgduplicate TINYINT DEFAULT 0,\
1118 ADD epgchange TINYINT DEFAULT 0")
1120 ALTER TABLE in_status ADD version TINYINT")
1122 self.change_version_in_status("93")
1123 def update_db_93to94(self):
1124 db = self.connect_db()
1125 self.drop_in_settings()
1126 self.new_in_settings()
1128 ALTER TABLE timeline ADD counter TINYINT DEFAULT -1")
1130 self.change_version_in_status("94")
1131 def update_db_94to95(self):
1132 db = self.connect_db()
1133 self.drop_in_settings()
1134 self.new_in_settings()
1136 ALTER TABLE timeline ADD epgexp VARCHAR(200)")
1138 self.change_version_in_status("95")
1139 def update_db_95to96(self):
1140 db = self.connect_db()
1141 self.drop_in_settings()
1142 self.new_in_settings()
1144 self.change_version_in_status("96")
1145 def update_db_96to98(self):
1146 db = self.connect_db()
1147 self.drop_in_settings()
1148 self.new_in_settings()
1150 self.change_version_in_status("98")