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,\
106 except Exception, inst:
107 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
108 recdblist.addCommonlogEX("Error", "new_epg_ch (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
111 def add_epg_ch(self, bctype, chtxt, ch, csch, updatetime):
112 db = self.connect_db()
114 INSERT INTO epg_ch (bctype,chtxt,ch,csch,chname,updatetime,status,visible)\
115 VALUES (%s,%s,%s,%s,"",%s,%s,%s)', \
116 (bctype, chtxt, ch, csch, updatetime, "1","1"))
118 def delete_all_epg_ch(self):
119 db = self.connect_db()
123 def select_by_chtxt_epg_ch(self, chtxt):
124 db = self.connect_db()
125 dbexe = db[1].execute("\
126 SELECT bctype,chtxt,ch,csch,updatetime,chname,status,visible \
128 WHERE chtxt LIKE %s", \
133 dls = db[1].fetchall()
137 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
140 def select_by_bctype_epg_ch(self, bctype):
141 db = self.connect_db()
142 dbexe = db[1].execute("\
143 SELECT bctype,chtxt,ch,csch,updatetime,status,chname,status,visible \
145 WHERE bctype = %s", \
150 dls = db[1].fetchall()
153 #recdblist.printutf8(dl)
155 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
159 def select_by_ch_epg_ch(self, ch):
160 db = self.connect_db()
161 dbexe = db[1].execute("\
163 bctype,chtxt,ch,csch,updatetime,chname,status,visible \
170 dls = db[1].fetchall()
174 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
177 def select_by_csch_epg_ch(self, csch):
178 db = self.connect_db()
179 dbexe = db[1].execute("\
181 bctype,chtxt,ch,csch,updatetime,chname,status,visible \
188 dls = db[1].fetchall()
192 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
195 def select_all_epg_ch(self):
196 db = self.connect_db()
197 dbexe = db[1].execute("\
198 SELECT bctype,chtxt,ch,csch,updatetime,chname,status,visible \
204 dls = db[1].fetchall()
208 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
211 def change_visible_epg_ch(self,chtxt,visible):
212 db = self.connect_db()
214 UPDATE epg_ch SET visible=%s WHERE chtxt=%s",(visible,chtxt))
216 def change_logodata_epg_ch(self,chtxt,logonum,logodata):
217 db = self.connect_db()
219 UPDATE epg_ch SET logo"+str(logonum)+"=%s WHERE chtxt=%s",(logodata,chtxt))
221 def set_new_status(self,dhour):
222 db = self.connect_db()
223 dbexe = db[1].execute("UPDATE epg_ch \
227 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
232 def select_get_update_epg_ch(self, dhour):
233 db = self.connect_db()
234 dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
237 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
243 ORDER BY status DESC")
245 #recdblist.printutf8(dbexe)
247 ret = db[1].fetchall()
250 def update_by_bctype_epg_ch(self, bctype):
251 db = self.connect_db()
257 WHERE bctype = %s", (bctype,))
259 def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
260 db = self.connect_db()
266 WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
268 def update_status_by_bctype_epg_ch(self, bctype, status):
269 db = self.connect_db()
275 WHERE bctype = %s", \
279 def update_chname_by_chtxt_epg_ch(self,chtxt,chname):
280 db = self.connect_db()
289 def add_auto_proc_tmp(self,type,title,chtxt):
290 db = self.connect_db()
292 INSERT IGNORE into auto_proc_tmp \
294 values (%s,%s,%s)',(type,title,chtxt))
297 def new_auto_proc_tmp(self):
298 db = self.connect_db()
300 db[1].execute('drop table auto_proc_tmp')
301 except Exception, inst:
302 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
303 recdblist.addCommonlogEX("Error", "new_auto_proc_tmp drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
307 CREATE TABLE auto_proc_tmp \
310 title VARCHAR(100) PRIMARY KEY,\
312 UNIQUE unibayeskey(title)\
314 except Exception, inst:
315 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
316 recdblist.addCommonlogEX("Error", "new_auto_proc_tmp (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
319 def update_auto_proc(self):
320 db = self.connect_db()
322 db[1].execute('INSERT INTO auto_proc SELECT * FROM auto_proc_tmp')
323 except Exception, inst:
324 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
325 recdblist.addCommonlogEX("Error", "update_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
328 def new_auto_proc(self):
329 db = self.connect_db()
331 db[1].execute('drop table auto_proc')
332 except Exception, inst:
333 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
334 recdblist.addCommonlogEX("Error", "new_auto_proc drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
338 CREATE TABLE auto_proc \
341 title VARCHAR(100) PRIMARY KEY,\
343 UNIQUE unibayeskey(title)\
345 except Exception, inst:
346 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
347 recdblist.addCommonlogEX("Error", "new_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
350 def add_auto_proc(self,type,title,chtxt):
351 db = self.connect_db()
353 INSERT IGNORE into auto_proc \
355 values (%s,%s,%s)',(type,title,chtxt))
358 def drop_in_settings(self):
359 db = self.connect_db()
361 db[1].execute('drop table in_settings')
362 except Exception, inst:
363 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
364 recdblist.addCommonlogEX("Error", "drop_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
366 def new_in_settings(self):
367 db = self.connect_db()
370 CREATE TABLE in_settings \
374 auto_opt VARCHAR(20),\
375 auto_del_tmp TINYINT\
377 db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"H\",1)")
378 except Exception, inst:
379 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
380 recdblist.addCommonlogEX("Error", "new_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
383 def select_all_in_settings(self):
384 db = self.connect_db()
385 dbexe = db[1].execute("\
386 SELECT auto_jbk,auto_bayes,auto_del_tmp,auto_opt \
392 dls = db[1].fetchall()
402 def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
403 db = self.connect_db()
405 INSERT IGNORE into in_timeline_log \
406 (chtxt,title,btime,etime,opt,exp,longexp,category) \
407 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
408 ( chtxt, title, btime, etime, opt,exp,longexp,category))
411 def del_in_timeline_log(self, title="", chtxt="", btime=""):
415 db = self.connect_db()
417 DELETE FROM in_timeline_log \
418 WHERE title = %s AND chtxt = %s AND btime = %s", \
419 (title, chtxt, btime))
422 def new_in_timeline_log(self):
423 db = self.connect_db()
426 CREATE TABLE in_timeline_log \
428 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
436 category VARCHAR(100),\
437 UNIQUE uni (chtxt,title,btime,category)\
439 except Exception, inst:
440 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
441 recdblist.addCommonlogEX("Error", "new_in_timeline_log (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
444 def select_chtxt_by_title_timeline_log(self,title):
445 db = self.connect_db()
446 dbexe = db[1].execute("\
448 FROM in_timeline_log \
449 WHERE title LIKE \"%"+title+"%\"\
451 ORDER by sum(1) DESC limit 1")
452 retdb=db[1].fetchall()
459 def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt="" ,counter=-1):
460 db = self.connect_db()
462 INSERT IGNORE into timeline \
463 (type,chtxt,title,btime,etime,deltatime,deltaday,opt,counter) \
464 values (%s,%s,%s,%s,%s,%s,%s,%s,%s)', \
465 (type, chtxt, title, btime, etime, deltatime, deltaday, opt ,counter))
468 def update_epg_timeline(self,type,chtxt,title,btime,epgbtime,epgetime,epgtitle,epgexp,epgcategory):
469 db = self.connect_db()
472 SET epgbtime=%s,epgetime=%s,epgtitle=%s,epgexp=%s,epgcategory=%s \
473 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
474 (epgbtime,epgetime,epgtitle,epgexp,epgcategory,type, chtxt, title, btime))
477 def update_status_change_timeline(self,type,chtxt,title,btime,epgchange):
478 db = self.connect_db()
482 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
483 (epgchange , type, chtxt, title, btime))
486 def update_status_dup_timeline(self,type,chtxt,title,btime,epgduplicate):
487 db = self.connect_db()
490 SET epgduplicate =%s \
491 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
492 (epgduplicate , type, chtxt, title, btime))
495 def del_timeline(self, type="", title="", chtxt="", btime=""):
499 db = self.connect_db()
501 DELETE FROM timeline \
502 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
503 (type, title, chtxt, btime))
506 def select_all_timeline(self):
507 db = self.connect_db()
509 dbr = db[1].execute("\
510 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
512 dbl = db[1].fetchall()
515 recdata = self.getdic_timeline(dbl)
517 def select_bytime_timeline(self, dminutes):
518 db = self.connect_db()
520 #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 )")
521 dbr = db[1].execute("SELECT \
522 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
524 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
525 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
526 dbl = db[1].fetchall()
528 #recdblist.printutf8(dbl)
530 recdatum = self.getdic_timeline(dbl)
532 def select_by_name_time_timeline(self,title,btime,btime2):
533 db = self.connect_db()
535 #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 )")
536 dbr = db[1].execute("SELECT \
537 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
539 WHERE btime > %s AND \
540 btime < %s AND title = %s",(btime,btime2,title))
541 dbl = db[1].fetchall()
543 #recdblist.printutf8(dbl)
545 recdatum = self.getdic_timeline(dbl)
547 def select_bytime_all_timeline(self,btime,etime):
548 db = self.connect_db()
550 #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 )")
551 dbr = db[1].execute("SELECT \
552 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
554 WHERE btime >= %s AND \
555 etime <= %s",(btime,etime))
556 dbl = db[1].fetchall()
558 #recdblist.printutf8(dbl)
560 recdatum = self.getdic_timeline(dbl)
562 def select_byepgtime_all_timeline(self,epgbtime,epgetime):
563 db = self.connect_db()
565 #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 )")
566 dbr = db[1].execute("SELECT \
567 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
569 WHERE epgbtime >= %s AND \
570 epgetime <= %s",(epgbtime,epgetime))
571 dbl = db[1].fetchall()
573 #recdblist.printutf8(dbl)
575 recdatum=self.getdic_timeline(dbl)
577 def select_byepgtime_over_timeline(self,epgbtime,epgetime):
578 db = self.connect_db()
580 #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 )")
581 dbr = db[1].execute("SELECT \
582 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
584 WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"\
585 ,(epgbtime,epgetime))
586 dbl = db[1].fetchall()
588 #recdblist.printutf8(dbl)
590 recdatum=self.getdic_timeline(dbl)
592 def count_schedule_timeline(self, btime, etime):
595 return [te num,bs/cs num]
597 db = self.connect_db()
598 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
599 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
602 db[1].execute(dbexe, (btime, etime,btime,etime))
604 for typet, bctypet, chtxtt, titlet in dbl:
605 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
606 if bctypet.find("cs") > -1:
608 elif bctypet.find("bs") > -1:
610 elif bctypet.find("te") > -1:
614 def search_schedule_timeline(self,btime,etime):
617 return [(type,bctype,chtxt,title,btime,etime)]
619 db = self.connect_db()
620 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"
621 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
623 db[1].execute(dbexe, (btime, etime,btime,etime))
625 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
626 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
627 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
630 def count_epgschedule_timeline(self, epgbtime, epgetime):
633 return [te num,bs/cs num]
635 db = self.connect_db()
636 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
637 dbexe = dbexe + " WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"
640 db[1].execute(dbexe, (epgbtime, epgetime))
642 for typet, bctypet, chtxtt, titlet in dbl:
643 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
644 if bctypet.find("cs") > -1:
646 elif bctypet.find("bs") > -1:
648 elif bctypet.find("te") > -1:
652 def search_epgschedule_timeline(self,epgbtime,epgetime):
655 return [(type,bctype,chtxt,title,btime,etime)]
657 db = self.connect_db()
658 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"
659 dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )"
661 db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime))
663 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
664 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
665 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
668 def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
669 db = self.connect_db()
671 #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 )")
672 dbr = db[1].execute("SELECT \
673 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
675 WHERE btime > %s AND \
677 AND chtxt=%s ",(btime,etime,chtxt))
678 dbl = db[1].fetchall()
680 #recdblist.printutf8(dbl)
682 recdatum = self.getdic_timeline(dbl)
684 def getdic_timeline(self,timelinelists):
686 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt ,epgbtimet , epgetimet ,epgtitlet ,epgduplicatet ,epgchanget ,countert in timelinelists:
691 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
692 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
697 ret['epgbtime'] = epgbtimet.strftime("%Y-%m-%d %H:%M:%S")
698 ret['epgetime'] = epgetimet.strftime("%Y-%m-%d %H:%M:%S")
700 ret['epgbtime'] = "2010-01-01 00:00:00"
701 ret['epgetime'] = "2010-01-01 00:00:00"
702 ret['epgtitle'] = epgtitlet
703 ret['epgduplicate'] = epgduplicatet
704 ret['epgchange'] = epgchanget
705 if deltatime == None or deltatime == "":
707 if deltaday == None or deltaday == "":
709 if typet == recdblist.REC_KEYWORD:
710 ret['deltatime'] = deltatime
711 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
712 ret['deltatime'] = deltatime
713 ret['deltaday'] = deltaday
715 ret['counter'] = int(countert)
720 def delete_old_timeline(self, dhour):
721 db = self.connect_db()
723 DELETE FROM timeline \
725 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
728 def new_in_auto_jbk_key(self):
729 db = self.connect_db()
732 CREATE TABLE in_auto_jbk_key \
734 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
735 keyword VARCHAR(200),\
736 auto TINYINT DEFAULT 0,\
738 UNIQUE unijbk (keyword)\
740 except Exception, inst:
741 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
742 recdblist.addCommonlogEX("Error", "new_in_auto_jbk_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
744 def add_in_auto_jbk_key(self,key):
745 db = self.connect_db()
747 INSERT IGNORE into in_auto_jbk_key \
753 def select_all_in_auto_jbk_key(self):
754 db = self.connect_db()
755 dbexe = db[1].execute("\
756 SELECT keyword,auto,opt \
757 FROM in_auto_jbk_key \
761 ret = db[1].fetchall()
764 def drop_in_status(self):
765 db = self.connect_db()
767 db[1].execute('drop table in_status')
768 except Exception, inst:
769 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
770 recdblist.addCommonlogEX("Error", "drop_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
772 def new_in_status(self):
773 db = self.connect_db()
776 CREATE TABLE in_status \
778 ts2avi TINYINT DEFAULT 0,\
779 terec TINYINT DEFAULT 0,\
780 bscsrec TINYINT DEFAULT 0,\
781 b252ts TINYINT DEFAULT 0,\
782 installed TINYINT DEFAULT 0,\
785 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0,0)")
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_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
790 def select_all_in_status(self):
791 db = self.connect_db()
792 dbexe = db[1].execute("\
793 SELECT ts2avi,terec,bscsrec,b252ts \
799 dls = db[1].fetchall()
809 def select_version_in_status(self):
810 db = self.connect_db()
813 dbexe = db[1].execute("\
818 dls = db[1].fetchall()
822 version=int(str(r[0]))
823 except Exception, inst:
824 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
825 recdblist.addCommonlogEX("Error", "select_version_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
827 def change_version_in_status(self,version):
828 db = self.connect_db()
830 UPDATE in_status SET version=%s",str(version))
832 def change_ts2avi_in_status(self,i):
837 db = self.connect_db()
839 UPDATE in_status SET ts2avi=ts2avi+%s",i)
841 def change_terec_in_status(self,i):
846 db = self.connect_db()
848 UPDATE in_status SET terec=terec+%s",i)
850 def change_bscsrec_in_status(self,i):
855 db = self.connect_db()
857 UPDATE in_status SET bscsrec=bscsrec+%s",i)
859 def change_b252ts_in_status(self,i):
864 db = self.connect_db()
866 UPDATE in_status SET b252ts=b252ts+%s",i)
868 def select_installed_in_status(self):
869 db = self.connect_db()
870 dbexe = db[1].execute("\
871 SELECT ts2avi,terec,bscsrec,b252ts,installed \
877 dls = db[1].fetchall()
883 def change_installed_in_status(self):
887 db = self.connect_db()
889 UPDATE in_status SET installed=1")
891 def change_chscaned_in_status(self):
895 db = self.connect_db()
897 UPDATE in_status SET installed=2")
899 def new_epg_timeline(self, bctype):
900 db = self.connect_db()
903 DELETE FROM epg_timeline \
904 WHERE bctype = %s", \
906 except Exception, inst:
907 if not ((type(inst)==MySQLdb.ProgrammingError and (inst[0]==1007 or inst[0]==1146))or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
908 recdblist.addCommonlogEX("Error", "new_epg_timeline delete (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
911 CREATE TABLE epg_timeline \
914 channel VARCHAR(100) NOT NULL,\
920 category VARCHAR(100),\
921 UNIQUE unitv(bctype,channel,start,stop,title)\
923 except Exception, inst:
924 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
925 recdblist.addCommonlogEX("Error", "new_epg_timeline (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
928 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
929 db = self.connect_db()
931 INSERT IGNORE INTO epg_timeline \
932 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
933 (bctype, channel, start, stop, title, desc,longdesc,category))
936 def add_multi_epg_timeline(self, tvlists):
938 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
940 db = self.connect_db()
942 INSERT IGNORE INTO epg_timeline \
943 (bctype,channel,start,stop,title,exp,longexp,category) \
944 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
947 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
948 db = self.connect_db()
951 channel,title,start,stop,exp,longexp,category \
958 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
961 retall = db[1].fetchall()
964 def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
965 db = self.connect_db()
968 epg_ch.chtxt,title,start,stop,exp,longexp,category \
971 WHERE epg_ch.chtxt=epg_timeline.channel \
980 ( title LIKE \'%%"+keyword+"%%\' ) \
982 ( exp LIKE \'%%"+keyword+"%%\' ) \
984 ( longexp LIKE \'%%"+keyword+"%%\' ) \
986 dbcmd = db[1].execute(dbexe,(btime, etime))
989 retall = db[1].fetchall()
992 def new_in_auto_bayes_key(self):
993 db = self.connect_db()
995 db[1].execute('CREATE TABLE in_auto_bayes_key \
997 keychar VARCHAR(10),\
999 ratio_rec DECIMAL(32,14),\
1000 ratio_all DECIMAL(32,14),\
1001 UNIQUE unibayeskey(keychar,chtxt)\
1003 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
1004 except Exception, inst:
1005 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1006 recdblist.addCommonlogEX("Error", "new_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1009 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
1010 db = self.connect_db()
1011 ratio_rec=str(ratio_rec)
1012 ratio_all=str(ratio_all)
1014 INSERT IGNORE INTO in_auto_bayes_key \
1015 (keychar,chtxt,ratio_rec,ratio_all) \
1016 values (%s,%s,%s,%s)',\
1017 (key,chtxt,ratio_rec,ratio_all))
1019 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
1020 db = self.connect_db()
1021 add_rec_num=str(add_rec_num)
1022 add_all_num=str(add_all_num)
1024 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",\
1025 (add_rec_num,add_all_num,chtxt))
1027 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
1030 db = self.connect_db()
1032 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)\
1035 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1036 beforenum=str(beforenum)
1038 db = self.connect_db()
1040 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)\
1043 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1044 db = self.connect_db()
1045 beforenumf=beforenum
1046 beforenum=str(beforenum)
1047 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1048 VALUES (%s,%s,%s,%s)\
1049 ON DUPLICATE KEY UPDATE \
1050 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
1052 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
1054 list={key:addnum}のリスト
1056 beforenumf=beforenum
1057 beforenum=str(beforenum)
1058 db = self.connect_db()
1059 for i,j in list.items():
1060 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
1062 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1063 VALUES (%s,%s,%s,%s)\
1064 ON DUPLICATE KEY UPDATE \
1065 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
1066 except Exception, inst:
1067 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1068 recdblist.addCommonlogEX("Error", "change_multi_ratio_all_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1070 def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1071 beforenum=str(beforenum)
1073 db = self.connect_db()
1075 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)\
1078 def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1079 db = self.connect_db()
1080 beforenumf=beforenum
1081 beforenum=str(beforenum)
1082 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1083 VALUES (%s,%s,%s,%s)\
1084 ON DUPLICATE KEY UPDATE \
1085 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
1087 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
1088 beforenumf=beforenum
1089 beforenum=str(beforenum)
1090 db = self.connect_db()
1091 for i,j in list.items():
1092 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
1094 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1095 VALUES (%s,%s,%s,%s)\
1096 ON DUPLICATE KEY UPDATE \
1097 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
1098 except Exception, inst:
1099 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1100 recdblist.addCommonlogEX("Error", "change_multi_ratio_rec_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1102 def select_by_key_in_auto_bayes_key(self,key,chtxt):
1103 db = self.connect_db()
1104 dbexe = db[1].execute("\
1105 SELECT keychar,chtxt,ratio_rec,ratio_all \
1106 FROM in_auto_bayes_key \
1107 WHERE keychar = %s AND chtxt = %s", \
1111 dls = db[1].fetchall()
1117 def new_auto_timeline_keyword(self):
1118 db = self.connect_db()
1121 CREATE TABLE auto_timeline_keyword \
1123 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1125 title VARCHAR(100),\
1128 UNIQUE uni (chtxt,title,btime,etime)\
1130 except Exception, inst:
1131 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1132 recdblist.addCommonlogEX("Error", "new_auto_timeline_keyword (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1133 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
1134 db = self.connect_db()
1136 INSERT IGNORE into auto_timeline_keyword \
1137 (chtxt,title,btime,etime) \
1138 values (%s,%s,%s,%s)', \
1139 (chtxt, title, btime, etime))
1142 def delete_old_auto_timeline_keyword(self, dhour):
1143 db = self.connect_db()
1145 DELETE FROM auto_timeline_keyword \
1147 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1149 def new_auto_timeline_bayes(self):
1150 db = self.connect_db()
1153 CREATE TABLE auto_timeline_bayes \
1155 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1157 title VARCHAR(100),\
1161 UNIQUE uni (chtxt,title,btime,etime)\
1163 except Exception, inst:
1164 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1165 recdblist.addCommonlogEX("Error", "new_auto_timeline_bayes (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1166 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
1167 db = self.connect_db()
1169 INSERT IGNORE into auto_timeline_bayes \
1170 (chtxt,title,btime,etime,point) \
1171 values (%s,%s,%s,%s,%s)', \
1172 (chtxt, title, btime, etime,point))
1174 def delete_old_auto_timeline_bayes(self, dhour):
1175 db = self.connect_db()
1177 DELETE FROM auto_timeline_bayes \
1179 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1181 def update_db_to93(self):
1182 db = self.connect_db()
1183 self.drop_in_settings()
1184 self.new_in_settings()
1186 ALTER TABLE timeline ADD epgtitle VARCHAR(100),\
1187 ADD epgbtime DATETIME,\
1188 ADD epgetime DATETIME,\
1189 ADD epgduplicate TINYINT DEFAULT 0,\
1190 ADD epgchange TINYINT DEFAULT 0")
1192 ALTER TABLE in_status ADD version TINYINT")
1194 self.change_version_in_status("93")
1195 def update_db_93to94(self):
1196 db = self.connect_db()
1197 self.drop_in_settings()
1198 self.new_in_settings()
1200 ALTER TABLE timeline ADD counter TINYINT DEFAULT -1")
1202 self.change_version_in_status("94")
1203 def update_db_94to95(self):
1204 db = self.connect_db()
1205 self.drop_in_settings()
1206 self.new_in_settings()
1208 ALTER TABLE timeline ADD epgexp VARCHAR(200)")
1210 self.change_version_in_status("95")
1211 def update_db_95to96(self):
1212 db = self.connect_db()
1213 self.drop_in_settings()
1214 self.new_in_settings()
1216 self.change_version_in_status("96")
1217 def update_db_96to98(self):
1218 db = self.connect_db()
1219 self.drop_in_settings()
1220 self.new_in_settings()
1222 self.change_version_in_status("98")
1223 def update_db_98to100(self):
1224 ###ここで前のepg_chをバックアップしてchtxtの変換をする必要がある。
1225 db = self.connect_db()
1226 self.drop_in_settings()
1227 self.new_in_settings()
1229 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')")
1231 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'")
1233 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'")
1236 ALTER TABLE epg_ch DROP ontv")
1240 ALTER TABLE epg_ch ADD logo0 BLOB,\
1248 ALTER TABLE in_auto_jbk_key ADD auto TINYINT DEFAULT 0")
1250 ALTER TABLE in_auto_jbk_key ADD opt VARCHAR(20) DEFAULT \"\"")
1252 self.change_installed_in_status()#チャンネルスキャンをさせる
1253 self.change_version_in_status("100")
1254 def update_db_100to101(self):
1255 db = self.connect_db()
1256 self.drop_in_settings()
1257 self.new_in_settings()
1261 ALTER TABLE timeline ADD epgcategory VARCHAR(100)\
1266 #self.change_installed_in_status()#チャンネルスキャンをさせる
1267 self.change_version_in_status("101")