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 rectime \
32 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
38 deltatime VARCHAR(5),\
41 UNIQUE uni (type,chtxt,title,btime,deltaday)\
55 con = MySQLdb.connect(db=self.dbname, host=self.dbhost, port=self.dbport, user=self.dbusr, passwd=self.dbpasswd, charset="utf8")
57 cur.execute('set names utf8;')
59 def close_db(self, db):
63 db = self.connect_db()
65 db[1].execute('drop table chdata')
73 ontv VARCHAR(30) PRIMARY KEY,\
83 def add_chdata(self, bctype, ontv, chtxt, ch, csch, updatetime):
84 db = self.connect_db()
87 VALUES (%s,%s,%s,%s,%s,%s,%s)', \
88 (bctype, ontv, chtxt, ch, csch, updatetime, "1"))
90 def select_by_ontv_chdata(self, ontv):
91 db = self.connect_db()
92 dbexe = db[1].execute("\
93 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
100 dls = db[1].fetchall()
104 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
107 def select_by_chtxt_chdata(self, chtxt):
108 db = self.connect_db()
109 dbexe = db[1].execute("\
110 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
117 dls = db[1].fetchall()
121 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
124 def select_by_bctype_chdata(self, bctype):
125 db = self.connect_db()
126 dbexe = db[1].execute("\
127 SELECT bctype,ontv,chtxt,ch,csch,updatetime,status \
129 WHERE bctype = %s", \
134 dls = db[1].fetchall()
139 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
143 def select_by_ch_chdata(self, ch):
144 db = self.connect_db()
145 dbexe = db[1].execute("\
147 bctype,ontv,chtxt,ch,csch,updatetime \
154 dls = db[1].fetchall()
158 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
161 def select_all_chdata(self):
162 db = self.connect_db()
163 dbexe = db[1].execute("\
164 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
170 dls = db[1].fetchall()
174 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
177 def select_get_update_chdata(self, dhour):
178 db = self.connect_db()
179 dbexe = db[1].execute("SELECT bctype,chtxt,status FROM chdata \
182 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
188 ORDER BY status DESC")
192 ret = db[1].fetchall()
195 def update_by_bctype_chdata(self, bctype):
196 db = self.connect_db()
202 WHERE bctype = %s", (bctype,))
204 def update_by_bctype_and_chtxt_chdata(self, bctype, chtxt):
205 db = self.connect_db()
211 WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
213 def update_status_by_bctype_chdata(self, bctype, status):
214 db = self.connect_db()
219 WHERE bctype = %s", \
224 def add_procstatus(self,type,title):
225 db = self.connect_db()
227 INSERT IGNORE into procstatus \
233 def new_procstatus(self):
234 db = self.connect_db()
236 db[1].execute('drop table procstatus')
241 CREATE TABLE procstatus \
244 title VARCHAR(100) PRIMARY KEY,\
245 UNIQUE unibayeskey(title)\
250 def add_reclog(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
251 db = self.connect_db()
253 INSERT IGNORE into reclog \
254 (chtxt,title,btime,etime,opt,exp,longexp,category) \
255 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
256 ( chtxt, title, btime, etime, opt,exp,longexp,category))
259 def del_reclog(self, title="", chtxt="", btime=""):
263 db = self.connect_db()
266 WHERE title = %s AND chtxt = %s AND btime = %s", \
267 (title, chtxt, btime))
270 def new_reclog(self):
271 db = self.connect_db()
274 CREATE TABLE reclog \
276 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
284 category VARCHAR(100),\
285 UNIQUE uni (chtxt,title,btime,category)\
290 def add_rectime(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
291 db = self.connect_db()
293 INSERT IGNORE into rectime \
294 (type,chtxt,title,btime,etime,deltatime,deltaday,opt) \
295 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
296 (type, chtxt, title, btime, etime, deltatime, deltaday, opt))
299 def del_rectime(self, type="", title="", chtxt="", btime=""):
303 db = self.connect_db()
305 DELETE FROM rectime \
306 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
307 (type, title, chtxt, btime))
310 def select_all_rectime(self):
311 db = self.connect_db()
313 dbr = db[1].execute("\
314 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
316 dbl = db[1].fetchall()
319 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
323 ret['title'] = title.encode('utf-8')
324 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
325 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
329 ret['deltatime'] = ""
331 if deltatime == None:
336 ret['deltatime'] = deltatime
337 elif typet == 'keyevery':
338 ret['deltatime'] = deltatime
339 ret['deltaday'] = deltaday
343 def select_bytime_rectime(self, dminutes):
344 db = self.connect_db()
346 #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 )")
347 dbr = db[1].execute("SELECT \
348 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
350 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
351 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
352 dbl = db[1].fetchall()
356 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
360 ret['title'] = title.encode('utf-8')
361 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
362 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
366 if deltatime == None or deltatime == "":
368 if deltaday == None or deltaday == "":
371 ret['deltatime'] = deltatime
372 elif typet == 'keyevery':
373 ret['deltatime'] = deltatime
374 ret['deltaday'] = deltaday
377 def delete_old_rectime(self, dhour):
378 db = self.connect_db()
380 DELETE FROM rectime \
382 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
385 def new_recjbk(self):
386 db = self.connect_db()
389 CREATE TABLE recjbk \
391 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
392 keyword VARCHAR(200),\
393 UNIQUE unijbk (keyword)\
398 def add_recjbk(self,key):
399 db = self.connect_db()
401 INSERT IGNORE into recjbk \
407 def select_all_recjbk(self):
408 db = self.connect_db()
409 dbexe = db[1].execute("\
415 ret = db[1].fetchall()
418 def new_status(self):
419 db = self.connect_db()
422 CREATE TABLE status \
424 ts2avi TINYINT DEFAULT 0,\
425 terec TINYINT DEFAULT 0,\
426 bscsrec TINYINT DEFAULT 0,\
427 b252ts TINYINT DEFAULT 0\
429 db[1].execute("INSERT IGNORE into status VALUE (0,0,0,0)")
434 def select_all_status(self):
435 db = self.connect_db()
436 dbexe = db[1].execute("\
437 SELECT ts2avi,terec,bscsrec,b252ts \
443 dls = db[1].fetchall()
454 def change_ts2avi_status(self,i):
459 db = self.connect_db()
461 UPDATE status SET ts2avi=ts2avi+%s",i)
463 def change_terec_status(self,i):
468 db = self.connect_db()
470 UPDATE status SET terec=terec+%s",i)
472 def change_bscsrec_status(self,i):
477 db = self.connect_db()
479 UPDATE status SET bscsrec=bscsrec+%s",i)
481 def change_b252ts_status(self,i):
486 db = self.connect_db()
488 UPDATE status SET b252ts=b252ts+%s",i)
490 def new_tv(self, bctype):
491 db = self.connect_db()
495 WHERE bctype = %s", \
504 channel VARCHAR(100) NOT NULL,\
510 category VARCHAR(100),\
511 UNIQUE unitv(bctype,channel,start,stop,title)\
517 def add_tv(self, bctype, channel, start, stop, title, desc,longdesc, category):
518 db = self.connect_db()
520 INSERT IGNORE INTO tv \
521 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
522 (bctype, channel, start, stop, title, desc,longdesc,category))
525 def add_multi_tv(self, tvlists):
527 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
529 db = self.connect_db()
531 INSERT IGNORE INTO tv \
532 (bctype,channel,start,stop,title,exp,longexp,category) \
533 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
537 def select_by_time_ngram_tv(self, btime, etime, chtxt):
538 db = self.connect_db()
541 chdata.chtxt,title,start,stop,exp,longexp,category \
544 WHERE chdata.ontv=tv.channel \
551 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
554 retall = db[1].fetchall()
557 def select_by_time_auto_suggest_tv(self,keyword,btime,etime):
558 db = self.connect_db()
561 chdata.chtxt,title,start,stop,exp,longexp,category \
564 WHERE chdata.ontv=tv.channel \
571 ( title LIKE \'%%"+keyword+"%%\' ) \
573 ( exp LIKE \'%%"+keyword+"%%\' ) \
575 ( longexp LIKE \'%%"+keyword+"%%\' ) \
577 dbcmd = db[1].execute(dbexe,(btime, etime))
580 retall = db[1].fetchall()
583 def new_ch(self, bctype):
584 db = self.connect_db()
586 db[1].execute("DELETE FROM ch WHERE bctype = %s", (bctype,))
594 channel VARCHAR(20) NOT NULL,\
595 display VARCHAR(100),\
596 UNIQUE unich(bctype,channel)\
602 def add_ch(self, bctype, channel, display):
603 db = self.connect_db()
604 db[1].execute('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', (bctype, channel, display))
607 def add_multi_ch(self, chlists):
609 chlists is (bctype,channel,display) lists
611 db = self.connect_db()
612 db[1].executemany('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', chlists)
614 def new_bayeskey(self):
615 db = self.connect_db()
617 db[1].execute('CREATE TABLE bayeskey \
619 keychar VARCHAR(10),\
621 ratio_rec DECIMAL(32,14),\
622 ratio_all DECIMAL(32,14),\
623 UNIQUE unibayeskey(keychar,chtxt)\
625 db[1].execute('CREATE INDEX keycharindex ON bayeskey(keychar)')
630 def add_bayeskey(self,key,chtxt,ratio_rec,ratio_all):
631 db = self.connect_db()
632 ratio_rec=str(ratio_rec)
633 ratio_all=str(ratio_all)
635 INSERT IGNORE INTO bayeskey \
636 (keychar,chtxt,ratio_rec,ratio_all) \
637 values (%s,%s,%s,%s)',\
638 (key,chtxt,ratio_rec,ratio_all))
640 def add_num_bayeskey(self,chtxt,add_rec_num,add_all_num):
641 db = self.connect_db()
642 add_rec_num=str(add_rec_num)
643 add_all_num=str(add_all_num)
645 UPDATE bayeskey SET ratio_rec=ratio_rec+%s,ratio_all=ratio_all+%s WHERE keychar=\"NUM\" AND chtxt=%s",\
646 (add_rec_num,add_all_num,chtxt))
648 def change_bayeskey(self,key,chtxt,new_ratio_rec,new_ratio_all):
651 db = self.connect_db()
653 UPDATE bayeskey SET ratio_rec=%s,ratio_all=%s WHERE keychar=%s AND chtxt=%s",(str(new_ratio_rec),str(new_ratio_all),key,chtxt)\
656 def change_ratio_all_reduce_bayeskey(self,chtxt,beforenum,newnum):
657 beforenum=str(beforenum)
659 db = self.connect_db()
661 UPDATE bayeskey SET ratio_all=ratio_all*%s/(%s+%s) WHERE chtxt=%s AND NOT (keychar=\"NUM\")",(beforenum,newnum,beforenum,chtxt)\
664 def change_ratio_all_bayeskey(self,key,chtxt,beforenum,addnum):
665 db = self.connect_db()
667 beforenum=str(beforenum)
668 db[1].execute("INSERT INTO bayeskey (keychar,chtxt,ratio_rec,ratio_all) \
669 VALUES (%s,%s,%s,%s)\
670 ON DUPLICATE KEY UPDATE \
671 ratio_all=(ratio_all*%s+%s)/%s",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
673 def change_multi_ratio_all_bayeskey(self,chtxt,beforenum,list):
675 list={key:addnum}のリスト
678 beforenum=str(beforenum)
679 db = self.connect_db()
680 for i,j in list.items():
681 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
682 db[1].execute("INSERT INTO bayeskey (keychar,chtxt,ratio_rec,ratio_all) \
683 VALUES (%s,%s,%s,%s)\
684 ON DUPLICATE KEY UPDATE \
685 ratio_all=(ratio_all*%s+%s)/%s",retl)
687 def change_ratio_rec_reduce_bayeskey(self,chtxt,beforenum,newnum):
688 beforenum=str(beforenum)
690 db = self.connect_db()
692 UPDATE bayeskey SET ratio_rec=ratio_rec*%s/(%s+%s) WHERE chtxt=%s AND NOT (keychar=\"NUM\")",(beforenum,newnum,beforenum,chtxt)\
695 def change_ratio_rec_bayeskey(self,key,chtxt,beforenum,addnum):
696 db = self.connect_db()
698 beforenum=str(beforenum)
699 db[1].execute("INSERT INTO bayeskey (keychar,chtxt,ratio_rec,ratio_all) \
700 VALUES (%s,%s,%s,%s)\
701 ON DUPLICATE KEY UPDATE \
702 ratio_rec=(ratio_rec*%s+%s)/%s",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
704 def change_multi_ratio_rec_bayeskey(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
706 beforenum=str(beforenum)
707 db = self.connect_db()
708 for i,j in list.items():
709 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
710 db[1].execute("INSERT INTO bayeskey (keychar,chtxt,ratio_rec,ratio_all) \
711 VALUES (%s,%s,%s,%s)\
712 ON DUPLICATE KEY UPDATE \
713 ratio_rec=(ratio_rec*%s+%s)/%s",retl)
715 def select_by_key_bayeskey(self,key,chtxt):
716 db = self.connect_db()
717 dbexe = db[1].execute("\
718 SELECT keychar,chtxt,ratio_rec,ratio_all \
720 WHERE keychar = %s AND chtxt = %s", \
724 dls = db[1].fetchall()