def drop_known_tables(database: Database) -> None:
"""Permanently delete tables defined in the platform code and remove and all their data from database.
These tables are defined as subclasses of :class:`~forecasting_platform.internal_schema.InternalSchemaBase` or
:class:`~forecasting_platform.dsx_schema.DsxWriteSchemaBase`.
This will not delete unknown tables, e.g. tables used by other programs or removed from the platform.
"""
if database.is_disabled():
logger.error(
f"Cannot drop tables, because {database} connection is not available"
)
return
defined_table_names = database.get_defined_table_names()
logger.info(f"Dropping own database tables: {defined_table_names}")
previous_table_names = database.get_existing_table_names()
logger.info(f"Previously existing tables: {previous_table_names}")
database.schema_base_class.metadata.drop_all()
new_table_names = database.get_existing_table_names()
logger.info(f"Now existing tables: {new_table_names}")
assert all(name in defined_table_names
for name in set(previous_table_names) - set(new_table_names))
def update_forecast_data_with_cleaned_data_sales(
internal_database: Database, cleaned_data_run_id: Optional[int],
cleaned_data_newest_month: Optional[datetime]) -> None:
"""Update actual values for all previous forecasts with newest cleaned data up to newest date in cleaned data.
Actual values with NULL are set to zero if date is before or equal to the newest date in cleaning data.
Any dates after newest date are left unchanged.
Args:
internal_database: Service to access internal database.
cleaned_data_run_id: ID of latest successful platform run, which included cleaning.
cleaned_data_newest_month: Newest month in cleaned data table associated with the ``cleaned_data_run_id``.
"""
if internal_database.is_disabled():
logger.warning(
"Skipping update of previous forecasts due to disabled database")
return
assert cleaned_data_run_id is not None, "Invalid program state: Expected cleaned_data_run_id to exist"
with internal_database.transaction_context() as session:
updated_existing = session.execute(ForecastData.update().where(
CleanedData.c.run_id == cleaned_data_run_id).where(
ForecastData.c.Contract_ID == CleanedData.c.Contract_ID).where(
ForecastData.c.Item_ID == CleanedData.c.Item_ID).where(
ForecastData.c.Predicted_Month ==
CleanedData.c.Date_YYYYMM).values({
"Actual":
CleanedData.c.Order_Quantity,
"Accuracy":
compute_accuracy_as_sql(
CleanedData.c.Order_Quantity,
ForecastData.c.Prediction_Post),
}))
logger.info(
f"Updated {updated_existing.rowcount} rows of forecast_data with old actual values to"
f" newest actual values from cleaned_data")
assert cleaned_data_newest_month, "Invalid program state: Expected cleaned_data_newest_month to exist"
newest_month = int(
cleaned_data_newest_month.strftime(PREDICTION_MONTH_FORMAT))
with internal_database.transaction_context() as session:
updated_nulls = session.execute(ForecastData.update().where(
ForecastData.c.Predicted_Month <= newest_month).where(
ForecastData.c.Actual == None) # noqa: E711
.values({
"Actual":
0,
"Accuracy":
compute_accuracy_as_sql(
0,
ForecastData.c.Prediction_Post)
}))
logger.info(
f"Updated {updated_nulls.rowcount} rows of forecast_data without actual values to"
f" newest actual values from cleaned_data")
def ensure_schema_exists(database: Database) -> None:
"""Ensure database schema configured to be used with database exists and create it if missing."""
if database.is_disabled():
logger.error(
f"Cannot setup schema, because {database} connection is not available"
)
return
with database.transaction_context() as session:
connection = session.connection()
schema_name = database._database_schema
existing_schemas = connection.dialect.get_schema_names(connection)
if schema_name in existing_schemas:
logger.info(f"Found schema: {schema_name} in {database}")
return
logger.info(f"Creating schema: {schema_name} in {database}")
connection.execute(schema.CreateSchema(schema_name))
def ensure_tables_exist(database: Database) -> None:
"""Ensure tables defined in the platform code exist in the database and create them if they are missing.
These tables are defined as subclasses of :class:`~forecasting_platform.internal_schema.InternalSchemaBase` or
:class:`~forecasting_platform.dsx_schema.DsxWriteSchemaBase`.
This will not update existing tables, e.g. when new columns are added.
"""
if database.is_disabled():
logger.error(
f"Cannot setup tables, because {database} connection is not available"
)
return
logger.info(
f"Previously existing tables: {database.get_existing_table_names()}")
database.schema_base_class.metadata.create_all()
logger.info(f"Now existing tables: {database.get_existing_table_names()}")
def data_loader() -> DataLoader:
internal_database = Database(DatabaseType.internal)
dsx_read_database = Database(DatabaseType.dsx_read)
internal_database.is_disabled = lambda: True # type: ignore
return DataLoader(internal_database, dsx_read_database)
def data_output() -> DataOutput:
internal_database = Database(DatabaseType.internal)
dsx_write_database = Database(DatabaseType.dsx_write)
internal_database.is_disabled = lambda: True # type: ignore
return DataOutput(RUNTIME_CONFIG, internal_database, dsx_write_database)