def __get_report_queries(extract_params):
extract_type = extract_params[Extract.REPORT_TYPE]
queries = {}
if extract_type == ExtractType.studentRegistrationStatistics:
academic_year_query = student_reg_statistics.get_academic_year_query(
extract_params[TaskConstants.ACADEMIC_YEAR],
extract_params[TaskConstants.STATE_CODE])
match_id_query = student_reg_statistics.get_match_id_query(
extract_params[TaskConstants.ACADEMIC_YEAR],
extract_params[TaskConstants.STATE_CODE])
queries = {
QueryType.QUERY: compile_query_to_sql_text(academic_year_query),
QueryType.MATCH_ID_QUERY: compile_query_to_sql_text(match_id_query)
}
elif extract_type == ExtractType.studentAssessmentCompletion:
registered_query = student_reg_completion.get_academic_year_query(
extract_params[TaskConstants.ACADEMIC_YEAR],
extract_params[TaskConstants.STATE_CODE])
asmt_query = student_reg_completion.get_assessment_query(
extract_params[TaskConstants.ACADEMIC_YEAR],
extract_params[TaskConstants.STATE_CODE])
queries = {
QueryType.QUERY: compile_query_to_sql_text(registered_query),
QueryType.ASMT_OUTCOME_QUERY: compile_query_to_sql_text(asmt_query)
}
return queries
def test_compile_query_to_sql_text(self):
with UnittestEdcoreDBConnection() as connection:
fact = connection.get_table('fact_asmt_outcome_vw')
query = select([fact.c.state_code], from_obj=[fact])
query = query.where(fact.c.state_code == 'UT')
str_query = compile_query_to_sql_text(query)
self.assertIn("fact_asmt_outcome_vw.state_code = 'UT'", str_query)
def explode_data_to_dim_table(conf, source_table, target_table, column_mapping, column_types):
'''
Main function to move data from source table to target tables.
Source table can be INT_SBAC_ASMT, and INT_SBAC_ASMT_OUTCOME. Target table can be any dim tables in star schema.
@param conf: one dictionary which has database settings, and guid_batch
@param source_table: name of the source table where the data comes from
@param target_table: name of the target table where the data should be moved to
@param column_mapping: list of tuple of:
column_name_in_target, column_name_in_source
@param column_types: data types of all columns in one target table
'''
# create database connection to target
with get_target_connection(conf[mk.TENANT_NAME], conf[mk.GUID_BATCH]) as conn:
# create insertion query
# TODO: find out if the affected rows, time can be returned, so that the returned info can be put in the log
# send only data that is needed to be inserted (such insert, update) to dimenstion table
query = create_insert_query(conf, source_table, target_table, column_mapping, column_types, True,
'C' if source_table in op_table_conf else None)
logger.info(compile_query_to_sql_text(query))
# execute the query
affected_rows = execute_udl_queries(conn, [query],
'Exception -- exploding data from integration to target ' +
'{target_table}'.format(target_table=target_table),
'move_to_target', 'explode_data_to_dim_table')
return affected_rows
def explode_data_to_dim_table(conf, source_table, target_table, column_mapping,
column_types):
'''
Main function to move data from source table to target tables.
Source table can be INT_SBAC_ASMT, and INT_SBAC_ASMT_OUTCOME. Target table can be any dim tables in star schema.
@param conf: one dictionary which has database settings, and guid_batch
@param source_table: name of the source table where the data comes from
@param target_table: name of the target table where the data should be moved to
@param column_mapping: list of tuple of:
column_name_in_target, column_name_in_source
@param column_types: data types of all columns in one target table
'''
# create database connection to target
with get_target_connection(conf[mk.TENANT_NAME],
conf[mk.GUID_BATCH]) as conn:
# create insertion query
# TODO: find out if the affected rows, time can be returned, so that the returned info can be put in the log
# send only data that is needed to be inserted (such insert, update) to dimenstion table
query = create_insert_query(
conf, source_table, target_table, column_mapping, column_types,
True, 'C' if source_table in op_table_conf else None)
logger.info(compile_query_to_sql_text(query))
# execute the query
affected_rows = execute_udl_queries(
conn, [query],
'Exception -- exploding data from integration to target ' +
'{target_table}'.format(target_table=target_table),
'move_to_target', 'explode_data_to_dim_table')
return affected_rows
def test_compile_query_to_sql_text(self):
with UnittestEdcoreDBConnection() as connection:
fact = connection.get_table('fact_asmt_outcome_vw')
query = select([fact.c.state_code], from_obj=[fact])
query = query.where(fact.c.state_code == 'UT')
str_query = compile_query_to_sql_text(query)
self.assertIn("fact_asmt_outcome_vw.state_code = 'UT'", str_query)
def test_get_extract_assessment_query_compiled(self):
params = {'stateCode': 'NC',
'asmtYear': '2015',
'asmtType': 'SUMMATIVE',
'asmtSubject': 'Math',
'extractType': 'studentAssessment'}
query = compile_query_to_sql_text(get_extract_assessment_query(params))
self.assertIsNotNone(query)
self.assertIsInstance(query, str)
self.assertIn('SUMMATIVE', query)
def test_get_extract_items_query_compiled(self):
params = {'stateCode': 'NC',
'asmtYear': '2015',
'asmtType': 'SUMMATIVE',
'asmtSubject': 'Math',
'asmtGrade': '03',
'extractType': 'itemLevel'}
query = compile_query_to_sql_text(get_extract_assessment_item_and_raw_query(params, ExtractType.itemLevel))
self.assertIsNotNone(query)
self.assertIsInstance(query, str)
self.assertIn('SUMMATIVE', query)
def test_get_extract_assessment_query_compiled(self):
params = {
'stateCode': 'NC',
'asmtYear': '2015',
'asmtType': 'SUMMATIVE',
'asmtSubject': 'Math',
'extractType': 'studentAssessment'
}
query = compile_query_to_sql_text(get_extract_assessment_query(params))
self.assertIsNotNone(query)
self.assertIsInstance(query, str)
self.assertIn('SUMMATIVE', query)
def test_get_extract_items_query_compiled(self):
params = {
'stateCode': 'NC',
'asmtYear': '2015',
'asmtType': 'SUMMATIVE',
'asmtSubject': 'Math',
'asmtGrade': '03',
'extractType': 'itemLevel'
}
query = compile_query_to_sql_text(
get_extract_assessment_item_and_raw_query(params,
ExtractType.itemLevel))
self.assertIsNotNone(query)
self.assertIsInstance(query, str)
self.assertIn('SUMMATIVE', query)
def _create_new_task(request_id, user, tenant, params, query, extract_type=None, asmt_metadata=False, is_tenant_level=False,
extract_file_path=None):
'''
TODO comment
'''
task = {}
task[TaskConstants.TASK_TASK_ID] = create_new_entry(user, request_id, params)
task[TaskConstants.TASK_QUERIES] = {QueryType.QUERY: compile_query_to_sql_text(query)}
if asmt_metadata:
task[TaskConstants.TASK_FILE_NAME] = get_asmt_metadata_file_path(params, tenant, request_id)
task[TaskConstants.EXTRACTION_DATA_TYPE] = ExtractionDataType.QUERY_JSON
else:
if extract_file_path is not None:
task[TaskConstants.TASK_FILE_NAME] = extract_file_path(params, tenant, request_id,
is_tenant_level=is_tenant_level)
if extract_type and extract_type is ExtractType.itemLevel:
task[TaskConstants.EXTRACTION_DATA_TYPE] = ExtractionDataType.QUERY_ITEMS_CSV
elif extract_type and extract_type is ExtractType.rawData:
task[TaskConstants.EXTRACTION_DATA_TYPE] = ExtractionDataType.QUERY_RAW_XML
else:
task[TaskConstants.EXTRACTION_DATA_TYPE] = ExtractionDataType.QUERY_CSV
return task
def _create_new_task(request_id,
user,
tenant,
params,
query,
extract_type=None,
asmt_metadata=False,
is_tenant_level=False,
extract_file_path=None):
'''
TODO comment
'''
task = {}
task[TaskConstants.TASK_TASK_ID] = create_new_entry(
user, request_id, params)
task[TaskConstants.TASK_QUERIES] = {
QueryType.QUERY: compile_query_to_sql_text(query)
}
if asmt_metadata:
task[TaskConstants.TASK_FILE_NAME] = get_asmt_metadata_file_path(
params, tenant, request_id)
task[
TaskConstants.EXTRACTION_DATA_TYPE] = ExtractionDataType.QUERY_JSON
else:
if extract_file_path is not None:
task[TaskConstants.TASK_FILE_NAME] = extract_file_path(
params, tenant, request_id, is_tenant_level=is_tenant_level)
if extract_type and extract_type is ExtractType.itemLevel:
task[TaskConstants.
EXTRACTION_DATA_TYPE] = ExtractionDataType.QUERY_ITEMS_CSV
elif extract_type and extract_type is ExtractType.rawData:
task[TaskConstants.
EXTRACTION_DATA_TYPE] = ExtractionDataType.QUERY_RAW_XML
else:
task[TaskConstants.
EXTRACTION_DATA_TYPE] = ExtractionDataType.QUERY_CSV
return task
def test_get_schema_check_query(self):
expected_query = "SELECT schema_name FROM information_schema.schemata WHERE schema_name = '90901b70-ddaa-11e2-a95d-68a86d3c2f82'"
query = _get_schema_check_query('90901b70-ddaa-11e2-a95d-68a86d3c2f82')
query_string = str(compile_query_to_sql_text(query)).replace("\n", "")
self.assertEquals(query_string, expected_query)
def test_get_schema_check_query(self):
expected_query = "SELECT schema_name FROM information_schema.schemata WHERE schema_name = '90901b70-ddaa-11e2-a95d-68a86d3c2f82'"
query = _get_schema_check_query('90901b70-ddaa-11e2-a95d-68a86d3c2f82')
query_string = str(compile_query_to_sql_text(query)).replace("\n", "")
self.assertEquals(query_string, expected_query)