ERM - OpenAccess derived table for publication requests 'openaccess_publication_request'
- Stefan Dombek
- Owen Stephens
Content
Overview
- The purpose of this derived table is to show statistics associated with Open Access publication requests.
Github Issue
- https://github.com/folio-org/folio-analytics/issues/784 (ERM - Create derived table 'openaccess_publication_request' for Metadb)
Attributes for the derived table
| Folio Table | Folio Attribute | Description | Datatype | Sample output | FK (From table) | PK (To table) | Notes |
|---|---|---|---|---|---|---|---|
| folio_oa.publication_request | pr_id | UUID for the publication request | UUID | 3002f645-1b70-4cf2-83ce-ee0cfdd1dd3e | Table: folio_oa.publication_request Attribute: pr_id | Table: folio_oa.charge Attribute: ch_owner_fk | |
| folio_oa.publication_request | pr_request_date | Date when the request was made (manually) | TIMESTAMP | 2021-01-04 00:00:00.000 | |||
| folio_oa.publication_request | pr_date_created | Timestamp when the record was created (comes from the system) | TIMESTAMP | 2023-02-17 08:47:44.597 | |||
| folio_oa.publication_request | pr_last_updated | Timestamp when the record is last updated (comes from the system) | TIMESTAMP | 2023-05-24 21:16:33.135 | |||
| folio_oa.publication_request | pr_request_number | Number created from the system for a publication request | VARCHAR | OAM-001 2023-001 | It is possible to create a template how it will be created optionally with a number generator | ||
| folio_oa.publication_request | pr_title | Title of the publication | VARCHAR | Decontamination of lupine (Lupinus angustifolius) flakes ... | Article or book title | ||
| folio_oa.publication_request | pr_request_status | UUID reference to a refdata value | UUID | 8aaa80fe859fe8ee0185a0ea5e230045 | Table: folio_oa.publication_request Attribute: pr_request_status | Table: folio_oa.refdata_value Attribute: rdv_id | |
| folio_oa.refdata_value | pr_request_status_value | Refdata value for publication request status | VARCHAR | open | |||
| folio_oa.refdata_value | pr_request_status_label | Refdata label for publication request status | VARCHAR | Open | |||
| folio_oa.publication_request | pr_pub_type_fk | UUID reference to a refdata value | UUID | 8aaa80fe859fe8ee0185a0ea5e06003e | Table: folio_oa.publication_request Attribute: pr_pub_type_fk | Table: folio_oa.refdata_value Attribute: rdv_id | |
| folio_oa.refdata_value | pr_pub_type_value | Refdata value for publication request publication type | VARCHAR | journal_article | |||
| folio_oa.refdata_value | pr_pub_type_label | Refdata label for publication request publication type | VARCHAR | Zeitschriftenartikel | |||
| folio_oa.publication_request | pr_authnames | List of names of the authors of the publication | VARCHAR | Just a string, not separated by the system | |||
| folio_oa.publication_request | pr_corresponding_author_fk | FK for a record in folio_oa.request_party | UUID | 4dc4e261-6150-47a7-a6a1-51f2ae3ef295 | Table: folio_oa.publication_request Attribute: pr_corresponding_author_fk | Table: folio_oa. folio_oa.request_party Attribute: rp_id | folio_oa.request_party can be used to get the relation between the role to the "party record" Limited by 2 roles |
| folio_oa.refdata_value | pr_corresponding_author_role_value | Refdata value for the role of the corresponding author | VARCHAR | corresponding_author | |||
| folio_oa.refdata_value | pr_corresponding_author_role_label | Refdata label for role of the corresponding author | VARCHAR | Korrespondenzautor | |||
| folio_oa.party | pr_corresponding_author_name | Name of the linked corresponding author | VARCHAR | Erika Musterfrau | |||
| folio_oa.request_party | pr_corresponding_author_rp_party_fk | FK to the table party | UUID | a6034022-5cad-40e3-935e-ca73e65b3bf6 | Table: folio_oa.request_party Attribute: rp_party_fk | Table: folio_oa. party Attribute: p_id | |
| folio_oa.publication_request | pr_local_ref | ID to use for a local repository or storage | VARCHAR | There is a separate column for DOI | |||
| folio_oa.publication_request | pr_pub_url | URL for the publication | VARCHAR | ||||
| folio_oa.publication_request | pr_subtype | UUID reference to a refdata value | UUID | Table: folio_oa.publication_request Attribute: pr_subtype | Table: folio_oa.refdata_value Attribute: rdv_id | Type of publication, e.g. Review, Research Article ... | |
| folio_oa.refdata_value | pr_subtype_value | Refdata value for publication request subtype | VARCHAR | ||||
| folio_oa.refdata_value | pr_subtype_label | Refdata label for publication request subtype | VARCHAR | ||||
| folio_oa.publication_request | pr_publisher | UUID reference to a refdata value | UUID | 8aaa844885f4499b01862b15321b0017 | Table: folio_oa.publication_request Attribute: pr_publisher | Table: folio_oa.refdata_value Attribute: rdv_id | |
| folio_oa.refdata_value | pr_publisher_value | Refdata value for publication request publisher | VARCHAR | wiley | |||
| folio_oa.refdata_value | pr_publisher_label | Refdata label for publication request publisher | VARCHAR | Wiley | |||
| folio_oa.publication_request | pr_license | UUID reference to a refdata value | UUID | 8aaa809385efeeed0185f3644cb5000d | Table: folio_oa.publication_request Attribute: pr_license | Table: folio_oa.refdata_value Attribute: rdv_id | License used for the publication |
| folio_oa.refdata_value | pr_license_value | Refdata value for publication request license | VARCHAR | cc_by | |||
| folio_oa.refdata_value | pr_license_label | Refdata label for publication request license | VARCHAR | CC BY | |||
| folio_oa.publication_request | pr_doi | DOI for the publication | VARCHAR | 10.3389/fmicb.2022.822369 | |||
| folio_oa.publication_request | pr_group_fk | ? | ? | ||||
| folio_oa.publication_request | pr_agreement_reference | FK for publication request agreement | UUID | Table: folio_oa.publication_request Attribute: pr_agreement_reference | Table: folio_oa.publication_request_agreement Attribute: rol_id | Remote ID to Agreements Table: folio_oa.publication_request_agreement Attribute: rol_remote_id | |
| folio_oa.publication_request | pr_without_agreement | Is there a agreement to the publication request | BOOLEAN | A link to a agreement is not required | |||
| folio_oa.publication_request | pr_work_fk | FK for a record in folio_oa.work | UUID | b39a2800-1249-489e-8c8c-e1939c48dd73 | Table: folio_oa.publication_request Attribute: pr_work_fk | Table: folio_oa.work Attribute: w_id | |
| folio_oa.publication_request | pr_book_date_of_publication | Year of publication | VARCHAR | For books only | |||
| folio_oa.publication_request | pr_book_place_of_publication | Place of publication | VARCHAR | For books only | |||
| folio_oa.publication_request | pr_work_indexed_in_doaj_fk | UUID reference to a refdata value | UUID | 8aaa80fe859fe8ee0185a0ea5d8e0021 | Table: folio_oa.publication_request Attribute: pr_work_indexed_in_doaj_fk | Table: folio_oa.refdata_value Attribute: rdv_id | |
| folio_oa.refdata_value | pr_work_indexed_in_doaj_value | Refdata value for publication request doaj status | VARCHAR | yes | |||
| folio_oa.refdata_value | pr_work_indexed_in_doaj_label | Refdata label for publication request doaj status | VARCHAR | Yes | |||
| folio_oa.publication_request | pr_work_oa_status_fk | UUID reference to a refdata value | UUID | 8aaa80fe859fe8ee0185a0ea5d9b0024 | Table: folio_oa.publication_request Attribute: pr_work_oa_status_fk | Table: folio_oa.refdata_value Attribute: rdv_id | |
| folio_oa.refdata_value | pr_work_oa_status_value | Refdata value for publication request open access status | VARCHAR | gold | |||
| folio_oa.refdata_value | pr_work_oa_status_label | Refdata label for publication request open access status | VARCHAR | Gold | |||
| folio_oa.publication_request | pr_corresponding_institution_level_1_fk | UUID reference to a refdata value | UUID | 8aaa80aa85c4472e0185e331db060010 | Table: folio_oa.publication_request Attribute: pr_corresponding_institution_level_1_fk | Table: folio_oa.refdata_value Attribute: rdv_id | |
| folio_oa.refdata_value | pr_corresponding_institution_level_1_value | Refdata value for publication request "institution level 1" | VARCHAR | veterinarmedizinische_fakultat | |||
| folio_oa.refdata_value | pr_corresponding_institution_level_1_label | Refdata label for publication request "institution level 1" | VARCHAR | Veterinärmedizinische Fakultät | |||
| folio_oa.publication_request | pr_corresponding_institution_level_2 | Second level for free naming the institution | VARCHAR | Institut für Bakteriologie | |||
| folio_oa.publication_request | pr_retrospective_oa | Indicates if the request is for ‘retrospective OA’ | BOOLEAN | Where a publication previously published as closed access is converted to open access through a payment or other mechanism | |||
| folio_oa.publication_request | pr_closure_reason_fk | UUID reference to a refdata value | UUID | Table: folio_oa.publication_request Attribute: pr_closure_reason_fk | Table: folio_oa.refdata_value Attribute: rdv_id | ||
| folio_oa.refdata_value | pr_closure_reason_value | Refdata value for publication request closure reason | VARCHAR | ||||
| folio_oa.refdata_value | pr_closure_reason_label | Refdata label for publication request closure reason | VARCHAR | ||||
| folio_oa.publication_request | pr_request_contact_fk | FK for a record in folio_oa.request_party | UUID | f0c58bbc-0a47-4b80-9c16-f7b98918c512 | Table: folio_oa.publication_request Attribute: pr_request_contact_fk | Table: folio_oa. folio_oa.request_party Attribute: rp_id | |
| folio_oa.refdata_value | pr_request_contact_role_value | Refdata value for role of the request contact | VARCHAR | request_contact | |||
| folio_oa.refdata_value | pr_request_contact_role_label | Refdata label for role of the request contact | VARCHAR | Antragsteller | |||
| folio_oa.party | pr_request_contact_name | Name of the linked request contact | VARCHAR | Erika Musterfrau | |||
folio_oa.request_party | pr_request_contact_rp_party_fk | FK to the table party | UUID | a6034022-5cad-40e3-935e-ca73e65b3bf6 | Table: folio_oa.request_party Attribute: rp_party_fk | Table: folio_oa. party Attribute: p_id |
Linking approach
folio_oa.publication_request
LEFT JOIN folio_oa.refdata_value AS pr_status ON pr_status.rdv_id = publication_request.pr_request_status
LEFT JOIN folio_oa.refdata_value AS pr_pub_type ON pr_pub_type.rdv_id = publication_request.pr_pub_type_fk
LEFT JOIN folio_oa.refdata_value AS pr_subtype ON pr_subtype.rdv_id = publication_request.pr_subtype
LEFT JOIN folio_oa.refdata_value AS pr_publisher ON pr_publisher.rdv_id = publication_request.pr_publisher
LEFT JOIN folio_oa.refdata_value AS pr_license ON pr_license.rdv_id = publication_request.pr_license
LEFT JOIN folio_oa.refdata_value AS pr_doaj_status ON pr_doaj_status.rdv_id = publication_request.pr_work_indexed_in_doaj_fk
LEFT JOIN folio_oa.refdata_value AS pr_oa_status ON pr_oa_status.rdv_id = publication_request.pr_work_oa_status_fk
LEFT JOIN folio_oa.refdata_value AS pr_corresponding_institution_level_1 ON pr_corresponding_institution_level_1.rdv_id = publication_request.pr_corresponding_institution_level_1_fk
LEFT JOIN folio_oa.refdata_value AS pr_closure_reason ON pr_closure_reason.rdv_id = publication_request.pr_closure_reason_fk
LEFT JOIN folio_oa.request_party AS pr_corresponding_author ON pr_corresponding_author.rp_id = publication_request.pr_corresponding_author_fk
LEFT JOIN folio_oa.refdata_value AS rp_role_corresponding_author ON rp_role_corresponding_author.rdv_id = pr_corresponding_author.rp_role
LEFT JOIN folio_oa.request_party AS pr_request_contact ON pr_request_contact.rp_id = publication_request.pr_request_contact_fk
LEFT JOIN folio_oa.refdata_value AS rp_role_request_contact ON rp_role_request_contact.rdv_id = pr_request_contact.rp_role
LEFT JOIN folio_oa.party AS party_corresponding_author ON party_corresponding_author.p_id = pr_corresponding_author.rp_party_fk
LEFT JOIN folio_oa.party AS party_request_contact ON party_request_contact.p_id = pr_request_contact.rp_party_fk
Explanation to special relations between tables
Relation "folio_oa.publication_request" to "folio_agreements.subscription_agreement"
You can link a publication request to an agreement via a third table "folio_oa.publication_request_agreement".
folio_oa.publication_request
LEFT JOIN folio_oa.publication_request_agreement ON publication_request_agreement.rol_id = publication_request.pr_agreement_reference
LEFT JOIN folio_agreements.subscription_agreement ON subscription_agreement.sa_id = publication_request_agreement.rol_remote_id
Relation "folio_oa.publication_request" to "folio_oa.party"
The publication_request table contains 2 FKs that go to the party table.
- pr_corresponding_author_fk
- pr_request_contact_fk
In order to get the right link, an additional table "request_party" is used, which is connected here for each FK with a LEFT JOIN. It behaves similarly to the refdata_value
LEFT JOIN folio_oa.request_party AS pr_corresponding_author ON pr_corresponding_author.rp_id = publication_request.pr_corresponding_author_fk
LEFT JOIN folio_oa.refdata_value AS rp_role_corresponding_author ON rp_role_corresponding_author.rdv_id = pr_corresponding_author.rp_role
LEFT JOIN folio_oa.request_party AS pr_request_contact ON pr_request_contact.rp_id = publication_request.pr_request_contact_fk
LEFT JOIN folio_oa.refdata_value AS rp_role_request_contact ON rp_role_request_contact.rdv_id = pr_request_contact.rp_role
The FK "rp_party_fk" in table "folio_oa.request_party" can be used to link to each party for the different roles.
This FK is include for the different roles as
- pr_request_contact_rp_party_fk
- pr_corresponding_author_rp_party_fk
Linking between the derived tables
You can link the derived tables "openaccess_publication_request" and "openaccess_party" via the UUIDs
- openaccess_publication_request.pr_request_contact_rp_party_fk = openaccess_party.p_id
- openaccess_publication_request.pr_corresponding_author_rp_party_fk= openaccess_party.p_id
In practice, the link must be made twice from the derived table "openaccess_publication_request" to the derived table "openaccess_party".
openaccess_publication_request
LEFT JOIN openaccess_party AS openaccess_party_request_contact ON openaccess_party_request_contact.p_id = openaccess_publication_request.pr_request_contact_rp_party_fk
LEFT JOIN openaccess_party AS openaccess_party_corresponding_author ON openaccess_party_corresponding_author.p_id =
openaccess_publication_request.pr_corresponding_author_rp_party_fk
Relation "folio_oa.publication_request" to "folio_oa.publication_status"
A publication request can have different processing statuses over time. You can find these processing statuses with their dates in the "publication_status" table.
folio_oa.publication_request
LEFT JOIN folio_oa.publication_status ON publication_status.ps_owner_fk = publication_request.pr_id
LEFT JOIN folio_oa.refdata_value AS publication_status_description ON publication_status_description.rdv_id = publication_status.ps_publication_status
Data for types that are in the “publication_request” and “works” tables
It appears that some data about types is duplicated in the "publication_request" and "works" tables. However, that is not entirely true. What is the difference?
- "publication_request" contains the types for a publication when the request was created in the system.
- "works" contains the types for a "work" with the current types (The data needs to be refreshed to get the latest.). These may change over time and differ from the types at the time the publication request was made.