def createIcon(self, icon):
""" Creates icon for user based on icon sent on create form
TODO: This should be on MySQLUserStore
"""
# FIXME: Move user icon into filesystem for better performance, similar to project icon
self.icon = None
if isinstance(icon, unicode) or not icon.filename:
return
content_type = icon.type
with admin_transaction() as cursor:
try:
cursor.execute("INSERT INTO user_icon VALUES(null, '" + safe_string(
icon.value) + "', '" + safe_string(content_type) + "')")
# Resolve last inserted icon id
cursor.execute("SELECT last_insert_id() FROM user_icon")
row = cursor.fetchone()
if row:
if row[0] != 0:
# If nonzero is returned, row was successfully added
self.icon = row[0]
except:
conf.log.exception("Exception. Failed creating icon.")
raise
def get_user_tasks(self, username):
"""
Method for querying users tasks in a specific project context
Gives url to ticket and it's summary
"""
env_url = conf.getEnvironmentUrl(self.env_name) + "/ticket/"
# Base query
query = (
"SELECT concat('%(env_url)s', tc.id) AS url, tc.summary, tc.description, tc.priority, tc.time, "
"`enum`.`value` FROM `%(project)s`.ticket AS tc "
"INNER JOIN `%(project)s`.`enum` ON `enum`.`name` = tc.priority AND `enum`.`type` = 'priority' "
"WHERE tc.owner = '%(user)s' AND tc.status <> 'closed'" % {
'project': self.env_name,
'env_url': safe_string(env_url),
'user': safe_string(username)
})
# Retrieve data
rows = []
with admin_query() as cursor:
try:
cursor.execute(query)
rows = cursor.fetchall()
except:
conf.log.exception(
"Exception. Project.get_user_tasks query failed. '''%s'''"
% query)
return rows
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 _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_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 get_user_tasks(self, username):
"""
Method for querying users tasks in a specific project context
Gives url to ticket and it's summary
"""
env_url = conf.getEnvironmentUrl(self.env_name) + "/ticket/"
# Base query
query = ("SELECT concat('%(env_url)s', tc.id) AS url, tc.summary, tc.description, tc.priority, tc.time, "
"`enum`.`value` FROM `%(project)s`.ticket AS tc "
"INNER JOIN `%(project)s`.`enum` ON `enum`.`name` = tc.priority AND `enum`.`type` = 'priority' "
"WHERE tc.owner = '%(user)s' AND tc.status <> 'closed'" %
{'project': self.env_name,
'env_url': safe_string(env_url),
'user': safe_string(username)})
# Retrieve data
rows = []
with admin_query() as cursor:
try:
cursor.execute(query)
rows = cursor.fetchall()
except:
conf.log.exception("Exception. Project.get_user_tasks query failed. '''%s'''" % query)
return rows
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_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 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_user_task_sums(self, username):
""" Method for querying user task sums (total tickets and closed tickets)
"""
# Build query
query = ("SELECT tc.status, COUNT(*) "
"FROM `{0}`.ticket AS tc "
"WHERE tc.owner = %s "
"GROUP BY tc.status").format(safe_string(self.env_name))
# Retrieve data
rows = []
with admin_query() as cursor:
try:
cursor.execute(query, username)
rows = cursor.fetchall()
except:
conf.log.exception(
"Exception. Project.get_user_task_sums query failed. '''%s'''"
% query)
# go through tasks
total = 0
closed = 0
for row in rows:
if row[0] == 'closed':
closed = row[1]
total += row[1]
return total, closed
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 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 from_operational(self, project_identifier, forum_id):
# Alternative way to do this would be to open the connection straight into the project database...
query = """
SELECT id, name, author, moderators, subscribers, subject, description
FROM %(project_identifier)s.forum WHERE id = %(forum_id)s
""" % {'project_identifier': safe_string(project_identifier), 'forum_id': safe_int(forum_id)}
dibo = None
with admin_query() as cursor:
try:
cursor.execute(query)
row = cursor.fetchone()
if not row:
return None
dibo = {'forum_key':row[0],
'discussion_name':row[1],
'author':row[2],
'moderators':row[3],
'subscribers':row[4],
'subject':row[5],
'description':row[6]
}
except:
conf.log.exception("Failed reading a record from discussion dimension. %s" % str(dibo))
pd = ProjectDimension()
project = pd.from_operational(project_identifier)
dibo['project_key'] = project['project_key']
dibo['project_identifier'] = project['identifier']
dibo['project_name'] = project['project_name']
return dibo
def get_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_user_task_sums(self, username):
""" Method for querying user task sums (total tickets and closed tickets)
"""
# Build query
query = ("SELECT tc.status, COUNT(*) "
"FROM `{0}`.ticket AS tc "
"WHERE tc.owner = %s "
"GROUP BY tc.status").format(safe_string(self.env_name))
# Retrieve data
rows = []
with admin_query() as cursor:
try:
cursor.execute(query, username)
rows = cursor.fetchall()
except:
conf.log.exception("Exception. Project.get_user_task_sums query failed. '''%s'''" % query)
# go through tasks
total = 0
closed = 0
for row in rows:
if row[0] == 'closed':
closed = row[1]
total += row[1]
return total, closed
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_posts(self, user, projects):
#project_name, subject, body, createtime
""" Get posts
"""
posts = []
query = None
if self.env.project_identifier == "home":
return posts
with trac_db_query(self.env) as cursor:
for prj in projects:
query = ("SELECT id, forum, 0, time, subject, body FROM `%(dbname)s`.`topic` "
"WHERE author = '%(user)s' "
"UNION ALL "
"SELECT m.id, m.forum, m.topic, m.time, t.subject, m.body FROM "
"`%(dbname)s`.`message` m, `%(dbname)s`.`topic` t "
"WHERE m.author = '%(user)s' AND m.topic = t.id" %
{'dbname': safe_string(prj.env_name),
'user': safe_string(user)})
try:
cursor.execute(query)
for row in cursor:
posts.append({
'project': prj,
'id': row[0],
'forum_id': row[1],
'topic_id': row[2],
'time': to_datetime(row[3]),
'subject': unicode((row[4] != '') and row[4] or '<no subject>'),
'body': unicode(row[5]),
'type': (row[2] == 0) and 'NEWTOPIC' or 'POST'
})
except:
self.log.exception(
"MyProjectsModule._get_posts query failed for project %s with query: '''%s'''" %
(prj.env_name, query))
import operator
posts.sort(key = operator.itemgetter('time'), reverse = True)
return posts
def undo(self):
# Remove only if command was executed successfully
if self.success:
with admin_transaction() as cursor:
try:
# Remove database
cursor.execute("DROP DATABASE `{0}`".format(safe_string(self.dbname)))
except Exception:
conf.log.exception('Failed to drop database: {0}'.format(self.dbname))
return False
return True
def undo(self):
# Remove only if command was executed successfully
if self.success:
with admin_transaction() as cursor:
try:
# Remove database
cursor.execute("DROP DATABASE `{0}`".format(safe_string(self.dbname)))
except Exception:
conf.log.exception('Failed to drop database: {0}'.format(self.dbname))
return False
return True
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 update_last_login(username):
""" Updates user's last login timestamp to user table
"""
if not username:
return
query = "UPDATE user SET last_login=NOW() WHERE user.username='******'" % safe_string(username)
with admin_transaction() as cursor:
try:
cursor.execute(query)
except:
conf.log.exception("Query failed: %s" % query)
raise
def _expire_cookie(self, req):
""" Instruct the user agent to drop the auth cookie by setting the
"expires" property to a date in the past.
"""
if req.incookie.has_key(COOKIE_AUTH):
cache = UserCache.instance()
sql_safe_cookie_value = safe_string(req.incookie[COOKIE_AUTH].value)
cache.clearUserCookieName(sql_safe_cookie_value)
# Remove cached cookie
self.cookie.remove(sql_safe_cookie_value)
# Create cookie by setting expiration to past
self._set_outcookie(req, cookie='', expires=(datetime.utcnow() - timedelta(10000)))
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 do(self):
# Create db (schema)
with admin_transaction() as cursor:
# Create database
try:
query = """
CREATE DATABASE `{0}`
DEFAULT CHARACTER SET utf8 COLLATE utf8_bin
""".format(safe_string(self.dbname))
cursor.execute(query)
except:
conf.log.exception("Error occurred on creating database! %s" % query)
return False
self.success = True
return True
def get_authored_projects(self, user):
"""
Get projects that are created by given user
:param User user: User who is author of the project
:returns: List of project objects
"""
query = """
SELECT projects.*
FROM projects
INNER JOIN user ON projects.author = user.user_id
WHERE user.username = '******'
ORDER BY project_name
""".format(safe_string(user.username))
return self.queryProjectObjects(query)
def do(self):
# Create db (schema)
with admin_transaction() as cursor:
# Create database
try:
query = """
CREATE DATABASE `{0}`
DEFAULT CHARACTER SET utf8 COLLATE utf8_bin
""".format(safe_string(self.dbname))
cursor.execute(query)
except:
conf.log.exception("Error occurred on creating database! %s" % query)
return False
self.success = True
return True
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 get_archived_project(self, project_archive_id):
"""
Get project from archive
:param int project_archive_id: Id of the project archive
:returns: The archived project if found (one), otherwise None
"""
query = """
SELECT * FROM project_archive
WHERE project_archive_id = {0}
LIMIT 1
""".format(safe_string(str(project_archive_id)))
projects = self.query_archived_projects(query)
if len(projects) == 1:
return projects[0]
return None
def get_recipients(self, resid):
""" List all admin emails in to field
"""
cc = []
to = []
# Wrap and escape admins in quotes
admins = CQDEPermissionStore(env=self.env).get_users_with_permissions(['TRAC_ADMIN'])
recipients = ", ".join(["'%s'" % safe_string(admin) for admin in admins])
# Query for admin email addresses
with admin_transaction() as cursor:
query = "SELECT mail FROM user WHERE username IN ({0})".format(recipients)
try:
cursor.execute(query)
to = [row[0] for row in cursor]
except:
self.env.log.exception("Exception. MembershipApi.get_recipients query failed. '''%s'''" % query)
return to, cc
def get_nonbrowsable_context_ids(self):
""" Return a list of those contexts that are not seen as categories
by user but as a metadata related with project.
Like Natural language, License, or Development status.
"""
cntx_in = ', '.join(["'%s'" % safe_string(ctx) for ctx in conf.non_browsable_contexts])
query = "SELECT context_id FROM contexts WHERE context_name IN (%s)" % cntx_in
cntxts = []
with admin_query() as cursor:
try:
cursor.execute(query)
for row in cursor:
cntxts.append(row[0])
except:
conf.log.exception("Getting non-browsable context id's failed with query '%s'" % query)
return cntxts
def get_archived_project(self, project_archive_id):
"""
Get project from archive
:param int project_archive_id: Id of the project archive
:returns: The archived project if found (one), otherwise None
"""
query = """
SELECT * FROM project_archive
WHERE project_archive_id = {0}
LIMIT 1
""".format(
safe_string(str(project_archive_id))
)
projects = self.query_archived_projects(query)
if len(projects) == 1:
return projects[0]
return None
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 get_emails(self, usernames):
if not usernames:
return []
usernames_wrapped = []
for username in usernames:
usernames_wrapped.append("'" + safe_string(username) + "'")
usernames_str = ','.join(usernames_wrapped)
mails = []
query = "SELECT DISTINCT mail FROM user WHERE username IN (" + usernames_str + ")"
with admin_query() as cursor:
try:
cursor.execute(query)
for row in cursor:
if '@' in row[0]:
mails.append(row[0])
except:
conf.log.exception("Exception. Query failed when getting emails '''%s'''" % query)
raise
return mails
def _get_name_for_cookie(self, req, cookie, send_cookie = True):
""" Fetch cookie->username from database based on cookie value
"""
cache = UserCache.instance()
sql_safe_cookie_value = safe_string(cookie.value)
active = self.cookie.get(sql_safe_cookie_value)
name = cache.getUserCookieName(sql_safe_cookie_value)
if name:
if not active:
if send_cookie:
self._set_outcookie(req, sql_safe_cookie_value)
self.cookie.add(sql_safe_cookie_value)
return name
row = None
with admin_query() as cursor:
try:
cursor.execute("SELECT name FROM auth_cookie WHERE cookie=%s", (sql_safe_cookie_value,))
row = cursor.fetchone()
except Exception:
self.log.exception("Failed to get the name for the cookie")
if not row:
# The cookie is invalid (or has been purged from the database), so
# tell the user agent to drop it as it is invalid
if send_cookie:
self._expire_cookie(req)
return None
if not active:
if send_cookie:
self._set_outcookie(req, sql_safe_cookie_value)
self.cookie.add(sql_safe_cookie_value)
cache.setUserCookieName(sql_safe_cookie_value, row[0])
return row[0]
def get_messages_with_query(cls, user_id, query, limit=30):
"""
Retrieve messages based on query that is matched with:
:param int user_id: Id of the user whose messages are wanted to be retrieved
:param str query:
Query value matching with fields:
- username
- given name
- last name
- message content
:param int limit: Maximum number of messages to fetch
:returns: List of message objects matching with the query
"""
messages = []
# Query: match with username and message
sql = '''
SELECT *, CONCAT_WS(' ',
m.content, m.env, sender.givenName, sender.lastName, sender.username, receiver.username
) COLLATE utf8_general_ci AS query
FROM message AS m
LEFT JOIN message_group_recipient AS mgr ON m.message_group_id = mgr.message_group_id
LEFT JOIN user AS sender ON sender.user_id = mgr.user_id
LEFT JOIN user AS receiver ON receiver.user_id = mgr.user_id
WHERE mgr.user_id = %s
HAVING query LIKE '%%{0}%%'
ORDER BY m.created DESC
LIMIT %s
'''.format(safe_string(query))
with admin_query(cursors.DictCursor) as cursor:
cursor.execute(sql, (user_id, limit))
messages = [cls.to_object(row) for row in cursor.fetchall()]
return messages
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 from_operational(self, project_identifier, forum_id):
# Alternative way to do this would be to open the connection straight into the project database...
query = """
SELECT id, name, author, moderators, subscribers, subject, description
FROM %(project_identifier)s.forum WHERE id = %(forum_id)s
""" % {
'project_identifier': safe_string(project_identifier),
'forum_id': safe_int(forum_id)
}
dibo = None
with admin_query() as cursor:
try:
cursor.execute(query)
row = cursor.fetchone()
if not row:
return None
dibo = {
'forum_key': row[0],
'discussion_name': row[1],
'author': row[2],
'moderators': row[3],
'subscribers': row[4],
'subject': row[5],
'description': row[6]
}
except:
conf.log.exception(
"Failed reading a record from discussion dimension. %s" %
str(dibo))
pd = ProjectDimension()
project = pd.from_operational(project_identifier)
dibo['project_key'] = project['project_key']
dibo['project_identifier'] = project['identifier']
dibo['project_name'] = project['project_name']
return dibo
def get_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_recipients(self, resid):
""" List all admin emails in to field
"""
cc = []
to = []
# Wrap and escape admins in quotes
admins = CQDEPermissionStore(env=self.env).get_users_with_permissions(
['TRAC_ADMIN'])
recipients = ", ".join(
["'%s'" % safe_string(admin) for admin in admins])
# Query for admin email addresses
with admin_transaction() as cursor:
query = "SELECT mail FROM user WHERE username IN ({0})".format(
recipients)
try:
cursor.execute(query)
to = [row[0] for row in cursor]
except:
self.env.log.exception(
"Exception. MembershipApi.get_recipients query failed. '''%s'''"
% query)
return to, cc
def __init__(self, env_name):
conf = Configuration.instance()
self.log = conf.log
self.news_forum_name = conf.news_forum_name
self.env_name = db.safe_string(env_name)
def __init__(self, project):
Command.__init__(self)
self.dbname = safe_string(project.env_name)
self.name = "CreateTracDatabase"
def get_projects_with_params(self,
username,
perm,
namelike=None,
categories=None):
"""
:returns: a list of archived projects where user have right for permission (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 pa.* FROM project_archive AS pa
INNER JOIN `group` ON group.trac_environment_key = (
SELECT environment_id
FROM trac_environment
WHERE identifier = pa.environment_name
)
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
"""
# NOTE! When project is archived/removed, the category information is also removed
if categories:
query += (
"INNER JOIN project_categories ON pa.orig_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("pa.project_name LIKE '%" + search +
"%'")
where = " OR ".join(conditions)
query += "AND (" + where + ") "
query += "ORDER BY pa.project_name"
return self.query_archived_projects(query)
def _query_users(self, req, query, fields, states=None, auth=None, perm=None, limit=None):
"""
Implements the full search based on given parameters.
Maximum number of results is 30
:param str query: Search query matching into fields
:param list fields:
Name of fields where to search.
.. NOTE:: Fields are combined for search, in provided order
:param list states: Optional list of state names to include in search
:param str auth: Optional name of authentication backend/organization to limit down the results
:returns: List of results (each result presented as dictionary
"""
rows = []
home_perm = PermissionCache(HomeProject().get_env(), username=req.authname)
default_limit = 30
limit = int(limit or default_limit)
# Search terms shorter than 2 - except for the persons with USER_AUTHOR perms
if len(query) < 2 and 'USER_AUTHOR' not in home_perm:
return []
# Validate limit
if limit > default_limit and 'USER_AUTHOR' not in home_perm:
limit = default_limit
# NOTE1: Fields are validated and escaped when reading from the request
# Construct where: join fields so that we can search from all of them
# NOTE2: Fields type other that varchar cannot be collated
char_fields = [self.db_fields[field] for field in fields if field not in self.nonchar_fields]
where = ["WHERE CONCAT_WS(' ', {0}) COLLATE utf8_general_ci LIKE '%{1}%'"
.format(','.join(char_fields), query.lower())]
# Do not list special users
where.append('u.username NOT IN ("anonymous", "authenticated")')
# Limit down the results based on authentication method
if auth:
where.append("LOWER(auth.method) = '{0}'".format(auth))
# Limit down the results based on status key
if states:
# Handle special status: expired
if 'expired' in states:
where.append("(u.expires IS NOT NULL AND u.expires <= NOW())")
states.remove('expired')
# If also other type of states defined
if states:
where.append("LOWER(us.status_label) IN ({0})".format(','.join(["'%s'" % safe_string(state) for state in states])))
# Limit down per permissions
# NOTE: This does not really check the Trac resource permission (it would be far too slow), just check the author_id
# FIXME: Do proper check and cache permissions results do proper check?
if perm and perm != 'USER_VIEW':
# Limit results down everybody else but user admins
if 'USER_ADMIN' not in home_perm:
userstore = get_userstore()
user = userstore.getUser(req.authname)
where.append('(u.author_id = {0} OR u.user_id = {0} OR FIND_IN_SET({0}, u.deputies) > 0)'.format(safe_int(user.id)))
# Check if user is not home admin and remove private fields from query
if 'USER_ADMIN' not in home_perm:
for pfield in [pfield for pfield in self.sens_fields if pfield in fields]:
fields.remove(pfield)
# Construct SQL queries
select_fields = ['{0} AS {1}'.format(self.db_fields[field], field) for field in fields]
select_sql = ','.join(select_fields)
where_sql = ' AND '.join(where)
# SQL query for fetching username - in-casesentive
sql = """
SELECT {0}
FROM user AS u
LEFT JOIN authentication AS auth ON auth.id = u.authentication_key
LEFT JOIN user_status AS us ON us.user_status_id = u.user_status_key
{1}
ORDER BY u.givenName, u.lastname, u.username
LIMIT {2}
""".format(select_sql, where_sql, limit)
with admin_query(cursors.DictCursor) as cursor:
try:
cursor.execute(sql)
rows = cursor.fetchall()
except Exception:
self.log.exception("Failed to get users with query %s" % sql)
return rows
def __init__(self, project):
Command.__init__(self)
self.dbname = safe_string(project.env_name)
self.name = "CreateTracDatabase"
