0035.yml

version: 35
description: hooks hooks phase 2
migrationScript: 0035-migration.sql
downgradeScript: 0035-downgrade.sql
methods:
  hooks_entities_load:
    deprecated: true
    description: See taskcluster-lib-entities
    mode: read
    serviceName: hooks
    args: partition_key text, row_key text
    returns: table (partition_key_out text, row_key_out text, value jsonb, version integer, etag uuid)
    body: |-
      begin
        return query
        select
          hooks_entities_load.partition_key,
          hooks_entities_load.row_key,
          encrypted_entity_buf_encode(
            encrypted_entity_buf_encode(
              entity_buf_encode(
                entity_buf_encode(
                  entity_buf_encode(
                    entity_buf_encode(
                      entity_buf_encode(
                        jsonb_build_object(
                          'PartitionKey', encode_string_key(hook_group_id),
                          'RowKey', encode_string_key(hook_id),
                          'hookGroupId', hook_group_id,
                          'hookId', hook_id,
                          'nextScheduledDate', next_scheduled_date),
                        'metadata', metadata::text),
                      'task', task::text),
                    'bindings', bindings::text),
                  'schedule', schedule::text),
                'triggerSchema', trigger_schema::text),
              'nextTaskId', encrypted_next_task_id),
            'triggerToken', encrypted_trigger_token) as value,
          1 as version,
          hooks.etag as etag
        from hooks
        where
          hooks.hook_group_id = decode_string_key(hooks_entities_load.partition_key) and
          hooks.hook_id = decode_string_key(hooks_entities_load.row_key);
      end
  hooks_entities_create:
    deprecated: true
    serviceName: hooks
    description: See taskcluster-lib-entities
    mode: write
    args: pk text, rk text, properties jsonb, overwrite boolean, version integer
    returns: uuid
    body: |-
      declare
        new_row hooks%ROWTYPE;
      begin
        select
          (properties ->> 'hookGroupId')::text,
          (properties ->> 'hookId')::text,
          entity_buf_decode(properties, 'metadata')::jsonb,
          entity_buf_decode(properties, 'task')::jsonb,
          entity_buf_decode(properties, 'bindings')::jsonb,
          entity_buf_decode(properties, 'schedule')::jsonb,
          entity_to_crypto_container_v0(properties, 'triggerToken')::jsonb,
          entity_to_crypto_container_v0(properties, 'nextTaskId')::jsonb,
          (properties ->> 'nextScheduledDate')::timestamptz,
          entity_buf_decode(properties, 'triggerSchema')::jsonb,
          public.gen_random_uuid()
        into new_row;
        if overwrite then
          raise exception 'overwrite not implemented';
        else
          execute 'insert into hooks select $1.*' using new_row;
        end if;
        return new_row.etag;
      end
  hooks_entities_remove:
    deprecated: true
    serviceName: hooks
    description: See taskcluster-lib-entities
    mode: write
    args: partition_key text, row_key text
    returns: table (etag uuid)
    body: |-
      begin
        return query delete from hooks
        where
          hooks.hook_group_id = decode_string_key(hooks_entities_remove.partition_key) and
          hooks.hook_id = decode_string_key(hooks_entities_remove.row_key)
        returning hooks.etag;
      end
  hooks_entities_modify:
    deprecated: true
    serviceName: hooks
    description: See taskcluster-lib-entities
    mode: write
    args: partition_key text, row_key text, properties jsonb, version integer, old_etag uuid
    returns: table (etag uuid)
    body: |-
      declare
        new_row hooks%ROWTYPE;
      begin
        select
          (properties ->> 'hookGroupId')::text,
          (properties ->> 'hookId')::text,
          entity_buf_decode(properties, 'metadata')::jsonb,
          entity_buf_decode(properties, 'task')::jsonb,
          entity_buf_decode(properties, 'bindings')::jsonb,
          entity_buf_decode(properties, 'schedule')::jsonb,
          entity_to_crypto_container_v0(properties, 'triggerToken')::jsonb,
          entity_to_crypto_container_v0(properties, 'nextTaskId')::jsonb,
          (properties ->> 'nextScheduledDate')::timestamptz,
          entity_buf_decode(properties, 'triggerSchema')::jsonb,
          public.gen_random_uuid() as etag
        into new_row;
        update hooks
        set (
          metadata,
          task,
          bindings,
          schedule,
          encrypted_trigger_token,
          encrypted_next_task_id,
          next_scheduled_date,
          trigger_schema,
          etag
        ) = (
          new_row.metadata,
          new_row.task,
          new_row.bindings,
          new_row.schedule,
          new_row.encrypted_trigger_token,
          new_row.encrypted_next_task_id,
          new_row.next_scheduled_date,
          new_row.trigger_schema,
          new_row.etag
        )
        where
          hooks.hook_group_id = decode_string_key(hooks_entities_modify.partition_key) and
          hooks.hook_id = decode_string_key(hooks_entities_modify.row_key) and
          hooks.etag = hooks_entities_modify.old_etag;
        if found then
          return query select new_row.etag;
          return;
        end if;
        perform hooks.etag from hooks
        where
          hooks.hook_group_id = decode_string_key(hooks_entities_modify.partition_key) and
          hooks.hook_id = decode_string_key(hooks_entities_modify.row_key);
        if found then
          raise exception 'unsuccessful update' using errcode = 'P0004';
        else
          raise exception 'no such row' using errcode = 'P0002';
        end if;
      end
  hooks_entities_scan:
    deprecated: true
    description: See taskcluster-lib-entities
    mode: read
    serviceName: hooks
    args: pk text, rk text, condition text, size integer, page integer
    returns: table (partition_key text, row_key text, value jsonb, version integer, etag uuid)
    body: |-
      declare
        cond text[];
        nsd_cond_operator text;
        nsd_cond_operand timestamptz;
        partition_key_var text;
        row_key_var text;
      begin
        if not condition is null then
          cond := regexp_split_to_array(condition, '\s+');
          nsd_cond_operator := cond[4];
          nsd_cond_operand := cond[5] :: timestamptz;

          return query select
            encode_string_key(hook_group_id) as partition_key,
            encode_string_key(hook_id) as row_key,
            encrypted_entity_buf_encode(
              encrypted_entity_buf_encode(
                entity_buf_encode(
                  entity_buf_encode(
                    entity_buf_encode(
                      entity_buf_encode(
                        entity_buf_encode(
                          jsonb_build_object(
                            'PartitionKey', encode_string_key(hook_group_id),
                            'RowKey', encode_string_key(hook_id),
                            'hookGroupId', hook_group_id,
                            'hookId', hook_id,
                            'nextScheduledDate', next_scheduled_date),
                          'metadata', metadata::text),
                        'task', task::text),
                      'bindings', bindings::text),
                    'schedule', schedule::text),
                  'triggerSchema', trigger_schema::text),
                'nextTaskId', encrypted_next_task_id),
              'triggerToken', encrypted_trigger_token) as value,
            1 as version,
            hooks.etag as etag from hooks
          where
            (hooks_entities_scan.pk is null or decode_string_key(hooks_entities_scan.pk) = hook_group_id) and
            (hooks_entities_scan.pk is null or decode_string_key(hooks_entities_scan.rk) = hook_id) and
            case
              when nsd_cond_operator = '=' then next_scheduled_date = nsd_cond_operand
              when nsd_cond_operator = '<' then next_scheduled_date < nsd_cond_operand
              when nsd_cond_operator = '<=' then next_scheduled_date <= nsd_cond_operand
              when nsd_cond_operator = '>' then next_scheduled_date > nsd_cond_operand
              when nsd_cond_operator = '>=' then next_scheduled_date >= nsd_cond_operand
              else next_scheduled_date <> nsd_cond_operand
            end
          order by hooks.hook_group_id, hooks.hook_id
          limit case
            when (size is not null and size > 0) then size + 1
            else null
          end
          offset case
            when (page is not null and page > 0) then page
            else 0
          end;
        else
          return query select
            encode_string_key(hook_group_id) as partition_key,
            encode_string_key(hook_id) as row_key,
            encrypted_entity_buf_encode(
              encrypted_entity_buf_encode(
                entity_buf_encode(
                  entity_buf_encode(
                    entity_buf_encode(
                      entity_buf_encode(
                        entity_buf_encode(
                          jsonb_build_object(
                            'PartitionKey', encode_string_key(hook_group_id),
                            'RowKey', encode_string_key(hook_id),
                            'hookGroupId', hook_group_id,
                            'hookId', hook_id,
                            'nextScheduledDate', next_scheduled_date),
                          'metadata', metadata::text),
                        'task', task::text),
                      'bindings', bindings::text),
                    'schedule', schedule::text),
                  'triggerSchema', trigger_schema::text),
                'nextTaskId', encrypted_next_task_id),
              'triggerToken', encrypted_trigger_token) as value,
            1 as version,
            hooks.etag as etag from hooks
          where
            (hooks_entities_scan.pk is null or decode_string_key(hooks_entities_scan.pk) = hook_group_id) and
            (hooks_entities_scan.rk is null or decode_string_key(hooks_entities_scan.rk) = hook_id)
          order by hooks.hook_group_id, hooks.hook_id
          limit case
            when (size is not null and size > 0) then size + 1
            else null
          end
          offset case
            when (size is not null and size > 0 and page is not null and page > 0) then page
            else 0
          end;
        end if;
      end
  get_hook:
    description: |-
      Get a hook. The returned table will have one or zero rows.
    mode: read
    serviceName: hooks
    args: hook_group_id_in text, hook_id_in text
    returns: table(hook_group_id text, hook_id text, metadata jsonb, task jsonb, bindings jsonb, schedule jsonb, encrypted_trigger_token jsonb, encrypted_next_task_id jsonb, next_scheduled_date timestamptz, trigger_schema jsonb)
    body: |-
      begin
        return query select
          hooks.hook_group_id,
          hooks.hook_id,
          hooks.metadata,
          hooks.task,
          hooks.bindings,
          hooks.schedule,
          hooks.encrypted_trigger_token,
          hooks.encrypted_next_task_id,
          hooks.next_scheduled_date,
          hooks.trigger_schema
        from hooks
        where
          hooks.hook_group_id = hook_group_id_in and
          hooks.hook_id = hook_id_in;
      end

  create_hook:
    description: |-
      Create a new hook. Raises UNIQUE_VIOLATION if the artifact already exists.
      Returns the newly created hook.
    mode: write
    serviceName: hooks
    args: hook_group_id_in text, hook_id_in text, metadata_in jsonb, task_in jsonb, bindings_in jsonb, schedule_in jsonb, encrypted_trigger_token_in jsonb, encrypted_next_task_id_in jsonb, next_scheduled_date_in timestamptz, trigger_schema_in jsonb
    returns: table(hook_group_id text, hook_id text, metadata jsonb, task jsonb, bindings jsonb, schedule jsonb, encrypted_trigger_token jsonb, encrypted_next_task_id jsonb, next_scheduled_date timestamptz, trigger_schema jsonb)
    body: |-
      begin
        return query insert
          into hooks (hook_group_id, hook_id, metadata, task, bindings, schedule, encrypted_trigger_token, encrypted_next_task_id, next_scheduled_date, trigger_schema)
          values (hook_group_id_in, hook_id_in, metadata_in, task_in, bindings_in, schedule_in, encrypted_trigger_token_in, encrypted_next_task_id_in, next_scheduled_date_in, trigger_schema_in)
        returning
          hooks.hook_group_id,
          hooks.hook_id,
          hooks.metadata,
          hooks.task,
          hooks.bindings,
          hooks.schedule,
          hooks.encrypted_trigger_token,
          hooks.encrypted_next_task_id,
          hooks.next_scheduled_date,
          hooks.trigger_schema;
      end

  update_hook:
    serviceName: hooks
    description: |-
      Update a queue artifact.
      Returns the up-to-date hook row that have the same hook group id and hook id.
    mode: write
    args: hook_group_id_in text, hook_id_in text, metadata_in jsonb, task_in jsonb, bindings_in jsonb, schedule_in jsonb, encrypted_trigger_token_in jsonb, encrypted_next_task_id_in jsonb, next_scheduled_date_in timestamptz, trigger_schema_in jsonb
    returns: table(hook_group_id text, hook_id text, metadata jsonb, task jsonb, bindings jsonb, schedule jsonb, encrypted_trigger_token jsonb, encrypted_next_task_id jsonb, next_scheduled_date timestamptz, trigger_schema jsonb)
    body: |-
      declare
        updated_row hooks%ROWTYPE;
      begin
        update hooks
        set (metadata, task, bindings, schedule, encrypted_trigger_token, encrypted_next_task_id, next_scheduled_date, trigger_schema) = (
          coalesce(metadata_in, hooks.metadata),
          coalesce(task_in, hooks.task),
          coalesce(bindings_in, hooks.bindings),
          coalesce(schedule_in, hooks.schedule),
          coalesce(encrypted_trigger_token_in, hooks.encrypted_trigger_token),
          coalesce(encrypted_next_task_id_in, hooks.encrypted_next_task_id),
          coalesce(next_scheduled_date_in, hooks.next_scheduled_date),
          coalesce(trigger_schema_in, hooks.trigger_schema)
        )
        where
          hooks.hook_group_id = hook_group_id_in and
          hooks.hook_id = hook_id_in
        returning
          hooks.hook_group_id,
          hooks.hook_id,
          hooks.metadata,
          hooks.task,
          hooks.bindings,
          hooks.schedule,
          hooks.encrypted_trigger_token,
          hooks.encrypted_next_task_id,
          hooks.next_scheduled_date,
          hooks.trigger_schema
        into updated_row;
        if found then
          return query select
            updated_row.hook_group_id,
            updated_row.hook_id,
            updated_row.metadata,
            updated_row.task,
            updated_row.bindings,
            updated_row.schedule,
            updated_row.encrypted_trigger_token,
            updated_row.encrypted_next_task_id,
            updated_row.next_scheduled_date,
            updated_row.trigger_schema;
          return;
        else
          raise exception 'no such row' using errcode = 'P0002';
        end if;
      end
  get_hooks:
    description: |-
      Get existing hooks filtered by the optional `hook_group_id`,
      ordered by the `hook_group_id` and `hook_id`.
      If the pagination arguments are both NULL, all rows are returned.
      Otherwise, page_size rows are returned at offset page_offset.
    mode: read
    serviceName: hooks
    args: hook_group_id_in text, next_scheduled_date_in timestamptz, page_size_in integer, page_offset_in integer
    returns: table(hook_group_id text, hook_id text, metadata jsonb, task jsonb, bindings jsonb, schedule jsonb, encrypted_trigger_token jsonb, encrypted_next_task_id jsonb, next_scheduled_date timestamptz, trigger_schema jsonb)
    body: |-
      begin
        return query
        select
          hooks.hook_group_id,
          hooks.hook_id,
          hooks.metadata,
          hooks.task,
          hooks.bindings,
          hooks.schedule,
          hooks.encrypted_trigger_token,
          hooks.encrypted_next_task_id,
          hooks.next_scheduled_date,
          hooks.trigger_schema
        from hooks
        where
          (hooks.hook_group_id = hook_group_id_in or hook_group_id_in is null) and
          (hooks.next_scheduled_date < next_scheduled_date_in or next_scheduled_date_in is null)
        order by hooks.hook_group_id, hooks.hook_id
        limit get_page_limit(page_size_in)
        offset get_page_offset(page_offset_in);
      end
  delete_hook:
    description: |-
      Delete a hook.
    mode: write
    serviceName: hooks
    args: hook_group_id_in text, hook_id_in text
    returns: void
    body: |-
      begin
        delete from hooks
        where
          hooks.hook_group_id = hook_group_id_in and
          hooks.hook_id = hook_id_in;
      end