def downgrade():
context.execute("REVOKE SELECT, UPDATE, INSERT, DELETE \
ON borrower, deed, mortgage_document, naa_audit, verify_match \
FROM " + str(os.getenv('APP_SQL_USERNAME')))
context.execute("REVOKE USAGE, SELECT ON SEQUENCE borrower_id_seq, deed_id_seq, naa_audit_id_seq \
FROM " + str(os.getenv('APP_SQL_USERNAME')))
def run_migrations_online():
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
configuration = config.get_section(config.config_ini_section)
configuration["sqlalchemy.url"] = get_url()
connectable = engine_from_config(
configuration, prefix="sqlalchemy.", poolclass=pool.NullPool
)
with connectable.connect() as connection:
context.configure(
connection=connection,
target_metadata=target_metadata,
compare_type=True,
include_schemas=True, # schemas,
version_table_schema=POSTGRES_SCHEMA,
include_object=include_schemas([None, POSTGRES_SCHEMA])
)
with context.begin_transaction():
context.execute(f"CREATE SCHEMA IF NOT EXISTS {POSTGRES_SCHEMA};")
context.execute(f"SET search_path TO {POSTGRES_SCHEMA}")
context.run_migrations()
def run_migrations_online():
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
url = config.get_main_option("sqlalchemy.url")
default_schema = config.get_main_option("schema")
version_table = config.get_main_option("version_table")
connectable = engine_from_config(
config.get_section(config.config_ini_section),
prefix="sqlalchemy.",
poolclass=pool.NullPool,
)
with connectable.connect() as connection:
context.configure(
url=url,
connection=connection,
target_metadata=target_metadata,
version_table=version_table,
version_table_schema=default_schema,
include_schemas=True,
)
with context.begin_transaction():
context.execute("SET search_path TO public")
context.run_migrations()
def run_migrations_online():
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
connectable = engine_from_config(
{
"sqlalchemy.url":
ALEMBIC_CONFIG.url.__to_string__(hide_password=False)
},
prefix="sqlalchemy.",
poolclass=pool.NullPool,
)
with connectable.connect() as connection:
context.configure(
connection=connection,
target_metadata=target_metadata,
include_object=include_object,
transaction_per_migration=True,
process_revision_directives=writer,
)
with context.begin_transaction():
context.execute("SET search_path TO public")
context.run_migrations()
def run_migrations_online():
"""
Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
# Prevents an auto-migration from being generated when there are no changes to the schema:
# - See: http://alembic.zzzcomputing.com/en/latest/cookbook.html
def process_revision_directives(context, revision, directives):
if getattr(config.cmd_opts, 'autogenerate', False):
script = directives[0]
if script.upgrade_ops.is_empty():
directives[:] = []
logger.info('No changes in schema detected.')
engine = engine_from_config(config.get_section(config.config_ini_section),
prefix='sqlalchemy.',
poolclass=pool.NullPool)
connection = engine.connect()
context.configure(connection=connection,
target_metadata=target_metadata,
process_revision_directives=process_revision_directives,
**app.extensions['migrate'].configure_args)
try:
with context.begin_transaction():
context.execute('SET search_path TO public, shared, extensions;')
context.run_migrations()
finally:
connection.close()
def run_migrations_online():
connectable = engine_from_config(config.get_section(
config.config_ini_section),
prefix='sqlalchemy.',
poolclass=pool.NullPool)
with connectable.connect() as connection:
"""
Configure migration context
1. Pass our models metadata
2. Set schema for alembic_version table
3. Load all available schemas
"""
context.configure(connection=connection,
target_metadata=target_metadata,
version_table_schema=target_metadata.schema,
include_schemas=True)
with context.begin_transaction():
"""
By default search_path is setted to "$user",public
that why alembic can't create foreign keys correctly
"""
context.execute('SET search_path TO public')
context.run_migrations()
def downgrade():
context.execute(
"REVOKE SELECT, UPDATE, INSERT, DELETE ON TABLE \"price_history\" \
TO " + current_app.config.get('APP_SQL_USERNAME'))
# ### commands auto generated by Alembic - please adjust! ###
op.drop_table('price_history')
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('restriction',
sa.Column('restriction_id', sa.String(), nullable=False),
sa.Column('restriction_type', sa.String(), nullable=True),
sa.Column('restriction_text', sa.String(), nullable=True),
sa.PrimaryKeyConstraint('restriction_id')
)
# ### end Alembic commands ###
context.execute("GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE \"restriction\" TO " +
current_app.config.get('APP_SQL_USERNAME'))
def run_migrations_online():
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
# for the direct-to-DB use case, start a transaction on all
# engines, then run all migrations, then commit all transactions.
engines = {}
for name in re.split(r',\s*', db_names):
engines[name] = rec = {}
rec['engine'] = engine_from_config(
context.config.get_section(name),
prefix='sqlalchemy.',
poolclass=pool.NullPool)
for name, rec in engines.items():
engine = rec['engine']
rec['connection'] = conn = engine.connect()
if USE_TWOPHASE:
rec['transaction'] = conn.begin_twophase()
else:
rec['transaction'] = conn.begin()
try:
for name, rec in engines.items():
context.configure(
connection=rec['connection'],
upgrade_token="%s_upgrades",
downgrade_token="%s_downgrades",
target_metadata=target_metadata.get(name)
)
context.execute("--running migrations for engine %s" % name)
context.run_migrations(engine=name)
if USE_TWOPHASE:
for rec in engines.values():
rec['transaction'].prepare()
for rec in engines.values():
rec['transaction'].commit()
except:
for rec in engines.values():
rec['transaction'].rollback()
raise
finally:
for rec in engines.values():
rec['connection'].close()
def run_migrations_online():
engine_ = create_engine(url, poolclass=pool.NullPool)
with engine_.connect() as connection:
context.configure(
connection=connection,
target_metadata=target_metadata,
)
with context.begin_transaction() as trans:
context.execute('SET search_path TO public')
context.run_migrations()
trans.commit()
def run_migrations_online():
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
# for the direct-to-DB use case, start a transaction on all
# engines, then run all migrations, then commit all transactions.
engines = {}
for name in re.split(r',\s*', db_names):
engines[name] = rec = {}
rec['engine'] = engine_from_config(context.config.get_section(name),
prefix='sqlalchemy.',
poolclass=pool.NullPool)
for name, rec in engines.items():
engine = rec['engine']
rec['connection'] = conn = engine.connect()
if USE_TWOPHASE:
rec['transaction'] = conn.begin_twophase()
else:
rec['transaction'] = conn.begin()
try:
for name, rec in engines.items():
context.configure(connection=rec['connection'],
upgrade_token="%s_upgrades",
downgrade_token="%s_downgrades",
target_metadata=target_metadata.get(name))
context.execute("--running migrations for engine %s" % name)
context.run_migrations(engine=name)
if USE_TWOPHASE:
for rec in engines.values():
rec['transaction'].prepare()
for rec in engines.values():
rec['transaction'].commit()
except:
for rec in engines.values():
rec['transaction'].rollback()
raise
finally:
for rec in engines.values():
rec['connection'].close()
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('thing',
sa.Column('thing_id', postgresql.UUID(), nullable=False),
sa.Column('foo', sa.String(), nullable=False),
sa.Column('bar', sa.String(), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('archived_at', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('thing_id'))
# ### end Alembic commands ###
# ### Grant permissions for the user found in config variable APP_SQL_USERNAME ###
context.execute("GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE thing TO " +
current_app.config.get('APP_SQL_USERNAME'))
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'organisation', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('organisation_id', sa.String(), nullable=False),
sa.Column('organisation_name', sa.String(length=50), nullable=False),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('organisation_id'))
# ### end Alembic commands ###
# MANUALLY ADDED the line below to grant permissions for the user found in config variable
# APP_SQL_USERNAME on table organisation.
context.execute(
"GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE organisation TO " +
current_app.config.get('APP_SQL_USERNAME'))
context.execute("GRANT USAGE, SELECT ON SEQUENCE organisation_id_seq TO " +
current_app.config.get('APP_SQL_USERNAME'))
def run_migrations_online():
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
connectable = create_engine(get_url())
with connectable.connect() as connection:
context.configure(
connection=connection,
process_revision_directives=process_revision_directives,
target_metadata=target_metadata)
with context.begin_transaction():
context.execute('SET search_path TO public')
context.run_migrations()
def upgrade():
stmt = "select table_name from information_schema.tables where table_name='invitation';"
result = context.execute(stmt)
if result and result.scalar():
return
table = sa.inspect(Invitation).tables[0]
op.create_table(table.name, *[c.copy() for c in table.columns])
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'address',
sa.Column('address_id',
sa.Integer(),
autoincrement=True,
nullable=False),
sa.Column('house_name_number', sa.String(), nullable=False),
sa.Column('street', sa.String(), nullable=False),
sa.Column('town_city', sa.String(), nullable=False),
sa.Column('county', sa.String(), nullable=True),
sa.Column('country', sa.String(), nullable=False),
sa.Column('postcode', sa.String(), nullable=False),
sa.PrimaryKeyConstraint('address_id'))
op.create_table('restriction',
sa.Column('restriction_id', sa.String(), nullable=False),
sa.Column('restriction_type', sa.String(), nullable=True),
sa.Column('restriction_text', sa.String(), nullable=True),
sa.PrimaryKeyConstraint('restriction_id'))
op.create_table(
'user',
sa.Column('identity',
sa.Integer(),
autoincrement=False,
nullable=False),
sa.Column('first_name', sa.String(), nullable=False),
sa.Column('last_name', sa.String(), nullable=False),
sa.Column('email_address', sa.String(), nullable=False),
sa.Column('phone_number', sa.String(), nullable=False),
sa.Column('address_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['address_id'],
['address.address_id'],
), sa.PrimaryKeyConstraint('identity'))
op.create_index(op.f('ix_user_email_address'),
'user', ['email_address'],
unique=True)
# ### end Alembic commands ###
context.execute(
"GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE \"user\", \"restriction\", \"address\" TO "
+ current_app.config.get('APP_SQL_USERNAME'))
context.execute("GRANT USAGE ON SEQUENCE \"address_address_id_seq\" TO " +
current_app.config.get('APP_SQL_USERNAME'))
def run_migrations_offline():
"""Run migrations in 'offline' mode.
This configures the context with just a URL
and not an Engine, though an Engine is acceptable
here as well. By skipping the Engine creation
we don't even need a DBAPI to be available.
Calls to context.execute() here emit the given string to the
script output.
"""
context.configure(url=app_config["DB_URL"])
with context.begin_transaction():
if config.get_main_option("bdr").strip().lower() == "true":
context.execute("SET LOCAL bdr.permit_ddl_locking = true")
context.run_migrations()
def run_migrations_offline():
"""Run migrations in 'offline' mode.
This configures the context with just a URL
and not an Engine, though an Engine is acceptable
here as well. By skipping the Engine creation
we don't even need a DBAPI to be available.
Calls to context.execute() here emit the given string to the
script output.
"""
url = config.get_main_option("sqlalchemy.url")
context.configure(url=url)
with context.begin_transaction():
if config.get_main_option('bdr').strip().lower() == 'true':
context.execute('SET LOCAL bdr.permit_ddl_locking = true')
context.run_migrations()
def run_migrations_online():
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
# specify here how the engine is acquired
engine = _get_dbas_engine()
with engine.connect() as connection:
context.configure(connection=connection,
target_metadata=target_metadata,
version_table_schema=target_metadata.schema,
include_schemas=True)
with context.begin_transaction():
context.execute('SET search_path TO public')
context.run_migrations()
def run_migrations_online():
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
connectable = engine_from_config(
config.get_section(config.config_ini_section),
prefix="sqlalchemy.",
poolclass=pool.NullPool,
)
with connectable.connect() as connection:
context.configure(connection=connection,
target_metadata=target_metadata)
with context.begin_transaction():
context.execute("DROP TABLE IF EXISTS alembic_version;")
context.run_migrations()
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'price_history', sa.Column('title_number', sa.String(),
nullable=False),
sa.Column('date',
sa.DateTime(),
server_default=sa.text('now()'),
nullable=False),
sa.Column('price_amount', sa.Integer(), nullable=False),
sa.Column('price_currency', sa.String(), nullable=False),
sa.ForeignKeyConstraint(
['title_number'],
['title.title_number'],
), sa.PrimaryKeyConstraint('title_number', 'date'))
# ### end Alembic commands ###
context.execute(
"GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE \"price_history\" \
TO " + current_app.config.get('APP_SQL_USERNAME'))
def run_migrations_offline():
"""Run migrations in 'offline' mode.
This configures the context with just a URL
and not an Engine, though an Engine is acceptable
here as well. By skipping the Engine creation
we don't even need a DBAPI to be available.
Calls to context.execute() here emit the given string to the
script output.
"""
url = config.get_main_option("sqlalchemy.url")
context.configure(
url=url,
target_metadata=target_metadata,
literal_binds=True,
dialect_opts={"paramstyle": "named"},
)
with context.begin_transaction():
context.execute("DROP TABLE IF EXISTS alembic_version;")
context.run_migrations()
def run_migrations_offline():
"""Run migrations in 'offline' mode.
This configures the context with just a URL
and not an Engine, though an Engine is acceptable
here as well. By skipping the Engine creation
we don't even need a DBAPI to be available.
Calls to context.execute() here emit the given string to the
script output.
"""
url = config.get_main_option("sqlalchemy.url")
context.configure(url=url)
with context.begin_transaction():
# If the configuration indicates this script is for a Postgres-BDR database,
# then we need to acquire the global DDL lock before migrating.
postgres_bdr = config.get_main_option('offline_postgres_bdr')
if postgres_bdr is not None and postgres_bdr.strip().lower() == 'true':
_log.info('Emitting SQL to allow for global DDL locking with BDR')
context.execute('SET LOCAL bdr.permit_ddl_locking = true')
context.run_migrations()
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'address',
sa.Column('address_id',
sa.Integer(),
autoincrement=True,
nullable=False),
sa.Column('house_name_number', sa.String(), nullable=False),
sa.Column('street', sa.String(), nullable=False),
sa.Column('town_city', sa.String(), nullable=False),
sa.Column('county', sa.String(), nullable=True),
sa.Column('country', sa.String(), nullable=False),
sa.Column('postcode', sa.String(), nullable=False),
sa.PrimaryKeyConstraint('address_id'))
op.create_table(
'user',
sa.Column('identity',
sa.Integer(),
autoincrement=False,
nullable=False),
sa.Column('first_name', sa.String(), nullable=False),
sa.Column('last_name', sa.String(), nullable=False),
sa.Column('email_address', sa.String(), nullable=False),
sa.Column('phone_number', sa.String(), nullable=False),
sa.Column('address_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['address_id'],
['address.address_id'],
), sa.PrimaryKeyConstraint('identity'))
op.create_index(op.f('ix_user_email_address'),
'user', ['email_address'],
unique=True)
op.create_table(
'case', sa.Column('case_reference', sa.String(), nullable=False),
sa.Column('case_type', sa.String(), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('status', sa.String(), nullable=False),
sa.Column('title_number', sa.String(), nullable=True),
sa.Column('assigned_staff_id', sa.Integer(), nullable=False),
sa.Column('client_id', sa.Integer(), nullable=False),
sa.Column('counterparty_id', sa.Integer(), nullable=False),
sa.Column('counterparty_conveyancer_org', sa.String(), nullable=False),
sa.Column('counterparty_conveyancer_contact_id',
sa.Integer(),
nullable=False),
sa.Column('address_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['address_id'], ['address.address_id'],
onupdate='CASCADE',
ondelete='CASCADE'),
sa.ForeignKeyConstraint(
['assigned_staff_id'],
['user.identity'],
), sa.ForeignKeyConstraint(
['client_id'],
['user.identity'],
),
sa.ForeignKeyConstraint(
['counterparty_conveyancer_contact_id'],
['user.identity'],
), sa.ForeignKeyConstraint(
['counterparty_id'],
['user.identity'],
), sa.PrimaryKeyConstraint('case_reference'))
op.create_index(op.f('ix_case_created_at'),
'case', ['created_at'],
unique=False)
# ### end Alembic commands ###
context.execute(
"GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE \"user\", \"case\", \"address\" TO "
+ current_app.config.get('APP_SQL_USERNAME'))
context.execute("GRANT USAGE ON SEQUENCE \"address_address_id_seq\" TO " +
current_app.config.get('APP_SQL_USERNAME'))
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'address',
sa.Column('address_id',
sa.Integer(),
autoincrement=True,
nullable=False),
sa.Column('house_name_or_number', sa.String(), nullable=False),
sa.Column('street_name', sa.String(), nullable=False),
sa.Column('city', sa.String(), nullable=False),
sa.Column('county', sa.String(), nullable=False),
sa.Column('country', sa.String(), nullable=False),
sa.Column('postcode', sa.String(), nullable=False),
sa.PrimaryKeyConstraint('address_id'))
op.create_table(
'conveyancer',
sa.Column('conveyancer_id',
sa.Integer(),
autoincrement=True,
nullable=False),
sa.Column('x500_name', sa.String(), nullable=False),
sa.Column('company_name', sa.String(), nullable=False),
sa.PrimaryKeyConstraint('conveyancer_id'),
sa.UniqueConstraint('x500_name'))
op.create_table(
'owner',
sa.Column('identity',
sa.Integer(),
autoincrement=False,
nullable=False),
sa.Column('forename', sa.String(), nullable=False),
sa.Column('surname', sa.String(), nullable=False),
sa.Column('email', sa.String(), nullable=False),
sa.Column('phone', sa.String(), nullable=False),
sa.Column('owner_type', sa.String(), nullable=False),
sa.Column('address_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['address_id'],
['address.address_id'],
), sa.PrimaryKeyConstraint('identity'))
op.create_index(op.f('ix_owner_email'), 'owner', ['email'], unique=True)
op.create_table(
'title', sa.Column('title_number', sa.String(), nullable=False),
sa.Column('created_at',
sa.DateTime(),
server_default=sa.text('now()'),
nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('lock', sa.DateTime(), nullable=True),
sa.Column('owner_identity', sa.Integer(), nullable=False),
sa.Column('address_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['address_id'], ['address.address_id'],
onupdate='CASCADE',
ondelete='CASCADE'),
sa.ForeignKeyConstraint(
['owner_identity'],
['owner.identity'],
), sa.PrimaryKeyConstraint('title_number'))
op.create_table(
'charge',
sa.Column('charge_id',
sa.Integer(),
autoincrement=True,
nullable=False),
sa.Column('charge_date',
sa.DateTime(),
server_default=sa.text('now()'),
nullable=False),
sa.Column('charge_lender', sa.String(), nullable=False),
sa.Column('charge_amount', sa.Float(), nullable=False),
sa.Column('charge_currency_type', sa.String(), nullable=False),
sa.Column('title_number', sa.String(), nullable=True),
sa.ForeignKeyConstraint(
['title_number'],
['title.title_number'],
), sa.PrimaryKeyConstraint('charge_id'))
op.create_table(
'restriction',
sa.Column('restriction_id',
sa.Integer(),
autoincrement=True,
nullable=False),
sa.Column('restriction_code', sa.String(), nullable=False),
sa.Column('restriction_type', sa.String(), nullable=False),
sa.Column('restriction_text', sa.String(), nullable=False),
sa.Column('consenting_party', sa.String(), nullable=False),
sa.Column('restriction_date',
sa.DateTime(),
server_default=sa.text('now()'),
nullable=False),
sa.Column('title_number', sa.String(), nullable=True),
sa.Column('charge_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['charge_id'], ['charge.charge_id'],
onupdate='CASCADE',
ondelete='CASCADE'),
sa.ForeignKeyConstraint(
['title_number'],
['title.title_number'],
), sa.PrimaryKeyConstraint('restriction_id'))
# ### end Alembic commands ###
context.execute(
"GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE \"address\", \"conveyancer\", \"owner\", \
\"title\", \"charge\", \"restriction\" TO " +
current_app.config.get('APP_SQL_USERNAME'))
context.execute("GRANT USAGE ON SEQUENCE \"address_address_id_seq\", \
\"charge_charge_id_seq\", \
\"conveyancer_conveyancer_id_seq\", \
\"restriction_restriction_id_seq\" TO " +
current_app.config.get('APP_SQL_USERNAME'))
