rec10/trunk/src/dbSQLite.py

   1 #!/usr/bin/python
   2 # coding: UTF-8
   3 # Rec10 TS Recording Tools
   4 # Copyright (C) 2009 Yukikaze
   5 import sqlite3
   6 import recdb
   7 import os
   8 path = str(os.path.dirname(os.path.abspath(__file__))) + "/"
   9 dbpath = path + "ch.db"
  10 class DB_SQLite():
  11     dbpath=""
  12     def __init__(self,dbpath):
  13         self.dbpath = dbpath
  14         db = self.connect_db(480)
  15         try:
  16             db.execute('CREATE TABLW rectime (type TEXT,chtxt TEXT,title TEXT,btime TEXT,etime TEXT,deltatime TEXT,deltaday TEXT,opt TEXT,id INTEGER PRIMARY KEY,UNIQUE(type,chtxt,title,btime,deltaday))')
  17         except:
  18             ""
  19         db.commit
  20         self.close_db(db)
  21     def connect_db(self,tout=10):
  22         global dbpath
  23         """
  24         dbへの接続(timeoutは秒)
  25
  26         """
  27         tout=tout*1000
  28         return sqlite3.connect(self.dbpath, timeout=tout)
  29     def close_db(self,db):
  30         db.close()
  31     def new_chdata(self):
  32         db = self.connect_db(480)
  33         try:
  34             db.execute('DROP TABLE chdata')
  35             db.commit()
  36         except:
  37             ""
  38         try:
  39             db.execute('CREATE TABLE chdata (bctype TEXT,ontv TEXT,chtxt TEXT,ch TEXT,csch TEXT,station TEXT,station_name TEXT,updatetime TEXT)')
  40         except:
  41             ""
  42         db.commit()
  43         self.close_db(db)
  44     def add_chdata(self,bctype, ontv, chtxt, ch, csch, station, station_name,updatetime):
  45         db = self.connect_db(480)
  46         db.execute('INSERT OR IGNORE INTO chdata VALUES (?,?,?,?,?,?,?,datetime(?))',(bctype,ontv,chtxt,ch,csch,station,station_name,updatetime))
  47         db.commit()
  48         self.close_db(db)
  49     def select_by_ontv_chdata(self,ontv):
  50         db = self.connect_db(480)
  51         dbexe=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE ontv = ?",(ontv,))
  52         ret=dbexe.fetchall()
  53         self.close_db(db)
  54         return ret
  55     def select_by_chtxt_chdata(self,chtxt):
  56         db = self.connect_db(480)
  57         dbexe=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE chtxt = ?",(chtxt,))
  58         ret=dbexe.fetchall()
  59         self.close_db(db)
  60         return ret
  61     def select_by_bctype_chdata(self,bctype):
  62         db = self.connect_db(480)
  63         dbexe=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE bctype = ?",(bctype,))
  64         ret=dbexe.fetchall()
  65         self.close_db(db)
  66         return ret
  67     def select_by_ch_chdata(self,ch):
  68         db = self.connect_db(480)
  69         dbexe=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE ch = ?",(ch,))
  70         ret=dbexe.fetchall()
  71         self.close_db(db)
  72         return ret
  73     def select_all_chdata(self):
  74         db = self.connect_db(480)
  75         dbexe=db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata ")
  76         ret=dbexe.fetchall()
  77         self.close_db(db)
  78         return ret
  79     def select_get_update_chdata(self,dhour):
  80         db = self.connect_db(480)
  81         dbexe=db.execute("SELECT bctype,chtxt FROM chdata WHERE updatetime < datetime(\'now\',\'localtime\',\'-"+dhour+" hours\')")
  82         ret=dbexe.fetchall()
  83         self.close_db(db)
  84         return ret
  85     def update_by_bctype_chdata(self,bctype):
  86         db = self.connect_db(480)
  87         db.execute("UPDATE chdata SET updatetime=datetime(\'now\',\'localtime\') WHERE bctype = ?",(bctype,))
  88         db.commit()
  89         self.close_db(db)
  90     def update_by_bctype_and_chtxt_chdata(self,bctype,chtxt):
  91         db = self.connect_db(480)
  92         db.execute("UPDATE chdata SET updatetime=datetime(\'now\',\'localtime\') WHERE bctype = ? AND chtxt = ?",(bctype,chtxt))
  93         db.commit()
  94         self.close_db(db)
  95     def add_rectime(self,type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
  96         db = self.connect_db(480)
  97         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))
  98         db.commit()
  99         self.close_db(db)
 100     def del_rectime(self,type="", title="", chtxt="", btime=""):
 101         """
 102
 103         """
 104         db=self.connect_db(480)
 105         db.execute("DELETE FROM rectime where type = ? AND title = ? AND chtxt = ? AND btime = datetime(?)", (type, title, chtxt, btime))
 106         db.commit()
 107         self.close_db(db)
 108     def select_all_rectime(self):
 109         db=self.connect_db(480)
 110         recdata=[]
 111         for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in db.execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime"):
 112             ret={}
 113             ret['type']=typet
 114             ret['chtxt']=chtxt
 115             ret['title']=title.encode('utf-8')
 116             ret['btime']=btime
 117             ret['etime']=etime
 118             ret['opt']=opt
 119             if deltatime == None:
 120                 deltatime="3"
 121             if deltaday == None:
 122                 deltaday="7"
 123             if typet == 'key':
 124                 ret['deltatime']=deltatime
 125             elif typet == 'keyevery':
 126                 ret['deltatime']=deltatime
 127                 ret['deltaday']=deltaday
 128             recdata.append(ret)
 129         self.close_db(db)
 130         return recdata
 131     def select_bytime_rectime(self,dminutes):
 132         db=self.connect_db(480)
 133         recdatum=[]
 134         for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in db.execute("SELECT type, chtxt, title, DATETIME(btime), DATETIME(etime), deltatime ,deltaday ,opt FROM rectime WHERE btime < datetime(\'now\',\'localtime\',\'+" + dminutes + " minutes\') AND btime >datetime(\'now\',\'localtime\',\'-" + dminutes + " minutes\')"):
 135             ret={}
 136             ret['type']=typet
 137             ret['chtxt']=chtxt
 138             ret['title']=title.encode('utf-8')
 139             ret['btime']=btime
 140             ret['etime']=etime
 141             ret['opt']=opt
 142             if deltatime == None or deltatime == "":
 143                 deltatime="3"
 144             if deltaday == None or deltaday == "":
 145                 deltaday="7"
 146             if typet == 'key':
 147                 ret['deltatime']=deltatime
 148             elif typet == 'keyevery':
 149                 ret['deltatime']=deltatime
 150                 ret['deltaday']=deltaday
 151             recdatum.append(ret)
 152         self.close_db(db)
 153         return recdatum
 154     def delete_old_rectime(self,dhour):
 155         db=self.connect_db(480)
 156         db.execute("DELETE FROM rectime WHERE NOT ( type = ? OR type = ? ) AND btime < datetime(\'now\',\'localtime\',\'-" + dhour + " hours\')", (recdb.REC_MISS_ENCODE, recdb.REC_KEYWORD_EVERY_SOME_DAYS))
 157         db.commit()
 158         self.close_db(db)
 159     def new_tv(self,bctype):
 160         db=self.connect_db(480)
 161         try:
 162             db.execute("DELETE FROM tv WHERE bctype = ?", (bctype,))
 163         except:
 164             ""
 165         try:
 166             db.execute('CREATE TABLE tv (bctype TEXT,channel TEXT NOT NULL,start TEXT,stop  TEXT,title TEXT,desc  TEXT, UNIQUE (bctype,channel,start,stop,title,desc))')
 167         except:
 168             ""
 169         db.commit()
 170         self.close_db(db)
 171     def add_tv(self,bctype, channel, start, stop, title, desc):
 172         db=self.connect_db(480)
 173         db.execute('INSERT OR IGNORE INTO tv (bctype,channel,start,stop,title,desc) values (?,?,?,?,?,?)', (bctype, channel, start, stop, title, desc))
 174         db.commit()
 175         self.close_db(db)
 176     def add_multi_tv(self,tvlists):
 177         """
 178         tvlists is (bctype,channel,start,stop,title,desc) lists.
 179         """
 180         db=self.connect_db(480)
 181         db.executemany('INSERT OR IGNORE INTO tv (bctype,channel,start,stop,title,desc) values (?,?,?,?,?,?)', tvlists)
 182         db.commit()
 183         self.close_db(db)
 184     def select_by_title_tv(self,title):
 185         db=self.connect_db(480)
 186         ret=[]
 187         for ch, title, start, stop in db.execute("SELECT channel,title,start,stop FROM tv WHERE title LIKE \"%"+titletxt+"%\""):
 188             btime=start.replace(" +0900","")
 189             btime=btime[0:4]+"-"+btime[4:6]+"-"+btime[6:8]+" "+btime[8:10]+":"+btime[10:12]+":00"
 190             etime=stop.replace(" +0900","")
 191             etime=etime[0:4]+"-"+etime[4:6]+"-"+etime[6:8]+" "+etime[8:10]+":"+etime[10:12]+":00"
 192             try:
 193                 chtxt=chdb.ontvsearch(ch)['chtxt']
 194             except:
 195                 chtxt="Unknown"
 196             ret.append(chtxt+","+title.encode('utf-8')+","+btime+","+etime)
 197         self.close_db(db)
 198         return ret
 199     def select_by_time_ngram_tv(self,btime,etime,chtxt):
 200         db=self.connect_db(480)
 201         dbexe="SELECT chdata.chtxt,title,start,stop FROM tv INNER JOIN chdata WHERE chdata.ontv=tv.channel AND start >= ? AND start <= ? AND chdata.chtxt=?"
 202         dbcmd=db.execute(dbexe,(btime,etime,chtxt))
 203         retall=dbcmd.fetchall()
 204         self.close_db(db)
 205         return retall
 206     def new_ch(self,bctype):
 207         db=self.connect_db(480)
 208         try:
 209             db.execute("DELETE FROM ch WHERE bctype = ?",(bctype,))
 210         except:
 211             ""
 212         try:
 213             db.execute('CREATE TABLE ch (bctype TEXT,channel TEXT NOT NULL,display TEXT,UNIQUE (bctype,channel))')
 214         except:
 215             ""
 216         db.commit()
 217         self.close_db(db)
 218     def add_ch(self,bctype, channel, display):
 219         db=self.connect_db(480)
 220         db.execute('INSERT OR IGNORE INTO ch values (?,?,?)', (bctype, channel, display))
 221         db.commit()
 222         self.close_db(db)
 223     def add_multi_ch(self,chlists):
 224         """
 225         chlists is (bctype,channel,display) lists
 226         """
 227         db=self.connect_db(480)
 228         db.executemany('INSERT OR IGNORE INTO ch values (?,?,?)', chlists)
 229         db.commit()
 230         self.close_db(db)