Apr 132014
 

In den letzten beiden Monaten war ich gut beschäftigt und bin kaum dazu gekommen, mich um die angekündigte Fortsetzung meiner "Datenbank mit Gradle"-Beiträge (Teil I und Teil II) zu kümmern. Diesmal soll es um die Parametrisierung der (PL/)SQL-Scripte von Gradle aus und um unterschiedliche Build-Umgebungen (z. B. Entwicklung, Test und Produktion) gehen. Ich möchte für jede Umgebung eine bestimmte Pluggable Database (PDB) verwenden, darauf einen Nutzer und eine Tabelle mit Indexen anlegen und wieder löschen.

Konfiguration mittels ConfigSlurper

Eine schöne Möglichkeit für hierarchisch und/oder thematisch organisierte Konfigurationen – die ich für meine verschiedenen Build-Umgebungen brauche – bietet Groovys ConfigSlurper, den ich noch von meinem ersten, ein paar Jahre zurückliegenden Groovy-Projekt kenne. Eine Recherche zu Gradle & ConfigSlurper liefert u. a. diesen Artikel mit verschiedenen Mail-Server-Konfigurationen, der mir als Vorlage für meine Lösung dient. Meine (ConfigSlurper-geeignete) Konfiguration sieht so aus:

// Default configuration values
jdbc {
    driver   = 'oracle.jdbc.OracleDriver'
    archive  = 'lib/ojdbc6.jar'
    url      = 'jdbc:oracle:thin:@localhost:1521:DBSID'
    username = 'sys as sysdba'
    password = '***'
}

// Various environments
environments {
    // A generic environment
    free {
        pdb {
            name     = "${System.console().readLine '\nEnter PDB name: '}"
            username = "${System.console().readLine '\nEnter name of PDB user to create: '}"
            password = "${System.console().readLine '\nEnter password of PDB user to create: '}"
        }
        jdbc {
            url      = "${System.console().readLine '\nEnter JDBC URL (jdbc:oracle:thin:@//<host>:<port>/<pdb>): '}"
        }
    }
    
    // the development PDB
    devel {
        pdb {
            name     = 'kermit'
            username = 'kermit01'
            password = 'h8ve-a-g00d-PW'
        } 
        jdbc {
            archive  = 'lib/ojdbc7.jar'
            url      = 'jdbc:oracle:thin:@//localhost:1521/kermit.blau-it.de'
        }
    }
    
    // the PDB for testing
    test {
        pdb {
            name     = 'gonzo'
            username = 'gonzo007'
            password = '3965723489093491'
        } 
        jdbc {
            url      = 'jdbc:oracle:thin:@//localhost:1521/gonzo.blau-it.de'
        }
    }
    
    // the production PDB
    prod {
        pdb {
            name     = 'piggy'
            username = 'piggy42'
            password = 'Ksed9D30x$'
        } 
        jdbc {
            url = 'jdbc:oracle:thin:@//localhost:1521/piggy.blau-it.de'
        }
    }
}

Die jdbc-Abschnitte enthalten die Parameter für den Aufbau der Datenbank-Verbindungen zu den verschiedenen PDB's. Der jdbc-Teil am Anfang der Datei enthält dabei Standard-Werte, die in den jdbc-Abschnitten der einzelnen Build-Umgebungen nicht wiederholt werden müssen, wohl aber überschrieben werden können (z. B. wird in der devel-Umgebung ein anderer JDBC-Treiber verwendet).

In den pdb-Abschnitten sind die PDB-Namen, die anzulegenden Nutzer und ihre Passwörter angegeben. Eine Besonderheit stellt die Umgebung free dar, bei der alle Parameter vom Nutzer eingegeben werden. Daran wird auch deutlich, dass es sich bei ConfigSlurper-Dateien nicht um passive Konfigurationsfiles handelt, sondern um "getarnte" Groovy-Scripts.

(PL/)SQL-Scripte, Bindevariablen und Konfigurationsparameter

Wie können die Konfigurationparameter nun aus den SQL-Scripten referenziert werden? Groovys Datenbank-Schicht bietet an sich bereits eine ganze Reihe Möglichkeiten, SQL-Statements mit Referenzen auf Groovy-Variablen zu versehen, analog zur Variablen/Funktionssubstitution in GStrings. Damit wären aber meine SQL-Scripte nicht mehr "standalone" nutzbar, also in verschiedenen Datenbank-Frontends (TOAD, Oracle SQL Developer …). In Gradle/Groovy sind die Parameter über eine hierarchische Syntax ansprechbar, z. B. env.pdb.name. In Oracle SQL können Bindevariablen allerdings keine Punkte enthalten. Meine Lösung dafür ist, statt der Punkte Unterstriche zu verwenden, was im folgenden Script zum "Öffnen" der aktuell verbundenen PDB so aussieht (s. Oracles Doku zum Umgang mit PDB's) :

Datei src/00.open_pdb.sql

-- Ensure that the PDB is open
declare
    pdbName     long := upper(:pdb_name);  -- references env.pdb.name in gradle
    openMode    long;
begin
    select name, open_mode into pdbName, openMode 
    from v$pdbs
    where name = NVL(pdbName, name);
	
    if openMode = 'MOUNTED' then 
        execute immediate 'alter pluggable database ' || pdbName || 
                          ' open read write';
    end if;
end;
/

Da ich in meinem letzten Beitrag die Datei zum Anlegen eines Nutzers 01.create_user.sql genannt habe, beginnt dieser Dateiname mit 00. Das Script zum Anlegen eines DB-Nutzers enthält 3 Teile:

  1. Droppen (Löschen) des DB-Nutzers (drop user)
  2. Anlegen des DB-Nutzers (create user)
  3. Anlegen einer Tabelle für den DB-Nutzer (create table) und Rechtevergabe (grant)

Datei src/01.create_user.sql

-- First drop the user if it already exists
declare
    ex_UserNotFound     exception; pragma exception_init(ex_UserNotFound, -1918);
begin
    execute immediate 'drop user ' || :pdb_username || ' cascade';   
exception
when ex_UserNotFound then null;
end; 
/

-- Create the user as a PDB user
begin
    execute immediate 'create user '      || :pdb_username 
                   || '  identified by "' || :pdb_password || '"'
                   || '  account unlock';
end;
/

-- Create a table for the user
declare
    pdbUser     long := :pdb_username;
begin
    execute immediate '
create table ' || pdbUser || '.COLORS (
    ID          number                      primary key,
    NAME        varchar2(40)                not null,
    RGB         number,
    CMYK        number,
    CREATED     timestamp with time zone    default current_timestamp   not null,
    CREATED_BY  varchar2(30)                default user                not null,          
    MODIFIED    timestamp with time zone    not null,          
    MODIFIED_BY varchar2(30)                not null,

    constraint COLORS_NAME_IDX unique (NAME) using index
)';

    execute immediate 'grant ALL on ' || pdbUser || '.COLORS to ' || pdbUser || ' with grant option';
end;
/

Hier werden die beiden anderen pdb-Konfigurationsparameter username und password genutzt. Damit das Binden der Variablen in PL/SQL möglichst unkompliziert ist, empfiehlt es sich, die Binde-Variablen auf lokale PL/SQL-Variable zu legen, so dass die Binde-Variablen nur jeweils einmal im Script auftauchen.

Um den Erfolg des create-user-Scripts zu kontrollieren kommt als nächstes ein Script mit einfachen select-Anweisungen.

Datei src/02.query_users.sql

select username, user_id, account_status, common 
from dba_users
where username = upper(:pdb_username)   -- Oracle user and object names are capitalized!
/
select object_type, object_name, status
from all_objects
where owner = upper(:pdb_username)
/

Mit dem nächsten beiden Scripts wird der DB-Nutzer wieder gelöscht und die PDB geschlossen.

Datei src/99.drop_user.sql

begin
    execute immediate 'drop user ' || :pdb_username || ' cascade';
end;
/

Datei src/9A.close_pdb.sql

-- Close the PDB
declare
    pdbName     long := upper(:pdb_name);
    openMode    long;
begin
    select name, open_mode into pdbName, openMode 
    from v$pdbs;
    
    if openMode != 'MOUNTED' then 
        execute immediate 'alter pluggable database ' || pdbName || ' close';
    end if;
end; 
/

Das neue Build-Script

Mein gradle-Buildscript enthält nun einen neuen Abschnitt zum Einlesen der Konfiguration mittels ConfigSlurper (ext ganz am Anfang) und eine Funktion zum Zusammenstellen der Bindevariablen für ein konkretes Script (collectBindVariables):

// Load the environment-specific properties
// Expects the following properties
// * ENV (optional): name of the environment to apply; defaults to "devel"
// * CONFIG_FILE (optional): path to the environment configuration file; 
//   defaults to "environments.groovy"
ext {
    def envName     = project.hasProperty('ENV') ? project.property('ENV') : 'devel'
    def configFile  = file(hasProperty('CONFIG_FILE') ? CONFIG_FILE : 'environments.groovy')
    project.ext.env = new ConfigSlurper(envName).parse(configFile.toURL())
    println "Using environment (ENV) $envName:"
    println env
}

// The task loads and registers a JDBC driver for database access
// It expects the following properties:
// * env.jdbc.driver:   for instance oracle.jdbc.OracleDriver
// * env.jdbc.archive:  path to the JDBC driver archive
// * env.pdb:           name of the pluggable database  
task loadDriver << {
    URLClassLoader loader = GroovyObject.class.classLoader
    println "Loading driver $env.jdbc.driver from archive $env.jdbc.archive for PDB $env.pdb."
    loader.addURL(file(env.jdbc.archive).toURL())
    java.sql.DriverManager.registerDriver(loader.loadClass(env.jdbc.driver).newInstance())
}

// Files containing SQL commands are organized in file tree and sorted by name
FileTree scriptTree = fileTree(dir: 'src', include: '**/*.sql')
List     sortedScripts

task sortScripts << {
    sortedScripts = scriptTree.collect { relativePath(it) }.sort()
    println "Executing ${sortedScripts.size} SQL files in the following order:" 
    sortedScripts.each { path -> println "  $path" }
}

// Split each file in the SQL file tree into SQL commands, detect the type
// (call or query) and execute them accordingly
// The task expects the following properties:
//  * DB_URL
//  * DB_USERNAME
//  * DB_PASSWORD
task callDatabase(dependsOn: [ loadDriver, sortScripts ] ) << {
    println "Connecting to database '$env.jdbc.url' with user '$env.jdbc.username' ..."
    def sql = groovy.sql.Sql.newInstance(env.jdbc.url, env.jdbc.username, env.jdbc.password)
    println '... connected'
    
    sortedScripts.each { path -> executeSQLCommands(sql, path) }
}

// Execute SQL commands in the given file
def executeSQLCommands(sql, path) {
    int commandCount = 0
    
    println "Processing '$path' ..."
    splitIntoSQLCommands(path).each { typedCmd ->
        def command  = typedCmd[0]
        def type     = typedCmd[1]
        def bindVars = collectBindVariables(command, type)
        
        print "Type $type, bind vars $bindVars:\n${command[0..Math.min(120, command.length() - 1)] + ' ...'}\n"
        
        switch (type) {
        case 'select':
            sql.eachRow(command, bindVars) { row -> println "  ${rowToString(row)}" }; break
        case 'call':
            sql.call   (command, bindVars); break
        case 'execute':
            sql.execute(command, bindVars); break
        default:
            assert false: "Unknown SQL statement type '$type' in:\n$command" 
        }
        commandCount++ 
    }  
    print "... $commandCount commands executed in '$path'.\n\n"
} 

// Split a SQL file into a set of SQL commands
// The method extracts text blocks terminated by a single / at the first
// position in a line and only whitespaces after it.
// It returns a list of pairs with the SQL statement as the first pair 
// part and the statement type as the second.
def splitIntoSQLCommands(path) {
    def result = []
    def sb     = new StringBuilder(8192)
    def type   = null
    
    file(path).eachLine { line ->
        if (line ==~ /\/\s*/) {
            def trimmed = sb.toString().trim()
            if (trimmed) {
                result << [ trimmed, type ]
            }  
            sb.setLength(0)
            type = null
        } else {
            if ( ! type) {
                type = getCommandType(line)
            }
            sb << line << '\n'
        }
    }
    return result
}

// Format a row into a string
def rowToString(row) {
    def result   = new StringBuffer()
    def metaData = row.getMetaData()
    
    for (i in 1..metaData.getColumnCount()) {
        if (i > 1) result << ', '  
        result << metaData.getColumnName(i) << ': "' << row.getAt(i - 1).toString() << '"'
    }
    return result.toString()
}

// Detect if the given SQL command is a select, a stored procedure call
// or a DDL/DML SQL statement.
def getCommandType(sqlLine) {
    def matcher    = sqlLine =~ /^\s*(\w+).*/
    def firstToken = matcher ? matcher[0][1].toLowerCase() : null
    
    if (matcher) {
        println "Found match '${matcher[0]}' -> '$firstToken'"
    }
    if ( ! firstToken) {
        firstToken
    } else if (firstToken == 'select') {
        'select'
    } else if (firstToken in ['declare', 'begin' ]) {
        'call'
    } else {
        'execute'
    }
}

// Find the bind variables Oracle style: 
//      select * from user_objects where object_name = :NAME
// and associate the approbriate environment values with it.
def collectBindVariables(sql, type) {
    def isQuery = type == 'select'
    def vars    = isQuery ? [:] : []
    def matcher = sql =~ /:(\w+)/
    
    matcher.each { nameSeq ->
        def var = env
        nameSeq[1].split('_').each { part -> var = var."$part" }
        
        // need bind vars as ordinary strings to avoid SQLException: invalid column type
        // (happens with a GString).
        if (isQuery) {
            vars[nameSeq[1]] = var.toString()   
        } else { 
            vars << var.toString()              
        }
    }
    return vars
}

Die Routine collectBindVariables sucht nach Bindeausdrücken (regulärer Ausdruck /:(\w+)/ bedeutet "Doppelpunkt gefolgt von mindestens einem Wort-Zeichen"). Gefundene Ausdrücke werden an den Unterstrichen geteilt und daraus der gradle-Variablenausdruck gebildet (Closure {part -> var = var."$part" }). Je nachdem, ob es sich um ein select oder eine andere SQL-Anweisung handelt, liefert die Routine eine Map mit Bindevariablen-Name -> Wert-Einträgen oder eine Liste mit den reinen Werten. Das wird nötig durch die unterschiedlichen Parameter an Sql.eachRow für selects einseits und Sql.call bzw. Sql.execute andererseits (ok, nicht jeder wird glücklich über Routinen mit so variablen Return-Typen sein, aber das Script bleibt kurz – 160 Zeilen – und ohne Wiederholungen a la collectBindVariablesAsList + collectBindVariablesAsMap).

Ein nötiger Trick zum Schluss

Leider funktioniert das Script so nicht: es scheitert in SQL.eachRow, weil selbst das derzeit modernste Gradle 1.11 mit Groovy 1.8.6 kommt, die Übergabe einer Map mit den Bindevariablen an eachRow aber erst mit Groovy 1.8.7 eingeführt wurde (s. Groovy-API-Dokumentation, Class Sql). Ich habe deshalb – vermutlich seeehr illegal – das Archiv $GRADLE_HOME/lib/groovy-all-1.8.6.jar gegen groovy-all-1.8.9 von Maven Central ausgetauscht, natürlich unter dem "falschen" Namen groovy-all-1.8.6.jar. Das Ergebnis kann man so kontrollieren:

heiko@nb:/projects/dbgradle$ gradle --version

------------------------------------------------------------
Gradle 1.11
------------------------------------------------------------

Build time:   2014-02-11 11:34:39 UTC
Build number: none
Revision:     a831fa866d46cbee94e61a09af15f9dd95987421

Groovy:       1.8.9
Ant:          Apache Ant(TM) version 1.9.2 compiled on July 8 2013
Ivy:          2.2.0
JVM:          1.7.0_51 (Oracle Corporation 24.45-b08)
OS:           Linux 3.5.0-48-generic amd64

heiko@nb:/projects/dbgradle$

 

 Posted by at 16:00

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)