def testProcessStarredByIDCond(self):
fd = BUILTIN_ISSUE_FIELDS['starredby_id']
cond = ast_pb2.MakeCond(ast_pb2.QueryOp.EQ, [fd], [], [111L])
left_joins, where = ast2select._ProcessStarredByIDCond(
cond, 'Cond1', 'User1')
self.assertEqual([('IssueStar AS Cond1 ON Issue.id = Cond1.issue_id '
'AND Cond1.user_id = %s', [111L])], left_joins)
self.assertTrue(sql._IsValidJoin(left_joins[0][0]))
self.assertEqual([('Cond1.user_id IS NOT NULL', [])], where)
self.assertTrue(sql._IsValidWhereCond(where[0][0]))
def _verifySQL(self, cols, left_joins, where, group_by=None):
for col in cols:
self.assertTrue(sql._IsValidColumnName(col))
for join_str, _ in left_joins:
self.assertTrue(sql._IsValidJoin(join_str))
for where_str, _ in where:
self.assertTrue(sql._IsValidWhereCond(where_str))
if group_by:
for groupby_str in group_by:
self.assertTrue(sql._IsValidGroupByTerm(groupby_str))
def testProcessAttachmentCond_TextHas(self):
fd = BUILTIN_ISSUE_FIELDS['attachment']
cond = ast_pb2.MakeCond(ast_pb2.QueryOp.TEXT_HAS, [fd], ['jpg'], [])
left_joins, where = ast2select._ProcessAttachmentCond(
cond, 'Cond1', 'User1')
self.assertEqual(
[('Attachment AS Cond1 ON Issue.id = Cond1.issue_id AND '
'Cond1.deleted = %s', [False])], left_joins)
self.assertTrue(sql._IsValidJoin(left_joins[0][0]))
self.assertEqual([('(Cond1.filename LIKE %s)', ['%jpg%'])], where)
self.assertTrue(sql._IsValidWhereCond(where[0][0]))
def testProcessLabelIDCond_NegatedMultipleValue(self):
fd = BUILTIN_ISSUE_FIELDS['label_id']
cond = ast_pb2.MakeCond(ast_pb2.QueryOp.NE, [fd], [], [1, 2])
left_joins, where = ast2select._ProcessLabelIDCond(
cond, 'Cond1', 'User1')
self.assertEqual(
[('Issue2Label AS Cond1 ON Issue.id = Cond1.issue_id AND '
'Issue.shard = Cond1.issue_shard AND '
'Cond1.label_id IN (%s,%s)', [1, 2])], left_joins)
self.assertTrue(sql._IsValidJoin(left_joins[0][0]))
self.assertEqual([('Cond1.label_id IS NULL', [])], where)
self.assertTrue(sql._IsValidWhereCond(where[0][0]))
def testProcessComponentIDCond(self):
fd = BUILTIN_ISSUE_FIELDS['component_id']
cond = ast_pb2.MakeCond(ast_pb2.QueryOp.EQ, [fd], [], [101])
left_joins, where = ast2select._ProcessComponentIDCond(
cond, 'Cond1', 'User1')
self.assertEqual(
[('Issue2Component AS Cond1 ON Issue.id = Cond1.issue_id AND '
'Issue.shard = Cond1.issue_shard AND '
'Cond1.component_id = %s', [101])], left_joins)
self.assertTrue(sql._IsValidJoin(left_joins[0][0]))
self.assertEqual([('Cond1.component_id IS NOT NULL', [])], where)
self.assertTrue(sql._IsValidWhereCond(where[0][0]))
def testProcessOwnerLastVisitCond(self):
fd = BUILTIN_ISSUE_FIELDS['ownerlastvisit']
NOW = 1234567890
cond = ast_pb2.MakeCond(ast_pb2.QueryOp.LT, [fd], [], [NOW])
left_joins, where = ast2select._ProcessOwnerLastVisitCond(
cond, 'Cond1', 'User1')
self.assertEqual(
[('User AS Cond1 ON (Issue.owner_id = Cond1.user_id OR '
'Issue.derived_owner_id = Cond1.user_id)', [])], left_joins)
self.assertTrue(sql._IsValidJoin(left_joins[0][0]))
self.assertEqual([('Cond1.last_visit_timestamp < %s', [NOW])], where)
self.assertTrue(sql._IsValidWhereCond(where[0][0]))
def testProcessCommentByIDCond_NotEqualsUserID(self):
fd = BUILTIN_ISSUE_FIELDS['commentby_id']
cond = ast_pb2.MakeCond(ast_pb2.QueryOp.NE, [fd], [], [111L])
left_joins, where = ast2select._ProcessCommentByIDCond(
cond, 'Cond1', 'User1')
self.assertEqual(
[('Comment AS Cond1 ON Issue.id = Cond1.issue_id AND '
'Cond1.commenter_id = %s AND Cond1.deleted_by IS NULL', [111L])],
left_joins)
self.assertTrue(sql._IsValidJoin(left_joins[0][0]))
self.assertEqual([('Cond1.commenter_id IS NULL', [])], where)
self.assertTrue(sql._IsValidWhereCond(where[0][0]))
def testProcessCcCond_SingleNegative(self):
fd = BUILTIN_ISSUE_FIELDS['cc']
cond = ast_pb2.MakeCond(ast_pb2.QueryOp.NOT_TEXT_HAS, [fd],
['example.com'], [])
left_joins, where = ast2select._ProcessCcCond(cond, 'Cond1', 'User1')
self.assertEqual([(
'(Issue2Cc AS Cond1 JOIN User AS User1 '
'ON Cond1.cc_id = User1.user_id AND (LOWER(User1.email) LIKE %s)) '
'ON Issue.id = Cond1.issue_id AND Issue.shard = Cond1.issue_shard',
['%example.com%'])], left_joins)
self.assertTrue(sql._IsValidJoin(left_joins[0][0]))
self.assertEqual([('User1.email IS NULL', [])], where)
self.assertTrue(sql._IsValidWhereCond(where[0][0]))
def testProcessReporterCond(self):
fd = BUILTIN_ISSUE_FIELDS['reporter']
cond = ast_pb2.MakeCond(ast_pb2.QueryOp.TEXT_HAS, [fd],
['example.com'], [])
left_joins, where = ast2select._ProcessReporterCond(
cond, 'Cond1', 'User1')
self.assertEqual(
[('User AS Cond1 ON Issue.reporter_id = Cond1.user_id', [])],
left_joins)
self.assertTrue(sql._IsValidJoin(left_joins[0][0]))
self.assertEqual([('(LOWER(Cond1.email) LIKE %s)', ['%example.com%'])],
where)
self.assertTrue(sql._IsValidWhereCond(where[0][0]))
def testProcessIsOwnerBouncing(self):
fd = BUILTIN_ISSUE_FIELDS['ownerbouncing']
cond = ast_pb2.MakeCond(ast_pb2.QueryOp.EQ, [fd], [], [])
left_joins, where = ast2select._ProcessIsOwnerBouncing(
cond, 'Cond1', 'User1')
self.assertEqual(
[('User AS Cond1 ON (Issue.owner_id = Cond1.user_id OR '
'Issue.derived_owner_id = Cond1.user_id)', [])], left_joins)
self.assertTrue(sql._IsValidJoin(left_joins[0][0]))
self.assertEqual([('(Cond1.email_bounce_timestamp IS NOT NULL AND'
' Cond1.email_bounce_timestamp != %s)', [0])],
where)
self.assertTrue(sql._IsValidWhereCond(where[0][0]))
def testProcessStarredByCond(self):
fd = BUILTIN_ISSUE_FIELDS['starredby']
cond = ast_pb2.MakeCond(ast_pb2.QueryOp.TEXT_HAS, [fd],
['example.com'], [])
left_joins, where = ast2select._ProcessStarredByCond(
cond, 'Cond1', 'User1')
self.assertEqual([(
'(IssueStar AS Cond1 JOIN User AS User1 '
'ON Cond1.user_id = User1.user_id AND (LOWER(User1.email) LIKE %s)) '
'ON Issue.id = Cond1.issue_id', ['%example.com%'])], left_joins)
self.assertTrue(sql._IsValidJoin(left_joins[0][0]))
self.assertEqual([('User1.email IS NOT NULL', [])], where)
self.assertTrue(sql._IsValidWhereCond(where[0][0]))
def testHasBlockedCond(self):
for op, expected in ((ast_pb2.QueryOp.IS_DEFINED, 'IS NOT NULL'),
(ast_pb2.QueryOp.IS_NOT_DEFINED, 'IS NULL')):
fd = BUILTIN_ISSUE_FIELDS['blockedon_id']
cond = ast_pb2.MakeCond(op, [fd], [], [])
left_joins, where = ast2select._ProcessBlockedOnIDCond(
cond, 'Cond1', None)
self.assertEqual(
[('IssueRelation AS Cond1 ON Issue.id = Cond1.issue_id AND '
'Cond1.kind = %s', ['blockedon'])], left_joins)
self.assertTrue(sql._IsValidJoin(left_joins[0][0]))
self.assertEqual([('Cond1.issue_id %s' % expected, [])], where)
self.assertTrue(sql._IsValidWhereCond(where[0][0]))
def testBlockedOnIDCond_NegatedSingleValue(self):
fd = BUILTIN_ISSUE_FIELDS['blockedon_id']
txt_cond = ast_pb2.MakeCond(ast_pb2.QueryOp.NE, [fd], ['1'], [])
num_cond = ast_pb2.MakeCond(ast_pb2.QueryOp.NE, [fd], [], [1L])
for cond, expected in ((txt_cond, '1'), (num_cond, 1L)):
left_joins, where = ast2select._ProcessBlockedOnIDCond(
cond, 'Cond1', 'Issue1')
self.assertEqual(
[('IssueRelation AS Cond1 ON Issue.id = Cond1.issue_id AND '
'Cond1.kind = %s AND Cond1.dst_issue_id = %s',
['blockedon', expected])], left_joins)
self.assertTrue(sql._IsValidJoin(left_joins[0][0]))
self.assertEqual([('Cond1.issue_id IS NULL', [])], where)
self.assertTrue(sql._IsValidWhereCond(where[0][0]))
def testProcessCustomFieldCond_DateType(self):
fd = tracker_pb2.FieldDef(field_id=1,
project_id=789,
field_name='Deadline',
field_type=tracker_pb2.FieldTypes.DATE_TYPE)
val = int(time.mktime(datetime.datetime(2016, 10, 5).timetuple()))
cond = ast_pb2.MakeCond(ast_pb2.QueryOp.EQ, [fd], [], [val])
left_joins, where = ast2select._ProcessCustomFieldCond(
cond, 'Cond1', 'User1')
self.assertEqual(
[('Issue2FieldValue AS Cond1 ON Issue.id = Cond1.issue_id AND '
'Issue.shard = Cond1.issue_shard AND '
'Cond1.field_id = %s AND '
'Cond1.date_value = %s', [1, val])], left_joins)
self.assertTrue(sql._IsValidJoin(left_joins[0][0]))
self.assertEqual([('Cond1.field_id IS NOT NULL', [])], where)
self.assertTrue(sql._IsValidWhereCond(where[0][0]))
def testProcessCustomFieldCond_UserType_ByID(self):
fd = tracker_pb2.FieldDef(field_id=1,
project_id=789,
field_name='ExecutiveProducer',
field_type=tracker_pb2.FieldTypes.USER_TYPE)
val = 111L
cond = ast_pb2.MakeCond(ast_pb2.QueryOp.EQ, [fd], [], [val])
left_joins, where = ast2select._ProcessCustomFieldCond(
cond, 'Cond1', 'User1')
self.assertEqual(
[('Issue2FieldValue AS Cond1 ON Issue.id = Cond1.issue_id AND '
'Issue.shard = Cond1.issue_shard AND '
'Cond1.field_id = %s AND '
'Cond1.user_id = %s', [1, val])], left_joins)
self.assertTrue(sql._IsValidJoin(left_joins[0][0]))
self.assertEqual([('Cond1.field_id IS NOT NULL', [])], where)
self.assertTrue(sql._IsValidWhereCond(where[0][0]))
def testMergedIntoIDCond_MultiValue(self):
fd = BUILTIN_ISSUE_FIELDS['mergedinto_id']
txt_cond = ast_pb2.MakeCond(ast_pb2.QueryOp.EQ, [fd], ['1', '2', '3'],
[])
num_cond = ast_pb2.MakeCond(ast_pb2.QueryOp.EQ, [fd], [], [1L, 2L, 3L])
for cond, expected in ((txt_cond, ['1', '2',
'3']), (num_cond, [1L, 2L, 3L])):
left_joins, where = ast2select._ProcessMergedIntoIDCond(
cond, 'Cond1', 'Issue1')
self.assertEqual(
[('IssueRelation AS Cond1 ON Issue.id = Cond1.issue_id AND '
'Cond1.kind = %s AND Cond1.dst_issue_id IN (%s,%s,%s)',
['mergedinto'] + expected)], left_joins)
self.assertTrue(sql._IsValidJoin(left_joins[0][0]))
self.assertEqual([('Cond1.issue_id IS NOT NULL', [])], where)
self.assertTrue(sql._IsValidWhereCond(where[0][0]))
def testBuildSQLQuery_Normal(self):
owner_field = BUILTIN_ISSUE_FIELDS['owner']
reporter_id_field = BUILTIN_ISSUE_FIELDS['reporter_id']
conds = [
ast_pb2.MakeCond(ast_pb2.QueryOp.TEXT_HAS, [owner_field],
['example.com'], []),
ast_pb2.MakeCond(ast_pb2.QueryOp.EQ, [reporter_id_field], [],
[111L])
]
ast = ast_pb2.QueryAST(conjunctions=[ast_pb2.Conjunction(conds=conds)])
left_joins, where = ast2select.BuildSQLQuery(ast)
self.assertEqual([('User AS Cond0 ON (Issue.owner_id = Cond0.user_id '
'OR Issue.derived_owner_id = Cond0.user_id)', [])],
left_joins)
self.assertTrue(sql._IsValidJoin(left_joins[0][0]))
self.assertEqual([('(LOWER(Cond0.email) LIKE %s)', ['%example.com%']),
('Issue.reporter_id = %s', [111L])], where)
self.assertTrue(sql._IsValidWhereCond(where[0][0]))
