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 chtxt VARCHAR(20) PRIMARY KEY,\
98 def add_epg_ch(self, bctype, chtxt, ch, csch, updatetime):
99 db = self.connect_db()
102 VALUES (%s,%s,%s,%s,"",%s,%s,%s)', \
103 (bctype, chtxt, ch, csch, updatetime, "1","1"))
105 def select_by_chtxt_epg_ch(self, chtxt):
106 db = self.connect_db()
107 dbexe = db[1].execute("\
108 SELECT bctype,chtxt,ch,csch,updatetime \
115 dls = db[1].fetchall()
119 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
122 def select_by_bctype_epg_ch(self, bctype):
123 db = self.connect_db()
124 dbexe = db[1].execute("\
125 SELECT bctype,chtxt,ch,csch,updatetime,status \
127 WHERE bctype = %s", \
132 dls = db[1].fetchall()
135 #recdblist.printutf8(dl)
137 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
141 def select_by_ch_epg_ch(self, ch):
142 db = self.connect_db()
143 dbexe = db[1].execute("\
145 bctype,chtxt,ch,csch,updatetime \
152 dls = db[1].fetchall()
156 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
159 def select_all_epg_ch(self):
160 db = self.connect_db()
161 dbexe = db[1].execute("\
162 SELECT bctype,chtxt,ch,csch,updatetime \
168 dls = db[1].fetchall()
172 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
175 def set_new_status(self,dhour):
176 db = self.connect_db()
177 dbexe = db[1].execute("UPDATE epg_ch \
181 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
186 def select_get_update_epg_ch(self, dhour):
187 db = self.connect_db()
188 dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
191 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
197 ORDER BY status DESC")
199 #recdblist.printutf8(dbexe)
201 ret = db[1].fetchall()
204 def update_by_bctype_epg_ch(self, bctype):
205 db = self.connect_db()
211 WHERE bctype = %s", (bctype,))
213 def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
214 db = self.connect_db()
220 WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
222 def update_status_by_bctype_epg_ch(self, bctype, status):
223 db = self.connect_db()
229 WHERE bctype = %s", \
233 def update_chname_by_chtxt_epg_ch(self,chtxt,chname):
234 db = self.connect_db()
243 def add_auto_proc_tmp(self,type,title,chtxt):
244 db = self.connect_db()
246 INSERT IGNORE into auto_proc_tmp \
248 values (%s,%s,%s)',(type,title,chtxt))
251 def new_auto_proc_tmp(self):
252 db = self.connect_db()
254 db[1].execute('drop table auto_proc_tmp')
259 CREATE TABLE auto_proc_tmp \
262 title VARCHAR(100) PRIMARY KEY,\
264 UNIQUE unibayeskey(title)\
269 def update_auto_proc(self):
270 db = self.connect_db()
272 db[1].execute('INSERT INTO auto_proc SELECT * FROM auto_proc_tmp')
276 def new_auto_proc(self):
277 db = self.connect_db()
279 db[1].execute('drop table auto_proc')
284 CREATE TABLE auto_proc \
287 title VARCHAR(100) PRIMARY KEY,\
289 UNIQUE unibayeskey(title)\
294 def add_auto_proc(self,type,title,chtxt):
295 db = self.connect_db()
297 INSERT IGNORE into auto_proc \
299 values (%s,%s,%s)',(type,title,chtxt))
302 def drop_in_settings(self):
303 db = self.connect_db()
305 db[1].execute('drop table in_settings')
309 def new_in_settings(self):
310 db = self.connect_db()
313 CREATE TABLE in_settings \
317 auto_opt VARCHAR(20),\
318 auto_del_tmp TINYINT\
320 db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"H\",1)")
324 def select_all_in_settings(self):
325 db = self.connect_db()
326 dbexe = db[1].execute("\
327 SELECT auto_jbk,auto_bayes,auto_del_tmp,auto_opt \
333 dls = db[1].fetchall()
343 def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
344 db = self.connect_db()
346 INSERT IGNORE into in_timeline_log \
347 (chtxt,title,btime,etime,opt,exp,longexp,category) \
348 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
349 ( chtxt, title, btime, etime, opt,exp,longexp,category))
352 def del_in_timeline_log(self, title="", chtxt="", btime=""):
356 db = self.connect_db()
358 DELETE FROM in_timeline_log \
359 WHERE title = %s AND chtxt = %s AND btime = %s", \
360 (title, chtxt, btime))
363 def new_in_timeline_log(self):
364 db = self.connect_db()
367 CREATE TABLE in_timeline_log \
369 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
377 category VARCHAR(100),\
378 UNIQUE uni (chtxt,title,btime,category)\
383 def select_chtxt_by_title_timeline_log(self,title):
384 db = self.connect_db()
385 dbexe = db[1].execute("\
387 FROM in_timeline_log \
388 WHERE title LIKE \"%"+title+"%\"\
390 ORDER by sum(1) DESC limit 1")
391 retdb=db[1].fetchall()
398 def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt="" ,counter=-1):
399 db = self.connect_db()
401 INSERT IGNORE into timeline \
402 (type,chtxt,title,btime,etime,deltatime,deltaday,opt,counter) \
403 values (%s,%s,%s,%s,%s,%s,%s,%s,%s)', \
404 (type, chtxt, title, btime, etime, deltatime, deltaday, opt ,counter))
407 def update_epg_timeline(self,type,chtxt,title,btime,epgbtime,epgetime,epgtitle,epgexp):
408 db = self.connect_db()
411 SET epgbtime=%s,epgetime=%s,epgtitle=%s,epgexp=%s \
412 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
413 (epgbtime,epgetime,epgtitle,epgexp,type, chtxt, title, btime))
416 def update_status_change_timeline(self,type,chtxt,title,btime,epgchange):
417 db = self.connect_db()
421 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
422 (epgchange , type, chtxt, title, btime))
425 def update_status_dup_timeline(self,type,chtxt,title,btime,epgduplicate):
426 db = self.connect_db()
429 SET epgduplicate =%s \
430 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
431 (epgduplicate , type, chtxt, title, btime))
434 def del_timeline(self, type="", title="", chtxt="", btime=""):
438 db = self.connect_db()
440 DELETE FROM timeline \
441 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
442 (type, title, chtxt, btime))
445 def select_all_timeline(self):
446 db = self.connect_db()
448 dbr = db[1].execute("\
449 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
451 dbl = db[1].fetchall()
454 recdata = self.getdic_timeline(dbl)
456 def select_bytime_timeline(self, dminutes):
457 db = self.connect_db()
459 #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 )")
460 dbr = db[1].execute("SELECT \
461 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
463 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
464 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
465 dbl = db[1].fetchall()
467 #recdblist.printutf8(dbl)
469 recdatum = self.getdic_timeline(dbl)
471 def select_by_name_time_timeline(self,title,btime,btime2):
472 db = self.connect_db()
474 #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 )")
475 dbr = db[1].execute("SELECT \
476 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
478 WHERE btime > %s AND \
479 btime < %s AND title = %s",(btime,btime2,title))
480 dbl = db[1].fetchall()
482 #recdblist.printutf8(dbl)
484 recdatum = self.getdic_timeline(dbl)
486 def select_bytime_all_timeline(self,btime,etime):
487 db = self.connect_db()
489 #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 )")
490 dbr = db[1].execute("SELECT \
491 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
493 WHERE btime > %s AND \
494 etime < %s",(btime,etime))
495 dbl = db[1].fetchall()
497 #recdblist.printutf8(dbl)
499 recdatum = self.getdic_timeline(dbl)
501 def select_byepgtime_all_timeline(self,epgbtime,epgetime):
502 db = self.connect_db()
504 #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 )")
505 dbr = db[1].execute("SELECT \
506 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
508 WHERE epgbtime >= %s AND \
509 epgetime <= %s",(epgbtime,epgetime))
510 dbl = db[1].fetchall()
512 #recdblist.printutf8(dbl)
514 recdatum=self.getdic_timeline(dbl)
516 def select_byepgtime_over_timeline(self,epgbtime,epgetime):
517 db = self.connect_db()
519 #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 )")
520 dbr = db[1].execute("SELECT \
521 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
523 WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"\
524 ,(epgbtime,epgetime))
525 dbl = db[1].fetchall()
527 #recdblist.printutf8(dbl)
529 recdatum=self.getdic_timeline(dbl)
531 def count_schedule_timeline(self, btime, etime):
534 return [te num,bs/cs num]
536 db = self.connect_db()
537 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
538 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
541 db[1].execute(dbexe, (btime, etime,btime,etime))
543 for typet, bctypet, chtxtt, titlet in dbl:
544 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
545 if bctypet.find("cs") > -1:
547 elif bctypet.find("bs") > -1:
549 elif bctypet.find("te") > -1:
553 def search_schedule_timeline(self,btime,etime):
556 return [(type,bctype,chtxt,title,btime,etime)]
558 db = self.connect_db()
559 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"
560 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
562 db[1].execute(dbexe, (btime, etime,btime,etime))
564 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
565 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
566 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
569 def count_epgschedule_timeline(self, epgbtime, epgetime):
572 return [te num,bs/cs num]
574 db = self.connect_db()
575 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
576 dbexe = dbexe + " WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"
579 db[1].execute(dbexe, (epgbtime, epgetime))
581 for typet, bctypet, chtxtt, titlet 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 if bctypet.find("cs") > -1:
585 elif bctypet.find("bs") > -1:
587 elif bctypet.find("te") > -1:
591 def search_epgschedule_timeline(self,epgbtime,epgetime):
594 return [(type,bctype,chtxt,title,btime,etime)]
596 db = self.connect_db()
597 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"
598 dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )"
600 db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime))
602 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
603 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
604 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
607 def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
608 db = self.connect_db()
610 #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 )")
611 dbr = db[1].execute("SELECT \
612 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
614 WHERE btime > %s AND \
616 AND chtxt=%s ",(btime,etime,chtxt))
617 dbl = db[1].fetchall()
619 #recdblist.printutf8(dbl)
621 recdatum = self.getdic_timeline(dbl)
623 def getdic_timeline(self,timelinelists):
625 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt ,epgbtimet , epgetimet ,epgtitlet ,epgduplicatet ,epgchanget ,countert in timelinelists:
630 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
631 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
636 ret['epgbtime'] = epgbtimet.strftime("%Y-%m-%d %H:%M:%S")
637 ret['epgetime'] = epgetimet.strftime("%Y-%m-%d %H:%M:%S")
639 ret['epgbtime'] = "2010-01-01 00:00:00"
640 ret['epgetime'] = "2010-01-01 00:00:00"
641 ret['epgtitle'] = epgtitlet
642 ret['epgduplicate'] = epgduplicatet
643 ret['epgchange'] = epgchanget
644 if deltatime == None or deltatime == "":
646 if deltaday == None or deltaday == "":
648 if typet == recdblist.REC_KEYWORD:
649 ret['deltatime'] = deltatime
650 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
651 ret['deltatime'] = deltatime
652 ret['deltaday'] = deltaday
654 ret['counter'] = int(countert)
659 def delete_old_timeline(self, dhour):
660 db = self.connect_db()
662 DELETE FROM timeline \
664 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
667 def new_in_auto_jbk_key(self):
668 db = self.connect_db()
671 CREATE TABLE in_auto_jbk_key \
673 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
674 keyword VARCHAR(200),\
675 UNIQUE unijbk (keyword)\
680 def add_in_auto_jbk_key(self,key):
681 db = self.connect_db()
683 INSERT IGNORE into in_auto_jbk_key \
689 def select_all_in_auto_jbk_key(self):
690 db = self.connect_db()
691 dbexe = db[1].execute("\
693 FROM in_auto_jbk_key \
697 ret = db[1].fetchall()
700 def drop_in_status(self):
701 db = self.connect_db()
703 db[1].execute('drop table in_status')
707 def new_in_status(self):
708 db = self.connect_db()
711 CREATE TABLE in_status \
713 ts2avi TINYINT DEFAULT 0,\
714 terec TINYINT DEFAULT 0,\
715 bscsrec TINYINT DEFAULT 0,\
716 b252ts TINYINT DEFAULT 0,\
717 installed TINYINT DEFAULT 0,\
720 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0)")
724 def select_all_in_status(self):
725 db = self.connect_db()
726 dbexe = db[1].execute("\
727 SELECT ts2avi,terec,bscsrec,b252ts \
733 dls = db[1].fetchall()
743 def select_version_in_status(self):
744 db = self.connect_db()
747 dbexe = db[1].execute("\
752 dls = db[1].fetchall()
756 version=int(str(r[0]))
760 def change_version_in_status(self,version):
761 db = self.connect_db()
763 UPDATE in_status SET version=%s",str(version))
765 def change_ts2avi_in_status(self,i):
770 db = self.connect_db()
772 UPDATE in_status SET ts2avi=ts2avi+%s",i)
774 def change_terec_in_status(self,i):
779 db = self.connect_db()
781 UPDATE in_status SET terec=terec+%s",i)
783 def change_bscsrec_in_status(self,i):
788 db = self.connect_db()
790 UPDATE in_status SET bscsrec=bscsrec+%s",i)
792 def change_b252ts_in_status(self,i):
797 db = self.connect_db()
799 UPDATE in_status SET b252ts=b252ts+%s",i)
801 def select_installed_in_status(self):
802 db = self.connect_db()
803 dbexe = db[1].execute("\
804 SELECT ts2avi,terec,bscsrec,b252ts,installed \
810 dls = db[1].fetchall()
816 def change_installed_in_status(self):
820 db = self.connect_db()
822 UPDATE in_status SET installed=1")
824 def change_chscaned_in_status(self):
828 db = self.connect_db()
830 UPDATE in_status SET installed=2")
832 def new_epg_timeline(self, bctype):
833 db = self.connect_db()
836 DELETE FROM epg_timeline \
837 WHERE bctype = %s", \
843 CREATE TABLE epg_timeline \
846 channel VARCHAR(100) NOT NULL,\
852 category VARCHAR(100),\
853 UNIQUE unitv(bctype,channel,start,stop,title)\
859 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
860 db = self.connect_db()
862 INSERT IGNORE INTO epg_timeline \
863 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
864 (bctype, channel, start, stop, title, desc,longdesc,category))
867 def add_multi_epg_timeline(self, tvlists):
869 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
871 db = self.connect_db()
873 INSERT IGNORE INTO epg_timeline \
874 (bctype,channel,start,stop,title,exp,longexp,category) \
875 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
878 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
879 db = self.connect_db()
882 epg_ch.chtxt,title,start,stop,exp,longexp,category \
885 WHERE epg_ch.chtxt=epg_timeline.channel \
892 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
895 retall = db[1].fetchall()
898 def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
899 db = self.connect_db()
902 epg_ch.chtxt,title,start,stop,exp,longexp,category \
905 WHERE epg_ch.chtxt=epg_timeline.channel \
912 ( title LIKE \'%%"+keyword+"%%\' ) \
914 ( exp LIKE \'%%"+keyword+"%%\' ) \
916 ( longexp LIKE \'%%"+keyword+"%%\' ) \
918 dbcmd = db[1].execute(dbexe,(btime, etime))
921 retall = db[1].fetchall()
924 def new_in_auto_bayes_key(self):
925 db = self.connect_db()
927 db[1].execute('CREATE TABLE in_auto_bayes_key \
929 keychar VARCHAR(10),\
931 ratio_rec DECIMAL(32,14),\
932 ratio_all DECIMAL(32,14),\
933 UNIQUE unibayeskey(keychar,chtxt)\
935 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
940 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
941 db = self.connect_db()
942 ratio_rec=str(ratio_rec)
943 ratio_all=str(ratio_all)
945 INSERT IGNORE INTO in_auto_bayes_key \
946 (keychar,chtxt,ratio_rec,ratio_all) \
947 values (%s,%s,%s,%s)',\
948 (key,chtxt,ratio_rec,ratio_all))
950 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
951 db = self.connect_db()
952 add_rec_num=str(add_rec_num)
953 add_all_num=str(add_all_num)
955 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",\
956 (add_rec_num,add_all_num,chtxt))
958 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
961 db = self.connect_db()
963 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)\
966 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
967 beforenum=str(beforenum)
969 db = self.connect_db()
971 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)\
974 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
975 db = self.connect_db()
977 beforenum=str(beforenum)
978 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
979 VALUES (%s,%s,%s,%s)\
980 ON DUPLICATE KEY UPDATE \
981 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
983 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
985 list={key:addnum}のリスト
988 beforenum=str(beforenum)
989 db = self.connect_db()
990 for i,j in list.items():
991 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
993 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
994 VALUES (%s,%s,%s,%s)\
995 ON DUPLICATE KEY UPDATE \
996 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
1000 def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1001 beforenum=str(beforenum)
1003 db = self.connect_db()
1005 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)\
1008 def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1009 db = self.connect_db()
1010 beforenumf=beforenum
1011 beforenum=str(beforenum)
1012 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1013 VALUES (%s,%s,%s,%s)\
1014 ON DUPLICATE KEY UPDATE \
1015 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
1017 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
1018 beforenumf=beforenum
1019 beforenum=str(beforenum)
1020 db = self.connect_db()
1021 for i,j in list.items():
1022 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
1024 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1025 VALUES (%s,%s,%s,%s)\
1026 ON DUPLICATE KEY UPDATE \
1027 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
1031 def select_by_key_in_auto_bayes_key(self,key,chtxt):
1032 db = self.connect_db()
1033 dbexe = db[1].execute("\
1034 SELECT keychar,chtxt,ratio_rec,ratio_all \
1035 FROM in_auto_bayes_key \
1036 WHERE keychar = %s AND chtxt = %s", \
1040 dls = db[1].fetchall()
1046 def new_auto_timeline_keyword(self):
1047 db = self.connect_db()
1050 CREATE TABLE auto_timeline_keyword \
1052 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1054 title VARCHAR(100),\
1057 UNIQUE uni (chtxt,title,btime,etime)\
1061 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
1062 db = self.connect_db()
1064 INSERT IGNORE into auto_timeline_keyword \
1065 (chtxt,title,btime,etime) \
1066 values (%s,%s,%s,%s)', \
1067 (chtxt, title, btime, etime))
1070 def delete_old_auto_timeline_keyword(self, dhour):
1071 db = self.connect_db()
1073 DELETE FROM auto_timeline_keyword \
1075 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1077 def new_auto_timeline_bayes(self):
1078 db = self.connect_db()
1081 CREATE TABLE auto_timeline_bayes \
1083 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1085 title VARCHAR(100),\
1089 UNIQUE uni (chtxt,title,btime,etime)\
1093 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
1094 db = self.connect_db()
1096 INSERT IGNORE into auto_timeline_bayes \
1097 (chtxt,title,btime,etime,point) \
1098 values (%s,%s,%s,%s,%s)', \
1099 (chtxt, title, btime, etime,point))
1101 def delete_old_auto_timeline_bayes(self, dhour):
1102 db = self.connect_db()
1104 DELETE FROM auto_timeline_bayes \
1106 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1108 def update_db_to93(self):
1109 db = self.connect_db()
1110 self.drop_in_settings()
1111 self.new_in_settings()
1113 ALTER TABLE timeline ADD epgtitle VARCHAR(100),\
1114 ADD epgbtime DATETIME,\
1115 ADD epgetime DATETIME,\
1116 ADD epgduplicate TINYINT DEFAULT 0,\
1117 ADD epgchange TINYINT DEFAULT 0")
1119 ALTER TABLE in_status ADD version TINYINT")
1121 self.change_version_in_status("93")
1122 def update_db_93to94(self):
1123 db = self.connect_db()
1124 self.drop_in_settings()
1125 self.new_in_settings()
1127 ALTER TABLE timeline ADD counter TINYINT DEFAULT -1")
1129 self.change_version_in_status("94")
1130 def update_db_94to95(self):
1131 db = self.connect_db()
1132 self.drop_in_settings()
1133 self.new_in_settings()
1135 ALTER TABLE timeline ADD epgexp VARCHAR(200)")
1137 self.change_version_in_status("95")
1138 def update_db_95to96(self):
1139 db = self.connect_db()
1140 self.drop_in_settings()
1141 self.new_in_settings()
1143 self.change_version_in_status("96")
1144 def update_db_96to98(self):
1145 db = self.connect_db()
1146 self.drop_in_settings()
1147 self.new_in_settings()
1149 self.change_version_in_status("98")