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 isshow 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 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 change_isshow_epg_ch(self,chtxt,isshow):
183 db = self.connect_db()
185 UPDATE epg_ch SET isshow=%s WHERE chtxt=%s",(isshow,chtxt))
187 def set_new_status(self,dhour):
188 db = self.connect_db()
189 dbexe = db[1].execute("UPDATE epg_ch \
193 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
198 def select_get_update_epg_ch(self, dhour):
199 db = self.connect_db()
200 dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
203 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
209 ORDER BY status DESC")
211 #recdblist.printutf8(dbexe)
213 ret = db[1].fetchall()
216 def update_by_bctype_epg_ch(self, bctype):
217 db = self.connect_db()
223 WHERE bctype = %s", (bctype,))
225 def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
226 db = self.connect_db()
232 WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
234 def update_status_by_bctype_epg_ch(self, bctype, status):
235 db = self.connect_db()
241 WHERE bctype = %s", \
245 def update_chname_by_chtxt_epg_ch(self,chtxt,chname):
246 db = self.connect_db()
255 def add_auto_proc_tmp(self,type,title,chtxt):
256 db = self.connect_db()
258 INSERT IGNORE into auto_proc_tmp \
260 values (%s,%s,%s)',(type,title,chtxt))
263 def new_auto_proc_tmp(self):
264 db = self.connect_db()
266 db[1].execute('drop table auto_proc_tmp')
267 except Exception, inst:
268 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
269 recdblist.addCommonlogEX("Error", "new_auto_proc_tmp drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
273 CREATE TABLE auto_proc_tmp \
276 title VARCHAR(100) PRIMARY KEY,\
278 UNIQUE unibayeskey(title)\
280 except Exception, inst:
281 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
282 recdblist.addCommonlogEX("Error", "new_auto_proc_tmp (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
285 def update_auto_proc(self):
286 db = self.connect_db()
288 db[1].execute('INSERT INTO auto_proc SELECT * FROM auto_proc_tmp')
289 except Exception, inst:
290 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
291 recdblist.addCommonlogEX("Error", "update_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
294 def new_auto_proc(self):
295 db = self.connect_db()
297 db[1].execute('drop table auto_proc')
298 except Exception, inst:
299 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
300 recdblist.addCommonlogEX("Error", "new_auto_proc drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
304 CREATE TABLE auto_proc \
307 title VARCHAR(100) PRIMARY KEY,\
309 UNIQUE unibayeskey(title)\
311 except Exception, inst:
312 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
313 recdblist.addCommonlogEX("Error", "new_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
316 def add_auto_proc(self,type,title,chtxt):
317 db = self.connect_db()
319 INSERT IGNORE into auto_proc \
321 values (%s,%s,%s)',(type,title,chtxt))
324 def drop_in_settings(self):
325 db = self.connect_db()
327 db[1].execute('drop table in_settings')
328 except Exception, inst:
329 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
330 recdblist.addCommonlogEX("Error", "drop_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
332 def new_in_settings(self):
333 db = self.connect_db()
336 CREATE TABLE in_settings \
340 auto_opt VARCHAR(20),\
341 auto_del_tmp TINYINT\
343 db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"H\",1)")
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_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
349 def select_all_in_settings(self):
350 db = self.connect_db()
351 dbexe = db[1].execute("\
352 SELECT auto_jbk,auto_bayes,auto_del_tmp,auto_opt \
358 dls = db[1].fetchall()
368 def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
369 db = self.connect_db()
371 INSERT IGNORE into in_timeline_log \
372 (chtxt,title,btime,etime,opt,exp,longexp,category) \
373 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
374 ( chtxt, title, btime, etime, opt,exp,longexp,category))
377 def del_in_timeline_log(self, title="", chtxt="", btime=""):
381 db = self.connect_db()
383 DELETE FROM in_timeline_log \
384 WHERE title = %s AND chtxt = %s AND btime = %s", \
385 (title, chtxt, btime))
388 def new_in_timeline_log(self):
389 db = self.connect_db()
392 CREATE TABLE in_timeline_log \
394 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
402 category VARCHAR(100),\
403 UNIQUE uni (chtxt,title,btime,category)\
405 except Exception, inst:
406 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
407 recdblist.addCommonlogEX("Error", "new_in_timeline_log (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
410 def select_chtxt_by_title_timeline_log(self,title):
411 db = self.connect_db()
412 dbexe = db[1].execute("\
414 FROM in_timeline_log \
415 WHERE title LIKE \"%"+title+"%\"\
417 ORDER by sum(1) DESC limit 1")
418 retdb=db[1].fetchall()
425 def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt="" ,counter=-1):
426 db = self.connect_db()
428 INSERT IGNORE into timeline \
429 (type,chtxt,title,btime,etime,deltatime,deltaday,opt,counter) \
430 values (%s,%s,%s,%s,%s,%s,%s,%s,%s)', \
431 (type, chtxt, title, btime, etime, deltatime, deltaday, opt ,counter))
434 def update_epg_timeline(self,type,chtxt,title,btime,epgbtime,epgetime,epgtitle,epgexp):
435 db = self.connect_db()
438 SET epgbtime=%s,epgetime=%s,epgtitle=%s,epgexp=%s \
439 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
440 (epgbtime,epgetime,epgtitle,epgexp,type, chtxt, title, btime))
443 def update_status_change_timeline(self,type,chtxt,title,btime,epgchange):
444 db = self.connect_db()
448 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
449 (epgchange , type, chtxt, title, btime))
452 def update_status_dup_timeline(self,type,chtxt,title,btime,epgduplicate):
453 db = self.connect_db()
456 SET epgduplicate =%s \
457 WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
458 (epgduplicate , type, chtxt, title, btime))
461 def del_timeline(self, type="", title="", chtxt="", btime=""):
465 db = self.connect_db()
467 DELETE FROM timeline \
468 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
469 (type, title, chtxt, btime))
472 def select_all_timeline(self):
473 db = self.connect_db()
475 dbr = db[1].execute("\
476 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
478 dbl = db[1].fetchall()
481 recdata = self.getdic_timeline(dbl)
483 def select_bytime_timeline(self, dminutes):
484 db = self.connect_db()
486 #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 )")
487 dbr = db[1].execute("SELECT \
488 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
490 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
491 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
492 dbl = db[1].fetchall()
494 #recdblist.printutf8(dbl)
496 recdatum = self.getdic_timeline(dbl)
498 def select_by_name_time_timeline(self,title,btime,btime2):
499 db = self.connect_db()
501 #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 )")
502 dbr = db[1].execute("SELECT \
503 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
505 WHERE btime > %s AND \
506 btime < %s AND title = %s",(btime,btime2,title))
507 dbl = db[1].fetchall()
509 #recdblist.printutf8(dbl)
511 recdatum = self.getdic_timeline(dbl)
513 def select_bytime_all_timeline(self,btime,etime):
514 db = self.connect_db()
516 #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 )")
517 dbr = db[1].execute("SELECT \
518 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
520 WHERE btime > %s AND \
521 etime < %s",(btime,etime))
522 dbl = db[1].fetchall()
524 #recdblist.printutf8(dbl)
526 recdatum = self.getdic_timeline(dbl)
528 def select_byepgtime_all_timeline(self,epgbtime,epgetime):
529 db = self.connect_db()
531 #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 )")
532 dbr = db[1].execute("SELECT \
533 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
535 WHERE epgbtime >= %s AND \
536 epgetime <= %s",(epgbtime,epgetime))
537 dbl = db[1].fetchall()
539 #recdblist.printutf8(dbl)
541 recdatum=self.getdic_timeline(dbl)
543 def select_byepgtime_over_timeline(self,epgbtime,epgetime):
544 db = self.connect_db()
546 #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 )")
547 dbr = db[1].execute("SELECT \
548 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
550 WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"\
551 ,(epgbtime,epgetime))
552 dbl = db[1].fetchall()
554 #recdblist.printutf8(dbl)
556 recdatum=self.getdic_timeline(dbl)
558 def count_schedule_timeline(self, btime, etime):
561 return [te num,bs/cs num]
563 db = self.connect_db()
564 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
565 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
568 db[1].execute(dbexe, (btime, etime,btime,etime))
570 for typet, bctypet, chtxtt, titlet in dbl:
571 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
572 if bctypet.find("cs") > -1:
574 elif bctypet.find("bs") > -1:
576 elif bctypet.find("te") > -1:
580 def search_schedule_timeline(self,btime,etime):
583 return [(type,bctype,chtxt,title,btime,etime)]
585 db = self.connect_db()
586 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"
587 dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
589 db[1].execute(dbexe, (btime, etime,btime,etime))
591 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
592 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
593 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
596 def count_epgschedule_timeline(self, epgbtime, epgetime):
599 return [te num,bs/cs num]
601 db = self.connect_db()
602 dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
603 dbexe = dbexe + " WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"
606 db[1].execute(dbexe, (epgbtime, epgetime))
608 for typet, bctypet, chtxtt, titlet in dbl:
609 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
610 if bctypet.find("cs") > -1:
612 elif bctypet.find("bs") > -1:
614 elif bctypet.find("te") > -1:
618 def search_epgschedule_timeline(self,epgbtime,epgetime):
621 return [(type,bctype,chtxt,title,btime,etime)]
623 db = self.connect_db()
624 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"
625 dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )"
627 db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime))
629 for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
630 if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
631 ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
634 def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
635 db = self.connect_db()
637 #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 )")
638 dbr = db[1].execute("SELECT \
639 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
641 WHERE btime > %s AND \
643 AND chtxt=%s ",(btime,etime,chtxt))
644 dbl = db[1].fetchall()
646 #recdblist.printutf8(dbl)
648 recdatum = self.getdic_timeline(dbl)
650 def getdic_timeline(self,timelinelists):
652 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt ,epgbtimet , epgetimet ,epgtitlet ,epgduplicatet ,epgchanget ,countert in timelinelists:
657 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
658 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
663 ret['epgbtime'] = epgbtimet.strftime("%Y-%m-%d %H:%M:%S")
664 ret['epgetime'] = epgetimet.strftime("%Y-%m-%d %H:%M:%S")
666 ret['epgbtime'] = "2010-01-01 00:00:00"
667 ret['epgetime'] = "2010-01-01 00:00:00"
668 ret['epgtitle'] = epgtitlet
669 ret['epgduplicate'] = epgduplicatet
670 ret['epgchange'] = epgchanget
671 if deltatime == None or deltatime == "":
673 if deltaday == None or deltaday == "":
675 if typet == recdblist.REC_KEYWORD:
676 ret['deltatime'] = deltatime
677 elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
678 ret['deltatime'] = deltatime
679 ret['deltaday'] = deltaday
681 ret['counter'] = int(countert)
686 def delete_old_timeline(self, dhour):
687 db = self.connect_db()
689 DELETE FROM timeline \
691 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
694 def new_in_auto_jbk_key(self):
695 db = self.connect_db()
698 CREATE TABLE in_auto_jbk_key \
700 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
701 keyword VARCHAR(200),\
702 auto TINYINT DEFAULT 0,\
704 UNIQUE unijbk (keyword)\
706 except Exception, inst:
707 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
708 recdblist.addCommonlogEX("Error", "new_in_auto_jbk_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
710 def add_in_auto_jbk_key(self,key):
711 db = self.connect_db()
713 INSERT IGNORE into in_auto_jbk_key \
719 def select_all_in_auto_jbk_key(self):
720 db = self.connect_db()
721 dbexe = db[1].execute("\
722 SELECT keyword,auto,opt \
723 FROM in_auto_jbk_key \
727 ret = db[1].fetchall()
730 def drop_in_status(self):
731 db = self.connect_db()
733 db[1].execute('drop table in_status')
734 except Exception, inst:
735 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
736 recdblist.addCommonlogEX("Error", "drop_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
738 def new_in_status(self):
739 db = self.connect_db()
742 CREATE TABLE in_status \
744 ts2avi TINYINT DEFAULT 0,\
745 terec TINYINT DEFAULT 0,\
746 bscsrec TINYINT DEFAULT 0,\
747 b252ts TINYINT DEFAULT 0,\
748 installed TINYINT DEFAULT 0,\
751 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0,0)")
752 except Exception, inst:
753 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
754 recdblist.addCommonlogEX("Error", "new_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
756 def select_all_in_status(self):
757 db = self.connect_db()
758 dbexe = db[1].execute("\
759 SELECT ts2avi,terec,bscsrec,b252ts \
765 dls = db[1].fetchall()
775 def select_version_in_status(self):
776 db = self.connect_db()
779 dbexe = db[1].execute("\
784 dls = db[1].fetchall()
788 version=int(str(r[0]))
789 except Exception, inst:
790 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
791 recdblist.addCommonlogEX("Error", "select_version_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
793 def change_version_in_status(self,version):
794 db = self.connect_db()
796 UPDATE in_status SET version=%s",str(version))
798 def change_ts2avi_in_status(self,i):
803 db = self.connect_db()
805 UPDATE in_status SET ts2avi=ts2avi+%s",i)
807 def change_terec_in_status(self,i):
812 db = self.connect_db()
814 UPDATE in_status SET terec=terec+%s",i)
816 def change_bscsrec_in_status(self,i):
821 db = self.connect_db()
823 UPDATE in_status SET bscsrec=bscsrec+%s",i)
825 def change_b252ts_in_status(self,i):
830 db = self.connect_db()
832 UPDATE in_status SET b252ts=b252ts+%s",i)
834 def select_installed_in_status(self):
835 db = self.connect_db()
836 dbexe = db[1].execute("\
837 SELECT ts2avi,terec,bscsrec,b252ts,installed \
843 dls = db[1].fetchall()
849 def change_installed_in_status(self):
853 db = self.connect_db()
855 UPDATE in_status SET installed=1")
857 def change_chscaned_in_status(self):
861 db = self.connect_db()
863 UPDATE in_status SET installed=2")
865 def new_epg_timeline(self, bctype):
866 db = self.connect_db()
869 DELETE FROM epg_timeline \
870 WHERE bctype = %s", \
872 except Exception, inst:
873 if not ((type(inst)==MySQLdb.ProgrammingError and (inst[0]==1007 or inst[0]==1146))or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
874 recdblist.addCommonlogEX("Error", "new_epg_timeline delete (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
877 CREATE TABLE epg_timeline \
880 channel VARCHAR(100) NOT NULL,\
886 category VARCHAR(100),\
887 UNIQUE unitv(bctype,channel,start,stop,title)\
889 except Exception, inst:
890 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
891 recdblist.addCommonlogEX("Error", "new_epg_timeline (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
894 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
895 db = self.connect_db()
897 INSERT IGNORE INTO epg_timeline \
898 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
899 (bctype, channel, start, stop, title, desc,longdesc,category))
902 def add_multi_epg_timeline(self, tvlists):
904 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
906 db = self.connect_db()
908 INSERT IGNORE INTO epg_timeline \
909 (bctype,channel,start,stop,title,exp,longexp,category) \
910 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
913 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
914 db = self.connect_db()
917 channel,title,start,stop,exp,longexp,category \
924 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
927 retall = db[1].fetchall()
930 def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
931 db = self.connect_db()
934 epg_ch.chtxt,title,start,stop,exp,longexp,category \
937 WHERE epg_ch.chtxt=epg_timeline.channel \
944 ( title LIKE \'%%"+keyword+"%%\' ) \
946 ( exp LIKE \'%%"+keyword+"%%\' ) \
948 ( longexp LIKE \'%%"+keyword+"%%\' ) \
950 dbcmd = db[1].execute(dbexe,(btime, etime))
953 retall = db[1].fetchall()
956 def new_in_auto_bayes_key(self):
957 db = self.connect_db()
959 db[1].execute('CREATE TABLE in_auto_bayes_key \
961 keychar VARCHAR(10),\
963 ratio_rec DECIMAL(32,14),\
964 ratio_all DECIMAL(32,14),\
965 UNIQUE unibayeskey(keychar,chtxt)\
967 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
968 except Exception, inst:
969 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
970 recdblist.addCommonlogEX("Error", "new_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
973 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
974 db = self.connect_db()
975 ratio_rec=str(ratio_rec)
976 ratio_all=str(ratio_all)
978 INSERT IGNORE INTO in_auto_bayes_key \
979 (keychar,chtxt,ratio_rec,ratio_all) \
980 values (%s,%s,%s,%s)',\
981 (key,chtxt,ratio_rec,ratio_all))
983 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
984 db = self.connect_db()
985 add_rec_num=str(add_rec_num)
986 add_all_num=str(add_all_num)
988 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",\
989 (add_rec_num,add_all_num,chtxt))
991 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
994 db = self.connect_db()
996 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)\
999 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
1000 beforenum=str(beforenum)
1002 db = self.connect_db()
1004 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)\
1007 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
1008 db = self.connect_db()
1009 beforenumf=beforenum
1010 beforenum=str(beforenum)
1011 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1012 VALUES (%s,%s,%s,%s)\
1013 ON DUPLICATE KEY UPDATE \
1014 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
1016 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
1018 list={key:addnum}のリスト
1020 beforenumf=beforenum
1021 beforenum=str(beforenum)
1022 db = self.connect_db()
1023 for i,j in list.items():
1024 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
1026 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1027 VALUES (%s,%s,%s,%s)\
1028 ON DUPLICATE KEY UPDATE \
1029 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
1030 except Exception, inst:
1031 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1032 recdblist.addCommonlogEX("Error", "change_multi_ratio_all_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1034 def change_ratio_rec_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_rec=CONVERT(ratio_rec*%s/(%s+%s),DECIMAL(32,14)) WHERE chtxt=%s AND NOT (keychar=\"NUM\")",(beforenum,newnum,beforenum,chtxt)\
1042 def change_ratio_rec_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_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
1051 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
1052 beforenumf=beforenum
1053 beforenum=str(beforenum)
1054 db = self.connect_db()
1055 for i,j in list.items():
1056 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
1058 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
1059 VALUES (%s,%s,%s,%s)\
1060 ON DUPLICATE KEY UPDATE \
1061 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
1062 except Exception, inst:
1063 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1064 recdblist.addCommonlogEX("Error", "change_multi_ratio_rec_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1066 def select_by_key_in_auto_bayes_key(self,key,chtxt):
1067 db = self.connect_db()
1068 dbexe = db[1].execute("\
1069 SELECT keychar,chtxt,ratio_rec,ratio_all \
1070 FROM in_auto_bayes_key \
1071 WHERE keychar = %s AND chtxt = %s", \
1075 dls = db[1].fetchall()
1081 def new_auto_timeline_keyword(self):
1082 db = self.connect_db()
1085 CREATE TABLE auto_timeline_keyword \
1087 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1089 title VARCHAR(100),\
1092 UNIQUE uni (chtxt,title,btime,etime)\
1094 except Exception, inst:
1095 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1096 recdblist.addCommonlogEX("Error", "new_auto_timeline_keyword (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1097 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
1098 db = self.connect_db()
1100 INSERT IGNORE into auto_timeline_keyword \
1101 (chtxt,title,btime,etime) \
1102 values (%s,%s,%s,%s)', \
1103 (chtxt, title, btime, etime))
1106 def delete_old_auto_timeline_keyword(self, dhour):
1107 db = self.connect_db()
1109 DELETE FROM auto_timeline_keyword \
1111 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1113 def new_auto_timeline_bayes(self):
1114 db = self.connect_db()
1117 CREATE TABLE auto_timeline_bayes \
1119 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
1121 title VARCHAR(100),\
1125 UNIQUE uni (chtxt,title,btime,etime)\
1127 except Exception, inst:
1128 if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
1129 recdblist.addCommonlogEX("Error", "new_auto_timeline_bayes (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
1130 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
1131 db = self.connect_db()
1133 INSERT IGNORE into auto_timeline_bayes \
1134 (chtxt,title,btime,etime,point) \
1135 values (%s,%s,%s,%s,%s)', \
1136 (chtxt, title, btime, etime,point))
1138 def delete_old_auto_timeline_bayes(self, dhour):
1139 db = self.connect_db()
1141 DELETE FROM auto_timeline_bayes \
1143 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
1145 def update_db_to93(self):
1146 db = self.connect_db()
1147 self.drop_in_settings()
1148 self.new_in_settings()
1150 ALTER TABLE timeline ADD epgtitle VARCHAR(100),\
1151 ADD epgbtime DATETIME,\
1152 ADD epgetime DATETIME,\
1153 ADD epgduplicate TINYINT DEFAULT 0,\
1154 ADD epgchange TINYINT DEFAULT 0")
1156 ALTER TABLE in_status ADD version TINYINT")
1158 self.change_version_in_status("93")
1159 def update_db_93to94(self):
1160 db = self.connect_db()
1161 self.drop_in_settings()
1162 self.new_in_settings()
1164 ALTER TABLE timeline ADD counter TINYINT DEFAULT -1")
1166 self.change_version_in_status("94")
1167 def update_db_94to95(self):
1168 db = self.connect_db()
1169 self.drop_in_settings()
1170 self.new_in_settings()
1172 ALTER TABLE timeline ADD epgexp VARCHAR(200)")
1174 self.change_version_in_status("95")
1175 def update_db_95to96(self):
1176 db = self.connect_db()
1177 self.drop_in_settings()
1178 self.new_in_settings()
1180 self.change_version_in_status("96")
1181 def update_db_96to98(self):
1182 db = self.connect_db()
1183 self.drop_in_settings()
1184 self.new_in_settings()
1186 self.change_version_in_status("98")
1187 def update_db_98to100(self):
1188 ###ここで前のepg_chをバックアップしてchtxtの変換をする必要がある。
1189 db = self.connect_db()
1190 self.drop_in_settings()
1191 self.new_in_settings()
1193 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')")
1195 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'")
1197 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'")
1199 ALTER TABLE epg_ch DROP ontv")
1201 ALTER TABLE in_auto_jbk_key ADD auto TINYINT DEFAULT 0")
1203 ALTER TABLE in_auto_jbk_key ADD opt VARCHAR(20) DEFAULT \"\"")
1205 self.change_installed_in_status()#チャンネルスキャンをさせる
1206 self.change_version_in_status("100")