X-Git-Url: http://git.osdn.net/view?p=rec10%2Frec10-git.git;a=blobdiff_plain;f=rec10%2Ftrunk%2Fsrc%2FdbMySQL.py;h=de2660694517935b500c84f9b70ef1494303b1d3;hp=51b196748abfe7773634121dc00d0d7d2e18dfa1;hb=975a6c548b6b2c399741a3fbde870c5aa6e7ab0f;hpb=b7f05edd7c1919a4ec275acc593f8eb9296e3399

diff --git a/rec10/trunk/src/dbMySQL.py b/rec10/trunk/src/dbMySQL.py
index 51b1967..de26606 100644
--- a/rec10/trunk/src/dbMySQL.py
+++ b/rec10/trunk/src/dbMySQL.py
@@ -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)\
             )')
@@ -95,19 +96,27 @@ class DB_MySQL:
             chname VARCHAR(100),\
             updatetime DATETIME,\
             status TINYINT,\
-            visible TINYINT DEFAULT 1\
+            visible TINYINT DEFAULT 1,\
+            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, 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,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()
@@ -117,7 +126,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,))
@@ -129,12 +138,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,))
@@ -147,6 +158,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
@@ -154,7 +167,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,))
@@ -166,12 +179,34 @@ 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,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[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 = []
@@ -182,6 +217,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):
@@ -189,6 +226,11 @@ class DB_MySQL:
         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 \
@@ -218,6 +260,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("\
@@ -227,6 +287,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("\
@@ -247,14 +308,26 @@ class DB_MySQL:
                       (status, bctype)\
                       )
         self.close_db(db)
-    def update_chname_by_chtxt_epg_ch(self,chtxt,chname):
+    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 \
-        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):
@@ -436,13 +509,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):
@@ -522,8 +595,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)
@@ -726,7 +799,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()
@@ -1202,15 +1275,18 @@ class DB_MySQL:
         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")
-        db[1].execute("\
-        ALTER TABLE epg_ch ADD logo0 BLOG,\
-        ADD logo1 BLOG,\
-        ADD logo2 BLOG,\
-        ADD logo3 BLOG,\
-        ADD logo4 BLOG,\
-        ADD logo5 BLOG,\
+        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")
@@ -1218,4 +1294,18 @@ class DB_MySQL:
         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_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()
+        try:
+            db[1].execute("\
+            ALTER TABLE timeline ADD epgcategory VARCHAR(100)\
+            ")
+        except:
+            ""
+        self.close_db(db)
+        #self.change_installed_in_status()#チャンネルスキャンをさせる
+        self.change_version_in_status("101")
\ No newline at end of file