示例#1
0
    def __init__(self, dbconfig):

        if not isinstance(dbconfig, DataBaseConfiguration):
            raise DatabaseConfigurationError, """The dbconfig input is not a valid """\
                """DataBaseConfiguration object."""

        self.protocol = dbconfig.protocol
        self.user_name = dbconfig.user_name
        self.password = dbconfig.password
        self.host_name = dbconfig.host_name
        self.database_name = dbconfig.database_name
        self.database_config_object = dbconfig
        """
        self.engine = None
        self.connection = None
        self.result = None
        self.query = None
        self.metadata = None
        self.table_name = None
        self.session = None
        self.class_name = None
        self.database_config_object = None
        """
        self.dbcon_obj = DataBaseConnection(dbconfig)
        print self.dbcon_obj
    def __init__(self, dbconfig):

        if not isinstance(dbconfig, DataBaseConfiguration):
            raise DatabaseConfigurationError, """The dbconfig input is not a valid """\
                """DataBaseConfiguration object."""

        self.protocol = dbconfig.protocol
        self.user_name = dbconfig.user_name
        self.password = dbconfig.password
        self.host_name = dbconfig.host_name
        self.database_name = dbconfig.database_name
        self.database_config_object = dbconfig
        """
        self.engine = None
        self.connection = None
        self.result = None
        self.query = None
        self.metadata = None
        self.table_name = None
        self.session = None
        self.class_name = None
        self.database_config_object = None
        """
        self.dbcon_obj = DataBaseConnection(dbconfig)
        print self.dbcon_obj
class QueryBrowser(object):

    ########## initialization ##########
    #initialize the class
    def __init__(self, dbconfig):

        if not isinstance(dbconfig, DataBaseConfiguration):
            raise DatabaseConfigurationError, """The dbconfig input is not a valid """\
                """DataBaseConfiguration object."""

        self.protocol = dbconfig.protocol
        self.user_name = dbconfig.user_name
        self.password = dbconfig.password
        self.host_name = dbconfig.host_name
        self.database_name = dbconfig.database_name
        self.database_config_object = dbconfig
        """
        self.engine = None
        self.connection = None
        self.result = None
        self.query = None
        self.metadata = None
        self.table_name = None
        self.session = None
        self.class_name = None
        self.database_config_object = None
        """
        self.dbcon_obj = DataBaseConnection(dbconfig)
        print self.dbcon_obj

    ########## initialization ends ##########

    ########## methods for mapping ##########

    #separate function for mapper
    def table_mapper(self, class_name, table_name):
        """
        This method is used to create a mapper to the table in the database

        Input:
        Class name and Table name

        Output:
        Creates a table mapper object
        """

        #before creating the mapper check if the table exists
        self.table_name = table_name
        self.class_name = class_name
        table_flag = self.dbcon_obj.check_if_table_exists(table_name)
        if table_flag:
            #table exists
            try:
                #print 'table name is %s and class name is %s'%(table_name, class_name)
                #load the table
                new_table = Table(self.table_name, self.dbcon_obj.metadata, autoload=True)

                #create mapper
                class_name = class_name.upper()
                mapper(eval(class_name), new_table)

                #create an object for the mapper
                self.temp_object = eval(class_name)()

                #print the session and mapper object
                #print 'the session object is %s and the mapper object is %s\n'%(self.dbcon_obj.session, self.temp_object)
                return self.temp_object
            except Exception, e:
                print 'Failed to create mapper'
                print e
                #raise Exception
        else:
 def __init__(self):
     self.dbc = DataBaseConnection()
class DataBaseManager(object):
    def __init__(self):
        self.dbc = DataBaseConnection()

    def add_user(self, user_info_dict):
        user_id = user_info_dict['user_id']
        user_name = user_info_dict['user_name']
        user_password = user_info_dict['user_password']
        sql = "insert into user_info values ('%s', '%s', '%s')" % (user_id, user_name, user_password)
        return self.dbc.execute(sql)

    def get_user(self, user_info_dict):
        condition = ""
        for key in user_info_dict.keys():
            condition = condition + str(key) + "='" + str(user_info_dict[key]) + "' and "
        condition = condition[0:len(condition)-4]
        sql = "select * from user_info where " + condition
        return self.dbc.select(sql)

    def get_manager(self, user_info_dict):
        condition = ""
        for key in user_info_dict.keys():
            condition = condition + str(key) + "='" + str(user_info_dict[key]) + "' and "
        if len(condition) > 0:
            condition = " where " + condition[0:len(condition)-4]
        sql = "select * from manager_info " + condition
        return self.dbc.select(sql)

    def add_content(self, content_info_dict):
        user_id = content_info_dict['user_id']
        content_title = content_info_dict['content_title']
        content_date = content_info_dict['content_date']
        content = content_info_dict['content']
        sql = "insert into CONTENT_INFO VALUES ('%s', '%s', '%s', '%s')" % (
            user_id,
            content_title,
            content_date,
            content
        )
        return self.dbc.execute(sql)

    def get_content(self, content_info_dict):
        user_id = content_info_dict['user_id']
        content_title = content_info_dict['content_title']
        content_date = content_info_dict['content_date']
        sql = "select content from CONTENT_INFO WHERE USER_ID='%s' AND CONTENT_TITLE='%s' and CONTENT_DATE='%s'" % (
            user_id,
            content_title,
            content_date
        )
        return self.dbc.select(sql)

    def update_content(self, content_info_dict):
        user_id = content_info_dict['user_id']
        content_title = content_info_dict['content_title']
        content_date = content_info_dict['content_date']
        content = content_info_dict['content']
        sql = "UPDATE CONTENT_INFO SET CONTENT='%s' WHERE USER_ID='%s' and CONTENT_TITLE='%s' and CONTENT_DATE='%s'" % (
            content,
            user_id,
            content_title,
            content_date
        )
        return self.dbc.execute(sql)

    def get_content_titles(self, content_title_info_dict):
        user_id = content_title_info_dict['user_id']
        # first_index = content_title_info_dict['first_index']
        # list_length = content_title_info_dict['list_length']
        # sql = "SELECT CONTENT_TITLE FROM " \
        #       "(SELECT ci.*,ROWNUM ROWM FROM CONTENT_INFO ci WHERE USER_ID='%s' ORDER BY ROWNUM DESC) " \
        #       "WHERE %s<=ROWM AND ROWM<%s" % (user_id, first_index, first_index+list_length)
        # if not first_index or not list_length:
        sql = "SELECT *FROM CONTENT_INFO WHERE USER_ID='%s' ORDER BY CONTENT_DATE DESC" % user_id
        return self.dbc.select(sql)

    def get_user_content_number(self):
        sql = "SELECT USER_INFO.USER_NAME,b.ucount,uuid " \
              "FROM USER_INFO,(SELECT COUNT(CONTENT_INFO.USER_ID) ucount, CONTENT_INFO.USER_ID uuid " \
              "from CONTENT_INFO GROUP BY CONTENT_INFO.USER_ID) b WHERE b.uuid = USER_INFO.USER_ID"
        return self.dbc.select(sql)

    def get_user_number(self):
        sql = "SELECT COUNT(*) NUM FROM USER_INFO"
        return self.dbc.select(sql)

    def get_content_number(self):
        sql = "SELECT COUNT(*) NUM FROM CONTENT_INFO"
        return self.dbc.select(sql)

    def delete_manager(self, manager_name):
        sql = "DELETE FROM MANAGER_INFO WHERE MANAGER_NAME = '%s'" % manager_name
        return self.dbc.execute(sql)

    def add_manager(self, manager_info):
        manager_id = manager_info['manager_id']
        manager_name = manager_info['manager_name']
        manager_password = manager_info['manager_password']
        sql = "insert into MANAGER_INFO values ('%s', '%s', '%s')" % (manager_id, manager_name, manager_password)
        return self.dbc.execute(sql)

    # 开发者管理
    def add_developer(self, developer_info):
        developer_name = developer_info['developer_name']
        developer_password = developer_info['developer_password']
        sql = "insert into DEVELOPER_INFO VALUES ('%s', '%s')" % (developer_name, developer_password)
        return self.dbc.execute(sql)

    def get_developer(self, developer_info):
        sql = "select * from DEVELOPER_INFO WHERE 1=1 "
        condition = ""
        if developer_info['developer_name']:
            condition = " and DEVELOPER_NAME='%s'" % developer_info['developer_name']
        sql += condition
        return self.dbc.select(sql)

    # cdk管理
    def add_app(self, app_info):
        developer_name = app_info['developer_name']
        developer_cdk = app_info['developer_cdk']
        developer_cdp = app_info['developer_cdp']
        app_name = app_info['app_name']
        sql = "insert into DEVELOPER_CDK VALUES ('%s', '%s', '%s', '%s')" % (developer_name, developer_cdk,
                                                                             developer_cdp, app_name)
        return self.dbc.execute(sql)

    def get_app(self, app_info):
        sql = "select * from DEVELOPER_CDK WHERE 1=1 "
        condition = ""
        if app_info['developer_name']:
            condition = " and DEVELOPER_NAME='%s'" % app_info['developer_name']
        sql += condition
        return self.dbc.select(sql)

    # 开发者所属的用户
    def add_user_developer(self, user_info):
        developer_cdk = user_info['developer_cdk']
        user_name = user_info['user_name']
        sql = "insert into DEVELOPER_USERS VALUES ('%s', '%s')" % (developer_cdk, user_name)
        return self.dbc.execute(sql)

    def get_user_developer(self, user_info):
        sql = "select * from DEVELOPER_USERS WHERE 1=1 "
        condition = ""
        if user_info['developer_cdk']:
            condition = " and DEVELOPER_CDK='%s'" % user_info['developer_cdk']
        sql += condition
        return self.dbc.execute(sql)

    def get_developer_app_user(self, developer_info):
        sql = "SELECT " \
              "DEVELOPER_CDK.DEVELOPER_NAME, DEVELOPER_CDK.DEVELOPER_CDK, DEVELOPER_CDK.DEVELOPER_CDP, " \
              "DEVELOPER_CDK.APP_NAME, count(DEVELOPER_USERS.USER_NAME) user_num " \
              "FROM DEVELOPER_CDK " \
              "LEFT JOIN DEVELOPER_USERS " \
              "ON DEVELOPER_CDK.DEVELOPER_CDK=DEVELOPER_USERS.DEVELOPER_CDK " \
              "WHERE DEVELOPER_CDK.DEVELOPER_NAME='%s' " \
              "GROUP BY DEVELOPER_CDK.DEVELOPER_NAME,DEVELOPER_CDK.DEVELOPER_CDK,DEVELOPER_CDK.DEVELOPER_CDP," \
              "DEVELOPER_CDK.APP_NAME" % developer_info['developer_name']
        return self.dbc.select(sql)

    # 获取指定的app的用户及其数量
    def get_developer_app_info(self, developer_app_info):
        sql = "SELECT users.user_name,count(CONTENT_INFO.CONTENT) num " \
              "FROM " \
              "(SELECT DEVELOPER_USERS.DEVELOPER_CDK cdk,USER_INFO.USER_NAME user_name,USER_INFO.USER_ID user_id " \
              "FROM DEVELOPER_USERS,USER_INFO " \
              "WHERE DEVELOPER_USERS.USER_NAME=USER_INFO.USER_NAME " \
              "AND DEVELOPER_USERS.DEVELOPER_CDK='%s' ) users " \
              "LEFT JOIN CONTENT_INFO ON users.user_id = CONTENT_INFO.USER_ID " \
              "GROUP BY users.user_name" % developer_app_info['developer_cdk']
        return self.dbc.select(sql)

    # 获取所有开发者
    def get_all_developer(self):
        sql = "select DEVELOPER_NAME from DEVELOPER_INFO"
        return self.dbc.select(sql)

    # 将用户授权到某个应用
    def user_auth_app(self, info):
        sql = "INSERT INTO DEVELOPER_USERS VALUES ('%s', '%s')" % (info['developer_cdk'], info['user_name'])
        return self.dbc.execute(sql)
示例#6
0
class QueryBrowser(object):

    ########## initialization ##########
    #initialize the class
    def __init__(self, dbconfig):

        if not isinstance(dbconfig, DataBaseConfiguration):
            raise DatabaseConfigurationError, """The dbconfig input is not a valid """\
                """DataBaseConfiguration object."""

        self.protocol = dbconfig.protocol
        self.user_name = dbconfig.user_name
        self.password = dbconfig.password
        self.host_name = dbconfig.host_name
        self.database_name = dbconfig.database_name
        self.database_config_object = dbconfig
        """
        self.engine = None
        self.connection = None
        self.result = None
        self.query = None
        self.metadata = None
        self.table_name = None
        self.session = None
        self.class_name = None
        self.database_config_object = None
        """
        self.dbcon_obj = DataBaseConnection(dbconfig)
        print self.dbcon_obj

    ########## initialization ends ##########

    ########## methods for mapping ##########

    #separate function for mapper
    def table_mapper(self, class_name, table_name):
        """
        This method is used to create a mapper to the table in the database

        Input:
        Class name and Table name

        Output:
        Creates a table mapper object
        """

        #before creating the mapper check if the table exists
        self.table_name = table_name
        self.class_name = class_name
        table_flag = self.dbcon_obj.check_if_table_exists(table_name)
        if table_flag:
            #table exists
            try:
                #print 'table name is %s and class name is %s'%(table_name, class_name)
                #load the table
                new_table = Table(self.table_name,
                                  self.dbcon_obj.metadata,
                                  autoload=True)

                #create mapper
                class_name = class_name.upper()
                mapper(eval(class_name), new_table)

                #create an object for the mapper
                self.temp_object = eval(class_name)()

                #print the session and mapper object
                #print 'the session object is %s and the mapper object is %s\n'%(self.dbcon_obj.session, self.temp_object)
                return self.temp_object
            except Exception, e:
                print 'Failed to create mapper'
                print e
                #raise Exception
        else: