def loadCrossRules(filename):
""" Populate multifield rule table with cross file validation rules """
validationDb = ValidatorValidationInterface()
with open(filename, 'rU') as ruleFile:
reader = csv.DictReader(ruleFile)
for record in reader:
fileId = validationDb.getFileId(record["file"])
validationDb.addMultiFieldRule(fileId,record["rule_type"],record["rule_text_one"],record["rule_text_two"],record["description"],record["rule_label"],record["rule_timing"])
def loadFields(filename):
"""
Load schema file to create validation rules and removes existing
schemas
Arguments:
filename -- filename of csv file that holds TAS data
"""
totalTASAdded = 0
totalExistingTAS = 0
#Step 1 Clean out the database
database = ValidatorValidationInterface()
database.deleteTAS()
lastRecord = {}
#Step 2 add the new data
with open(filename, 'rU') as csvfile:
#skip the first line of the csv as its just metadata
next(csvfile, None)
#second line contains headers
reader = csv.DictReader(csvfile)
#Loop over each row
for index, record in enumerate(reader):
#Let the user know that the script is still running.
if (index % 40000 == 0):
print("".join(["Loading ... ", str(index)]))
#Pad Record
record["ATA"] = record["ATA"].zfill(3)
record["AID"] = record["AID"].zfill(3)
record["BPOA"] = record["BPOA"].zfill(4)
record["EPOA"] = record["EPOA"].zfill(4)
record["A"] = record["A"].zfill(1)
record["MAIN"] = record["MAIN"].zfill(4)
record["SUB"] = record["SUB"].zfill(3)
#Check if record exists
if (not (LoaderUtils.compareRecords(record, lastRecord,
TASLoader.FILE_SCHEMA))):
if (LoaderUtils.checkRecord(record,
TASLoader.FILE_SCHEMA)):
if (database.addTAS(record["ATA"], record["AID"],
record["BPOA"], record["EPOA"],
record["A"], record["MAIN"],
record["SUB"])):
totalTASAdded += 1
else:
totalExistingTAS += 1
else:
raise ValueError('CSV File does not follow schema')
else:
totalExistingTAS += 1
lastRecord = record
#Step 3 Report Metrics for debuging
print("".join(["Total TAS added : ", str(totalTASAdded)]))
print("".join(["Duplicate TAS in file :", str(totalExistingTAS)]))
print("".join(
["Total TAS in file : ",
str(totalExistingTAS + totalTASAdded)]))
def loadRules(fileTypeName, filename):
""" Populate rule and multi_field_rule tables from rule rile
Args:
filename: File with rule specifications
fileTypeName: Which type of file to load rules for
"""
validationDb = ValidatorValidationInterface()
fileId = validationDb.getFileId(fileTypeName)
with open(filename, 'rU') as ruleFile:
reader = csv.DictReader(ruleFile)
for record in reader:
if(FieldCleaner.cleanString(record["is_single_field"]) == "true"):
# Find column ID based on field name
try:
columnId = validationDb.getColumnId(FieldCleaner.cleanName(record["field_name"]),fileTypeName)
except Exception as e:
print("Failed on field " + FieldCleaner.cleanName(record["field_name"]) + " and file " + fileTypeName)
raise e
# Write to rule table
if "rule_timing" in record and "rule_label" in record:
validationDb.addRule(columnId,record["rule_type"],record["rule_text_one"],record["description"],record["rule_timing"],record["rule_label"])
else:
validationDb.addRule(columnId,record["rule_type"],record["rule_text_one"],record["description"])
else:
# Write to multi_field_rule table
validationDb.addMultiFieldRule(fileId,record["rule_type"],record["rule_text_one"],record["rule_text_two"],record["description"])
def loadFields(filename):
"""
Load schema file to create validation rules and removes existing
schemas
Arguments:
filename -- filename of csv file that holds TAS data
"""
totalTASAdded = 0
totalExistingTAS = 0
#Step 1 Clean out the database
database = ValidatorValidationInterface()
database.deleteTAS()
lastRecord = {}
#Step 2 add the new data
with open(filename,'rU') as csvfile:
#skip the first line of the csv as its just metadata
next(csvfile, None)
#second line contains headers
reader = csv.DictReader(csvfile)
#Loop over each row
for index,record in enumerate(reader):
#Let the user know that the script is still running.
if(index % 40000 == 0) :
print("".join(["Loading ... ",str(index)]))
#Pad Record
record["ATA"] = record["ATA"].zfill(3)
record["AID"] = record["AID"].zfill(3)
record["BPOA"] = record["BPOA"].zfill(4)
record["EPOA"] = record["EPOA"].zfill(4)
record["A"] = record["A"].zfill(1)
record["MAIN"] = record["MAIN"].zfill(4)
record["SUB"] = record["SUB"].zfill(3)
#Check if record exists
if(not (LoaderUtils.compareRecords(record,lastRecord,TASLoader.FILE_SCHEMA))) :
if(LoaderUtils.checkRecord(record,TASLoader.FILE_SCHEMA)) :
if(database.addTAS(record["ATA"],
record["AID"],
record["BPOA"],
record["EPOA"],
record["A"],
record["MAIN"],
record["SUB"])) :
totalTASAdded += 1
else :
totalExistingTAS += 1
else :
raise ValueError('CSV File does not follow schema')
else :
totalExistingTAS += 1
lastRecord = record
#Step 3 Report Metrics for debuging
print("".join(["Total TAS added : ",str(totalTASAdded)]))
print("".join(["Duplicate TAS in file :",str(totalExistingTAS)]))
print("".join(["Total TAS in file : ",str(totalExistingTAS + totalTASAdded)]))
def loadCrossRules(filename):
""" Populate multifield rule table with cross file validation rules """
validationDb = ValidatorValidationInterface()
with open(filename, 'rU') as ruleFile:
reader = csv.DictReader(ruleFile)
for record in reader:
fileId = validationDb.getFileId(record["file"])
validationDb.addMultiFieldRule(fileId, record["rule_type"],
record["rule_text_one"],
record["rule_text_two"],
record["description"],
record["rule_label"],
record["rule_timing"])
def loadFields(fileTypeName,schemaFileName):
"""
Load schema file to create validation rules and removes existing
schemas
Arguments:
schemaFileName -- filename of csv file that holds schema definition
fileTypeName -- the type of file that the schema represents
"""
#Step 1 Clean out the database
database = ValidatorValidationInterface()
database.removeRulesByFileType(fileTypeName)
database.removeColumnsByFileType(fileTypeName)
#Step 2 add the new fields
with open(schemaFileName, 'rU') as csvfile:
reader = csv.DictReader(csvfile)
for record in reader:
record = FieldCleaner.cleanRecord(record)
if(LoaderUtils.checkRecord(record, ["fieldname","required","data_type"])) :
columnId = database.addColumnByFileType(fileTypeName,FieldCleaner.cleanString(record["fieldname"]),record["required"],record["data_type"])
if "field_length" in record:
# When a field length is specified, create a rule for it
length = record["field_length"].strip()
if(len(length) > 0):
# If there are non-whitespace characters here, create a length rule
database.addRule(columnId,"LENGTH",length,"Field must be no longer than specified limit")
else :
raise ValueError('CSV File does not follow schema')
def loadTas(tasFile=None, dropIdx=True):
""" Load all valid TAS combinations into database and index the TASLookup table """
validatorDb = ValidatorValidationInterface()
connection = validatorDb.engine.connect()
# drop indexes
table = TASLookup.__table__
indexes = table.indexes
if dropIdx:
for i in indexes:
try:
i.drop(bind=connection)
except:
pass
# load TAS
if tasFile:
filename = tasFile
else:
filename = "../config/all_tas_betc.csv"
try:
TASLoader.loadFields(filename)
except IOError:
print("Can't open file: {}".format(filename))
raise
# re-create indexes
if dropIdx:
for i in indexes:
i.create(bind=connection)
def loadCgac(filename):
LoaderUtils.loadCsv(filename, CGAC, ValidatorValidationInterface(), {
"cgac": "cgac_code",
"agency": "agency_name"
}, {"cgac_code": {
"pad_to_length": 3,
"skip_duplicates": True
}})
def loadObjectClass(filename):
LoaderUtils.loadCsv(
filename, ObjectClass, ValidatorValidationInterface(), {
"max_oc_code": "object_class_code",
"max_object_class_name": "object_class_name"
}, {"object_class_code": {
"skip_duplicates": True
}})
def loadRules(fileTypeName, filename):
""" Populate rule and multi_field_rule tables from rule rile
Args:
filename: File with rule specifications
fileTypeName: Which type of file to load rules for
"""
validationDb = ValidatorValidationInterface()
fileId = validationDb.getFileId(fileTypeName)
with open(filename, 'rU') as ruleFile:
reader = csv.DictReader(ruleFile)
for record in reader:
if (FieldCleaner.cleanString(
record["is_single_field"]) == "true"):
# Find column ID based on field name
try:
columnId = validationDb.getColumnId(
FieldCleaner.cleanName(record["field_name"]),
fileTypeName)
except Exception as e:
print("Failed on field " +
FieldCleaner.cleanName(record["field_name"]) +
" and file " + fileTypeName)
raise e
# Write to rule table
if "rule_timing" in record and "rule_label" in record:
validationDb.addRule(columnId, record["rule_type"],
record["rule_text_one"],
record["description"],
record["rule_timing"],
record["rule_label"])
else:
validationDb.addRule(columnId, record["rule_type"],
record["rule_text_one"],
record["description"])
else:
# Write to multi_field_rule table
validationDb.addMultiFieldRule(fileId, record["rule_type"],
record["rule_text_one"],
record["rule_text_two"],
record["description"])
def loadRules(fileTypeName, filename):
""" Populate rule table from rule rile
Args:
filename: File with rule specifications
fileTypeName: Which type of file to load rules for
"""
validationDb = ValidatorValidationInterface()
fileId = validationDb.getFileId(fileTypeName)
with open(filename, 'rU') as ruleFile:
reader = csv.DictReader(ruleFile)
for record in reader:
if (FieldCleaner.cleanString(
record["is_single_field"]) == "true"):
# Find column ID based on field name
try:
columnId = validationDb.getColumnId(
FieldCleaner.cleanName(record["field_name"]),
fileTypeName)
except Exception as e:
raise Exception("".join([
str(e), "Failed on field ",
FieldCleaner.cleanName(record["field_name"]),
" and file ", fileTypeName
]))
else:
# Multi field rules don't get a file_column
columnId = None
# Look up rule timing id
try:
ruleTimingId = validationDb.getRuleTimingIdByName(
FieldCleaner.cleanName(record["rule_timing"]))
except Exception as e:
raise Exception("".join([
str(e), " Rule load failed on timing value ",
FieldCleaner.cleanName(record["rule_timing"]),
" and file ", fileTypeName
]))
# Target file info is applicable to cross-file rules only
targetFileId = None
# Write to rule table
try:
validationDb.addRule(columnId,
str(record["rule_type"]),
str(record["rule_text_one"]),
str(record["rule_text_two"]),
str(record["description"]),
ruleTimingId,
str(record["rule_label"]),
targetFileId=targetFileId,
fileId=fileId)
except Exception as e:
raise Exception(
'{}: rule insert failed (file={}, rule={}'.format(
e, fileTypeName, record["description"]))
def loadCrossRules(filename):
""" Populate rule table with cross file validation rules """
validationDb = ValidatorValidationInterface()
with open(filename, 'rU') as ruleFile:
reader = csv.DictReader(ruleFile)
for record in reader:
fileId = validationDb.getFileId(record["file"])
if record["target_file"]:
targetFileId = validationDb.getFileId(record["target_file"])
else:
targetFileId = None
# Look up rule timing id
try:
ruleTimingId = validationDb.getRuleTimingIdByName(
FieldCleaner.cleanName(record["rule_timing"]))
except Exception as e:
raise Exception("".join(
[str(e), "Cross-file rule load failed on timing value ", FieldCleaner.cleanName(record["rule_timing"]),
" and file ",
fileTypeName]))
try:
validationDb.addRule(
None, record["rule_type"], record["rule_text_one"],
record["rule_text_two"], record["description"], ruleTimingId,
record["rule_label"], targetFileId, fileId = fileId)
except Exception as e:
raise Exception('{}: cross-file rule insert failed (rule={}'.format(
e, record["description"]))
def loadFields(fileTypeName, schemaFileName):
"""
Load schema file to create validation rules and removes existing
schemas
Arguments:
schemaFileName -- filename of csv file that holds schema definition
fileTypeName -- the type of file that the schema represents
"""
#Step 1 Clean out the database
database = ValidatorValidationInterface()
database.removeRulesByFileType(fileTypeName)
database.removeColumnsByFileType(fileTypeName)
#Step 2 add the new fields
with open(schemaFileName, 'rU') as csvfile:
reader = csv.DictReader(csvfile)
for record in reader:
record = FieldCleaner.cleanRecord(record)
if (LoaderUtils.checkRecord(
record, ["fieldname", "required", "data_type"])):
columnId = database.addColumnByFileType(
fileTypeName,
FieldCleaner.cleanString(record["fieldname"]),
record["required"], record["data_type"])
if "field_length" in record:
# When a field length is specified, create a rule for it
length = record["field_length"].strip()
if (len(length) > 0):
# If there are non-whitespace characters here, create a length rule
database.addRule(
columnId, "LENGTH", length, "",
"Field must be no longer than specified limit")
else:
raise ValueError('CSV File does not follow schema')
def loadProgramActivity(filename):
LoaderUtils.loadCsv(
filename, ProgramActivity, ValidatorValidationInterface(), {
"year": "budget_year",
"agency_id": "agency_id",
"alloc_id": "allocation_transfer_id",
"account": "account_number",
"pa_code": "program_activity_code",
"pa_name": "program_activity_name"
}, {
"program_activity_code": {
"pad_to_length": 4
},
"agency_id": {
"pad_to_length": 3
},
"account_number": {
"pad_to_length": 4
},
"allocation_transfer_id": {
"pad_to_length": 3
}
})
def loadRules(fileTypeName, filename):
""" Populate rule table from rule rile
Args:
filename: File with rule specifications
fileTypeName: Which type of file to load rules for
"""
validationDb = ValidatorValidationInterface()
fileId = validationDb.getFileId(fileTypeName)
with open(filename, 'rU') as ruleFile:
reader = csv.DictReader(ruleFile)
for record in reader:
if(FieldCleaner.cleanString(record["is_single_field"]) == "true"):
# Find column ID based on field name
try:
columnId = validationDb.getColumnId(FieldCleaner.cleanName(record["field_name"]),fileTypeName)
except Exception as e:
raise Exception("".join([str(e),"Failed on field ",FieldCleaner.cleanName(record["field_name"])," and file ",fileTypeName]))
else:
# Multi field rules don't get a file_column
columnId = None
# Look up rule timing id
try:
ruleTimingId = validationDb.getRuleTimingIdByName(
FieldCleaner.cleanName(record["rule_timing"]))
except Exception as e:
raise Exception("".join(
[str(e), " Rule load failed on timing value ", FieldCleaner.cleanName(record["rule_timing"]), " and file ",
fileTypeName]))
# Target file info is applicable to cross-file rules only
targetFileId = None
# Write to rule table
try:
validationDb.addRule(columnId,
str(record["rule_type"]), str(record["rule_text_one"]),
str(record["rule_text_two"]), str(record["description"]),
ruleTimingId, str(record["rule_label"]),
targetFileId=targetFileId, fileId=fileId)
except Exception as e:
raise Exception('{}: rule insert failed (file={}, rule={}'.format(
e, fileTypeName, record["description"]))
def loadCrossRules(filename):
""" Populate rule table with cross file validation rules """
validationDb = ValidatorValidationInterface()
with open(filename, 'rU') as ruleFile:
reader = csv.DictReader(ruleFile)
for record in reader:
fileId = validationDb.getFileId(record["file"])
if record["target_file"]:
targetFileId = validationDb.getFileId(
record["target_file"])
else:
targetFileId = None
# Look up rule timing id
try:
ruleTimingId = validationDb.getRuleTimingIdByName(
FieldCleaner.cleanName(record["rule_timing"]))
except Exception as e:
raise Exception("".join([
str(e), "Cross-file rule load failed on timing value ",
FieldCleaner.cleanName(record["rule_timing"]),
" and file ", fileTypeName
]))
try:
validationDb.addRule(None,
record["rule_type"],
record["rule_text_one"],
record["rule_text_two"],
record["description"],
ruleTimingId,
record["rule_label"],
targetFileId,
fileId=fileId)
except Exception as e:
raise Exception(
'{}: cross-file rule insert failed (rule={}'.format(
e, record["description"]))
def test_schema_rules(self):
"""Test schema rules."""
lessRule = RuleType()
lessRule.name = "LESS"
greaterRule = RuleType()
greaterRule.name = "GREATER"
lengthRule = RuleType()
lengthRule.name = "LENGTH"
equalRule = RuleType()
equalRule.name = "EQUAL"
notRule = RuleType()
notRule.name = "NOT EQUAL"
setRule = RuleType()
setRule.name = "IN_SET"
sumRule = RuleType()
sumRule.name = "SUM"
sumToValueRule = MultiFieldRuleType()
sumToValueRule.name = "SUM_TO_VALUE"
schema = self.schema
interfaces = self.interfaces
rule1 = Rule()
rule1.rule_type = equalRule
rule1.file_column = schema["test1"]
rule1.rule_text_1 = "hello"
rule1.rule_timing_id = 1
rule2 = Rule()
rule2.rule_type = notRule
rule2.file_column = schema["test1"]
rule2.rule_text_1 = "bye"
rule2.rule_timing_id = 1
rule3 = Rule()
rule3.rule_type = lengthRule
rule3.file_column = schema["test1"]
rule3.rule_text_1 = "6"
rule3.rule_timing_id = 1
rule4 = Rule()
rule4.rule_type = equalRule
rule4.file_column = schema["test3"]
rule4.rule_text_1 = "YES"
rule4.rule_timing_id = 1
rule5 = Rule()
rule5.rule_type = equalRule
rule5.file_column = schema["test4"]
rule5.rule_text_1 = "44"
rule5.rule_timing_id = 1
rule6 = Rule()
rule6.rule_type = lessRule
rule6.file_column = schema["test4"]
rule6.rule_text_1 = "45"
rule6.rule_timing_id = 1
rule7 = Rule()
rule7.rule_type = greaterRule
rule7.file_column = schema["test2"]
rule7.rule_text_1 = ".5"
rule7.rule_timing_id = 1
rule8 = Rule()
rule8.rule_type = setRule
rule8.file_column = schema["test6"]
rule8.rule_text_1 = "X, F, A"
rule8.rule_timing_id = 1
rule9 = Rule()
rule9.rule_type = sumRule
rule9.file_column = schema["test2"]
rule9.rule_text_1 = "test7"
rule9.rule_text_2 = "test2,test4,test5"
rule9.rule_timing_id = 1
rule10 = MultiFieldRule()
rule10.rule_type = sumToValueRule
rule10.rule_text_1 = "46"
rule10.rule_text_2 = "test2,test4,test5"
rule10.rule_timing_id = 1
vvi = ValidatorValidationInterface()
fileId = vvi.getFileId("award")
vvi.addMultiFieldRule(fileId, "SUM_TO_VALUE", rule10.rule_text_1, rule10.rule_text_2, "Evaluates the sum of fields to a number")
rules = [rule1, rule2, rule3, rule4, rule5, rule6, rule7, rule8, rule9]
record = {
"test1": "hello",
"test2": "1.0",
"test3": "YES",
"test4": "44",
"test5": "1",
"test6": "X",
"test7": "46"
}
self.assertTrue(Validator.validate(
record, rules, schema, "award", self.interfaces)[0])
record = {
"test1": "goodbye",
"test2": ".4",
"test3": "NO",
"test4": "45",
"test5": "1",
"test6": "Q",
"test7": "46.5"
}
self.assertFalse(Validator.validate(
record, [rule3], schema, "award", interfaces)[0])
self.assertFalse(Validator.validate(
record, [rule4], schema, "award", interfaces)[0])
self.assertFalse(Validator.validate(
record, [rule5], schema, "award", interfaces)[0])
self.assertFalse(Validator.validate(
record, [rule6], schema, "award", interfaces)[0])
self.assertFalse(Validator.validate(
record, [rule7], schema, "award", interfaces)[0])
self.assertFalse(Validator.validate(
record, [rule8], schema, "award", interfaces)[0])
self.assertFalse(Validator.validate(
record, [rule9], schema, "award", interfaces)[0])
self.assertFalse(Validator.validate(
record, rules, schema, "award", interfaces)[0])
def insertCodes():
"""Insert static data."""
validatorDb = ValidatorValidationInterface()
# insert rule timing
ruleTimingList = [
(1, 'file_validation', 'Run during pre-load validation of a file'),
(2, 'prerequisite', 'Run only when referenced by another rule'),
(3, 'cross_file', 'This rule is checked during cross-file validation'),
(4, 'multi_field',
'This rule is run after field level validations are complete')
]
for r in ruleTimingList:
ruleTiming = RuleTiming(rule_timing_id=r[0],
name=r[1],
description=r[2])
validatorDb.session.merge(ruleTiming)
# insert file types
fileTypeList = [(1, 'award', 'award file'),
(2, 'award_financial', 'award_financial file'),
(3, 'appropriations', 'appropriations file'),
(4, 'program_activity',
'program activity and object class file')]
for f in fileTypeList:
fileType = FileType(file_id=f[0], name=f[1], description=f[2])
validatorDb.session.merge(fileType)
# insert rule types
ruleTypeList = [
(1, 'TYPE', 'checks type'), (2, 'EQUAL', 'equals operatior '),
(3, 'NOT_EQUAL', 'not equals operator '),
(4, 'LESS', 'less than operator '),
(5, 'GREATER', 'greater than operator'),
(6, 'LENGTH', 'string length'), (7, 'IN_SET', 'value must be in set'),
(8, 'MIN_LENGTH', 'length of data must be at least reference value'),
(9, 'REQUIRED_CONDITIONAL',
'field is required if secondary rule passes'),
(10, 'SUM', 'field is equal to the sum of other fields'),
(11, 'CAR_MATCH', 'Matching a set of fields against a CAR file'),
(12, 'FIELD_MATCH', 'Match a set of fields against a different file'),
(13, 'RULE_IF', 'Apply first rule if second rule passes'),
(14, 'SUM_TO_VALUE',
'Sum a set of fields and compare to specified value'),
(15, 'REQUIRE_ONE_OF_SET',
'At least one of these fields must be present'),
(16, 'SUM_FIELDS', 'Field is equal to the sum of other fields'),
(17, 'NOT', 'passes if and only if specified rule fails'),
(18, 'SUM_BY_TAS', 'Check if two fields summed by TAS are equal'),
(19, 'EXISTS_IN_TABLE', 'Check that value exists in specified table'),
(20, 'REQUIRED_SET_CONDITIONAL',
'Check that all fields in set are present if conditional rule passes'
),
(21, 'CHECK_PREFIX',
'Check first character against a value in another field'),
(22, 'SET_EXISTS_IN_TABLE',
'Check whether set of values exists in specified table')
]
for r in ruleTypeList:
ruleType = RuleType(rule_type_id=r[0], name=r[1], description=r[2])
validatorDb.session.merge(ruleType)
# insert field types
fieldTypeList = [(1, 'INT', 'integer type'),
(2, 'DECIMAL', 'decimal type '), (3, 'BOOLEAN', 'yes/no'),
(4, 'STRING', 'string type'), (5, 'LONG', 'long integer')]
for f in fieldTypeList:
fieldType = FieldType(field_type_id=f[0], name=f[1], description=f[2])
validatorDb.session.merge(fieldType)
validatorDb.session.commit()
validatorDb.session.close()
def test_schema_rules(self):
"""Test schema rules."""
lessRule = RuleType()
lessRule.name = "LESS"
greaterRule = RuleType()
greaterRule.name = "GREATER"
lengthRule = RuleType()
lengthRule.name = "LENGTH"
equalRule = RuleType()
equalRule.name = "EQUAL"
notRule = RuleType()
notRule.name = "NOT EQUAL"
setRule = RuleType()
setRule.name = "IN_SET"
sumRule = RuleType()
sumRule.name = "SUM"
sumToValueRule = RuleType()
sumToValueRule.name = "SUM_TO_VALUE"
schema = self.schema
interfaces = self.interfaces
rule1 = Rule()
rule1.rule_type = equalRule
rule1.file_column = schema["test1"]
rule1.rule_text_1 = "hello"
rule1.rule_timing_id = 1
rule2 = Rule()
rule2.rule_type = notRule
rule2.file_column = schema["test1"]
rule2.rule_text_1 = "bye"
rule2.rule_timing_id = 1
rule3 = Rule()
rule3.rule_type = lengthRule
rule3.file_column = schema["test1"]
rule3.rule_text_1 = "6"
rule3.rule_timing_id = 1
rule4 = Rule()
rule4.rule_type = equalRule
rule4.file_column = schema["test3"]
rule4.rule_text_1 = "YES"
rule4.rule_timing_id = 1
rule5 = Rule()
rule5.rule_type = equalRule
rule5.file_column = schema["test4"]
rule5.rule_text_1 = "44"
rule5.rule_timing_id = 1
rule6 = Rule()
rule6.rule_type = lessRule
rule6.file_column = schema["test4"]
rule6.rule_text_1 = "45"
rule6.rule_timing_id = 1
rule7 = Rule()
rule7.rule_type = greaterRule
rule7.file_column = schema["test2"]
rule7.rule_text_1 = ".5"
rule7.rule_timing_id = 1
rule8 = Rule()
rule8.rule_type = setRule
rule8.file_column = schema["test6"]
rule8.rule_text_1 = "X, F, A"
rule8.rule_timing_id = 1
rule9 = Rule()
rule9.rule_type = sumRule
rule9.file_column = schema["test2"]
rule9.rule_text_1 = "test7"
rule9.rule_text_2 = "test2,test4,test5"
rule9.rule_timing_id = 1
rule10 = Rule()
rule10.rule_type = sumToValueRule
rule10.rule_text_1 = "46"
rule10.rule_text_2 = "test2,test4,test5"
rule10.rule_timing_id = 4
vvi = ValidatorValidationInterface()
fileId = vvi.getFileId("award")
vvi.addRule(None, "SUM_TO_VALUE", rule10.rule_text_1, rule10.rule_text_2, "Evaluates the sum of fields to a number",rule10.rule_timing_id,fileId = fileId)
rules = [rule1, rule2, rule3, rule4, rule5, rule6, rule7, rule8, rule9]
record = {
"test1": "hello",
"test2": "1.0",
"test3": "YES",
"test4": "44",
"test5": "1",
"test6": "X",
"test7": "46"
}
self.assertTrue(Validator.validate(
record, rules, schema, "award", self.interfaces)[0])
record = {
"test1": "goodbye",
"test2": ".4",
"test3": "NO",
"test4": "45",
"test5": "1",
"test6": "Q",
"test7": "46.5"
}
self.assertFalse(Validator.validate(
record, [rule3], schema, "award", interfaces)[0])
self.assertFalse(Validator.validate(
record, [rule4], schema, "award", interfaces)[0])
self.assertFalse(Validator.validate(
record, [rule5], schema, "award", interfaces)[0])
self.assertFalse(Validator.validate(
record, [rule6], schema, "award", interfaces)[0])
self.assertFalse(Validator.validate(
record, [rule7], schema, "award", interfaces)[0])
self.assertFalse(Validator.validate(
record, [rule8], schema, "award", interfaces)[0])
self.assertFalse(Validator.validate(
record, [rule9], schema, "award", interfaces)[0])
self.assertFalse(Validator.validate(
record, rules, schema, "award", interfaces)[0])
def __init__(self):
""" Create the interfaces """
self.jobDb = ValidatorJobTrackerInterface()
self.errorDb = ValidatorErrorInterface()
self.stagingDb = ValidatorStagingInterface()
self.validationDb = ValidatorValidationInterface()
def setupValidationDB(hardReset=False):
"""Create validation tables from model metadata and do initial inserts."""
createDatabase(CONFIG_DB['validator_db_name'])
validatorDb = ValidatorValidationInterface()
# TODO: use Alembic for initial db setup
if hardReset:
validationModels.Base.metadata.drop_all(validatorDb.engine)
validationModels.Base.metadata.create_all(validatorDb.engine)
validatorDb.session.commit()
validatorDb.session.close()
# insert rule timing
ruleTimingList = [
(1, 'file_validation', 'Run during pre-load validation of a file'),
(2, 'prerequisite', 'Run only when referenced by another rule'),
(3, 'cross-file', 'This rule is checked during cross-file validation')
]
for r in ruleTimingList:
ruleTiming = RuleTiming(rule_timing_id=r[0],
name=r[1],
description=r[2])
validatorDb.session.merge(ruleTiming)
# insert file types
fileTypeList = [(1, 'award', 'award file'),
(2, 'award_financial', 'award_financial file'),
(3, 'appropriations', 'appropriations file'),
(4, 'program_activity',
'program activity and object class file')]
for f in fileTypeList:
fileType = FileType(file_id=f[0], name=f[1], description=f[2])
validatorDb.session.merge(fileType)
# insert rule types
ruleTypeList = [(1, 'TYPE', 'checks type'),
(2, 'EQUAL', 'equals operatior '),
(3, 'NOT EQUAL', 'not equals operator '),
(4, 'LESS', 'less than operator '),
(5, 'GREATER', 'greater than operator'),
(6, 'LENGTH', 'string length'),
(7, 'IN_SET', 'value must be in set'),
(8, 'MIN LENGTH',
'length of data must be at least reference value'),
(9, 'REQUIRED_CONDITIONAL',
'field is required if secondary rule passes'),
(10, 'SUM', 'field is equal to the sum of other fields')]
for r in ruleTypeList:
ruleType = RuleType(rule_type_id=r[0], name=r[1], description=r[2])
validatorDb.session.merge(ruleType)
# insert field types
fieldTypeList = [(1, 'INT', 'integer type'),
(2, 'DECIMAL', 'decimal type '), (3, 'BOOLEAN', 'yes/no'),
(4, 'STRING', 'string type'), (5, 'LONG', 'long integer')]
for f in fieldTypeList:
fieldType = FieldType(field_type_id=f[0], name=f[1], description=f[2])
validatorDb.session.merge(fieldType)
# insert multi-field rule types
mfrTypeList = [
(1, 'CAR_MATCH', 'Matching a set of fields against a CAR file'),
(2, 'FIELD_MATCH', 'Match a set of fields against a different file'),
(3, 'RULE_IF', 'Apply first rule if second rule passes'),
(4, 'GREATER', 'Check if field is greater than specified value'),
(5, 'SUM_TO_VALUE',
'Sum a set of fields and compare to specified value')
]
for m in mfrTypeList:
mfrt = MultiFieldRuleType(multi_field_rule_type_id=m[0],
name=m[1],
description=m[2])
validatorDb.session.merge(mfrt)
validatorDb.session.commit()
validatorDb.session.close()