def table_drop(conn: Connection, table_name: str) -> None:
with conn:
table_name = _safe_table_name(table_name)
conn.execute(f"DROP TABLE IF EXISTS {table_name}")
conn.execute(
f"DELETE FROM {_SCHEMA_TABLE_NAME} WHERE table_name = '{table_name}'"
)
def table_merge(
conn: Connection,
table_names: List[str],
on: List[str],
how: str = "inner",
into_table: str = None,
) -> Optional[Iterable[Dict[str, Any]]]:
table_names = [_safe_table_name(name) for name in table_names]
assert len(table_names) == len(
set(table_names)), f"Table names must all be unique"
on = [_safe_column_name(col) for col in on]
left = table_names[0]
statement_join = f"SELECT * FROM {left}"
for right in table_names[1:]:
clause_on = " AND ".join(f"{left}.{col} = {right}.{col}" for col in on)
statement_join += f" {how.upper()} JOIN {right} ON ({clause_on})"
with conn:
if into_table:
combined_schema = {}
for table_name in table_names:
combined_schema.update(_fetch_table_schema(conn, table_name))
# Make sure the receiving table exists and is empty
into_table = _safe_table_name(into_table)
table_drop(conn, into_table)
table_create(conn, into_table, combined_schema)
# Coalesce all shared columns
if len(table_names) > 1:
coalesce_helper = lambda x: ",".join(f"{table}.{x}"
for table in table_names)
select_map = {
col: _safe_column_name(col)
for col in combined_schema.keys()
}
select_map.update({
col: f"COALESCE({coalesce_helper(col)}) AS {col}"
for col in on
})
clause_select = ",".join(select_map[col]
for col in combined_schema.keys())
statement_join = statement_join.replace(
"SELECT *", f"SELECT {clause_select}")
# Pre-pend the insert statement so the output goes into the table
conn.execute(f"INSERT INTO {into_table} " + statement_join)
# Otherwise perform the merge and yield records from the cursor
else:
cursor = conn.execute(statement_join)
return _output_named_records(cursor)
def __ensure_table(self, connection: Connection) -> None:
info = connection.execute(*compile(
Q(T.sqlite_master).fields(
'*').where((T.sqlite_master.name == 'version')
& (T.sqlite_master.type == 'table')))).fetchone()
if not info:
connection.execute(
'CREATE TABLE version (number INTEGER NOT NULL)')
connection.execute(
*Q(T(self.table), result=Result(
compile=compile)).insert({T(self.table).number: 0}))
def _statement_insert_record_tuple(
conn: Connection,
table_name: str,
columns: Tuple[str],
record: Tuple[str],
replace: bool = False,
) -> None:
table_name = _safe_table_name(table_name)
verb_insert = "INSERT " + ("OR REPLACE " if replace else "")
placeholders = ", ".join("?" for _ in columns)
column_names = ", ".join(_safe_column_name(name) for name in columns)
conn.execute(
f"{verb_insert} INTO {table_name} ({column_names}) VALUES ({placeholders})",
record)
def table_select_all(conn: Connection,
table_name: str,
sort_by: List[str] = None,
sort_ascending: bool = True) -> Iterable[Dict[str, Any]]:
table_name = _safe_table_name(table_name)
statement_select = f"SELECT * FROM {table_name}"
if sort_by:
sort_by = ",".join(_safe_column_name(col) for col in sort_by)
statement_select += f" ORDER BY {sort_by} {'ASC' if sort_ascending else 'DESC'}"
cursor = conn.execute(statement_select)
return _output_named_records(cursor)
def table_create(conn: Connection, table_name: str, schema: Dict[str,
str]) -> None:
table_name = _safe_table_name(table_name)
sql_schema = ", ".join(f"{_safe_column_name(name)} {dtype}"
for name, dtype in schema.items())
with conn:
# Create new table (ignore if exists)
conn.execute(f"CREATE TABLE IF NOT EXISTS {table_name} ({sql_schema})")
# Every time a table is created, store its schema in our helper table
_statement_insert_record_dict(
conn,
_SCHEMA_TABLE_NAME,
{
"table_name": table_name,
"schema_json": json.dumps(schema)
},
replace=True,
)
def _get_recipe(db: Connection, uuid: UUID, version: int) -> Optional[GetRecipe]:
recipe = db.execute(GET_RECIPE, (str(uuid), version)).fetchone()
if recipe is None:
return None
ingredients = db.execute(GET_INGREDIENTS, (str(recipe[0])))
steps = db.execute(GET_STEPS, (str(recipe[0])))
return GetRecipe(
uuid=UUID(recipe[1]),
version=recipe[2],
name=recipe[3],
duration_minute=recipe[4],
ingredients=[
Ingredient(
name=i[0],
numerator=i[1],
denominator=i[2],
measure=Measure[i[3]],
)
for i in ingredients
],
steps=[Step(name=s[0], duration_minute=s[1]) for s in steps],
)
def _fetch_table_schema(conn: Connection, table_name: str) -> Dict[str, str]:
with conn:
schema_json = conn.execute(
f'SELECT schema_json FROM {_SCHEMA_TABLE_NAME} WHERE table_name = "{table_name}"'
).fetchone()[0]
return json.loads(schema_json)
def create_table(connection: Connection, query: str) -> None:
connection.execute(query)
connection.commit()