def testProcessReporterIDCond(self): fd = BUILTIN_ISSUE_FIELDS['reporter_id'] cond = ast_pb2.MakeCond(ast_pb2.QueryOp.EQ, [fd], [], [111L]) left_joins, where = ast2select._ProcessReporterIDCond( cond, 'Cond1', 'User1') self.assertEqual([], left_joins) self.assertEqual([('Issue.reporter_id = %s', [111L])], where) self.assertTrue(sql._IsValidWhereCond(where[0][0]))
def testProcessAttachmentCond_HasAttachment(self): fd = BUILTIN_ISSUE_FIELDS['attachment'] cond = ast_pb2.MakeCond(ast_pb2.QueryOp.IS_DEFINED, [fd], [], []) left_joins, where = ast2select._ProcessAttachmentCond( cond, 'Cond1', 'User1') self.assertEqual([], left_joins) self.assertEqual([('(Issue.attachment_count IS NOT NULL AND ' 'Issue.attachment_count != %s)', [0])], where) self.assertTrue(sql._IsValidWhereCond(where[0][0])) cond = ast_pb2.MakeCond(ast_pb2.QueryOp.IS_NOT_DEFINED, [fd], [], []) left_joins, where = ast2select._ProcessAttachmentCond( cond, 'Cond1', 'User1') self.assertEqual([], left_joins) self.assertEqual([('(Issue.attachment_count IS NULL OR ' 'Issue.attachment_count = %s)', [0])], where) self.assertTrue(sql._IsValidWhereCond(where[0][0]))
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 testProcessStatusIDCond(self): fd = BUILTIN_ISSUE_FIELDS['status_id'] cond = ast_pb2.MakeCond(ast_pb2.QueryOp.EQ, [fd], [], [2]) left_joins, where = ast2select._ProcessStatusIDCond( cond, 'Cond1', 'User1') self.assertEqual([], left_joins) self.assertEqual([ ('(Issue.status_id = %s OR Issue.derived_status_id = %s)', [2, 2]) ], where) self.assertTrue(sql._IsValidWhereCond(where[0][0]))
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 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 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 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 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 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 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 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]))