Checks in CERA2_UPD

(sind weitgehend automatisiert in "metaXA / Admin"

/*
  CERA2_UPD Tabellen (für metaXA, citeXA):
  -------------------
    upd_institute 
    upd_institute_external_id
    upd_person 
    upd_person_external_id
    upd_citation
    upd_project
    
 to do: upd_authorlists
        upd_keylist
        upd_code
        upd_topic
 
    
Merge: Entries (alle Einträge in den o.a. Tabellen in denen "UPD_BY is not NULL")  (--> merge.sql)
       - Wie oft mergen? -->       mindestens Montag Morgen während CERA Wartung

Folgende Checks sollten vor dem übertragen nach cera2 gemacht werden:
         1. external_id: URL zugreifbar?
         2. restl. Felder sinnvoll ausgefüllt?
*/
 
   
-- 1. INSTITUTE
select count(*) from upd_institute;
select count(*) from cera2.institute;

--delete upd_institute;

insert into upd_institute (
    INSTITUTE_ID,  
    INSTITUTE_NAME,
    INSTITUTE_ACRONYM,
    DEPARTMENT_NAME,
    DEPARTMENT_ACRONYM,
    COUNTRY,
    STATE_OR_PROVINCE,
    PLACE,
    STREET,
    STREET_POSTAL_CODE,
    POBOX,
    POBOX_POSTAL_CODE,
    URL,
    ADDITIONAL_INFO) select 
    INSTITUTE_ID,  
    INSTITUTE_NAME,
    INSTITUTE_ACRONYM,
    DEPARTMENT_NAME,
    DEPARTMENT_ACRONYM,
    COUNTRY,
    STATE_OR_PROVINCE,
    PLACE,
    STREET,
    STREET_POSTAL_CODE,
    POBOX,
    POBOX_POSTAL_CODE,
    URL,
    ADDITIONAL_INFO from CERA2.INSTITUTE;

-- 2.) INSTITUTE_EXTERNAL_ID:

select count(*) from upd_institute_external_id;
select count(*) from cera2.institute_external_id;

--delete upd_institute_external_id;

insert into upd_institute_external_id (
    INSTITUTE_EXTERNAL_ID,
    EXTERNAL_ID_URL,
    INSTITUTE_ID,
    EXTERNAL_IDENTIFIER_TYPE_ID,
    EXTERNAL_ID) select INSTITUTE_EXTERNAL_ID,
    EXTERNAL_ID_URL,
    INSTITUTE_ID,
    EXTERNAL_IDENTIFIER_TYPE_ID,
    EXTERNAL_ID from cera2.institute_external_id;

-- 3.) PERSON

select count(*) from upd_person;
select count(*) from cera2.person;

select * from cera2.person where person_id not in (select person_id from upd_person);
select * from upd_person where person_id not in (select person_id from cera2.person);

--delete upd_person;

insert into upd_person (
    PERSON_ID,
    FIRST_NAME,
    SECOND_NAME,
    LAST_NAME,
    TITLE,
    INSTITUTE_ID,
    TELEPHONE,
    FAX,
    URL,
    EMAIL ) select
    PERSON_ID,
    FIRST_NAME,
    SECOND_NAME,
    LAST_NAME,
    TITLE,
    INSTITUTE_ID,
    TELEPHONE,
    FAX,
    URL,
    EMAIL from CERA2.PERSON;

-- 4.) PERSON_EXTERNAL_ID

select count(*) from upd_person_external_id;
select count(*) from cera2.person_external_id;

-- delete upd_person_external_id;

insert into upd_person_external_id (
  PERSON_EXTERNAL_ID,
  EXTERNAL_ID_URL,
  PERSON_ID,
  EXTERNAL_IDENTIFIER_TYPE_ID,
  EXTERNAL_ID,
  UPD_BY,
  UPD_DATE) select 
  PERSON_EXTERNAL_ID,
  EXTERNAL_ID_URL,
  PERSON_ID,
  EXTERNAL_IDENTIFIER_TYPE_ID,
  EXTERNAL_ID,
  NULL,
  NULL from CERA2.person_external_id;

-- 5.) CITATION

select count(*) from upd_citation;
select count(*) from cera2.citation;

-- ev. fehlen citations in UPD_CITATION (die über xmlload angelegte wurden):
select * from cera2.citation where citation_id not in (select citation_id from upd_citation);
--insert into upd_citation (citation_id, title, authors, publication, publisher, editor, publication_date, country, state, place, edition, access_spec, additional_info, presentation_id, citation_type_id) 
--     select * from cera2.citation where citation_id in (select citation_id from cera2.citation where citation_id not in (select citation_id from upd_citation));

-- Andersrum:
select citation_id, title, upd_by, upd_date from upd_citation where citation_id not in (select citation_id from cera2.citation);

insert into upd_citation (
  CITATION_ID,
  TITLE,
  AUTHORS,
  PUBLICATION,
  PUBLISHER,
  EDITOR,
  PUBLICATION_DATE,
  COUNTRY,
  STATE,
  PLACE,
  EDITION,
  ACCESS_SPEC,
  ADDITIONAL_INFO,
  PRESENTATION_ID,
  CITATION_TYPE_ID,
  UPD_BY,
  UPD_DATE) select 
  CITATION_ID,
  TITLE,
  AUTHORS,
  PUBLICATION,
  PUBLISHER,
  EDITOR,
  PUBLICATION_DATE,
  COUNTRY,
  STATE,
  PLACE,
  EDITION,
  ACCESS_SPEC,
  ADDITIONAL_INFO,
  PRESENTATION_ID,
  CITATION_TYPE_ID,
  NULL,
  NULL from cera2.citation;

-- table CERA2_UPD.REFERENCE
select * from cera2_upd.reference;
-- delete reference;                -- nach der Übernahme von Scholix Citations oder generieren durdh metaxa

-- 7.PROJECT

select count(*) from upd_project;
select count(*) from cera2.project;

select * from cera2.project where project_id not in (select project_id from upd_project);
select * from upd_project where project_id not in (select project_id from cera2.project);
--delete upd_project;

insert into upd_project (
  PROJECT_ID,
  PROJECT_ACRONYM,
  PROJECT_NAME,
  PROJECT_DESCR,
  UPD_BY,
  UPD_DATE) select 
  PROJECT_ID,
  PROJECT_ACRONYM,
  PROJECT_NAME,
  PROJECT_DESCR,
  NULL,
  NULL from cera2.project;

-- 8. CODELIST  (upd_codelists)
--    Parameterlisten (single, multi)

select * from upd_codelists where creation_date > '01-APR-2018';

-- 9. cera2_upd.upd_code

select count(*) from upd_code;
select count(*) from cera2.code;

--delete upd_code;

insert into upd_code (
  CODE_ID,
  CODE_TYPE,
  CODE_NUMBER,
  CODE_ACRONYM,
  TOPIC_NAME,
  UNIT_ACRONYM,
  UPD_BY,
  UPD_DATE)
  select c.code_id, c.code_type, c.code_number, c.code_acronym, t.topic_name, u.unit_acronym, NULL, NULL from 
    cera2.code c, cera2.unit u, cera2.topic t, cera2.code_reference cr where 
    c.unit_id = u.unit_id and 
    cr.code_id = c.code_id and 
    cr.topic_id = t.topic_id;

-- 10. cera2_upd.upd_topic

select * from upd_topic where upd_by is not NULL;

select count(*) from upd_topic;
select count(*) from cera2.topic;
--delete upd_topic;

insert into upd_topic ( 
  TOPIC_ID, TOPIC_NAME, TOPIC_ACRONYM, TOPIC_DESCR,
  TOPIC_POINTER, TOPIC_LEVEL, UPD_BY, UPD_DATE) select 
  topic_id, topic_name, topic_acronym, topic_descr, topic_pointer, topic_level, NULL, NULL from 
    cera2.topic ;

--
-- 11. Einträge in cera2_upd.key_connect und cera2_upd.contact (in metaxa erzeugt)
--     (nach Übernahme Einträge löschen, Tabelle leeren)

select * from upd_authorlists;       -- DOI-Autoren,...  geordnet

select * from upd_keylist;           -- User selected keywords ( + metaxa "suggested keywords")

select * from key_connect;            -- cera2_upd.key_connect zur Übernahme nach cera2.key_connect
--delete key_connect;                 -- 

select * from contact;               -- in "metaxa/admin" Löschen (nach Übernahme) noch einbauen
--delete contact;

MadWiki: CERA Documentation/CERA2_Metadata_Agent2 (last edited 2019-04-12 15:30:11 by HansHermannWinter)