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 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 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 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 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
