def init_db(force, create_db):
"""Initializes database.
This process involves several steps:
1. Table structure is created.
2. Indexes are created.
3. Views are created
"""
from listenbrainz import config
ts.init_db_connection(config.TIMESCALE_ADMIN_URI)
if force:
res = ts.run_sql_script_without_transaction(
os.path.join(TIMESCALE_SQL_DIR, 'drop_db.sql'))
if not res:
raise Exception(
'Failed to drop existing database and user! Exit code: %i' %
res)
if create_db or force:
print('TS: Creating user and a database...')
res = ts.run_sql_script_without_transaction(
os.path.join(TIMESCALE_SQL_DIR, 'create_db.sql'))
if not res:
raise Exception(
'Failed to create new database and user! Exit code: %i' % res)
ts.init_db_connection(config.TIMESCALE_ADMIN_LB_URI)
print('TS: Creating database extensions...')
res = ts.run_sql_script_without_transaction(
os.path.join(TIMESCALE_SQL_DIR, 'create_extensions.sql'))
# Don't raise an exception if the extension already exists
ts.init_db_connection(config.SQLALCHEMY_TIMESCALE_URI)
application = webserver.create_app()
with application.app_context():
print('TS: Creating tables...')
ts.run_sql_script(os.path.join(TIMESCALE_SQL_DIR, 'create_tables.sql'))
print('TS: Creating Functions...')
ts.run_sql_script(
os.path.join(TIMESCALE_SQL_DIR, 'create_functions.sql'))
print('TS: Creating views...')
ts.run_sql_script(os.path.join(TIMESCALE_SQL_DIR, 'create_views.sql'))
print('TS: Creating indexes...')
ts.run_sql_script(os.path.join(TIMESCALE_SQL_DIR,
'create_indexes.sql'))
print("Done!")
def reset_timescale_db(self):
ts.init_db_connection(config.TIMESCALE_ADMIN_URI)
ts.run_sql_script_without_transaction(os.path.join(TIMESCALE_SQL_DIR, 'drop_db.sql'))
ts.run_sql_script_without_transaction(os.path.join(TIMESCALE_SQL_DIR, 'create_db.sql'))
ts.engine.dispose()
ts.init_db_connection(config.TIMESCALE_ADMIN_LB_URI)
ts.run_sql_script_without_transaction(os.path.join(TIMESCALE_SQL_DIR, 'create_extensions.sql'))
ts.engine.dispose()
ts.init_db_connection(config.SQLALCHEMY_TIMESCALE_URI)
ts.run_sql_script(os.path.join(TIMESCALE_SQL_DIR, 'create_tables.sql'))
ts.run_sql_script(os.path.join(TIMESCALE_SQL_DIR, 'create_functions.sql'))
ts.run_sql_script(os.path.join(TIMESCALE_SQL_DIR, 'create_views.sql'))
ts.run_sql_script(os.path.join(TIMESCALE_SQL_DIR, 'create_indexes.sql'))
ts.engine.dispose()
def init_ts_db(force, create_db):
"""Initializes database.
This process involves several steps:
1. Table structure is created.
2. Indexes are created.
3. Views are created
"""
from listenbrainz import config
ts.init_db_connection(config.TIMESCALE_ADMIN_URI)
if force:
res = ts.run_sql_script_without_transaction(
os.path.join(TIMESCALE_SQL_DIR, 'drop_db.sql'))
if not res:
raise Exception(
'Failed to drop existing database and user! Exit code: %i' %
res)
if create_db or force:
print('TS: Creating user and a database...')
retries = 0
while True:
try:
res = ts.run_sql_script_without_transaction(
os.path.join(TIMESCALE_SQL_DIR, 'create_db.sql'))
break
except sqlalchemy.exc.OperationalError:
print(
"Trapped template1 access error, FFS! Sleeping, trying again."
)
retries += 1
if retries == 5:
raise
sleep(1)
continue
if not res:
raise Exception(
'Failed to create new database and user! Exit code: %i' % res)
ts.init_db_connection(config.TIMESCALE_ADMIN_LB_URI)
print('TS: Creating database extensions...')
res = ts.run_sql_script_without_transaction(
os.path.join(TIMESCALE_SQL_DIR, 'create_extensions.sql'))
# Don't raise an exception if the extension already exists
ts.init_db_connection(config.SQLALCHEMY_TIMESCALE_URI)
application = webserver.create_app()
with application.app_context():
print('TS: Creating Schemas...')
ts.run_sql_script(os.path.join(TIMESCALE_SQL_DIR,
'create_schemas.sql'))
print('TS: Creating Types...')
ts.run_sql_script(os.path.join(TIMESCALE_SQL_DIR, 'create_types.sql'))
print('TS: Creating tables...')
ts.run_sql_script(os.path.join(TIMESCALE_SQL_DIR, 'create_tables.sql'))
print('TS: Creating Functions...')
ts.run_sql_script(
os.path.join(TIMESCALE_SQL_DIR, 'create_functions.sql'))
print('TS: Creating views...')
ts.run_sql_script_without_transaction(
os.path.join(TIMESCALE_SQL_DIR, 'create_views.sql'))
print('TS: Creating indexes...')
ts.run_sql_script(os.path.join(TIMESCALE_SQL_DIR,
'create_indexes.sql'))
ts.create_view_indexes()
print('TS: Creating Primary and Foreign Keys...')
ts.run_sql_script(
os.path.join(TIMESCALE_SQL_DIR, 'create_primary_keys.sql'))
ts.run_sql_script(
os.path.join(TIMESCALE_SQL_DIR, 'create_foreign_keys.sql'))
print("Done!")
