def get(self, input_dict):
        if not self.table.select({f: input_dict.get(f) for f in self.decisionFields}):
            return problem(404, "Not Found", "Requested Required Signoff does not exist")

        try:
            page = int(connexion.request.args.get('page', 1))
            limit = int(connexion.request.args.get('limit', 100))
        except ValueError as msg:
            self.log.warning("Bad input: %s", msg)
            return problem(400, "Bad Request", str(msg))
        offset = limit * (page - 1)

        where_count = [self.table.history.data_version != null()]
        for field in self.decisionFields:
            where_count.append(getattr(self.table.history, field) == input_dict.get(field))
        total_count = self.table.history.count(where=where_count)

        where = [getattr(self.table.history, f) == input_dict.get(f) for f in self.decisionFields]
        where.append(self.table.history.data_version != null())
        revisions = self.table.history.select(
            where=where, limit=limit, offset=offset,
            order_by=[self.table.history.timestamp.desc()]
        )

        return jsonify(count=total_count, required_signoffs=revisions)

 def _network_result_filter_hook(self, query, filters):
     vals = filters and filters.get(external_net.EXTERNAL, [])
     if not vals:
         return query
     if vals[0]:
         return query.filter((ExternalNetwork.network_id != expr.null()))
     return query.filter((ExternalNetwork.network_id == expr.null()))

 def __init__( self, system = null(), module = null(), cType = null() ):
   """ just defines some instance members
   """
   self.system = system
   self.module = module
   self.cType = cType
   self.installationList = []

 def __query__(self):
     q = super(NullSelectableSelectFilter, self).__query__()
     arg_name = 'select.{}'.format(self.attribute_name)
     s = self.request_args.get(arg_name, self.default)
     if s == self.NULL:
         q = self.attribute.is_(null())
     elif s == self.NOT_NULL:
         q = self.attribute.isnot(null())
     return q

    def geoname_roundup():

        Session = create_session(check=True, **session_args)
        session = Session()
        insert = Insert(session)
        Session = create_session(check=False, database='CtyOD')
        insert.ctyod = Session()

        # Roundup addresses in the from_address position, to intersect with next query
        subquery = (
            session.query(Movement.id)
            .outerjoin(
                Association,
                and_(
                    Movement.from_address_id == Association.address_id,
                    Movement.to_address_id == Association.to_address_id,
                    )
                )
            .filter(Association.premises_id == null())
            .subquery()
            )

        # Roundup addresses in the to_address position
        query = (
            session.query(Movement.from_address_id, Movement.to_address_id)
            .outerjoin(
                Association,
                and_(
                    Movement.to_address_id == Association.address_id,
                    Movement.from_address_id == Association.from_address_id,
                    )
                )
            .filter(Association.premises_id == null())
            .join(subquery, Movement.id == subquery.c.movement_id)
            .group_by(Movement.from_address_id, Movement.to_address_id)
            )

        for movement in query:
            from_address = (
                session.query(Address)
                .filter_by(id=movement.from_address_id)
                .one()
                )
            to_address = (
                session.query(Address)
                .filter_by(id=movement.to_address_id)
                .one()
                )
            try:
                insert.premises(from_address, to_address)
                insert.session.commit()
            except:
                insert.session.rollback()

        insert.session.close()
        insert.ctyod.close()

    def get(self, sc_id):
        if not self.table.scheduled_changes.select({"sc_id": sc_id}):
            return Response(status=404, response="Scheduled change does not exist")

        try:
            page = int(request.args.get('page', 1))
            limit = int(request.args.get('limit', 100))
            assert page >= 1
        except (ValueError, AssertionError) as msg:
            self.log.warning("Bad input: %s", msg)
            return Response(status=400, response=json.dumps({"exception": msg}))

        offset = limit * (page - 1)
        total_count = self.table.scheduled_changes.history.t.count()\
            .where(self.table.scheduled_changes.history.sc_id == sc_id)\
            .where(self.table.scheduled_changes.history.data_version != null())\
            .execute()\
            .fetchone()[0]

        # Although Scheduled Changes are stored across two tables, we don't
        # expose that through the API. Because of this, we need to look up
        # history in both and return the combined version.
        # This is done by the database layer for non-history parts of Scheduled Changes, but
        # that's not feasible for History due to the inheritance structure of the tables,
        # so we do it here instead.
        revisions = self.table.scheduled_changes.history.select(
            where=[self.table.scheduled_changes.history.sc_id == sc_id,
                   self.table.scheduled_changes.history.data_version != null()],
            limit=limit,
            offset=offset,
            order_by=[self.table.scheduled_changes.history.timestamp.desc()],
        )
        # There's a big 'ol assumption here that the primary Scheduled Changes
        # table and the conditions table always keep their data version in sync.
        for r in revisions:
            cond = self.table.scheduled_changes.conditions.history.select(
                where=[self.table.scheduled_changes.conditions.history.sc_id == r["sc_id"],
                       self.table.scheduled_changes.conditions.history.data_version == r["data_version"]],
            )
            r.update(cond[0])

        ret = {
            "count": total_count,
            "revisions": [],
        }

        for rev in revisions:
            r = {}
            for k, v in rev.iteritems():
                if k == "data_version":
                    r["sc_data_version"] = v
                else:
                    r[k.replace("base_", "")] = v
            ret["revisions"].append(r)

        return jsonify(ret)

 def visit_binary(binary):
     mapper = reverse_direction and self.parent_property.mapper or self.parent_property.parent
     if isinstance(binary.left, expression._BindParamClause) and binary.left.key in bind_to_col:
         # reverse order if the NULL is on the left side
         binary.left = binary.right
         binary.right = expression.null()
         binary.operator = operators.is_
     elif isinstance(binary.right, expression._BindParamClause) and binary.right.key in bind_to_col:
         binary.right = expression.null()
         binary.operator = operators.is_

 def visit_binary(binary):
     if isinstance(binary.left, expression._BindParamClause) and binary.left._identifying_key in nulls:
         # reverse order if the NULL is on the left side
         binary.left = binary.right
         binary.right = expression.null()
         binary.operator = operators.is_
         binary.negate = operators.isnot
     elif isinstance(binary.right, expression._BindParamClause) and binary.right._identifying_key in nulls:
         binary.right = expression.null()
         binary.operator = operators.is_
         binary.negate = operators.isnot

    def get(self, release):
        releases = dbo.releases.getReleases(name=release, limit=1)
        if not releases:
            return Response(status=404,
                            response='Requested release does not exist')
        release = releases[0]
        table = dbo.releases.history

        try:
            page = int(request.args.get('page', 1))
            limit = int(request.args.get('limit', 10))
            assert page >= 1
        except (ValueError, AssertionError) as e:
            self.log.warning("Bad input: %s", json.dumps(e.args))
            return Response(status=400, response=json.dumps({"data": e.args}))
        offset = limit * (page - 1)
        total_count = table.t.count()\
            .where(table.name == release['name'])\
            .where(table.data_version != null())\
            .execute()\
            .fetchone()[0]

        revisions = table.select(
            where=[
                table.name == release['name'],
                table.data_version != null()
            ],
            limit=limit,
            offset=offset,
            order_by=[table.timestamp.desc()],
        )

        _mapping = [
            'data_version',
            'name',
            'product',
            'read_only',
            '_different',
            '_time_ago',
            'change_id',
            'changed_by',
            "timestamp",
        ]

        self.annotateRevisionDifferences(revisions)

        _revisions = []
        for r in revisions:
            _revisions.append(dict(
                (item, r[item])
                for item in _mapping
            ))

        return jsonify(revisions=_revisions, count=total_count)

def _get_filters(obj, history_table):
    query = get_input_dict()
    where = [False, False]
    where = [getattr(history_table, f) == query.get(f) for f in query]
    where.append(history_table.data_version != null())
    if hasattr(history_table, 'product'):
        where.append(history_table.product != null())
    if request.args.get('timestamp_from'):
        where.append(history_table.timestamp >= int(request.args.get('timestamp_from')))
    if request.args.get('timestamp_to'):
        where.append(history_table.timestamp <= int(request.args.get('timestamp_to')))
    return where

 def _get_filters(self):
     query = get_input_dict()
     where = [getattr(self.table.history, f) == query.get(f) for f in query]
     where.append(self.table.history.data_version != null())
     if hasattr(self.history_table, 'product'):
         where.append(self.history_table.product != null())
     request = connexion.request
     if request.args.get('timestamp_from'):
         where.append(self.history_table.timestamp >= int(request.args.get('timestamp_from')))
     if request.args.get('timestamp_to'):
         where.append(self.history_table.timestamp <= int(request.args.get('timestamp_to')))
     return where

 def avg_ct(self, cell_line):
     from collections import Iterable
     from sqlalchemy.sql.expression import null
     if self.ctoxicity:
         if isinstance(self.ctoxicity, Iterable):
             values = [ct.ic50 for ct in self.ctoxicity if ct.cell_line == cell_line and ct.active]
         else:
             values = null()
         try:
             self._avg_ct = round(sum(values)/len(values), 4)
         except ZeroDivisionError:
             self._avg_ct = null() # the default value
     else:
         self._avg_ct = null()

 def filter( self, trans, user, query, column_filter ):
     """ Modify query to filter histories by sharing status. """
     if column_filter == "All":
         pass
     elif column_filter:
         if column_filter == "private":
             query = query.filter( self.model_class.users_shared_with == null() )
             query = query.filter( self.model_class.importable == false() )
         elif column_filter == "shared":
             query = query.filter( self.model_class.users_shared_with != null() )
         elif column_filter == "accessible":
             query = query.filter( self.model_class.importable == true() )
         elif column_filter == "published":
             query = query.filter( self.model_class.published == true() )
     return query

def _story_build_summary_query():
    # first create a subquery for task statuses
    select_items = []
    select_items.append(Story)
    select_items.append(
        expr.case(
            [(func.sum(Task.status.in_(
                ['todo', 'inprogress', 'review'])) > 0,
              'active'),
             ((func.sum(Task.status == 'merged')) > 0, 'merged')],
            else_='invalid'
        ).label('status')
    )
    for task_status in Task.TASK_STATUSES:
        select_items.append(expr.cast(
            func.sum(Task.status == task_status), Integer
        ).label(task_status))
    select_items.append(expr.null().label('task_statuses'))

    result = select(select_items, None,
                    expr.Join(Story, Task, onclause=Story.id == Task.story_id,
                              isouter=True)) \
        .group_by(Story.id) \
        .alias('story_summary')

    return result

def get_data_sources(data_source, start_date, finish_date, forecast_date=None):

    if forecast_date is None:
        forecast_date = data_source.forecast_date

    if data_source.start_date == start_date and \
            data_source.finish_date == finish_date \
            and forecast_date == data_source.forecast_date:
        yield data_source
    else:
        for g_era in data_source.sess.query(GEra).filter(
                GEra.g_supply == data_source.g_supply,
                GEra.start_date <= finish_date,
                or_(
                    GEra.finish_date == null(),
                    GEra.finish_date >= start_date)):
            g_era_start = g_era.start_date

            if start_date < g_era_start:
                chunk_start = g_era_start
            else:
                chunk_start = start_date

            g_era_finish = g_era.finish_date

            chunk_finish = g_era_finish if \
                hh_after(finish_date, g_era_finish) else finish_date

            ds = GDataSource(
                data_source.sess, chunk_start, chunk_finish, forecast_date,
                g_era, data_source.caches, data_source.bill)
            yield ds

    def create_tsigkey(self, context, tsigkey):
        """ Create a TSIG Key """

        if tsigkey['algorithm'] not in TSIG_SUPPORTED_ALGORITHMS:
            raise exceptions.NotImplemented('Unsupported algorithm')

        tsigkey_m = models.TsigKey()

        tsigkey_m.update({
            'designate_id': tsigkey['id'],
            'name': tsigkey['name'],
            'algorithm': tsigkey['algorithm'],
            'secret': base64.b64encode(tsigkey['secret'])
        })

        tsigkey_m.save(self.session)

        # NOTE(kiall): Prepare and execute query to install this TSIG Key on
        #              every domain. We use a manual query here since anything
        #              else would be impossibly slow.
        query_select = select([null(),
                               models.Domain.__table__.c.id,
                               "'TSIG-ALLOW-AXFR'",
                               "'%s'" % tsigkey['name']])
        query = InsertFromSelect(models.DomainMetadata.__table__, query_select)

        # NOTE(kiall): A TX is required for, at the least, SQLite.
        self.session.begin()
        self.session.execute(query)
        self.session.commit()

 def _network_filter_hook(self, context, original_model, conditions):
     if conditions is not None and not hasattr(conditions, "__iter__"):
         conditions = (conditions,)
     # Apply the external network filter only in non-admin context
     if not context.is_admin and hasattr(original_model, "tenant_id"):
         conditions = expr.or_(ExternalNetwork.network_id != expr.null(), *conditions)
     return conditions

def allocate_edge_vnic_with_tunnel_index(session, edge_id, network_id):
    """Allocate an available edge vnic with tunnel index to network."""

    # TODO(berlin): temporary solution to let metadata and dhcp use
    # different vnics
    net_list = get_nsxv_internal_network(
        session, constants.InternalEdgePurposes.INTER_EDGE_PURPOSE)
    metadata_net_id = net_list[0]['network_id'] if net_list else None

    with session.begin(subtransactions=True):
        query = session.query(nsxv_models.NsxvEdgeVnicBinding)
        query = query.filter(
            nsxv_models.NsxvEdgeVnicBinding.edge_id == edge_id,
            nsxv_models.NsxvEdgeVnicBinding.network_id == expr.null())
        if metadata_net_id:
            vnic_binding = get_edge_vnic_binding(
                session, edge_id, metadata_net_id)
            if vnic_binding:
                vnic_index = vnic_binding.vnic_index
                query = query.filter(
                    nsxv_models.NsxvEdgeVnicBinding.vnic_index != vnic_index)

        binding = query.first()
        if not binding:
            msg = (_("Failed to allocate one available vnic on edge_id: "
                     ":%(edge_id)s to network_id: %(network_id)s") %
                   {'edge_id': edge_id, 'network_id': network_id})
            LOG.exception(msg)
            raise nsx_exc.NsxPluginException(err_msg=msg)
        binding['network_id'] = network_id
        session.add(binding)
        session.flush()
    return binding

    def check_project_depth(self, max_depth):
        with sql.session_for_read() as session:
            obj_list = []
            # Using db table self outerjoin to find the project descendants.
            #
            # We'll only outerjoin the project table `max_depth` times to
            # check whether current project tree exceed the max depth limit.
            #
            # For example:
            #
            # If max_depth is 2, we will take the outerjoin 2 times, then the
            # SQL result may be like:
            #
            #  +---- +-------------+-------------+-------------+
            #  | No. | project1_id | project2_id | project3_id |
            #  +--- -+-------------+-------------+-------------+
            #  |  1  |  domain_x   |             |             |
            #  +- ---+-------------+-------------+-------------+
            #  |  2  |  project_a  |             |             |
            #  +- ---+-------------+-------------+-------------+
            #  |  3  |  domain_y   |  project_a  |             |
            #  +- ---+-------------+-------------+-------------+
            #  |  4  |  project_b  |  project_c  |             |
            #  +- ---+-------------+-------------+-------------+
            #  |  5  |  domain_y   |  project_b  |  project_c  |
            #  +- ---+-------------+-------------+-------------+
            #
            # `project1_id` column is the root. It is a project or a domain.
            # If `project1_id` is a project, there must exist a line that
            # `project1` is its domain.
            #
            # We got 5 lines here. It includes three scenarios:
            #
            # 1). The No.1 line means there is a domain `domain_x` which has no
            #     children. The depth is 1.
            #
            # 2). The No.2 and No.3 lines mean project `project_a` has no child
            # and its parent is domain `domain_y`. The depth is 2.
            #
            # 3). The No.4 and No.5 lines mean project `project_b` has a child
            #     `project_c` and its parent is domain `domain_y`. The depth is
            #     3. This tree hit the max depth
            #
            # So we can see that if column "project3_id" has value, it means
            # some trees hit the max depth limit.

            for _ in range(max_depth + 1):
                obj_list.append(orm.aliased(Project))

            query = session.query(*obj_list)

            for index in range(max_depth):
                query = query.outerjoin(
                    obj_list[index + 1],
                    obj_list[index].id == obj_list[index + 1].parent_id)
            exceeded_lines = query.filter(
                obj_list[-1].id != expression.null())

            if exceeded_lines:
                return [line[max_depth].id for line in exceeded_lines]

    def __check_jobs_at_startup( self ):
        """
        Checks all jobs that are in the 'new', 'queued' or 'running' state in
        the database and requeues or cleans up as necessary.  Only run as the
        job handler starts.
        In case the activation is enforced it will filter out the jobs of inactive users.
        """
        jobs_at_startup = []
        if self.track_jobs_in_database:
            in_list = ( model.Job.states.QUEUED,
                        model.Job.states.RUNNING )
        else:
            in_list = ( model.Job.states.NEW,
                        model.Job.states.QUEUED,
                        model.Job.states.RUNNING )
        if self.app.config.user_activation_on:
                jobs_at_startup = self.sa_session.query( model.Job ).enable_eagerloads( False ) \
                    .outerjoin( model.User ) \
                    .filter( model.Job.state.in_( in_list ) &
                             ( model.Job.handler == self.app.config.server_name ) &
                             or_( ( model.Job.user_id == null() ), ( model.User.active == true() ) ) ).all()
        else:
            jobs_at_startup = self.sa_session.query( model.Job ).enable_eagerloads( False ) \
                .filter( model.Job.state.in_( in_list ) &
                         ( model.Job.handler == self.app.config.server_name ) ).all()

        for job in jobs_at_startup:
            if not self.app.toolbox.has_tool( job.tool_id, job.tool_version, exact=True ):
                log.warning( "(%s) Tool '%s' removed from tool config, unable to recover job" % ( job.id, job.tool_id ) )
                self.job_wrapper( job ).fail( 'This tool was disabled before the job completed.  Please contact your Galaxy administrator.' )
            elif job.job_runner_name is not None and job.job_runner_external_id is None:
                # This could happen during certain revisions of Galaxy where a runner URL was persisted before the job was dispatched to a runner.
                log.debug( "(%s) Job runner assigned but no external ID recorded, adding to the job handler queue" % job.id )
                job.job_runner_name = None
                if self.track_jobs_in_database:
                    job.set_state( model.Job.states.NEW )
                else:
                    self.queue.put( ( job.id, job.tool_id ) )
            elif job.job_runner_name is not None and job.job_runner_external_id is not None and job.destination_id is None:
                # This is the first start after upgrading from URLs to destinations, convert the URL to a destination and persist
                job_wrapper = self.job_wrapper( job )
                job_destination = self.dispatcher.url_to_destination(job.job_runner_name)
                if job_destination.id is None:
                    job_destination.id = 'legacy_url'
                job_wrapper.set_job_destination(job_destination, job.job_runner_external_id)
                self.dispatcher.recover( job, job_wrapper )
                log.info('(%s) Converted job from a URL to a destination and recovered' % (job.id))
            elif job.job_runner_name is None:
                # Never (fully) dispatched
                log.debug( "(%s) No job runner assigned and job still in '%s' state, adding to the job handler queue" % ( job.id, job.state ) )
                if self.track_jobs_in_database:
                    job.set_state( model.Job.states.NEW )
                else:
                    self.queue.put( ( job.id, job.tool_id ) )
            else:
                # Already dispatched and running
                job_wrapper = self.__recover_job_wrapper( job )
                self.dispatcher.recover( job, job_wrapper )
        if self.sa_session.dirty:
            self.sa_session.flush()