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_schemas.sql')) ts.run_sql_script(os.path.join(TIMESCALE_SQL_DIR, 'create_types.sql')) 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_without_transaction( os.path.join(TIMESCALE_SQL_DIR, 'create_views.sql')) ts.run_sql_script(os.path.join(TIMESCALE_SQL_DIR, 'create_indexes.sql')) ts.create_view_indexes() 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')) ts.engine.dispose()
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 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!")