class UserRepo:
dbl = None
def __init__(self):
self.dbl = DatabaseLibrary()
def getAllUsers(self, id):
if id == "":
return self.dbl.execute("SELECT * FROM tblusers")
else:
return self.dbl.execute("SELECT * FROM tblusers WHERE userID = %s",
[id])
def get_user(self):
return "get_user"
import sys
from robotremoteserver import RobotRemoteServer
from DatabaseLibrary import DatabaseLibrary
if __name__ == '__main__':
RobotRemoteServer(DatabaseLibrary(), host="0.0.0.0", *sys.argv[1:])
def __init__(self):
self.dbl = DatabaseLibrary()
def set_connection_info(self, user, password, connection_url):
self.conn = DatabaseLibrary()
self.conn.connect_to_database_using_custom_params('cx_Oracle',
"'%s','%s','%s'" % (user, password, connection_url))
class Xroads6DBUtil():
conn = None
QUERY_PERM_GROUP_BY_PERM_ID = 'SELECT ' \
' R.RSRC_ID AS PERM_ID, ' \
' P.PRSNTTN_LABEL AS PERM_LABEL, ' \
' R2.RSRC_ID AS PERM_GROUP_ID, ' \
' P2.PRSNTTN_LABEL AS PERM_GROIUP_LABEL ' \
'FROM RESOURC R ' \
' INNER JOIN PRESENTATION P ON P.PRSNTTN_ID = R.PRSNTTN_ID ' \
' INNER JOIN RSRC_TO_RSRC_REL RREL ON RREL.RSRC_ID1 = R.RSRC_ID ' \
' AND UPPER(RREL.REL_TYP_CD) = UPPER(\'PermissionGroup\') ' \
' INNER JOIN RESOURC R2 ON R2.RSRC_ID = RREL.RSRC_ID2 ' \
' INNER JOIN PRESENTATION P2 ON P2.PRSNTTN_ID = R2.PRSNTTN_ID ' \
'WHERE ( R.RSRC_ID IN ( %s ) OR P.PRSNTTN_LABEL IN ( %s) ) ' \
' AND R.RSRC_TYP_CD = \'2\' AND R2.RSRC_TYP_CD = \'3\' '
def set_connection_info(self, user, password, connection_url):
self.conn = DatabaseLibrary()
self.conn.connect_to_database_using_custom_params('cx_Oracle',
"'%s','%s','%s'" % (user, password, connection_url))
def get_permission_group(self, perm_id_label_list):
"""
:param perm_id_label_list: the list of permissions to query
:return: a map of from permission id to tuple of (permission_lable, permission_group_id, permission_group_label)
"""
perm_id_str = ', '.join([("'%s'" % id_label) for id_label in perm_id_label_list])
# for perm_id in perm_id_label_list:
# perm_id_str += "'%s', " % perm_id
#perm_id_str = perm_id_str[:len(perm_id_str) - 2]
logger.info('Permission detail query: \n' + self.QUERY_PERM_GROUP_BY_PERM_ID % ( perm_id_str, perm_id_str))
perm_to_group_map_rs = self.conn.query(self.QUERY_PERM_GROUP_BY_PERM_ID % (perm_id_str, perm_id_str))
result = {}
for row in perm_to_group_map_rs:
logger.debug(row)
result[row[0]] = row[1:]
logger.info('Permission detail query result: ')
logger.info(result)
return result
def get_permission_group_by_label(self, perm_grp_label):
query = "SELECT R.RSRC_ID FROM RESOURC R INNER JOIN PRESENTATION P ON P.PRSNTTN_ID = R.PRSNTTN_ID " \
"WHERE P.PRSNTTN_LABEL = '%s' AND R.RSRC_TYP_CD = '3'"
perm_grp_rs = self.conn.query(query)
if 0 == len(perm_grp_rs):
return None
else:
return (perm_grp_rs[0][0], perm_grp_label)
def get_permission_group_by_id(self, perm_grp_id):
query = "SELECT P.PRSNTTN_LABEL FROM RESOURC R INNER JOIN PRESENTATION P ON P.PRSNTTN_ID = R.PRSNTTN_ID " \
"WHERE R.RSRC_ID = '%s' AND R.RSRC_TYP_CD = '3'"
perm_grp_rs = self.conn.query(query)
if 0 == len(perm_grp_rs):
return None
else:
return (perm_grp_id, perm_grp_rs[0][0])
def company_should_have_resource_in_xroads6_db(self, company_id, resource_id, resource_type):
"""
Verify that the specified company have resource in the company resource table
:param company_id:
:param resource_id:
:param resource_type:
:return:
"""
query = "SELECT * FROM XROADS_OWNER.COMPANY_RESOURCE WHERE CMPNY_ID = '%s' " \
" AND RSRC_ID = '%s' AND RSRC_TYP_CD = '%s'" % (
company_id, resource_id, resource_type)
self.conn.check_if_exists_in_database(query)
def xroads6_db_should_have_resource(self, resource_id, resource_type):
"""
Verify that the specified company have resource in the company resource table
:param resource_id:
:param resource_type:
:return:
"""
query = "SELECT * FROM XROADS_OWNER.RESOURC R WHERE R.RSRC_ID = '%s' AND R.RSRC_TYP_CD = '%s' " % (
resource_id, resource_type)
self.conn.check_if_exists_in_database(query)
def get_usable_alternate_id(self):
prefix = 'alt_id_'
random_len = 8
for i in range(random_len):
prefix += chr(random.randint(97, 122))
query = "SELECT USR_ID FROM USER_XROADS UX WHERE UX.USR_ALTERNATE_ID = '%s'" % prefix
if self.conn.row_count(query) > 0:
return self.get_usable_alternate_id()
return prefix
def close_db_connection(self):
if self.conn is not None:
self.conn.disconnect_from_database()
def __init__(self):
self._instance = DatabaseLibrary()
self._instance.connect_to_database(DB_API_MODULE_NAME, DB_NAME,
DB_USER, DB_PASS, DB_HOST, DB_PORT)
class Call_Oracle():
conn = None
def __init__(self):
self.conn = DatabaseLibrary()
def login_db(self, db_name):
if 'test' in db_name:
self.login_to_test_db()
elif 'dev' in db_name:
self.login_to_dev_db()
else:
raise AssertionError("No such connection DB name %s"%db_name)
def login_to_test_db(self):
#self.conn = DatabaseLibrary()
#logger.info("Start to connect to Oracle...")
self.conn.connect_to_database_using_custom_params('cx_Oracle', r"'XROADS_RO','xroads_r34d', 'rhrac1scan.syniverse.com:1521/xrds3t'")
#logger.info("Successfully connected!")
def login_to_dev_db(self):
#self.conn = DatabaseLibrary()
#logger.info("Start to connect to Oracle...")
self.conn.connect_to_database_using_custom_params('cx_Oracle', r"'xroads_app','xroads', 'rhrac1scan.syniverse.com:1521/XRDSD1'")
#logger.info("Successfully connected!")
def verify_company_has_permission_in_DB(self, company_name, permission):
query = "select * from company_resource where cmpny_id in " \
"(select cmpny_cd from company_xroads where cmpny_name='%s') and rsrc_id= '%s' and RSRC_TYP_CD='2'"%(company_name, permission)
logger.debug("Verify Company has Permission by:\n %s" %query)
try:
self.conn.check_if_exists_in_database(query)
except AssertionError:
raise AssertionError("Incorect, we got: %s, by query \n %s"%(self.conn.query(query), query))
logger.info("==Correct! Company '%s' has '%s' in DB"%(company_name, permission))
def verify_company_has_no_such_permission_in_DB(self, company_name, permission):
query = "select * from company_resource where cmpny_id in " \
"(select cmpny_cd from company_xroads where cmpny_name='%s') and rsrc_id= '%s' and RSRC_TYP_CD='2'"%(company_name, permission)
logger.debug("Verify Company has no Permission by:\n %s"%query)
self.conn.check_if_not_exists_in_database(query)
logger.info("==Correct! Company %s does not has '%s' in DB"%(company_name, permission))
def verify_user_has_permission_in_DB(self, user_id, permission_id):
query = "select * from access_auth where RSRC_ID = '%s' and accessor_id in " \
"(select accessor_id from accessor where usr_id ='%s')"%(permission_id, user_id)
self.conn.check_if_exists_in_database(query)
logger.info("==Correct, User %s has permission '%s' in DB"%(user_id, permission_id))
def verify_user_has_no_such_permission_in_DB(self, user_id, permission_id):
query = "select * from access_auth where RSRC_ID = '%s' and accessor_id in " \
"(select accessor_id from accessor where usr_id ='%s')"%(permission_id, user_id)
logger.debug("Verify User has no such Permission by:\n %s"%query)
self.conn.check_if_not_exists_in_database(query)
logger.info("==Correct, user '%s' has not permissoin '%s' in DB"%(user_id, permission_id))
def get_permission_id_from_label(self, label_text, label_type, label_usage):
'''
get the permission id from the label text and type.
:param label_text: The text value of the label
:param label_type: 'group' or 'single'
:param label_usage: 'company' or 'user'
:param element_type: 'check_box' or 'radio_button'
:return:
'''
self.statement = None
label_text = string.strip(label_text)
logger.info("Get permission ID for lable type = '%s', and label_usage = '%s'"%(label_type, label_usage))
if label_type =='group' and label_usage=='company':
self.statement = "select RSRC_ID from resourc where RSRC_TYP_CD='3' and RSRC_GROUPTYPE='company' and PRSNTTN_ID in " \
"(select PRSNTTN_ID from presentation where PRSNTTN_LABEL in " \
"('%s'))"%label_text
elif label_type =='group' and label_usage=='user':
self.statement = "select RSRC_ID from resourc where RSRC_TYP_CD='3' and RSRC_GROUPTYPE='user' and PRSNTTN_ID in " \
"(select PRSNTTN_ID from presentation where PRSNTTN_LABEL in " \
"('%s'))"%label_text
elif label_type == 'single' and label_usage == 'company':
self.statement = "select RSRC_ID from resourc where RSRC_TYP_CD='2' " \
"and PRSNTTN_ID in (select PRSNTTN_ID from presentation where PRSNTTN_LABEL in ('%s'))"%label_text
elif label_type == 'single' and label_usage == 'user':
self.statement = "select RSRC_ID1 from RSRC_TO_RSRC_REL where REL_TYP_CD = 'Copermission' " \
"and RSRC_ID2 in (SELECT RSRC_ID FROM resourc WHERE RSRC_TYP_CD=2 " \
"and PRSNTTN_ID IN (SELECT PRSNTTN_ID FROM presentation " \
"WHERE PRSNTTN_LABEL IN = '%s'))"%label_text
else:
raise Exception("Sorry, no such label_type: '%s' and usage: '%s'"%(label_type, label_usage))
ds = self.conn.query(self.statement)
if len(ds)==0:
raise AssertionError("No result of %s, please check if you input label text correctly : '%s'"%(self.statement, label_text))
logger.info("Get '%s' id: '%s' "%(label_text, ds[0][0]))
return ds[0][0]
def calculate_company_permission(self, label_text, company_id):
base_query = "SELECT RSRC_ID FROM resourc WHERE RSRC_TYP_CD='2' AND PRSNTTN_ID IN " \
"(SELECT PRSNTTN_ID FROM presentation WHERE PRSNTTN_LABEL IN ('%s'))"%label_text
ext_query = "SELECT RSRC_ID FROM resourc WHERE CMPNY_TYP_CD<>'admin' AND RSRC_ID IN" \
"(SELECT distinct RSRC_ID2 FROM RSRC_TO_RSRC_REL WHERE REL_TYP_CD IN ('Implied', 'Copermission')" \
" AND RSRC_ID1 IN " \
"(SELECT RSRC_ID FROM resourc WHERE RSRC_TYP_CD='2' AND PRSNTTN_ID IN " \
"(SELECT PRSNTTN_ID FROM presentation WHERE PRSNTTN_LABEL IN " \
"('%s'))))"%label_text
print base_query
print ext_query
base_permission_ds = self.conn.query(base_query)
ext_permission_ds = self.conn.query(ext_query)
company_permission_list=[]
if len(base_permission_ds) == 2:
query_state = "select REL_TYP_CD from RSRC_TO_RSRC_REL where RSRC_ID1='%s' and RSRC_ID1='%s'"%(base_permission_ds[0][0], base_permission_ds[1][0])
#print "" + query_state
try:
self.conn.check_if_exists_in_database(query_state)
except AssertionError, ex:
# print "Base Permission : %s"%base_permission_ds[0]
company_permission_list +=base_permission_ds[0]
for each in ext_permission_ds:
company_permission_list +=each
logger.info("Company permission list:")
for each in company_permission_list:
logger.info(each)
return company_permission_list
def __init__(self):
self.conn = DatabaseLibrary()
def __init__(self, id = 0):
return DatabaseLibrary().set(self, id)
def __init__(self, *args, **kwargs) -> None:
self.logger = logging.getLogger(__name__)
DatabaseLibrary.__init__(self, *args, **kwargs)