3 # Rec10 TS Recording Tools
4 # Copyright (C) 2009-2011 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(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
32 recdblist.addCommonlogEX("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 epgcategory VARCHAR(100),\
54 counter TINYINT DEFAULT -1,\
55 UNIQUE uni (type,chtxt,title,btime,deltaday)\
57 except Exception, inst:
58 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
59 recdblist.addCommonlogEX("Error", "init (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
61 self.new_epg_timeline("")
62 self.new_in_timeline_log()
63 self.new_in_auto_bayes_key()
64 self.new_in_auto_jbk_key()
66 self.new_in_settings()
67 self.new_auto_timeline_bayes()
68 self.new_auto_timeline_keyword()
73 con = MySQLdb.connect(db=self.dbname, host=self.dbhost, port=self.dbport, user=self.dbusr, passwd=self.dbpasswd, charset="utf8")
75 cur.execute('set names utf8;')
77 def close_db(self, db):
81 db = self.connect_db()
83 db[1].execute('drop table epg_ch')
84 except Exception, inst:
85 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
86 recdblist.addCommonlogEX("Error", "new_epg_ch drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
93 chtxt VARCHAR(20) PRIMARY KEY,\
99 visible TINYINT DEFAULT 1,\
105 logoupdate DATETIME,\
106 logostatus TINYINT DEFAULT 2,\
109 except Exception, inst:
110 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
111 recdblist.addCommonlogEX("Error", "new_epg_ch (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
114 def add_epg_ch(self, bctype, chtxt, ch, csch, updatetime,logoupdate,scanupdate):
115 db = self.connect_db()
117 INSERT INTO epg_ch (bctype,chtxt,ch,csch,chname,updatetime,status,visible,logoupdate,scanupdate)\
118 VALUES (%s,%s,%s,%s,"",%s,%s,%s,%s,%s)', \
119 (bctype, chtxt, ch, csch, updatetime, "1","1",logoupdate,scanupdate))
121 def delete_all_epg_ch(self):
122 db = self.connect_db()
126 def select_by_chtxt_epg_ch(self, chtxt):
127 db = self.connect_db()
128 dbexe = db[1].execute("\
129 SELECT bctype,chtxt,ch,csch,updatetime,chname,status,visible,logoupdate,scanupdate \
131 WHERE chtxt LIKE %s", \
136 dls = db[1].fetchall()
140 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
141 r[8] = r[8].strftime("%Y-%m-%d %H:%M:%S")
142 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
145 def select_by_bctype_epg_ch(self, bctype):
146 db = self.connect_db()
147 dbexe = db[1].execute("\
148 SELECT bctype,chtxt,ch,csch,updatetime,status,chname,status,visible,logoupdate,scanupdate \
150 WHERE bctype = %s", \
155 dls = db[1].fetchall()
158 #recdblist.printutf8(dl)
160 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
161 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
162 r[10] = r[10].strftime("%Y-%m-%d %H:%M:%S")
166 def select_by_ch_epg_ch(self, ch):
167 db = self.connect_db()
168 dbexe = db[1].execute("\
170 bctype,chtxt,ch,csch,updatetime,chname,status,visible,logoupdate,scanupdate \
177 dls = db[1].fetchall()
181 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
182 r[8] = r[8].strftime("%Y-%m-%d %H:%M:%S")
183 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
186 def select_by_csch_epg_ch(self, csch):
187 db = self.connect_db()
188 dbexe = db[1].execute("\
190 bctype,chtxt,ch,csch,updatetime,chname,status,visible,logoupdate,scanupdate \
197 dls = db[1].fetchall()
201 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
202 r[8] = r[8].strftime("%Y-%m-%d %H:%M:%S")
203 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
206 def select_all_epg_ch(self):
207 db = self.connect_db()
208 dbexe = db[1].execute("\
209 SELECT bctype,chtxt,ch,csch,updatetime,chname,status,visible,logoupdate,scanupdate \
215 dls = db[1].fetchall()
219 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
220 r[8] = r[8].strftime("%Y-%m-%d %H:%M:%S")
221 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
224 def change_visible_epg_ch(self,chtxt,visible):
225 db = self.connect_db()
227 UPDATE epg_ch SET visible=%s WHERE chtxt=%s",(visible,chtxt))
229 def change_logodata_epg_ch(self,chtxt,logonum,logodata):
230 db = self.connect_db()
232 UPDATE epg_ch SET logo"+str(logonum)+"=%s WHERE chtxt=%s",(logodata,chtxt))
234 def set_new_status(self,dhour):
235 db = self.connect_db()
236 dbexe = db[1].execute("UPDATE epg_ch \
240 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
245 def select_get_update_epg_ch(self, dhour):
246 db = self.connect_db()
247 dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
250 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
256 ORDER BY status DESC")
258 #recdblist.printutf8(dbexe)
260 ret = db[1].fetchall()
263 def select_get_updatelogo_epg_ch(self, dhour):
264 db = self.connect_db()
265 dbexe = db[1].execute("SELECT bctype,chtxt,logostatus FROM epg_ch \
268 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
274 ORDER BY status DESC")
276 #recdblist.printutf8(dbexe)
278 ret = db[1].fetchall()
281 def update_by_bctype_epg_ch(self, bctype):
282 db = self.connect_db()
288 WHERE bctype = %s", (bctype,))
291 def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
292 db = self.connect_db()
298 WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
300 def update_chname_by_chtxt_epg_ch(self,chtxt,chname):
301 db = self.connect_db()
306 WHERE chtxt = %s", (chname,chtxt))
308 def update_status_by_bctype_epg_ch(self, bctype, status):
309 db = self.connect_db()
315 WHERE bctype = %s", \
319 def update_status_by_bctype_epg_ch(self, bctype, status):
320 db = self.connect_db()
326 WHERE bctype = %s", \
330 def update_logostatus_by_bctype_epg_ch(self,bctype,logostatus):
331 db = self.connect_db()
337 WHERE bctype = %s", \
338 (logostatus, bctype)\
341 def add_auto_proc_tmp(self,type,title,chtxt):
342 db = self.connect_db()
344 INSERT IGNORE into auto_proc_tmp \
346 values (%s,%s,%s)',(type,title,chtxt))
349 def new_auto_proc_tmp(self):
350 db = self.connect_db()
352 db[1].execute('drop table auto_proc_tmp')
353 except Exception, inst:
354 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
355 recdblist.addCommonlogEX("Error", "new_auto_proc_tmp drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
359 CREATE TABLE auto_proc_tmp \
362 title VARCHAR(100) PRIMARY KEY,\
364 UNIQUE unibayeskey(title)\
366 except Exception, inst:
367 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
368 recdblist.addCommonlogEX("Error", "new_auto_proc_tmp (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
371 def update_auto_proc(self):
372 db = self.connect_db()
374 db[1].execute('INSERT INTO auto_proc SELECT * FROM auto_proc_tmp')
375 except Exception, inst:
376 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
377 recdblist.addCommonlogEX("Error", "update_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
380 def new_auto_proc(self):
381 db = self.connect_db()
383 db[1].execute('drop table auto_proc')
384 except Exception, inst:
385 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
386 recdblist.addCommonlogEX("Error", "new_auto_proc drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
390 CREATE TABLE auto_proc \
393 title VARCHAR(100) PRIMARY KEY,\
395 UNIQUE unibayeskey(title)\
397 except Exception, inst:
398 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
399 recdblist.addCommonlogEX("Error", "new_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
402 def add_auto_proc(self,type,title,chtxt):
403 db = self.connect_db()
405 INSERT IGNORE into auto_proc \
407 values (%s,%s,%s)',(type,title,chtxt))
410 def drop_in_settings(self):
411 db = self.connect_db()
413 db[1].execute('drop table in_settings')
414 except Exception, inst:
415 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
416 recdblist.addCommonlogEX("Error", "drop_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
418 def new_in_settings(self):
419 db = self.connect_db()
422 CREATE TABLE in_settings \
426 auto_opt VARCHAR(20),\
427 auto_del_tmp TINYINT\
429 db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"H\",1)")
430 except Exception, inst:
431 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
432 recdblist.addCommonlogEX("Error", "new_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
435 def select_all_in_settings(self):
436 db = self.connect_db()
437 dbexe = db[1].execute("\
438 SELECT auto_jbk,auto_bayes,auto_del_tmp,auto_opt \
444 dls = db[1].fetchall()
454 def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
455 db = self.connect_db()
457 INSERT IGNORE into in_timeline_log \
458 (chtxt,title,btime,etime,opt,exp,longexp,category) \
459 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
460 ( chtxt, title, btime, etime, opt,exp,longexp,category))
463 def del_in_timeline_log(self, title="", chtxt="", btime=""):
467 db = self.connect_db()
469 DELETE FROM in_timeline_log \
470 WHERE title = %s AND chtxt = %s AND btime = %s", \
471 (title, chtxt, btime))
474 def new_in_timeline_log(self):
475 db = self.connect_db()
478 CREATE TABLE in_timeline_log \
480 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
488 category VARCHAR(100),\
489 UNIQUE uni (chtxt,title,btime,category)\
491 except Exception, inst:
492 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
493 recdblist.addCommonlogEX("Error", "new_in_timeline_log (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
496 def select_chtxt_by_title_timeline_log(self,title):
497 db = self.connect_db()
498 dbexe = db[1].execute("\
500 FROM in_timeline_log \
501 WHERE title LIKE \"%"+title+"%\"\
503 ORDER by sum(1) DESC limit 1")
504 retdb=db[1].fetchall()
511 def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt="" ,counter=-1):
512 db = self.connect_db()
514 INSERT IGNORE into timeline \
515 (type,chtxt,title,btime,etime,deltatime,deltaday,opt,counter) \
516 values (%s,%s,%s,%s,%s,%s,%s,%s,%s)', \
517 (type, chtxt, title, btime, etime, deltatime, deltaday, opt ,counter))
520 def update_epg_timeline(self,type,chtxt,title,btime,epgbtime,epgetime,epgtitle,epgexp,epgcategory):
521 db = self.connect_db()
524 SET epgbtime=%s,epgetime=%s,epgtitle=%s,epgexp=%s,epgcategory=%s \
525 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
526 (epgbtime,epgetime,epgtitle,epgexp,epgcategory,type, chtxt, title, btime))
529 def update_status_change_timeline(self,type,chtxt,title,btime,epgchange):
530 db = self.connect_db()
534 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
535 (epgchange , type, chtxt, title, btime))
538 def update_status_dup_timeline(self,type,chtxt,title,btime,epgduplicate):
539 db = self.connect_db()
542 SET epgduplicate =%s \
543 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
544 (epgduplicate , type, chtxt, title, btime))
547 def del_timeline(self, type="", title="", chtxt="", btime=""):
551 db = self.connect_db()
553 DELETE FROM timeline \
554 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
555 (type, title, chtxt, btime))
558 def select_all_timeline(self):
559 db = self.connect_db()
561 dbr = db[1].execute("\
562 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
564 dbl = db[1].fetchall()
567 recdata = self.getdic_timeline(dbl)
569 def select_bytime_timeline(self, dminutes):
570 db = self.connect_db()
572 #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 )")
573 dbr = db[1].execute("SELECT \
574 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
576 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
577 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
578 dbl = db[1].fetchall()
580 #recdblist.printutf8(dbl)
582 recdatum = self.getdic_timeline(dbl)
584 def select_by_name_time_timeline(self,title,btime,btime2):
585 db = self.connect_db()
587 #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 )")
588 dbr = db[1].execute("SELECT \
589 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
591 WHERE btime > %s AND \
592 btime < %s AND title = %s",(btime,btime2,title))
593 dbl = db[1].fetchall()
595 #recdblist.printutf8(dbl)
597 recdatum = self.getdic_timeline(dbl)
599 def select_bytime_all_timeline(self,btime,etime):
600 db = self.connect_db()
602 #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 )")
603 dbr = db[1].execute("SELECT \
604 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
606 WHERE btime >= %s AND \
607 etime <= %s",(btime,etime))
608 dbl = db[1].fetchall()
610 #recdblist.printutf8(dbl)
612 recdatum = self.getdic_timeline(dbl)
614 def select_byepgtime_all_timeline(self,epgbtime,epgetime):
615 db = self.connect_db()
617 #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 )")
618 dbr = db[1].execute("SELECT \
619 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
621 WHERE epgbtime >= %s AND \
622 epgetime <= %s",(epgbtime,epgetime))
623 dbl = db[1].fetchall()
625 #recdblist.printutf8(dbl)
627 recdatum=self.getdic_timeline(dbl)
629 def select_byepgtime_over_timeline(self,epgbtime,epgetime):
630 db = self.connect_db()
632 #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 )")
633 dbr = db[1].execute("SELECT \
634 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
636 WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"\
637 ,(epgbtime,epgetime))
638 dbl = db[1].fetchall()
640 #recdblist.printutf8(dbl)
642 recdatum=self.getdic_timeline(dbl)
644 def count_schedule_timeline(self, btime, etime):
647 return [te num,bs/cs num]
649 db = self.connect_db()
650 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
651 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
654 db[1].execute(dbexe, (btime, etime,btime,etime))
656 for typet, bctypet, chtxtt, titlet in dbl:
657 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
658 if bctypet.find("cs") > -1:
660 elif bctypet.find("bs") > -1:
662 elif bctypet.find("te") > -1:
666 def search_schedule_timeline(self,btime,etime):
669 return [(type,bctype,chtxt,title,btime,etime)]
671 db = self.connect_db()
672 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"
673 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
675 db[1].execute(dbexe, (btime, etime,btime,etime))
677 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
678 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
679 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
682 def count_epgschedule_timeline(self, epgbtime, epgetime):
685 return [te num,bs/cs num]
687 db = self.connect_db()
688 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
689 dbexe = dbexe + " WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"
692 db[1].execute(dbexe, (epgbtime, epgetime))
694 for typet, bctypet, chtxtt, titlet in dbl:
695 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
696 if bctypet.find("cs") > -1:
698 elif bctypet.find("bs") > -1:
700 elif bctypet.find("te") > -1:
704 def search_epgschedule_timeline(self,epgbtime,epgetime):
707 return [(type,bctype,chtxt,title,btime,etime)]
709 db = self.connect_db()
710 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"
711 dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )"
713 db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime))
715 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
716 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
717 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
720 def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
721 db = self.connect_db()
723 #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 )")
724 dbr = db[1].execute("SELECT \
725 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
727 WHERE btime > %s AND \
729 AND chtxt=%s ",(btime,etime,chtxt))
730 dbl = db[1].fetchall()
732 #recdblist.printutf8(dbl)
734 recdatum = self.getdic_timeline(dbl)
736 def getdic_timeline(self,timelinelists):
738 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt ,epgbtimet , epgetimet ,epgtitlet ,epgduplicatet ,epgchanget ,countert in timelinelists:
743 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
744 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
749 ret['epgbtime'] = epgbtimet.strftime("%Y-%m-%d %H:%M:%S")
750 ret['epgetime'] = epgetimet.strftime("%Y-%m-%d %H:%M:%S")
752 ret['epgbtime'] = "2010-01-01 00:00:00"
753 ret['epgetime'] = "2010-01-01 00:00:00"
754 ret['epgtitle'] = epgtitlet
755 ret['epgduplicate'] = epgduplicatet
756 ret['epgchange'] = epgchanget
757 if deltatime == None or deltatime == "":
759 if deltaday == None or deltaday == "":
761 if typet == recdblist.REC_KEYWORD:
762 ret['deltatime'] = deltatime
763 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
764 ret['deltatime'] = deltatime
765 ret['deltaday'] = deltaday
767 ret['counter'] = int(countert)
772 def delete_old_timeline(self, dhour):
773 db = self.connect_db()
775 DELETE FROM timeline \
777 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
780 def new_in_auto_jbk_key(self):
781 db = self.connect_db()
784 CREATE TABLE in_auto_jbk_key \
786 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
787 keyword VARCHAR(200),\
788 auto TINYINT DEFAULT 0,\
790 UNIQUE unijbk (keyword)\
792 except Exception, inst:
793 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
794 recdblist.addCommonlogEX("Error", "new_in_auto_jbk_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
796 def add_in_auto_jbk_key(self,key):
797 db = self.connect_db()
799 INSERT IGNORE into in_auto_jbk_key \
805 def select_all_in_auto_jbk_key(self):
806 db = self.connect_db()
807 dbexe = db[1].execute("\
808 SELECT keyword,auto,opt \
809 FROM in_auto_jbk_key \
813 ret = db[1].fetchall()
816 def drop_in_status(self):
817 db = self.connect_db()
819 db[1].execute('drop table in_status')
820 except Exception, inst:
821 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
822 recdblist.addCommonlogEX("Error", "drop_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
824 def new_in_status(self):
825 db = self.connect_db()
828 CREATE TABLE in_status \
830 ts2avi TINYINT DEFAULT 0,\
831 terec TINYINT DEFAULT 0,\
832 bscsrec TINYINT DEFAULT 0,\
833 b252ts TINYINT DEFAULT 0,\
834 installed TINYINT DEFAULT 0,\
837 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0,0)")
838 except Exception, inst:
839 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
840 recdblist.addCommonlogEX("Error", "new_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
842 def select_all_in_status(self):
843 db = self.connect_db()
844 dbexe = db[1].execute("\
845 SELECT ts2avi,terec,bscsrec,b252ts \
851 dls = db[1].fetchall()
861 def select_version_in_status(self):
862 db = self.connect_db()
865 dbexe = db[1].execute("\
870 dls = db[1].fetchall()
874 version=int(str(r[0]))
875 except Exception, inst:
876 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
877 recdblist.addCommonlogEX("Error", "select_version_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
879 def change_version_in_status(self,version):
880 db = self.connect_db()
882 UPDATE in_status SET version=%s",str(version))
884 def change_ts2avi_in_status(self,i):
889 db = self.connect_db()
891 UPDATE in_status SET ts2avi=ts2avi+%s",i)
893 def change_terec_in_status(self,i):
898 db = self.connect_db()
900 UPDATE in_status SET terec=terec+%s",i)
902 def change_bscsrec_in_status(self,i):
907 db = self.connect_db()
909 UPDATE in_status SET bscsrec=bscsrec+%s",i)
911 def change_b252ts_in_status(self,i):
916 db = self.connect_db()
918 UPDATE in_status SET b252ts=b252ts+%s",i)
920 def select_installed_in_status(self):
921 db = self.connect_db()
922 dbexe = db[1].execute("\
923 SELECT ts2avi,terec,bscsrec,b252ts,installed \
929 dls = db[1].fetchall()
935 def change_chscaned_in_status(self):
939 db = self.connect_db()
941 UPDATE in_status SET installed=2")
943 def change_installed_in_status(self,num=1):
947 db = self.connect_db()
949 UPDATE in_status SET installed=%s",(num,))
951 def new_epg_timeline(self, bctype):
952 db = self.connect_db()
955 DELETE FROM epg_timeline \
956 WHERE bctype = %s", \
958 except Exception, inst:
959 if not ((type(inst)==MySQLdb.ProgrammingError and (inst[0]==1007 or inst[0]==1146))or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
960 recdblist.addCommonlogEX("Error", "new_epg_timeline delete (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
963 CREATE TABLE epg_timeline \
966 channel VARCHAR(100) NOT NULL,\
972 category VARCHAR(100),\
973 UNIQUE unitv(bctype,channel,start,stop,title)\
975 except Exception, inst:
976 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
977 recdblist.addCommonlogEX("Error", "new_epg_timeline (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
980 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
981 db = self.connect_db()
983 INSERT IGNORE INTO epg_timeline \
984 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
985 (bctype, channel, start, stop, title, desc,longdesc,category))
988 def add_multi_epg_timeline(self, tvlists):
990 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
992 db = self.connect_db()
994 INSERT IGNORE INTO epg_timeline \
995 (bctype,channel,start,stop,title,exp,longexp,category) \
996 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
999 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
1000 db = self.connect_db()
1003 channel,title,start,stop,exp,longexp,category \
1010 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
1013 retall = db[1].fetchall()
1016 def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
1017 db = self.connect_db()
1020 epg_ch.chtxt,title,start,stop,exp,longexp,category \
1023 WHERE epg_ch.chtxt=epg_timeline.channel \
1032 ( title LIKE \'%%"+keyword+"%%\' ) \
1034 ( exp LIKE \'%%"+keyword+"%%\' ) \
1036 ( longexp LIKE \'%%"+keyword+"%%\' ) \
1038 dbcmd = db[1].execute(dbexe,(btime, etime))
1041 retall = db[1].fetchall()
1044 def new_in_auto_bayes_key(self):
1045 db = self.connect_db()
1047 db[1].execute('CREATE TABLE in_auto_bayes_key \
1049 keychar VARCHAR(10),\
1051 ratio_rec DECIMAL(32,14),\
1052 ratio_all DECIMAL(32,14),\
1053 UNIQUE unibayeskey(keychar,chtxt)\
1055 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
1056 except Exception, inst:
1057 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1058 recdblist.addCommonlogEX("Error", "new_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1061 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
1062 db = self.connect_db()
1063 ratio_rec=str(ratio_rec)
1064 ratio_all=str(ratio_all)
1066 INSERT IGNORE INTO in_auto_bayes_key \
1067 (keychar,chtxt,ratio_rec,ratio_all) \
1068 values (%s,%s,%s,%s)',\
1069 (key,chtxt,ratio_rec,ratio_all))
1071 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
1072 db = self.connect_db()
1073 add_rec_num=str(add_rec_num)
1074 add_all_num=str(add_all_num)
1076 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",\
1077 (add_rec_num,add_all_num,chtxt))
1079 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
1082 db = self.connect_db()
1084 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)\
1087 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1088 beforenum=str(beforenum)
1090 db = self.connect_db()
1092 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)\
1095 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1096 db = self.connect_db()
1097 beforenumf=beforenum
1098 beforenum=str(beforenum)
1099 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1100 VALUES (%s,%s,%s,%s)\
1101 ON DUPLICATE KEY UPDATE \
1102 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
1104 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
1106 list={key:addnum}のリスト
1108 beforenumf=beforenum
1109 beforenum=str(beforenum)
1110 db = self.connect_db()
1111 for i,j in list.items():
1112 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
1114 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1115 VALUES (%s,%s,%s,%s)\
1116 ON DUPLICATE KEY UPDATE \
1117 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
1118 except Exception, inst:
1119 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1120 recdblist.addCommonlogEX("Error", "change_multi_ratio_all_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1122 def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1123 beforenum=str(beforenum)
1125 db = self.connect_db()
1127 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)\
1130 def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1131 db = self.connect_db()
1132 beforenumf=beforenum
1133 beforenum=str(beforenum)
1134 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1135 VALUES (%s,%s,%s,%s)\
1136 ON DUPLICATE KEY UPDATE \
1137 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
1139 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
1140 beforenumf=beforenum
1141 beforenum=str(beforenum)
1142 db = self.connect_db()
1143 for i,j in list.items():
1144 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
1146 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1147 VALUES (%s,%s,%s,%s)\
1148 ON DUPLICATE KEY UPDATE \
1149 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
1150 except Exception, inst:
1151 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1152 recdblist.addCommonlogEX("Error", "change_multi_ratio_rec_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1154 def select_by_key_in_auto_bayes_key(self,key,chtxt):
1155 db = self.connect_db()
1156 dbexe = db[1].execute("\
1157 SELECT keychar,chtxt,ratio_rec,ratio_all \
1158 FROM in_auto_bayes_key \
1159 WHERE keychar = %s AND chtxt = %s", \
1163 dls = db[1].fetchall()
1169 def new_auto_timeline_keyword(self):
1170 db = self.connect_db()
1173 CREATE TABLE auto_timeline_keyword \
1175 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1177 title VARCHAR(100),\
1180 UNIQUE uni (chtxt,title,btime,etime)\
1182 except Exception, inst:
1183 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1184 recdblist.addCommonlogEX("Error", "new_auto_timeline_keyword (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1185 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
1186 db = self.connect_db()
1188 INSERT IGNORE into auto_timeline_keyword \
1189 (chtxt,title,btime,etime) \
1190 values (%s,%s,%s,%s)', \
1191 (chtxt, title, btime, etime))
1194 def delete_old_auto_timeline_keyword(self, dhour):
1195 db = self.connect_db()
1197 DELETE FROM auto_timeline_keyword \
1199 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1201 def new_auto_timeline_bayes(self):
1202 db = self.connect_db()
1205 CREATE TABLE auto_timeline_bayes \
1207 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1209 title VARCHAR(100),\
1213 UNIQUE uni (chtxt,title,btime,etime)\
1215 except Exception, inst:
1216 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1217 recdblist.addCommonlogEX("Error", "new_auto_timeline_bayes (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1218 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
1219 db = self.connect_db()
1221 INSERT IGNORE into auto_timeline_bayes \
1222 (chtxt,title,btime,etime,point) \
1223 values (%s,%s,%s,%s,%s)', \
1224 (chtxt, title, btime, etime,point))
1226 def delete_old_auto_timeline_bayes(self, dhour):
1227 db = self.connect_db()
1229 DELETE FROM auto_timeline_bayes \
1231 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1233 def update_db_to93(self):
1234 db = self.connect_db()
1235 self.drop_in_settings()
1236 self.new_in_settings()
1238 ALTER TABLE timeline ADD epgtitle VARCHAR(100),\
1239 ADD epgbtime DATETIME,\
1240 ADD epgetime DATETIME,\
1241 ADD epgduplicate TINYINT DEFAULT 0,\
1242 ADD epgchange TINYINT DEFAULT 0")
1244 ALTER TABLE in_status ADD version TINYINT")
1246 self.change_version_in_status("93")
1247 def update_db_93to94(self):
1248 db = self.connect_db()
1249 self.drop_in_settings()
1250 self.new_in_settings()
1252 ALTER TABLE timeline ADD counter TINYINT DEFAULT -1")
1254 self.change_version_in_status("94")
1255 def update_db_94to95(self):
1256 db = self.connect_db()
1257 self.drop_in_settings()
1258 self.new_in_settings()
1260 ALTER TABLE timeline ADD epgexp VARCHAR(200)")
1262 self.change_version_in_status("95")
1263 def update_db_95to96(self):
1264 db = self.connect_db()
1265 self.drop_in_settings()
1266 self.new_in_settings()
1268 self.change_version_in_status("96")
1269 def update_db_96to98(self):
1270 db = self.connect_db()
1271 self.drop_in_settings()
1272 self.new_in_settings()
1274 self.change_version_in_status("98")
1275 def update_db_98to100(self):
1276 ###ここで前のepg_chをバックアップしてchtxtの変換をする必要がある。
1277 self.drop_in_settings()
1278 self.new_in_settings()
1279 db = self.connect_db()
1281 UPDATE timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt SET timeline.chtxt=CONCAT(CONCAT(epg_ch.ch,'_'),epg_ch.csch) WHERE NOT (substring(epg_ch.bctype,1,2) = 'bs' OR substring(epg_ch.bctype,1,2) = 'cs')")
1283 UPDATE timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt SET timeline.chtxt=CONCAT('BS_',epg_ch.ch) WHERE substring(epg_ch.bctype,1,2) = 'bs'")
1285 UPDATE timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt SET timeline.chtxt=CONCAT('CS_',epg_ch.csch) WHERE substring(epg_ch.bctype,1,2) = 'cs'")
1288 ALTER TABLE epg_ch DROP ontv")
1292 ALTER TABLE epg_ch ADD logo0 BLOB,\
1300 ALTER TABLE in_auto_jbk_key ADD auto TINYINT DEFAULT 0")
1302 ALTER TABLE in_auto_jbk_key ADD opt VARCHAR(20) DEFAULT \"\"")
1304 self.change_installed_in_status(1)#チャンネルスキャンをさせる
1305 self.change_version_in_status("100")
1306 def update_db_100to101(self):
1307 self.drop_in_settings()
1308 self.new_in_settings()
1310 db = self.connect_db()
1313 ALTER TABLE timeline ADD epgcategory VARCHAR(100)\
1318 self.change_installed_in_status(1)
1319 self.change_version_in_status("101")
1320 #self.change_installed_in_status()#チャンネルスキャンをさせる