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)\
56 con = MySQLdb.connect(db=self.dbname, host=self.dbhost, port=self.dbport, user=self.dbusr, passwd=self.dbpasswd, charset="utf8")
58 cur.execute('set names utf8;')
60 def close_db(self, db):
64 db = self.connect_db()
66 db[1].execute('drop table chdata')
74 ontv VARCHAR(30) PRIMARY KEY,\
84 def add_chdata(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_chdata(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_chdata(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_chdata(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_chdata(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_chdata(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_chdata(self, dhour):
179 db = self.connect_db()
180 dbexe = db[1].execute("SELECT bctype,chtxt,status FROM chdata \
183 updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
189 ORDER BY status DESC")
193 ret = db[1].fetchall()
196 def update_by_bctype_chdata(self, bctype):
197 db = self.connect_db()
203 WHERE bctype = %s", (bctype,))
205 def update_by_bctype_and_chtxt_chdata(self, bctype, chtxt):
206 db = self.connect_db()
212 WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
214 def update_status_by_bctype_chdata(self, bctype, status):
215 db = self.connect_db()
220 WHERE bctype = %s", \
225 def add_procstatus(self,type,title):
226 db = self.connect_db()
228 INSERT IGNORE into procstatus \
234 def new_procstatus(self):
235 db = self.connect_db()
237 db[1].execute('drop table procstatus')
242 CREATE TABLE procstatus \
245 title VARCHAR(100) PRIMARY KEY,\
246 UNIQUE unibayeskey(title)\
251 def add_reclog(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
252 db = self.connect_db()
254 INSERT IGNORE into reclog \
255 (chtxt,title,btime,etime,opt,exp,longexp,category) \
256 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
257 ( chtxt, title, btime, etime, opt,exp,longexp,category))
260 def del_reclog(self, title="", chtxt="", btime=""):
264 db = self.connect_db()
267 WHERE title = %s AND chtxt = %s AND btime = %s", \
268 (title, chtxt, btime))
271 def new_reclog(self):
272 db = self.connect_db()
275 CREATE TABLE reclog \
277 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
285 category VARCHAR(100),\
286 UNIQUE uni (chtxt,title,btime,category)\
291 def add_rectime(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
292 db = self.connect_db()
294 INSERT IGNORE into rectime \
295 (type,chtxt,title,btime,etime,deltatime,deltaday,opt) \
296 values (%s,%s,%s,%s,%s,%s,%s,%s)', \
297 (type, chtxt, title, btime, etime, deltatime, deltaday, opt))
300 def del_rectime(self, type="", title="", chtxt="", btime=""):
304 db = self.connect_db()
306 DELETE FROM rectime \
307 WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
308 (type, title, chtxt, btime))
311 def select_all_rectime(self):
312 db = self.connect_db()
314 dbr = db[1].execute("\
315 SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
317 dbl = db[1].fetchall()
320 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
324 ret['title'] = title.encode('utf-8')
325 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
326 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
330 ret['deltatime'] = ""
332 if deltatime == None:
337 ret['deltatime'] = deltatime
338 elif typet == 'keyevery':
339 ret['deltatime'] = deltatime
340 ret['deltaday'] = deltaday
344 def select_bytime_rectime(self, dminutes):
345 db = self.connect_db()
347 #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 )")
348 dbr = db[1].execute("SELECT \
349 type, chtxt, title, btime, etime, deltatime ,deltaday ,opt \
351 WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
352 DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
353 dbl = db[1].fetchall()
357 for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
361 ret['title'] = title.encode('utf-8')
362 btime = btime.strftime("%Y-%m-%d %H:%M:%S")
363 etime = etime.strftime("%Y-%m-%d %H:%M:%S")
367 if deltatime == None or deltatime == "":
369 if deltaday == None or deltaday == "":
372 ret['deltatime'] = deltatime
373 elif typet == 'keyevery':
374 ret['deltatime'] = deltatime
375 ret['deltaday'] = deltaday
378 def delete_old_rectime(self, dhour):
379 db = self.connect_db()
381 DELETE FROM rectime \
383 btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
386 def new_recjbk(self):
387 db = self.connect_db()
390 CREATE TABLE recjbk \
392 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
393 keyword VARCHAR(200),\
394 UNIQUE unijbk (keyword)\
399 def add_recjbk(self,key):
400 db = self.connect_db()
402 INSERT IGNORE into recjbk \
408 def select_all_recjbk(self):
409 db = self.connect_db()
410 dbexe = db[1].execute("\
416 ret = db[1].fetchall()
419 def new_status(self):
420 db = self.connect_db()
423 CREATE TABLE status \
425 ts2avi TINYINT DEFAULT 0,\
426 terec TINYINT DEFAULT 0,\
427 bscsrec TINYINT DEFAULT 0,\
428 b252ts TINYINT DEFAULT 0\
430 db[1].execute("INSERT IGNORE into status VALUE (0,0,0,0)")
435 def select_all_status(self):
436 db = self.connect_db()
437 dbexe = db[1].execute("\
438 SELECT ts2avi,terec,bscsrec,b252ts \
444 dls = db[1].fetchall()
455 def change_ts2avi_status(self,i):
460 db = self.connect_db()
462 UPDATE status SET ts2avi=ts2avi+%s",i)
464 def change_terec_status(self,i):
469 db = self.connect_db()
471 UPDATE status SET terec=terec+%s",i)
473 def change_bscsrec_status(self,i):
478 db = self.connect_db()
480 UPDATE status SET bscsrec=bscsrec+%s",i)
482 def change_b252ts_status(self,i):
487 db = self.connect_db()
489 UPDATE status SET b252ts=b252ts+%s",i)
491 def new_tv(self, bctype):
492 db = self.connect_db()
496 WHERE bctype = %s", \
505 channel VARCHAR(100) NOT NULL,\
511 category VARCHAR(100),\
512 UNIQUE unitv(bctype,channel,start,stop,title)\
518 def add_tv(self, bctype, channel, start, stop, title, desc,longdesc, category):
519 db = self.connect_db()
521 INSERT IGNORE INTO tv \
522 VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
523 (bctype, channel, start, stop, title, desc,longdesc,category))
526 def add_multi_tv(self, tvlists):
528 tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
530 db = self.connect_db()
532 INSERT IGNORE INTO tv \
533 (bctype,channel,start,stop,title,exp,longexp,category) \
534 values(%s,%s,%s,%s,%s,%s,%s,%s)', \
538 def select_by_time_ngram_tv(self, btime, etime, chtxt):
539 db = self.connect_db()
542 chdata.chtxt,title,start,stop,exp,longexp,category \
545 WHERE chdata.ontv=tv.channel \
552 dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
555 retall = db[1].fetchall()
558 def select_by_time_auto_suggest_tv(self,keyword,btime,etime):
559 db = self.connect_db()
562 chdata.chtxt,title,start,stop,exp,longexp,category \
565 WHERE chdata.ontv=tv.channel \
572 ( title LIKE \'%%"+keyword+"%%\' ) \
574 ( exp LIKE \'%%"+keyword+"%%\' ) \
576 ( longexp LIKE \'%%"+keyword+"%%\' ) \
578 dbcmd = db[1].execute(dbexe,(btime, etime))
581 retall = db[1].fetchall()
584 def new_ch(self, bctype):
585 db = self.connect_db()
587 db[1].execute("DELETE FROM ch WHERE bctype = %s", (bctype,))
595 channel VARCHAR(20) NOT NULL,\
596 display VARCHAR(100),\
597 UNIQUE unich(bctype,channel)\
603 def add_ch(self, bctype, channel, display):
604 db = self.connect_db()
605 db[1].execute('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', (bctype, channel, display))
608 def add_multi_ch(self, chlists):
610 chlists is (bctype,channel,display) lists
612 db = self.connect_db()
613 db[1].executemany('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', chlists)
615 def new_bayeskey(self):
616 db = self.connect_db()
618 db[1].execute('CREATE TABLE bayeskey \
620 keychar VARCHAR(10),\
622 ratio_rec DECIMAL(32,14),\
623 ratio_all DECIMAL(32,14),\
624 UNIQUE unibayeskey(keychar,chtxt)\
626 db[1].execute('CREATE INDEX keycharindex ON bayeskey(keychar)')
631 def add_bayeskey(self,key,chtxt,ratio_rec,ratio_all):
632 db = self.connect_db()
633 ratio_rec=str(ratio_rec)
634 ratio_all=str(ratio_all)
636 INSERT IGNORE INTO bayeskey \
637 (keychar,chtxt,ratio_rec,ratio_all) \
638 values (%s,%s,%s,%s)',\
639 (key,chtxt,ratio_rec,ratio_all))
641 def add_num_bayeskey(self,chtxt,add_rec_num,add_all_num):
642 db = self.connect_db()
643 add_rec_num=str(add_rec_num)
644 add_all_num=str(add_all_num)
646 UPDATE bayeskey 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",\
647 (add_rec_num,add_all_num,chtxt))
649 def change_bayeskey(self,key,chtxt,new_ratio_rec,new_ratio_all):
652 db = self.connect_db()
654 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)\
657 def change_ratio_all_reduce_bayeskey(self,chtxt,beforenum,newnum):
658 beforenum=str(beforenum)
660 db = self.connect_db()
662 UPDATE bayeskey SET ratio_all=CONVERT(ratio_all*%s/(%s+%s),DECIMAL(32,14)) WHERE chtxt=%s AND NOT (keychar=\"NUM\")",(beforenum,newnum,beforenum,chtxt)\
665 def change_ratio_all_bayeskey(self,key,chtxt,beforenum,addnum):
666 db = self.connect_db()
668 beforenum=str(beforenum)
669 db[1].execute("INSERT INTO bayeskey (keychar,chtxt,ratio_rec,ratio_all) \
670 VALUES (%s,%s,%s,%s)\
671 ON DUPLICATE KEY UPDATE \
672 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
674 def change_multi_ratio_all_bayeskey(self,chtxt,beforenum,list):
676 list={key:addnum}のリスト
679 beforenum=str(beforenum)
680 db = self.connect_db()
681 for i,j in list.items():
682 retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
683 db[1].execute("INSERT INTO bayeskey (keychar,chtxt,ratio_rec,ratio_all) \
684 VALUES (%s,%s,%s,%s)\
685 ON DUPLICATE KEY UPDATE \
686 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
688 def change_ratio_rec_reduce_bayeskey(self,chtxt,beforenum,newnum):
689 beforenum=str(beforenum)
691 db = self.connect_db()
693 UPDATE bayeskey SET ratio_rec=CONVERT(ratio_rec*%s/(%s+%s),DECIMAL(32,14)) WHERE chtxt=%s AND NOT (keychar=\"NUM\")",(beforenum,newnum,beforenum,chtxt)\
696 def change_ratio_rec_bayeskey(self,key,chtxt,beforenum,addnum):
697 db = self.connect_db()
699 beforenum=str(beforenum)
700 db[1].execute("INSERT INTO bayeskey (keychar,chtxt,ratio_rec,ratio_all) \
701 VALUES (%s,%s,%s,%s)\
702 ON DUPLICATE KEY UPDATE \
703 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
705 def change_multi_ratio_rec_bayeskey(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
707 beforenum=str(beforenum)
708 db = self.connect_db()
709 for i,j in list.items():
710 retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
711 db[1].execute("INSERT INTO bayeskey (keychar,chtxt,ratio_rec,ratio_all) \
712 VALUES (%s,%s,%s,%s)\
713 ON DUPLICATE KEY UPDATE \
714 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
716 def select_by_key_bayeskey(self,key,chtxt):
717 db = self.connect_db()
718 dbexe = db[1].execute("\
719 SELECT keychar,chtxt,ratio_rec,ratio_all \
721 WHERE keychar = %s AND chtxt = %s", \
725 dls = db[1].fetchall()