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: