Wenn man mit zwei Datenbanken parallel arbeiten möchte, stellt sich immer die Frage, wie geht das am Besten?
Wir wollen hier mal die REST Schnittstelle als Verbindung zwischen Oracle und Postgres verwenden.
Wir verwenden hier PostgREST
https://postgrest.org/en/v7.0.0/
Installationsverzeichnis aussuchen:
cd /var/lib/pgsql
Software herunterladen:
wget https://github.com/PostgREST/postgrest/releases/download/v7.0.1/postgrest-v7.0.1-linux-x64-static.tar.xz
Auspacken:
tar xJf postgrest-<version>-<platform>.tar.xz
REM Rest Service Dienst starten
./postgrest
Schema und Beispieltabelle anlegen:
CREATE SCHEMA muniq;
CREATE TABLE muniq.todos ( id serial primary key, done boolean not null default false, task text not null, due timestamptz );
insert into muniq.todos (task) values ('finish tutorial 0'), ('pat self on back');
Rolle anlegen
CREATE ROLE web_anon nologin;
Nur Leserechte auf Schema
grant usage on schema muniq to web_anon;
grant select on muniq.todos to web_anon;
Anmelde Benutzer anlegen:
CREATE ROLE authenticator NOINHERIT LOGIN PASSWORD 'muso2021muso2021';
GRANT web_anon TO authenticator;
vi tutorial.conf
db-uri = "postgres://authenticator:muso2021muso2021@localhost:5432/xe18c"
db-schema = "muniq"
db-anon-role = "web_anon"
Passwort eintragen:
echo jwt-secret ="\"`date | md5sum | head -c32`\"" >> tutorial.conf
Rest Dienst mit Konfig-Datei starten
./postgrest tutorial.conf
Mit Ihrem Passwort gehen Sie bitte in die Webseite und führen folgende Schritte aus
cat tutorial.conf | grep jwt-secret
https://jwt.io/#debugger-io
1. Header:
{ "alg": "HS256", "typ": "JWT" }
2. Payload:
{"role": "todo_user"}
3. PWD aus jwt-secret
4. Secret nicht anwählen
5. Token rauskopieren
Verwenden Sie ein zweite Session:
Lesetest:
curl http://localhost:3000/todos
Schreibtest:
export TOKEN="eyJhbGciOaJIUzI1NiIsInR5cCI6IkpXxCJ8.eyJyb2xlIjoidd9kb191c2Vycn0.Vl2f3BH_4iLc-PzY1SE74svS9mDvgdSkEFH7_2ReJsA"
curl http://localhost:3000/todos -X POST \ -H "Authorization: Bearer $TOKEN" \ -H "Content-Type: application/json" \ -d '{"id":4,"task": "mp"}'
Beispiele für Lesefilter:
curl http://localhost:3000/todos -X GET \ -H "Authorization: Bearer $TOKEN" \ -H "Content-Type: application/json"
Eine Zeile anzeigen
id=1 (equal 1)
curl http://localhost:3000/todos?id=eq.1
id <3 (lower Then <)
curl http://localhost:3000/todos?id=lt.3
id > 3 (greater then >)
curl http://localhost:3000/todos?id=gt.3
id >3 or id <2
curl "http://localhost:3000/todos?or=(id.gt.3,id.lt.2)"
Weitere Beispiele: https://postgrest.org/en/v7.0.0/api.html
Insert neue Zeile
curl http://localhost:3000/todos -X POST \
-H "Authorization: Bearer $TOKEN" \
-H "Content-Type: application/json" \
-d '{"id":4,"task": "mp"}'
Update (auf alle Zeilen)
curl http://localhost:3000/todos -X PATCH \
-H "Authorization: Bearer $TOKEN" \
-H "Content-Type: application/json" \ -
d '{"done": true}'
Update (auf eine Zeile (alle Spalten müssen angegeben werden!))
curl http://localhost:3000/todos?id=eq.3 -X PUT \
-H "Authorization: Bearer $TOKEN" \
-H "Content-Type: application/json" \
-d '{"id":3, "task":"Test","done": true}'
Delete (eine Zeile)
curl http://localhost:3000/todos?id=eq.4 -X DELETE \
-H "Authorization: Bearer $TOKEN" \
-H "Content-Type: application/json"
Sehen wir uns mal die Seite von Oracle aus an. Wie kann man die Postgres Schnittstelle ansprechen?
Dafür bietet sich das Package apex_web_service an, das installiert ist, wenn Sie auch APEX/ORDS installiert haben.
Wir schreiben uns ein kleines Package, das die Daten im JSON Format an die Postgres REST Schnittstelle übergibt:
CREATE OR REPLACE PACKAGE postgres_rest IS
FUNCTION get (
table_name IN VARCHAR2,
filter IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;
PROCEDURE write (
table_name IN VARCHAR2,
modus IN VARCHAR2,
filter IN VARCHAR2 DEFAULT NULL,
body IN CLOB DEFAULT NULL);
END;
/
und der dazugehörige Body. Bitte beachten Sie, dass der Bearer Schlüssel in Zeile 11 angepasst werden muss. Diese haben Sie im Schritt auf der Seite https://jwt.io/#debugger-io
(siehe oben) bekommen.
Wenn der Schlüssel nicht passt, bekommen Sie eine Fehlermeldung, dass Sie keine Rechte auf der Tabelle haben.
Passen Sie auch bitte die IP Adresse vom Ziel Server an (bei uns 172.30.30.8)
CREATE OR REPLACE PACKAGE BODY postgres_rest IS
vRestResult CLOB;
v_request VARCHAR2(32000);
PROCEDURE INIT
IS BEGIN
apex_web_service.g_request_headers.delete();
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/json';
apex_web_service.g_request_headers(2).name := 'Authorization';
apex_web_service.g_request_headers(2).value := 'Bearer eyJhbGciOiJIUzI1NiIsInR5cdI7IkpXVCJ9.eyJyb2xlIjoidG9kb101c2VyIn0.CUZ02WPyLV-QdVtokEZmdqDkWrOqSStlWbADj1Sz_uU';
END INIT;
-- ################################################################################################
FUNCTION get (table_name IN VARCHAR2, filter IN VARCHAR2 DEFAULT NULL ) RETURN CLOB
IS
BEGIN
init;
IF filter IS NOT NULL THEN
v_request:=table_name||'?'||filter;
ELSE
v_request:=table_name;
END IF;
vRestResult := apex_web_service.make_rest_request(
p_url => 'http://172.30.30.8:3000/'||v_request,
p_http_method => 'GET');
RETURN vRestResult;
END;
-- ################################################################################################
PROCEDURE write (
table_name IN VARCHAR2,
modus IN VARCHAR2,
filter IN VARCHAR2 DEFAULT NULL,
body IN CLOB DEFAULT NULL
)
IS
v_mode VARCHAR2(2001);
BEGIN
init;
IF substr(upper(modus),1,1)='I' THEN
v_mode:='POST';
ELSIF substr(upper(modus),1,1)='U' THEN
v_mode:='PUT';
ELSIF substr(upper(modus),1,1)='D' THEN
v_mode:='DELETE';
ELSE
RAISE_APPLICATION_ERROR(-20000,'Invalid Mode (I)nsert, (U)pdate, (D)elete');
END IF;
IF v_mode IN ('PUT','DELETE') THEN
v_request:=table_name||'?'||filter;
ELSE
v_request:=table_name;
END IF;
vRestResult := apex_web_service.make_rest_request(
p_url => 'http://172.30.30.8:3000/'||v_request,
p_body =>body,
p_http_method => v_mode);
--dbms_output.put_line('Request:'||v_request);
--dbms_output.put_line(substr(vRestResult,1,4000));
END write;
END;
/
Als Bonus-Track schreiben wir uns einen Trigger, der das obige Oracle Package nutzt und alle Änderungen auf Oracle Seite in der Tabelle in eine gleiche Tabelle auf Postgres synchon spiegelt.
Hinweis die Tabelle (mit Primary Key) muss in Postgres jedoch angelegt werden:
Hinweis: Auf Postgres Seite ausführen !
CREATE TABLE muniq.emp (
empno numeric CONSTRAINT PK_EMP PRIMARY KEY,
ename character varying(10),
job character varying(9),
mgr numeric,
hiredate date,
sal numeric(7,2),
comm numeric(7,2),
deptno numeric(2,0));
Hinweis: Auf Oracle Seite ausführen
CREATE OR REPLACE TRIGGER EMP_TRG
AFTER DELETE OR INSERT OR UPDATE ON EMP
FOR EACH ROW
DECLARE
v_tab_name VARCHAR2(2001):='emp';
v_tab_filter VARCHAR2(2001):='empno=eq.'||nvl(:new.empno,:old.empno);
v_body CLOB;
BEGIN
v_body:='{
"empno": "' ||:new.empno ||'"' ||
CASE WHEN :new.mgr IS NOT NULL THEN ',"ename":"' ||:new.ename ||'"' END ||
CASE WHEN :new.job IS NOT NULL THEN ',"job":"' ||:new.job ||'"' END ||
CASE WHEN :new.mgr IS NOT NULL THEN ',"mgr":"' ||:new.mgr ||'"' END ||
CASE WHEN :new.hiredate IS NOT NULL THEN ',"mgr":"' ||:new.hiredate ||'"' END ||
CASE WHEN :new.sal IS NOT NULL THEN ',"sal":"' ||:new.sal ||'"' END ||
CASE WHEN :new.comm IS NOT NULL THEN ',"comm":"' ||:new.comm ||'"' END ||
CASE WHEN :new.deptno IS NOT NULL THEN ',"deptno":"' ||:new.deptno ||'"' END ||
'}';
--dbms_output.put_line('Debug:'||v_body);
IF INSERTING THEN
postgres_rest.write(
table_name =>v_tab_name,
modus =>'I',
body =>v_body);
ELSIF UPDATING THEN
postgres_rest.write(
table_name =>v_tab_name,
modus =>'U',
body =>v_body,
filter=>v_tab_filter);
ELSIF DELETING THEN
postgres_rest.write(
table_name =>v_tab_name,
modus =>'D',
filter =>v_tab_filter);
END IF;
END;
/
Beispiele zum Packageaufruf:
select postgres_rest.get(table_name=>'todos') from dual;
EXEC postgres_rest.write(table_name=>'todos',modus=>'I',body=>'{"id":6, "task": "1234"}');
EXEC postgres_rest.write(table_name=>'todos',modus=>'U',filter=>'id=eq.6',body=>'{"id":6, "task": "12345"}');
EXEC postgres_rest.write(table_name=>'todos',modus=>'D',filter=>'id=eq.6');
Beispiele zum Trigger-Testen:
insert into emp (empno,ename) values (8008,'Marco');
update emp set
ename='Marco2' where empno=8008;
delete from emp where empno=8008;
Nun kann man Oracle und Postgres wunderbar miteinander verbinden. Weitere Tipps & Tricks erfahren Sie u.a. in unserm Oracle ORDS Kurs.