def visit_column(self, column):
"""Create a column (table already exists).
:param column: column object
:type column: :class:`sqlalchemy.Column` instance
"""
if column.default is not None:
self.traverse_single(column.default)
table = self.start_alter_table(column)
self.append("ADD ")
self.append(self.get_column_specification(column))
for cons in column.constraints:
self.traverse_single(cons)
self.execute()
# ALTER TABLE STATEMENTS
# add indexes and unique constraints
if column.index_name:
Index(column.index_name, column).create()
elif column.unique_name:
constraint.UniqueConstraint(column,
name=column.unique_name).create()
# SA bounds FK constraints to table, add manually
for fk in column.foreign_keys:
self.add_foreignkey(fk.constraint)
# add primary key constraint if needed
if column.primary_key_name:
cons = constraint.PrimaryKeyConstraint(
column, name=column.primary_key_name)
cons.create()
def visit_column(self, column):
nullable = True
if not column.nullable:
nullable = False
column.nullable = True
table = self.start_alter_table(column)
self.append("ADD COLUMN ")
self.append(self.get_column_specification(column))
for cons in column.constraints:
self.traverse_single(cons)
if column.default is not None:
self.traverse_single(column.default)
self.execute()
#ALTER TABLE STATEMENTS
if not nullable:
self.start_alter_table(column)
self.append("ALTER COLUMN %s SET NOT NULL" %
self.preparer.format_column(column))
self.execute()
self.append("CALL SYSPROC.ADMIN_CMD('REORG TABLE %s')" %
self.preparer.format_table(table))
self.execute()
# add indexes and unique constraints
if column.index_name:
Index(column.index_name, column).create()
elif column.unique_name:
constraint.UniqueConstraint(column,
name=column.unique_name).create()
# SA bounds FK constraints to table, add manually
for fk in column.foreign_keys:
self.add_foreignkey(fk.constraint)
# add primary key constraint if needed
if column.primary_key_name:
pk = constraint.PrimaryKeyConstraint(
column, name=column.primary_key_name)
pk.create()
self.append("COMMIT")
self.execute()
self.append("CALL SYSPROC.ADMIN_CMD('REORG TABLE %s')" %
self.preparer.format_table(table))
self.execute()
def upgrade(migrate_engine):
if migrate_engine.name == 'sqlite':
upgrade_sqlite(migrate_engine)
return
meta = sqlalchemy.MetaData(bind=migrate_engine)
event_table = sqlalchemy.Table('event', meta, autoload=True)
event_uuid = sqlalchemy.Column('uuid',
sqlalchemy.String(length=36),
default=lambda: str(uuid.uuid4))
event_table.create_column(event_uuid)
if migrate_engine.name == 'postgresql':
sequence = sqlalchemy.Sequence('evt')
sqlalchemy.schema.CreateSequence(sequence,
bind=migrate_engine).execute()
event_id = sqlalchemy.Column(
'tmp_id',
sqlalchemy.Integer,
server_default=sqlalchemy.text("nextval('evt')"))
else:
event_id = sqlalchemy.Column('tmp_id', sqlalchemy.Integer)
event_table.create_column(event_id)
fake_autoincrement = itertools.count(1)
event_list = event_table.select().order_by(
sqlalchemy.sql.expression.asc(
event_table.c.created_at)).execute().fetchall()
for event in event_list:
values = {'tmp_id': fake_autoincrement.next(), 'uuid': event.id}
update = event_table.update().where(
event_table.c.id == event.id).values(values)
migrate_engine.execute(update)
cons = constraint.UniqueConstraint('uuid', table=event_table)
cons.create()
event_table.c.id.drop()
event_table.c.tmp_id.alter('id', sqlalchemy.Integer)
cons = constraint.PrimaryKeyConstraint('tmp_id', table=event_table)
cons.create()
event_table.c.tmp_id.alter(sqlalchemy.Integer, autoincrement=True)
def upgrade(migrate_engine):
"""Update quota_usages table to prevent races on creation.
Add race_preventer field and a unique constraint to prevent quota usage
duplicates and races that mess the quota system when first creating rows.
"""
# There's no need to set the race_preventer field for existing DB entries,
# since the race we want to prevent is only on creation.
meta = MetaData(bind=migrate_engine)
quota_usages = Table('quota_usages', meta, autoload=True)
if not hasattr(quota_usages.c, 'race_preventer'):
quota_usages.create_column(
Column('race_preventer', Boolean, nullable=True))
unique = constraint.UniqueConstraint('project_id',
'resource',
'race_preventer',
table=quota_usages)
unique.create(engine=migrate_engine)
tenant = {}
tenant['id'] = sqlalchemy.Column('id',
sqlalchemy.Integer,
primary_key=True,
autoincrement=True)
tenant['uid'] = sqlalchemy.Column('uid',
sqlalchemy.String(255),
unique=False,
nullable=True)
tenant['name'] = sqlalchemy.Column('name', sqlalchemy.String(255), unique=True)
tenant['desc'] = sqlalchemy.Column('desc', sqlalchemy.String(255))
tenant['enabled'] = sqlalchemy.Column('enabled', sqlalchemy.Integer)
tenants = sqlalchemy.Table('tenants', meta, *tenant.values())
unique_constraint = constraint.UniqueConstraint(tenant['uid'])
def upgrade(migrate_engine):
meta.bind = migrate_engine
tenant['uid'].alter(nullable=False)
assert not tenants.c.uid.nullable
unique_constraint.create()
def downgrade(migrate_engine):
meta.bind = migrate_engine
try:
# define the previous state of users
user = {}
user['id'] = sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True,
autoincrement=True)
user['uid'] = sqlalchemy.Column('uid', sqlalchemy.String(255), unique=False,
nullable=False)
user['name'] = sqlalchemy.Column('name', sqlalchemy.String(255), unique=True)
user['password'] = sqlalchemy.Column('password', sqlalchemy.String(255))
user['email'] = sqlalchemy.Column('email', sqlalchemy.String(255))
user['enabled'] = sqlalchemy.Column('enabled', sqlalchemy.Integer)
user['tenant_id'] = sqlalchemy.Column('tenant_id', sqlalchemy.Integer)
users = sqlalchemy.Table('users', meta, *user.values())
constraint.UniqueConstraint(user['uid'])
tenant = {}
tenant['id'] = sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True,
autoincrement=True)
tenant['uid'] = sqlalchemy.Column('uid', sqlalchemy.String(255), unique=False,
nullable=False)
tenant['name'] = sqlalchemy.Column('name', sqlalchemy.String(255), unique=True)
tenant['desc'] = sqlalchemy.Column('desc', sqlalchemy.String(255))
tenant['enabled'] = sqlalchemy.Column('enabled', sqlalchemy.Integer)
tenants = sqlalchemy.Table('tenants', meta, *tenant.values())
constraint.UniqueConstraint(tenant['uid'])
def upgrade(migrate_engine):
meta.bind = migrate_engine
def upgrade_resource(migrate_engine):
meta = sqlalchemy.MetaData(bind=migrate_engine)
res_table = sqlalchemy.Table('resource', meta, autoload=True)
res_uuid_column_kwargs = {}
if migrate_engine.name == 'ibm_db_sa':
# NOTE(mriedem): DB2 10.5 doesn't support unique constraints over
# nullable columns, it creates a unique index instead, so we have
# to make the uuid column non-nullable in the DB2 case.
res_uuid_column_kwargs['nullable'] = False
res_uuid = sqlalchemy.Column('uuid',
sqlalchemy.String(length=36),
default=lambda: str(uuid.uuid4),
**res_uuid_column_kwargs)
res_table.create_column(res_uuid)
if migrate_engine.name == 'postgresql':
sequence = sqlalchemy.Sequence('res')
sqlalchemy.schema.CreateSequence(sequence,
bind=migrate_engine).execute()
res_id = sqlalchemy.Column(
'tmp_id',
sqlalchemy.Integer,
server_default=sqlalchemy.text("nextval('res')"))
else:
res_id_column_kwargs = {}
if migrate_engine.name == 'ibm_db_sa':
# NOTE(mriedem): This is turned into a primary key constraint
# later so it must be non-nullable.
res_id_column_kwargs['nullable'] = False
res_id = sqlalchemy.Column('tmp_id', sqlalchemy.Integer,
**res_id_column_kwargs)
res_table.create_column(res_id)
fake_autoincrement = itertools.count(1)
res_list = res_table.select().order_by(
sqlalchemy.sql.expression.asc(
res_table.c.created_at)).execute().fetchall()
for res in res_list:
values = {'tmp_id': fake_autoincrement.next(), 'uuid': res.id}
update = res_table.update().where(
res_table.c.id == res.id).values(values)
migrate_engine.execute(update)
constraint_kwargs = {'table': res_table}
if migrate_engine.name == 'ibm_db_sa':
# NOTE(mriedem): DB2 gives a random name to the unique constraint
# if one is not provided so let's set the standard name ourselves.
constraint_kwargs['name'] = 'uniq_resource0uuid0'
cons = constraint.UniqueConstraint('uuid', **constraint_kwargs)
cons.create()
if migrate_engine.name == 'postgresql':
# resource_id_seq will be dropped in the case of removing `id` column
# set owner to none for saving this sequence (it is needed in the
# earlier migration)
migrate_engine.execute('alter sequence resource_id_seq owned by none')
res_table.c.id.drop()
alter_kwargs = {}
if migrate_engine.name == 'ibm_db_sa':
alter_kwargs['nullable'] = False
res_table.c.tmp_id.alter('id', sqlalchemy.Integer, **alter_kwargs)
cons = constraint.PrimaryKeyConstraint('tmp_id', table=res_table)
cons.create()
if migrate_engine.name == 'ibm_db_sa':
# NOTE(chenxiao): For DB2, setting "ID" column "autoincrement=True"
# can't make sense after above "tmp_id=>id" transformation,
# so should work around it.
sql = ("ALTER TABLE RESOURCE ALTER COLUMN ID SET GENERATED BY "
"DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1)")
migrate_engine.execute(sql)
else:
res_table.c.tmp_id.alter(sqlalchemy.Integer, autoincrement=True)
user['id'] = sqlalchemy.Column('id',
sqlalchemy.Integer,
primary_key=True,
autoincrement=True)
user['uid'] = sqlalchemy.Column('uid',
sqlalchemy.String(255),
unique=False,
nullable=True)
user['name'] = sqlalchemy.Column('name', sqlalchemy.String(255), unique=True)
user['password'] = sqlalchemy.Column('password', sqlalchemy.String(255))
user['email'] = sqlalchemy.Column('email', sqlalchemy.String(255))
user['enabled'] = sqlalchemy.Column('enabled', sqlalchemy.Integer)
user['tenant_id'] = sqlalchemy.Column('tenant_id', sqlalchemy.Integer)
users = sqlalchemy.Table('users', meta, *user.values())
unique_constraint = constraint.UniqueConstraint(user['uid'])
def upgrade(migrate_engine):
meta.bind = migrate_engine
user['uid'].alter(nullable=False)
assert not users.c.uid.nullable
unique_constraint.create()
def downgrade(migrate_engine):
meta.bind = migrate_engine
try:
