0071.yml

version: 71
description: add provider filter to get_non_stopped_workers_quntil
migrationScript: |
  begin
    grant select on queue_workers to $db_user_prefix$_worker_manager;
  end
downgradeScript: |
  begin
    revoke select on queue_workers from $db_user_prefix$_worker_manager;
  end
methods:
  get_non_stopped_workers_quntil:
    deprecated: true
  get_non_stopped_workers_quntil_providers:
    description: |-
      Get non-stopped workers filtered by the optional arguments,
      ordered by `worker_pool_id`, `worker_group`, and  `worker_id`.
      If the pagination arguments are both NULL, all rows are returned.
      Otherwise, page_size rows are returned at offset `page_offset`.
      The `quaratine_until` contains NULL or a date in the past if the
      worker is not quarantined, otherwise the date until which it is
      quaratined. `providers_filter_cond` and `providers_filter_value` used to
      filter `=` or `<>` provider by value.
    mode: read
    serviceName: worker_manager
    args: worker_pool_id_in text, worker_group_in text, worker_id_in text, providers_filter_cond text, providers_filter_value text, page_size_in integer, page_offset_in integer
    returns: table(worker_pool_id text, worker_group text, worker_id text, provider_id text, created timestamptz, expires timestamptz, state text, provider_data jsonb, capacity integer, last_modified timestamptz, last_checked timestamptz, secret jsonb, etag uuid, quarantine_until timestamptz)
    body: |-
      begin
        return query
        select
          workers.worker_pool_id,
          workers.worker_group,
          workers.worker_id,
          workers.provider_id,
          workers.created,
          workers.expires,
          workers.state,
          workers.provider_data,
          workers.capacity,
          workers.last_modified,
          workers.last_checked,
          workers.secret,
          workers.etag,
          queue_workers.quarantine_until
        from
          workers
          left join queue_workers on
            workers.worker_pool_id = queue_workers.task_queue_id and
            workers.worker_id = queue_workers.worker_id and
            workers.worker_group = queue_workers.worker_group
        where
          (workers.worker_pool_id = worker_pool_id_in or worker_pool_id_in is null) and
          (workers.worker_group = worker_group_in or worker_group_in is null) and
          (workers.worker_id = worker_id_in or worker_id_in is null) and
          (workers.state <> 'stopped') and
          (providers_filter_cond is null or providers_filter_value is null or
            case
              when providers_filter_cond = '='
                then workers.provider_id = ANY(string_to_array(providers_filter_value, ','))
              when providers_filter_cond = '<>'
                then workers.provider_id <> ALL(string_to_array(providers_filter_value, ','))
            end
            )
        order by worker_pool_id, worker_group, worker_id
        limit get_page_limit(page_size_in)
        offset get_page_offset(page_offset_in);
      end