def check_combinations_in_service_group_dialled_number_permissions(): """ Checks combinations of service-level with dialled number classifications. Checks that there is an unduplicated combination with from one service level to full set of dialled number classifications. Prints 'ok' if all combinations are defined; otherwise prints omission and duplication values. """ print "Checking group to number classification combinations in service_group_dialled_number_permissions" conn = pdatab.connect_permissions_db() ok = 0 #checking for missing origination groups ids oids = pdatab.query_permissions_db(conn, "SELECT DISTINCT orig_group FROM service_group_dialled_number_permissions") oids = [o[0] for o in oids] if show_omissions(oids, servicegroupids, 'orig_group'): ok = ok + 1 #checking each origination group id has a full complement of telephone number classifications for oid in oids: res = pdatab.query_permissions_db(conn, \ "SELECT dialled_number_classification FROM service_group_dialled_number_permissions WHERE orig_group = '%d'" % oid) l = [r[0] for r in res] if show_duplicates(l,oid) and show_omissions(l,numberclassids,oid): ok = ok + 1 pdatab.disconnect_permissions_db(conn) if ok == (len(servicegroupids) + 1): return True else: return False
def show_dialled_number_classification_combo(from_level, classification, verbose=False):
"""
Display permission for telno classification.
from_level - originating service level (or '%' wildcard)
classification - telno classification (e.g. 31 for LG1, 5 for mobile, 1 for Fixnet, etc)
verbose - when true, full permission stanza will be displayed
"""
conn = pdatab.connect_permissions_db()
levels = pdatab.query_permissions_db(conn, \
"SELECT service_level,service_group_id FROM ucid_service_level_groups WHERE service_level LIKE '%s' ORDER BY service_level" % from_level)
for level in levels:
permid = pdatab.query_permissions_db(conn, \
"SELECT permissions_id FROM service_group_dialled_number_permissions WHERE orig_group = '%d' \
AND dialled_number_classification = '%d'" % (level[1], classification))
permission = pdatab.query_permissions_db(conn, \
"SELECT permissions_xml_stanza FROM permissions WHERE permissions_id = '%s'" % (permid[0]))
print_white(level[0])
__print_permissions(permission[0][0])
if verbose:
print_green(permission[0][0])
pdatab.disconnect_permissions_db(conn)
def check_permissions():
print_white("checking permissions consistency")
print_white(separator)
print
perms_old = pdatab.query_permissions_db(conn_old, 'SELECT * FROM permissions ORDER BY permissions_id')
perms_new = pdatab.query_permissions_db(conn_new, 'SELECT * FROM permissions ORDER BY permissions_id')
print_white("checking totals between old and new permissions")
ok = True
if not (len(perms_old) == len(perms_new)):
ok = False
print ("permissions mismatch: old len %d new len %d" % (len(perms_old), len(perms_new)))
if ok:
print_green("ok")
else:
print_red("nok")
print_white("checking new with old permissions")
ok = True
for i in range(len(perms_old)):
if not (perms_old[i] == perms_new[i]):
ok = False
print perms_old[i]
print perms_new[i]
print
if ok:
print_green("ok")
else:
print_red("nok")
def show_dialled_number_combo(from_level, telno):
"""
Display the communication possibilities from service-level to telephone number.
from_level -- service-level of the initiator (% wildcard allowed)
telno -- exact telephone number
"""
conn = pdatab.connect_permissions_db()
levels = pdatab.query_permissions_db(conn, \
"SELECT service_level,service_group_id FROM ucid_service_level_groups WHERE service_level LIKE '%s'" % from_level)
creg = __compile_regexes(pdatab.query_permissions_db(conn, "SELECT regular_exp FROM dialled_number_classifications"))
for c in creg:
if(c.match(telno)):
details = __match_telno_description(conn, c.pattern)
for level in levels:
print_white("%s (%d) \t=>\t %s \"%s:%s\" (%d)" % (level[0],level[1],telno,details[0][2],details[0][3],details[0][1]))
__print_permissions(pdatab.query_permissions_db(conn, \
"SELECT permissions_xml_stanza FROM permissions WHERE permissions_id = \
(SELECT permissions_id FROM service_group_dialled_number_permissions WHERE orig_group = '%d' AND dialled_number_classification = '%d')" \
% (level[1],details[0][1]))[0][0])
print "total: ", len(levels)
pdatab.disconnect_permissions_db(conn)
def show_registration_permission(service_level):
conn = pdatab.connect_permissions_db()
levelres = pdatab.query_permissions_db(conn, \
"SELECT readable_description,service_group_id FROM ucid_service_level_groups WHERE service_level LIKE '%s'" % service_level)
if len(levelres) > 0:
for l in levelres:
print_green(l[0])
groupres = pdatab.query_permissions_db(conn, \
"SELECT service_group_name FROM service_level_groups WHERE service_group_id = '%d'" % l[1])
if len(groupres) > 0:
for g in groupres:
print_white("%s : %s(%d)" % (service_level,g[0],l[1]))
regres = pdatab.query_permissions_db(conn, \
"SELECT device_type_id,registrations_id FROM service_group_registrations WHERE service_group_id = '%d'" % l[1])
for r in regres:
devtype = pdatab.query_permissions_db(conn, \
"SELECT device_type_name,device_type_description FROM device_types WHERE device_type_id = '%d'" % r[0])
perm = pdatab.query_permissions_db(conn, \
"SELECT registrations_xml_stanza from registrations WHERE registrations_id = '%d'" % r[1])
print_white("\t%s(%d) : %s" % (devtype[0][0],r[0],devtype[0][1]))
print_white("\tpermission(%d)" % r[1])
prettyprint_registration_permission(perm[0][0])
else:
print_red('service_group_id (%d) is not defined' % l[1])
else:
print_red('%s is not defined' % service_level)
pdatab.disconnect_permissions_db(conn)
def check_servicelevels():
print_white("checking ucid_service_levels to ucid_service_level_groups consistency")
print_white(separator)
print
levels_old = sorted(pdatab.query_permissions_db(conn_old, 'SELECT service_level FROM ucid_service_levels'))
levels_new = sorted(pdatab.query_permissions_db(conn_new, 'SELECT service_level FROM ucid_service_levels'))
groups_new = pdatab.query_permissions_db(conn_new, 'SELECT service_level,service_group_id FROM ucid_service_level_groups')
print_white("checking level and group totals")
ok = True
if not (len(levels_old) == len(levels_new) and len(levels_old) == len(groups_new)):
ok = False
print_red("levels and groupings are inconsistent")
if ok:
print_green("ok")
else:
print_red("nok")
print_white("checking old levels present as new levels")
ok = True
groups = sorted(g[0] for g in groups_new)
levels = sorted(l[0] for l in levels_old)
for i in range(len(levels_old)):
if not (levels_old[i] == levels_new[i]):
ok = False
print_red(str(levels_old[i]) + " old vs new " + str(levels_new[i]))
if not (levels[i] == groups[i]):
ok = False
print_red(str(levels[i]) + " old vs new " + str(groups[i]))
if ok:
print_green("ok")
else:
print_red("nok")
def show_service_level_combo(from_level, to_level):
"""
Display the communication possibilities from one service-level to another.
from_level -- service-level of the initiator (% wildcard allowed)
to_level -- service-level the responder (% wildcard allowed)
"""
conn = pdatab.connect_permissions_db()
olevels = pdatab.query_permissions_db(conn,\
"SELECT service_level,service_group_id FROM ucid_service_level_groups WHERE service_level LIKE '%s'" % from_level)
tlevels = pdatab.query_permissions_db(conn,\
"SELECT service_level,service_group_id FROM ucid_service_level_groups WHERE service_level LIKE '%s'" % to_level)
for olevel in olevels:
for tlevel in tlevels:
permid = pdatab.query_permissions_db(conn,\
"SELECT permissions_id FROM service_group_permissions WHERE orig_group = '%s' AND term_group = '%s'" \
% (olevel[1],tlevel[1]))
print_white("%s (%d) \t=>\t %s (%d) \t: %d" % (olevel[0],olevel[1],tlevel[0],tlevel[1],permid[0][0]))
__print_permissions(pdatab.query_permissions_db(conn, \
("SELECT permissions_xml_stanza FROM permissions WHERE permissions_id='%d'" % permid[0][0]))[0][0])
pdatab.disconnect_permissions_db(conn)
def show_service_groups():
"""Display a full list of service_group_ids, showing corresponding service levels"""
conn = pdatab.connect_permissions_db()
groups = pdatab.query_permissions_db(conn, "SELECT service_group_id,service_group_name FROM service_level_groups ORDER BY service_group_id")
for group in groups:
print_white("%2d : %s" % (group[0],group[1]))
levels = pdatab.query_permissions_db(conn, \
"SELECT service_level FROM ucid_service_level_groups WHERE service_group_id ='%d' ORDER BY service_level" % group[0])
for level in levels:
print "\t%s" % level[0]
pdatab.disconnect_permissions_db(conn)
def show_dialled_number_combos_for_permission(permission_id,from_level='%',verbose=False):
"""
Discover which service-levels can call what dialled number classifications with this permission.
permission_id -- numeric ID of permission.
from_level -- optional service level of interest (or combibation with '%' wildcard).
verbose -- optional true will provide readable description of service level.
"""
conn = pdatab.connect_permissions_db()
print "Dialled number combinations for Permission ID = %d" % permission_id
permres = pdatab.query_permissions_db(conn, \
"SELECT permissions_xml_stanza FROM permissions WHERE permissions_id = '%d'" % permission_id)
if len(permres) > 0:
__print_permissions(permres[0][0])
else:
print 'Error: not defined - permission ID =',str(permission_id)
return
groups = pdatab.query_permissions_db(conn, \
"SELECT orig_group,dialled_number_classification FROM service_group_dialled_number_permissions WHERE permissions_id = '%d'" % permission_id)
combos = []
for group in groups:
levels = pdatab.query_permissions_db(conn, \
"SELECT service_level,readable_description,service_group_id FROM ucid_service_level_groups \
WHERE service_group_id = '%d' AND service_level LIKE '%s'" % (group[0],from_level))
combos.extend([level + (group[1],) for level in levels])
pdatab.disconnect_permissions_db(conn)
total = len(combos)
if total > 0:
combos = sorted(combos)
lastprinted = None
for combo in combos:
if not (lastprinted == combo[0]):
print_white("%s" % (combo[0]))
if verbose:
print combo[1]
print "\t(%d) => [%d]" % (combo[2],combo[3])
else:
print "\t(%d) => [%d]" % (combo[2],combo[3])
lastprinted = combo[0]
else:
print 'There are no dialled number combinations with permission = %d' % permission_id
print 'total:',total
def show_dialled_number_classifications():
"""Show number_class_ids and their interpretations"""
conn = pdatab.connect_permissions_db()
nids = pdatab.query_permissions_db(conn, "SELECT number_class_id,class_name FROM dialled_number_classes ORDER BY number_class_id")
print nids
for nid in nids:
print_white("%2d - %s" % (nid[0],nid[1]))
cls = pdatab.query_permissions_db(conn, \
"SELECT order_id,regular_exp,number_classification,readable_description FROM dialled_number_classifications \
WHERE number_class_id = '%d' ORDER BY order_id" % (nid[0]))
for cl in cls:
print "\t\"%s : %s\"" % (cl[2],cl[3])
pdatab.disconnect_permissions_db(conn)
def __match_telno_description(conn, regex):
regex = str(regex).replace("\\","\\\\")
query = ["SELECT number_class_id,number_classification,readable_description ",
"FROM dialled_number_classifications WHERE regular_exp='",str(regex),"'"]
return pdatab.query_permissions_db(conn, \
"SELECT order_id,number_class_id,number_classification,readable_description \
FROM dialled_number_classifications WHERE regular_exp='%s'" % regex )
def check_diallednumber_classifications():
print_white("checking dialled_number_classifications consistency")
print_white(separator)
print
dnc_old = sorted(pdatab.query_permissions_db(conn_old, 'SELECT * FROM dialled_number_classifications ORDER BY order_id'))
dnc_new = sorted(pdatab.query_permissions_db(conn_new, 'SELECT * FROM dialled_number_classifications ORDER BY order_id'))
print_white("checking identical")
ok = True
for i in range(len(dnc_old)):
if not (dnc_old[i] == dnc_new[i]):
ok = False
print_red(str(dnc_old[i]) + " old vs new " + str(dnc_new[i]))
if ok:
print_green("ok")
else:
print_red("nok")
def check_diallednumber_permissions():
print_white("checking service_level_permissions to service_group_permissions consistency")
print_white(separator)
print
print_white("checking new groups consistent with old levels")
ok = True
levels_old = pdatab.query_permissions_db(conn_old, 'SELECT * FROM dialled_number_permissions')
for l in levels_old:
#print l[2]
id = pdatab.query_permissions_db(conn_new, "SELECT permissions_id FROM service_group_dialled_number_permissions WHERE \
orig_group = (SELECT service_group_id FROM ucid_service_level_groups WHERE service_level = '" + l[0] + "') AND \
dialled_number_classification = '" + str(l[1]) + "'")
#print id[0][0]
if not (l[2] == id[0][0]):
ok = False
group = pdatab.query_permissions_db(conn_new, "SELECT service_group_id FROM ucid_service_level_groups WHERE service_level = '" + l[0] + "'")
print ("%s (group %d) vs dial class %d should be %d and not %d" % (l[0], group[0][0], l[1], l[2], id[0][0]))
if ok:
print_green("ok")
else:
print_red("nok")
print_white("checking new copy of levels consistent with old levels")
ok = True
total = pdatab.query_permissions_db(conn_new, 'SELECT COUNT(*) FROM dialled_number_permissions')
if not len(levels_old) == total[0][0]:
ok = False
print ("service mismatch: old len %d new len %d" % (len(levels_old),total[0][0]))
for l in levels_old:
id = pdatab.query_permissions_db(conn_new, "SELECT permissions_id FROM dialled_number_permissions WHERE orig_level = '"
+ l[0] + "' AND dialled_number_classification = '" + str(l[1]) + "'")
if not (l[2] == id[0][0]):
ok = False
print ("%s vs %s -> %d not %d" % (l[0], l[1], l[2], id[0][0]))
if ok:
print_green("ok")
else:
print_red("nok")
def setup():
"""
Creates reference sets of service-levels, permissions ids and number classifications.
Also checks duplication of values.
service-levels are read from service_level in ucid_service_levels
permissions ids are read from permissions_id in permissions
number classifications are read from number_class_id in dialled_number_classifications
"""
print 'Loading reference values and checking omissions and duplications'
conn = pdatab.connect_permissions_db(ip="localhost", port=3306, db="pcomp_srv")
levels = pdatab.query_permissions_db(conn, 'SELECT service_level,service_group_id FROM ucid_service_level_groups')
groups = pdatab.query_permissions_db(conn, 'SELECT service_group_id FROM service_level_groups')
permids = pdatab.query_permissions_db(conn, 'SELECT permissions_id FROM permissions')
numids = pdatab.query_permissions_db(conn, 'SELECT number_class_id FROM dialled_number_classifications')
pdatab.disconnect_permissions_db(conn)
ok = 0
groups = [g[0] for g in groups]
if show_omissions([l[1] for l in levels], groups, 'ucid_service_level_groups'):
ok = ok + 1
global servicegroupids
servicegroupids = ImmutableSet(groups)
levels = [l[0] for l in levels]
if show_duplicates(levels,'ucid_service_levels_groups'):
ok = ok + 1
global servicelevels
servicelevels = ImmutableSet(levels)
pids = [p[0] for p in permids]
if show_duplicates(pids,'permissions'):
ok = ok + 1
global permissionsids
permissionsids = ImmutableSet(pids)
# numberclassids will contain duplicates
global numberclassids
numberclassids = ImmutableSet(n[0] for n in numids)
if ok == 3:
print_green('ok')
def show_service_level_combos_for_permission(permission_id,from_level='%',to_level='%',verbose=False):
"""
Discover which combinations of service-level have this particular permission.
permission_id -- numeric ID of permission.
from_level -- optional service level of originator (including combinations with '%' wildcard)
to_level -- optional service level of destination (including combinations with '%' wildcard)
verbose -- optional true will provide readable description of service level.
"""
conn = pdatab.connect_permissions_db()
print_white("Service-Level combinations for Permission ID = %d" % (permission_id))
__print_permissions(pdatab.query_permissions_db(conn, \
"SELECT permissions_xml_stanza FROM permissions WHERE permissions_id = '%d'" % permission_id)[0][0])
groupings = pdatab.query_permissions_db(conn, \
"SELECT orig_group,term_group FROM service_group_permissions WHERE permissions_id = '%d'" % permission_id)
olevels = pdatab.query_permissions_db(conn, \
"SELECT service_group_id,service_level,readable_description FROM ucid_service_level_groups WHERE service_level LIKE '%s'" % from_level)
tlevels = pdatab.query_permissions_db(conn, \
"SELECT service_group_id,service_level,readable_description FROM ucid_service_level_groups WHERE service_level LIKE '%s'" % to_level)
pdatab.disconnect_permissions_db(conn)
total = 0
for o in olevels:
for t in tlevels:
for g in groupings:
if o[0] == g[0] and t[0] == g[1]:
total = total + 1
print_white("%s (%d) \t=>\t %s (%d)" % (o[1],o[0],t[1],t[0]))
if verbose:
print "orig:",o[2]
print "term:",t[2]
if total == 0:
print_red("There are no service-level combinations with permission = %d" % permission_id)
print 'total:',total
def show_available_permissions():
"""Show permissions IDs and their interpretation."""
conn = pdatab.connect_permissions_db()
perms = pdatab.query_permissions_db(conn, "SELECT permissions_id,permissions_xml_stanza FROM permissions")
pdatab.disconnect_permissions_db(conn)
for pid,pxml in perms:
print_white("Permission ID = %d" % pid)
__print_permissions(pxml)
print 'Total:',len(perms)
def print_permission(id):
"""Print permissions stanza associated with permission_id."""
conn = pdatab.connect_permissions_db()
perms = pdatab.query_permissions_db(conn, "SELECT permissions_id,permissions_xml_stanza FROM permissions WHERE permissions_id = '%d'" % (id))
pdatab.disconnect_permissions_db(conn)
for pxml in perms:
print_white("Permission ID = %d" % id)
print_yellow(pxml[1])
print 'Total:',len(perms)
def check_undefineds_in_service_group_permissions():
"""
Checks for undefined values in serviec_level_permissions table.
orig_level only has service-levels that are defined as service_level in ucid_service_levels
term_level only has service-levels that are defined as service_level in ucid_service_levels
permissions_id only has ids defined as permissions_id in permissions
Prints 'ok' if everything is defined; otherwise prints undefined values.
"""
print 'Checking undefineds in service_group_permissions'
conn = pdatab.connect_permissions_db()
res = pdatab.query_permissions_db(conn, 'SELECT orig_group,term_group,permissions_id FROM service_group_permissions')
pdatab.disconnect_permissions_db(conn)
ogroups = Set(l[0] for l in res)
tgroups = Set(n[1] for n in res)
permids = Set(p[2] for p in res)
ok = 1
delta = ogroups.difference(servicegroupids)
if len(delta) > 0:
ok = 0
print 'orig_group contains the following undefined service_group_ids:'
for d in delta:
print_red('\t',str(d))
delta = tgroups.difference(servicegroupids)
if len(delta) > 0:
ok = 0
print 'term_group contains the following undefined service_group_ids:'
for d in delta:
print_red('\t',str(d))
delta = permids.difference(permissionsids)
if len(delta) > 0:
ok = 0
print 'permissions_id contains the following undefined permissions ids:'
for d in delta:
print_red('\t',str(d))
return ok
def check_undefineds_in_service_group_dialled_number_permissions():
"""
Checks for undefined values in service_group_dialled_number_permissions table.
orig_group only has service_group_ids that are defined in service_level_groups
dialled_number_classification only has number classifications defined as number_class_id in dialled_number_classifications
permissions_id only has ids defined as permissions_id in permissions
Prints 'ok' if everything is defined; otherwise prints undefined values.
"""
print 'Checking undefineds in service_group_dialled_number_permissions'
conn = pdatab.connect_permissions_db()
res = pdatab.query_permissions_db(conn, 'SELECT orig_group,dialled_number_classification,permissions_id FROM service_group_dialled_number_permissions')
pdatab.disconnect_permissions_db(conn)
levels = Set(l[0] for l in res)
numids = Set(n[1] for n in res)
permids = Set(p[2] for p in res)
ok = True
delta = levels.difference(servicegroupids)
if len(delta) > 0:
ok = False
print 'orig_group contains the following undefined service-levels:'
for d in delta:
print_red('\t',str(d))
delta = numids.difference(numberclassids)
if len(delta) > 0:
ok = False
print 'dialled_number_classification contains the following undefined number classifications:'
for d in delta:
print_red('\t',str(d))
delta = permids.difference(permissionsids)
if len(delta) > 0:
ok = False
print 'permissions contains the following undefined permissions ids:'
for d in delta:
print_red('\t',str(d))
return ok
def __match_telnos(telnos):
conn = pdatab.connect_permissions_db()
creg = __compile_regexes(pdatab.query_permissions_db(conn, "SELECT regular_exp FROM dialled_number_classifications"))
for t in telnos:
count = 0
for c in creg:
if(c.match(t)):
count = count + 1
print t," matched ",c.pattern
details = __match_telno_description(conn, c.pattern)
if details == ():
print "no details found"
else:
print "\t",details[0][0]
print "\t",details[0][1]
print "\t",details[0][2]
if(count == 1):
print "ok"
else:
print "-->NOK: ",t," has ",count," matches"
pdatab.disconnect_permissions_db(conn)
def check_servicelevel_permissions():
print_white("checking service_level_permissions to service_group_permissions consistency")
print_white(separator)
print
levels_old = pdatab.query_permissions_db(conn_old, 'SELECT * FROM service_level_permissions')
print_white("checking new groups consistent with old levels")
ok = True
for l in levels_old:
id = pdatab.query_permissions_db(conn_new, "SELECT permissions_id FROM service_group_permissions WHERE orig_group = \
(SELECT service_group_id FROM ucid_service_level_groups WHERE service_level = '" + l[0] + "') AND term_group = \
(SELECT service_group_id FROM ucid_service_level_groups WHERE service_level = '" + l[1] + "')")
if not (l[2] == id[0][0]):
ok = False
group0 = pdatab.query_permissions_db(conn_new, "SELECT service_group_id FROM ucid_service_level_groups WHERE service_level = '" + l[0] + "'")
group1 = pdatab.query_permissions_db(conn_new, "SELECT service_group_id FROM ucid_service_level_groups WHERE service_level = '" + l[1] + "'")
print ("%s (group %d) vs %s (group %d) should be %d and not %d" % (l[0], group0[0][0], l[1], group1[0][0], l[2], id[0][0]))
if ok:
print_green("ok")
else:
print_red("nok")
print_white("checking new copy of levels consistent with old levels")
ok = True
total = pdatab.query_permissions_db(conn_new, 'SELECT COUNT(*) FROM service_level_permissions')
if not len(levels_old) == total[0][0]:
ok = False
print ("levels mismatch: old len %d new len %d" % (len(levels_old),total[0][0]))
for l in levels_old:
id = pdatab.query_permissions_db(conn_new, "SELECT permissions_id FROM service_level_permissions WHERE orig_level = '"
+ l[0] + "' AND term_level = '" + l[1] + "'")
if not (l[2] == id[0][0]):
ok = False
print ("%s vs %s -> %d not %d" % (l[0], l[1], l[2], id[0][0]))
if ok:
print_green("ok")
else:
print_red("nok")
