Oracle11g: FTP- und WebDAV Uploads in eigene Tabellen

Vor einiger Zeit sind in der Community zwei Tipps zum Umgang mit dem virtuellen Dateisystem (XML DB Repository) in der Oracle-Datenbank erschienen:

Darin können Sie lesen, wie Sie das ab Oracle9i Release 2 verfügbare XML DB Repository nutzen, FTP und WebDAV aktivieren und die Inhalte mit einer Application Express-Anwendung bearbeiten können.

Bis einschließlich Oracle10g Release 2 werden alle in das XML DB Repository geladenen Dateien in einer systemseitig vorgegebenen Tabelle abgespeichert (für die Experten: XDB$RESOURCE im Schema XDB). Ab Oracle11g ist es jedoch möglich, per FTP oder WebDAV hochgeladene Dateien auch in eigene Tabellen umzuleiten - und wie das geht, lesen Sie in diesem Tipp.

Dieser Tipp ist nur in einer Oracle11g-Umgebung (mindestens Version 11.1.0.6) lauffähig.

Als Ausgangspunkt werden wir die Anwendung zur "Bildbearbeitung mit Application Express nehmen". Diese war ebenfalls Gegenstand eines Community-Tipps. Damit Sie diesen nun jedoch nicht komplett nacharbeiten müssen, können Sie die Anwendung als hier als Packaged Application herunterladen. Packen Sie das ZIP-Archiv einfach aus und importieren Sie die Anwendung ganz normal (Zeichensatz UTF-8).

Noch ein Performance-Tipp: Die Bildverarbeitung wird unter Oracle11g rasend schnell, wenn Sie sicherstellen, dass der Datenbankparamater java_jit_enabled auf true steht.

Abbildung 1: Importieren der Bildbearbeitungs-Anwendung

Laden Sie zum Test der Anwendung einige Bilder hoch - dann sollte die Seite wie in Abbildung 2 aussehen.

Abbildung 2: Anwendungsseite nach dem Hochladen einiger Bilder

Im folgenden wird die Datenbank so konfiguriert, dass Sie per FTP Bilder hochladen können und diese automatisch in die Tabelle INTERMEDIA_IMAGE gespeichert werden. Sie werden dann über drei Zugänge zu dieser Tabelle verfügen: Die APEX-Applikation, FTP und WebDAV bzw. HTTP.

XML DB Protokollserver einrichten

Führen Sie als erstes die im Tipp Ordner und Dateien: Das virtuelle Dateisystem der Datenbank nutzen unter Vorbereitungen aufgeführten Schritte aus, um die FTP- und WebDAV Protokollserver der Datenbank zu aktivieren. Wenn Sie nun per FTP oder WebDAV Dateien hochladen, werden diese (noch) in die oben genannte Standardtabelle für das XML DB Repository abgelegt. das Standardverhalten.

Zur Konfiguration werden einige PL/SQL Skripte benötigt - diese finden Sie im der Datei ftp-upload-eigene-tabellen.zip. Laden Sie das Archiv herunter und packen Sie es in ein Arbeitsverzeichnis Ihrer Wahl aus. Die Skripte sind zum Einspielen mit SQL*Plus vorgesehen; die Skripte im Unterverzeichnis dba müssen mit DBA-Privilegien (mindestens die Rolle XDBADMIN) eingespielt werden; für die Skripte im Unterverzeichnis apex-parsing-schema müssen Sie als Parsing Schema Ihrer APEX-Applikation angemeldet sein.

Da Sie in weiteren Verlauf wahrscheinlich recht viel mit FTP arbeiten werden, ist es sinnvoll, den standardmäßigen " Timeout" von einer Minute heraufzusetzen. Lassen Sie dazu (einmalig) das Skript dba/00-set-ftp-session-timeout.sql laufen. Geben Sie einen Timeout von 10 bis 15 Minuten an - Sie müssen sich dann nicht ständig neu mit dem FTP-Server verbinden.

Einrichten eines Ordners im virtuellen Dateisystem

Zunächst benötigen Sie einen speziellen Ordner für die hochzuladenden Bilder im XML DB Repository; alles, was in diesen Ordner hochgeladen wird, soll in unsere Tabelle INTERMEDIA_IMAGE gespeichert werden. Führen Sie das folgende SQL-Skript (wie alle) mit SQL*Plus aus - im ZIP Archiv ist es in die Datei dba/01-create-folders.sql. Zu Beginn werden Sie zur Eingabe des Parsing Schemas Ihrer APEX-Anwendung aufgefordert.

Es legt einen neuen Datenbankuser UPLOAD (mit Passwort "upload") (nur mit dem CREATE SESSION-Privileg) an, diesen User können Sie dann für die FTP- und WebDAV Verbindungen nutzen.
Es erzeugt das Verzeichnis /apex-images und legt das Parsing Schema Ihrer APEX-Anwendung (Sie werden zur wiederum zur Eingabe desselben aufgefordert) als Eigentümer fest. Darunter werden zwei Verzeichnisse erzeugt: config speichert Konfigurationsdateien und upload ist das eigentliche Verzeichnis zum Hochladen.

create user UPLOAD identified by upload
/

grant create session to upload
/

declare
  b boolean := false;
begin
  b := DBMS_XDB.createFolder(
   abspath => '/apex-images'
  );
  b := DBMS_XDB.createFolder(
   abspath => '/apex-images/upload'
  );
  b := DBMS_XDB.createFolder(
   abspath => '/apex-images/config'
  );
end;
/
sho err

update resource_view
set res = updatexml(res, '/Resource/Owner/text()', '[Hier das APEX Parsing Schema einsetzen]')
where under_path(res, '/apex-images') = 1
/

In einem FTP-Werkzeug sieht die Struktur dann in etwa wie in Abbildung 3 aus.

Abbildung 3: Grafische Ansicht der erzeugten Ordnerstruktur

Als nächstes müssen Zugriffsrechte gesetzt werden. Die Verzeichnisse sollen für jedermann lesbar und für den UPLOAD-User und das Parsing Schema les- und schreibbar sein. Im XML DB Repository muss dazu zunächst eine Access Control List (ACL) erzeugt und diese dann den Verzeichnissen zugewiesen werden. Die folgenden SQL Anweisungen erledigen dies (im ZIP-Archiv dba/02-set-acl.sql).

declare
  b boolean := false;
begin
  b := dbms_xdb.createresource(
    abspath => '/sys/acls/apex-upload-acl.xml',
    data    => '<acl description="ACL APEX Upload-Anwendung" '||
               '     xmlns="http://xmlns.oracle.com/xdb/acl.xsd" ' ||
               '     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" '||
               '     xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd http://xmlns.oracle.com/xdb/acl.xsd" shared="true">' ||
               ' <ace>' ||
               '   <grant>true</grant>' ||
               '   <principal>[Hier das APEX Parsing Schema einsetzen]</principal>' ||
               '   <privilege>' ||
               '     <all/>' ||
               '   </privilege>' ||
               ' </ace>' ||
               ' <ace>' ||
               '   <grant>true</grant>' ||
               '   <principal>UPLOAD</principal>' ||
               '   <privilege>' ||
               '     <all/>' ||
               '   </privilege>' ||
               ' </ace>' ||
               ' <ace>' ||
               '   <grant>true</grant>' ||
               '   <principal>PUBLIC</principal>' ||
               '   <privilege>' ||
               '     <read-properties/>' ||
               '     <read-contents/>' ||
               '     <read-acl/>' ||
               '     <resolve/>' ||
               '   </privilege>' ||
               ' </ace>' ||
               '</acl>'
  );
end;
/
sho err

begin
  dbms_xdb.setacl(
    res_path => '/apex-images',
    acl_path => '/sys/acls/bootstrap_acl.xml'
  );
  dbms_xdb.setacl(
    res_path => '/apex-images/config',
    acl_path => '/sys/acls/bootstrap_acl.xml'
  );
  dbms_xdb.setacl(
    res_path => '/apex-images/upload',
    acl_path => '/sys/acls/apex-upload-acl.xml'
  );
end;
/
sho err

Einrichten des XML DB Event-Handlers

Als nächstes wird auf dem Order /apex-images/upload ein XML DB Event Handler eingerichtet. Sobald eine Datei hochgeladen, verändert, gelöscht oder abgerufen wird, soll PL/SQL-Logik aufgerufen werden, welche die Änderungen dann an der Tabelle INTERMEDIA_IMAGE vornimmt.

Erzeugen Sie mit dem Skript apex-parsing-schema/03-plsql-package.sql das SQL-Paket PROCESS_IMAGE; es enthält für die Aktionen Create (Hochladen einer neuen Datei), Update (Neu-Hochladen einer Datei), Delete (Löschen einer Datei) und Render (Abrufen einer Datei) PL/SQL Code, der festlegt, was die Datenbank in diesen Fällen zu tun hat. Lassen Sie diesen Code im Parsing Schema Ihrer APEX-Anwendung ablaufen. Der Kürze halber ist auf dieser Seite nur die Package Specification aufgeführt - den vollständigen Body finden Sie mitsamt einigen Kommentaren im Code im ZIP-Archiv.

create context ctx_handle using process_image
/

CREATE OR REPLACE PACKAGE PROCESS_IMAGE AS
  PROCEDURE handlePreCreate (eventObject DBMS_XEVENT.XDBRepositoryEvent);
  PROCEDURE handlePreDelete (eventObject DBMS_XEVENT.XDBRepositoryEvent);
  PROCEDURE handlePreUpdate (eventObject DBMS_XEVENT.XDBRepositoryEvent);
  procedure handleRender (eventObject DBMS_XEVENT.XDBRepositoryEvent);
  procedure set_trigger_firing;
  procedure set_xdbevent_firing;
  procedure set_nothing_firing;
  function  is_trigger_firing return boolean;
  function  is_xdbevent_firing return boolean;
END;
/
sho err

Die nun folgenden SQL-Anweisungen legen die XML-Definition des Event Handlers im Verzeichnis /apex-images/config ab und aktivieren diesen dann. Im ZIP-Archiv ist das Skript dazu in der Datei dba/04-register-event.sql enthalten. Wenn Sie dagegen den Code aus dieser Seite per Copy & Paste übertragen, achten Sie darauf, an der rot markierten Stelle den Namen des Parsing Schemas Ihrer Anwendung einzusetzen.

-- Das ALTER SESSION-Kommando ist in Version 11.1.0.6 nötig; in 11.1.0.7 kann es entfallen
alter session set events='31150 trace name context forever, level 0x4000'
/
    
DECLARE
  b BOOLEAN := FALSE;
BEGIN
  b := DBMS_XDB.createResource(
   abspath => '/apex-images/config/upload-image-handler.xml',
   data    => '<ResConfig xmlns="http://xmlns.oracle.com/xdb/XDBResConfig.xsd"' ||
              '           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"' ||
              '           xsi:schemaLocation="http://xmlns.oracle.com/xdb/XDBResConfig.xsd' ||
              '                               http://xmlns.oracle.com/xdb/XDBResConfig.xsd">' ||
              ' <event-listeners>' ||
              '   <listener>' ||
              '     <description>Upload Images</description>' ||
              '     <schema>[Hier das APEX Parsing Schema einsetzen]</schema>' ||
              '     <source>PROCESS_IMAGE</source>' ||
              '     <language>PL/SQL</language>' ||
              '     <events>' ||
              '       <Render/>' ||
              '       <Pre-Create/>' ||
              '       <Pre-Delete/>' ||
              '       <Pre-Update/>' ||
              '     </events>' ||
              '     <pre-condition>' ||
              '       <existsNode>' ||
              '         <XPath>/Resource[@Container="false"]</XPath>' ||
              '       </existsNode>' ||
              '     </pre-condition>' ||
              '   </listener>' ||
              ' </event-listeners>' ||
              ' <defaultChildConfig>' ||
              '    <configuration>' ||
              '      <path>/apex-images/config/upload-image-handler.xml</path>' ||
              '    </configuration>' ||
              '  </defaultChildConfig>' ||
              '</ResConfig>'
  );
END;
/
sho err

BEGIN
  DBMS_RESCONFIG.appendResConfig(
    '/apex-images/upload/', 
    '/apex-images/config/upload-image-handler.xml',
    DBMS_RESCONFIG.APPEND_RECURSIVE
  );
END;
/
sho err

COMMIT
/

Damit ist der Event Handler aktiviert und die Arbeiten am virtuellen Dateisystem des XML DB Repsoitory sind abgeschlossen. Sie können schon testen; am besten leeren Sie dazu zunächst die Tabelle INTERMEDIA_IMAGE, so dass die APEX-Seite wie in Abbildung 4 aussieht:

Abbildung 4: Anwendungsseite mit leerer Tabelle

Nehmen Sie nun einen FTP-Client, verbinden Sie als User UPLOAD mit dem FTP-Server der Datenbank, wechseln Sie ins Verzeichnis /apex-images/upload und laden Sie einige Bilder hoch. Im Folgenden sehen Sie die Ausgabe des Kommandozeilen FTP-Clients, den Sie auf nahezu jedem Rechner finden.

D:\oracle-bilder> ftp -n
ftp> open 192.168.2.140 2100
Connected to 192.168.2.140.
220- vmware.de.oracle.com
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 vmware.de.oracle.com FTP Server (Oracle XML DB/Oracle Database) ready.
ftp> user upload upload
331 pass required for UPLOAD
230 UPLOAD logged in
ftp> cd /apex-images/upload
250 CWD Command successful
ftp> dir
200 PORT Command successful
150 ASCII Data Connection
226 ASCII Transfer Complete
ftp> bin
200  Type set to I.
ftp> prom
Interactive mode Off .
ftp> mput *
200 PORT Command successful
150 BIN Data Connection
226 BIN Transfer Complete
ftp: 73606 bytes sent in 0,00Seconds 73606000,00Kbytes/sec.
200 PORT Command successful
150 BIN Data Connection
226 BIN Transfer Complete
ftp: 121423 bytes sent in 0,00Seconds 121423000,00Kbytes/sec.
200 PORT Command successful
150 BIN Data Connection
226 BIN Transfer Complete
ftp: 97191 bytes sent in 0,00Seconds 97191000,00Kbytes/sec.
ftp>

Schauen Sie sich das Ergebnis zunächst im FTP-Client an.

ftp> dir
200 PORT Command successful
150 ASCII Data Connection
-rw-r--r--   1 UPLOAD   oracle         0 OCT 01 22:11 Bldg_600_1_lores.jpg
-rw-r--r--   1 UPLOAD   oracle         0 OCT 01 22:11 Bldg_600_2_lores.jpg
-rw-r--r--   1 UPLOAD   oracle         0 OCT 01 22:11 Campus_2_lores.jpg
226 ASCII Transfer Complete
ftp: 369 bytes received in 0,00Seconds 369000,00Kbytes/sec.
ftp>

Interessanterweise haben die hochgeladenen Bilder eine Größe von Null (0) Bytes. Schauen Sie nun mal in die APEX-Anwendung hinein und frischen Sie die Ansicht im Browser nochmals auf.

Abbildung 5: Anwendungsseite mit per FTP gefüllter Tabelle

Wenn Sie die Bilder nun im FTP-Client mit dem DEL-Kommando wieder löschen, verschwinden sie auch aus der Tabelle ... Probieren Sie es aus. Sie haben nun quasi einen FTP-Zugang zu Ihrer Tabelle implementiert.

Auch das Abrufen der Bilder über die Protokollserver ist möglich; dazu enthält das PL/SQL-Paket einen entsprechenden Render-Handler. Mit dem FTP GET-Kommando kommt der Inhalt des Bildes wieder auf Ihre lokale Festplatte zurück ...

ftp> bin
200  Type set to I.
ftp> get
Remote file Bldg_600_1_lores.jpg
Local file t.jpg
200 PORT Command successful
150 BIN Data Connection
226 BIN Transfer Complete
ftp: 73606 bytes received in 0,03Seconds 2374,39Kbytes/sec.
ftp>

Der Abschluß: DML an der Tabelle auf das XML DB Repository übertragen

Sie haben nun gesehen, wie Aktionen im virtuellen Dateisystem direkt auf die Tabelle INTERMEDIA_IMAGE übertragen werden. Doch wenn Sie an der Tabelle selbst arbeiten (bspw. über die APEX-Anwendung ein Bild löschen), passiert im virtuellen Dateisystem nichts. Der Eintrag für die Datei bleibt erhalten und zeigt "ins Leere".

Um diesen Zustand abzustellen, sollen Änderungen an der Tabelle per Trigger auf das XML DB Repository übertragen werden. Damit sich Trigger und die Events des XML DB Repository nicht gegenseitig in die Quere kommen, synchronisieren sie sich mit dem CONTEXT-Object CTX_HANDLE (wurde zusammen mit dem PL/SQL-Paket PROCESS_IMAGE angelegt). Mit dem Skript apex-parsing-schema/05-trigger.sql richten Sie die Trigger ein.

create or replace trigger tr_del_images
before delete on intermedia_image
for each row
begin
  if not process_image.is_xdbevent_firing then
    process_image.set_trigger_firing;
    dbms_xdb.deleteresource(
      abspath => '/apex-images/upload/'||:old.name
    );
    process_image.set_nothing_firing;
  end if;
end;
/  
sho err

create or replace trigger tr_ins_images
before insert on intermedia_image
for each row
declare
  b      boolean := false;
  bl     blob;
begin
  if instr(:new.name, '/') != 0 then
    :new.name := substr(:new.name, instr(:new.name, '/') + 1);
  end if;
  if not process_image.is_xdbevent_firing then
    process_image.set_trigger_firing;
    dbms_lob.createtemporary(bl, true, dbms_lob.call);
    b := dbms_xdb.createresource(
      abspath => '/apex-images/upload/'||:new.name,
      data    => bl
    );
    process_image.set_nothing_firing;
  end if;
end;
/  
sho err

Damit ist die Anwendung fertig. Wenn Sie nun an der Tabelle per INSERT, UPDATE oder DELETE Änderungen vornehmen, werden diese automatisch in das virtuelle Dateisystem übertragen und die entsprechenden Datei-Einträge werden dann erstellt oder gelöscht.

Da die Synchronisation zwischen Tabelle und virtuellem Dateisystem in diesem Beispiel anhand des Dateinamens erfolgt, dürfte es sinnvoll sein, einen UNIQUE-Constraint auf die Spalte NAME der Tabelle INTERMEDIA_IMAGE zu erstellen. Natürlich können Sie in eigenen Anwendungen auch andere Wege gehen: ID, volle Verzeichnispfade oder anderes - die Synchronisation erfolgt (wie Sie gesehen haben) allein durch PL/SQL-Code.

alter table INTERMEDIA_IMAGE add constraint un_filename unique(name)
/

Im ZIP-Archiv finden Sie noch das Skript dba/00-cleanup.sql, mit dem Sie die gesamte Konfiguration des virtuellen Dateisystems wieder löschen - die Tabelle INTERMEDIA_IMAGE selbst bleibt unberührt.

Die technische Grundlage dieser Anwendung sind, wie bereits erwähnt, die in Oracle11g neuen XML DB Repository Events. Dazu finden Sie hier weitere Informationen:

Die Bilder selbst werden in der Tabelle durch den Datentypen ORDIMAGE repräsentiert; dieser und dessen Möglichkeiten wurden im Tipp Bildbearbeitung mit Oracle Application Express vorgestellt. Mehr Informationen dazu finden Sie hier:

Oracle Multimedia User's Guide

Zurück zur Community-Seite