+++ /dev/null
-#!/usr/bin/python
-# coding: UTF-8
-# Rec10 TS Recording Tools
-# Copyright (C) 2009 Yukikaze
-import os
-import sqlite
-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 schedule_rectime(self, btime, etime):
- db = self.connect_db(120)
- dbexe = "SELECT type,chdata.bctype,rectime.chtxt,rectime.title,rectime.btime,rectime.etime FROM rectime INNER JOIN chdata ON rectime.chtxt=chdata.chtxt"
- dbexe = dbexe + " WHERE NOT( ( rectime.etime <= ? ) OR ( rectime.btime >= ? ) )"
- rets = []
- for typet, bctypet, chtxtt, titlet, btimet, etimet in db.execute(dbexe, (btime, etime)):
- ret = [typet, bctypet, chtxtt, titlet, btimet, etimet]
- rets.append(ret)
- self.close_db(db)
- return rets
- 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