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,\
98 visible TINYINT DEFAULT 1\
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 delete_all_epg_ch(self):
113 db = self.connect_db()
117 def select_by_chtxt_epg_ch(self, chtxt):
118 db = self.connect_db()
119 dbexe = db[1].execute("\
120 SELECT bctype,chtxt,ch,csch,updatetime \
122 WHERE chtxt LIKE %s", \
127 dls = db[1].fetchall()
131 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
134 def select_by_bctype_epg_ch(self, bctype):
135 db = self.connect_db()
136 dbexe = db[1].execute("\
137 SELECT bctype,chtxt,ch,csch,updatetime,status \
139 WHERE bctype = %s", \
144 dls = db[1].fetchall()
147 #recdblist.printutf8(dl)
149 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
153 def select_by_ch_epg_ch(self, ch):
154 db = self.connect_db()
155 dbexe = db[1].execute("\
157 bctype,chtxt,ch,csch,updatetime \
164 dls = db[1].fetchall()
168 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
171 def select_all_epg_ch(self):
172 db = self.connect_db()
173 dbexe = db[1].execute("\
174 SELECT bctype,chtxt,ch,csch,updatetime \
180 dls = db[1].fetchall()
184 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
187 def change_visible_epg_ch(self,chtxt,visible):
188 db = self.connect_db()
190 UPDATE epg_ch SET visible=%s WHERE chtxt=%s",(visible,chtxt))
192 def set_new_status(self,dhour):
193 db = self.connect_db()
194 dbexe = db[1].execute("UPDATE epg_ch \
198 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
203 def select_get_update_epg_ch(self, dhour):
204 db = self.connect_db()
205 dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
208 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
214 ORDER BY status DESC")
216 #recdblist.printutf8(dbexe)
218 ret = db[1].fetchall()
221 def update_by_bctype_epg_ch(self, bctype):
222 db = self.connect_db()
228 WHERE bctype = %s", (bctype,))
230 def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
231 db = self.connect_db()
237 WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
239 def update_status_by_bctype_epg_ch(self, bctype, status):
240 db = self.connect_db()
246 WHERE bctype = %s", \
250 def update_chname_by_chtxt_epg_ch(self,chtxt,chname):
251 db = self.connect_db()
260 def add_auto_proc_tmp(self,type,title,chtxt):
261 db = self.connect_db()
263 INSERT IGNORE into auto_proc_tmp \
265 values (%s,%s,%s)',(type,title,chtxt))
268 def new_auto_proc_tmp(self):
269 db = self.connect_db()
271 db[1].execute('drop table auto_proc_tmp')
272 except Exception, inst:
273 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
274 recdblist.addCommonlogEX("Error", "new_auto_proc_tmp drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
278 CREATE TABLE auto_proc_tmp \
281 title VARCHAR(100) PRIMARY KEY,\
283 UNIQUE unibayeskey(title)\
285 except Exception, inst:
286 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
287 recdblist.addCommonlogEX("Error", "new_auto_proc_tmp (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
290 def update_auto_proc(self):
291 db = self.connect_db()
293 db[1].execute('INSERT INTO auto_proc SELECT * FROM auto_proc_tmp')
294 except Exception, inst:
295 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
296 recdblist.addCommonlogEX("Error", "update_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
299 def new_auto_proc(self):
300 db = self.connect_db()
302 db[1].execute('drop table auto_proc')
303 except Exception, inst:
304 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
305 recdblist.addCommonlogEX("Error", "new_auto_proc drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
309 CREATE TABLE auto_proc \
312 title VARCHAR(100) PRIMARY KEY,\
314 UNIQUE unibayeskey(title)\
316 except Exception, inst:
317 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
318 recdblist.addCommonlogEX("Error", "new_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
321 def add_auto_proc(self,type,title,chtxt):
322 db = self.connect_db()
324 INSERT IGNORE into auto_proc \
326 values (%s,%s,%s)',(type,title,chtxt))
329 def drop_in_settings(self):
330 db = self.connect_db()
332 db[1].execute('drop table in_settings')
333 except Exception, inst:
334 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
335 recdblist.addCommonlogEX("Error", "drop_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
337 def new_in_settings(self):
338 db = self.connect_db()
341 CREATE TABLE in_settings \
345 auto_opt VARCHAR(20),\
346 auto_del_tmp TINYINT\
348 db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"H\",1)")
349 except Exception, inst:
350 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
351 recdblist.addCommonlogEX("Error", "new_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
354 def select_all_in_settings(self):
355 db = self.connect_db()
356 dbexe = db[1].execute("\
357 SELECT auto_jbk,auto_bayes,auto_del_tmp,auto_opt \
363 dls = db[1].fetchall()
373 def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
374 db = self.connect_db()
376 INSERT IGNORE into in_timeline_log \
377 (chtxt,title,btime,etime,opt,exp,longexp,category) \
378 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
379 ( chtxt, title, btime, etime, opt,exp,longexp,category))
382 def del_in_timeline_log(self, title="", chtxt="", btime=""):
386 db = self.connect_db()
388 DELETE FROM in_timeline_log \
389 WHERE title = %s AND chtxt = %s AND btime = %s", \
390 (title, chtxt, btime))
393 def new_in_timeline_log(self):
394 db = self.connect_db()
397 CREATE TABLE in_timeline_log \
399 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
407 category VARCHAR(100),\
408 UNIQUE uni (chtxt,title,btime,category)\
410 except Exception, inst:
411 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
412 recdblist.addCommonlogEX("Error", "new_in_timeline_log (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
415 def select_chtxt_by_title_timeline_log(self,title):
416 db = self.connect_db()
417 dbexe = db[1].execute("\
419 FROM in_timeline_log \
420 WHERE title LIKE \"%"+title+"%\"\
422 ORDER by sum(1) DESC limit 1")
423 retdb=db[1].fetchall()
430 def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt="" ,counter=-1):
431 db = self.connect_db()
433 INSERT IGNORE into timeline \
434 (type,chtxt,title,btime,etime,deltatime,deltaday,opt,counter) \
435 values (%s,%s,%s,%s,%s,%s,%s,%s,%s)', \
436 (type, chtxt, title, btime, etime, deltatime, deltaday, opt ,counter))
439 def update_epg_timeline(self,type,chtxt,title,btime,epgbtime,epgetime,epgtitle,epgexp):
440 db = self.connect_db()
443 SET epgbtime=%s,epgetime=%s,epgtitle=%s,epgexp=%s \
444 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
445 (epgbtime,epgetime,epgtitle,epgexp,type, chtxt, title, btime))
448 def update_status_change_timeline(self,type,chtxt,title,btime,epgchange):
449 db = self.connect_db()
453 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
454 (epgchange , type, chtxt, title, btime))
457 def update_status_dup_timeline(self,type,chtxt,title,btime,epgduplicate):
458 db = self.connect_db()
461 SET epgduplicate =%s \
462 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
463 (epgduplicate , type, chtxt, title, btime))
466 def del_timeline(self, type="", title="", chtxt="", btime=""):
470 db = self.connect_db()
472 DELETE FROM timeline \
473 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
474 (type, title, chtxt, btime))
477 def select_all_timeline(self):
478 db = self.connect_db()
480 dbr = db[1].execute("\
481 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
483 dbl = db[1].fetchall()
486 recdata = self.getdic_timeline(dbl)
488 def select_bytime_timeline(self, dminutes):
489 db = self.connect_db()
491 #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 )")
492 dbr = db[1].execute("SELECT \
493 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
495 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
496 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
497 dbl = db[1].fetchall()
499 #recdblist.printutf8(dbl)
501 recdatum = self.getdic_timeline(dbl)
503 def select_by_name_time_timeline(self,title,btime,btime2):
504 db = self.connect_db()
506 #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 )")
507 dbr = db[1].execute("SELECT \
508 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
510 WHERE btime > %s AND \
511 btime < %s AND title = %s",(btime,btime2,title))
512 dbl = db[1].fetchall()
514 #recdblist.printutf8(dbl)
516 recdatum = self.getdic_timeline(dbl)
518 def select_bytime_all_timeline(self,btime,etime):
519 db = self.connect_db()
521 #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 )")
522 dbr = db[1].execute("SELECT \
523 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
525 WHERE btime > %s AND \
526 etime < %s",(btime,etime))
527 dbl = db[1].fetchall()
529 #recdblist.printutf8(dbl)
531 recdatum = self.getdic_timeline(dbl)
533 def select_byepgtime_all_timeline(self,epgbtime,epgetime):
534 db = self.connect_db()
536 #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 )")
537 dbr = db[1].execute("SELECT \
538 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
540 WHERE epgbtime >= %s AND \
541 epgetime <= %s",(epgbtime,epgetime))
542 dbl = db[1].fetchall()
544 #recdblist.printutf8(dbl)
546 recdatum=self.getdic_timeline(dbl)
548 def select_byepgtime_over_timeline(self,epgbtime,epgetime):
549 db = self.connect_db()
551 #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 )")
552 dbr = db[1].execute("SELECT \
553 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
555 WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"\
556 ,(epgbtime,epgetime))
557 dbl = db[1].fetchall()
559 #recdblist.printutf8(dbl)
561 recdatum=self.getdic_timeline(dbl)
563 def count_schedule_timeline(self, btime, etime):
566 return [te num,bs/cs num]
568 db = self.connect_db()
569 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
570 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
573 db[1].execute(dbexe, (btime, etime,btime,etime))
575 for typet, bctypet, chtxtt, titlet in dbl:
576 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
577 if bctypet.find("cs") > -1:
579 elif bctypet.find("bs") > -1:
581 elif bctypet.find("te") > -1:
585 def search_schedule_timeline(self,btime,etime):
588 return [(type,bctype,chtxt,title,btime,etime)]
590 db = self.connect_db()
591 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"
592 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
594 db[1].execute(dbexe, (btime, etime,btime,etime))
596 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
597 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
598 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
601 def count_epgschedule_timeline(self, epgbtime, epgetime):
604 return [te num,bs/cs num]
606 db = self.connect_db()
607 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
608 dbexe = dbexe + " WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"
611 db[1].execute(dbexe, (epgbtime, epgetime))
613 for typet, bctypet, chtxtt, titlet in dbl:
614 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
615 if bctypet.find("cs") > -1:
617 elif bctypet.find("bs") > -1:
619 elif bctypet.find("te") > -1:
623 def search_epgschedule_timeline(self,epgbtime,epgetime):
626 return [(type,bctype,chtxt,title,btime,etime)]
628 db = self.connect_db()
629 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"
630 dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )"
632 db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime))
634 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
635 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
636 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
639 def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
640 db = self.connect_db()
642 #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 )")
643 dbr = db[1].execute("SELECT \
644 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
646 WHERE btime > %s AND \
648 AND chtxt=%s ",(btime,etime,chtxt))
649 dbl = db[1].fetchall()
651 #recdblist.printutf8(dbl)
653 recdatum = self.getdic_timeline(dbl)
655 def getdic_timeline(self,timelinelists):
657 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt ,epgbtimet , epgetimet ,epgtitlet ,epgduplicatet ,epgchanget ,countert in timelinelists:
662 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
663 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
668 ret['epgbtime'] = epgbtimet.strftime("%Y-%m-%d %H:%M:%S")
669 ret['epgetime'] = epgetimet.strftime("%Y-%m-%d %H:%M:%S")
671 ret['epgbtime'] = "2010-01-01 00:00:00"
672 ret['epgetime'] = "2010-01-01 00:00:00"
673 ret['epgtitle'] = epgtitlet
674 ret['epgduplicate'] = epgduplicatet
675 ret['epgchange'] = epgchanget
676 if deltatime == None or deltatime == "":
678 if deltaday == None or deltaday == "":
680 if typet == recdblist.REC_KEYWORD:
681 ret['deltatime'] = deltatime
682 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
683 ret['deltatime'] = deltatime
684 ret['deltaday'] = deltaday
686 ret['counter'] = int(countert)
691 def delete_old_timeline(self, dhour):
692 db = self.connect_db()
694 DELETE FROM timeline \
696 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
699 def new_in_auto_jbk_key(self):
700 db = self.connect_db()
703 CREATE TABLE in_auto_jbk_key \
705 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
706 keyword VARCHAR(200),\
707 auto TINYINT DEFAULT 0,\
709 UNIQUE unijbk (keyword)\
711 except Exception, inst:
712 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
713 recdblist.addCommonlogEX("Error", "new_in_auto_jbk_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
715 def add_in_auto_jbk_key(self,key):
716 db = self.connect_db()
718 INSERT IGNORE into in_auto_jbk_key \
724 def select_all_in_auto_jbk_key(self):
725 db = self.connect_db()
726 dbexe = db[1].execute("\
727 SELECT keyword,auto,opt \
728 FROM in_auto_jbk_key \
732 ret = db[1].fetchall()
735 def drop_in_status(self):
736 db = self.connect_db()
738 db[1].execute('drop table in_status')
739 except Exception, inst:
740 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
741 recdblist.addCommonlogEX("Error", "drop_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
743 def new_in_status(self):
744 db = self.connect_db()
747 CREATE TABLE in_status \
749 ts2avi TINYINT DEFAULT 0,\
750 terec TINYINT DEFAULT 0,\
751 bscsrec TINYINT DEFAULT 0,\
752 b252ts TINYINT DEFAULT 0,\
753 installed TINYINT DEFAULT 0,\
756 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0,0)")
757 except Exception, inst:
758 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
759 recdblist.addCommonlogEX("Error", "new_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
761 def select_all_in_status(self):
762 db = self.connect_db()
763 dbexe = db[1].execute("\
764 SELECT ts2avi,terec,bscsrec,b252ts \
770 dls = db[1].fetchall()
780 def select_version_in_status(self):
781 db = self.connect_db()
784 dbexe = db[1].execute("\
789 dls = db[1].fetchall()
793 version=int(str(r[0]))
794 except Exception, inst:
795 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
796 recdblist.addCommonlogEX("Error", "select_version_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
798 def change_version_in_status(self,version):
799 db = self.connect_db()
801 UPDATE in_status SET version=%s",str(version))
803 def change_ts2avi_in_status(self,i):
808 db = self.connect_db()
810 UPDATE in_status SET ts2avi=ts2avi+%s",i)
812 def change_terec_in_status(self,i):
817 db = self.connect_db()
819 UPDATE in_status SET terec=terec+%s",i)
821 def change_bscsrec_in_status(self,i):
826 db = self.connect_db()
828 UPDATE in_status SET bscsrec=bscsrec+%s",i)
830 def change_b252ts_in_status(self,i):
835 db = self.connect_db()
837 UPDATE in_status SET b252ts=b252ts+%s",i)
839 def select_installed_in_status(self):
840 db = self.connect_db()
841 dbexe = db[1].execute("\
842 SELECT ts2avi,terec,bscsrec,b252ts,installed \
848 dls = db[1].fetchall()
854 def change_installed_in_status(self):
858 db = self.connect_db()
860 UPDATE in_status SET installed=1")
862 def change_chscaned_in_status(self):
866 db = self.connect_db()
868 UPDATE in_status SET installed=2")
870 def new_epg_timeline(self, bctype):
871 db = self.connect_db()
874 DELETE FROM epg_timeline \
875 WHERE bctype = %s", \
877 except Exception, inst:
878 if not ((type(inst)==MySQLdb.ProgrammingError and (inst[0]==1007 or inst[0]==1146))or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
879 recdblist.addCommonlogEX("Error", "new_epg_timeline delete (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
882 CREATE TABLE epg_timeline \
885 channel VARCHAR(100) NOT NULL,\
891 category VARCHAR(100),\
892 UNIQUE unitv(bctype,channel,start,stop,title)\
894 except Exception, inst:
895 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
896 recdblist.addCommonlogEX("Error", "new_epg_timeline (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
899 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
900 db = self.connect_db()
902 INSERT IGNORE INTO epg_timeline \
903 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
904 (bctype, channel, start, stop, title, desc,longdesc,category))
907 def add_multi_epg_timeline(self, tvlists):
909 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
911 db = self.connect_db()
913 INSERT IGNORE INTO epg_timeline \
914 (bctype,channel,start,stop,title,exp,longexp,category) \
915 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
918 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
919 db = self.connect_db()
922 channel,title,start,stop,exp,longexp,category \
929 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
932 retall = db[1].fetchall()
935 def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
936 db = self.connect_db()
939 epg_ch.chtxt,title,start,stop,exp,longexp,category \
942 WHERE epg_ch.chtxt=epg_timeline.channel \
949 ( title LIKE \'%%"+keyword+"%%\' ) \
951 ( exp LIKE \'%%"+keyword+"%%\' ) \
953 ( longexp LIKE \'%%"+keyword+"%%\' ) \
955 dbcmd = db[1].execute(dbexe,(btime, etime))
958 retall = db[1].fetchall()
961 def new_in_auto_bayes_key(self):
962 db = self.connect_db()
964 db[1].execute('CREATE TABLE in_auto_bayes_key \
966 keychar VARCHAR(10),\
968 ratio_rec DECIMAL(32,14),\
969 ratio_all DECIMAL(32,14),\
970 UNIQUE unibayeskey(keychar,chtxt)\
972 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
973 except Exception, inst:
974 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
975 recdblist.addCommonlogEX("Error", "new_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
978 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
979 db = self.connect_db()
980 ratio_rec=str(ratio_rec)
981 ratio_all=str(ratio_all)
983 INSERT IGNORE INTO in_auto_bayes_key \
984 (keychar,chtxt,ratio_rec,ratio_all) \
985 values (%s,%s,%s,%s)',\
986 (key,chtxt,ratio_rec,ratio_all))
988 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
989 db = self.connect_db()
990 add_rec_num=str(add_rec_num)
991 add_all_num=str(add_all_num)
993 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",\
994 (add_rec_num,add_all_num,chtxt))
996 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
999 db = self.connect_db()
1001 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)\
1004 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1005 beforenum=str(beforenum)
1007 db = self.connect_db()
1009 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)\
1012 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1013 db = self.connect_db()
1014 beforenumf=beforenum
1015 beforenum=str(beforenum)
1016 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1017 VALUES (%s,%s,%s,%s)\
1018 ON DUPLICATE KEY UPDATE \
1019 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
1021 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
1023 list={key:addnum}のリスト
1025 beforenumf=beforenum
1026 beforenum=str(beforenum)
1027 db = self.connect_db()
1028 for i,j in list.items():
1029 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
1031 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1032 VALUES (%s,%s,%s,%s)\
1033 ON DUPLICATE KEY UPDATE \
1034 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
1035 except Exception, inst:
1036 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1037 recdblist.addCommonlogEX("Error", "change_multi_ratio_all_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1039 def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1040 beforenum=str(beforenum)
1042 db = self.connect_db()
1044 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)\
1047 def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1048 db = self.connect_db()
1049 beforenumf=beforenum
1050 beforenum=str(beforenum)
1051 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1052 VALUES (%s,%s,%s,%s)\
1053 ON DUPLICATE KEY UPDATE \
1054 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
1056 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
1057 beforenumf=beforenum
1058 beforenum=str(beforenum)
1059 db = self.connect_db()
1060 for i,j in list.items():
1061 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
1063 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1064 VALUES (%s,%s,%s,%s)\
1065 ON DUPLICATE KEY UPDATE \
1066 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
1067 except Exception, inst:
1068 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1069 recdblist.addCommonlogEX("Error", "change_multi_ratio_rec_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1071 def select_by_key_in_auto_bayes_key(self,key,chtxt):
1072 db = self.connect_db()
1073 dbexe = db[1].execute("\
1074 SELECT keychar,chtxt,ratio_rec,ratio_all \
1075 FROM in_auto_bayes_key \
1076 WHERE keychar = %s AND chtxt = %s", \
1080 dls = db[1].fetchall()
1086 def new_auto_timeline_keyword(self):
1087 db = self.connect_db()
1090 CREATE TABLE auto_timeline_keyword \
1092 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1094 title VARCHAR(100),\
1097 UNIQUE uni (chtxt,title,btime,etime)\
1099 except Exception, inst:
1100 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1101 recdblist.addCommonlogEX("Error", "new_auto_timeline_keyword (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1102 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
1103 db = self.connect_db()
1105 INSERT IGNORE into auto_timeline_keyword \
1106 (chtxt,title,btime,etime) \
1107 values (%s,%s,%s,%s)', \
1108 (chtxt, title, btime, etime))
1111 def delete_old_auto_timeline_keyword(self, dhour):
1112 db = self.connect_db()
1114 DELETE FROM auto_timeline_keyword \
1116 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1118 def new_auto_timeline_bayes(self):
1119 db = self.connect_db()
1122 CREATE TABLE auto_timeline_bayes \
1124 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1126 title VARCHAR(100),\
1130 UNIQUE uni (chtxt,title,btime,etime)\
1132 except Exception, inst:
1133 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1134 recdblist.addCommonlogEX("Error", "new_auto_timeline_bayes (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1135 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
1136 db = self.connect_db()
1138 INSERT IGNORE into auto_timeline_bayes \
1139 (chtxt,title,btime,etime,point) \
1140 values (%s,%s,%s,%s,%s)', \
1141 (chtxt, title, btime, etime,point))
1143 def delete_old_auto_timeline_bayes(self, dhour):
1144 db = self.connect_db()
1146 DELETE FROM auto_timeline_bayes \
1148 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1150 def update_db_to93(self):
1151 db = self.connect_db()
1152 self.drop_in_settings()
1153 self.new_in_settings()
1155 ALTER TABLE timeline ADD epgtitle VARCHAR(100),\
1156 ADD epgbtime DATETIME,\
1157 ADD epgetime DATETIME,\
1158 ADD epgduplicate TINYINT DEFAULT 0,\
1159 ADD epgchange TINYINT DEFAULT 0")
1161 ALTER TABLE in_status ADD version TINYINT")
1163 self.change_version_in_status("93")
1164 def update_db_93to94(self):
1165 db = self.connect_db()
1166 self.drop_in_settings()
1167 self.new_in_settings()
1169 ALTER TABLE timeline ADD counter TINYINT DEFAULT -1")
1171 self.change_version_in_status("94")
1172 def update_db_94to95(self):
1173 db = self.connect_db()
1174 self.drop_in_settings()
1175 self.new_in_settings()
1177 ALTER TABLE timeline ADD epgexp VARCHAR(200)")
1179 self.change_version_in_status("95")
1180 def update_db_95to96(self):
1181 db = self.connect_db()
1182 self.drop_in_settings()
1183 self.new_in_settings()
1185 self.change_version_in_status("96")
1186 def update_db_96to98(self):
1187 db = self.connect_db()
1188 self.drop_in_settings()
1189 self.new_in_settings()
1191 self.change_version_in_status("98")
1192 def update_db_98to100(self):
1193 ###ここで前のepg_chをバックアップしてchtxtの変換をする必要がある。
1194 db = self.connect_db()
1195 self.drop_in_settings()
1196 self.new_in_settings()
1198 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')")
1200 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'")
1202 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'")
1204 ALTER TABLE epg_ch DROP ontv")
1206 ALTER TABLE in_auto_jbk_key ADD auto TINYINT DEFAULT 0")
1208 ALTER TABLE in_auto_jbk_key ADD opt VARCHAR(20) DEFAULT \"\"")
1210 self.change_installed_in_status()#チャンネルスキャンをさせる
1211 self.change_version_in_status("100")