def upgrade():
op.add_column("metrics", sa.Column("changed_by_fk", sa.Integer(), nullable=True))
op.add_column("metrics", sa.Column("changed_on", sa.DateTime(), nullable=True))
op.add_column("metrics", sa.Column("created_by_fk", sa.Integer(), nullable=True))
op.add_column("metrics", sa.Column("created_on", sa.DateTime(), nullable=True))
try:
op.alter_column("columns", "changed_on", existing_type=sa.DATETIME(), nullable=True)
op.alter_column("columns", "created_on", existing_type=sa.DATETIME(), nullable=True)
op.alter_column("css_templates", "changed_on", existing_type=sa.DATETIME(), nullable=True)
op.alter_column("css_templates", "created_on", existing_type=sa.DATETIME(), nullable=True)
op.alter_column("dashboards", "changed_on", existing_type=sa.DATETIME(), nullable=True)
op.alter_column("dashboards", "created_on", existing_type=sa.DATETIME(), nullable=True)
op.alter_column("datasources", "changed_by_fk", existing_type=sa.INTEGER(), nullable=True)
op.alter_column("datasources", "changed_on", existing_type=sa.DATETIME(), nullable=True)
op.alter_column("datasources", "created_by_fk", existing_type=sa.INTEGER(), nullable=True)
op.alter_column("datasources", "created_on", existing_type=sa.DATETIME(), nullable=True)
op.alter_column("dbs", "changed_on", existing_type=sa.DATETIME(), nullable=True)
op.alter_column("dbs", "created_on", existing_type=sa.DATETIME(), nullable=True)
op.alter_column("slices", "changed_on", existing_type=sa.DATETIME(), nullable=True)
op.alter_column("slices", "created_on", existing_type=sa.DATETIME(), nullable=True)
op.alter_column("sql_metrics", "changed_on", existing_type=sa.DATETIME(), nullable=True)
op.alter_column("sql_metrics", "created_on", existing_type=sa.DATETIME(), nullable=True)
op.alter_column("table_columns", "changed_on", existing_type=sa.DATETIME(), nullable=True)
op.alter_column("table_columns", "created_on", existing_type=sa.DATETIME(), nullable=True)
op.alter_column("tables", "changed_on", existing_type=sa.DATETIME(), nullable=True)
op.alter_column("tables", "created_on", existing_type=sa.DATETIME(), nullable=True)
op.alter_column("url", "changed_on", existing_type=sa.DATETIME(), nullable=True)
op.alter_column("url", "created_on", existing_type=sa.DATETIME(), nullable=True)
op.create_foreign_key(None, "metrics", "ab_user", ["changed_by_fk"], ["id"])
op.create_foreign_key(None, "metrics", "ab_user", ["created_by_fk"], ["id"])
except:
pass
def upgrade():
conn = op.get_bind()
op.add_column('external_identities', sa.Column('local_user_id',
sa.Integer()))
external_identities_t = table('external_identities',
sa.Column('local_user_name', sa.Unicode(50)),
sa.Column('local_user_id', sa.Integer))
users_t = table('users',
sa.Column('user_name', sa.Unicode(50)),
sa.Column('id', sa.Integer))
stmt = external_identities_t.update().values(local_user_id=users_t.c.id). \
where(users_t.c.user_name == external_identities_t.c.local_user_name)
conn.execute(stmt)
op.drop_constraint('pk_external_identities', 'external_identities',
type='primary')
op.drop_constraint('fk_external_identities_local_user_name_users',
'external_identities', type='foreignkey')
op.drop_column('external_identities', 'local_user_name')
op.create_primary_key('pk_external_identities', 'external_identities',
cols=['external_id', 'local_user_id',
'provider_name'])
op.create_foreign_key(None, 'external_identities', 'users',
remote_cols=['id'],
local_cols=['local_user_id'], onupdate='CASCADE',
ondelete='CASCADE')
def upgrade():
# constraint name is autogenerated differently between pg versions
try:
op.drop_constraint("reportunknownpackages_report_id_key", t)
except:
op.execute('ROLLBACK')
op.drop_constraint(
"reportunknownpackages_report_id_type_name_installed_epoch_i_key",
t)
op.drop_constraint("reportunknownpackages_installed_arch_id_fkey", t)
op.drop_column(t, "running_epoch")
op.drop_column(t, "running_version")
op.drop_column(t, "running_release")
op.drop_column(t, "running_arch_id")
op.alter_column(t, "installed_epoch", new_column_name="epoch")
op.alter_column(t, "installed_version", new_column_name="version")
op.alter_column(t, "installed_release", new_column_name="release")
op.alter_column(t, "installed_arch_id", new_column_name="arch_id")
op.create_foreign_key("reportunknownpackages_arch_id_fkey", t,
"archs", ["arch_id"], ["id"])
fields = ["report_id", "type", "name", "epoch",
"version", "release", "arch_id"]
op.create_unique_constraint("reportunknownpackages_report_id_key", t,
fields)
def downgrade():
### commands auto generated by Alembic - please adjust! ###
op.add_column(u'user', sa.Column('org_id', sa.INTEGER(), autoincrement=False, nullable=True))
op.drop_constraint(None, 'user', type_='foreignkey')
op.create_foreign_key(u'user_org_id_fkey', 'user', 'org', ['org_id'], ['id'])
op.drop_column(u'user', 'team_id')
op.drop_table('team')
def downgrade():
op.drop_constraint("reportunknownpackages_report_id_key", t)
op.drop_constraint("reportunknownpackages_arch_id_fkey", t)
op.add_column(t, sa.Column('running_epoch', sa.Integer(),
nullable=True))
op.add_column(t, sa.Column('running_version', sa.String(64),
nullable=True))
op.add_column(t, sa.Column('running_release', sa.String(64),
nullable=True))
op.add_column(t, sa.Column('running_arch_id', sa.Integer(),
sa.ForeignKey('archs.id'),
nullable=True))
op.alter_column(t, "epoch", new_column_name="installed_epoch")
op.alter_column(t, "version", new_column_name="installed_version")
op.alter_column(t, "release", new_column_name="installed_release")
op.alter_column(t, "arch_id", new_column_name="installed_arch_id")
fields = ["report_id", "type", "name", "installed_epoch",
"installed_version", "installed_release", "installed_arch_id",
"running_epoch", "running_version", "running_release",
"running_arch_id"]
op.create_unique_constraint("reportunknownpackages_report_id_key", t,
fields)
op.create_foreign_key("reportunknownpackages_installed_arch_id_fkey", t,
"archs", ["installed_arch_id"], ["id"])
def upgrade_table(table_name):
"""Add audit foreign key to a table."""
op.add_column(
table_name,
sa.Column("audit_id", sa.Integer(), nullable=True)
)
op.execute("""
UPDATE {table_name} AS t
JOIN contexts AS c ON
c.id = t.context_id AND
c.related_object_type = "Audit"
JOIN audits AS au ON
c.related_object_id = au.id
SET
t.audit_id = au.id
""".format(
table_name=table_name,
))
op.alter_column(
table_name,
"audit_id",
existing_type=sa.Integer(),
nullable=False
)
op.create_foreign_key(
"fk_{}_audits".format(table_name),
table_name,
"audits",
["audit_id"],
["id"],
ondelete="RESTRICT"
)
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.drop_constraint(u'notes_section_id_fkey', 'notes', type_='foreignkey')
op.drop_column('notes', 'section_id')
op.drop_table('sections')
op.add_column('notes', sa.Column('notebook_id', sa.Integer(), nullable=True))
op.create_foreign_key(None, 'notes', 'notebooks', ['notebook_id'], ['id'])
def upgrade():
op.alter_column(
'cycle_task_group_object_tasks',
'cycle_task_group_object_id',
existing_type=sa.Integer(),
nullable=True
)
op.add_column(
'cycle_task_group_object_tasks',
sa.Column('cycle_task_group_id', sa.Integer(), nullable=False)
)
op.execute("""
UPDATE cycle_task_group_object_tasks
SET cycle_task_group_id=(
SELECT cycle_task_group_id
FROM cycle_task_group_objects
WHERE id=cycle_task_group_object_tasks.cycle_task_group_object_id
)
""")
op.create_foreign_key(
"cycle_task_group_id", "cycle_task_group_object_tasks",
"cycle_task_groups", ["cycle_task_group_id"], ["id"]
)
def upgrade():
op.create_table('portgroups',
sa.Column('created_at', sa.DateTime(), nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('uuid', sa.String(length=36), nullable=True),
sa.Column('name', sa.String(length=255), nullable=True),
sa.Column('node_id', sa.Integer(), nullable=True),
sa.Column('address', sa.String(length=18), nullable=True),
sa.Column('extra', sa.Text(), nullable=True),
sa.ForeignKeyConstraint(['node_id'], ['nodes.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('uuid', name='uniq_portgroups0uuid'),
sa.UniqueConstraint('address',
name='uniq_portgroups0address'),
sa.UniqueConstraint('name', name='uniq_portgroups0name'),
mysql_ENGINE='InnoDB',
mysql_DEFAULT_CHARSET='UTF8')
op.add_column(u'ports', sa.Column('local_link_connection', sa.Text(),
nullable=True))
op.add_column(u'ports', sa.Column('portgroup_id', sa.Integer(),
nullable=True))
op.add_column(u'ports', sa.Column('pxe_enabled', sa.Boolean(),
default=True))
op.create_foreign_key('fk_portgroups_ports', 'ports', 'portgroups',
['portgroup_id'], ['id'])
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.add_column('relationships', sa.Column('followed_by_id', sa.Integer(), nullable=True))
op.add_column('relationships', sa.Column('user_id', sa.Integer(), nullable=True))
op.create_unique_constraint('unique_idx_user_id_followed_by_id', 'relationships', ['user_id', 'followed_by_id'])
op.create_foreign_key(None, 'relationships', 'users', ['followed_by_id'], ['id'])
op.create_foreign_key(None, 'relationships', 'users', ['user_id'], ['id'])
def upgrade():
op.add_column('secrets', sa.Column('project_id', sa.String(length=36),
nullable=True))
op.create_index(op.f('ix_secrets_project_id'), 'secrets', ['project_id'],
unique=False)
op.create_foreign_key('secrets_project_fk', 'secrets', 'projects',
['project_id'], ['id'])
def upgrade():
# OpenStack has decided that "down" migrations are not supported.
# The downgrade() method has been omitted for this reason.
op.add_column('listener_statistics',
sa.Column('amphora_id',
sa.String(36),
nullable=False)
)
op.drop_constraint('fk_listener_statistics_listener_id',
'listener_statistics',
type_='foreignkey')
op.drop_constraint('PRIMARY',
'listener_statistics',
type_='primary')
op.create_primary_key('pk_listener_statistics', 'listener_statistics',
['listener_id', 'amphora_id'])
op.create_foreign_key('fk_listener_statistics_listener_id',
'listener_statistics',
'listener',
['listener_id'],
['id'])
op.create_foreign_key('fk_listener_statistic_amphora_id',
'listener_statistics',
'amphora',
['amphora_id'],
['id'])
def upgrade():
op.create_foreign_key(
"fk_vehicles_markers_pk", "vehicles", "markers", ["accident_id", "provider_code"], ["id", "provider_code"]
)
op.create_foreign_key(
"fk_involved_markers_pk", "involved", "markers", ["accident_id", "provider_code"], ["id", "provider_code"]
)
def upgrade():
op.execute('ALTER TABLE roombooking.rooms ALTER COLUMN owner_id TYPE int USING owner_id::int')
op.create_foreign_key(None,
'rooms', 'users',
['owner_id'], ['id'],
source_schema='roombooking', referent_schema='users')
op.create_index(None, 'rooms', ['owner_id'], unique=False, schema='roombooking')
def upgrade():
conn = op.get_bind()
# Minimal examples of the tables we need to manipulate
listener = sa.sql.table(
'listener',
sa.sql.column('load_balancer_id', sa.String),
sa.sql.column('default_pool_id', sa.String))
pool = sa.sql.table(
'pool',
sa.sql.column('load_balancer_id', sa.String),
sa.sql.column('id', sa.String))
# This foreign key does not need to be unique anymore. To remove the
# uniqueness but keep the foreign key we have to do some juggling.
op.drop_constraint('fk_listener_pool_id', 'listener',
type_='foreignkey')
op.drop_constraint('uq_listener_default_pool_id', 'listener',
type_='unique')
op.create_foreign_key('fk_listener_pool_id', 'listener',
'pool', ['default_pool_id'], ['id'])
op.add_column(u'pool',
sa.Column('load_balancer_id', sa.String(36),
sa.ForeignKey('load_balancer.id'), nullable=True))
# Populate this new column appropriately
select_obj = sa.select([listener.c.load_balancer_id,
listener.c.default_pool_id]).where(
listener.c.default_pool_id is not None)
result = conn.execute(select_obj)
for row in result:
stmt = pool.update().values(load_balancer_id=row[0]).where(
pool.c.id == row[1])
op.execute(stmt)
def upgrade(pyramid_env):
with context.begin_transaction():
op.create_table(
'abstract_agent_account',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('type', sa.String(length=60), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.rename_table(u'email_account', 'agent_email_account')
op.rename_table(u'idprovider_account', 'idprovider_agent_account')
op.execute("INSERT INTO abstract_agent_account (SELECT id, 'agent_email_account' FROM agent_email_account)")
op.execute("UPDATE idprovider_agent_account SET id = id + (SELECT max(id) FROM agent_email_account)")
op.execute("INSERT INTO abstract_agent_account (SELECT id, 'idprovider_agent_account' FROM idprovider_agent_account)")
op.execute("select setval('abstract_agent_account_id_seq', (SELECT max(id)+1 FROM abstract_agent_account), false)")
op.execute("alter table agent_email_account alter column id drop default")
op.execute("alter table idprovider_agent_account alter column id drop default")
op.create_foreign_key('fk_id', 'agent_email_account', 'abstract_agent_account', ['id'], ['id'], ondelete='CASCADE')
op.create_foreign_key('fk_id', 'idprovider_agent_account', 'abstract_agent_account', ['id'], ['id'], ondelete='CASCADE')
op.execute('drop sequence email_account_id_seq')
op.execute('drop sequence idprovider_account_id_seq')
### end Alembic commands ###
# Do stuff with the app's models here.
with transaction.manager:
pass
def upgrade():
op.add_column(
'environments',
sa.Column(
'zone_id',
INTEGER(),
info={'after': 'prefix'}
)
)
op.execute(
'update environments join zones on environments.domain = '
'zones.zoneName set environments.zone_id = zones.ZoneID'
)
op.alter_column(
'environments',
'zone_id',
nullable=False,
existing_type=INTEGER()
)
op.create_foreign_key(
'fk_environments_zone_id_zones',
'environments',
'zones',
['zone_id'],
['zoneID']
)
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.add_column('gene', sa.Column('reference_assembly_id', sa.Integer(), nullable=True))
op.create_unique_constraint('gene_within_assembly_unique', 'gene', ['name', 'reference_assembly_id'])
op.drop_constraint('gene_reference_assemlby_id_fkey', 'gene', type_='foreignkey')
op.create_foreign_key(None, 'gene', 'reference_assembly', ['reference_assembly_id'], ['id'])
op.drop_column('gene', 'reference_assemlby_id')
def downgrade():
### commands auto generated by Alembic - please adjust! ###
op.add_column('gene', sa.Column('reference_assemlby_id', sa.INTEGER(), autoincrement=False, nullable=True))
op.drop_constraint(None, 'gene', type_='foreignkey')
op.create_foreign_key('gene_reference_assemlby_id_fkey', 'gene', 'reference_assembly', ['reference_assemlby_id'], ['id'])
op.drop_constraint('gene_within_assembly_unique', 'gene', type_='unique')
op.drop_column('gene', 'reference_assembly_id')
def downgrade():
op.drop_index(op.f('ix_team_domain'), table_name='team')
op.drop_column('team', 'domain')
op.drop_constraint('organization_members_id_fkey', 'organization', type_='foreignkey')
op.drop_column('organization', 'members_id')
op.drop_constraint('organization_owners_id_fkey', 'organization', type_='foreignkey')
op.create_foreign_key('fk_organization_owners_id', 'organization', 'team', ['owners_id'], ['id'])
def downgrade():
from autonomie.alembic.utils import force_rename_table
from autonomie.alembic.utils import rename_column
for table, key in foreign_key_names:
remove_foreign_key(table, key)
for table in ('estimation', 'invoice', 'cancelinvoice', 'manualinvoice'):
fkey = "%s_ibfk_1" % table
query = "alter table %s drop foreign key %s;" % (table, fkey)
op.execute(query)
rename_column(table, 'customer_id', 'client_id')
for table, key in foreign_key_names:
op.create_foreign_key(
fkey,
table,
'customer',
['client_id'],
['id'])
force_rename_table('project_customer', 'project_client')
# Rename the column
fkey = "project_client_ibfk_2"
remove_foreign_key("project_client", "project_customer_ibfk_2")
rename_column('project_client', 'customer_id', 'client_id')
op.create_foreign_key(
"project_client_ibfk_2",
'project_client',
'customer',
['client_id'],
['id'])
def upgrade(active_plugins=None, options=None):
op.add_column(
'comments', sa.Column('in_reply_to', sa.Integer(), nullable=True))
dialect = op.get_bind().engine.dialect
if dialect.supports_alter:
op.create_foreign_key(
'comments_ibfk_1', 'comments', 'comments', ['in_reply_to'], ['id'])
def add_key(fk):
fk_name = fk.name
fk_table = fk.parent.table.name
fk_ref = fk.column.table.name
fk_local_cols = [fk.parent.name]
fk_remote_cols = [fk.column.name]
op.create_foreign_key(fk_name, fk_table, fk_ref, fk_local_cols, fk_remote_cols)
def upgrade():
print 'Deleting orphaned attachments...'
op.execute("""
CREATE TEMP TABLE orphaned_folder_ids ON COMMIT DROP AS (
SELECT id FROM attachments.folders fo WHERE fo.link_type = 1 AND NOT EXISTS (
SELECT 1 FROM categories.categories WHERE id = fo.category_id
)
);
CREATE TEMP TABLE orphaned_attachment_ids ON COMMIT DROP AS (
SELECT id FROM attachments.attachments WHERE folder_id IN (SELECT id FROM orphaned_folder_ids)
);
UPDATE attachments.attachments SET file_id = NULL WHERE file_id IS NOT NULL AND id IN (
SELECT id FROM orphaned_attachment_ids
);
DELETE FROM attachments.files WHERE attachment_id IN (SELECT id FROM orphaned_attachment_ids);
DELETE FROM attachments.legacy_attachment_id_map WHERE attachment_id IN (
SELECT id FROM orphaned_attachment_ids
);
DELETE FROM attachments.attachment_principals WHERE attachment_id IN (SELECT id FROM orphaned_attachment_ids);
DELETE FROM attachments.attachments WHERE id IN (SELECT id FROM orphaned_attachment_ids);
DELETE FROM attachments.legacy_folder_id_map WHERE folder_id IN (SELECT id FROM orphaned_folder_ids);
DELETE FROM attachments.folder_principals WHERE folder_id IN (SELECT id FROM orphaned_folder_ids);
DELETE FROM attachments.folders WHERE id IN (SELECT id FROM orphaned_folder_ids);
""")
op.create_foreign_key(None,
'folders', 'categories',
['category_id'], ['id'],
source_schema='attachments', referent_schema='categories')
def upgrade():
op.create_foreign_key(
'network_groups_nodegroups_fk', 'network_groups', 'nodegroups',
['group_id'], ['id'])
op.create_foreign_key(
'nodes_nodegroups_fk', 'nodes', 'nodegroups', ['group_id'], ['id'])
op.alter_column(
'oswl_stats', 'resource_checksum', existing_type=sa.TEXT(),
nullable=False)
op.create_unique_constraint(
'oswl_stats_cluster_id_created_date_resource_type_unique_key',
'oswl_stats', ['cluster_id', 'created_date', 'resource_type'])
op.alter_column('clusters', 'name', type_=sa.TEXT())
extend_ip_addrs_model_upgrade()
extend_node_model_upgrade()
configurable_hostnames_upgrade()
extend_plugin_model_upgrade()
upgrade_node_roles_metadata()
node_roles_as_plugin_upgrade()
migrate_volumes_into_extension_upgrade()
networking_templates_upgrade()
extend_releases_model_upgrade()
upgrade_task_names()
vms_conf_upgrade()
extend_nic_model_upgrade()
upgrade_cluster_ui_settings()
upgrade_cluster_bond_settings()
extensions_field_upgrade()
set_deployable_false_for_old_releases()
upgrade_node_labels()
extend_segmentation_type()
network_groups_name_upgrade()
def upgrade():
if migration.schema_has_table('nsxv3_lbaas_l7rules'):
op.drop_constraint('fk_nsxv3_lbaas_l7rules_id', 'nsxv3_lbaas_l7rules',
'foreignkey')
op.drop_constraint('l7rule_id', 'nsxv3_lbaas_l7rules', 'primary')
op.drop_column('nsxv3_lbaas_l7rules', 'loadbalancer_id')
op.drop_column('nsxv3_lbaas_l7rules', 'l7rule_id')
op.rename_table('nsxv3_lbaas_l7rules', 'nsxv3_lbaas_l7policies')
if migration.schema_has_table('lbaas_l7policies'):
op.create_foreign_key(
'fk_nsxv3_lbaas_l7policies_id', 'nsxv3_lbaas_l7policies',
'lbaas_l7policies', ['l7policy_id'], ['id'],
ondelete='CASCADE')
else:
op.create_table(
'nsxv3_lbaas_l7policies',
sa.Column('l7policy_id', sa.String(36), nullable=False),
sa.Column('lb_rule_id', sa.String(36), nullable=False),
sa.Column('lb_vs_id', sa.String(36), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('l7policy_id'))
if migration.schema_has_table('lbaas_l7policies'):
op.create_foreign_key(
'fk_nsxv3_lbaas_l7policies_id', 'nsxv3_lbaas_l7policies',
'lbaas_l7policies', ['l7policy_id'], ['id'],
ondelete='CASCADE')
def upgrade():
op.add_column('alert', sa.Column('entity_id', sa.String(length=32),
nullable=True))
op.add_column('alert', sa.Column('query_text', sa.Unicode(), nullable=True))
op.create_foreign_key(None, 'alert', 'entity', ['entity_id'], ['id'])
bind = op.get_bind()
meta = sa.MetaData()
meta.bind = bind
meta.reflect()
alert_table = meta.tables['alert']
rp = bind.execute(sa.select([alert_table]))
for alert in rp.fetchall():
deleted_at = alert.deleted_at
query_text = alert.query.get('q', [None])[0]
entity_id = alert.query.get('entity', [None])[0]
if entity_id is not None and len(entity_id) < 30:
entity_id = None
if entity_id is None and query_text is None:
deleted_at = datetime.utcnow()
q = sa.update(alert_table).where(alert_table.c.id == alert.id)
q = q.values(query_text=query_text,
entity_id=entity_id,
deleted_at=deleted_at)
bind.execute(q)
op.drop_column('alert', 'query')
op.drop_column('alert', 'signature')
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table('weather',
sa.Column(
'id', sqlalchemy_utils.types.uuid.UUIDType(), nullable=False),
sa.Column(
'date', sa.DateTime(timezone=True), nullable=True),
sa.Column('rain', sa.Boolean(), nullable=True),
sa.Column(
'temperature', sa.Float(precision=1), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_table('sport_schedule',
sa.Column(
'id', sqlalchemy_utils.types.uuid.UUIDType(), nullable=False),
sa.Column(
'user_id', sqlalchemy_utils.types.uuid.UUIDType(), nullable=True),
sa.Column('name', sa.String(length=100), nullable=True),
sa.Column('reminder_minutes', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint(
'user_id', 'name', name='sport_schedule_name_uc')
)
op.add_column('user_activity', sa.Column(
'weather_id', sqlalchemy_utils.types.uuid.UUIDType(), nullable=True))
op.create_foreign_key(
None, 'user_activity', 'weather', ['weather_id'], ['id'])
op.drop_column('user_activity', 'temperature')
op.drop_column('user_activity', 'rain')
def upgrade(active_plugins=None, options=None):
inspector = reflection.Inspector.from_engine(op.get_bind())
unique_constraints = inspector.get_unique_constraints(
'gp_policy_target_groups')
op.drop_constraint('gp_policy_target_groups_ibfk_nsp',
'gp_policy_target_groups',
'foreignkey')
for constraint in unique_constraints:
if constraint['column_names'] == ['network_service_policy_id']:
op.drop_constraint(constraint['name'],
'gp_policy_target_groups',
'unique')
break
op.create_foreign_key('gp_policy_target_groups_ibfk_nsp',
source='gp_policy_target_groups',
referent='gp_network_service_policies',
local_cols=['network_service_policy_id'],
remote_cols=['id'])
with migration.remove_fks_from_table(
'gpm_service_policy_ipaddress_mappings'):
op.drop_constraint(
None,
table_name='gpm_service_policy_ipaddress_mappings',
type_='primary')
op.create_primary_key(
name='pk_policytargetgroup_servicepolicyid',
table_name='gpm_service_policy_ipaddress_mappings',
cols=['policy_target_group', 'service_policy_id'])
def upgrade():
op.drop_constraint('fk_organization_owners_id', 'organization', type_='foreignkey')
op.create_foreign_key('organization_owners_id_fkey', 'organization', 'team', ['owners_id'], ['id'])
op.add_column('organization', sa.Column('members_id', sa.Integer(), nullable=True))
op.create_foreign_key('organization_members_id_fkey', 'organization', 'team', ['members_id'], ['id'])
op.add_column('team', sa.Column('domain', sa.Unicode(length=253), nullable=True))
op.create_index(op.f('ix_team_domain'), 'team', ['domain'], unique=False)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('Shows', sa.Column('venue_id', sa.Integer(), nullable=False))
op.create_foreign_key(None, 'Shows', 'Venue', ['venue_id'], ['id'])
op.drop_column('Shows', 'venue_name')
op.drop_column('Shows', 'artist_name')
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('TOUR', sa.Column('location_id', sa.Integer(), nullable=False))
op.drop_constraint('TOUR_location_fkey', 'TOUR', type_='foreignkey')
op.create_foreign_key(None, 'TOUR', 'LOCATION', ['location_id'], ['id'])
op.drop_column('TOUR', 'location')
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_constraint('db_dimension_measure_id_fkey',
'db_dimension',
type_='foreignkey')
op.drop_constraint('db_page_parent_guid_fkey',
'db_page',
type_='foreignkey')
op.drop_constraint('db_upload_page_id_fkey',
'db_upload',
type_='foreignkey')
op.drop_constraint('db_page_pkey', 'db_page', type_='primary')
op.add_column('db_page',
sa.Column('parent_version', sa.String(), nullable=True))
op.add_column('db_page', sa.Column('version', sa.String(), nullable=True))
op.add_column('db_page',
sa.Column('created_at', sa.DateTime(), nullable=True))
op.add_column('db_page',
sa.Column('updated_at', sa.DateTime(), nullable=True))
op.add_column('db_dimension',
sa.Column('measure_version', sa.String(), nullable=True))
op.add_column('db_upload',
sa.Column('page_version', sa.String(), nullable=True))
op.execute("UPDATE db_page SET version = '1.0'")
op.execute(
"UPDATE db_page SET parent_version = '1.0' WHERE parent_guid IS NOT NULL"
)
op.execute("UPDATE db_page SET created_at = now()")
op.execute("UPDATE db_dimension SET measure_version = '1.0'")
op.execute("UPDATE db_upload SET page_version = '1.0'")
op.alter_column('db_page',
'version',
existing_type=sa.String(),
nullable=False)
op.alter_column('db_page',
'created_at',
existing_type=sa.DateTime(),
nullable=False)
op.alter_column('db_dimension',
'measure_id',
existing_type=sa.VARCHAR(length=255),
nullable=False)
op.alter_column('db_dimension',
'measure_version',
existing_type=sa.String(),
nullable=False)
op.alter_column('db_upload',
'page_id',
existing_type=sa.VARCHAR(length=255),
nullable=False)
op.alter_column('db_upload',
'page_version',
existing_type=sa.String(),
nullable=False)
op.create_primary_key('db_page_guid_version_pkey', 'db_page',
['guid', 'version'])
op.create_unique_constraint('uix_db_page_guid_version', 'db_page',
['guid', 'version'])
op.create_foreign_key('db_dimension_measure_id_version_fkey',
'db_dimension', 'db_page',
['measure_id', 'measure_version'],
['guid', 'version'])
op.create_foreign_key('db_upload_page_id_version_fkey', 'db_upload',
'db_page', ['page_id', 'page_version'],
['guid', 'version'])
op.create_foreign_key('db_page_parent_guid_version_fkey', 'db_page',
'db_page', ['parent_guid', 'parent_version'],
['guid', 'version'])
op.drop_column('db_page', 'page_json')
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('transactions',
sa.Column('object_user_id', sa.Integer(), nullable=True))
op.create_foreign_key(None, 'transactions', 'users', ['object_user_id'],
['id'])
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('users', sa.Column('client_id', sa.Integer(), nullable=True))
op.create_foreign_key(None, 'users', 'clients', ['client_id'],
['client_id'])
def upgrade():
# Enum tables (will be prepopulated with values through bulk_insert)
alarm_states = op.create_table('alarm_state',
sa.Column('name',
sa.String(length=20),
nullable=False),
sa.PrimaryKeyConstraint('name'),
mysql_charset='utf8mb4',
mysql_collate='utf8mb4_unicode_ci')
op.bulk_insert(alarm_states, [{
'name': 'UNDETERMINED'
}, {
'name': 'OK'
}, {
'name': 'ALARM'
}])
ad_severities = op.create_table('alarm_definition_severity',
sa.Column('name',
sa.String(length=20),
nullable=False),
sa.PrimaryKeyConstraint('name'),
mysql_charset='utf8mb4',
mysql_collate='utf8mb4_unicode_ci')
op.bulk_insert(ad_severities, [{
'name': 'LOW'
}, {
'name': 'MEDIUM'
}, {
'name': 'HIGH'
}, {
'name': 'CRITICAL'
}])
nm_types = op.create_table('notification_method_type',
sa.Column('name',
sa.String(length=20),
nullable=False),
sa.PrimaryKeyConstraint('name'),
mysql_charset='utf8mb4',
mysql_collate='utf8mb4_unicode_ci')
op.bulk_insert(nm_types, [{
'name': 'EMAIL'
}, {
'name': 'WEBHOOK'
}, {
'name': 'PAGERDUTY'
}])
stream_action_types = op.create_table('stream_actions_action_type',
sa.Column('name',
sa.String(length=20),
nullable=False),
sa.PrimaryKeyConstraint('name'),
mysql_charset='utf8mb4',
mysql_collate='utf8mb4_unicode_ci')
op.bulk_insert(stream_action_types, [{'name': 'FIRE'}, {'name': 'EXPIRE'}])
op.create_table(
'alarm_definition',
sa.Column('id', sa.String(length=36), nullable=False),
sa.Column('tenant_id', sa.String(length=36), nullable=False),
sa.Column('name',
sa.String(length=255),
nullable=False,
server_default=''),
sa.Column('description',
sa.String(length=255),
nullable=True,
server_default=None),
sa.Column('expression', sa.dialects.mysql.LONGTEXT(), nullable=False),
sa.Column('severity', sa.String(length=20), nullable=False),
sa.Column('match_by',
sa.String(length=255),
nullable=True,
server_default=''),
sa.Column('actions_enabled',
sa.Boolean(),
nullable=False,
server_default='1'),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=False),
sa.Column('deleted_at',
sa.DateTime(),
nullable=True,
server_default=None),
sa.PrimaryKeyConstraint('id'),
sa.Index('tenant_id', 'tenant_id'),
sa.Index('deleted_at', 'deleted_at'),
sa.Index('fk_alarm_definition_severity', 'severity'),
sa.ForeignKeyConstraint(['severity'],
['alarm_definition_severity.name']),
mysql_charset='utf8mb4',
mysql_collate='utf8mb4_unicode_ci')
op.create_table('alarm',
sa.Column('id', sa.String(length=36), nullable=False),
sa.Column('alarm_definition_id',
sa.String(length=36),
nullable=False,
server_default=''),
sa.Column('state', sa.String(length=20), nullable=False),
sa.Column('lifecycle_state',
sa.String(length=50, collation=False),
nullable=True,
server_default=None),
sa.Column('link',
sa.String(length=512, collation=False),
nullable=True,
server_default=None),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.Column('state_updated_at', sa.DateTime(),
nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=False),
sa.PrimaryKeyConstraint('id'),
sa.Index('alarm_definition_id', 'alarm_definition_id'),
sa.Index('fk_alarm_alarm_state', 'state'),
sa.ForeignKeyConstraint(['alarm_definition_id'],
['alarm_definition.id'],
name='fk_alarm_definition_id',
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['state'], ['alarm_state.name'],
name='fk_alarm_alarm_state'),
mysql_charset='utf8mb4',
mysql_collate='utf8mb4_unicode_ci')
op.create_table(
'notification_method',
sa.Column('id', sa.String(length=36), nullable=False),
sa.Column('tenant_id', sa.String(length=36), nullable=False),
sa.Column('name',
sa.String(length=250),
nullable=True,
server_default=None),
sa.Column(
'type',
sa.String(length=20),
# Note: the typo below is deliberate since we need to match
# the constraint name from the SQL script where it is
# misspelled as well.
sa.ForeignKey('notification_method_type.name',
name='fk_alarm_noticication_method_type'),
nullable=False),
sa.Column('address',
sa.String(length=512),
nullable=True,
server_default=None),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=False),
sa.PrimaryKeyConstraint('id'),
mysql_charset='utf8mb4',
mysql_collate='utf8mb4_unicode_ci')
op.create_table(
'alarm_action',
sa.Column(
'alarm_definition_id',
sa.String(length=36),
nullable=False,
),
sa.Column('alarm_state', sa.String(length=20), nullable=False),
sa.Column('action_id', sa.String(length=36), nullable=False),
sa.PrimaryKeyConstraint('alarm_definition_id', 'alarm_state',
'action_id'),
sa.ForeignKeyConstraint(['action_id'], ['notification_method.id'],
name='fk_alarm_action_notification_method_id',
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['alarm_state'], ['alarm_state.name']),
sa.ForeignKeyConstraint(['alarm_definition_id'],
['alarm_definition.id'],
ondelete='CASCADE'),
mysql_charset='utf8mb4',
mysql_collate='utf8mb4_unicode_ci')
op.create_table(
'alarm_metric',
sa.Column('alarm_id', sa.String(length=36), nullable=False),
sa.Column('metric_definition_dimensions_id',
sa.BINARY(20),
nullable=False,
server_default='\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0'),
sa.PrimaryKeyConstraint('alarm_id', 'metric_definition_dimensions_id'),
sa.Index('alarm_id', 'alarm_id'),
sa.Index('metric_definition_dimensions_id',
'metric_definition_dimensions_id'),
mysql_charset='utf8mb4',
mysql_collate='utf8mb4_unicode_ci')
# For some mysterious alembic/sqlalchemy reason this foreign key constraint
# ends up missing when specified upon table creation. Hence we need to add
# it through an ALTER TABLE operation:
op.create_foreign_key('fk_alarm_id',
'alarm_metric',
'alarm', ['alarm_id'], ['id'],
ondelete='CASCADE')
op.create_table(
'metric_definition',
sa.Column('id',
sa.BINARY(20),
nullable=False,
server_default='\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0'),
sa.Column('name', sa.String(length=255), nullable=False),
sa.Column('tenant_id', sa.String(length=36), nullable=False),
sa.Column('region',
sa.String(length=255),
nullable=False,
server_default=''),
sa.PrimaryKeyConstraint('id'),
mysql_charset='utf8mb4',
mysql_collate='utf8mb4_unicode_ci')
op.create_table(
'metric_definition_dimensions',
sa.Column('id',
sa.BINARY(20),
nullable=False,
server_default='\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0'),
sa.Column('metric_definition_id',
sa.BINARY(20),
nullable=False,
server_default='\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0'),
sa.Column('metric_dimension_set_id',
sa.BINARY(20),
nullable=False,
server_default='\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0'),
sa.PrimaryKeyConstraint('id'),
sa.Index('metric_definition_id', 'metric_definition_id'),
sa.Index('metric_dimension_set_id', 'metric_dimension_set_id'),
mysql_charset='utf8mb4',
mysql_collate='utf8mb4_unicode_ci')
# mysql limits the size of a unique key to 767 bytes. The utf8mb4 charset
# requires 4 bytes to be allocated for each character while the utf8
# charset requires 3 bytes. The utf8 charset should be sufficient for any
# reasonable characters, see the definition of supplementary characters for
# what it doesn't support. Even with utf8, the unique key length would be
# 785 bytes so only a subset of the name is used. Potentially the size of
# the name should be limited to 250 characters which would resolve this
# issue.
#
# The unique key is required to allow high performance inserts without
# doing a select by using the "insert into metric_dimension ... on
# duplicate key update dimension_set_id=dimension_set_id syntax
op.create_table(
'metric_dimension',
sa.Column('dimension_set_id',
sa.BINARY(20),
nullable=False,
server_default='\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0'),
sa.Column('name',
sa.String(length=255),
nullable=False,
server_default=''),
sa.Column('value',
sa.String(length=255),
nullable=False,
server_default=''),
sa.Index('metric_dimension_key',
'dimension_set_id',
'name',
unique=True,
mysql_length={'name': 252}),
sa.Index('dimension_set_id', 'dimension_set_id'),
mysql_charset='utf8',
mysql_collate='utf8_unicode_ci',
mysql_comment='PRIMARY KEY (`id`)')
op.create_table('sub_alarm_definition',
sa.Column('id', sa.String(length=36), nullable=False),
sa.Column('alarm_definition_id',
sa.String(length=36),
sa.ForeignKey('alarm_definition.id',
ondelete='CASCADE',
name='fk_sub_alarm_definition'),
nullable=False,
server_default=''),
sa.Column('function', sa.String(length=10),
nullable=False),
sa.Column('metric_name',
sa.String(length=100),
nullable=True,
server_default=None),
sa.Column('operator', sa.String(length=5), nullable=False),
sa.Column('threshold',
sa.dialects.mysql.DOUBLE(),
nullable=False),
sa.Column('period', sa.Integer(), nullable=False),
sa.Column('periods', sa.Integer(), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=False),
sa.PrimaryKeyConstraint('id'),
mysql_charset='utf8mb4',
mysql_collate='utf8mb4_unicode_ci')
op.create_table('sub_alarm_definition_dimension',
sa.Column('sub_alarm_definition_id',
sa.String(length=36),
sa.ForeignKey(
'sub_alarm_definition.id',
ondelete='CASCADE',
name='fk_sub_alarm_definition_dimension'),
nullable=False,
server_default=''),
sa.Column('dimension_name',
sa.String(length=255),
nullable=False,
server_default=''),
sa.Column('value',
sa.String(length=255),
nullable=True,
server_default=None),
mysql_charset='utf8mb4',
mysql_collate='utf8mb4_unicode_ci')
op.create_table(
'sub_alarm',
sa.Column('id',
sa.dialects.mysql.VARCHAR(length=36,
collation='utf8mb4_unicode_ci'),
nullable=False),
sa.Column('alarm_id',
sa.dialects.mysql.VARCHAR(length=36,
collation='utf8mb4_unicode_ci'),
sa.ForeignKey('alarm.id',
ondelete='CASCADE',
name='fk_sub_alarm'),
nullable=False,
server_default=''),
sa.Column('sub_expression_id',
sa.dialects.mysql.VARCHAR(length=36,
collation='utf8mb4_unicode_ci'),
sa.ForeignKey('sub_alarm_definition.id',
name='fk_sub_alarm_expr'),
nullable=False,
server_default=''),
sa.Column('expression',
sa.dialects.mysql.LONGTEXT(collation='utf8mb4_unicode_ci'),
nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=False),
sa.PrimaryKeyConstraint('id'))
op.create_table('schema_migrations',
sa.Column('version', sa.String(length=255),
nullable=False),
sa.UniqueConstraint('version',
name='unique_schema_migrations'),
mysql_charset='latin1')
op.create_table('stream_definition',
sa.Column('id', sa.String(length=36), nullable=False),
sa.Column('tenant_id',
sa.String(length=36),
nullable=False),
sa.Column('name',
sa.String(length=190),
nullable=False,
server_default=''),
sa.Column('description',
sa.String(length=255),
nullable=True,
server_default=None),
sa.Column('select_by',
sa.dialects.mysql.LONGTEXT(),
nullable=True,
server_default=None),
sa.Column('group_by',
sa.dialects.mysql.LONGTEXT(length=20),
nullable=True,
server_default=None),
sa.Column('fire_criteria',
sa.dialects.mysql.LONGTEXT(length=20),
nullable=True,
server_default=None),
sa.Column('expiration',
sa.dialects.mysql.INTEGER(display_width=10,
unsigned=True),
nullable=True,
server_default='0'),
sa.Column('actions_enabled',
sa.Boolean(),
nullable=False,
server_default='1'),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=False),
sa.Column('deleted_at',
sa.DateTime(),
nullable=True,
server_default=None),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('tenant_id',
'name',
name='tenant_name'),
sa.Index('name', 'name'),
sa.Index('tenant_id', 'tenant_id'),
sa.Index('deleted_at', 'deleted_at'),
sa.Index('created_at', 'created_at'),
sa.Index('updated_at', 'updated_at'),
mysql_charset='utf8mb4',
mysql_collate='utf8mb4_unicode_ci')
op.create_table(
'stream_actions',
sa.Column('stream_definition_id',
sa.String(length=36),
sa.ForeignKey('stream_definition.id',
name='fk_stream_action_stream_definition_id',
ondelete='CASCADE'),
nullable=False),
sa.Column('action_id',
sa.String(length=36),
sa.ForeignKey('notification_method.id',
name='fk_stream_action_notification_method_id',
ondelete='CASCADE'),
nullable=False),
sa.Column('action_type',
sa.String(length=20),
sa.ForeignKey('stream_actions_action_type.name'),
nullable=False),
sa.PrimaryKeyConstraint('stream_definition_id', 'action_id',
'action_type'),
sa.Index('stream_definition_id', 'stream_definition_id'),
sa.Index('action_type', 'action_type'),
mysql_charset='utf8mb4',
mysql_collate='utf8mb4_unicode_ci')
op.create_table(
'event_transform',
sa.Column('id',
sa.dialects.mysql.VARCHAR(length=36,
charset='utf8mb4',
collation='utf8mb4_unicode_ci'),
nullable=False),
sa.Column('tenant_id',
sa.dialects.mysql.VARCHAR(length=36,
charset='utf8mb4',
collation='utf8mb4_unicode_ci'),
nullable=False),
sa.Column('name',
sa.dialects.mysql.VARCHAR(length=64,
charset='utf8mb4',
collation='utf8mb4_unicode_ci'),
nullable=False),
sa.Column('description',
sa.dialects.mysql.VARCHAR(length=250,
charset='utf8mb4',
collation='utf8mb4_unicode_ci'),
nullable=False),
sa.Column('specification',
sa.dialects.mysql.LONGTEXT(charset='utf8mb4',
collation='utf8mb4_unicode_ci'),
nullable=False),
sa.Column('enabled', sa.Boolean(), nullable=True, server_default=None),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=False),
sa.Column('deleted_at',
sa.DateTime(),
nullable=True,
server_default=None),
sa.PrimaryKeyConstraint('id'),
sa.Index('name', 'name'),
sa.Index('tenant_id', 'tenant_id'),
sa.Index('deleted_at', 'deleted_at'),
sa.Index('created_at', 'created_at'),
sa.Index('updated_at', 'updated_at'),
sa.UniqueConstraint('tenant_id', 'name', name='tenant_name'),
mysql_charset='utf8mb4')
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('video__game__achievements',
sa.Column('video_game_id', sa.Integer(), nullable=True))
op.create_foreign_key(None, 'video__game__achievements', 'video__game',
['video_game_id'], ['id'])
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_constraint(None, 'certs', type_='foreignkey')
op.create_foreign_key('certs_ibfk_3', 'certs', 'user', ['transfer_id'], ['id'])
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('conversation', sa.Column('board_id', sa.Integer(), nullable=True))
op.create_foreign_key(None, 'conversation', 'board', ['board_id'], ['id'])
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('roles', sa.Column('user_id', sa.Integer(), nullable=True))
op.create_foreign_key(None, 'roles', 'user', ['user_id'], ['id'])
op.drop_column('roles', 'capabilities')
def create_fk_if_absent(source_table, dest_table, source_columns, dest_columns):
if find_fk(source_table, source_columns) is None:
op.create_foreign_key(None, source_table, dest_table,
source_columns, dest_columns)
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.alter_column('authorities',
'owner',
existing_type=sa.VARCHAR(length=128),
nullable=True)
op.drop_column('authorities', 'not_after')
op.drop_column('authorities', 'bits')
op.drop_column('authorities', 'cn')
op.drop_column('authorities', 'not_before')
op.add_column('certificates',
sa.Column('root_authority_id', sa.Integer(), nullable=True))
op.alter_column('certificates',
'body',
existing_type=sa.TEXT(),
nullable=False)
op.alter_column('certificates',
'owner',
existing_type=sa.VARCHAR(length=128),
nullable=True)
op.drop_constraint(u'certificates_authority_id_fkey',
'certificates',
type_='foreignkey')
op.create_foreign_key(None,
'certificates',
'authorities', ['authority_id'], ['id'],
ondelete='CASCADE')
op.create_foreign_key(None,
'certificates',
'authorities', ['root_authority_id'], ['id'],
ondelete='CASCADE')
### end Alembic commands ###
# link existing certificate to their authority certificates
conn = op.get_bind()
for id, body, owner in conn.execute(
text('select id, body, owner from authorities')):
if not owner:
owner = "lemur@nobody"
# look up certificate by body, if duplications are found, pick one
stmt = text('select id from certificates where body=:body')
stmt = stmt.bindparams(body=body)
root_certificate = conn.execute(stmt).fetchone()
if root_certificate:
stmt = text(
'update certificates set root_authority_id=:root_authority_id where id=:id'
)
stmt = stmt.bindparams(root_authority_id=id,
id=root_certificate[0])
op.execute(stmt)
# link owner roles to their authorities
stmt = text('select id from roles where name=:name')
stmt = stmt.bindparams(name=owner)
owner_role = conn.execute(stmt).fetchone()
if not owner_role:
stmt = text(
'insert into roles (name, description) values (:name, :description)'
)
stmt = stmt.bindparams(
name=owner,
description='Lemur generated role or existing owner.')
op.execute(stmt)
stmt = text('select id from roles where name=:name')
stmt = stmt.bindparams(name=owner)
owner_role = conn.execute(stmt).fetchone()
stmt = text(
'select * from roles_authorities where role_id=:role_id and authority_id=:authority_id'
)
stmt = stmt.bindparams(role_id=owner_role[0], authority_id=id)
exists = conn.execute(stmt).fetchone()
if not exists:
stmt = text(
'insert into roles_authorities (role_id, authority_id) values (:role_id, :authority_id)'
)
stmt = stmt.bindparams(role_id=owner_role[0], authority_id=id)
op.execute(stmt)
# link owner roles to their certificates
for id, owner in conn.execute(text('select id, owner from certificates')):
if not owner:
owner = "lemur@nobody"
stmt = text('select id from roles where name=:name')
stmt = stmt.bindparams(name=owner)
owner_role = conn.execute(stmt).fetchone()
if not owner_role:
stmt = text(
'insert into roles (name, description) values (:name, :description)'
)
stmt = stmt.bindparams(
name=owner,
description='Lemur generated role or existing owner.')
op.execute(stmt)
# link owner roles to their authorities
stmt = text('select id from roles where name=:name')
stmt = stmt.bindparams(name=owner)
owner_role = conn.execute(stmt).fetchone()
stmt = text(
'select * from roles_certificates where role_id=:role_id and certificate_id=:certificate_id'
)
stmt = stmt.bindparams(role_id=owner_role[0], certificate_id=id)
exists = conn.execute(stmt).fetchone()
if not exists:
stmt = text(
'insert into roles_certificates (role_id, certificate_id) values (:role_id, :certificate_id)'
)
stmt = stmt.bindparams(role_id=owner_role[0], certificate_id=id)
op.execute(stmt)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('tarefa', sa.Column('projeto_id',
sa.Integer(),
nullable=True))
op.create_foreign_key(None, 'tarefa', 'projeto', ['projeto_id'], ['id'])
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('results', sa.Column('song_id', sa.Integer(), nullable=True))
op.create_foreign_key(None, 'results', 'songs', ['song_id'], ['id'])
op.drop_column('results', 'song')
def upgrade() -> None:
op.add_column("venue",
sa.Column("businessUnitId", sa.Integer(), nullable=True))
op.create_foreign_key(None, "venue", "business_unit", ["businessUnitId"],
["id"])
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('verifier_progression',
sa.Column('equipped_quote_id', sa.Integer(), nullable=True))
op.create_foreign_key(None, 'verifier_progression', 'verifier_quote',
['equipped_quote_id'], ['id'])
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_foreign_key(None, 'comment', 'pictures', ['picId'], ['id'])
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_constraint(u'AdmissionType_school_id_fkey',
'AdmissionType',
type_='foreignkey')
op.create_foreign_key(None,
'AdmissionType',
'SchoolInfo', ['school_id'], ['id'],
ondelete='SET NULL')
op.add_column('ForgotPassword',
sa.Column('school_id', sa.String(), nullable=True))
op.create_foreign_key(None,
'ForgotPassword',
'SchoolInfo', ['school_id'], ['id'],
ondelete='SET NULL')
op.add_column('PercentagePrice',
sa.Column('school_id', sa.String(), nullable=True))
op.create_unique_constraint(None, 'PercentagePrice', ['school_id'])
op.create_foreign_key(None, 'PercentagePrice', 'SchoolInfo', ['school_id'],
['id'])
op.add_column('SchoolInfo',
sa.Column('admin_status', sa.Boolean(), nullable=True))
op.add_column('SchoolInfo', sa.Column('alias', sa.String(), nullable=True))
op.add_column('SchoolInfo', sa.Column('email', sa.String(),
nullable=False))
op.add_column('SchoolInfo', sa.Column('name', sa.String(), nullable=False))
op.add_column('SchoolInfo',
sa.Column('password', sa.String(), nullable=True))
op.add_column('SchoolInfo',
sa.Column('reset_password', sa.Boolean(), nullable=True))
op.add_column('SchoolInfo',
sa.Column('user_type_id', sa.String(), nullable=True))
op.alter_column('SchoolInfo',
'account_number',
existing_type=sa.VARCHAR(),
nullable=False)
op.alter_column('SchoolInfo',
'bank_name',
existing_type=sa.VARCHAR(),
nullable=False)
op.drop_constraint(u'SchoolInfo_school_id_key',
'SchoolInfo',
type_='unique')
op.create_unique_constraint(None, 'SchoolInfo', ['email'])
op.create_unique_constraint(None, 'SchoolInfo', ['name'])
op.drop_constraint(u'SchoolInfo_school_id_fkey',
'SchoolInfo',
type_='foreignkey')
op.create_foreign_key(None,
'SchoolInfo',
'UserType', ['user_type_id'], ['id'],
ondelete='SET NULL')
op.drop_column('SchoolInfo', 'school_id')
op.add_column('User', sa.Column('school_id', sa.String(), nullable=True))
op.create_foreign_key(None, 'User', 'SchoolInfo', ['school_id'], ['id'])
op.add_column('Wallet', sa.Column('school_id', sa.String(), nullable=True))
op.create_foreign_key(None, 'Wallet', 'SchoolInfo', ['school_id'], ['id'])
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('Verify', sa.Column('user_id', sa.INTEGER(), nullable=True))
op.create_foreign_key(None, 'Verify', 'User', ['user_id'], ['id'])
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('Cast', sa.Column('movie_id', sa.Integer(), nullable=False))
op.create_foreign_key(None, 'Cast', 'Movie', ['movie_id'], ['id'])
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_constraint(op.f('users_email_key'),
'users',
schema='sampledb',
type_='unique')
op.drop_index(op.f('idx_sampledb_users_full_name'),
table_name='users',
schema='sampledb')
op.drop_index(op.f('idx_sampledb_users_email'),
table_name='users',
schema='sampledb')
op.drop_constraint(op.f('provenance_dataset_parent_id_datasets_fkey'),
'provenance',
schema='sampledb',
type_='foreignkey')
op.drop_constraint(op.f('provenance_dataset_id_datasets_fkey'),
'provenance',
schema='sampledb',
type_='foreignkey')
op.create_foreign_key('provenance_dataset_parent_id_datasets_fkey',
'provenance',
'datasets', ['dataset_parent_id'], ['id'],
source_schema='sampledb',
referent_schema='sampledb')
op.create_foreign_key('provenance_dataset_id_datasets_fkey',
'provenance',
'datasets', ['dataset_id'], ['id'],
source_schema='sampledb',
referent_schema='sampledb')
op.drop_index(op.f('idx_sampledb_provenance_dataset_parent_id'),
table_name='provenance',
schema='sampledb')
op.drop_index(op.f('idx_sampledb_provenance_dataset_id'),
table_name='provenance',
schema='sampledb')
op.add_column('datasets',
sa.Column('midias_table_name',
sa.VARCHAR(),
autoincrement=False,
nullable=True),
schema='sampledb')
op.drop_constraint(op.f('datasets_user_id_users_fkey'),
'datasets',
schema='sampledb',
type_='foreignkey')
op.drop_constraint(
op.f('datasets_classification_system_id_class_systems_fkey'),
'datasets',
schema='sampledb',
type_='foreignkey')
op.drop_constraint(op.f('datasets_collect_method_id_collect_method_fkey'),
'datasets',
schema='sampledb',
type_='foreignkey')
op.drop_constraint(op.f('datasets_version_successor_datasets_fkey'),
'datasets',
schema='sampledb',
type_='foreignkey')
op.drop_constraint(op.f('datasets_version_predecessor_datasets_fkey'),
'datasets',
schema='sampledb',
type_='foreignkey')
op.create_foreign_key('datasets_user_id_users_fkey',
'datasets',
'users', ['user_id'], ['id'],
source_schema='sampledb',
referent_schema='sampledb')
op.create_foreign_key(
'datasets_classification_system_id_class_systems_fkey',
'datasets',
'class_systems', ['classification_system_id'], ['id'],
source_schema='sampledb',
referent_schema='lccs')
op.create_foreign_key('datasets_collect_method_id_collect_method_fkey',
'datasets',
'collect_method', ['collect_method_id'], ['id'],
source_schema='sampledb',
referent_schema='sampledb')
op.drop_index(op.f('idx_sampledb_datasets_user_id'),
table_name='datasets',
schema='sampledb')
op.drop_index(op.f('idx_sampledb_datasets_start_date'),
table_name='datasets',
schema='sampledb')
op.drop_index(op.f('idx_sampledb_datasets_name'),
table_name='datasets',
schema='sampledb')
op.drop_index(op.f('idx_sampledb_datasets_collect_method_id'),
table_name='datasets',
schema='sampledb')
op.drop_index(op.f('idx_sampledb_datasets_classification_system_id'),
table_name='datasets',
schema='sampledb')
op.drop_index('idx_datasets_start_date_end_date',
table_name='datasets',
schema='sampledb')
op.drop_constraint(op.f('datasets_name_key'),
'datasets',
schema='sampledb',
type_='unique')
op.alter_column('datasets',
'version',
existing_type=sa.VARCHAR(),
nullable=True,
schema='sampledb')
op.alter_column('datasets',
'name',
existing_type=sa.VARCHAR(),
nullable=True,
schema='sampledb')
op.drop_column('datasets', 'version_successor', schema='sampledb')
op.drop_column('datasets', 'version_predecessor', schema='sampledb')
op.drop_column('datasets', 'title', schema='sampledb')
op.drop_column('datasets', 'is_public', schema='sampledb')
op.alter_column('datasets',
'dataset_table_name',
new_column_name='observation_table_name',
schema='sampledb')
op.drop_index(op.f('idx_sampledb_collect_method_name'),
table_name='collect_method',
schema='sampledb')
op.drop_constraint(op.f('collect_method_name_key'),
'collect_method',
schema='sampledb',
type_='unique')
op.alter_column('collect_method',
'name',
existing_type=sa.VARCHAR(),
nullable=True,
schema='sampledb')
session = Session(bind=op.get_bind())
dataset_type = DatasetType()
dataset_type.drop()
session.commit()
session.execute("CREATE OR REPLACE VIEW {} AS " \
"SELECT datasets.created_at, datasets.updated_at, datasets.id, datasets.name, " \
"datasets.start_date, datasets.end_date, datasets.observation_table_name, " \
"datasets.midias_table_name, datasets.metadata_json, datasets.version, " \
"datasets.description, class_systems.name AS classification_system_name, " \
"users.full_name AS user_name, collect_method.name AS collect_method " \
"FROM {} AS datasets, {} AS class_systems, {} AS users, {} AS collect_method " \
"WHERE users.id = datasets.user_id " \
"AND class_systems.id = datasets.classification_system_id " \
"AND collect_method.id = datasets.collect_method_id;"
.format(DatasetView.__table__,
Datasets.__table__,
'lccs.class_systems',
'sampledb.users',
CollectMethod.__table__)
)
session.commit()
def downgrade():
op.create_table(
'team',
sa.Column(
'id',
sa.INTEGER(),
server_default=sa.text("nextval('team_id_seq'::regclass)"),
nullable=False,
),
sa.Column('created_at',
postgresql.TIMESTAMP(),
autoincrement=False,
nullable=False),
sa.Column('updated_at',
postgresql.TIMESTAMP(),
autoincrement=False,
nullable=False),
sa.Column('title',
sa.VARCHAR(length=250),
autoincrement=False,
nullable=False),
sa.Column('userid',
sa.VARCHAR(length=22),
autoincrement=False,
nullable=False),
sa.Column('owners', sa.BOOLEAN(), autoincrement=False, nullable=False),
sa.Column('orgid',
sa.VARCHAR(length=22),
autoincrement=False,
nullable=False),
sa.Column('members', sa.BOOLEAN(), autoincrement=False,
nullable=False),
sa.PrimaryKeyConstraint('id', name='team_pkey'),
postgresql_ignore_search_path=False,
)
op.create_table(
'organization',
sa.Column(
'id',
sa.INTEGER(),
server_default=sa.text("nextval('organization_id_seq'::regclass)"),
nullable=False,
),
sa.Column('created_at',
postgresql.TIMESTAMP(),
autoincrement=False,
nullable=False),
sa.Column('updated_at',
postgresql.TIMESTAMP(),
autoincrement=False,
nullable=False),
sa.Column('domain',
sa.VARCHAR(length=253),
autoincrement=False,
nullable=True),
sa.Column('logo_image',
sa.VARCHAR(length=250),
autoincrement=False,
nullable=True),
sa.Column('cover_image',
sa.VARCHAR(length=250),
autoincrement=False,
nullable=True),
sa.Column('description',
sa.TEXT(),
autoincrement=False,
nullable=False),
sa.Column('status', sa.INTEGER(), autoincrement=False, nullable=False),
sa.Column('userid',
sa.VARCHAR(length=22),
autoincrement=False,
nullable=False),
sa.Column('name',
sa.VARCHAR(length=250),
autoincrement=False,
nullable=False),
sa.Column('title',
sa.VARCHAR(length=250),
autoincrement=False,
nullable=False),
sa.Column('admin_team_id',
sa.INTEGER(),
autoincrement=False,
nullable=True),
sa.Column('hiring_team_id',
sa.INTEGER(),
autoincrement=False,
nullable=True),
sa.ForeignKeyConstraint(
['admin_team_id'],
['team.id'],
name='organization_admin_team_id_fkey',
ondelete='SET NULL',
),
sa.ForeignKeyConstraint(
['hiring_team_id'],
['team.id'],
name='organization_hiring_team_id_fkey',
ondelete='SET NULL',
),
sa.PrimaryKeyConstraint('id', name='organization_pkey'),
postgresql_ignore_search_path=False,
)
op.create_table(
'org_location',
sa.Column('id', sa.INTEGER(), nullable=False),
sa.Column('created_at',
postgresql.TIMESTAMP(),
autoincrement=False,
nullable=False),
sa.Column('updated_at',
postgresql.TIMESTAMP(),
autoincrement=False,
nullable=False),
sa.Column('latitude', sa.NUMERIC(), autoincrement=False,
nullable=True),
sa.Column('longitude',
sa.NUMERIC(),
autoincrement=False,
nullable=True),
sa.Column('org_id', sa.INTEGER(), autoincrement=False, nullable=False),
sa.Column('title',
sa.VARCHAR(length=80),
autoincrement=False,
nullable=True),
sa.Column('address1',
sa.VARCHAR(length=80),
autoincrement=False,
nullable=True),
sa.Column('address2',
sa.VARCHAR(length=80),
autoincrement=False,
nullable=True),
sa.Column('city',
sa.VARCHAR(length=80),
autoincrement=False,
nullable=True),
sa.Column('state',
sa.VARCHAR(length=80),
autoincrement=False,
nullable=True),
sa.Column('postcode',
sa.VARCHAR(length=16),
autoincrement=False,
nullable=True),
sa.Column('country',
sa.VARCHAR(length=80),
autoincrement=False,
nullable=True),
sa.Column('geonameid',
sa.INTEGER(),
autoincrement=False,
nullable=True),
sa.Column('url_id', sa.INTEGER(), autoincrement=False, nullable=False),
sa.ForeignKeyConstraint(['org_id'], ['organization.id'],
name='org_location_org_id_fkey'),
sa.PrimaryKeyConstraint('id', name='org_location_pkey'),
)
op.create_table(
'users_teams',
sa.Column('created_at',
postgresql.TIMESTAMP(),
autoincrement=False,
nullable=False),
sa.Column('updated_at',
postgresql.TIMESTAMP(),
autoincrement=False,
nullable=False),
sa.Column('user_id', sa.INTEGER(), autoincrement=False,
nullable=False),
sa.Column('team_id', sa.INTEGER(), autoincrement=False,
nullable=False),
sa.ForeignKeyConstraint(['team_id'], ['team.id'],
name='users_teams_team_id_fkey'),
sa.ForeignKeyConstraint(['user_id'], ['user.id'],
name='users_teams_user_id_fkey'),
sa.PrimaryKeyConstraint('user_id', 'team_id', name='users_teams_pkey'),
)
op.add_column(
'jobpost',
sa.Column('org_id', sa.INTEGER(), autoincrement=False, nullable=True))
op.create_foreign_key('jobpost_org_id_fkey', 'jobpost', 'organization',
['org_id'], ['id'])
op.create_index('ix_jobpost_org_id', 'jobpost', ['org_id'], unique=False)
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('musical_piece', sa.Column('author_id', mysql.INTEGER(display_width=11), autoincrement=False, nullable=True))
op.drop_constraint(None, 'musical_piece', type_='foreignkey')
op.create_foreign_key('musical_piece_ibfk_1', 'musical_piece', 'person', ['author_id'], ['id'])
op.drop_column('musical_piece', 'composer_id')
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('blogposts',
sa.Column('blogpic_id', sa.Integer(), nullable=True))
op.create_foreign_key(None, 'blogposts', 'blogpics', ['blogpic_id'],
['id'])
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_constraint(None, 'attendings', type_='foreignkey')
op.drop_constraint(None, 'attendings', type_='foreignkey')
op.create_foreign_key('attendings_user_id_fkey', 'attendings', 'users', ['user_id'], ['netid'])
op.create_foreign_key('attendings_post_id_fkey', 'attendings', 'postings', ['post_id'], ['id'])
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
session = Session(bind=op.get_bind())
session.execute(f"DROP VIEW IF EXISTS {DatasetView.__table__}")
dataset_type = DatasetType()
dataset_type.create()
session.commit()
op.alter_column('collect_method',
'name',
existing_type=sa.VARCHAR(),
nullable=False,
schema='sampledb')
op.create_unique_constraint(op.f('collect_method_name_key'),
'collect_method', ['name'],
schema='sampledb')
op.create_index(op.f('idx_sampledb_collect_method_name'),
'collect_method', ['name'],
unique=False,
schema='sampledb')
op.alter_column('datasets',
'observation_table_name',
new_column_name='dataset_table_name',
schema='sampledb')
op.add_column('datasets',
sa.Column('is_public',
sa.Boolean(),
nullable=False,
server_default='t'),
schema='sampledb')
op.add_column('datasets',
sa.Column('title',
sa.String(length=255),
nullable=False,
server_default='Titulo'),
schema='sampledb')
op.add_column('datasets',
sa.Column('version_predecessor', sa.Integer(),
nullable=True),
schema='sampledb')
op.add_column('datasets',
sa.Column('version_successor', sa.Integer(), nullable=True),
schema='sampledb')
op.alter_column('datasets',
'name',
existing_type=sa.VARCHAR(),
nullable=False,
schema='sampledb')
op.alter_column('datasets',
'version',
existing_type=sa.VARCHAR(),
nullable=False,
schema='sampledb')
op.create_unique_constraint(op.f('datasets_name_key'),
'datasets', ['name', 'version'],
schema='sampledb')
op.create_index('idx_datasets_start_date_end_date',
'datasets', ['start_date', 'end_date'],
unique=False,
schema='sampledb')
op.create_index(op.f('idx_sampledb_datasets_classification_system_id'),
'datasets', ['classification_system_id'],
unique=False,
schema='sampledb')
op.create_index(op.f('idx_sampledb_datasets_collect_method_id'),
'datasets', ['collect_method_id'],
unique=False,
schema='sampledb')
op.create_index(op.f('idx_sampledb_datasets_name'),
'datasets', ['name'],
unique=False,
schema='sampledb')
op.create_index(op.f('idx_sampledb_datasets_start_date'),
'datasets', [sa.text('start_date DESC')],
unique=False,
schema='sampledb')
op.create_index(op.f('idx_sampledb_datasets_user_id'),
'datasets', ['user_id'],
unique=False,
schema='sampledb')
op.drop_constraint('datasets_collect_method_id_collect_method_fkey',
'datasets',
schema='sampledb',
type_='foreignkey')
op.drop_constraint('datasets_classification_system_id_class_systems_fkey',
'datasets',
schema='sampledb',
type_='foreignkey')
op.drop_constraint('datasets_user_id_users_fkey',
'datasets',
schema='sampledb',
type_='foreignkey')
op.create_foreign_key(op.f('datasets_version_predecessor_datasets_fkey'),
'datasets',
'datasets', ['version_predecessor'], ['id'],
source_schema='sampledb',
referent_schema='sampledb',
onupdate='CASCADE',
ondelete='CASCADE')
op.create_foreign_key(op.f('datasets_version_successor_datasets_fkey'),
'datasets',
'datasets', ['version_successor'], ['id'],
source_schema='sampledb',
referent_schema='sampledb',
onupdate='CASCADE',
ondelete='CASCADE')
op.create_foreign_key(
op.f('datasets_collect_method_id_collect_method_fkey'),
'datasets',
'collect_method', ['collect_method_id'], ['id'],
source_schema='sampledb',
referent_schema='sampledb',
onupdate='CASCADE',
ondelete='CASCADE')
op.create_foreign_key(
op.f('datasets_classification_system_id_class_systems_fkey'),
'datasets',
'class_systems', ['classification_system_id'], ['id'],
source_schema='sampledb',
referent_schema='lccs',
onupdate='CASCADE',
ondelete='CASCADE')
op.create_foreign_key(op.f('datasets_user_id_users_fkey'),
'datasets',
'users', ['user_id'], ['id'],
source_schema='sampledb',
referent_schema='sampledb',
ondelete='CASCADE')
op.drop_column('datasets', 'midias_table_name', schema='sampledb')
op.create_index(op.f('idx_sampledb_provenance_dataset_id'),
'provenance', ['dataset_id'],
unique=False,
schema='sampledb')
op.create_index(op.f('idx_sampledb_provenance_dataset_parent_id'),
'provenance', ['dataset_parent_id'],
unique=False,
schema='sampledb')
op.drop_constraint('provenance_dataset_id_datasets_fkey',
'provenance',
schema='sampledb',
type_='foreignkey')
op.drop_constraint('provenance_dataset_parent_id_datasets_fkey',
'provenance',
schema='sampledb',
type_='foreignkey')
op.create_foreign_key(op.f('provenance_dataset_id_datasets_fkey'),
'provenance',
'datasets', ['dataset_id'], ['id'],
source_schema='sampledb',
referent_schema='sampledb',
onupdate='CASCADE',
ondelete='CASCADE')
op.create_foreign_key(op.f('provenance_dataset_parent_id_datasets_fkey'),
'provenance',
'datasets', ['dataset_parent_id'], ['id'],
source_schema='sampledb',
referent_schema='sampledb',
onupdate='CASCADE',
ondelete='CASCADE')
op.create_index(op.f('idx_sampledb_users_email'),
'users', ['email'],
unique=False,
schema='sampledb')
op.create_index(op.f('idx_sampledb_users_full_name'),
'users', ['full_name'],
unique=False,
schema='sampledb')
op.create_unique_constraint(op.f('users_email_key'),
'users', ['email'],
schema='sampledb')
session.execute("CREATE VIEW {} AS " \
"SELECT datasets.created_at, datasets.updated_at, datasets.id, datasets.name, " \
"datasets.title, datasets.start_date, datasets.end_date, datasets.dataset_table_name, " \
"datasets.version, datasets.version_successor, datasets.version_predecessor, " \
"datasets.description, class_systems.name AS classification_system_name, " \
"class_systems.id AS classification_system_id, class_systems.version AS classification_system_version, " \
"users.id AS user_id, users.full_name AS user_name, collect_method.name AS collect_method_name, " \
"collect_method.id AS collect_method_id, " \
"datasets.metadata_json, datasets.is_public "
"FROM {} AS datasets, {} AS class_systems, {} AS users, {} AS collect_method " \
"WHERE users.id = datasets.user_id " \
"AND class_systems.id = datasets.classification_system_id " \
"AND collect_method.id = datasets.collect_method_id;"
.format(DatasetView.__table__,
Datasets.__table__,
'lccs.class_systems',
'sampledb.users',
CollectMethod.__table__)
)
session.commit()
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('fp_attribute_group',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('sort_order', sa.Integer(), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_table('fp_banner', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=64), nullable=False),
sa.Column('status', sa.SmallInteger(), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'fp_cart', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('api_id', sa.Integer(), nullable=True),
sa.Column('customer_id', sa.Integer(), nullable=True),
sa.Column('session_id', sa.String(length=32), nullable=False),
sa.Column('product_id', sa.Integer(), nullable=True),
sa.Column('recurring_id', sa.Integer(), nullable=True),
sa.Column('option', sa.Text(), nullable=True),
sa.Column('quantity', sa.Integer(), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_table('fp_customer_group',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('approval', sa.Integer(), nullable=True),
sa.Column('sort_order', sa.Integer(), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'fp_tax_class', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('title', sa.String(length=32), nullable=False),
sa.Column('description', sa.String(length=255), nullable=False),
sa.Column('created_at', sa.Integer(), nullable=True),
sa.Column('updated_at', sa.Integer(), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'fp_tax_rate', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('geo_zone_id', sa.Integer(), nullable=True),
sa.Column('name', sa.String(length=32), nullable=False),
sa.Column('rate', sa.Float(precision=15, asdecimal=4), nullable=True),
sa.Column('type', sa.String(length=1), nullable=False),
sa.Column('created_at', sa.Integer(), nullable=True),
sa.Column('updated_at', sa.Integer(), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'fp_weight_class', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('value', sa.Float(precision=15, asdecimal=8), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'fp_attribute', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('attribute_group_id', sa.Integer(), nullable=True),
sa.Column('sort_order', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['attribute_group_id'],
['fp_attribute_group.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'fp_attribute_group_description',
sa.Column('attribute_group_id', sa.Integer(), nullable=False),
sa.Column('language_id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=64), nullable=False),
sa.ForeignKeyConstraint(
['attribute_group_id'],
['fp_attribute_group.id'],
), sa.ForeignKeyConstraint(
['language_id'],
['fp_language.id'],
), sa.PrimaryKeyConstraint('attribute_group_id', 'language_id'))
op.create_table(
'fp_customer', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('customer_group_id', sa.Integer(), nullable=True),
sa.Column('language_id', sa.Integer(), nullable=True),
sa.Column('first_name', sa.String(length=32), nullable=True),
sa.Column('last_name', sa.String(length=32), nullable=True),
sa.Column('email', sa.String(length=96), nullable=False),
sa.Column('password', sa.String(length=40), nullable=False),
sa.Column('telephone', sa.String(length=32), nullable=True),
sa.Column('fax', sa.String(length=32), nullable=True),
sa.Column('salt', sa.String(length=9), nullable=True),
sa.Column('cart', sa.Text(), nullable=True),
sa.Column('wishlist', sa.Text(), nullable=True),
sa.Column('newsletter', sa.SmallInteger(), nullable=True),
sa.Column('address_id', sa.Integer(), nullable=True),
sa.Column('ip', sa.String(length=40), nullable=True),
sa.Column('status', sa.SmallInteger(), nullable=True),
sa.Column('approved', sa.SmallInteger(), nullable=True),
sa.Column('safe', sa.SmallInteger(), nullable=True),
sa.Column('token', sa.Text(), nullable=True),
sa.Column('code', sa.String(length=40), nullable=True),
sa.Column('created_at', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['customer_group_id'],
['fp_customer_group.id'],
), sa.ForeignKeyConstraint(
['language_id'],
['fp_language.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_fp_customer_email'),
'fp_customer', ['email'],
unique=True)
op.create_table(
'fp_customer_group_description',
sa.Column('customer_group_id', sa.Integer(), nullable=False),
sa.Column('language_id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=32), nullable=True),
sa.Column('description', sa.Text(), nullable=True),
sa.ForeignKeyConstraint(
['customer_group_id'],
['fp_customer_group.id'],
), sa.ForeignKeyConstraint(
['language_id'],
['fp_language.id'],
), sa.PrimaryKeyConstraint('customer_group_id', 'language_id'))
op.create_table(
'fp_stock_status', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('language_id', sa.Integer(), nullable=True),
sa.Column('name', sa.String(length=32), nullable=True),
sa.ForeignKeyConstraint(
['language_id'],
['fp_language.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'fp_weight_class_description',
sa.Column('weight_class_id', sa.Integer(), nullable=False),
sa.Column('language_id', sa.Integer(), nullable=False),
sa.Column('title', sa.String(length=32), nullable=False),
sa.Column('unit', sa.String(length=4), nullable=False),
sa.ForeignKeyConstraint(
['language_id'],
['fp_language.id'],
),
sa.ForeignKeyConstraint(
['weight_class_id'],
['fp_weight_class.id'],
), sa.PrimaryKeyConstraint('weight_class_id', 'language_id'))
op.create_table(
'fp_zone', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('country_id', sa.Integer(), nullable=True),
sa.Column('name', sa.String(length=128), nullable=False),
sa.Column('code', sa.String(length=32), nullable=False),
sa.Column('status', sa.SmallInteger(), nullable=True),
sa.ForeignKeyConstraint(
['country_id'],
['fp_country.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'fp_attribute_description',
sa.Column('attribute_id', sa.Integer(), nullable=False),
sa.Column('language_id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=64), nullable=False),
sa.ForeignKeyConstraint(
['attribute_id'],
['fp_attribute.id'],
), sa.ForeignKeyConstraint(
['language_id'],
['fp_language.id'],
), sa.PrimaryKeyConstraint('attribute_id', 'language_id'))
op.create_table(
'fp_banner_image', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('banner_id', sa.Integer(), nullable=True),
sa.Column('language_id', sa.Integer(), nullable=True),
sa.Column('title', sa.String(length=64), nullable=False),
sa.Column('link', sa.String(length=255), nullable=True),
sa.Column('image', sa.Integer(), nullable=True),
sa.Column('sort_order', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['banner_id'],
['fp_banner.id'],
), sa.ForeignKeyConstraint(
['image'],
['fp_asset.id'],
), sa.ForeignKeyConstraint(
['language_id'],
['fp_language.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'fp_customer_search', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('language_id', sa.Integer(), nullable=True),
sa.Column('customer_id', sa.Integer(), nullable=True),
sa.Column('keyword', sa.String(length=255), nullable=True),
sa.Column('category_id', sa.Integer(), nullable=True),
sa.Column('ip', sa.String(length=40), nullable=True),
sa.Column('created_at', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['customer_id'],
['fp_customer.id'],
), sa.ForeignKeyConstraint(
['language_id'],
['fp_language.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'fp_product_description',
sa.Column('product_id', sa.Integer(), nullable=False),
sa.Column('language_id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=100), nullable=False),
sa.Column('description', sa.Text(), nullable=True),
sa.Column('tag', sa.Text(), nullable=True),
sa.Column('meta_title', sa.String(length=255), nullable=False),
sa.Column('meta_description', sa.String(length=255), nullable=False),
sa.Column('meta_keyword', sa.String(length=255), nullable=False),
sa.ForeignKeyConstraint(
['language_id'],
['fp_language.id'],
), sa.ForeignKeyConstraint(
['product_id'],
['fp_product.id'],
), sa.PrimaryKeyConstraint('product_id', 'language_id'))
op.create_table(
'fp_product_discount', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('product_id', sa.Integer(), nullable=True),
sa.Column('customer_group_id', sa.Integer(), nullable=True),
sa.Column('quantity', sa.Integer(), nullable=True),
sa.Column('priority', sa.Integer(), nullable=True),
sa.Column('price', sa.Float(precision=15, asdecimal=4), nullable=True),
sa.Column('date_start', sa.Date(), nullable=False),
sa.Column('date_end', sa.Date(), nullable=False),
sa.ForeignKeyConstraint(
['customer_group_id'],
['fp_customer_group.id'],
), sa.ForeignKeyConstraint(
['product_id'],
['fp_product.id'],
), sa.PrimaryKeyConstraint('id'))
op.add_column('fp_product',
sa.Column('cover_id', sa.Integer(), nullable=True))
op.add_column('fp_product',
sa.Column('date_available', sa.Date(), nullable=True))
op.add_column('fp_product',
sa.Column('ean', sa.String(length=14), nullable=True))
op.add_column(
'fp_product',
sa.Column('height', sa.Float(precision=15, asdecimal=8),
nullable=True))
op.add_column('fp_product',
sa.Column('isbn', sa.String(length=17), nullable=True))
op.add_column('fp_product',
sa.Column('jan', sa.String(length=13), nullable=True))
op.add_column(
'fp_product',
sa.Column('length', sa.Float(precision=15, asdecimal=8),
nullable=True))
op.add_column('fp_product',
sa.Column('length_class_id', sa.Integer(), nullable=True))
op.add_column('fp_product',
sa.Column('location', sa.String(length=128), nullable=True))
op.add_column('fp_product',
sa.Column('mini_cnt', sa.Integer(), nullable=True))
op.add_column('fp_product',
sa.Column('model', sa.String(length=64), nullable=True))
op.add_column('fp_product',
sa.Column('mpn', sa.String(length=64), nullable=True))
op.add_column('fp_product',
sa.Column('points', sa.SmallInteger(), nullable=True))
op.add_column('fp_product',
sa.Column('quantity', sa.Integer(), nullable=True))
op.add_column('fp_product',
sa.Column('shipping', sa.SmallInteger(), nullable=True))
op.add_column('fp_product',
sa.Column('sku', sa.String(length=11), nullable=False))
op.add_column('fp_product',
sa.Column('sort_order', sa.SmallInteger(), nullable=True))
op.add_column('fp_product',
sa.Column('stock_status_id', sa.Integer(), nullable=True))
op.add_column('fp_product',
sa.Column('subtract', sa.SmallInteger(), nullable=True))
op.add_column('fp_product',
sa.Column('tax_class_id', sa.Integer(), nullable=True))
op.add_column('fp_product',
sa.Column('upc', sa.String(length=12), nullable=True))
op.add_column(
'fp_product',
sa.Column('weight', sa.Float(precision=15, asdecimal=8),
nullable=True))
op.add_column('fp_product',
sa.Column('weight_class_id', sa.Integer(), nullable=True))
op.add_column(
'fp_product',
sa.Column('width', sa.Float(precision=15, asdecimal=8), nullable=True))
op.create_index(op.f('ix_fp_product_sku'),
'fp_product', ['sku'],
unique=True)
op.drop_index('ix_fp_product_rid', table_name='fp_product')
op.create_foreign_key(None, 'fp_product', 'fp_asset', ['cover_id'], ['id'])
op.drop_column('fp_product', 'description')
op.drop_column('fp_product', 'rid')
op.drop_column('fp_product', 'name')
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('musical_piece', sa.Column('composer_id', sa.Integer(), nullable=True))
op.drop_constraint('musical_piece_ibfk_1', 'musical_piece', type_='foreignkey')
op.create_foreign_key(None, 'musical_piece', 'person', ['composer_id'], ['id'])
op.drop_column('musical_piece', 'author_id')
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.add_column('conditional',
sa.Column('evaluation', sa.Integer(), nullable=True))
op.create_foreign_key(None, 'conditional', 'spring_evals', ['evaluation'],
['id'])
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_constraint(None, 'Show', type_='foreignkey')
op.create_foreign_key('Show_artist_id_fkey', 'Show', 'Artist',
['artist_id'], ['id'])
