Sheila's Blog

Having set up the database folder structure in Automated Build Database scripts you now need to add the oracle xe scripts.

You create them in PROJECT_HOME/db/oraclexe/testdb db/oraclexe/testdb/build-oraclexe-testdb.xml db/oraclexe/testdb/createTables.sql db/oraclexe/testdb/dropTables.sql db/oraclexe/testdb/populateTables.sql db/oraclexe/testdb/testData.sql

The build script itself is fairly self-explanatory.  It replicates the targets from the master build script.  It reads the xe.properties and schema.properties files and adds the correct jar file to the classpath.

For each target you first check if the property oraclexe.db.enable has been set so the target will only run if you’ve configured schema.properties to include the oraclexe database.   Then in each target you connect to the database and execute the statements in the appropriate sql script.

oraclexe/testdb/build-xe-testdb.xml: <?xml version="1.0"?> <project basedir="." name="xe-mdm-build"> <property file="../../config/schema.properties" /> <property file="../../config/xe.properties" /> <property name="template.dir" value="../templates" /> <property name="lib.dir" value="../../lib" /> <property name="build.dir" value="../../build" /> <!-- Class path settings --> <fileset id="lib" dir="${lib.dir}/oracle"> <include name="*.jar" /> </fileset> <path id="classpath"> <fileset refid="lib" /> </path> <target name="init" if="xe.db.enable"> <mkdir dir="${build.dir}"/> </target> <target name="clean" if="xe.db.enable"> <delete dir="${build.dir}"/> </target> <target name="gen-sql" if="xe.db.enable" depends="clean,init" description="Generates the sql files"> <copy file="${template.dir}/createUser.template" tofile="${build.dir}/createTestUser.sql" overwrite="true"> <!-- replace template tokens marked with ~~ before and after --> <filterset begintoken="~~" endtoken="~~"> <filter token="template.username" value="${xe.username}" /> <filter token="template.password" value="${xe.password}" /> </filterset> </copy> <copy file="${template.dir}/dropUser.template" tofile="${build.dir}/dropTestUser.sql" overwrite="true"> <!-- replace template tokens marked with ~~ before and after --> <filterset begintoken="~~" endtoken="~~"> <filter token="template.username" value="${xe.username}" /> </filterset> </copy> </target> <target name="createUser" if="xe.db.enable" depends="gen-sql" description="Creates user specified in properties file"> <echo>Executing xe-mdm:createUser </echo> <exec dir="${template.dir}" executable="cmd"> <arg line="/c run.bat ${xe.rootuser} ${xe.rootpass} ${xe.sid} ${build.dir}/createTestUser.sql"/> </exec> </target> <target name="dropUser" if="xe.db.enable" depends="gen-sql" description="Creates user specified in properties file"> <echo>Executing xe-mdm:dropUser </echo> <exec dir="${template.dir}" executable="cmd"> <arg line="/c run.bat ${xe.rootuser} ${xe.rootpass} ${xe.sid} ${build.dir}/dropTestUser.sql"/> </exec> </target> <target name="recreateTables" depends="dropTables,createTables,populateTables,testData" description="Refreshes the database tables"/> <target name="createTables" if="xe.db.enable" description="Creates Database Tables"> <echo>Executing xe-mdm:createtables </echo> <sql driver="${xe.driver}" src="${xe.create.tables.script}" url="${xe.url}" userid="${xe.username}" password="${xe.password}" print="${print}"> <classpath refid="classpath"/> </sql> </target> <target name="populateTables" if="xe.db.enable" description="Populates Database Tables"> <echo>Executing xe-mdm:populateTables </echo> <sql driver="${xe.driver}" src="${xe.populate.tables.script}" url="${xe.url}" userid="${xe.username}" password="${xe.password}" print="${print}"> <classpath refid="classpath"/> </sql> </target> <target name="dropTables" if="xe.db.enable" description="Drops the Database Tables"> <echo>Executing xe-mdm:dropTables </echo> <sql driver="${xe.driver}" src="${xe.drop.tables.script}" url="${xe.url}" userid="${xe.username}" password="${xe.password}" print="${print}" onerror="continue"> <classpath refid="classpath"/> </sql> </target> <target name="testData" if="xe.db.enable" description="Refreshes test data"> <echo>Executing xe-mdm:testData </echo> <sql driver="${xe.driver}" src="${xe.testdata.script}" url="${xe.url}" userid="${xe.username}" password="${xe.password}" print="${print}"> <classpath refid="classpath"/> </sql> </target> </project>

For oraclexe you need to generate scripts for creating and dropping the user and run them from a batch file.  The values in xe.properties are used to fill in the details.  Create the following templates:

oraclexe/templates/createUser.template: create user ~~template.username~~ identified by ~~template.password~~ DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 50M ON users; GRANT CONNECT, RESOURCE TO ~~template.username~~;

oraclexe/templates/dropUser.template:

In createUser.template we want to sustitute a value for the token template.username. We choose ~~ to indicate the start and end of the token. In the gen-sql target you state the start and end of the tokens are ~~ and indicate which value should replace the token in the template. In this case ~~template.username~~ will be replaced with the xe.username value. The generated sql scripts will be stored in db/build.

oraclexe/templates/run.bat:@echo off SET sysdba_userid=%1 SET sysdba_psswd=%2 SET sid=%3 SET sql_file=%4 SQLPlus %sysdba_userid%/%sysdba_psswd%@%sid% as sysdba @%sql_file% echo Finished processing, exiting ...

The createUser and dropUser targets are run after gen-sql. They execute a command line prompt and pass the database connection details and sql script to run.bat before launching it.

Now the only thing left to do is add the sql statements to the .sql scripts to create the database you want.  Note that for targets that drop tables or users we set onerror="continue" so that the build won't fail if you add in new tables and attempt to drop them before they've been created.

Tags:   automated build · oraclexeNo Comments