ERM - OpenAccess derived table for checklists 'openaccess_checklists'
- Stefan Dombek
Content
Overview
- The purpose of this derived table is to show the checklist items to an Open Access publication requests.
Github Issue
- https://github.com/folio-org/folio-analytics/issues/794 (ERM - Create derived table 'openaccess_checklists' for Metadb)
Attributes for the derived table
| Folio Table | Folio Attribute | Description | Datatype | Sample Output | FK (From table) | PK (To table) | Notes |
|---|---|---|---|---|---|---|---|
| folio_oa.checklist_item | cli_id | UUID of the checklist item | UUID | f3f357f4-fdd4-40da-9133-bd2e87ca74ee | |||
| folio_oa.checklist_item | cli_version | INTEGER | 0 | ||||
| folio_oa.checklist_item | cli_date_created | Timestamp when the checklist item was created | TIMESTAMP | 2023-03-10 12:54:19.659 | |||
| folio_oa.checklist_item | cli_last_updated | Timestamp when the checklist item was updated | TIMESTAMP | 2023-03-10 12:54:19.659 | |||
| folio_oa.checklist_item | cli_definition_fk | UUID reference to folio_oa.checklist_item_definition | UUID | da97441a-a990-4320-b3a5-56782dfa3ed7 | Table: folio_oa.checklist_item Attribute: cli_definition_fk | Table: folio_oa.checklist_item_definition Attribute: clid_id | |
| folio_oa.checklist_item_definition | cli_definition_value | Value of the definition for a checklist item | VARCHAR | nachwuchswissenschaftlerin | Original attribute name: clid_name | ||
| folio_oa.checklist_item_definition | cli_definition_label | Label for the definition for a checklist item | VARCHAR | NachwuchswissenschaftlerIn | |||
| folio_oa.checklist_item_definition | cli_definition_description | Description for the checklist item definition | TEXT | Der/die Antragststeller:in ist Nachwuchswissenschaftler:in. | Original attribute name: clid_description | ||
| folio_oa.checklist_item | cli_parent_fk | UUID reference to associated data where the checklists are used | UUID | 3e0a383d-b1d9-4a8f-ad39-d35670e7158d | Table: folio_oa.checklist_item Attribute: cli_parent_fk | Example Table: folio_oa.publication_request Attribute: pr_id | |
| folio_oa.checklist_item | cli_outcome_fk | UUID reference to a refdata value | UUID | 8aaa80fe859fe8ee0185a0ea5d21000c | Table: folio_oa.checklist_item Attribute: cli_outcome_fk | Table: folio_oa.refdata_value Attribute: rdv_id | |
| folio_oa.refdata_value | cli_outcome_value | Refdata value for checklist item outcome | VARCHAR | yes | |||
| folio_oa.refdata_value | cli_outcome_label | Refdata label for checklist item outcome | VARCHAR | Ja | |||
| folio_oa.checklist_item | cli_status_fk | UUID reference to a refdata value | UUID | 8aaa80fe859fe8ee0185a0ea5e330047 | Table: folio_oa.checklist_item Attribute: cli_status_fk | Table: folio_oa.refdata_value Attribute: rdv_id | |
| folio_oa.refdata_value | cli_status_value | Refdata value for checklist item status | VARCHAR | erfolgreich_abgeschlossen | |||
| folio_oa.refdata_value | cli_status_label | Refdata label for checklist item status | VARCHAR | Erfolgreich abgeschlossen | |||
| folio_oa.publication_request | publication_request_id | UUID reference to a record of the publication request | UUID | 2a5945b2-ffda-41f8-bee7-02e5ca52474b | Table: folio_oa.checklist_item Attribute: cli_parent_fk | Table: folio_oa.publication_request Attribute: pr_id | The attribute should enable a better handling. Normally the 'cli_parent_fk' attribute would be sufficient. However, the connection is clearer this way. |
Linking approach
folio_oa.checklist_item
LEFT JOIN folio_oa.checklist_item_definition ON checklist_item_definition.clid_id :: UUID = checklist_item.cli_definition_fk :: UUID
LEFT JOIN folio_oa.refdata_value AS cli_outcome ON cli_outcome.rdv_id :: UUID = checklist_item.cli_outcome_fk :: UUID
LEFT JOIN folio_oa.refdata_value AS cli_status ON cli_status.rdv_id :: UUID = checklist_item.cli_status_fk :: UUID
LEFT JOIN folio_oa.publication_request ON publication_request.pr_id :: UUID = checklist_item.cli_parent_fk :: UUID
Explanation to special relations between tables
Relation between the derived tables "openaccess_publication_request" and "openaccess_checklists"
There are 2 ways to connect these two derived tables via UUIDs.
- The derived table for the checklist items contains a FK "cli_parent_fk". In this case the "parent" is the publication request and you have to link to the publication request via the UUID "pr_id" in the derived table for the "publication_request".
openaccess_publication_request
LEFT JOIN openaccess_checklists ON openaccess_checklists.cli_parent_fk = openaccess_publication_request.pr_id - There is a attribute "publication_request_id" in the derived table for the checklists that should enable a better handling for the connection to the derived table for the publication requests. Normally the 'cli_parent_fk' attribute would be sufficient. However, the connection is clearer this way.
openaccess_publication_request
LEFT JOIN openaccess_checklists ON openaccess_checklists.publication_request_id = openaccess_publication_request.pr_id