'''
Created on Jun 27, 2011

@author: Pravin
'''

from sqlalchemy import create_engine
from sqlalchemy.engine.reflection import Inspector
from orangescape.model.server.migration.AbstractVersionHandler import AbstractVersionHandler
                        
class RDBMSVersionHandler(AbstractVersionHandler):
    from sqlalchemy.engine.url import URL
    import appconfig as config 
    
    DB_SCHEMA = config.SQL_SCHEMA
    DB_INSTANCE = config.SQL_INSTANCE
#    databaseEngine = create_engine('mysql://root:root@localhost/test',echo=False)
    databaseEngine = create_engine(URL('mysql+googledialect', database=DB_SCHEMA, username='root', password='root'),instance=DB_INSTANCE, paramstyle='format')
    connection = databaseEngine.connect()
    
    def executeScripts(self,query):
        self.connection.execute(query)
        
    def createBackUpTable(self, modelName):
        self.clearBackUpTableIfExists(modelName)
        
        tblBackupSqlQuery = "Create Table bkup_tbl" + modelName + " As Select * From tbl" + modelName
        sysBackupSqlQuery = "Create Table bkup_sys" + modelName + " As Select * From sys" + modelName
        tempBackupSqlQuery = "Create Table bkup_temp" + modelName + " As Select * From temp"  + modelName             
        
        self.executeScripts(tblBackupSqlQuery)
        self.executeScripts(sysBackupSqlQuery)
        self.executeScripts(tempBackupSqlQuery)

    def clearBackUpTableIfExists(self, modelName):
        tempDropTableQuery = "Drop Table IF EXISTS bkup_temp" + modelName
        sysDropTableQuery = "Drop Table IF EXISTS bkup_sys" + modelName
        tblDropTableQuery = "Drop Table IF EXISTS bkup_tbl" + modelName

        self.executeScripts(tempDropTableQuery)
        self.executeScripts(sysDropTableQuery)
        self.executeScripts(tblDropTableQuery)        
        
    def getForeignkeyConstraintName(self,tableName,foreignKeyColumn):
        inspectorObject = Inspector(self.databaseEngine)
        for foreignKeyList in inspectorObject.get_foreign_keys(tableName, self.DB_SCHEMA):
            for constrainKeyName in foreignKeyList["constrained_columns"]:
                if constrainKeyName == foreignKeyColumn:
                    return foreignKeyList["name"]

class MySQLHandler(RDBMSVersionHandler):
    def generateAlterTableScripts(self):
        alterScriptDict = dict()        
        for modelId in self.appLevelMismatchModelList:
            alterScriptList = []
            modelName = self.appLevelMismatchModelList[modelId].get('Sheet Name')
#            if self.modelLevelMismatchList.has_key(modelId):
            for mismatchColumn in self.modelLevelMismatchList[modelId]['mismatchReferenceCellDict']:
                mismatchList = self.modelLevelMismatchList[modelId]['mismatchReferenceCellDict'][mismatchColumn]               
                if 'Added' in mismatchList.get('Mismatch Type'):
                    if self.modelLevelMismatchList[modelId]['mismatchPublicCellDict'].has_key(mismatchColumn):
                        publicmisMatchList = self.modelLevelMismatchList[modelId]['mismatchPublicCellDict'][mismatchColumn]
                        if publicmisMatchList['newVerDetails'].get('Data Type') == 'Text':
                            if publicmisMatchList['newVerDetails'].get('Text Length'):
                                datatype = 'Varchar' + "("+publicmisMatchList['newVerDetails'].get('Text Length')+")"
                            else:
                                datatype = 'Varchar(255)'
                        else:
                            datatype = publicmisMatchList['newVerDetails'].get('Data Type')
                                               
                        if 'Added' in publicmisMatchList.get('Mismatch Type'):
                            tblAddColumnAlterScript = "Alter Table tbl" + modelName + " ADD " + publicmisMatchList['newVerDetails'].get('Cell Name') + " " + datatype
                            alterScriptList.append(tblAddColumnAlterScript)                            
                        if 'Renamed' in publicmisMatchList.get('Mismatch Type') or 'Data Type Changed' in publicmisMatchList.get('Mismatch Type') or 'Text Length Changed' in publicmisMatchList.get('Mismatch Type'):
                            tblRenameColumnAlterScript = "Alter Table tbl"+modelName+" Change "+publicmisMatchList['oldVerDetails'].get('Cell Name')+" "+publicmisMatchList['newVerDetails'].get('Cell Name')+" "+datatype
                            alterScriptList.append(tblRenameColumnAlterScript)
                                                        
                        tblAddForeignKeyAlterScript = "Alter Table tbl" + modelName + " ADD CONSTRAINT " + mismatchColumn + " FOREIGN KEY (" + mismatchList['newVerDetails'].get('Cell Name') + ") REFERENCES tbl" + mismatchList['newVerDetails'].get('Referred Sheet Name') + " (" + mismatchList['newVerDetails'].get('Referred Sheet Name') + "_Id)"
                        alterScriptList.append(tblAddForeignKeyAlterScript)
                    else:
                        tblAddForeignKeyAlterScript = "Alter Table tbl" + modelName + " ADD CONSTRAINT " + mismatchColumn + " FOREIGN KEY (" + mismatchList['newVerDetails'].get('Cell Name') + ") REFERENCES tbl" + mismatchList['newVerDetails'].get('Referred Sheet Name') + " (" + mismatchList['newVerDetails'].get('Referred Sheet Name') + "_Id)"
                        alterScriptList.append(tblAddForeignKeyAlterScript)
                    alterScriptDict[modelName] = alterScriptList
                    
                if 'Referred Sheet Changed' in mismatchList.get('Mismatch Type'):
                    constraintName = self.getForeignkeyConstraintName("tbl"+modelName, mismatchList['oldVerDetails'].get('Cell Name'))
                    tblDropForeignKeyAlterScript = "Alter Table tbl" + modelName + " DROP FOREIGN KEY " + constraintName
                    tblAddForeignKeyAlterScript = "Alter Table tbl" + modelName + " ADD CONSTRAINT " + mismatchColumn + " FOREIGN KEY (" + mismatchList['newVerDetails'].get('Cell Name') + ") REFERENCES tbl" + mismatchList['newVerDetails'].get('Referred Sheet Name') + " (" + mismatchList['newVerDetails'].get('Referred Sheet Name') + "_Id)"
                    alterScriptList.append(tblDropForeignKeyAlterScript)
                    alterScriptList.append(tblAddForeignKeyAlterScript)
                    alterScriptDict[modelName] = alterScriptList

                if 'Deleted' in mismatchList.get('Mismatch Type'):
                    if not self.modelLevelMismatchList[modelId]['mismatchPublicCellDict'].has_key(mismatchColumn):
                        constraintName = self.getForeignkeyConstraintName("tbl"+modelName, mismatchList['oldVerDetails'].get('Cell Name'))
                        tblDropForeignKeyAlterScript = "Alter Table tbl" + modelName + " DROP FOREIGN KEY " + constraintName
                        alterScriptList.append(tblDropForeignKeyAlterScript)
                        alterScriptDict[modelName] = alterScriptList
                    

            for mismatchColumn in self.modelLevelMismatchList[modelId]['mismatchPublicCellDict']:
                mismatchList = self.modelLevelMismatchList[modelId]['mismatchPublicCellDict'][mismatchColumn]                
                if mismatchList['newVerDetails'].get('Data Type') == 'Text':
                    if mismatchList['newVerDetails'].get('Text Length'):
                        datatype = 'Varchar' + "("+mismatchList['newVerDetails'].get('Text Length')+")"
                    else:
                        datatype = 'Varchar(255)'
                else:
                    datatype = mismatchList['newVerDetails'].get('Data Type')
                    
                if 'Deleted' in mismatchList.get('Mismatch Type'):
                    if self.modelLevelMismatchList[modelId]['mismatchReferenceCellDict'].has_key(mismatchColumn):
                        referenceMismatchList = self.modelLevelMismatchList[modelId]['mismatchReferenceCellDict'][mismatchColumn]
                        constraintName = self.getForeignkeyConstraintName("tbl"+modelName, referenceMismatchList['oldVerDetails'].get('Cell Name'))
                        tblDropForeignKeyAlterScript = "Alter Table tbl" + modelName + " DROP FOREIGN KEY " + constraintName
                        alterScriptList.append(tblDropForeignKeyAlterScript)
                        
                        tblDropColumnAlterScript = "Alter Table tbl"+modelName+" Drop Column "+mismatchList['oldVerDetails'].get('Cell Name')
                        alterScriptList.append(tblDropColumnAlterScript)
                    else:
                        tblDropColumnAlterScript = "Alter Table tbl"+modelName+" Drop Column "+mismatchList['oldVerDetails'].get('Cell Name')
                        alterScriptList.append(tblDropColumnAlterScript)
                    alterScriptDict[modelName] = alterScriptList
                        
                if 'Added' in mismatchList.get('Mismatch Type'):
                    if not self.modelLevelMismatchList[modelId]['mismatchReferenceCellDict'].has_key(mismatchColumn):
                        tblAddColumnAlterScript = "Alter Table tbl" + modelName + " ADD " + mismatchList['newVerDetails'].get('Cell Name') + " " + datatype
                        alterScriptList.append(tblAddColumnAlterScript)
                    alterScriptDict[modelName] = alterScriptList
                    
                if 'Renamed' in mismatchList.get('Mismatch Type') or 'Data Type Changed' in mismatchList.get('Mismatch Type') or 'Text Length Changed' in mismatchList.get('Mismatch Type'):
                    if not self.modelLevelMismatchList[modelId]['mismatchReferenceCellDict'].has_key(mismatchColumn):                    
                        tblRenameColumnAlterScript = "Alter Table tbl"+modelName+" Change "+mismatchList['oldVerDetails'].get('Cell Name')+" "+mismatchList['newVerDetails'].get('Cell Name')+" "+datatype
                        alterScriptList.append(tblRenameColumnAlterScript)
                        alterScriptDict[modelName] = alterScriptList
                    
            for mismatchColumn in self.modelLevelMismatchList[modelId]['mismatchPrivateCellDict']:
                mismatchList = self.modelLevelMismatchList[modelId]['mismatchPrivateCellDict'][mismatchColumn]                
                if mismatchList['newVerDetails'].get('Data Type') == 'Text':
                    textLength = '(255)'
                    datatype = 'Varchar'
                else:
                    textLength = ''
                    datatype = mismatchList['newVerDetails'].get('Data Type')
                    
                if 'Deleted' in mismatchList.get('Mismatch Type'):
                    tempDropColumnAlterScript = "Alter Table temp"+modelName+" Drop Column "+mismatchList['oldVerDetails'].get('Cell Name')
                    alterScriptList.append(tempDropColumnAlterScript)
                    alterScriptDict[modelName] = alterScriptList
                    
                if 'Added' in mismatchList.get('Mismatch Type'):
                    tempAddColumnAlterScript = "Alter Table temp" + modelName + " ADD " + mismatchList['newVerDetails'].get('Cell Name') + " " + datatype + textLength
                    alterScriptList.append(tempAddColumnAlterScript)
                    alterScriptDict[modelName] = alterScriptList
                    
                if 'Data Type Changed' in mismatchList.get('Mismatch Type'):
                    tempRenameColumnAlterScript = "Alter Table temp" + modelName+" Change " + mismatchList['oldVerDetails'].get('Cell Name') +" " + mismatchList['newVerDetails'].get('Cell Name') +" "+ datatype + textLength
                    alterScriptList.append(tempRenameColumnAlterScript)                         
                    alterScriptDict[modelName] = alterScriptList 
        return alterScriptDict 
    
    def executeAlterScripts(self):        
        scriptsDict = self.generateAlterTableScripts()
        for model in scriptsDict:
            self.createBackUpTable(model)
            for script in scriptsDict[model]:
                self.executeScripts(script)
        
        self.connection.close()
                
class OracleHandler(RDBMSVersionHandler):
    def generateAlterTableScripts(self):
        for modelId in self.appLevelMismatchModelList:
            modelName = self.applicationSheetMetadata[modelId]
            if self.modelLevelMismatchList.has_key(modelId):
                for mismatchColumn in self.modelLevelMismatchList[modelId]:
                    sheetMetadatList = self.modelLevelMismatchList[modelId][mismatchColumn]
                    if sheetMetadatList.get('Mismatch Type') == 'Public Cell Renamed':
                        tblRenameColumnAlterScript = "Alter Table tbl"+modelName+" Rename Column "+sheetMetadatList.get('Old Cell Name')+" To "+sheetMetadatList.get('New Cell Name')
                        print tblRenameColumnAlterScript  
                    if sheetMetadatList.get('Mismatch Type') == 'Private Cell Data Type Changed':
                        tempDataTypeAlterScript = "Alter Table temp"+modelName+" Modify ( "+sheetMetadatList.get('Cell Name')+"  " +sheetMetadatList.get('New Data Type')+" )"
                        print tempDataTypeAlterScript
                    if sheetMetadatList.get('Mismatch Type') == 'Public Cell Data Type Changed':
                        tblDataTypeAlterScript = "Alter Table tbl"+modelName+" Modify ( "+sheetMetadatList.get('Cell Name')+"  " +sheetMetadatList.get('New Data Type')+" )"
                        print tblDataTypeAlterScript                        
                    if sheetMetadatList.get('Mismatch Type') == 'Public Cell Missing':
                        tblDropColumnAlterScript = "Alter Table tbl"+modelName+" Drop "+sheetMetadatList.get('Cell Name')
                        print tblDropColumnAlterScript  
                    if sheetMetadatList.get('Mismatch Type') == 'Private Cell Missing':
                        tempDropColumnAlterScript = "Alter Table temp"+modelName+" Drop "+sheetMetadatList.get('Cell Name')
                        print tempDropColumnAlterScript
                    if sheetMetadatList.get('Mismatch Type') == 'Reference Cell Missing':
                        RDBMSVersionHandler = RDBMSVersionHandler()
                        constraintName = RDBMSVersionHandler.getForeignkeyConstraintName(modelName, sheetMetadatList.get('Cell Name'))
                        tblDropForeignKeyAlterScript = "Alter Table tbl"+modelName+" DROP Constraint "+constraintName
                        print tblDropForeignKeyAlterScript
                    if sheetMetadatList.get('Mismatch Type') == 'Reference Cell Changed':
                        constraintName = self.getForeignkeyConstraintName(modelName, sheetMetadatList.get('Cell Name'))
                        tblDropForeignKeyAlterScript = "Alter Table tbl"+modelName+" DROP Constraint "+constraintName
                        tblAddForeignKeyAlterScript = "Alter Table tbl"+modelName+" ADD FOREIGN KEY ("+sheetMetadatList.get('Cell Name')+") REFERENCES "+sheetMetadatList.get('New Referred Sheet Name')+" ("+modelName+"_Id)"
                        print tblDropForeignKeyAlterScript
                        print tblAddForeignKeyAlterScript
                