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")
146 r[8] = r[8].strftime("%Y-%m-%d %H:%M:%S")
147 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
150 def select_by_bctype_epg_ch(self, bctype):
151 db = self.connect_db()
152 dbexe = db[1].execute("\
153 SELECT bctype,chtxt,ch,csch,updatetime,status,chname,status,visible,logoupdate,scanupdate \
155 WHERE bctype = %s", \
160 dls = db[1].fetchall()
163 #recdblist.printutf8(dl)
165 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
166 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
167 r[10] = r[10].strftime("%Y-%m-%d %H:%M:%S")
171 def select_by_ch_epg_ch(self, ch):
172 db = self.connect_db()
173 dbexe = db[1].execute("\
175 bctype,chtxt,ch,csch,updatetime,chname,status,visible,logoupdate,scanupdate \
182 dls = db[1].fetchall()
186 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
187 r[8] = r[8].strftime("%Y-%m-%d %H:%M:%S")
188 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
191 def select_by_csch_epg_ch(self, csch):
192 db = self.connect_db()
193 dbexe = db[1].execute("\
195 bctype,chtxt,ch,csch,updatetime,chname,status,visible,logoupdate,scanupdate \
202 dls = db[1].fetchall()
206 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
207 r[8] = r[8].strftime("%Y-%m-%d %H:%M:%S")
208 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
211 def select_all_epg_ch(self):
212 db = self.connect_db()
213 dbexe = db[1].execute("\
214 SELECT bctype,chtxt,ch,csch,updatetime,chname,status,visible,logoupdate,scanupdate \
220 dls = db[1].fetchall()
224 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
225 r[8] = r[8].strftime("%Y-%m-%d %H:%M:%S")
226 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
229 def change_visible_epg_ch(self,chtxt,visible):
230 db = self.connect_db()
232 UPDATE epg_ch SET visible=%s WHERE chtxt=%s",(visible,chtxt))
234 def change_logodata_epg_ch(self,chtxt,logonum,logodata):
235 db = self.connect_db()
237 UPDATE epg_ch SET logo"+str(logonum)+"=%s WHERE chtxt=%s",(logodata,chtxt))
239 def set_new_status(self,dhour):
240 db = self.connect_db()
241 dbexe = db[1].execute("UPDATE epg_ch \
245 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
250 def select_get_update_epg_ch(self, dhour):
251 db = self.connect_db()
252 dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
255 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
261 ORDER BY status DESC")
263 #recdblist.printutf8(dbexe)
265 ret = db[1].fetchall()
268 def select_get_updatelogo_epg_ch(self, dhour):
269 db = self.connect_db()
270 dbexe = db[1].execute("SELECT bctype,chtxt,logostatus FROM epg_ch \
273 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
279 ORDER BY status DESC")
281 #recdblist.printutf8(dbexe)
283 ret = db[1].fetchall()
286 def update_by_bctype_epg_ch(self, bctype):
287 db = self.connect_db()
293 WHERE bctype = %s", (bctype,))
296 def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
297 db = self.connect_db()
303 WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
305 def update_chname_by_chtxt_epg_ch(self,chtxt,chname):
306 db = self.connect_db()
311 WHERE chtxt = %s", (chname,chtxt))
313 def update_status_by_bctype_epg_ch(self, bctype, status):
314 db = self.connect_db()
320 WHERE bctype = %s", \
324 def update_logostatus_by_bctype_epg_ch(self,bctype,logostatus):
325 db = self.connect_db()
331 WHERE bctype = %s", \
332 (logostatus, bctype)\
335 def add_auto_proc_tmp(self,type,title,chtxt):
336 db = self.connect_db()
338 INSERT IGNORE into auto_proc_tmp \
340 values (%s,%s,%s)',(type,title,chtxt))
343 def new_auto_proc_tmp(self):
344 db = self.connect_db()
346 db[1].execute('drop table auto_proc_tmp')
347 except Exception, inst:
348 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
349 recdblist.addCommonlogEX("Error", "new_auto_proc_tmp drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
353 CREATE TABLE auto_proc_tmp \
356 title VARCHAR(100) PRIMARY KEY,\
358 UNIQUE unibayeskey(title)\
360 except Exception, inst:
361 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
362 recdblist.addCommonlogEX("Error", "new_auto_proc_tmp (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
365 def update_auto_proc(self):
366 db = self.connect_db()
368 db[1].execute('INSERT INTO auto_proc SELECT * FROM auto_proc_tmp')
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", "update_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
374 def new_auto_proc(self):
375 db = self.connect_db()
377 db[1].execute('drop table auto_proc')
378 except Exception, inst:
379 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
380 recdblist.addCommonlogEX("Error", "new_auto_proc drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
384 CREATE TABLE auto_proc \
387 title VARCHAR(100) PRIMARY KEY,\
389 UNIQUE unibayeskey(title)\
391 except Exception, inst:
392 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
393 recdblist.addCommonlogEX("Error", "new_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
396 def add_auto_proc(self,type,title,chtxt):
397 db = self.connect_db()
399 INSERT IGNORE into auto_proc \
401 values (%s,%s,%s)',(type,title,chtxt))
404 def drop_in_settings(self):
405 db = self.connect_db()
407 db[1].execute('drop table in_settings')
408 except Exception, inst:
409 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
410 recdblist.addCommonlogEX("Error", "drop_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
412 def new_in_settings(self):
413 db = self.connect_db()
416 CREATE TABLE in_settings \
420 auto_opt VARCHAR(20),\
421 auto_del_tmp TINYINT\
423 db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"H\",1)")
424 except Exception, inst:
425 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
426 recdblist.addCommonlogEX("Error", "new_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
429 def select_all_in_settings(self):
430 db = self.connect_db()
431 dbexe = db[1].execute("\
432 SELECT auto_jbk,auto_bayes,auto_del_tmp,auto_opt \
438 dls = db[1].fetchall()
448 def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
449 db = self.connect_db()
451 INSERT IGNORE into in_timeline_log \
452 (chtxt,title,btime,etime,opt,exp,longexp,category) \
453 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
454 ( chtxt, title, btime, etime, opt,exp,longexp,category))
457 def del_in_timeline_log(self, title="", chtxt="", btime=""):
461 db = self.connect_db()
463 DELETE FROM in_timeline_log \
464 WHERE title = %s AND chtxt = %s AND btime = %s", \
465 (title, chtxt, btime))
468 def new_in_timeline_log(self):
469 db = self.connect_db()
472 CREATE TABLE in_timeline_log \
474 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
482 category VARCHAR(100),\
483 UNIQUE uni (chtxt,title,btime,category)\
485 except Exception, inst:
486 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
487 recdblist.addCommonlogEX("Error", "new_in_timeline_log (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
490 def select_chtxt_by_title_timeline_log(self,title):
491 db = self.connect_db()
492 dbexe = db[1].execute("\
494 FROM in_timeline_log \
495 WHERE title LIKE \"%"+title+"%\"\
497 ORDER by sum(1) DESC limit 1")
498 retdb=db[1].fetchall()
505 def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt="" ,counter=-1):
506 db = self.connect_db()
508 INSERT IGNORE into timeline \
509 (type,chtxt,title,btime,etime,deltatime,deltaday,opt,counter) \
510 values (%s,%s,%s,%s,%s,%s,%s,%s,%s)', \
511 (type, chtxt, title, btime, etime, deltatime, deltaday, opt ,counter))
514 def update_epg_timeline(self,type,chtxt,title,btime,epgbtime,epgetime,epgtitle,epgexp,epgcategory):
515 db = self.connect_db()
518 SET epgbtime=%s,epgetime=%s,epgtitle=%s,epgexp=%s,epgcategory=%s \
519 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
520 (epgbtime,epgetime,epgtitle,epgexp,epgcategory,type, chtxt, title, btime))
523 def update_status_change_timeline(self,type,chtxt,title,btime,epgchange):
524 db = self.connect_db()
528 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
529 (epgchange , type, chtxt, title, btime))
532 def update_status_dup_timeline(self,type,chtxt,title,btime,epgduplicate):
533 db = self.connect_db()
536 SET epgduplicate =%s \
537 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
538 (epgduplicate , type, chtxt, title, btime))
541 def del_timeline(self, type="", title="", chtxt="", btime=""):
545 db = self.connect_db()
547 DELETE FROM timeline \
548 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
549 (type, title, chtxt, btime))
552 def select_all_timeline(self):
553 db = self.connect_db()
555 dbr = db[1].execute("\
556 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
558 dbl = db[1].fetchall()
561 recdata = self.getdic_timeline(dbl)
563 def select_bytime_timeline(self, dminutes):
564 db = self.connect_db()
566 #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 )")
567 dbr = db[1].execute("SELECT \
568 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
570 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
571 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
572 dbl = db[1].fetchall()
574 #recdblist.printutf8(dbl)
576 recdatum = self.getdic_timeline(dbl)
578 def select_by_name_time_timeline(self,title,btime,btime2):
579 db = self.connect_db()
581 #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 )")
582 dbr = db[1].execute("SELECT \
583 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
585 WHERE btime > %s AND \
586 btime < %s AND title = %s",(btime,btime2,title))
587 dbl = db[1].fetchall()
589 #recdblist.printutf8(dbl)
591 recdatum = self.getdic_timeline(dbl)
593 def select_bytime_all_timeline(self,btime,etime):
594 db = self.connect_db()
596 #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 )")
597 dbr = db[1].execute("SELECT \
598 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
600 WHERE btime >= %s AND \
601 etime <= %s",(btime,etime))
602 dbl = db[1].fetchall()
604 #recdblist.printutf8(dbl)
606 recdatum = self.getdic_timeline(dbl)
608 def select_byepgtime_all_timeline(self,epgbtime,epgetime):
609 db = self.connect_db()
611 #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 )")
612 dbr = db[1].execute("SELECT \
613 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
615 WHERE epgbtime >= %s AND \
616 epgetime <= %s",(epgbtime,epgetime))
617 dbl = db[1].fetchall()
619 #recdblist.printutf8(dbl)
621 recdatum=self.getdic_timeline(dbl)
623 def select_byepgtime_over_timeline(self,epgbtime,epgetime):
624 db = self.connect_db()
626 #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 )")
627 dbr = db[1].execute("SELECT \
628 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
630 WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"\
631 ,(epgbtime,epgetime))
632 dbl = db[1].fetchall()
634 #recdblist.printutf8(dbl)
636 recdatum=self.getdic_timeline(dbl)
638 def count_schedule_timeline(self, btime, etime):
641 return [te num,bs/cs num]
643 db = self.connect_db()
644 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
645 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
648 db[1].execute(dbexe, (btime, etime,btime,etime))
650 for typet, bctypet, chtxtt, titlet in dbl:
651 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
652 if bctypet.find("cs") > -1:
654 elif bctypet.find("bs") > -1:
656 elif bctypet.find("te") > -1:
660 def search_schedule_timeline(self,btime,etime):
663 return [(type,bctype,chtxt,title,btime,etime)]
665 db = self.connect_db()
666 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"
667 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
669 db[1].execute(dbexe, (btime, etime,btime,etime))
671 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
672 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
673 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
676 def count_epgschedule_timeline(self, epgbtime, epgetime):
679 return [te num,bs/cs num]
681 db = self.connect_db()
682 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
683 dbexe = dbexe + " WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"
686 db[1].execute(dbexe, (epgbtime, epgetime))
688 for typet, bctypet, chtxtt, titlet in dbl:
689 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
690 if bctypet.find("cs") > -1:
692 elif bctypet.find("bs") > -1:
694 elif bctypet.find("te") > -1:
698 def search_epgschedule_timeline(self,epgbtime,epgetime):
701 return [(type,bctype,chtxt,title,btime,etime)]
703 db = self.connect_db()
704 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"
705 dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )"
707 db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime))
709 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
710 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
711 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
714 def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
715 db = self.connect_db()
717 #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 )")
718 dbr = db[1].execute("SELECT \
719 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
721 WHERE btime > %s AND \
723 AND chtxt=%s ",(btime,etime,chtxt))
724 dbl = db[1].fetchall()
726 #recdblist.printutf8(dbl)
728 recdatum = self.getdic_timeline(dbl)
730 def getdic_timeline(self,timelinelists):
732 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt ,epgbtimet , epgetimet ,epgtitlet ,epgduplicatet ,epgchanget ,countert in timelinelists:
737 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
738 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
743 ret['epgbtime'] = epgbtimet.strftime("%Y-%m-%d %H:%M:%S")
744 ret['epgetime'] = epgetimet.strftime("%Y-%m-%d %H:%M:%S")
746 ret['epgbtime'] = "2010-01-01 00:00:00"
747 ret['epgetime'] = "2010-01-01 00:00:00"
748 ret['epgtitle'] = epgtitlet
749 ret['epgduplicate'] = epgduplicatet
750 ret['epgchange'] = epgchanget
751 if deltatime == None or deltatime == "":
753 if deltaday == None or deltaday == "":
755 if typet == recdblist.REC_KEYWORD:
756 ret['deltatime'] = deltatime
757 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
758 ret['deltatime'] = deltatime
759 ret['deltaday'] = deltaday
761 ret['counter'] = int(countert)
766 def delete_old_timeline(self, dhour):
767 db = self.connect_db()
769 DELETE FROM timeline \
771 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
774 def new_in_auto_jbk_key(self):
775 db = self.connect_db()
778 CREATE TABLE in_auto_jbk_key \
780 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
781 keyword VARCHAR(200),\
782 auto TINYINT DEFAULT 0,\
784 UNIQUE unijbk (keyword)\
786 except Exception, inst:
787 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
788 recdblist.addCommonlogEX("Error", "new_in_auto_jbk_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
790 def add_in_auto_jbk_key(self,key):
791 db = self.connect_db()
793 INSERT IGNORE into in_auto_jbk_key \
799 def select_all_in_auto_jbk_key(self):
800 db = self.connect_db()
801 dbexe = db[1].execute("\
802 SELECT keyword,auto,opt \
803 FROM in_auto_jbk_key \
807 ret = db[1].fetchall()
810 def drop_in_status(self):
811 db = self.connect_db()
813 db[1].execute('drop table in_status')
814 except Exception, inst:
815 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
816 recdblist.addCommonlogEX("Error", "drop_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
818 def new_in_status(self):
819 db = self.connect_db()
822 CREATE TABLE in_status \
824 ts2avi TINYINT DEFAULT 0,\
825 terec TINYINT DEFAULT 0,\
826 bscsrec TINYINT DEFAULT 0,\
827 b252ts TINYINT DEFAULT 0,\
828 installed TINYINT DEFAULT 0,\
831 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0,0)")
832 except Exception, inst:
833 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
834 recdblist.addCommonlogEX("Error", "new_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
836 def select_all_in_status(self):
837 db = self.connect_db()
838 dbexe = db[1].execute("\
839 SELECT ts2avi,terec,bscsrec,b252ts \
845 dls = db[1].fetchall()
855 def select_version_in_status(self):
856 db = self.connect_db()
859 dbexe = db[1].execute("\
864 dls = db[1].fetchall()
868 version=int(str(r[0]))
869 except Exception, inst:
870 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
871 recdblist.addCommonlogEX("Error", "select_version_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
873 def change_version_in_status(self,version):
874 db = self.connect_db()
876 UPDATE in_status SET version=%s",str(version))
878 def change_ts2avi_in_status(self,i):
883 db = self.connect_db()
885 UPDATE in_status SET ts2avi=ts2avi+%s",i)
887 def change_terec_in_status(self,i):
892 db = self.connect_db()
894 UPDATE in_status SET terec=terec+%s",i)
896 def change_bscsrec_in_status(self,i):
901 db = self.connect_db()
903 UPDATE in_status SET bscsrec=bscsrec+%s",i)
905 def change_b252ts_in_status(self,i):
910 db = self.connect_db()
912 UPDATE in_status SET b252ts=b252ts+%s",i)
914 def select_installed_in_status(self):
915 db = self.connect_db()
916 dbexe = db[1].execute("\
917 SELECT ts2avi,terec,bscsrec,b252ts,installed \
923 dls = db[1].fetchall()
929 def change_chscaned_in_status(self):
933 db = self.connect_db()
935 UPDATE in_status SET installed=2")
937 def change_installed_in_status(self,num=1):
941 db = self.connect_db()
943 UPDATE in_status SET installed=%s",(num,))
945 def new_epg_timeline(self, bctype):
946 db = self.connect_db()
949 DELETE FROM epg_timeline \
950 WHERE bctype = %s", \
952 except Exception, inst:
953 if not ((type(inst)==MySQLdb.ProgrammingError and (inst[0]==1007 or inst[0]==1146))or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
954 recdblist.addCommonlogEX("Error", "new_epg_timeline delete (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
957 CREATE TABLE epg_timeline \
960 channel VARCHAR(100) NOT NULL,\
966 category VARCHAR(100),\
967 UNIQUE unitv(bctype,channel,start,stop,title)\
969 except Exception, inst:
970 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
971 recdblist.addCommonlogEX("Error", "new_epg_timeline (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
974 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
975 db = self.connect_db()
977 INSERT IGNORE INTO epg_timeline \
978 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
979 (bctype, channel, start, stop, title, desc,longdesc,category))
982 def add_multi_epg_timeline(self, tvlists):
984 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
986 db = self.connect_db()
988 INSERT IGNORE INTO epg_timeline \
989 (bctype,channel,start,stop,title,exp,longexp,category) \
990 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
993 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
994 db = self.connect_db()
997 channel,title,start,stop,exp,longexp,category \
1004 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
1007 retall = db[1].fetchall()
1010 def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
1011 db = self.connect_db()
1014 epg_ch.chtxt,title,start,stop,exp,longexp,category \
1017 WHERE epg_ch.chtxt=epg_timeline.channel \
1026 ( title LIKE \'%%"+keyword+"%%\' ) \
1028 ( exp LIKE \'%%"+keyword+"%%\' ) \
1030 ( longexp LIKE \'%%"+keyword+"%%\' ) \
1032 dbcmd = db[1].execute(dbexe,(btime, etime))
1035 retall = db[1].fetchall()
1038 def new_in_auto_bayes_key(self):
1039 db = self.connect_db()
1041 db[1].execute('CREATE TABLE in_auto_bayes_key \
1043 keychar VARCHAR(10),\
1045 ratio_rec DECIMAL(32,14),\
1046 ratio_all DECIMAL(32,14),\
1047 UNIQUE unibayeskey(keychar,chtxt)\
1049 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
1050 except Exception, inst:
1051 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1052 recdblist.addCommonlogEX("Error", "new_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1055 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
1056 db = self.connect_db()
1057 ratio_rec=str(ratio_rec)
1058 ratio_all=str(ratio_all)
1060 INSERT IGNORE INTO in_auto_bayes_key \
1061 (keychar,chtxt,ratio_rec,ratio_all) \
1062 values (%s,%s,%s,%s)',\
1063 (key,chtxt,ratio_rec,ratio_all))
1065 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
1066 db = self.connect_db()
1067 add_rec_num=str(add_rec_num)
1068 add_all_num=str(add_all_num)
1070 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",\
1071 (add_rec_num,add_all_num,chtxt))
1073 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
1076 db = self.connect_db()
1078 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)\
1081 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1082 beforenum=str(beforenum)
1084 db = self.connect_db()
1086 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)\
1089 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1090 db = self.connect_db()
1091 beforenumf=beforenum
1092 beforenum=str(beforenum)
1093 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1094 VALUES (%s,%s,%s,%s)\
1095 ON DUPLICATE KEY UPDATE \
1096 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
1098 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
1100 list={key:addnum}のリスト
1102 beforenumf=beforenum
1103 beforenum=str(beforenum)
1104 db = self.connect_db()
1105 for i,j in list.items():
1106 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
1108 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1109 VALUES (%s,%s,%s,%s)\
1110 ON DUPLICATE KEY UPDATE \
1111 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
1112 except Exception, inst:
1113 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1114 recdblist.addCommonlogEX("Error", "change_multi_ratio_all_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1116 def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1117 beforenum=str(beforenum)
1119 db = self.connect_db()
1121 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)\
1124 def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1125 db = self.connect_db()
1126 beforenumf=beforenum
1127 beforenum=str(beforenum)
1128 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1129 VALUES (%s,%s,%s,%s)\
1130 ON DUPLICATE KEY UPDATE \
1131 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
1133 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
1134 beforenumf=beforenum
1135 beforenum=str(beforenum)
1136 db = self.connect_db()
1137 for i,j in list.items():
1138 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
1140 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1141 VALUES (%s,%s,%s,%s)\
1142 ON DUPLICATE KEY UPDATE \
1143 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
1144 except Exception, inst:
1145 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1146 recdblist.addCommonlogEX("Error", "change_multi_ratio_rec_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1148 def select_by_key_in_auto_bayes_key(self,key,chtxt):
1149 db = self.connect_db()
1150 dbexe = db[1].execute("\
1151 SELECT keychar,chtxt,ratio_rec,ratio_all \
1152 FROM in_auto_bayes_key \
1153 WHERE keychar = %s AND chtxt = %s", \
1157 dls = db[1].fetchall()
1163 def new_auto_timeline_keyword(self):
1164 db = self.connect_db()
1167 CREATE TABLE auto_timeline_keyword \
1169 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1171 title VARCHAR(100),\
1174 UNIQUE uni (chtxt,title,btime,etime)\
1176 except Exception, inst:
1177 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1178 recdblist.addCommonlogEX("Error", "new_auto_timeline_keyword (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1179 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
1180 db = self.connect_db()
1182 INSERT IGNORE into auto_timeline_keyword \
1183 (chtxt,title,btime,etime) \
1184 values (%s,%s,%s,%s)', \
1185 (chtxt, title, btime, etime))
1188 def delete_old_auto_timeline_keyword(self, dhour):
1189 db = self.connect_db()
1191 DELETE FROM auto_timeline_keyword \
1193 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1195 def new_auto_timeline_bayes(self):
1196 db = self.connect_db()
1199 CREATE TABLE auto_timeline_bayes \
1201 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1203 title VARCHAR(100),\
1207 UNIQUE uni (chtxt,title,btime,etime)\
1209 except Exception, inst:
1210 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1211 recdblist.addCommonlogEX("Error", "new_auto_timeline_bayes (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1212 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
1213 db = self.connect_db()
1215 INSERT IGNORE into auto_timeline_bayes \
1216 (chtxt,title,btime,etime,point) \
1217 values (%s,%s,%s,%s,%s)', \
1218 (chtxt, title, btime, etime,point))
1220 def delete_old_auto_timeline_bayes(self, dhour):
1221 db = self.connect_db()
1223 DELETE FROM auto_timeline_bayes \
1225 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1227 def update_db_to93(self):
1228 db = self.connect_db()
1229 self.drop_in_settings()
1230 self.new_in_settings()
1232 ALTER TABLE timeline ADD epgtitle VARCHAR(100),\
1233 ADD epgbtime DATETIME,\
1234 ADD epgetime DATETIME,\
1235 ADD epgduplicate TINYINT DEFAULT 0,\
1236 ADD epgchange TINYINT DEFAULT 0")
1238 ALTER TABLE in_status ADD version TINYINT")
1240 self.change_version_in_status("93")
1241 def update_db_93to94(self):
1242 db = self.connect_db()
1243 self.drop_in_settings()
1244 self.new_in_settings()
1246 ALTER TABLE timeline ADD counter TINYINT DEFAULT -1")
1248 self.change_version_in_status("94")
1249 def update_db_94to95(self):
1250 db = self.connect_db()
1251 self.drop_in_settings()
1252 self.new_in_settings()
1254 ALTER TABLE timeline ADD epgexp VARCHAR(200)")
1256 self.change_version_in_status("95")
1257 def update_db_95to96(self):
1258 db = self.connect_db()
1259 self.drop_in_settings()
1260 self.new_in_settings()
1262 self.change_version_in_status("96")
1263 def update_db_96to98(self):
1264 db = self.connect_db()
1265 self.drop_in_settings()
1266 self.new_in_settings()
1268 self.change_version_in_status("98")
1269 def update_db_98to100(self):
1270 ###ここで前のepg_chをバックアップしてchtxtの変換をする必要がある。
1271 self.drop_in_settings()
1272 self.new_in_settings()
1273 db = self.connect_db()
1275 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')")
1277 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'")
1279 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'")
1282 ALTER TABLE epg_ch DROP ontv")
1286 ALTER TABLE epg_ch ADD logo0 BLOB,\
1294 ALTER TABLE in_auto_jbk_key ADD auto TINYINT DEFAULT 0")
1296 ALTER TABLE in_auto_jbk_key ADD opt VARCHAR(20) DEFAULT \"\"")
1298 self.change_installed_in_status(1)#チャンネルスキャンをさせる
1299 self.change_version_in_status("100")
1300 def update_db_100to101(self):
1301 self.drop_in_settings()
1302 self.new_in_settings()
1304 db = self.connect_db()
1307 ALTER TABLE timeline ADD epgcategory VARCHAR(100)\
1312 self.change_installed_in_status(1)
1313 self.change_version_in_status("101")
1314 #self.change_installed_in_status()#チャンネルスキャンをさせる