Projekt Schema Riser

Nach dem Genuss von Rich Hickeys Vortrag 'Database as a Value' wurde mir bewust dass moderne Datenbankanwendungen
im Bereich Billing und Accounting und Projekt Dokumentation ohne die im Vortrag aufgezählten Eigenschaften nicht gut funktionieren können.

Das motivierte mich neue Dienste und Funktionalität zur Unterstützung von Oracle Apex Anwendungen meines Auftraggebers zu entwickeln.
Weil die Programme in einem kleinen mittelständischen Betrieb laufen,
stand nur eine Oracle Standard Edition 11g ohne zusätzliche Lizenzen zur Verfügung.

Mit einer Sammlung von selbst erstellten Packages und materialized Views und Tabellen
und einer Apex Anwendung namens Schema Riser gibt es nun:

- Eigene Workspaces/Namespaces je Mandanten innerhalb eines Schemata für Apex Applikationen

- Historisierung je Tabelle mit abgestuften Anforderungen:
Aufzeichnung des Zeitpunkt und Benutzer der letzten Änderung je Zeile.
Aufzeichnung aller Änderungen mit Details je Spalte.
Erhalten von allen Versionen einer Zeile mit Zeitpunkt inklusive Blobs und gelöschten Zeilen.
Views für die Journale und Protokolle und Views zum Anzeigen der Daten zu einem bestimmten Zeitpunkt
oder im Vergleich zwischen heute und einem bestimmten Zeitpunkt.

Views für den inkrementellen Export aller Änderungen seit einen bestimmten Zeitpunkt.
Prozedur für die inkrementelle Synchronisation von remote Kopien via database link

- Erzeugung von Apex Application User Accounts je Schema die nur die nötigen Berechtigungen erhalten.

- Erzeugung von Menschen-Lesbaren Ansichten der Daten je Tabelle. Die seriellen Fremdschlüsselwerte werden über einfache Regeln
durch ihre lesbaren eindeutigen Beschreibungen ersetzt.

- Migration von Flat Table zu Index Organized Table

- Aktivierung der Index Kompression

- Index Monitoring und regelbasierte Erstellung und Löschung von Indexes für Foreign Keys.

-----------

wema_schema.Add_Schema
Installation von neuen Schemata mit eigenen Tablespaces für Daten und Indexes,
erweiterten Berechtigungen für die Verwendung des System Katalogs und Packages zur Neuanlage und Änderung von Datenbank Objekten.
Ebenfalls wird ein Schema Applikation Benutzer angelegt, der nur die benötigten Berechtigungen für die Ausführung von Apex Applikationen erhält.

wema_ctx
Package und Trigger zur Verwaltung eines eigenen Application Kontext mit aktuellen Mandanten, Benutzer-Daten und Zugriffszeitpunkt.

wema_vpd_conf
Package zur Konfiguration der Regeln für die Erzeugung von zusätzlichen Datenbank Objekten zur Aufzeichnung und Auswertung von Datenänderungen mit Benutzern und Zeitpunkten.
Für die Auswertung der Änderungs-Historie können schnelle Zugriffspfade bestimmt werden.

Optional kann ein eigenen Namespace zur Unterteilung je Mandanten konfiguriert werden.
Dazu wird jeder Tabelle eine neue Spalte namens WORKSPACE$_ID hinzugefügt, die zusammen mit einem eigenen Application Kontext die transparente Selektion für den aktuellen Mandanten mithilfe von Views ermöglicht.

Optional können bestimmte Tabellen so konfiguriert werden, dass die Daten darin immutable oder unveränderlich werden.
Dazu wird den Tabellen die neue Spalte DELETED_MARK hinzugefügt. Mit Hilfe von INSTEAD OF Triggern werden DELETE Statements dann so ausgeführt, dass anstatt zu löschen die Spalte DELETED_MARK auf einen berechneten Wert gesetzt wird.

wema_vpd_changelog
Package mit Tabellen, Views zur Aufzeichnung der Änderungs-Historie. Die Änderungs-Historie wird sehr kompakt mit den Primary Key Werten und dem BEVORE Image Werten in einem VARRAY und sehr schnell mit BULK Operationen aufgezeichnet.
Zugriffspfade für ausgewählte Domains (Primary und Foreign Keys) ermöglichen schnelle Auswertungen und historische Ansichten der Daten.

Für die Migration von vorher verwendeten Tabellen zur Aufzeichnung der Änderungen zu dem hier verwendeten Tablellen sind Beispiel Statements vorhanden.

wema_vpd und wema_vpd_pack
Package mit Views und materialized Views für die Analyse des System Katalogs und Prozeduren zur Erzeugung von zusätzlichen Datenbank Objekten. Bei der Ausführung werden entsprechend der Konfiguration ausgewählten Benutzer Tabellen mit den Spalten für den letzten Änderungs-Benutzer und Änderungs-Zeitpunkt ergänzt. Defaults und Trigger sorgen für die automatische Aktualisierung dieser Spalten.

Ausgewählten Tabellen werden die Spalten WORKSPACE$_ID und DELETED_MARK hinzugefügt.
Für diese Tabellen wird der Tabellenname so geändert, dass er mit _BT (für base table) endet und es werden Views mit den originalen Namen und Spalten und einem Filter für den aktuelle WORKSPACE$_ID und nicht gesetztem DELETED_MARK angelegt.

Alle Primary Key, Unique Key und Foreign Key Constraints und deren Indexes werden so geändert,
dass Zeilen mit identische Schlüsselwerte in mehrere Namespaces und als gelöscht markierte Zeilen im gleichen Schema gespeichert werden können. Sowohl flat tables als auch index organized tables können automatisch angepasst werden.

Für Ausgewählte Tabellen werden Trigger zur Aufzeichnung von Änderungen (DML Operationen) in der Tabelle WEMA_CHANGELOG_BT hinzugefügt.

Es werden INSTEAD OF Triggern für die neuen Views hinzugefügt, die UPDATE Operationen so umformulieren, dass Updates auf BLOB Spalten neue Zeilen erzeugen. Dies geschied, weil BLOB Columns nicht in die Tabelle WEMA_CHANGELOG_BT kopiert werden können. DELETE Operationen werden so umformuliert, dass anstatt zu Löschen die Spalte DELETED_MARK gesetzt wird.
Dabei werden alle Eigenschaften der FOREIGN KEYS wie ON DELETE CASCADE oder SET NULL rekursiv unterstützt.

Alle Tabellen wird ein FOREIGN KEY mit einer Referenz auf eine Zeile in der Tabelle USER_NAMESPACES hinzugefügt, die ein Löschen aller Daten eines Mandanten mit dem Löschen einer Zeile in der Tabelle USER_NAMESPACES ermöglicht.

Für historische Ansichten werden je Tabelle Views erzeugt, die alle historischen Ansichten der Daten und die historischen Ansichten zu einem bestimmten Zeitpunkt ermöglicht.

Für den Schema Applikation Benutzer [SCHEMA_NAME]_APP_USER werden Views, Synonyms und Grants so erzeugt, das die Apex Applikation alle normalen Datenzugriffe und Änderungen durchführen kann.

Die Prozedur wema_vpd.VPD_Prepare_All_Tables kann nach einer Änderung der Konfiguration oder von Schema Objekten erneut ausgeführt werden, um die Objekte nachträglich an geänderte Anforderungen anzupassen.

wema_vpd_mgr
Package mit Views für die Verwaltung der Namespaces. Namespaces können hiermit neu erzeugt, aus einem anderen Schema gefüllt, Als Kopie mit allen Daten oder nur mit Stammdaten dupliziert werden. Ebenfalls können Namespaces mit einen Template Namespace synchronisiert werden, um einen bestimmten Startzustand der Daten wiederherzustellen.
Ausserdem sind Prozeduren zum Vergleich von Namespaces, permanenten Löschen von als gelöscht markierten Daten und dem Löschen von Namespaces inklusive aller Daten und Change Log Einträge vorhanden.

import_utl
Package mit Views und materialized Views für die Analyse des System Katalogs und Prozeduren zur Erzeugung von Views und Triggers mit allen Details zum Exportieren und Importieren je Benutzer Tabelle.

Die Prozedur import_utl.Generate_Imp_Table erzeugt für eine Benutzer Tabelle je eine
- Import View mit instead of insert trigger mit allen einfachen Spalten der Benutzer Tabelle
und übersetzen Fremdschlüsselwerten. Die seriellen Schlüsselwerte werden durch ihre
lesbaren eindeutigen Beschreibungen ersetzt.
- History View ist eine Übersicht mit farblicher Hervorhebung der geänderten Werte zu einem bestimmenten Zeitpunkt.

- Import Table mit allen Spalten als Textfelder für den direkten Import aus einer .csv Datei.

- Import Check View mit Fehler Details je Spalte und als Übersicht mit farblicher Hervorhebung der fehlerhaften Werte.
Für alle Werte wird geprüft, ob eine Konvertierung zu internen Datentypen möglich ist und ob alle Check Constraints erfüllt sind. Für alle Fremdschlüssel wird geprüft, ob ein Lookup oder ggf. eine Neuanlage möglich ist.
- Import Differenz View mit farblicher Hervorhebung der unterschiedlichen Werte im Vergleich zwischen dem Datenbestand und den Daten eines Import Jobs

- Import nur der als fehlerfrei geprüften Zeilen aus dem Importtabelle in den Datenbestand.

- komfortable Erzeugung von Ansichten für die Überprüfung und den Vergleich
von Importdaten und Änderungen mit vielen Optionen wie Filter für Errors, Differences, New Rows, Missing Rows und Filter Combine (Union oder Intersect).

zip_utl
Package zum sehr schnellen Endpacken mit parallelen Prozessen und Erzeugen von bis zu 4 GB großen Zip Archiven für den Upload und Download von tausenden Dokumenten in einem Vorgang. Die Daten werden dazu aus/in zwei einfachen Tabellen, eine für recursive Verzeichnisse mit Parent Links und eine für den Dokumente-Inhalt mit Metadaten geladen. Das Package berücksichtigt auch das korrekte Dokument Datum mit Uhrzeit (Datum der letzten Änderung durch den Autor).