def save(self, replace = False):
""" Make an sql query for inserting this record into database
"""
# FIXME: The logic does not work as expected: if the data (ticket, for example) is changed, a new row
# is always created.
statement = "INSERT IGNORE"
if replace:
statement = "REPLACE"
# Strip HTML elements from specified fields
for fieldname in ('description', 'title', 'summary'):
self.fields[fieldname] = re_striphtml.sub('', self.fields[fieldname])
fields = 'date,dateuid,kind,filter,author,project_name,project_identifier,project_id,url,description,title,summary,checksum'
query = statement + " INTO timeline_cache(%s) " % fields
query += "VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
with admin_transaction() as cursor:
try:
cursor.execute(query, (self.date, self.dateuid, self.kind, self.filter, self.author,
self.project_name, self.project_identifier,
self.project_id, self.url, self.description,
self.title, self.summary or '', str(self.checksum)))
except:
conf.log.exception("Saving an event to timeline cache failed")
def undo(self):
if not self.success:
return True
query_get = """
SELECT trac_environment_key
FROM projects
WHERE environment_name = %s
"""
query_str = """
DELETE FROM trac_environment
WHERE identifier = %s
"""
cache = ProjectCache.instance()
with admin_transaction() as cursor:
try:
cursor.execute(query_get, self.short_name)
row = cursor.fetchone()
cursor.execute(query_str, self.short_name)
if row:
cache.clearProject(row[0])
cache.clearProjectId(self.short_name)
except Exception:
conf.log.exception('Failed to removed project {0} from database'.format(self.short_name))
return False
return True
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 delete(self, backup_id):
"""
Deletes the database dump from the filesystem and the database
row where it is being defined
:param backup_id: Backup identifier, as an integer
"""
backup = {}
assert isinstance(backup_id, long)
# Create dictionary containing the info about the backup
backup = {'id':backup_id}
# Open the db connection for adding the restore information
with admin_transaction() as cursor:
# Update restore into to project_backup table. Use result count to check if the id was actually
# found or not
query = '''
DELETE FROM project_backup
WHERE id = %s
'''
cursor.execute(query, backup['id'])
# Check if the backup_id was actually found?
if not cursor.rowcount:
raise TracError('Backup cannot be found')
# Delete the backup from filesystem (if it can be found)
dump_path = self.backup_path_tmpl % (self.project.env_name, backup['id'])
if os.path.exists(dump_path):
os.remove(dump_path)
return backup
def save(self):
"""
Saves the changes in properties into database
>>> mg = MessageGroup()
>>> mg.save()
>>> mgr = MessageGroupRecipient()
>>> mgr.recipients = [123, 423, 123]
>>> mgr.id = mg.id
>>> mgr.save()
"""
# If either of the properties is None
if None in (self.recipients, self.id):
raise ValueError('Both recipients and id needs to be set first')
recipients_str = [str(receiver_id) for receiver_id in self.recipients]
# Update existing group: remove existing members and insert new
remove_sql = 'DELETE FROM message_group_recipient WHERE message_group_id = %s'
update_sql = 'INSERT INTO message_group_recipient (message_group_id, user_id) VALUES (%s, %s)'
update_values = zip([self.id]*len(self.recipients), self.recipients)
with admin_transaction() as cursor:
cursor.execute(remove_sql, self.id)
cursor.executemany(update_sql, update_values)
logging.info('Updated message group {0} into database'.format(self))
return self
def updatePassword(self, user, password):
""" Changes user password into given raw password
:param User user: user to be updated (id must be set)
:param str password: password either ordinary or unicode string
"""
self.__cache.clear_user_by_user(user)
if not password:
return False
if not user.id:
return False
# Do update
with admin_transaction() as cursor:
try:
cursor.execute("SELECT SHA1_PW FROM user WHERE user_id = %s", user.id)
sha = cursor.fetchone()
# TODO: move str(sha[0]) part into the clearAuthentication method
self.__authcache.clearAuthentication(user.username, str(sha[0]).encode('utf-8'))
cursor.execute("UPDATE user SET SHA1_PW = SHA1(%s) WHERE user_id = %s",
(password.encode('utf-8'), user.id))
except Exception:
conf.log.exception("Failed to update password.")
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 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 update_user_email(self, user, email=None):
"""
Updates user email address.
:param str email: when given, updates only when different from user.mail
"""
if email is not None:
if user.mail == email:
return
else:
user.mail = email
self.__cache.clear_user_by_user(user)
# TODO: Update also the email in global and project-specific session(s)
query = '''
UPDATE user
SET mail = %s
WHERE user_id = %s
'''
with admin_transaction() as cursor:
try:
cursor.execute(query, (user.mail, user.id))
except:
conf.log.exception("Exception: updating user failed '''%s'''." % query)
raise
def backup(self, user_id, description=None):
"""
Creates a database backup of the trac instance.
.. IMPORTANT:: Only the **database** is backed up, while attachments are left as is.
:param user_id: Id the user who did the restore
:param description: Optional description about the backup, why it was done or current state...
Returns:
True if all went well, otherwise TracError is raised.
"""
assert isinstance(user_id, long), 'User id needs to be long int'
description = description if description else ''
dump_path = None
# Create database entry about the back
with admin_transaction() as cursor:
cursor.execute((
"INSERT INTO project_backup (project_key, created_by, description)"
"VALUES (%s, %s, %s)"),
(self.project.id, user_id, description))
# Now, take the last inserted id and use it to generate unique dump path
dump_path = self.backup_path_tmpl % (self.project.env_name,
cursor.lastrowid)
# Use Trac's database manager to dump the database into filesystem
try:
self.dm.backup(dump_path)
except OSError, err:
self.env.log.exception(err)
raise TracError('Failed to dump database: %s' % err)
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 save(self):
"""
Saves the changes set to properties into database
>>> p = Project()
>>> p.author = newauthor
>>> p.save()
"""
# Ensure the data is validated
self.validate()
# Construct SQL update statement using db fields and setting %s placeholder for values
sql = '''
UPDATE projects
SET {0}
WHERE project_id = %s
'''.format(', '.join(
['{0}=%s'.format(field) for field in self.FIELDS.values()]))
with admin_transaction() as cursor:
cursor.execute(sql,
([getattr(self, pro)
for pro in self.FIELDS.keys()] + [self.id]))
# Clear the project cache
cache = ProjectCache.instance()
cache.clear_project(self)
conf.log.info('Saved project {0} changes into database'.format(self))
def undo(self):
if not self.success:
return True
query_get = """
SELECT trac_environment_key
FROM projects
WHERE environment_name = %s
"""
query_str = """
DELETE FROM trac_environment
WHERE identifier = %s
"""
cache = ProjectCache.instance()
with admin_transaction() as cursor:
try:
cursor.execute(query_get, self.short_name)
row = cursor.fetchone()
cursor.execute(query_str, self.short_name)
if row:
cache.clearProject(row[0])
cache.clearProjectId(self.short_name)
except Exception:
conf.log.exception('Failed to removed project {0} from database'.format(self.short_name))
return False
return True
def save(self):
"""
Saves the changes set to properties into database
>>> p = Project()
>>> p.author = newauthor
>>> p.save()
"""
# Ensure the data is validated
self.validate()
# Construct SQL update statement using db fields and setting %s placeholder for values
sql = '''
UPDATE projects
SET {0}
WHERE project_id = %s
'''.format(', '.join(['{0}=%s'.format(field) for field in self.FIELDS.values()]))
with admin_transaction() as cursor:
cursor.execute(sql, ([getattr(self, pro) for pro in self.FIELDS.keys()] + [self.id]))
# Clear the project cache
cache = ProjectCache.instance()
cache.clear_project(self)
conf.log.info('Saved project {0} changes into database'.format(self))
def backup(self, user_id, description=None):
"""
Creates a database backup of the trac instance.
.. IMPORTANT:: Only the **database** is backed up, while attachments are left as is.
:param user_id: Id the user who did the restore
:param description: Optional description about the backup, why it was done or current state...
Returns:
True if all went well, otherwise TracError is raised.
"""
assert isinstance(user_id, long), 'User id needs to be long int'
description = description if description else ''
dump_path = None
# Create database entry about the back
with admin_transaction() as cursor:
cursor.execute(("INSERT INTO project_backup (project_key, created_by, description)"
"VALUES (%s, %s, %s)"), (self.project.id, user_id, description))
# Now, take the last inserted id and use it to generate unique dump path
dump_path = self.backup_path_tmpl % (self.project.env_name, cursor.lastrowid)
# Use Trac's database manager to dump the database into filesystem
try:
self.dm.backup(dump_path)
except OSError, err:
self.env.log.exception(err)
raise TracError('Failed to dump database: %s' % err)
def delete(self):
"""
Deletes the message from the database
"""
sql = 'DELETE FROM message WHERE id = %s'
with admin_transaction() as cursor:
cursor.execute(sql, self.id)
logging.info('Deleted message {0} from database'.format(self))
def report_downgrade(self, migration):
""" Writes into database that migration was downgraded
"""
query = "DELETE FROM `migration` WHERE migration_name = '" + migration + "'"
with admin_transaction() as cursor:
try:
cursor.execute(query)
except:
log.exception("Failed to remove migration from database")
raise
def _delete_db_cookies(self, req):
""" Delete cookie from auth_cookie table and
wipe also older than 10 days old cookies.
"""
query = "DELETE FROM auth_cookie WHERE name = %s OR time < %s"
with admin_transaction() as cursor:
try:
cursor.execute(query, (req.authname, int(time.time()) - 86400 * 10))
except Exception:
self.log.exception("Failed to delete cookie for: %s" % req.authname)
def report_upgrade(self, migration):
""" Writes into database that migration was upgraded
"""
query = "INSERT INTO `migration` (migration_name, datetime) VALUES('" + migration + "', null)"
with admin_transaction() as cursor:
try:
cursor.execute(query)
except:
log.exception("Failed to insert migration to database")
raise
def remove_by_filter(self, identifier, filter):
""" Clear out specific kind of envents by project
"""
query = "DELETE FROM timeline_cache WHERE filter = %s AND project_identifier = %s"
with admin_transaction() as cursor:
try:
cursor.execute(query, (filter, identifier))
except:
conf.log.exception("Failed removing items from timeline: %s, (%s,%s)" %
(query, filter, identifier))
def report_downgrade(self, migration):
""" Writes into database that migration was downgraded
"""
query = "DELETE FROM `migration` WHERE migration_name = '" + migration + "'"
with admin_transaction() as cursor:
try:
cursor.execute(query)
except:
log.exception("Failed to remove migration from database")
raise
def report_upgrade(self, migration):
""" Writes into database that migration was upgraded
"""
query = "INSERT INTO `migration` (migration_name, datetime) VALUES('" + migration + "', null)"
with admin_transaction() as cursor:
try:
cursor.execute(query)
except:
log.exception("Failed to insert migration to database")
raise
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 restore(self, backup_id, user_id):
"""
Restores the database dump over the existing setup
:param backup_id: Backup identifier, as an integer
:param user_id: Id the user who did the restore
Returns:
A dictionary of the restored backup
"""
backup = {}
assert isinstance(user_id, long), 'User id needs to be long integer'
assert isinstance(backup_id, long), 'Backup id needs to be long integer'
# Create dictionary containing the info about the backup
backup = {'id':backup_id, 'restored':datetime.utcnow(), 'restored_by':user_id}
# Open the db connection for adding the restore information, if any of the operations fail,
# the database transaction will be rolled back in the context manager
with admin_transaction() as cursor:
# Update restore into to project_backup table. Use result count to check if the id was
# actually found or not
query = '''
UPDATE project_backup
SET restored=%s, restored_by=%s
WHERE id = %s
'''
cursor.execute(query, (backup['restored'], backup['restored_by'] , backup['id']))
# Check if the backup_id was actually found?
if not cursor.rowcount:
raise TracError('Backup cannot be found')
# Do the actual database restore
try:
mysqlp = self._get_mysql_process(self.env)
except OSError, e:
raise TracError(_("Unable to run mysql command: %(msg)s", msg=exception_to_unicode(e)))
# Pass the backup into stdin
backup_path = self.backup_path_tmpl % (self.project.env_name, backup['id'])
if not os.path.exists(backup_path):
conf.log.error('User failed to restore project backup')
raise TracError(_('Backup file cannot be found'))
with open(backup_path, 'r+b') as backup_input:
errmsg = mysqlp.communicate(input=backup_input.read())
if mysqlp.returncode != 0:
msg = _('Restoring the database backup failed: %(msg)s', msg=to_unicode(errmsg.strip()))
conf.log.error(msg)
raise TracError(msg)
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 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 clean_ssh_key_update_flags(self):
""" Cleans ssh key update flags
"""
query = "DELETE FROM ssh_key_update"
with admin_transaction() as cursor:
try:
cursor.execute(query)
except:
conf.log.exception("Exception. clean_ssh_key_update_flags procedure failed: %s" % query)
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_project_visibility(self, visibility, published='NULL'):
"""
Sets public attribute of project to true : false
"""
query = "UPDATE projects SET public = %s, published = %s WHERE trac_environment_key = %s"
with admin_transaction() as cursor:
try:
cursor.execute(query, (visibility, published, self.trac_environment_key))
except Exception as e:
conf.log.exception("Exception. Failed updating project visibility with query '''%s'''" % query)
raise
def store_organization(self, organization):
""" Stores organization into database
"""
with admin_transaction() as cursor:
try:
query = "INSERT INTO organization (organization_name,sorting) VALUES (%s, %s)"
cursor.execute(query, (organization.name, str(organization.sorting)))
except:
conf.log.exception("Exception. store_organization call failed.")
raise
return True
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 deleteUser(self, user):
""" Function for removing user from persistent storage
"""
self.__cache.clear_user_by_user(user)
query = "DELETE FROM user WHERE user_id = %s"
with admin_transaction() as cursor:
try:
cursor.execute(query, user.id)
except:
conf.log.exception('Failed to delete the user: %s' % user)
return False
return True
def unset_flag(self, user_id, flag):
"""
Removes a flag from message. See FLAG_* variables
:param int user_id: Id of the user who is to be flagged
:param int flag: Flag value (removes the matching flag)
"""
sql = '''
DELETE FROM message_flag
WHERE id = %s AND user_id = %s AND flag = %s
'''
with admin_transaction() as cursor:
cursor.execute(sql, (self.id, user_id, flag))
def clear_project_visibilities(self, project_id):
query = "DELETE FROM project_user_visibility WHERE project_id = %s"
with admin_transaction() as cursor:
try:
cursor.execute(query, project_id)
except Exception as e:
if self.verbose is not None:
print "Exception. In method clear_visibilities, the following query failed."
print query
print e
conf.log.exception("Exception. ProjectUserVisibilityGenerator.clear_visibilities "
"failed with query '''%s'''." % query)
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 add_public_project_visibility(self, project_id):
# check if anonymous user exists
anon = get_userstore().getUser('anonymous')
if not anon:
return
# add entry to the visibility table if not there yet
sql = "INSERT IGNORE INTO project_user_visibility VALUES(%s, %s)"
with admin_transaction() as cursor:
try:
cursor.execute(sql, (project_id, anon.id))
except Exception, e:
conf.log.exception("Project query failed: {0}".format(sql))
def remove_public_project_visibility(self, project_id):
# check if anonymous user exists
anon = get_userstore().getUser('anonymous')
if not anon:
return
# remove entry from the visibility table
sql = "DELETE FROM project_user_visibility WHERE project_id=%s AND user_id=%s LIMIT 1"
with admin_transaction() as cursor:
try:
cursor.execute(sql, (project_id, anon.id))
except Exception, e:
conf.log.exception("Project query failed: {0}".format(sql))
def delete(self):
if self._default is not None:
raise TracError("Downloads directory is not configurable")
query = """
DELETE FROM `{0}`.system WHERE name = 'files_downloads_dir'
""".format(self.env_name)
try:
with admin_transaction() as cursor:
cursor.execute(query)
self.mc.delete(self._memcache_key())
except Exception:
conf.log.exception("Exception. ProjectDownloadConfig save failed.")
raise TracError("Error while saving files download information.")
def clean_ssh_key_update_flags(self):
""" Cleans ssh key update flags
"""
query = "DELETE FROM ssh_key_update"
with admin_transaction() as cursor:
try:
cursor.execute(query)
except:
conf.log.exception(
"Exception. clean_ssh_key_update_flags procedure failed: %s"
% query)
return False
return True
def rename_news_forum(self, news_forum_name):
"""
Renames the news forum's subject. The actual forum name cannot be changed.
:param str news_forum_name: New name for the news forum subject
"""
query = "UPDATE `%s`.forum SET subject = %%s WHERE name = %%s" % self.env_name
with db.admin_transaction() as cursor:
try:
cursor.execute(query, (news_forum_name, self.news_forum_name))
except Exception:
self.log.exception("SQL query failed: %s" % query)
raise
def clear_project_visibilities(self, project_id):
query = "DELETE FROM project_user_visibility WHERE project_id = %s"
with admin_transaction() as cursor:
try:
cursor.execute(query, project_id)
except Exception as e:
if self.verbose is not None:
print "Exception. In method clear_visibilities, the following query failed."
print query
print e
conf.log.exception(
"Exception. ProjectUserVisibilityGenerator.clear_visibilities "
"failed with query '''%s'''." % query)
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 db_downgrade(self, commands):
"""
:param commands: List of queries
:return: False on failure
"""
with admin_transaction() as cr:
try:
for command in commands:
cr.execute(command)
except MySQLdb.IntegrityError:
log.exception(
"Exception. Migration downgrade failed with commands '''%s'''."
% str(commands))
return False
return True
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 db_upgrade(self, commands):
"""
:param commands: List of queries
:return: False on failure
"""
with admin_transaction() as cr:
current_query = None
try:
for command in commands:
current_query = command
cr.execute(command)
except MySQLdb.IntegrityError:
log.exception("Exception. Migration failed with query: %s" %
str(current_query))
return False
return True
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 request_membership(self, authname, message):
users = get_userstore()
user = users.getUser(authname)
with admin_transaction() as cursor:
query = "INSERT INTO membership_request VALUES(%s, %s)"
try:
cursor.execute(query, (self.project.id, user.id))
except:
self.env.log.exception(
"Exception. MembershipApi.request_membership query failed. '''%s'''"
% query)
req_notifier = MembershipRequestedNotifier(self.env, self.project,
message, authname)
req_notifier.notify_admins()
def save(self):
if self._default is not None and self._default != self._downloads_dir:
raise TracError("Downloads directory is not configurable")
conf.log.debug("saving files_downloads_dir value %s to DB" %
self._downloads_dir)
query = """
INSERT IGNORE INTO `{0}`.system (name, value)
VALUES ('files_downloads_dir', %s)
ON DUPLICATE KEY UPDATE value=VALUES(value)
""".format(self.env_name)
try:
with admin_transaction() as cursor:
cursor.execute(query, self._downloads_dir)
self.mc.delete(self._memcache_key())
except Exception:
conf.log.exception("Exception. ProjectDownloadConfig save failed.")
raise TracError("Error while saving files download information.")
def create_permission_id(permission_name):
"""
:param str permission_name: Permission name
:return: Created permission id or None
"""
with admin_transaction() as cursor:
try:
cursor.execute("INSERT INTO action(action_string) VALUES(%s)", permission_name)
cursor.execute("SELECT action_id FROM action WHERE action_string = %s", permission_name)
row = cursor.fetchone()
if row is not None:
return row[0]
else:
return None
except Exception:
conf.log.exception("Exception. Failed creating permission id with name '%s'" % str(permission_name))
raise
def _remove_membership_req(self, username):
user = get_userstore().getUser(username)
if not user:
raise TracError('User cannot be found with name: "%s"' % username)
query = """
DELETE FROM membership_request
WHERE project_key = %s AND user_key = %s
"""
with admin_transaction() as cursor:
try:
cursor.execute(query, (self.project.id, user.id))
except:
self.env.log.exception(
"Exception. MembershipApi._remove_membership_req query failed. '''%s'''"
% query)
def clean_up(self, days_to_keep=None):
""" Clean up old events
"""
# If no days given, we do not clean up anything
if days_to_keep is None:
return
# Today in local time
to_date = datetime.now(datefmt.localtz)
sql_date = self._sql_date(to_date)
# Query for deleting everything before days_to_keep from today
query = "DELETE FROM timeline_cache WHERE date < DATE_SUB('%s', INTERVAL %s DAY)" % (sql_date, days_to_keep)
with admin_transaction() as cursor:
try:
cursor.execute(query)
except:
conf.log.exception("Timeline cache cleanup failed")
def add_ssh_key(self, user_id, ssh_key, description):
""" Add a new ssh key for user
"""
query = """
INSERT INTO ssh_keys
VALUES(NULL, %s, %s, %s, NOW())
"""
with admin_transaction() as cursor:
try:
cursor.execute(query, (user_id, ssh_key, description))
except:
conf.log.exception("Failed to add SSH key")
return False
if not self.add_ssh_key_update_flag():
return False
return True
def create_authentication(self, authentication_name):
try:
with admin_transaction() as cursor:
try:
query = "INSERT IGNORE INTO authentication(method) VALUES(%s)"
cursor.execute(query, authentication_name)
except:
conf.log.exception(
"Exception. create_authentication(%s) query failed" %
str(authentication_name))
# Re-raise the exception so that the context manager can roll back
raise
except:
# Return False on failure, but trust logging is used in context manager and
# query execution
return False
return True
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)