def normalize_headers(header_row, long_headers, long_to_short_dict):
""" Clean the headers (remove extra spaces and lowercase) and convert them to short headers if we're given long
headers
Args:
header_row: an array of the file headers given
long_headers: boolean indicating if we're using the long versions of the headers (True for long)
long_to_short_dict: a dictionary containing a mapping from long headers to short ones for this file type
Yields:
A string containing the cleaned header name (converted to short version if long versions were provided and
there is a mapping for that header).
"""
for header in header_row:
header = FieldCleaner.clean_string(header)
# Replace headers that don't match DB but are allowed by the broker with their DB matches
if header == 'deobligationsrecoveriesrefundsofprioryearbyprogramobjectclass_cpe':
header = 'deobligationsrecoveriesrefundsdofprioryearbyprogramobjectclass_cpe'
elif header == 'facevalueloanguarantee':
header = 'facevalueofdirectloanorloanguarantee'
elif header == 'budgetauthorityavailableamounttotal_cpe':
header = 'totalbudgetaryresources_cpe'
elif header == 'correctionlatedeleteindicator':
header = 'correctiondeleteindicator'
elif header == 'place_of_performance_zip4':
header = 'place_of_performance_zip4a'
# yield the short header when applicable, otherwise yield the cleaned header, whatever it is
if long_headers and header in long_to_short_dict:
yield FieldCleaner.clean_string(long_to_short_dict[header])
else:
yield header
def normalize_headers(header_row, long_headers, long_to_short_dict):
for header in header_row:
header = FieldCleaner.clean_string(header)
# Replace correctly spelled header (which does NOT match the db) with the misspelling that DOES match the db
if header == 'deobligationsrecoveriesrefundsofprioryearbyprogramobjectclass_cpe':
header = 'deobligationsrecoveriesrefundsdofprioryearbyprogramobjectclass_cpe'
if long_headers and header in long_to_short_dict:
yield FieldCleaner.clean_string(long_to_short_dict[header])
else:
yield header
def normalize_headers(header_row, long_headers, long_to_short_dict):
for header in header_row:
header = FieldCleaner.clean_string(header)
# Replace correctly spelled header (which does NOT match the db) with the misspelling that DOES match the db
if header == 'deobligationsrecoveriesrefundsofprioryearbyprogramobjectclass_cpe':
header = 'deobligationsrecoveriesrefundsdofprioryearbyprogramobjectclass_cpe'
if header == 'facevalueloanguarantee':
header = 'facevalueofdirectloanorloanguarantee'
if long_headers and header in long_to_short_dict:
yield FieldCleaner.clean_string(long_to_short_dict[header])
else:
yield header
def load_fields(file_type_name, schema_file_name):
"""Load specified schema from a .csv."""
with create_app().app_context():
sess = GlobalDB.db().session
# get file type object for specified fileTypeName
file_type = sess.query(FileType).filter(
FileType.name == file_type_name).one()
# delete existing schema from database
SchemaLoader.remove_columns_by_file_type(sess, file_type)
# get allowable datatypes
type_query = sess.query(FieldType.name,
FieldType.field_type_id).all()
types = {
data_type.name: data_type.field_type_id
for data_type in type_query
}
# add schema to database
with open(schema_file_name, 'rU') as csvfile:
reader = csv.DictReader(csvfile)
file_column_count = 0
for record in reader:
record = FieldCleaner.clean_record(record)
fields = ["fieldname", "required", "data_type"]
if all(field in record for field in fields):
SchemaLoader.add_column_by_file_type(
sess, types, file_type,
FieldCleaner.clean_string(record["fieldname"]),
FieldCleaner.clean_string(
record["fieldname_short"]), record["required"],
record["data_type"], record["padded_flag"],
record["field_length"])
file_column_count += 1
else:
raise ValueError('CSV File does not follow schema')
sess.commit()
logger.info({
'message':
'{} {} schema records added to {}'.format(
file_column_count, file_type_name,
FileColumn.__tablename__),
'message_type':
'ValidatorInfo',
'file_type':
file_type.letter_name
})
def count_and_set_headers(self, csv_schema, header_row):
"""Track how many times we've seen a field we were expecting and set self.expected_headers and
self.flex_headers"""
self.expected_headers = []
self.flex_headers = []
# Track how many times we've seen a field we were expecting. Keyed by the shorter, machine-readable column names
expected_fields = {}
for schema in csv_schema:
expected_fields[FieldCleaner.clean_string(schema.name_short)] = 0
for header_value in header_row:
if header_value not in expected_fields:
# Add flex headers to flex list
if str(header_value).startswith("flex_"):
self.flex_headers.append(header_value)
else:
self.flex_headers.append(None)
# Allow unexpected headers, just mark the header as None so we skip it when reading
self.expected_headers.append(None)
else:
self.flex_headers.append(None)
self.expected_headers.append(header_value)
expected_fields[header_value] += 1
return expected_fields
def count_and_set_headers(self, csv_schema, header_row):
"""Track how many times we've seen a field we were expecting and set self.expected_headers and
self.flex_headers"""
self.expected_headers = []
self.flex_headers = []
# Track how many times we've seen a field we were expecting. Keyed by the shorter, machine-readable column names
expected_fields = {}
for schema in csv_schema:
expected_fields[FieldCleaner.clean_string(schema.name_short)] = 0
for header_value in header_row:
if header_value not in expected_fields:
# Add flex headers to flex list
if str(header_value).startswith("flex_"):
self.flex_headers.append(header_value)
else:
self.flex_headers.append(None)
# Allow unexpected headers, just mark the header as None so we skip it when reading
self.expected_headers.append(None)
else:
self.flex_headers.append(None)
self.expected_headers.append(header_value)
expected_fields[header_value] += 1
return expected_fields
def use_long_headers(header_row, long_to_short_dict):
"""Check to see if header contains long or short column names"""
col_matches = 0
for value in header_row:
if FieldCleaner.clean_string(value) in long_to_short_dict:
col_matches += 1
# if most of column headers are in the long format, we'll treat the file as having long headers
return col_matches > .5 * len(header_row)
def use_long_headers(header_row, long_to_short_dict):
"""Check to see if header contains long or short column names"""
col_matches = 0
for value in header_row:
if FieldCleaner.clean_string(value) in long_to_short_dict:
col_matches += 1
# if most of column headers are in the long format, we'll treat the file as having long headers
return col_matches > .5 * len(header_row)
def load_fields(file_type_name, schema_file_name):
"""Load specified schema from a .csv."""
with create_app().app_context():
sess = GlobalDB.db().session
# get file type object for specified fileTypeName
file_type = sess.query(FileType).filter(FileType.name == file_type_name).one()
# delete existing schema from database
SchemaLoader.remove_columns_by_file_type(sess, file_type)
# get allowable datatypes
type_query = sess.query(FieldType.name, FieldType.field_type_id).all()
types = {data_type.name: data_type.field_type_id for data_type in type_query}
# add schema to database
with open(schema_file_name, 'rU') as csvfile:
reader = csv.DictReader(csvfile)
file_column_count = 0
for record in reader:
record = FieldCleaner.clean_record(record)
fields = ["fieldname", "required", "data_type"]
if all(field in record for field in fields):
SchemaLoader.add_column_by_file_type(
sess,
types,
file_type,
FieldCleaner.clean_string(record["fieldname"]),
FieldCleaner.clean_string(record["fieldname_short"]),
record["required"],
record["data_type"],
record["padded_flag"],
record["field_length"])
file_column_count += 1
else:
raise ValueError('CSV File does not follow schema')
sess.commit()
logger.info({
'message': '{} {} schema records added to {}'.format(file_column_count, file_type_name,
FileColumn.__tablename__),
'message_type': 'ValidatorInfo',
'file_type': file_type.letter_name
})
def load_labels(cls, filename):
"""Load non-SQL-based validation rules to db."""
with create_app().app_context():
sess = GlobalDB.db().session
# Delete all records currently in table
sess.query(ValidationLabel).delete()
filename = os.path.join(cls.validation_labels_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:
validation_label = ValidationLabel(
label=row['label'],
error_message=row['error_message'],
column_name=row['column_name'],
label_type=row['label_type'])
# 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"]))
validation_label.file_id = file_id
sess.merge(validation_label)
sess.commit()
def load_labels(cls, filename):
"""Load non-SQL-based validation rules to db."""
with create_app().app_context():
sess = GlobalDB.db().session
# Delete all records currently in table
sess.query(ValidationLabel).delete()
filename = os.path.join(cls.validation_labels_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:
validation_label = ValidationLabel(label=row['label'], error_message=row['error_message'],
column_name=row['column_name'], label_type=row['label_type'])
# 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"]))
validation_label.file_id = file_id
sess.merge(validation_label)
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()
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()
