def sql_values_from_criteria(criteria, queue, table, gbl=False):
''' Given a criteria dictionary, for the indicated DB table
and queue, return a tuple composed of lists whose elements can be used
to construct SQLite clauses. If gbl is true, build a clause that
will affect all database records if criteria is missing - a global effect.
Args:
criteria - criteria dictionary
queue - database queue
table - database table
gbl - if True, global
Returns: a tuple for SQLite clauses respectively: WHERE, INTO, VALUES
'''
where = list() # for WHERE clause
intol = list() # for INTO clause
vals = list() # for VALUES clause
for crit in AIdb.getCriteria(queue, table, onlyUsed=False, strip=True):
# Determine if this crit is a range criteria or not.
is_range_crit = AIdb.isRangeCriteria(queue, crit, table)
# Get the value from the manifest
values = criteria[crit]
# the critera manifest didn't specify this criteria
if values is None:
# if the criteria we're processing is a range criteria, fill in
# NULL for two columns, MINcrit and MAXcrit
vals += ["NULL"]
if is_range_crit:
where += ["MIN" + crit + " IS NULL"]
where += ["MAX" + crit + " IS NULL"]
intol += ["MIN" + crit]
intol += ["MAX" + crit]
vals += ["NULL"]
# this is a single value
else:
where += [crit + " IS NULL"]
intol += [crit]
# This is a value criteria (not a range). 'values' is a list
# with one or more items.
elif not is_range_crit:
intol += [crit]
val = AIdb.format_value(crit, " ".join(values))
where += [crit + "=" + val]
vals += [val]
# Else this is a range criteria. 'values' is a two-item list
else:
# Set the MIN column for this range criteria
if values[0] == 'unbounded':
if not gbl:
where += ["MIN" + crit + " IS NULL"]
intol += ["MIN" + crit]
vals += ['NULL']
else:
intol += ["MIN" + crit]
if crit == 'mac':
val = AIdb.format_value(crit, verifyXML.checkMAC(
values[0])).upper()
where += ["HEX(MIN" + crit + ")<=HEX(" + val + ")"]
else:
val = AIdb.format_value(crit, values[0]).lower()
where += ["MIN" + crit + "<=" + val]
vals += [val]
# Set the MAX column for this range criteria
if values[1] == 'unbounded':
if not gbl:
where += ["MAX" + crit + " IS NULL"]
intol += ["MAX" + crit]
vals += ['NULL']
else:
intol += ["MAX" + crit]
if crit == 'mac':
val = AIdb.format_value(crit, verifyXML.checkMAC(
values[1])).upper()
where += ["HEX(MAX" + crit + ")>=HEX(" + val + ")"]
else:
val = AIdb.format_value(crit, values[1]).lower()
where += ["MAX" + crit + ">=" + val]
vals += [val]
return where, intol, vals
def test_checkMAC_1(self):
'''Ensure that MAC address is properly converted and padded'''
mac = verifyXML.checkMAC('00:11:2:33:04:FF')
self.assertEqual(mac, '0011023304ff')
def test_checkMAC_2(self):
'''Ensure that dash is accepted delimiter in MAC address'''
mac = verifyXML.checkMAC('00-11-2-33-04-FF')
self.assertEqual(mac, '0011023304ff')
def sql_values_from_criteria(criteria, queue, table, gbl=False):
''' Given a criteria dictionary, for the indicated DB table
and queue, return a tuple composed of lists whose elements can be used
to construct SQLite clauses. If gbl is true, build a clause that
will affect all database records if criteria is missing - a global effect.
Args:
criteria - criteria dictionary
queue - database queue
table - database table
gbl - if True, global
Returns: a tuple for SQLite clauses respectively: WHERE, INTO, VALUES
'''
where = list() # for WHERE clause
intol = list() # for INTO clause
vals = list() # for VALUES clause
for crit in AIdb.getCriteria(queue, table, onlyUsed=False, strip=True):
# Determine if this crit is a range criteria or not.
is_range_crit = AIdb.isRangeCriteria(queue, crit, table)
# Get the value from the manifest
values = criteria[crit]
# the critera manifest didn't specify this criteria
if values is None:
# if the criteria we're processing is a range criteria, fill in
# NULL for two columns, MINcrit and MAXcrit
vals += ["NULL"]
if is_range_crit:
where += ["MIN" + crit + " IS NULL"]
where += ["MAX" + crit + " IS NULL"]
intol += ["MIN" + crit]
intol += ["MAX" + crit]
vals += ["NULL"]
# this is a single value
else:
where += [crit + " IS NULL"]
intol += [crit]
# This is a value criteria (not a range). 'values' is a list
# with one or more items.
elif not is_range_crit:
intol += [crit]
val = AIdb.format_value(crit, " ".join(values))
where += [crit + "=" + val]
vals += [val]
# Else this is a range criteria. 'values' is a two-item list
else:
# Set the MIN column for this range criteria
if values[0] == 'unbounded':
if not gbl:
where += ["MIN" + crit + " IS NULL"]
intol += ["MIN" + crit]
vals += ['NULL']
else:
intol += ["MIN" + crit]
if crit == 'mac':
val = AIdb.format_value(crit,
verifyXML.checkMAC(values[0])).upper()
where += ["HEX(MIN" + crit + ")<=HEX(" + val + ")"]
else:
val = AIdb.format_value(crit, values[0]).lower()
where += ["MIN" + crit + "<=" + val]
vals += [val]
# Set the MAX column for this range criteria
if values[1] == 'unbounded':
if not gbl:
where += ["MAX" + crit + " IS NULL"]
intol += ["MAX" + crit]
vals += ['NULL']
else:
intol += ["MAX" + crit]
if crit == 'mac':
val = AIdb.format_value(crit,
verifyXML.checkMAC(values[1])).upper()
where += ["HEX(MAX" + crit + ")>=HEX(" + val + ")"]
else:
val = AIdb.format_value(crit, values[1]).lower()
where += ["MAX" + crit + ">=" + val]
vals += [val]
return where, intol, vals