Kopie des alten Systems |
Dies ist eine alte Kopie des GenWiki und spiegelt den Stand vom 8. Mai 2022 wider. This is an old copy of the GenWiki and reflects the status as of May 8, 2022. Please visit us at wiki.genealogy.net |
GOV/Webservice/Oracle
aus GenWiki, dem genealogischen Lexikon zum Mitmachen.
< GOV | Webservice
Inhaltsverzeichnis |
Auslesen von GOV-Einträgen mit Oracle und dem GOV-Simple-WebService
Tabellen
Item-Tabelle
CREATE sequence seq_gov_data start WITH 1 increment BY 1 ORDER;
CREATE TABLE f_gov_data ( lfd_id number(10), item_id varchar2(40), item_data XMLTYPE, item_childs XMLTYPE, lfd_id_parent number(10), verarbeitet varchar2(1) );
CREATE UNIQUE INDEX pk_f_gov_data ON f_gov_data (lfd_id); ALTER TABLE f_gov_data ADD PRIMARY KEY (lfd_id) USING INDEX pk_f_gov_data;
Kind-Objekt-Tabelle
CREATE TABLE f_gov_child ( child_id number(10), child_item_id varchar2(40), parent_item_id varchar2(40), lfd_id_parent number(10), verarbeitet varchar2(1) );
CREATE UNIQUE INDEX pk_f_gov_child ON f_gov_child (child_id); ALTER TABLE f_gov_child ADD PRIMARY KEY (child_id) USING INDEX pk_f_gov_child;
CREATE sequence seq_gov_child start WITH 1 increment BY 1 ORDER;
Funktion zum Auslesen eines GOV-Eintrags
CREATE OR REPLACE FUNCTION GOV_GET_ITEMID (p_aktion IN CHAR, p_name IN CHAR) RETURN XMLTYPE AS l_envelope CLOB; l_xml XMLTYPE; l_result XMLTYPE; BEGIN l_envelope := '<?xml version="1.0" encoding="UTF-8"?> <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/"> <soap:Body> <' || p_aktion || ' xmlns="http://gov.genealogy.net/ws"> <itemId xmlns="">' || p_name || '</itemId> </' || p_aktion || '> </soap:Body> </soap:Envelope>'; -- Request abschicken und XML-Response holen l_xml := APEX_WEB_SERVICE.make_request( p_url => 'http://gov.genealogy.net/services/SimpleService', p_envelope => l_envelope ); RETURN l_xml; END; /
Prozedur zum Auslesen der Kind-Objekte
CREATE OR REPLACE PROCEDURE gov_get_child AS v_anz_saetze number(8) := 1; v_satznr number(10) := 0; v_lfd_id number (8) := 0; v_item_id varchar2(40):= ' '; CURSOR c_satz IS SELECT lfd_id, item_id FROM f_gov_data WHERE verarbeitet IS NULL AND item_data IS NULL ORDER BY lfd_id; BEGIN SELECT count(*) INTO v_anz_saetze FROM f_gov_data WHERE verarbeitet IS NULL AND item_data IS NULL; OPEN c_satz; LOOP FETCH c_satz INTO v_lfd_id , v_item_id; v_satznr := v_satznr + 1; EXIT WHEN v_satznr > v_anz_saetze; merge INTO f_gov_data f USING ( SELECT lfd_id, item_id, gov_get_itemId ('getObject',item_id) AS item_data, gov_get_itemId ('getChildObjects',item_id) AS item_childs FROM f_gov_data WHERE lfd_id = v_lfd_id ) s ON (f.lfd_id = s.lfd_id) when matched then UPDATE SET f.item_data = s.item_data, f.item_childs = s.item_childs ; commit; END LOOP; CLOSE c_satz; END; /
Prinzip des Auslesens
erster Eintrag (Wurzel)
INSERT INTO f_gov_data (lfd_id, item_id, item_data, item_childs ) VALUES (seq_gov_data.NEXTVAL, 'objekt_123', gov_get_itemId ('getObject','objekt_123'), gov_get_itemId ('getChildObjects','objekt_123') );
Kinder der neuen Sätze ermitteln
INSERT INTO f_gov_child (child_id, child_item_id, parent_item_id, lfd_id_parent) SELECT seq_gov_child.NEXTVAL AS child_id, item AS child_item_id, item_id AS parent_item_id, lfd_id AS lfd_id_parent FROM ( SELECT DISTINCT lfd_id, t.item_id, y.item FROM f_gov_data t, XMLTable (XMLNamespaces('http://gov.genealogy.net/ws' AS "ns3"), '//out' passing item_childs COLUMNS item XMLType path 'ns3:item' ) x, XMLTable (XMLNamespaces('http://gov.genealogy.net/ws' AS "ns3"), 'ns3:item' passing x.item COLUMNS item varchar2(39) path '.' ) y WHERE verarbeitet IS NULL AND item_data IS NOT NULL );
Und wieder den ersten Schritt ausführen - mit den Daten aus Schritt zwei.
Parsen der Ergebnisse
letzte Änderung und Koordinaten
SELECT item_id AS govid, to_date(substr(last_modification_c,1,10) || ' ' || substr(last_modification_c,12,8),'yyyy-mm-dd hh24:mi:ss') AS last_modification, id, artkoord, CASE WHEN (breite IS NOT NULL) then cast(REPLACE(breite,'.',',') AS number(18,14)) else NULL end AS breite, CASE WHEN (laenge IS NOT NULL) then cast(REPLACE(laenge,'.',',') AS number(18,14)) else NULL end AS laenge, to_number(hoehe) AS hoehe, count (*) over (partition BY item_id ORDER BY rownum) AS lfd_id FROM f_gov_data t, XMLTable (XMLNamespaces('http://gov.genealogy.net/ws' AS "ns3", 'http://gov.genealogy.net/data' AS "ns2" ), '//out' passing t.item_data COLUMNS last_modification_c varchar2(30 char) path '@last-modification', id varchar2(30 char) path '@id', breite varchar2(100) path 'ns2:position/@lat', laenge varchar2(100) path 'ns2:position/@lon', artkoord varchar2(1) path 'ns2:position/@type', hoehe varchar2(10) path 'ns2:position/@height' ) y
Name mit Datum
SELECT govid, lfd_id, c_n, x_q, case -- nur eine Jahreszahl when ((c_y IS NOT NULL) ) then c_y -- nur Jahr für Beginn und Ende when ((c_by IS NOT NULL) AND (c_ey IS NOT NULL)) then c_by || ' - ' || c_ey -- nur Jahr für Beginn when ((c_by IS NOT NULL) ) then c_by || ' - ' -- nur Jahr für Ende when ((c_ey IS NOT NULL) ) then '- ' || c_ey -- Datum für Beginn und Ende mit Formatierung (Trunkierung) when ((c_b IS NOT NULL) AND (c_e IS NOT NULL)) then to_char(to_date(c_b, 'J'), f_bp) || ' - ' || to_char(to_date(c_e, 'J'), f_ep) -- Datum für Beginn when ((c_b IS NOT NULL) ) then to_char(to_date(c_b, 'J'), f_bp) || ' - ' -- Datum für Ende when ((c_e IS NOT NULL) ) then ' - ' || to_char(to_date(c_e, 'J'), f_ep) end AS c_z, case when (c_y IS NOT NULL) then to_date(c_y || '-01-01','YYYY-MM-DD') when (c_by IS NOT NULL) then to_date(c_by || '-01-01','YYYY-MM-DD') when (c_b IS NOT NULL) then to_date(c_b, 'J') end AS d_b, case when (c_y IS NOT NULL) then to_date(c_y || '-01-01','YYYY-MM-DD') when (c_ey IS NOT NULL) then to_date(c_ey || '-01-01','YYYY-MM-DD') when (c_e IS NOT NULL) then to_date(c_e, 'J') end AS d_b FROM ( SELECT item_id AS govid, c_n, c_by, c_ey, c_y, c_b, -- Formatierungsmaske case when (c_bp = '0') then 'YYYY' when (c_bp = '1') then 'YYYY-MM' when (c_bp = '2') then 'YYYY-MM-DD' else NULL end AS f_bp, c_bp, to_date(c_e, 'J') AS d_e, c_e, case when (c_ep = '0') then 'YYYY' when (c_ep = '1') then 'YYYY-MM' when (c_ep = '2') then 'YYYY-MM-DD' else NULL end AS f_ep, c_ep, c_q AS x_q, -- XML im Text umwandeln für Trace -- y.c_q.getStringVal(), count (*) over (partition BY item_id ORDER BY rownum) AS lfd_id FROM f_gov_data t, XMLTable (XMLNamespaces('http://gov.genealogy.net/data' AS "ns2"), '//out/ns2:name' passing t.item_data COLUMNS c_n varchar2(100 char) path '@value', c_by varchar2(4 char) path '@begin-year', c_ey varchar2(4 char) path '@end-year', c_y varchar2(4 char) path '@year', c_b varchar2(8 char) path 'ns2:timespan/ns2:begin/@jd', c_bp varchar2(1) path 'ns2:timespan/ns2:begin/@precision', c_e varchar2(8 char) path 'ns2:timespan/ns2:end/@jd', c_ep varchar2(1) path 'ns2:timespan/ns2:end/@precision', c_q xmltype path '.' ) y )