www.symax.de

von Thomas Geisel (Kommentare: 0)

Zusammenspiel von Oracle und Dropbox oder anderen Filehosting-Diensten - auch ohne API

Wie Sie Dateien mit Oracle zu Filehosting-Diensten (Dropbox et al.) auslagern und in Datenbanken nutzen können

Inhalt

Einleitung

In einer Oracle-Datenbank können binäre Dokumente in LOB-Spalten abgelegt werden. Aber nicht immer ist das der Weisheit letzter Schluss. Gelegentlich möchte man Dokumente weiterhin im Dateisystem des Betriebssystems verwalten, dennoch aber einen Zugriff darauf aus der Oracle-DB heraus ermöglichen.

Ein Grund dafür kann beispielsweise sein, dass Sie eine Oracle-XE einsetzen, welche nur einen limitierten Speicherplatz von 10 GB hat, der durch das Speichern von Dokumente in der Datenbank schnell belegt würde.

Ein weiterer Grund ist aber auch der Gedanke, dass man Dokumente, die über einen Dropbox-Account bereits zwischen Geräten synchronisiert werden, in der Oracle-Datenbankanwendung bereitstellen möchte.

Hierzu bietet z.B. Dropbox die Core-API an, welche theoretisch zu diesem Zweck angesprochen werden kann, allerdings zwei Nachteile mit sich bringt:

  1. Die Anwendung ist dann fest auf Dropbox eingestellt und funktioniert nicht mit anderen Cloud-Systemen wie Owncloud/Nextcloud, Skydrive oder ähnlichen Lösungen
  2. Die Dropbox-API erfordert eine SSL-gesicherte Kommunikation, was aber von der Oracle-XE nicht unterstützt wird

Die in diesem Artikel vorgestellte Lösung bietet eine Alternative dazu an, in dem durch "external-jobs" die benötigten Dateioperationen durchgeführt werden und die Lösung somit unabhängig von SSL oder spezifischen APIs ist.

Umgesetzt wird das nachstehend beispielhaft auf der Windows-Plattform, allerdings kann die Lösung durch geringe Modifikation der externen Script-Befehle leicht auf Unix portiert werden.

Vorarbeiten und Vorbemerkungen

In diesem Artikel werden die Daten des Oracle-Sample-Schemas HR genutzt und die Aktionen als User HR ausgeführt.
Daher ist es nötig, diesen User – sofern noch nicht geschehen – freizuschalten und ihm ein Passwort zuzuweisen:

sqlplus sys/<pw>@<db> as sysdba
ALTER USER HR IDENTIFIED BY hr ACCOUNT UNLOCK;

Darüber hinaus benötigt der HR-User noch einige Rechte, um den Scheduler anzusprechen und Directories erzeugen bzw. löschen zu können:

grant execute on dbms_scheduler TO hr;
grant create job to hr;
grant create external job to hr;
grant create any directory to hr;
grant drop any directory to hr;
grant create table to hr;
exit

Um die weiter unten aufgeführten SQL- bzw. PL/SQL-Scripte dann direkt ausführen zu können, führen Sie eine Anmeldung als User "HR" durch:

sqlplus hr/hr@<db>

Vorab sei noch angemerkt, dass es sich bei den folgenden Schritten um ein exemplarisches Beispiel handelt, dessen Sinnhaftigkeit und Vollständigkeit hier nicht diskutiert werden soll. Es dient lediglich dazu, die nötigen Techniken zu vermitteln, die bei einer vergleichbaren Aufgabenstellung als Grundlagen dienen.

Aufgabenstellung

Basierend auf den Employee-Daten im HR-Schema, soll eine Möglichkeit geschaffen werden, binäre Dokumente wie Fotos, PDF-Dateien oder Ähnliches zu jedem Employee hinterlegen und abrufen zu können.

Da hierfür reine PL/SQL-Techniken sowie einige wenige Befehle des Betriebssystems verwendet werden, kann zudem die Synchronisation mit Dropbox, Nextcloud, Skydrive oder ähnlichen Filehosting-Lösungen darauf angewendet werden.

Schritt 1: Stammverzeichnis für die LOB-Dateien einrichten

Zunächst benötigen wir ein Verzeichnis, in welchem die Shell-Scripte mit den Betriebssystembefehlen hinterlegt und darunter die individuellen Verzeichnisse je Employee angelegt werden.

Dazu starten Sie mit [Windows]-[R] und der Eingabe von cmd.exe ein Shell-Umgebung und führen dort aus:

md c:\lob_data

Auf diesem Verzeichnis (und seinen künftigen Unterverzeichnisse) muss der Oracle-Prozess die Rechte erhalten:

  • Scripte auszuführen sowie
  • Dateien und Verzeichnisse anzulegen,
  • zu beschreiben und auch wieder zu löschen:
icacls c:\lob_data /grant orainst:(OI)(CI)(F)

In diesem Beispiel läuft der Oracle-Dienst unter dem Benutzeraccount ORAINST und es wird die Vererbung aktiviert und Vollzugriff (F) erteilt.


(Sofern Ihre Oracle-Instanz unter dem lokalen Systemkonto läuft, können Sie diesen Schritt ignorieren)

Als User "HR" erzeugen Sie nun ein Directory, welches auf genau dieses Verzeichnis im Dateisystem verweist:

create or replace directory LOB_DIR as 'c:\lob_data';

Schritt 2: Script für das Auflisten eines Verzeichnisinhalts

Als nächstes benötigen wir ein Betriebssystem-Script, welches die Dateiinhalte eines bestimmten Verzeichnisses auflistet und in eine Textdatei schreibt.

Legen Sie dazu in dem oben erstellten Verzeichnis die Datei list_files.cmd an und tragen dort folgenden Inhalt ein:

@echo off

rem Codepage wechseln, damit Umlaute korrekt ausgegeben werden
chcp 1252 > nul

rem Das Script erwartet mind. 1 Argument mit einer eindeutigen SessionID SID 
rem und ein optionales 2.tes Argument mit dem Unterverzeichnisnamen.
rem Wenn nicht mind. die SID angegeben, dann nichts tun

if "%1" equ "" goto :EOF

rem Drive und Path des Scripts (Param 0) legen das Basisverzeichnis fest 
set basedir=%~dp0

rem Die Ausgabedatei wird in diesem Basisverzeichnis mit der SID als Dateiname erzeugt
set outfile=%basedir%\%1.txt
set listdir=%basedir%

rem Wird als 2.tes Argument das Unterverzeichnis angegeben, dann an ListDir anhängen
if "%2" neq "" set listdir=%listdir%\%2

rem Header in die Ausgabedatei schreiben
echo name,extension,size,timestamp > %outfile%

rem alle Dateien in dem Listdir enumerieren und Name, Extension, Size und Timestamp komma-separiert in Ausgabedatei schreiben
for %%D in (%listdir%\*) do (
   rem nur wenn Datei nicht mit Punkt beginnt, dann auch rausschreiben (Umweg über     Env-Variable ist notwendig!)
   set f=%%~nD
   set f=!f:~0,1!
   if "!f!" neq "." (  
      echo "%%~nD","%%~xD","%%~zD","%%~tD" >> %outfile%
      )
   )

Die so erzeugte Textdatei, welche später in der Oracle-DB als external-table angebunden und ausgelesen wird, könnte dann z.B. folgenden Inhalt haben:

name,extension,size,timestamp 
"Anstellungsvertrag",".docx","79605","06.03.2017 11:21" 
"foto",".jpg","50542","31.05.2016 17:16" 
"Lebenslauf",".pdf","123710","27.01.2017 11:15"

Schritt 3: Objekte für die Rückgabe eines Verzeichnislistings anlegen

Die Technik hierzu ist bereits in "Oracles-OO-Techniken für effiziente Abfragen nutzen" beschrieben, so dass hier nicht mehr ausführlich darauf eingegangen wird.

Wir brauchen also nun einen Object-Type, der eine einzelne Zeile eines Directory-Listings aufnimmt sowie eine Collection, die den kompletten Verzeichnisinhalt aufnehmen kann:

CREATE OR REPLACE TYPE T_FILEINFO FORCE AS OBJECT
(
   FName           varchar2(200),    -- der Dateiname
   FExt            varchar2(20),     -- die Extension
   FSize           integer,          -- die Dateigröße in Bytes
   FModified       date              -- das Datum der letzten Änderung
)
/
CREATE OR REPLACE TYPE T_FILEINFOLIST AS TABLE OF T_FILEINFO;
/

Schritt 4: Hilfsroutine zur Ausführung externer Scripte

Da wir in der Folge mehrere unterschiedliche externe Scripte ausführen werden, ist es sinnvoll, eine Hilfsfunktion zu entwickeln, die in PL/SQL einfach mit dem Scriptnamen und den Argumenten aufgerufen werden kann.

Zum Ausführen eines externen Scripts nutzen wir den Oracle-Scheduler, dem wir einen adhoc-Job übermitteln und diesen unmittelbar zur Ausführung bringen.

Dazu muss allerdings der OracleJobScheduler-Dienst aktiviert sein und laufen! Sie können das in der Computerverwaltung–Dienste prüfen und ändern, oder pauschal mit folgenden Befehlen erledigen.

Starten Sie dazu zunächst eine cmd.exe als Administrator ([Windows], cmd.exe eintippen, rechtsklick, "Als Administrator ausführen") und aktivieren und starten Sie den Dienst mit Hilfe von sc.exe:

sc config OracleJobScheduler<instanzname> start= auto
sc start OracleJobScheduler<instanzname>

wobei <instanzname> für die SID der Oracle-Instanz steht, also z.B. ORCL oder XE …

CREATE OR REPLACE PROCEDURE runextjob(
                        p_scriptname VARCHAR2,
                        p_sid INTEGER,
                        p_subDir VARCHAR2 DEFAULT NULL
                        )
   AS
      basedir VARCHAR2(100) := 'c:\lob_data';
      extcmd VARCHAR(100) := 'C:\windows\system32\cmd.exe';
      jobname VARCHAR2(100) := 'os_command_job_' || p_sid;
      argcnt INTEGER := 3;
   BEGIN
      IF ( p_scriptname IS NULL OR p_sid IS NULL) THEN
         RETURN;
      END IF;
      
      IF (p_subDir IS NOT NULL) THEN
         argcnt := argcnt + 1;
      END IF;
      
      -- job mit der entsprechenden Anzahl Argumente anlegen
      DBMS_SCHEDULER.create_job(
         job_name             => jobname,
         job_type             => 'EXECUTABLE',
         number_of_arguments  => argcnt,
         job_action           => extcmd,
         auto_drop            => FALSE,
         enabled              => FALSE);
         
      -- Argumente übergeben, wobei Args 1 (/c), 3 (OS-Script) und 3 (SID) immer vorhanden sind
      DBMS_SCHEDULER.set_job_argument_value(jobname,1,'/c');
      DBMS_SCHEDULER.set_job_argument_value(jobname,2, basedir || '\' || p_scriptname );
      DBMS_SCHEDULER.set_job_argument_value(jobname,3, to_char(p_sid));
      IF (argcnt > 3) THEN
         DBMS_SCHEDULER.set_job_argument_value(jobname, 4, convert( p_subDir, 'WE8MSWIN1252'));
      END IF;

      -- job ausführen und danach wieder löschen
      dbms_scheduler.run_job(job_name => jobname, use_current_session=> TRUE);
      dbms_scheduler.drop_job(jobname,force => TRUE);      
     
   EXCEPTION
      WHEN others THEN 
         -- bei Fehlern den Job auch wieder löschen
         dbms_scheduler.drop_job(jobname,force => TRUE);      
         raise;
   END;
/

Jetzt können wir die Procedure bereits testen und uns den Inhalt des Verzeichnisses c:\lob_data ausgeben lassen:

CALL runextjob(p_scriptname => 'list_files.cmd'
             , p_sid => sys_context('USERENV','SID')
             , p_subdir => NULL);

Nach erfolgreicher Ausführung sollte sich im Verzeichnis c:\lob_data nun eine Textdatei mit dem Namen zu aktuellen SessionID befinden (z.B. 183.txt), deren Inhalt voraussichtlich so aussieht:

name,extension,size,timestamp 
"list_files",".cmd","1184","22.08.2017 11:39"

Schritt 5: Die Table-Function für das Verzeichnis-Listing erstellen

Die eigentliche Funktion, die uns nun in unserer Anwendung den Inhalt eines Verzeichnisses anzeigen soll, muss nur noch die vorbereiteten Elemente zusammenfügen und dabei

  1. das externe Script für das gewünschte Verzeichnis ausführen
  2. die von dem Script erzeugte Ergebnisdatei als external-table anbinden
  3. und dessen Inhalt in die Collection überführen und diese zurückliefern

Demzufolge ist diese Funktion leicht zu bauen, aber vorher benötigen wir noch einen TYPE, der einen REF-CURSOR darstellt, da die Funktion Code enthalten soll, um eine Tabelle auszulesen, die zum Compile-Zeitpunkt noch garnicht existiert.

Ein solcher Ref-Cursor muss in einem Package deklariert werden und wird daher wie folgt erzeugt:

CREATE OR REPLACE PACKAGE RC
AS
  TYPE ref_cur IS REF CURSOR;
END;
/

Bei der Table-Function ist nun noch ein Detail zu beachten:  da die Function auch in SQL-Abfragen genutzt werden soll, jedoch während Abfragen eigentlich keine DDL-Operationen zulässig sind, muss diese als AUTONOMOUS_TRANSACTION ausgeführt werden.

CREATE OR REPLACE FUNCTION getDirList(p_subDir VARCHAR2 DEFAULT NULL)
      RETURN t_FileInfoList
   AS
      PRAGMA AUTONOMOUS_TRANSACTION;
      ret T_FileInfoList := T_FileInfoList();
      inf t_FileInfo;
      sid INTEGER := sys_context('USERENV','SID');
      cmd VARCHAR2(4000);
      cur rc.ref_cur;
      s VARCHAR2(4000); 
   BEGIN
      -- externen Job ausführen, um Directory-Listing in Datei zu schreiben
      runextjob('list_files.cmd', sid, p_subDir );
      -- Dynamic-SQL Befehl für External-Table zusammenbauen
      cmd := 'CREATE TABLE ext_' || sid || '(' || chr(10) ||
'   fname  VARCHAR2(200),' || chr(10) ||
'   fext   VARCHAR2(200),' || chr(10) ||
'   fsize  VARCHAR2(20),' || chr(10) ||
'   modified  VARCHAR2(40)' || chr(10) ||
')' || chr(10) ||
'   ORGANIZATION EXTERNAL (' || chr(10) ||
'     TYPE oracle_loader' || chr(10) ||
'     DEFAULT DIRECTORY LOB_DIR' || chr(10) ||
'       ACCESS PARAMETERS (' || chr(10) ||
'       RECORDS DELIMITED BY 0X''0D0A''' || chr(10) ||
'       CHARACTERSET WE8MSWIN1252' || chr(10) ||
'       STRING SIZES ARE IN CHARACTERS' || chr(10) ||
'       TERRITORY "AMERICA"' || chr(10) ||
'       BADFILE '''||sid||'.bad''' || chr(10) ||
'       LOGFILE '''||sid||'.log''' || chr(10) ||
'       SKIP 1' || chr(10) ||
'       FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY ''"''' || chr(10) ||
'       MISSING FIELD VALUES ARE NULL' || chr(10) ||
'       REJECT ROWS WITH ALL NULL FIELDS' || chr(10) ||
'       (' || chr(10) ||
'         fname,' || chr(10) ||
'         fext,' || chr(10) ||
'         fsize,' || chr(10) ||
'         modified' || chr(10) ||
'       )' || chr(10) ||
'       )' || chr(10) ||
'       LOCATION ('''||sid||'.txt'')' || chr(10) ||
'     )' || chr(10) ||
'   REJECT LIMIT 1' || chr(10) ||
'   PARALLEL' || chr(10) ||
'   NOMONITORING';

      -- Datei mit Directory-Listing als external-table einbinden
      EXECUTE IMMEDIATE cmd;

      BEGIN
         -- Inhalt der neuen External-Table per dynamic-SQL lesen
         cmd := 'SELECT fname, fext, fsize, modified FROM ext_' || sid;
         OPEN cur FOR cmd;
         LOOP
            inf := T_FileInfo(NULL, NULL, NULL, NULL);
            FETCH cur INTO inf.FName, inf.FExt, inf.FSize, s;
            EXIT WHEN cur%NOTFOUND;
            inf.FModified := to_date(s, 'dd.mm.yyyy hh24:mi');
            ret.extend;
            ret(ret.last) := inf;
         END LOOP;
         CLOSE cur;

         -- nachdem alles gelesen ist, die External-Table wieder löschen
         EXECUTE IMMEDIATE 'drop table ext_' || sid;
         -- und nun die resultierende Collection zurückgeben
         COMMIT;
         RETURN ret;         
         
      EXCEPTION
         WHEN others THEN 
            EXECUTE IMMEDIATE 'drop table ext_' || sid;
            ROLLBACK;
            RAISE;
      END;
   END;
/

Diese Table-Function kann bereits auf dem Verzeichnis c:\lob_data getestet werden:

SELECT * FROM TABLE(getdirlist(p_subdir => NULL));

Das Ergebnis sollte etwa wie folgt aussehen:

FNAME		FEXT		FSIZE		FMODIFIED
244			.txt		32			22.08.2017 14:15:00
list_files	.cmd		1184		22.08.2017 11:39:00

Schritt 6: Den Dropbox-Client in Betrieb nehmen

Nachdem wir nun beliebige Verzeichnisinhalte auflisten können, ist es an der Zeit den Dropbox-Client bzw. die entsprechenden Module von Nextcloud oder Skydrive zu installieren.

Auf die Installation wird hier nicht eingegangen, da diese Tools jeweils über eigene Installer verfügen, die zuverlässig und leicht verständlich durch die Installation und Erstkonfiguration führen.

Wichtig ist nur, dass das zu synchronisierende Verzeichnis ebenfalls auf c:\lob_data eingestellt wird.

Nehmen wir nun an, dass wir für jeden Employee ein eigenes Unterverzeichnis haben wollen, in welchem korrespondierende Dokumente hinterlegt sein sollen, dann würde sich folgende Verzeichnisstruktur ergeben:

C:
	\lob_data
		\emp100
		\emp101
		\emp102
		…

Um dann die vorliegenden Dokumente zu einem Employee abzurufen, kann z.B. eine SQL-Anweisung wie die Folgende verwendet werden:

SELECT e.employee_id, e.email, f.*
  FROM employees e
  CROSS JOIN TABLE(getDirList(p_subDir => 'emp'||e.employee_id)) f 
 WHERE ( e.last_name = 'Kochhar' AND e.first_name = 'Neena' )
    OR ( e.last_name = 'Seo' AND e.first_name = 'John' );

Das Resultat könnte dann z.B. so aussehen:

EMPLOYEE_ID		EMAIL	FNAME					FEXT	FSIZE	FMODIFIED
101	NKOCHHAR			Anstellungsvertrag		.docx	79605	06.03.2017 11:21:00
101	NKOCHHAR			foto					.jpg	50542	31.05.2016 17:16:00
101	NKOCHHAR			Lebenslauf				.pdf	123710	27.01.2017 11:15:00
139	JSEO				Dienstwagenvereinbarung	.docx	79605	06.03.2017 11:21:00
139	JSEO				foto					.jpg	50542	31.05.2016 17:16:00
139	JSEO				Oracle-Zertifikat		.pdf	123710	27.01.2017 11:15:00

Ausblick und nächste Schritte

Es ist offensichtlich, dass diese Technik selbstverständlich nicht dazu geeignet ist, Massendaten abzufragen, da für jeden Employee das externe Script aufgerufen, dessen Ergebnisdatei als External-Table angebunden und ausgelesen und diese schließlich wieder gelöscht wird. (Aber wäre eine exzessive Nutzung von z.B. der Dropbox-API letztlich wirklich performanter?)

Eine Optimierung könnte also z.B. sein, in einem nächtlichen Job die Liste der Dokumente pro Employee mit einer internen Tabelle zu synchronisieren, so dass zumindest Anzahl, Namen, Typen und Dateigrößen sowie –Timestamp in der Datenbank für Übersichtsabfragen schnell abrufbar sind und nur beim Aufruf einer speziellen Maske in der jeweiligen Anwendung noch mal konkret den aktuellen Verzeichnisinhalt zu laden.

Um dann diese externen Dokument abzurufen und deren Inhalt an eine Anwendung zurückzugeben, muss schließlich aber noch eine Function erstellt werden, welchen diesen als BLOB zurückgibt.

Hier bietet sich das Lesen des Dateiinhalts via BFile in einen BLOB an, allerdings muss dazu erst dynamisch das Directory angelegt werden, da wir zwar weiter oben das Directory LOB_DIR erzeugt haben, von dort aus aber nicht in den EMPxxx-Unterverzeichnissen gelesen werden kann.

Die Function könnte sinngemäß also so aussehen:

CREATE OR REPLACE FUNCTION getDocContent(
                        p_EmpID INTEGER,
                        p_DocName VARCHAR2)
      RETURN BLOB
   AS
      PRAGMA AUTONOMOUS_TRANSACTION;
      extfile BFILE;
      ret BLOB;
      dirname VARCHAR2(100) := 'doc' || sys_context('USERENV','SID');;
   BEGIN
      EXECUTE IMMEDIATE 'create directory ' || dirname || ' as c:\lob_data\emp' || p_EmpID; 
      extfile := BFILENAME(dirname, convert( p_DocName, 'WE8MSWIN1252') );
      DBMS_LOB.OPEN(extfile, DBMS_LOB.LOB_READONLY);
      DBMS_LOB.CREATETEMPORARY(lob_loc => ret, cache => TRUE, dur => dbms_lob.session);
      DBMS_LOB.OPEN(ret, DBMS_LOB.LOB_READWRITE);
      DBMS_LOB.LOADFROMFILE(dest_lob => ret, src_lob  => extfile, amount => DBMS_LOB.getLength(extfile));
      DBMS_LOB.CLOSE(extfile);
      DBMS_LOB.CLOSE(ret);
      EXECUTE IMMEDIATE 'drop directory ' || dirname;          
      COMMIT;
      RETURN ret;
   EXCEPTION
      WHEN others THEN 
         EXECUTE IMMEDIATE 'drop directory ' || dirname;          
         RAISE;
   END;
/

Über unsere erstellte Hilfsroutine runExtJob können natürlich noch weitere OS-Scripte ausgeführt werden.

Z.B. um Dokumente zu packen/entpacken, um diese in ein anderes Format zu konvertieren, per Hilfsprogramme an SFTP-Freigaben zu schieben oder per S/SMTP zu mailen (beides geht von Hause aus z.B. in der XE nicht, da diese kein SSL unterstützt) oder an Druckserver zu übermitteln.

Ein abschließender Satz zu dem eigentlichen Titel dieses Artikels - denn wir sind im Laufe der letzten Seiten doch sehr wenig auf Dropbox, Owncloud/Nextcloud, Skydrive und wie sie alle heißen, eingegangen:

Die anfängliche Hürde, ein Oracle-Datenbanksystem mit diesen Diensten zu koppeln und dann komplexe APIs anzusprechen durchzuführen, wird durch die hier vorgestellte Technik ganz leicht entschärft:  lassen Sie die Synchronisation des jeweiligen Cloud-Speichersystems durch deren native Clients durchführen und interagieren Sie mit den Inhalten letztlich nur über den transparenten und leicht portierbaren Weg, simple OS-Kommandos aus der Oracle heraus auszuführen und deren Ergebnisse innerhalb der Datenbank zu verwerten.

Zurück

Einen Kommentar schreiben