3 # Rec10 TS Recording Tools
4 # Copyright (C) 2009-2010 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 counter TINYINT DEFAULT -1,\
54 UNIQUE uni (type,chtxt,title,btime,deltaday)\
56 except Exception, inst:
57 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
58 recdblist.addCommonlogEX("Error", "init (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
60 self.new_epg_timeline("")
61 self.new_in_timeline_log()
62 self.new_in_auto_bayes_key()
63 self.new_in_auto_jbk_key()
65 self.new_in_settings()
66 self.new_auto_timeline_bayes()
67 self.new_auto_timeline_keyword()
72 con = MySQLdb.connect(db=self.dbname, host=self.dbhost, port=self.dbport, user=self.dbusr, passwd=self.dbpasswd, charset="utf8")
74 cur.execute('set names utf8;')
76 def close_db(self, db):
80 db = self.connect_db()
82 db[1].execute('drop table epg_ch')
83 except Exception, inst:
84 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
85 recdblist.addCommonlogEX("Error", "new_epg_ch drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
92 chtxt VARCHAR(20) PRIMARY KEY,\
100 except Exception, inst:
101 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
102 recdblist.addCommonlogEX("Error", "new_epg_ch (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
105 def add_epg_ch(self, bctype, chtxt, ch, csch, updatetime):
106 db = self.connect_db()
109 VALUES (%s,%s,%s,%s,"",%s,%s,%s)', \
110 (bctype, chtxt, ch, csch, updatetime, "1","1"))
112 def select_by_chtxt_epg_ch(self, chtxt):
113 db = self.connect_db()
114 dbexe = db[1].execute("\
115 SELECT bctype,chtxt,ch,csch,updatetime \
117 WHERE chtxt LIKE %s", \
122 dls = db[1].fetchall()
126 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
129 def select_by_bctype_epg_ch(self, bctype):
130 db = self.connect_db()
131 dbexe = db[1].execute("\
132 SELECT bctype,chtxt,ch,csch,updatetime,status \
134 WHERE bctype = %s", \
139 dls = db[1].fetchall()
142 #recdblist.printutf8(dl)
144 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
148 def select_by_ch_epg_ch(self, ch):
149 db = self.connect_db()
150 dbexe = db[1].execute("\
152 bctype,chtxt,ch,csch,updatetime \
159 dls = db[1].fetchall()
163 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
166 def select_all_epg_ch(self):
167 db = self.connect_db()
168 dbexe = db[1].execute("\
169 SELECT bctype,chtxt,ch,csch,updatetime \
175 dls = db[1].fetchall()
179 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
182 def set_new_status(self,dhour):
183 db = self.connect_db()
184 dbexe = db[1].execute("UPDATE epg_ch \
188 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
193 def select_get_update_epg_ch(self, dhour):
194 db = self.connect_db()
195 dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
198 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
204 ORDER BY status DESC")
206 #recdblist.printutf8(dbexe)
208 ret = db[1].fetchall()
211 def update_by_bctype_epg_ch(self, bctype):
212 db = self.connect_db()
218 WHERE bctype = %s", (bctype,))
220 def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
221 db = self.connect_db()
227 WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
229 def update_status_by_bctype_epg_ch(self, bctype, status):
230 db = self.connect_db()
236 WHERE bctype = %s", \
240 def update_chname_by_chtxt_epg_ch(self,chtxt,chname):
241 db = self.connect_db()
250 def add_auto_proc_tmp(self,type,title,chtxt):
251 db = self.connect_db()
253 INSERT IGNORE into auto_proc_tmp \
255 values (%s,%s,%s)',(type,title,chtxt))
258 def new_auto_proc_tmp(self):
259 db = self.connect_db()
261 db[1].execute('drop table auto_proc_tmp')
262 except Exception, inst:
263 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
264 recdblist.addCommonlogEX("Error", "new_auto_proc_tmp drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
268 CREATE TABLE auto_proc_tmp \
271 title VARCHAR(100) PRIMARY KEY,\
273 UNIQUE unibayeskey(title)\
275 except Exception, inst:
276 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
277 recdblist.addCommonlogEX("Error", "new_auto_proc_tmp (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
280 def update_auto_proc(self):
281 db = self.connect_db()
283 db[1].execute('INSERT INTO auto_proc SELECT * FROM auto_proc_tmp')
284 except Exception, inst:
285 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
286 recdblist.addCommonlogEX("Error", "update_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
289 def new_auto_proc(self):
290 db = self.connect_db()
292 db[1].execute('drop table auto_proc')
293 except Exception, inst:
294 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
295 recdblist.addCommonlogEX("Error", "new_auto_proc drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
299 CREATE TABLE auto_proc \
302 title VARCHAR(100) PRIMARY KEY,\
304 UNIQUE unibayeskey(title)\
306 except Exception, inst:
307 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
308 recdblist.addCommonlogEX("Error", "new_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
311 def add_auto_proc(self,type,title,chtxt):
312 db = self.connect_db()
314 INSERT IGNORE into auto_proc \
316 values (%s,%s,%s)',(type,title,chtxt))
319 def drop_in_settings(self):
320 db = self.connect_db()
322 db[1].execute('drop table in_settings')
323 except Exception, inst:
324 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
325 recdblist.addCommonlogEX("Error", "drop_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
327 def new_in_settings(self):
328 db = self.connect_db()
331 CREATE TABLE in_settings \
335 auto_opt VARCHAR(20),\
336 auto_del_tmp TINYINT\
338 db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"H\",1)")
339 except Exception, inst:
340 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
341 recdblist.addCommonlogEX("Error", "new_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
344 def select_all_in_settings(self):
345 db = self.connect_db()
346 dbexe = db[1].execute("\
347 SELECT auto_jbk,auto_bayes,auto_del_tmp,auto_opt \
353 dls = db[1].fetchall()
363 def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
364 db = self.connect_db()
366 INSERT IGNORE into in_timeline_log \
367 (chtxt,title,btime,etime,opt,exp,longexp,category) \
368 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
369 ( chtxt, title, btime, etime, opt,exp,longexp,category))
372 def del_in_timeline_log(self, title="", chtxt="", btime=""):
376 db = self.connect_db()
378 DELETE FROM in_timeline_log \
379 WHERE title = %s AND chtxt = %s AND btime = %s", \
380 (title, chtxt, btime))
383 def new_in_timeline_log(self):
384 db = self.connect_db()
387 CREATE TABLE in_timeline_log \
389 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
397 category VARCHAR(100),\
398 UNIQUE uni (chtxt,title,btime,category)\
400 except Exception, inst:
401 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
402 recdblist.addCommonlogEX("Error", "new_in_timeline_log (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
405 def select_chtxt_by_title_timeline_log(self,title):
406 db = self.connect_db()
407 dbexe = db[1].execute("\
409 FROM in_timeline_log \
410 WHERE title LIKE \"%"+title+"%\"\
412 ORDER by sum(1) DESC limit 1")
413 retdb=db[1].fetchall()
420 def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt="" ,counter=-1):
421 db = self.connect_db()
423 INSERT IGNORE into timeline \
424 (type,chtxt,title,btime,etime,deltatime,deltaday,opt,counter) \
425 values (%s,%s,%s,%s,%s,%s,%s,%s,%s)', \
426 (type, chtxt, title, btime, etime, deltatime, deltaday, opt ,counter))
429 def update_epg_timeline(self,type,chtxt,title,btime,epgbtime,epgetime,epgtitle,epgexp):
430 db = self.connect_db()
433 SET epgbtime=%s,epgetime=%s,epgtitle=%s,epgexp=%s \
434 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
435 (epgbtime,epgetime,epgtitle,epgexp,type, chtxt, title, btime))
438 def update_status_change_timeline(self,type,chtxt,title,btime,epgchange):
439 db = self.connect_db()
443 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
444 (epgchange , type, chtxt, title, btime))
447 def update_status_dup_timeline(self,type,chtxt,title,btime,epgduplicate):
448 db = self.connect_db()
451 SET epgduplicate =%s \
452 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
453 (epgduplicate , type, chtxt, title, btime))
456 def del_timeline(self, type="", title="", chtxt="", btime=""):
460 db = self.connect_db()
462 DELETE FROM timeline \
463 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
464 (type, title, chtxt, btime))
467 def select_all_timeline(self):
468 db = self.connect_db()
470 dbr = db[1].execute("\
471 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
473 dbl = db[1].fetchall()
476 recdata = self.getdic_timeline(dbl)
478 def select_bytime_timeline(self, dminutes):
479 db = self.connect_db()
481 #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 )")
482 dbr = db[1].execute("SELECT \
483 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
485 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
486 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
487 dbl = db[1].fetchall()
489 #recdblist.printutf8(dbl)
491 recdatum = self.getdic_timeline(dbl)
493 def select_by_name_time_timeline(self,title,btime,btime2):
494 db = self.connect_db()
496 #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 )")
497 dbr = db[1].execute("SELECT \
498 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
500 WHERE btime > %s AND \
501 btime < %s AND title = %s",(btime,btime2,title))
502 dbl = db[1].fetchall()
504 #recdblist.printutf8(dbl)
506 recdatum = self.getdic_timeline(dbl)
508 def select_bytime_all_timeline(self,btime,etime):
509 db = self.connect_db()
511 #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 )")
512 dbr = db[1].execute("SELECT \
513 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
515 WHERE btime > %s AND \
516 etime < %s",(btime,etime))
517 dbl = db[1].fetchall()
519 #recdblist.printutf8(dbl)
521 recdatum = self.getdic_timeline(dbl)
523 def select_byepgtime_all_timeline(self,epgbtime,epgetime):
524 db = self.connect_db()
526 #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 )")
527 dbr = db[1].execute("SELECT \
528 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
530 WHERE epgbtime >= %s AND \
531 epgetime <= %s",(epgbtime,epgetime))
532 dbl = db[1].fetchall()
534 #recdblist.printutf8(dbl)
536 recdatum=self.getdic_timeline(dbl)
538 def select_byepgtime_over_timeline(self,epgbtime,epgetime):
539 db = self.connect_db()
541 #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 )")
542 dbr = db[1].execute("SELECT \
543 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
545 WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"\
546 ,(epgbtime,epgetime))
547 dbl = db[1].fetchall()
549 #recdblist.printutf8(dbl)
551 recdatum=self.getdic_timeline(dbl)
553 def count_schedule_timeline(self, btime, etime):
556 return [te num,bs/cs num]
558 db = self.connect_db()
559 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
560 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
563 db[1].execute(dbexe, (btime, etime,btime,etime))
565 for typet, bctypet, chtxtt, titlet in dbl:
566 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
567 if bctypet.find("cs") > -1:
569 elif bctypet.find("bs") > -1:
571 elif bctypet.find("te") > -1:
575 def search_schedule_timeline(self,btime,etime):
578 return [(type,bctype,chtxt,title,btime,etime)]
580 db = self.connect_db()
581 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"
582 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
584 db[1].execute(dbexe, (btime, etime,btime,etime))
586 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
587 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
588 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
591 def count_epgschedule_timeline(self, epgbtime, epgetime):
594 return [te num,bs/cs num]
596 db = self.connect_db()
597 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
598 dbexe = dbexe + " WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"
601 db[1].execute(dbexe, (epgbtime, epgetime))
603 for typet, bctypet, chtxtt, titlet in dbl:
604 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
605 if bctypet.find("cs") > -1:
607 elif bctypet.find("bs") > -1:
609 elif bctypet.find("te") > -1:
613 def search_epgschedule_timeline(self,epgbtime,epgetime):
616 return [(type,bctype,chtxt,title,btime,etime)]
618 db = self.connect_db()
619 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"
620 dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )"
622 db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime))
624 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
625 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
626 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
629 def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
630 db = self.connect_db()
632 #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 )")
633 dbr = db[1].execute("SELECT \
634 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
636 WHERE btime > %s AND \
638 AND chtxt=%s ",(btime,etime,chtxt))
639 dbl = db[1].fetchall()
641 #recdblist.printutf8(dbl)
643 recdatum = self.getdic_timeline(dbl)
645 def getdic_timeline(self,timelinelists):
647 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt ,epgbtimet , epgetimet ,epgtitlet ,epgduplicatet ,epgchanget ,countert in timelinelists:
652 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
653 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
658 ret['epgbtime'] = epgbtimet.strftime("%Y-%m-%d %H:%M:%S")
659 ret['epgetime'] = epgetimet.strftime("%Y-%m-%d %H:%M:%S")
661 ret['epgbtime'] = "2010-01-01 00:00:00"
662 ret['epgetime'] = "2010-01-01 00:00:00"
663 ret['epgtitle'] = epgtitlet
664 ret['epgduplicate'] = epgduplicatet
665 ret['epgchange'] = epgchanget
666 if deltatime == None or deltatime == "":
668 if deltaday == None or deltaday == "":
670 if typet == recdblist.REC_KEYWORD:
671 ret['deltatime'] = deltatime
672 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
673 ret['deltatime'] = deltatime
674 ret['deltaday'] = deltaday
676 ret['counter'] = int(countert)
681 def delete_old_timeline(self, dhour):
682 db = self.connect_db()
684 DELETE FROM timeline \
686 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
689 def new_in_auto_jbk_key(self):
690 db = self.connect_db()
693 CREATE TABLE in_auto_jbk_key \
695 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
696 keyword VARCHAR(200),\
697 auto TINYINT DEFAULT 0,\
699 UNIQUE unijbk (keyword)\
701 except Exception, inst:
702 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
703 recdblist.addCommonlogEX("Error", "new_in_auto_jbk_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
705 def add_in_auto_jbk_key(self,key):
706 db = self.connect_db()
708 INSERT IGNORE into in_auto_jbk_key \
714 def select_all_in_auto_jbk_key(self):
715 db = self.connect_db()
716 dbexe = db[1].execute("\
717 SELECT keyword,auto,opt \
718 FROM in_auto_jbk_key \
722 ret = db[1].fetchall()
725 def drop_in_status(self):
726 db = self.connect_db()
728 db[1].execute('drop table in_status')
729 except Exception, inst:
730 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
731 recdblist.addCommonlogEX("Error", "drop_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
733 def new_in_status(self):
734 db = self.connect_db()
737 CREATE TABLE in_status \
739 ts2avi TINYINT DEFAULT 0,\
740 terec TINYINT DEFAULT 0,\
741 bscsrec TINYINT DEFAULT 0,\
742 b252ts TINYINT DEFAULT 0,\
743 installed TINYINT DEFAULT 0,\
746 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0,0)")
747 except Exception, inst:
748 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
749 recdblist.addCommonlogEX("Error", "new_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
751 def select_all_in_status(self):
752 db = self.connect_db()
753 dbexe = db[1].execute("\
754 SELECT ts2avi,terec,bscsrec,b252ts \
760 dls = db[1].fetchall()
770 def select_version_in_status(self):
771 db = self.connect_db()
774 dbexe = db[1].execute("\
779 dls = db[1].fetchall()
783 version=int(str(r[0]))
784 except Exception, inst:
785 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
786 recdblist.addCommonlogEX("Error", "select_version_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
788 def change_version_in_status(self,version):
789 db = self.connect_db()
791 UPDATE in_status SET version=%s",str(version))
793 def change_ts2avi_in_status(self,i):
798 db = self.connect_db()
800 UPDATE in_status SET ts2avi=ts2avi+%s",i)
802 def change_terec_in_status(self,i):
807 db = self.connect_db()
809 UPDATE in_status SET terec=terec+%s",i)
811 def change_bscsrec_in_status(self,i):
816 db = self.connect_db()
818 UPDATE in_status SET bscsrec=bscsrec+%s",i)
820 def change_b252ts_in_status(self,i):
825 db = self.connect_db()
827 UPDATE in_status SET b252ts=b252ts+%s",i)
829 def select_installed_in_status(self):
830 db = self.connect_db()
831 dbexe = db[1].execute("\
832 SELECT ts2avi,terec,bscsrec,b252ts,installed \
838 dls = db[1].fetchall()
844 def change_installed_in_status(self):
848 db = self.connect_db()
850 UPDATE in_status SET installed=1")
852 def change_chscaned_in_status(self):
856 db = self.connect_db()
858 UPDATE in_status SET installed=2")
860 def new_epg_timeline(self, bctype):
861 db = self.connect_db()
864 DELETE FROM epg_timeline \
865 WHERE bctype = %s", \
867 except Exception, inst:
868 if not ((type(inst)==MySQLdb.ProgrammingError and (inst[0]==1007 or inst[0]==1146))or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
869 recdblist.addCommonlogEX("Error", "new_epg_timeline delete (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
872 CREATE TABLE epg_timeline \
875 channel VARCHAR(100) NOT NULL,\
881 category VARCHAR(100),\
882 UNIQUE unitv(bctype,channel,start,stop,title)\
884 except Exception, inst:
885 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
886 recdblist.addCommonlogEX("Error", "new_epg_timeline (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
889 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
890 db = self.connect_db()
892 INSERT IGNORE INTO epg_timeline \
893 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
894 (bctype, channel, start, stop, title, desc,longdesc,category))
897 def add_multi_epg_timeline(self, tvlists):
899 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
901 db = self.connect_db()
903 INSERT IGNORE INTO epg_timeline \
904 (bctype,channel,start,stop,title,exp,longexp,category) \
905 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
908 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
909 db = self.connect_db()
912 channel,title,start,stop,exp,longexp,category \
919 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
922 retall = db[1].fetchall()
925 def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
926 db = self.connect_db()
929 epg_ch.chtxt,title,start,stop,exp,longexp,category \
932 WHERE epg_ch.chtxt=epg_timeline.channel \
939 ( title LIKE \'%%"+keyword+"%%\' ) \
941 ( exp LIKE \'%%"+keyword+"%%\' ) \
943 ( longexp LIKE \'%%"+keyword+"%%\' ) \
945 dbcmd = db[1].execute(dbexe,(btime, etime))
948 retall = db[1].fetchall()
951 def new_in_auto_bayes_key(self):
952 db = self.connect_db()
954 db[1].execute('CREATE TABLE in_auto_bayes_key \
956 keychar VARCHAR(10),\
958 ratio_rec DECIMAL(32,14),\
959 ratio_all DECIMAL(32,14),\
960 UNIQUE unibayeskey(keychar,chtxt)\
962 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
963 except Exception, inst:
964 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
965 recdblist.addCommonlogEX("Error", "new_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
968 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
969 db = self.connect_db()
970 ratio_rec=str(ratio_rec)
971 ratio_all=str(ratio_all)
973 INSERT IGNORE INTO in_auto_bayes_key \
974 (keychar,chtxt,ratio_rec,ratio_all) \
975 values (%s,%s,%s,%s)',\
976 (key,chtxt,ratio_rec,ratio_all))
978 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
979 db = self.connect_db()
980 add_rec_num=str(add_rec_num)
981 add_all_num=str(add_all_num)
983 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",\
984 (add_rec_num,add_all_num,chtxt))
986 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
989 db = self.connect_db()
991 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)\
994 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
995 beforenum=str(beforenum)
997 db = self.connect_db()
999 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)\
1002 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1003 db = self.connect_db()
1004 beforenumf=beforenum
1005 beforenum=str(beforenum)
1006 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1007 VALUES (%s,%s,%s,%s)\
1008 ON DUPLICATE KEY UPDATE \
1009 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
1011 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
1013 list={key:addnum}のリスト
1015 beforenumf=beforenum
1016 beforenum=str(beforenum)
1017 db = self.connect_db()
1018 for i,j in list.items():
1019 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
1021 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1022 VALUES (%s,%s,%s,%s)\
1023 ON DUPLICATE KEY UPDATE \
1024 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
1025 except Exception, inst:
1026 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1027 recdblist.addCommonlogEX("Error", "change_multi_ratio_all_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1029 def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1030 beforenum=str(beforenum)
1032 db = self.connect_db()
1034 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)\
1037 def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1038 db = self.connect_db()
1039 beforenumf=beforenum
1040 beforenum=str(beforenum)
1041 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1042 VALUES (%s,%s,%s,%s)\
1043 ON DUPLICATE KEY UPDATE \
1044 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
1046 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
1047 beforenumf=beforenum
1048 beforenum=str(beforenum)
1049 db = self.connect_db()
1050 for i,j in list.items():
1051 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
1053 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1054 VALUES (%s,%s,%s,%s)\
1055 ON DUPLICATE KEY UPDATE \
1056 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
1057 except Exception, inst:
1058 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1059 recdblist.addCommonlogEX("Error", "change_multi_ratio_rec_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1061 def select_by_key_in_auto_bayes_key(self,key,chtxt):
1062 db = self.connect_db()
1063 dbexe = db[1].execute("\
1064 SELECT keychar,chtxt,ratio_rec,ratio_all \
1065 FROM in_auto_bayes_key \
1066 WHERE keychar = %s AND chtxt = %s", \
1070 dls = db[1].fetchall()
1076 def new_auto_timeline_keyword(self):
1077 db = self.connect_db()
1080 CREATE TABLE auto_timeline_keyword \
1082 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1084 title VARCHAR(100),\
1087 UNIQUE uni (chtxt,title,btime,etime)\
1089 except Exception, inst:
1090 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1091 recdblist.addCommonlogEX("Error", "new_auto_timeline_keyword (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1092 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
1093 db = self.connect_db()
1095 INSERT IGNORE into auto_timeline_keyword \
1096 (chtxt,title,btime,etime) \
1097 values (%s,%s,%s,%s)', \
1098 (chtxt, title, btime, etime))
1101 def delete_old_auto_timeline_keyword(self, dhour):
1102 db = self.connect_db()
1104 DELETE FROM auto_timeline_keyword \
1106 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1108 def new_auto_timeline_bayes(self):
1109 db = self.connect_db()
1112 CREATE TABLE auto_timeline_bayes \
1114 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1116 title VARCHAR(100),\
1120 UNIQUE uni (chtxt,title,btime,etime)\
1122 except Exception, inst:
1123 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1124 recdblist.addCommonlogEX("Error", "new_auto_timeline_bayes (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1125 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
1126 db = self.connect_db()
1128 INSERT IGNORE into auto_timeline_bayes \
1129 (chtxt,title,btime,etime,point) \
1130 values (%s,%s,%s,%s,%s)', \
1131 (chtxt, title, btime, etime,point))
1133 def delete_old_auto_timeline_bayes(self, dhour):
1134 db = self.connect_db()
1136 DELETE FROM auto_timeline_bayes \
1138 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1140 def update_db_to93(self):
1141 db = self.connect_db()
1142 self.drop_in_settings()
1143 self.new_in_settings()
1145 ALTER TABLE timeline ADD epgtitle VARCHAR(100),\
1146 ADD epgbtime DATETIME,\
1147 ADD epgetime DATETIME,\
1148 ADD epgduplicate TINYINT DEFAULT 0,\
1149 ADD epgchange TINYINT DEFAULT 0")
1151 ALTER TABLE in_status ADD version TINYINT")
1153 self.change_version_in_status("93")
1154 def update_db_93to94(self):
1155 db = self.connect_db()
1156 self.drop_in_settings()
1157 self.new_in_settings()
1159 ALTER TABLE timeline ADD counter TINYINT DEFAULT -1")
1161 self.change_version_in_status("94")
1162 def update_db_94to95(self):
1163 db = self.connect_db()
1164 self.drop_in_settings()
1165 self.new_in_settings()
1167 ALTER TABLE timeline ADD epgexp VARCHAR(200)")
1169 self.change_version_in_status("95")
1170 def update_db_95to96(self):
1171 db = self.connect_db()
1172 self.drop_in_settings()
1173 self.new_in_settings()
1175 self.change_version_in_status("96")
1176 def update_db_96to98(self):
1177 db = self.connect_db()
1178 self.drop_in_settings()
1179 self.new_in_settings()
1181 self.change_version_in_status("98")
1182 def update_db_98to100(self):
1183 ###ここで前のepg_chをバックアップしてchtxtの変換をする必要がある。
1184 db = self.connect_db()
1185 self.drop_in_settings()
1186 self.new_in_settings()
1188 UPDATE timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt SET timeline.chtxt=CONCAT(CONCAT(epg_ch.ch,'_'),epg_ch.csch)")
1190 ALTER TABLE epg_ch DROP ontv")
1192 ALTER TABLE in_auto_jbk_key ADD auto TINYINT DEFAULT 0")
1194 ALTER TABLE in_auto_jbk_key ADD opt VARCHAR(20) DEFAULT \"\"")
1196 self.change_installed_in_status()#チャンネルスキャンをさせる
1197 self.change_version_in_status("100")