class AbstractMigration:
"""
Generic Migration.
Further specific migrations should inherit and implement these methods.
A "Migration" is something which makes a change to database SCHEMA.
It should be an incremental change which is reversible.
The history of migrations is representative of versions of the DB structure.
The Schema defines the structure of the database.
eg. Adding / removing tables, adding / removing columns to / from tables.
"""
migrations: List[str] = []
cursor = get_cursor()
@classmethod
def run(cls) -> None:
cls.addSQL()
cls.migrations.append(
f"INSERT INTO migrations (id) VALUES ('{cls.getMigrationID()}')")
for sql in cls.migrations:
cls.cursor.execute(sql)
commit()
@classmethod
def getMigrationID(cls) -> str:
pass
@classmethod
def addSQL(cls) -> None:
pass
def runMigration(cls, migration: Type[AbstractMigration]) -> None:
"""
Checks the migration list for registered migrations
and runs them in sequence.
"""
sql = f"SELECT id FROM migrations WHERE id = '{migration.getMigrationID()}'"
migrationExists = get_cursor().execute(sql)
if not migrationExists:
migration.run()
def _saveIngredients(cls, recipe: Recipe) -> None:
ingredientsString = "ingredients"
value = [recipe.getIngredientsText()]
placeholder = "%s"
sql = f"INSERT INTO {cls.ingredientsTable} ({ingredientsString}) VALUES ({placeholder})"
cursor = db.get_cursor()
cursor.execute(sql, value)
db.commit()
def init_db(cls) -> None:
sql = """
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
create table migrations
(
id varchar(255)
);
create unique index migrations_sequence_uindex
on migrations (id);
alter table migrations
add constraint migrations_id
primary key (id);
"""
get_cursor().execute(sql)
def _saveMethod(cls, recipe: Recipe) -> None:
methodString = "method"
value = [recipe.getMethodJson()]
placeholder = "%s"
sql = f"INSERT INTO {cls.methodTable} ({methodString}) VALUES ({placeholder})"
print(sql)
print(value)
cursor = db.get_cursor()
cursor.execute(sql, value)
db.commit()
def getByID(cls, recipeID: Union[str, int]) -> Recipe:
sql = f"SELECT * FROM recipes WHERE recipe_id = '{recipeID}'"
cursor = db.get_cursor()
cursor.execute(sql)
row = cursor.fetchone()
rowDict: dict = {
"author": row[1],
"title": row[2],
"ingredients": row[3],
"cookTime": row[4],
"method": row[5],
"serves": row[6],
}
recipe: Recipe = Recipe(**rowDict)
return recipe
def getByTitle(cls, title: str) -> Recipe:
sql = f"SELECT * FROM recipes WHERE id = '{title}'"
cursor = db.get_cursor()
cursor.execute(sql)
row = cursor.fetchone()
rowDict: dict = {
"author": row[0],
"title": row[1],
"ingredients": row[2],
"cookTime": row[3],
"method": row[4],
"serves": row[5],
}
recipe: Recipe = Recipe(**rowDict)
return recipe
def save(cls, recipe: Recipe) -> None:
cursor = db.get_cursor()
def saveIngredient(ingredient_: Ingredient):
ingredientSql = "INSERT INTO ingredients (ingredient, quantity, recipe_id) VALUES (%s, %s, %s)"
cursor.execute(ingredientSql, [ingredient_.ingredient, ingredient_.quantity, recipe.id])
methodString = "method"
columnsString = ", ".join(list(cls.columns))
values = [
recipe.author,
recipe.title,
recipe.cookTime,
recipe.serves
]
method = [recipe.getMethodJson()]
placeholders = ", ".join(["%s"] * 4)
placeholder = "%s"
sql = f"INSERT INTO {cls.tableName} ({columnsString}) VALUES ({placeholders})"
print(sql)
print(values)
cursor.execute(sql, values)
idSql = f"SELECT recipe_id FROM {cls.tableName} ORDER BY recipe_id DESC LIMIT 1;"
recipe.id = cursor.execute(idSql)
methodSql = f"INSERT INTO {cls.methodTable} ({methodString}) VALUES ({placeholder})"
cursor.execute(methodSql, method)
for ingredient in recipe.ingredients:
print(ingredient.ingredient)
print(ingredient.quantity)
saveIngredient(ingredient)
db.commit()