appengine/monorail/search/ast2select.py

# 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

"""Convert a user's issue search AST into SQL clauses.

The main query is done on the Issues table.
 + Some simple conditions are implemented as WHERE conditions on the Issue
   table rows.  These are generated by the _Compare() function.
 + More complex conditions are implemented via a "LEFT JOIN ... ON ..." clause
   plus a check in the WHERE clause to select only rows where the join's ON
   condition was satisfied.  These are generated by appending a clause to
   the left_joins list plus calling _CompareAlreadyJoined().  Each such left
   join defines a unique alias to keep it separate from other conditions.

The functions that generate SQL snippets need to insert table names, column
names, alias names, and value placeholders into the generated string.  These
functions use the string format() method and the "{varname}" syntax to avoid
confusion with the "%s" syntax used for SQL value placeholders.
"""

import logging

from framework import sql
from proto import ast_pb2
from proto import tracker_pb2
from services import tracker_fulltext


NATIVE_SEARCHABLE_FIELDS = {
    'id': 'local_id',
    'stars': 'star_count',
    'attachments': 'attachment_count',
    'opened': 'opened',
    'closed': 'closed',
    'modified': 'modified',
    'spam': 'is_spam'
    }


def BuildSQLQuery(query_ast):
  """Translate the user's query into an SQL query.

  Args:
    query_ast: user query abstract syntax tree parsed by query2ast.py.

  Returns:
    A pair of lists (left_joins, where) to use when building the SQL SELECT
    statement.  Each of them is a list of (str, [val, ...]) pairs.
  """
  left_joins = []
  where = []
  # TODO(jrobbins): Handle "OR" in queries.  For now, we just process the
  # first conjunction and assume that it is the only one.
  assert len(query_ast.conjunctions) == 1, 'TODO(jrobbins) handle "OR" queries'
  conj = query_ast.conjunctions[0]

  for cond_num, cond in enumerate(conj.conds):
    cond_left_joins, cond_where = _ProcessCond(cond_num, cond)
    left_joins.extend(cond_left_joins)
    where.extend(cond_where)

  return left_joins, where


def _ProcessBlockedOnIDCond(cond, alias, _user_alias):
  """Convert a blockedon_id=issue_id cond to SQL."""
  return _GetBlockIDCond(cond, alias, blocking_id=False)


def _ProcessBlockingIDCond(cond, alias, _user_alias):
  """Convert a blocking_id:1,2 cond to SQL."""
  return _GetBlockIDCond(cond, alias, blocking_id=True)


def _GetBlockIDCond(cond, alias, blocking_id=False):
  """Convert either a blocking_id or blockedon_id cond to SQL.

  If blocking_id is False then it is treated as a blockedon_id request,
  otherwise it is treated as a blocking_id request.
  """
  matching_issue_col = 'issue_id' if blocking_id else 'dst_issue_id'
  ret_issue_col = 'dst_issue_id' if blocking_id else 'issue_id'

  kind_cond_str, kind_cond_args = _Compare(
      alias, ast_pb2.QueryOp.EQ, tracker_pb2.FieldTypes.STR_TYPE, 'kind',
      ['blockedon'])
  left_joins = [(
      ('IssueRelation AS {alias} ON Issue.id = {alias}.%s AND '
       '{kind_cond}' % ret_issue_col).format(
           alias=alias, kind_cond=kind_cond_str), kind_cond_args)]

  field_type, field_values = _GetFieldTypeAndValues(cond)
  if field_values:
    where = [_Compare(
        alias, ast_pb2.QueryOp.EQ, field_type, matching_issue_col,
        field_values)]
  else:
    # If no field values are specified display all issues which have the
    # property.
    where = [_CompareAlreadyJoined(alias, cond.op, ret_issue_col)]

  return left_joins, where


def _GetFieldTypeAndValues(cond):
  """Returns the field type and values to use from the condition.

  This function should be used when we do not know what values are present on
  the condition. Eg: cond.int_values could be set if ast2ast.py preprocessing is
  first done. If that preprocessing is not done then str_values could be set
  instead.
  If both int values and str values exist on the condition then the int values
  are returned.
  """
  if cond.int_values:
    return tracker_pb2.FieldTypes.INT_TYPE, cond.int_values
  else:
    return tracker_pb2.FieldTypes.STR_TYPE, cond.str_values


def _ProcessOwnerCond(cond, alias, _user_alias):
  """Convert an owner:substring cond to SQL."""
  left_joins = [(
      'User AS {alias} ON (Issue.owner_id = {alias}.user_id '
      'OR Issue.derived_owner_id = {alias}.user_id)'.format(
          alias=alias), [])]
  where = [_Compare(alias, cond.op, tracker_pb2.FieldTypes.STR_TYPE, 'email',
                    cond.str_values)]

  return left_joins, where


def _ProcessOwnerIDCond(cond, _alias, _user_alias):
  """Convert an owner_id=user_id cond to SQL."""
  field_type, field_values = _GetFieldTypeAndValues(cond)
  explicit_str, explicit_args = _Compare(
      'Issue', cond.op, field_type, 'owner_id', field_values)
  derived_str, derived_args = _Compare(
      'Issue', cond.op, field_type, 'derived_owner_id', field_values)
  if cond.op in (ast_pb2.QueryOp.NE, ast_pb2.QueryOp.NOT_TEXT_HAS):
    where = [(explicit_str, explicit_args), (derived_str, derived_args)]
  else:
    if cond.op == ast_pb2.QueryOp.IS_NOT_DEFINED:
      op = ' AND '
    else:
      op = ' OR '
    where = [
        ('(' + explicit_str + op + derived_str + ')',
         explicit_args + derived_args)]

  return [], where


def _ProcessReporterCond(cond, alias, _user_alias):
  """Convert a reporter:substring cond to SQL."""
  left_joins = [(
      'User AS {alias} ON Issue.reporter_id = {alias}.user_id'.format(
          alias=alias), [])]
  where = [_Compare(alias, cond.op, tracker_pb2.FieldTypes.STR_TYPE, 'email',
                    cond.str_values)]

  return left_joins, where


def _ProcessReporterIDCond(cond, _alias, _user_alias):
  """Convert a reporter_ID=user_id cond to SQL."""
  field_type, field_values = _GetFieldTypeAndValues(cond)
  where = [_Compare(
      'Issue', cond.op, field_type, 'reporter_id', field_values)]
  return [], where


def _ProcessCcCond(cond, alias, user_alias):
  """Convert a cc:substring cond to SQL."""
  email_cond_str, email_cond_args = _Compare(
      user_alias, cond.op, tracker_pb2.FieldTypes.STR_TYPE, 'email',
      cond.str_values)
  # Note: email_cond_str will have parens, if needed.
  left_joins = [(
      '(Issue2Cc AS {alias} JOIN User AS {user_alias} '
      'ON {alias}.cc_id = {user_alias}.user_id AND {email_cond}) '
      'ON Issue.id = {alias}.issue_id AND '
      'Issue.shard = {alias}.issue_shard'.format(
          alias=alias, user_alias=user_alias, email_cond=email_cond_str),
      email_cond_args)]
  where = [_CompareAlreadyJoined(user_alias, cond.op, 'email')]

  return left_joins, where


def _ProcessCcIDCond(cond, alias, _user_alias):
  """Convert a cc_id=user_id cond to SQL."""
  join_str = (
      'Issue2Cc AS {alias} ON Issue.id = {alias}.issue_id AND '
      'Issue.shard = {alias}.issue_shard'.format(
          alias=alias))
  if cond.op in (ast_pb2.QueryOp.IS_DEFINED, ast_pb2.QueryOp.IS_NOT_DEFINED):
    left_joins = [(join_str, [])]
  else:
    field_type, field_values = _GetFieldTypeAndValues(cond)
    cond_str, cond_args = _Compare(
        alias, ast_pb2.QueryOp.EQ, field_type, 'cc_id', field_values)
    left_joins = [(join_str + ' AND ' + cond_str, cond_args)]

  where = [_CompareAlreadyJoined(alias, cond.op, 'cc_id')]
  return left_joins, where


def _ProcessStarredByCond(cond, alias, user_alias):
  """Convert a starredby:substring cond to SQL."""
  email_cond_str, email_cond_args = _Compare(
      user_alias, cond.op, tracker_pb2.FieldTypes.STR_TYPE, 'email',
      cond.str_values)
  # Note: email_cond_str will have parens, if needed.
  left_joins = [(
      '(IssueStar AS {alias} JOIN User AS {user_alias} '
      'ON {alias}.user_id = {user_alias}.user_id AND {email_cond}) '
      'ON Issue.id = {alias}.issue_id'.format(
          alias=alias, user_alias=user_alias, email_cond=email_cond_str),
      email_cond_args)]
  where = [_CompareAlreadyJoined(user_alias, cond.op, 'email')]

  return left_joins, where


def _ProcessStarredByIDCond(cond, alias, _user_alias):
  """Convert a starredby_id=user_id cond to SQL."""
  join_str = 'IssueStar AS {alias} ON Issue.id = {alias}.issue_id'.format(
      alias=alias)
  if cond.op in (ast_pb2.QueryOp.IS_DEFINED, ast_pb2.QueryOp.IS_NOT_DEFINED):
    left_joins = [(join_str, [])]
  else:
    field_type, field_values = _GetFieldTypeAndValues(cond)
    cond_str, cond_args = _Compare(
        alias, ast_pb2.QueryOp.EQ, field_type, 'user_id', field_values)
    left_joins = [(join_str + ' AND ' + cond_str, cond_args)]

  where = [_CompareAlreadyJoined(alias, cond.op, 'user_id')]
  return left_joins, where


def _ProcessCommentByCond(cond, alias, user_alias):
  """Convert a commentby:substring cond to SQL."""
  email_cond_str, email_cond_args = _Compare(
      user_alias, cond.op, tracker_pb2.FieldTypes.STR_TYPE, 'email',
      cond.str_values)
  # Note: email_cond_str will have parens, if needed.
  left_joins = [(
      '(Comment AS {alias} JOIN User AS {user_alias} '
      'ON {alias}.commenter_id = {user_alias}.user_id AND {email_cond}) '
      'ON Issue.id = {alias}.issue_id'.format(
          alias=alias, user_alias=user_alias, email_cond=email_cond_str),
      email_cond_args)]
  where = [_CompareAlreadyJoined(user_alias, cond.op, 'email')]

  return left_joins, where


def _ProcessCommentByIDCond(cond, alias, _user_alias):
  """Convert a commentby_id=user_id cond to SQL."""
  left_joins = [(
      'Comment AS {alias} ON Issue.id = {alias}.issue_id'.format(
          alias=alias), [])]
  if cond.op in (ast_pb2.QueryOp.IS_DEFINED, ast_pb2.QueryOp.IS_NOT_DEFINED):
    where = [_CompareAlreadyJoined(alias, cond.op, 'commenter_id')]
  else:
    field_type, field_values = _GetFieldTypeAndValues(cond)
    where = [_Compare(alias, cond.op, field_type, 'commenter_id', field_values)]

  return left_joins, where


def _ProcessStatusIDCond(cond, _alias, _user_alias):
  """Convert a status_id=ID cond to SQL."""
  field_type, field_values = _GetFieldTypeAndValues(cond)
  explicit_str, explicit_args = _Compare(
      'Issue', cond.op, field_type, 'status_id', field_values)
  derived_str, derived_args = _Compare(
      'Issue', cond.op, field_type, 'derived_status_id', field_values)
  if cond.op in (ast_pb2.QueryOp.IS_NOT_DEFINED, ast_pb2.QueryOp.NE):
    where = [(explicit_str, explicit_args), (derived_str, derived_args)]
  else:
    where = [
        ('(' + explicit_str + ' OR ' + derived_str + ')',
         explicit_args + derived_args)]

  return [], where


def _ProcessLabelIDCond(cond, alias, _user_alias):
  """Convert a label_id=ID cond to SQL."""
  join_str = (
      'Issue2Label AS {alias} ON Issue.id = {alias}.issue_id AND '
      'Issue.shard = {alias}.issue_shard'.format(alias=alias))
  field_type, field_values = _GetFieldTypeAndValues(cond)
  cond_str, cond_args = _Compare(
      alias, ast_pb2.QueryOp.EQ, field_type, 'label_id', field_values)
  left_joins = [(join_str + ' AND ' + cond_str, cond_args)]
  where = [_CompareAlreadyJoined(alias, cond.op, 'label_id')]
  return left_joins, where


def _ProcessComponentIDCond(cond, alias, _user_alias):
  """Convert a component_id=ID cond to SQL."""
  # This is a built-in field, so it shadows any other fields w/ the same name.
  join_str = (
      'Issue2Component AS {alias} ON Issue.id = {alias}.issue_id AND '
      'Issue.shard = {alias}.issue_shard'.format(alias=alias))
  if cond.op in (ast_pb2.QueryOp.IS_DEFINED, ast_pb2.QueryOp.IS_NOT_DEFINED):
    left_joins = [(join_str, [])]
  else:
    field_type, field_values = _GetFieldTypeAndValues(cond)
    cond_str, cond_args = _Compare(
        alias, ast_pb2.QueryOp.EQ, field_type, 'component_id', field_values)
    left_joins = [(join_str + ' AND ' + cond_str, cond_args)]

  where = [_CompareAlreadyJoined(alias, cond.op, 'component_id')]
  return left_joins, where


def _ProcessCustomFieldCond(cond, alias, user_alias):
  """Convert a custom field cond to SQL."""
  # TODO(jrobbins): handle ambiguous field names that map to multiple
  # field definitions, especially for cross-project search.
  field_def = cond.field_defs[0]
  val_type = field_def.field_type

  join_str = (
      'Issue2FieldValue AS {alias} ON Issue.id = {alias}.issue_id AND '
      'Issue.shard = {alias}.issue_shard AND '
      '{alias}.field_id = %s'.format(alias=alias))
  left_joins = [(join_str, [field_def.field_id])]
  if val_type == tracker_pb2.FieldTypes.INT_TYPE:
    where = [_Compare(alias, cond.op, val_type, 'int_value', cond.int_values)]
  elif val_type == tracker_pb2.FieldTypes.STR_TYPE:
    where = [_Compare(alias, cond.op, val_type, 'str_value', cond.str_values)]
  elif val_type == tracker_pb2.FieldTypes.USER_TYPE:
    if cond.int_values or cond.op in (
        ast_pb2.QueryOp.IS_DEFINED, ast_pb2.QueryOp.IS_NOT_DEFINED):
      where = [_Compare(alias, cond.op, val_type, 'user_id', cond.int_values)]
    else:
      email_cond_str, email_cond_args = _Compare(
          user_alias, cond.op, val_type, 'email', cond.str_values)
      left_joins.append((
          'User AS {user_alias} ON {alias}.user_id = {user_alias}.user_id '
          'AND {email_cond}'.format(
              alias=alias, user_alias=user_alias, email_cond=email_cond_str),
          email_cond_args))
      where = [_CompareAlreadyJoined(user_alias, cond.op, 'email')]

  return left_joins, where


def _ProcessAttachmentCond(cond, alias, _user_alias):
  """Convert has:attachment and -has:attachment cond to SQL."""
  if cond.op in (ast_pb2.QueryOp.IS_DEFINED, ast_pb2.QueryOp.IS_NOT_DEFINED):
    left_joins = []
    where = [_Compare('Issue', cond.op, tracker_pb2.FieldTypes.INT_TYPE,
                      'attachment_count', cond.int_values)]
  else:
    field_def = cond.field_defs[0]
    val_type = field_def.field_type
    left_joins = [
      ('Attachment AS {alias} ON Issue.id = {alias}.issue_id AND '
       '{alias}.deleted = %s'.format(alias=alias),
       [False])]
    where = [_Compare(alias, cond.op, val_type, 'filename', cond.str_values)]

  return left_joins, where


_PROCESSORS = {
    'owner': _ProcessOwnerCond,
    'owner_id': _ProcessOwnerIDCond,
    'reporter': _ProcessReporterCond,
    'reporter_id': _ProcessReporterIDCond,
    'cc': _ProcessCcCond,
    'cc_id': _ProcessCcIDCond,
    'starredby': _ProcessStarredByCond,
    'starredby_id': _ProcessStarredByIDCond,
    'commentby': _ProcessCommentByCond,
    'commentby_id': _ProcessCommentByIDCond,
    'status_id': _ProcessStatusIDCond,
    'label_id': _ProcessLabelIDCond,
    'component_id': _ProcessComponentIDCond,
    'blockedon_id': _ProcessBlockedOnIDCond,
    'blocking_id': _ProcessBlockingIDCond,
    'attachment': _ProcessAttachmentCond,
    }


def _ProcessCond(cond_num, cond):
  """Translate one term of the user's search into an SQL query.

  Args:
    cond_num: integer cond number used to make distinct local variable names.
    cond: user query cond parsed by query2ast.py.

  Returns:
    A pair of lists (left_joins, where) to use when building the SQL SELECT
    statement.  Each of them is a list of (str, [val, ...]) pairs.
  """
  alias = 'Cond%d' % cond_num
  user_alias = 'User%d' % cond_num
  # Note: a condition like [x=y] has field_name "x", there may be multiple
  # field definitions that match "x", but they will all have field_name "x".
  field_def = cond.field_defs[0]
  assert all(field_def.field_name == fd.field_name for fd in cond.field_defs)

  if field_def.field_name in NATIVE_SEARCHABLE_FIELDS:
    col = NATIVE_SEARCHABLE_FIELDS[field_def.field_name]
    where = [_Compare(
        'Issue', cond.op, field_def.field_type, col,
        cond.str_values or cond.int_values)]
    return [], where

  elif field_def.field_name in _PROCESSORS:
    proc = _PROCESSORS[field_def.field_name]
    return proc(cond, alias, user_alias)

  elif field_def.field_id:  # it is a search on a custom field
    return _ProcessCustomFieldCond(cond, alias, user_alias)

  elif (field_def.field_name in tracker_fulltext.ISSUE_FULLTEXT_FIELDS or
        field_def.field_name == 'any_field'):
    pass  # handled by full-text search.

  else:
    logging.error('untranslated search cond %r', cond)

  return [], []


def _Compare(alias, op, val_type, col, vals):
  """Return an SQL comparison for the given values. For use in WHERE or ON.

  Args:
    alias: String name of the table or alias defined in a JOIN clause.
    op: One of the operators defined in ast_pb2.py.
    val_type: One of the value types defined in ast_pb2.py.
    col: string column name to compare to vals.
    vals: list of values that the user is searching for.

  Returns:
    (cond_str, cond_args) where cond_str is a SQL condition that may contain
    some %s placeholders, and cond_args is the list of values that fill those
    placeholders.  If the condition string contains any AND or OR operators,
    the whole expression is put inside parens.

  Raises:
    NoPossibleResults: The user's query is impossible to ever satisfy, e.g.,
        it requires matching an empty set of labels.
  """
  vals_ph = sql.PlaceHolders(vals)
  if col in ['label', 'status', 'email']:
    alias_col = 'LOWER(%s.%s)' % (alias, col)
  else:
    alias_col = '%s.%s' % (alias, col)

  def Fmt(cond_str):
    return cond_str.format(alias_col=alias_col, vals_ph=vals_ph)

  no_value = (0 if val_type in [tracker_pb2.FieldTypes.DATE_TYPE,
                                tracker_pb2.FieldTypes.INT_TYPE] else '')
  if op == ast_pb2.QueryOp.IS_DEFINED:
    return Fmt('({alias_col} IS NOT NULL AND {alias_col} != %s)'), [no_value]
  if op == ast_pb2.QueryOp.IS_NOT_DEFINED:
    return Fmt('({alias_col} IS NULL OR {alias_col} = %s)'), [no_value]

  if val_type in [tracker_pb2.FieldTypes.DATE_TYPE,
                  tracker_pb2.FieldTypes.INT_TYPE]:
    if op == ast_pb2.QueryOp.TEXT_HAS:
      op = ast_pb2.QueryOp.EQ
    if op == ast_pb2.QueryOp.NOT_TEXT_HAS:
      op = ast_pb2.QueryOp.NE

  if op == ast_pb2.QueryOp.EQ:
    if not vals:
      raise NoPossibleResults('Column %s has no possible value' % alias_col)
    elif len(vals) == 1:
      cond_str = Fmt('{alias_col} = %s')
    else:
      cond_str = Fmt('{alias_col} IN ({vals_ph})')
    return cond_str, vals

  if op == ast_pb2.QueryOp.NE:
    if not vals:
      return 'TRUE', []  # a no-op that matches every row.
    elif len(vals) == 1:
      comp = Fmt('{alias_col} != %s')
    else:
      comp = Fmt('{alias_col} NOT IN ({vals_ph})')
    return '(%s IS NULL OR %s)' % (alias_col, comp), vals

  # Note: These operators do not support quick-OR
  val = vals[0]

  if op == ast_pb2.QueryOp.GT:
    return Fmt('{alias_col} > %s'), [val]
  if op == ast_pb2.QueryOp.LT:
    return Fmt('{alias_col} < %s'), [val]
  if op == ast_pb2.QueryOp.GE:
    return Fmt('{alias_col} >= %s'), [val]
  if op == ast_pb2.QueryOp.LE:
    return Fmt('{alias_col} <= %s'), [val]

  if op == ast_pb2.QueryOp.TEXT_MATCHES:
    return Fmt('{alias_col} LIKE %s'), [val]
  if op == ast_pb2.QueryOp.NOT_TEXT_MATCHES:
    return Fmt('({alias_col} IS NULL OR {alias_col} NOT LIKE %s)'), [val]

  if op == ast_pb2.QueryOp.TEXT_HAS:
    return Fmt('{alias_col} LIKE %s'), ['%' + val + '%']
  if op == ast_pb2.QueryOp.NOT_TEXT_HAS:
    return (Fmt('({alias_col} IS NULL OR {alias_col} NOT LIKE %s)'),
            ['%' + val + '%'])

  logging.error('unknown op: %r', op)


def _CompareAlreadyJoined(alias, op, col):
  """Return a WHERE clause comparison that checks that a join succeeded."""
  def Fmt(cond_str):
    return cond_str.format(alias_col='%s.%s' % (alias, col))

  if op in (ast_pb2.QueryOp.EQ, ast_pb2.QueryOp.TEXT_HAS,
            ast_pb2.QueryOp.TEXT_MATCHES, ast_pb2.QueryOp.IS_DEFINED):
    return Fmt('{alias_col} IS NOT NULL'), []

  if op in (ast_pb2.QueryOp.NE, ast_pb2.QueryOp.NOT_TEXT_HAS,
            ast_pb2.QueryOp.NOT_TEXT_MATCHES,
            ast_pb2.QueryOp.IS_NOT_DEFINED):
    return Fmt('{alias_col} IS NULL'), []

  logging.error('unknown op: %r', op)


class Error(Exception):
  """Base class for errors from this module."""


class NoPossibleResults(Error):
  """The query could never match any rows from the database, so don't try.."""