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 if not (type(inst)==MySQLdb.ProgrammingError and (inst[0] == 1007 or inst[0]==1050)):
32 recdblist.Commonlogex("Error", "init (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
33 db = self.connect_db()
36 CREATE TABLE timeline \
38 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
44 deltatime VARCHAR(5),\
47 epgtitle VARCHAR(100),\
50 epgduplicate TINYINT DEFAULT 0,\
51 epgchange TINYINT DEFAULT 0,\
53 counter TINYINT DEFAULT -1,\
54 UNIQUE uni (type,chtxt,title,btime,deltaday)\
56 except Exception, inst:
57 recdblist.Commonlogex("Error", "init (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=800)
59 self.new_epg_timeline("")
60 self.new_in_timeline_log()
61 self.new_in_auto_bayes_key()
62 self.new_in_auto_jbk_key()
64 self.new_in_settings()
65 self.new_auto_timeline_bayes()
66 self.new_auto_timeline_keyword()
71 con = MySQLdb.connect(db=self.dbname, host=self.dbhost, port=self.dbport, user=self.dbusr, passwd=self.dbpasswd, charset="utf8")
73 cur.execute('set names utf8;')
75 def close_db(self, db):
79 db = self.connect_db()
81 db[1].execute('drop table epg_ch')
89 chtxt VARCHAR(20) PRIMARY KEY,\
100 def add_epg_ch(self, bctype, chtxt, ch, csch, updatetime):
101 db = self.connect_db()
104 VALUES (%s,%s,%s,%s,"",%s,%s,%s)', \
105 (bctype, chtxt, ch, csch, updatetime, "1","1"))
107 def select_by_chtxt_epg_ch(self, chtxt):
108 db = self.connect_db()
109 dbexe = db[1].execute("\
110 SELECT bctype,chtxt,ch,csch,updatetime \
117 dls = db[1].fetchall()
121 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
124 def select_by_bctype_epg_ch(self, bctype):
125 db = self.connect_db()
126 dbexe = db[1].execute("\
127 SELECT bctype,chtxt,ch,csch,updatetime,status \
129 WHERE bctype = %s", \
134 dls = db[1].fetchall()
137 #recdblist.printutf8(dl)
139 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
143 def select_by_ch_epg_ch(self, ch):
144 db = self.connect_db()
145 dbexe = db[1].execute("\
147 bctype,chtxt,ch,csch,updatetime \
154 dls = db[1].fetchall()
158 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
161 def select_all_epg_ch(self):
162 db = self.connect_db()
163 dbexe = db[1].execute("\
164 SELECT bctype,chtxt,ch,csch,updatetime \
170 dls = db[1].fetchall()
174 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
177 def set_new_status(self,dhour):
178 db = self.connect_db()
179 dbexe = db[1].execute("UPDATE epg_ch \
183 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
188 def select_get_update_epg_ch(self, dhour):
189 db = self.connect_db()
190 dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
193 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
199 ORDER BY status DESC")
201 #recdblist.printutf8(dbexe)
203 ret = db[1].fetchall()
206 def update_by_bctype_epg_ch(self, bctype):
207 db = self.connect_db()
213 WHERE bctype = %s", (bctype,))
215 def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
216 db = self.connect_db()
222 WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
224 def update_status_by_bctype_epg_ch(self, bctype, status):
225 db = self.connect_db()
231 WHERE bctype = %s", \
235 def update_chname_by_chtxt_epg_ch(self,chtxt,chname):
236 db = self.connect_db()
245 def add_auto_proc_tmp(self,type,title,chtxt):
246 db = self.connect_db()
248 INSERT IGNORE into auto_proc_tmp \
250 values (%s,%s,%s)',(type,title,chtxt))
253 def new_auto_proc_tmp(self):
254 db = self.connect_db()
256 db[1].execute('drop table auto_proc_tmp')
261 CREATE TABLE auto_proc_tmp \
264 title VARCHAR(100) PRIMARY KEY,\
266 UNIQUE unibayeskey(title)\
271 def update_auto_proc(self):
272 db = self.connect_db()
274 db[1].execute('INSERT INTO auto_proc SELECT * FROM auto_proc_tmp')
278 def new_auto_proc(self):
279 db = self.connect_db()
281 db[1].execute('drop table auto_proc')
286 CREATE TABLE auto_proc \
289 title VARCHAR(100) PRIMARY KEY,\
291 UNIQUE unibayeskey(title)\
296 def add_auto_proc(self,type,title,chtxt):
297 db = self.connect_db()
299 INSERT IGNORE into auto_proc \
301 values (%s,%s,%s)',(type,title,chtxt))
304 def drop_in_settings(self):
305 db = self.connect_db()
307 db[1].execute('drop table in_settings')
311 def new_in_settings(self):
312 db = self.connect_db()
315 CREATE TABLE in_settings \
319 auto_opt VARCHAR(20),\
320 auto_del_tmp TINYINT\
322 db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"H\",1)")
326 def select_all_in_settings(self):
327 db = self.connect_db()
328 dbexe = db[1].execute("\
329 SELECT auto_jbk,auto_bayes,auto_del_tmp,auto_opt \
335 dls = db[1].fetchall()
345 def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
346 db = self.connect_db()
348 INSERT IGNORE into in_timeline_log \
349 (chtxt,title,btime,etime,opt,exp,longexp,category) \
350 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
351 ( chtxt, title, btime, etime, opt,exp,longexp,category))
354 def del_in_timeline_log(self, title="", chtxt="", btime=""):
358 db = self.connect_db()
360 DELETE FROM in_timeline_log \
361 WHERE title = %s AND chtxt = %s AND btime = %s", \
362 (title, chtxt, btime))
365 def new_in_timeline_log(self):
366 db = self.connect_db()
369 CREATE TABLE in_timeline_log \
371 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
379 category VARCHAR(100),\
380 UNIQUE uni (chtxt,title,btime,category)\
385 def select_chtxt_by_title_timeline_log(self,title):
386 db = self.connect_db()
387 dbexe = db[1].execute("\
389 FROM in_timeline_log \
390 WHERE title LIKE \"%"+title+"%\"\
392 ORDER by sum(1) DESC limit 1")
393 retdb=db[1].fetchall()
400 def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt="" ,counter=-1):
401 db = self.connect_db()
403 INSERT IGNORE into timeline \
404 (type,chtxt,title,btime,etime,deltatime,deltaday,opt,counter) \
405 values (%s,%s,%s,%s,%s,%s,%s,%s,%s)', \
406 (type, chtxt, title, btime, etime, deltatime, deltaday, opt ,counter))
409 def update_epg_timeline(self,type,chtxt,title,btime,epgbtime,epgetime,epgtitle,epgexp):
410 db = self.connect_db()
413 SET epgbtime=%s,epgetime=%s,epgtitle=%s,epgexp=%s \
414 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
415 (epgbtime,epgetime,epgtitle,epgexp,type, chtxt, title, btime))
418 def update_status_change_timeline(self,type,chtxt,title,btime,epgchange):
419 db = self.connect_db()
423 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
424 (epgchange , type, chtxt, title, btime))
427 def update_status_dup_timeline(self,type,chtxt,title,btime,epgduplicate):
428 db = self.connect_db()
431 SET epgduplicate =%s \
432 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
433 (epgduplicate , type, chtxt, title, btime))
436 def del_timeline(self, type="", title="", chtxt="", btime=""):
440 db = self.connect_db()
442 DELETE FROM timeline \
443 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
444 (type, title, chtxt, btime))
447 def select_all_timeline(self):
448 db = self.connect_db()
450 dbr = db[1].execute("\
451 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
453 dbl = db[1].fetchall()
456 recdata = self.getdic_timeline(dbl)
458 def select_bytime_timeline(self, dminutes):
459 db = self.connect_db()
461 #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 )")
462 dbr = db[1].execute("SELECT \
463 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
465 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
466 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
467 dbl = db[1].fetchall()
469 #recdblist.printutf8(dbl)
471 recdatum = self.getdic_timeline(dbl)
473 def select_by_name_time_timeline(self,title,btime,btime2):
474 db = self.connect_db()
476 #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 )")
477 dbr = db[1].execute("SELECT \
478 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
480 WHERE btime > %s AND \
481 btime < %s AND title = %s",(btime,btime2,title))
482 dbl = db[1].fetchall()
484 #recdblist.printutf8(dbl)
486 recdatum = self.getdic_timeline(dbl)
488 def select_bytime_all_timeline(self,btime,etime):
489 db = self.connect_db()
491 #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 )")
492 dbr = db[1].execute("SELECT \
493 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
495 WHERE btime > %s AND \
496 etime < %s",(btime,etime))
497 dbl = db[1].fetchall()
499 #recdblist.printutf8(dbl)
501 recdatum = self.getdic_timeline(dbl)
503 def select_byepgtime_all_timeline(self,epgbtime,epgetime):
504 db = self.connect_db()
506 #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 )")
507 dbr = db[1].execute("SELECT \
508 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
510 WHERE epgbtime >= %s AND \
511 epgetime <= %s",(epgbtime,epgetime))
512 dbl = db[1].fetchall()
514 #recdblist.printutf8(dbl)
516 recdatum=self.getdic_timeline(dbl)
518 def select_byepgtime_over_timeline(self,epgbtime,epgetime):
519 db = self.connect_db()
521 #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 )")
522 dbr = db[1].execute("SELECT \
523 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
525 WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"\
526 ,(epgbtime,epgetime))
527 dbl = db[1].fetchall()
529 #recdblist.printutf8(dbl)
531 recdatum=self.getdic_timeline(dbl)
533 def count_schedule_timeline(self, btime, etime):
536 return [te num,bs/cs num]
538 db = self.connect_db()
539 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
540 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
543 db[1].execute(dbexe, (btime, etime,btime,etime))
545 for typet, bctypet, chtxtt, titlet in dbl:
546 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
547 if bctypet.find("cs") > -1:
549 elif bctypet.find("bs") > -1:
551 elif bctypet.find("te") > -1:
555 def search_schedule_timeline(self,btime,etime):
558 return [(type,bctype,chtxt,title,btime,etime)]
560 db = self.connect_db()
561 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"
562 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
564 db[1].execute(dbexe, (btime, etime,btime,etime))
566 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
567 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
568 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
571 def count_epgschedule_timeline(self, epgbtime, epgetime):
574 return [te num,bs/cs num]
576 db = self.connect_db()
577 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
578 dbexe = dbexe + " WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"
581 db[1].execute(dbexe, (epgbtime, epgetime))
583 for typet, bctypet, chtxtt, titlet in dbl:
584 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
585 if bctypet.find("cs") > -1:
587 elif bctypet.find("bs") > -1:
589 elif bctypet.find("te") > -1:
593 def search_epgschedule_timeline(self,epgbtime,epgetime):
596 return [(type,bctype,chtxt,title,btime,etime)]
598 db = self.connect_db()
599 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"
600 dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )"
602 db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime))
604 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
605 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
606 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
609 def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
610 db = self.connect_db()
612 #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 )")
613 dbr = db[1].execute("SELECT \
614 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
616 WHERE btime > %s AND \
618 AND chtxt=%s ",(btime,etime,chtxt))
619 dbl = db[1].fetchall()
621 #recdblist.printutf8(dbl)
623 recdatum = self.getdic_timeline(dbl)
625 def getdic_timeline(self,timelinelists):
627 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt ,epgbtimet , epgetimet ,epgtitlet ,epgduplicatet ,epgchanget ,countert in timelinelists:
632 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
633 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
638 ret['epgbtime'] = epgbtimet.strftime("%Y-%m-%d %H:%M:%S")
639 ret['epgetime'] = epgetimet.strftime("%Y-%m-%d %H:%M:%S")
641 ret['epgbtime'] = "2010-01-01 00:00:00"
642 ret['epgetime'] = "2010-01-01 00:00:00"
643 ret['epgtitle'] = epgtitlet
644 ret['epgduplicate'] = epgduplicatet
645 ret['epgchange'] = epgchanget
646 if deltatime == None or deltatime == "":
648 if deltaday == None or deltaday == "":
650 if typet == recdblist.REC_KEYWORD:
651 ret['deltatime'] = deltatime
652 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
653 ret['deltatime'] = deltatime
654 ret['deltaday'] = deltaday
656 ret['counter'] = int(countert)
661 def delete_old_timeline(self, dhour):
662 db = self.connect_db()
664 DELETE FROM timeline \
666 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
669 def new_in_auto_jbk_key(self):
670 db = self.connect_db()
673 CREATE TABLE in_auto_jbk_key \
675 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
676 keyword VARCHAR(200),\
677 auto TINYINT DEFAULT 0,\
679 UNIQUE unijbk (keyword)\
684 def add_in_auto_jbk_key(self,key):
685 db = self.connect_db()
687 INSERT IGNORE into in_auto_jbk_key \
693 def select_all_in_auto_jbk_key(self):
694 db = self.connect_db()
695 dbexe = db[1].execute("\
696 SELECT keyword,auto,opt \
697 FROM in_auto_jbk_key \
701 ret = db[1].fetchall()
704 def drop_in_status(self):
705 db = self.connect_db()
707 db[1].execute('drop table in_status')
711 def new_in_status(self):
712 db = self.connect_db()
715 CREATE TABLE in_status \
717 ts2avi TINYINT DEFAULT 0,\
718 terec TINYINT DEFAULT 0,\
719 bscsrec TINYINT DEFAULT 0,\
720 b252ts TINYINT DEFAULT 0,\
721 installed TINYINT DEFAULT 0,\
724 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0,0)")
725 except Exception, inst:
726 recdblist.Commonlogex("Error", "new_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=800)
728 def select_all_in_status(self):
729 db = self.connect_db()
730 dbexe = db[1].execute("\
731 SELECT ts2avi,terec,bscsrec,b252ts \
737 dls = db[1].fetchall()
747 def select_version_in_status(self):
748 db = self.connect_db()
751 dbexe = db[1].execute("\
756 dls = db[1].fetchall()
760 version=int(str(r[0]))
764 def change_version_in_status(self,version):
765 db = self.connect_db()
767 UPDATE in_status SET version=%s",str(version))
769 def change_ts2avi_in_status(self,i):
774 db = self.connect_db()
776 UPDATE in_status SET ts2avi=ts2avi+%s",i)
778 def change_terec_in_status(self,i):
783 db = self.connect_db()
785 UPDATE in_status SET terec=terec+%s",i)
787 def change_bscsrec_in_status(self,i):
792 db = self.connect_db()
794 UPDATE in_status SET bscsrec=bscsrec+%s",i)
796 def change_b252ts_in_status(self,i):
801 db = self.connect_db()
803 UPDATE in_status SET b252ts=b252ts+%s",i)
805 def select_installed_in_status(self):
806 db = self.connect_db()
807 dbexe = db[1].execute("\
808 SELECT ts2avi,terec,bscsrec,b252ts,installed \
814 dls = db[1].fetchall()
820 def change_installed_in_status(self):
824 db = self.connect_db()
826 UPDATE in_status SET installed=1")
828 def change_chscaned_in_status(self):
832 db = self.connect_db()
834 UPDATE in_status SET installed=2")
836 def new_epg_timeline(self, bctype):
837 db = self.connect_db()
840 DELETE FROM epg_timeline \
841 WHERE bctype = %s", \
843 except Exception, inst:
844 recdblist.Commonlogex("Error", "new_epg_timeline (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=800)
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.chtxt=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.chtxt=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()
1114 self.drop_in_settings()
1115 self.new_in_settings()
1117 ALTER TABLE timeline ADD epgtitle VARCHAR(100),\
1118 ADD epgbtime DATETIME,\
1119 ADD epgetime DATETIME,\
1120 ADD epgduplicate TINYINT DEFAULT 0,\
1121 ADD epgchange TINYINT DEFAULT 0")
1123 ALTER TABLE in_status ADD version TINYINT")
1125 self.change_version_in_status("93")
1126 def update_db_93to94(self):
1127 db = self.connect_db()
1128 self.drop_in_settings()
1129 self.new_in_settings()
1131 ALTER TABLE timeline ADD counter TINYINT DEFAULT -1")
1133 self.change_version_in_status("94")
1134 def update_db_94to95(self):
1135 db = self.connect_db()
1136 self.drop_in_settings()
1137 self.new_in_settings()
1139 ALTER TABLE timeline ADD epgexp VARCHAR(200)")
1141 self.change_version_in_status("95")
1142 def update_db_95to96(self):
1143 db = self.connect_db()
1144 self.drop_in_settings()
1145 self.new_in_settings()
1147 self.change_version_in_status("96")
1148 def update_db_96to98(self):
1149 db = self.connect_db()
1150 self.drop_in_settings()
1151 self.new_in_settings()
1153 self.change_version_in_status("98")
1154 def update_db_98to99(self):
1155 db = self.connect_db()
1156 self.drop_in_settings()
1157 self.new_in_settings()
1159 ALTER TABLE epg_ch DEL ontv")
1161 ALTER TABLE in_auto_jbk_key ADD auto TINYINT DEFAULT 0")
1163 ALTER TABLE in_auto_jbk_key ADD opt VARCHAR(20)")
1165 self.change_installed_in_status()#チャンネルスキャンをさせる
1166 self.change_version_in_status("99")