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 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,\
105 except Exception, inst:
106 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
107 recdblist.addCommonlogEX("Error", "new_epg_ch (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
110 def add_epg_ch(self, bctype, chtxt, ch, csch, updatetime):
111 db = self.connect_db()
114 VALUES (%s,%s,%s,%s,"",%s,%s,%s)', \
115 (bctype, chtxt, ch, csch, updatetime, "1","1"))
117 def delete_all_epg_ch(self):
118 db = self.connect_db()
122 def select_by_chtxt_epg_ch(self, chtxt):
123 db = self.connect_db()
124 dbexe = db[1].execute("\
125 SELECT bctype,chtxt,ch,csch,updatetime,chname,status,visible \
127 WHERE chtxt LIKE %s", \
132 dls = db[1].fetchall()
136 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
139 def select_by_bctype_epg_ch(self, bctype):
140 db = self.connect_db()
141 dbexe = db[1].execute("\
142 SELECT bctype,chtxt,ch,csch,updatetime,status,chname,status,visible \
144 WHERE bctype = %s", \
149 dls = db[1].fetchall()
152 #recdblist.printutf8(dl)
154 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
158 def select_by_ch_epg_ch(self, ch):
159 db = self.connect_db()
160 dbexe = db[1].execute("\
162 bctype,chtxt,ch,csch,updatetime,chname,status,visible \
169 dls = db[1].fetchall()
173 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
176 def select_by_csch_epg_ch(self, csch):
177 db = self.connect_db()
178 dbexe = db[1].execute("\
180 bctype,chtxt,ch,csch,updatetime,chname,status,visible \
187 dls = db[1].fetchall()
191 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
194 def select_all_epg_ch(self):
195 db = self.connect_db()
196 dbexe = db[1].execute("\
197 SELECT bctype,chtxt,ch,csch,updatetime,chname,status,visible \
203 dls = db[1].fetchall()
207 r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
210 def change_visible_epg_ch(self,chtxt,visible):
211 db = self.connect_db()
213 UPDATE epg_ch SET visible=%s WHERE chtxt=%s",(visible,chtxt))
215 def change_visible_epg_ch(self,chtxt,logonum,logodata):
216 db = self.connect_db()
218 UPDATE epg_ch SET logo"+str(logonum)+"=%s WHERE chtxt=%s",(logodata,chtxt))
220 def set_new_status(self,dhour):
221 db = self.connect_db()
222 dbexe = db[1].execute("UPDATE epg_ch \
226 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
231 def select_get_update_epg_ch(self, dhour):
232 db = self.connect_db()
233 dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
236 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
242 ORDER BY status DESC")
244 #recdblist.printutf8(dbexe)
246 ret = db[1].fetchall()
249 def update_by_bctype_epg_ch(self, bctype):
250 db = self.connect_db()
256 WHERE bctype = %s", (bctype,))
258 def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
259 db = self.connect_db()
265 WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
267 def update_status_by_bctype_epg_ch(self, bctype, status):
268 db = self.connect_db()
274 WHERE bctype = %s", \
278 def update_chname_by_chtxt_epg_ch(self,chtxt,chname):
279 db = self.connect_db()
288 def add_auto_proc_tmp(self,type,title,chtxt):
289 db = self.connect_db()
291 INSERT IGNORE into auto_proc_tmp \
293 values (%s,%s,%s)',(type,title,chtxt))
296 def new_auto_proc_tmp(self):
297 db = self.connect_db()
299 db[1].execute('drop table auto_proc_tmp')
300 except Exception, inst:
301 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
302 recdblist.addCommonlogEX("Error", "new_auto_proc_tmp drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
306 CREATE TABLE auto_proc_tmp \
309 title VARCHAR(100) PRIMARY KEY,\
311 UNIQUE unibayeskey(title)\
313 except Exception, inst:
314 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
315 recdblist.addCommonlogEX("Error", "new_auto_proc_tmp (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
318 def update_auto_proc(self):
319 db = self.connect_db()
321 db[1].execute('INSERT INTO auto_proc SELECT * FROM auto_proc_tmp')
322 except Exception, inst:
323 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
324 recdblist.addCommonlogEX("Error", "update_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
327 def new_auto_proc(self):
328 db = self.connect_db()
330 db[1].execute('drop table auto_proc')
331 except Exception, inst:
332 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
333 recdblist.addCommonlogEX("Error", "new_auto_proc drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
337 CREATE TABLE auto_proc \
340 title VARCHAR(100) PRIMARY KEY,\
342 UNIQUE unibayeskey(title)\
344 except Exception, inst:
345 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
346 recdblist.addCommonlogEX("Error", "new_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
349 def add_auto_proc(self,type,title,chtxt):
350 db = self.connect_db()
352 INSERT IGNORE into auto_proc \
354 values (%s,%s,%s)',(type,title,chtxt))
357 def drop_in_settings(self):
358 db = self.connect_db()
360 db[1].execute('drop table in_settings')
361 except Exception, inst:
362 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
363 recdblist.addCommonlogEX("Error", "drop_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
365 def new_in_settings(self):
366 db = self.connect_db()
369 CREATE TABLE in_settings \
373 auto_opt VARCHAR(20),\
374 auto_del_tmp TINYINT\
376 db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"H\",1)")
377 except Exception, inst:
378 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
379 recdblist.addCommonlogEX("Error", "new_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
382 def select_all_in_settings(self):
383 db = self.connect_db()
384 dbexe = db[1].execute("\
385 SELECT auto_jbk,auto_bayes,auto_del_tmp,auto_opt \
391 dls = db[1].fetchall()
401 def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
402 db = self.connect_db()
404 INSERT IGNORE into in_timeline_log \
405 (chtxt,title,btime,etime,opt,exp,longexp,category) \
406 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
407 ( chtxt, title, btime, etime, opt,exp,longexp,category))
410 def del_in_timeline_log(self, title="", chtxt="", btime=""):
414 db = self.connect_db()
416 DELETE FROM in_timeline_log \
417 WHERE title = %s AND chtxt = %s AND btime = %s", \
418 (title, chtxt, btime))
421 def new_in_timeline_log(self):
422 db = self.connect_db()
425 CREATE TABLE in_timeline_log \
427 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
435 category VARCHAR(100),\
436 UNIQUE uni (chtxt,title,btime,category)\
438 except Exception, inst:
439 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
440 recdblist.addCommonlogEX("Error", "new_in_timeline_log (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
443 def select_chtxt_by_title_timeline_log(self,title):
444 db = self.connect_db()
445 dbexe = db[1].execute("\
447 FROM in_timeline_log \
448 WHERE title LIKE \"%"+title+"%\"\
450 ORDER by sum(1) DESC limit 1")
451 retdb=db[1].fetchall()
458 def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt="" ,counter=-1):
459 db = self.connect_db()
461 INSERT IGNORE into timeline \
462 (type,chtxt,title,btime,etime,deltatime,deltaday,opt,counter) \
463 values (%s,%s,%s,%s,%s,%s,%s,%s,%s)', \
464 (type, chtxt, title, btime, etime, deltatime, deltaday, opt ,counter))
467 def update_epg_timeline(self,type,chtxt,title,btime,epgbtime,epgetime,epgtitle,epgexp):
468 db = self.connect_db()
471 SET epgbtime=%s,epgetime=%s,epgtitle=%s,epgexp=%s \
472 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
473 (epgbtime,epgetime,epgtitle,epgexp,type, chtxt, title, btime))
476 def update_status_change_timeline(self,type,chtxt,title,btime,epgchange):
477 db = self.connect_db()
481 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
482 (epgchange , type, chtxt, title, btime))
485 def update_status_dup_timeline(self,type,chtxt,title,btime,epgduplicate):
486 db = self.connect_db()
489 SET epgduplicate =%s \
490 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
491 (epgduplicate , type, chtxt, title, btime))
494 def del_timeline(self, type="", title="", chtxt="", btime=""):
498 db = self.connect_db()
500 DELETE FROM timeline \
501 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
502 (type, title, chtxt, btime))
505 def select_all_timeline(self):
506 db = self.connect_db()
508 dbr = db[1].execute("\
509 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
511 dbl = db[1].fetchall()
514 recdata = self.getdic_timeline(dbl)
516 def select_bytime_timeline(self, dminutes):
517 db = self.connect_db()
519 #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 )")
520 dbr = db[1].execute("SELECT \
521 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
523 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
524 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
525 dbl = db[1].fetchall()
527 #recdblist.printutf8(dbl)
529 recdatum = self.getdic_timeline(dbl)
531 def select_by_name_time_timeline(self,title,btime,btime2):
532 db = self.connect_db()
534 #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 )")
535 dbr = db[1].execute("SELECT \
536 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
538 WHERE btime > %s AND \
539 btime < %s AND title = %s",(btime,btime2,title))
540 dbl = db[1].fetchall()
542 #recdblist.printutf8(dbl)
544 recdatum = self.getdic_timeline(dbl)
546 def select_bytime_all_timeline(self,btime,etime):
547 db = self.connect_db()
549 #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 )")
550 dbr = db[1].execute("SELECT \
551 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
553 WHERE btime >= %s AND \
554 etime <= %s",(btime,etime))
555 dbl = db[1].fetchall()
557 #recdblist.printutf8(dbl)
559 recdatum = self.getdic_timeline(dbl)
561 def select_byepgtime_all_timeline(self,epgbtime,epgetime):
562 db = self.connect_db()
564 #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 )")
565 dbr = db[1].execute("SELECT \
566 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
568 WHERE epgbtime >= %s AND \
569 epgetime <= %s",(epgbtime,epgetime))
570 dbl = db[1].fetchall()
572 #recdblist.printutf8(dbl)
574 recdatum=self.getdic_timeline(dbl)
576 def select_byepgtime_over_timeline(self,epgbtime,epgetime):
577 db = self.connect_db()
579 #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 )")
580 dbr = db[1].execute("SELECT \
581 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
583 WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"\
584 ,(epgbtime,epgetime))
585 dbl = db[1].fetchall()
587 #recdblist.printutf8(dbl)
589 recdatum=self.getdic_timeline(dbl)
591 def count_schedule_timeline(self, btime, etime):
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.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
601 db[1].execute(dbexe, (btime, etime,btime,etime))
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_schedule_timeline(self,btime,etime):
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.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
622 db[1].execute(dbexe, (btime, etime,btime,etime))
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 count_epgschedule_timeline(self, epgbtime, epgetime):
632 return [te num,bs/cs num]
634 db = self.connect_db()
635 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
636 dbexe = dbexe + " WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"
639 db[1].execute(dbexe, (epgbtime, epgetime))
641 for typet, bctypet, chtxtt, titlet in dbl:
642 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
643 if bctypet.find("cs") > -1:
645 elif bctypet.find("bs") > -1:
647 elif bctypet.find("te") > -1:
651 def search_epgschedule_timeline(self,epgbtime,epgetime):
654 return [(type,bctype,chtxt,title,btime,etime)]
656 db = self.connect_db()
657 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"
658 dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )"
660 db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime))
662 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
663 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
664 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
667 def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
668 db = self.connect_db()
670 #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 )")
671 dbr = db[1].execute("SELECT \
672 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
674 WHERE btime > %s AND \
676 AND chtxt=%s ",(btime,etime,chtxt))
677 dbl = db[1].fetchall()
679 #recdblist.printutf8(dbl)
681 recdatum = self.getdic_timeline(dbl)
683 def getdic_timeline(self,timelinelists):
685 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt ,epgbtimet , epgetimet ,epgtitlet ,epgduplicatet ,epgchanget ,countert in timelinelists:
690 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
691 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
696 ret['epgbtime'] = epgbtimet.strftime("%Y-%m-%d %H:%M:%S")
697 ret['epgetime'] = epgetimet.strftime("%Y-%m-%d %H:%M:%S")
699 ret['epgbtime'] = "2010-01-01 00:00:00"
700 ret['epgetime'] = "2010-01-01 00:00:00"
701 ret['epgtitle'] = epgtitlet
702 ret['epgduplicate'] = epgduplicatet
703 ret['epgchange'] = epgchanget
704 if deltatime == None or deltatime == "":
706 if deltaday == None or deltaday == "":
708 if typet == recdblist.REC_KEYWORD:
709 ret['deltatime'] = deltatime
710 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
711 ret['deltatime'] = deltatime
712 ret['deltaday'] = deltaday
714 ret['counter'] = int(countert)
719 def delete_old_timeline(self, dhour):
720 db = self.connect_db()
722 DELETE FROM timeline \
724 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
727 def new_in_auto_jbk_key(self):
728 db = self.connect_db()
731 CREATE TABLE in_auto_jbk_key \
733 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
734 keyword VARCHAR(200),\
735 auto TINYINT DEFAULT 0,\
737 UNIQUE unijbk (keyword)\
739 except Exception, inst:
740 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
741 recdblist.addCommonlogEX("Error", "new_in_auto_jbk_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
743 def add_in_auto_jbk_key(self,key):
744 db = self.connect_db()
746 INSERT IGNORE into in_auto_jbk_key \
752 def select_all_in_auto_jbk_key(self):
753 db = self.connect_db()
754 dbexe = db[1].execute("\
755 SELECT keyword,auto,opt \
756 FROM in_auto_jbk_key \
760 ret = db[1].fetchall()
763 def drop_in_status(self):
764 db = self.connect_db()
766 db[1].execute('drop table in_status')
767 except Exception, inst:
768 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
769 recdblist.addCommonlogEX("Error", "drop_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
771 def new_in_status(self):
772 db = self.connect_db()
775 CREATE TABLE in_status \
777 ts2avi TINYINT DEFAULT 0,\
778 terec TINYINT DEFAULT 0,\
779 bscsrec TINYINT DEFAULT 0,\
780 b252ts TINYINT DEFAULT 0,\
781 installed TINYINT DEFAULT 0,\
784 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0,0)")
785 except Exception, inst:
786 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
787 recdblist.addCommonlogEX("Error", "new_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
789 def select_all_in_status(self):
790 db = self.connect_db()
791 dbexe = db[1].execute("\
792 SELECT ts2avi,terec,bscsrec,b252ts \
798 dls = db[1].fetchall()
808 def select_version_in_status(self):
809 db = self.connect_db()
812 dbexe = db[1].execute("\
817 dls = db[1].fetchall()
821 version=int(str(r[0]))
822 except Exception, inst:
823 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
824 recdblist.addCommonlogEX("Error", "select_version_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
826 def change_version_in_status(self,version):
827 db = self.connect_db()
829 UPDATE in_status SET version=%s",str(version))
831 def change_ts2avi_in_status(self,i):
836 db = self.connect_db()
838 UPDATE in_status SET ts2avi=ts2avi+%s",i)
840 def change_terec_in_status(self,i):
845 db = self.connect_db()
847 UPDATE in_status SET terec=terec+%s",i)
849 def change_bscsrec_in_status(self,i):
854 db = self.connect_db()
856 UPDATE in_status SET bscsrec=bscsrec+%s",i)
858 def change_b252ts_in_status(self,i):
863 db = self.connect_db()
865 UPDATE in_status SET b252ts=b252ts+%s",i)
867 def select_installed_in_status(self):
868 db = self.connect_db()
869 dbexe = db[1].execute("\
870 SELECT ts2avi,terec,bscsrec,b252ts,installed \
876 dls = db[1].fetchall()
882 def change_installed_in_status(self):
886 db = self.connect_db()
888 UPDATE in_status SET installed=1")
890 def change_chscaned_in_status(self):
894 db = self.connect_db()
896 UPDATE in_status SET installed=2")
898 def new_epg_timeline(self, bctype):
899 db = self.connect_db()
902 DELETE FROM epg_timeline \
903 WHERE bctype = %s", \
905 except Exception, inst:
906 if not ((type(inst)==MySQLdb.ProgrammingError and (inst[0]==1007 or inst[0]==1146))or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
907 recdblist.addCommonlogEX("Error", "new_epg_timeline delete (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
910 CREATE TABLE epg_timeline \
913 channel VARCHAR(100) NOT NULL,\
919 category VARCHAR(100),\
920 UNIQUE unitv(bctype,channel,start,stop,title)\
922 except Exception, inst:
923 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
924 recdblist.addCommonlogEX("Error", "new_epg_timeline (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
927 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
928 db = self.connect_db()
930 INSERT IGNORE INTO epg_timeline \
931 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
932 (bctype, channel, start, stop, title, desc,longdesc,category))
935 def add_multi_epg_timeline(self, tvlists):
937 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
939 db = self.connect_db()
941 INSERT IGNORE INTO epg_timeline \
942 (bctype,channel,start,stop,title,exp,longexp,category) \
943 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
946 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
947 db = self.connect_db()
950 channel,title,start,stop,exp,longexp,category \
957 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
960 retall = db[1].fetchall()
963 def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
964 db = self.connect_db()
967 epg_ch.chtxt,title,start,stop,exp,longexp,category \
970 WHERE epg_ch.chtxt=epg_timeline.channel \
979 ( title LIKE \'%%"+keyword+"%%\' ) \
981 ( exp LIKE \'%%"+keyword+"%%\' ) \
983 ( longexp LIKE \'%%"+keyword+"%%\' ) \
985 dbcmd = db[1].execute(dbexe,(btime, etime))
988 retall = db[1].fetchall()
991 def new_in_auto_bayes_key(self):
992 db = self.connect_db()
994 db[1].execute('CREATE TABLE in_auto_bayes_key \
996 keychar VARCHAR(10),\
998 ratio_rec DECIMAL(32,14),\
999 ratio_all DECIMAL(32,14),\
1000 UNIQUE unibayeskey(keychar,chtxt)\
1002 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
1003 except Exception, inst:
1004 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1005 recdblist.addCommonlogEX("Error", "new_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1008 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
1009 db = self.connect_db()
1010 ratio_rec=str(ratio_rec)
1011 ratio_all=str(ratio_all)
1013 INSERT IGNORE INTO in_auto_bayes_key \
1014 (keychar,chtxt,ratio_rec,ratio_all) \
1015 values (%s,%s,%s,%s)',\
1016 (key,chtxt,ratio_rec,ratio_all))
1018 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
1019 db = self.connect_db()
1020 add_rec_num=str(add_rec_num)
1021 add_all_num=str(add_all_num)
1023 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",\
1024 (add_rec_num,add_all_num,chtxt))
1026 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
1029 db = self.connect_db()
1031 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)\
1034 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1035 beforenum=str(beforenum)
1037 db = self.connect_db()
1039 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)\
1042 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1043 db = self.connect_db()
1044 beforenumf=beforenum
1045 beforenum=str(beforenum)
1046 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1047 VALUES (%s,%s,%s,%s)\
1048 ON DUPLICATE KEY UPDATE \
1049 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
1051 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
1053 list={key:addnum}のリスト
1055 beforenumf=beforenum
1056 beforenum=str(beforenum)
1057 db = self.connect_db()
1058 for i,j in list.items():
1059 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
1061 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1062 VALUES (%s,%s,%s,%s)\
1063 ON DUPLICATE KEY UPDATE \
1064 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
1065 except Exception, inst:
1066 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1067 recdblist.addCommonlogEX("Error", "change_multi_ratio_all_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1069 def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1070 beforenum=str(beforenum)
1072 db = self.connect_db()
1074 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)\
1077 def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1078 db = self.connect_db()
1079 beforenumf=beforenum
1080 beforenum=str(beforenum)
1081 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1082 VALUES (%s,%s,%s,%s)\
1083 ON DUPLICATE KEY UPDATE \
1084 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
1086 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
1087 beforenumf=beforenum
1088 beforenum=str(beforenum)
1089 db = self.connect_db()
1090 for i,j in list.items():
1091 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
1093 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1094 VALUES (%s,%s,%s,%s)\
1095 ON DUPLICATE KEY UPDATE \
1096 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
1097 except Exception, inst:
1098 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1099 recdblist.addCommonlogEX("Error", "change_multi_ratio_rec_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1101 def select_by_key_in_auto_bayes_key(self,key,chtxt):
1102 db = self.connect_db()
1103 dbexe = db[1].execute("\
1104 SELECT keychar,chtxt,ratio_rec,ratio_all \
1105 FROM in_auto_bayes_key \
1106 WHERE keychar = %s AND chtxt = %s", \
1110 dls = db[1].fetchall()
1116 def new_auto_timeline_keyword(self):
1117 db = self.connect_db()
1120 CREATE TABLE auto_timeline_keyword \
1122 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1124 title VARCHAR(100),\
1127 UNIQUE uni (chtxt,title,btime,etime)\
1129 except Exception, inst:
1130 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1131 recdblist.addCommonlogEX("Error", "new_auto_timeline_keyword (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1132 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
1133 db = self.connect_db()
1135 INSERT IGNORE into auto_timeline_keyword \
1136 (chtxt,title,btime,etime) \
1137 values (%s,%s,%s,%s)', \
1138 (chtxt, title, btime, etime))
1141 def delete_old_auto_timeline_keyword(self, dhour):
1142 db = self.connect_db()
1144 DELETE FROM auto_timeline_keyword \
1146 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1148 def new_auto_timeline_bayes(self):
1149 db = self.connect_db()
1152 CREATE TABLE auto_timeline_bayes \
1154 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1156 title VARCHAR(100),\
1160 UNIQUE uni (chtxt,title,btime,etime)\
1162 except Exception, inst:
1163 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1164 recdblist.addCommonlogEX("Error", "new_auto_timeline_bayes (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1165 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
1166 db = self.connect_db()
1168 INSERT IGNORE into auto_timeline_bayes \
1169 (chtxt,title,btime,etime,point) \
1170 values (%s,%s,%s,%s,%s)', \
1171 (chtxt, title, btime, etime,point))
1173 def delete_old_auto_timeline_bayes(self, dhour):
1174 db = self.connect_db()
1176 DELETE FROM auto_timeline_bayes \
1178 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1180 def update_db_to93(self):
1181 db = self.connect_db()
1182 self.drop_in_settings()
1183 self.new_in_settings()
1185 ALTER TABLE timeline ADD epgtitle VARCHAR(100),\
1186 ADD epgbtime DATETIME,\
1187 ADD epgetime DATETIME,\
1188 ADD epgduplicate TINYINT DEFAULT 0,\
1189 ADD epgchange TINYINT DEFAULT 0")
1191 ALTER TABLE in_status ADD version TINYINT")
1193 self.change_version_in_status("93")
1194 def update_db_93to94(self):
1195 db = self.connect_db()
1196 self.drop_in_settings()
1197 self.new_in_settings()
1199 ALTER TABLE timeline ADD counter TINYINT DEFAULT -1")
1201 self.change_version_in_status("94")
1202 def update_db_94to95(self):
1203 db = self.connect_db()
1204 self.drop_in_settings()
1205 self.new_in_settings()
1207 ALTER TABLE timeline ADD epgexp VARCHAR(200)")
1209 self.change_version_in_status("95")
1210 def update_db_95to96(self):
1211 db = self.connect_db()
1212 self.drop_in_settings()
1213 self.new_in_settings()
1215 self.change_version_in_status("96")
1216 def update_db_96to98(self):
1217 db = self.connect_db()
1218 self.drop_in_settings()
1219 self.new_in_settings()
1221 self.change_version_in_status("98")
1222 def update_db_98to100(self):
1223 ###ここで前のepg_chをバックアップしてchtxtの変換をする必要がある。
1224 db = self.connect_db()
1225 self.drop_in_settings()
1226 self.new_in_settings()
1228 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')")
1230 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'")
1232 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'")
1235 ALTER TABLE epg_ch DROP ontv")
1239 ALTER TABLE epg_ch ADD logo0 BLOB,\
1247 ALTER TABLE in_auto_jbk_key ADD auto TINYINT DEFAULT 0")
1249 ALTER TABLE in_auto_jbk_key ADD opt VARCHAR(20) DEFAULT \"\"")
1251 self.change_installed_in_status()#チャンネルスキャンをさせる
1252 self.change_version_in_status("100")
1253 def update_db_100to101(self):
1254 db = self.connect_db()
1255 self.drop_in_settings()
1256 self.new_in_settings()
1259 ALTER TABLE epg_ch ADD logo0 BLOB,\
1269 ALTER TABLE timeline ADD epgcategory VARCHAR(100)\
1272 self.change_installed_in_status()#チャンネルスキャンをさせる
1273 self.change_version_in_status("101")