Ein Beispiel für ein zentrales, wiederverwendbares Build-File, dass u.a. die Reihenfolge-Problematik löst, ist dieses (feel free to use it :-):
[xml]
<?xml version="1.0" encoding="UTF-8"?>
<!–
load.xml
Copyright 2011 Heiko Blau
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
–>
<project name="bluePLSQL" default="help">
<description><![CDATA[
This is a generic buildfile to load SQL and PL/SQL sources into a Oracle
database.
Run "ant -f ${ant.file} help"
]]>
</description>
<!– A few constants that You can override from the command line –>
<property environment="env"/>
<property name="DRIVER_CLASS" value="oracle.jdbc.OracleDriver"/>
<property name="DRIVER_CLASSPATH" value=""/>
<property name="DRIVER_PATTERN" value="ojdbc?.jar"/>
<property name="SOURCES.DEF" value="*.sql,*.plsql"/>
<property name="SOURCES.TXT" value="sources.txt"/>
<!–
Sometimes one needs an if or a loop, a variable and others things not
available in plain old ant.
–>
<taskdef resource="net/sf/antcontrib/antlib.xml" classpath="${ant.home}/lib/ant-contrib.jar"/>
<!– Targets are located in a shared buildfile –>
<target name="help">
<echo><![CDATA[
This is a generic buildfile to load SQL and PL/SQL source files from the
current working directory into a Oracle database (${user.dir}).
Targets that can be build using this file (${ant.file}):
check: Verifies that all mandatory parameters are given and that the
database connection be established.
load: Loads all files in ${user.dir}.
help: Display this message.
It expects the following properties from the caller (-D<prop>=… on the
command line):
USER: a user/schema name to logon to
PASS: the password
URL: the JDBC connection string or the TNS name (requires OCI shared
libs on java.library.path)
Optional properties:
DRIVER_CLASS: full java class name for the Oracle JDBC driver
implementation; set this only if the default doesn’t
work.
DRIVER_CLASSPATH: path to the Oracle jdbc archive; if not in the class
path the script attempts to detect the archive using
the environment variable ORACLE_HOME, so use this
only if the detection mechanism doesn’t work.
The script looks for a file "sources.txt" in the current directory. If it
exists every line is treated as an comma-separated list of file name
pattern in ant style (using also the "**/*.sql" syntax for recursive
search) describing the files to load. This file therefore provides also a
way to determine the load order of the files – that’s important to avoid
compilation errors occuring when the dependencies of SQL and PL/SQL are
not taken in account.
Examples:
1. Loads all files specified in sources.txt/ all **/*.sql and **/*.plsql
into the database schema gonzo@mydb
> ant -f /path/to/this/load.xml -DUSER=gonzo -DPASS=not-me -DURL=java:oracle:thin@my.db.server:1521:mydb load
2. Checks if the mydb database on my.db.server is available
> ant -f /path/to/this/load.xml -DUSER=gonzo -DPASS=not-me -DURL=java:oracle:thin@my.db.server:1521:mydb check
]]>
</echo>
</target>
<!–
Verifies the mandatory parameters and establishes the database
connection.
–>
<target name="check">
<fail unless="USER">No user name given (-DUSER=…)</fail>
<fail unless="PASS">No password given (-DPASS=…)</fail>
<fail unless="URL">No database connection URL or TNS name given (-DURL=…)</fail>
<!– is Oracle’s JDBC driver archive available? –>
<if><equals arg1="${DRIVER_CLASSPATH}" arg2=""/>
<then>
<if><isset property="env.ORACLE_HOME"/>
<then>
<echo>Looking for JDBC driver archive in "${env.ORACLE_HOME}" using pattern "${DRIVER_PATTERN}" …</echo>
<for param="ojdbc-jar">
<path>
<sort>
<fileset dir="${env.ORACLE_HOME}/jdbc/lib" includes="${DRIVER_PATTERN}"/>
</sort>
</path>
<sequential>
<var name="DRIVER_CLASSPATH" value="@{ojdbc-jar}"/>
</sequential>
</for>
<if><equals arg1="${DRIVER_CLASSPATH}" arg2=""/>
<then>
<echo>No JDBC driver archive found.</echo>
<var name="cp.full" value="${java.class.path}"/>
</then>
<else>
<echo>Using JDBC driver archive "${DRIVER_CLASSPATH}"</echo>
<var name="cp.full" value="${java.class.path}${path.separator}${DRIVER_CLASSPATH}"/>
</else>
</if>
</then>
<else>
<var name="cp.full" value="${java.class.path}"/>
</else>
</if>
</then>
<else>
<!– driver jar explicitely given -> has the highest priority in classpath –>
<var name="cp.full" value="${DRIVER_CLASSPATH}${path.separator}${java.class.path}"/>
</else>
</if>
<fail message="Oracle JDBC driver is not in classpath:${line.separator}${cp.full}">
<condition><not><available classname="${DRIVER_CLASS}" classpath="${cp.full}"/></not></condition>
</fail>
<!– Thin- or OCI-URL? –>
<if><matches pattern="jdbc:.+" string="${URL}"/>
<then>
<property name="database.url" value="${URL}"/>
</then>
<else>
<property name="database.url" value="jdbc:oracle:oci:@${URL}"/>
</else>
</if>
<!– Establish connection –>
<echo>Connecting to "${database.url}" with user "${USER}" …</echo>
<runSQL>select 1 from dual</runSQL>
</target>
<!– Loads/executes all files into the database –>
<target name="load" depends="check">
<!– detect source file pattern to load –>
<if><not><isset property="SOURCES"/></not>
<then>
<if><available file="${user.dir}/${SOURCES.TXT}"/>
<then><loadfile property="SOURCES" srcfile="${user.dir}/${SOURCES.TXT}"/></then>
</if>
<if><not><isset property="SOURCES"/></not>
<then><property name="SOURCES" value="${SOURCES.DEF}"/></then>
</if>
</then>
</if>
<!– pattern are separated by comma or newline –>
<echo>Loading files matching one of the following pattern:${line.separator}</echo>
<for list="${SOURCES}" param="src-pattern" delimiter=", " trim="true" keepgoing="true">
<sequential>
<load-sources include="@{src-pattern}"/>
</sequential>
</for>
<!– recompile schema and get the current errors –>
<runSQL call="true">begin dbms_utility.compile_schema(user()); end;</runSQL>
<runSQL>select * from user_errors</runSQL>
</target>
<!– Loads files from a fileset –>
<macrodef name="load-sources">
<attribute name="include"/>
<attribute name="exclude" default=""/>
<sequential>
<var name="file.count" unset="true"/>
<echo>Counting files matching "@{include}"/ not matching "@{exclude}" in "${user.dir}" …</echo>
<resourcecount property="file.count">
<fileset dir="${user.dir}">
<include name="@{include}"/>
<exclude name="@{exclude}"/>
</fileset>
</resourcecount>
<if><equals arg1="${file.count}" arg2="0"/>
<then><echo>No files found matching pattern "@{pattern}".</echo></then>
<else>
<var name="load-source.errors" unset="true"/>
<var name="load-source.warnings" unset="true"/>
<sql driver="${DRIVER_CLASS}"
classpath="${cp.full}"
url="${database.url}"
userid="${USER}"
password="${PASS}"
delimiter="/"
delimitertype="row"
keepformat="true"
onerror="continue">
<!– ant 1.8 new features: showWarnings="true" errorproperty="load-source.errors" warningproperty="load-source.warnings" –>
<path>
<fileset dir="${user.dir}">
<include name="@{include}"/>
<exclude name="@{exclude}"/>
</fileset>
</path>
</sql>
<print-if-set property="load-source.errors" message="Errors: %s"/>
<print-if-set property="load-source.warnings" message="Warnings: %s"/>
</else>
</if>
</sequential>
</macrodef>
<!– Prints a message if a property is set and not empty –>
<macrodef name="print-if-set">
<attribute name="property"/>
<attribute name="message"/>
<sequential>
<if><and><isset property="@{property}"/><not><equals arg1="@{property}" arg2=""/></not></and>
<then>
<propertyregex property="print-if-set.msg"
input="@{message}"
regexp="(.*)%s(.*)"
replace="\1@{property}\2"/>
<echo>${print-if-set.msg}</echo>
</then>
</if>
</sequential>
</macrodef>
<!– Runs either a select statement and prints the result set or calls a PL/SQL-Block –>
<macrodef name="runSQL">
<attribute name="call" default="false"/>
<text name="sql"/>
<sequential>
<if><istrue value="@{call}"/>
<then>
<echo>Executing SQL as a call …</echo>
<sql driver="${DRIVER_CLASS}"
classpath="${cp.full}"
url="${database.url}"
userid="${USER}"
password="${PASS}"
delimiter="/"
delimitertype="row"
keepformat="true">
@{sql}
/
</sql>
</then>
<else>
<echo>Executing SQL as a select …</echo>
<sql driver="${DRIVER_CLASS}"
classpath="${cp.full}"
url="${database.url}"
userid="${USER}"
password="${PASS}"
print="true">
@{sql};
</sql>
</else>
</if>
</sequential>
</macrodef>
</project>
[/xml]
Das ist ganz schön monströs für eine eigentlich recht simple Aufgabe, bestimmte Dateien aus einer Verzeichnisstruktur in einer bestimmten Reihenfolge auf einer Datenbank auszuführen. Und es fällt auf, dass die konditionalen und Schleifen-Tasks von ant-contrib eine wichtige Rolle spielen – ant bzw. xml wird in Richtung einer Programmiersprache verbogen (manche würden sagen: missbraucht). Ok, ein echter ant-Guru kann hier sicher noch jede Menge Verbesserungen vornehmen, aber ein gelegentlicher ant-Nutzer wie ich endet vermutlich mit einem ähnlichen Build-File. Wenn man ein solches Build-File – was der normale Gang der Dinge ist – lange nicht anguckt hat und plötzlich eine Veränderung/Erweiterung/Bugfix vorgenommen werden muss, ist es recht mühsam zu verstehen, was man da in grauer Vorzeit programmiert hat.
Welche offenen Punkte hat das Script noch?
- Jede Datei kann eine wilde Kombination aus DDL- (create table, create procedure …) und DML-Anweisungen (insert, update, delete) sowie PL/SQL-Scripten (begin … end;) enthalten; das Script ist nicht in der Lage festzustellen, ob und wenn ja welche Datenbank-Objekte gerade angelegt bzw. ersetzt wurden – daher der abschließende Aufruf von dbms_utility.compile_schema und select * from user_errors.
- Das schon oben erwähnte Problem der unterschiedlichen Auswirkung von Syntax-Fehlern in „normalen“ DDL-Statements und PL/SQL-Routinen bleibt bestehen.
- Mein Script funktioniert noch nicht richtig: das select auf user_errors bringt nämlich keine Ergebnisse, auch wenn fehlerhafte PL/SQL-, View- oder Java-Dateien geladen worden sind – ich arbeite dran; für jeden Hinweis, wo der Bug liegt, bin ich dankbar.
Wie auch in anderen Situationen bin ich mit der ant-Lösung also nicht ganz zufrieden. Also werde ich mich demnächst mit großen Erwartungen auf Graddle stürzen … stay tuned.