Dienstag, 29. September 2015

Dateien in Tabellen speichern: Komplette REST-API mit ORDS

Heute geht es doch nochmals um ORDS - das angekündigte Posting zu Node.js muss noch etwas warten. Ich möchte heute zeigen, wie Ihr mit der Oracle-Datenbank und ORDS einen REST-Service aufsetzt, der Dateien in einer Tabelle speichert und aus dieser abrufen kann. PUT und POST Requests sollen Dateien (Bilder, Dokumente) in eine Tabelle laden, GET-Requests sollen sie abrufen; DELETE logischerweise löschen. Dazu soll genau ein REST-Modul entstehen, welches diese vier Fälle (und noch einen mehr) komplett abdeckt.
Im letzten Blog-Posting wurde ja das neue rest-Kommando im neuen SQL Developer Command Line Interface (sqlcl vorgestellt - die Ausgabe dieses Tools ist die Vorlage für das Erzeugen unseres neuen REST-Service, denn ich möchte euch gerne die Skripte zeigen; die lassen sich doch wesentlich leichter nachvollziehen als das Klicken in einem Werkzeug. Wir werden in diesem Posting viel mit dem (derzeit undokumentierten) PL/SQL-Paket ORDS_SERVICES arbeiten.
Dieses Beispiel lässt sich übrigens auch ganz wunderbar im Oracle Database Cloud Service nachvollziehen; das ist sicherlich einer der einfachsten und schnellsten Wege, an einen laufenden Datenbank-Service im Internet zu kommen.
Zuerst braucht es, wie immer in einer Datenbank, eine Tabelle. Die legen wir auch genz einfach an. Dieses Blog-Posting geht davon aus, dass euer Datenbankschema DOCUMENTS heißt - passt die Skripte bei Bedarf einfach an.
create table my_documents(
  id           number(10)   generated always as identity
 ,mimetype     varchar2(200) 
 ,content      blob
 ,created      date         default sysdate not null
 ,last_updated date         default sysdate not null
 ,constraint  pk_mydocuments primary key (id)
)
/
Danach geht es an die Erstellung der REST-Services. Der erste Service soll zum Hochladen von Dokumenten dienen. Ihr könnt die REST-Services mit dem SQL Developer anlegen, in diesem Blog-Posting zeige ich aber den Skriptbasierten Ansatz; Ihr könnt also in eurem SQL-Tool mit Copy & Paste arbeiten. Zuerst sollte das Datenbankschema für REST-Zugriffe freigegeben werden (ORDS.ENABLE_SCHEMA). Führt diesen Call nicht als DBA; sondern als User DOCUMENTS aus. Ich gehe davon aus, dass euer ORDS bereits läuft - wenn nicht, könnt Ihr hier Details zum initiale Setup nachlesen.
BEGIN
  ORDS.ENABLE_SCHEMA(
    p_enabled             => TRUE,
    p_schema              => 'DOCUMENTS',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'documents',
    p_auto_rest_auth      => FALSE
  );
END;
/

COMMIT
/
Als nächstes erzeugen wir den REST-Service. Das folgende Skript löscht zuerst ein etwaiges bereits definiertes REST-Modul docmodule; danach wird es neu angelegt. Ein Module ist als "Container" für mehrere zusammengehörige REST-Dienste zu verstehen. Direkt danach kommt die Definition der URL-Templates. Einem URL-Template können dann mit ORDS_SERVICES.ADD_HANDLER die REST-Dienste zugeordnet werden - und zwar genau ein Dienst pro HTTP-Methode (GET, PUT, POST, DELETE und andere).
declare
  l_moduleid   number;
  l_templateid number;
  l_handlerid  number;
begin
  -- evtl. vorhandenes REST-Modul löschen
  ords_services.delete_module('docmodule');

  -- REST Modul anlegen
  l_moduleid := ords_services.create_module(
    p_name       => 'docmodule'
   ,p_uri_prefix => 'doc/'
   ,p_status     => 'PUBLISHED'
  );

  -- 1. URL-Template
  l_templateid := ords_services.add_template(
    p_module_id    => l_moduleid
   ,p_uri_template => 'documents/'
  );

  -- POST-Handler für das URL Template. Der REQUEST führt
  -- PL/SQL-Code aus und speichert die per Request hochgeladene Datei (:body) in die
  -- Tabelle ab.
  l_handlerid := ords_services.add_handler(
    p_template_id  => l_templateid
   ,p_source_type  => ords.source_type_plsql
   ,p_source       => 'declare
                         l_id my_documents.id%type;
                       begin 
                        insert into my_documents(
                          mimetype, content
                        ) values (
                          :content_type, :body
                        ) returning id into l_id; 
                        owa_util.mime_header(''application/json'', true);
                        htp.p(''{"status": "created", "id": ''||l_id||''}'');
                       end;'
   ,p_method       => 'POST'
  );
end;
/

commit
/
Der entscheidende Code befindet sich im Aufruf von ORDS_SERVICES.ADD_HANDLER. Hier wird als Argument für P_SOURCE ein PL/SQL-Block übergeben. Der macht im Grunde nichts weiter als ein SQL INSERT in die oben erzeugte Tabelle MY_DOCUMENTS. Wenn Ihr hier PL/SQL-Code schreibt, stellt ORDS euch auch einige Umgebungsvariablen bereit: Zwei seht ihr hier: :body enthält den konkreten Request Body des POST-Requests und :content_type enthält den Mime-Type, den der REST-Client im HTTP-Header Content-Type mitgeschickt hat. Es gibt aber noch ein paar mehr - dieses Blog-Posting von Jon Dixon listet sie auf. Wenn der INSERT erfolgt ist, wird die neu erzeugte ID des Dokument in einem JSON-Format zurückgegeben. Probiert den neuen REST-Service mal aus - mit dem Chrome Advanced REST Client sieht das ganze so aus ...
... und mit dem Kommandozeilentool curl so (hier sind noch einige Statusmeldungen dabei). Ich persönlich habe eine Vorliebe für curl zum Testen von REST-Services entwickelt - damit ist man einfach nicht mehr den "Launen" der verschiedenen GUI-Werkzeuge ausgeliefert (und irgendeine Marotte haben sie alle).
D:\>curl -v -X POST "http://localhost:8081/ords/documents/doc/documents/" --data-binary "@ar.png" -H "Content-Type:image/png"
*   Trying 127.0.0.1...
* Connected to localhost (127.0.0.1) port 8081 (#0)
> POST /ords/documents/doc/documents/ HTTP/1.1
> Host: localhost:8081
> User-Agent: curl/7.44.0
> Accept: */*
> Content-Type:image/png
> Content-Length: 46098
> Expect: 100-continue
>
< HTTP/1.1 100 Continue
* We are completely uploaded and fine
< HTTP/1.1 200 OK
< Content-Type: application/json; charset=UTF-8
< Transfer-Encoding: chunked
<
{"status": "created", "id": 2}
* Connection #0 to host localhost left intact
Schauen wir uns die Daten in der Tabelle an - das geschieht im SQL-Client.
SQL> select * from my_documents;

  ID MIMETYPE   CONTENT                        CREATED             LAST_UPDATED
---- ---------- ------------------------------ ------------------- -------------------
   3 image/png  89504E470D0A1A0A0000000D494844 29.09.2015 11:01:22 29.09.2015 11:01:22
                52000003850000021B0802000000D3
                A9AC70000000097048597300000EC4
                00000EC401952B0E1B000020004944
                4154789CEC9D796055C5D9FF9F67CE
                0D9B408424

   4 image/png  89504E471A01DFDA562FD82A0151AA 29.09.2015 11:01:58 29.09.2015 11:01:58
                C43D8A8822EE0BB1EFFB6B53B1AE68
                5B3535EDAB2501AD5A2D4A6D956A5B
                631537625D63DFAA80D1BEF5B56025
                B26917D942EE39F3FCFE983973E62C
                F7DE736F2E
Das ist doch ein sehr einfacher und angenehmer Weg, Dateien in eine Tabelle zu laden, oder? Und damit ist der erste REST-Service (Upload) auch schon fertig. Die nächsten drei Services liegen nahe. Für das URL-Pattern /doc/documents/{id} soll das Dokument mit der jeweiligen ID ...
  • mit der Methode GET abgerufen
  • mit der Methode PUT aktualisiert
  • mit der Methode DELETE gelöscht
... werden. Das Skript dafür sieht so aus (damit Ihr einfaches Copy & Paste machen könnt, hier nochmals das vollständige Skript. Der bereits vorhandene erste Teil ist ausgegraut.
declare
  l_moduleid   number;
  l_templateid number;
  l_handlerid  number;
begin
  -- evtl. vorhandenes REST-Modul löschen
  ords_services.delete_module('docmodule');

  -- REST Modul anlegen
  l_moduleid := ords_services.create_module(
    p_name       => 'docmodule'
   ,p_uri_prefix => 'doc/'
   ,p_status     => 'PUBLISHED'
  );

  -- 1. URL-Template
  l_templateid := ords_services.add_template(
    p_module_id    => l_moduleid
   ,p_uri_template => 'documents/'
  );

  -- POST-Handler für das URL Template. Der REQUEST führt
  -- PL/SQL-Code aus und speichert die per Request hochgeladene Datei (:body) in die
  -- Tabelle ab.
  l_handlerid := ords_services.add_handler(
    p_template_id  => l_templateid
   ,p_source_type  => ords.source_type_plsql
   ,p_source       => 'declare
                         l_id my_documents.id%type;
                       begin 
                        insert into my_documents(
                          mimetype, content
                        ) values (
                          :content_type, :body
                        ) returning id into l_id; 
                        owa_util.mime_header(''application/json'', true);
                        htp.p(''{"status": "created", "id": ''||l_id||''}'');
                       end;'
   ,p_method       => 'POST'
  );

  -- 2. URL-Template
  l_templateid := ords_services.add_template(
    p_module_id    => l_moduleid
   ,p_uri_template => 'documents/{id}'
  );

  -- GET-Handler für das URL Template. Das angefragte Dokument ({id}) wird
  -- per SQL Query abgerufen und ausgegeben. Dazu bietet ORDS einen fertigen
  -- "Source Type" MEDIA QUERY an.
  l_handlerid := ords_services.add_handler(
    p_template_id  => l_templateid
   ,p_source_type  => ords.source_type_media
   ,p_source       => 'select mimetype, content from my_documents where id = :id'
   ,p_method       => 'GET'
  );

  -- DELETE-Handler für das URL Template. Es wird (per PL/SQL-Block) eine 
  -- SQL DELETE-Anweisung ausgeführt.
  l_handlerid := ords_services.add_handler(
    p_template_id  => l_templateid
   ,p_source_type  => ords.source_type_plsql
   ,p_source       => 'begin 
                        delete from my_documents where id = :id; 
                        owa_util.mime_header(''application/json'', true);
                        htp.p(''{"status": "deleted"}'');
                       end;'
   ,p_method       => 'DELETE'
  );

  -- PUT-Handler für das URL Template. Es wird (per PL/SQL-Block) eine 
  -- SQL UPDATE-Anweisung ausgeführt.
  l_handlerid := ords_services.add_handler(
    p_template_id  => l_templateid
   ,p_source_type  => ords.source_type_plsql
   ,p_source       => 'begin 
                        update my_documents set content = :body, mimetype = :content_type, last_updated = sysdate where id = :id; 
                        owa_util.mime_header(''application/json'', true);
                        htp.p(''{"status": "updated", "id": ''||:id||''}'');
                       end;'
   ,p_method       => 'PUT'
  );
end;
/

commit
/
Testen wir die neuen REST-Dienste.
  • Beginnen wir mit GET (das Dokument soll in die Datei file.bin) gespeichert werden:
    D:\>curl -v -X GET http://localhost:8081/ords/documents/doc/documents/3 -o file.bin
      % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                     Dload  Upload   Total   Spent    Left  Speed
      0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0*   Trying 127.0.0.1...
    * Connected to localhost (127.0.0.1) port 8081 (#0)
    > GET /ords/documents/doc/documents/5 HTTP/1.1
    > Host: localhost:8081
    > User-Agent: curl/7.44.0
    > Accept: */*
    >
    < HTTP/1.1 200 OK
    < Content-Type: image/png
    < ETag: "ZuV0opwWReMeFm8NwmK8IPxuZfldcE+uB7n95A2Bwg6VE9z89b31m2VMNiKGWqTeD+IZ41hpPHAcZppbuOw/yA=="
    < Transfer-Encoding: chunked
    <
    { [16214 bytes data]
    100 46098    0 46098    0     0   261k      0 --:--:-- --:--:-- --:--:--  288k
    * Connection #0 to host localhost left intact
    
    An der Ausgabe (Content-Type) könnte der Client auch erkennen, was da konkret vom REST-Service ausgeliefert wird. In diesem Fall wäre es ein Bild (image/png).
  • Dann löschen wir ein Dokument ...
    D:\>curl -v -X DELETE http://localhost:8081/ords/documents/doc/documents/3
    *   Trying 127.0.0.1...
    * Connected to localhost (127.0.0.1) port 8081 (#0)
    > DELETE /ords/documents/doc/documents/3 HTTP/1.1
    > Host: localhost:8081
    > User-Agent: curl/7.44.0
    > Accept: */*
    >
    < HTTP/1.1 200 OK
    < Content-Type: application/json
    < Transfer-Encoding: chunked
    <
    {"status": "deleted"}
    * Connection #0 to host localhost left intact
    
  • Und schließlich noch ein Update ...
    D:\>curl -v -X PUT "http://localhost:8081/ords/documents/doc/documents/4" --data-binary "@index.html" -H "Content-Type:text/html"
    *   Trying 127.0.0.1...
    * Connected to localhost (127.0.0.1) port 8081 (#0)
    > PUT /ords/documents/doc/documents/4 HTTP/1.1
    > Host: localhost:8081
    > User-Agent: curl/7.44.0
    > Accept: */*
    > Content-Type:text/html
    > Content-Length: 381
    >
    * upload completely sent off: 381 out of 381 bytes
    < HTTP/1.1 200 OK
    < Content-Type: text/html; charset=UTF-8
    < Transfer-Encoding: chunked
    <
    {"status": "updated", "id": 4}
    * Connection #0 to host localhost left intact
    
Fehlt zum Abschluß nur noch eine Übersicht über die bereits vorhandenen Dokumente - hierzu bietet sich wiederum ein GET-Request auf die Collection selbst an - also das URL-Pattern /doc/documents/. Wenn eine ID angehägt wird, soll das konkrete Dokument ausgeliefert werden (das ist bereits fertig), wenn keine ID angehängt wird, soll eine Übersicht - im JSON-Format mit Links zu den einzelnen Dokumenten - ausgegeben werden. Hier wiederum das komplette Skript - die Neuerungen sind farblich hervorgehoben.
declare
  l_moduleid   number;
  l_templateid number;
  l_handlerid  number;
begin
  -- evtl. vorhandenes REST-Modul löschen
  ords_services.delete_module('docmodule');

  -- REST Modul anlegen
  l_moduleid := ords_services.create_module(
    p_name       => 'docmodule'
   ,p_uri_prefix => 'doc/'
   ,p_status     => 'PUBLISHED'
  );

  -- 1. URL-Template
  l_templateid := ords_services.add_template(
    p_module_id    => l_moduleid
   ,p_uri_template => 'documents/'
  );

  -- POST-Handler für das URL Template. Der REQUEST führt
  -- PL/SQL-Code aus und speichert die per Request hochgeladene Datei (:body) in die
  -- Tabelle ab.
  l_handlerid := ords_services.add_handler(
    p_template_id  => l_templateid
   ,p_source_type  => ords.source_type_plsql
   ,p_source       => 'declare
                         l_id my_documents.id%type;
                       begin 
                        insert into my_documents(
                          mimetype, content
                        ) values (
                          :content_type, :body
                        ) returning id into l_id; 
                        owa_util.mime_header(''application/json'', true);
                        htp.p(''{"status": "created", "id": ''||l_id||''}'');
                       end;'
   ,p_method       => 'POST'
  );

  -- GET-Handler für das URL Template "documents/" (ohne ID). Es wird (Source Type "feed")
  -- eine Übersicht mit allen Dokumenten ausgegeben.
  l_handlerid := ords_services.add_handler(
    p_template_id  => l_templateid
   ,p_source_type  => ords.source_type_feed
   ,p_source       => 'select 
                         id, 
                         mimetype, 
                         dbms_lob.getlength(content) as doc_size, 
                         created, 
                         last_updated 
                       from my_documents'
   ,p_method       => 'GET'
  );

  -- 2. URL-Template
  l_templateid := ords_services.add_template(
    p_module_id    => l_moduleid
   ,p_uri_template => 'documents/{id}'
  );

  -- GET-Handler für das URL Template. Das angefragte Dokument ({id}) wird
  -- per SQL Query abgerufen und ausgegeben. Dazu bietet ORDS einen fertigen
  -- "Source Type" MEDIA QUERY an.
  l_handlerid := ords_services.add_handler(
    p_template_id  => l_templateid
   ,p_source_type  => ords.source_type_media
   ,p_source       => 'select mimetype, content from my_documents where id = :id'
   ,p_method       => 'GET'
  );

  -- DELETE-Handler für das URL Template. Es wird (per PL/SQL-Block) eine 
  -- SQL DELETE-Anweisung ausgeführt.
  l_handlerid := ords_services.add_handler(
    p_template_id  => l_templateid
   ,p_source_type  => ords.source_type_plsql
   ,p_source       => 'begin 
                        delete from my_documents where id = :id; 
                        owa_util.mime_header(''application/json'', true);
                        htp.p(''{"status": "deleted"}'');
                       end;'
   ,p_method       => 'DELETE'
  );

  -- PUT-Handler für das URL Template. Es wird (per PL/SQL-Block) eine 
  -- SQL UPDATE-Anweisung ausgeführt.
  l_handlerid := ords_services.add_handler(
    p_template_id  => l_templateid
   ,p_source_type  => ords.source_type_plsql
   ,p_source       => 'begin 
                        update my_documents set content = :body, mimetype = :content_type, last_updated = sysdate where id = :id; 
                        owa_util.mime_header(''application/json'', true);
                        htp.p(''{"status": "updated", "id": ''||:id||''}'');
                       end;'
   ,p_method       => 'PUT'
  );
end;
/

commit
/
Probieren wir auch diesen Service aus ... erst Mal mit curl ...
D:\>curl -X GET -v http://localhost:8081/ords/documents/doc/documents/
*   Trying 127.0.0.1...
* Connected to localhost (127.0.0.1) port 8081 (#0)
> GET /ords/documents/doc/documents/ HTTP/1.1
> Host: localhost:8081
> User-Agent: curl/7.44.0
> Accept: */*
>
< HTTP/1.1 200 OK
< Content-Type: application/json
< ETag: "Fr7HbBdEKCjVbKL+qMgCZu5WTPRr7dRMvrsdVzUskvYWkqM/OCEVrCeqYEbwUZ7Ki4u3deYNXSxIVJhIidX5Uw=="
< Transfer-Encoding: chunked
<
{"items":[{"uri":{"$ref":"http://localhost:8081/ords/documents/doc/documents/3"},"id":3,"mimetype":"image/png","doc_size
":46098,"created":"2015-09-29T09:01:22Z","last_updated":"2015-09-29T09:01:22Z"},{"uri":{"$ref":"http://localhost:8081/or
ds/documents/doc/documents/4"},"id":4,"mimetype":"text/html","doc_size":381,"created":"2015-09-29T09:01:58Z","last_updat
ed":"2015-09-29T09:01:58Z"}],"first":{"$ref":"http://localhost:8081/ords/documents/doc/documents/"}}
* Connection #0 to host localhost left intact
Dieser einfache GET-Request kann auch im Browser getestet werden ...
Auf diese Art und Weise könnt Ihr nun noch weitere Dienste hinzufügen und so einen komplette REST-Schnittstelle für die Tabelle schaffen - in der Praxis braucht es sicherlich noch ein paar Tabellen mehr. Die REST-Dienste können dann von beliebigen Anwendungen genutzt werden; ob Node.js, PHP, Java oder .NET: Alle greifen über die einheitliche REST-Schnittstelle zu; der konkrete Datenbanktreiber wird bedeutungslos und die Tabellenzugriffe werden so einfach wie noch nie. Viel Spaß damit.

Kommentare:

  1. I'm always stunned what people do with REST. Have you tried integrating this with a HTML file input item? danke

    AntwortenLöschen
  2. Hi Scott,

    not exactly - I once did the reverse - doing a REST Request for a "service" accepting a "classic" HTML Form. So I had to construct the request body (multipart/form-data) with PL/SQL. Integrating ORDS with an HTML form would mean the other way around - i.e. parsing the "multipart/form-data" request body and extract the individual form elements (incl. file uploads).

    Interesting, though ... would be something for another blog posting ;-)

    Best regards from Munich

    -Carsten

    AntwortenLöschen