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             ret['deltatime']=""
 120             ret['deltaday']=""
 121             if deltatime == None:
 122                 deltatime="3"
 123             if deltaday == None:
 124                 deltaday="7"
 125             if typet == 'key':
 126                 ret['deltatime']=deltatime
 127             elif typet == 'keyevery':
 128                 ret['deltatime']=deltatime
 129                 ret['deltaday']=deltaday
 130             recdata.append(ret)
 131         self.close_db(db)
 132         return recdata
 133     def select_bytime_rectime(self,dminutes):
 134         db=self.connect_db(480)
 135         recdatum=[]
 136         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\')"):
 137             ret={}
 138             ret['type']=typet
 139             ret['chtxt']=chtxt
 140             ret['title']=title.encode('utf-8')
 141             ret['btime']=btime
 142             ret['etime']=etime
 143             ret['opt']=opt
 144             if deltatime == None or deltatime == "":
 145                 deltatime="3"
 146             if deltaday == None or deltaday == "":
 147                 deltaday="7"
 148             if typet == 'key':
 149                 ret['deltatime']=deltatime
 150             elif typet == 'keyevery':
 151                 ret['deltatime']=deltatime
 152                 ret['deltaday']=deltaday
 153             recdatum.append(ret)
 154         self.close_db(db)
 155         return recdatum
 156     def delete_old_rectime(self,dhour):
 157         db=self.connect_db(480)
 158         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))
 159         db.commit()
 160         self.close_db(db)
 161     def new_tv(self,bctype):
 162         db=self.connect_db(480)
 163         try:
 164             db.execute("DELETE FROM tv WHERE bctype = ?", (bctype,))
 165         except:
 166             ""
 167         try:
 168             db.execute('CREATE TABLE tv (bctype TEXT,channel TEXT NOT NULL,start TEXT,stop  TEXT,title TEXT,exp  TEXT, UNIQUE (bctype,channel,start,stop,title,exp))')
 169         except:
 170             ""
 171         db.commit()
 172         self.close_db(db)
 173     def add_tv(self,bctype, channel, start, stop, title, exp):
 174         db=self.connect_db(480)
 175         db.execute('INSERT OR IGNORE INTO tv (bctype,channel,start,stop,title,exp) values (?,?,?,?,?,?)', (bctype, channel, start, stop, title, exp))
 176         db.commit()
 177         self.close_db(db)
 178     def add_multi_tv(self,tvlists):
 179         """
 180         tvlists is (bctype,channel,start,stop,title,exp) lists.
 181         """
 182         db=self.connect_db(480)
 183         db.executemany('INSERT OR IGNORE INTO tv (bctype,channel,start,stop,title,exp) values (?,?,?,?,?,?)', tvlists)
 184         db.commit()
 185         self.close_db(db)
 186     def select_by_title_tv(self,title):
 187         db=self.connect_db(480)
 188         ret=[]
 189         for ch, title, start, stop in db.execute("SELECT channel,title,start,stop FROM tv WHERE title LIKE \"%"+titletxt+"%\""):
 190             btime=start.replace(" +0900","")
 191             btime=btime[0:4]+"-"+btime[4:6]+"-"+btime[6:8]+" "+btime[8:10]+":"+btime[10:12]+":00"
 192             etime=stop.replace(" +0900","")
 193             etime=etime[0:4]+"-"+etime[4:6]+"-"+etime[6:8]+" "+etime[8:10]+":"+etime[10:12]+":00"
 194             try:
 195                 chtxt=chdb.ontvsearch(ch)['chtxt']
 196             except:
 197                 chtxt="Unknown"
 198             ret.append(chtxt+","+title.encode('utf-8')+","+btime+","+etime)
 199         self.close_db(db)
 200         return ret
 201     def select_by_time_ngram_tv(self,btime,etime,chtxt):
 202         db=self.connect_db(480)
 203         dbexe="SELECT chdata.chtxt,title,start,stop FROM tv INNER JOIN chdata WHERE chdata.ontv=tv.channel AND start >= ? AND start <= ? AND chdata.chtxt=?"
 204         dbcmd=db.execute(dbexe,(btime,etime,chtxt))
 205         retall=dbcmd.fetchall()
 206         self.close_db(db)
 207         return retall
 208     def new_ch(self,bctype):
 209         db=self.connect_db(480)
 210         try:
 211             db.execute("DELETE FROM ch WHERE bctype = ?",(bctype,))
 212         except:
 213             ""
 214         try:
 215             db.execute('CREATE TABLE ch (bctype TEXT,channel TEXT NOT NULL,display TEXT,UNIQUE (bctype,channel))')
 216         except:
 217             ""
 218         db.commit()
 219         self.close_db(db)
 220     def add_ch(self,bctype, channel, display):
 221         db=self.connect_db(480)
 222         db.execute('INSERT OR IGNORE INTO ch values (?,?,?)', (bctype, channel, display))
 223         db.commit()
 224         self.close_db(db)
 225     def add_multi_ch(self,chlists):
 226         """
 227         chlists is (bctype,channel,display) lists
 228         """
 229         db=self.connect_db(480)
 230         db.executemany('INSERT OR IGNORE INTO ch values (?,?,?)', chlists)
 231         db.commit()
 232         self.close_db(db)