#!/usr/bin/env jython

import re
import sys
import os
import logging

from java.lang import *
from java.sql import *
from xml.etree.ElementTree import ElementTree

'''
Base class of all MySql database classes.
'''
class MySql(object):

    driverName="com.mysql.jdbc.Driver"

    '''
    Constructor

    '''
    def __init__(self, logger, doc, dbType):

        # set up logging
        self.logger = logger
        self.doc = doc
        self.logger.debug("MySql class constructor")

        # open config and grab database parameters
        self.dbName = self.doc.find(dbType +"/name").text
        self.dbHost = self.doc.find(dbType +"/host").text
        self.dbUser = self.doc.find(dbType +"/user").text
        self.dbPass = self.doc.find(dbType +"/password").text

        # set up JDBC connection
        self.url = "jdbc:mysql://"+self.dbHost+"/"+self.dbName+"?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))
        except:
            self.logger.error("Unable to connect to " + self.url)
            self.everythingOK = False
            return

        self.everythingOK = True

    '''
    Disconnect from database
    '''
    def disconnect(self):
        self.con.close()

    '''
    Destructor
    '''
    def __del__(self):

        self.logger.debug("MySql destructor")
        self.disconnect()
    
    '''
    Locks a table. This is wait if the locks is already held by another session
    '''
    def lockTable(self, table):
        self.execute("LOCK TABLES " + table + " WRITE")

    '''
    Unlocks all tables locked by this session.
    '''
    def unlockTables(self):
        self.execute("UNLOCK TABLES")

    '''
    Kills the last connection ID, so long as it's not THIS connection ID
    '''
    def killLastConnectionID(self):
   
        connectionID = self.getLastConnectionID()
        if connectionID == self.connectionID:
            self.logger.error("NOT going to kill THIS connection ID")
            return

        sql = "KILL %d" % connectionID
        self.execute(sql)

    '''
    Gets the last connection ID
    '''
    def getLastConnectionID(self):

        sql = "SELECT ID \
               FROM INFORMATION_SCHEMA.PROCESSLIST \
               WHERE DB='" + self.dbName + "' \
               ORDER BY ID"
        rs = self.executeQuery(sql)
        rs.last()
        return rs.getInt(1)

    '''
    Updates all rows of this column of this table with this value
    '''
    def updateAllRows(self, table, column, value):

        sql = "UPDATE " + table + " SET " + column + " = " + value
        self.execute(sql)

    '''
    Drops a table
    '''
    def dropTable(self, table):

        sql = "DROP TABLE " + table
        try: self.execute(sql)
        except: return

    '''
    Alters a column to be unique
    '''
    def makeColumnUnique(self, table, column):

        self.logger.debug("Making '"+column+"' unique on table '"+table+"'")

        sql = "ALTER TABLE " + table + " ADD UNIQUE (" + column + ")"
        try:
            self.execute(sql)
        except: pass
            #self.logger.warn("Index already in place on '" + column + "' for table '" + table + "'")

    '''
    Alters a column to be the primary key
    '''
    def makeColumnPrimaryKey(self, table, column):

        self.logger.debug("Making '"+column+"' the primary key on table '"+table+"'")

        sql = "ALTER TABLE " + table + " ADD PRIMARY KEY (" + column + ")"
        try:
            self.execute(sql)
        except: pass
            #self.logger.warn("Index already in place on '" + column + "' for table '" + table + "'")
    '''
    Adds an index to the supplied table and column
    '''
    def createIndex(self, table, column):

        #self.logger.debug("Creating index on column '"+column+"' for table '"+table+"'")

        sql = "CREATE INDEX "+table+"_"+column+"_index ON "+table+" ("+column+")"
        try:
            self.execute(sql)
        except: pass
            #self.logger.warn("Index already in place on '" + column + "' for table '" + table + "'")
    '''
    TODO
    '''
    def execute(self, sql):

        stmt = self.con.createStatement()
        stmt.execute(sql)
        stmt.close()

    '''
    TODO
    '''
    def executeQuery(self, sql):

        stmt = self.con.createStatement()
        rs = stmt.executeQuery(sql)
        #stmt.close()
        return rs

    '''
    TODO
    '''
    def executeUpdatableQuery(self, sql):

        stmt = self.con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)
        rs = stmt.executeQuery(sql)
        #stmt.close()
        return rs
    '''
    Returns a list of column names for this table
    '''
    def getColumnNames(self, tableName):

       sql = "SHOW COLUMNS FROM " + tableName
       rs = self.executeQuery(sql)
       columns = []
       while (rs.next()): columns.append(rs.getString(1))
       rs.close()
       
       return columns

    '''
    Replaces all NULL values in the provided table and column ith the provided substitute 
    '''
    def replaceNullsInThisColumn(self, tableName, column, sub):

      sql = "UPDATE " + tableName + " SET " + column + " = " + sub + " WHERE " + column + " IS NULL"
      self.execute(sql)

    '''
    Replaces all NULL values in the provided table with the provided substitute 
    '''
    def replaceNulls(self, tableName, sub):

       # get list of columns
       columns = self.getColumnNames(tableName)

       # now loop through all columns and replace all NULLs with sub
       for column in columns:
          
          sql = "UPDATE " + tableName + " SET " + column + " = " + sub + " WHERE " + column + " IS NULL"
          self.execute(sql)

    '''
    Reports the number of rows with this columns set as this numeric value and then deletes them 
    '''
    def reportAndDeleteRowsWithThisValue(self, tableName, columnName, value):

        sql = "SELECT COUNT(*) FROM " + tableName + " WHERE " + columnName + " = " + value
        rs = self.executeQuery(sql)
        rs.first()
        nBad = rs.getInt(1)

        sql="DELETE FROM " + tableName + " WHERE " + columnName + " = " + value
        self.execute(sql)
        self.logger.debugPair("%s rows with %s" % (columnName, value), "%d deleted" % nBad)

        return nBad

    '''
    Reports the number of rows with this column NULL and then deletes them 
    '''
    def reportAndDeleteRowsWithNULLS(self, tableName, columnName):

        sql = "SELECT COUNT(*) FROM " + tableName + " WHERE " + columnName + " IS NULL"
        rs = self.executeQuery(sql)
        rs.first()
        nBad = rs.getInt(1)

        sql="DELETE FROM " + tableName + " WHERE " + columnName + " IS NULL"
        self.execute(sql)
        self.logger.debugPair("NULL %s values in %s" % (columnName, tableName), "%d deleted" % nBad)

        return nBad

    '''
    Searches a table and reports the columns that are either partially or completely populated with NULLs
    '''
    def reportNulls(self, tableName, showPartials):

       # first, count rows
       sql = "SELECT COUNT(*) FROM " + tableName
       rs = self.executeQuery(sql)
       rs.first()
       numRows = rs.getInt(1)

       # get list of columns
       columns = self.getColumnNames(tableName)

       print "+----------------------+---------------+"
       print "|  %25s           |" % tableName
       print "+----------------------+---------------+"

       # now see which columns are full of NULLS, with are partially NULL
       for column in columns:
          
          sql = "SELECT COUNT(*) FROM " + tableName + " WHERE " + column + " IS NULL"
          rs = self.executeQuery(sql)
          rs.first()
          if rs.getInt(1) == numRows:
              print "| %20s | all NULL      |" % column
          elif showPartials and rs.getInt(1) > 0:
              print "| %20s | partial NULL  |" % column
       rs.close()
       print "+----------------------+---------------+"

    '''
    Returns a row count for this table
    '''
    def getRowCount(self, table):

        sql = "SELECT COUNT(*) FROM " + table
        try:
            rs = self.executeQuery(sql)  
            rs.first()
            return rs.getInt(1)
        except:
            self.logger.exception("Could not count rows for table: '" + table + "'")
            return -1


