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:
- Droppen (Löschen) des DB-Nutzers (drop user)
- Anlegen des DB-Nutzers (create user)
- 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$