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 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 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 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 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 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 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, 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 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 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 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 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_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 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 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