X-Git-Url: http://git.osdn.net/view?a=blobdiff_plain;f=rec10%2Ftrunk%2Fsrc%2FdbMySQL.py;h=ff539f9d56df73953227252002363178e44f26ad;hb=9f11176cee340c95cae2b7dbf6091ef9b390dcb2;hp=1dab3ea3c80a1f00b37794471d5ed9f01f14f121;hpb=da6b8533a60a5c1c4530523cdc23f48f4a351767;p=rec10%2Frec10-git.git

diff --git a/rec10/trunk/src/dbMySQL.py b/rec10/trunk/src/dbMySQL.py
index 1dab3ea..ff539f9 100644
--- a/rec10/trunk/src/dbMySQL.py
+++ b/rec10/trunk/src/dbMySQL.py
@@ -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:
@@ -72,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):
@@ -97,23 +101,27 @@ class DB_MySQL:
             updatetime DATETIME,\
             status TINYINT,\
             visible TINYINT DEFAULT 1,\
+            logo0 BLOB,\
             logo1 BLOB,\
             logo2 BLOB,\
             logo3 BLOB,\
             logo4 BLOB,\
-            logo5 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, updatetime,logoupdate,scanupdate):
         db = self.connect_db()
         db[1].execute('\
-        INSERT INTO epg_ch (bctype,chtxt,ch,csch,chname,updatetime,status,visible)\
-        VALUES (%s,%s,%s,%s,"",%s,%s,%s)', \
-                      (bctype, chtxt, ch, csch, updatetime, "1","1"))
+        INSERT INTO epg_ch (bctype,chtxt,ch,csch,chname,updatetime,status,visible,logoupdate,scanupdate)\
+        VALUES (%s,%s,%s,%s,"",%s,%s,%s,%s,%s)', \
+                      (bctype, chtxt, ch, csch, updatetime, "1","1",logoupdate,scanupdate))
         self.close_db(db)
     def delete_all_epg_ch(self):
         db = self.connect_db()
@@ -123,7 +131,7 @@ class DB_MySQL:
     def select_by_chtxt_epg_ch(self, chtxt):
         db = self.connect_db()
         dbexe = db[1].execute("\
-        SELECT bctype,chtxt,ch,csch,updatetime,chname,status,visible \
+        SELECT bctype,chtxt,ch,csch,updatetime,chname,status,visible,logoupdate,scanupdate \
         FROM epg_ch \
         WHERE chtxt LIKE %s", \
                               (chtxt,))
@@ -135,12 +143,14 @@ class DB_MySQL:
         for dl in dls:
             r = list(dl)
             r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
+            r[8] = r[8].strftime("%Y-%m-%d %H:%M:%S")
+            r[9] = r[9].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,chname,status,visible \
+        SELECT bctype,chtxt,ch,csch,updatetime,status,chname,status,visible,logoupdate,scanupdate \
         FROM epg_ch \
         WHERE bctype = %s", \
                               (bctype,))
@@ -153,6 +163,8 @@ class DB_MySQL:
             #recdblist.printutf8(dl)
             r = list(dl)
             r[4] = r[4].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")
             r[5] = str(r[5])
             ret.append(r)
         return ret
@@ -160,7 +172,7 @@ class DB_MySQL:
         db = self.connect_db()
         dbexe = db[1].execute("\
         SELECT \
-        bctype,chtxt,ch,csch,updatetime,chname,status,visible \
+        bctype,chtxt,ch,csch,updatetime,chname,status,visible,logoupdate,scanupdate \
         FROM epg_ch \
         WHERE ch = %s", \
                               (ch,))
@@ -172,13 +184,15 @@ class DB_MySQL:
         for dl in dls:
             r = list(dl)
             r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
+            r[8] = r[8].strftime("%Y-%m-%d %H:%M:%S")
+            r[9] = r[9].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,updatetime,chname,status,visible \
+        bctype,chtxt,ch,csch,updatetime,chname,status,visible,logoupdate,scanupdate \
         FROM epg_ch \
         WHERE csch = %s", \
                               (csch,))
@@ -190,12 +204,14 @@ class DB_MySQL:
         for dl in dls:
             r = list(dl)
             r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
+            r[8] = r[8].strftime("%Y-%m-%d %H:%M:%S")
+            r[9] = r[9].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,chname,status,visible \
+        SELECT bctype,chtxt,ch,csch,updatetime,chname,status,visible,logoupdate,scanupdate \
         FROM epg_ch \
         ")
         ret = []
@@ -206,6 +222,8 @@ class DB_MySQL:
         for dl in dls:
             r = list(dl)
             r[4] = r[4].strftime("%Y-%m-%d %H:%M:%S")
+            r[8] = r[8].strftime("%Y-%m-%d %H:%M:%S")
+            r[9] = r[9].strftime("%Y-%m-%d %H:%M:%S")
             ret.append(r)
         return ret
     def change_visible_epg_ch(self,chtxt,visible):
@@ -247,6 +265,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("\
@@ -256,6 +292,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("\
@@ -265,6 +302,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("\
@@ -276,14 +321,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):
@@ -880,21 +926,21 @@ vim         ")
             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()
@@ -1222,9 +1268,9 @@ vim         ")
         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("\
@@ -1249,13 +1295,13 @@ vim         ")
         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_installed_in_status(1)#チャンネルスキャンをさせる
         self.change_version_in_status("100")
     def update_db_100to101(self):
-        db = self.connect_db()
         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)\
@@ -1263,5 +1309,7 @@ vim         ")
         except:
             ""
         self.close_db(db)
+        self.change_installed_in_status(1)
+        self.change_version_in_status("101")
         #self.change_installed_in_status()#チャンネルスキャンをさせる
-        self.change_version_in_status("101")
\ No newline at end of file
+