#!/usr/bin/python
# coding: UTF-8
# Rec10 TS Recording Tools
# Copyright (C) 2009 Yukikaze
import MySQLdb
from decimal import Decimal
class DB_MySQL:
    dbname = ""
    dbhost = ""
    dbusr = ""
    dbpasswd = ""
    dbport = 0
    def __init__(self, dbname, user, passwd, host="localhost", port=3306):
        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")
            cur.close()
            con.close()
        except:
            ""
        db = self.connect_db()
        try:
            db[1].execute('\
            CREATE TABLE timeline \
            (\
            id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
            type VARCHAR(20),\
            chtxt VARCHAR(20),\
            title VARCHAR(100),\
            btime DATETIME,\
            etime DATETIME,\
            deltatime VARCHAR(5),\
            deltaday VARCHAR(5),\
            opt VARCHAR(20),\
            UNIQUE uni (type,chtxt,title,btime,deltaday)\
            )')
        except:
            ""
        self.close_db(db)
        self.new_epg_timeline("")
        self.new_epg_ch()
        self.new_in_timeline_log()
        self.new_in_auto_bayes_key()
        self.new_in_auto_jbk_key()
        self.new_auto_proc()
        self.new_in_status()
        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()
        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('\
            CREATE TABLE epg_ch \
            (\
            bctype VARCHAR(15),\
            ontv VARCHAR(30) PRIMARY KEY,\
            chtxt VARCHAR(15),\
            ch VARCHAR(20),\
            csch VARCHAR(20),\
            chname VARCHAR(100),\
            updatetime DATETIME,\
            status TINYINT\
            )')
        except:
            ""
        self.close_db(db)
    def add_epg_ch(self, bctype, ontv, chtxt, ch, csch, updatetime):
        db = self.connect_db()
        db[1].execute('\
        INSERT INTO epg_ch \
        VALUES (%s,%s,%s,%s,%s,"",%s,%s)', \
                      (bctype, ontv, chtxt, ch, csch, updatetime, "1"))
        self.close_db(db)
    def select_by_ontv_epg_ch(self, ontv):
        db = self.connect_db()
        dbexe = db[1].execute("\
        SELECT bctype,ontv,chtxt,ch,csch,updatetime \
        FROM epg_ch \
        WHERE ontv = %s", \
                              (ontv,))
        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")
            ret.append(r)
        return ret
    def select_by_chtxt_epg_ch(self, chtxt):
        db = self.connect_db()
        dbexe = db[1].execute("\
        SELECT bctype,ontv,chtxt,ch,csch,updatetime \
        FROM epg_ch \
        WHERE chtxt = %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")
            ret.append(r)
        return ret
    def select_by_bctype_epg_ch(self, bctype):
        db = self.connect_db()
        dbexe = db[1].execute("\
        SELECT bctype,ontv,chtxt,ch,csch,updatetime,status \
        FROM epg_ch \
        WHERE bctype = %s", \
                              (bctype,))
        ret = []
        dls = []
        if dbexe > 0:
            dls = db[1].fetchall()
        self.close_db(db)
        for dl in dls:
            #print dl
            r = list(dl)
            r[5] = r[5].strftime("%Y-%m-%d %H:%M:%S")
            r[6] = str(r[6])
            ret.append(r)
        return ret
    def select_by_ch_epg_ch(self, ch):
        db = self.connect_db()
        dbexe = db[1].execute("\
        SELECT \
        bctype,ontv,chtxt,ch,csch,updatetime \
        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")
            ret.append(r)
        return ret
    def select_all_epg_ch(self):
        db = self.connect_db()
        dbexe = db[1].execute("\
        SELECT bctype,ontv,chtxt,ch,csch,updatetime \
        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")
            ret.append(r)
        return ret
    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 = []
        #print 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_status_by_bctype_epg_ch(self, bctype, status):
        db = self.connect_db()
        db[1].execute("\
        UPDATE epg_ch \
        SET \
        status=%s \
        WHERE bctype = %s", \
                      (status, bctype)\
                      )
        self.close_db(db)
    def update_chname_by_ontv_epg_ch(self,ontv,chname):
        db = self.connect_db()
        db[1].execute("\
        UPDATE epg_ch \
        SET \
        chname=%s \
        WHERE ontv = %s", \
                      (chname, ontv)\
                      )
        self.close_db(db)
    def add_auto_proc(self,type,title):
        db = self.connect_db()
        db[1].execute('\
        INSERT IGNORE into auto_proc \
        (type,title) \
        values (%s,%s)', \
                      ( type, title))
        ##db.commit()
        self.close_db(db)
    def new_auto_proc(self):
        db = self.connect_db()
        try:
            db[1].execute('drop table auto_proc')
        except:
            ""
        try:
            db[1].execute('\
            CREATE TABLE auto_proc \
            (\
            type VARCHAR(20),\
            title VARCHAR(100) PRIMARY KEY,\
            UNIQUE unibayeskey(title)\
            )')
        except:
            ""
        self.close_db(db)
    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:
            ""
        self.close_db(db)
    def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
        db = self.connect_db()
        db[1].execute('\
        INSERT IGNORE into timeline \
        (type,chtxt,title,btime,etime,deltatime,deltaday,opt) \
        values (%s,%s,%s,%s,%s,%s,%s,%s)', \
                      (type, chtxt, title, btime, etime, deltatime, deltaday, opt))
        ##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 \
        FROM timeline")
        dbl = db[1].fetchall()
        self.close_db(db)
        if dbr > 0:
            for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
                ret = {}
                ret['type'] = typet
                ret['chtxt'] = chtxt
                ret['title'] = title.encode('utf-8')
                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
                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_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 \
        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)
        #print dbl
        if dbr > 0:
            for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dbl:
                ret = {}
                ret['type'] = typet
                ret['chtxt'] = chtxt
                ret['title'] = title.encode('utf-8')
                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
                if deltatime == None or deltatime == "":
                    deltatime = "3"
                if deltaday == None or deltaday == "":
                    deltaday = "7"
                if typet == 'key':
                    ret['deltatime'] = deltatime
                elif typet == 'keyevery':
                    ret['deltatime'] = deltatime
                    ret['deltaday'] = deltaday
                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),\
            UNIQUE unijbk (keyword)\
            )")
        except:
            ""
        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 \
        FROM in_auto_jbk_key \
        ")
        ret = []
        if dbexe > 0:
            ret = db[1].fetchall()
        self.close_db(db)
        return ret
    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\
            )")
            db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0)")
        except:
            ""
        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 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 new_epg_timeline(self, bctype):
        db = self.connect_db()
        try:
            db[1].execute("\
            DELETE FROM epg_timeline \
            WHERE bctype = %s", \
                          (bctype,))
        except:
            ""
        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:
            ""
        #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 \
        chdata.chtxt,title,start,stop,exp,longexp,category \
        FROM epg_timeline \
        INNER JOIN chdata \
        WHERE chdata.ontv=epg_timeline.channel \
        AND \
        start >= %s \
        AND \
        start <= %s \
        AND \
        chdata.chtxt=%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_auto_suggest_epg_timeline(self,keyword,btime,etime):
        db = self.connect_db()
        dbexe = "\
        SELECT \
        chdata.chtxt,title,start,stop,exp,longexp,category \
        FROM epg_timeline \
        INNER JOIN chdata \
        WHERE chdata.ontv=epg_timeline.channel \
        AND \
        start >= %s \
        AND \
        start <= %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_epg_ch(self, bctype):
    #    db = self.connect_db()
    #    try:
    #        db[1].execute("DELETE FROM ch WHERE bctype = %s", (bctype,))
    #    except:
    #        ""
    #    try:
    #        db[1].execute('\
    #        CREATE TABLE epg_ch \
    #        (\
    #        bctype VARCHAR(20),\
    #        channel VARCHAR(20) NOT NULL,\
    #        display VARCHAR(100),\
    #        UNIQUE unich(bctype,channel)\
    #        )')
    #    except:
    #        ""
        #db.commit()
    #    self.close_db(db)
    #def add_epg_ch(self, bctype, channel, display):
    #    db = self.connect_db()
    ##    db[1].execute('INSERT IGNORE INTO epg_ch VALUES (%s,%s,%s)', (bctype, channel, display))
        #db.commit()
    #    self.close_db(db)
    #def add_multi_ch(self, chlists):
    #    """
    #    chlists is (bctype,channel,display) lists
    ##    """
    #    db = self.connect_db()
    #    db[1].executemany('INSERT IGNORE INTO ch VALUES (%s,%s,%s)', chlists)
    #    self.close_db(db)
    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:
            ""
        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)
            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)
        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)
            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)
        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(20),\
            title VARCHAR(100),\
            btime DATETIME,\
            etime DATETIME,\
            UNIQUE uni (chtxt,title,btime,etime)\
            )')
        except:
            ""
    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(20),\
            title VARCHAR(100),\
            btime DATETIME,\
            etime DATETIME,\
            UNIQUE uni (chtxt,title,btime,etime)\
            )')
        except:
            ""
    def add_auto_timeline_bayes(self,chtxt="", title="", btime="", etime=""):
        db = self.connect_db()
        db[1].execute('\
        INSERT IGNORE into auto_timeline_bayes \
        (chtxt,title,btime,etime) \
        values (%s,%s,%s,%s)', \
                      (chtxt, title, btime, etime))
        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)
