3 # Rec10 TS Recording Tools
4 # Copyright (C) 2009-2012 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,\
101 chname VARCHAR(100),\
102 updatetime DATETIME,\
104 visible TINYINT DEFAULT 1,\
111 logoupdate DATETIME,\
112 logostatus TINYINT DEFAULT 2,\
115 except Exception, inst:
116 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
117 recdblist.addCommonlogEX("Error", "new_epg_ch (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
120 def add_epg_ch(self, bctype, chtxt, ch, csch,tsid,updatetime,logoupdate,scanupdate):
121 db = self.connect_db()
123 INSERT INTO epg_ch (bctype,chtxt,ch,csch,tsid,chname,updatetime,status,visible,logoupdate,scanupdate)\
124 VALUES (%s,%s,%s,%s,%s,"",%s,%s,%s,%s,%s)', \
125 (bctype, chtxt, ch, csch,tsid,updatetime, "1","1",logoupdate,scanupdate))
127 def delete_all_epg_ch(self):
128 db = self.connect_db()
132 def select_by_chtxt_epg_ch(self, chtxt):
133 db = self.connect_db()
134 dbexe = db[1].execute("\
135 SELECT bctype,chtxt,ch,csch,tsid,updatetime,chname,status,visible,logoupdate,scanupdate \
137 WHERE chtxt LIKE %s", \
142 dls = db[1].fetchall()
146 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
148 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
150 r[9]="2012-04-01 00:00:00"
151 r[10] = r[10].strftime("%Y-%m-%d %H:%M:%S")
154 def select_by_bctype_epg_ch(self, bctype):
155 db = self.connect_db()
156 dbexe = db[1].execute("\
157 SELECT bctype,chtxt,ch,csch,tsid,updatetime,status,chname,status,visible,logoupdate,scanupdate \
159 WHERE bctype = %s", \
164 dls = db[1].fetchall()
167 #recdblist.printutf8(dl)
169 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
171 r[10] = r[10].strftime("%Y-%m-%d %H:%M:%S")
173 r[10]="2011-04-01 00:00:00"
174 r[11] = r[11].strftime("%Y-%m-%d %H:%M:%S")
178 ret.insert(0,ret[random.randint(0,len(ret)-1)])
180 def select_by_ch_epg_ch(self, ch):
181 db = self.connect_db()
182 dbexe = db[1].execute("\
184 bctype,chtxt,ch,csch,tsid,updatetime,chname,status,visible,logoupdate,scanupdate \
191 dls = db[1].fetchall()
195 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
196 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
197 r[10] = r[10].strftime("%Y-%m-%d %H:%M:%S")
200 def select_by_csch_epg_ch(self, csch):
201 db = self.connect_db()
202 dbexe = db[1].execute("\
204 bctype,chtxt,ch,csch,tsid,updatetime,chname,status,visible,logoupdate,scanupdate \
211 dls = db[1].fetchall()
215 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
216 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
217 r[10] = r[10].strftime("%Y-%m-%d %H:%M:%S")
220 def select_all_epg_ch(self):
221 db = self.connect_db()
222 dbexe = db[1].execute("\
223 SELECT bctype,chtxt,ch,csch,tsid,updatetime,chname,status,visible,logoupdate,scanupdate \
229 dls = db[1].fetchall()
233 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
234 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
235 r[10] = r[10].strftime("%Y-%m-%d %H:%M:%S")
238 def change_visible_epg_ch(self,chtxt,visible):
239 db = self.connect_db()
241 UPDATE epg_ch SET visible=%s WHERE chtxt=%s",(visible,chtxt))
243 def change_logodata_epg_ch(self,chtxt,logonum,logodata):
244 db = self.connect_db()
246 UPDATE epg_ch SET logo"+str(logonum)+"=%s WHERE chtxt=%s",(logodata,chtxt))
248 def set_new_status(self,dhour):
249 db = self.connect_db()
250 dbexe = db[1].execute("UPDATE epg_ch \
254 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
259 def select_get_update_epg_ch(self, dhour):
260 db = self.connect_db()
261 dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
264 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
270 ORDER BY status DESC")
272 #recdblist.printutf8(dbexe)
274 ret = db[1].fetchall()
277 def select_get_updatelogo_epg_ch(self, dhour):
278 db = self.connect_db()
279 dbexe = db[1].execute("SELECT bctype,chtxt,logostatus FROM epg_ch \
282 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
288 ORDER BY status DESC")
290 #recdblist.printutf8(dbexe)
292 ret = db[1].fetchall()
295 def update_by_bctype_epg_ch(self, bctype):
296 db = self.connect_db()
302 WHERE bctype = %s", (bctype,))
305 def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
306 db = self.connect_db()
312 WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
314 def update_chname_by_chtxt_epg_ch(self,chtxt,chname):
315 db = self.connect_db()
320 WHERE chtxt = %s", (chname,chtxt))
322 def update_status_by_bctype_epg_ch(self, bctype, status):
323 db = self.connect_db()
329 WHERE bctype = %s", \
333 def update_logostatus_by_bctype_epg_ch(self,bctype,logostatus):
334 db = self.connect_db()
340 WHERE bctype = %s", \
341 (logostatus, bctype)\
344 def add_auto_proc_tmp(self,type,title,chtxt):
345 db = self.connect_db()
347 INSERT IGNORE into auto_proc_tmp \
349 values (%s,%s,%s)',(type,title,chtxt))
352 def new_auto_proc_tmp(self):
353 db = self.connect_db()
355 db[1].execute('drop table auto_proc_tmp')
356 except Exception, inst:
357 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
358 recdblist.addCommonlogEX("Error", "new_auto_proc_tmp drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
362 CREATE TABLE auto_proc_tmp \
365 title VARCHAR(100) PRIMARY KEY,\
367 UNIQUE unibayeskey(title)\
369 except Exception, inst:
370 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
371 recdblist.addCommonlogEX("Error", "new_auto_proc_tmp (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
374 def update_auto_proc(self):
375 db = self.connect_db()
377 db[1].execute('INSERT INTO auto_proc SELECT * FROM auto_proc_tmp')
378 except Exception, inst:
379 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
380 recdblist.addCommonlogEX("Error", "update_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
383 def new_auto_proc(self):
384 db = self.connect_db()
386 db[1].execute('drop table auto_proc')
387 except Exception, inst:
388 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
389 recdblist.addCommonlogEX("Error", "new_auto_proc drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
393 CREATE TABLE auto_proc \
396 title VARCHAR(100) PRIMARY KEY,\
398 UNIQUE unibayeskey(title)\
400 except Exception, inst:
401 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
402 recdblist.addCommonlogEX("Error", "new_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
405 def add_auto_proc(self,type,title,chtxt):
406 db = self.connect_db()
408 INSERT IGNORE into auto_proc \
410 values (%s,%s,%s)',(type,title,chtxt))
413 def drop_in_settings(self):
414 db = self.connect_db()
416 db[1].execute('drop table in_settings')
417 except Exception, inst:
418 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
419 recdblist.addCommonlogEX("Error", "drop_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
421 def new_in_settings(self):
422 db = self.connect_db()
425 CREATE TABLE in_settings \
429 auto_opt VARCHAR(20),\
430 auto_del_tmp TINYINT\
432 db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"H\",1)")
433 except Exception, inst:
434 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
435 recdblist.addCommonlogEX("Error", "new_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
438 def select_all_in_settings(self):
439 db = self.connect_db()
440 dbexe = db[1].execute("\
441 SELECT auto_jbk,auto_bayes,auto_del_tmp,auto_opt \
447 dls = db[1].fetchall()
457 def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
458 db = self.connect_db()
460 INSERT IGNORE into in_timeline_log \
461 (chtxt,title,btime,etime,opt,exp,longexp,category) \
462 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
463 ( chtxt, title, btime, etime, opt,exp,longexp,category))
466 def del_in_timeline_log(self, title="", chtxt="", btime=""):
470 db = self.connect_db()
472 DELETE FROM in_timeline_log \
473 WHERE title = %s AND chtxt = %s AND btime = %s", \
474 (title, chtxt, btime))
477 def new_in_timeline_log(self):
478 db = self.connect_db()
481 CREATE TABLE in_timeline_log \
483 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
491 category VARCHAR(100),\
492 UNIQUE uni (chtxt,title,btime,category)\
494 except Exception, inst:
495 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
496 recdblist.addCommonlogEX("Error", "new_in_timeline_log (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
499 def select_chtxt_by_title_timeline_log(self,title):
500 db = self.connect_db()
501 dbexe = db[1].execute("\
503 FROM in_timeline_log \
504 WHERE title LIKE \"%"+title+"%\"\
506 ORDER by sum(1) DESC limit 1")
507 retdb=db[1].fetchall()
514 def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt="" ,counter=-1):
515 db = self.connect_db()
517 INSERT IGNORE into timeline \
518 (type,chtxt,title,btime,etime,deltatime,deltaday,opt,counter) \
519 values (%s,%s,%s,%s,%s,%s,%s,%s,%s)', \
520 (type, chtxt, title, btime, etime, deltatime, deltaday, opt ,counter))
523 def update_epg_timeline(self,type,chtxt,title,btime,epgbtime,epgetime,epgtitle,epgexp,epgcategory):
524 db = self.connect_db()
527 SET epgbtime=%s,epgetime=%s,epgtitle=%s,epgexp=%s,epgcategory=%s \
528 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
529 (epgbtime,epgetime,epgtitle,epgexp,epgcategory,type, chtxt, title, btime))
532 def update_status_change_timeline(self,type,chtxt,title,btime,epgchange):
533 db = self.connect_db()
537 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
538 (epgchange , type, chtxt, title, btime))
541 def update_status_dup_timeline(self,type,chtxt,title,btime,epgduplicate):
542 db = self.connect_db()
545 SET epgduplicate =%s \
546 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
547 (epgduplicate , type, chtxt, title, btime))
550 def del_timeline(self, type="", title="", chtxt="", btime=""):
554 db = self.connect_db()
556 DELETE FROM timeline \
557 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
558 (type, title, chtxt, btime))
561 def select_all_timeline(self):
562 db = self.connect_db()
564 dbr = db[1].execute("\
565 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
567 dbl = db[1].fetchall()
570 recdata = self.getdic_timeline(dbl)
572 def select_bytime_timeline(self, dminutes):
573 db = self.connect_db()
575 #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 )")
576 dbr = db[1].execute("SELECT \
577 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
579 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
580 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
581 dbl = db[1].fetchall()
583 #recdblist.printutf8(dbl)
585 recdatum = self.getdic_timeline(dbl)
587 def select_by_name_time_timeline(self,title,btime,btime2):
588 db = self.connect_db()
590 #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 )")
591 dbr = db[1].execute("SELECT \
592 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
594 WHERE btime > %s AND \
595 btime < %s AND title = %s",(btime,btime2,title))
596 dbl = db[1].fetchall()
598 #recdblist.printutf8(dbl)
600 recdatum = self.getdic_timeline(dbl)
602 def select_bytime_all_timeline(self,btime,etime):
603 db = self.connect_db()
605 #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 )")
606 dbr = db[1].execute("SELECT \
607 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
609 WHERE btime >= %s AND \
610 etime <= %s",(btime,etime))
611 dbl = db[1].fetchall()
613 #recdblist.printutf8(dbl)
615 recdatum = self.getdic_timeline(dbl)
617 def select_byepgtime_all_timeline(self,epgbtime,epgetime):
618 db = self.connect_db()
620 #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 )")
621 dbr = db[1].execute("SELECT \
622 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
624 WHERE epgbtime >= %s AND \
625 epgetime <= %s",(epgbtime,epgetime))
626 dbl = db[1].fetchall()
628 #recdblist.printutf8(dbl)
630 recdatum=self.getdic_timeline(dbl)
632 def select_byepgtime_over_timeline(self,epgbtime,epgetime):
633 db = self.connect_db()
635 #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 )")
636 dbr = db[1].execute("SELECT \
637 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
639 WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"\
640 ,(epgbtime,epgetime))
641 dbl = db[1].fetchall()
643 #recdblist.printutf8(dbl)
645 recdatum=self.getdic_timeline(dbl)
647 def count_schedule_timeline(self, btime, etime):
650 return [te num,bs/cs num]
652 db = self.connect_db()
653 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
654 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
657 db[1].execute(dbexe, (btime, etime,btime,etime))
659 for typet, bctypet, chtxtt, titlet in dbl:
660 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
661 if bctypet.find("cs") > -1:
663 elif bctypet.find("bs") > -1:
665 elif bctypet.find("te") > -1:
669 def search_schedule_timeline(self,btime,etime):
672 return [(type,bctype,chtxt,title,btime,etime)]
674 db = self.connect_db()
675 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"
676 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
678 db[1].execute(dbexe, (btime, etime,btime,etime))
680 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
681 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
682 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
685 def count_epgschedule_timeline(self, epgbtime, epgetime):
688 return [te num,bs/cs num]
690 db = self.connect_db()
691 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
692 dbexe = dbexe + " WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"
695 db[1].execute(dbexe, (epgbtime, epgetime))
697 for typet, bctypet, chtxtt, titlet in dbl:
698 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
699 if bctypet.find("cs") > -1:
701 elif bctypet.find("bs") > -1:
703 elif bctypet.find("te") > -1:
707 def search_epgschedule_timeline(self,epgbtime,epgetime):
710 return [(type,bctype,chtxt,title,btime,etime)]
712 db = self.connect_db()
713 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"
714 dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )"
716 db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime))
718 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
719 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
720 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
723 def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
724 db = self.connect_db()
726 #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 )")
727 dbr = db[1].execute("SELECT \
728 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
730 WHERE btime > %s AND \
732 AND chtxt=%s ",(btime,etime,chtxt))
733 dbl = db[1].fetchall()
735 #recdblist.printutf8(dbl)
737 recdatum = self.getdic_timeline(dbl)
739 def getdic_timeline(self,timelinelists):
741 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt ,epgbtimet , epgetimet ,epgtitlet ,epgduplicatet ,epgchanget ,countert in timelinelists:
746 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
747 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
752 ret['epgbtime'] = epgbtimet.strftime("%Y-%m-%d %H:%M:%S")
753 ret['epgetime'] = epgetimet.strftime("%Y-%m-%d %H:%M:%S")
755 ret['epgbtime'] = "2010-01-01 00:00:00"
756 ret['epgetime'] = "2010-01-01 00:00:00"
757 ret['epgtitle'] = epgtitlet
758 ret['epgduplicate'] = epgduplicatet
759 ret['epgchange'] = epgchanget
760 if deltatime == None or deltatime == "":
762 if deltaday == None or deltaday == "":
764 if typet == recdblist.REC_KEYWORD:
765 ret['deltatime'] = deltatime
766 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
767 ret['deltatime'] = deltatime
768 ret['deltaday'] = deltaday
770 ret['counter'] = int(countert)
775 def delete_old_timeline(self, dhour):
776 db = self.connect_db()
778 DELETE FROM timeline \
780 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
783 def new_in_auto_jbk_key(self):
784 db = self.connect_db()
787 CREATE TABLE in_auto_jbk_key \
789 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
790 keyword VARCHAR(200),\
791 auto TINYINT DEFAULT 0,\
793 UNIQUE unijbk (keyword)\
795 except Exception, inst:
796 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
797 recdblist.addCommonlogEX("Error", "new_in_auto_jbk_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
799 def add_in_auto_jbk_key(self,key):
800 db = self.connect_db()
802 INSERT IGNORE into in_auto_jbk_key \
808 def select_all_in_auto_jbk_key(self):
809 db = self.connect_db()
810 dbexe = db[1].execute("\
811 SELECT keyword,auto,opt \
812 FROM in_auto_jbk_key \
816 ret = db[1].fetchall()
819 def drop_in_status(self):
820 db = self.connect_db()
822 db[1].execute('drop table in_status')
823 except Exception, inst:
824 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
825 recdblist.addCommonlogEX("Error", "drop_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
827 def new_in_status(self):
828 db = self.connect_db()
831 CREATE TABLE in_status \
833 ts2avi TINYINT DEFAULT 0,\
834 terec TINYINT DEFAULT 0,\
835 bscsrec TINYINT DEFAULT 0,\
836 b252ts TINYINT DEFAULT 0,\
837 installed TINYINT DEFAULT 0,\
840 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0,0)")
841 except Exception, inst:
842 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
843 recdblist.addCommonlogEX("Error", "new_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
845 def select_all_in_status(self):
846 db = self.connect_db()
847 dbexe = db[1].execute("\
848 SELECT ts2avi,terec,bscsrec,b252ts \
854 dls = db[1].fetchall()
864 def select_version_in_status(self):
865 db = self.connect_db()
868 dbexe = db[1].execute("\
873 dls = db[1].fetchall()
877 version=int(str(r[0]))
878 except Exception, inst:
879 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
880 recdblist.addCommonlogEX("Error", "select_version_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
882 def change_version_in_status(self,version):
883 db = self.connect_db()
885 UPDATE in_status SET version=%s",str(version))
887 def change_ts2avi_in_status(self,i):
892 db = self.connect_db()
894 UPDATE in_status SET ts2avi=ts2avi+%s",i)
896 def change_terec_in_status(self,i):
901 db = self.connect_db()
903 UPDATE in_status SET terec=terec+%s",i)
905 def change_bscsrec_in_status(self,i):
910 db = self.connect_db()
912 UPDATE in_status SET bscsrec=bscsrec+%s",i)
914 def change_b252ts_in_status(self,i):
919 db = self.connect_db()
921 UPDATE in_status SET b252ts=b252ts+%s",i)
923 def select_installed_in_status(self):
924 db = self.connect_db()
925 dbexe = db[1].execute("\
926 SELECT ts2avi,terec,bscsrec,b252ts,installed \
932 dls = db[1].fetchall()
938 def change_chscaned_in_status(self):
942 db = self.connect_db()
944 UPDATE in_status SET installed=2")
946 def change_installed_in_status(self,num=1):
950 db = self.connect_db()
952 UPDATE in_status SET installed=%s",(num,))
954 def new_epg_timeline(self, bctype):
955 db = self.connect_db()
958 DELETE FROM epg_timeline \
959 WHERE bctype = %s", \
961 except Exception, inst:
962 if not ((type(inst)==MySQLdb.ProgrammingError and (inst[0]==1007 or inst[0]==1146))or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
963 recdblist.addCommonlogEX("Error", "new_epg_timeline delete (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
966 CREATE TABLE epg_timeline \
969 channel VARCHAR(100) NOT NULL,\
975 category VARCHAR(100),\
976 UNIQUE unitv(bctype,channel,start,stop,title)\
978 except Exception, inst:
979 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
980 recdblist.addCommonlogEX("Error", "new_epg_timeline (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
983 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
984 db = self.connect_db()
986 INSERT IGNORE INTO epg_timeline \
987 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
988 (bctype, channel, start, stop, title, desc,longdesc,category))
991 def add_multi_epg_timeline(self, tvlists):
993 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
995 db = self.connect_db()
997 INSERT IGNORE INTO epg_timeline \
998 (bctype,channel,start,stop,title,exp,longexp,category) \
999 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
1002 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
1003 db = self.connect_db()
1006 channel,title,start,stop,exp,longexp,category \
1013 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
1016 retall = db[1].fetchall()
1019 def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
1020 db = self.connect_db()
1023 epg_ch.chtxt,title,start,stop,exp,longexp,category \
1026 WHERE epg_ch.chtxt=epg_timeline.channel \
1035 ( title LIKE \'%%"+keyword+"%%\' ) \
1037 ( exp LIKE \'%%"+keyword+"%%\' ) \
1039 ( longexp LIKE \'%%"+keyword+"%%\' ) \
1041 dbcmd = db[1].execute(dbexe,(btime, etime))
1044 retall = db[1].fetchall()
1047 def new_in_auto_bayes_key(self):
1048 db = self.connect_db()
1050 db[1].execute('CREATE TABLE in_auto_bayes_key \
1052 keychar VARCHAR(10),\
1054 ratio_rec DECIMAL(32,14),\
1055 ratio_all DECIMAL(32,14),\
1056 UNIQUE unibayeskey(keychar,chtxt)\
1058 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
1059 except Exception, inst:
1060 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1061 recdblist.addCommonlogEX("Error", "new_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1064 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
1065 db = self.connect_db()
1066 ratio_rec=str(ratio_rec)
1067 ratio_all=str(ratio_all)
1069 INSERT IGNORE INTO in_auto_bayes_key \
1070 (keychar,chtxt,ratio_rec,ratio_all) \
1071 values (%s,%s,%s,%s)',\
1072 (key,chtxt,ratio_rec,ratio_all))
1074 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
1075 db = self.connect_db()
1076 add_rec_num=str(add_rec_num)
1077 add_all_num=str(add_all_num)
1079 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",\
1080 (add_rec_num,add_all_num,chtxt))
1082 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
1085 db = self.connect_db()
1087 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)\
1090 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1091 beforenum=str(beforenum)
1093 db = self.connect_db()
1095 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)\
1098 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1099 db = self.connect_db()
1100 beforenumf=beforenum
1101 beforenum=str(beforenum)
1102 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1103 VALUES (%s,%s,%s,%s)\
1104 ON DUPLICATE KEY UPDATE \
1105 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
1107 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
1109 list={key:addnum}のリスト
1111 beforenumf=beforenum
1112 beforenum=str(beforenum)
1113 db = self.connect_db()
1114 for i,j in list.items():
1115 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
1117 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1118 VALUES (%s,%s,%s,%s)\
1119 ON DUPLICATE KEY UPDATE \
1120 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
1121 except Exception, inst:
1122 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1123 recdblist.addCommonlogEX("Error", "change_multi_ratio_all_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1125 def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1126 beforenum=str(beforenum)
1128 db = self.connect_db()
1130 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)\
1133 def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1134 db = self.connect_db()
1135 beforenumf=beforenum
1136 beforenum=str(beforenum)
1137 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1138 VALUES (%s,%s,%s,%s)\
1139 ON DUPLICATE KEY UPDATE \
1140 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
1142 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
1143 beforenumf=beforenum
1144 beforenum=str(beforenum)
1145 db = self.connect_db()
1146 for i,j in list.items():
1147 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
1149 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1150 VALUES (%s,%s,%s,%s)\
1151 ON DUPLICATE KEY UPDATE \
1152 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
1153 except Exception, inst:
1154 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1155 recdblist.addCommonlogEX("Error", "change_multi_ratio_rec_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1157 def select_by_key_in_auto_bayes_key(self,key,chtxt):
1158 db = self.connect_db()
1159 dbexe = db[1].execute("\
1160 SELECT keychar,chtxt,ratio_rec,ratio_all \
1161 FROM in_auto_bayes_key \
1162 WHERE keychar = %s AND chtxt = %s", \
1166 dls = db[1].fetchall()
1172 def new_auto_timeline_keyword(self):
1173 db = self.connect_db()
1176 CREATE TABLE auto_timeline_keyword \
1178 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1180 title VARCHAR(100),\
1183 UNIQUE uni (chtxt,title,btime,etime)\
1185 except Exception, inst:
1186 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1187 recdblist.addCommonlogEX("Error", "new_auto_timeline_keyword (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1188 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
1189 db = self.connect_db()
1191 INSERT IGNORE into auto_timeline_keyword \
1192 (chtxt,title,btime,etime) \
1193 values (%s,%s,%s,%s)', \
1194 (chtxt, title, btime, etime))
1197 def delete_old_auto_timeline_keyword(self, dhour):
1198 db = self.connect_db()
1200 DELETE FROM auto_timeline_keyword \
1202 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1204 def new_auto_timeline_bayes(self):
1205 db = self.connect_db()
1208 CREATE TABLE auto_timeline_bayes \
1210 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1212 title VARCHAR(100),\
1216 UNIQUE uni (chtxt,title,btime,etime)\
1218 except Exception, inst:
1219 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1220 recdblist.addCommonlogEX("Error", "new_auto_timeline_bayes (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1221 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
1222 db = self.connect_db()
1224 INSERT IGNORE into auto_timeline_bayes \
1225 (chtxt,title,btime,etime,point) \
1226 values (%s,%s,%s,%s,%s)', \
1227 (chtxt, title, btime, etime,point))
1229 def delete_old_auto_timeline_bayes(self, dhour):
1230 db = self.connect_db()
1232 DELETE FROM auto_timeline_bayes \
1234 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1236 def new_auto_classify(self):
1237 db = self.connect_db()
1240 CREATE TABLE auto_classify \
1242 frompath VARCHAR(1024),\
1243 topath VARCHAR(1024),\
1246 except Exception, inst:
1247 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1248 recdblist.addCommonlogEX("Error", "new_auto_classify (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1249 def add_auto_classify(self, frompath="", topath=""):
1250 db = self.connect_db()
1252 INSERT IGNORE into auto_classify \
1253 (frompath,topath,do) \
1254 values (%s,%s,0)', \
1257 def delete_old_auto_classify(self, frompath):
1258 db = self.connect_db()
1260 DELETE FROM auto_classify \
1262 frompath == %s", (frompath))
1264 def update_db_to93(self):
1265 db = self.connect_db()
1266 self.drop_in_settings()
1267 self.new_in_settings()
1269 ALTER TABLE timeline ADD epgtitle VARCHAR(100),\
1270 ADD epgbtime DATETIME,\
1271 ADD epgetime DATETIME,\
1272 ADD epgduplicate TINYINT DEFAULT 0,\
1273 ADD epgchange TINYINT DEFAULT 0")
1275 ALTER TABLE in_status ADD version TINYINT")
1277 self.change_version_in_status("93")
1278 def update_db_93to94(self):
1279 db = self.connect_db()
1280 self.drop_in_settings()
1281 self.new_in_settings()
1283 ALTER TABLE timeline ADD counter TINYINT DEFAULT -1")
1285 self.change_version_in_status("94")
1286 def update_db_94to95(self):
1287 db = self.connect_db()
1288 self.drop_in_settings()
1289 self.new_in_settings()
1291 ALTER TABLE timeline ADD epgexp VARCHAR(200)")
1293 self.change_version_in_status("95")
1294 def update_db_95to96(self):
1295 db = self.connect_db()
1296 self.drop_in_settings()
1297 self.new_in_settings()
1299 self.change_version_in_status("96")
1300 def update_db_96to98(self):
1301 db = self.connect_db()
1302 self.drop_in_settings()
1303 self.new_in_settings()
1305 self.change_version_in_status("98")
1306 def update_db_98to100(self):
1307 ###ここで前のepg_chをバックアップしてchtxtの変換をする必要がある。
1308 self.drop_in_settings()
1309 self.new_in_settings()
1310 db = self.connect_db()
1312 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')")
1314 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'")
1316 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'")
1319 ALTER TABLE epg_ch DROP ontv")
1323 ALTER TABLE epg_ch ADD logo0 BLOB,\
1331 ALTER TABLE in_auto_jbk_key ADD auto TINYINT DEFAULT 0")
1333 ALTER TABLE in_auto_jbk_key ADD opt VARCHAR(20) DEFAULT \"\"")
1335 self.change_installed_in_status(1)#チャンネルスキャンをさせる
1336 self.change_version_in_status("100")
1337 def update_db_100to101(self):
1338 self.drop_in_settings()
1339 self.new_in_settings()
1341 db = self.connect_db()
1344 ALTER TABLE timeline ADD epgcategory VARCHAR(100)\
1349 self.change_installed_in_status(1)
1350 self.change_version_in_status("101")
1351 #self.change_installed_in_status()#チャンネルスキャンをさせる
1352 def update_db_101to102(sekf):
1353 self.drop_in_settings()
1354 self.new_in_settings()
1356 db = self.connect_db()
1359 ALTER TABLE epg_ch ADD tsid VARCHAR(20)\
1364 self.change_installed_in_status(1)
1365 self.change_version_in_status("102")
1366 #self.change_installed_in_status()#チャンネルスキャンをさせる