Changeset 34041 for branches/meh_branches/ppstack_test/ippToPsps/jython/mysql.py

Timestamp:

Jun 19, 2012, 5:24:19 PM (14 years ago)

Author:

mhuber

Message:

merging latest r34040 trunk changes to branch

Location:

branches/meh_branches/ppstack_test

Files:

• . (modified) (1 prop)
• ippToPsps/jython/mysql.py (modified) (8 diffs)

branches/meh_branches/ppstack_test/ippToPsps/jython/mysql.py

@@ -19 +19 @@
     '''
     Constructor
-
     '''
     def __init__(self, logger, config, dbType, dbName=None):
@@ -35 +34 @@
         self.dbPass = config.getDbPassword(dbType)
 
+        # a user friendly connection sring
+        connectionStr = self.dbName + "@" + self.dbHost
+
         # set up JDBC connection
-        self.url = "jdbc:mysql://"+self.dbHost+"/"+self.dbName+"?user="+self.dbUser+"&password="+self.dbPass
+        self.url = "jdbc:mysql://"+self.dbHost+"/"+self.dbName+"?autoReconnect=true&user="+self.dbUser+"&password="+self.dbPass
         try:
             self.con = DriverManager.getConnection(self.url)
             self.connectionID = self.getLastConnectionID()
-            self.logger.debug("MySQL connection to %s with ID %d" % (dbType, self.connectionID))
+            self.logger.infoPair("Connected to MySQL Db", connectionStr)
         except:
-            self.logger.error("Unable to connect to " + self.url)
-            self.everythingOK = False # TODO need this?
+            self.logger.errorPair("Unable to connect to", connectionStr)
             self.connected = False
-            return
-
-        self.everythingOK = True
+            raise
+
         self.connected = True
 
@@ -89 +89 @@
         sql = "KILL %d" % connectionID
         self.execute(sql)
+
+    '''
+    Checks whether this table exists
+    '''
+    def tableExists(self, tableName):
+
+        sql = "SELECT COUNT(*) \
+               FROM information_schema.tables \
+               WHERE table_schema = '" + self.dbName + "' \
+               AND table_name = '" + tableName + "'"
+
+        try:
+            rs = self.executeQuery(sql)
+            rs.first()
+            if rs.getInt(1) > 0: return True
+            else: return False
+        except:
+            self.logger.errorPair("Could not check if table exists", sql)
+            pass
+
+        return False
 
     '''
@@ -151 +172 @@
 
     '''
+    Drops a column from a table
+    '''
+    def dropColumn(self, table, column):
+
+        sql = "ALTER TABLE " + table + " DROP " + column
+        try: self.execute(sql)
+        except: return
+
+    '''
+    Drops a list of tables
+    '''
+    def dropTables(self, tables):
+        for table in tables: self.dropTable(table)
+
+    '''
     Drops a table
     '''
@@ -185 +221 @@
             #self.logger.warn("Index already in place on '" + column + "' for table '" + table + "'")
     '''
+    Changes the database engine for this table to InnoDb
+    '''
+    def changeEngineToInnoDB(self, table):
+        self.changeEngine(table, "InnoDB")
+
+    '''
+    Changes the database engine for this table
+    '''
+    def changeEngine(self, table, engine):
+
+        sql = "ALTER TABLE " + table + " ENGINE=" + engine
+        try: 
+            self.execute(sql)
+        except: 
+            self.logger.errorPair("Could not change table engine", sql)
+            return False
+
+        return True
+
+    '''
     Adds an index to the supplied table and column
     '''
@@ -196 +252 @@
         except: pass
             #self.logger.warn("Index already in place on '" + column + "' for table '" + table + "'")
+
+    '''
+    Checks that we have a connection
+    '''
+    def isConnected(self):
+
+        try: 
+            self.con
+            self.connected = True
+        except:
+            self.logger.errorPair("Not connected to", self.url)
+            self.connected = False
+
+        return self.connected
+
     '''
     TODO
     '''
     def execute(self, sql):
+
+        if not self.isConnected(): raise
 
         stmt = self.con.createStatement()
@@ -210 +283 @@
     def executeQuery(self, sql):
 
+        if not self.isConnected(): raise
+  
         stmt = self.con.createStatement()
         rs = stmt.executeQuery(sql)
@@ -336 +411 @@
             return -1
 
-
+    '''
+    Adds a column to this table which contains a row count
+    '''
+    def addRowCountColumn(self, table, colName):
+
+        self.execute("ALTER TABLE " + table + " ADD COLUMN " + colName + " INTEGER FIRST")
+        self.execute("SET @i = 0")
+        self.execute("UPDATE " + table + " SET " + colName + "=(@i:=@i+1)")
+        self.execute("ALTER TABLE " + table + " ADD PRIMARY KEY (" + colName + ")")
+