cold_api.yaml

#
# cold_api.yaml defines the controlled object list with Dataset JSON API.
#
# @author: R. S. Doiel <rsdoiel@caltech.edu>
#
host: localhost:8112
# This htdocs directory is provided by cold_ui so we don't enable it.
#htdocs: htdocs
collections:
  # Each collection is an object. The path prefix is
  # /api/<dataset_name>/...
  - dataset: people.ds
    query:
      people_vocabulary: |
        select json_object('clpid', src->'clpid',
            'family_name', src->'family_name',
            'given_name', src->'given_name',
            'debug_orcid', src->'orcid',
            'debug_orcid_length', length(src->>'orcid'),
            'identifiers',
              if(length(src->>'orcid') > 1,
                json_array(
                  json_object(
                    'scheme', 'clpid',
                    'identifier', src->'clpid'
                  ),
                  json_object(
                    'scheme', 'orcid',
                    'identifier', src->'orcid'
                  )
                ),
                length(src->>'orcid') = 0,
                json_array(
                  json_object(
                    'scheme', 'clpid',
                    'identifier', src->'clpid'
                  )
                )
              ),
            'affiliations',
              if(src->>'ror' = 'https://ror.org/05dxps055',
                json_array(
                  json_object(
                    'id', replace(src->>'ror', 'https://ror.org/', ''),
                    'name', 'Caltech'
                  )
                ),
                src->>'ror' <> 'https://ror.org/05dxps055',
                json_array()
              )
            )
          as src
        from people
        order by src->'family_name' ASC, src->'given_name' ASC
      people_names: |
        select json_object(
          'clpid', src->'clpid',
          'family_name', src->'family_name',
          'given_name', src->'given_name',
          'orcid', ifnull(src->'orcid', ''),
          'thesis_id', ifnull(src->'thesis_id', ''),
          'directory_id', ifnull(src->'directory_user_id', '')) as src
        from people
        order by src->'family_name' ASC, src->'given_name' ASC;
      missing_bios: |
        select json_object('clpid', json_extract(src, '$.clpid'), 'ims_uid', json_extract(src, '$.directory_user_id')) as src
        from people
        where json_extract(src, '$.directory_user_id') != '' and json_extract(src, '$.bio') == '';
      directory_people: |
        select json_object(
            'clpid', json_extract(src, '$.clpid'),
            'imss_uid', json_extract(src, '$.directory_user_id'), 'caltech', json_extract(src, '$.caltech')
          ) as src
        from people
        where json_extract(src, '$.directory_user_id') != '';
      # family_name,given_name,cl_people_id,thesis_id,advisor_id,authors_id,archivesspace_id,directory_id,viaf_id,lcnaf,isni,wikidata,snac,orcid,image,educated_at,caltech,jpl,faculty,alumn,staff,postdoc,visitor,retired,emeritus,status,directory_person_type,title,bio,division,authors_count,thesis_count,data_count,advisor_count,editor_count,updated
      people_csv: |
        select json_object(
          'family_name', src->'family_name',
          'given_name', src->'given_name',
          'cl_people_id', src->'clpid',
          'thesis_id', src->'thesis_id',
          'advisor_id', src->'advisor_id',
          'authors_id', src->'authors_id',
          'archivesspace_id', src->'archivesspace_id',
          'directory_id', src->'directory_user_id',
          'viaf_id', src->'viaf',
          'lcnaf', src->'lcnaf',
          'isni', src->'isni',
          'wikidata', src->'wikidata',
          'orcid', src->'orcid',
          'image', '',
          'educated_at', src->'educated_at',
          'caltech', src->'caltech',
          'jpl', src->'jpl',
          'faculty', src->'faculty',
          'alumn', src->'alumn',
          'staff', src->'staff',
          'postdoc', src->'postdoc',
          'visitor', src->'visitor',
          'retired', src->'retired',
          'emeritus', src->'emeritus',
          'status', src->'status',
          'directory_person_type', src->'directory_person_type',
          'title', src->'title',
          'bio', src->'bio',
          'division', src->'division',
          'authors_count', '', 'thesis_count', '', 'data_count', '', 'advisor_count', '', 'editor_count', '',
          'updated', '', 'include_in_feeds', src->'include_in_feeds') as src
        from people order
        by src->'family_name', src->'given_name'
      # Provide a report of divisions and their members
      division_people: |
        select json_object(
          'division', src->'division', 'clpid', src->'clpid', 'orcid', src->'orcid',
          'family_name', src->'family_name', 'given_name', src->'given_name' ) as src
        from people
        where where src->'division' is not null and
          src->>'division' <> ''
        order by src->>'division', src->>'family_name', src->>'given_name'
      # People  Membership report, expands clgid per group per clpid
      people_membership: |
        WITH flattened_data AS (
            SELECT
                json_extract(src, '$.clpid') AS clpid,
                json_extract(src, '$.orcid') AS orcid,
                json_extract(src, '$.family_name') AS family_name,
                json_extract(src, '$.given_name') AS given_name,
                json_extract(g.value, '$.clgid') AS clgid,
                json_extract(g.value, '$.group_name') AS group_name
            FROM
                people,
                json_each(json_extract(src, '$.groups')) AS g
        )
        SELECT
            json_object(
              'clgid', clgid,
                'group_name', group_name,
              'clpid', clpid,
              'orcid', orcid,
                'family_name', family_name,
                'given_name', given_name
            ) as obj
        FROM
            flattened_data
        ORDER BY
            clgid, clpid;
      # What follows are object level permissions
      lookup_clgid: |
        select json_object(
          'clpid', src->'clpid',
          'family_name', src->'family_name',
          'given_name', src->'given_name') as src
        from people
        where exists (select true from json_each(src, '$.groups') where json_extract(value, '$.clgid') = ?)
        order by src->'family_name' ASC, src->'given_name' asc;
    keys: true
    create: true
    read: true
    update: true
    # This sets versioning behavior
    versions: true
  - dataset: groups.ds
    query:
      group_vocabulary: |
        select json_object('clgid', src->'clgid', 'group_name', src->'name') as src
        from groups
        order by src->'name' asc;
      group_names: |
        select json_object('clgid', src->'clgid', 'group_name', src->'name') as src
        from groups
        order by src->'name' asc;
      lookup_name: |
        select json_object('clgid', src->'clgid', 'group_name', src->'name')
        from groups
        where (src->>'name' like ?) or
              exists(
                    select true
                    from json_each(src->'alternative')
                    where (json_each.value like ?)
              )
        order by src->>'name' asc;
      lookup_name_or_clgid:
        select json_object('clgid', src->'clgid', 'group_name', src->'name')
        from groups
        where (src->>'name' like ?) or
              (src->>'clgid' like ?) or
              exists(
                    select true
                    from json_each(src->'alternative')
                    where (json_each.value like ?)
              )
        order by src->>'name' asc;
    # What follows are object level permissions
    keys: true
    create: true
    read: true
    update: true
    # This sets versioning behavior
    versions: true
  - dataset: funders.ds
    query:
      funder_names: |
        select json_object('clfid', src->'clfid', 'name', src->'name', 'ror', src->'ror') as src
        from funders
        order by src->'name';
    # What follows are object level permissions
    keys: true
    create: true
    read: true
    update: true
    # This sets versioning behavior
    versions: true
  - dataset: subjects.ds
    query:
      subject_names: |
        select json_object('clsid', src->'clsid', 'name', src->'name') as src
        from subjects
        order by src->'name';
    # What follows are object level permissions
    keys: true
    create: true
    read: true
    update: true
    # This sets versioning behavior
    versions: true
  - dataset: thesis_options.ds
    query:
      thesis_option_names: |
        select json_object(
           'option_id', src->'option_id',
           'name', src->'name',
           'division', src->'division'
        ) as src
        from thesis_options
        order by src->'name';
    # What follows are object level permissions
    keys: true
    create: true
    read: true
    update: true
    # This sets versioning behavior
    versions: true
  - dataset: journals.ds
    query:
      journal_names: |
        select json_object('issn', src->'issn', 'name', src->'name') as src
        from journals
        order by src->'name';
    # What follows are object level permissions
    keys: true
    create: true
    read: true
    update: true
    # This sets versioning behavior
    versions: true
  - dataset: doi_prefix.ds
    query:
      doi_prefix_names: |
        select json_object('doi_prefix', src->'doi_prefix', 'name', src->'name') as src
        from doi_prefix
        order by src->'name';
    # What follows are object level permissions
    keys: true
    create: true
    read: true
    update: true
    # This sets versioning behavior
    versions: true
  - dataset: reports.ds
    query:
      # report_list shows the report requests in a stack view, most recently changed at the time regardless of status
      report_list: |
        select json_object('id', src->'id', 'link', src->'link', 'content_type', src->'content_type',
          'report_name', src->'report_name', 'emails', src->'emails', 'status', src->'status',
          'requested', src->'requested', 'updated', src->'updated') as src
        from reports
        order by src->>'updated' desc;
      # next request implements the FIFO, i.e. a queue
      next_request: |
        select json_object('id', src->'id', 'link', src->'link', 'content_type', src->'content_type',
          'report_name', src->'report_name', 'emails', src->'emails', 'status', src->'status',
          'requested', src->'requested', 'updated', src->'updated') as src
        from reports
        where src->>'status' = 'requested'
        order by src->>'updated' asc;
    keys: true
    read: true
    create: true
    update: true
    # This sets versioning behavior
    versions: true
  - dataset: ror.ds
    query:
      # Retrieve ROR by ror prefix value
      get_ror: |
        select src
        from ror
        where _key = ?;
      # Lookup a ror
      lookup_by_name_or_acronym: |
        select json_object(
          'ror', _key,
          'name', src->'name',
          'country', src->'country',
          'acronyms', src->'acronyms'
        ) as src
        from ror
        where src->>'name' like ?
          or exists (
            select 1
            from json_each(src->'acronyms')
            where value like ?)
        order by src->'name';
      # Lookup a ror by name
      lookup_by_name: |
        select json_object(
          'ror', _key,
          'name', src->'name',
          'country', src->'country',
          'acronyms', src->'acronyms'
        ) as src
        from ror
        where src->>'name' like ?
        order by src->'name';
      # Lookup a ror by acronym
      lookup_by_acronym: |
        select json_object('ror', _key,
          'name', src->'name',
          'country', src->'country',
          'acronyms', src->'acronyms'
        ) as src
        from ror
        where exists (
            select 1
            from json_each(src->'acronyms')
            where value like ?)
        order by src->'name';
      # clear the ror data
      clear_ror_data: |
        delete from ror
    keys: true
    read: true
    create: true
    update: true
    # This sets versioning behavior
    versions: true
  - dataset: rdm_review_queue.ds
    query:
      # browser JSON objects by descending updated timestamp
      browse:
        select src
        from rdm_review_queue
        order by updated desc

      # Search the JSON object for a user provided value
      search: |
        select src
        from rdm_review_queue
        where src like ?
        order by updated desc

      # search by name
      by_name: |
        select src
        from rdm_review_queue
        where exists (
            select 1
            from json_each(src, '$.creators')
            where json_extract(value, '$.person_or_org.name') like ?
        )

      # search by clpid
      by_clpid: |
        SELECT src
        FROM rdm_review_queue
        WHERE EXISTS (
            SELECT 1
            FROM json_each(src, '$.creators') AS creator
            JOIN json_each(creator.value, '$.person_or_org.identifiers') AS identifier
            WHERE json_extract(identifier.value, '$.scheme') = 'clpid'
              AND json_extract(identifier.value, '$.identifier') = ?
        )

      # search by orcid
      by_orcid: |
        SELECT src
        FROM rdm_review_queue
        WHERE EXISTS (
            SELECT 1
            FROM json_each(src, '$.creators') AS creator
            JOIN json_each(creator.value, '$.person_or_org.identifiers') AS identifier
            WHERE json_extract(identifier.value, '$.scheme') = 'orcid'
              AND json_extract(identifier.value, '$.identifier') = ?
        )

      # search by group name
      by_clgid: |
        SELECT src
        FROM rdm_review_queue
        WHERE EXISTS (
            SELECT 1
            FROM json_each(src, '$.custom_fields."caltech:groups"')
            WHERE json_extract(value, '$.id') = ?
        )

    keys: true
    read: true
    # datasetd requires create,update permissions for support queries
    create: true
    update: true
    # This sets versioning behavior
    versions: true