def get_participated_public_projects(self, username):
        """ Get public projects username has participated in
        """
        store = get_userstore()
        user = store.getUser(username)
        if not user:
            return []

        anon = store.getUser('anonymous')

        order = " ORDER BY projects.project_name"

        # We need projects where _both_ anonymous and the specified user exist
        query = """
        SELECT projects.environment_name AS name, projects.description AS description, projects.created AS date,
          '%(user_name)s' AS author, projects.project_name, projects.icon_name
        FROM projects
        INNER JOIN `group` ON group.trac_environment_key = projects.trac_environment_key
        INNER JOIN user_group ON user_group.group_key = group.group_id
        INNER JOIN `user` ON user_group.user_key = user.user_id
        WHERE user_group.user_key = %(user_id)d AND EXISTS
        (SELECT * FROM projects P
        INNER JOIN `group` ON `group`.trac_environment_key = P.trac_environment_key
        INNER JOIN user_group ON user_group.group_key = group.group_id
        WHERE user_group.user_key = %(anon_id)d AND projects.project_id = P.project_id)
        """ % {"user_name": safe_string(user.getDisplayName().encode('utf-8')),
               "user_id": safe_int(user.id), "anon_id": safe_int(anon.id)}
        query += order
        return self.__queryProjectsWithDescr(query)

    def get_all_users(self, limit=0, count=50, initial=None):
        """ List all users

            If no parameters given, lists first 50 users.
            If limit given, lists first 50 users from the limit.
            If initial given, lists only users having that initial.
        """
        query = "SELECT username, givenName, lastName, mail, mobile FROM user "
        query += "WHERE username NOT IN ('authenticated', 'anonymous') "
        if initial:
            query += "AND (username LIKE '" + safe_string(initial[0].upper()) + "%' "
            query += "OR username LIKE '" + safe_string(initial[0].lower()) + "%') "
        query += "ORDER BY username LIMIT %d,%d" % (safe_int(limit), safe_int(count))

        users = []
        with admin_query() as cursor:
            try:
                cursor.execute(query)
                for user in cursor:
                    s = {'username': user[0],
                         'first': user[1],
                         'last': user[2],
                         'email': user[3],
                         'mobile': user[4]}
                    users.append(s)
            except:
                conf.log.exception("Exception. Users.get_all_users query failed '''%s'''." % query)
                raise

        return users

    def public_project_count(self):
        """ Number of public projects
        """

        # Chances are that we get these from the cache
        anon = get_userstore().getUser('anonymous')
        auth = None #users.getUser('authenticated')

        users_in = []
        if anon:
            users_in.append(str(safe_int(anon.id)))
        if auth:
            users_in.append(str(safe_int(auth.id)))
        users_in_str = ','.join(users_in)

        query = ("SELECT count(DISTINCT project_id) FROM projects "
                 "INNER JOIN `group` ON `group`.trac_environment_key = projects.trac_environment_key "
                 "INNER JOIN user_group ON user_group.group_key = `group`.group_id "
                 "WHERE user_group.user_key IN(%s)" % users_in_str)

        count = 0
        with admin_query() as cursor:
            cursor.execute(query)
            row = cursor.fetchone()
            count = row[0]

        return count

    def move_category_to_new_parent(self, category_id, new_parent_id, all_categories = None):
        """
        Move a category (and its child categories) to new context,
        possibly setting the parent category to null.

        :param int category: category moved
        :param int new_parent_id: new parent id
        :param all_categories: equal to self.get_all_categories()
        """
        if not all_categories:
            all_categories = self.get_all_categories()
        # all_categories comes from database

        # Validate category_id
        category_id = safe_int(category_id)
        if not all_categories.has_key(category_id):
            raise Exception("No repositioned category found.")
        category = all_categories[category_id]
        parent_category = None
        # Validate new_parent_id
        new_parent_id = safe_int(new_parent_id)
        if not all_categories.has_key(new_parent_id):
            raise Exception("No new parent category found.")
        parent_category = all_categories[new_parent_id]

        must_update_context = False
        if category.parent == new_parent_id and parent_category.context == category.context:
            raise Exception("Category's context and parent are already as required.")

        # Prevent making eternal loops.
        is_sub_category = self._is_sub_category_or_self(new_parent_id, category_id, all_categories)

        if is_sub_category:
            raise Exception("Cannot move category under its sub category.")

        change_context_query = ''
        if parent_category.context != category.context:
            must_update_context = True
            change_context_query = self._change_context_query(category_id, all_categories)

        try:
            with admin_transaction() as cursor:
                if must_update_context:
                    cursor.execute(change_context_query, parent_category.context)
                cursor.execute("UPDATE `categories` "
                               "    SET `parent_id` = %s "
                               "  WHERE `category_id` = %s ", (new_parent_id, category_id))
        except Exception as e:
            conf.log.exception("Failed to change parent category of %s to be %d: %s",
                category.name, new_parent_id, e)
            raise Exception("Error when updating parent.")
        finally:
            cache = CategoryCache.instance()
            cache.clearAllCategories()

    def bind_category_project(self, project_key, category_key):
        """ Sets project to belong into given category
        """
        query = ("INSERT INTO project_categories (project_key, category_key) VALUES(%d, %d)" %
                 (safe_int(project_key), safe_int(category_key)))
        try:
            with admin_transaction() as cursor:
                cursor.execute(query)
        except:
            conf.log.exception("Failed to bind project %s into category %s" % (category_key, project_key))
            return False

        return True

    def move_category_to_root_of_context(self, category_id, new_context_id, all_categories = None):
        """
        Move a category (and its child categories) to new context (if not already there)
        and set the parent category to null.

        :param int category: category moved
        :param int new_context_id: new context
        :param all_categories: equal to self.get_all_categories()
        """

        if not all_categories:
            all_categories = self.get_all_categories()
        # all_categories comes from database

        # Validate new_context_id
        new_context_id = safe_int(new_context_id)
        context = self.get_context_by_id(new_context_id)
        if not context:
            raise Exception("Context was invalid.")

        # Validate category_id
        category_id = safe_int(category_id)
        if not all_categories.has_key(category_id):
            raise Exception("Category not found.")
        category = all_categories[category_id]

        must_change_context = True
        if category.context == new_context_id:
            if category.parent is None:
                raise Exception("Category is already a root category and has the required context.")
            else:
                must_change_context = False

        change_context_query = self._change_context_query(category_id, all_categories)

        try:
            with admin_transaction() as cursor:
                if must_change_context:
                    cursor.execute(change_context_query,
                        new_context_id)
                cursor.execute("UPDATE `categories` "
                               "    SET `parent_id` = NULL "
                               "  WHERE `category_id` = %s ", category_id)
        except:
            conf.log.exception("Failed to move category %s into context %d",
                category.name, new_context_id)
            raise Exception("Error when doing database transaction.")
        finally:
            cache = CategoryCache.instance()
            cache.clearAllCategories()

    def get_featured_projects(self, limit=None, count=None):
        """ List all featured projects
        """
        query = ("SELECT projects.*, project_selected.value AS priority FROM project_selected "
                 "INNER JOIN projects ON  project_selected.project_id = projects.project_id "
                 "ORDER BY priority ")
        if limit:
            if count:
                query += "LIMIT %d,%d" % (safe_int(limit), safe_int(count))
            else:
                query += "LIMIT %d" % safe_int(limit)

        projects = self.queryProjectObjects(query)
        return projects

    def get_newest_participated_projects(self, username, projectcount):
        """ Get those projects that user with 'username' has participated
            ordered by newest first, limited by projectcount
        """
        user = get_userstore().getUser(username)

        query = "SELECT projects.*, '" + safe_string(
            user.getDisplayName().encode('utf-8')) + "' FROM projects "
        query += "INNER JOIN `group` ON group.trac_environment_key = projects.trac_environment_key "
        query += "INNER JOIN user_group ON user_group.group_key = group.group_id "
        query += "INNER JOIN user ON user_group.user_key = user.user_id "
        query += "WHERE user_group.user_key = %d " % safe_int(user.id)
        query += "ORDER BY projects.created DESC LIMIT %d" % safe_int(projectcount)

        return self.__queryProjects(query)

    def unbind_category_project(self, project_key, category_key):
        """ Sets project NOT to belong into given category
        """
        params = (safe_int(project_key), safe_int(category_key))
        query = "DELETE FROM project_categories "
        query += "WHERE project_key = %d AND category_key = %d" % params

        try:
            with admin_transaction() as cursor:
                cursor.execute(query)
        except:
            conf.log.exception("Failed to unbind category %s from project %s" % (category_key, project_key))
            return False

        return True

    def _list_users(self, req):
        """
        Process the ajax request for fetching users from database. Require at least
        two characters in the name string before allowing any values. Disallow % character
        and allow _ only as a normal character, that is part of the username.
        """
        name = req.args.get('q', '')
        auth = safe_string(req.args.get('auth', '').lower())
        perm = safe_string(req.args.get('perm', '').upper())
        limit = safe_int(req.args.get('limit', '30'))
        status = safe_string(req.args.get('status', '').lower())
        raw_fields = safe_string(req.args.get('field', 'username').lower())
        fields = [field for field in raw_fields.split(',') if field.strip() in self.db_fields.keys()]

        # If no fields or % in query => not allowed
        if not fields or '%' in name:
            return send_json(req, '', status=403)

        # Allow underscore in names/query
        name = safe_string(name).replace('_', '\_')
        states = [stat.lower() for stat in status.split(',') if stat]

        # Do the query
        rows = self._query_users(req, query=name, fields=fields, states=states, auth=auth, perm=perm, limit=limit)

        # Construct response in JSON list format
        # Serialize datetime objects into iso format: 2012-05-15T09:43:14
        return send_json(req, rows)

    def _parse_args(self, args, content):
        """
        Parse args from incoming args or from content. Depending on which is set.
        If called as a macro, the content holds the arguments. Otherwise it's
        the args param that holds the arguments.

        In macro case, a tuple is returned, first element is list of arguments,
        second is a dict. We only support the dict format.
        """
        env_name = ""
        count = 0
        title = "Latest announcements"

        if args is None:
            args = parse_args(content)
            if len(args) > 1:
                args = args[1]

        if args is not None and "project" in args:
            env_name = args["project"]
            env_name = env_name.strip("\" '")
            env_name = env_name.encode("utf-8")
            env_name = safe_string(env_name)

        if args is not None and "count" in args:
            count = args["count"]
            count = safe_int(count)

        if args is not None and "title" in args:
            title = args["title"]
            title = title.strip('"')

        self.log.debug("Returning args: {0}".format((env_name, count, title)))

        return env_name, count, title

    def from_analytical(self, project_identifier, forum_id):
        """ Return user from analytical database
        """
        query = """
        SELECT forum_key, discussion_name, author, moderators, subscribers,
               subject, description, project_key, project_identifier, project_name
        FROM discussion_dim
        WHERE project_identifier = '%s' AND forum_key = %d
        AND VALID_TO IS NULL""" % (safe_string(project_identifier), safe_int(forum_id))

        row = []
        with analytical_query() as cursor:
            try:
                cursor.execute(query)
                row = cursor.fetchone()
            except:
                conf.log.exception(
                    "Failed reading discussion forum from analytical db. {project: %s, forum: %d}" %
                    (project_identifier, forum_id))

        if not row:
            return None

        project = {'forum_key': row[0],
                   'discussion_name': MySQLdb.escape_string(row[1]),
                   'author': MySQLdb.escape_string(row[2]),
                   'moderators': MySQLdb.escape_string(row[3]),
                   'subscribers': MySQLdb.escape_string(row[4]),
                   'subject': MySQLdb.escape_string(row[5]),
                   'description': MySQLdb.escape_string(row[6]),
                   'project_key': row[7],
                   'project_identifier': MySQLdb.escape_string(row[8]),
                   'project_name': MySQLdb.escape_string(row[9])
        }
        return project

    def updateUser(self, user):
        """
        Updates user but not a password.
        There is a separate method for updating password
        """
        self.__cache.clear_user_by_user(user)

        user.icon = safe_int(user.icon) or None

        # FIXME: Usernames can not be changed. Unnecessary update?
        query = '''
        UPDATE user
        SET
            username = %s, mail = %s, mobile = %s, givenName = %s, lastName = %s, icon_id = %s,
            authentication_key = %s, user_status_key = %s, created = %s, expires = %s, author_id = %s
        WHERE user_id = %s
        '''
        params = (
            user.username, user.mail, user.mobile, user.givenName.encode('utf-8'), user.lastName.encode('utf-8'),
            user.icon, str(user.authentication_key), str(user.status), user.created,
            user.expires, user.author_id, user.id
        )

        with admin_transaction() as cursor:
            try:
                cursor.execute(query, params)
            except:
                conf.log.exception("Exception: updating user failed '''%s'''." % query)
                raise

        self.storeUserOrganizations(user)
        return self.updateUserPreferences(user)

    def update_featured_projects(self, projects):
        """ update featured projects
        """
        with admin_transaction() as cursor:
            try:
                # First cleanup selected projects
                query = "DELETE FROM project_selected"
                cursor.execute(query)

                # Then update new projects
                if len(projects) > 0:
                    query = "INSERT INTO project_selected (project_id,value) VALUES "

                    line = "((SELECT projects.project_id FROM projects WHERE environment_name = '%s'), %d)"
                    lines = []

                    for project, value in projects:
                        lines.append(line % (safe_string(project), safe_int(value)))

                    query += ",".join(lines)

                    cursor.execute(query)
            except:
                conf.log.exception("Update featured project transaction failed %s" % query)
                raise

    def get_projects_with_rights(self, username, action):
        """
        :returns: a list of projects where user have right for "action".

        .. note::

           Permissions coming via LDAP groups are not included in the results

        """
        user = get_userstore().getUser(username)

        # Get subjects
        subjects = set([username])
        subjects.update(get_special_users(username))

        # Surround string elements with ' and join them with comma
        actions_str = ','.join("'%s'" % safe_string(p) for p in [action, 'TRAC_ADMIN'])
        subjects_str = ','.join(["'{0}'".format(safe_string(subject)) for subject in subjects])
        organizations_str = ','.join(["{0}".format(safe_int(org_key)) for org_key in user.organization_keys])

        query = ("SELECT DISTINCT projects.* FROM projects "
                 "INNER JOIN `group` ON group.trac_environment_key = projects.trac_environment_key "
                 "INNER JOIN group_permission ON group_permission.group_key = group.group_id "
                 "INNER JOIN action ON group_permission.permission_key = action.action_id "
                 "LEFT JOIN user_group ON user_group.group_key = group.group_id "
                 "LEFT JOIN user ON user.user_id = user_group.user_key "
                 "LEFT JOIN organization_group ON organization_group.group_key = group.group_id "
                 "WHERE (user.username IN(%s) "
                 "OR organization_group.organization_key IN(%s)) "
                 "AND action.action_string IN(%s) "
                 "ORDER BY projects.project_name" % (subjects_str, organizations_str, actions_str))

        return self.queryProjectObjects(query)

    def batch_insert(self, visibilities):
        query = "INSERT INTO project_user_visibility (project_id, user_id) VALUES "

        query += ",".join(["(%d,%d)" % (safe_int(visibility.project_id),
                                        safe_int(visibility.user_id))
                           for visibility in visibilities])

        with admin_transaction() as cursor:
            try:
                cursor.execute(query)
            except Exception as e:
                if self.verbose is not None:
                    print "Exception. In method batch_insert, the following query failed."
                    print query
                    print e
                conf.log.exception("Exception. ProjectUserVisibilityGenerator.batch_insert '''%s'''." % query)

    def from_operational(self, project_identifier, forum_id):
        # Alternative way to do this would be to open the connection straight into the project database...
        query = """
        SELECT id, name, author, moderators, subscribers, subject, description
        FROM %(project_identifier)s.forum WHERE id = %(forum_id)s
        """ % {'project_identifier': safe_string(project_identifier), 'forum_id': safe_int(forum_id)}

        dibo = None
        with admin_query() as cursor:
            try:
                cursor.execute(query)
                row = cursor.fetchone()
                if not row:
                    return None
                dibo = {'forum_key':row[0],
                        'discussion_name':row[1],
                        'author':row[2],
                        'moderators':row[3],
                        'subscribers':row[4],
                        'subject':row[5],
                        'description':row[6]
                }
            except:
                conf.log.exception("Failed reading a record from discussion dimension. %s" % str(dibo))

        pd = ProjectDimension()
        project = pd.from_operational(project_identifier)
        dibo['project_key'] = project['project_key']
        dibo['project_identifier'] = project['identifier']
        dibo['project_name'] = project['project_name']
        return dibo

    def get_project_news(self, limit=0, f_name=None):
        """
        Return all news for a project. The data is in form of a list of dicts, containing subject,
        author, time for news post, body of the news, number of comments to the news and an
        identifier.

        :param int limit: Optional limit to limit the results
        :returns: The project news, if any
        """
        news = []
        forum_name = self.news_forum_name

        # Check the first available forum
        if f_name is not None:
            if self.get_news_forum_by_name(f_name) is not None:
                forum_name = f_name
            else:
                forum_name = self.get_news_forum_by_name(None, True)
        elif self.get_news_forum_by_name(self.news_forum_name) is None:
            forum_name = self.get_news_forum_by_name(None, True)

        if not limit:
            query = '''
                SELECT t.subject, t.author, t.time, t.body, COUNT(m.id), t.id
                FROM `%(database)s`.topic t
                LEFT JOIN `%(database)s`.message m ON t.id = m.topic
                INNER JOIN `%(database)s`.forum f ON t.forum = f.id
                WHERE f.name = %%s
                GROUP BY t.id
                ORDER by t.time DESC
            ''' % {'database': self.env_name}
        else:
            query = '''
                SELECT t.subject, t.author, t.time, t.body, COUNT(m.id), t.id
                FROM `%(database)s`.topic t
                LEFT JOIN `%(database)s`.message m ON t.id = m.topic
                INNER JOIN `%(database)s`.forum f ON t.forum = f.id
                WHERE f.name = %%s
                GROUP BY t.id
                ORDER by t.time DESC
                LIMIT %(limit)d
            ''' % {'limit': db.safe_int(limit),
                   'database': self.env_name}

        with db.admin_query() as cursor:
            try:
                cursor.execute(query, (forum_name,))
                for row in cursor:
                    news.append({'subject': row[0],
                                 'author': row[1],
                                 'time': row[2],
                                 'body': row[3],
                                 'num_comments': row[4],
                                 'id': row[5]})
            except Exception:
                self.log.exception("SQL query failed: %s" % query)
                raise

        return news

    def get_anonymous_user_id(self):
        anon = get_userstore().getUser('anonymous')
        if anon:
            anon_id = safe_int(anon.id)
        else:
            anon_id = None

        return anon_id

 def edit_featured(self, context, download_ids):
     try:
         sql = "UPDATE download SET featured = 1 WHERE id IN (" + \
          ', '.join([to_unicode(safe_int(download_id)) for download_id in download_ids]) + ')'
         self.log.debug(sql)
         context.cursor.execute(sql)
     except:
         self.log.exception("Downloads featured operation failed, query was %s ", sql)