Oracle-Datenbank: Teamwork mit Bordmitteln

Synchronisieren Sie mit DDL-Triggern gemeinsames Arbeiten durch Ein -und Ausschenken von Objekten. Und das sogar für beliebige Datenbanktools.

Viele namhafte Tools zur Datenbankentwicklung bieten Funktionen zur Unterstützung von Entwicklerteams. So können Sie Datenbankobjekte zur Bearbeitung ausschenken. Andere Entwickler, können solange keine Änderungen an diesem Objekt mehr vornehmen, bis dieses wieder eingecheckt wurde. Leider funktioniert das meist nicht werkzeugübergreifend, da diese Tools oft eigene Tabellen für die Verwaltung der Bearbeitungszustände der Datenbankobjekte nutzen.

In diesen Beitrag möchte ich Ihnen eine einfache, aber effektive Möglichkeit zeigen, wie Sie so eine Funktion mit Bordmitteln der Oracle Datenbank selber entwickeln können. Vor allem ist bei der vorgestellten Lösung beachtenswert, dass Sie werkzeugübergreifend funktioniert.

Datenmodell

Benötigt werden zwei Tabellen. Über die Tabelle ddl_object_state wird der Bearbeitungsstand der Datenbankobjekte verwaltet. Sobald ein Entwickler ein Objekt bearbeiten möchten, blockt er dieses. Dabei wird ein Datensatz in dieser Tabelle eingefügt. Ist die Bearbeitung abgeschlossen, so wird das Objekt wieder freigegeben und der zugehörige Einträge entfernt.


CREATE TABLE ddl_object_state (
          object_type VARCHAR2(50 BYTE),
          object_owner VARCHAR2(32 BYTE),
          object_name VARCHAR2(32 BYTE), 
          changed_on DATE,
          changed_by VARCHAR2(32 BYTE)
)
/

Listing 1: Tabelle ddl_object_state

In der zweiten Tabelle ddl_object_state_hist wird beim Ein- und Auschecken neben den Statusinformationen auch ein DDL-Extrakt des bearbeiteten Objektes eingetragen. So dass Sie auf einen Stand zu Beginn und zum Ende der Bearbeitung zurückgreifen können.


CREATE TABLE ddl_object_state_hist
(
   object_type VARCHAR2 (50 BYTE),
   object_owner VARCHAR2 (32 BYTE),
   object_name VARCHAR2 (32 BYTE),
   status_code VARCHAR2 (20 BYTE),
   changed_on DATE,
   changed_by VARCHAR2 (32 BYTE),
   ddl_sql CLOB
)
/

Listing 2: Tabelle ddl_object_state_hist

PL/SQL Package ddl_util

Das PL/SQL Package fasst die benötigten Prozeduren zusammen.

PROCEDURE lock_object (
     p_object_type VARCHAR2,
     p_owner VARCHAR2,
     p_name VARCHAR2,
     p_trace_hist BOOLEAN DEFAULT true
  ) IS
    l_c NUMBER;
    l_locked_by VARCHAR2(32);
    l_changed_on DATE;
    l_ddl_extract CLOB;
BEGIN
    SELECT
      COUNT(*)
     INTO l_c
    FROM ddl_object_state
    WHERE object_owner = p_owner
       AND object_name = p_name;

  IF l_c = 0 THEN
       INSERT INTO ddl_object_state (
          object_type,
          object_owner,
          object_name,
          changed_on,
          changed_by )
       SELECT
          p_object_type,
          p_owner object_owner,
          p_name object_name,
          SYSDATE changed_on,
          ora_login_user changed_by
       FROM dual;

    IF p_trace_hist THEN
       l_ddl_extract := dbms_metadata.get_ddl(p_object_type,p_name);

       INSERT INTO ddl_object_state_hist (
          object_type,
          object_owner,
          object_name,
          ddl_sql,
          status_code,
          changed_on,
          changed_by )
       SELECT
          p_object_type,
          p_owner object_owner,
          p_name object_name,
          l_ddl_extract,
          'LOCK',
          SYSDATE changed_on,
          ora_login_user changed_by
       FROM dual;
    END IF;
 ELSE
      SELECT
          changed_by,
          changed_on
      INTO
        l_locked_by,
        l_changed_on
      FROM ddl_object_state
      WHERE object_owner = p_owner
        AND object_name = p_name;

     IF l_locked_by <> ora_login_user THEN
        raise_application_error(-20001,'Object was already locked by '
             || l_locked_by
             || ' at '
             || TO_CHAR(l_changed_on,'dd.mm.yyyy HH24:Mi:ss') );
     END IF;
 END IF;

END;

Listing 3: Procedure lock_object

Die Prozedur lock_object erhält als Parameter beim Aufruf den Objekttyp, den Besitzer des Objektes und den Namen des Objektes.

Als erstes prüft die Prozedur, ob das Objekt bereits von einem anderen User ausgescheckt wurde. Ist das der Fall wird ein application_error -20001 ausgelöst. Wenn das Objekt nicht in Bearbeitung ist, trägt die Prozedur diese in der Tabelle ddl_object_state ein und extrahiert eine DDL-Anweisung des Objektes. Diese wird dann in der Tabelle ddl_object_state archiviert.

PROCEDURE unlock_object (
     p_object_type VARCHAR2,
     p_owner VARCHAR2,
     p_name VARCHAR2,
     p_trace_hist BOOLEAN DEFAULT true,
     p_secure_mode BOOLEAN DEFAULT true) 
IS
     l_c NUMBER;
     l_locked_by VARCHAR2(32);
     l_changed_on DATE;
     l_ddl_extract CLOB;
BEGIN
     SELECT COUNT(*)
       INTO l_c
     FROM ddl_object_state
     WHERE object_owner = p_owner
       AND object_name = p_name;

 IF l_c = 1 THEN
     SELECT
       changed_by,
       changed_on
     INTO
       l_locked_by,
       l_changed_on
     FROM ddl_object_state
     WHERE object_owner = p_owner
       AND object_name = p_name;

    IF p_secure_mode AND l_locked_by <> ora_login_user  THEN
        raise_application_error(-20002,'You have no permissions in secure mode to unlock the object. The Object was locked by '
              || l_locked_by
              || ' at '
              || TO_CHAR(l_changed_on,'dd.mm.yyyy HH24:Mi:ss') );
    END IF;

    DELETE ddl_object_state
      WHERE object_owner = p_owner
        AND object_name = p_name;

    IF p_trace_hist THEN
      l_ddl_extract := dbms_metadata.get_ddl(p_object_type,p_name);

      INSERT INTO ddl_object_state_hist (
            object_type,
            object_owner,
            object_name,
            ddl_sql,
            status_code,
            changed_on,
            changed_by )
      SELECT
            p_object_type,
            p_owner object_owner,
            p_name object_name,
            l_ddl_extract,
            'UNLOCK',
            SYSDATE changed_on,
            ora_login_user changed_by
      FROM dual;
   END IF;
 
 END IF;

END;

Listing 4: Procedure unlock_object

Um ein Datenbankobjekt nach der Bearbeitung wieder frei zu geben, nutzen Sie die Prozedur unlock_object. Diese prüft im Secure Mode, ob Sie im Vorfeld das entsprechende Objekt auch selber ausgelockt haben. Ist das nicht der Fall, wird der Applikationsfehler -20002 ausgelöst. Ein von Ihnen ausgelocktes Objekt wird durch das Löschen des zugehörigen Eintrags in der Tabelle ddl_object_state wieder zur Bearbeitung frei gegeben. In der Tabelle ddl_object_state_hist wird wieder ein DDL-Extrakt gespeichert.

Objekte, die von einem anderen Entwickler als Sie selbst geblockt wurden, können Sie durch das Setzen des Parameters p_secure_mode = false wieder freigegeben. Auch dabei wird ein Historiendatensatz erzeugt. Anschließend können Sie so den Besitz durch Aufrufen von lock_object übernehmen.

PROCEDURE check_object_state (
             p_object_type VARCHAR2,
             p_owner VARCHAR2,
             p_name VARCHAR2
           );

Listing 5: Procedure check_object_state

Mit der dritten Procedur check_object_state können Sie einfach den Bearbeitungsstand eine Datenbankobjektes überprüfen.

Wie bereits erwähnt, speichern die Prozeduren beim Ein- und Auschecken jeweils ein DDL-Statements in der Historientabelle. Wenn Sie eine genaue Protokollierung aller Anpassungen benötigen, möchte ich Sie auf den Artikel Oracle Datenbankentwicklung: DDL-Trigger zur Auditierung von Strukturänderungen hinweisen.

DDL-Trigger

Anschließend benötigen Sie noch zwei DDL-Trigger, die Sie in dem Entwicklungsschema anlegen müssen.


CREATE OR REPLACE TRIGGER trg_ddl_util_before 
     BEFORE ALTER OR DROP 
    ON DATABASE 
DECLARE 
BEGIN
      IF ora_dict_obj_name NOT IN (
               'TRG_DDL_UTIL_BEFORE',
               'TRG_DDL_UTIL_AFTER',
               'DDL_UTIL' ) 
          AND ora_login_user NOT IN (
               'SYS',
               'SYSTEM' ) 
      THEN
         ddl_util.lock_object(ora_dict_obj_type,ora_dict_obj_owner,ora_dict_obj_name);
      END IF;
END;
/

Listing 6: Trigger trg_ddl_util_before

Der Trigger trg_ddl_util_before wird bei ALTER- und DROP-Anweisungen ausgeführt. Für einen Create-Befehl macht dieser Zeitpunkt keinen Sinn, da das Objekt in der Datenbank noch nicht vorliegen kann.

Bei der Verwendung von DDL-Triggern sollten Sie besondere Vorsicht walten lassen. Da diese Trigger auch bei Änderungen an sich selbst gefeuert werden, kann es vorkommen, dass man bei einer fehlerhaften Programmierung evtl. keine Änderungen mehr an den Triggern vornehmen kann. Aus diesem Grund prüft der Trigger zu Beginn, ob Änderungen an ihm selbst bzw. an dem Trigger trg_ddl_util_after ausgeführt werden sollen. Des Weiteren werden die Accounts das und system von der Verarbeitung ausgenommen. Allerdings würde auch ein invaliden Package ddl_util zu Problemen führen können.

Die eigentlich Logik des Triggers besteht dann nur noch im Aufruf der Prozedur ddl_util.lock_object. Diese prüft, ob das Objekt von einem anderen Entwickler zur Bearbeitung ausgescheckt wurde. In diesem Fall wird wie bereits erwähnt eine Exception gefeuert und so die Änderungen an dem Objekt dem aktuellen Nutzer verwährt. Bei Objekten, die noch nicht ausgescheckt sind, wird das über diesen Trigger automatisch erledigt.

Alternativ kann es aber auch durchaus sinnvoll sein, dass Ausschecken eines Objektes vor der Bearbeitung manuell durch das Aufrufen von  ddl_util.lock_object durchzuführen. So erspart man sich, dass Änderungen beim Speichern durch den Trigger erst im Nachhinein verworfen werden.

CREATE OR REPLACE TRIGGER trg_ddl_util_after 
    AFTER CREATE OR DROP 
 ON DATABASE 
DECLARE 
BEGIN
    IF ora_dict_obj_name NOT IN (
              'TRG_DDL_UTIL_BEFORE',
              'TRG_DDL_UTIL_AFTER',
              'DDL_UTIL')
    AND ora_login_user NOT IN (
              'SYS',
              'SYSTEM' )
    THEN
        IF ora_sysevent = 'CREATE' THEN
            ddl_util.lock_object(ora_dict_obj_type,ora_dict_obj_owner,ora_dict_obj_name);
       END IF;

       IF ora_sysevent = 'DROP' THEN
           ddl_util.unlock_object(ora_dict_obj_type,ora_dict_obj_owner,ora_dict_obj_name,false,true);
       END IF;

    END IF;
END;
/

Listing 7: Trigger trg_ddl_util_after

Wird nun ein neues Objekt angelegt, kann dieses nicht im Vorfeld geblockt werden. In so einem Fall kommt nun der zweite Trigger trg_ddl_util_after ins Spiel. Dieser prüft ob ein Create-Statement ausgeführt wurde. Wenn das der Fall war, wird das neu erstellte Datenbankobjekt automatisch ausgecheckt und der DDL-Extrakt archiviert.

Bei einem Drop-Befehl verhält es sich etwas anders. Hier existiert das Objekt zum Ausführungszeitpunkt ( AFTER ) des Triggers nicht mehr. Es muss aber noch aus der Tabelle ddl_objet_state entfernt werden.

Abbildung 1: Übersicht ddl_util

In Abbildung 1 sehen Sie alle beschriebenen Objekte.

Drop …

Jetzt habe ich der Einfachheit halber Ihnen noch ein kurzes Skript hier eingefügt, mit dem Sie die Tabellen, Trigger und das Package wieder los werden können.


DROP TRIGGER trg_ddl_util_before;
DROP TRIGGER trg_ddl_util_after;
DROP PACKAGE ddl_util;
DROP TABLE ddl_object_state;
DROP TABLE ddl_object_state_hist;

Beispiel

Im Folgenden möchte ich Ihnen die Nutzung der beschrieben Methode anhand einiger kleiner Beispiele demonstrieren. Dazu wurden in dem Schema MY_PROJECTS die beiden Tabellen, das Package und die beiden Trigger angelegt. diesen Account wurde eine Verbindung im SQL Developer hergestellt.

Dann gibt es einen weiteren Account namens DEVELOPER1, welcher mit DBA-Rechten versehen wurde. Der DEVELOPER1  ist via. sqlplus mit der Datenbank verbunden.

Tabelle T1 anlegen

Zu Beginn soll eine Tabelle erzeugt werden.

Abbildung 2: Tabelle T1 anlegen

Wie Sie der Abbildung 2 entnehmen können, wurde die Tabelle T1 ohne Probleme erzeugt. Dabei wurde der Trigger trg_ddl_util_after ausgeführt und ein Lock-Eintrag in der Tabelle ddl_object_state eingefügt.

Abbildung 3: Datenbanktabelle T1 für ausgecheckt

Tabelle T2 erweitern

Jetzt soll der DEVELOPER1 dieser Tabelle eine neue Spalte hinzufügen.

Abbildung 4 : Spalte in T1 ergänzen

Wie zu erwarten war, wird dieser Vorgang mit der Exception ORA-20001 durch den Trigger trg_ddl_util_before unterbunden. Der Meldung können Sie entnehmen, dass die Tabelle durch den User MY_PROJECTS am 17.06.2018 um 20:59:17 gesperrt wurde.

Abbildung 5: Objekt einchecken

Erst wenn der Benutzer MY_PROJECTS die Tabelle wieder zur Bearbeitung frei gibt, kann der DEVELOPER1 die Spalte ergänzen.

Abbildung 6: Spalte hinzufügen

Dies hat wiederum zu Folge, dass die Tabelle jetzt durch den User DEVELOPER1 ausgecheckt wurde.

Tabelle T1 löschen

Setzt nun der User MY_PROJECTS eine Drop Anweisung für die Tabelle T1 ab, wird auch diese mit einer entsprechenden Fehlermeldung abgebrochen.

Abbildung 7: Drop der Tabelle nicht möglich

Möchten Sie jetzt aber die Tabelle trotzdem löschen und die Sperre durch den DEVELOPER1 übergehen, so können Sie das durch den Aufruf der Prozedur unlock_objects mit folgenden Parametern tun.


BEGIN
  DDL_UTIL.UNLOCK_OBJECT(
         P_OBJECT_TYPE = 'TABLE',
         P_OWNER =       'MY_PROJECTS',
         P_NAME =        'T1',
         P_TRACE_HIST =  true,
         P_SECURE_MODE = false
        );
   commit;
END;

Änderungshistorie

Die Trigger bzw. das PL/SQL Package ddl_util sorgen beim Ein- und Auschecken der Datenbankobjekte auch dafür, dass ein DDL-Extrakt in der Tabelle ddl_object_state_hist archiviert wird. So können Sie auf einfache Weise auf ältere Entwicklungsstände zugreifen.

Abbildung 8: Änderungshistorie

Download

Abschließend habe ich Ihnen ein DDL-Script zum Anlegen der beschriebenen Objekte an diesen Beitrag angefügt. Die Verwendung erfolgt aber auf eigene Gefahr.

Oracle-Datenbank: Teamwork mit Bordmitteln Download ddl_util.sql

Dieses DDL-Sript ist im Rahmen des Beitrags Oracle-Datenbank: Teamwork mit Boardmitteln entstanden.