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 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 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 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))
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 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 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 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))
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 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
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 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]))
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 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 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 _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 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 _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 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)
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 _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
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)
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
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
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)
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)
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 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
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)
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)
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()
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)
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')
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)
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()
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)
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)
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
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
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
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()
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
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
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
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
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)
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)
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)