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 COLLATE utf8_general_ci;")
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")
79 cur.execute('set names utf8;')
81 def close_db(self, db):
85 db = self.connect_db()
87 db[1].execute('drop table epg_ch')
88 except Exception, inst:
89 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
90 recdblist.addCommonlogEX("Error", "new_epg_ch drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
97 chtxt VARCHAR(20) PRIMARY KEY,\
100 chname VARCHAR(100),\
101 updatetime DATETIME,\
103 visible TINYINT DEFAULT 1,\
110 logoupdate DATETIME,\
111 logostatus TINYINT DEFAULT 2,\
114 except Exception, inst:
115 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
116 recdblist.addCommonlogEX("Error", "new_epg_ch (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
119 def add_epg_ch(self, bctype, chtxt, ch, csch, updatetime,logoupdate,scanupdate):
120 db = self.connect_db()
122 INSERT INTO epg_ch (bctype,chtxt,ch,csch,chname,updatetime,status,visible,logoupdate,scanupdate)\
123 VALUES (%s,%s,%s,%s,"",%s,%s,%s,%s,%s)', \
124 (bctype, chtxt, ch, csch, updatetime, "1","1",logoupdate,scanupdate))
126 def delete_all_epg_ch(self):
127 db = self.connect_db()
131 def select_by_chtxt_epg_ch(self, chtxt):
132 db = self.connect_db()
133 dbexe = db[1].execute("\
134 SELECT bctype,chtxt,ch,csch,updatetime,chname,status,visible,logoupdate,scanupdate \
136 WHERE chtxt LIKE %s", \
141 dls = db[1].fetchall()
145 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
147 r[8] = r[8].strftime("%Y-%m-%d %H:%M:%S")
149 r[8]="2011-04-01 00:00:00"
150 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
153 def select_by_bctype_epg_ch(self, bctype):
154 db = self.connect_db()
155 dbexe = db[1].execute("\
156 SELECT bctype,chtxt,ch,csch,updatetime,status,chname,status,visible,logoupdate,scanupdate \
158 WHERE bctype = %s", \
163 dls = db[1].fetchall()
166 #recdblist.printutf8(dl)
168 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
170 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
172 r[9]="2011-04-01 00:00:00"
173 r[10] = r[10].strftime("%Y-%m-%d %H:%M:%S")
177 ret.insert(0,ret[random.randint(0,len(ret)-1)])
179 def select_by_ch_epg_ch(self, ch):
180 db = self.connect_db()
181 dbexe = db[1].execute("\
183 bctype,chtxt,ch,csch,updatetime,chname,status,visible,logoupdate,scanupdate \
190 dls = db[1].fetchall()
194 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
195 r[8] = r[8].strftime("%Y-%m-%d %H:%M:%S")
196 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
199 def select_by_csch_epg_ch(self, csch):
200 db = self.connect_db()
201 dbexe = db[1].execute("\
203 bctype,chtxt,ch,csch,updatetime,chname,status,visible,logoupdate,scanupdate \
210 dls = db[1].fetchall()
214 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
215 r[8] = r[8].strftime("%Y-%m-%d %H:%M:%S")
216 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
219 def select_all_epg_ch(self):
220 db = self.connect_db()
221 dbexe = db[1].execute("\
222 SELECT bctype,chtxt,ch,csch,updatetime,chname,status,visible,logoupdate,scanupdate \
228 dls = db[1].fetchall()
232 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
233 r[8] = r[8].strftime("%Y-%m-%d %H:%M:%S")
234 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
237 def change_visible_epg_ch(self,chtxt,visible):
238 db = self.connect_db()
240 UPDATE epg_ch SET visible=%s WHERE chtxt=%s",(visible,chtxt))
242 def change_logodata_epg_ch(self,chtxt,logonum,logodata):
243 db = self.connect_db()
245 UPDATE epg_ch SET logo"+str(logonum)+"=%s WHERE chtxt=%s",(logodata,chtxt))
247 def set_new_status(self,dhour):
248 db = self.connect_db()
249 dbexe = db[1].execute("UPDATE epg_ch \
253 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
258 def select_get_update_epg_ch(self, dhour):
259 db = self.connect_db()
260 dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
263 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
269 ORDER BY status DESC")
271 #recdblist.printutf8(dbexe)
273 ret = db[1].fetchall()
276 def select_get_updatelogo_epg_ch(self, dhour):
277 db = self.connect_db()
278 dbexe = db[1].execute("SELECT bctype,chtxt,logostatus FROM epg_ch \
281 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
287 ORDER BY status DESC")
289 #recdblist.printutf8(dbexe)
291 ret = db[1].fetchall()
294 def update_by_bctype_epg_ch(self, bctype):
295 db = self.connect_db()
301 WHERE bctype = %s", (bctype,))
304 def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
305 db = self.connect_db()
311 WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
313 def update_chname_by_chtxt_epg_ch(self,chtxt,chname):
314 db = self.connect_db()
319 WHERE chtxt = %s", (chname,chtxt))
321 def update_status_by_bctype_epg_ch(self, bctype, status):
322 db = self.connect_db()
328 WHERE bctype = %s", \
332 def update_logostatus_by_bctype_epg_ch(self,bctype,logostatus):
333 db = self.connect_db()
339 WHERE bctype = %s", \
340 (logostatus, bctype)\
343 def add_auto_proc_tmp(self,type,title,chtxt):
344 db = self.connect_db()
346 INSERT IGNORE into auto_proc_tmp \
348 values (%s,%s,%s)',(type,title,chtxt))
351 def new_auto_proc_tmp(self):
352 db = self.connect_db()
354 db[1].execute('drop table auto_proc_tmp')
355 except Exception, inst:
356 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
357 recdblist.addCommonlogEX("Error", "new_auto_proc_tmp drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
361 CREATE TABLE auto_proc_tmp \
364 title VARCHAR(100) PRIMARY KEY,\
366 UNIQUE unibayeskey(title)\
368 except Exception, inst:
369 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
370 recdblist.addCommonlogEX("Error", "new_auto_proc_tmp (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
373 def update_auto_proc(self):
374 db = self.connect_db()
376 db[1].execute('INSERT INTO auto_proc SELECT * FROM auto_proc_tmp')
377 except Exception, inst:
378 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
379 recdblist.addCommonlogEX("Error", "update_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
382 def new_auto_proc(self):
383 db = self.connect_db()
385 db[1].execute('drop table auto_proc')
386 except Exception, inst:
387 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
388 recdblist.addCommonlogEX("Error", "new_auto_proc drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
392 CREATE TABLE auto_proc \
395 title VARCHAR(100) PRIMARY KEY,\
397 UNIQUE unibayeskey(title)\
399 except Exception, inst:
400 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
401 recdblist.addCommonlogEX("Error", "new_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
404 def add_auto_proc(self,type,title,chtxt):
405 db = self.connect_db()
407 INSERT IGNORE into auto_proc \
409 values (%s,%s,%s)',(type,title,chtxt))
412 def drop_in_settings(self):
413 db = self.connect_db()
415 db[1].execute('drop table in_settings')
416 except Exception, inst:
417 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
418 recdblist.addCommonlogEX("Error", "drop_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
420 def new_in_settings(self):
421 db = self.connect_db()
424 CREATE TABLE in_settings \
428 auto_opt VARCHAR(20),\
429 auto_del_tmp TINYINT\
431 db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"H\",1)")
432 except Exception, inst:
433 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
434 recdblist.addCommonlogEX("Error", "new_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
437 def select_all_in_settings(self):
438 db = self.connect_db()
439 dbexe = db[1].execute("\
440 SELECT auto_jbk,auto_bayes,auto_del_tmp,auto_opt \
446 dls = db[1].fetchall()
456 def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
457 db = self.connect_db()
459 INSERT IGNORE into in_timeline_log \
460 (chtxt,title,btime,etime,opt,exp,longexp,category) \
461 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
462 ( chtxt, title, btime, etime, opt,exp,longexp,category))
465 def del_in_timeline_log(self, title="", chtxt="", btime=""):
469 db = self.connect_db()
471 DELETE FROM in_timeline_log \
472 WHERE title = %s AND chtxt = %s AND btime = %s", \
473 (title, chtxt, btime))
476 def new_in_timeline_log(self):
477 db = self.connect_db()
480 CREATE TABLE in_timeline_log \
482 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
490 category VARCHAR(100),\
491 UNIQUE uni (chtxt,title,btime,category)\
493 except Exception, inst:
494 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
495 recdblist.addCommonlogEX("Error", "new_in_timeline_log (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
498 def select_chtxt_by_title_timeline_log(self,title):
499 db = self.connect_db()
500 dbexe = db[1].execute("\
502 FROM in_timeline_log \
503 WHERE title LIKE \"%"+title+"%\"\
505 ORDER by sum(1) DESC limit 1")
506 retdb=db[1].fetchall()
513 def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt="" ,counter=-1):
514 db = self.connect_db()
516 INSERT IGNORE into timeline \
517 (type,chtxt,title,btime,etime,deltatime,deltaday,opt,counter) \
518 values (%s,%s,%s,%s,%s,%s,%s,%s,%s)', \
519 (type, chtxt, title, btime, etime, deltatime, deltaday, opt ,counter))
522 def update_epg_timeline(self,type,chtxt,title,btime,epgbtime,epgetime,epgtitle,epgexp,epgcategory):
523 db = self.connect_db()
526 SET epgbtime=%s,epgetime=%s,epgtitle=%s,epgexp=%s,epgcategory=%s \
527 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
528 (epgbtime,epgetime,epgtitle,epgexp,epgcategory,type, chtxt, title, btime))
531 def update_status_change_timeline(self,type,chtxt,title,btime,epgchange):
532 db = self.connect_db()
536 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
537 (epgchange , type, chtxt, title, btime))
540 def update_status_dup_timeline(self,type,chtxt,title,btime,epgduplicate):
541 db = self.connect_db()
544 SET epgduplicate =%s \
545 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
546 (epgduplicate , type, chtxt, title, btime))
549 def del_timeline(self, type="", title="", chtxt="", btime=""):
553 db = self.connect_db()
555 DELETE FROM timeline \
556 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
557 (type, title, chtxt, btime))
560 def select_all_timeline(self):
561 db = self.connect_db()
563 dbr = db[1].execute("\
564 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
566 dbl = db[1].fetchall()
569 recdata = self.getdic_timeline(dbl)
571 def select_bytime_timeline(self, dminutes):
572 db = self.connect_db()
574 #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 )")
575 dbr = db[1].execute("SELECT \
576 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
578 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
579 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
580 dbl = db[1].fetchall()
582 #recdblist.printutf8(dbl)
584 recdatum = self.getdic_timeline(dbl)
586 def select_by_name_time_timeline(self,title,btime,btime2):
587 db = self.connect_db()
589 #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 )")
590 dbr = db[1].execute("SELECT \
591 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
593 WHERE btime > %s AND \
594 btime < %s AND title = %s",(btime,btime2,title))
595 dbl = db[1].fetchall()
597 #recdblist.printutf8(dbl)
599 recdatum = self.getdic_timeline(dbl)
601 def select_bytime_all_timeline(self,btime,etime):
602 db = self.connect_db()
604 #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 )")
605 dbr = db[1].execute("SELECT \
606 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
608 WHERE btime >= %s AND \
609 etime <= %s",(btime,etime))
610 dbl = db[1].fetchall()
612 #recdblist.printutf8(dbl)
614 recdatum = self.getdic_timeline(dbl)
616 def select_byepgtime_all_timeline(self,epgbtime,epgetime):
617 db = self.connect_db()
619 #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 )")
620 dbr = db[1].execute("SELECT \
621 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
623 WHERE epgbtime >= %s AND \
624 epgetime <= %s",(epgbtime,epgetime))
625 dbl = db[1].fetchall()
627 #recdblist.printutf8(dbl)
629 recdatum=self.getdic_timeline(dbl)
631 def select_byepgtime_over_timeline(self,epgbtime,epgetime):
632 db = self.connect_db()
634 #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 )")
635 dbr = db[1].execute("SELECT \
636 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
638 WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"\
639 ,(epgbtime,epgetime))
640 dbl = db[1].fetchall()
642 #recdblist.printutf8(dbl)
644 recdatum=self.getdic_timeline(dbl)
646 def count_schedule_timeline(self, btime, etime):
649 return [te num,bs/cs num]
651 db = self.connect_db()
652 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
653 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
656 db[1].execute(dbexe, (btime, etime,btime,etime))
658 for typet, bctypet, chtxtt, titlet in dbl:
659 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
660 if bctypet.find("cs") > -1:
662 elif bctypet.find("bs") > -1:
664 elif bctypet.find("te") > -1:
668 def search_schedule_timeline(self,btime,etime):
671 return [(type,bctype,chtxt,title,btime,etime)]
673 db = self.connect_db()
674 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"
675 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
677 db[1].execute(dbexe, (btime, etime,btime,etime))
679 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
680 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
681 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
684 def count_epgschedule_timeline(self, epgbtime, epgetime):
687 return [te num,bs/cs num]
689 db = self.connect_db()
690 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
691 dbexe = dbexe + " WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"
694 db[1].execute(dbexe, (epgbtime, epgetime))
696 for typet, bctypet, chtxtt, titlet in dbl:
697 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
698 if bctypet.find("cs") > -1:
700 elif bctypet.find("bs") > -1:
702 elif bctypet.find("te") > -1:
706 def search_epgschedule_timeline(self,epgbtime,epgetime):
709 return [(type,bctype,chtxt,title,btime,etime)]
711 db = self.connect_db()
712 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"
713 dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )"
715 db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime))
717 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
718 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
719 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
722 def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
723 db = self.connect_db()
725 #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 )")
726 dbr = db[1].execute("SELECT \
727 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
729 WHERE btime > %s AND \
731 AND chtxt=%s ",(btime,etime,chtxt))
732 dbl = db[1].fetchall()
734 #recdblist.printutf8(dbl)
736 recdatum = self.getdic_timeline(dbl)
738 def getdic_timeline(self,timelinelists):
740 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt ,epgbtimet , epgetimet ,epgtitlet ,epgduplicatet ,epgchanget ,countert in timelinelists:
745 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
746 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
751 ret['epgbtime'] = epgbtimet.strftime("%Y-%m-%d %H:%M:%S")
752 ret['epgetime'] = epgetimet.strftime("%Y-%m-%d %H:%M:%S")
754 ret['epgbtime'] = "2010-01-01 00:00:00"
755 ret['epgetime'] = "2010-01-01 00:00:00"
756 ret['epgtitle'] = epgtitlet
757 ret['epgduplicate'] = epgduplicatet
758 ret['epgchange'] = epgchanget
759 if deltatime == None or deltatime == "":
761 if deltaday == None or deltaday == "":
763 if typet == recdblist.REC_KEYWORD:
764 ret['deltatime'] = deltatime
765 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
766 ret['deltatime'] = deltatime
767 ret['deltaday'] = deltaday
769 ret['counter'] = int(countert)
774 def delete_old_timeline(self, dhour):
775 db = self.connect_db()
777 DELETE FROM timeline \
779 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
782 def new_in_auto_jbk_key(self):
783 db = self.connect_db()
786 CREATE TABLE in_auto_jbk_key \
788 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
789 keyword VARCHAR(200),\
790 auto TINYINT DEFAULT 0,\
792 UNIQUE unijbk (keyword)\
794 except Exception, inst:
795 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
796 recdblist.addCommonlogEX("Error", "new_in_auto_jbk_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
798 def add_in_auto_jbk_key(self,key):
799 db = self.connect_db()
801 INSERT IGNORE into in_auto_jbk_key \
807 def select_all_in_auto_jbk_key(self):
808 db = self.connect_db()
809 dbexe = db[1].execute("\
810 SELECT keyword,auto,opt \
811 FROM in_auto_jbk_key \
815 ret = db[1].fetchall()
818 def drop_in_status(self):
819 db = self.connect_db()
821 db[1].execute('drop table in_status')
822 except Exception, inst:
823 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
824 recdblist.addCommonlogEX("Error", "drop_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
826 def new_in_status(self):
827 db = self.connect_db()
830 CREATE TABLE in_status \
832 ts2avi TINYINT DEFAULT 0,\
833 terec TINYINT DEFAULT 0,\
834 bscsrec TINYINT DEFAULT 0,\
835 b252ts TINYINT DEFAULT 0,\
836 installed TINYINT DEFAULT 0,\
839 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0,0)")
840 except Exception, inst:
841 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
842 recdblist.addCommonlogEX("Error", "new_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
844 def select_all_in_status(self):
845 db = self.connect_db()
846 dbexe = db[1].execute("\
847 SELECT ts2avi,terec,bscsrec,b252ts \
853 dls = db[1].fetchall()
863 def select_version_in_status(self):
864 db = self.connect_db()
867 dbexe = db[1].execute("\
872 dls = db[1].fetchall()
876 version=int(str(r[0]))
877 except Exception, inst:
878 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
879 recdblist.addCommonlogEX("Error", "select_version_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
881 def change_version_in_status(self,version):
882 db = self.connect_db()
884 UPDATE in_status SET version=%s",str(version))
886 def change_ts2avi_in_status(self,i):
891 db = self.connect_db()
893 UPDATE in_status SET ts2avi=ts2avi+%s",i)
895 def change_terec_in_status(self,i):
900 db = self.connect_db()
902 UPDATE in_status SET terec=terec+%s",i)
904 def change_bscsrec_in_status(self,i):
909 db = self.connect_db()
911 UPDATE in_status SET bscsrec=bscsrec+%s",i)
913 def change_b252ts_in_status(self,i):
918 db = self.connect_db()
920 UPDATE in_status SET b252ts=b252ts+%s",i)
922 def select_installed_in_status(self):
923 db = self.connect_db()
924 dbexe = db[1].execute("\
925 SELECT ts2avi,terec,bscsrec,b252ts,installed \
931 dls = db[1].fetchall()
937 def change_chscaned_in_status(self):
941 db = self.connect_db()
943 UPDATE in_status SET installed=2")
945 def change_installed_in_status(self,num=1):
949 db = self.connect_db()
951 UPDATE in_status SET installed=%s",(num,))
953 def new_epg_timeline(self, bctype):
954 db = self.connect_db()
957 DELETE FROM epg_timeline \
958 WHERE bctype = %s", \
960 except Exception, inst:
961 if not ((type(inst)==MySQLdb.ProgrammingError and (inst[0]==1007 or inst[0]==1146))or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
962 recdblist.addCommonlogEX("Error", "new_epg_timeline delete (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
965 CREATE TABLE epg_timeline \
968 channel VARCHAR(100) NOT NULL,\
974 category VARCHAR(100),\
975 UNIQUE unitv(bctype,channel,start,stop,title)\
977 except Exception, inst:
978 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
979 recdblist.addCommonlogEX("Error", "new_epg_timeline (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
982 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
983 db = self.connect_db()
985 INSERT IGNORE INTO epg_timeline \
986 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
987 (bctype, channel, start, stop, title, desc,longdesc,category))
990 def add_multi_epg_timeline(self, tvlists):
992 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
994 db = self.connect_db()
996 INSERT IGNORE INTO epg_timeline \
997 (bctype,channel,start,stop,title,exp,longexp,category) \
998 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
1001 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
1002 db = self.connect_db()
1005 channel,title,start,stop,exp,longexp,category \
1012 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
1015 retall = db[1].fetchall()
1018 def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
1019 db = self.connect_db()
1022 epg_ch.chtxt,title,start,stop,exp,longexp,category \
1025 WHERE epg_ch.chtxt=epg_timeline.channel \
1034 ( title LIKE \'%%"+keyword+"%%\' ) \
1036 ( exp LIKE \'%%"+keyword+"%%\' ) \
1038 ( longexp LIKE \'%%"+keyword+"%%\' ) \
1040 dbcmd = db[1].execute(dbexe,(btime, etime))
1043 retall = db[1].fetchall()
1046 def new_in_auto_bayes_key(self):
1047 db = self.connect_db()
1049 db[1].execute('CREATE TABLE in_auto_bayes_key \
1051 keychar VARCHAR(10),\
1053 ratio_rec DECIMAL(32,14),\
1054 ratio_all DECIMAL(32,14),\
1055 UNIQUE unibayeskey(keychar,chtxt)\
1057 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
1058 except Exception, inst:
1059 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1060 recdblist.addCommonlogEX("Error", "new_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1063 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
1064 db = self.connect_db()
1065 ratio_rec=str(ratio_rec)
1066 ratio_all=str(ratio_all)
1068 INSERT IGNORE INTO in_auto_bayes_key \
1069 (keychar,chtxt,ratio_rec,ratio_all) \
1070 values (%s,%s,%s,%s)',\
1071 (key,chtxt,ratio_rec,ratio_all))
1073 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
1074 db = self.connect_db()
1075 add_rec_num=str(add_rec_num)
1076 add_all_num=str(add_all_num)
1078 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",\
1079 (add_rec_num,add_all_num,chtxt))
1081 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
1084 db = self.connect_db()
1086 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)\
1089 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1090 beforenum=str(beforenum)
1092 db = self.connect_db()
1094 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)\
1097 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1098 db = self.connect_db()
1099 beforenumf=beforenum
1100 beforenum=str(beforenum)
1101 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1102 VALUES (%s,%s,%s,%s)\
1103 ON DUPLICATE KEY UPDATE \
1104 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
1106 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
1108 list={key:addnum}のリスト
1110 beforenumf=beforenum
1111 beforenum=str(beforenum)
1112 db = self.connect_db()
1113 for i,j in list.items():
1114 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
1116 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1117 VALUES (%s,%s,%s,%s)\
1118 ON DUPLICATE KEY UPDATE \
1119 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
1120 except Exception, inst:
1121 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1122 recdblist.addCommonlogEX("Error", "change_multi_ratio_all_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1124 def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1125 beforenum=str(beforenum)
1127 db = self.connect_db()
1129 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)\
1132 def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1133 db = self.connect_db()
1134 beforenumf=beforenum
1135 beforenum=str(beforenum)
1136 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1137 VALUES (%s,%s,%s,%s)\
1138 ON DUPLICATE KEY UPDATE \
1139 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
1141 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
1142 beforenumf=beforenum
1143 beforenum=str(beforenum)
1144 db = self.connect_db()
1145 for i,j in list.items():
1146 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
1148 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1149 VALUES (%s,%s,%s,%s)\
1150 ON DUPLICATE KEY UPDATE \
1151 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
1152 except Exception, inst:
1153 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1154 recdblist.addCommonlogEX("Error", "change_multi_ratio_rec_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1156 def select_by_key_in_auto_bayes_key(self,key,chtxt):
1157 db = self.connect_db()
1158 dbexe = db[1].execute("\
1159 SELECT keychar,chtxt,ratio_rec,ratio_all \
1160 FROM in_auto_bayes_key \
1161 WHERE keychar = %s AND chtxt = %s", \
1165 dls = db[1].fetchall()
1171 def new_auto_timeline_keyword(self):
1172 db = self.connect_db()
1175 CREATE TABLE auto_timeline_keyword \
1177 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1179 title VARCHAR(100),\
1182 UNIQUE uni (chtxt,title,btime,etime)\
1184 except Exception, inst:
1185 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1186 recdblist.addCommonlogEX("Error", "new_auto_timeline_keyword (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1187 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
1188 db = self.connect_db()
1190 INSERT IGNORE into auto_timeline_keyword \
1191 (chtxt,title,btime,etime) \
1192 values (%s,%s,%s,%s)', \
1193 (chtxt, title, btime, etime))
1196 def delete_old_auto_timeline_keyword(self, dhour):
1197 db = self.connect_db()
1199 DELETE FROM auto_timeline_keyword \
1201 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1203 def new_auto_timeline_bayes(self):
1204 db = self.connect_db()
1207 CREATE TABLE auto_timeline_bayes \
1209 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1211 title VARCHAR(100),\
1215 UNIQUE uni (chtxt,title,btime,etime)\
1217 except Exception, inst:
1218 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1219 recdblist.addCommonlogEX("Error", "new_auto_timeline_bayes (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1220 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
1221 db = self.connect_db()
1223 INSERT IGNORE into auto_timeline_bayes \
1224 (chtxt,title,btime,etime,point) \
1225 values (%s,%s,%s,%s,%s)', \
1226 (chtxt, title, btime, etime,point))
1228 def delete_old_auto_timeline_bayes(self, dhour):
1229 db = self.connect_db()
1231 DELETE FROM auto_timeline_bayes \
1233 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1235 def update_db_to93(self):
1236 db = self.connect_db()
1237 self.drop_in_settings()
1238 self.new_in_settings()
1240 ALTER TABLE timeline ADD epgtitle VARCHAR(100),\
1241 ADD epgbtime DATETIME,\
1242 ADD epgetime DATETIME,\
1243 ADD epgduplicate TINYINT DEFAULT 0,\
1244 ADD epgchange TINYINT DEFAULT 0")
1246 ALTER TABLE in_status ADD version TINYINT")
1248 self.change_version_in_status("93")
1249 def update_db_93to94(self):
1250 db = self.connect_db()
1251 self.drop_in_settings()
1252 self.new_in_settings()
1254 ALTER TABLE timeline ADD counter TINYINT DEFAULT -1")
1256 self.change_version_in_status("94")
1257 def update_db_94to95(self):
1258 db = self.connect_db()
1259 self.drop_in_settings()
1260 self.new_in_settings()
1262 ALTER TABLE timeline ADD epgexp VARCHAR(200)")
1264 self.change_version_in_status("95")
1265 def update_db_95to96(self):
1266 db = self.connect_db()
1267 self.drop_in_settings()
1268 self.new_in_settings()
1270 self.change_version_in_status("96")
1271 def update_db_96to98(self):
1272 db = self.connect_db()
1273 self.drop_in_settings()
1274 self.new_in_settings()
1276 self.change_version_in_status("98")
1277 def update_db_98to100(self):
1278 ###ここで前のepg_chをバックアップしてchtxtの変換をする必要がある。
1279 self.drop_in_settings()
1280 self.new_in_settings()
1281 db = self.connect_db()
1283 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')")
1285 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'")
1287 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'")
1290 ALTER TABLE epg_ch DROP ontv")
1294 ALTER TABLE epg_ch ADD logo0 BLOB,\
1302 ALTER TABLE in_auto_jbk_key ADD auto TINYINT DEFAULT 0")
1304 ALTER TABLE in_auto_jbk_key ADD opt VARCHAR(20) DEFAULT \"\"")
1306 self.change_installed_in_status(1)#チャンネルスキャンをさせる
1307 self.change_version_in_status("100")
1308 def update_db_100to101(self):
1309 self.drop_in_settings()
1310 self.new_in_settings()
1312 db = self.connect_db()
1315 ALTER TABLE timeline ADD epgcategory VARCHAR(100)\
1320 self.change_installed_in_status(1)
1321 self.change_version_in_status("101")
1322 #self.change_installed_in_status()#チャンネルスキャンをさせる