def db(app): # pylint: disable=redefined-outer-name, invalid-name
"""Return a session-wide initialised database.
Drops all existing tables - Meta follows Postgres FKs
"""
with app.app_context():
# clear all custom views
views_sql = """select table_name from INFORMATION_SCHEMA.views
WHERE table_schema = ANY (current_schemas(false))
"""
sess = _db.session()
for view in [name for (name, ) in sess.execute(text(views_sql))]:
try:
sess.execute(text('DROP VIEW public.%s ;' % view))
print('DROP VIEW public.%s ' % view)
except Exception as err: # pylint: disable=broad-except
print(f'Error: {err}')
sess.commit()
# Clear out any existing tables
metadata = MetaData(_db.engine)
metadata.reflect()
for table in metadata.tables.values():
for fk in table.foreign_keys: # pylint: disable=invalid-name
_db.engine.execute(DropConstraint(fk.constraint))
metadata.drop_all()
_db.drop_all()
sequence_sql = """SELECT sequence_name FROM information_schema.sequences
WHERE sequence_schema='public'
"""
sess = _db.session()
for seq in [name for (name, ) in sess.execute(text(sequence_sql))]:
try:
sess.execute(text('DROP SEQUENCE public.%s ;' % seq))
print('DROP SEQUENCE public.%s ' % seq)
except Exception as err: # pylint: disable=broad-except
print(f'Error: {err}')
sess.commit()
# ############################################
# There are 2 approaches, an empty database, or the same one that the app will use
# create the tables
# _db.create_all()
# or
# Use Alembic to load all of the DB revisions including supporting lookup data
# This is the path we'll use in auth_api!!
# even though this isn't referenced directly, it sets up the internal configs that upgrade needs
Migrate(app, _db)
upgrade()
return _db
def db(app): # pylint: disable=redefined-outer-name, invalid-name
"""Return a session-wide initialised database.
Drops schema, and recreate.
"""
with app.app_context():
drop_schema_sql = """DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
"""
sess = _db.session()
sess.execute(drop_schema_sql)
sess.commit()
# ############################################
# There are 2 approaches, an empty database, or the same one that the app will use
# create the tables
# _db.create_all()
# or
# Use Alembic to load all of the DB revisions including supporting lookup data
# This is the path we'll use in auth_api!!
# even though this isn't referenced directly, it sets up the internal configs that upgrade needs
Migrate(app, _db)
upgrade()
return _db