Пример #1
0
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"
Пример #2
0
import sys
from robotremoteserver import RobotRemoteServer
from DatabaseLibrary import DatabaseLibrary

if __name__ == '__main__':
    RobotRemoteServer(DatabaseLibrary(), host="0.0.0.0", *sys.argv[1:])

Пример #3
0
 def __init__(self):
     self.dbl = DatabaseLibrary()
Пример #4
0
 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))
Пример #5
0
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()
Пример #6
0
 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)
Пример #7
0
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
Пример #8
0
 def __init__(self):
     self.conn = DatabaseLibrary()
Пример #9
0
 def __init__(self, id = 0):
     return DatabaseLibrary().set(self, id)
Пример #10
0
 def __init__(self, *args, **kwargs) -> None:
     self.logger = logging.getLogger(__name__)
     DatabaseLibrary.__init__(self, *args, **kwargs)