Python database.Database类代码示例

    def assign_to(self,user):
        """
        Assigns this role to a user
        """
        session_user = Session.get_current_session_user()

        db = Database()
        
        #check if sessionuser has role
        
        has_role = session_user.has_role(self)

        stmnt = "SELECT COUNT(URI_RIG_ID) AS CNT FROM USERRIGHTS WHERE URI_RIG_ID IN \
            (SELECT RRI_RIG_ID FROM ROLERIGHTS WHERE RRI_ROL_ID = ? ) ;"
        cur = db.query(stmnt,(self._id,))
        res = cur.fetchone()[0]

        has_all_permissions_of_role = res == len(self.get_permissions())

        if not has_role and not has_all_permissions_of_role:
            raise PermissionException(PermissionException.get_msg(7))

        for role in user.get_grantable_roles():
            if role["name"] == self._name:
                stmnt = "UPDATE OR INSERT INTO USERROLES (URO_USR_ID, URO_ROL_ID) \
                    VALUES (?,?) MATCHING (URO_USR_ID, URO_ROL_ID) ;";
                db.query(stmnt, (user.get_id(),self._id),commit=True)
                PokeManager.add_activity(ActivityType.USER)
                return
        raise PermissionException(PermissionException.get_msg(8))

    def generate(cls):
        """
        Generates an Activity Report. This report contains,
        how many activities have happened since the last poke
        further it contains the activity types.
        """
        session  = Session.get_current_session()

        db = Database()
        stmnt = "SELECT ATV_TYPE, MAX(ATV_ID) AS LATEST_ID, COUNT(ATV_ID) AS AMOUNT FROM ACTIVITIES WHERE ATV_SES_ID != ? OR ATV_SES_ID IS NULL AND ATV_ID >= \
                COALESCE((SELECT SPO_ATV_ID FROM SESSIONPOKE WHERE SPO_SES_ID = ?),0) GROUP BY ATV_TYPE;"
        cur = db.query(stmnt, (session.get_id(), session.get_id()))

        activity_report = ActivityReport()

        res = cur.fetchallmap()
        for row in res:
            activity = Activity()
            activity.set_id(row["LATEST_ID"])
            activity.set_activity_type(row["ATV_TYPE"])

            activity_report._activities.append(activity)

            if activity_report._latest_id < row["LATEST_ID"]:
                activity_report._latest_id = row["LATEST_ID"]

            activity_report._amount += row["AMOUNT"]
        
        return activity_report

    def create_role(cls, data=None):
        if data is None:
            raise PermissionException(PermissionException.get_msg(10))
        if data["name"] is None:
            raise PermissionException(PermissionException.get_msg(11))

        db = Database()

        stmnt = "SELECT ROL_ID FROM ROLES WHERE ROL_NAME = ? ;"
        cur = db.query(stmnt,(data["name"],))
        res = cur.fetchonemap()
        if res is not None:
            raise PermissionException(PermissionException.get_msg(13, data["name"]))
        
        role_id = db.get_seq_next("ROL_GEN")
        role = Role()
        role.set_id(role_id)
        role.set_name(data["name"])
        role.store()

        if data.has_key("rights"):
            for permission in data["rights"]:
                if permission["granted"]:
                    role.add_permission(permission["name"])
                else:
                    role.remove_permission(permission["name"])
            role.store()

        return role

    def get_menu_item_by_id(cls, menu_item_id):
        """
        This function looks for a MenuItem with the given ID in the database
        and returns it
        If the MenuItem does not exist this returns null
        """
        db = Database()
        stmnt = "SELECT MNI_NAME, MNI_MNU_ID, MNI_MNI_ID, MNI_ATL_ID, MNI_ORDER \
                 FROM MENUITEMS WHERE MNI_ID = ? ;"
        cur = db.query(stmnt,(menu_item_id,))
        row = cur.fetchonemap()
        if row is not None:
            menu_item = MenuItem()
            menu_item.set_id(menu_item_id)
            menu_item.set_name(row["MNI_NAME"],True)
            menu_item.set_order(row["MNI_ORDER"])
            if row["MNI_MNU_ID"] is not None:
                menu_item.set_menu_id(row["MNI_MNU_ID"],True)
            if row["MNI_MNI_ID"] is not None:
                menu_item.set_parent_menu_item_id(row["MNI_MNI_ID"],True)
            if row["MNI_ATL_ID"] is not None:
                menu_item.set_action_list_id(row["MNI_ATL_ID"])

            return menu_item
        return None

    def get_action_by_id(cls, action_id):
        """
        This function looks for an Action with the given ID in the database
        and returns it
        If the action does not exist this returns null 
        """
        db = Database()
        stmnt = "SELECT ACT_NAME, ACT_ATL_ID, ACT_VIE_ID, \
                     ACT_SPA_ID, ACT_WGT_ID, ACT_URL, ACT_ORDER \
                 FROM ACTIONS WHERE ACT_ID = ?;"
        cur = db.query(stmnt, (action_id,))
        row = cur.fetchonemap()
        if row is not None:
            action = Action()
            if row["ACT_VIE_ID"] is not None:
                action.set_view_id(row["ACT_VIE_ID"],True)
            if row["ACT_URL"] is not None:
                action.set_url(row["ACT_URL"], True)
            if row["ACT_WGT_ID"] is not None and row["ACT_SPA_ID"] is not None:
                action.set_widget_space_constellation(row["ACT_WGT_ID"], row["ACT_SPA_ID"], True)
            action.set_id(action_id)
            action.set_name(row["ACT_NAME"],True)
            action.set_action_list_id(row["ACT_ATL_ID"])
            action.set_order(row["ACT_ORDER"])

            return action

        return None

    def check_permission(cls, permission, user):
        """
        checks whether a user has a specific permission
        """
        if user.__class__.__name__ == "User":
            user_id = user.get_id()
        elif type(user) != int:
            raise PermissionException(PermissionException.get_msg(9))

        db = Database()
        stmnt = "select 1 as RESULT from RDB$DATABASE  where CAST( ? AS VARCHAR(64)) in(select rig_name \
                from USERROLES \
                left join ROLES \
                  on rol_id = uro_rol_id \
                left join ROLERIGHTS \
                  on rri_rol_id = rol_id \
                left join RIGHTS \
                  on rig_id = rri_rig_id \
                where uro_usr_id = ? \
                union \
                select rig_name \
                from USERRIGHTS \
                left join RIGHTS \
                  on rig_id = uri_rig_id \
                where uri_usr_id = ?) ; " \
        
        cur = db.query(stmnt,(permission,user_id,user_id))

        res = cur.fetchone()
        if res is None:
            return False
        res = res[0]
        return res == 1

 def delete(self):
     """
     deletes this session
     """
     db = Database()
     stmnt = "DELETE FROM SESSIONS WHERE SES_ID = ? ;"
     db.query(stmnt,(self._id,),commit=True)

    def get_session(cls,cookies):
        """
        returns the session if it's not expired or nonexistant
        """
        cookie = SimpleCookie(cookies)
        session_id = cookie['session_id'].value
        
        db = Database()
        stmnt = "SELECT SES_USR_ID, SES_EXPIRES FROM SESSIONS WHERE SES_ID = ? ;"

        cur = db.query(stmnt,(session_id,))
        row = cur.fetchonemap()

        session=None

        if row is not None:
            user = User.get_user_by_id(row["SES_USR_ID"])
            session = Session(user)
            session._id = session_id
            expiration = row["SES_EXPIRES"]
            if expiration < datetime.now():
                raise SessionException(SessionException.get_msg(0))    
            session._expiration = row["SES_EXPIRES"]
        else:
            raise SessionException(SessionException.get_msg(2))
        return session

    def uninstall_module(cls,module, hard=False):
        """
        uninstall a module
        the flag "hard" actually deletes the files of this module in modpath
        module can be module or module meta
        """
        if module.__class__.__name__ != "Module":
            nr = cls._get_module_id_from_name(module_meta["name"])
            module = cls.get_module(nr)

        Action.delete_actions_with_module(module)
        View.delete_mappings_with_module(module)
        CSSManager().delete_definitions_with_module(module)

        db = Database()
        db.remove_tables_for_module(module)
        Permission.remove_permissions_for_module(module)

        if hard:
            modpath = Configuration().get_entry('global.modpath')
            version = module.get_version()
            shutil.rmtree(modpath+"/"+module.get_name()+"/v"+version[0]+"_"+version[1]+"_"+version[2])

        cls._unregister_module(module)
        PokeManager.add_activity(ActivityType.MODULE)

 def delete(self):
     """
     Deletes the ActionList from the DB
     """
     db = Database()
     stmnt = "DELETE FROM ACTIONLISTS WHERE ATL_ID = ? ;"
     db.query(stmnt, (self.get_id(),),commit=True)
     PokeManager.add_activity(ActivityType.MENU)

 def delete(self):
     """
     deletes this role from the database
     """
     db = Database()
     stmnt = "DELETE FROM ROLES WHERE ROL_ID = ? ;"
     db.query(stmnt,(self._id,),commit=True)
     PokeManager.add_activity(ActivityType.ROLE)

 def store(self):
     """
     currently only one repository can be owned by one skarphed instance
     """
     db = Database()
     stmnt = "UPDATE OR INSERT INTO REPOSITORIES (REP_ID, REP_NAME, REP_IP, REP_PORT, REP_LASTUPDATE, REP_PUBLICKEY) VALUES (1,?,?,?,?,?) MATCHING (REP_ID) ;"
     db.query(stmnt,(self._name, self._ip, self._port, self._lastupdate, self.get_public_key()),commit=True)
     PokeManager.add_activity(ActivityType.REPOSITORY)

 def remove_permission(cls, permission, module=""):
     """
     removes a permission from the database
     """
     db = Database()
     stmnt = "DELETE FROM RIGHTS WHERE RIG_NAME = ? ;"
     db.query(stmnt, (permission,),commit=True)
     PokeManager.add_activity(ActivityType.PERMISSION)

 def remove_permissions_for_module(cls,module):
     """
     removes the permissions of a module
     """
     module_name = module.get_name()
     db = Database()
     stmnt = "DELETE FROM RIGHTS WHERE RIG_NAME LIKE ? ;"
     db.query(stmnt, (module_name+"%",),commit=True)

 def cleanup_css_sessiontable(cls):
     """
     Cleans up old css filenames
     """
     db = Database()
     stmnt = "DELETE FROM CSSSESSION WHERE CSE_OUTDATED = 1 ;"
     db.query(stmnt, commit=True)
     return