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