def env_id(env_name):
"""
Helper function for getting Trac `environment_id` or `environment_key` based
on environment name.
.. NOTE:: Avoid using! This one needs to be phased out.
Use `trac_environment_key` from :class:`multiproject.common.projects.project`
instead or even better switch to `project_id` !
"""
query = "SELECT environment_id FROM trac_environment WHERE identifier = %s"
row = None
with admin_query() as cursor:
try:
cursor.execute(query, env_name)
row = cursor.fetchone()
except:
# NOTE: this import must remain here or circular import will occur
from multiproject.core.configuration import conf
conf.log.exception("Didn't find environment id for %s" % env_name)
if row:
return row[0]
return 0
def downloads_dir(self):
if self._downloads_dir_fetched:
return self._downloads_dir
self._downloads_dir_fetched = True
new_downloads_dir = self._default
memcache_key = None
was_cached = True
if new_downloads_dir is None:
memcache_key = self._memcache_key()
new_downloads_dir = self.mc.get(memcache_key)
if new_downloads_dir is None:
was_cached = False
query = """
SELECT value FROM `{0}`.system WHERE name = 'files_downloads_dir'
""".format(self.env_name)
try:
with admin_query() as cursor:
cursor.execute(query)
for row in cursor:
new_downloads_dir = row[0]
except Exception:
conf.log.exception("Exception. Querying downloads dir failed.")
raise TracError("Error while fetching downloads dir.")
try:
self._downloads_dir = self.validate_dir(new_downloads_dir)
if not was_cached:
self.mc.set(memcache_key, self._downloads_dir,
self.DOWNLOADS_CACHE_TIME)
except DownloadDirValidationException:
self._downloads_dir = ''
return self._downloads_dir
def query_archived_projects(self, query):
""" Returns a list of archived projects given with query
"""
projects = []
with admin_query() as cursor:
try:
cursor.execute(query)
for project in cursor.fetchall():
# Create regular project
prj = Project(id=project[1],
env_name=project[3],
project_name=project[2],
description=None,
author_id=project[4],
created=project[5],
parent_id=project[6])
# Add some archiving parameters
prj.archive_folder_name = project[7]
prj.archive_path = conf.archive_path + "/" + project[7]
prj.archive_date = project[8]
prj.remove_due = project[9]
prj.removed_at = project[10]
prj.project_archive_id = project[0]
projects.append(prj)
except:
conf.log.exception(
"Querying archived projects failed with query '''%s'''" %
query)
return projects
def get_ssh_keys_by_user_id(self, user_id):
""" Returns ssh keys for user
"""
if not user_id:
return None
keys = []
query = """
SELECT key_id, user_id, ssh_key, comment, added
FROM ssh_keys
WHERE user_id = %s
"""
with admin_query() as cursor:
try:
cursor.execute(query, (user_id, ))
for row in cursor:
keys.append(SshKey.from_sql_row(row))
except:
conf.log.exception(
"Exception. get_ssh_keys_by_user_id(%s) procedure failed: %s"
% (str(user_id), query))
return keys
def do(self):
self.date = date.today()
# Create folder path in format "20101224_someproject_447"
self.base_path = conf.archive_path + "/"
self.folder_name = str(
self.date.strftime('%Y%m%d_')) + self.project.env_name + "_" + str(
self.project.archive_id)
self.project.archive_folder_name = self.folder_name
self.project.archive_path = self.base_path + self.folder_name
if os.path.exists(self.project.archive_path):
conf.log.debug(
"Failed to create path for project archive. Path exists.")
return False
os.makedirs(self.project.archive_path)
# Update project archive folder
with admin_query() as cursor:
try:
cursor.callproc("update_project_archive_folder", [
self.project.archive_id, self.project.archive_folder_name
])
except:
return False
return True
def _get_summary(self):
"""
Returns the summary statistics/numbers for members::
{'total_count':123, 'active_count':89, 'passive_count':34}
:returns: Summary in dict
"""
active_within_months = 2
query = """
SELECT
COUNT(u1.user_id) AS total_count,
COUNT(u2.user_id) AS active_count
FROM user AS u1
LEFT JOIN (
SELECT user_id
FROM user
WHERE last_login > NOW() - INTERVAL %s MONTH
) AS u2 ON u1.user_id = u2.user_id
"""
summary = {}
with admin_query(cursors.DictCursor) as cursor:
cursor.execute(query, active_within_months)
summary = cursor.fetchone()
# Calculate passive number manually
summary['passive_count'] = summary['total_count'] - summary['active_count']
return summary
def get_news_forum_by_name(self, name, first=False):
"""
Get the forum name for news forum. News forum name is defined in method params. If parameter
'first' is true, get the first forum name if available
:returns: The database name for news forum or None
"""
forum_name = None
if first is False:
query = "SELECT name FROM `%s`.forum WHERE name = '%s'" % (
self.env_name, name)
else:
query = "SELECT name FROM `%s`.forum ORDER BY id ASC LIMIT 1" % self.env_name
with db.admin_query() as cursor:
try:
cursor.execute(query)
row = cursor.fetchone()
if row:
forum_name = row[0]
except Exception:
self.log.exception("SQL query failed: %s" % query)
raise
return forum_name
def _get_all_user_permissions(self, username):
"""
:returns:
List of tuples, containing the project_id and permission/action::
[(698, 'WEBDAV'),
(698, 'XML_RPC'),
(698, 'VERSION_CONTROL'),
(700, 'WEBDAV'),
(700, 'XML_RPC'),
(700, 'VERSION_CONTROL')]
"""
usernames = "('anonymous')"
if username != 'anonymous':
usernames = "('anonymous', 'authenticated')"
# Using `project_user_visibility` table here would not be wise,
# since it is not always up-to-date.
query = """
SELECT DISTINCT p.project_id,a.action_string FROM action AS a
INNER JOIN group_permission AS gp ON gp.permission_key = a.action_id
INNER JOIN `group` AS g ON g.group_id = gp.group_key
INNER JOIN projects AS p ON p.trac_environment_key = g.trac_environment_key
INNER JOIN user_group AS ug ON ug.group_key = g.group_id
INNER JOIN user AS u ON u.user_id = ug.user_key
WHERE u.username IN %s
""" % usernames
with admin_query() as cursor:
try:
cursor.execute(query)
return [(row[0], row[1]) for row in cursor]
except:
conf.log.exception("Failed reading user permissions %s" % query)
def get_news_forum_by_name(self, name, first=False):
"""
Get the forum name for news forum. News forum name is defined in method params. If parameter
'first' is true, get the first forum name if available
:returns: The database name for news forum or None
"""
forum_name = None
if first is False:
query = "SELECT name FROM `%s`.forum WHERE name = '%s'" % (self.env_name, name)
else:
query = "SELECT name FROM `%s`.forum ORDER BY id ASC LIMIT 1" % self.env_name
with db.admin_query() as cursor:
try:
cursor.execute(query)
row = cursor.fetchone()
if row:
forum_name = row[0]
except Exception:
self.log.exception("SQL query failed: %s" % query)
raise
return forum_name
def read(identifier):
"""
Static method for reading trac environment from database. Returns an instance of a Trac project
environment, or raises TracError if database read fails (or environment is not found).
The data is also saved to memcache daemon if it's in use.
:param identifier: The project identifier in uri, that's tried to be read.
"""
row = TracEnvironment._try_from_cache(identifier)
if not row:
with admin_query() as cursor:
try:
query = "SELECT environment_id, identifier FROM trac_environment WHERE identifier = %s"
cursor.execute(query, identifier)
row = cursor.fetchone()
TracEnvironment._set_into_cache(identifier, row)
except:
conf.log.exception(
"Failed finding trac environment for %s" % identifier)
pass
if not row:
raise TracError('Environment %s not found' % identifier)
params = {'environment_id': row[0], 'identifier': row[1]}
return TracEnvironment(params)
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_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 applied(self):
"""
Check if column exists or not
:returns: True if exists, otherwise False
"""
if self.pretend_to_be_not_applied:
return False
count_false_published = 0
count_false_private = 0
with admin_query() as cursor:
perm_conditions = self._get_perm_conditions()
# Select count for public projects with null published date
cursor.execute("""
SELECT COUNT(projects.environment_name) FROM projects
WHERE ({0})
AND projects.published is NULL
""".format(' AND '.join(perm_conditions)))
count_false_private = int(cursor.fetchone()[0])
# Select count for non-public projects with non-null published date
cursor.execute("""
SELECT COUNT(projects.environment_name) FROM projects
WHERE (NOT {0})
AND projects.published is not NULL
""".format(' OR NOT '.join(perm_conditions)))
count_false_published = int(cursor.fetchone()[0])
return count_false_private + count_false_published == 0
def add_ldapgroup_to_group(self, ldapgroup_name, group_name):
"""
Adds LDAP group into a group
:param str ldapgroup_name: LDAP group name
:param str group_name: Trac permission group name
:raises DatabaseError: Query failure
"""
# Create ldap group if it doesn't exist
ldapgroup_name = ldapgroup_name.encode('utf-8')
ldapgroup_id = self._ldapgroups.get_ldapgroup_id(ldapgroup_name)
if ldapgroup_id is None:
if not self._ldapgroups.store_ldapgroup(ldapgroup_name):
conf.log.error("LDAP: Storing new ldap group %s failed. (user group %s)" %
(ldapgroup_name, group_name))
ldapgroup_id = self._ldapgroups.get_ldapgroup_id(ldapgroup_name)
# Create group if it doesn't exist
group_name = group_name.encode('utf-8')
group_id = self.get_group_id(group_name)
if group_id is None:
self.create_group(group_name)
group_id = self.get_group_id(group_name)
self._cache.clear_trac_environment_ldap_groups(self.trac_environment_key)
with admin_query() as cursor:
cursor.callproc("add_ldapgroup_to_group", [ldapgroup_id, group_id])
def grant_permission_to_group(self, group_name, permission_name):
"""
Grants permission to group.
Updates the published time of the project accordingly.
:param str group_name: Group name, will be created if does not exists
:param str permission_name: Perm name, will be created if does not exists
:raises InvalidPermissionState: Permission can not be granted
:raises DatabaseError: Query failure
"""
# check that this is valid change
gp = self.get_all_group_permissions() + [(group_name, permission_name)]
self.is_valid_group_members(group_permissions=gp)
permission_id = get_permission_id(permission_name)
# Create group if it doesn't exist
group_name = group_name.encode('utf-8')
group_id = self.get_group_id(group_name)
if group_id is None:
self.create_group(group_name)
group_id = self.get_group_id(group_name)
self._cache.clear_group_perms(self.trac_environment_key)
with admin_query() as cursor:
cursor.callproc("grant_permission_to_group", [group_id, permission_id])
self._update_published_time()
def project_environment_exists(self, env_name):
""" Checks if a project with given identifier (env_name) exists
"""
row = []
query = """
SELECT COUNT(project_id)
FROM projects
WHERE environment_name = %s
"""
query_project_archived = """
SELECT COUNT(orig_project_id)
FROM project_archive
WHERE environment_name = %s
"""
with admin_query() as cursor:
try:
cursor.execute(query, env_name)
row = cursor.fetchone()
if bool(row[0]) == False:
cursor.execute(query_project_archived, env_name)
row = cursor.fetchone()
except Exception, e:
conf.log.exception(e)
return False
def remove_user_from_group(self, user_name, group_name):
"""
Removes user from group.
Updates the published time of the project accordingly.
:param str user_name: User name
:param str group_name: Group name
:raises InvalidPermissionState: User cannot be removed
:raises DatabaseError: Query failure
:raises ValueError: User not found
"""
user = get_userstore().getUser(user_name)
if not user:
raise ValueError('User not found')
# TODO: just check that there's TRAC_ADMIN left?
# Checks that it is ok to remove user from group
ug = self.get_all_user_groups()
ug = [(user, group) for user, group in ug if not (user == user_name and group == group_name)]
self.is_valid_group_members(user_groups=ug)
group_name = group_name.encode('utf-8')
group_id = self.get_group_id(group_name)
self._cache.clear_user_groups(self.trac_environment_key)
with admin_query() as cursor:
cursor.callproc("remove_user_from_group", [user.id, group_id])
self._update_published_time()
def is_public_project(self):
"""
.. WARNING:: Use :class:`~multiproject.common.projects.project.Project` instead!
Function checks if the project defined in ``self.trac_environment_key``
is considered public. This is True if anonymous user group has permissions
defined in configuration. Example::
public_anon_group = Public viewers:PROJECT_SUMMARY_VIEW,VERSION_CONTROL_VIEW
Otherwise the project is private and function will return False.
"""
# Read the public group permissions from config (key returns tuple: (groupname, list of permissions))
required_group_perms = conf.public_anon_group[1]
permissions = []
with admin_query() as cursor:
cursor.callproc("get_project_public_permissions", [self.trac_environment_key])
permissions = cursor.fetchall()
public_group_perms = [pgp[0] for pgp in permissions]
# Iterate required permission and ensure all of the are found. Generated list contains the missing
# permissions and thus the outcome is: True=public, False=private
missing_perms = [rgp for rgp in required_group_perms if rgp not in public_group_perms]
# All the required 'public project' permissions were found => Public project
return len(missing_perms) == 0
def applied(self):
"""
Check if the ssh key related tables already exist in trac_admin database.
:returns: True if the tables are there, False if they're not.
"""
conf = Configuration.instance()
with admin_query() as cursor:
cursor.execute('''
SELECT table_name FROM information_schema.tables
WHERE table_schema = '{0}'
AND table_name = 'ssh_keys'
'''.format(conf.db_admin_schema_name))
if cursor.rowcount != 1:
return False
cursor.execute('''
SELECT table_name FROM information_schema.tables
WHERE table_schema = '{0}'
AND table_name = 'ssh_key_update'
'''.format(conf.db_admin_schema_name))
if cursor.rowcount != 1:
return False
return True
def denied_protocols(self, storage_type):
""" Returns a set of denied schemes
"""
# Try from cache
denied = self.cache.get_project_protocols(self.project_id, storage_type)
if denied:
return denied
denied = []
table = self._table_by_type(storage_type)
query = """
SELECT prt.scheme FROM `%s` AS dsp
INNER JOIN protocol AS prt
ON prt.protocol_id = dsp.protocol_key
""" % table
query += "WHERE dsp.project_key = %s"
with admin_query() as cursor:
try:
cursor.execute(query, self.project_id)
for row in cursor:
denied.append(row[0])
except:
conf.log.exception("Error occurred while reading project protocol list")
raise
self.cache.set_project_protocols(self.project_id, storage_type, set(denied))
return set(denied)
def get_all_users(self, except_anon=True):
"""
Returns :py:class:`User` objects for every user except 'authenticated' and, if
anon_id is given, 'anonymous'.
No row for 'authenticated' users are generated currently, this is why the 'authenticated'
user is left out.
:param bool except_anon: when True, don't include anon user
:returns: A list of :py:class:`User` objects
"""
if except_anon:
query = """SELECT user_id, username FROM `user`
WHERE username NOT IN ('anonymous', 'authenticated') """
else:
query = """SELECT user_id, username FROM `user`
WHERE username NOT IN ('authenticated') """
users = []
with admin_query() as cursor:
try:
cursor.execute(query)
for row in cursor:
user = User.from_sql_row(row)
users.append(user)
except Exception as e:
if self.verbose is not None:
print "Exception. In method get_all_users, the following query failed."
print query
print e
conf.log.exception(
"Exception. ProjectUserVisibilityGenerator.get_all_users failed "
"with query '''%s'''." % query)
return users
def add_organization_to_group(self, organization_name, group_name):
"""
Add organization to the group, creates group if it doesn't already exists.
:param str organization_name: Name of organization
:param str group_name: Name of group to be added
:raises ValueError: Organization already exists in the group
:raises DatabaseError: Query failure
"""
if organization_name in [org[0] for org in self.get_all_organization_groups() if org[1] == group_name]:
raise ValueError('Organization %s already exists' % organization_name)
organization_id = self._organizations.get_organization_id(organization_name)
# Create group if it doesn't exist
group_name = group_name.encode('utf-8')
group_id = self.get_group_id(group_name)
if group_id is None:
self.create_group(group_name)
group_id = self.get_group_id(group_name)
self._cache.clear_organization_groups(self.trac_environment_key)
with admin_query() as cursor:
cursor.callproc("add_organization_to_group", [organization_id, group_id])
def downloads_dir(self):
if self._downloads_dir_fetched:
return self._downloads_dir
self._downloads_dir_fetched = True
new_downloads_dir = self._default
memcache_key = None
was_cached = True
if new_downloads_dir is None:
memcache_key = self._memcache_key()
new_downloads_dir = self.mc.get(memcache_key)
if new_downloads_dir is None:
was_cached = False
query = """
SELECT value FROM `{0}`.system WHERE name = 'files_downloads_dir'
""".format(self.env_name)
try:
with admin_query() as cursor:
cursor.execute(query)
for row in cursor:
new_downloads_dir = row[0]
except Exception:
conf.log.exception("Exception. Querying downloads dir failed.")
raise TracError("Error while fetching downloads dir.")
try:
self._downloads_dir = self.validate_dir(new_downloads_dir)
if not was_cached:
self.mc.set(memcache_key, self._downloads_dir, self.DOWNLOADS_CACHE_TIME)
except DownloadDirValidationException:
self._downloads_dir = ''
return self._downloads_dir
def resolve_project_id(env_name):
"""
Helper function for resolving project id based on name
.. NOTE:: Avoid using! This one needs to be phased out.
Use `project_id` from :class:`multiproject.common.projects.project` instead.
"""
query = """
SELECT project_id
FROM projects
WHERE environment_name = %s
"""
row = None
with admin_query() as cursor:
try:
cursor.execute(query, env_name)
row = cursor.fetchone()
except:
# NOTE: this import must remain here or circular import will occur
from multiproject.core.configuration import conf
conf.log.exception("Failed to get project id with query: %s" %
query)
if row:
return row[0]
return 0
def env_id(env_name):
"""
Helper function for getting Trac `environment_id` or `environment_key` based
on environment name.
.. NOTE:: Avoid using! This one needs to be phased out.
Use `trac_environment_key` from :class:`multiproject.common.projects.project`
instead or even better switch to `project_id` !
"""
query = "SELECT environment_id FROM trac_environment WHERE identifier = %s"
row = None
with admin_query() as cursor:
try:
cursor.execute(query, env_name)
row = cursor.fetchone()
except:
# NOTE: this import must remain here or circular import will occur
from multiproject.core.configuration import conf
conf.log.exception("Didn't find environment id for %s" % env_name)
if row:
return row[0]
return 0
def _get_summary(self):
"""
Returns the summary statistics/numbers for projects::
{'total_count':123, 'public_count':89, 'private_count':34}
:returns: Summary in dict
"""
query = """
SELECT
COUNT(p1.project_id) AS total_count,
COUNT(p2.project_id) AS public_count
FROM projects AS p1
LEFT JOIN (
SELECT project_id
FROM projects
WHERE published IS NOT NULL
) AS p2 ON p1.project_id = p2.project_id
"""
summary = {}
with admin_query(cursors.DictCursor) as cursor:
cursor.execute(query)
summary = cursor.fetchone()
# Calculate private number manually
summary['private_count'] = summary['total_count'] - summary['public_count']
return summary
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 from_operational(self, identifier):
""" Read a project from operational database
"""
query = """
SELECT p.environment_name AS identifier,
p.project_name,
u.username AS author,
p.created,
p.updated,
p.published,
p.project_id
FROM projects AS p
INNER JOIN user AS u ON u.user_id = p.author
WHERE p.environment_name = '%s'""" % identifier
row = []
with admin_query() as cursor:
try:
cursor.execute(query)
row = cursor.fetchone()
except:
conf.log.exception("Getting project from operational db failed. %s" % identifier)
if not row:
return None
project = {'identifier': row[0],
'project_name': MySQLdb.escape_string(row[1]),
'author': MySQLdb.escape_string(row[2]),
'created': row[3],
'updated': row[4],
'published': row[5],
'project_key': row[6]}
return project
def get_project_counts_per_category(self, username):
# Try from cache
cache = ProjectCache.instance()
items = cache.get_project_counts_per_category(username)
if items:
return items
anon_et_al = "(%s)"
if username != 'anonymous':
anon_et_al = "('anonymous', %s)"
# Query public project count / category
query = """SELECT pc.category_key, COUNT(pc.project_key)
FROM project_categories AS pc
INNER JOIN project_user_visibility v ON v.project_id = pc.project_key
INNER JOIN user AS u ON u.user_id = v.user_id
WHERE u.username IN {anon_et_al}
GROUP BY pc.category_key;""".format(anon_et_al = anon_et_al)
items = {}
with admin_query() as cursor:
try:
cursor.execute(query, username)
for row in cursor:
items[row[0]] = row[1]
except Exception, e:
conf.log.exception(
"Exception. Projects.get_project_counts_per_category failed with query '''%s'''." %
query)
def __queryProjects(self, project_query):
""" Method that queries projects using given query and then wraps them
into dictionary
Used with project list
"""
projects = []
with admin_query() as cursor:
try:
cursor.execute(project_query)
for project in cursor:
author = project[Project.FIELD_COUNT]
if len(project) > Project.FIELD_COUNT and conf.expose_user_identity:
# Given name is at Project.FIELD_COUNT + 1
author = project[Project.FIELD_COUNT + 1] or _('(invalid given name)')
if len(project) > Project.FIELD_COUNT + 1:
# Last name is at Project.F
author += " " + (project[Project.FIELD_COUNT + 2] or _('(invalid last name)'))
# FIXME: description is project_name and name is env_name
projects.append({'description': project[1],
'name': project[2],
'author': author,
'date': project[5],
'updated': project[6],
'published': project[7],
'icon_name': project[9]
})
except:
conf.log.exception("Project query failed: {0}".format(project_query))
raise
return projects
def remove_user_from_group(self, user_name, group_name):
"""
Removes user from group.
:param str user_name: User name
:param str group_name: Group name
:raises InvalidPermissionState: User cannot be removed
:raises DatabaseError: Query failure
:raises ValueError: User not found
"""
userstore = get_userstore()
user = userstore.getUser(user_name)
if not user:
raise InvalidPermissionsState('Unknown user %s' % user_name)
# Get the group
group_name = group_name.encode('utf-8')
group_id = self.get_group_id(group_name)
if group_id is None:
conf.log.exception("Group %s doesn't exists'" % group_name)
self._cache.clear_user_groups(self.trac_environment_key)
with admin_query() as cursor:
cursor.callproc("remove_user_from_group", [user.id, group_id])
def _get_summary(self):
"""
Returns the summary statistics/numbers for projects::
{'total_count':123, 'public_count':89, 'private_count':34}
:returns: Summary in dict
"""
query = """
SELECT
COUNT(p1.project_id) AS total_count,
COUNT(p2.project_id) AS public_count
FROM projects AS p1
LEFT JOIN (
SELECT project_id
FROM projects
WHERE published IS NOT NULL
) AS p2 ON p1.project_id = p2.project_id
"""
summary = {}
with admin_query(cursors.DictCursor) as cursor:
cursor.execute(query)
summary = cursor.fetchone()
# Calculate private number manually
summary['private_count'] = summary['total_count'] - summary['public_count']
return summary
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 add_deputy(self, user_id, deputy_name):
"""
Add deputy for user
Returns Boolean value
"""
deputy = self.getUser(deputy_name)
deputies_id = None
query = "SELECT deputies FROM user WHERE user_id = '%s'" % user_id
with admin_query() as cursor:
try:
cursor.execute(query)
row = cursor.fetchone()
deputies_id = row[0];
except:
conf.log.exception("Exception. Query failed when getting deputies '''%s'''" % query)
return False
if not deputies_id:
deputies_id = deputy.id
else:
deputies_id = deputies_id+","+str(deputy.id)
query = "UPDATE user SET deputies = '%s' WHERE user_id = '%s' " % (deputies_id, user_id)
with admin_transaction() as cursor:
try:
cursor.execute(query)
return True
except:
conf.log.exception("Exception. Query failed when updating deputies '''%s'''" % query)
return False
def get_private_project_pairs(self, public_id_pairs = None):
query = "SELECT project_id, trac_environment_key FROM projects "
if public_id_pairs:
public_project_ids = [pair[0] for pair in public_id_pairs]
# TODO: What if there are really many public projects?
pub_projs = ','.join(str(id) for id in public_project_ids)
query += "WHERE projects.project_id NOT IN (%s)" % pub_projs
id_pairs = []
with admin_query() as cursor:
try:
cursor.execute(query)
for row in cursor:
id_pair = (int(row[0]), int(row[1]))
id_pairs.append(id_pair)
except Exception as e:
if self.verbose is not None:
print "Exception. In method get_private_project_pairs, the following query failed."
print query
print e
conf.log.exception("Exception. ProjectUserVisibilityGenerator.get_private_project_pairs failed "
"with query '''%s'''." % query)
return id_pairs
def get_all_users(self, except_anon=True):
"""
Returns :py:class:`User` objects for every user except 'authenticated' and, if
anon_id is given, 'anonymous'.
No row for 'authenticated' users are generated currently, this is why the 'authenticated'
user is left out.
:param bool except_anon: when True, don't include anon user
:returns: A list of :py:class:`User` objects
"""
if except_anon:
query = """SELECT user_id, username FROM `user`
WHERE username NOT IN ('anonymous', 'authenticated') """
else:
query = """SELECT user_id, username FROM `user`
WHERE username NOT IN ('authenticated') """
users = []
with admin_query() as cursor:
try:
cursor.execute(query)
for row in cursor:
user = User.from_sql_row(row)
users.append(user)
except Exception as e:
if self.verbose is not None:
print "Exception. In method get_all_users, the following query failed."
print query
print e
conf.log.exception("Exception. ProjectUserVisibilityGenerator.get_all_users failed "
"with query '''%s'''." % query)
return users
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 get(cls, id):
"""
Fetches message group recipient information from the database
:param cls:
:param id: Id of the message group
:return: MessageGroup
"""
group_info = None
recipients = []
sql = '''
SELECT mgr.user_id
FROM message_group_recipient AS mgr
WHERE mgr.message_group_id = %s
'''
with admin_query(cursors.DictCursor) as cursor:
cursor.execute(sql, id)
for row in cursor.fetchall():
recipients.append(row['user_id'])
mgr = MessageGroupRecipient()
mgr.id = id
mgr.recipients = recipients
return mgr
def denied_protocols(self, storage_type):
""" Returns a set of denied schemes
"""
# Try from cache
denied = self.cache.get_project_protocols(self.project_id,
storage_type)
if denied:
return denied
denied = []
table = self._table_by_type(storage_type)
query = """
SELECT prt.scheme FROM `%s` AS dsp
INNER JOIN protocol AS prt
ON prt.protocol_id = dsp.protocol_key
""" % table
query += "WHERE dsp.project_key = %s"
with admin_query() as cursor:
try:
cursor.execute(query, self.project_id)
for row in cursor:
denied.append(row[0])
except:
conf.log.exception(
"Error occurred while reading project protocol list")
raise
self.cache.set_project_protocols(self.project_id, storage_type,
set(denied))
return set(denied)
def grant_permission_to_group(self, group_name, permission_name):
"""
Grants permission to group.
:param str group_name: Group name, will be created if does not exists
:param str permission_name: Perm name, will be created if does not exists
:raises InvalidPermissionState: Permission can not be granted
:raises DatabaseError: Query failure
"""
# check that this is valid change
gp = self.get_all_group_permissions() + [(group_name, permission_name)]
self.is_valid_group_members(group_permissions=gp)
permission_id = get_permission_id(permission_name)
# Create group if it doesn't exist
group_name = group_name.encode('utf-8')
group_id = self.get_group_id(group_name)
if group_id is None:
self.create_group(group_name)
group_id = self.get_group_id(group_name)
self._cache.clear_group_perms(self.trac_environment_key)
with admin_query() as cursor:
try:
cursor.callproc("grant_permission_to_group", [group_id, permission_id])
# User already exists in the group
except MySQLdb.IntegrityError:
conf.log.warning('Group %s already has permission: %s' % (group_name, permission_name))
def get_private_project_pairs(self, public_id_pairs=None):
query = "SELECT project_id, trac_environment_key FROM projects "
if public_id_pairs:
public_project_ids = [pair[0] for pair in public_id_pairs]
# TODO: What if there are really many public projects?
pub_projs = ','.join(str(id) for id in public_project_ids)
query += "WHERE projects.project_id NOT IN (%s)" % pub_projs
id_pairs = []
with admin_query() as cursor:
try:
cursor.execute(query)
for row in cursor:
id_pair = (int(row[0]), int(row[1]))
id_pairs.append(id_pair)
except Exception as e:
if self.verbose is not None:
print "Exception. In method get_private_project_pairs, the following query failed."
print query
print e
conf.log.exception(
"Exception. ProjectUserVisibilityGenerator.get_private_project_pairs failed "
"with query '''%s'''." % query)
return id_pairs
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_public_project_pairs(self, anon_id):
"""
:param anon_id: Anonymous user_id
:return: List of tuples (project_id, trac_environment_key)
"""
# TODO: it's not clearly defined when project is public
perms = ['PROJECT_VIEW', 'TRAC_ADMIN']
perm_ids = [get_permission_id(perm) for perm in perms]
query = """SELECT DISTINCT p.project_id, p.trac_environment_key
FROM projects p
INNER JOIN `group` g ON g.trac_environment_key = p.trac_environment_key
INNER JOIN user_group ON user_group.group_key = g.group_id
INNER JOIN group_permission ON group_permission.group_key = g.group_id
WHERE user_group.user_key = {0}
AND group_permission.permission_key IN ({1})
""".format(anon_id, ', '.join([str(int(id)) for id in perm_ids]))
id_pairs = []
with admin_query() as cursor:
try:
cursor.execute(query)
for row in cursor:
id_pair = (int(row[0]), int(row[1]))
id_pairs.append(id_pair)
except Exception as e:
if self.verbose is not None:
print "Exception. In method get_public_project_pairs, the following query failed."
print query
print e
conf.log.exception("Exception. ProjectUserVisibilityGenerator.get_public_project_pairs "
"failed with query '''%s'''." % query)
return id_pairs
def get_categories_by_project(self, project_key, context_id):
"""
Searches categories belonging in project
:returns: A list of categories
"""
and_context_id = ''
if context_id:
and_context_id = 'AND cat.context_id = %s'
query = """SELECT cat.* FROM categories AS cat
INNER JOIN project_categories AS pc ON pc.category_key = cat.category_id
WHERE pc.project_key = %s {and_context_id}""".format(and_context_id=and_context_id)
category_list = []
with admin_query() as cursor:
try:
if context_id:
cursor.execute(query, (project_key, context_id))
else:
cursor.execute(query, project_key)
for row in cursor:
category_list.append(Category.from_sql_row(row))
except:
conf.log.exception("Exception. Failed searching project categories. Query('%s'), project_key %d." %
(str(query), project_key))
return category_list
def resolve_project_id(env_name):
"""
Helper function for resolving project id based on name
.. NOTE:: Avoid using! This one needs to be phased out.
Use `project_id` from :class:`multiproject.common.projects.project` instead.
"""
query = """
SELECT project_id
FROM projects
WHERE environment_name = %s
"""
row = None
with admin_query() as cursor:
try:
cursor.execute(query, env_name)
row = cursor.fetchone()
except:
# NOTE: this import must remain here or circular import will occur
from multiproject.core.configuration import conf
conf.log.exception("Failed to get project id with query: %s" % query)
if row:
return row[0]
return 0
def get_expired_users(self, when=None):
"""
Returns users that accounts are expired, or will soon expire (if when date is in future)
:param datetime when: Date in future, other wise returns the accounts that are already expired
:returns: List of user objects
"""
users = []
when = when or datetime.utcnow()
query = """
SELECT user.*
FROM user
LEFT JOIN user_status ON user_status.user_status_id = user.user_status_key
WHERE
user.expires <= %s AND
LOWER(user_status.status_label) != 'banned'
ORDER BY user.expires DESC
"""
with admin_query() as cursor:
cursor.execute(query, when)
for row in cursor:
users.append(self.sqlToUser(row))
return users
def _get_summary(self):
"""
Returns the summary statistics/numbers for members::
{'total_count':123, 'active_count':89, 'passive_count':34}
:returns: Summary in dict
"""
active_within_months = 2
query = """
SELECT
COUNT(u1.user_id) AS total_count,
COUNT(u2.user_id) AS active_count
FROM user AS u1
LEFT JOIN (
SELECT user_id
FROM user
WHERE last_login > NOW() - INTERVAL %s MONTH
) AS u2 ON u1.user_id = u2.user_id
"""
summary = {}
with admin_query(cursors.DictCursor) as cursor:
cursor.execute(query, active_within_months)
summary = cursor.fetchone()
# Calculate passive number manually
summary['passive_count'] = summary['total_count'] - summary['active_count']
return summary
def db_applied(self, cmd, line, column, result, rows):
with admin_query() as cr:
try:
cr.execute(cmd)
if column is not None and line is not None:
row = cr.fetchall()
if len(row) > line and row[line][column] == result:
return True
elif line is not None:
row = cr.fetchall()
if len(row) > line and row[line] == result:
return True
elif rows is not None:
row = cr.fetchall()
if len(row) == rows:
return True
elif result is not None:
row = cr.fetchone()
if row[0] == result:
return True
except:
params = (str(cmd), str(line), str(column), str(result),
str(rows))
log.exception(
"Exception. Failed checking if migration was applied {command:%s, line:%s, column:%s, result:%s, rows: %s}."
% params)
return False
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 applied(self):
"""
Check if column exists or not
:returns: True if exists, otherwise False
"""
with admin_query(cursors.DictCursor) as cursor:
cursor.execute('DESC contexts')
return 'edit_type' in [row['Field'] for row in cursor]
def applied(self):
with admin_query() as cursor:
cursor.execute("SELECT * FROM action WHERE action_string='FILES_DOWNLOADS_VIEW'")
result = cursor.fetchone() is not None
if not result:
cursor.execute("SELECT * FROM action WHERE action_string='WEBDAV'")
result = cursor.fetchone() is None
return result
def _list_project_identifiers(self):
query = "SELECT environment_name FROM projects"
with admin_query() as cursor:
try:
cursor.execute(query)
return [row[0] for row in cursor]
except:
conf.log.exception("Listing all project identifiers failed")
def _read_all_actions(self):
query = "SELECT action_string FROM action"
with admin_query() as cursor:
try:
cursor.execute(query)
return [row[0] for row in cursor]
except Exception:
conf.log.exception("Failed to read actions")
def do(self):
with admin_query() as cursor:
try:
cursor.callproc("remove_archived_project_record",
[self.project.project_archive_id])
except Exception, e:
conf.log.exception(e)
return False
def _get_project(project_id=None, env_name=None, use_cache=True):
by_env = False
if env_name:
by_env = True
param = env_name
elif project_id:
if not project_id:
return None
param = project_id
else:
return None
cache = ProjectCache.instance()
# Try cache
if use_cache:
if by_env:
project = cache.get_project_by_env_name(env_name)
else:
project = cache.getProject(project_id)
if project:
return project
query = (
"SELECT project_id, environment_name, project_name, description, author, created, updated, "
"published, parent_id, icon_name, trac_environment_key, public "
"FROM projects WHERE {0} = %s".format(
'environment_name' if by_env else 'project_id'))
try:
with admin_query() as cursor:
cursor.execute(query, param)
row = cursor.fetchone()
if not row:
return None
project = Project(id=row[0],
env_name=row[1],
project_name=row[2],
description=row[3],
author_id=row[4],
created=row[5],
updated=row[6],
published=row[7],
parent_id=row[8],
icon_name=row[9],
trac_environment_key=row[10],
public=row[11])
if use_cache:
if by_env:
cache.set_project_by_env_name(env_name, project)
else:
cache.setProject(project)
return project
except Exception as e:
conf.log.exception(
"Exception occurred while running query: '''%s'''" % query)
return None
