def add_column(self, table_name, column, schema=None):
nullable = True
pk = False
if not column.nullable:
nullable = False
column.nullable = True
if column.primary_key:
pk = True
column.primary_key = False
super(IbmDbImpl, self).add_column(table_name, column, schema)
if not nullable:
self._exec(base.ColumnNullable(table_name, column.name, nullable, schema=schema))
if pk:
db2reflector = ibm_db_reflection.DB2Reflector(self.bind.dialect)
other_pk = db2reflector.get_primary_keys(self.connection, table_name, schema)
if other_pk:
self._exec("ALTER TABLE %s DROP PRIMARY KEY" % (
base.format_table_name(self.dialect.ddl_compiler(self.dialect, None), table_name, schema)))
try:
self._exec("ALTER TABLE %s ALTER COLUMN %s DROP IDENTITY" % (
base.format_table_name(self.dialect.ddl_compiler(self.dialect, None), table_name, schema),
base.format_column_name(self.dialect.ddl_compiler(self.dialect, None), column.name)))
except Exception:
pass #Continue since identity does not exist.
pk_sql = "ALTER TABLE %s ADD PRIMARY KEY(%s)" % (
base.format_table_name(self.dialect.ddl_compiler(self.dialect, None), table_name, schema),
base.format_column_name(self.dialect.ddl_compiler(self.dialect, None), column.name))
self._exec(pk_sql)
def add_column(self, table_name, column, schema=None):
nullable = True
pk = False
if not column.nullable:
nullable = False
column.nullable = True
if column.primary_key:
pk = True
column.primary_key = False
super(IbmDbImpl, self).add_column(table_name, column, schema)
if not nullable:
self._exec(
base.ColumnNullable(table_name,
column.name,
nullable,
schema=schema))
if pk:
db2reflector = ibm_db_reflection.DB2Reflector(self.bind.dialect)
other_pk = db2reflector.get_primary_keys(self.connection,
table_name, schema)
if other_pk:
self._exec("ALTER TABLE %s DROP PRIMARY KEY" %
(base.format_table_name(
self.dialect.ddl_compiler(self.dialect, None),
table_name, schema)))
try:
self._exec(
"ALTER TABLE %s ALTER COLUMN %s DROP IDENTITY" %
(base.format_table_name(
self.dialect.ddl_compiler(self.dialect, None),
table_name, schema),
base.format_column_name(
self.dialect.ddl_compiler(self.dialect, None),
column.name)))
except Exception:
pass #Continue since identity does not exist.
pk_sql = "ALTER TABLE %s ADD PRIMARY KEY(%s)" % (
base.format_table_name(
self.dialect.ddl_compiler(self.dialect, None), table_name,
schema),
base.format_column_name(
self.dialect.ddl_compiler(self.dialect, None),
column.name))
self._exec(pk_sql)
def visit_column_comment(element, compiler, **kw):
ddl = "ALTER TABLE {table_name} COMMENT COLUMN {column_name} {comment}"
comment = (compiler.sql_compiler.render_literal_value(
element.comment or '', sqltypes.String()))
return ddl.format(
table_name=format_table_name(compiler, element.table_name,
element.schema),
column_name=format_column_name(compiler, element.column_name),
comment=comment,
)
def visit_rename_column(element, compiler, **kw):
return "EXEC sp_rename '%s.%s', '%s', 'COLUMN'" % (
format_table_name(compiler, element.table_name, element.schema),
format_column_name(compiler, element.column_name),
format_column_name(compiler, element.newname)
)
def visit_rename_column(element, compiler, **kw):
return "EXEC sp_rename '%s.%s', '%s', 'COLUMN'" % (
format_table_name(compiler, element.table_name, element.schema),
format_column_name(compiler, element.column_name),
format_column_name(compiler, element.newname))
def alter_column(self, table_name, column_name,
nullable=None,
server_default=False,
name=None,
type_=None,
schema=None,
autoincrement=None,
existing_type=None,
existing_server_default=None,
existing_nullable=None,
existing_autoincrement=None
):
primary_key_columns = None
deferred_primary_key = None
if type_ or name:
insp = reflection.Inspector.from_engine(self.bind.engine)
primary_key_columns = insp.get_pk_constraint(table_name, schema).get('constrained_columns')
if autoincrement is not None or existing_autoincrement is not None:
util.warn("autoincrement and existing_autoincrement only make sense for MySQL")
if nullable is not None:
self._exec(base.ColumnNullable(table_name, column_name,
nullable, schema=schema,
existing_type=existing_type,
existing_server_default=existing_server_default,
existing_nullable=existing_nullable,
))
if server_default is not False:
self._exec(base.ColumnDefault(
table_name, column_name, server_default,
schema=schema,
existing_type=existing_type,
existing_server_default=existing_server_default,
existing_nullable=existing_nullable,
))
if type_ is not None:
if primary_key_columns and column_name.lower() in primary_key_columns:
self._exec("ALTER TABLE %s DROP PRIMARY KEY" %
(base.format_table_name(self.dialect.ddl_compiler(self.dialect, None), table_name, schema)))
try:
self._exec("ALTER TABLE %s ALTER COLUMN %s DROP IDENTITY" %
(base.format_table_name(self.dialect.ddl_compiler(self.dialect, None), table_name, schema),
base.format_column_name(self.dialect.ddl_compiler(self.dialect, None), column_name)))
except Exception:
pass #Continue since identity does not exist.
if name is not None:
primary_key_columns.remove(column_name.lower())
primary_key_columns.add(name.lower())
deferred_primary_key = True
self._exec(base.ColumnType(
table_name, column_name, type_, schema=schema,
existing_type=existing_type,
existing_server_default=existing_server_default,
existing_nullable=existing_nullable,
))
if name is not None:
if primary_key_columns and not deferred_primary_key and column_name.lower() in primary_key_columns:
self._exec("ALTER TABLE %s DROP PRIMARY KEY" %
(base.format_table_name(self.dialect.ddl_compiler(self.dialect, None), table_name, schema)))
primary_key_deferred = True
self._exec(base.ColumnName(
table_name, column_name, name, schema=schema,
existing_type=existing_type,
existing_server_default=existing_server_default,
existing_nullable=existing_nullable,
))
if deferred_primary_key:
self._exec("Alter TABLE %s ADD PRIMARY KEY(%s)" %
(base.format_table_name(self.dialect.ddl_compiler(self.dialect, None), table_name, schema),
','.join(base.format_column_name(self.dialect.ddl_compiler(self.dialect, None), col) for col in primary_key_columns)))
def visit_rename_table(element, compiler, **kw):
return "RENAME TABLE %s TO %s" % (
base.format_table_name(compiler, element.table_name, element.schema),
base.format_table_name(compiler, element.new_table_name, element.schema)
)
def alter_column(self,
table_name,
column_name,
nullable=None,
server_default=False,
name=None,
type_=None,
schema=None,
autoincrement=None,
existing_type=None,
existing_server_default=None,
existing_nullable=None,
existing_autoincrement=None):
primary_key_columns = None
deferred_primary_key = None
if type_ or name:
insp = reflection.Inspector.from_engine(self.connection)
primary_key_columns = insp.get_pk_constraint(
table_name, schema).get('constrained_columns')
if autoincrement is not None or existing_autoincrement is not None:
util.warn(
"autoincrement and existing_autoincrement only make sense for MySQL"
)
if nullable is not None:
self._exec(
base.ColumnNullable(
table_name,
column_name,
nullable,
schema=schema,
existing_type=existing_type,
existing_server_default=existing_server_default,
existing_nullable=existing_nullable,
))
if server_default is not False:
self._exec(
base.ColumnDefault(
table_name,
column_name,
server_default,
schema=schema,
existing_type=existing_type,
existing_server_default=existing_server_default,
existing_nullable=existing_nullable,
))
if type_ is not None:
if isinstance(type_, sa_types.Enum):
try:
self._exec(
"ALTER TABLE %s DROP CHECK %s" %
(base.format_table_name(
self.dialect.ddl_compiler(self.dialect, None),
table_name, schema), type_.name))
except Exception:
pass #continue since check constraint doesn't exist
if primary_key_columns and column_name.lower(
) in primary_key_columns:
self._exec("ALTER TABLE %s DROP PRIMARY KEY" %
(base.format_table_name(
self.dialect.ddl_compiler(self.dialect, None),
table_name, schema)))
try:
self._exec(
"ALTER TABLE %s ALTER COLUMN %s DROP IDENTITY" %
(base.format_table_name(
self.dialect.ddl_compiler(self.dialect, None),
table_name, schema),
base.format_column_name(
self.dialect.ddl_compiler(self.dialect, None),
column_name)))
except Exception:
pass #Continue since identity does not exist.
if name is not None:
primary_key_columns.remove(column_name.lower())
primary_key_columns.add(name.lower())
deferred_primary_key = True
self._exec(
base.ColumnType(
table_name,
column_name,
type_,
schema=schema,
existing_type=existing_type,
existing_server_default=existing_server_default,
existing_nullable=existing_nullable,
))
if name is not None:
if primary_key_columns and not deferred_primary_key and column_name.lower(
) in primary_key_columns:
self._exec("ALTER TABLE %s DROP PRIMARY KEY" %
(base.format_table_name(
self.dialect.ddl_compiler(self.dialect, None),
table_name, schema)))
primary_key_deferred = True
self._exec(
base.ColumnName(
table_name,
column_name,
name,
schema=schema,
existing_type=existing_type,
existing_server_default=existing_server_default,
existing_nullable=existing_nullable,
))
if deferred_primary_key:
self._exec(
"Alter TABLE %s ADD PRIMARY KEY(%s)" % (base.format_table_name(
self.dialect.ddl_compiler(self.dialect, None), table_name,
schema), ','.join(
base.format_column_name(
self.dialect.ddl_compiler(self.dialect, None), col)
for col in primary_key_columns)))
def visit_rename_table(element, compiler, **kw):
return "RENAME TABLE %s TO %s" % (
base.format_table_name(compiler, element.table_name, element.schema),
base.format_table_name(compiler, element.new_table_name,
element.schema))