Rec10WEB/trunk/src/rec10web_dbSQLite.py

   1 #!/usr/bin/python
   2 # coding: UTF-8
   3 # Rec10 TS Recording Tools
   4 # Copyright (C) 2009 Yukikaze
   5 import os
   6 import sqlite
   7 path = str(os.path.dirname(os.path.abspath(__file__))) + "/"
   8 dbpath = path + "ch.db"
   9 class DB_SQLite:
  10     dbpath = ""
  11     def __init__(self, dbpath):
  12         self.dbpath = dbpath
  13     def connect_db(self, tout=10):
  14         global dbpath
  15         """
  16         dbへの接続(timeoutは秒)
  17
  18         """
  19         tout = tout * 1000
  20         return sqlite3.connect(self.dbpath, timeout=tout)
  21     def close_db(self, db):
  22         db.close()
  23     def select_by_ontv_ch(self, ontv):
  24         db = self.connect_db(120)
  25         ret = db.execute("SELECT bctype,channel,display FROM ch WHERE channel= ? ", (ontv, ))
  26         ret = ret.fetchall()
  27         self.close_db(db)
  28         return ret
  29     def select_all_rectime(self):
  30         db = self.connect_db(120)
  31         recdata = []
  32         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"):
  33             ret = {}
  34             ret['id'] = id
  35             ret['type'] = typet
  36             ret['chtxt'] = chtxt
  37             ret['title'] = title.encode('utf-8')
  38             ret['btime'] = btime
  39             ret['etime'] = etime
  40             ret['opt'] = opt
  41             ret['deltatime'] = ""
  42             ret['deltaday'] = ""
  43             if deltatime == None:
  44                 deltatime = "3"
  45             if deltaday == None:
  46                 deltaday = "7"
  47             if typet == 'key':
  48                 ret['deltatime'] = deltatime
  49             elif typet == 'keyevery':
  50                 ret['deltatime'] = deltatime
  51                 ret['deltaday'] = deltaday
  52             recdata.append(ret)
  53         self.close_db(db)
  54         return recdata
  55     def select_by_chtxt_rectime(self, chtxt):
  56         db = self.connect_db(120)
  57         recdata = []
  58         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, )):
  59             ret = {}
  60             ret['type'] = typet
  61             ret['chtxt'] = chtxt
  62             ret['title'] = title.encode('utf-8')
  63             ret['btime'] = btime
  64             ret['etime'] = etime
  65             ret['opt'] = opt
  66             if deltatime == None:
  67                 deltatime = "3"
  68             if deltaday == None:
  69                 deltaday = "7"
  70             if typet == 'key':
  71                 ret['deltatime'] = deltatime
  72             elif typet == 'keyevery':
  73                 ret['deltatime'] = deltatime
  74                 ret['deltaday'] = deltaday
  75             recdata.append(ret)
  76         self.close_db(db)
  77         return recdata
  78     def select_by_id_rectime(self, id):
  79         db = self.connect_db(120)
  80         recdata = []
  81         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, )):
  82             ret = {}
  83             ret['type'] = typet
  84             ret['chtxt'] = chtxt
  85             ret['title'] = title.encode('utf-8')
  86             ret['btime'] = btime
  87             ret['etime'] = etime
  88             ret['opt'] = opt
  89             if deltatime == None:
  90                 deltatime = "3"
  91             if deltaday == None:
  92                 deltaday = "7"
  93             if typet == 'key':
  94                 ret['deltatime'] = deltatime
  95             elif typet == 'keyevery':
  96                 ret['deltatime'] = deltatime
  97                 ret['deltaday'] = deltaday
  98             recdata.append(ret)
  99         self.close_db(db)
 100         return recdata
 101     def select_all_chdata(self):
 102         db = self.connect_db(120)
 103         ret = db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata")
 104         ret = ret.fetchall()
 105         self.close_db(db)
 106         return ret
 107     def select_by_chtxt_chdata(self, chtxt):
 108         db = self.connect_db(120)
 109         ret = db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE chtxt=?", (chtxt, ))
 110         ret = ret.fetchall()
 111         self.close_db(db)
 112         return ret
 113     def select_by_station_chdata(self, station):
 114         db = self.connect_db(120)
 115         ret = db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE station=?", (station, ))
 116         ret = ret.fetchall()
 117         self.close_db(db)
 118         return ret
 119     def select_by_station_name_chdata(self, station_name):
 120         db = self.connect_db(120)
 121         ret = db.execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM chdata WHERE station_name=?", (station_name, ))
 122         ret = ret.fetchall()
 123         self.close_db(db)
 124         return ret
 125     def add_rectime(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
 126         db = self.connect_db(480)
 127         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))
 128         db.commit()
 129         self.close_db(db)
 130     def schedule_rectime(self, btime, etime):
 131         db = self.connect_db(120)
 132         dbexe = "SELECT type,chdata.bctype,rectime.chtxt,rectime.title,rectime.btime,rectime.etime FROM rectime INNER JOIN chdata ON rectime.chtxt=chdata.chtxt"
 133         dbexe = dbexe + " WHERE NOT( ( rectime.etime <= ? ) OR ( rectime.btime >= ? ) )"
 134         rets = []
 135         for typet, bctypet, chtxtt, titlet, btimet, etimet in db.execute(dbexe, (btime, etime)):
 136             ret = [typet, bctypet, chtxtt, titlet, btimet, etimet]
 137             rets.append(ret)
 138         self.close_db(db)
 139         return rets
 140     def count_schedule_rectime(self, btime, etime):
 141         """
 142         count rectasknum
 143         return [te num,bs/cs num]
 144         """
 145         db = self.connect_db(120)
 146         dbexe = "SELECT type,chdata.bctype,rectime.chtxt,rectime.title FROM rectime INNER JOIN chdata ON rectime.chtxt=chdata.chtxt"
 147         dbexe = dbexe + " WHERE NOT( ( rectime.etime <= ? ) OR ( rectime.btime >= ? ) )"
 148         Srec = 0
 149         Trec = 0
 150         for typet, bctypet, chtxtt, titlet in db.execute(dbexe, (btime, etime)):
 151             if (typet == "rec") or (typet == "res") or (typet == "key") or (typet == "keyevery"):
 152                 if bctypet.find("cs") > -1:
 153                     Srec = Srec + 1
 154                 elif bctypet.find("bs") > -1:
 155                     Srec = Srec + 1
 156                 elif bctypet.find("te") > -1:
 157                     Trec = Trec + 1
 158         self.close_db(db)
 159         return [Trec, Srec]
 160     def del_by_id_rectime(self, id):
 161         db = self.connect_db(120)
 162         db.execute('DELETE FROM rectime WHERE id=' + id)
 163         self.close_db(db)