def _api_PUSHES(self):
        """Returns a JSON representation of pushes."""
        rpp = util.get_int_arg(self.request, 'rpp', 50)
        offset = util.get_int_arg(self.request, 'offset', 0)
        state = util.get_str_arg(self.request, 'state', '')
        user = util.get_str_arg(self.request, 'user', '')

        filters = []
        if state != '':
            filters.append(db.push_pushes.c.state == state)
        if user != '':
            filters.append(db.push_pushes.c.user == user)

        push_query = db.push_pushes.select(
            whereclause=SA.and_(*filters),
            order_by=db.push_pushes.c.modified.desc(),
        )

        pushes_count = push_query.alias('pushes_count').count()

        if offset > 0:
            push_query = push_query.offset(offset)
        if rpp > 0:
            push_query = push_query.limit(rpp)

        db.execute_transaction_cb([push_query, pushes_count, ], self._on_PUSHES_db_response)

    def on_existing_checklist_retrieved(self, success, db_results):
        if not success or not db_results:
            # We should have the new request in db by this time.
            return self.send_error(500)

        existing_checklist_types = set(x['type'] for x in db_results.fetchall())
        queries = []

        necessary_checklist_types = set()

        canonical_tag_name = { 'search': 'search-backend' }
        for cl_type in ['pushplans', 'search', 'hoods']:
            tag = canonical_tag_name.get(cl_type, cl_type)
            if tag in self.tag_list:
                necessary_checklist_types.add(cl_type)
                necessary_checklist_types.add('{0}-cleanup'.format(cl_type))

        types_to_add = necessary_checklist_types - existing_checklist_types
        types_to_remove = existing_checklist_types - necessary_checklist_types

        for type_ in types_to_add:
            for target in checklist_reminders[type_].keys():
                queries.append(db.push_checklist.insert().values(
                    {'request': self.requestid, 'type': type_, 'target': target}
                ))

        if types_to_remove:
            queries.append(db.push_checklist.delete().where(SA.and_(
                db.push_checklist.c.request == self.requestid,
                db.push_checklist.c.type.in_(types_to_remove),
            )))

        db.execute_transaction_cb(queries, self.on_checklist_upsert_complete)

    def test_hoods_checklists(self):
        with fake_checklist_request():
            # insert fake data from FakeDataMixin
            fake_pushid = 2
            self.insert_pushes()
            self.insert_requests()
            req = self.get_requests_by_user('testuser1')[0]
            self.insert_pushcontent(req['id'], fake_pushid)

            # insert fake checklist data
            checklist_queries = []
            checklist_items = (
                {'request': req['id'], 'type': 'hoods', 'target': 'stage'},
                {'request': req['id'], 'type': 'hoods', 'target': 'prod'},
                {'request': req['id'], 'type': 'hoods-cleanup', 'target': 'post-verify-stage'},
            )
            for checklist_item in checklist_items:
                checklist_queries.append(db.push_checklist.insert(checklist_item))

            db.execute_transaction_cb(checklist_queries, on_db_return)

            uri = "/checklist?id=%d" % fake_pushid
            response = self.fetch(uri)
            T.assert_equal(response.error, None)
            T.assert_not_in("No checklist items for this push", response.body)
            T.assert_in("Notify testuser1 to deploy Geoservices to stage", response.body)
            T.assert_in("Notify testuser1 to deploy Geoservices to prod", response.body)

    def test_checklist_duplicate(self):
        with fake_checklist_request():
            # insert fake data from FakeDataMixin
            fake_pushid = 2
            self.insert_pushes()
            self.insert_requests()
            test1_request = self.get_requests_by_user('testuser1')[0]
            test2_request = self.get_requests_by_user('testuser2')[0]
            self.insert_pushcontent(test1_request['id'], fake_pushid)
            self.insert_pushcontent(test2_request['id'], fake_pushid)

            # insert fake checklist data
            checklist_queries = []
            for req in (test1_request, test2_request):
                checklist_queries.append(db.push_checklist.insert({
                    'request': req['id'],
                    'type': 'search',
                    'target': 'prod'
                }))
                checklist_queries.append(db.push_checklist.insert({
                    'request': req['id'],
                    'type': 'search-cleanup',
                    'target': 'post-verify-prod'
                }))
            db.execute_transaction_cb(checklist_queries, on_db_return)

            uri = "/checklist?id=%d" % fake_pushid
            response = self.fetch(uri)
            T.assert_equal(response.error, None)
            T.assert_not_in("No checklist items for this push", response.body)
            T.assert_not_equal(re.search("for testuser\d,testuser\d", response.body), None)
            T.assert_in("Before Certifying - Do In Prod", response.body)

    def test_checklist_single_search_tag(self):
        with fake_checklist_request():
            # insert fake data from FakeDataMixin
            fake_pushid = 2
            self.insert_pushes()
            self.insert_requests()
            test1_request = self.get_requests_by_user('testuser1')[0]
            self.insert_pushcontent(test1_request['id'], fake_pushid)

            # insert fake checklist data
            checklist_queries = [
                db.push_checklist.insert({
                    'request': test1_request['id'],
                    'type': 'search',
                    'target': 'prod'
                }),
                db.push_checklist.insert({
                    'request': test1_request['id'],
                    'type': 'search-cleanup',
                    'target': 'post-verify-prod'
                }),
            ]
            db.execute_transaction_cb(checklist_queries, on_db_return)

            uri = "/checklist?id=%d" % fake_pushid
            response = self.fetch(uri)
            T.assert_equal(response.error, None)
            T.assert_not_in("No checklist items for this push", response.body)
            T.assert_not_in("multiple requests", response.body)
            T.assert_in("for testuser1", response.body)
            T.assert_in("Before Certifying - Do In Prod", response.body)

def convert_checklist(old, new):
    print 'Renaming %s to %s in checklist types' % (old, new)

    cb = partial(convert_checklist_callback, old, new)

    cselect_query = db.push_checklist.select()
    db.execute_transaction_cb([cselect_query], cb)

 def post(self):
     if not self.current_user:
         return self.send_error(403)
     self.requestid = pushmanager.core.util.get_int_arg(self.request, 'id')
     self.pushid = pushmanager.core.util.get_int_arg(self.request, 'push')
     select_query = db.push_pushes.select().where(
         db.push_pushes.c.id == self.pushid,
     )
     update_query = db.push_requests.update().where(SA.and_(
         db.push_requests.c.state == 'staged',
         db.push_requests.c.id == self.requestid,
         SA.exists(
             [1],
             SA.and_(
                 db.push_pushcontents.c.push == self.pushid,
                 db.push_pushcontents.c.request == self.requestid,
             )
         ))).values({
             'state': 'verified',
         })
     finished_query = db.push_requests.select().where(SA.and_(
         db.push_requests.c.state == 'staged',
         SA.exists(
             [1],
             SA.and_(
                 db.push_pushcontents.c.push == self.pushid,
                 db.push_pushcontents.c.request == db.push_requests.c.id,
             )
         )))
     db.execute_transaction_cb([select_query, update_query, finished_query], self.on_db_complete)

 def post(self):
     if not self.current_user:
         return self.send_error(403)
     self.pushid = pushmanager.core.util.get_int_arg(self.request, "id")
     push_query = (
         db.push_pushes.update()
         .where(db.push_pushes.c.id == self.pushid)
         .values({"state": "live", "modified": time.time()})
     )
     request_query = (
         db.push_requests.update()
         .where(
             SA.and_(
                 db.push_requests.c.state == "blessed",
                 SA.exists(
                     [1],
                     SA.and_(
                         db.push_pushcontents.c.push == self.pushid,
                         db.push_pushcontents.c.request == db.push_requests.c.id,
                     ),
                 ),
             )
         )
         .values({"state": "live", "modified": time.time()})
     )
     reset_query = (
         db.push_requests.update()
         .where(
             SA.exists(
                 [1],
                 SA.and_(
                     db.push_requests.c.state == "pickme",
                     db.push_pushcontents.c.push == self.pushid,
                     db.push_pushcontents.c.request == db.push_requests.c.id,
                 ),
             )
         )
         .values({"state": "requested"})
     )
     delete_query = db.push_pushcontents.delete().where(
         SA.exists(
             [1],
             SA.and_(
                 db.push_pushcontents.c.push == self.pushid,
                 db.push_pushcontents.c.request == db.push_requests.c.id,
                 db.push_requests.c.state == "requested",
             ),
         )
     )
     live_query = db.push_requests.select().where(
         SA.and_(
             db.push_requests.c.state == "live",
             db.push_pushcontents.c.push == self.pushid,
             db.push_pushcontents.c.request == db.push_requests.c.id,
         )
     )
     db.execute_transaction_cb(
         [push_query, request_query, reset_query, delete_query, live_query], self.on_db_complete
     )

def convert_tag(old, new):
    print 'Renaming %s to %s in tags' % (old, new)

    cb = partial(convert_tag_callback, old, new)

    rselect_query = db.push_requests.select()
    db.execute_transaction_cb([rselect_query], cb)

    if old in checklist_reminders.keys():
        print """%s is handled specially in pushmanager.
Additional code changes are required before pushmanger can be restarted.
""" % old

    def on_db_complete(self, success, db_results):
        self.check_db_results(success, db_results)

        reqs, _, _ = db_results
        removal_dicts = []
        for req in reqs:
            if req['watchers']:
                user_string = '%s (%s)' % (req['user'], req['watchers'])
                users = [req['user']] + req['watchers'].split(',')
            else:
                user_string = req['user']
                users = [req['user']]
            msg = (
                """
                <p>
                    %(pushmaster)s has removed request for %(user)s from a push:
                </p>
                <p>
                    <strong>%(user)s - %(title)s</strong><br />
                    <em>%(repo)s/%(branch)s</em>
                </p>
                <p>
                    Regards,<br />
                    PushManager
                </p>"""
                ) % pushmanager.core.util.EscapedDict({
                    'pushmaster': self.current_user,
                    'user': user_string,
                    'title': req['title'],
                    'repo': req['repo'],
                    'branch': req['branch'],
                })
            subject = "[push] %s - %s" % (user_string, req['title'])
            MailQueue.enqueue_user_email(users, msg, subject)
            msg = '%(pushmaster)s has removed request "%(title)s" for %(user)s from a push' % {
                    'pushmaster': self.current_user,
                    'title': req['title'],
                    'pushid': self.pushid,
                    'user': user_string,
                }
            XMPPQueue.enqueue_user_xmpp(users, msg)
            removal_dicts.append({
                'request': req['id'],
                'push': self.pushid,
                'reason': 'removal after %s' % req['state'],
                'pushmaster': self._current_user,
                'timestamp': int(time.time()),
            })

        removal_queries = [db.push_removals.insert(removal) for removal in removal_dicts]
        db.execute_transaction_cb(removal_queries, self.on_db_insert_complete)

 def post(self):
     if not self.current_user:
         return self.send_error(403)
     self.requestid = pushmanager.core.util.get_int_arg(self.request, 'id')
     update_query = db.push_requests.update().where(SA.and_(
         db.push_requests.c.id == self.requestid,
         db.push_requests.c.user == self.current_user,
         db.push_requests.c.state == 'delayed',
     )).values({
         'state': 'requested',
     })
     select_query = db.push_requests.select().where(
         db.push_requests.c.id == self.requestid,
     )
     db.execute_transaction_cb([update_query, select_query], self.on_db_complete)

    def on_existing_checklist_retrieved(self, success, db_results):
        if not success or not db_results:
            # We should have the new request in db by this time.
            return self.send_error(500)

        existing_checklist_types = set(x['type'] for x in db_results.fetchall())
        queries = []

        necessary_checklist_types = set()

        if 'pushplans' in self.tag_list:
            necessary_checklist_types.add('pushplans')
            necessary_checklist_types.add('pushplans-cleanup')
        if 'search-backend' in self.tag_list:
            necessary_checklist_types.add('search')
            necessary_checklist_types.add('search-cleanup')
        if 'hoods' in self.tag_list:
            necessary_checklist_types.add('hoods')
            necessary_checklist_types.add('hoods-cleanup')

        types_to_add = necessary_checklist_types - existing_checklist_types
        types_to_remove = existing_checklist_types - necessary_checklist_types

        # Different types of checklist items need to happen at different points.
        targets_by_type = {
            'pushplans' : ('stage', 'prod'),
            'search' : ('post-stage', 'prod', 'post-prod', 'post-verify'),
            'hoods' : ('stage', 'post-stage', 'prod'),
            # We need to append checklist items to clean up after
            # push plans & search checklist items.
            'pushplans-cleanup' : ('post-verify-stage',),
            'search-cleanup': ('post-verify-prod',),
            'hoods-cleanup' : ('post-verify-stage',),
        }

        for type_ in types_to_add:
            for target in targets_by_type[type_]:
                queries.append(db.push_checklist.insert().values(
                    {'request': self.requestid, 'type': type_, 'target': target}
                ))

        if types_to_remove:
            queries.append(db.push_checklist.delete().where(SA.and_(
                db.push_checklist.c.request == self.requestid,
                db.push_checklist.c.type.in_(types_to_remove),
            )))

        db.execute_transaction_cb(queries, self.on_checklist_upsert_complete)

def convert_tag_callback(oldtag, newtag, success, db_results):
    check_db_results(success, db_results)

    requests = db_results[0].fetchall()

    update_queries = []
    for request in requests:
        if tags_contain(request['tags'], [oldtag]):
            updated_tags = del_from_tags_str(request['tags'], oldtag)
            updated_tags = add_to_tags_str(updated_tags, newtag)
            update_query = db.push_requests.update().where(
                db.push_requests.c.id == request.id
                ).values({'tags': updated_tags})
            update_queries.append(update_query)

    db.execute_transaction_cb(update_queries, check_db_results)

    def post(self):
        if not self.current_user:
            return self.send_error(403)
        self.pushid = pushmanager.core.util.get_int_arg(self.request, 'push')
        self.request_ids = self.request.arguments.get('request', [])

        insert_queries = [
                InsertIgnore(db.push_pushcontents, ({'request': int(i), 'push': self.pushid}))
                for i in self.request_ids
        ]
        update_query = db.push_requests.update().where(
            db.push_requests.c.id.in_(self.request_ids)).values({'state':'added'})
        request_query = db.push_requests.select().where(
            db.push_requests.c.id.in_(self.request_ids))

        db.execute_transaction_cb(insert_queries + [update_query, request_query], self.on_db_complete)

    def test_transaction_with_successful_condition(self):
        def on_return(success, _):
            assert success

        requestid = 1

        db.execute_transaction_cb(
            [db.push_pushcontents.insert({'request': 2, 'push': 2})],
            on_return,
            condition=(
                db.push_pushcontents.select(
                    db.push_pushcontents.c.request == requestid
                ),
                lambda results: results.fetchone().request == requestid
            )
        )

    def test_transaction_with_unsuccessful_condition(self):
        def on_return(success, _):
            # Transaction should fail since the condition will not be
            # successful
            T.assert_equal(success, False)

        # This will fail better not log errors
        with mock.patch("%s.db.logging.error" % __name__):
            db.execute_transaction_cb(
                [db.push_pushcontents.insert({'request': 2, 'push': 2})],
                on_return,
                condition=(
                    # Just a phony query that we don't really care about
                    db.push_pushcontents.select(),
                    lambda results: False
                )
            )

 def post(self):
     if not self.current_user:
         return self.send_error(403)
     self.requestid = pushmanager.core.util.get_int_arg(self.request, "id")
     update_query = (
         db.push_requests.update()
         .where(
             SA.and_(
                 db.push_requests.c.id == self.requestid,
                 db.push_requests.c.user == self.current_user,
                 db.push_requests.c.state.in_(["requested", "delayed"]),
             )
         )
         .values({"state": "discarded"})
     )
     select_query = db.push_requests.select().where(db.push_requests.c.id == self.requestid)
     db.execute_transaction_cb([update_query, select_query], self.on_db_complete)

 def post(self):
     if not self.current_user:
         return self.send_error(403)
     self.pushtype = self._arg('push-type')
     insert_query = db.push_pushes.insert({
         'title': self._arg('push-title'),
         'user': self.current_user,
         'branch': self._arg('push-branch'),
         'revision': "0"*40,
         'created': time.time(),
         'modified': time.time(),
         'state': 'accepting',
         'pushtype': self.pushtype,
         })
     select_query = db.push_requests.select().where(
         db.push_requests.c.state == 'requested',
     )
     db.execute_transaction_cb([insert_query, select_query], self.on_db_complete)

    def _api_PUSHDATA(self):
        """Returns all the information on a push in JSON. This is the same data that is shown on the push page"""
        push_id = util.get_int_arg(self.request, 'id')
        if not push_id:
            return self.send_error(404)

        push_info_query = db.push_pushes.select(db.push_pushes.c.id == push_id)
        contents_query = db.push_requests.select(
            SA.and_(
                db.push_requests.c.id == db.push_pushcontents.c.request,
                db.push_pushcontents.c.push == push_id,
            ),
            order_by=(db.push_requests.c.user, db.push_requests.c.title),
        )
        available_query = db.push_requests.select(
            db.push_requests.c.state == 'requested',
        )
        db.execute_transaction_cb([push_info_query, contents_query, available_query], self._on_PUSHDATA_db_response)

def convert_checklist_callback(old, new, success, db_results):
    check_db_results(success, db_results)

    checklists = db_results[0].fetchall()

    convert = {
        old: new,
        '%s-cleanup' % old: '%s-cleanup' % new
    }

    update_queries = []
    for checklist in checklists:
        if checklist['type'] in convert.keys():
            update_query = db.push_checklist.update().where(
                db.push_checklist.c.id == checklist.id
                ).values({'type': convert[checklist['type']]})
            update_queries.append(update_query)

    db.execute_transaction_cb(update_queries, check_db_results)