From 5c85a3eff5ac1071be01aecf6d17fd6de7c72371 Mon Sep 17 00:00:00 2001 From: gn64_jp Date: Sun, 9 Aug 2009 02:42:37 +0000 Subject: [PATCH] begin to ready for implementing MySQL function. git-svn-id: svn+ssh://svn.sourceforge.jp/svnroot/rec10@97 4e526526-5e11-4fc0-8910-f8fd03428081 --- Rec10WEB/trunk/src/rec10web.py | 120 ++++++++----------------- Rec10WEB/trunk/src/rec10web_dbMySQL.py | 154 ++++++++++++++++++++++++++++++++ Rec10WEB/trunk/src/rec10web_dbSQLite.py | 154 ++++++++++++++++++++++++++++++++ Rec10WEB/trunk/src/rswi.htm | 1 + Rec10WEB/trunk/src/rswi_edit.htm | 1 + 5 files changed, 348 insertions(+), 82 deletions(-) create mode 100644 Rec10WEB/trunk/src/rec10web_dbMySQL.py create mode 100644 Rec10WEB/trunk/src/rec10web_dbSQLite.py diff --git a/Rec10WEB/trunk/src/rec10web.py b/Rec10WEB/trunk/src/rec10web.py index 9594d13..ea20ded 100644 --- a/Rec10WEB/trunk/src/rec10web.py +++ b/Rec10WEB/trunk/src/rec10web.py @@ -3,20 +3,29 @@ # Rec10 TS Recording Tools # Copyright (C) 2009 Yukikaze import cgi -import cgitb +import cgitb;cgitb.enable() import sqlite3 import os import re import datetime -cgitb.enable() +import rec10web_dbSQLite +#cgitb.enable() path=str(os.path.dirname(os.path.abspath(__file__)))+"/" dbpath=path+"ch.db" - +database=rec10web_dbSQLite.DB_SQLite(dbpath) def getRecDate(): - db=sqlite3.connect(dbpath) - recdata="\"id\",\"type\",\"chtxt\",\"title\",\"btime\",\"etime\",\"deltaday\",\"opt\"\n" + dl=database.select_all_rectime() recdata="id,type,chtxt,title,btime,etime,deltaday,opt\n" - for id, typet, chtxt, title, btime, etime, deltatime ,deltaday ,opt in db.execute("SELECT id, type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime"): + for d in dl: + id=d['id'] + typet=d['type'] + chtxt=d['chtxt'] + title=d['title'] + btime=d['btime'] + etime=d['etime'] + deltatime=d['deltatime'] + deltaday=d['deltaday'] + opt=d['opt'] if deltaday==None: deltaday="なし" elif deltaday=="": @@ -34,7 +43,9 @@ def getRecDate(): elif typet=="grid": typet="外部委託" elif typet=="ts2avi": - typet="EncQue" + typet="縁故予約" + elif typet=="b252ts": + typet="復号予約" btime=btime[5:16] etime=etime[5:16] btime=btime.replace("-","/") @@ -48,35 +59,17 @@ def getRecDate(): f.write(recdata) f.close() def getchtxt(): - db=sqlite3.connect(dbpath) + dl=database.select_all_chdata() chopt="" - for bctype, ontv, chtxt, ch, csch ,updatetime in db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata"): + for bctype, ontv, chtxt, ch, csch ,updatetime in dl: chdisplay="" - for bctype,channel,display in db.execute("SELECT bctype,channel,display FROM ch WHERE channel=\""+ontv+"\""): + for bctype,channel,display in database.select_by_ontv_ch(ontv): chdisplay=display - chopt=chopt+"\n " - db.close() + if chdisplay!="": + chopt=chopt+"\n " return chopt def edit_date(id): - db=sqlite3.connect(dbpath) - ret={} - for idt,typet,chtxt,title,btime,etime,deltatime,opt,deltaday in db.execute("SELECT id,type,chtxt,title,btime,etime,deltatime,opt,deltaday From rectime WHERE id ="+id): - ret['id']=idt - ret['type']=typet - ret['chtxt']=chtxt - ret['title']=title - ret['btime']=btime - ret['etime']=etime - try: - ret['deltatime']=deltatime - except: - ret['deltatime']="" - ret['opt']=opt - try: - ret['deltaday']=daltaday - except: - ret['deltaday']="" - db.close() + ret=database.select_by_id_rectime(id) return ret def reserv_recday(keyword,chtxt,btime,etime,deltatime,opt,deltaday,day): @@ -96,24 +89,13 @@ def reserv_recday(keyword,chtxt,btime,etime,deltatime,opt,deltaday,day): def reserv(type,keyword,chtxt,btime,etime,deltatime,opt,deltaday): MaxT=2 MaxS=2 - db=sqlite3.connect(dbpath) - bt="\'"+btime+"\'" - et="\'"+etime+"\'" - dbexe="SELECT type,chdata.bctype,rectime.chtxt,rectime.title FROM rectime INNER JOIN chdata ON rectime.chtxt=chdata.chtxt" - #dbexe="SELECT bctype,chtxt,title FROM rectime INNER JOIN chdata ON rectime.chtxt=chdata.chtxt" - - dbexe=dbexe+" WHERE NOT( ( rectime.etime <= "+bt+" ) OR ( rectime.btime >= "+et+" ) )" - for typet,bctypet, chtxtt, titlet in db.execute(dbexe): - if (typet=="rec") or (typet=="res") or (typet=="key") or (typet=="keyevery"): - if bctypet.find("cs")>-1: - MaxS=MaxS-1 - elif bctypet.find("bs")>-1: - MaxS=MaxS-1 - elif bctypet.find("te")>-1: - MaxT=MaxT-1 + tsnum=database.count_schedule_rectime(btime,etime) + MaxT=MaxT-tsnum[0] + MaxS=MaxS-tsnum[1] bctype="" rett="" - for bctypet, chtxtt in db.execute("SELECT bctype,chtxt FROM chdata WHERE chtxt=\'"+chtxt+"\'"): + bctypegetdb=database.select_by_chtxt_chdata(chtxt) + for bctypet,ontvt,chtxtt,cht,cscht,updatetimet in bctypegetdb: bctype=bctypet if (type=="rec") or (type=="res") or (type=="key") or (type=="keyevery"): if bctype.find("cs")>-1: @@ -123,13 +105,9 @@ def reserv(type,keyword,chtxt,btime,etime,deltatime,opt,deltaday): elif bctype.find("te")>-1: MaxT=MaxT-1 if MaxS>-1 and MaxT>-1: - recline="\""+type+"\",\""+chtxt+"\",\""+keyword+"\",datetime(\""+btime+"\"),datetime(\""+etime+"\"),\""+deltatime+"\",\""+opt+"\",\""+deltaday+"\"" - #print recline - db.execute('insert into rectime ("type","chtxt","title","btime","etime","deltatime","opt","deltaday") values ('+recline+')') - db.commit() + database.add_rectime(type=type,chtxt=chtxt,title=keyword,btime=btime,etime=etime,deltatime=deltatime,deltaday=deltaday,opt=opt) else: rett="over" - db.close() return rett def change_reserv(id,type,keyword,chtxt,btime,etime,deltatime,opt,deltaday): """ @@ -137,26 +115,12 @@ def change_reserv(id,type,keyword,chtxt,btime,etime,deltatime,opt,deltaday): """ MaxT=2 MaxS=2 - db=sqlite3.connect(dbpath) - bt="\'"+btime+"\'" - et="\'"+etime+"\'" - idn=int(id) - dbexe="SELECT id,type,chdata.bctype,rectime.chtxt,rectime.title FROM rectime INNER JOIN chdata ON rectime.chtxt=chdata.chtxt" - #dbexe="SELECT bctype,chtxt,title FROM rectime INNER JOIN chdata ON rectime.chtxt=chdata.chtxt" - - dbexe=dbexe+" WHERE NOT( ( rectime.etime <= "+bt+" ) OR ( rectime.btime >= "+et+" ) )" - for idt,typet,bctypet, chtxtt, titlet in db.execute(dbexe): - if idt != idn: - if (typet=="rec") or (typet=="res") or (typet=="key") or (typet=="keyevery"): - if bctypet.find("cs")>-1: - MaxS=MaxS-1 - elif bctypet.find("bs")>-1: - MaxS=MaxS-1 - elif bctypet.find("te")>-1: - MaxT=MaxT-1 + tsnum=database.count_schedule_rectime(btime,etime) + MaxT=MaxT-tsnum[0] + MaxS=MaxS-tsnum[1] bctype="" rett="" - for bctypet, chtxtt in db.execute("SELECT bctype,chtxt FROM chdata WHERE chtxt=\'"+chtxt+"\'"): + for bctypet, chtxtt in database.select_by_chtxt_chdata(chtxt): bctype=bctypet if (type=="rec") or (type=="res") or (type=="key") or (type=="keyevery"): if bctype.find("cs")>-1: @@ -166,28 +130,20 @@ def change_reserv(id,type,keyword,chtxt,btime,etime,deltatime,opt,deltaday): elif bctype.find("te")>-1: MaxT=MaxT-1 if MaxS>-1 and MaxT>-1: - db.execute('delete from rectime where id='+id) - recline="\""+type+"\",\""+chtxt+"\",\""+keyword+"\",datetime(\""+btime+"\"),datetime(\""+etime+"\"),\""+deltatime+"\",\""+opt+"\",\""+deltaday+"\"" - #print recline - db.execute('insert into rectime ("type","chtxt","title","btime","etime","deltatime","opt","deltaday") values ('+recline+')') - db.commit() + database.del_by_id_rectime(id) + database.add_rectime(type=type, chtxt=chtxt, title=keyword, btime=btime, etime=etime, deltatime=deltatime, deltaday=deltaday, opt=opt) else: rett="over" - db.close() return rett def station2chtxt(station): - db=sqlite3.connect(dbpath) ret="" - for bctype, ontv, chtxt, ch, csch ,updatetime in db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE station=\""+station+"\""): + for bctype, ontv, chtxt, ch, csch ,updatetime in database.select_by_station_chdata(station): ret=chtxt - db.close() return ret def station_name2chtxt(station): - db=sqlite3.connect(dbpath) ret="" - for bctype, ontv, chtxt, ch, csch ,updatetime in db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE station_name=\""+station+"\""): + for bctype, ontv, chtxt, ch, csch ,updatetime in database.select_by_station_name_chdata(station): ret=chtxt - db.close() return ret def readHtmlSrcSimple(): f = open(path+'rswi.htm') diff --git a/Rec10WEB/trunk/src/rec10web_dbMySQL.py b/Rec10WEB/trunk/src/rec10web_dbMySQL.py new file mode 100644 index 0000000..785c511 --- /dev/null +++ b/Rec10WEB/trunk/src/rec10web_dbMySQL.py @@ -0,0 +1,154 @@ +#!/usr/bin/python +# coding: UTF-8 +# Rec10 TS Recording Tools +# Copyright (C) 2009 Yukikaze +import sqlite3 +import os +path = str(os.path.dirname(os.path.abspath(__file__))) + "/" +dbpath = path + "ch.db" +class DB_SQLite(): + dbpath="" + def __init__(self,dbpath): + self.dbpath = dbpath + def connect_db(self,tout=10): + global dbpath + """ + dbへの接続(timeoutは秒) + + """ + tout=tout*1000 + return sqlite3.connect(self.dbpath, timeout=tout) + def close_db(self,db): + db.close() + + def select_by_ontv_ch(self,ontv): + db=self.connect_db(120) + ret=db.execute("SELECT bctype,channel,display FROM ch WHERE channel= ? ",(ontv,)) + ret=ret.fetchall() + self.close_db(db) + return ret + def select_all_rectime(self): + db=self.connect_db(120) + recdata=[] + for id,typet, chtxt, title, btime, etime, deltatime, deltaday, opt in db.execute("SELECT id,type,chtxt,title,btime,etime,deltatime,deltaday,opt FROM rectime"): + ret={} + ret['id']=id + ret['type']=typet + ret['chtxt']=chtxt + ret['title']=title.encode('utf-8') + ret['btime']=btime + ret['etime']=etime + ret['opt']=opt + ret['deltatime']="" + ret['deltaday']="" + if deltatime == None: + deltatime="3" + if deltaday == None: + deltaday="7" + if typet == 'key': + ret['deltatime']=deltatime + elif typet == 'keyevery': + ret['deltatime']=deltatime + ret['deltaday']=deltaday + recdata.append(ret) + self.close_db(db) + return recdata + def select_by_chtxt_rectime(self,chtxt): + db=self.connect_db(120) + recdata=[] + for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in db.execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE chtxt = ? ",(chtxt,)): + ret={} + ret['type']=typet + ret['chtxt']=chtxt + ret['title']=title.encode('utf-8') + ret['btime']=btime + ret['etime']=etime + ret['opt']=opt + if deltatime == None: + deltatime="3" + if deltaday == None: + deltaday="7" + if typet == 'key': + ret['deltatime']=deltatime + elif typet == 'keyevery': + ret['deltatime']=deltatime + ret['deltaday']=deltaday + recdata.append(ret) + self.close_db(db) + return recdata + def select_by_id_rectime(self,id): + db=self.connect_db(120) + recdata=[] + for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in db.execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE id = ? ",(id,)): + ret={} + ret['type']=typet + ret['chtxt']=chtxt + ret['title']=title.encode('utf-8') + ret['btime']=btime + ret['etime']=etime + ret['opt']=opt + if deltatime == None: + deltatime="3" + if deltaday == None: + deltaday="7" + if typet == 'key': + ret['deltatime']=deltatime + elif typet == 'keyevery': + ret['deltatime']=deltatime + ret['deltaday']=deltaday + recdata.append(ret) + self.close_db(db) + return recdata + def select_all_chdata(self): + db=self.connect_db(120) + ret=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata") + ret=ret.fetchall() + self.close_db(db) + return ret + def select_by_chtxt_chdata(self,chtxt): + db=self.connect_db(120) + ret=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE chtxt=?",(chtxt,)) + ret=ret.fetchall() + self.close_db(db) + return ret + def select_by_station_chdata(self,station): + db=self.connect_db(120) + ret=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE station=?",(station,)) + ret=ret.fetchall() + self.close_db(db) + return ret + def select_by_station_name_chdata(self,station_name): + db=self.connect_db(120) + ret=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE station_name=?",(station_name,)) + ret=ret.fetchall() + self.close_db(db) + return ret + def add_rectime(self,type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""): + db = self.connect_db(480) + db.execute('INSERT OR IGNORE INTO rectime (type,chtxt,title,btime,etime,deltatime,deltaday,opt) values (?,?,?,datetime(?),datetime(?),?,?,?)', (type, chtxt, title, btime, etime, deltatime, deltaday, opt)) + db.commit() + self.close_db(db) + def count_schedule_rectime(self,btime,etime): + """ + count rectasknum + return [te num,bs/cs num] + """ + db=self.connect_db(120) + dbexe="SELECT type,chdata.bctype,rectime.chtxt,rectime.title FROM rectime INNER JOIN chdata ON rectime.chtxt=chdata.chtxt" + dbexe=dbexe+" WHERE NOT( ( rectime.etime <= ? ) OR ( rectime.btime >= ? ) )" + Srec=0 + Trec=0 + for typet,bctypet, chtxtt, titlet in db.execute(dbexe,(btime,etime)): + if (typet=="rec") or (typet=="res") or (typet=="key") or (typet=="keyevery"): + if bctypet.find("cs")>-1: + Srec=Srec+1 + elif bctypet.find("bs")>-1: + Srec=Srec+1 + elif bctypet.find("te")>-1: + Trec=Trec+1 + self.close_db(db) + return [Trec,Srec] + def del_by_id_rectime(self,id): + db=self.connect_db(120) + db.execute('DELETE FROM rectime WHERE id='+id) + self.close_db(db) \ No newline at end of file diff --git a/Rec10WEB/trunk/src/rec10web_dbSQLite.py b/Rec10WEB/trunk/src/rec10web_dbSQLite.py new file mode 100644 index 0000000..785c511 --- /dev/null +++ b/Rec10WEB/trunk/src/rec10web_dbSQLite.py @@ -0,0 +1,154 @@ +#!/usr/bin/python +# coding: UTF-8 +# Rec10 TS Recording Tools +# Copyright (C) 2009 Yukikaze +import sqlite3 +import os +path = str(os.path.dirname(os.path.abspath(__file__))) + "/" +dbpath = path + "ch.db" +class DB_SQLite(): + dbpath="" + def __init__(self,dbpath): + self.dbpath = dbpath + def connect_db(self,tout=10): + global dbpath + """ + dbへの接続(timeoutは秒) + + """ + tout=tout*1000 + return sqlite3.connect(self.dbpath, timeout=tout) + def close_db(self,db): + db.close() + + def select_by_ontv_ch(self,ontv): + db=self.connect_db(120) + ret=db.execute("SELECT bctype,channel,display FROM ch WHERE channel= ? ",(ontv,)) + ret=ret.fetchall() + self.close_db(db) + return ret + def select_all_rectime(self): + db=self.connect_db(120) + recdata=[] + for id,typet, chtxt, title, btime, etime, deltatime, deltaday, opt in db.execute("SELECT id,type,chtxt,title,btime,etime,deltatime,deltaday,opt FROM rectime"): + ret={} + ret['id']=id + ret['type']=typet + ret['chtxt']=chtxt + ret['title']=title.encode('utf-8') + ret['btime']=btime + ret['etime']=etime + ret['opt']=opt + ret['deltatime']="" + ret['deltaday']="" + if deltatime == None: + deltatime="3" + if deltaday == None: + deltaday="7" + if typet == 'key': + ret['deltatime']=deltatime + elif typet == 'keyevery': + ret['deltatime']=deltatime + ret['deltaday']=deltaday + recdata.append(ret) + self.close_db(db) + return recdata + def select_by_chtxt_rectime(self,chtxt): + db=self.connect_db(120) + recdata=[] + for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in db.execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE chtxt = ? ",(chtxt,)): + ret={} + ret['type']=typet + ret['chtxt']=chtxt + ret['title']=title.encode('utf-8') + ret['btime']=btime + ret['etime']=etime + ret['opt']=opt + if deltatime == None: + deltatime="3" + if deltaday == None: + deltaday="7" + if typet == 'key': + ret['deltatime']=deltatime + elif typet == 'keyevery': + ret['deltatime']=deltatime + ret['deltaday']=deltaday + recdata.append(ret) + self.close_db(db) + return recdata + def select_by_id_rectime(self,id): + db=self.connect_db(120) + recdata=[] + for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in db.execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE id = ? ",(id,)): + ret={} + ret['type']=typet + ret['chtxt']=chtxt + ret['title']=title.encode('utf-8') + ret['btime']=btime + ret['etime']=etime + ret['opt']=opt + if deltatime == None: + deltatime="3" + if deltaday == None: + deltaday="7" + if typet == 'key': + ret['deltatime']=deltatime + elif typet == 'keyevery': + ret['deltatime']=deltatime + ret['deltaday']=deltaday + recdata.append(ret) + self.close_db(db) + return recdata + def select_all_chdata(self): + db=self.connect_db(120) + ret=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata") + ret=ret.fetchall() + self.close_db(db) + return ret + def select_by_chtxt_chdata(self,chtxt): + db=self.connect_db(120) + ret=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE chtxt=?",(chtxt,)) + ret=ret.fetchall() + self.close_db(db) + return ret + def select_by_station_chdata(self,station): + db=self.connect_db(120) + ret=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE station=?",(station,)) + ret=ret.fetchall() + self.close_db(db) + return ret + def select_by_station_name_chdata(self,station_name): + db=self.connect_db(120) + ret=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE station_name=?",(station_name,)) + ret=ret.fetchall() + self.close_db(db) + return ret + def add_rectime(self,type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""): + db = self.connect_db(480) + db.execute('INSERT OR IGNORE INTO rectime (type,chtxt,title,btime,etime,deltatime,deltaday,opt) values (?,?,?,datetime(?),datetime(?),?,?,?)', (type, chtxt, title, btime, etime, deltatime, deltaday, opt)) + db.commit() + self.close_db(db) + def count_schedule_rectime(self,btime,etime): + """ + count rectasknum + return [te num,bs/cs num] + """ + db=self.connect_db(120) + dbexe="SELECT type,chdata.bctype,rectime.chtxt,rectime.title FROM rectime INNER JOIN chdata ON rectime.chtxt=chdata.chtxt" + dbexe=dbexe+" WHERE NOT( ( rectime.etime <= ? ) OR ( rectime.btime >= ? ) )" + Srec=0 + Trec=0 + for typet,bctypet, chtxtt, titlet in db.execute(dbexe,(btime,etime)): + if (typet=="rec") or (typet=="res") or (typet=="key") or (typet=="keyevery"): + if bctypet.find("cs")>-1: + Srec=Srec+1 + elif bctypet.find("bs")>-1: + Srec=Srec+1 + elif bctypet.find("te")>-1: + Trec=Trec+1 + self.close_db(db) + return [Trec,Srec] + def del_by_id_rectime(self,id): + db=self.connect_db(120) + db.execute('DELETE FROM rectime WHERE id='+id) + self.close_db(db) \ No newline at end of file diff --git a/Rec10WEB/trunk/src/rswi.htm b/Rec10WEB/trunk/src/rswi.htm index 806fc1a..ecbd0f7 100644 --- a/Rec10WEB/trunk/src/rswi.htm +++ b/Rec10WEB/trunk/src/rswi.htm @@ -293,6 +293,7 @@ + 番組名/キーワード 隔日予約 + 番組名/キーワード