3 # Rec10 TS Recording Tools
4 # Copyright (C) 2009 Yukikaze
6 from decimal import Decimal
13 def __init__(self, dbname, user, passwd, host="localhost", port=3306):
17 self.dbpasswd = passwd
20 con = MySQLdb.connect(user=user, passwd=passwd)
22 cur.execute('CREATE DATABASE ' + dbname + " DEFAULT CHARACTER SET utf8")
27 db = self.connect_db()
30 CREATE TABLE timeline \
32 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
38 deltatime VARCHAR(5),\
41 UNIQUE uni (type,chtxt,title,btime,deltaday)\
46 self.new_epg_timeline("")
48 self.new_in_timeline_log()
49 self.new_in_auto_bayes_key()
50 self.new_in_auto_jbk_key()
53 self.new_auto_timeline_bayes()
54 self.new_auto_timeline_keyword()
59 con = MySQLdb.connect(db=self.dbname, host=self.dbhost, port=self.dbport, user=self.dbusr, passwd=self.dbpasswd, charset="utf8")
61 cur.execute('set names utf8;')
63 def close_db(self, db):
67 db = self.connect_db()
73 ontv VARCHAR(30) PRIMARY KEY,\
84 def add_epg_ch(self, bctype, ontv, chtxt, ch, csch, updatetime):
85 db = self.connect_db()
88 VALUES (%s,%s,%s,%s,%s,"",%s,%s)', \
89 (bctype, ontv, chtxt, ch, csch, updatetime, "1"))
91 def select_by_ontv_epg_ch(self, ontv):
92 db = self.connect_db()
93 dbexe = db[1].execute("\
94 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
101 dls = db[1].fetchall()
105 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
108 def select_by_chtxt_epg_ch(self, chtxt):
109 db = self.connect_db()
110 dbexe = db[1].execute("\
111 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
118 dls = db[1].fetchall()
122 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
125 def select_by_bctype_epg_ch(self, bctype):
126 db = self.connect_db()
127 dbexe = db[1].execute("\
128 SELECT bctype,ontv,chtxt,ch,csch,updatetime,status \
130 WHERE bctype = %s", \
135 dls = db[1].fetchall()
140 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
144 def select_by_ch_epg_ch(self, ch):
145 db = self.connect_db()
146 dbexe = db[1].execute("\
148 bctype,ontv,chtxt,ch,csch,updatetime \
155 dls = db[1].fetchall()
159 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
162 def select_all_epg_ch(self):
163 db = self.connect_db()
164 dbexe = db[1].execute("\
165 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
171 dls = db[1].fetchall()
175 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
178 def select_get_update_epg_ch(self, dhour):
179 db = self.connect_db()
180 dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
183 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
189 ORDER BY status DESC")
193 ret = db[1].fetchall()
196 def update_by_bctype_epg_ch(self, bctype):
197 db = self.connect_db()
203 WHERE bctype = %s", (bctype,))
205 def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
206 db = self.connect_db()
212 WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
214 def update_status_by_bctype_epg_ch(self, bctype, status):
215 db = self.connect_db()
220 WHERE bctype = %s", \
224 def update_chname_by_ontv_epg_ch(self,ontv,chname):
225 db = self.connect_db()
234 def add_auto_proc(self,type,title):
235 db = self.connect_db()
237 INSERT IGNORE into auto_proc \
243 def new_auto_proc(self):
244 db = self.connect_db()
246 db[1].execute('drop table auto_proc')
251 CREATE TABLE auto_proc \
254 title VARCHAR(100) PRIMARY KEY,\
255 UNIQUE unibayeskey(title)\
260 def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
261 db = self.connect_db()
263 INSERT IGNORE into in_timeline_log \
264 (chtxt,title,btime,etime,opt,exp,longexp,category) \
265 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
266 ( chtxt, title, btime, etime, opt,exp,longexp,category))
269 def del_in_timeline_log(self, title="", chtxt="", btime=""):
273 db = self.connect_db()
275 DELETE FROM in_timeline_log \
276 WHERE title = %s AND chtxt = %s AND btime = %s", \
277 (title, chtxt, btime))
280 def new_in_timeline_log(self):
281 db = self.connect_db()
284 CREATE TABLE in_timeline_log \
286 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
294 category VARCHAR(100),\
295 UNIQUE uni (chtxt,title,btime,category)\
300 def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
301 db = self.connect_db()
303 INSERT IGNORE into timeline \
304 (type,chtxt,title,btime,etime,deltatime,deltaday,opt) \
305 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
306 (type, chtxt, title, btime, etime, deltatime, deltaday, opt))
309 def del_timeline(self, type="", title="", chtxt="", btime=""):
313 db = self.connect_db()
315 DELETE FROM timeline \
316 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
317 (type, title, chtxt, btime))
320 def select_all_timeline(self):
321 db = self.connect_db()
323 dbr = db[1].execute("\
324 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
326 dbl = db[1].fetchall()
329 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
333 ret['title'] = title.encode('utf-8')
334 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
335 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
339 ret['deltatime'] = ""
341 if deltatime == None:
346 ret['deltatime'] = deltatime
347 elif typet == 'keyevery':
348 ret['deltatime'] = deltatime
349 ret['deltaday'] = deltaday
353 def select_bytime_timeline(self, dminutes):
354 db = self.connect_db()
356 #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 )")
357 dbr = db[1].execute("SELECT \
358 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
360 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
361 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
362 dbl = db[1].fetchall()
366 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
370 ret['title'] = title.encode('utf-8')
371 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
372 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
376 if deltatime == None or deltatime == "":
378 if deltaday == None or deltaday == "":
381 ret['deltatime'] = deltatime
382 elif typet == 'keyevery':
383 ret['deltatime'] = deltatime
384 ret['deltaday'] = deltaday
387 def delete_old_timeline(self, dhour):
388 db = self.connect_db()
390 DELETE FROM timeline \
392 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
395 def new_in_auto_jbk_key(self):
396 db = self.connect_db()
399 CREATE TABLE in_auto_jbk_key \
401 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
402 keyword VARCHAR(200),\
403 UNIQUE unijbk (keyword)\
408 def add_in_auto_jbk_key(self,key):
409 db = self.connect_db()
411 INSERT IGNORE into in_auto_jbk_key \
417 def select_all_in_auto_jbk_key(self):
418 db = self.connect_db()
419 dbexe = db[1].execute("\
421 FROM in_auto_jbk_key \
425 ret = db[1].fetchall()
428 def new_in_status(self):
429 db = self.connect_db()
432 CREATE TABLE in_status \
434 ts2avi TINYINT DEFAULT 0,\
435 terec TINYINT DEFAULT 0,\
436 bscsrec TINYINT DEFAULT 0,\
437 b252ts TINYINT DEFAULT 0\
439 db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0)")
443 def select_all_in_status(self):
444 db = self.connect_db()
445 dbexe = db[1].execute("\
446 SELECT ts2avi,terec,bscsrec,b252ts \
452 dls = db[1].fetchall()
463 def change_ts2avi_in_status(self,i):
468 db = self.connect_db()
470 UPDATE in_status SET ts2avi=ts2avi+%s",i)
472 def change_terec_in_status(self,i):
477 db = self.connect_db()
479 UPDATE in_status SET terec=terec+%s",i)
481 def change_bscsrec_in_status(self,i):
486 db = self.connect_db()
488 UPDATE in_status SET bscsrec=bscsrec+%s",i)
490 def change_b252ts_in_status(self,i):
495 db = self.connect_db()
497 UPDATE in_status SET b252ts=b252ts+%s",i)
499 def new_epg_timeline(self, bctype):
500 db = self.connect_db()
503 DELETE FROM epg_timeline \
504 WHERE bctype = %s", \
510 CREATE TABLE epg_timeline \
513 channel VARCHAR(100) NOT NULL,\
519 category VARCHAR(100),\
520 UNIQUE unitv(bctype,channel,start,stop,title)\
526 def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
527 db = self.connect_db()
529 INSERT IGNORE INTO epg_timeline \
530 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
531 (bctype, channel, start, stop, title, desc,longdesc,category))
534 def add_multi_epg_timeline(self, tvlists):
536 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
538 db = self.connect_db()
540 INSERT IGNORE INTO epg_timeline \
541 (bctype,channel,start,stop,title,exp,longexp,category) \
542 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
545 def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
546 db = self.connect_db()
549 chdata.chtxt,title,start,stop,exp,longexp,category \
552 WHERE chdata.ontv=epg_timeline.channel \
559 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
562 retall = db[1].fetchall()
565 def select_by_time_auto_suggest_epg_timeline(self,keyword,btime,etime):
566 db = self.connect_db()
569 chdata.chtxt,title,start,stop,exp,longexp,category \
572 WHERE chdata.ontv=epg_timeline.channel \
579 ( title LIKE \'%%"+keyword+"%%\' ) \
581 ( exp LIKE \'%%"+keyword+"%%\' ) \
583 ( longexp LIKE \'%%"+keyword+"%%\' ) \
585 dbcmd = db[1].execute(dbexe,(btime, etime))
588 retall = db[1].fetchall()
591 #def new_epg_ch(self, bctype):
592 # db = self.connect_db()
594 # db[1].execute("DELETE FROM ch WHERE bctype = %s", (bctype,))
599 # CREATE TABLE epg_ch \
601 # bctype VARCHAR(20),\
602 # channel VARCHAR(20) NOT NULL,\
603 # display VARCHAR(100),\
604 # UNIQUE unich(bctype,channel)\
610 #def add_epg_ch(self, bctype, channel, display):
611 # db = self.connect_db()
612 ## db[1].execute('INSERT IGNORE INTO epg_ch VALUES (%s,%s,%s)', (bctype, channel, display))
615 #def add_multi_ch(self, chlists):
617 # chlists is (bctype,channel,display) lists
619 # db = self.connect_db()
620 # db[1].executemany('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', chlists)
622 def new_in_auto_bayes_key(self):
623 db = self.connect_db()
625 db[1].execute('CREATE TABLE in_auto_bayes_key \
627 keychar VARCHAR(10),\
629 ratio_rec DECIMAL(32,14),\
630 ratio_all DECIMAL(32,14),\
631 UNIQUE unibayeskey(keychar,chtxt)\
633 db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
638 def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
639 db = self.connect_db()
640 ratio_rec=str(ratio_rec)
641 ratio_all=str(ratio_all)
643 INSERT IGNORE INTO in_auto_bayes_key \
644 (keychar,chtxt,ratio_rec,ratio_all) \
645 values (%s,%s,%s,%s)',\
646 (key,chtxt,ratio_rec,ratio_all))
648 def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
649 db = self.connect_db()
650 add_rec_num=str(add_rec_num)
651 add_all_num=str(add_all_num)
653 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",\
654 (add_rec_num,add_all_num,chtxt))
656 def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
659 db = self.connect_db()
661 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)\
664 def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
665 beforenum=str(beforenum)
667 db = self.connect_db()
669 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)\
672 def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
673 db = self.connect_db()
675 beforenum=str(beforenum)
676 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
677 VALUES (%s,%s,%s,%s)\
678 ON DUPLICATE KEY UPDATE \
679 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
681 def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
683 list={key:addnum}のリスト
686 beforenum=str(beforenum)
687 db = self.connect_db()
688 for i,j in list.items():
689 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
690 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
691 VALUES (%s,%s,%s,%s)\
692 ON DUPLICATE KEY UPDATE \
693 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
695 def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
696 beforenum=str(beforenum)
698 db = self.connect_db()
700 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)\
703 def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
704 db = self.connect_db()
706 beforenum=str(beforenum)
707 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
708 VALUES (%s,%s,%s,%s)\
709 ON DUPLICATE KEY UPDATE \
710 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
712 def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
714 beforenum=str(beforenum)
715 db = self.connect_db()
716 for i,j in list.items():
717 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
718 db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
719 VALUES (%s,%s,%s,%s)\
720 ON DUPLICATE KEY UPDATE \
721 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
723 def select_by_key_in_auto_bayes_key(self,key,chtxt):
724 db = self.connect_db()
725 dbexe = db[1].execute("\
726 SELECT keychar,chtxt,ratio_rec,ratio_all \
727 FROM in_auto_bayes_key \
728 WHERE keychar = %s AND chtxt = %s", \
732 dls = db[1].fetchall()
739 def new_auto_timeline_keyword(self):
740 db = self.connect_db()
743 CREATE TABLE auto_timeline_keyword \
745 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
750 UNIQUE uni (chtxt,title,btime,etime)\
754 def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
755 db = self.connect_db()
757 INSERT IGNORE into auto_timeline_keyword \
758 (chtxt,title,btime,etime) \
759 values (%s,%s,%s,%s)', \
760 (chtxt, title, btime, etime))
763 def delete_old_auto_timeline_keyword(self, dhour):
764 db = self.connect_db()
766 DELETE FROM auto_timeline_keyword \
768 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
770 def new_auto_timeline_bayes(self):
771 db = self.connect_db()
774 CREATE TABLE auto_timeline_bayes \
776 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
781 UNIQUE uni (chtxt,title,btime,etime)\
785 def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime=""):
786 db = self.connect_db()
788 INSERT IGNORE into auto_timeline_bayes \
789 (chtxt,title,btime,etime) \
790 values (%s,%s,%s,%s)', \
791 (chtxt, title, btime, etime))
793 def delete_old_auto_timeline_bayes(self, dhour):
794 db = self.connect_db()
796 DELETE FROM auto_timeline_bayes \
798 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")