def soft_delete_from_production_for_assignment_relation(
self, table: str, source_system: str) -> None:
"""
Updates production records that do not have a match in the staging table
by setting their `deletedat` value to the current timestamp, but
only for the related assignments in the staging table.
Parameters
----------
table: str
Name of the table to soft delete on, not including the `stg_`.
source_system: str
The SourceSystem currently being processed.
"""
assert table.strip() != "", "Argument `table` cannot be whitespace"
statement = ""
if self.engine == DbEngine.MSSQL:
statement = MS_builder.soft_delete_from_production_for_assignment_relation(
table, source_system)
if self.engine == DbEngine.POSTGRESQL:
statement = PG_builder.soft_delete_from_production_for_assignment_relation(
table, source_system)
row_count = self._exec(statement)
logger.debug(f"Soft-deleted {row_count} records in table `{table}`")
def add_processed_file(self, path: str, resource_name: str, rows: int):
"""
Records that a file has been processed and thus should not be processed
a second time.
Parameters
----------
path: str
Filesystem path for the file that was processed.
resource_name: str
Name of the resource covered by the file.
rows: int
Number of rows in the file.
"""
statement = ""
if self.engine == DbEngine.MSSQL:
statement = MS_builder.add_processed_file(path, resource_name,
rows)
if self.engine == DbEngine.POSTGRESQL:
statement = PG_builder.add_processed_file(path, resource_name,
rows)
statement = statement.strip()
try:
_ = self._exec(statement)
except ProgrammingError as pe:
logger.exception(pe)
raise
def unsoft_delete_returned_submission_types(self,
source_system: str) -> None:
"""
Unmarks previously "deleted" Assignment Submission Types when they are
present in the incoming data.
Parameters
----------
source_system: str
The name of the source system for the current import process.
"""
statement = ""
if self.engine == DbEngine.MSSQL:
statement = MS_builder.unsoft_delete_returned_submission_types(
source_system)
if self.engine == DbEngine.POSTGRESQL:
statement = PG_builder.unsoft_delete_returned_submission_types(
source_system)
row_count = self._exec(statement)
logger.debug(
f"Un-soft deleted {row_count} records in table `{Table.ASSIGNMENT_SUBMISSION_TYPES}`."
)
def insert_new_records_to_production(self, table: str,
columns: List[str]) -> None:
"""
Copies new records from the staging table to the production table.
Parameters
----------
table: str
Name of the table to truncate, not including the `stg_` prefix
columns: List[str]
A list of the column names in the table
"""
assert table.strip() != "", "Argument `table` cannot be whitespace"
assert len(columns) > 0, "Argument `columns` cannot be empty"
column_string = ",".join([f"\n {c}" for c in columns])
statement = ""
if self.engine == DbEngine.MSSQL:
statement = MS_builder.insert_new_records_to_production(
table, column_string)
if self.engine == DbEngine.POSTGRESQL:
statement = PG_builder.insert_new_records_to_production(
table, column_string)
row_count = self._exec(statement)
logger.debug(f"Inserted {row_count} records into table `{table}`.")
def truncate_staging_table(self, table: str) -> None:
"""
Executes a truncate command on the staging version of a table.
Parameters
----------
table: str
Name of the table to truncate, not including the `stg_` prefix
"""
assert table.strip() != "", "Argument `table` cannot be whitespace"
if self.engine == DbEngine.MSSQL:
self._exec(MS_builder.truncate_stg_table(table))
if self.engine == DbEngine.POSTGRESQL:
self._exec(PG_builder.truncate_stg_table(table))
def insert_new_submission_types(self) -> None:
"""
Inserts new Assignment Submission Type records from staging table
into the production table.
"""
statement = ""
if self.engine == DbEngine.MSSQL:
statement = MS_builder.insert_new_submission_types()
if self.engine == DbEngine.POSTGRESQL:
statement = PG_builder.insert_new_submission_types()
row_count = self._exec(statement)
logger.debug(
f"Updated {row_count} records in table `{Table.ASSIGNMENT_SUBMISSION_TYPES}`."
)
def enable_staging_natural_key_index(self, table: str) -> None:
"""
Re-builds the natural key index on the staging table.
Parameters
----------
table: str
Name of the table to truncate, not including the `stg_` prefix
"""
assert table.strip() != "", "Argument `table` cannot be whitespace"
if self.engine == DbEngine.MSSQL:
self._exec(MS_builder.enable_staging_natural_key_index(table))
if self.engine == DbEngine.POSTGRESQL:
self._exec(PG_builder.recreate_staging_natural_key_index(table))
def copy_updates_to_production(self, table: str,
columns: List[str]) -> None:
"""
Updates modified records in production based on the staging table, based
on the LastModifiedDate.
Parameters
----------
table: str
Name of the table to truncate, not including the `stg_` prefix
columns: List[str]
A list of the column names in the table
"""
assert table.strip() != "", "Argument `table` cannot be whitespace"
assert len(columns) > 0, "Argument `columns` cannot be empty"
update_columns = (
",".join([
f"\n {c} = stg.{c}" for c in columns if c not in (
# These are natural key columns that should never be
# updated.
"SourceSystem",
"SourceSystemIdentifier",
"LMSSectionSourceSystemIdentifier",
"LMSUserSourceSystemIdentifier",
"AssignmentSourceSystemIdentifier",
)
]) + ",\n DeletedAt = NULL")
statement = ""
if self.engine == DbEngine.MSSQL:
statement = MS_builder.copy_updates_to_production(
table, update_columns)
if self.engine == DbEngine.POSTGRESQL:
statement = PG_builder.copy_updates_to_production(
table, update_columns)
row_count = self._exec(statement)
logger.debug(f"Updated {row_count} records in table `{table}`.")
def get_processed_files(self, resource_name: str) -> Set[str]:
try:
query = ""
if self.engine == DbEngine.MSSQL:
query = MS_builder.get_processed_files(resource_name)
if self.engine == DbEngine.POSTGRESQL:
query = PG_builder.get_processed_files(resource_name)
query = query.strip()
result = pd.read_sql_query(query, self.db_adapter.engine)
result.columns = result.columns.str.lower()
if "fullpath" in result:
return set(result["fullpath"])
return set()
except ProgrammingError as pe:
logger.exception(pe)
raise
def insert_new_records_to_production_for_assignment_and_user_relation(
self, table: str, columns: List[str]) -> None:
"""
Copies new records from the staging table to the production table. Specialized
for tables that have a foreign key to both Assignment and LMSUser.
Parameters
----------
table: str
Name of the table to truncate, not including the `stg_` prefix
columns: List[str]
A list of the column names in the table
"""
assert table.strip() != "", "Argument `table` cannot be whitespace"
assert len(columns) > 0, "Argument `columns` cannot be empty"
insert_columns = ",".join([
f"\n {c}" for c in columns
if not (c == "AssignmentSourceSystemIdentifier"
or c == "LMSUserSourceSystemIdentifier")
])
select_columns = ",".join([
f"\n stg.{c}" for c in columns
if not (c == "AssignmentSourceSystemIdentifier"
or c == "LMSUserSourceSystemIdentifier")
])
statement = ""
if self.engine == DbEngine.MSSQL:
statement = MS_builder.insert_new_records_to_production_for_assignment_and_user_relation(
table, insert_columns, select_columns)
if self.engine == DbEngine.POSTGRESQL:
statement = PG_builder.insert_new_records_to_production_for_assignment_and_user_relation(
table, insert_columns, select_columns)
row_count = self._exec(statement)
logger.debug(f"Inserted {row_count} records into table `{table}`.")