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)
