def migrateTableColumnToMySQL(self, state, source_column, targetTable): target_column = GenericMigration.migrateTableColumnToMySQL(self, state, source_column, targetTable) # MySQL specific for attr in ["autoIncrement", "expression", "generated", "generatedStorage"]: setattr(target_column, attr, getattr(source_column, attr)) return target_column
def migrateUpdateForChanges(self, state, target_catalog): """ Create datatype cast expression for target column based on source datatype. """ for targetSchema in target_catalog.schemata: for targetTable in targetSchema.tables: for target_column in targetTable.columns: # SQL expression to use for converting the column data to the target type # eg.: CAST(? as NVARCHAR(max)) type_cast_expression = None source_datatype = None source_column = state.lookupSourceObject(target_column) if source_column: source_datatype = GenericMigration.getColumnDataType( self, source_column) if source_column and source_datatype: if source_datatype == 'XML': type_cast_expression = "CAST(? as NVARCHAR(max))" if type_cast_expression: target_column.owner.customData[ "columnTypeCastExpression:%s" % target_column. name] = "%s as ?" % type_cast_expression return target_catalog
def migrateTableColumnToMySQL(self, state, source_column, targetTable): target_column = GenericMigration.migrateTableColumnToMySQL(self, state, source_column, targetTable) # MySQL specific for attr in ["autoIncrement"]: setattr(target_column, attr, getattr(source_column, attr)) return target_column
def migrateUpdateForChanges(self, state, target_catalog): """ Create datatype cast expression for target column based on source datatype. """ for targetSchema in target_catalog.schemata: for targetTable in targetSchema.tables: for target_column in targetTable.columns: # SQL expression to use for converting the column data to the target type # eg.: CAST(? as NVARCHAR(max)) type_cast_expression = None source_datatype = None source_column = state.lookupSourceObject(target_column) if source_column: source_datatype = GenericMigration.getColumnDataType( self, source_column) if source_column and source_datatype: if source_datatype == 'SYSNAME': type_cast_expression = "CONVERT(VARCHAR(30), ?)" elif source_datatype == 'LONGSYSNAME': type_cast_expression = "CONVERT(VARCHAR(255), ?)" elif source_datatype in ['DECIMAL', 'NUMERIC' ] and source_column.identity: if source_column.precision < 5: type_cast_expression = "CONVERT(SMALLINT, ?)" elif source_column.precision < 10: type_cast_expression = "CONVERT(INT, ?)" else: type_cast_expression = "CONVERT(BIGINT, ?)" if type_cast_expression: target_column.owner.customData[ "columnTypeCastExpression:%s" % target_column. name] = "%s as ?" % type_cast_expression return target_catalog
def migrateTableColumnToMySQL(self, state, source_column, targetTable): target_column = GenericMigration.migrateTableColumnToMySQL(self, state, source_column, targetTable) if target_column: # Autoincrement for integer datatypes: if source_column.simpleType: source_datatype = source_column.simpleType.name if source_datatype in ['INT', 'TINYINT', 'SMALLINT', 'BIGINT']: target_column.autoIncrement = source_column.identity # TODO set charset/collation #target_column.characterSetName = return target_column
def migrateUpdateForChanges(self, state, target_catalog): """ Create datatype cast expression for target column based on source datatype. """ for targetSchema in target_catalog.schemata: for targetTable in targetSchema.tables: for target_column in targetTable.columns: # SQL expression to use for converting the column data to the target type # eg.: CAST(? as NVARCHAR(max)) type_cast_expression = None source_datatype = None source_column = state.lookupSourceObject(target_column) if source_column: source_datatype = GenericMigration.getColumnDataType(self, source_column) if source_column and source_datatype: target_datatype = target_column.simpleType.name.upper() if source_datatype in ['VARCHAR', 'NVARCHAR']: if source_datatype == 'VARCHAR': if target_column.length > 4000 or target_column.length == -1: # NVARCHAR is limited to 4000 - if you need more, you must use MAX insted BUG #18167872 type_cast_expression = "CAST(? as NVARCHAR(MAX))" # If in source table is VARCHAR(MAX) column lnegth is 0 - so must be casted to NVARCHAR(MAX) BUG #18105486 else: type_cast_expression = "CAST(? as NVARCHAR(%d))" % target_column.length elif source_datatype in ['TEXT', 'NTEXT']: if source_datatype == 'TEXT': type_cast_expression = "CAST(? as NTEXT)" elif source_datatype in ['CHAR', 'NCHAR']: # MSSQL CHAR's (also VARCHAR's) max length is 8000 non Unicode characters if source_datatype == 'CHAR': type_cast_expression = "CAST(? as NCHAR(%d))" % target_column.length elif source_datatype == 'UNIQUEIDENTIFIER': type_cast_expression = "CAST(? as VARCHAR(64))" elif source_datatype == 'SYSNAME': # the relevant info is in http://msdn.microsoft.com/en-us/library/ms191240(v=sql.105).aspx type_cast_expression = "CAST(? as VARCHAR(128))" # floating point datatypes: elif source_datatype in ['DECIMAL', 'NUMERIC']: if source_column.scale == 0: type_cast_expression = "CAST(? as %s)" % ('INT' if target_datatype == 'MEDIUMINT' else target_datatype) elif source_datatype == 'XML': type_cast_expression = "CAST(? as NVARCHAR(max))" elif source_datatype in ['GEOMETRY', 'GEOGRAPHY']: type_cast_expression = '?.STAsText()' elif source_datatype == 'HIERARCHYID': type_cast_expression = "CAST(? as VARCHAR(max))" elif source_datatype == 'SQL_VARIANT': type_cast_expression = "CAST(? as NVARCHAR(max))" elif source_datatype in ['BINARY', 'VARBINARY', 'TIMESTAMP', 'ROWVERSION']: type_cast_expression = 'CONVERT(VARBINARY(MAX), ?, 0)' if type_cast_expression: target_column.owner.customData["columnTypeCastExpression:%s" % target_column.name] = "%s as ?" % type_cast_expression return target_catalog
def migrateTablePrimaryKeyToMySQL(self, state, sourceTable, targetTable): res = GenericMigration.migrateTablePrimaryKeyToMySQL(self, state, sourceTable, targetTable) if targetTable.primaryKey: for icolumn in targetTable.primaryKey.columns: if not icolumn.referencedColumn.isNotNull: icolumn.referencedColumn.isNotNull = 1 icolumn.referencedColumn.defaultValueIsNull = 0 icolumn.referencedColumn.defaultValue = '' # force primary keys to be NOT NULL state.addMigrationLogEntry(1, sourceTable, targetTable, 'Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL.') return res
def migrateTablePrimaryKeyToMySQL(self, state, sourceTable, targetTable): res = GenericMigration.migrateTablePrimaryKeyToMySQL( self, state, sourceTable, targetTable) if targetTable.primaryKey: for icolumn in targetTable.primaryKey.columns: if not icolumn.referencedColumn.isNotNull: icolumn.referencedColumn.isNotNull = 1 icolumn.referencedColumn.defaultValueIsNull = 0 icolumn.referencedColumn.defaultValue = '' # force primary keys to be NOT NULL state.addMigrationLogEntry( 1, sourceTable, targetTable, 'Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL.' ) return res
def migrateTableToMySQL(self, state, sourceTable, target_schema): targetTable = GenericMigration.migrateTableToMySQL( self, state, sourceTable, target_schema) # MySQL attributes for attr in [ "tableEngine", "nextAutoInc", "password", "delayKeyWrite", "defaultCharacterSetName", "defaultCollationName", "mergeUnion", "mergeInsert", "tableDataDir", "tableIndexDir", "packKeys", "raidType", "raidChunks", "raidChunkSize", "checksum", "rowFormat", "keyBlockSize", "avgRowLength", "minRows", "maxRows", "partitionType", "partitionExpression", "partitionCount", "subpartitionType", "subpartitionExpression", "subpartitionCount" ]: setattr(targetTable, attr, getattr(sourceTable, attr)) if True: def copy_partitions(owner, part_list): l = [] for src in part_list: dst = grt.classes.db_mysql_PartitionDefinition() for attr in [ "name", "value", "comment", "dataDirectory", "indexDirectory", "maxRows", "minRows" ]: setattr(dst, attr, getattr(src, attr)) dst.owner = owner dst.subpartitionDefinitions.extend( copy_partitions(dst, src.subpartitionDefinitions)) l.append(dst) return l # partition defs targetTable.partitionDefinitions.extend( copy_partitions(targetTable, sourceTable.partitionDefinitions)) return targetTable
def migrateUpdateForChanges(self, state, target_catalog): """ Create datatype cast expression for target column based on source datatype. """ for targetSchema in target_catalog.schemata: for targetTable in targetSchema.tables: for target_column in targetTable.columns: # SQL expression to use for converting the column data to the target type # eg.: CAST(? as NVARCHAR(max)) type_cast_expression = None source_datatype = None source_column = state.lookupSourceObject(target_column) if source_column: source_datatype = GenericMigration.getColumnDataType(self, source_column) if source_column and source_datatype: if source_datatype == 'XML': type_cast_expression = "CAST(? as NVARCHAR(max))" if type_cast_expression: target_column.owner.customData["columnTypeCastExpression:%s" % target_column.name] = "%s as ?" % type_cast_expression return target_catalog
def migrateTableToMySQL(self, state, sourceTable, target_schema): targetTable = GenericMigration.migrateTableToMySQL(self, state, sourceTable, target_schema) # MySQL attributes for attr in ["tableEngine", "nextAutoInc", "password", "delayKeyWrite", "defaultCharacterSetName", "defaultCollationName", "mergeUnion", "mergeInsert", "tableDataDir", "tableIndexDir", "packKeys", "raidType", "raidChunks", "raidChunkSize", "checksum", "rowFormat", "keyBlockSize", "avgRowLength", "minRows", "maxRows", "partitionType", "partitionExpression", "partitionCount", "subpartitionType", "subpartitionExpression", "subpartitionCount"]: setattr(targetTable, attr, getattr(sourceTable, attr)) if True: def copy_partitions(owner, part_list): l = [] for src in part_list: dst = grt.classes.db_mysql_PartitionDefinition() for attr in ["name", "value", "comment", "dataDirectory", "indexDirectory", "maxRows", "minRows"]: setattr(dst, attr, getattr(src, attr)) dst.owner = owner dst.subpartitionDefinitions.extend(copy_partitions(dst, src.subpartitionDefinitions)) l.append(dst) return l # partition defs targetTable.partitionDefinitions.extend(copy_partitions(targetTable, sourceTable.partitionDefinitions)) return targetTable
def migrateTriggerToMySQL(self, state, source_trigger, target_table): target_trigger = GenericMigration.migrateTriggerToMySQL( self, state, source_trigger, target_table) target_trigger.commentedOut = 0 return target_trigger
def migrateTableForeignKeyToMySQL(self, state, source_fk, targetTable): target_fk = GenericMigration.migrateTableForeignKeyToMySQL(self, state, source_fk, targetTable) ### TODO: migrate constraints return target_fk
def migrateUpdateForChanges(self, state, target_catalog): """ Create datatype cast expression for target column based on source datatype. """ for targetSchema in target_catalog.schemata: for targetTable in targetSchema.tables: for target_column in targetTable.columns: # SQL expression to use for converting the column data to the target type # eg.: CAST(? as NVARCHAR(max)) type_cast_expression = None source_datatype = None source_column = state.lookupSourceObject(target_column) if source_column: source_datatype = GenericMigration.getColumnDataType( self, source_column) if source_column and source_datatype: target_datatype = target_column.simpleType.name.upper() if source_datatype in ['VARCHAR', 'NVARCHAR']: if source_datatype == 'VARCHAR': if target_column.length > 4000 or target_column.length == -1: # NVARCHAR is limited to 4000 - if you need more, you must use MAX insted BUG #18167872 type_cast_expression = "CAST(? as NVARCHAR(MAX))" # If in source table is VARCHAR(MAX) column lnegth is 0 - so must be casted to NVARCHAR(MAX) BUG #18105486 else: type_cast_expression = "CAST(? as NVARCHAR(%d))" % target_column.length elif source_datatype in ['TEXT', 'NTEXT']: if source_datatype == 'TEXT': type_cast_expression = "CAST(? as NTEXT)" elif source_datatype in [ 'CHAR', 'NCHAR' ]: # MSSQL CHAR's (also VARCHAR's) max length is 8000 non Unicode characters if source_datatype == 'CHAR': type_cast_expression = "CAST(? as NCHAR(%d))" % target_column.length elif source_datatype == 'UNIQUEIDENTIFIER': type_cast_expression = "CAST(? as VARCHAR(64))" elif source_datatype == 'SYSNAME': # the relevant info is in http://msdn.microsoft.com/en-us/library/ms191240(v=sql.105).aspx type_cast_expression = "CAST(? as VARCHAR(128))" # floating point datatypes: elif source_datatype in ['DECIMAL', 'NUMERIC']: if source_column.scale == 0: type_cast_expression = "CAST(? as %s)" % ( 'INT' if target_datatype == 'MEDIUMINT' else target_datatype) elif source_datatype == 'XML': type_cast_expression = "CAST(? as NVARCHAR(max))" elif source_datatype in ['GEOMETRY', 'GEOGRAPHY']: type_cast_expression = '?.STAsText()' elif source_datatype == 'HIERARCHYID': type_cast_expression = "CAST(? as VARCHAR(max))" elif source_datatype == 'SQL_VARIANT': type_cast_expression = "CAST(? as NVARCHAR(max))" elif source_datatype in [ 'BINARY', 'VARBINARY', 'TIMESTAMP', 'ROWVERSION' ]: type_cast_expression = 'CONVERT(VARBINARY(MAX), ?, 0)' if type_cast_expression: target_column.owner.customData[ "columnTypeCastExpression:%s" % target_column. name] = "%s as ?" % type_cast_expression return target_catalog
def migrateRoutineToMySQL(self, state, source_routine, target_schema): target_routine = GenericMigration.migrateRoutineToMySQL( self, state, source_routine, target_schema) target_routine.commentedOut = 0 return target_routine
def migrateRoutineToMySQL(self, state, source_routine, target_schema): target_routine = GenericMigration.migrateRoutineToMySQL(self, state, source_routine, target_schema) target_routine.commentedOut = 0 return target_routine
def migrateViewToMySQL(self, state, source_view, target_schema): target_view = GenericMigration.migrateViewToMySQL(self, state, source_view, target_schema) target_view.commentedOut = 0 return target_view
def migrateTriggerToMySQL(self, state, source_trigger, target_table): target_trigger = GenericMigration.migrateTriggerToMySQL(self, state, source_trigger, target_table) target_trigger.commentedOut = 0 return target_trigger
def migrateViewToMySQL(self, state, source_view, target_schema): target_view = GenericMigration.migrateViewToMySQL( self, state, source_view, target_schema) target_view.commentedOut = 0 return target_view