#!/usr/bin/python
# coding: UTF-8
# Rec10 TS Recording Tools
# Copyright (C) 2009-2012 Yukikaze
import MySQLdb
import recdblist
import warnings
import traceback

from decimal import Decimal
class DB_MySQL:
    dbname = ""
    dbhost = ""
    dbusr = ""
    dbpasswd = ""
    dbport = 0
    def __init__(self, dbname, user, passwd, host="localhost", port=3306):
        warnings.filterwarnings('ignore', "Data truncated for column")
        self.dbname = dbname
        self.dbhost = host
        self.dbusr = user
        self.dbpasswd = passwd
        self.dbport = port
        try:
            con = MySQLdb.connect(user=user, passwd=passwd)
            cur = con.cursor()
            cur.execute('CREATE DATABASE ' + dbname + " DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;")
            cur.close()
            con.close()
        except Exception, inst:
            if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
                recdblist.addCommonlogEX("Error", "init (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
        db = self.connect_db()
        try:
            db[1].execute('\
            CREATE TABLE timeline \
            (\
            id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
            type VARCHAR(40),\
            chtxt VARCHAR(40),\
            title VARCHAR(100),\
            btime DATETIME,\
            etime DATETIME,\
            deltatime VARCHAR(5),\
            deltaday VARCHAR(5),\
            opt VARCHAR(20),\
            epgtitle VARCHAR(100),\
            epgbtime DATETIME,\
            epgetime DATETIME,\
            epgduplicate TINYINT DEFAULT 0,\
            epgchange TINYINT DEFAULT 0,\
            epgexp VARCHAR(200),\
            epgcategory VARCHAR(100),\
            counter TINYINT DEFAULT -1,\
            UNIQUE uni (type,chtxt,title,btime,deltaday)\
            )')
        except Exception, inst:
            if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
                recdblist.addCommonlogEX("Error", "init (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
        self.close_db(db)
        self.new_epg_timeline("")
        self.new_in_timeline_log()
        self.new_in_auto_bayes_key()
        self.new_in_auto_jbk_key()
        self.new_in_status()
        self.new_in_settings()
        self.new_auto_timeline_bayes()
        self.new_auto_timeline_keyword()
    def connect_db(self):
        """
        dbへの接続
        """
        con = MySQLdb.connect(db=self.dbname, host=self.dbhost, port=self.dbport, user=self.dbusr, passwd=self.dbpasswd, charset="utf8")
        cur = con.cursor()
        try:
            con.autocommit(1)
        except:
            ""
        cur.execute('set names utf8;')
        return [con, cur]
    def close_db(self, db):
        db[1].close()
        db[0].close()
    def new_epg_ch(self):
        db = self.connect_db()
        try:
            db[1].execute('drop table epg_ch')
        except Exception, inst:
            if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
                recdblist.addCommonlogEX("Error", "new_epg_ch drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)

        try:
            db[1].execute('\
            CREATE TABLE epg_ch \
            (\
            bctype VARCHAR(15),\
            chtxt VARCHAR(20) PRIMARY KEY,\
            ch VARCHAR(20),\
            csch VARCHAR(20),\
            tsid VARCHAR(20),\
            chname VARCHAR(100),\
            updatetime DATETIME,\
            status TINYINT,\
            visible TINYINT DEFAULT 1,\
            logo0 BLOB,\
            logo1 BLOB,\
            logo2 BLOB,\
            logo3 BLOB,\
            logo4 BLOB,\
            logo5 BLOB,\
            logoupdate DATETIME,\
            logostatus TINYINT DEFAULT 2,\
            scanupdate DATETIME\
            )')
        except Exception, inst:
            if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
                recdblist.addCommonlogEX("Error", "new_epg_ch (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)

        self.close_db(db)
    def add_epg_ch(self, bctype, chtxt, ch, csch,tsid,updatetime,logoupdate,scanupdate):
        db = self.connect_db()
        db[1].execute('\
        INSERT INTO epg_ch (bctype,chtxt,ch,csch,tsid,chname,updatetime,status,visible,logoupdate,scanupdate)\
        VALUES (%s,%s,%s,%s,%s,"",%s,%s,%s,%s,%s)', \
                      (bctype, chtxt, ch, csch,tsid,updatetime, "1","1",logoupdate,scanupdate))
        self.close_db(db)
    def delete_all_epg_ch(self):
        db = self.connect_db()
        db[1].execute('\
        DROP TABLE epg_ch ')
        self.close_db(db)
    def select_by_chtxt_epg_ch(self, chtxt):
        db = self.connect_db()
        dbexe = db[1].execute("\
        SELECT bctype,chtxt,ch,csch,tsid,updatetime,chname,status,visible,logoupdate,scanupdate \
        FROM epg_ch \
        WHERE chtxt LIKE %s", \
                              (chtxt,))
        ret = []
        dls = []
        if dbexe > 0:
            dls = db[1].fetchall()
        self.close_db(db)
        for dl in dls:
            r = list(dl)
            r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
            if r[9]!=None:
                r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
            else:
                r[9]="2012-04-01 00:00:00"
            r[10] = r[10].strftime("%Y-%m-%d %H:%M:%S")
            ret.append(r)
        return ret
    def select_by_bctype_epg_ch(self, bctype):
        db = self.connect_db()
        dbexe = db[1].execute("\
        SELECT bctype,chtxt,ch,csch,tsid,updatetime,status,chname,status,visible,logoupdate,scanupdate \
        FROM epg_ch \
        WHERE bctype = %s", \
                              (bctype,))
        ret = []
        dls = []
        if dbexe > 0:
            dls = db[1].fetchall()
        self.close_db(db)
        for dl in dls:
            #recdblist.printutf8(dl)
            r = list(dl)
            r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
            if r[10]!=None:
                r[10] = r[10].strftime("%Y-%m-%d %H:%M:%S")
            else:
                r[10]="2011-04-01 00:00:00"
            r[11] = r[11].strftime("%Y-%m-%d %H:%M:%S")
            r[6] = str(r[6])
            ret.append(r)
        import random
        ret.insert(0,ret[random.randint(0,len(ret)-1)])
        return ret
    def select_by_ch_epg_ch(self, ch):
        db = self.connect_db()
        dbexe = db[1].execute("\
        SELECT \
        bctype,chtxt,ch,csch,tsid,updatetime,chname,status,visible,logoupdate,scanupdate \
        FROM epg_ch \
        WHERE ch = %s", \
                              (ch,))
        ret = []
        dls = []
        if dbexe > 0:
            dls = db[1].fetchall()
        self.close_db(db)
        for dl in dls:
            r = list(dl)
            r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
            r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
            r[10] = r[10].strftime("%Y-%m-%d %H:%M:%S")
            ret.append(r)
        return ret
    def select_by_csch_epg_ch(self, csch):
        db = self.connect_db()
        dbexe = db[1].execute("\
        SELECT \
        bctype,chtxt,ch,csch,tsid,updatetime,chname,status,visible,logoupdate,scanupdate \
        FROM epg_ch \
        WHERE csch = %s", \
                              (csch,))
        ret = []
        dls = []
        if dbexe > 0:
            dls = db[1].fetchall()
        self.close_db(db)
        for dl in dls:
            r = list(dl)
            r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
            r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
            r[10] = r[10].strftime("%Y-%m-%d %H:%M:%S")
            ret.append(r)
        return ret
    def select_all_epg_ch(self):
        db = self.connect_db()
        dbexe = db[1].execute("\
        SELECT bctype,chtxt,ch,csch,tsid,updatetime,chname,status,visible,logoupdate,scanupdate \
        FROM epg_ch \
        ")
        ret = []
        dls = []
        if dbexe > 0:
            dls = db[1].fetchall()
        self.close_db(db)
        for dl in dls:
            r = list(dl)
            r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
            r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
            r[10] = r[10].strftime("%Y-%m-%d %H:%M:%S")
            ret.append(r)
        return ret
    def change_visible_epg_ch(self,chtxt,visible):
        db = self.connect_db()
        db[1].execute("\
        UPDATE epg_ch SET visible=%s WHERE chtxt=%s",(visible,chtxt))
        self.close_db(db)
    def change_logodata_epg_ch(self,chtxt,logonum,logodata):
        db = self.connect_db()
        db[1].execute("\
        UPDATE epg_ch SET logo"+str(logonum)+"=%s WHERE chtxt=%s",(logodata,chtxt))
        self.close_db(db)
    def set_new_status(self,dhour):
        db = self.connect_db()
        dbexe = db[1].execute("UPDATE epg_ch \
        SET status = 1 \
        WHERE \
        ( \
        updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
        AND \
        status = 0 \
        )"\
        )
    def select_get_update_epg_ch(self, dhour):
        db = self.connect_db()
        dbexe = db[1].execute("SELECT bctype,chtxt,status FROM epg_ch \
        WHERE (\
        ( \
        updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
        AND \
        status = 1 \
        ) \
        OR \
        status > 1 )\
        ORDER BY status DESC")
        ret = []
        #recdblist.printutf8(dbexe)
        if dbexe > 0:
            ret = db[1].fetchall()
        self.close_db(db)
        return ret
    def select_get_updatelogo_epg_ch(self, dhour):
        db = self.connect_db()
        dbexe = db[1].execute("SELECT bctype,chtxt,logostatus FROM epg_ch \
        WHERE (\
        ( \
        updatetime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR) \
        AND \
        logostatus = 1 \
        ) \
        OR \
        logostatus > 1 )\
        ORDER BY status DESC")
        ret = []
        #recdblist.printutf8(dbexe)
        if dbexe > 0:
            ret = db[1].fetchall()
        self.close_db(db)
        return ret
    def update_by_bctype_epg_ch(self, bctype):
        db = self.connect_db()
        db[1].execute("\
        UPDATE epg_ch \
        SET \
        updatetime=now() , \
        status = 1 \
        WHERE bctype = %s", (bctype,))
        self.close_db(db)

    def update_by_bctype_and_chtxt_epg_ch(self, bctype, chtxt):
        db = self.connect_db()
        db[1].execute("\
        UPDATE epg_ch \
        SET \
        updatetime=now() , \
        status = 1\
        WHERE bctype = %s AND chtxt = %s", (bctype, chtxt))
        self.close_db(db)
    def update_chname_by_chtxt_epg_ch(self,chtxt,chname):
        db = self.connect_db()
        db[1].execute("\
        UPDATE epg_ch \
        SET \
        chname = %s \
        WHERE chtxt = %s", (chname,chtxt))
        self.close_db(db)
    def update_status_by_bctype_epg_ch(self, bctype, status):
        db = self.connect_db()
        db[1].execute("\
        UPDATE epg_ch \
        SET \
        status=%s , \
        updatetime=now() \
        WHERE bctype = %s", \
                      (status, bctype)\
                      )
        self.close_db(db)
    def update_logostatus_by_bctype_epg_ch(self,bctype,logostatus):
        db = self.connect_db()
        db[1].execute("\
        UPDATE epg_ch \
        SET \
        logostatus=%s , \
        logoupdate=now() \
        WHERE bctype = %s", \
                      (logostatus, bctype)\
                      )
        self.close_db(db)
    def add_auto_proc_tmp(self,type,title,chtxt):
        db = self.connect_db()
        db[1].execute('\
        INSERT IGNORE into auto_proc_tmp \
        (type,title,chtxt) \
        values (%s,%s,%s)',(type,title,chtxt))
        ##db.commit()
        self.close_db(db)
    def new_auto_proc_tmp(self):
        db = self.connect_db()
        try:
            db[1].execute('drop table auto_proc_tmp')
        except Exception, inst:
            if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
                recdblist.addCommonlogEX("Error", "new_auto_proc_tmp drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)

        try:
            db[1].execute('\
            CREATE TABLE auto_proc_tmp \
            (\
            type VARCHAR(20),\
            title VARCHAR(100) PRIMARY KEY,\
            chtxt VARCHAR(30),\
            UNIQUE unibayeskey(title)\
            )')
        except Exception, inst:
            if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
                recdblist.addCommonlogEX("Error", "new_auto_proc_tmp (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)

        self.close_db(db)
    def update_auto_proc(self):
        db = self.connect_db()
        try:
            db[1].execute('INSERT INTO auto_proc SELECT * FROM auto_proc_tmp')
        except Exception, inst:
            if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
                recdblist.addCommonlogEX("Error", "update_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)

        self.close_db(db)
    def new_auto_proc(self):
        db = self.connect_db()
        try:
            db[1].execute('drop table auto_proc')
        except Exception, inst:
            if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
                recdblist.addCommonlogEX("Error", "new_auto_proc drop (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)

        try:
            db[1].execute('\
            CREATE TABLE auto_proc \
            (\
            type VARCHAR(20),\
            title VARCHAR(100) PRIMARY KEY,\
            chtxt VARCHAR(30),\
            UNIQUE unibayeskey(title)\
            )')
        except Exception, inst:
            if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
                recdblist.addCommonlogEX("Error", "new_auto_proc (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)

        self.close_db(db)
    def add_auto_proc(self,type,title,chtxt):
        db = self.connect_db()
        db[1].execute('\
        INSERT IGNORE into auto_proc \
        (type,title,chtxt) \
        values (%s,%s,%s)',(type,title,chtxt))
        ##db.commit()
        self.close_db(db)
    def drop_in_settings(self):
        db = self.connect_db()
        try:
            db[1].execute('drop table in_settings')
        except Exception, inst:
            if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
                recdblist.addCommonlogEX("Error", "drop_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
        self.close_db(db)
    def new_in_settings(self):
        db = self.connect_db()
        try:
            db[1].execute('\
            CREATE TABLE in_settings \
            (\
            auto_jbk TINYINT,\
            auto_bayes TINYINT,\
            auto_opt VARCHAR(20),\
            auto_del_tmp TINYINT\
            )')
            db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"H\",1)")
        except Exception, inst:
            if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
                recdblist.addCommonlogEX("Error", "new_in_settings (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)

        self.close_db(db)
    def select_all_in_settings(self):
        db = self.connect_db()
        dbexe = db[1].execute("\
        SELECT auto_jbk,auto_bayes,auto_del_tmp,auto_opt \
        FROM in_settings \
        ")
        ret = []
        dls = []
        if dbexe > 0:
            dls = db[1].fetchall()
        self.close_db(db)
        for dl in dls:
            r = list(dl)
            r[0]=str(r[0])
            r[1]=str(r[1])
            r[2]=str(r[2])
            r[3]=r[3]
            ret.append(r)
        return ret
    def add_in_timeline_log(self , chtxt="", title="", btime="", etime="", opt="", exp="", longexp="", category=""):
        db = self.connect_db()
        db[1].execute('\
        INSERT IGNORE into in_timeline_log \
        (chtxt,title,btime,etime,opt,exp,longexp,category) \
        values (%s,%s,%s,%s,%s,%s,%s,%s)', \
                      ( chtxt, title, btime, etime, opt,exp,longexp,category))
        ##db.commit()
        self.close_db(db)
    def del_in_timeline_log(self, title="", chtxt="", btime=""):
        """

        """
        db = self.connect_db()
        db[1].execute("\
        DELETE FROM in_timeline_log \
        WHERE title = %s AND chtxt = %s AND btime = %s", \
                      (title, chtxt, btime))
        #db.commit()
        self.close_db(db)
    def new_in_timeline_log(self):
        db = self.connect_db()
        try:
            db[1].execute('\
            CREATE TABLE in_timeline_log \
            (\
            id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
            chtxt VARCHAR(20),\
            title VARCHAR(100),\
            btime DATETIME,\
            etime DATETIME,\
            opt VARCHAR(20),\
            exp VARCHAR(200),\
            longexp TEXT,\
            category VARCHAR(100),\
            UNIQUE uni (chtxt,title,btime,category)\
            )')
        except Exception, inst:
            if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
                recdblist.addCommonlogEX("Error", "new_in_timeline_log (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)

        self.close_db(db)
    def select_chtxt_by_title_timeline_log(self,title):
        db = self.connect_db()
        dbexe = db[1].execute("\
        SELECT chtxt \
        FROM in_timeline_log \
        WHERE title LIKE \"%"+title+"%\"\
        GROUP by chtxt\
        ORDER by sum(1) DESC limit 1")
        retdb=db[1].fetchall()
        ret=""
        if ret!=None:
            if len(retdb)>0:
                ret=retdb[0][0]
        self.close_db(db)
        return ret
    def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt="" ,counter=-1):
        db = self.connect_db()
        db[1].execute('\
        INSERT IGNORE into timeline \
        (type,chtxt,title,btime,etime,deltatime,deltaday,opt,counter) \
        values (%s,%s,%s,%s,%s,%s,%s,%s,%s)', \
                      (type, chtxt, title, btime, etime, deltatime, deltaday, opt ,counter))
        ##db.commit()
        self.close_db(db)
    def update_epg_timeline(self,type,chtxt,title,btime,epgbtime,epgetime,epgtitle,epgexp,epgcategory):
        db = self.connect_db()
        db[1].execute('\
        UPDATE timeline \
        SET epgbtime=%s,epgetime=%s,epgtitle=%s,epgexp=%s,epgcategory=%s \
        WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
                      (epgbtime,epgetime,epgtitle,epgexp,epgcategory,type, chtxt, title, btime))
        ##db.commit()
        self.close_db(db)
    def update_status_change_timeline(self,type,chtxt,title,btime,epgchange):
        db = self.connect_db()
        db[1].execute('\
        UPDATE timeline \
        SET epgchange =%s \
        WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
                      (epgchange , type, chtxt, title, btime))
        ##db.commit()
        self.close_db(db)
    def update_status_dup_timeline(self,type,chtxt,title,btime,epgduplicate):
        db = self.connect_db()
        db[1].execute('\
        UPDATE timeline \
        SET epgduplicate =%s \
        WHERE type=%s AND chtxt=%s AND title=%s AND btime=%s ', \
                      (epgduplicate , type, chtxt, title, btime))
        ##db.commit()
        self.close_db(db)
    def del_timeline(self, type="", title="", chtxt="", btime=""):
        """

        """
        db = self.connect_db()
        db[1].execute("\
        DELETE FROM timeline \
        WHERE type = %s AND title = %s AND chtxt = %s AND btime = %s", \
                      (type, title, chtxt, btime))
        #db.commit()
        self.close_db(db)
    def select_all_timeline(self):
        db = self.connect_db()
        recdata = []
        dbr = db[1].execute("\
        SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
        FROM timeline")
        dbl = db[1].fetchall()
        self.close_db(db)
        if dbr > 0:
            recdata = self.getdic_timeline(dbl)
        return recdata
    def select_bytime_timeline(self, dminutes):
        db = self.connect_db()
        recdatum = []
        #dbr=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE btime < DATE_SUB(now(),INTERVAL "+dminutes+" MINUTE ) AND btime > DATE_ADD(now(),INTERVAL "+dminutes+" MINUTE )")
        dbr = db[1].execute("SELECT \
        type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
        FROM timeline \
        WHERE btime BETWEEN DATE_SUB(now(),INTERVAL " + dminutes + " MINUTE ) AND \
        DATE_ADD(now(),INTERVAL " + dminutes + " MINUTE )")
        dbl = db[1].fetchall()
        self.close_db(db)
        #recdblist.printutf8(dbl)
        if dbr > 0:
            recdatum = self.getdic_timeline(dbl)
        return recdatum
    def select_by_name_time_timeline(self,title,btime,btime2):
        db = self.connect_db()
        recdatum = []
        #dbr=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE btime < DATE_SUB(now(),INTERVAL "+dminutes+" MINUTE ) AND btime > DATE_ADD(now(),INTERVAL "+dminutes+" MINUTE )")
        dbr = db[1].execute("SELECT \
        type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
        FROM timeline \
        WHERE btime > %s AND \
        btime < %s AND title = %s",(btime,btime2,title))
        dbl = db[1].fetchall()
        self.close_db(db)
        #recdblist.printutf8(dbl)
        if dbr > 0:
            recdatum = self.getdic_timeline(dbl)
        return recdatum
    def select_bytime_all_timeline(self,btime,etime):
        db = self.connect_db()
        recdatum = []
        #dbr=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE btime < DATE_SUB(now(),INTERVAL "+dminutes+" MINUTE ) AND btime > DATE_ADD(now(),INTERVAL "+dminutes+" MINUTE )")
        dbr = db[1].execute("SELECT \
        type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
        FROM timeline \
        WHERE btime >= %s AND \
        etime <= %s",(btime,etime))
        dbl = db[1].fetchall()
        self.close_db(db)
        #recdblist.printutf8(dbl)
        if dbr > 0:
            recdatum = self.getdic_timeline(dbl)
        return recdatum
    def select_byepgtime_all_timeline(self,epgbtime,epgetime):
        db = self.connect_db()
        recdatum = []
        #dbr=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE btime < DATE_SUB(now(),INTERVAL "+dminutes+" MINUTE ) AND btime > DATE_ADD(now(),INTERVAL "+dminutes+" MINUTE )")
        dbr = db[1].execute("SELECT \
        type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
        FROM timeline \
        WHERE epgbtime >= %s AND \
        epgetime <= %s",(epgbtime,epgetime))
        dbl = db[1].fetchall()
        self.close_db(db)
        #recdblist.printutf8(dbl)
        if dbr > 0:
            recdatum=self.getdic_timeline(dbl)
        return recdatum
    def select_byepgtime_over_timeline(self,epgbtime,epgetime):
        db = self.connect_db()
        recdatum = []
        #dbr=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE btime < DATE_SUB(now(),INTERVAL "+dminutes+" MINUTE ) AND btime > DATE_ADD(now(),INTERVAL "+dminutes+" MINUTE )")
        dbr = db[1].execute("SELECT \
        type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
        FROM timeline \
        WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"\
        ,(epgbtime,epgetime))
        dbl = db[1].fetchall()
        self.close_db(db)
        #recdblist.printutf8(dbl)
        if dbr > 0:
            recdatum=self.getdic_timeline(dbl)
        return recdatum
    def count_schedule_timeline(self, btime, etime):
        """
        count rectasknum
        return [te num,bs/cs num]
        """
        db = self.connect_db()
        dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
        dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
        Srec = 0
        Trec = 0
        db[1].execute(dbexe, (btime, etime,btime,etime))
        dbl=db[1].fetchall()
        for typet, bctypet, chtxtt, titlet in dbl:
            if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
                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 search_schedule_timeline(self,btime,etime):
        """
        count rectasknum
        return [(type,bctype,chtxt,title,btime,etime)]
        """
        db = self.connect_db()
        dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title,timeline.btime,timeline.etime FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
        dbexe = dbexe + " WHERE ((NOT(( timeline.etime <= %s )OR( timeline.btime >= %s ))) OR ((timeline.btime = %s) AND (timeline.etime = %s) ) )"
        ret=[]
        db[1].execute(dbexe, (btime, etime,btime,etime))
        dbl=db[1].fetchall()
        for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
            if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
                ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
        self.close_db(db)
        return ret
    def count_epgschedule_timeline(self, epgbtime, epgetime):
        """
        count rectasknum
        return [te num,bs/cs num]
        """
        db = self.connect_db()
        dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
        dbexe = dbexe + " WHERE (NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s )))"
        Srec = 0
        Trec = 0
        db[1].execute(dbexe, (epgbtime, epgetime))
        dbl=db[1].fetchall()
        for typet, bctypet, chtxtt, titlet in dbl:
            if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
                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 search_epgschedule_timeline(self,epgbtime,epgetime):
        """
        count rectasknum
        return [(type,bctype,chtxt,title,btime,etime)]
        """
        db = self.connect_db()
        dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title,timeline.btime,timeline.etime FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
        dbexe = dbexe + " WHERE ((NOT(( timeline.epgetime <= %s )OR( timeline.epgbtime >= %s ))) OR ((timeline.epgbtime = %s) AND (timeline.epgetime = %s) ) )"
        ret=[]
        db[1].execute(dbexe, (epgbtime, epgetime,epgbtime,epgetime))
        dbl=db[1].fetchall()
        for typet, bctypet, chtxtt, titlet , btimet, etimet in dbl:
            if (typet == recdblist.REC_RESERVE) or (typet == recdblist.REC_FINAL_RESERVE) or (typet == recdblist.REC_KEYWORD) or (typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS):
                ret.append([typet,bctypet,chtxtt,titlet,btimet,etimet])
        self.close_db(db)
        return ret
    def select_bytime_bychtxt_all_timeline(self,btime,etime,chtxt):
        db = self.connect_db()
        recdatum = []
        #dbr=db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM rectime WHERE btime < DATE_SUB(now(),INTERVAL "+dminutes+" MINUTE ) AND btime > DATE_ADD(now(),INTERVAL "+dminutes+" MINUTE )")
        dbr = db[1].execute("SELECT \
        type, chtxt, title, btime, etime, deltatime ,deltaday ,opt ,epgbtime ,epgetime ,epgtitle ,epgduplicate ,epgchange ,counter\
        FROM timeline \
        WHERE btime > %s AND \
        etime < %s\
        AND chtxt=%s ",(btime,etime,chtxt))
        dbl = db[1].fetchall()
        self.close_db(db)
        #recdblist.printutf8(dbl)
        if dbr > 0:
            recdatum = self.getdic_timeline(dbl)
        return recdatum
    def getdic_timeline(self,timelinelists):
        recdatum=[]
        for typet, chtxt, title, btime, etime, deltatime, deltaday, opt ,epgbtimet , epgetimet ,epgtitlet ,epgduplicatet ,epgchanget ,countert in timelinelists:
            ret = {}
            ret['type'] = typet
            ret['chtxt'] = chtxt
            ret['title'] = title
            btime = btime.strftime("%Y-%m-%d %H:%M:%S")
            etime = etime.strftime("%Y-%m-%d %H:%M:%S")
            ret['btime'] = btime
            ret['etime'] = etime
            ret['opt'] = opt
            try:
                ret['epgbtime'] = epgbtimet.strftime("%Y-%m-%d %H:%M:%S")
                ret['epgetime'] = epgetimet.strftime("%Y-%m-%d %H:%M:%S")
            except:
                ret['epgbtime'] = "2010-01-01 00:00:00"
                ret['epgetime'] = "2010-01-01 00:00:00"
            ret['epgtitle'] = epgtitlet
            ret['epgduplicate'] = epgduplicatet
            ret['epgchange'] = epgchanget
            if deltatime == None or deltatime == "":
                deltatime = "3"
            if deltaday == None or deltaday == "":
                deltaday = "7"
            if typet == recdblist.REC_KEYWORD:
                ret['deltatime'] = deltatime
            elif typet == recdblist.REC_KEYWORD_EVERY_SOME_DAYS:
                ret['deltatime'] = deltatime
                ret['deltaday'] = deltaday
            try:
                ret['counter'] = int(countert)
            except:
                ret['counter']=-1
            recdatum.append(ret)
        return recdatum
    def delete_old_timeline(self, dhour):
        db = self.connect_db()
        db[1].execute("\
        DELETE FROM timeline \
        WHERE \
        btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
        #db.commit()
        self.close_db(db)
    def new_in_auto_jbk_key(self):
        db = self.connect_db()
        try:
            db[1].execute("\
            CREATE TABLE in_auto_jbk_key \
            (\
            id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
            keyword VARCHAR(200),\
            auto TINYINT DEFAULT 0,\
            opt VARCHAR(20),\
            UNIQUE unijbk (keyword)\
            )")
        except Exception, inst:
            if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
                recdblist.addCommonlogEX("Error", "new_in_auto_jbk_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
        self.close_db(db)
    def add_in_auto_jbk_key(self,key):
        db = self.connect_db()
        db[1].execute('\
        INSERT IGNORE into in_auto_jbk_key \
        (keyword) \
        values (%s)', \
                      (key,))
        ##db.commit()
        self.close_db(db)
    def select_all_in_auto_jbk_key(self):
        db = self.connect_db()
        dbexe = db[1].execute("\
        SELECT keyword,auto,opt \
        FROM in_auto_jbk_key \
vim         ")
        ret = []
        if dbexe > 0:
            ret = db[1].fetchall()
        self.close_db(db)
        return ret
    def drop_in_status(self):
        db = self.connect_db()
        try:
            db[1].execute('drop table in_status')
        except Exception, inst:
            if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and (inst[0]==1050 or inst[0]==1051))):
                recdblist.addCommonlogEX("Error", "drop_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
        self.close_db(db)
    def new_in_status(self):
        db = self.connect_db()
        try:
            db[1].execute("\
            CREATE TABLE in_status \
            (\
            ts2avi TINYINT DEFAULT 0,\
            terec TINYINT DEFAULT 0,\
            bscsrec TINYINT DEFAULT 0,\
            b252ts TINYINT DEFAULT 0,\
            installed TINYINT DEFAULT 0,\
            version TINYINT\
            )")
            db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0,0)")
        except Exception, inst:
            if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
                recdblist.addCommonlogEX("Error", "new_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
        self.close_db(db)
    def select_all_in_status(self):
        db = self.connect_db()
        dbexe = db[1].execute("\
        SELECT ts2avi,terec,bscsrec,b252ts \
        FROM in_status \
        ")
        ret = []
        dls = []
        if dbexe > 0:
            dls = db[1].fetchall()
        self.close_db(db)
        for dl in dls:
            r = list(dl)
            r[0]=str(r[0])
            r[1]=str(r[1])
            r[2]=str(r[2])
            r[3]=str(r[3])
            ret.append(r)
        return ret
    def select_version_in_status(self):
        db = self.connect_db()
        version=0
        try:
            dbexe = db[1].execute("\
            SELECT version \
            FROM in_status \
            ")
            if dbexe > 0:
                dls = db[1].fetchall()
            self.close_db(db)
            for dl in dls:
                r = list(dl)
                version=int(str(r[0]))
        except Exception, inst:
            if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
                recdblist.addCommonlogEX("Error", "select_version_in_status (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
        return version
    def change_version_in_status(self,version):
        db = self.connect_db()
        db[1].execute("\
        UPDATE in_status SET version=%s",str(version))
        self.close_db(db)
    def change_ts2avi_in_status(self,i):
        """
        statuをiだけ増減する
        iはint
        """
        db = self.connect_db()
        db[1].execute("\
        UPDATE in_status SET ts2avi=ts2avi+%s",i)
        self.close_db(db)
    def change_terec_in_status(self,i):
        """
        statuをiだけ増減する
        iはint
        """
        db = self.connect_db()
        db[1].execute("\
        UPDATE in_status SET terec=terec+%s",i)
        self.close_db(db)
    def change_bscsrec_in_status(self,i):
        """
        statuをiだけ増減する
        iはint
        """
        db = self.connect_db()
        db[1].execute("\
        UPDATE in_status SET bscsrec=bscsrec+%s",i)
        self.close_db(db)
    def change_b252ts_in_status(self,i):
        """
        statuをiだけ増減する
        iはint
        """
        db = self.connect_db()
        db[1].execute("\
        UPDATE in_status SET b252ts=b252ts+%s",i)
        self.close_db(db)
    def select_installed_in_status(self):
        db = self.connect_db()
        dbexe = db[1].execute("\
        SELECT ts2avi,terec,bscsrec,b252ts,installed \
        FROM in_status \
        ")
        ret = 0
        dls = []
        if dbexe > 0:
            dls = db[1].fetchall()
        self.close_db(db)
        for dl in dls:
            r = list(dl)
            ret=r[4]
        return ret
    def change_chscaned_in_status(self):
        """
        installedを設定する
        """
        db = self.connect_db()
        db[1].execute("\
        UPDATE in_status SET installed=2")
        self.close_db(db)
    def change_installed_in_status(self,num=1):
        """
        installedを設定する
        """
        db = self.connect_db()
        db[1].execute("\
        UPDATE in_status SET installed=%s",(num,))
        self.close_db(db)
    def new_epg_timeline(self, bctype):
        db = self.connect_db()
        try:
            db[1].execute("\
            DELETE FROM epg_timeline \
            WHERE bctype = %s", \
                          (bctype,))
        except Exception, inst:
            if not ((type(inst)==MySQLdb.ProgrammingError and (inst[0]==1007 or inst[0]==1146))or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
                recdblist.addCommonlogEX("Error", "new_epg_timeline delete (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
        try:
            db[1].execute("\
            CREATE TABLE epg_timeline \
            (\
            bctype VARCHAR(20),\
            channel VARCHAR(100) NOT NULL,\
            start VARCHAR(30),\
            stop  VARCHAR(30),\
            title VARCHAR(100),\
            exp VARCHAR(200),\
            longexp TEXT,\
            category VARCHAR(100),\
            UNIQUE unitv(bctype,channel,start,stop,title)\
            )")
        except Exception, inst:
            if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
                recdblist.addCommonlogEX("Error", "new_epg_timeline (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
        #db.commit()
        self.close_db(db)
    def add_epg_timeline(self, bctype, channel, start, stop, title, desc,longdesc, category):
        db = self.connect_db()
        db[1].execute('\
        INSERT IGNORE INTO epg_timeline \
        VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', \
                      (bctype, channel, start, stop, title, desc,longdesc,category))
        #db.commit()
        self.close_db(db)
    def add_multi_epg_timeline(self, tvlists):
        """
        tvlists is (bctype,channel,start,stop,title,desc,longdesc,category) lists.
        """
        db = self.connect_db()
        db[1].executemany('\
        INSERT IGNORE INTO epg_timeline \
        (bctype,channel,start,stop,title,exp,longexp,category) \
        values(%s,%s,%s,%s,%s,%s,%s,%s)', \
                          tvlists)
        self.close_db(db)
    def select_by_time_ngram_epg_timeline(self, btime, etime, chtxt):
        db = self.connect_db()
        dbexe = "\
        SELECT \
        channel,title,start,stop,exp,longexp,category \
        FROM epg_timeline \
        WHERE start >= %s \
        AND \
        start <= %s \
        AND \
        channel LIKE %s"
        dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
        retall = []
        if dbcmd > 0:
            retall = db[1].fetchall()
        self.close_db(db)
        return retall
    def select_by_time_keyword_auto_suggest_epg_timeline(self,keyword,btime,etime):
        db = self.connect_db()
        dbexe = "\
        SELECT \
        epg_ch.chtxt,title,start,stop,exp,longexp,category \
        FROM epg_timeline \
        INNER JOIN epg_ch \
        WHERE epg_ch.chtxt=epg_timeline.channel \
        AND \
        epg_ch.visible=1 \
        AND \
        start >= %s \
        AND \
        stop <= %s \
        AND \
        ( \
        ( title LIKE \'%%"+keyword+"%%\' ) \
        OR \
        ( exp LIKE \'%%"+keyword+"%%\' ) \
        OR \
        ( longexp LIKE \'%%"+keyword+"%%\' ) \
        )"
        dbcmd = db[1].execute(dbexe,(btime, etime))
        retall = []
        if dbcmd > 0:
            retall = db[1].fetchall()
        self.close_db(db)
        return retall
    def new_in_auto_bayes_key(self):
        db = self.connect_db()
        try:
            db[1].execute('CREATE TABLE in_auto_bayes_key \
            (\
            keychar VARCHAR(10),\
            chtxt VARCHAR(20),\
            ratio_rec DECIMAL(32,14),\
            ratio_all DECIMAL(32,14),\
            UNIQUE unibayeskey(keychar,chtxt)\
            )')
            db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
        except Exception, inst:
            if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
                recdblist.addCommonlogEX("Error", "new_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
        self.close_db(db)

    def add_in_auto_bayes_key(self,key,chtxt,ratio_rec,ratio_all):
        db = self.connect_db()
        ratio_rec=str(ratio_rec)
        ratio_all=str(ratio_all)
        db[1].execute('\
        INSERT IGNORE INTO in_auto_bayes_key \
        (keychar,chtxt,ratio_rec,ratio_all) \
        values (%s,%s,%s,%s)',\
        (key,chtxt,ratio_rec,ratio_all))
        self.close_db(db)
    def add_num_in_auto_bayes_key(self,chtxt,add_rec_num,add_all_num):
        db = self.connect_db()
        add_rec_num=str(add_rec_num)
        add_all_num=str(add_all_num)
        db[1].execute("\
        UPDATE in_auto_bayes_key SET ratio_rec=CONVERT(ratio_rec+%s,DECIMAL(32,14)),ratio_all=CONVERT(ratio_all+%s,DECIMAL(32,14)) WHERE keychar=\"NUM\" AND chtxt=%s",\
        (add_rec_num,add_all_num,chtxt))
        self.close_db(db)
    def change_in_auto_bayes_key(self,key,chtxt,new_ratio_rec,new_ratio_all):
        """
        """
        db = self.connect_db()
        db[1].execute("\
        UPDATE in_auto_bayes_key SET ratio_rec=%s,ratio_all=%s WHERE keychar=%s AND chtxt=%s",(str(new_ratio_rec),str(new_ratio_all),key,chtxt)\
        )
        self.close_db(db)
    def change_ratio_all_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
        beforenum=str(beforenum)
        newnum=str(newnum)
        db = self.connect_db()
        db[1].execute("\
        UPDATE in_auto_bayes_key SET ratio_all=CONVERT(ratio_all*%s/(%s+%s),DECIMAL(32,14)) WHERE chtxt=%s AND NOT (keychar=\"NUM\")",(beforenum,newnum,beforenum,chtxt)\
        )
        self.close_db(db)
    def change_ratio_all_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
        db = self.connect_db()
        beforenumf=beforenum
        beforenum=str(beforenum)
        db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
        VALUES (%s,%s,%s,%s)\
        ON DUPLICATE KEY UPDATE \
        ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,"0",str(Decimal(addnum)/beforenumf),beforenum,chtxt,key))
        self.close_db(db)
    def change_multi_ratio_all_in_auto_bayes_key(self,chtxt,beforenum,list):
        """
        list={key:addnum}のリスト
        """
        beforenumf=beforenum
        beforenum=str(beforenum)
        db = self.connect_db()
        for i,j in list.items():
            retl=(i,chtxt,"0",str(Decimal(j)/beforenumf),beforenum,str(j),beforenum)
            try:
                db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
                VALUES (%s,%s,%s,%s)\
                ON DUPLICATE KEY UPDATE \
                ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
            except Exception, inst:
                if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
                    recdblist.addCommonlogEX("Error", "change_multi_ratio_all_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
        self.close_db(db)
    def change_ratio_rec_reduce_in_auto_bayes_key(self,chtxt,beforenum,newnum):
        beforenum=str(beforenum)
        newnum=str(newnum)
        db = self.connect_db()
        db[1].execute("\
        UPDATE in_auto_bayes_key SET ratio_rec=CONVERT(ratio_rec*%s/(%s+%s),DECIMAL(32,14)) WHERE chtxt=%s AND NOT (keychar=\"NUM\")",(beforenum,newnum,beforenum,chtxt)\
        )
        self.close_db(db)
    def change_ratio_rec_in_auto_bayes_key(self,key,chtxt,beforenum,addnum):
        db = self.connect_db()
        beforenumf=beforenum
        beforenum=str(beforenum)
        db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
        VALUES (%s,%s,%s,%s)\
        ON DUPLICATE KEY UPDATE \
        ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",(key,chtxt,str(Decimal(addnum)/beforenumf),"0",beforenum,chtxt,key))
        self.close_db(db)
    def change_multi_ratio_rec_in_auto_bayes_key(self,chtxt,beforenum,list):#self,key,chtxt,beforenum,addnum):
        beforenumf=beforenum
        beforenum=str(beforenum)
        db = self.connect_db()
        for i,j in list.items():
            retl=(i,chtxt,str(Decimal(j)/beforenumf),"0",beforenum,str(j),beforenum)
            try:
                db[1].execute("INSERT INTO in_auto_bayes_key (keychar,chtxt,ratio_rec,ratio_all) \
                VALUES (%s,%s,%s,%s)\
                ON DUPLICATE KEY UPDATE \
                ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
            except Exception, inst:
                if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
                    recdblist.addCommonlogEX("Error", "change_multi_ratio_rec_in_auto_bayes_key (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
        self.close_db(db)
    def select_by_key_in_auto_bayes_key(self,key,chtxt):
        db = self.connect_db()
        dbexe = db[1].execute("\
        SELECT keychar,chtxt,ratio_rec,ratio_all \
        FROM in_auto_bayes_key \
        WHERE keychar = %s AND chtxt = %s", \
                              (key,chtxt))
        dls = []
        if dbexe > 0:
            dls = db[1].fetchall()
        self.close_db(db)
        if len(dls)>0:
            return dls[0]
        else:
            return dls
    def new_auto_timeline_keyword(self):
        db = self.connect_db()
        try:
            db[1].execute('\
            CREATE TABLE auto_timeline_keyword \
            (\
            id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
            chtxt VARCHAR(40),\
            title VARCHAR(100),\
            btime DATETIME,\
            etime DATETIME,\
            UNIQUE uni (chtxt,title,btime,etime)\
            )')
        except Exception, inst:
            if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
                recdblist.addCommonlogEX("Error", "new_auto_timeline_keyword (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
    def add_auto_timeline_keyword(self,chtxt="", title="", btime="", etime=""):
        db = self.connect_db()
        db[1].execute('\
        INSERT IGNORE into auto_timeline_keyword \
        (chtxt,title,btime,etime) \
        values (%s,%s,%s,%s)', \
                       (chtxt, title, btime, etime))
        ##db.commit()
        self.close_db(db)
    def delete_old_auto_timeline_keyword(self, dhour):
        db = self.connect_db()
        db[1].execute("\
        DELETE FROM auto_timeline_keyword \
        WHERE \
        btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
        self.close_db(db)
    def new_auto_timeline_bayes(self):
        db = self.connect_db()
        try:
            db[1].execute('\
            CREATE TABLE auto_timeline_bayes \
            (\
            id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
            chtxt VARCHAR(40),\
            title VARCHAR(100),\
            btime DATETIME,\
            etime DATETIME,\
            point INT,\
            UNIQUE uni (chtxt,title,btime,etime)\
            )')
        except Exception, inst:
            if not ((type(inst)==MySQLdb.ProgrammingError and inst[0]==1007)or(type(inst)==MySQLdb.OperationalError and inst[0]==1050)):
                recdblist.addCommonlogEX("Error", "new_auto_timeline_bayes (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=200)
    def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime="",point=""):
        db = self.connect_db()
        db[1].execute('\
        INSERT IGNORE into auto_timeline_bayes \
        (chtxt,title,btime,etime,point) \
        values (%s,%s,%s,%s,%s)', \
                      (chtxt, title, btime, etime,point))
        self.close_db(db)
    def delete_old_auto_timeline_bayes(self, dhour):
        db = self.connect_db()
        db[1].execute("\
        DELETE FROM auto_timeline_bayes \
        WHERE \
        btime < DATE_SUB(now(),INTERVAL " + dhour + " HOUR )")
        self.close_db(db)
    def update_db_to93(self):
        db = self.connect_db()
        self.drop_in_settings()
        self.new_in_settings()
        db[1].execute("\
        ALTER TABLE timeline ADD epgtitle VARCHAR(100),\
        ADD epgbtime DATETIME,\
        ADD epgetime DATETIME,\
        ADD epgduplicate TINYINT DEFAULT 0,\
        ADD epgchange TINYINT DEFAULT 0")
        db[1].execute("\
        ALTER TABLE in_status ADD version TINYINT")
        self.close_db(db)
        self.change_version_in_status("93")
    def update_db_93to94(self):
        db = self.connect_db()
        self.drop_in_settings()
        self.new_in_settings()
        db[1].execute("\
        ALTER TABLE timeline ADD counter TINYINT DEFAULT -1")
        self.close_db(db)
        self.change_version_in_status("94")
    def update_db_94to95(self):
        db = self.connect_db()
        self.drop_in_settings()
        self.new_in_settings()
        db[1].execute("\
        ALTER TABLE timeline ADD epgexp VARCHAR(200)")
        self.close_db(db)
        self.change_version_in_status("95")
    def update_db_95to96(self):
        db = self.connect_db()
        self.drop_in_settings()
        self.new_in_settings()
        self.close_db(db)
        self.change_version_in_status("96")
    def update_db_96to98(self):
        db = self.connect_db()
        self.drop_in_settings()
        self.new_in_settings()
        self.close_db(db)
        self.change_version_in_status("98")
    def update_db_98to100(self):
        ###ここで前のepg_chをバックアップしてchtxtの変換をする必要がある。
        self.drop_in_settings()
        self.new_in_settings()
        db = self.connect_db()
        db[1].execute("\
        UPDATE timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt SET timeline.chtxt=CONCAT(CONCAT(epg_ch.ch,'_'),epg_ch.csch) WHERE NOT (substring(epg_ch.bctype,1,2) = 'bs' OR substring(epg_ch.bctype,1,2) = 'cs')")
        db[1].execute("\
        UPDATE timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt SET timeline.chtxt=CONCAT('BS_',epg_ch.ch) WHERE substring(epg_ch.bctype,1,2) = 'bs'")
        db[1].execute("\
        UPDATE timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt SET timeline.chtxt=CONCAT('CS_',epg_ch.csch) WHERE substring(epg_ch.bctype,1,2) = 'cs'")
        try:
            db[1].execute("\
            ALTER TABLE epg_ch DROP ontv")
        except:
            ""
        db[1].execute("\
        ALTER TABLE epg_ch ADD logo0 BLOB,\
        ADD logo1 BLOB,\
        ADD logo2 BLOB,\
        ADD logo3 BLOB,\
        ADD logo4 BLOB,\
        ADD logo5 BLOB\
        ")
        db[1].execute("\
        ALTER TABLE in_auto_jbk_key ADD auto TINYINT DEFAULT 0")
        db[1].execute("\
        ALTER TABLE in_auto_jbk_key ADD opt VARCHAR(20) DEFAULT \"\"")
        self.close_db(db)
        self.change_installed_in_status(1)#チャンネルスキャンをさせる
        self.change_version_in_status("100")
    def update_db_100to101(self):
        self.drop_in_settings()
        self.new_in_settings()
        self.new_epg_ch()
        db = self.connect_db()
        try:
            db[1].execute("\
            ALTER TABLE timeline ADD epgcategory VARCHAR(100)\
            ")
        except:
            ""
        self.close_db(db)
        self.change_installed_in_status(1)
        self.change_version_in_status("101")
        #self.change_installed_in_status()#チャンネルスキャンをさせる
    def update_db_101to102(sekf):
        self.drop_in_settings()
        self.new_in_settings()
        self.new_epg_ch()
        db = self.connect_db()
        try:
            db[1].execute("\
            ALTER TABLE epg_ch ADD tsid VARCHAR(20)\
            ")
        except:
            ""
        self.close_db(db)
        self.change_installed_in_status(1)
        self.change_version_in_status("102")
        #self.change_installed_in_status()#チャンネルスキャンをさせる

