Export nach Postgres und SQLite

Das Tool Ora2SQLite bietet folgende Export Möglichkeiten:

• Oracle nach SQLite
• Oracle nach Postgres
• Postgres nach SQLite

Einsatzgebiete

Der Einsatz einer SQLite Datenbank kann z.B. für einen Auskunftsarbeitsplatz genutzt werden, der Offline verwendet wird, an welchem also keine Verbindung zur Oracle-Datenbank besteht. Oder Web Server Rechnern auf denen keine Oracle Datenbank installiert ist.

Es besteht unter anderem die Möglichkeit, aus einem MapEdit oder Topobase/Map Fachschale in Oracle eine SQLite Datenbank zu erstellen und diese Datenbank in MapEdit als Datenbankverbindung zu nutzen. Dann werden parallel auch die MapGuide Karten an die SQLite-Datenbank angebunden. Die Änderung des Datenbankproviders der MapGuide Karten erfolgt mit Hilfe des MapGuide Connection Switcher. Der MapGuide Connection Switcher ist ein Tool von MuM und kann als Plugin Kommando in den Ribbons eingerichtet werden.

Wo finde ich das Tool?

Tool Mum.Geo.Ora2SQLite.exe befindet auf dem MapEdit Server in dem Verzeichnis C:\Program Files (x86)\MuM MapEdit\Ora2SQLite. Darin starten Sie das Tool über die Mum.Geo.Ora2SQLite.exe:

Export

Nach dem Starten von Ora2Sqlite sehen sie dieses Fenster.

Hier können Sie die Quell Datenbank, die zu übertragenden Daten und die Ziel Datenbank bestimmen.

SOURCE DATABASE

Bei der Quell Datenbanken stehen Ihnen zwei Datenbanktypen zur Auswahl: Oracle oder Postgres. Je nach Datenbanktyp können Sie sich mit den entsprechenden Anmeldedaten mit der Quelldatenbank verbinden. In den Feldern Pre-Script und Post-Script kann ein SQL-File angegeben werden, das entsprechend vor oder nach der Übernahme ausgeführt werden soll. Wenn Sie auf Connect klicken, bekommen Sie im zweiten Fenster TABLES alle Tabellen der Datenbank angezeigt.

TABLES

Es besteht nun die Möglichkeit über die verschiedenen Schaltbuttons entweder alle Tabellen oder nur einzelne Tabellen auszuwählen, oder nur die Systemtabellen oder die Domaintabellen auszuwählen. Mit den Buttons < und <<< können aus der Exportliste einzelne oder alle Tabellen aus Exportliste entfernt werden.

Hinweis

Es wird, egal welche Tabellen gewählt werden, immer die komplette Datenstruktur aller Tabellen und Views in der Zieldatenbank angelegt. Die Auswahl gibt nur an welche Datensätze welcher Tabelle übernommen werden sollen.

Dies wurde so gemacht weil diverse Einstellungen wie Views, Label Definitionen, Dialog Definitionen, SQLs in Dialog Definitionen etc auf alle möglichen Tabellen gehen können. Dadurch, dass immer die Datenstruktur aller Tabellen übertragen wird, wird gewährleistet dass alle Einstellungen und damit das Programm weiterhin funktionieren.

Über die Pfeiltaste < können die Daten einzelner Tabellen die nicht benötigt werden aus dem Export herausgenommen werden. Dadurch wird der Export schneller und die Ziel-Datenbank kleiner.

Warnung

Es sollten mindestens immer alle System Tabellen (Knopf SYSTEM>>) ausgewählt werden ausser die Zieldatenbank wird nicht im Zusammenhang mit MapEdit verwendet.

Mit dem Button CreateExportTables.dat können die aktuell gewählten Tabellen in eine Datei ExportTables.dat gespeichert werden. Diese *.dat Datei kann dann optional im Kommandozeilen Modus verwendet werden um festzulegen welche Tabellendaten exportiert werden sollen.

Wird der Schalter Create Live Views deaktiviert, dann erzeugt das Programm statt Views eine Tabelle mit der gleichen Stuktur wie die View.

Fehlermeldung

Error7: Can not create view WW_V_STRECKEN SQL logic error near ".": syntax error Used View SQL: SELECT a.fid, a.geom,........ Tritt diese Fehlermeldung auf muss die View manuell auf ANSI SQL angepasst werden. Wenn man sich hundertprozentig sicher ist dass die View in der Zieldatenbank keine Verwendung findet kann man die Fehlermeldung ignorieren.

TARGET DATABASE

Als Zieldatenbank stehen zwei Datenbanktypen zur Auswahl Postgres oder SQLite

Achtung

Bitte beachten, dass die Oracle SRID normalerweise nicht dieselbe wie die SRID unter Postgres. Daher unbedingt die Postgres SRID Nummer vorgeben. Wenn die Übertragung der SRID nicht gewünscht wird, die Zahl -1 vergeben. In Postgres wird unabhängig der Groß-Kleinschreibung die Datenbank immer in Kleinbuchstaben angelegt.

Bei dem Export nach SQLite muss der SQLite Dateinamen angegeben werden.
Vorhandene Dateien/Datenbanken werden überschrieben.

Create Live Views

Ist "Create Live Views" aktiviert, dann versucht das Programm aus der Oracle Definition der Views in SQLite echte Views anzulegen. Das ist nur in einfachen Fällen erfolgreich, weswegen ein händisches Nacharbeiten mit Hilfe eines Scriptes notwendig ist, wenn man echte Views benötigt.

Warnung

Diese Option ist standardmässig aktiviert!!

Wen Sie eine reine Auskunfs SQLite Datenbank anlegen wollen sollten Sie diese Option ausschalten über "createliveviews=false", ansonsten werden ggf Views nicht angelegt und sie erhalten Fehlermeldungen.

Wenn diese Option ausgeschalten ist, werden aus den Oracle Views Tabellen erzeugt. D.h. die Daten sind redundant, aber das Anlegen geschieht ohne Probleme. Dies ist natürlich nur sinnvoll, wenn man rein lesend auf die Daten zugreift. Wenn man Daten in der SQLite Datei über MapEdit verändern will, dann muss man "Create Live Views" einschalten und die Views manuell (bzw. via Script) anlegen.

Beachten Sie das SQLite nicht die gleiche SQL Syntax und Funktionen wie Oracle unterstützt. D.h. In Oracle angelegte Views verwenden ggf Oracle spezifische Funktionen die in SQLite nicht funktionieren. Dadurch kann das Programm ggf Views in SQLIte nicht anlegen und sie müssen diese händisch nachträglich korrigieren. Siehe dazu auch Pre/Post Script.

Force View Creation

Ab Version 24.1.65

Diese Option steht nur zur Verfügung wenn die Zieldatenbank Postgres ist und macht nur Sinn wenn "Create Live Views" eingeschaltet ist.

Wird diese Option eingeschalten dann wird erzwungen das immer alle Views angelegt werden. Bei Views die nicht angelegt werden konnten wird dann als View SQL fix

SELECT 'Defect View, please fix' AS col1

angelegt.

Warnung

Die so angelegten Views sind natürlich falsch und müssen hinterher angepasst werden, anonsten kann es zu Programm abstürzen führen.

Wozu ist das gut?

Diese Option wurde so gewünscht damit man bei "defekten" Views hinterher in der AppBuilder UI den View einfach korrigiert kann. Im AppBuilder wird dabei der orginale Oracle View in der UI angezeigt und dieser kann dort dann auf postgres angepasst werden. Welche Views defekt sind finden Sie im LOG File der Migration.

Beispiel:

Wir wollen eine Oracle Datenbank nach Postgres konvertieren.

Der View "vw_baum" hat in Oracle folgende Syntax:

create view vw_baum as select b.fid,t.value from baum b, baum_type_tbd t where b.id_baum_typ=t.id(+)

Postgres kennt im Gegensatz zu Oracle den Join Befehl (+) nicht und das Programm kann nun den View in Postgres nicht anlegen.

Das gleiche passiert wenn Oracle Funktionen oder Syntax verwendet wird die Postgres nicht kennt.

Wird die Option "Force View Creation" eingeschalten dann wir ein View mit dem Namen "vw_baum" und als SQL

SELECT 'Defect View, please fix' AS col1

verwendet.

Ist die Option ausgeschalten wird kein View "vw_baum" angelegt.

Create Planning Attributs

Diese Funktion findet im Modul "Varianten Planung" Anwendung. Wenn sqlite Dateien für das Plannungsmodul erzeugen werden sollen, muss dieser Haken gesetzt werden.

Log File

Geben sie bei Logfile einen Name und Speicherort für die Export Log Datei an.
Bitte prüfen Sie, dass das Verzeichnis für die Log-Datei nicht schreibgeschützt ist.

Hier kann optional auch der Platzhalter

{DateTime}

genutzt werden um das aktuelle Datum mit Uhrzeit einzufügen. Dadurch kann man bei wiederholtem Export ein überschrieben der Log Dateien verhindern.

Beispiel:

C:\Test\osnab_{DateTime}.log

Log Fids

Ist diese Option eingeschaltet dann werden in der Log Datei die einzelnen Objekte mit FID geloggt. Beachten Sie das dadurch die Logdatei grösser wird und dies nur bei Bedarf eingeschaltet werden sollte.

Extended Log Infos

Ist diese Option eingeschaltet dann werden in der Log Datei detailiertere Informationen geloggt. Beachten Sie das dadurch die Logdatei grösser wird und dies nur bei Bedarf eingeschaltet werden sollte.

Export Starten

Durch Betätigen des Buttons CREATE wird der Export in die gewünschte Datenbank gestartet.

Im Fall das die Ziel Datenbank eine SQLite Datenbank ist, wird die Export Datei zuerst als *.tmp datei erzeugt und am Ende, wenn das Programm komplett durchgelaufen ist, umbenannt. Damit ist garantiert, dass die Datei nur dann vorhanden ist, wenn das Programm wirklich komplett durchgelaufen ist.

Pre / Post Script

Es kann optional jeweils eine SQL Skriptdatei angegeben werden.

Das Pre (vor) Script wird nach dem Anlegen der Datentruktur aber vor dem Export der Daten ausgeführt.

Das Post (nach) Script wird nach dem Export der Daten ausgeführt.

Mit diesen Scripten können manuelle Ändrugen an den Daten vorgenommen werden. z.B. Wenn View Defintionen angepasst werden müssen etc.

Kommentare können angegeben werden, in dem die Zeile mit -- beginnt. Nach jedem SQL (oder SQL Block) muss in einer eigene Zeile der Befehl --EXECUTE stehen damit das SQL ausgeführt wird.

Besteht das Script nur aus einzeiligen SQl Anweisungen (also ein SQL pro Zeile) Dann kann auch am anfang --AUTO EXECUTE gesetzt werden. Dann wird kein --EXECUTE benötigt.

Steht im Postscript SQL eine CREATE VIEW xy AS-Anweisung, dann wird der Views wie im Script definiert angelegt und nicht wie in der Datenbank definiert.

Kommandozeilen Parameter

Das Programm kann im Kommandozeilen Modus (via Dos Kommando Fenster oder Batch Datei) ausgeführt werden. Dadurch kann z.B. der Export als täglicher Task via Batchdatei eingerichtet werden.

Alle Parameter werden mit ";" getrennt.

Hier ein Beispiel für den Export mit Hilfe einer Batchdatei und Oracle als Quell-Datenbank und das Ziel sind SQLite Datenbanken:

Beispiele:

"C:\Program Files (x86)\MuM MapEdit\Ora2SQLite\Mum.Geo.Ora2SQLite.exe" Username=OSNAB;Password=avs;Service=micha;Filename=C:\test22.sqlite;LogFilename=C:\osnab.log;

"C:\Program Files (x86)\MuM MapEdit\Ora2SQLite\Mum.Geo.Ora2SQLite.exe" Username=STROM;Password=avs;Service=topo;Filename=E:\SQLITE\strom.db;

"C:\Program Files (x86)\MuM MapEdit\Ora2SQLite\Mum.Geo.Ora2SQLite.exe" Username=WASSER;Password=avs;Service=topo;CreateLiveViews=false;ExportTablesFilename=C:Test\Exp_Wasser.dat;Filename=E:\SQLITE\wasser.db;LogFilename=C:\wasser.log;

Bei Quell Datenbank Oracle

Oracle Benutzername, Passwort und Service

Username=OSNAB;Password=avs;Service=orcl;

Beispiel:

Mum.Geo.Ora2SQLite.exe Username=OSNAB;Password=avs;Service=micha;Filename=C:\test22.sqlite;LogFilename=C:\osnab.log

Bei Ziel Datenbank Postgres

Wenn Postgres die Zieldatenbank ist, wird diese vom Programm erzeugt und muss nicht vorhanden sein. Datenbanken in Postgres werden immer in Kleinbuchstaben angelegt.

PGUserId=postgres;        (Das Feld 'Super User Name' in der UI)
PGPassword=avs;           (Das Feld 'Super User Password' in der UI)
PGSuperDatabase=postgres; (Das Feld 'Super Database' in der UI)
PGPort=5432;              (Das Feld 'Port' in der UI)
PGServer=localhost;       (Das Feld 'Server' in der UI)
PGDatabase=osnab_me_1;    (Das Feld 'Database' in der UI)
PGRecreateDB=True;        (Das Feld 'Recreate Database' in der UI) <<< Wenn die  Datenbank
schon da ist wird sie geloscht und neu erzeugt)
PGSrid=25832              (Srid der Ziel Datenbank)

Hier eine Beispiel einer Batchdatei für Oracle in eine Postgres Datenbank:

"C:\Program Files (x86)\MuM MapEdit\Ora2SQLite\Mum.Geo.Ora2SQLite.exe" Username=SW_ALKIS;Password=avs;Service=GISR2;PGUserId=postgres;PGPassword=pwd;PGSuperDatabase=pg_sw_gis;PGPort=5432;PGServer=SRVPGSQL.stwsw.intern;PGDatabase=mum_sw_alkis;PGRecreateDB=True;CreateLiveViews=False;

Warnung

Der Super User muss Rechte zum anlegen neuer Datenbanken haben.

Der Parameter PGSrid steht erst ab Version 22.2.46, davor wird die letzte Einstellung genommen die via UI gesetzt wurde.

SQLite Ziel Datei

Filename=C:\wasser.sqlite

Der Platzhalter {auto} kann verwendet werden um einen automatischen Namen aus dem Oracle Usernamen und dem aktuellen Datum zu bilden zu lassen.

Filename=C:\test\{auto}

Der neue Name ist dann Oracle Username plus "." plus Datum plus ".db"

Soll statt dem Oracle Username ein anderer Namen verwendet werden kann der Parameter "Aliasname" verwendet werden.

Aliasname=Wasser

Der neue Dateiname ist dann Aliasname plus "." plus Datum plus ".db"

Bei Quell Datenbank Postgres

Ab Version 22.2.171/23.1.134

PGUserId=postgres;        (Das Feld 'User Name' in der UI)
PGPassword=avs;           (Das Feld 'Password' in der UI)
PGServer=localhost;       (Das Feld 'Server' in der UI)
PGDatabase=osnab_me_1;    (Das Feld 'Database' in der UI)
PGPort=5432;              (Das Feld 'Port' in der UI)

Hier eine Beispiel einer Batchdatei zum Export von Postgres nach SQLite:

"C:\Program Files (x86)\MuM MapEdit\Ora2SQLite\Mum.Geo.Ora2SQLite.exe" PGUserId=postgres;PGPassword=avs;PGDatabase=osnab_me_po;PGPort=5432;PGServer=localhost;Filename=C:\Transfer\osnab.sqlite;LogFilename=Transfer\osnab.log;

ExportTablesFilename

Optional die durch den Knopf "Create ExportTables.dat" erzeugte Datei. Wird diese Option nicht gesetzt werden alle Tabellen exportiert.

ExportTablesFilename=C:Test\ExportTablesOSNAB.dat

Pre/PostScript

PostScriptFilename=C:Test\post.sql

PreScriptFilename=C:Test\pre.sql

Statt PostScriptFilename kann auch ScriptFilename verwendet werden. Die ist wegen Rückwärtskompatibilität zu alten Programm Versionen so.

Log Datei (Logfile)

Hier kann der Dateiname der Log Datei angegeben werden.

LogFilename=C:\Test\osnab.log

Im Namen kann optional der Platzhalter {DateTime} genutzt werden um das aktuelle Datum mit Uhrzeit einzufügen. Dadurch kann man bei wiederholtem Export ein überschrieben der Log Dateien verhindern.

Beispiel:

LogFilename=C:\Test\osnab_{DateTime}.log

Programmabbruch

Im Falle eines Fehlers wird im Log-File ein FATAL ERROR gemeldet. (Die Meldung kommt nicht, wenn das Programm abgebrochen wurde etc). Außerdem wird keine Datenbank erzeugt, wenn das Programm aus irgendwelchen anderen Gründen beendet wird. z.B. wenn der Rechner runtergefahren wird oder das Programm mit dem Taskmanager abgeschossen wurde etc.

Das Programm bricht von sich aus ab, wenn eine der folgenden ORA-Meldungen auftritt, wodurch keine SQLite erstellt wird;

• ORA-03114: not connected to ORACLE
• ORA-01089: immediate shutdown or close in progress – no operations are permitted
• ORA-01034: oracle not available
• ORA-00603: ORACLE server session terminated by fatal error
• ORA-12154: TNS:could not resolve the connect identifier specified (#1)
• ORA-03113: end-of-file on communication channel

Programm bleibt stehen

Wenn das Programm scheinbar stehen bleibt, dann liegt das meistens an kaputten oder sehr langsamen Views. Views die zu lange braucht werden im Logfile geloggt. Bitte schliessen Sie diese dann entweder vom Export aus oder optimieren Sie die Geschwindigkeit dieser Views.

Besonderhieten bei Postgres und Views

siehe Datenstruktur Views ganz unten.

Views