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 UNIQUE uni (type,chtxt,title,btime,deltaday)\
55 self.new_epg_timeline("")
56 self.new_in_timeline_log()
57 self.new_in_auto_bayes_key()
58 self.new_in_auto_jbk_key()
60 self.new_in_settings()
61 self.new_auto_timeline_bayes()
62 self.new_auto_timeline_keyword()
67 con = MySQLdb.connect(db=self.dbname, host=self.dbhost, port=self.dbport, user=self.dbusr, passwd=self.dbpasswd, charset="utf8")
69 cur.execute('set names utf8;')
71 def close_db(self, db):
75 db = self.connect_db()
77 db[1].execute('drop table epg_ch')
85 ontv VARCHAR(30) PRIMARY KEY,\
97 def add_epg_ch(self, bctype, ontv, chtxt, ch, csch, updatetime):
98 db = self.connect_db()
101 VALUES (%s,%s,%s,%s,%s,"",%s,%s,%s)', \
102 (bctype, ontv, chtxt, ch, csch, updatetime, "1","1"))
104 def select_by_ontv_epg_ch(self, ontv):
105 db = self.connect_db()
106 dbexe = db[1].execute("\
107 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
114 dls = db[1].fetchall()
118 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
121 def select_by_chtxt_epg_ch(self, chtxt):
122 db = self.connect_db()
123 dbexe = db[1].execute("\
124 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
131 dls = db[1].fetchall()
135 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
138 def select_by_bctype_epg_ch(self, bctype):
139 db = self.connect_db()
140 dbexe = db[1].execute("\
141 SELECT bctype,ontv,chtxt,ch,csch,updatetime,status \
143 WHERE bctype = %s", \
148 dls = db[1].fetchall()
151 #recdblist.printutf8(dl)
153 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
157 def select_by_ch_epg_ch(self, ch):
158 db = self.connect_db()
159 dbexe = db[1].execute("\
161 bctype,ontv,chtxt,ch,csch,updatetime \
168 dls = db[1].fetchall()
172 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
175 def select_all_epg_ch(self):
176 db = self.connect_db()
177 dbexe = db[1].execute("\
178 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
184 dls = db[1].fetchall()
188 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
191 def set_new_status(self,dhour):
192 db = self.connect_db()
193 dbexe = db[1].execute("UPDATE epg_ch \
197 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
202 def select_get_update_epg_ch(self, dhour):
203 db = self.connect_db()
204 dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
207 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
213 ORDER BY status DESC")
215 #recdblist.printutf8(dbexe)
217 ret = db[1].fetchall()
220 def update_by_bctype_epg_ch(self, bctype):
221 db = self.connect_db()
227 WHERE bctype = %s", (bctype,))
229 def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
230 db = self.connect_db()
236 WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
238 def update_status_by_bctype_epg_ch(self, bctype, status):
239 db = self.connect_db()
245 WHERE bctype = %s", \
249 def update_chname_by_ontv_epg_ch(self,ontv,chname):
250 db = self.connect_db()
259 def add_auto_proc_tmp(self,type,title,chtxt):
260 db = self.connect_db()
262 INSERT IGNORE into auto_proc_tmp \
264 values (%s,%s,%s)',(type,title,chtxt))
267 def new_auto_proc_tmp(self):
268 db = self.connect_db()
270 db[1].execute('drop table auto_proc_tmp')
275 CREATE TABLE auto_proc_tmp \
278 title VARCHAR(100) PRIMARY KEY,\
280 UNIQUE unibayeskey(title)\
285 def update_auto_proc(self):
286 db = self.connect_db()
288 db[1].execute('INSERT INTO auto_proc SELECT * FROM auto_proc_tmp')
292 def new_auto_proc(self):
293 db = self.connect_db()
295 db[1].execute('drop table auto_proc')
300 CREATE TABLE auto_proc \
303 title VARCHAR(100) PRIMARY KEY,\
305 UNIQUE unibayeskey(title)\
310 def add_auto_proc(self,type,title,chtxt):
311 db = self.connect_db()
313 INSERT IGNORE into auto_proc \
315 values (%s,%s,%s)',(type,title,chtxt))
318 def drop_in_settings(self):
319 db = self.connect_db()
321 db[1].execute('drop table in_settings')
325 def new_in_settings(self):
326 db = self.connect_db()
329 CREATE TABLE in_settings \
333 auto_opt VARCHAR(20),\
334 auto_del_tmp TINYINT\
336 db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"G\",1)")
340 def select_all_in_settings(self):
341 db = self.connect_db()
342 dbexe = db[1].execute("\
343 SELECT auto_jbk,auto_bayes,auto_del_tmp,auto_opt \
349 dls = db[1].fetchall()
359 def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
360 db = self.connect_db()
362 INSERT IGNORE into in_timeline_log \
363 (chtxt,title,btime,etime,opt,exp,longexp,category) \
364 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
365 ( chtxt, title, btime, etime, opt,exp,longexp,category))
368 def del_in_timeline_log(self, title="", chtxt="", btime=""):
372 db = self.connect_db()
374 DELETE FROM in_timeline_log \
375 WHERE title = %s AND chtxt = %s AND btime = %s", \
376 (title, chtxt, btime))
379 def new_in_timeline_log(self):
380 db = self.connect_db()
383 CREATE TABLE in_timeline_log \
385 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
393 category VARCHAR(100),\
394 UNIQUE uni (chtxt,title,btime,category)\
399 def select_chtxt_by_title_timeline_log(self,title):
400 db = self.connect_db()
401 dbexe = db[1].execute("\
403 FROM in_timeline_log \
404 WHERE title LIKE \"%"+title+"%\"\
406 ORDER by sum(1) DESC limit 1")
407 retdb=db[1].fetchall()
414 def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
415 db = self.connect_db()
417 INSERT IGNORE into timeline \
418 (type,chtxt,title,btime,etime,deltatime,deltaday,opt) \
419 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
420 (type, chtxt, title, btime, etime, deltatime, deltaday, opt))
423 def update_epg_timeline(self,type,chtxt,title,btime,epgbtime,epgetime,epgtitle):
424 db = self.connect_db()
427 SET epgbtime=%s,epgetime=%s,epgtitle=%s \
428 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
429 (epgbtime,epgetime,epgtitle,type, chtxt, title, btime))
432 def update_status_change_timeline(self,type,chtxt,title,btime,epgchange):
433 db = self.connect_db()
437 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
438 (epgchange , type, chtxt, title, btime))
441 def update_status_dup_timeline(self,type,chtxt,title,btime,epgduplicate):
442 db = self.connect_db()
445 SET epgduplicate =%s \
446 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
447 (epgduplicate , type, chtxt, title, btime))
450 def del_timeline(self, type="", title="", chtxt="", btime=""):
454 db = self.connect_db()
456 DELETE FROM timeline \
457 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
458 (type, title, chtxt, btime))
461 def select_all_timeline(self):
462 db = self.connect_db()
464 dbr = db[1].execute("\
465 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
467 dbl = db[1].fetchall()
470 recdata = self.getdic_timeline(dbl)
472 def select_bytime_timeline(self, dminutes):
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 \
479 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
480 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
481 dbl = db[1].fetchall()
483 #recdblist.printutf8(dbl)
485 recdatum = self.getdic_timeline(dbl)
487 def select_by_name_time_timeline(self,title,btime,btime2):
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 \
494 WHERE btime > %s AND \
495 btime < %s AND title = %s",(btime,btime2,title))
496 dbl = db[1].fetchall()
498 #recdblist.printutf8(dbl)
500 recdatum = self.getdic_timeline(dbl)
502 def select_bytime_all_timeline(self,btime,etime):
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 \
509 WHERE btime > %s AND \
510 etime < %s",(btime,etime))
511 dbl = db[1].fetchall()
513 #recdblist.printutf8(dbl)
515 recdatum = self.getdic_timeline(dbl)
517 def select_byepgtime_all_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 \
524 WHERE epgbtime >= %s AND \
525 epgetime <= %s",(epgbtime,epgetime))
526 dbl = db[1].fetchall()
528 #recdblist.printutf8(dbl)
530 recdatum=self.getdic_timeline(dbl)
532 def select_byepgtime_over_timeline(self,epgbtime,epgetime):
533 db = self.connect_db()
535 #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 )")
536 dbr = db[1].execute("SELECT \
537 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
539 WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"\
540 ,(epgbtime,epgetime))
541 dbl = db[1].fetchall()
543 #recdblist.printutf8(dbl)
545 recdatum=self.getdic_timeline(dbl)
547 def count_schedule_timeline(self, btime, etime):
550 return [te num,bs/cs num]
552 db = self.connect_db()
553 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
554 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
557 db[1].execute(dbexe, (btime, etime,btime,etime))
559 for typet, bctypet, chtxtt, titlet in dbl:
560 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
561 if bctypet.find("cs") > -1:
563 elif bctypet.find("bs") > -1:
565 elif bctypet.find("te") > -1:
569 def search_schedule_timeline(self,btime,etime):
572 return [(type,bctype,chtxt,title,btime,etime)]
574 db = self.connect_db()
575 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"
576 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
578 db[1].execute(dbexe, (btime, etime,btime,etime))
580 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
581 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
582 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
585 def count_epgschedule_timeline(self, epgbtime, epgetime):
588 return [te num,bs/cs num]
590 db = self.connect_db()
591 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
592 dbexe = dbexe + " WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"
595 db[1].execute(dbexe, (epgbtime, epgetime))
597 for typet, bctypet, chtxtt, titlet in dbl:
598 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
599 if bctypet.find("cs") > -1:
601 elif bctypet.find("bs") > -1:
603 elif bctypet.find("te") > -1:
607 def search_epgschedule_timeline(self,epgbtime,epgetime):
610 return [(type,bctype,chtxt,title,btime,etime)]
612 db = self.connect_db()
613 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"
614 dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )"
616 db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime))
618 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
619 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
620 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
623 def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
624 db = self.connect_db()
626 #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 )")
627 dbr = db[1].execute("SELECT \
628 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange \
630 WHERE btime > %s AND \
632 AND chtxt=%s ",(btime,etime,chtxt))
633 dbl = db[1].fetchall()
635 #recdblist.printutf8(dbl)
637 recdatum = self.getdic_timeline(dbl)
639 def getdic_timeline(self,timelinelists):
641 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt ,epgbtimet , epgetimet ,epgtitlet ,epgduplicatet ,epgchanget in timelinelists:
646 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
647 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
652 ret['epgbtime'] = epgbtimet.strftime("%Y-%m-%d %H:%M:%S")
653 ret['epgetime'] = epgetimet.strftime("%Y-%m-%d %H:%M:%S")
655 ret['epgbtime'] = "2010-01-01 00:00:00"
656 ret['epgetime'] = "2010-01-01 00:00:00"
657 ret['epgtitle'] = epgtitlet
658 ret['epgduplicate'] = epgduplicatet
659 ret['epgchange'] = epgchanget
660 if deltatime == None or deltatime == "":
662 if deltaday == None or deltaday == "":
664 if typet == recdblist.REC_KEYWORD:
665 ret['deltatime'] = deltatime
666 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
667 ret['deltatime'] = deltatime
668 ret['deltaday'] = deltaday
671 def delete_old_timeline(self, dhour):
672 db = self.connect_db()
674 DELETE FROM timeline \
676 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
679 def new_in_auto_jbk_key(self):
680 db = self.connect_db()
683 CREATE TABLE in_auto_jbk_key \
685 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
686 keyword VARCHAR(200),\
687 UNIQUE unijbk (keyword)\
692 def add_in_auto_jbk_key(self,key):
693 db = self.connect_db()
695 INSERT IGNORE into in_auto_jbk_key \
701 def select_all_in_auto_jbk_key(self):
702 db = self.connect_db()
703 dbexe = db[1].execute("\
705 FROM in_auto_jbk_key \
709 ret = db[1].fetchall()
712 def drop_in_status(self):
713 db = self.connect_db()
715 db[1].execute('drop table in_status')
719 def new_in_status(self):
720 db = self.connect_db()
723 CREATE TABLE in_status \
725 ts2avi TINYINT DEFAULT 0,\
726 terec TINYINT DEFAULT 0,\
727 bscsrec TINYINT DEFAULT 0,\
728 b252ts TINYINT DEFAULT 0,\
729 installed TINYINT DEFAULT 0,\
732 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0)")
736 def select_all_in_status(self):
737 db = self.connect_db()
738 dbexe = db[1].execute("\
739 SELECT ts2avi,terec,bscsrec,b252ts \
745 dls = db[1].fetchall()
755 def select_version_in_status(self):
756 db = self.connect_db()
759 dbexe = db[1].execute("\
764 dls = db[1].fetchall()
768 version=int(str(r[0]))
772 def change_version_in_status(self,version):
773 db = self.connect_db()
775 UPDATE in_status SET version=%s",str(version))
777 def change_ts2avi_in_status(self,i):
782 db = self.connect_db()
784 UPDATE in_status SET ts2avi=ts2avi+%s",i)
786 def change_terec_in_status(self,i):
791 db = self.connect_db()
793 UPDATE in_status SET terec=terec+%s",i)
795 def change_bscsrec_in_status(self,i):
800 db = self.connect_db()
802 UPDATE in_status SET bscsrec=bscsrec+%s",i)
804 def change_b252ts_in_status(self,i):
809 db = self.connect_db()
811 UPDATE in_status SET b252ts=b252ts+%s",i)
813 def select_installed_in_status(self):
814 db = self.connect_db()
815 dbexe = db[1].execute("\
816 SELECT ts2avi,terec,bscsrec,b252ts,installed \
822 dls = db[1].fetchall()
828 def change_installed_in_status(self):
832 db = self.connect_db()
834 UPDATE in_status SET installed=1")
836 def new_epg_timeline(self, bctype):
837 db = self.connect_db()
840 DELETE FROM epg_timeline \
841 WHERE bctype = %s", \
847 CREATE TABLE epg_timeline \
850 channel VARCHAR(100) NOT NULL,\
856 category VARCHAR(100),\
857 UNIQUE unitv(bctype,channel,start,stop,title)\
863 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
864 db = self.connect_db()
866 INSERT IGNORE INTO epg_timeline \
867 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
868 (bctype, channel, start, stop, title, desc,longdesc,category))
871 def add_multi_epg_timeline(self, tvlists):
873 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
875 db = self.connect_db()
877 INSERT IGNORE INTO epg_timeline \
878 (bctype,channel,start,stop,title,exp,longexp,category) \
879 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
882 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
883 db = self.connect_db()
886 epg_ch.chtxt,title,start,stop,exp,longexp,category \
889 WHERE epg_ch.ontv=epg_timeline.channel \
896 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
899 retall = db[1].fetchall()
902 def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
903 db = self.connect_db()
906 epg_ch.chtxt,title,start,stop,exp,longexp,category \
909 WHERE epg_ch.ontv=epg_timeline.channel \
916 ( title LIKE \'%%"+keyword+"%%\' ) \
918 ( exp LIKE \'%%"+keyword+"%%\' ) \
920 ( longexp LIKE \'%%"+keyword+"%%\' ) \
922 dbcmd = db[1].execute(dbexe,(btime, etime))
925 retall = db[1].fetchall()
928 def new_in_auto_bayes_key(self):
929 db = self.connect_db()
931 db[1].execute('CREATE TABLE in_auto_bayes_key \
933 keychar VARCHAR(10),\
935 ratio_rec DECIMAL(32,14),\
936 ratio_all DECIMAL(32,14),\
937 UNIQUE unibayeskey(keychar,chtxt)\
939 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
944 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
945 db = self.connect_db()
946 ratio_rec=str(ratio_rec)
947 ratio_all=str(ratio_all)
949 INSERT IGNORE INTO in_auto_bayes_key \
950 (keychar,chtxt,ratio_rec,ratio_all) \
951 values (%s,%s,%s,%s)',\
952 (key,chtxt,ratio_rec,ratio_all))
954 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
955 db = self.connect_db()
956 add_rec_num=str(add_rec_num)
957 add_all_num=str(add_all_num)
959 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",\
960 (add_rec_num,add_all_num,chtxt))
962 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
965 db = self.connect_db()
967 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)\
970 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
971 beforenum=str(beforenum)
973 db = self.connect_db()
975 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)\
978 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
979 db = self.connect_db()
981 beforenum=str(beforenum)
982 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
983 VALUES (%s,%s,%s,%s)\
984 ON DUPLICATE KEY UPDATE \
985 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
987 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
989 list={key:addnum}のリスト
992 beforenum=str(beforenum)
993 db = self.connect_db()
994 for i,j in list.items():
995 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
997 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
998 VALUES (%s,%s,%s,%s)\
999 ON DUPLICATE KEY UPDATE \
1000 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
1004 def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1005 beforenum=str(beforenum)
1007 db = self.connect_db()
1009 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)\
1012 def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1013 db = self.connect_db()
1014 beforenumf=beforenum
1015 beforenum=str(beforenum)
1016 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1017 VALUES (%s,%s,%s,%s)\
1018 ON DUPLICATE KEY UPDATE \
1019 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
1021 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
1022 beforenumf=beforenum
1023 beforenum=str(beforenum)
1024 db = self.connect_db()
1025 for i,j in list.items():
1026 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
1028 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1029 VALUES (%s,%s,%s,%s)\
1030 ON DUPLICATE KEY UPDATE \
1031 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
1035 def select_by_key_in_auto_bayes_key(self,key,chtxt):
1036 db = self.connect_db()
1037 dbexe = db[1].execute("\
1038 SELECT keychar,chtxt,ratio_rec,ratio_all \
1039 FROM in_auto_bayes_key \
1040 WHERE keychar = %s AND chtxt = %s", \
1044 dls = db[1].fetchall()
1050 def new_auto_timeline_keyword(self):
1051 db = self.connect_db()
1054 CREATE TABLE auto_timeline_keyword \
1056 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1058 title VARCHAR(100),\
1061 UNIQUE uni (chtxt,title,btime,etime)\
1065 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
1066 db = self.connect_db()
1068 INSERT IGNORE into auto_timeline_keyword \
1069 (chtxt,title,btime,etime) \
1070 values (%s,%s,%s,%s)', \
1071 (chtxt, title, btime, etime))
1074 def delete_old_auto_timeline_keyword(self, dhour):
1075 db = self.connect_db()
1077 DELETE FROM auto_timeline_keyword \
1079 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1081 def new_auto_timeline_bayes(self):
1082 db = self.connect_db()
1085 CREATE TABLE auto_timeline_bayes \
1087 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1089 title VARCHAR(100),\
1093 UNIQUE uni (chtxt,title,btime,etime)\
1097 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
1098 db = self.connect_db()
1100 INSERT IGNORE into auto_timeline_bayes \
1101 (chtxt,title,btime,etime,point) \
1102 values (%s,%s,%s,%s,%s)', \
1103 (chtxt, title, btime, etime,point))
1105 def delete_old_auto_timeline_bayes(self, dhour):
1106 db = self.connect_db()
1108 DELETE FROM auto_timeline_bayes \
1110 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1112 def update_db_to93(self):
1113 db = self.connect_db()
1115 ALTER TABLE timeline ADD epgtitle VARCHAR(100),\
1116 ADD epgbtime DATETIME,\
1117 ADD epgetime DATETIME,\
1118 ADD epgduplicate TINYINT DEFAULT 0,\
1119 ADD epgchange TINYINT DEFAULT 0")
1121 ALTER TABLE in_status ADD version TINYINT")
1123 self.change_version_in_status("93")