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()