Esempio n. 1
0
    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
Esempio n. 2
0
    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)
Esempio n. 3
0
    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
Esempio n. 4
0
    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()
Esempio n. 5
0
    def unwatch_project(self, user_id, project_id):
        user_id = safe_int(user_id)
        project_id = safe_int(project_id)

        with admin_query() as cursor:
            try:
                cursor.callproc("unwatch_project", [user_id, project_id])
            except:
                conf.log.exception(
                    "Failed to remove project %d from user's %d watch list" %
                    (project_id, user_id))
Esempio n. 6
0
    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
Esempio n. 7
0
    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
Esempio n. 8
0
    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()
Esempio n. 9
0
    def watch_project(self, user_id, project_id, notification="immediate"):
        user_id = safe_int(user_id)
        project_id = safe_int(project_id)

        if notification not in self.notifications:
            notification = self.notifications[0]

        with admin_query() as cursor:
            try:
                cursor.callproc("watch_project",
                                [user_id, project_id, notification])
            except:
                conf.log.exception(
                    "Failed to add project %d into user's %d watch list" %
                    (project_id, user_id))
Esempio n. 10
0
    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)
Esempio n. 11
0
    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
Esempio n. 12
0
    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 _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
Esempio n. 14
0
    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
Esempio n. 15
0
    def add_and_bind_category(self, req, context_data):
        """
        Called by render_admin_panel
        """
        req.perm.require('TRAC_ADMIN')
        if req.method != 'POST':
            raise TracError('POST request required')

        category_name = req.args.get('category_name', '')
        l_url = req.args.get('license_url')
        if category_name and l_url:
            category_name = category_name.strip()
            l_url = l_url.strip()
            category_name = category_name + "#" + l_url

        cat = self.categ.get_category_by_name(category_name)
        if not cat:
            context_id = safe_int(req.args.get('context_id', '').strip())

            if not context_id or context_id not in context_data[
                    'context_by_id']:
                add_warning(req, _("Invalid context"))
                return

            if not category_name:
                add_warning(req, _("Invalid category"))
                return

            context = context_data['context_by_id'][context_id]

            if context.edit_type != context.EDIT_TYPE_ADD:
                add_warning(
                    req,
                    _("Adding category into context %(context_name) is not allowed",
                      context_name=context.name))
                return

            try:
                self.categ.add_category(category_name, category_name,
                                        context_id, None)
                cat = self.categ.get_category_by_name(category_name)
            except Exception as e:
                add_warning(
                    req,
                    _('Category %(what)s cannot be added.', what=category_name)
                    + _(str(e)))
                return

        project_key = context_data['project_key']
        if cat and project_key and self.categ.bind_category_project(
                project_key, cat.category_id):
            add_notice(
                req,
                _('Category %(what)s has been added.',
                  what=category_name.split("#")[0]))
        else:
            add_warning(
                req,
                _('Category %(what)s cannot be added.',
                  what=category_name.split("#")[0]))
Esempio n. 16
0
    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
Esempio n. 17
0
    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
Esempio n. 18
0
    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)
Esempio n. 20
0
    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)
Esempio n. 21
0
    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
Esempio n. 22
0
    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)
Esempio n. 23
0
    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
Esempio n. 25
0
 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)
Esempio n. 26
0
    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
Esempio n. 27
0
    def _is_sub_category_or_self(self, category_id, parent_category_id, all_categories = None):
        """
        Returns True if category_id is the same or sub category of the parent_category_id.
        Returns True when parent_category cannot be put as a child for category_id.
        Raises exception on error.

        .. Note:: Returns true also when category_id == parent_category_id

        :param int category_id: category id, must be valid category id!
        :param int parent_category_id: expected parent category id, must be valid category id!
        :param all_categories: equal to self.get_all_categories()
        :returns: bool is category sub category of parent category or the same
        """
        category_id = safe_int(category_id)
        parent_category_id = safe_int(parent_category_id)
        if category_id == parent_category_id:
            return True
        if not all_categories:
            all_categories = self.get_all_categories()
        category = all_categories[category_id]
        if not category.parent:
            return False

        parent_category = all_categories[category.parent]
        number_of_all_categories = len(all_categories)
        categories_handled = 0
        result = False
        while parent_category:
            # Success condition
            if parent_category.category_id == parent_category_id:
                result = True
                break
            categories_handled += 1
            # Error conditions
            if parent_category.category_id == category_id:
                self.log.warning("Error in category data: Category %s is a sub category for itself" % category_id)
                raise Exception("Category is a sub category for itself")
            if categories_handled > number_of_all_categories:
                self.log.warning("Error in category data: Eternal loop when trying to find parents of %s" % category_id)
                raise Exception("Category is a sub category for itself")
            # Break loop condition
            if not parent_category.parent:
                break
            parent_category = all_categories[parent_category.parent]
        return result
Esempio n. 28
0
    def get_child_projects(self):
        """ Returns projects that have repository of this project
        """
        from multiproject.common.projects import Projects

        query = "SELECT * FROM projects WHERE parent_id = %d" % safe_int(self.id)
        api = Projects()

        return api.queryProjectObjects(query)
Esempio n. 29
0
 def storeUserOrganizations(self, user):
     with admin_transaction() as cursor:
         try:
             query = "DELETE FROM user_organization WHERE user_key = " + str(safe_int(user.id))
             cursor.execute(query)
             if not user.organization_keys:
                 return
             query = "INSERT INTO user_organization (user_key, organization_key) VALUES "
             pairs = []
             for organization_id in user.organization_keys:
                 pairs.append("(" + str(safe_int(user.id)) + ", " +
                              str(safe_int(organization_id)) + ")")
             query += ",".join(pairs)
             conf.log.debug(query)
             cursor.execute(query)
         except:
             conf.log.exception("Exception. update of user organizations failed '''%s'''." % query)
             raise
Esempio n. 30
0
 def get_number_of_downloads(self, context, download_ids = None):
     sql = 'SELECT SUM(count) FROM download' + (download_ids and
       (' WHERE id in (' + ', '.join([to_unicode(safe_int(download_id)) for download_id
       in download_ids]) + ')') or '')
     self.log.debug(sql)
     context.cursor.execute(sql)
     for row in context.cursor:
         return row[0]
     return None
Esempio n. 31
0
 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)
Esempio n. 32
0
    def get_child_projects(self):
        """ Returns projects that have repository of this project
        """
        from multiproject.common.projects import Projects

        query = "SELECT * FROM projects WHERE parent_id = %d" % safe_int(
            self.id)
        api = Projects()

        return api.queryProjectObjects(query)
Esempio n. 33
0
    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)
Esempio n. 34
0
 def get_number_of_downloads(self, context, download_ids=None):
     sql = 'SELECT SUM(count) FROM download' + (
         download_ids and (' WHERE id in (' + ', '.join([
             to_unicode(safe_int(download_id))
             for download_id in download_ids
         ]) + ')') or '')
     self.log.debug(sql)
     context.cursor.execute(sql)
     for row in context.cursor:
         return row[0]
     return None
Esempio n. 35
0
    def get_projects_with_params(self, username, perm, namelike=None, categories=None):
        """ Returns a list of projects where user have right for "action".
        """
        categories = categories or []
        user = get_userstore().getUser(username)
        user_organization = user.organization_keys

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

        # Construct comma separated lists for queries
        actions_str = ','.join("'%s'" % safe_string(p) for p in [perm, 'TRAC_ADMIN'])
        subjects_str = ','.join("'%s'" % safe_string(subject) for subject in subjects)
        categories_str = ','.join("'%s'" % safe_string(cat) for cat in categories)

        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 ")

        if categories:
            query += ("INNER JOIN project_categories ON projects.project_id = project_categories.project_key "
                      "INNER JOIN categories ON categories.category_id = project_categories.category_key ")

        query += ("LEFT JOIN organization_group ON organization_group.group_key = group.group_id "
                  "WHERE (user.username IN (%s) " % subjects_str)

        if not user_organization:
            query += "OR organization_group.organization_key = NULL )"
        else:
            # List user organizations as safe int, separated with comma: (1,5,3,65)
            orc = lambda org_key: str(safe_int(org_key))
            query += "OR organization_group.organization_key IN (%s) ) " % ','.join(map(orc, user_organization))

        query += "AND action.action_string IN(" + actions_str + ") "

        if categories:
            query += "AND categories.category_name IN(" + categories_str + ") "

        if namelike:
            conditions = []
            search_strs = namelike.split(' ')
            for search in search_strs:
                if not search == '':
                    search = safe_string(search)
                    conditions.append("projects.project_name LIKE '%" + search + "%'")

            where = " OR ".join(conditions)
            query += "AND (" + where + ") "
        query += "ORDER BY projects.project_name"
        return self.queryProjectObjects(query)
Esempio n. 36
0
    def get_team(self):
        """ Returns a list of those users that have rights to project
        """
        query = ("SELECT DISTINCT user.* FROM user "
                 "INNER JOIN user_group ON user.user_id = user_group.user_key "
                 "INNER JOIN `group` ON user_group.group_key = group.group_id "
                 "WHERE group.trac_environment_key = %d "
                 "AND user.username NOT IN('anonymous', 'authenticated')" %
                 safe_int(self.trac_environment_key))

        userstore = get_userstore()
        return userstore.query_users(query)
Esempio n. 37
0
    def downloaded(self, context, download):
        """Called when a file is downloaded
        """
        store = conf.getUserStore()
        user = store.getUser(context.req.authname)

        db = self.env.get_db_cnx()
        cursor = db.cursor()

        query = "INSERT INTO download_log (release_id,user_id) VALUES(%u,%u)" % \
                  (safe_int(download['id']), safe_int(user.id))

        try:
            cursor.execute(query)
            db.commit()
        except:
            self.env.log.debug("Cannot update tracking data. query=[%s]" %
                               query)
        finally:
            cursor.close()
            db.close()
Esempio n. 38
0
    def get_team(self):
        """ Returns a list of those users that have rights to project
        """
        query = ("SELECT DISTINCT user.* FROM user "
                 "INNER JOIN user_group ON user.user_id = user_group.user_key "
                 "INNER JOIN `group` ON user_group.group_key = group.group_id "
                 "WHERE group.trac_environment_key = %d "
                 "AND user.username NOT IN('anonymous', 'authenticated')" %
                 safe_int(self.trac_environment_key))

        userstore = get_userstore()
        return userstore.query_users(query)
Esempio n. 39
0
    def _do_org_update(self):
        """
        Updates organizations from project.ini to database
        Organizations are in format::

            org.<type>.<identifier> = <backend>, <organization>

        Where:

        - type: Organization type: auths | email
        - identifier: Identifier for organization. Id for auths and mail domain for email
        - backend: Authentication backend
        - organization: Organization name

        """
        orgman = self.env[OrganizationManager]
        organizations = orgman.get_organizations()

        if not orgman.use_organizations:
            printout('Organizations are disabled. Nothing to do.')
            return

        if not organizations:
            printout(
                'No organizations were found from configuration. Nothing to do.'
            )
            return

        # Surround organizations with '
        organizations_sql = []
        for organization in sorted(organizations,
                                   key=lambda org: org['position']):
            organizations_sql.append("('{0}', {1})".format(
                safe_string(organization['name']),
                safe_int(organization['position'])))

        query = """
        INSERT IGNORE INTO organization (organization_name, sorting)
        VALUES {0}
        ON DUPLICATE KEY UPDATE sorting=VALUES(sorting)
        """.format(','.join(organizations_sql))

        with admin_transaction() as cursor:
            try:
                cursor.execute(query)
            except:
                logging.exception(
                    "Organization update failed (conf -> db) with query %s" %
                    query)
                printout('Failed to update organization info. Check the logs')
                return

        printout('Updated organization info into db successfully')
Esempio n. 40
0
    def search_project(self, keywords, category_ids, sub_page=1, limit=5):
        """ Search for projects with fulltext and categories
        """
        limit = safe_int(limit)
        limit_attr = {'limit_start': (safe_int(sub_page) - 1) * limit, 'limit': limit}

        select = "SELECT DISTINCT p.project_id,p.project_name,p.environment_name,p.description,"\
                 "p.author,p.created,p.updated,p.published,p.parent_id,p.icon_name FROM "\
                 "(SELECT DISTINCT * , CONCAT(project_name COLLATE utf8_general_ci,environment_name, description) as f FROM projects) AS p "

        wheres = []
        joins = []

        if category_ids:
            # Given categories
            search_cat_list = category_ids

            joins += ["INNER JOIN project_categories AS pc ON pc.project_key = p.project_id "]

            # Join id:s into in clause wrapped in quotes
            id_list = ', '.join([str(safe_int(id)) for id in search_cat_list])
            wheres += [" pc.category_key IN (%s)" % id_list]

        if keywords:
            # Escape SQL query. Escape also the special % and _ characters
            wheres += ["f LIKE '%{0}%'".format(safe_string(kw.replace('%', '\%').replace('_', '\_'))) for kw in keywords.split() if kw]

        if len(wheres) > 0:
            # Merge where statements
            where_str = " WHERE " + ' AND '.join(wheres)
        else:
            where_str = ""

        # Merge join statements
        join_str = " ".join(joins)

        attr_str = " ORDER BY IFNULL(p.published, p.created) DESC LIMIT %(limit_start)d, %(limit)d" % limit_attr
        query = select + join_str + where_str + attr_str

        return self.__queryProjectsWithDescr(query)
Esempio n. 41
0
    def downloaded(self, context, download):
        """Called when a file is downloaded
        """
        store = conf.getUserStore()
        user = store.getUser(context.req.authname)

        db = self.env.get_db_cnx()
        cursor = db.cursor()

        query = "INSERT INTO download_log (release_id,user_id) VALUES(%u,%u)" % (
            safe_int(download["id"]),
            safe_int(user.id),
        )

        try:
            cursor.execute(query)
            db.commit()
        except:
            self.env.log.debug("Cannot update tracking data. query=[%s]" % query)
        finally:
            cursor.close()
            db.close()
Esempio n. 42
0
    def __init__(self, project):
        Command.__init__(self)
        self.short_name = project.env_name
        self.long_name = project.project_name
        self.author = project.author
        self.description = project.description
        self.name = "ListUpProject"

        self.project = project
        # Parent is null or project id
        self.parent = None
        if project.parent_id:
            self.parent = safe_int(project.parent_id)
Esempio n. 43
0
    def __init__(self, project):
        Command.__init__(self)
        self.short_name = project.env_name
        self.long_name = project.project_name
        self.author = project.author
        self.description = project.description
        self.name = "ListUpProject"

        self.project = project
        # Parent is null or project id
        self.parent = None
        if project.parent_id:
            self.parent = safe_int(project.parent_id)
Esempio n. 44
0
    def updateUserPreferences(self, user):
        with admin_transaction() as cursor:
            try:
                for item, value in user.preferences.iteritems():
                    query = "INSERT INTO user_preference (user_key, item, value) VALUES ("
                    query += (str(safe_int(user.id)) + ", '" + safe_string(item)
                              + "', '" + safe_string(str(value)) + "') ")
                    query += "ON DUPLICATE KEY UPDATE value = '" + safe_string(str(value)) + "'"
                    cursor.execute(query)
            except:
                conf.log.exception("Exception. update of user preferences failed '''%s'''." % query)
                raise

        return True
Esempio n. 45
0
    def get_category_by_name_filter(self, name_filter, limit=None, context_ids=None):
        """
        Returns a list of categories matching filter.
        If context_ids are provided, search is restricted to the corresponding contexts.

        :returns: list of matched Categories
        """
        default_limit = 100

        if name_filter:
            name_filter = name_filter.lower()
        and_context_ids = ''
        if context_ids and len(context_ids) < 20:
            and_context_ids = ' AND context_id IN ({0}) '.format(
                ','.join([str(safe_int(id)) for id in context_ids])
            )

        limit = safe_int(limit) or default_limit

        query = """ SELECT * FROM categories
                    WHERE LOWER(category_name) LIKE '%{0}%'
                        {1}
                    LIMIT {2}""".format(safe_string(name_filter), and_context_ids, limit)

        categories = []
        conf.log.debug("Querying categories with query: %s", query)
        try:
            with admin_query() as cursor:
                cursor.execute(query)

                for row in cursor:
                    categories.append(Category.from_sql_row(row))
        except Exception:
            conf.log.exception(
                "Exception. Query failed when searching category by name_filter('%s') from database." % str(name_filter))

        return categories
Esempio n. 46
0
    def get_context_by_id(self, context_id):
        """ Returns a context object by id
        """
        context_id = safe_int(context_id)
        context = None
        with admin_query() as cursor:
            try:
                cursor.execute("SELECT * FROM contexts WHERE context_id = %s", context_id)
                row = cursor.fetchone()
                if row:
                    context = Context.from_sql_row(row)
            except:
                conf.log.exception("Exception. Was not able to read context(%d) from database.", context_id)

        return context
Esempio n. 47
0
    def add_category(self, name, description, context, parent):
        """
        Add a new category.
        Raises Exception on error.
        """
        parent = safe_int(parent)
        name = str(name)
        context = safe_int(context)

        query = ("INSERT INTO `categories` (`category_name`, `description`, `parent_id`, `context_id`)"
                " VALUES(%s, %s, %s, %s);")
        existing_category = self.get_category_by_name(name)
        if existing_category:
            raise Exception("A category with the same name already exists.")

        try:
            with admin_transaction() as cursor:
                cursor.execute(query, (name, description, parent, context))
        except:
            conf.log.exception("Error when adding category.")
            raise Exception("Error when adding category.")
        finally:
            cache = CategoryCache.instance()
            cache.clearAllCategories()
Esempio n. 48
0
    def get_projects_by_user(self, user_id):
        user_id = safe_int(user_id)

        watched_projects = []
        with admin_query() as cursor:
            try:
                query = "SELECT * FROM watchlist WHERE user_key = %s"
                cursor.execute(query, user_id)

                for row in cursor:
                    p = Watch.from_sql_row(row)
                    watched_projects.append(p)
            except:
                conf.log.exception(
                    "Exception: Watchlist: get_projects_by_user query failed.")

        return watched_projects
Esempio n. 49
0
    def get_organization_name(self, organization_id):
        organization_id = safe_int(organization_id)
        organization_name = self._cache.get_organization_name(organization_id)
        if organization_name is not None:
            return organization_name

        with admin_query() as cursor:
            try:
                query = "SELECT organization_name FROM organization WHERE organization.organization_id = %s"
                cursor.execute(query, organization_id)
                row = cursor.fetchone()
                if row:
                    organization_name = row[0]
                    self._cache.set_organization_name(organization_id, organization_name)
            except:
                conf.log.exception("Exception. get_organization_name(%s) failed" % str(organization_id))

        return organization_name
Esempio n. 50
0
    def get_category_by_id(self, category_id):
        """
        :returns: Category
        """
        category = None
        category_id = safe_int(category_id)

        with admin_query() as cursor:
            try:
                cursor.execute("SELECT * FROM categories WHERE category_id = %s", category_id)
                row = cursor.fetchone()
                if row:
                    category = Category.from_sql_row(row)
            except:
                conf.log.exception(
                    "Exception. Query failed when searching category name by category_id('%s')." % str(category_id))

        return category
Esempio n. 51
0
    def _change_context_query(self, category_id, all_categories = None):
        """
        Returns a string like "UPDATE categories SET context_id = %s WHERE ... " for
        updating the contexts of the category and its child categories.

        If category doesn't exist, returns "".

        :param category_id: category id in question, must be valid category id!
        :param all_categories: equal to self.get_all_categories()
        :returns: sql_query
        """
        category_id = safe_int(category_id)
        if not all_categories:
            all_categories = self.get_all_categories()
        # all_categories comes from database
        format_id = lambda id_as_int: str(int(id_as_int))

        if not category_id or not all_categories.has_key(category_id):
            return "", _("Category was not found")
        category = all_categories[category_id]

        # Get children ids recursively, since their contexts must be updated also
        recursive_children_ids = [format_id(category.category_id)]
        not_handled_categories = category.children

        number_of_all_categories = len(all_categories)
        categories_handled = 0

        while len(not_handled_categories) > 0:
            new_not_handled_categories = []
            for category in not_handled_categories:
                # Filter and map here, if needed
                recursive_children_ids.append(format_id(category.category_id))
                categories_handled += 1
                if categories_handled > number_of_all_categories:
                    self.log.warning("Error in category data: Eternal loop when trying to find children of %s" % category_id)
                    raise Exception("Inconsistent data: there was eternal recursion.")
                new_not_handled_categories.extend(category.children)
            not_handled_categories = new_not_handled_categories

        query = ("UPDATE `categories` "
                 "    SET `context_id` = %%s "
                 "  WHERE `category_id` IN (%s) " % (', '.join(recursive_children_ids)))
        return query
Esempio n. 52
0
    def process_request(self, req):
        """ Process image request

            Image is fetch based on username or user_id if both given, id is
            used on query

            Example query parts of request:
            ?username=jdoe
            ?user_id=238
        """
        user_id = req.args.get('user_id')
        username = req.args.get('username')

        # Prefer user id if available
        if username:
            where = "user.username = '******'" % MySQLdb.escape_string(username)
        if user_id:
            where = "user.user_id = %s" % safe_int(user_id)

        content = ""
        content_type = "/image/png"

        query = ("SELECT user_icon.* FROM user_icon "
                 "INNER JOIN user ON user.icon_id = user_icon.icon_id "
                 "WHERE %s" % where)

        with admin_query() as cursor:
            try:
                cursor.execute(query)
                row = cursor.fetchone()
                if row:
                    content = row[1]
                    content_type = row[2]
            except:
                self.log.exception(
                    "IconRenderer.process_request failed with query %s" %
                    query)

        # Create response
        req.send_response(200)
        req.send_header('Content-Type', content_type)
        req.send_header('Content-Length', len(content))
        req.end_headers()
        req.write(content)
Esempio n. 53
0
    def get_categories(self, context_id):
        """ Returns a list of Category class instances
        ### Used only in xmlrpc
        """
        context_id = safe_int(context_id) or 0

        categories = []
        with admin_query() as cursor:
            try:
                query = "SELECT * FROM categories WHERE context_id = %d" % context_id
                cursor.execute(query)

                for row in cursor:
                    cat = Category.from_sql_row(row)
                    categories.append(cat)
            except:
                conf.log.exception(
                    "Was not able to read categories of context('%s') from database." % str(context_id))

        return sorted(categories, key=lambda x: x.name)
Esempio n. 54
0
    def get_resource_description(self,
                                 resource,
                                 format='default',
                                 context=None,
                                 **kwargs):
        # Create context.
        context = Context('downloads-core')
        db = self.env.get_db_cnx()
        context.cursor = db.cursor()

        # Get download from ID.
        api = self.env[DownloadsApi]
        download = api.get_download(context, safe_int(resource.id))

        if format == 'compact':
            return download['file']
        elif format == 'summary':
            return '(%s) %s' % (pretty_size(
                download['size']), download['description'])
        return download['file']
    def upgrade(self):
        if self.applied():
            print "Migration already applied".rjust(12)
            return True

        auth_store = CQDEAuthenticationStore.instance()
        id = auth_store.get_authentication_id(auth_store.LOCAL)
        if not id:
            print "Error: local authentication id not found: name is %s" % auth_store.LOCAL
            return False

        queries = [
            """
        UPDATE `user`
           SET SHA1_PW = 'invalidNonLocalUserPwHash'
         WHERE authentication_key <> %s
        """ % safe_int(id)
        ]

        return self.manager.db_upgrade(queries)