Datenbank-Schema “bauen” mit Gradle – Teil II

Im meinem letzten Blog habe ich gezeigt, wie man mit Gradle & Groovy recht schnell eine Datenbank ansprechen kann. Heute möchte ich die Datenbank-Parameter und die auszuführenden SQL-Befehle an das Buildscript übergeben. Eine einfache Möglichkeit zur Parametrisierung des Builds sind Properties, die als -P-Optionen auf der Kommandozeile übergeben werden können oder in der Datei gradle.properties (s. dazu "Gradle properties and system properties" im Gradle User's Guide):

JDBC_DRIVER_NAME = oracle.jdbc.OracleDriver
JDBC_ARCHIVE_PATH = lib/ojdbc7.jar

DB_URL      = jdbc:oracle:thin:@localhost:1521:DBSID
DB_USERNAME = system
DB_PASSWORD = ***

Die so vereinbarten Properties können in Gradle-Scripts wie normale Variablen verwendet werden. Mein Buildscript sieht nach Ersatz der Literale gegen die Properties so aus:

// The task loads and registers a JDBC driver for database access
// It expects the following properties:
// * JDBC_DRIVER_NAME:   for instance oracle.jdbc.OracleDriver
// * JDBC_ARCHIVE_PATH:  path to the JDBC driver archive
task loadDriver {
    URLClassLoader loader = GroovyObject.class.classLoader
    loader.addURL(file(JDBC_ARCHIVE_PATH).toURL())
    java.sql.DriverManager.registerDriver(loader.loadClass(JDBC_DRIVER_NAME).newInstance())
}

// Connect to the database
// The task expects the following properties:
//  * DB_URL
//  * DB_USERNAME
//  * DB_PASSWORD
task callDatabase(dependsOn: loadDriver ) {
    println "Connecting to database '$DB_URL' with user '$DB_USERNAME' ..."
    def sql = groovy.sql.Sql.newInstance(DB_URL, DB_USERNAME, DB_PASSWORD)
    println '... connected'
}

Der Aufruf von gradle bringt:

heiko@nb:/projects/dbgradle$ gradle callDatabase
Connecting to database 'jdbc:oracle:thin:@localhost:1521:DBSID' with user 'system' ...
... connected
:loadDriver UP-TO-DATE
:callDatabase UP-TO-DATE

BUILD SUCCESSFUL

Total time: 2.442 secs

Ich teste noch, ob die Übergabe der Properties über die Kommandozeile funktioniert, indem ich ich in der Datei gradle.properties die Property JDBC_DRIVER_NAME auskommentiere. Der einfache Aufruf bring zunächst diesen Fehler:

heiko@nb:/projects/dbgradle$ gradle callDatabase

FAILURE: Build failed with an exception.

* Where:
Build file '/data40/projects/DatabaseWithGradle/part-2/build.gradle' line: 8

* What went wrong:
A problem occurred evaluating root project 'part-2'.
> Could not find property 'JDBC_DRIVER_NAME' on task ':loadDriver'.

* Try:
Run with --stacktrace option to get the stack trace. Run with --info or --debug option to get more log output.

BUILD FAILED

Total time: 2.147 secs

Die Übergabe der Property über den -P-Schalter führt dann wieder zu einem erfolgreichen build:

heiko@nb:/projects/dbgradle$ gradle -PJDBC_DRIVER_NAME=oracle.jdbc.OracleDriver callDatabase
Connecting to database 'jdbc:oracle:thin:@localhost:1521:DBSID' with user 'system' ...
... connected
:loadDriver UP-TO-DATE
:callDatabase UP-TO-DATE

BUILD SUCCESSFUL

Total time: 2.382 secs

Jetzt möchte ich mehrere SQL-Scripts der Reihe nach ausführen. Dazu lege ich einen Folder src an, in dem ich meine Scripte mit einer führenden Ordnungsnummer versehen ablege. Zunächst möchte ich nur einen Datenbank-Nutzer anlegen, in einem nachfolgenden select prüfen, dass er existiert, und den Nutzer dann wieder entfernen. Da ich eine funkelnagelneue mandantenfähige (Multitenant) Oracle 12c-Datenbank benutze, die zwischen Container Database (CDB) und Pluggable Database (PDB) unterscheidet, kann man nicht mehr einfach create user GONZO identified by *** aufrufen, sondern muss sich entweder für eine PDB entscheiden (alter session set container = MY_PDB) oder der anzulegende Nutzername muss mit C## anfangen (s. Overview to the Multitenant architecture).

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 C##GONZO';   
exception
when ex_UserNotFound then null;
end; 
/

-- Create the user as a CDB user
create user C##GONZO 
    identified by "h8ve-a-g00d-PW" 
    account unlock
/

Datei src/02.query_users.sql

select username, user_id, account_status, common 
from cdb_users
where username = 'C##GONZO'
/
select object_type, object_name 
from cdb_objects
where owner = 'C##GONZO'
/

Datei src/99.drop_user.sql

drop user C##GONZO
/

Diese Dateien müssen nun in der richtigen Reihenfolge ausgeführt werden. Da eine Datei mehrere SQL-Kommandos enthalten kann, müssen diese Kommandos extrahiert werden; außerdem muss festgestellt werden, ob es sich um eine Query (select), eine andere SQL-Anweisung (DML, DDL …) oder um einen Stored-Procedure-Call handelt. Ich verwende zunächst einen Gradle-FileTree, um die Dateien im Build anzuziehen. Der FileTree wird anschliessend in einer eigenen Task sortiert:

// The task loads and registers a JDBC driver for database access
// It expects the following properties:
// * JDBC_DRIVER_NAME:   for instance oracle.jdbc.OracleDriver
// * JDBC_ARCHIVE_PATH:  path to the JDBC driver archive
task loadDriver {
    URLClassLoader loader = GroovyObject.class.classLoader
    loader.addURL(file(JDBC_ARCHIVE_PATH).toURL())
    java.sql.DriverManager.registerDriver(loader.loadClass(JDBC_DRIVER_NAME).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 '$DB_URL' with user '$DB_USERNAME' ..."
    def sql = groovy.sql.Sql.newInstance(DB_URL, DB_USERNAME, DB_PASSWORD)
    println '... connected'
}

Der nächste Build-Lauf bringt dann dieses Ergebnis:

heiko@nb:/projects/dbgradle$ gradle callDatabase
Executing 3 SQL files in the following order:
  src/01.create_user.sql
  src/02.query_users.sql
  src/99.drop_user.sql
Connecting to database 'jdbc:oracle:thin:@localhost:1521:DBSID' with user 'system' ...
... connected
:loadDriver UP-TO-DATE
:sortScripts UP-TO-DATE
:callDatabase UP-TO-DATE

BUILD SUCCESSFUL

Total time: 6.356 secs

Für die SQL-Extraktion aus den Dateien und die Analyse des Befehlstyps verwende ich normale Groovy-Methoden, die man im Buildscript zwischen Gradle-Build-Anweisungen notieren kann (s. Organizing Build Logic im Gradle User Manual). Das fertige build.gradle sieht dann so aus:

// The task loads and registers a JDBC driver for database access
// It expects the following properties:
// * JDBC_DRIVER_NAME:   for instance oracle.jdbc.OracleDriver
// * JDBC_ARCHIVE_PATH:  path to the JDBC driver archive
task loadDriver {
    URLClassLoader loader = GroovyObject.class.classLoader
    loader.addURL(file(JDBC_ARCHIVE_PATH).toURL())
    java.sql.DriverManager.registerDriver(loader.loadClass(JDBC_DRIVER_NAME).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 '$DB_URL' with user '$DB_USERNAME' ..."
    def sql = groovy.sql.Sql.newInstance(DB_URL, DB_USERNAME, DB_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 { command ->
        def type = getCommandType(command)
        println "Type $type:\n$command"
        switch (type) {
        case 'select':
            sql.eachRow(command) { row -> println "  ${rowToString(row)}" }
            break
        case 'call':
            sql.call(command)
            break
        default:
            sql.execute(command)
        }
        commandCount++ 
    }  
    println "... $commandCount commands executed in '$path'."
} 

// Split a SQL file into a set of SQL commands
String[] splitIntoSQLCommands(path) {
    def result = []
    def sb     = new StringBuilder(8192)
    
    file(path).eachLine { line ->
        if (line.length() > 0 && line[0] == '/') {
            def cmd = sb.toString().trim()
            if ( ! cmd.isEmpty()) {
                result << cmd
            }  
            sb.setLength(0)
        } else if ( ! line.trim().startsWith('--')) {
            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.
// NOTE: we should tolerate leading comments in future versions
def getCommandType(sql) {
    def pattern    = ~/\s/
    def matcher    = pattern.matcher(sql)
    def firstToken = matcher.find() ? sql.substring(0, matcher.start()).toLowerCase() : ""
    
    if (firstToken == 'select') {
        'select'
    } else if (firstToken in ['declare', 'begin' ]) {
        'call'
    } else {
        'execute'
    }
}

Die Groovy-Methoden executeSQLCommands, splitIntoSQLCommands, rowToString und getCommandType können auch "getypter" notiert werden, also z. B. "String getCommandType(String sql)"; ebenso können die lokalen Variablen wie pattern und matcher mit ihren Klassen statt des lakonischen def vereinbart werden. Ich bevorzuge die knappere script-artige Form, während die eher java-artige, getypte Variante andere Entwicklern beim Verständnis und der Modifikation des Scripts helfen kann.

Jetzt habe ich also ein Grundgerüst für ein Schema-Setup. Da das Buildscript beliebigen SQL-Code ausführt, kann man natürlich auch andere Datenbank-Aufgaben wie Anlegen von Tabellen mit ihren  Indexen und Constraints, Views oder die Installation von PL/SQL-Artefakten (Packages, Prozeduren, Funktionen, Trigger) damit erledigen.

Allerdings sind die SQL- bzw. PL/SQL-Scripte noch nicht parametrisierbar. Um beispielsweise Entwicklungs-, Test- und Produktiv-Datenbanken einrichten zu können, müssten alle Scripts für jede Umgebung kopiert und angepasst werden – kein anstrebenswerter Weg, schon gar nicht mit Gradle. Wie also kann man elegant Parameter an andere Scripte (bei mir SQL und PL/SQL) übergeben und verschiedene Umgebungen (Entwicklung, Test etc.) defininieren? Damit werde ich mich im nächsten Blog beschäftigen.