X-Git-Url: http://git.osdn.net/view?a=blobdiff_plain;ds=sidebyside;f=rec10%2Ftrunk%2Fsrc%2FdbMySQL.py;h=437075d3e63a0fb89d95cd9721c15d347e6f838f;hb=15cb813200bf0bfb9062d85633fecf5a342c2d6e;hp=8dc094cd8cafa5f2b11e1ae54508b4e981ee999d;hpb=13d06db43f0a7ef196f462544564665b3f2c4285;p=rec10%2Frec10-git.git

diff --git a/rec10/trunk/src/dbMySQL.py b/rec10/trunk/src/dbMySQL.py
index 8dc094c..437075d 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-2011 Yukikaze
 import MySQLdb
 import recdblist
 import warnings
@@ -28,7 +28,8 @@ class DB_MySQL:
             cur.close()
             con.close()
         except Exception, inst:
-            recdblist.Commonlogex("Error", "init (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=500)
+            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('\
@@ -53,7 +54,8 @@ class DB_MySQL:
             UNIQUE uni (type,chtxt,title,btime,deltaday)\
             )')
         except Exception, inst:
-            recdblist.Commonlogex("Error", "init (dbMySQL.py)", str(type(inst)),str(inst)+traceback.format_exc(),log_level=500)
+            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()
@@ -78,8 +80,10 @@ class DB_MySQL:
         db = self.connect_db()
         try:
             db[1].execute('drop table epg_ch')
-        except:
-            ""
+        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 \
@@ -91,10 +95,12 @@ class DB_MySQL:
             chname VARCHAR(100),\
             updatetime DATETIME,\
             status TINYINT,\
-            isshow TINYINT\
+            visible TINYINT DEFAULT 1\
             )')
-        except:
-            ""
+        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):
         db = self.connect_db()
@@ -103,12 +109,17 @@ class DB_MySQL:
         VALUES (%s,%s,%s,%s,"",%s,%s,%s)', \
                       (bctype, chtxt, ch, csch, updatetime, "1","1"))
         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,updatetime,chname,status,visible \
         FROM epg_ch \
-        WHERE chtxt = %s", \
+        WHERE chtxt LIKE %s", \
                               (chtxt,))
         ret = []
         dls = []
@@ -123,7 +134,7 @@ class DB_MySQL:
     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,updatetime,status,chname,status,visible \
         FROM epg_ch \
         WHERE bctype = %s", \
                               (bctype,))
@@ -143,7 +154,7 @@ class DB_MySQL:
         db = self.connect_db()
         dbexe = db[1].execute("\
         SELECT \
-        bctype,chtxt,ch,csch,updatetime \
+        bctype,chtxt,ch,csch,updatetime,chname,status,visible \
         FROM epg_ch \
         WHERE ch = %s", \
                               (ch,))
@@ -160,7 +171,7 @@ class DB_MySQL:
     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,updatetime,chname,status,visible \
         FROM epg_ch \
         ")
         ret = []
@@ -173,6 +184,11 @@ class DB_MySQL:
             r[4] = r[4].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 set_new_status(self,dhour):
         db = self.connect_db()
         dbexe = db[1].execute("UPDATE epg_ch \
@@ -253,8 +269,10 @@ class DB_MySQL:
         db = self.connect_db()
         try:
             db[1].execute('drop table auto_proc_tmp')
-        except:
-            ""
+        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 \
@@ -264,22 +282,28 @@ class DB_MySQL:
             chtxt VARCHAR(30),\
             UNIQUE unibayeskey(title)\
             )')
-        except:
-            ""
+        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:
-            ""
+        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:
-            ""
+        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 \
@@ -289,8 +313,10 @@ class DB_MySQL:
             chtxt VARCHAR(30),\
             UNIQUE unibayeskey(title)\
             )')
-        except:
-            ""
+        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()
@@ -304,8 +330,9 @@ class DB_MySQL:
         db = self.connect_db()
         try:
             db[1].execute('drop table in_settings')
-        except:
-            ""
+        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()
@@ -319,8 +346,10 @@ class DB_MySQL:
             auto_del_tmp TINYINT\
             )')
             db[1].execute("INSERT IGNORE into in_settings VALUE (0,0,\"H\",1)")
-        except:
-            ""
+        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()
@@ -378,8 +407,10 @@ class DB_MySQL:
             category VARCHAR(100),\
             UNIQUE uni (chtxt,title,btime,category)\
             )')
-        except:
-            ""
+        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()
@@ -491,8 +522,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)
@@ -673,10 +704,13 @@ class DB_MySQL:
             (\
             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
             keyword VARCHAR(200),\
+            auto TINYINT DEFAULT 0,\
+            opt VARCHAR(20),\
             UNIQUE unijbk (keyword)\
             )")
-        except:
-            ""
+        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()
@@ -690,9 +724,9 @@ class DB_MySQL:
     def select_all_in_auto_jbk_key(self):
         db = self.connect_db()
         dbexe = db[1].execute("\
-        SELECT keyword \
+        SELECT keyword,auto,opt \
         FROM in_auto_jbk_key \
-        ")
+vim         ")
         ret = []
         if dbexe > 0:
             ret = db[1].fetchall()
@@ -702,8 +736,9 @@ class DB_MySQL:
         db = self.connect_db()
         try:
             db[1].execute('drop table in_status')
-        except:
-            ""
+        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()
@@ -718,9 +753,10 @@ class DB_MySQL:
             installed TINYINT DEFAULT 0,\
             version TINYINT\
             )")
-            db[1].execute("INSERT IGNORE into in_status VALUE (0,0,0,0,0)")
-        except:
-            ""
+            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()
@@ -755,8 +791,9 @@ class DB_MySQL:
             for dl in dls:
                 r = list(dl)
                 version=int(str(r[0]))
-        except:
-            ""
+        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()
@@ -837,8 +874,9 @@ class DB_MySQL:
             DELETE FROM epg_timeline \
             WHERE bctype = %s", \
                           (bctype,))
-        except:
-            ""
+        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 \
@@ -853,8 +891,9 @@ class DB_MySQL:
             category VARCHAR(100),\
             UNIQUE unitv(bctype,channel,start,stop,title)\
             )")
-        except:
-            ""
+        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):
@@ -880,16 +919,13 @@ class DB_MySQL:
         db = self.connect_db()
         dbexe = "\
         SELECT \
-        epg_ch.chtxt,title,start,stop,exp,longexp,category \
+        channel,title,start,stop,exp,longexp,category \
         FROM epg_timeline \
-        INNER JOIN epg_ch \
-        WHERE epg_ch.chtxt=epg_timeline.channel \
-        AND \
-        start >= %s \
+        WHERE start >= %s \
         AND \
         start <= %s \
         AND \
-        epg_ch.chtxt=%s"
+        channel LIKE %s"
         dbcmd = db[1].execute(dbexe, (btime, etime, chtxt))
         retall = []
         if dbcmd > 0:
@@ -905,6 +941,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 \
@@ -934,8 +972,9 @@ class DB_MySQL:
             UNIQUE unibayeskey(keychar,chtxt)\
             )')
             db[1].execute('CREATE INDEX keycharindex ON in_auto_bayes_key(keychar)')
-        except:
-            ""
+        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):
@@ -995,8 +1034,9 @@ class DB_MySQL:
                 VALUES (%s,%s,%s,%s)\
                 ON DUPLICATE KEY UPDATE \
                 ratio_all=CONVERT((ratio_all*%s+%s)/%s,DECIMAL(32,14))",retl)
-            except:
-                ""
+            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)
@@ -1026,8 +1066,9 @@ class DB_MySQL:
                 VALUES (%s,%s,%s,%s)\
                 ON DUPLICATE KEY UPDATE \
                 ratio_rec=CONVERT((ratio_rec*%s+%s)/%s,DECIMAL(32,14))",retl)
-            except:
-                ""
+            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()
@@ -1057,8 +1098,9 @@ class DB_MySQL:
             etime DATETIME,\
             UNIQUE uni (chtxt,title,btime,etime)\
             )')
-        except:
-            ""
+        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('\
@@ -1089,8 +1131,9 @@ class DB_MySQL:
             point INT,\
             UNIQUE uni (chtxt,title,btime,etime)\
             )')
-        except:
-            ""
+        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('\
@@ -1147,4 +1190,35 @@ class DB_MySQL:
         self.drop_in_settings()
         self.new_in_settings()
         self.close_db(db)
-        self.change_version_in_status("98")
\ No newline at end of file
+        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[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()#チャンネルスキャンをさせる
+        self.change_version_in_status("100")
\ No newline at end of file