appengine/monorail/services/features_svc.py - infra/infra - Git at Google

 # Copyright 2016 The Chromium Authors. All rights reserved.
 # Use of this source code is govered by a BSD-style
 # license that can be found in the LICENSE file or at
 # https://developers.google.com/open-source/licenses/bsd

 """A class that provides persistence for Monorail's additional features.

 Business objects are described in tracker_pb2.py and tracker_bizobj.py.
 """

 import collections
 import logging

 from features import filterrules_helpers
 from framework import sql
 from tracker import tracker_bizobj
 from tracker import tracker_constants

 QUICKEDITHISTORY_TABLE_NAME = 'QuickEditHistory'
 QUICKEDITMOSTRECENT_TABLE_NAME = 'QuickEditMostRecent'
 SAVEDQUERY_TABLE_NAME = 'SavedQuery'
 PROJECT2SAVEDQUERY_TABLE_NAME = 'Project2SavedQuery'
 SAVEDQUERYEXECUTESINPROJECT_TABLE_NAME = 'SavedQueryExecutesInProject'
 USER2SAVEDQUERY_TABLE_NAME = 'User2SavedQuery'
 FILTERRULE_TABLE_NAME = 'FilterRule'
 FILTERRULE_COLS = ['project_id', 'rank', 'predicate', 'consequence']


 QUICKEDITHISTORY_COLS = [
     'user_id', 'project_id', 'slot_num', 'command', 'comment']
 QUICKEDITMOSTRECENT_COLS = ['user_id', 'project_id', 'slot_num']
 SAVEDQUERY_COLS = ['id', 'name', 'base_query_id', 'query']
 PROJECT2SAVEDQUERY_COLS = ['project_id', 'rank', 'query_id']
 SAVEDQUERYEXECUTESINPROJECT_COLS = ['query_id', 'project_id']
 USER2SAVEDQUERY_COLS = ['user_id', 'rank', 'query_id', 'subscription_mode']


 class FeaturesService(object):
   """The persistence layer for servlets in the features directory."""

   def __init__(self, cache_manager):
     """Initialize this object so that it is ready to use.

     Args:
       cache_manager: local cache with distributed invalidation.
     """
     self.quickedithistory_tbl = sql.SQLTableManager(QUICKEDITHISTORY_TABLE_NAME)
     self.quickeditmostrecent_tbl = sql.SQLTableManager(
         QUICKEDITMOSTRECENT_TABLE_NAME)

     self.savedquery_tbl = sql.SQLTableManager(SAVEDQUERY_TABLE_NAME)
     self.project2savedquery_tbl = sql.SQLTableManager(
         PROJECT2SAVEDQUERY_TABLE_NAME)
     self.savedqueryexecutesinproject_tbl = sql.SQLTableManager(
         SAVEDQUERYEXECUTESINPROJECT_TABLE_NAME)
     self.user2savedquery_tbl = sql.SQLTableManager(USER2SAVEDQUERY_TABLE_NAME)

     self.filterrule_tbl = sql.SQLTableManager(FILTERRULE_TABLE_NAME)

     self.saved_query_cache = cache_manager.MakeCache('user', max_size=1000)

   ### QuickEdit command history

   def GetRecentCommands(self, cnxn, user_id, project_id):
     """Return recent command items for the "Redo" menu.

     Args:
       cnxn: Connection to SQL database.
       user_id: int ID of the current user.
       project_id: int ID of the current project.

     Returns:
       A pair (cmd_slots, recent_slot_num).  cmd_slots is a list of
       3-tuples that can be used to populate the "Redo" menu of the
       quick-edit dialog.  recent_slot_num indicates which of those
       slots should initially populate the command and comment fields.
     """
     # Always start with the standard 5 commands.
     history = tracker_constants.DEFAULT_RECENT_COMMANDS[:]
     # If the user has modified any, then overwrite some standard ones.
     history_rows = self.quickedithistory_tbl.Select(
         cnxn, cols=['slot_num', 'command', 'comment'],
         user_id=user_id, project_id=project_id)
     for slot_num, command, comment in history_rows:
       if slot_num < len(history):
         history[slot_num - 1] = (command, comment)

     slots = []
     for idx, (command, comment) in enumerate(history):
       slots.append((idx + 1, command, comment))

     recent_slot_num = self.quickeditmostrecent_tbl.SelectValue(
         cnxn, 'slot_num', default=1, user_id=user_id, project_id=project_id)

     return slots, recent_slot_num

   def StoreRecentCommand(
       self, cnxn, user_id, project_id, slot_num, command, comment):
     """Store the given command and comment in the user's command history."""
     self.quickedithistory_tbl.InsertRow(
         cnxn, replace=True, user_id=user_id, project_id=project_id,
         slot_num=slot_num, command=command, comment=comment)
     self.quickeditmostrecent_tbl.InsertRow(
         cnxn, replace=True, user_id=user_id, project_id=project_id,
         slot_num=slot_num)

   def ExpungeQuickEditHistory(self, cnxn, project_id):
     """Completely delete every users' quick edit history for this project."""
     self.quickeditmostrecent_tbl.Delete(cnxn, project_id=project_id)
     self.quickedithistory_tbl.Delete(cnxn, project_id=project_id)

   ### Saved User and Project Queries

   def GetSavedQueries(self, cnxn, query_ids):
     """Retrieve the specified SaveQuery PBs."""
     # TODO(jrobbins): RAM cache
     saved_queries = {}
     savedquery_rows = self.savedquery_tbl.Select(
         cnxn, cols=SAVEDQUERY_COLS, id=query_ids)
     for saved_query_tuple in savedquery_rows:
       qid, name, base_id, query = saved_query_tuple
       saved_queries[qid] = tracker_bizobj.MakeSavedQuery(
           qid, name, base_id, query)

     sqeip_rows = self.savedqueryexecutesinproject_tbl.Select(
         cnxn, cols=SAVEDQUERYEXECUTESINPROJECT_COLS,
         query_id=query_ids)
     for query_id, project_id in sqeip_rows:
       saved_queries[query_id].executes_in_project_ids.append(project_id)

     return saved_queries

   def GetSavedQuery(self, cnxn, query_id):
     """Retrieve the specified SaveQuery PB."""
     saved_queries = self.GetSavedQueries(cnxn, [query_id])
     return saved_queries[query_id]

   def _GetUsersSavedQueriesDict(self, cnxn, user_ids):
     """Return a dict of all SavedQuery PBs for the specified users."""
     results_dict, missed_uids = self.saved_query_cache.GetAll(user_ids)

     if missed_uids:
       savedquery_rows = self.user2savedquery_tbl.Select(
           cnxn, cols=SAVEDQUERY_COLS + ['user_id', 'subscription_mode'],
           left_joins=[('SavedQuery ON query_id = id', [])],
           order_by=[('rank', [])], user_id=missed_uids)
       sqeip_rows = self.savedqueryexecutesinproject_tbl.Select(
           cnxn, cols=SAVEDQUERYEXECUTESINPROJECT_COLS,
           query_id={row[0] for row in savedquery_rows})
       sqeip_dict = {}
       for qid, pid in sqeip_rows:
         sqeip_dict.setdefault(qid, []).append(pid)

       for saved_query_tuple in savedquery_rows:
         query_id, name, base_id, query, uid, sub_mode = saved_query_tuple
         sq = tracker_bizobj.MakeSavedQuery(
             query_id, name, base_id, query, subscription_mode=sub_mode,
             executes_in_project_ids=sqeip_dict.get(query_id, []))
         results_dict.setdefault(uid, []).append(sq)

     self.saved_query_cache.CacheAll(results_dict)
     return results_dict

   # TODO(jrobbins): change this termonology to "canned query" rather than
   # "saved" throughout the application.
   def GetSavedQueriesByUserID(self, cnxn, user_id):
     """Return a list of SavedQuery PBs for the specified user."""
     saved_queries_dict = self._GetUsersSavedQueriesDict(cnxn, [user_id])
     saved_queries = saved_queries_dict.get(user_id, [])
     return saved_queries[:]

   def GetCannedQueriesForProjects(self, cnxn, project_ids):
     """Return a dict {project_id: [saved_query]} for the specified projects."""
     # TODO(jrobbins): caching
     cannedquery_rows = self.project2savedquery_tbl.Select(
         cnxn, cols=['project_id'] + SAVEDQUERY_COLS,
         left_joins=[('SavedQuery ON query_id = id', [])],
         order_by=[('rank', [])], project_id=project_ids)

     result_dict = collections.defaultdict(list)
     for cq_row in cannedquery_rows:
       project_id = cq_row[0]
       canned_query_tuple = cq_row[1:]
       result_dict[project_id].append(
           tracker_bizobj.MakeSavedQuery(*canned_query_tuple))

     return result_dict

   def GetCannedQueriesByProjectID(self, cnxn, project_id):
     """Return the list of SavedQueries for the specified project."""
     project_ids_to_canned_queries = self.GetCannedQueriesForProjects(
         cnxn, [project_id])
     return project_ids_to_canned_queries.get(project_id, [])

   def _UpdateSavedQueries(self, cnxn, saved_queries, commit=True):
     """Store the given SavedQueries to the DB."""
     savedquery_rows = [
         (sq.query_id or None, sq.name, sq.base_query_id, sq.query)
         for sq in saved_queries]
     existing_query_ids = [sq.query_id for sq in saved_queries if sq.query_id]
     if existing_query_ids:
       self.savedquery_tbl.Delete(cnxn, id=existing_query_ids, commit=commit)

     generated_ids = self.savedquery_tbl.InsertRows(
         cnxn, SAVEDQUERY_COLS, savedquery_rows, commit=commit,
         return_generated_ids=True)
     if generated_ids:
       logging.info('generated_ids are %r', generated_ids)
       for sq in saved_queries:
         generated_id = generated_ids.pop(0)
         if not sq.query_id:
           sq.query_id = generated_id

   def UpdateCannedQueries(self, cnxn, project_id, canned_queries):
     """Update the canned queries for a project.

     Args:
       cnxn: connection to SQL database.
       project_id: int project ID of the project that contains these queries.
       canned_queries: list of SavedQuery PBs to update.
     """
     self.project2savedquery_tbl.Delete(
         cnxn, project_id=project_id, commit=False)
     self._UpdateSavedQueries(cnxn, canned_queries, commit=False)
     project2savedquery_rows = [
         (project_id, rank, sq.query_id)
         for rank, sq in enumerate(canned_queries)]
     self.project2savedquery_tbl.InsertRows(
         cnxn, PROJECT2SAVEDQUERY_COLS, project2savedquery_rows,
         commit=False)
     cnxn.Commit()

   def UpdateUserSavedQueries(self, cnxn, user_id, saved_queries):
     """Store the given saved_queries for the given user."""
     saved_query_ids = [sq.query_id for sq in saved_queries if sq.query_id]
     self.savedqueryexecutesinproject_tbl.Delete(
         cnxn, query_id=saved_query_ids, commit=False)
     self.user2savedquery_tbl.Delete(cnxn, user_id=user_id, commit=False)

     self._UpdateSavedQueries(cnxn, saved_queries, commit=False)
     user2savedquery_rows = []
     for rank, sq in enumerate(saved_queries):
       user2savedquery_rows.append(
           (user_id, rank, sq.query_id, sq.subscription_mode or 'noemail'))

     self.user2savedquery_tbl.InsertRows(
         cnxn, USER2SAVEDQUERY_COLS, user2savedquery_rows, commit=False)

     sqeip_rows = []
     for sq in saved_queries:
       for pid in sq.executes_in_project_ids:
         sqeip_rows.append((sq.query_id, pid))

     self.savedqueryexecutesinproject_tbl.InsertRows(
         cnxn, SAVEDQUERYEXECUTESINPROJECT_COLS, sqeip_rows, commit=False)
     cnxn.Commit()

     self.saved_query_cache.Invalidate(cnxn, user_id)

   ### Subscriptions

   def GetSubscriptionsInProjects(self, cnxn, project_ids):
     """Return all saved queries for users that have any subscription there.

     Args:
       cnxn: Connection to SQL database.
       project_ids: list of int project IDs that contain the modified issues.

     Returns:
       A dict {user_id: all_saved_queries, ...} for all users that have any
       subscription in any of the specified projects.
     """
     join_str = (
         'SavedQueryExecutesInProject ON '
         'SavedQueryExecutesInProject.query_id = User2SavedQuery.query_id')
     # TODO(jrobbins): cache this since it rarely changes.
     subscriber_rows = self.user2savedquery_tbl.Select(
         cnxn, cols=['user_id'], distinct=True,
         joins=[(join_str, [])],
         subscription_mode='immediate', project_id=project_ids)
     subscriber_ids = [row[0] for row in subscriber_rows]
     logging.info('subscribers relevant to projects %r are %r',
                  project_ids, subscriber_ids)
     user_ids_to_saved_queries = self._GetUsersSavedQueriesDict(
         cnxn, subscriber_ids)
     return user_ids_to_saved_queries

   def ExpungeSavedQueriesExecuteInProject(self, cnxn, project_id):
     """Remove any references from saved queries to projects in the database."""
     self.savedqueryexecutesinproject_tbl.Delete(cnxn, project_id=project_id)

     savedquery_rows = self.project2savedquery_tbl.Select(
         cnxn, cols=['query_id'], project_id=project_id)
     savedquery_ids = [row[0] for row in savedquery_rows]
     self.project2savedquery_tbl.Delete(cnxn, project_id=project_id)
     self.savedquery_tbl.Delete(cnxn, id=savedquery_ids)

   ### Filter rules

   def _DeserializeFilterRules(self, filterrule_rows):
     """Convert the given DB row tuples into PBs."""
     result_dict = collections.defaultdict(list)

     for filterrule_row in sorted(filterrule_rows):
       project_id, _rank, predicate, consequence = filterrule_row
       (default_status, default_owner_id, add_cc_ids, add_labels,
        add_notify) = self._DeserializeRuleConsequence(consequence)
       rule = filterrules_helpers.MakeRule(
           predicate, default_status=default_status,
           default_owner_id=default_owner_id, add_cc_ids=add_cc_ids,
           add_labels=add_labels, add_notify=add_notify)
       result_dict[project_id].append(rule)

     return result_dict

   def _DeserializeRuleConsequence(self, consequence):
     """Decode the THEN-part of a filter rule."""
     (default_status, default_owner_id, add_cc_ids, add_labels,
      add_notify) = None, None, [], [], []
     for action in consequence.split():
       verb, noun = action.split(':')
       if verb == 'default_status':
         default_status = noun
       elif verb == 'default_owner_id':
         default_owner_id = int(noun)
       elif verb == 'add_cc_id':
         add_cc_ids.append(int(noun))
       elif verb == 'add_label':
         add_labels.append(noun)
       elif verb == 'add_notify':
         add_notify.append(noun)

     return (default_status, default_owner_id, add_cc_ids, add_labels,
             add_notify)

   def _GetFilterRulesByProjectIDs(self, cnxn, project_ids):
     """Return {project_id: [FilterRule, ...]} for the specified projects."""
     # TODO(jrobbins): caching
     filterrule_rows = self.filterrule_tbl.Select(
         cnxn, cols=FILTERRULE_COLS, project_id=project_ids)
     return self._DeserializeFilterRules(filterrule_rows)

   def GetFilterRules(self, cnxn, project_id):
     """Return a list of FilterRule PBs for the specified project."""
     rules_by_project_id = self._GetFilterRulesByProjectIDs(cnxn, [project_id])
     return rules_by_project_id[project_id]

   def _SerializeRuleConsequence(self, rule):
     """Put all actions of a filter rule into one string."""
     assignments = []
     for add_lab in rule.add_labels:
       assignments.append('add_label:%s' % add_lab)
     if rule.default_status:
       assignments.append('default_status:%s' % rule.default_status)
     if rule.default_owner_id:
       assignments.append('default_owner_id:%d' % rule.default_owner_id)
     for add_cc_id in rule.add_cc_ids:
       assignments.append('add_cc_id:%d' % add_cc_id)
     for add_notify in rule.add_notify_addrs:
       assignments.append('add_notify:%s' % add_notify)

     return ' '.join(assignments)

   def UpdateFilterRules(self, cnxn, project_id, rules):
     """Update the filter rules part of a project's issue configuration.

     Args:
       cnxn: connection to SQL database.
       project_id: int ID of the current project.
       rules: a list of FilterRule PBs.
     """
     rows = []
     for rank, rule in enumerate(rules):
       predicate = rule.predicate
       consequence = self._SerializeRuleConsequence(rule)
       if predicate and consequence:
         rows.append((project_id, rank, predicate, consequence))

     self.filterrule_tbl.Delete(cnxn, project_id=project_id)
     self.filterrule_tbl.InsertRows(cnxn, FILTERRULE_COLS, rows)

   def ExpungeFilterRules(self, cnxn, project_id):
     """Completely destroy filter rule info for the specified project."""
     self.filterrule_tbl.Delete(cnxn, project_id=project_id)
	# Copyright 2016 The Chromium Authors. All rights reserved.
	# Use of this source code is govered by a BSD-style
	# license that can be found in the LICENSE file or at
	# https://developers.google.com/open-source/licenses/bsd

	"""A class that provides persistence for Monorail's additional features.

	Business objects are described in tracker_pb2.py and tracker_bizobj.py.
	"""

	import collections
	import logging

	from features import filterrules_helpers
	from framework import sql
	from tracker import tracker_bizobj
	from tracker import tracker_constants

	QUICKEDITHISTORY_TABLE_NAME = 'QuickEditHistory'
	QUICKEDITMOSTRECENT_TABLE_NAME = 'QuickEditMostRecent'
	SAVEDQUERY_TABLE_NAME = 'SavedQuery'
	PROJECT2SAVEDQUERY_TABLE_NAME = 'Project2SavedQuery'
	SAVEDQUERYEXECUTESINPROJECT_TABLE_NAME = 'SavedQueryExecutesInProject'
	USER2SAVEDQUERY_TABLE_NAME = 'User2SavedQuery'
	FILTERRULE_TABLE_NAME = 'FilterRule'
	FILTERRULE_COLS = ['project_id', 'rank', 'predicate', 'consequence']


	QUICKEDITHISTORY_COLS = [
	'user_id', 'project_id', 'slot_num', 'command', 'comment']
	QUICKEDITMOSTRECENT_COLS = ['user_id', 'project_id', 'slot_num']
	SAVEDQUERY_COLS = ['id', 'name', 'base_query_id', 'query']
	PROJECT2SAVEDQUERY_COLS = ['project_id', 'rank', 'query_id']
	SAVEDQUERYEXECUTESINPROJECT_COLS = ['query_id', 'project_id']
	USER2SAVEDQUERY_COLS = ['user_id', 'rank', 'query_id', 'subscription_mode']


	class FeaturesService(object):
	"""The persistence layer for servlets in the features directory."""

	def __init__(self, cache_manager):
	"""Initialize this object so that it is ready to use.

	Args:
	cache_manager: local cache with distributed invalidation.
	"""
	self.quickedithistory_tbl = sql.SQLTableManager(QUICKEDITHISTORY_TABLE_NAME)
	self.quickeditmostrecent_tbl = sql.SQLTableManager(
	QUICKEDITMOSTRECENT_TABLE_NAME)

	self.savedquery_tbl = sql.SQLTableManager(SAVEDQUERY_TABLE_NAME)
	self.project2savedquery_tbl = sql.SQLTableManager(
	PROJECT2SAVEDQUERY_TABLE_NAME)
	self.savedqueryexecutesinproject_tbl = sql.SQLTableManager(
	SAVEDQUERYEXECUTESINPROJECT_TABLE_NAME)
	self.user2savedquery_tbl = sql.SQLTableManager(USER2SAVEDQUERY_TABLE_NAME)

	self.filterrule_tbl = sql.SQLTableManager(FILTERRULE_TABLE_NAME)

	self.saved_query_cache = cache_manager.MakeCache('user', max_size=1000)

	### QuickEdit command history

	def GetRecentCommands(self, cnxn, user_id, project_id):
	"""Return recent command items for the "Redo" menu.

	Args:
	cnxn: Connection to SQL database.
	user_id: int ID of the current user.
	project_id: int ID of the current project.

	Returns:
	A pair (cmd_slots, recent_slot_num). cmd_slots is a list of
	3-tuples that can be used to populate the "Redo" menu of the
	quick-edit dialog. recent_slot_num indicates which of those
	slots should initially populate the command and comment fields.
	"""
	# Always start with the standard 5 commands.
	history = tracker_constants.DEFAULT_RECENT_COMMANDS[:]
	# If the user has modified any, then overwrite some standard ones.
	history_rows = self.quickedithistory_tbl.Select(
	cnxn, cols=['slot_num', 'command', 'comment'],
	user_id=user_id, project_id=project_id)
	for slot_num, command, comment in history_rows:
	if slot_num < len(history):
	history[slot_num - 1] = (command, comment)

	slots = []
	for idx, (command, comment) in enumerate(history):
	slots.append((idx + 1, command, comment))

	recent_slot_num = self.quickeditmostrecent_tbl.SelectValue(
	cnxn, 'slot_num', default=1, user_id=user_id, project_id=project_id)

	return slots, recent_slot_num

	def StoreRecentCommand(
	self, cnxn, user_id, project_id, slot_num, command, comment):
	"""Store the given command and comment in the user's command history."""
	self.quickedithistory_tbl.InsertRow(
	cnxn, replace=True, user_id=user_id, project_id=project_id,
	slot_num=slot_num, command=command, comment=comment)
	self.quickeditmostrecent_tbl.InsertRow(
	cnxn, replace=True, user_id=user_id, project_id=project_id,
	slot_num=slot_num)

	def ExpungeQuickEditHistory(self, cnxn, project_id):
	"""Completely delete every users' quick edit history for this project."""
	self.quickeditmostrecent_tbl.Delete(cnxn, project_id=project_id)
	self.quickedithistory_tbl.Delete(cnxn, project_id=project_id)

	### Saved User and Project Queries

	def GetSavedQueries(self, cnxn, query_ids):
	"""Retrieve the specified SaveQuery PBs."""
	# TODO(jrobbins): RAM cache
	saved_queries = {}
	savedquery_rows = self.savedquery_tbl.Select(
	cnxn, cols=SAVEDQUERY_COLS, id=query_ids)
	for saved_query_tuple in savedquery_rows:
	qid, name, base_id, query = saved_query_tuple
	saved_queries[qid] = tracker_bizobj.MakeSavedQuery(
	qid, name, base_id, query)

	sqeip_rows = self.savedqueryexecutesinproject_tbl.Select(
	cnxn, cols=SAVEDQUERYEXECUTESINPROJECT_COLS,
	query_id=query_ids)
	for query_id, project_id in sqeip_rows:
	saved_queries[query_id].executes_in_project_ids.append(project_id)

	return saved_queries

	def GetSavedQuery(self, cnxn, query_id):
	"""Retrieve the specified SaveQuery PB."""
	saved_queries = self.GetSavedQueries(cnxn, [query_id])
	return saved_queries[query_id]

	def _GetUsersSavedQueriesDict(self, cnxn, user_ids):
	"""Return a dict of all SavedQuery PBs for the specified users."""
	results_dict, missed_uids = self.saved_query_cache.GetAll(user_ids)

	if missed_uids:
	savedquery_rows = self.user2savedquery_tbl.Select(
	cnxn, cols=SAVEDQUERY_COLS + ['user_id', 'subscription_mode'],
	left_joins=[('SavedQuery ON query_id = id', [])],
	order_by=[('rank', [])], user_id=missed_uids)
	sqeip_rows = self.savedqueryexecutesinproject_tbl.Select(
	cnxn, cols=SAVEDQUERYEXECUTESINPROJECT_COLS,
	query_id={row[0] for row in savedquery_rows})
	sqeip_dict = {}
	for qid, pid in sqeip_rows:
	sqeip_dict.setdefault(qid, []).append(pid)

	for saved_query_tuple in savedquery_rows:
	query_id, name, base_id, query, uid, sub_mode = saved_query_tuple
	sq = tracker_bizobj.MakeSavedQuery(
	query_id, name, base_id, query, subscription_mode=sub_mode,
	executes_in_project_ids=sqeip_dict.get(query_id, []))
	results_dict.setdefault(uid, []).append(sq)

	self.saved_query_cache.CacheAll(results_dict)
	return results_dict

	# TODO(jrobbins): change this termonology to "canned query" rather than
	# "saved" throughout the application.
	def GetSavedQueriesByUserID(self, cnxn, user_id):
	"""Return a list of SavedQuery PBs for the specified user."""
	saved_queries_dict = self._GetUsersSavedQueriesDict(cnxn, [user_id])
	saved_queries = saved_queries_dict.get(user_id, [])
	return saved_queries[:]

	def GetCannedQueriesForProjects(self, cnxn, project_ids):
	"""Return a dict {project_id: [saved_query]} for the specified projects."""
	# TODO(jrobbins): caching
	cannedquery_rows = self.project2savedquery_tbl.Select(
	cnxn, cols=['project_id'] + SAVEDQUERY_COLS,
	left_joins=[('SavedQuery ON query_id = id', [])],
	order_by=[('rank', [])], project_id=project_ids)

	result_dict = collections.defaultdict(list)
	for cq_row in cannedquery_rows:
	project_id = cq_row[0]
	canned_query_tuple = cq_row[1:]
	result_dict[project_id].append(
	tracker_bizobj.MakeSavedQuery(*canned_query_tuple))

	return result_dict

	def GetCannedQueriesByProjectID(self, cnxn, project_id):
	"""Return the list of SavedQueries for the specified project."""
	project_ids_to_canned_queries = self.GetCannedQueriesForProjects(
	cnxn, [project_id])
	return project_ids_to_canned_queries.get(project_id, [])

	def _UpdateSavedQueries(self, cnxn, saved_queries, commit=True):
	"""Store the given SavedQueries to the DB."""
	savedquery_rows = [
	(sq.query_id or None, sq.name, sq.base_query_id, sq.query)
	for sq in saved_queries]
	existing_query_ids = [sq.query_id for sq in saved_queries if sq.query_id]
	if existing_query_ids:
	self.savedquery_tbl.Delete(cnxn, id=existing_query_ids, commit=commit)

	generated_ids = self.savedquery_tbl.InsertRows(
	cnxn, SAVEDQUERY_COLS, savedquery_rows, commit=commit,
	return_generated_ids=True)
	if generated_ids:
	logging.info('generated_ids are %r', generated_ids)
	for sq in saved_queries:
	generated_id = generated_ids.pop(0)
	if not sq.query_id:
	sq.query_id = generated_id

	def UpdateCannedQueries(self, cnxn, project_id, canned_queries):
	"""Update the canned queries for a project.

	Args:
	cnxn: connection to SQL database.
	project_id: int project ID of the project that contains these queries.
	canned_queries: list of SavedQuery PBs to update.
	"""
	self.project2savedquery_tbl.Delete(
	cnxn, project_id=project_id, commit=False)
	self._UpdateSavedQueries(cnxn, canned_queries, commit=False)
	project2savedquery_rows = [
	(project_id, rank, sq.query_id)
	for rank, sq in enumerate(canned_queries)]
	self.project2savedquery_tbl.InsertRows(
	cnxn, PROJECT2SAVEDQUERY_COLS, project2savedquery_rows,
	commit=False)
	cnxn.Commit()

	def UpdateUserSavedQueries(self, cnxn, user_id, saved_queries):
	"""Store the given saved_queries for the given user."""
	saved_query_ids = [sq.query_id for sq in saved_queries if sq.query_id]
	self.savedqueryexecutesinproject_tbl.Delete(
	cnxn, query_id=saved_query_ids, commit=False)
	self.user2savedquery_tbl.Delete(cnxn, user_id=user_id, commit=False)

	self._UpdateSavedQueries(cnxn, saved_queries, commit=False)
	user2savedquery_rows = []
	for rank, sq in enumerate(saved_queries):
	user2savedquery_rows.append(
	(user_id, rank, sq.query_id, sq.subscription_mode or 'noemail'))

	self.user2savedquery_tbl.InsertRows(
	cnxn, USER2SAVEDQUERY_COLS, user2savedquery_rows, commit=False)

	sqeip_rows = []
	for sq in saved_queries:
	for pid in sq.executes_in_project_ids:
	sqeip_rows.append((sq.query_id, pid))

	self.savedqueryexecutesinproject_tbl.InsertRows(
	cnxn, SAVEDQUERYEXECUTESINPROJECT_COLS, sqeip_rows, commit=False)
	cnxn.Commit()

	self.saved_query_cache.Invalidate(cnxn, user_id)

	### Subscriptions

	def GetSubscriptionsInProjects(self, cnxn, project_ids):
	"""Return all saved queries for users that have any subscription there.

	Args:
	cnxn: Connection to SQL database.
	project_ids: list of int project IDs that contain the modified issues.

	Returns:
	A dict {user_id: all_saved_queries, ...} for all users that have any
	subscription in any of the specified projects.
	"""
	join_str = (
	'SavedQueryExecutesInProject ON '
	'SavedQueryExecutesInProject.query_id = User2SavedQuery.query_id')
	# TODO(jrobbins): cache this since it rarely changes.
	subscriber_rows = self.user2savedquery_tbl.Select(
	cnxn, cols=['user_id'], distinct=True,
	joins=[(join_str, [])],
	subscription_mode='immediate', project_id=project_ids)
	subscriber_ids = [row[0] for row in subscriber_rows]
	logging.info('subscribers relevant to projects %r are %r',
	project_ids, subscriber_ids)
	user_ids_to_saved_queries = self._GetUsersSavedQueriesDict(
	cnxn, subscriber_ids)
	return user_ids_to_saved_queries

	def ExpungeSavedQueriesExecuteInProject(self, cnxn, project_id):
	"""Remove any references from saved queries to projects in the database."""
	self.savedqueryexecutesinproject_tbl.Delete(cnxn, project_id=project_id)

	savedquery_rows = self.project2savedquery_tbl.Select(
	cnxn, cols=['query_id'], project_id=project_id)
	savedquery_ids = [row[0] for row in savedquery_rows]
	self.project2savedquery_tbl.Delete(cnxn, project_id=project_id)
	self.savedquery_tbl.Delete(cnxn, id=savedquery_ids)

	### Filter rules

	def _DeserializeFilterRules(self, filterrule_rows):
	"""Convert the given DB row tuples into PBs."""
	result_dict = collections.defaultdict(list)

	for filterrule_row in sorted(filterrule_rows):
	project_id, _rank, predicate, consequence = filterrule_row
	(default_status, default_owner_id, add_cc_ids, add_labels,
	add_notify) = self._DeserializeRuleConsequence(consequence)
	rule = filterrules_helpers.MakeRule(
	predicate, default_status=default_status,
	default_owner_id=default_owner_id, add_cc_ids=add_cc_ids,
	add_labels=add_labels, add_notify=add_notify)
	result_dict[project_id].append(rule)

	return result_dict

	def _DeserializeRuleConsequence(self, consequence):
	"""Decode the THEN-part of a filter rule."""
	(default_status, default_owner_id, add_cc_ids, add_labels,
	add_notify) = None, None, [], [], []
	for action in consequence.split():
	verb, noun = action.split(':')
	if verb == 'default_status':
	default_status = noun
	elif verb == 'default_owner_id':
	default_owner_id = int(noun)
	elif verb == 'add_cc_id':
	add_cc_ids.append(int(noun))
	elif verb == 'add_label':
	add_labels.append(noun)
	elif verb == 'add_notify':
	add_notify.append(noun)

	return (default_status, default_owner_id, add_cc_ids, add_labels,
	add_notify)

	def _GetFilterRulesByProjectIDs(self, cnxn, project_ids):
	"""Return {project_id: [FilterRule, ...]} for the specified projects."""
	# TODO(jrobbins): caching
	filterrule_rows = self.filterrule_tbl.Select(
	cnxn, cols=FILTERRULE_COLS, project_id=project_ids)
	return self._DeserializeFilterRules(filterrule_rows)

	def GetFilterRules(self, cnxn, project_id):
	"""Return a list of FilterRule PBs for the specified project."""
	rules_by_project_id = self._GetFilterRulesByProjectIDs(cnxn, [project_id])
	return rules_by_project_id[project_id]

	def _SerializeRuleConsequence(self, rule):
	"""Put all actions of a filter rule into one string."""
	assignments = []
	for add_lab in rule.add_labels:
	assignments.append('add_label:%s' % add_lab)
	if rule.default_status:
	assignments.append('default_status:%s' % rule.default_status)
	if rule.default_owner_id:
	assignments.append('default_owner_id:%d' % rule.default_owner_id)
	for add_cc_id in rule.add_cc_ids:
	assignments.append('add_cc_id:%d' % add_cc_id)
	for add_notify in rule.add_notify_addrs:
	assignments.append('add_notify:%s' % add_notify)

	return ' '.join(assignments)

	def UpdateFilterRules(self, cnxn, project_id, rules):
	"""Update the filter rules part of a project's issue configuration.

	Args:
	cnxn: connection to SQL database.
	project_id: int ID of the current project.
	rules: a list of FilterRule PBs.
	"""
	rows = []
	for rank, rule in enumerate(rules):
	predicate = rule.predicate
	consequence = self._SerializeRuleConsequence(rule)
	if predicate and consequence:
	rows.append((project_id, rank, predicate, consequence))

	self.filterrule_tbl.Delete(cnxn, project_id=project_id)
	self.filterrule_tbl.InsertRows(cnxn, FILTERRULE_COLS, rows)

	def ExpungeFilterRules(self, cnxn, project_id):
	"""Completely destroy filter rule info for the specified project."""
	self.filterrule_tbl.Delete(cnxn, project_id=project_id)