def setUpClass(cls):
""" Set up class-wide resources (test data) """
super(SettingsTests, cls).setUpClass()
# TODO: refactor into a pytest fixture
with create_app().app_context():
# get the submission test user
sess = GlobalDB.db().session
cls.session = sess
cgac = CGAC(cgac_code='097')
rule = RuleSql(rule_sql_id=1,
rule_sql='',
rule_label='FABS1',
rule_error_message='',
query_name='',
file_id=1,
rule_severity_id=2,
rule_cross_file_flag=False)
sess.add_all([cgac, rule])
sess.commit()
default_setting = RuleSetting(agency_code='097',
rule_label=rule.rule_label,
file_id=rule.file_id,
target_file_id=rule.target_file_id,
priority=1,
impact_id=1)
sess.add(default_setting)
sess.commit()
def addSqlRule(self,
ruleSql,
ruleLabel,
ruleDescription,
ruleErrorMsg,
fileId,
ruleSeverity,
crossFileFlag=False,
queryName=None,
targetFileId=None):
"""Insert SQL-based validation rule.
Args:
ruleSql = the validation expressed as SQL
ruleLabel = rule label used in the DATA Act standard
ruleDescription = rule description
ruleErrorMsg = the rule's standard error message
crossFileFlag = indicates whether or not the rule is cross-file
fileType = file type that the rule applies to
severityName = severity of the rule
Returns:
ID of new rule
"""
newRule = RuleSql(rule_sql=ruleSql,
rule_label=ruleLabel,
rule_description=ruleDescription,
rule_error_message=ruleErrorMsg,
rule_cross_file_flag=crossFileFlag,
file_id=fileId,
rule_severity=ruleSeverity,
query_name=queryName,
target_file_id=targetFileId)
self.session.add(newRule)
self.session.commit()
return True
def load_sql(cls, filename):
"""Load SQL-based validation rules to db."""
with create_app().app_context():
sess = GlobalDB.db().session
# Delete all records currently in table
sess.query(RuleSql).delete()
filename = os.path.join(cls.sql_rules_path, filename)
# open csv
with open(filename, 'rU') as csvfile:
# read header
header = csvfile.readline()
# split header into filed names
raw_field_names = header.split(',')
field_names = []
# clean field names
for field in raw_field_names:
field_names.append(FieldCleaner.clean_string(field))
unknown_fields = set(field_names) - set(cls.headers)
if len(unknown_fields) != 0:
raise KeyError("".join([
"Found unexpected fields: ",
str(list(unknown_fields))
]))
missing_fields = set(cls.headers) - set(field_names)
if len(missing_fields) != 0:
raise ValueError("".join([
"Missing required fields: ",
str(list(missing_fields))
]))
reader = csv.DictReader(csvfile, fieldnames=field_names)
for row in reader:
sql = cls.read_sql_str(row['query_name'])
rule_sql = RuleSql(
rule_sql=sql,
rule_label=row['rule_label'],
rule_error_message=row['rule_error_message'],
query_name=row['query_name'])
# look up file type id
try:
file_id = FILE_TYPE_DICT[row["file_type"]]
except Exception as e:
raise Exception(
"{}: file type={}, rule label={}. Rule not loaded."
.format(e, row["file_type"], row["rule_label"]))
try:
if row["target_file"].strip() == "":
# No target file provided
target_file_id = None
else:
target_file_id = FILE_TYPE_DICT[row["target_file"]]
except Exception as e:
raise Exception(
"{}: file type={}, rule label={}. Rule not loaded."
.format(e, row["target_file"], row["rule_label"]))
# set cross file flag
flag = FieldCleaner.clean_string(
row["rule_cross_file_flag"])
if flag in ('true', 't', 'y', 'yes'):
cross_file_flag = True
else:
cross_file_flag = False
rule_sql.rule_severity_id = RULE_SEVERITY_DICT[
row['severity_name']]
rule_sql.file_id = file_id
rule_sql.target_file_id = target_file_id
rule_sql.rule_cross_file_flag = cross_file_flag
sess.merge(rule_sql)
sess.commit()
def test_file_filter_rulesql(database):
sess = database.session
# Setup RuleSql
rsql_a = RuleSql(rule_sql='',
rule_label='A1',
rule_error_message='',
query_name='',
file_id=FILE_TYPE_DICT_LETTER_ID['A'],
rule_severity_id=RULE_SEVERITY_DICT['fatal'],
rule_cross_file_flag=False,
target_file_id=None)
rsql_b = RuleSql(rule_sql='',
rule_label='B2',
rule_error_message='',
query_name='',
file_id=FILE_TYPE_DICT_LETTER_ID['B'],
rule_severity_id=RULE_SEVERITY_DICT['fatal'],
rule_cross_file_flag=False,
target_file_id=None)
rsql_c = RuleSql(rule_sql='',
rule_label='C3',
rule_error_message='',
query_name='',
file_id=FILE_TYPE_DICT_LETTER_ID['C'],
rule_severity_id=RULE_SEVERITY_DICT['fatal'],
rule_cross_file_flag=False,
target_file_id=None)
rsql_cross_ab = RuleSql(rule_sql='',
rule_label='A4',
rule_error_message='',
query_name='',
file_id=FILE_TYPE_DICT_LETTER_ID['A'],
rule_severity_id=RULE_SEVERITY_DICT['fatal'],
rule_cross_file_flag=True,
target_file_id=FILE_TYPE_DICT_LETTER_ID['B'])
rsql_cross_ba = RuleSql(rule_sql='',
rule_label='B5',
rule_error_message='',
query_name='',
file_id=FILE_TYPE_DICT_LETTER_ID['B'],
rule_severity_id=RULE_SEVERITY_DICT['fatal'],
rule_cross_file_flag=True,
target_file_id=FILE_TYPE_DICT_LETTER_ID['A'])
rsql_cross_bc = RuleSql(rule_sql='',
rule_label='B6',
rule_error_message='',
query_name='',
file_id=FILE_TYPE_DICT_LETTER_ID['B'],
rule_severity_id=RULE_SEVERITY_DICT['fatal'],
rule_cross_file_flag=True,
target_file_id=FILE_TYPE_DICT_LETTER_ID['C'])
all_rules = [
rsql_a, rsql_b, rsql_c, rsql_cross_ab, rsql_cross_ba, rsql_cross_bc
]
sess.add_all(all_rules)
sess.commit()
base_query = sess.query(RuleSql)
# no file list, no filtering
files = []
query = filters_helper.file_filter(base_query, RuleSql, files)
expected_results = all_rules
results = query.all()
assert set(results) == set(expected_results)
# filter by single file
files = ['A', 'C']
query = filters_helper.file_filter(base_query, RuleSql, files)
expected_results = [rsql_a, rsql_c]
results = query.all()
assert set(results) == set(expected_results)
# filter by cross file
files = ['cross-AB']
query = filters_helper.file_filter(base_query, RuleSql, files)
expected_results = [rsql_cross_ab, rsql_cross_ba]
results = query.all()
assert set(results) == set(expected_results)
def setup_tests(sess):
db_objects = []
# Setup agencies
cgac1 = CGACFactory(cgac_code='097', agency_name='CGAC')
frec = FRECFactory(cgac=cgac1, frec_code='1125', agency_name='FREC')
db_objects.extend([cgac1, frec])
# Setup rules
rsql_a1 = RuleSql(rule_sql='',
rule_label='A1',
rule_error_message='A1 Description',
query_name='',
file_id=FILE_TYPE_DICT_LETTER_ID['A'],
rule_severity_id=RULE_SEVERITY_DICT['warning'],
rule_cross_file_flag=False,
target_file_id=None)
rsql_a2 = RuleSql(rule_sql='',
rule_label='A2',
rule_error_message='A2 Description',
query_name='',
file_id=FILE_TYPE_DICT_LETTER_ID['A'],
rule_severity_id=RULE_SEVERITY_DICT['fatal'],
rule_cross_file_flag=False,
target_file_id=None)
rsql_a3 = RuleSql(rule_sql='',
rule_label='A3',
rule_error_message='A3 Description',
query_name='',
file_id=FILE_TYPE_DICT_LETTER_ID['A'],
rule_severity_id=RULE_SEVERITY_DICT['warning'],
rule_cross_file_flag=False,
target_file_id=None)
rsql_a4 = RuleSql(rule_sql='',
rule_label='A4',
rule_error_message='A4 Description',
query_name='',
file_id=FILE_TYPE_DICT_LETTER_ID['A'],
rule_severity_id=RULE_SEVERITY_DICT['fatal'],
rule_cross_file_flag=False,
target_file_id=None)
rsql_b1 = RuleSql(rule_sql='',
rule_label='B1',
rule_error_message='B1 Description',
query_name='',
file_id=FILE_TYPE_DICT_LETTER_ID['B'],
rule_severity_id=RULE_SEVERITY_DICT['warning'],
rule_cross_file_flag=False,
target_file_id=None)
rsql_b2 = RuleSql(rule_sql='',
rule_label='B2',
rule_error_message='B2 Description',
query_name='',
file_id=FILE_TYPE_DICT_LETTER_ID['B'],
rule_severity_id=RULE_SEVERITY_DICT['fatal'],
rule_cross_file_flag=False,
target_file_id=None)
rsql_b3 = RuleSql(rule_sql='',
rule_label='B3',
rule_error_message='B3 Description',
query_name='',
file_id=FILE_TYPE_DICT_LETTER_ID['B'],
rule_severity_id=RULE_SEVERITY_DICT['warning'],
rule_cross_file_flag=False,
target_file_id=None)
rsql_b4 = RuleSql(rule_sql='',
rule_label='B4',
rule_error_message='B4 Description',
query_name='',
file_id=FILE_TYPE_DICT_LETTER_ID['B'],
rule_severity_id=RULE_SEVERITY_DICT['fatal'],
rule_cross_file_flag=False,
target_file_id=None)
rsql_c1 = RuleSql(rule_sql='',
rule_label='C1',
rule_error_message='C1 Description',
query_name='',
file_id=FILE_TYPE_DICT_LETTER_ID['C'],
rule_severity_id=RULE_SEVERITY_DICT['fatal'],
rule_cross_file_flag=False,
target_file_id=None)
rsql_c2 = RuleSql(rule_sql='',
rule_label='C2',
rule_error_message='C2 Description',
query_name='',
file_id=FILE_TYPE_DICT_LETTER_ID['C'],
rule_severity_id=RULE_SEVERITY_DICT['fatal'],
rule_cross_file_flag=False,
target_file_id=None)
sess.add_all([
rsql_a1, rsql_a2, rsql_a3, rsql_a4, rsql_b1, rsql_b2, rsql_b3, rsql_b4,
rsql_c1, rsql_c2
])
# Setup default rules
load_default_rule_settings(sess)
sess.add_all(db_objects)
sess.commit()
def load_sql(cls, filename):
"""Load SQL-based validation rules to db."""
with create_app().app_context():
sess = GlobalDB.db().session
# Delete all records currently in table
sess.query(RuleSql).delete()
filename = os.path.join(cls.sql_rules_path, filename)
# open csv
with open(filename, 'rU') as csvfile:
# read header
header = csvfile.readline()
# split header into filed names
raw_field_names = header.split(',')
field_names = []
# clean field names
for field in raw_field_names:
field_names.append(FieldCleaner.clean_string(field))
unknown_fields = set(field_names) - set(cls.headers)
if len(unknown_fields) != 0:
raise KeyError("".join(["Found unexpected fields: ", str(list(unknown_fields))]))
missing_fields = set(cls.headers) - set(field_names)
if len(missing_fields) != 0:
raise ValueError("".join(["Missing required fields: ", str(list(missing_fields))]))
reader = csv.DictReader(csvfile, fieldnames=field_names)
for row in reader:
sql = cls.read_sql_str(row['query_name'])
rule_sql = RuleSql(rule_sql=sql, rule_label=row['rule_label'],
rule_error_message=row['rule_error_message'], query_name=row['query_name'])
# look up file type id
try:
file_id = FILE_TYPE_DICT[row["file_type"]]
except Exception as e:
raise Exception("{}: file type={}, rule label={}. Rule not loaded.".format(
e, row["file_type"], row["rule_label"]))
try:
if row["target_file"].strip() == "":
# No target file provided
target_file_id = None
else:
target_file_id = FILE_TYPE_DICT[row["target_file"]]
except Exception as e:
raise Exception("{}: file type={}, rule label={}. Rule not loaded.".format(
e, row["target_file"], row["rule_label"]))
# set cross file flag
flag = FieldCleaner.clean_string(row["rule_cross_file_flag"])
if flag in ('true', 't', 'y', 'yes'):
cross_file_flag = True
else:
cross_file_flag = False
rule_sql.rule_severity_id = RULE_SEVERITY_DICT[row['severity_name']]
rule_sql.file_id = file_id
rule_sql.target_file_id = target_file_id
rule_sql.rule_cross_file_flag = cross_file_flag
sess.merge(rule_sql)
sess.commit()