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")
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,\
106 logoupdate DATETIME,\
107 logostatus TINYINT DEFAULT 2,\
110 except Exception, inst:
111 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
112 recdblist.addCommonlogEX("Error", "new_epg_ch (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
115 def add_epg_ch(self, bctype, chtxt, ch, csch, updatetime,logoupdate,scanupdate):
116 db = self.connect_db()
118 INSERT INTO epg_ch (bctype,chtxt,ch,csch,chname,updatetime,status,visible,logoupdate,scanupdate)\
119 VALUES (%s,%s,%s,%s,"",%s,%s,%s,%s,%s)', \
120 (bctype, chtxt, ch, csch, updatetime, "1","1",logoupdate,scanupdate))
122 def delete_all_epg_ch(self):
123 db = self.connect_db()
127 def select_by_chtxt_epg_ch(self, chtxt):
128 db = self.connect_db()
129 dbexe = db[1].execute("\
130 SELECT bctype,chtxt,ch,csch,updatetime,chname,status,visible,logoupdate,scanupdate \
132 WHERE chtxt LIKE %s", \
137 dls = db[1].fetchall()
141 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
142 r[8] = r[8].strftime("%Y-%m-%d %H:%M:%S")
143 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
146 def select_by_bctype_epg_ch(self, bctype):
147 db = self.connect_db()
148 dbexe = db[1].execute("\
149 SELECT bctype,chtxt,ch,csch,updatetime,status,chname,status,visible,logoupdate,scanupdate \
151 WHERE bctype = %s", \
156 dls = db[1].fetchall()
159 #recdblist.printutf8(dl)
161 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
162 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
163 r[10] = r[10].strftime("%Y-%m-%d %H:%M:%S")
167 def select_by_ch_epg_ch(self, ch):
168 db = self.connect_db()
169 dbexe = db[1].execute("\
171 bctype,chtxt,ch,csch,updatetime,chname,status,visible,logoupdate,scanupdate \
178 dls = db[1].fetchall()
182 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
183 r[8] = r[8].strftime("%Y-%m-%d %H:%M:%S")
184 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
187 def select_by_csch_epg_ch(self, csch):
188 db = self.connect_db()
189 dbexe = db[1].execute("\
191 bctype,chtxt,ch,csch,updatetime,chname,status,visible,logoupdate,scanupdate \
198 dls = db[1].fetchall()
202 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
203 r[8] = r[8].strftime("%Y-%m-%d %H:%M:%S")
204 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
207 def select_all_epg_ch(self):
208 db = self.connect_db()
209 dbexe = db[1].execute("\
210 SELECT bctype,chtxt,ch,csch,updatetime,chname,status,visible,logoupdate,scanupdate \
216 dls = db[1].fetchall()
220 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
221 r[8] = r[8].strftime("%Y-%m-%d %H:%M:%S")
222 r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
225 def change_visible_epg_ch(self,chtxt,visible):
226 db = self.connect_db()
228 UPDATE epg_ch SET visible=%s WHERE chtxt=%s",(visible,chtxt))
230 def change_logodata_epg_ch(self,chtxt,logonum,logodata):
231 db = self.connect_db()
233 UPDATE epg_ch SET logo"+str(logonum)+"=%s WHERE chtxt=%s",(logodata,chtxt))
235 def set_new_status(self,dhour):
236 db = self.connect_db()
237 dbexe = db[1].execute("UPDATE epg_ch \
241 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
246 def select_get_update_epg_ch(self, dhour):
247 db = self.connect_db()
248 dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
251 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
257 ORDER BY status DESC")
259 #recdblist.printutf8(dbexe)
261 ret = db[1].fetchall()
264 def select_get_updatelogo_epg_ch(self, dhour):
265 db = self.connect_db()
266 dbexe = db[1].execute("SELECT bctype,chtxt,logostatus FROM epg_ch \
269 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
275 ORDER BY status DESC")
277 #recdblist.printutf8(dbexe)
279 ret = db[1].fetchall()
282 def update_by_bctype_epg_ch(self, bctype):
283 db = self.connect_db()
289 WHERE bctype = %s", (bctype,))
292 def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
293 db = self.connect_db()
299 WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
301 def update_chname_by_chtxt_epg_ch(self,chtxt,chname):
302 db = self.connect_db()
307 WHERE chtxt = %s", (chname,chtxt))
309 def update_status_by_bctype_epg_ch(self, bctype, status):
310 db = self.connect_db()
316 WHERE bctype = %s", \
320 def update_logostatus_by_bctype_epg_ch(self,bctype,logostatus):
321 db = self.connect_db()
327 WHERE bctype = %s", \
328 (logostatus, bctype)\
331 def add_auto_proc_tmp(self,type,title,chtxt):
332 db = self.connect_db()
334 INSERT IGNORE into auto_proc_tmp \
336 values (%s,%s,%s)',(type,title,chtxt))
339 def new_auto_proc_tmp(self):
340 db = self.connect_db()
342 db[1].execute('drop table auto_proc_tmp')
343 except Exception, inst:
344 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
345 recdblist.addCommonlogEX("Error", "new_auto_proc_tmp drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
349 CREATE TABLE auto_proc_tmp \
352 title VARCHAR(100) PRIMARY KEY,\
354 UNIQUE unibayeskey(title)\
356 except Exception, inst:
357 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
358 recdblist.addCommonlogEX("Error", "new_auto_proc_tmp (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
361 def update_auto_proc(self):
362 db = self.connect_db()
364 db[1].execute('INSERT INTO auto_proc SELECT * FROM auto_proc_tmp')
365 except Exception, inst:
366 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
367 recdblist.addCommonlogEX("Error", "update_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
370 def new_auto_proc(self):
371 db = self.connect_db()
373 db[1].execute('drop table auto_proc')
374 except Exception, inst:
375 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
376 recdblist.addCommonlogEX("Error", "new_auto_proc drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
380 CREATE TABLE auto_proc \
383 title VARCHAR(100) PRIMARY KEY,\
385 UNIQUE unibayeskey(title)\
387 except Exception, inst:
388 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
389 recdblist.addCommonlogEX("Error", "new_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
392 def add_auto_proc(self,type,title,chtxt):
393 db = self.connect_db()
395 INSERT IGNORE into auto_proc \
397 values (%s,%s,%s)',(type,title,chtxt))
400 def drop_in_settings(self):
401 db = self.connect_db()
403 db[1].execute('drop table in_settings')
404 except Exception, inst:
405 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
406 recdblist.addCommonlogEX("Error", "drop_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
408 def new_in_settings(self):
409 db = self.connect_db()
412 CREATE TABLE in_settings \
416 auto_opt VARCHAR(20),\
417 auto_del_tmp TINYINT\
419 db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"H\",1)")
420 except Exception, inst:
421 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
422 recdblist.addCommonlogEX("Error", "new_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
425 def select_all_in_settings(self):
426 db = self.connect_db()
427 dbexe = db[1].execute("\
428 SELECT auto_jbk,auto_bayes,auto_del_tmp,auto_opt \
434 dls = db[1].fetchall()
444 def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
445 db = self.connect_db()
447 INSERT IGNORE into in_timeline_log \
448 (chtxt,title,btime,etime,opt,exp,longexp,category) \
449 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
450 ( chtxt, title, btime, etime, opt,exp,longexp,category))
453 def del_in_timeline_log(self, title="", chtxt="", btime=""):
457 db = self.connect_db()
459 DELETE FROM in_timeline_log \
460 WHERE title = %s AND chtxt = %s AND btime = %s", \
461 (title, chtxt, btime))
464 def new_in_timeline_log(self):
465 db = self.connect_db()
468 CREATE TABLE in_timeline_log \
470 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
478 category VARCHAR(100),\
479 UNIQUE uni (chtxt,title,btime,category)\
481 except Exception, inst:
482 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
483 recdblist.addCommonlogEX("Error", "new_in_timeline_log (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
486 def select_chtxt_by_title_timeline_log(self,title):
487 db = self.connect_db()
488 dbexe = db[1].execute("\
490 FROM in_timeline_log \
491 WHERE title LIKE \"%"+title+"%\"\
493 ORDER by sum(1) DESC limit 1")
494 retdb=db[1].fetchall()
501 def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt="" ,counter=-1):
502 db = self.connect_db()
504 INSERT IGNORE into timeline \
505 (type,chtxt,title,btime,etime,deltatime,deltaday,opt,counter) \
506 values (%s,%s,%s,%s,%s,%s,%s,%s,%s)', \
507 (type, chtxt, title, btime, etime, deltatime, deltaday, opt ,counter))
510 def update_epg_timeline(self,type,chtxt,title,btime,epgbtime,epgetime,epgtitle,epgexp,epgcategory):
511 db = self.connect_db()
514 SET epgbtime=%s,epgetime=%s,epgtitle=%s,epgexp=%s,epgcategory=%s \
515 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
516 (epgbtime,epgetime,epgtitle,epgexp,epgcategory,type, chtxt, title, btime))
519 def update_status_change_timeline(self,type,chtxt,title,btime,epgchange):
520 db = self.connect_db()
524 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
525 (epgchange , type, chtxt, title, btime))
528 def update_status_dup_timeline(self,type,chtxt,title,btime,epgduplicate):
529 db = self.connect_db()
532 SET epgduplicate =%s \
533 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
534 (epgduplicate , type, chtxt, title, btime))
537 def del_timeline(self, type="", title="", chtxt="", btime=""):
541 db = self.connect_db()
543 DELETE FROM timeline \
544 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
545 (type, title, chtxt, btime))
548 def select_all_timeline(self):
549 db = self.connect_db()
551 dbr = db[1].execute("\
552 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
554 dbl = db[1].fetchall()
557 recdata = self.getdic_timeline(dbl)
559 def select_bytime_timeline(self, dminutes):
560 db = self.connect_db()
562 #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 )")
563 dbr = db[1].execute("SELECT \
564 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
566 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
567 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
568 dbl = db[1].fetchall()
570 #recdblist.printutf8(dbl)
572 recdatum = self.getdic_timeline(dbl)
574 def select_by_name_time_timeline(self,title,btime,btime2):
575 db = self.connect_db()
577 #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 )")
578 dbr = db[1].execute("SELECT \
579 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
581 WHERE btime > %s AND \
582 btime < %s AND title = %s",(btime,btime2,title))
583 dbl = db[1].fetchall()
585 #recdblist.printutf8(dbl)
587 recdatum = self.getdic_timeline(dbl)
589 def select_bytime_all_timeline(self,btime,etime):
590 db = self.connect_db()
592 #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 )")
593 dbr = db[1].execute("SELECT \
594 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
596 WHERE btime >= %s AND \
597 etime <= %s",(btime,etime))
598 dbl = db[1].fetchall()
600 #recdblist.printutf8(dbl)
602 recdatum = self.getdic_timeline(dbl)
604 def select_byepgtime_all_timeline(self,epgbtime,epgetime):
605 db = self.connect_db()
607 #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 )")
608 dbr = db[1].execute("SELECT \
609 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
611 WHERE epgbtime >= %s AND \
612 epgetime <= %s",(epgbtime,epgetime))
613 dbl = db[1].fetchall()
615 #recdblist.printutf8(dbl)
617 recdatum=self.getdic_timeline(dbl)
619 def select_byepgtime_over_timeline(self,epgbtime,epgetime):
620 db = self.connect_db()
622 #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 )")
623 dbr = db[1].execute("SELECT \
624 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
626 WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"\
627 ,(epgbtime,epgetime))
628 dbl = db[1].fetchall()
630 #recdblist.printutf8(dbl)
632 recdatum=self.getdic_timeline(dbl)
634 def count_schedule_timeline(self, btime, etime):
637 return [te num,bs/cs num]
639 db = self.connect_db()
640 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
641 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
644 db[1].execute(dbexe, (btime, etime,btime,etime))
646 for typet, bctypet, chtxtt, titlet in dbl:
647 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
648 if bctypet.find("cs") > -1:
650 elif bctypet.find("bs") > -1:
652 elif bctypet.find("te") > -1:
656 def search_schedule_timeline(self,btime,etime):
659 return [(type,bctype,chtxt,title,btime,etime)]
661 db = self.connect_db()
662 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"
663 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
665 db[1].execute(dbexe, (btime, etime,btime,etime))
667 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
668 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
669 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
672 def count_epgschedule_timeline(self, epgbtime, epgetime):
675 return [te num,bs/cs num]
677 db = self.connect_db()
678 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
679 dbexe = dbexe + " WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"
682 db[1].execute(dbexe, (epgbtime, epgetime))
684 for typet, bctypet, chtxtt, titlet in dbl:
685 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
686 if bctypet.find("cs") > -1:
688 elif bctypet.find("bs") > -1:
690 elif bctypet.find("te") > -1:
694 def search_epgschedule_timeline(self,epgbtime,epgetime):
697 return [(type,bctype,chtxt,title,btime,etime)]
699 db = self.connect_db()
700 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"
701 dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )"
703 db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime))
705 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
706 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
707 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
710 def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
711 db = self.connect_db()
713 #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 )")
714 dbr = db[1].execute("SELECT \
715 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
717 WHERE btime > %s AND \
719 AND chtxt=%s ",(btime,etime,chtxt))
720 dbl = db[1].fetchall()
722 #recdblist.printutf8(dbl)
724 recdatum = self.getdic_timeline(dbl)
726 def getdic_timeline(self,timelinelists):
728 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt ,epgbtimet , epgetimet ,epgtitlet ,epgduplicatet ,epgchanget ,countert in timelinelists:
733 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
734 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
739 ret['epgbtime'] = epgbtimet.strftime("%Y-%m-%d %H:%M:%S")
740 ret['epgetime'] = epgetimet.strftime("%Y-%m-%d %H:%M:%S")
742 ret['epgbtime'] = "2010-01-01 00:00:00"
743 ret['epgetime'] = "2010-01-01 00:00:00"
744 ret['epgtitle'] = epgtitlet
745 ret['epgduplicate'] = epgduplicatet
746 ret['epgchange'] = epgchanget
747 if deltatime == None or deltatime == "":
749 if deltaday == None or deltaday == "":
751 if typet == recdblist.REC_KEYWORD:
752 ret['deltatime'] = deltatime
753 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
754 ret['deltatime'] = deltatime
755 ret['deltaday'] = deltaday
757 ret['counter'] = int(countert)
762 def delete_old_timeline(self, dhour):
763 db = self.connect_db()
765 DELETE FROM timeline \
767 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
770 def new_in_auto_jbk_key(self):
771 db = self.connect_db()
774 CREATE TABLE in_auto_jbk_key \
776 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
777 keyword VARCHAR(200),\
778 auto TINYINT DEFAULT 0,\
780 UNIQUE unijbk (keyword)\
782 except Exception, inst:
783 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
784 recdblist.addCommonlogEX("Error", "new_in_auto_jbk_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
786 def add_in_auto_jbk_key(self,key):
787 db = self.connect_db()
789 INSERT IGNORE into in_auto_jbk_key \
795 def select_all_in_auto_jbk_key(self):
796 db = self.connect_db()
797 dbexe = db[1].execute("\
798 SELECT keyword,auto,opt \
799 FROM in_auto_jbk_key \
803 ret = db[1].fetchall()
806 def drop_in_status(self):
807 db = self.connect_db()
809 db[1].execute('drop table in_status')
810 except Exception, inst:
811 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
812 recdblist.addCommonlogEX("Error", "drop_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
814 def new_in_status(self):
815 db = self.connect_db()
818 CREATE TABLE in_status \
820 ts2avi TINYINT DEFAULT 0,\
821 terec TINYINT DEFAULT 0,\
822 bscsrec TINYINT DEFAULT 0,\
823 b252ts TINYINT DEFAULT 0,\
824 installed TINYINT DEFAULT 0,\
827 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0,0)")
828 except Exception, inst:
829 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
830 recdblist.addCommonlogEX("Error", "new_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
832 def select_all_in_status(self):
833 db = self.connect_db()
834 dbexe = db[1].execute("\
835 SELECT ts2avi,terec,bscsrec,b252ts \
841 dls = db[1].fetchall()
851 def select_version_in_status(self):
852 db = self.connect_db()
855 dbexe = db[1].execute("\
860 dls = db[1].fetchall()
864 version=int(str(r[0]))
865 except Exception, inst:
866 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
867 recdblist.addCommonlogEX("Error", "select_version_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
869 def change_version_in_status(self,version):
870 db = self.connect_db()
872 UPDATE in_status SET version=%s",str(version))
874 def change_ts2avi_in_status(self,i):
879 db = self.connect_db()
881 UPDATE in_status SET ts2avi=ts2avi+%s",i)
883 def change_terec_in_status(self,i):
888 db = self.connect_db()
890 UPDATE in_status SET terec=terec+%s",i)
892 def change_bscsrec_in_status(self,i):
897 db = self.connect_db()
899 UPDATE in_status SET bscsrec=bscsrec+%s",i)
901 def change_b252ts_in_status(self,i):
906 db = self.connect_db()
908 UPDATE in_status SET b252ts=b252ts+%s",i)
910 def select_installed_in_status(self):
911 db = self.connect_db()
912 dbexe = db[1].execute("\
913 SELECT ts2avi,terec,bscsrec,b252ts,installed \
919 dls = db[1].fetchall()
925 def change_chscaned_in_status(self):
929 db = self.connect_db()
931 UPDATE in_status SET installed=2")
933 def change_installed_in_status(self,num=1):
937 db = self.connect_db()
939 UPDATE in_status SET installed=%s",(num,))
941 def new_epg_timeline(self, bctype):
942 db = self.connect_db()
945 DELETE FROM epg_timeline \
946 WHERE bctype = %s", \
948 except Exception, inst:
949 if not ((type(inst)==MySQLdb.ProgrammingError and (inst[0]==1007 or inst[0]==1146))or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
950 recdblist.addCommonlogEX("Error", "new_epg_timeline delete (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
953 CREATE TABLE epg_timeline \
956 channel VARCHAR(100) NOT NULL,\
962 category VARCHAR(100),\
963 UNIQUE unitv(bctype,channel,start,stop,title)\
965 except Exception, inst:
966 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
967 recdblist.addCommonlogEX("Error", "new_epg_timeline (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
970 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
971 db = self.connect_db()
973 INSERT IGNORE INTO epg_timeline \
974 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
975 (bctype, channel, start, stop, title, desc,longdesc,category))
978 def add_multi_epg_timeline(self, tvlists):
980 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
982 db = self.connect_db()
984 INSERT IGNORE INTO epg_timeline \
985 (bctype,channel,start,stop,title,exp,longexp,category) \
986 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
989 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
990 db = self.connect_db()
993 channel,title,start,stop,exp,longexp,category \
1000 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
1003 retall = db[1].fetchall()
1006 def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
1007 db = self.connect_db()
1010 epg_ch.chtxt,title,start,stop,exp,longexp,category \
1013 WHERE epg_ch.chtxt=epg_timeline.channel \
1022 ( title LIKE \'%%"+keyword+"%%\' ) \
1024 ( exp LIKE \'%%"+keyword+"%%\' ) \
1026 ( longexp LIKE \'%%"+keyword+"%%\' ) \
1028 dbcmd = db[1].execute(dbexe,(btime, etime))
1031 retall = db[1].fetchall()
1034 def new_in_auto_bayes_key(self):
1035 db = self.connect_db()
1037 db[1].execute('CREATE TABLE in_auto_bayes_key \
1039 keychar VARCHAR(10),\
1041 ratio_rec DECIMAL(32,14),\
1042 ratio_all DECIMAL(32,14),\
1043 UNIQUE unibayeskey(keychar,chtxt)\
1045 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
1046 except Exception, inst:
1047 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1048 recdblist.addCommonlogEX("Error", "new_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1051 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
1052 db = self.connect_db()
1053 ratio_rec=str(ratio_rec)
1054 ratio_all=str(ratio_all)
1056 INSERT IGNORE INTO in_auto_bayes_key \
1057 (keychar,chtxt,ratio_rec,ratio_all) \
1058 values (%s,%s,%s,%s)',\
1059 (key,chtxt,ratio_rec,ratio_all))
1061 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
1062 db = self.connect_db()
1063 add_rec_num=str(add_rec_num)
1064 add_all_num=str(add_all_num)
1066 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",\
1067 (add_rec_num,add_all_num,chtxt))
1069 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
1072 db = self.connect_db()
1074 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)\
1077 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1078 beforenum=str(beforenum)
1080 db = self.connect_db()
1082 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)\
1085 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1086 db = self.connect_db()
1087 beforenumf=beforenum
1088 beforenum=str(beforenum)
1089 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1090 VALUES (%s,%s,%s,%s)\
1091 ON DUPLICATE KEY UPDATE \
1092 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
1094 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
1096 list={key:addnum}のリスト
1098 beforenumf=beforenum
1099 beforenum=str(beforenum)
1100 db = self.connect_db()
1101 for i,j in list.items():
1102 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
1104 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1105 VALUES (%s,%s,%s,%s)\
1106 ON DUPLICATE KEY UPDATE \
1107 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
1108 except Exception, inst:
1109 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1110 recdblist.addCommonlogEX("Error", "change_multi_ratio_all_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1112 def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1113 beforenum=str(beforenum)
1115 db = self.connect_db()
1117 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)\
1120 def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1121 db = self.connect_db()
1122 beforenumf=beforenum
1123 beforenum=str(beforenum)
1124 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1125 VALUES (%s,%s,%s,%s)\
1126 ON DUPLICATE KEY UPDATE \
1127 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
1129 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
1130 beforenumf=beforenum
1131 beforenum=str(beforenum)
1132 db = self.connect_db()
1133 for i,j in list.items():
1134 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
1136 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1137 VALUES (%s,%s,%s,%s)\
1138 ON DUPLICATE KEY UPDATE \
1139 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
1140 except Exception, inst:
1141 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1142 recdblist.addCommonlogEX("Error", "change_multi_ratio_rec_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1144 def select_by_key_in_auto_bayes_key(self,key,chtxt):
1145 db = self.connect_db()
1146 dbexe = db[1].execute("\
1147 SELECT keychar,chtxt,ratio_rec,ratio_all \
1148 FROM in_auto_bayes_key \
1149 WHERE keychar = %s AND chtxt = %s", \
1153 dls = db[1].fetchall()
1159 def new_auto_timeline_keyword(self):
1160 db = self.connect_db()
1163 CREATE TABLE auto_timeline_keyword \
1165 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1167 title VARCHAR(100),\
1170 UNIQUE uni (chtxt,title,btime,etime)\
1172 except Exception, inst:
1173 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1174 recdblist.addCommonlogEX("Error", "new_auto_timeline_keyword (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1175 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
1176 db = self.connect_db()
1178 INSERT IGNORE into auto_timeline_keyword \
1179 (chtxt,title,btime,etime) \
1180 values (%s,%s,%s,%s)', \
1181 (chtxt, title, btime, etime))
1184 def delete_old_auto_timeline_keyword(self, dhour):
1185 db = self.connect_db()
1187 DELETE FROM auto_timeline_keyword \
1189 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1191 def new_auto_timeline_bayes(self):
1192 db = self.connect_db()
1195 CREATE TABLE auto_timeline_bayes \
1197 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1199 title VARCHAR(100),\
1203 UNIQUE uni (chtxt,title,btime,etime)\
1205 except Exception, inst:
1206 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1207 recdblist.addCommonlogEX("Error", "new_auto_timeline_bayes (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1208 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
1209 db = self.connect_db()
1211 INSERT IGNORE into auto_timeline_bayes \
1212 (chtxt,title,btime,etime,point) \
1213 values (%s,%s,%s,%s,%s)', \
1214 (chtxt, title, btime, etime,point))
1216 def delete_old_auto_timeline_bayes(self, dhour):
1217 db = self.connect_db()
1219 DELETE FROM auto_timeline_bayes \
1221 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1223 def update_db_to93(self):
1224 db = self.connect_db()
1225 self.drop_in_settings()
1226 self.new_in_settings()
1228 ALTER TABLE timeline ADD epgtitle VARCHAR(100),\
1229 ADD epgbtime DATETIME,\
1230 ADD epgetime DATETIME,\
1231 ADD epgduplicate TINYINT DEFAULT 0,\
1232 ADD epgchange TINYINT DEFAULT 0")
1234 ALTER TABLE in_status ADD version TINYINT")
1236 self.change_version_in_status("93")
1237 def update_db_93to94(self):
1238 db = self.connect_db()
1239 self.drop_in_settings()
1240 self.new_in_settings()
1242 ALTER TABLE timeline ADD counter TINYINT DEFAULT -1")
1244 self.change_version_in_status("94")
1245 def update_db_94to95(self):
1246 db = self.connect_db()
1247 self.drop_in_settings()
1248 self.new_in_settings()
1250 ALTER TABLE timeline ADD epgexp VARCHAR(200)")
1252 self.change_version_in_status("95")
1253 def update_db_95to96(self):
1254 db = self.connect_db()
1255 self.drop_in_settings()
1256 self.new_in_settings()
1258 self.change_version_in_status("96")
1259 def update_db_96to98(self):
1260 db = self.connect_db()
1261 self.drop_in_settings()
1262 self.new_in_settings()
1264 self.change_version_in_status("98")
1265 def update_db_98to100(self):
1266 ###ここで前のepg_chをバックアップしてchtxtの変換をする必要がある。
1267 self.drop_in_settings()
1268 self.new_in_settings()
1269 db = self.connect_db()
1271 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')")
1273 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'")
1275 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'")
1278 ALTER TABLE epg_ch DROP ontv")
1282 ALTER TABLE epg_ch ADD logo0 BLOB,\
1290 ALTER TABLE in_auto_jbk_key ADD auto TINYINT DEFAULT 0")
1292 ALTER TABLE in_auto_jbk_key ADD opt VARCHAR(20) DEFAULT \"\"")
1294 self.change_installed_in_status(1)#チャンネルスキャンをさせる
1295 self.change_version_in_status("100")
1296 def update_db_100to101(self):
1297 self.drop_in_settings()
1298 self.new_in_settings()
1300 db = self.connect_db()
1303 ALTER TABLE timeline ADD epgcategory VARCHAR(100)\
1308 self.change_installed_in_status(1)
1309 self.change_version_in_status("101")
1310 #self.change_installed_in_status()#チャンネルスキャンをさせる