X-Git-Url: http://git.osdn.net/view?p=rec10%2Frec10-git.git;a=blobdiff_plain;f=rec10%2Ftrunk%2Fsrc%2FdbMySQL.py;h=cce2e869784ffa9793bb351eea8c1cf7281ddfa8;hp=6f74f6fd7e4c8eacf5cfff07b6b36e7a07c35828;hb=12414a3ba81d93316e351ab51aa49bb71cf42f60;hpb=542f09519a5ef6cac4dc75c4c8fd6653d46e69a6

diff --git a/rec10/trunk/src/dbMySQL.py b/rec10/trunk/src/dbMySQL.py
index 6f74f6f..cce2e86 100644
--- a/rec10/trunk/src/dbMySQL.py
+++ b/rec10/trunk/src/dbMySQL.py
@@ -1,7 +1,7 @@
 #!/usr/bin/python
 # coding: UTF-8
 # Rec10 TS Recording Tools
-# Copyright (C) 2009-2010 Yukikaze
+# Copyright (C) 2009-2012 Yukikaze
 import MySQLdb
 import recdblist
 import warnings
@@ -24,7 +24,7 @@ class DB_MySQL:
         try:
             con = MySQLdb.connect(user=user, passwd=passwd)
             cur = con.cursor()
-            cur.execute('CREATE DATABASE ' + dbname + " DEFAULT CHARACTER SET utf8")
+            cur.execute('CREATE DATABASE ' + dbname + " DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;")
             cur.close()
             con.close()
         except Exception, inst:
@@ -50,6 +50,7 @@ class DB_MySQL:
             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)\
             )')
@@ -71,6 +72,10 @@ class DB_MySQL:
         """
         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):
@@ -92,27 +97,42 @@ class DB_MySQL:
             chtxt VARCHAR(20) PRIMARY KEY,\
             ch VARCHAR(20),\
             csch VARCHAR(20),\
+            tsid VARCHAR(20),\
             chname VARCHAR(100),\
             updatetime DATETIME,\
             status TINYINT,\
-            isshow TINYINT DEFAULT 1\
+            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, updatetime):
+    def add_epg_ch(self, bctype, chtxt, ch, csch,tsid,updatetime,logoupdate,scanupdate):
         db = self.connect_db()
         db[1].execute('\
-        INSERT INTO epg_ch \
-        VALUES (%s,%s,%s,%s,"",%s,%s,%s)', \
-                      (bctype, chtxt, ch, csch, updatetime, "1","1"))
+        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,updatetime \
+        SELECT bctype,chtxt,ch,csch,tsid,updatetime,chname,status,visible,logoupdate,scanupdate \
         FROM epg_ch \
         WHERE chtxt LIKE %s", \
                               (chtxt,))
@@ -123,13 +143,18 @@ class DB_MySQL:
         self.close_db(db)
         for dl in dls:
             r = list(dl)
-            r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
+            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,updatetime,status \
+        SELECT bctype,chtxt,ch,csch,tsid,updatetime,status,chname,status,visible,logoupdate,scanupdate \
         FROM epg_ch \
         WHERE bctype = %s", \
                               (bctype,))
@@ -141,15 +166,22 @@ class DB_MySQL:
         for dl in dls:
             #recdblist.printutf8(dl)
             r = list(dl)
-            r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
-            r[5] = str(r[5])
+            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,updatetime \
+        bctype,chtxt,ch,csch,tsid,updatetime,chname,status,visible,logoupdate,scanupdate \
         FROM epg_ch \
         WHERE ch = %s", \
                               (ch,))
@@ -160,13 +192,35 @@ class DB_MySQL:
         self.close_db(db)
         for dl in dls:
             r = list(dl)
-            r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
+            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,updatetime \
+        SELECT bctype,chtxt,ch,csch,tsid,updatetime,chname,status,visible,logoupdate,scanupdate \
         FROM epg_ch \
         ")
         ret = []
@@ -176,13 +230,20 @@ class DB_MySQL:
         self.close_db(db)
         for dl in dls:
             r = list(dl)
-            r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
+            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_isshow_epg_ch(self,chtxt,isshow):
+    def change_visible_epg_ch(self,chtxt,visible):
         db = self.connect_db()
         db[1].execute("\
-        UPDATE epg_ch SET isshow=%s WHERE chtxt=%s",(isshow,chtxt))
+        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()
@@ -213,6 +274,24 @@ class DB_MySQL:
             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("\
@@ -222,6 +301,7 @@ class DB_MySQL:
         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("\
@@ -231,6 +311,14 @@ class DB_MySQL:
         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("\
@@ -242,14 +330,15 @@ class DB_MySQL:
                       (status, bctype)\
                       )
         self.close_db(db)
-    def update_chname_by_chtxt_epg_ch(self,chtxt,chname):
+    def update_logostatus_by_bctype_epg_ch(self,bctype,logostatus):
         db = self.connect_db()
         db[1].execute("\
         UPDATE epg_ch \
         SET \
-        chname=%s \
-        WHERE chtxt = %s", \
-                      (chname, chtxt)\
+        logostatus=%s , \
+        logoupdate=now() \
+        WHERE bctype = %s", \
+                      (logostatus, bctype)\
                       )
         self.close_db(db)
     def add_auto_proc_tmp(self,type,title,chtxt):
@@ -431,13 +520,13 @@ class DB_MySQL:
                       (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):
+    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 \
+        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,type, chtxt, title, btime))
+                      (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):
@@ -517,8 +606,8 @@ class DB_MySQL:
         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))
+        WHERE btime >= %s AND \
+        etime <= %s",(btime,etime))
         dbl = db[1].fetchall()
         self.close_db(db)
         #recdblist.printutf8(dbl)
@@ -721,7 +810,7 @@ class DB_MySQL:
         dbexe = db[1].execute("\
         SELECT keyword,auto,opt \
         FROM in_auto_jbk_key \
-        ")
+vim         ")
         ret = []
         if dbexe > 0:
             ret = db[1].fetchall()
@@ -846,21 +935,21 @@ class DB_MySQL:
             r = list(dl)
             ret=r[4]
         return ret
-    def change_installed_in_status(self):
+    def change_chscaned_in_status(self):
         """
         installedを設定する
         """
         db = self.connect_db()
         db[1].execute("\
-        UPDATE in_status SET installed=1")
+        UPDATE in_status SET installed=2")
         self.close_db(db)
-    def change_chscaned_in_status(self):
+    def change_installed_in_status(self,num=1):
         """
         installedを設定する
         """
         db = self.connect_db()
         db[1].execute("\
-        UPDATE in_status SET installed=2")
+        UPDATE in_status SET installed=%s",(num,))
         self.close_db(db)
     def new_epg_timeline(self, bctype):
         db = self.connect_db()
@@ -936,6 +1025,8 @@ class DB_MySQL:
         INNER JOIN epg_ch \
         WHERE epg_ch.chtxt=epg_timeline.channel \
         AND \
+        epg_ch.visible=1 \
+        AND \
         start >= %s \
         AND \
         stop <= %s \
@@ -1186,21 +1277,63 @@ class DB_MySQL:
         self.change_version_in_status("98")
     def update_db_98to100(self):
         ###ここで前のepg_chをバックアップしてchtxtの変換をする必要がある。
-        db = self.connect_db()
         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 DROP ontv")
+        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()#チャンネルスキャンをさせる
-        self.change_version_in_status("100")
\ No newline at end of file
+        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()#チャンネルスキャンをさせる
+