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_status_by_bctype_epg_ch(self, bctype, status):
301 db = self.connect_db()
307 WHERE bctype = %s", \
311 def update_status_by_bctype_epg_ch(self, bctype, status):
312 db = self.connect_db()
318 WHERE bctype = %s", \
322 def update_logostatus_by_bctype_epg_ch(self,bctype,logostatus):
323 db = self.connect_db()
329 WHERE bctype = %s", \
330 (logostatus, bctype)\
333 def add_auto_proc_tmp(self,type,title,chtxt):
334 db = self.connect_db()
336 INSERT IGNORE into auto_proc_tmp \
338 values (%s,%s,%s)',(type,title,chtxt))
341 def new_auto_proc_tmp(self):
342 db = self.connect_db()
344 db[1].execute('drop table auto_proc_tmp')
345 except Exception, inst:
346 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
347 recdblist.addCommonlogEX("Error", "new_auto_proc_tmp drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
351 CREATE TABLE auto_proc_tmp \
354 title VARCHAR(100) PRIMARY KEY,\
356 UNIQUE unibayeskey(title)\
358 except Exception, inst:
359 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
360 recdblist.addCommonlogEX("Error", "new_auto_proc_tmp (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
363 def update_auto_proc(self):
364 db = self.connect_db()
366 db[1].execute('INSERT INTO auto_proc SELECT * FROM auto_proc_tmp')
367 except Exception, inst:
368 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
369 recdblist.addCommonlogEX("Error", "update_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
372 def new_auto_proc(self):
373 db = self.connect_db()
375 db[1].execute('drop table auto_proc')
376 except Exception, inst:
377 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
378 recdblist.addCommonlogEX("Error", "new_auto_proc drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
382 CREATE TABLE auto_proc \
385 title VARCHAR(100) PRIMARY KEY,\
387 UNIQUE unibayeskey(title)\
389 except Exception, inst:
390 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
391 recdblist.addCommonlogEX("Error", "new_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
394 def add_auto_proc(self,type,title,chtxt):
395 db = self.connect_db()
397 INSERT IGNORE into auto_proc \
399 values (%s,%s,%s)',(type,title,chtxt))
402 def drop_in_settings(self):
403 db = self.connect_db()
405 db[1].execute('drop table in_settings')
406 except Exception, inst:
407 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
408 recdblist.addCommonlogEX("Error", "drop_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
410 def new_in_settings(self):
411 db = self.connect_db()
414 CREATE TABLE in_settings \
418 auto_opt VARCHAR(20),\
419 auto_del_tmp TINYINT\
421 db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"H\",1)")
422 except Exception, inst:
423 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
424 recdblist.addCommonlogEX("Error", "new_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
427 def select_all_in_settings(self):
428 db = self.connect_db()
429 dbexe = db[1].execute("\
430 SELECT auto_jbk,auto_bayes,auto_del_tmp,auto_opt \
436 dls = db[1].fetchall()
446 def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
447 db = self.connect_db()
449 INSERT IGNORE into in_timeline_log \
450 (chtxt,title,btime,etime,opt,exp,longexp,category) \
451 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
452 ( chtxt, title, btime, etime, opt,exp,longexp,category))
455 def del_in_timeline_log(self, title="", chtxt="", btime=""):
459 db = self.connect_db()
461 DELETE FROM in_timeline_log \
462 WHERE title = %s AND chtxt = %s AND btime = %s", \
463 (title, chtxt, btime))
466 def new_in_timeline_log(self):
467 db = self.connect_db()
470 CREATE TABLE in_timeline_log \
472 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
480 category VARCHAR(100),\
481 UNIQUE uni (chtxt,title,btime,category)\
483 except Exception, inst:
484 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
485 recdblist.addCommonlogEX("Error", "new_in_timeline_log (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
488 def select_chtxt_by_title_timeline_log(self,title):
489 db = self.connect_db()
490 dbexe = db[1].execute("\
492 FROM in_timeline_log \
493 WHERE title LIKE \"%"+title+"%\"\
495 ORDER by sum(1) DESC limit 1")
496 retdb=db[1].fetchall()
503 def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt="" ,counter=-1):
504 db = self.connect_db()
506 INSERT IGNORE into timeline \
507 (type,chtxt,title,btime,etime,deltatime,deltaday,opt,counter) \
508 values (%s,%s,%s,%s,%s,%s,%s,%s,%s)', \
509 (type, chtxt, title, btime, etime, deltatime, deltaday, opt ,counter))
512 def update_epg_timeline(self,type,chtxt,title,btime,epgbtime,epgetime,epgtitle,epgexp,epgcategory):
513 db = self.connect_db()
516 SET epgbtime=%s,epgetime=%s,epgtitle=%s,epgexp=%s,epgcategory=%s \
517 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
518 (epgbtime,epgetime,epgtitle,epgexp,epgcategory,type, chtxt, title, btime))
521 def update_status_change_timeline(self,type,chtxt,title,btime,epgchange):
522 db = self.connect_db()
526 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
527 (epgchange , type, chtxt, title, btime))
530 def update_status_dup_timeline(self,type,chtxt,title,btime,epgduplicate):
531 db = self.connect_db()
534 SET epgduplicate =%s \
535 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
536 (epgduplicate , type, chtxt, title, btime))
539 def del_timeline(self, type="", title="", chtxt="", btime=""):
543 db = self.connect_db()
545 DELETE FROM timeline \
546 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
547 (type, title, chtxt, btime))
550 def select_all_timeline(self):
551 db = self.connect_db()
553 dbr = db[1].execute("\
554 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
556 dbl = db[1].fetchall()
559 recdata = self.getdic_timeline(dbl)
561 def select_bytime_timeline(self, dminutes):
562 db = self.connect_db()
564 #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 )")
565 dbr = db[1].execute("SELECT \
566 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
568 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
569 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
570 dbl = db[1].fetchall()
572 #recdblist.printutf8(dbl)
574 recdatum = self.getdic_timeline(dbl)
576 def select_by_name_time_timeline(self,title,btime,btime2):
577 db = self.connect_db()
579 #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 )")
580 dbr = db[1].execute("SELECT \
581 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
583 WHERE btime > %s AND \
584 btime < %s AND title = %s",(btime,btime2,title))
585 dbl = db[1].fetchall()
587 #recdblist.printutf8(dbl)
589 recdatum = self.getdic_timeline(dbl)
591 def select_bytime_all_timeline(self,btime,etime):
592 db = self.connect_db()
594 #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 )")
595 dbr = db[1].execute("SELECT \
596 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
598 WHERE btime >= %s AND \
599 etime <= %s",(btime,etime))
600 dbl = db[1].fetchall()
602 #recdblist.printutf8(dbl)
604 recdatum = self.getdic_timeline(dbl)
606 def select_byepgtime_all_timeline(self,epgbtime,epgetime):
607 db = self.connect_db()
609 #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 )")
610 dbr = db[1].execute("SELECT \
611 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
613 WHERE epgbtime >= %s AND \
614 epgetime <= %s",(epgbtime,epgetime))
615 dbl = db[1].fetchall()
617 #recdblist.printutf8(dbl)
619 recdatum=self.getdic_timeline(dbl)
621 def select_byepgtime_over_timeline(self,epgbtime,epgetime):
622 db = self.connect_db()
624 #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 )")
625 dbr = db[1].execute("SELECT \
626 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
628 WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"\
629 ,(epgbtime,epgetime))
630 dbl = db[1].fetchall()
632 #recdblist.printutf8(dbl)
634 recdatum=self.getdic_timeline(dbl)
636 def count_schedule_timeline(self, btime, etime):
639 return [te num,bs/cs num]
641 db = self.connect_db()
642 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
643 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
646 db[1].execute(dbexe, (btime, etime,btime,etime))
648 for typet, bctypet, chtxtt, titlet in dbl:
649 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
650 if bctypet.find("cs") > -1:
652 elif bctypet.find("bs") > -1:
654 elif bctypet.find("te") > -1:
658 def search_schedule_timeline(self,btime,etime):
661 return [(type,bctype,chtxt,title,btime,etime)]
663 db = self.connect_db()
664 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"
665 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
667 db[1].execute(dbexe, (btime, etime,btime,etime))
669 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
670 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
671 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
674 def count_epgschedule_timeline(self, epgbtime, epgetime):
677 return [te num,bs/cs num]
679 db = self.connect_db()
680 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
681 dbexe = dbexe + " WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"
684 db[1].execute(dbexe, (epgbtime, epgetime))
686 for typet, bctypet, chtxtt, titlet in dbl:
687 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
688 if bctypet.find("cs") > -1:
690 elif bctypet.find("bs") > -1:
692 elif bctypet.find("te") > -1:
696 def search_epgschedule_timeline(self,epgbtime,epgetime):
699 return [(type,bctype,chtxt,title,btime,etime)]
701 db = self.connect_db()
702 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"
703 dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )"
705 db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime))
707 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
708 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
709 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
712 def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
713 db = self.connect_db()
715 #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 )")
716 dbr = db[1].execute("SELECT \
717 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
719 WHERE btime > %s AND \
721 AND chtxt=%s ",(btime,etime,chtxt))
722 dbl = db[1].fetchall()
724 #recdblist.printutf8(dbl)
726 recdatum = self.getdic_timeline(dbl)
728 def getdic_timeline(self,timelinelists):
730 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt ,epgbtimet , epgetimet ,epgtitlet ,epgduplicatet ,epgchanget ,countert in timelinelists:
735 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
736 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
741 ret['epgbtime'] = epgbtimet.strftime("%Y-%m-%d %H:%M:%S")
742 ret['epgetime'] = epgetimet.strftime("%Y-%m-%d %H:%M:%S")
744 ret['epgbtime'] = "2010-01-01 00:00:00"
745 ret['epgetime'] = "2010-01-01 00:00:00"
746 ret['epgtitle'] = epgtitlet
747 ret['epgduplicate'] = epgduplicatet
748 ret['epgchange'] = epgchanget
749 if deltatime == None or deltatime == "":
751 if deltaday == None or deltaday == "":
753 if typet == recdblist.REC_KEYWORD:
754 ret['deltatime'] = deltatime
755 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
756 ret['deltatime'] = deltatime
757 ret['deltaday'] = deltaday
759 ret['counter'] = int(countert)
764 def delete_old_timeline(self, dhour):
765 db = self.connect_db()
767 DELETE FROM timeline \
769 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
772 def new_in_auto_jbk_key(self):
773 db = self.connect_db()
776 CREATE TABLE in_auto_jbk_key \
778 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
779 keyword VARCHAR(200),\
780 auto TINYINT DEFAULT 0,\
782 UNIQUE unijbk (keyword)\
784 except Exception, inst:
785 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
786 recdblist.addCommonlogEX("Error", "new_in_auto_jbk_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
788 def add_in_auto_jbk_key(self,key):
789 db = self.connect_db()
791 INSERT IGNORE into in_auto_jbk_key \
797 def select_all_in_auto_jbk_key(self):
798 db = self.connect_db()
799 dbexe = db[1].execute("\
800 SELECT keyword,auto,opt \
801 FROM in_auto_jbk_key \
805 ret = db[1].fetchall()
808 def drop_in_status(self):
809 db = self.connect_db()
811 db[1].execute('drop table in_status')
812 except Exception, inst:
813 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
814 recdblist.addCommonlogEX("Error", "drop_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
816 def new_in_status(self):
817 db = self.connect_db()
820 CREATE TABLE in_status \
822 ts2avi TINYINT DEFAULT 0,\
823 terec TINYINT DEFAULT 0,\
824 bscsrec TINYINT DEFAULT 0,\
825 b252ts TINYINT DEFAULT 0,\
826 installed TINYINT DEFAULT 0,\
829 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0,0)")
830 except Exception, inst:
831 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
832 recdblist.addCommonlogEX("Error", "new_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
834 def select_all_in_status(self):
835 db = self.connect_db()
836 dbexe = db[1].execute("\
837 SELECT ts2avi,terec,bscsrec,b252ts \
843 dls = db[1].fetchall()
853 def select_version_in_status(self):
854 db = self.connect_db()
857 dbexe = db[1].execute("\
862 dls = db[1].fetchall()
866 version=int(str(r[0]))
867 except Exception, inst:
868 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
869 recdblist.addCommonlogEX("Error", "select_version_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
871 def change_version_in_status(self,version):
872 db = self.connect_db()
874 UPDATE in_status SET version=%s",str(version))
876 def change_ts2avi_in_status(self,i):
881 db = self.connect_db()
883 UPDATE in_status SET ts2avi=ts2avi+%s",i)
885 def change_terec_in_status(self,i):
890 db = self.connect_db()
892 UPDATE in_status SET terec=terec+%s",i)
894 def change_bscsrec_in_status(self,i):
899 db = self.connect_db()
901 UPDATE in_status SET bscsrec=bscsrec+%s",i)
903 def change_b252ts_in_status(self,i):
908 db = self.connect_db()
910 UPDATE in_status SET b252ts=b252ts+%s",i)
912 def select_installed_in_status(self):
913 db = self.connect_db()
914 dbexe = db[1].execute("\
915 SELECT ts2avi,terec,bscsrec,b252ts,installed \
921 dls = db[1].fetchall()
927 def change_installed_in_status(self):
931 db = self.connect_db()
933 UPDATE in_status SET installed=1")
935 def change_chscaned_in_status(self):
939 db = self.connect_db()
941 UPDATE in_status SET installed=2")
943 def new_epg_timeline(self, bctype):
944 db = self.connect_db()
947 DELETE FROM epg_timeline \
948 WHERE bctype = %s", \
950 except Exception, inst:
951 if not ((type(inst)==MySQLdb.ProgrammingError and (inst[0]==1007 or inst[0]==1146))or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
952 recdblist.addCommonlogEX("Error", "new_epg_timeline delete (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
955 CREATE TABLE epg_timeline \
958 channel VARCHAR(100) NOT NULL,\
964 category VARCHAR(100),\
965 UNIQUE unitv(bctype,channel,start,stop,title)\
967 except Exception, inst:
968 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
969 recdblist.addCommonlogEX("Error", "new_epg_timeline (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
972 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
973 db = self.connect_db()
975 INSERT IGNORE INTO epg_timeline \
976 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
977 (bctype, channel, start, stop, title, desc,longdesc,category))
980 def add_multi_epg_timeline(self, tvlists):
982 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
984 db = self.connect_db()
986 INSERT IGNORE INTO epg_timeline \
987 (bctype,channel,start,stop,title,exp,longexp,category) \
988 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
991 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
992 db = self.connect_db()
995 channel,title,start,stop,exp,longexp,category \
1002 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
1005 retall = db[1].fetchall()
1008 def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
1009 db = self.connect_db()
1012 epg_ch.chtxt,title,start,stop,exp,longexp,category \
1015 WHERE epg_ch.chtxt=epg_timeline.channel \
1024 ( title LIKE \'%%"+keyword+"%%\' ) \
1026 ( exp LIKE \'%%"+keyword+"%%\' ) \
1028 ( longexp LIKE \'%%"+keyword+"%%\' ) \
1030 dbcmd = db[1].execute(dbexe,(btime, etime))
1033 retall = db[1].fetchall()
1036 def new_in_auto_bayes_key(self):
1037 db = self.connect_db()
1039 db[1].execute('CREATE TABLE in_auto_bayes_key \
1041 keychar VARCHAR(10),\
1043 ratio_rec DECIMAL(32,14),\
1044 ratio_all DECIMAL(32,14),\
1045 UNIQUE unibayeskey(keychar,chtxt)\
1047 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
1048 except Exception, inst:
1049 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1050 recdblist.addCommonlogEX("Error", "new_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1053 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
1054 db = self.connect_db()
1055 ratio_rec=str(ratio_rec)
1056 ratio_all=str(ratio_all)
1058 INSERT IGNORE INTO in_auto_bayes_key \
1059 (keychar,chtxt,ratio_rec,ratio_all) \
1060 values (%s,%s,%s,%s)',\
1061 (key,chtxt,ratio_rec,ratio_all))
1063 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
1064 db = self.connect_db()
1065 add_rec_num=str(add_rec_num)
1066 add_all_num=str(add_all_num)
1068 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",\
1069 (add_rec_num,add_all_num,chtxt))
1071 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
1074 db = self.connect_db()
1076 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)\
1079 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1080 beforenum=str(beforenum)
1082 db = self.connect_db()
1084 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)\
1087 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1088 db = self.connect_db()
1089 beforenumf=beforenum
1090 beforenum=str(beforenum)
1091 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1092 VALUES (%s,%s,%s,%s)\
1093 ON DUPLICATE KEY UPDATE \
1094 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
1096 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
1098 list={key:addnum}のリスト
1100 beforenumf=beforenum
1101 beforenum=str(beforenum)
1102 db = self.connect_db()
1103 for i,j in list.items():
1104 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
1106 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1107 VALUES (%s,%s,%s,%s)\
1108 ON DUPLICATE KEY UPDATE \
1109 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
1110 except Exception, inst:
1111 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1112 recdblist.addCommonlogEX("Error", "change_multi_ratio_all_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1114 def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1115 beforenum=str(beforenum)
1117 db = self.connect_db()
1119 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)\
1122 def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1123 db = self.connect_db()
1124 beforenumf=beforenum
1125 beforenum=str(beforenum)
1126 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1127 VALUES (%s,%s,%s,%s)\
1128 ON DUPLICATE KEY UPDATE \
1129 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
1131 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
1132 beforenumf=beforenum
1133 beforenum=str(beforenum)
1134 db = self.connect_db()
1135 for i,j in list.items():
1136 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
1138 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1139 VALUES (%s,%s,%s,%s)\
1140 ON DUPLICATE KEY UPDATE \
1141 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
1142 except Exception, inst:
1143 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1144 recdblist.addCommonlogEX("Error", "change_multi_ratio_rec_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1146 def select_by_key_in_auto_bayes_key(self,key,chtxt):
1147 db = self.connect_db()
1148 dbexe = db[1].execute("\
1149 SELECT keychar,chtxt,ratio_rec,ratio_all \
1150 FROM in_auto_bayes_key \
1151 WHERE keychar = %s AND chtxt = %s", \
1155 dls = db[1].fetchall()
1161 def new_auto_timeline_keyword(self):
1162 db = self.connect_db()
1165 CREATE TABLE auto_timeline_keyword \
1167 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1169 title VARCHAR(100),\
1172 UNIQUE uni (chtxt,title,btime,etime)\
1174 except Exception, inst:
1175 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1176 recdblist.addCommonlogEX("Error", "new_auto_timeline_keyword (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1177 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
1178 db = self.connect_db()
1180 INSERT IGNORE into auto_timeline_keyword \
1181 (chtxt,title,btime,etime) \
1182 values (%s,%s,%s,%s)', \
1183 (chtxt, title, btime, etime))
1186 def delete_old_auto_timeline_keyword(self, dhour):
1187 db = self.connect_db()
1189 DELETE FROM auto_timeline_keyword \
1191 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1193 def new_auto_timeline_bayes(self):
1194 db = self.connect_db()
1197 CREATE TABLE auto_timeline_bayes \
1199 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1201 title VARCHAR(100),\
1205 UNIQUE uni (chtxt,title,btime,etime)\
1207 except Exception, inst:
1208 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1209 recdblist.addCommonlogEX("Error", "new_auto_timeline_bayes (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1210 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
1211 db = self.connect_db()
1213 INSERT IGNORE into auto_timeline_bayes \
1214 (chtxt,title,btime,etime,point) \
1215 values (%s,%s,%s,%s,%s)', \
1216 (chtxt, title, btime, etime,point))
1218 def delete_old_auto_timeline_bayes(self, dhour):
1219 db = self.connect_db()
1221 DELETE FROM auto_timeline_bayes \
1223 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1225 def update_db_to93(self):
1226 db = self.connect_db()
1227 self.drop_in_settings()
1228 self.new_in_settings()
1230 ALTER TABLE timeline ADD epgtitle VARCHAR(100),\
1231 ADD epgbtime DATETIME,\
1232 ADD epgetime DATETIME,\
1233 ADD epgduplicate TINYINT DEFAULT 0,\
1234 ADD epgchange TINYINT DEFAULT 0")
1236 ALTER TABLE in_status ADD version TINYINT")
1238 self.change_version_in_status("93")
1239 def update_db_93to94(self):
1240 db = self.connect_db()
1241 self.drop_in_settings()
1242 self.new_in_settings()
1244 ALTER TABLE timeline ADD counter TINYINT DEFAULT -1")
1246 self.change_version_in_status("94")
1247 def update_db_94to95(self):
1248 db = self.connect_db()
1249 self.drop_in_settings()
1250 self.new_in_settings()
1252 ALTER TABLE timeline ADD epgexp VARCHAR(200)")
1254 self.change_version_in_status("95")
1255 def update_db_95to96(self):
1256 db = self.connect_db()
1257 self.drop_in_settings()
1258 self.new_in_settings()
1260 self.change_version_in_status("96")
1261 def update_db_96to98(self):
1262 db = self.connect_db()
1263 self.drop_in_settings()
1264 self.new_in_settings()
1266 self.change_version_in_status("98")
1267 def update_db_98to100(self):
1268 ###ここで前のepg_chをバックアップしてchtxtの変換をする必要がある。
1269 db = self.connect_db()
1270 self.drop_in_settings()
1271 self.new_in_settings()
1273 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')")
1275 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'")
1277 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'")
1280 ALTER TABLE epg_ch DROP ontv")
1284 ALTER TABLE epg_ch ADD logo0 BLOB,\
1292 ALTER TABLE in_auto_jbk_key ADD auto TINYINT DEFAULT 0")
1294 ALTER TABLE in_auto_jbk_key ADD opt VARCHAR(20) DEFAULT \"\"")
1296 self.change_installed_in_status()#チャンネルスキャンをさせる
1297 self.change_version_in_status("100")
1298 def update_db_100to101(self):
1299 db = self.connect_db()
1300 self.drop_in_settings()
1301 self.new_in_settings()
1305 ALTER TABLE timeline ADD epgcategory VARCHAR(100)\
1310 #self.change_installed_in_status()#チャンネルスキャンをさせる
1311 self.change_version_in_status("101")