parameters: (self, state, target_catalog)
return_statement: return target_catalog
docstring: Create datatype cast expression for target column based on source datatype.
docstring_summary: Create datatype cast expression for target column based on source datatype.
docstring_tokens: Create datatype cast expression for target column based on source datatype .
function: 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
function_tokens: def migrateUpdateForChanges ( self , state , target_catalog ) : 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