def add_constraint(self, constraint):
if self._dbengine.dialect.name != 'sqlite':
# It is impossible to add constraints to a pre-existing table for
# SQLite
# source: http://www.sqlite.org/omitted.html
create_constraint = AddConstraint(constraint, bind=self._dbengine)
create_constraint.execute()
def add_constraint(self, constraint):
if self._dbengine.dialect.name != 'sqlite':
# It is impossible to add constraints to a pre-existing table for
# SQLite
# source: http://www.sqlite.org/omitted.html
create_constraint = AddConstraint(constraint, bind=self._dbengine)
create_constraint.execute()
def test_conditional_constraint_deprecated(self):
metadata, users, engine = self.metadata, self.users, self.engine
nonpg_mock = engines.mock_engine(dialect_name='sqlite')
pg_mock = engines.mock_engine(dialect_name='postgresql')
constraint = CheckConstraint('a < b', name='my_test_constraint'
, table=users)
# by placing the constraint in an Add/Drop construct, the
# 'inline_ddl' flag is set to False
AddConstraint(constraint, on='postgresql'
).execute_at('after-create', users)
DropConstraint(constraint, on='postgresql'
).execute_at('before-drop', users)
metadata.create_all(bind=nonpg_mock)
strings = ' '.join(str(x) for x in nonpg_mock.mock)
assert 'my_test_constraint' not in strings
metadata.drop_all(bind=nonpg_mock)
strings = ' '.join(str(x) for x in nonpg_mock.mock)
assert 'my_test_constraint' not in strings
metadata.create_all(bind=pg_mock)
strings = ' '.join(str(x) for x in pg_mock.mock)
assert 'my_test_constraint' in strings
metadata.drop_all(bind=pg_mock)
strings = ' '.join(str(x) for x in pg_mock.mock)
assert 'my_test_constraint' in strings
def forwards(apps, schema_editor):
"""
Ensure all data tables have the new geo_version column, with a default of ''
"""
session = get_session()
inspector = inspect(session.bind)
try:
for data_table in DATA_TABLES.itervalues():
db_model = data_table.model
table = db_model.__table__
cols = [c['name'] for c in inspector.get_columns(table.name)]
if 'geo_version' in cols:
continue
# remove the old primary key constraint, if any
pk = inspector.get_pk_constraint(table.name)['name']
if pk:
session.execute("ALTER TABLE %s DROP CONSTRAINT %s" %
(table.name, pk))
# add the new column
session.execute(
"ALTER TABLE %s ADD COLUMN geo_version VARCHAR(100) DEFAULT ''"
% table.name)
# add the correct new constraint
session.execute(AddConstraint(table.primary_key))
session.commit()
finally:
session.close()
def _add_constraint_in_db(
self,
constraint: ConstraintOrIndex,
errors: str = 'raise',
) -> None:
assert errors in _VALID_ERRORS_OPTIONS
if self._constraint_already_active(constraint):
return
if constraint.table.name not in self._reflected_table_lookup:
logger.warning(f'Cannot add {constraint.name}, '
f'table {constraint.table.name} does not exist')
return
with self._db.engine.connect() as conn:
logger.info(f'Adding {constraint.name}')
try:
if isinstance(constraint, Index):
conn.execute(CreateIndex(constraint))
else:
# We add a copy instead of the original constraint.
# Otherwise, when you later call metadata.create_all
# to create tables, SQLAlchemy thinks the
# constraints have already been created and skips
# them.
c = copy(constraint)
conn.execute(AddConstraint(c))
except SQLAlchemyError:
if errors == 'raise':
raise
elif errors == 'ignore':
logger.info(f'Unable to add {constraint.name}')
def test_conditional_constraint(self):
metadata, users, engine = self.metadata, self.users, self.engine
nonpg_mock = engines.mock_engine(dialect_name="sqlite")
pg_mock = engines.mock_engine(dialect_name="postgresql")
constraint = CheckConstraint("a < b",
name="my_test_constraint",
table=users)
# by placing the constraint in an Add/Drop construct, the
# 'inline_ddl' flag is set to False
event.listen(
users,
"after_create",
AddConstraint(constraint).execute_if(dialect="postgresql"),
)
event.listen(
users,
"before_drop",
DropConstraint(constraint).execute_if(dialect="postgresql"),
)
metadata.create_all(bind=nonpg_mock)
strings = " ".join(str(x) for x in nonpg_mock.mock)
assert "my_test_constraint" not in strings
metadata.drop_all(bind=nonpg_mock)
strings = " ".join(str(x) for x in nonpg_mock.mock)
assert "my_test_constraint" not in strings
metadata.create_all(bind=pg_mock)
strings = " ".join(str(x) for x in pg_mock.mock)
assert "my_test_constraint" in strings
metadata.drop_all(bind=pg_mock)
strings = " ".join(str(x) for x in pg_mock.mock)
assert "my_test_constraint" in strings
def reverse(apps, schema_editor):
"""
Drop the new geo_version column from all data tables
"""
session = get_session()
inspector = inspect(session.bind)
try:
for data_table in DATA_TABLES.itervalues():
db_model = data_table.model
table = db_model.__table__
# remove the primary key constraint, if any
pk = inspector.get_pk_constraint(table.name)['name']
if pk:
session.execute("ALTER TABLE %s DROP CONSTRAINT %s" %
(table.name, pk))
# drop the new column
session.execute("ALTER TABLE %s DROP COLUMN geo_version" %
table.name)
# add the old pk constraint
pk = table.primary_key
pk.columns.remove(table.c.geo_version)
session.execute(AddConstraint(pk))
session.commit()
finally:
session.close()
def _update_states_table_with_foreign_key_options(engine):
"""Add the options to foreign key constraints."""
inspector = reflection.Inspector.from_engine(engine)
alters = []
for foreign_key in inspector.get_foreign_keys(TABLE_STATES):
if foreign_key["name"] and not foreign_key["options"]:
alters.append({
"old_fk":
ForeignKeyConstraint((), (), name=foreign_key["name"]),
"columns":
foreign_key["constrained_columns"],
})
if not alters:
return
states_key_constraints = Base.metadata.tables[
TABLE_STATES].foreign_key_constraints
old_states_table = Table( # noqa: F841 pylint: disable=unused-variable
TABLE_STATES, MetaData(), *[alter["old_fk"] for alter in alters])
for alter in alters:
try:
engine.execute(DropConstraint(alter["old_fk"]))
for fkc in states_key_constraints:
if fkc.column_keys == alter["columns"]:
engine.execute(AddConstraint(fkc))
except (InternalError, OperationalError):
_LOGGER.exception("Could not update foreign options in %s table",
TABLE_STATES)
def _update_states_table_with_foreign_key_options(connection, engine):
"""Add the options to foreign key constraints."""
inspector = sqlalchemy.inspect(engine)
alters = []
for foreign_key in inspector.get_foreign_keys(TABLE_STATES):
if foreign_key["name"] and (
# MySQL/MariaDB will have empty options
not foreign_key.get("options") or
# Postgres will have ondelete set to None
foreign_key.get("options", {}).get("ondelete") is None):
alters.append({
"old_fk":
ForeignKeyConstraint((), (), name=foreign_key["name"]),
"columns":
foreign_key["constrained_columns"],
})
if not alters:
return
states_key_constraints = Base.metadata.tables[
TABLE_STATES].foreign_key_constraints
old_states_table = Table( # noqa: F841 pylint: disable=unused-variable
TABLE_STATES, MetaData(), *(alter["old_fk"] for alter in alters))
for alter in alters:
try:
connection.execute(DropConstraint(alter["old_fk"]))
for fkc in states_key_constraints:
if fkc.column_keys == alter["columns"]:
connection.execute(AddConstraint(fkc))
except (InternalError, OperationalError):
_LOGGER.exception("Could not update foreign options in %s table",
TABLE_STATES)
def test_conditional_constraint_deprecated(self):
metadata, users = self.metadata, self.users
nonpg_mock = engines.mock_engine(dialect_name="sqlite")
pg_mock = engines.mock_engine(dialect_name="postgresql")
constraint = CheckConstraint("a < b",
name="my_test_constraint",
table=users)
# by placing the constraint in an Add/Drop construct, the
# 'inline_ddl' flag is set to False
AddConstraint(constraint,
on="postgresql").execute_at("after-create", users)
DropConstraint(constraint,
on="postgresql").execute_at("before-drop", users)
metadata.create_all(bind=nonpg_mock)
strings = " ".join(str(x) for x in nonpg_mock.mock)
assert "my_test_constraint" not in strings
metadata.drop_all(bind=nonpg_mock)
strings = " ".join(str(x) for x in nonpg_mock.mock)
assert "my_test_constraint" not in strings
metadata.create_all(bind=pg_mock)
strings = " ".join(str(x) for x in pg_mock.mock)
assert "my_test_constraint" in strings
metadata.drop_all(bind=pg_mock)
strings = " ".join(str(x) for x in pg_mock.mock)
assert "my_test_constraint" in strings
def create_fks(self):
"""Create foreign key constraints on PostgreSQL table"""
for fk in self.foreign_keys:
try:
self.logger.info("Creating foreign key {}".format(fk.name))
self.conn.execute(AddConstraint(fk))
except SQLAlchemyError:
self.logger.warn("Error creating foreign key {}".format(
fk.name))
def create_fk(db):
from sqlalchemy.schema import AddConstraint
from sqlalchemy import text
connection = db.get_new_connection('create_fk')
connection.execute(text("START TRANSACTION"))
for table in build_metadata().sorted_tables:
for fk in table.foreign_keys:
connection.execute(AddConstraint(fk.constraint))
connection.execute(text("COMMIT"))
def constraint_ddl(tables, engine, drop=False):
output = []
for table in tables:
for constraint in table.constraints:
# Avoid duplicating primary key constraint definitions (they are
# included in CREATE TABLE statements).
if not isinstance(constraint, PrimaryKeyConstraint):
if not drop:
ddl = AddConstraint(constraint)
else:
ddl = DropConstraint(constraint)
output.append(str(ddl.compile(dialect=engine.dialect)).strip())
output.append(';\n\n')
return output
def create_table_constraints_ddl(tableObj, engine, debug_level=-1):
ddl_text = ''
constraints = tableObj.constraints
for constraint in constraints:
ddl_obj = AddConstraint(constraint)
try:
ddl_string = str(ddl_obj.compile(dialect=engine.dialect)).strip()
except Exception as e:
# if e.message.find('it has no name')<0:
# print(e)
ddl_string = ''
if ddl_string:
if not ddl_text:
ddl_text = ddl_string
else:
ddl_text = ddl_text + '\n' + ddl_string
ddl_string = ddl_text
if int(debug_level) > 0:
msg = f"table [[{tableObj.name}]] create_table_constraints_DDL: [{ddl_string}]"
log_message(msg)
return ddl_string
def constraint_ddl(tables, engine, drop=False):
output = []
for table in tables:
constraints = sorted(list(table.constraints), key=lambda k: k.name,
reverse=drop)
for constraint in constraints:
# Avoid duplicating primary key constraint definitions (they are
# included in CREATE TABLE statements).
if not isinstance(constraint, PrimaryKeyConstraint):
if not drop:
ddl = AddConstraint(constraint)
else:
ddl = DropConstraint(constraint)
output.append(str(ddl.compile(dialect=engine.dialect)).strip())
output.append(';\n\n')
return output
def restore_foreign_keys(table_name, engine):
import models
orig_meta = models.Base.metadata.tables[table_name]
constraints = list(orig_meta.constraints)
fks = [
item for item in constraints if isinstance(item, ForeignKeyConstraint)
]
connection = engine.connect()
transaction = connection.begin()
for fkc in fks:
connection.execute(AddConstraint(fkc))
transaction.commit()
connection.close()
def downgrade(migrate_engine):
meta = sqlalchemy.MetaData()
meta.bind = migrate_engine
if migrate_engine.name not in ['mysql', 'postgresql']:
return
image_properties = Table('image_properties', meta, autoload=True)
image_members = Table('image_members', meta, autoload=True)
images = Table('images', meta, autoload=True)
if migrate_engine.name == 'postgresql':
constraint = UniqueConstraint(image_properties.c.image_id,
image_properties.c.name,
name='ix_image_properties_image_id_name')
migrate_engine.execute(DropConstraint(constraint))
constraint = UniqueConstraint(image_properties.c.image_id,
image_properties.c.name)
migrate_engine.execute(AddConstraint(constraint))
index = Index('ix_image_properties_image_id_name',
image_properties.c.image_id, image_properties.c.name)
migrate_engine.execute(CreateIndex(index))
images.c.id.alter(
server_default=Sequence('images_id_seq').next_value())
if migrate_engine.name == 'mysql':
constraint = UniqueConstraint(image_properties.c.image_id,
image_properties.c.name,
name='image_id')
migrate_engine.execute(AddConstraint(constraint))
image_members.c.status.alter(nullable=True, server_default=None)
images.c.protected.alter(nullable=True, server_default=None)
def create_table_constraints(self,
table,
_include_foreign_key_constraints=None):
# EXASOL does not support FK constraints that reference
# the table being created. Thus, these need to be created
# via ALTER TABLE after table creation
# TODO: FKs that reference other tables could be inlined
# the create rule could be more specific but for now, ALTER
# TABLE for all FKs work.
for c in [
c for c in table._sorted_constraints
if isinstance(c, ForeignKeyConstraint)
]:
c._create_rule = lambda: False
event.listen(table, "after_create", AddConstraint(c))
return super(EXADDLCompiler, self).create_table_constraints(table)
def __copy_constraints(self):
"""
Migrates constraints, UKs, CCs and FKs.
"""
o_engine = create_engine(self.o_engine_conn)
d_engine = create_engine(self.d_engine_conn)
metadata = MetaData()
metadata.reflect(o_engine)
insp = inspect(o_engine)
tables = filter(
lambda x: x[0] not in self.exclude, metadata.tables.items())
for table_name, table in tables:
constraints_to_keep = []
# keep unique constraints
uks = insp.get_unique_constraints(table_name)
for uk in uks:
uk_cols = filter(
lambda c: c.name in uk["column_names"], table._columns)
uuk = UniqueConstraint(*uk_cols, name=uk["name"])
uuk._set_parent(table)
constraints_to_keep.append(uuk)
# keep check constraints
ccs = filter(
lambda cons: isinstance(
cons, CheckConstraint), table.constraints
)
for cc in ccs:
cc.sqltext = TextClause(str(cc.sqltext).replace('"', ""))
constraints_to_keep.append(cc)
# keep fks
for fk in filter(
lambda cons: isinstance(
cons, ForeignKeyConstraint), table.constraints
):
constraints_to_keep.append(fk)
# create all constraints
for cons in constraints_to_keep:
try:
d_engine.execute(AddConstraint(cons))
except Exception as e:
logger.warning(e)
def test_alter_table_distribute_by(self):
dbc = DistributeByConstraint('a', 'b')
self.tables.t.append_constraint(dbc)
config.db.execute(DropConstraint(dbc))
insp = inspect(testing.db)
for c in insp.get_columns('t'):
assert c['is_distribution_key'] == False
config.db.execute(AddConstraint(dbc))
insp = inspect(testing.db)
for c in insp.get_columns('t'):
if not (c['name'] == 'c'):
assert c['is_distribution_key'] == True
else:
assert c['is_distribution_key'] == False
def _visit_constraint(self, constraint):
constraint.name = self.get_constraint_name(constraint)
if (isinstance(constraint, UniqueConstraint) and
is_unique_constraint_with_null_columns_supported(
self.dialect)):
for column in constraint:
if column.nullable:
constraint.exclude_nulls = True
break
if getattr(constraint, 'exclude_nulls', None):
index = Index(constraint.name,
*(column for column in constraint),
unique=True)
sql = self.process(CreateIndex(index))
sql += ' EXCLUDE NULL KEYS'
else:
sql = self.process(AddConstraint(constraint))
self.append(sql)
self.execute()
def rename_vo(old_vo, new_vo, insert_new_vo=False, description=None, email=None, commit_changes=False, skip_history=False):
"""
Updates rows so that entries associated with `old_vo` are now associated with `new_vo` as part of multi-VO migration.
:param old_vo: The 3 character string for the current VO (for a single-VO instance this will be 'def').
:param new_vo: The 3 character string for the new VO.
:param insert_new_vo: If True then an entry for `new_vo` is created in the database.
:param description: Full description of the new VO, unused if `insert_new_vo` is False.
:param email: Admin email for the new VO, unused if `insert_new_vo` is False.
:param commit_changes: If True then changes are made against the database directly.
If False, then nothing is commited and the commands needed are dumped to be run later.
:param skip_history: If True then tables without FKC containing historical data will not be converted to save time.
"""
success = True
engine = session.get_engine()
conn = engine.connect()
trans = conn.begin()
inspector = reflection.Inspector.from_engine(engine)
metadata = MetaData(bind=conn, reflect=True)
dialect = engine.dialect.name
# Gather all the columns that need updating and all relevant foreign key constraints
all_fks = []
tables_and_columns = []
for table_name in inspector.get_table_names():
if skip_history and ('_history' in table_name or '_hist_recent' in table_name):
continue
fks = []
table = Table(table_name, metadata)
for column in table.c:
if 'scope' in column.name or column.name == 'account':
tables_and_columns.append((table, column))
for fk in inspector.get_foreign_keys(table_name):
if not fk['name']:
continue
if 'scope' in fk['referred_columns'] or 'account' in fk['referred_columns']:
fks.append(ForeignKeyConstraint(fk['constrained_columns'], [fk['referred_table'] + '.' + r for r in fk['referred_columns']],
name=fk['name'], table=table, **fk['options']))
all_fks.extend(fks)
try:
bound_params = {'old_vo': old_vo,
'new_vo': new_vo,
'old_vo_suffix': '' if old_vo == 'def' else old_vo,
'new_vo_suffix': '' if new_vo == 'def' else '@%s' % new_vo,
'split_character': '@',
'int_1': 1,
'int_2': 2,
'new_description': description,
'new_email': email,
'datetime': datetime.utcnow()}
bound_params_text = {}
for key in bound_params:
if isinstance(bound_params[key], int):
bound_params_text[key] = bound_params[key]
else:
bound_params_text[key] = "'%s'" % bound_params[key]
if insert_new_vo:
table = Table('vos', metadata)
insert_command = table.insert().values(vo=bindparam('new_vo'),
description=bindparam('new_description'),
email=bindparam('new_email'),
updated_at=bindparam('datetime'),
created_at=bindparam('datetime'))
print(str(insert_command) % bound_params_text + ';')
if commit_changes:
conn.execute(insert_command, bound_params)
# Drop all FKCs affecting InternalAccounts/Scopes
for fk in all_fks:
print(str(DropConstraint(fk)) + ';')
if commit_changes:
conn.execute(DropConstraint(fk))
# Update columns
for table, column in tables_and_columns:
update_command = table.update().where(split_vo(dialect, column, return_vo=True) == bindparam('old_vo_suffix'))
if new_vo == 'def':
update_command = update_command.values({column.name: split_vo(dialect, column)})
else:
update_command = update_command.values({column.name: split_vo(dialect, column) + cast(bindparam('new_vo_suffix'), CHAR(4))})
print(str(update_command) % bound_params_text + ';')
if commit_changes:
conn.execute(update_command, bound_params)
table = Table('rses', metadata)
update_command = table.update().where(table.c.vo == bindparam('old_vo')).values(vo=bindparam('new_vo'))
print(str(update_command) % bound_params_text + ';')
if commit_changes:
conn.execute(update_command, bound_params)
# Re-add the FKCs we dropped
for fkc in all_fks:
print(str(AddConstraint(fkc)) + ';')
if commit_changes:
conn.execute(AddConstraint(fkc))
except:
success = False
print(format_exc())
print('Exception occured, changes not committed to DB.')
if commit_changes and success:
trans.commit()
trans.close()
return success
def remove_vo(vo, commit_changes=False, skip_history=False):
"""
Deletes rows associated with `vo` as part of multi-VO migration.
:param vo: The 3 character string for the VO being removed from the DB.
:param commit_changes: If True then changes are made against the database directly.
If False, then nothing is commited and the commands needed are dumped to be run later.
:param skip_history: If True then tables without FKC containing historical data will not be converted to save time.
"""
success = True
engine = session.get_engine()
conn = engine.connect()
trans = conn.begin()
inspector = reflection.Inspector.from_engine(engine)
metadata = MetaData(bind=conn, reflect=True)
dialect = engine.dialect.name
# Gather all the columns that need deleting and all relevant foreign key constraints
all_fks = []
tables_and_columns = []
tables_and_columns_rse = []
for table_name in inspector.get_table_names():
if skip_history and ('_history' in table_name or '_hist_recent' in table_name):
continue
fks = []
table = Table(table_name, metadata)
for column in table.c:
if 'scope' in column.name or column.name == 'account':
tables_and_columns.append((table, column))
if 'rse_id' in column.name:
tables_and_columns_rse.append((table, column))
for fk in inspector.get_foreign_keys(table_name):
if not fk['name']:
continue
if 'scope' in fk['referred_columns'] or 'account' in fk['referred_columns'] or ('rse' in fk['referred_table'] and 'id' in fk['referred_columns']):
fks.append(ForeignKeyConstraint(fk['constrained_columns'], [fk['referred_table'] + '.' + r for r in fk['referred_columns']],
name=fk['name'], table=table, **fk['options']))
all_fks.extend(fks)
try:
bound_params = {'vo': vo,
'vo_suffix': '' if vo == 'def' else vo,
'split_character': '@',
'int_1': 1,
'int_2': 2}
bound_params_text = {}
for key in bound_params:
if isinstance(bound_params[key], int):
bound_params_text[key] = bound_params[key]
else:
bound_params_text[key] = "'%s'" % bound_params[key]
# Drop all FKCs affecting InternalAccounts/Scopes or RSE IDs
for fk in all_fks:
print(str(DropConstraint(fk)) + ';')
if commit_changes:
conn.execute(DropConstraint(fk))
# Delete rows
for table, column in tables_and_columns:
delete_command = table.delete().where(split_vo(dialect, column, return_vo=True) == bindparam('vo_suffix'))
print(str(delete_command) % bound_params_text + ';')
if commit_changes:
conn.execute(delete_command, bound_params)
rse_table = Table('rses', metadata)
for table, column in tables_and_columns_rse:
delete_command = table.delete().where(column == rse_table.c.id).where(rse_table.c.vo == bindparam('vo'))
print(str(delete_command) % bound_params_text + ';')
if commit_changes:
conn.execute(delete_command, bound_params)
delete_command = rse_table.delete().where(rse_table.c.vo == bindparam('vo'))
print(str(delete_command) % bound_params_text + ';')
if commit_changes:
conn.execute(delete_command, bound_params)
table = Table('vos', metadata)
delete_command = table.delete().where(table.c.vo == bindparam('vo'))
print(str(delete_command) % bound_params_text + ';')
if commit_changes:
conn.execute(delete_command, bound_params)
# Re-add the FKCs we dropped
for fkc in all_fks:
print(str(AddConstraint(fkc)) + ';')
if commit_changes:
conn.execute(AddConstraint(fkc))
except:
success = False
print(format_exc())
print('Exception occured, changes not committed to DB.')
if commit_changes and success:
trans.commit()
trans.close()
return success
def upgrade(migrate_engine):
meta = sqlalchemy.MetaData()
meta.bind = migrate_engine
if migrate_engine.name not in ['mysql', 'postgresql']:
return
image_properties = Table('image_properties', meta, autoload=True)
image_members = Table('image_members', meta, autoload=True)
images = Table('images', meta, autoload=True)
# We have to ensure that we doesn't have `nulls` values since we are going
# to set nullable=False
migrate_engine.execute(
update(image_members).where(
image_members.c.status == sql.expression.null()).values(
status='pending'))
migrate_engine.execute(
update(images).where(
images.c.protected == sql.expression.null()).values(
protected=sql.expression.false()))
image_members.c.status.alter(nullable=False, server_default='pending')
images.c.protected.alter(nullable=False,
server_default=sql.expression.false())
if migrate_engine.name == 'postgresql':
Index('ix_image_properties_image_id_name', image_properties.c.image_id,
image_properties.c.name).drop()
# We have different names of this constraint in different versions of
# postgresql. Since we have only one constraint on this table, we can
# get it in the following way.
name = migrate_engine.execute("""SELECT conname
FROM pg_constraint
WHERE conrelid =
(SELECT oid
FROM pg_class
WHERE relname LIKE 'image_properties')
AND contype = 'u';""").scalar()
constraint = UniqueConstraint(image_properties.c.image_id,
image_properties.c.name,
name='%s' % name)
migrate_engine.execute(DropConstraint(constraint))
constraint = UniqueConstraint(image_properties.c.image_id,
image_properties.c.name,
name='ix_image_properties_image_id_name')
migrate_engine.execute(AddConstraint(constraint))
images.c.id.alter(server_default=None)
if migrate_engine.name == 'mysql':
constraint = UniqueConstraint(image_properties.c.image_id,
image_properties.c.name,
name='image_id')
migrate_engine.execute(DropConstraint(constraint))
image_locations = Table('image_locations', meta, autoload=True)
if len(image_locations.foreign_keys) == 0:
migrate_engine.execute(
AddConstraint(
ForeignKeyConstraint([image_locations.c.image_id],
[images.c.id])))
def create_pk(self):
"""Create primary key constraints on PostgreSQL table"""
self.logger.info("Creating {} primary key".format(self.sql_table))
self.conn.execute(AddConstraint(self.primary_key))
def _visit_constraint(self, constraint):
constraint.name = self.get_constraint_name(constraint)
self.append(self.process(AddConstraint(constraint)))
self.execute()
def add_foreignkey(self, fk):
self.connection.execute(AddConstraint(fk))
def downgrade(migrate_engine):
meta = sqlalchemy.MetaData()
meta.bind = migrate_engine
metadef_namespaces = Table('metadef_namespaces', meta, autoload=True)
metadef_properties = Table('metadef_properties', meta, autoload=True)
metadef_objects = Table('metadef_objects', meta, autoload=True)
metadef_ns_res_types = Table('metadef_namespace_resource_types',
meta,
autoload=True)
metadef_resource_types = Table('metadef_resource_types',
meta,
autoload=True)
metadef_tags = Table('metadef_tags', meta, autoload=True)
Index('ix_namespaces_namespace', metadef_namespaces.c.namespace).create()
Index('ix_objects_namespace_id_name', metadef_objects.c.namespace_id,
metadef_objects.c.name).create()
Index('ix_metadef_properties_namespace_id_name',
metadef_properties.c.namespace_id,
metadef_properties.c.name).create()
Index('ix_metadef_tags_name', metadef_tags.c.name).drop()
Index('ix_metadef_tags_namespace_id', metadef_tags.c.namespace_id,
metadef_tags.c.name).drop()
if migrate_engine.name != 'sqlite':
fkc = migrate.ForeignKeyConstraint([metadef_tags.c.namespace_id],
[metadef_namespaces.c.id])
fkc.drop()
Index('ix_tags_namespace_id_name', metadef_tags.c.namespace_id,
metadef_tags.c.name).create()
else:
# NOTE(ochuprykov): fkc can't be dropped via `migrate` in sqlite,so it
# is necessary to recreate table manually and populate it with data
temp = Table('temp_', meta,
*([c.copy() for c in metadef_tags.columns]))
temp.create()
migrate_engine.execute('insert into temp_ select * from metadef_tags')
metadef_tags.drop()
migrate_engine.execute('alter table temp_ rename to metadef_tags')
# Refresh old metadata for this table
meta = sqlalchemy.MetaData()
meta.bind = migrate_engine
metadef_tags = Table('metadef_tags', meta, autoload=True)
Index('ix_tags_namespace_id_name', metadef_tags.c.namespace_id,
metadef_tags.c.name).create()
uc = migrate.UniqueConstraint(metadef_tags.c.namespace_id,
metadef_tags.c.name)
uc.create()
if migrate_engine.name == 'mysql':
constraint = UniqueConstraint(metadef_properties.c.namespace_id,
metadef_properties.c.name,
name='namespace_id')
migrate_engine.execute(AddConstraint(constraint))
constraint = UniqueConstraint(metadef_objects.c.namespace_id,
metadef_objects.c.name,
name='namespace_id')
migrate_engine.execute(AddConstraint(constraint))
constraint = UniqueConstraint(metadef_ns_res_types.c.resource_type_id,
metadef_ns_res_types.c.namespace_id,
name='resource_type_id')
migrate_engine.execute(AddConstraint(constraint))
constraint = UniqueConstraint(metadef_namespaces.c.namespace,
name='namespace')
migrate_engine.execute(AddConstraint(constraint))
constraint = UniqueConstraint(metadef_resource_types.c.name,
name='name')
migrate_engine.execute(AddConstraint(constraint))
if migrate_engine.name == 'postgresql':
constraint = UniqueConstraint(metadef_objects.c.namespace_id,
metadef_objects.c.name)
migrate_engine.execute(AddConstraint(constraint))
constraint = UniqueConstraint(metadef_properties.c.namespace_id,
metadef_properties.c.name)
migrate_engine.execute(AddConstraint(constraint))
constraint = UniqueConstraint(metadef_namespaces.c.namespace)
migrate_engine.execute(AddConstraint(constraint))
constraint = UniqueConstraint(metadef_resource_types.c.name)
migrate_engine.execute(AddConstraint(constraint))
constraint = UniqueConstraint(
metadef_tags.c.namespace_id,
metadef_tags.c.name,
name='metadef_tags_namespace_id_name_key')
migrate_engine.execute(AddConstraint(constraint))
if migrate_engine.name == 'mysql':
fkc = migrate.ForeignKeyConstraint(
[metadef_ns_res_types.c.resource_type_id],
[metadef_namespaces.c.id],
name='metadef_namespace_resource_types_ibfk_2')
fkc.drop()
Index('ix_metadef_ns_res_types_namespace_id',
metadef_ns_res_types.c.namespace_id).drop()
fkc.create()
else:
Index('ix_metadef_ns_res_types_namespace_id',
metadef_ns_res_types.c.namespace_id).drop()
Index('ix_metadef_namespaces_namespace',
metadef_namespaces.c.namespace).drop()
Index('ix_metadef_namespaces_owner', metadef_namespaces.c.owner).drop()
Index('ix_metadef_objects_name', metadef_objects.c.name).drop()
Index('ix_metadef_objects_namespace_id',
metadef_objects.c.namespace_id).drop()
Index('ix_metadef_properties_name', metadef_properties.c.name).drop()
Index('ix_metadef_properties_namespace_id',
metadef_properties.c.namespace_id).drop()
def setup(context, drop):
""" Initialize a database for openFRED data.
Connect to the database specified in the `[openFRED]` section of oemof's
configuration file and set the database up to hold openFRED data.
This means that the configured schema is created if it doesn't already
exists. The same holds for the tables necessary to store openFRED data
inside the schema.
"""
section = context.obj["db"]["section"]
schema = oemof.db.config.get(section, "schema")
engine = oemof.db.engine(section)
inspector = inspect(engine)
metadata = MetaData(schema=schema, bind=engine, reflect=(not drop))
classes = mapped_classes(metadata)
if drop == "schema":
with engine.connect() as connection:
connection.execute(
"DROP SCHEMA IF EXISTS {} CASCADE".format(schema))
elif drop == "tables":
classes["__Base__"].metadata.drop_all(engine)
if schema not in inspector.get_schema_names():
engine.execute(CreateSchema(schema))
with engine.connect() as connection:
connection.execute("CREATE EXTENSION IF NOT EXISTS postgis;")
connection.execute("CREATE EXTENSION IF NOT EXISTS postgis_topology;")
classes["__Base__"].metadata.create_all(engine)
with db_session(engine) as session:
timespan = classes["Timespan"]
try:
ts = (session.query(timespan).filter_by(start=None,
stop=None).one_or_none())
except MRF:
click.echo("Multiple timestamps found which have no `start` "
"and/or `stop` values.\nAborting.")
ts = ts or classes["Timespan"]()
session.add(ts)
session.flush()
context = MigrationContext.configure(session.connection())
ops = Operations(context)
ops.alter_column(
table_name=str(classes["Series"].__table__.name),
column_name="timespan_id",
server_default=str(ts.id),
schema=schema,
)
constraint_name = "singular_null_timestamp_constraint"
if not [
c for c in timespan.__table__.constraints
if c.name == constraint_name
]:
constraint = CheckConstraint(
"(id = {}) OR ".format(ts.id) +
"(start IS NOT NULL AND stop IS NOT NULL)",
name=constraint_name,
)
timespan.__table__.append_constraint(constraint)
session.execute(AddConstraint(constraint))
return classes
from sqlalchemy.schema import AddConstraint
from pycroft.model.base import IntegerIdModel
from pycroft.model.types import IPAddress, IPNetwork
class VLAN(IntegerIdModel):
name = Column(String(127), nullable=False)
vid = Column(Integer, nullable=False)
__table_args = (CheckConstraint(between(vid, 1, 4094)), )
switch_ports = relationship('SwitchPort',
secondary='switch_port_default_vlans',
back_populates='default_vlans')
subnets = relationship('Subnet', back_populates='vlan')
class Subnet(IntegerIdModel):
address = Column(IPNetwork, nullable=False)
gateway = Column(IPAddress)
reserved_addresses = Column(Integer, default=0, nullable=True)
description = Column(String(50))
vlan_id = Column(Integer, ForeignKey(VLAN.id), nullable=False, index=True)
vlan = relationship(VLAN, back_populates="subnets")
# Ensure that the gateway is contained in the subnet
constraint = CheckConstraint(Subnet.gateway.op('<<')(Subnet.address))
event.listen(Subnet.__table__, "after_create",
AddConstraint(constraint).execute_if(dialect='postgresql'))
def make_dataset_table(table_name: str, create: bool = False) -> Table:
"""Create customized dataset table using a table name.
TODO: Create an example
Args:
table_name - Table name
create - Flag to create if not exists
Returns
dataset_table definition
"""
if create:
if not db.engine.dialect.has_table(connection=db.engine,
table_name=table_name,
schema=Config.SAMPLEDB_SCHEMA):
with db.session.begin_nested():
db.engine.execute(
f"CREATE TABLE {Config.SAMPLEDB_SCHEMA}.dataset_{table_name} OF dataset_type"
)
s_name = f"{Config.SAMPLEDB_SCHEMA}.dataset_{table_name}_id_seq"
db.engine.execute(f"CREATE SEQUENCE {s_name}")
klass = Table(f'dataset_{table_name}',
metadata,
autoload=True,
autoload_with=db.engine,
extend_existing=True)
# Add index, primary key and foreign key
db.engine.execute(
f"ALTER TABLE {Config.SAMPLEDB_SCHEMA}.{klass.name} ALTER COLUMN {klass.c.class_id.name} SET NOT NULL"
)
db.engine.execute(
f"ALTER TABLE {Config.SAMPLEDB_SCHEMA}.{klass.name} ALTER COLUMN {klass.c.start_date.name} SET NOT NULL"
)
db.engine.execute(
f"ALTER TABLE {Config.SAMPLEDB_SCHEMA}.{klass.name} ALTER COLUMN {klass.c.end_date.name} SET NOT NULL"
)
db.engine.execute(
f"ALTER TABLE {Config.SAMPLEDB_SCHEMA}.{klass.name} ALTER {klass.c.id.name} SET DEFAULT NEXTVAL('{s_name}');"
)
db.engine.execute(
AddConstraint(PrimaryKeyConstraint(klass.c.id)))
db.engine.execute(CreateIndex(Index(None, klass.c.user_id)))
db.engine.execute(CreateIndex(Index(None, klass.c.class_id)))
db.engine.execute(
CreateIndex(
Index(None, klass.c.location,
postgresql_using='gist')))
db.engine.execute(CreateIndex(Index(None, klass.c.start_date)))
db.engine.execute(CreateIndex(Index(None, klass.c.end_date)))
Index(f'idx_{klass.name}_start_end_date', klass.c.start_date,
klass.c.end_date)
db.engine.execute(
AddConstraint(
ForeignKeyConstraint(
name=
f"dataset_{table_name}_{klass.c.user_id.name}_fkey",
columns=[klass.c.user_id],
refcolumns=[Users.id],
onupdate="CASCADE",
ondelete="CASCADE")))
db.engine.execute(
AddConstraint(
ForeignKeyConstraint(
name=
f"dataset_{table_name}_{klass.c.class_id.name}_fkey",
columns=[klass.c.class_id],
refcolumns=[LucClass.id],
onupdate="CASCADE",
ondelete="CASCADE")))
db.session.commit()
else:
klass = Table(f'dataset_{table_name}',
metadata,
autoload=True,
autoload_with=db.engine)
return klass
def downgrade(migrate_engine):
meta = sqlalchemy.MetaData()
meta.bind = migrate_engine
inspector = inspect(migrate_engine)
metadef_namespaces = Table('metadef_namespaces', meta, autoload=True)
metadef_properties = Table('metadef_properties', meta, autoload=True)
metadef_objects = Table('metadef_objects', meta, autoload=True)
metadef_ns_res_types = Table('metadef_namespace_resource_types',
meta, autoload=True)
metadef_resource_types = Table('metadef_resource_types', meta,
autoload=True)
metadef_tags = Table('metadef_tags', meta, autoload=True)
constraints = [('ix_namespaces_namespace',
[metadef_namespaces.c.namespace]),
('ix_objects_namespace_id_name',
[metadef_objects.c.namespace_id,
metadef_objects.c.name]),
('ix_metadef_properties_namespace_id_name',
[metadef_properties.c.namespace_id,
metadef_properties.c.name])]
metadef_tags_constraints = inspector.get_unique_constraints('metadef_tags')
for constraint in metadef_tags_constraints:
if set(constraint['column_names']) == set(['namespace_id', 'name']):
constraints.append((constraint['name'],
[metadef_tags.c.namespace_id,
metadef_tags.c.name]))
if meta.bind.name == "ibm_db_sa":
# For db2, the following constraints need to be dropped first,
# otherwise the index like ix_metadef_ns_res_types_namespace_id
# will fail to drop. These constraints will be added back at
# the end. It should not affect the origional logic for other
# database backends.
for (constraint_name, cols) in constraints:
_change_db2_unique_constraint('drop', constraint_name, *cols)
else:
Index('ix_namespaces_namespace',
metadef_namespaces.c.namespace).create()
Index('ix_objects_namespace_id_name', metadef_objects.c.namespace_id,
metadef_objects.c.name).create()
Index('ix_metadef_properties_namespace_id_name',
metadef_properties.c.namespace_id,
metadef_properties.c.name).create()
Index('ix_metadef_tags_name', metadef_tags.c.name).drop()
Index('ix_metadef_tags_namespace_id', metadef_tags.c.namespace_id,
metadef_tags.c.name).drop()
if migrate_engine.name != 'sqlite':
fkc = migrate.ForeignKeyConstraint([metadef_tags.c.namespace_id],
[metadef_namespaces.c.id])
fkc.drop()
if meta.bind.name != "ibm_db_sa":
# This index would not be created when it is db2 backend.
Index('ix_tags_namespace_id_name', metadef_tags.c.namespace_id,
metadef_tags.c.name).create()
else:
# NOTE(ochuprykov): fkc can't be dropped via `migrate` in sqlite,so it
# is necessary to recreate table manually and populate it with data
temp = Table('temp_', meta, *(
[c.copy() for c in metadef_tags.columns]))
temp.create()
migrate_engine.execute('insert into temp_ select * from metadef_tags')
metadef_tags.drop()
migrate_engine.execute('alter table temp_ rename to metadef_tags')
# Refresh old metadata for this table
meta = sqlalchemy.MetaData()
meta.bind = migrate_engine
metadef_tags = Table('metadef_tags', meta, autoload=True)
Index('ix_tags_namespace_id_name', metadef_tags.c.namespace_id,
metadef_tags.c.name).create()
uc = migrate.UniqueConstraint(metadef_tags.c.namespace_id,
metadef_tags.c.name)
uc.create()
if migrate_engine.name == 'mysql':
constraint = UniqueConstraint(metadef_properties.c.namespace_id,
metadef_properties.c.name,
name='namespace_id')
migrate_engine.execute(AddConstraint(constraint))
constraint = UniqueConstraint(metadef_objects.c.namespace_id,
metadef_objects.c.name,
name='namespace_id')
migrate_engine.execute(AddConstraint(constraint))
constraint = UniqueConstraint(metadef_ns_res_types.c.resource_type_id,
metadef_ns_res_types.c.namespace_id,
name='resource_type_id')
migrate_engine.execute(AddConstraint(constraint))
constraint = UniqueConstraint(metadef_namespaces.c.namespace,
name='namespace')
migrate_engine.execute(AddConstraint(constraint))
constraint = UniqueConstraint(metadef_resource_types.c.name,
name='name')
migrate_engine.execute(AddConstraint(constraint))
if migrate_engine.name == 'postgresql':
constraint = UniqueConstraint(
metadef_objects.c.namespace_id,
metadef_objects.c.name)
migrate_engine.execute(AddConstraint(constraint))
constraint = UniqueConstraint(
metadef_properties.c.namespace_id,
metadef_properties.c.name)
migrate_engine.execute(AddConstraint(constraint))
constraint = UniqueConstraint(
metadef_namespaces.c.namespace)
migrate_engine.execute(AddConstraint(constraint))
constraint = UniqueConstraint(
metadef_resource_types.c.name)
migrate_engine.execute(AddConstraint(constraint))
constraint = UniqueConstraint(
metadef_tags.c.namespace_id,
metadef_tags.c.name,
name='metadef_tags_namespace_id_name_key')
migrate_engine.execute(AddConstraint(constraint))
if migrate_engine.name == 'mysql':
fkc = migrate.ForeignKeyConstraint(
[metadef_ns_res_types.c.resource_type_id],
[metadef_namespaces.c.id],
name='metadef_namespace_resource_types_ibfk_2')
fkc.drop()
Index('ix_metadef_ns_res_types_namespace_id',
metadef_ns_res_types.c.namespace_id).drop()
fkc.create()
else:
Index('ix_metadef_ns_res_types_namespace_id',
metadef_ns_res_types.c.namespace_id).drop()
Index('ix_metadef_namespaces_namespace',
metadef_namespaces.c.namespace).drop()
Index('ix_metadef_namespaces_owner', metadef_namespaces.c.owner).drop()
Index('ix_metadef_objects_name', metadef_objects.c.name).drop()
Index('ix_metadef_objects_namespace_id',
metadef_objects.c.namespace_id).drop()
Index('ix_metadef_properties_name', metadef_properties.c.name).drop()
Index('ix_metadef_properties_namespace_id',
metadef_properties.c.namespace_id).drop()
if meta.bind.name == "ibm_db_sa":
# For db2, add these constraints back. It should not affect the
# origional logic for other database backends.
for (constraint_name, cols) in constraints:
_change_db2_unique_constraint('create', constraint_name, *cols)
