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
