def db_initialise():
"""
Create the migrations folder and DB table if they are non-existent
:return: None
"""
generate_migration_file()
if not MySQLScheme.fetch_one(IS_MIGRATION_TABLE,
**{"args": {'schema': SCHEMA}}):
with open(MIGRATION_FILE, 'r') as init_sql:
data = init_sql.read()
if f"CREATE TABLE IF NOT EXISTS {MIGRATION_TABLE}" not in data:
when = str(int(time.time()))
sql_file = os.path.join(MIGRATION_FOLDER, f"{when}.sql")
with open(sql_file, 'w') as save_sql:
up = MYSQL_MIGRATION_UP.format(f"upgrade-{when}", when,
MIGRATION_TABLE)
down = MYSQL_MIGRATION_DOWN.format(f"downgrade-{when}",
MIGRATION_TABLE)
save_sql.write("\n\n".join([up, down]))
LOGGER.info(f"migration file: "
f"{os.path.join('migrations', sql_file)}")
else:
when = re.findall('[0-9]+', data)[0]
generate_migration_file()
dbi_query = anosql.from_path(MIGRATION_FILE, 'psycopg2')
MySQLScheme.commit(getattr(dbi_query, f"upgrade_{when}").sql)
LOGGER.info(f"initial successful migration: {when}")
def db_downgrade(step):
"""
Downgrades a DB to a previous version as specified with the `step`
:param step: number of downgrades to do
:return: None
"""
to_use = [_.strip('.sql') for _ in migration_files()]
# since it's a downgrade, a reverse of the migration is essential
to_use.reverse()
generate_migration_file()
dbd_query = anosql.from_path(MIGRATION_FILE, 'psycopg2')
try:
count = 0
for _ in to_use:
count += 1
if MySQLScheme.fetch_one(REVISION_EXISTS,
**{"args": {'revision': _}}):
MySQLScheme.commit(getattr(dbd_query, f"downgrade_{_}").sql)
LOGGER.info(f"successful downgrade: {_}")
if count == step:
break
except errors.ProgrammingError:
print("no more downgrade left")
def wrapper(*args, **kwargs):
connection = None
try:
connection = kwargs['conn'] = sqlite3.connect(SQLITE_DB_FILE)
return function(*args, **kwargs)
except (Exception, sqlite3.Error) as error:
if connection:
connection.close()
LOGGER.error(error)
raise error
def wrapper(*args, **kwargs):
connection = None
try:
connection = kwargs['conn'] = psycopg2.connect(DSN)
return function(*args, **kwargs)
except (Exception, psycopg2.DatabaseError,
psycopg2.errors.UndefinedTable) as error:
if connection:
connection.rollback()
connection.close()
LOGGER.error(error)
raise error
def db_migrate():
"""
Generate a new .sql file that you can alter to taste.
The epoch time of generation is used in naming the generated file
:return: None
"""
when = str(int(time.time()))
sql_file = os.path.join(MIGRATION_FOLDER, f"{when}.sql")
with open(sql_file, 'w') as save_sql:
up = MYSQL_UP.format(f"upgrade-{when}", when, MIGRATION_TABLE)
down = MYSQL_DOWN.format(f"downgrade-{when}", when, MIGRATION_TABLE)
save_sql.write("\n\n".join([up, down]))
LOGGER.info(f"migration file: {os.path.join('migrations', sql_file)}")
def db_upgrade():
"""
Runs an upgrade on a DB using the generated `MIGRATION_FILE`
:return: None
"""
generate_migration_file()
dbu_query = anosql.from_path(MIGRATION_FILE, 'psycopg2')
for time_step in [_.strip('.sql') for _ in migration_files()]:
decide = MySQLScheme.fetch_one(REVISION_EXISTS,
**{"args": {'revision': time_step}})
if not decide:
MySQLScheme.commit(getattr(dbu_query, f"upgrade_{time_step}").sql)
LOGGER.info(f"successful migration: {time_step}")
else:
LOGGER.info(f'migration already exists: {time_step}')
def wrapper(*args, **kwargs):
connection = None
try:
connection = kwargs['conn'] = MySQLConnection(
host=DB_URL.hostname,
user=DB_URL.username,
port=DB_URL.port or 3306,
password=DB_URL.password,
database=DB_URL.path.strip('/'))
return function(*args, **kwargs)
except (Exception, errors.Error) as error:
if connection:
connection.rollback()
connection.close()
LOGGER.error(error)
raise error
def status():
"""
Shows the already run migrations
:return: String
"""
response = []
to_use = [_.strip('.sql') for _ in migration_files()]
LOGGER.info(f"migration files: {to_use}")
try:
for step in to_use:
if MySQLScheme.fetch_one(REVISION_EXISTS,
**{"args": {'revision': step}}):
response.append(f"migrations done : {step}")
else:
response.append(f"migrations undone: {step}")
return "\n".join(response)
except errors.ProgrammingError:
return "No existing migration table"