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)\
54 con = MySQLdb.connect(db=self.dbname, host=self.dbhost, port=self.dbport, user=self.dbusr, passwd=self.dbpasswd, charset="utf8")
56 cur.execute('set names utf8;')
58 def close_db(self, db):
62 db = self.connect_db()
64 db[1].execute('drop table chdata')
72 ontv VARCHAR(30) PRIMARY KEY,\
82 def add_chdata(self, bctype, ontv, chtxt, ch, csch, updatetime):
83 db = self.connect_db()
86 VALUES (%s,%s,%s,%s,%s,%s,%s)', \
87 (bctype, ontv, chtxt, ch, csch, updatetime, "1"))
89 def select_by_ontv_chdata(self, ontv):
90 db = self.connect_db()
91 dbexe = db[1].execute("\
92 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
99 dls = db[1].fetchall()
103 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
106 def select_by_chtxt_chdata(self, chtxt):
107 db = self.connect_db()
108 dbexe = db[1].execute("\
109 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
116 dls = db[1].fetchall()
120 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
123 def select_by_bctype_chdata(self, bctype):
124 db = self.connect_db()
125 dbexe = db[1].execute("\
126 SELECT bctype,ontv,chtxt,ch,csch,updatetime,status \
128 WHERE bctype = %s", \
133 dls = db[1].fetchall()
138 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
142 def select_by_ch_chdata(self, ch):
143 db = self.connect_db()
144 dbexe = db[1].execute("\
146 bctype,ontv,chtxt,ch,csch,updatetime \
153 dls = db[1].fetchall()
157 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
160 def select_all_chdata(self):
161 db = self.connect_db()
162 dbexe = db[1].execute("\
163 SELECT bctype,ontv,chtxt,ch,csch,updatetime \
169 dls = db[1].fetchall()
173 r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
176 def select_get_update_chdata(self, dhour):
177 db = self.connect_db()
178 dbexe = db[1].execute("SELECT bctype,chtxt,status FROM chdata \
181 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
187 ORDER BY status DESC")
191 ret = db[1].fetchall()
194 def update_by_bctype_chdata(self, bctype):
195 db = self.connect_db()
201 WHERE bctype = %s", (bctype,))
203 def update_by_bctype_and_chtxt_chdata(self, bctype, chtxt):
204 db = self.connect_db()
210 WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
212 def update_status_by_bctype_chdata(self, bctype, status):
213 db = self.connect_db()
218 WHERE bctype = %s", \
222 def add_reclog(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
223 db = self.connect_db()
225 INSERT IGNORE into reclog \
226 (chtxt,title,btime,etime,opt,exp,longexp,category) \
227 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
228 ( chtxt, title, btime, etime, opt,exp,longexp,category))
231 def del_reclog(self, title="", chtxt="", btime=""):
235 db = self.connect_db()
238 WHERE title = %s AND chtxt = %s AND btime = %s", \
239 (title, chtxt, btime))
242 def new_reclog(self):
243 db = self.connect_db()
246 CREATE TABLE reclog \
248 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
256 category VARCHAR(100),\
257 UNIQUE uni (chtxt,title,btime,category)\
262 def add_rectime(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
263 db = self.connect_db()
265 INSERT IGNORE into rectime \
266 (type,chtxt,title,btime,etime,deltatime,deltaday,opt) \
267 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
268 (type, chtxt, title, btime, etime, deltatime, deltaday, opt))
271 def del_rectime(self, type="", title="", chtxt="", btime=""):
275 db = self.connect_db()
277 DELETE FROM rectime \
278 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
279 (type, title, chtxt, btime))
282 def select_all_rectime(self):
283 db = self.connect_db()
285 dbr = db[1].execute("\
286 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
288 dbl = db[1].fetchall()
291 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
295 ret['title'] = title.encode('utf-8')
296 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
297 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
301 ret['deltatime'] = ""
303 if deltatime == None:
308 ret['deltatime'] = deltatime
309 elif typet == 'keyevery':
310 ret['deltatime'] = deltatime
311 ret['deltaday'] = deltaday
315 def select_bytime_rectime(self, dminutes):
316 db = self.connect_db()
318 #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 )")
319 dbr = db[1].execute("SELECT \
320 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
322 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
323 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
324 dbl = db[1].fetchall()
328 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
332 ret['title'] = title.encode('utf-8')
333 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
334 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
338 if deltatime == None or deltatime == "":
340 if deltaday == None or deltaday == "":
343 ret['deltatime'] = deltatime
344 elif typet == 'keyevery':
345 ret['deltatime'] = deltatime
346 ret['deltaday'] = deltaday
349 def delete_old_rectime(self, dhour):
350 db = self.connect_db()
352 DELETE FROM rectime \
354 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
357 def new_status(self):
358 db = self.connect_db()
361 CREATE TABLE status \
363 ts2avi TINYINT DEFAULT 0,\
364 terec TINYINT DEFAULT 0,\
365 bscsrec TINYINT DEFAULT 0,\
366 b252ts TINYINT DEFAULT 0\
368 db[1].execute("INSERT IGNORE into status VALUE (0,0,0,0)")
373 def select_all_status(self):
374 db = self.connect_db()
375 dbexe = db[1].execute("\
376 SELECT ts2avi,terec,bscsrec,b252ts \
382 dls = db[1].fetchall()
393 def change_ts2avi_status(self,i):
398 db = self.connect_db()
400 UPDATE status SET ts2avi=ts2avi+%s",i)
402 def change_terec_status(self,i):
407 db = self.connect_db()
409 UPDATE status SET terec=terec+%s",i)
411 def change_bscsrec_status(self,i):
416 db = self.connect_db()
418 UPDATE status SET bscsrec=bscsrec+%s",i)
420 def change_b252ts_status(self,i):
425 db = self.connect_db()
427 UPDATE status SET b252ts=b252ts+%s",i)
429 def new_tv(self, bctype):
430 db = self.connect_db()
434 WHERE bctype = %s", \
443 channel VARCHAR(100) NOT NULL,\
449 category VARCHAR(100),\
450 UNIQUE unitv(bctype,channel,start,stop,title)\
456 def add_tv(self, bctype, channel, start, stop, title, desc,longdesc, category):
457 db = self.connect_db()
459 INSERT IGNORE INTO tv \
460 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
461 (bctype, channel, start, stop, title, desc,longdesc,category))
464 def add_multi_tv(self, tvlists):
466 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
468 db = self.connect_db()
470 INSERT IGNORE INTO tv \
471 (bctype,channel,start,stop,title,exp,longexp,category) \
472 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
476 def select_by_time_ngram_tv(self, btime, etime, chtxt):
477 db = self.connect_db()
480 chdata.chtxt,title,start,stop,exp,longexp,category \
483 WHERE chdata.ontv=tv.channel \
490 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
493 retall = db[1].fetchall()
496 def select_by_time_auto_suggest_tv(self,keyword,btime,etime,chtxt):
497 db = self.connect_db()
500 chdata.chtxt,title,start,stop,exp,longexp,category \
503 WHERE chdata.ontv=tv.channel \
510 ( title LIKE \'%%"+keyword+"%%\' ) \
512 ( exp LIKE \'%%"+keyword+"%%\' ) \
514 ( longexp LIKE \'%%"+keyword+"%%\' ) \
516 dbcmd = db[1].execute(dbexe,(btime, etime))
519 retall = db[1].fetchall()
522 def new_ch(self, bctype):
523 db = self.connect_db()
525 db[1].execute("DELETE FROM ch WHERE bctype = %s", (bctype,))
533 channel VARCHAR(20) NOT NULL,\
534 display VARCHAR(100),\
535 UNIQUE unich(bctype,channel)\
541 def add_ch(self, bctype, channel, display):
542 db = self.connect_db()
543 db[1].execute('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', (bctype, channel, display))
546 def add_multi_ch(self, chlists):
548 chlists is (bctype,channel,display) lists
550 db = self.connect_db()
551 db[1].executemany('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', chlists)
553 def new_bayeskey(self):
554 db = self.connect_db()
556 db[1].execute('CREATE TABLE bayeskey \
558 keychar VARCHAR(10),\
560 ratio_rec DECIMAL(32,14),\
561 ratio_all DECIMAL(32,14),\
562 UNIQUE unibayeskey(keychar,chtxt)\
564 db[1].execute('CREATE INDEX keycharindex ON bayeskey(keychar)')
569 def add_bayeskey(self,key,chtxt,ratio_rec,ratio_all):
570 db = self.connect_db()
571 ratio_rec=str(ratio_rec)
572 ratio_all=str(ratio_all)
574 INSERT IGNORE INTO bayeskey \
575 (keychar,chtxt,ratio_rec,ratio_all) \
576 values (%s,%s,%s,%s)',\
577 (key,chtxt,ratio_rec,ratio_all))
579 def add_num_bayeskey(self,chtxt,add_rec_num,add_all_num):
580 db = self.connect_db()
581 add_rec_num=str(add_rec_num)
582 add_all_num=str(add_all_num)
584 UPDATE bayeskey SET ratio_rec=ratio_rec+%s,ratio_all=ratio_all+%s WHERE keychar=\"NUM\" AND chtxt=%s",\
585 (add_rec_num,add_all_num,chtxt))
587 def change_bayeskey(self,key,chtxt,new_ratio_rec,new_ratio_all):
590 db = self.connect_db()
592 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)\
595 def change_ratio_all_reduce_bayeskey(self,chtxt,beforenum,newnum):
596 beforenum=str(beforenum)
598 db = self.connect_db()
600 UPDATE bayeskey SET ratio_all=ratio_all*%s/(%s+%s) WHERE chtxt=%s AND NOT (keychar=\"NUM\")",(beforenum,newnum,beforenum,chtxt)\
603 def change_ratio_all_bayeskey(self,key,chtxt,beforenum,addnum):
604 db = self.connect_db()
606 beforenum=str(beforenum)
607 db[1].execute("INSERT INTO bayeskey (keychar,chtxt,ratio_rec,ratio_all) \
608 VALUES (%s,%s,%s,%s)\
609 ON DUPLICATE KEY UPDATE \
610 ratio_all=(ratio_all*%s+%s)/%s",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
612 def change_multi_ratio_all_bayeskey(self,chtxt,beforenum,list):
614 list={key:addnum}のリスト
617 beforenum=str(beforenum)
618 db = self.connect_db()
619 for i,j in list.items():
620 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
621 db[1].execute("INSERT INTO bayeskey (keychar,chtxt,ratio_rec,ratio_all) \
622 VALUES (%s,%s,%s,%s)\
623 ON DUPLICATE KEY UPDATE \
624 ratio_all=(ratio_all*%s+%s)/%s",retl)
626 def change_ratio_rec_reduce_bayeskey(self,chtxt,beforenum,newnum):
627 beforenum=str(beforenum)
629 db = self.connect_db()
631 UPDATE bayeskey SET ratio_rec=ratio_rec*%s/(%s+%s) WHERE chtxt=%s AND NOT (keychar=\"NUM\")",(beforenum,newnum,beforenum,chtxt)\
634 def change_ratio_rec_bayeskey(self,key,chtxt,beforenum,addnum):
635 db = self.connect_db()
637 beforenum=str(beforenum)
638 db[1].execute("INSERT INTO bayeskey (keychar,chtxt,ratio_rec,ratio_all) \
639 VALUES (%s,%s,%s,%s)\
640 ON DUPLICATE KEY UPDATE \
641 ratio_rec=(ratio_rec*%s+%s)/%s",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
643 def change_multi_ratio_rec_bayeskey(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
645 beforenum=str(beforenum)
646 db = self.connect_db()
647 for i,j in list.items():
648 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
649 db[1].execute("INSERT INTO bayeskey (keychar,chtxt,ratio_rec,ratio_all) \
650 VALUES (%s,%s,%s,%s)\
651 ON DUPLICATE KEY UPDATE \
652 ratio_rec=(ratio_rec*%s+%s)/%s",retl)
654 def select_by_key_bayeskey(self,key,chtxt):
655 db = self.connect_db()
656 dbexe = db[1].execute("\
657 SELECT keychar,chtxt,ratio_rec,ratio_all \
659 WHERE keychar = %s AND chtxt = %s", \
663 dls = db[1].fetchall()