def create_database(con: connection, drop_database: bool) -> bool: """ Creates the database, dropping it first if requested. Args: con: A connection to the postgres database. drop_database: When true, will execute a DROP DATABASE command. Returns: True if the database already existed when the create was run. Raises: DatabaseError: An error occurred. """ con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) cur = get_cursor(con) if drop_database: _LOG.warning('Dropping database.') sql_file = f"database{SEP}database_drop.sql" execute_sql_from_file(cur, sql_file, "drop database") try: sql_file = f"database{SEP}database_create.sql" execute_sql_from_file(cur, sql_file, "database create") sql_file = f"database{SEP}database_comment.sql" execute_sql_from_file(cur, sql_file, "database comment") db_existed = False _LOG.warning('Database did not exist.') except ResourceExists as err: db_existed = True _LOG.warning(f"Database already exists: {str(err)}") finally: cur.close() return db_existed
def create_roles_and_users(con: connection, db_user: str, db_password: str) -> None: """ Creates the roles and users. Args: con (object): a connection to the postgres database db_user: The username to connect to the database with. db_password: The password to connect to the database with. Raises: DatabaseError: An error occurred. """ con.set_isolation_level(ISOLATION_LEVEL_READ_COMMITTED) cur = get_cursor(con) sql_file = f"roles{SEP}app_role.sql" execute_sql_from_file(cur, sql_file, "create application role") sql_file = f"roles{SEP}appdbo_role.sql" execute_sql_from_file(cur, sql_file, "create appdbo role") sql_file = f"users{SEP}dbo.sql" execute_sql_from_file(cur, sql_file, "create dbo user") sql_file = f"users{SEP}appuser.sql" execute_sql_from_file(cur, sql_file, "create application user") sql_stmt = f"ALTER USER {db_user} WITH PASSWORD '{db_password}';" execute_sql(cur, sql_stmt, "set pw for application user") sql_stmt = f"ALTER USER dbo WITH PASSWORD '{db_password}';" execute_sql(cur, sql_stmt, "set pw for dbo user") con.commit() cur.close()
def vacuum_database(connector: connection) -> None: """ Vacuum the database, reclaiming disk space and allowing the query optimiser to work on the new state of the database. Requires enough disk space to write a copy of the database tables. """ print("Vacuuming") print("This will take a while..") original_isolation_level = connector.isolation_level connector.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) cursor = connector.cursor() cursor.execute( """ VACUUM FULL ANALYZE; """ ) connector.set_isolation_level(original_isolation_level)