Python db.admin_query函数代码示例

    def remove_user_from_group(self, user_name, group_name):
        """
        Removes user from group.
        Updates the published time of the project accordingly.

        :param str user_name: User name
        :param str group_name: Group name
        :raises InvalidPermissionState: User cannot be removed
        :raises DatabaseError: Query failure
        :raises ValueError: User not found
        """
        user = get_userstore().getUser(user_name)
        if not user:
            raise ValueError('User not found')

        # TODO: just check that there's TRAC_ADMIN left?
        # Checks that it is ok to remove user from group
        ug = self.get_all_user_groups()
        ug = [(user, group) for user, group in ug if not (user == user_name and group == group_name)]
        self.is_valid_group_members(user_groups=ug)

        group_name = group_name.encode('utf-8')
        group_id = self.get_group_id(group_name)
        self._cache.clear_user_groups(self.trac_environment_key)

        with admin_query() as cursor:
            cursor.callproc("remove_user_from_group", [user.id, group_id])

        self._update_published_time()

    def remove_user_from_group(self, user_name, group_name):
        """
        Removes user from group.

        :param str user_name: User name
        :param str group_name: Group name
        :raises InvalidPermissionState: User cannot be removed
        :raises DatabaseError: Query failure
        :raises ValueError: User not found
        """

        userstore = get_userstore()
        user = userstore.getUser(user_name)

        if not user:
            raise InvalidPermissionsState('Unknown user %s' % user_name)

        # Get the group
        group_name = group_name.encode('utf-8')
        group_id = self.get_group_id(group_name)
        if group_id is None:
            conf.log.exception("Group %s doesn't exists'" % group_name)

        self._cache.clear_user_groups(self.trac_environment_key)

        with admin_query() as cursor:
            cursor.callproc("remove_user_from_group", [user.id, group_id])

    def grant_permission_to_group(self, group_name, permission_name):
        """
        Grants permission to group.

        :param str group_name: Group name, will be created if does not exists
        :param str permission_name: Perm name, will be created if does not exists
        :raises InvalidPermissionState: Permission can not be granted
        :raises DatabaseError: Query failure
        """
        # check that this is valid change
        gp = self.get_all_group_permissions() + [(group_name, permission_name)]
        self.is_valid_group_members(group_permissions=gp)

        permission_id = get_permission_id(permission_name)

        # Create group if it doesn't exist
        group_name = group_name.encode('utf-8')
        group_id = self.get_group_id(group_name)
        if group_id is None:
            self.create_group(group_name)
            group_id = self.get_group_id(group_name)

        self._cache.clear_group_perms(self.trac_environment_key)

        with admin_query() as cursor:
            try:
                cursor.callproc("grant_permission_to_group", [group_id, permission_id])
            # User already exists in the group
            except MySQLdb.IntegrityError:
                conf.log.warning('Group %s already has permission: %s' % (group_name, permission_name))

    def get_categories_by_project(self, project_key, context_id):
        """
        Searches categories belonging in project
        :returns: A list of categories
        """

        and_context_id = ''
        if context_id:
            and_context_id = 'AND cat.context_id = %s'
        query = """SELECT cat.* FROM categories AS cat
                        INNER JOIN project_categories AS pc ON pc.category_key = cat.category_id
                        WHERE pc.project_key = %s {and_context_id}""".format(and_context_id=and_context_id)

        category_list = []
        with admin_query() as cursor:
            try:
                if context_id:
                    cursor.execute(query, (project_key, context_id))
                else:
                    cursor.execute(query, project_key)

                for row in cursor:
                    category_list.append(Category.from_sql_row(row))
            except:
                conf.log.exception("Exception. Failed searching project categories. Query('%s'), project_key %d." %
                                   (str(query), project_key))

        return category_list

    def get_expired_users(self, when=None):
        """
        Returns users that accounts are expired, or will soon expire (if when date is in future)

        :param datetime when: Date in future, other wise returns the accounts that are already expired
        :returns: List of user objects
        """
        users = []
        when = when or datetime.utcnow()

        query = """
        SELECT user.*
        FROM user
        LEFT JOIN user_status ON user_status.user_status_id = user.user_status_key
        WHERE
            user.expires <= %s AND
            LOWER(user_status.status_label) != 'banned'
        ORDER BY user.expires DESC
        """

        with admin_query() as cursor:
            cursor.execute(query, when)
            for row in cursor:
                users.append(self.sqlToUser(row))

        return users

    def get_project_counts_per_category(self, username):
        # Try from cache
        cache = ProjectCache.instance()
        items = cache.get_project_counts_per_category(username)
        if items:
            return items

        anon_et_al = "(%s)"
        if username != 'anonymous':
            anon_et_al = "('anonymous', %s)"
        # Query public project count / category
        query = """SELECT pc.category_key, COUNT(pc.project_key)
                    FROM project_categories AS pc
                    INNER JOIN project_user_visibility v ON v.project_id = pc.project_key
                    INNER JOIN user AS u ON u.user_id = v.user_id
                    WHERE u.username IN {anon_et_al}
                    GROUP BY pc.category_key;""".format(anon_et_al = anon_et_al)

        items = {}

        with admin_query() as cursor:
            try:
                cursor.execute(query, username)
                for row in cursor:
                    items[row[0]] = row[1]
            except Exception, e:
                conf.log.exception(
                    "Exception. Projects.get_project_counts_per_category failed with query '''%s'''." %
                    query)

    def from_operational(self, identifier):
        """ Read a project from operational database
        """
        query = """
        SELECT  p.environment_name AS identifier,
                p.project_name,
                u.username AS author,
                p.created,
                p.updated,
                p.published,
                p.project_id
        FROM projects AS p
        INNER JOIN user AS u ON u.user_id = p.author
        WHERE p.environment_name = '%s'""" % identifier

        row = []
        with admin_query() as cursor:
            try:
                cursor.execute(query)
                row = cursor.fetchone()
            except:
                conf.log.exception("Getting project from operational db failed. %s" % identifier)

        if not row:
            return None

        project = {'identifier': row[0],
                   'project_name': MySQLdb.escape_string(row[1]),
                   'author': MySQLdb.escape_string(row[2]),
                   'created': row[3],
                   'updated': row[4],
                   'published': row[5],
                   'project_key': row[6]}

        return project

def env_id(env_name):
    """
    Helper function for getting Trac `environment_id` or `environment_key` based
    on environment name.

    .. NOTE:: Avoid using! This one needs to be phased out.

        Use `trac_environment_key` from :class:`multiproject.common.projects.project`
        instead or even better switch to `project_id` !
    """

    query = "SELECT environment_id FROM trac_environment WHERE identifier = %s"
    row = None

    with admin_query() as cursor:
        try:
            cursor.execute(query, env_name)
            row = cursor.fetchone()
        except:
            # NOTE: this import must remain here or circular import will occur
            from multiproject.core.configuration import conf
            conf.log.exception("Didn't find environment id for %s" % env_name)

    if row:
        return row[0]

    return 0

    def downloads_dir(self):
        if self._downloads_dir_fetched:
            return self._downloads_dir

        self._downloads_dir_fetched = True

        new_downloads_dir = self._default
        memcache_key = None
        was_cached = True
        if new_downloads_dir is None:
            memcache_key = self._memcache_key()
            new_downloads_dir = self.mc.get(memcache_key)

        if new_downloads_dir is None:
            was_cached = False
            query = """
                SELECT value FROM `{0}`.system WHERE name = 'files_downloads_dir'
            """.format(self.env_name)
            try:
                with admin_query() as cursor:
                    cursor.execute(query)
                    for row in cursor:
                        new_downloads_dir = row[0]
            except Exception:
                conf.log.exception("Exception. Querying downloads dir failed.")
                raise TracError("Error while fetching downloads dir.")

        try:
            self._downloads_dir = self.validate_dir(new_downloads_dir)
            if not was_cached:
                self.mc.set(memcache_key, self._downloads_dir, self.DOWNLOADS_CACHE_TIME)
        except DownloadDirValidationException:
            self._downloads_dir = ''
        return self._downloads_dir

    def denied_protocols(self, storage_type):
        """ Returns a set of denied schemes
        """
        # Try from cache
        denied = self.cache.get_project_protocols(self.project_id, storage_type)
        if denied:
            return denied

        denied = []
        table = self._table_by_type(storage_type)

        query = """
        SELECT prt.scheme FROM `%s` AS dsp 
        INNER JOIN protocol AS prt 
           ON prt.protocol_id = dsp.protocol_key
        """ % table
        query += "WHERE dsp.project_key = %s"
        with admin_query() as cursor:
            try:
                cursor.execute(query, self.project_id)
                for row in cursor:
                    denied.append(row[0])
            except:
                conf.log.exception("Error occurred while reading project protocol list")
                raise

        self.cache.set_project_protocols(self.project_id, storage_type, set(denied))
        return set(denied)

def resolve_project_id(env_name):
    """
    Helper function for resolving project id based on name

    .. NOTE:: Avoid using! This one needs to be phased out.

        Use `project_id` from :class:`multiproject.common.projects.project` instead.
    """
    query = """
    SELECT project_id
    FROM projects
    WHERE environment_name = %s
    """
    row = None

    with admin_query() as cursor:
        try:
            cursor.execute(query, env_name)
            row = cursor.fetchone()
        except:
            # NOTE: this import must remain here or circular import will occur
            from multiproject.core.configuration import conf
            conf.log.exception("Failed to get project id with query: %s" % query)

    if row:
        return row[0]

    return 0

    def get_news_forum_by_name(self, name, first=False):
        """
        Get the forum name for news forum. News forum name is defined in method params. If parameter 
        'first' is true, get the first forum name if available

        :returns: The database name for news forum or None
        """

        forum_name = None

        if first is False:
            query = "SELECT name FROM `%s`.forum WHERE name = '%s'" % (self.env_name, name)
        else:
            query = "SELECT name FROM `%s`.forum ORDER BY id ASC LIMIT 1" % self.env_name   

        with db.admin_query() as cursor:
            try:
                cursor.execute(query)
                row = cursor.fetchone()
                if row:
                    forum_name = row[0]
            except Exception:
                self.log.exception("SQL query failed: %s" % query)
                raise

        return forum_name

    def project_environment_exists(self, env_name):
        """ Checks if a project with given identifier (env_name) exists
        """
        row = []
        query = """
        SELECT COUNT(project_id)
        FROM projects
        WHERE environment_name = %s
        """
        query_project_archived = """
        SELECT COUNT(orig_project_id)
        FROM project_archive
        WHERE environment_name = %s
        """

        with admin_query() as cursor:
            try:
                cursor.execute(query, env_name)
                row = cursor.fetchone()
                if bool(row[0]) == False:
                    cursor.execute(query_project_archived, env_name)
                    row = cursor.fetchone()

            except Exception, e:
                conf.log.exception(e)
                return False

    def grant_permission_to_group(self, group_name, permission_name):
        """
        Grants permission to group.
        Updates the published time of the project accordingly.

        :param str group_name: Group name, will be created if does not exists
        :param str permission_name: Perm name, will be created if does not exists
        :raises InvalidPermissionState: Permission can not be granted
        :raises DatabaseError: Query failure
        """
        # check that this is valid change
        gp = self.get_all_group_permissions() + [(group_name, permission_name)]
        self.is_valid_group_members(group_permissions=gp)

        permission_id = get_permission_id(permission_name)

        # Create group if it doesn't exist
        group_name = group_name.encode('utf-8')
        group_id = self.get_group_id(group_name)
        if group_id is None:
            self.create_group(group_name)
            group_id = self.get_group_id(group_name)

        self._cache.clear_group_perms(self.trac_environment_key)

        with admin_query() as cursor:
            cursor.callproc("grant_permission_to_group", [group_id, permission_id])

        self._update_published_time()

    def _get_summary(self):
        """
        Returns the summary statistics/numbers for members::

            {'total_count':123, 'active_count':89, 'passive_count':34}

        :returns: Summary in dict
        """
        active_within_months = 2
        query = """
        SELECT
            COUNT(u1.user_id) AS total_count,
            COUNT(u2.user_id) AS active_count
        FROM user AS u1
        LEFT JOIN (
            SELECT user_id
            FROM user
            WHERE last_login > NOW() - INTERVAL %s MONTH
        ) AS u2 ON u1.user_id = u2.user_id
        """
        summary = {}

        with admin_query(cursors.DictCursor) as cursor:
            cursor.execute(query, active_within_months)
            summary = cursor.fetchone()

            # Calculate passive number manually
            summary['passive_count'] = summary['total_count'] - summary['active_count']

        return summary