FAQ

[Ant-user] How to script database upgrade using ANT script

Java Jboss
Nov 4, 2010 at 4:27 pm
Hi,

I’m a complete newbie to ANT and I need to write an ant script which
upgrades a database version from base level 1.00 to a given version number
by running a series of mysql (ver 5.1.45) scripts.

Using ANT script I need to:
1) create a database
2) import mysql database(s) dump to a new schema
3) check current database version
4) then check if sql script1 exists
4.1) if "No" then check for next sql script until it has checked for all
scripts then "end"
4.2) if "yes" then run first sql script then check if second sql script
exists - if "yes" then run second sql script and so on until the required
database version is reached.
5) check database version is at correct level then “end” or else start the
loop again
6) how can I script error checking also

Thank you all in advance.

--
View this message in context: http://ant.1045680.n5.nabble.com/How-to-script-database-upgrade-using-ANT-script-tp3250375p3250375.html
Sent from the Ant - Users mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org
reply

Search Discussions

21 responses

  • Ludwig, Michael at Nov 4, 2010 at 4:44 pm

    I need to write an ant script which upgrades a database version from
    base level 1.00 to a given version number by running a series of mysql
    (ver 5.1.45) scripts.

    Using ANT script I need to:
    1) create a database
    2) import mysql database(s) dump to a new schema
    3) check current database version Okay.
    4) then check if sql script1 exists
    4.1) if "No" then check for next sql script until it has checked for
    all scripts then "end"
    4.2) if "yes" then run first sql script then check if second sql
    script exists - if "yes" then run second sql script and so on until
    the required database version is reached.
    So, basically, loop over all scripts in order.
    5) check database version is at correct level then “end” or else start
    the loop again
    If it didn't succeed the first time around, why expect a different
    outcome on the next iteration? And how many times would you retry?

    I think that regardless of the driver you choose - Ant, shell, VBS, or
    whatever - this will boil down to calling the mysql command line client.
    Using ant, you can do that by means of the <exec/> task.

    Michael
  • Antoine Levy Lambert at Nov 4, 2010 at 4:45 pm
    Hi,

    you will need to use the <exec/> task for steps like creating the
    database and importing the dump into the new schema.
    Once your database is up, you can use the <sqlexec/> task to run DDL and
    DML statements.
    To check whether a script exists, there is a task called <available/>
    which can set a property if a file is available.
    For error checking, you can run <exec/> with failonerror="true" to stop
    your build if a script fails.
    You need to test before hand whether the system commands or the scripts
    that you will run to create the database and import the dump are
    returning proper exit statuses. (setting ERRORLEVEL on Windows or $? on
    UNIX).

    Regards,

    Antoine
    On 11/4/10 12:28 PM, Java Jboss wrote:
    Hi,

    I’m a complete newbie to ANT and I need to write an ant script which
    upgrades a database version from base level 1.00 to a given version number
    by running a series of mysql (ver 5.1.45) scripts.

    Using ANT script I need to:
    1) create a database
    2) import mysql database(s) dump to a new schema
    3) check current database version
    4) then check if sql script1 exists
    4.1) if "No" then check for next sql script until it has checked for all
    scripts then "end"
    4.2) if "yes" then run first sql script then check if second sql script
    exists - if "yes" then run second sql script and so on until the required
    database version is reached.
    5) check database version is at correct level then “end” or else start the
    loop again
    6) how can I script error checking also

    Thank you all in advance.

    ---------------------------------------------------------------------
    To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
    For additional commands, e-mail: user-help@ant.apache.org
  • Java Jboss at Nov 4, 2010 at 5:35 pm
    Hi Antoine,

    Thank you for this update. I am using a linux server. I've been asked to do this in ANT only. Can you possibly provide me an example of how a similar process might be scripted using ANT?

    As I have never used ANT or any other similar products before I am using Google for help.

    Thank you.


    Date: Thu, 4 Nov 2010 09:46:31 -0700
    From: ml-node+3250409-888287632-143298@n5.nabble.com
    To: apacheant@hotmail.co.uk
    Subject: Re: How to script database upgrade using ANT script

    Hi,

    you will need to use the <exec/> task for steps like creating the
    database and importing the dump into the new schema.
    Once your database is up, you can use the <sqlexec/> task to run DDL and
    DML statements.
    To check whether a script exists, there is a task called <available/>
    which can set a property if a file is available.
    For error checking, you can run <exec/> with failonerror="true" to stop
    your build if a script fails.
    You need to test before hand whether the system commands or the scripts
    that you will run to create the database and import the dump are
    returning proper exit statuses. (setting ERRORLEVEL on Windows or $? on
    UNIX).

    Regards,

    Antoine
    On 11/4/10 12:28 PM, Java Jboss wrote:

    Hi,

    I’m a complete newbie to ANT and I need to write an ant script which
    upgrades a database version from base level 1.00 to a given version number
    by running a series of mysql (ver 5.1.45) scripts.

    Using ANT script I need to:
    1) create a database
    2) import mysql database(s) dump to a new schema
    3) check current database version
    4) then check if sql script1 exists
    4.1) if "No" then check for next sql script until it has checked for all
    scripts then "end"
    4.2) if "yes" then run first sql script then check if second sql script
    exists - if "yes" then run second sql script and so on until the required
    database version is reached.
    5) check database version is at correct level then “end” or else start the
    loop again
    6) how can I script error checking also

    Thank you all in advance.
    ---------------------------------------------------------------------
    To unsubscribe, e-mail: [hidden email]
    For additional commands, e-mail: [hidden email]






    View message @ http://ant.1045680.n5.nabble.com/How-to-script-database-upgrade-using-ANT-script-tp3250375p3250409.html
    To unsubscribe from How to script database upgrade using ANT script, click here.

    --
    View this message in context: http://ant.1045680.n5.nabble.com/How-to-script-database-upgrade-using-ANT-script-tp3250375p3250488.html
    Sent from the Ant - Users mailing list archive at Nabble.com.
  • Antoine Levy Lambert at Nov 4, 2010 at 5:44 pm

    On 11/4/10 1:35 PM, Java Jboss wrote:
    Hi Antoine,

    Thank you for this update. I am using a linux server. I've been asked to do this in ANT only. Can you possibly provide me an example of how a similar process might be scripted using ANT?

    As I have never used ANT or any other similar products before I am using Google for help.
    I suggest that you start by reading the manual under
    http://ant.apache.org/manual/ first.

    You would start by writing a build file for your work looking like that

    <project name="databaseupgrade">
    <target name="all" depends="createdatabase,loaddump,runscripts"/>
    <target name="init">
    <property name="databasename" value="mynewdatabase"/>
    </target>
    <target name="createdatabase" depends="init">
    <exec command="sh" failonerror="true">
    <arg value="createdatabase.sh"/>
    <arg value="${databasename}"/>
    </exec>
    </target>
    <target name="loaddump">
    <!-- some action goes here -->
    </target>
    <target name="runscripts">
    <!-- some action gets here too -->
    </target>
    </project>
    Thank you.


    Date: Thu, 4 Nov 2010 09:46:31 -0700
    From: ml-node+3250409-888287632-143298@n5.nabble.com
    To: apacheant@hotmail.co.uk
    Subject: Re: How to script database upgrade using ANT script

    Hi,

    you will need to use the<exec/> task for steps like creating the
    database and importing the dump into the new schema.
    Once your database is up, you can use the<sqlexec/> task to run DDL and
    DML statements.
    To check whether a script exists, there is a task called<available/>
    which can set a property if a file is available.
    For error checking, you can run<exec/> with failonerror="true" to stop
    your build if a script fails.
    You need to test before hand whether the system commands or the scripts
    that you will run to create the database and import the dump are
    returning proper exit statuses. (setting ERRORLEVEL on Windows or $? on
    UNIX).

    Regards,

    Antoine
    On 11/4/10 12:28 PM, Java Jboss wrote:

    Hi,

    I’m a complete newbie to ANT and I need to write an ant script which
    upgrades a database version from base level 1.00 to a given version number
    by running a series of mysql (ver 5.1.45) scripts.

    Using ANT script I need to:
    1) create a database
    2) import mysql database(s) dump to a new schema
    3) check current database version
    4) then check if sql script1 exists
    4.1) if "No" then check for next sql script until it has checked for all
    scripts then "end"
    4.2) if "yes" then run first sql script then check if second sql script
    exists - if "yes" then run second sql script and so on until the required
    database version is reached.
    5) check database version is at correct level then “end” or else start the
    loop again
    6) how can I script error checking also

    Thank you all in advance.
    ---------------------------------------------------------------------
    To unsubscribe, e-mail: [hidden email]
    For additional commands, e-mail: [hidden email]






    View message @ http://ant.1045680.n5.nabble.com/How-to-script-database-upgrade-using-ANT-script-tp3250375p3250409.html
    To unsubscribe from How to script database upgrade using ANT script, click here.

    ---------------------------------------------------------------------
    To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
    For additional commands, e-mail: user-help@ant.apache.org
  • Java Jboss at Nov 4, 2010 at 10:42 pm
    Hi Antoine,

    Thank you very much for providing me this example. unfortunately I've been asked to use ANT only without using unix shell scripts. I have tried to tackle it as individual modules using ANT script running the 1st iteration of the mysql scripts against newschema1. I need to run each iteration against all 4 schemas, then it would do a version check and if version check wasn't met then it would start 2nd iteration and so on.

    I would appreciate if you could take a look at the script and assist. I need to create a recursive loop which I can't do and also some error checking module as well as some how linking these modules together so that they depend on each other etc... At the moment I have to run each target on its own.

    Please feel free to change my novice attempt at writing an ANT script.

    Thank you

    <!-- ***************************************
    Restore MySQL database
    **************************************** -->
    <target name="restore-db" >
    <!-- Clean up the database by deleting and then creating it. -->
    <antcall target="delete_db"/>
    <antcall target="create_db"/>
    <!-- Import data from the backup file. -->
    <exec executable="${my.sql}" input="db_dumps/db1.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    <exec executable="${my.sql}" input="db_dumps/db2.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema2>"/>
    </exec>
    <exec executable="${my.sql}" input="db_dumps/db3.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema3>"/>
    </exec>
    <exec executable="${my.sql}" input="db_dumps/db4.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema4>"/>
    </exec>
    </target>

    <target name="delete_db">
    <exec executable="${my.sql}">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="--force"/>
    <arg value="drop"/>
    <arg value="<newschema1>"/>
    <arg value="<newschema2>"/>
    <arg value="<newschema3>"/>
    <arg value="<newschema4>"/>
    </exec>
    </target>

    <target name="create_db">
    <exec executable="${my.sql}">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="--force"/>
    <arg value="create"/>
    <arg value="<newschema1>"/>
    <arg value="<newschema2>"/>
    <arg value="<newschema3>"/>
    <arg value="<newschema4>"/>
    </exec>
    </target>

    <!-- ***************************************
    Get database version
    **************************************** -->
    <target name="get-db-version" >
    <exec executable="${my.sql}" outputproperty="get-db-version.out" input="getdbver.sql">
    <arg line="--host=${server.ip}"/>
    <arg line="--user=root"/>
    <arg line="--password=<password>"/>
    <arg line="-D${newschema1}"/>
    </exec>
    <echo>Is at ${get-db-version.out}</echo>
    </target>

    <!-- ************************************************
    Check database upgrade scripts exists
    *********************************************** -->
    <target name="db-version" if="get-db-version.out" depends="get-db-version" >
    <echo>Running upgrade from ${get-db-version.out}</echo>

    <condition property="script1.exists">
    <available filepath="<directory>" file="script1.sql"/>
    </condition>
    <antcall target="run-script1"/>
    <condition property="script2.exists">
    <available filepath="<directory>" file="script2.sql"/>
    </condition>
    <antcall target="run-script2"/>
    <condition property="script3.exists">
    <available filepath="<directory>" file="script3.sql"/>
    </condition>
    <antcall target="run-script3"/>
    <condition property="script4.exists">
    <available filepath="<directory>" file="script4.sql"/>
    </condition>
    <antcall target="run-script4"/>
    </target>

    <!-- ***************************************
    Run database upgrade scripts
    **************************************** -->
    <target name="run-script1" if="script1.exists" >
    <echo>Running sql in dir <directory></echo>
    <exec executable="${my.sql}" input="<directory>/script1.sql" failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>
    <target name="run-script2" if="script2.exists" >
    <echo>Running sql in dir <directory></echo>
    <exec executable="${my.sql}" input="<directory>/script2.sql" failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>
    <target name="run-script3" if="script3.exists" >
    <echo>Running sql in dir <directory></echo>
    <exec executable="${my.sql}" input="<directory>/script3.sql" failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>
    <target name="run-script4" if="script4.exists" >
    <echo>Running sql in dir <directory></echo>
    <exec executable="${my.sql}" input="<directory>/script4.sql" failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>





    Date: Thu, 4 Nov 2010 10:45:35 -0700
    From: ml-node+3250502-1415855530-143298@n5.nabble.com
    To: apacheant@hotmail.co.uk
    Subject: Re: How to script database upgrade using ANT script
    On 11/4/10 1:35 PM, Java Jboss wrote:

    Hi Antoine,

    Thank you for this update. I am using a linux server. I've been asked to do this in ANT only. Can you possibly provide me an example of how a similar process might be scripted using ANT?

    As I have never used ANT or any other similar products before I am using Google for help.
    I suggest that you start by reading the manual under
    http://ant.apache.org/manual/ first.

    You would start by writing a build file for your work looking like that

    <project name="databaseupgrade">
    <target name="all" depends="createdatabase,loaddump,runscripts"/>
    <target name="init">
    <property name="databasename" value="mynewdatabase"/>
    </target>
    <target name="createdatabase" depends="init">
    <exec command="sh" failonerror="true">
    <arg value="createdatabase.sh"/>
    <arg value="${databasename}"/>
    </exec>
    </target>
    <target name="loaddump">
    <!-- some action goes here -->
    </target>
    <target name="runscripts">
    <!-- some action gets here too -->
    </target>
    </project>
    Thank you.


    Date: Thu, 4 Nov 2010 09:46:31 -0700
    From: [hidden email]
    To: [hidden email]
    Subject: Re: How to script database upgrade using ANT script

    Hi,

    you will need to use the<exec/> task for steps like creating the
    database and importing the dump into the new schema.
    Once your database is up, you can use the<sqlexec/> task to run DDL and
    DML statements.
    To check whether a script exists, there is a task called<available/>
    which can set a property if a file is available.
    For error checking, you can run<exec/> with failonerror="true" to stop
    your build if a script fails.
    You need to test before hand whether the system commands or the scripts
    that you will run to create the database and import the dump are
    returning proper exit statuses. (setting ERRORLEVEL on Windows or $? on
    UNIX).

    Regards,

    Antoine
    On 11/4/10 12:28 PM, Java Jboss wrote:

    Hi,

    I’m a complete newbie to ANT and I need to write an ant script which
    upgrades a database version from base level 1.00 to a given version number
    by running a series of mysql (ver 5.1.45) scripts.

    Using ANT script I need to:
    1) create a database
    2) import mysql database(s) dump to a new schema
    3) check current database version
    4) then check if sql script1 exists
    4.1) if "No" then check for next sql script until it has checked for all
    scripts then "end"
    4.2) if "yes" then run first sql script then check if second sql script
    exists - if "yes" then run second sql script and so on until the required
    database version is reached.
    5) check database version is at correct level then “end” or else start the
    loop again
    6) how can I script error checking also

    Thank you all in advance.
    ---------------------------------------------------------------------
    To unsubscribe, e-mail: [hidden email]
    For additional commands, e-mail: [hidden email]






    View message @ http://ant.1045680.n5.nabble.com/How-to-script-database-upgrade-using-ANT-script-tp3250375p3250409.html
    To unsubscribe from How to script database upgrade using ANT script, click here.
    ---------------------------------------------------------------------
    To unsubscribe, e-mail: [hidden email]
    For additional commands, e-mail: [hidden email]






    View message @ http://ant.1045680.n5.nabble.com/How-to-script-database-upgrade-using-ANT-script-tp3250375p3250502.html
    To unsubscribe from How to script database upgrade using ANT script, click here.

    --
    View this message in context: http://ant.1045680.n5.nabble.com/How-to-script-database-upgrade-using-ANT-script-tp3250375p3250988.html
    Sent from the Ant - Users mailing list archive at Nabble.com.
  • Bailey, Darragh at Nov 5, 2010 at 10:49 am

    -----Original Message-----
    From: Java Jboss
    Sent: 04 November 2010 22:43
    To: user@ant.apache.org
    Subject: RE: How to script database upgrade using ANT script



    Hi Antoine,

    Thank you very much for providing me this example.
    unfortunately I've been asked to use ANT only without using
    unix shell scripts. I have tried to tackle it as individual
    modules using ANT script running the 1st iteration of the
    mysql scripts against newschema1. I need to run each
    iteration against all 4 schemas, then it would do a version
    check and if version check wasn't met then it would start 2nd
    iteration and so on.

    I would appreciate if you could take a look at the script and
    assist. I need to create a recursive loop which I can't do
    and also some error checking module as well as some how
    linking these modules together so that they depend on each
    other etc... At the moment I have to run each target on its own.

    Please feel free to change my novice attempt at writing an ANT script.

    Thank you

    <!-- ***************************************
    Restore MySQL database
    **************************************** -->
    <target name="restore-db" >
    <!-- Clean up the database by deleting and then creating it. -->
    <antcall target="delete_db"/>
    <antcall target="create_db"/>
    <!-- Import data from the backup file. -->
    <exec executable="${my.sql}" input="db_dumps/db1.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    Take a look at the apply task to see if you can remove a number of mostly duplicated steps.


    --
    Regards,
    Darragh Bailey

    ---------------------------------------------------------------------
    To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
    For additional commands, e-mail: user-help@ant.apache.org
  • Java Jboss at Nov 5, 2010 at 11:25 am
    Hi Darragh,

    can you please provide an example of how I could do this?



    Date: Fri, 5 Nov 2010 03:49:57 -0700
    From: ml-node+3251630-1672610580-143298@n5.nabble.com
    To: apacheant@hotmail.co.uk
    Subject: RE: How to script database upgrade using ANT script



    -----Original Message-----
    From: Java Jboss [mailto:[hidden email]]
    Sent: 04 November 2010 22:43
    To: [hidden email]
    Subject: RE: How to script database upgrade using ANT script



    Hi Antoine,

    Thank you very much for providing me this example.
    unfortunately I've been asked to use ANT only without using
    unix shell scripts. I have tried to tackle it as individual
    modules using ANT script running the 1st iteration of the
    mysql scripts against newschema1. I need to run each
    iteration against all 4 schemas, then it would do a version
    check and if version check wasn't met then it would start 2nd
    iteration and so on.

    I would appreciate if you could take a look at the script and
    assist. I need to create a recursive loop which I can't do
    and also some error checking module as well as some how
    linking these modules together so that they depend on each
    other etc... At the moment I have to run each target on its own.

    Please feel free to change my novice attempt at writing an ANT script.

    Thank you

    <!-- ***************************************
    Restore MySQL database
    **************************************** -->
    <target name="restore-db" >
    <!-- Clean up the database by deleting and then creating it. -->
    <antcall target="delete_db"/>
    <antcall target="create_db"/>
    <!-- Import data from the backup file. -->
    <exec executable="${my.sql}" input="db_dumps/db1.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    Take a look at the apply task to see if you can remove a number of mostly duplicated steps.


    --
    Regards,
    Darragh Bailey

    ---------------------------------------------------------------------
    To unsubscribe, e-mail: [hidden email]
    For additional commands, e-mail: [hidden email]






    View message @ http://ant.1045680.n5.nabble.com/How-to-script-database-upgrade-using-ANT-script-tp3250375p3251630.html
    To unsubscribe from How to script database upgrade using ANT script, click here.

    --
    View this message in context: http://ant.1045680.n5.nabble.com/How-to-script-database-upgrade-using-ANT-script-tp3250375p3251660.html
    Sent from the Ant - Users mailing list archive at Nabble.com.
  • Ludwig, Michael at Nov 5, 2010 at 1:43 pm
    can you please provide an example of how I could do this?
    If you didn't top-post, it'd be easier to read your mail.

    Take a look at the documentation for the <apply/> task in
    the manual. Well, scroll down to the example section. Looks
    like the <apply/> task applies the command to each file in
    the nested <fileset/>. Which may or may not come in handy
    for your purposes.

    Note that the first example given in the manual is sort of
    stupid, as it is wasteful to call "ls -l" once for each file
    in a list of possibly hundreds of files, spawning a new
    process each time around.

    A better example would be a program that does something
    more time-consuming and resource-intensive (like database
    interaction) where the cost of spawning a new process
    barely weighs in.

    Maybe the maintainers can replace the example in the manual
    with this one:

    <apply executable="mysql">
    <arg value="-e"/>
    <sort>
    <fileset dir="batch/sql">
    <patternset>
    <include name="**/*.sql"/>
    </patternset>
    </fileset>
    </sort>
    </apply>

    Michael

    ---------------------------------------------------------------------
    To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
    For additional commands, e-mail: user-help@ant.apache.org
  • Ludwig, Michael at Nov 9, 2010 at 8:16 am

    I commented on the <apply> task:

    Note that the first example given in the manual is sort of
    stupid, as it is wasteful to call "ls -l" once for each file
    in a list of possibly hundreds of files, spawning a new
    process each time around.
    You can have Ant execute the batch all at once by supplying:

    <apply parallel="true" ...

    That might be a useful addition to the first example in the
    documentation.

    http://ant.apache.org/manual/Tasks/apply.html

    Michael

    ---------------------------------------------------------------------
    To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
    For additional commands, e-mail: user-help@ant.apache.org
  • Java Jboss at Nov 5, 2010 at 10:48 am
    Hi,

    Thank you for the suggestions so far from Antoine and Michael. unfortunately
    I've been asked to use ANT only without using unix shell scripts. I have
    tried to tackle it as individual modules using ANT script running the 1st
    iteration of the mysql scripts against newschema1. I need to run each
    iteration against all 4 schemas, then it would do a version check and if
    version check wasn't met then it would start 2nd iteration and so on.

    This is rather urgent so I would appreciate any assistance. I need to create
    a recursive loop which I can't do and also some error checking module as
    well as some how linking these modules together so that they depend on each
    other etc... At the moment I have to run each target on its own.

    Please feel free to change my novice attempt at writing an ANT script.

    Thank you

    <!-- ***************************************
    Restore MySQL database
    **************************************** -->
    <target name="restore-db" >
    <!-- Clean up the database by deleting and then creating it. -->
    <antcall target="delete_db"/>
    <antcall target="create_db"/>
    <!-- Import data from the backup file. -->
    <exec executable="${my.sql}" input="db_dumps/db1.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    <exec executable="${my.sql}" input="db_dumps/db2.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema2>"/>
    </exec>
    <exec executable="${my.sql}" input="db_dumps/db3.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema3>"/>
    </exec>
    <exec executable="${my.sql}" input="db_dumps/db4.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema4>"/>
    </exec>
    </target>

    <target name="delete_db">
    <exec executable="${my.sql}">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="--force"/>
    <arg value="drop"/>
    <arg value="<newschema1>"/>
    <arg value="<newschema2>"/>
    <arg value="<newschema3>"/>
    <arg value="<newschema4>"/>
    </exec>
    </target>

    <target name="create_db">
    <exec executable="${my.sql}">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="--force"/>
    <arg value="create"/>
    <arg value="<newschema1>"/>
    <arg value="<newschema2>"/>
    <arg value="<newschema3>"/>
    <arg value="<newschema4>"/>
    </exec>
    </target>

    <!-- ***************************************
    Get database version
    **************************************** -->
    <target name="get-db-version" >
    <exec executable="${my.sql}" outputproperty="get-db-version.out"
    input="getdbver.sql">
    <arg line="--host=${server.ip}"/>
    <arg line="--user=root"/>
    <arg line="--password=<password>"/>
    <arg line="-D${newschema1}"/>
    </exec>
    <echo>Is at ${get-db-version.out}</echo>
    </target>

    <!-- ************************************************
    Check database upgrade scripts exists
    *********************************************** -->
    <target name="db-version" if="get-db-version.out" depends="get-db-version" >
    <echo>Running upgrade from ${get-db-version.out}</echo>

    <condition property="script1.exists">
    <available filepath="<directory>" file="script1.sql"/>
    </condition>
    <antcall target="run-script1"/>
    <condition property="script2.exists">
    <available filepath="<directory>" file="script2.sql"/>
    </condition>
    <antcall target="run-script2"/>
    <condition property="script3.exists">
    <available filepath="<directory>" file="script3.sql"/>
    </condition>
    <antcall target="run-script3"/>
    <condition property="script4.exists">
    <available filepath="<directory>" file="script4.sql"/>
    </condition>
    <antcall target="run-script4"/>
    </target>

    <!-- ***************************************
    Run database upgrade scripts
    **************************************** -->
    <target name="run-script1" if="script1.exists" >
    <echo>Running sql in dir <directory></echo>
    <exec executable="${my.sql}" input="<directory>/script1.sql"
    failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>
    <target name="run-script2" if="script2.exists" >
    <echo>Running sql in dir <directory></echo>
    <exec executable="${my.sql}" input="<directory>/script2.sql"
    failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>
    <target name="run-script3" if="script3.exists" >
    <echo>Running sql in dir <directory></echo>
    <exec executable="${my.sql}" input="<directory>/script3.sql"
    failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>
    <target name="run-script4" if="script4.exists" >
    <echo>Running sql in dir <directory></echo>
    <exec executable="${my.sql}" input="<directory>/script4.sql"
    failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>


    --
    View this message in context: http://ant.1045680.n5.nabble.com/How-to-script-database-upgrade-using-ANT-script-tp3250375p3251629.html
    Sent from the Ant - Users mailing list archive at Nabble.com.

    ---------------------------------------------------------------------
    To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
    For additional commands, e-mail: user-help@ant.apache.org
  • Antoine Levy Lambert at Nov 5, 2010 at 11:23 am

    On 11/5/10 6:48 AM, Java Jboss wrote:
    Hi,

    Thank you for the suggestions so far from Antoine and Michael. unfortunately
    I've been asked to use ANT only without using unix shell scripts. I have
    tried to tackle it as individual modules using ANT script running the 1st
    iteration of the mysql scripts against newschema1. I need to run each
    iteration against all 4 schemas, then it would do a version check and if
    version check wasn't met then it would start 2nd iteration and so on.

    This is rather urgent so I would appreciate any assistance. I need to create
    a recursive loop which I can't do and also some error checking module as
    well as some how linking these modules together so that they depend on each
    other etc... At the moment I have to run each target on its own.

    Please feel free to change my novice attempt at writing an ANT script.

    Thank you
    You can look into macrodef if you need to run several times the same
    task or the same set of tasks with slightly different parameters.
    Also, if you need loops and recursions, you might be better off writing
    your own ant task (this is documented online too in the manual) and make
    this ant task use the exec and sql task to do the work.
    Regards,

    Antoine

    ---------------------------------------------------------------------
    To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
    For additional commands, e-mail: user-help@ant.apache.org
  • Brian Pontarelli at Nov 5, 2010 at 3:21 pm
    There are a number of projects for this as well. Here's one I know of:

    https://github.com/ReadyTalk/staccato

    There are a number of others out there as well. I'd suggest trying on of these projects to do your migrations and version checking because you'll run into maintenance nightmares otherwise.

    -bp

    On Nov 5, 2010, at 4:48 AM, Java Jboss wrote:


    Hi,

    Thank you for the suggestions so far from Antoine and Michael. unfortunately
    I've been asked to use ANT only without using unix shell scripts. I have
    tried to tackle it as individual modules using ANT script running the 1st
    iteration of the mysql scripts against newschema1. I need to run each
    iteration against all 4 schemas, then it would do a version check and if
    version check wasn't met then it would start 2nd iteration and so on.

    This is rather urgent so I would appreciate any assistance. I need to create
    a recursive loop which I can't do and also some error checking module as
    well as some how linking these modules together so that they depend on each
    other etc... At the moment I have to run each target on its own.

    Please feel free to change my novice attempt at writing an ANT script.

    Thank you

    <!-- ***************************************
    Restore MySQL database
    **************************************** -->
    <target name="restore-db" >
    <!-- Clean up the database by deleting and then creating it. -->
    <antcall target="delete_db"/>
    <antcall target="create_db"/>
    <!-- Import data from the backup file. -->
    <exec executable="${my.sql}" input="db_dumps/db1.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    <exec executable="${my.sql}" input="db_dumps/db2.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema2>"/>
    </exec>
    <exec executable="${my.sql}" input="db_dumps/db3.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema3>"/>
    </exec>
    <exec executable="${my.sql}" input="db_dumps/db4.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema4>"/>
    </exec>
    </target>

    <target name="delete_db">
    <exec executable="${my.sql}">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="--force"/>
    <arg value="drop"/>
    <arg value="<newschema1>"/>
    <arg value="<newschema2>"/>
    <arg value="<newschema3>"/>
    <arg value="<newschema4>"/>
    </exec>
    </target>

    <target name="create_db">
    <exec executable="${my.sql}">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="--force"/>
    <arg value="create"/>
    <arg value="<newschema1>"/>
    <arg value="<newschema2>"/>
    <arg value="<newschema3>"/>
    <arg value="<newschema4>"/>
    </exec>
    </target>

    <!-- ***************************************
    Get database version
    **************************************** -->
    <target name="get-db-version" >
    <exec executable="${my.sql}" outputproperty="get-db-version.out"
    input="getdbver.sql">
    <arg line="--host=${server.ip}"/>
    <arg line="--user=root"/>
    <arg line="--password=<password>"/>
    <arg line="-D${newschema1}"/>
    </exec>
    <echo>Is at ${get-db-version.out}</echo>
    </target>

    <!-- ************************************************
    Check database upgrade scripts exists
    *********************************************** -->
    <target name="db-version" if="get-db-version.out" depends="get-db-version" >
    <echo>Running upgrade from ${get-db-version.out}</echo>

    <condition property="script1.exists">
    <available filepath="<directory>" file="script1.sql"/>
    </condition>
    <antcall target="run-script1"/>
    <condition property="script2.exists">
    <available filepath="<directory>" file="script2.sql"/>
    </condition>
    <antcall target="run-script2"/>
    <condition property="script3.exists">
    <available filepath="<directory>" file="script3.sql"/>
    </condition>
    <antcall target="run-script3"/>
    <condition property="script4.exists">
    <available filepath="<directory>" file="script4.sql"/>
    </condition>
    <antcall target="run-script4"/>
    </target>

    <!-- ***************************************
    Run database upgrade scripts
    **************************************** -->
    <target name="run-script1" if="script1.exists" >
    <echo>Running sql in dir <directory></echo>
    <exec executable="${my.sql}" input="<directory>/script1.sql"
    failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>
    <target name="run-script2" if="script2.exists" >
    <echo>Running sql in dir <directory></echo>
    <exec executable="${my.sql}" input="<directory>/script2.sql"
    failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>
    <target name="run-script3" if="script3.exists" >
    <echo>Running sql in dir <directory></echo>
    <exec executable="${my.sql}" input="<directory>/script3.sql"
    failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>
    <target name="run-script4" if="script4.exists" >
    <echo>Running sql in dir <directory></echo>
    <exec executable="${my.sql}" input="<directory>/script4.sql"
    failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>


    --
    View this message in context: http://ant.1045680.n5.nabble.com/How-to-script-database-upgrade-using-ANT-script-tp3250375p3251629.html
    Sent from the Ant - Users mailing list archive at Nabble.com.

    ---------------------------------------------------------------------
    To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
    For additional commands, e-mail: user-help@ant.apache.org

    ---------------------------------------------------------------------
    To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
    For additional commands, e-mail: user-help@ant.apache.org
  • Gilbert Rebhan at Nov 5, 2010 at 8:32 pm

    -------- Original Message --------
    Subject: Re: How to script database upgrade using ANT script
    From: Java Jboss <apacheant@hotmail.co.uk>
    To: user@ant.apache.org
    Date: 05.11.2010 11:48

    This is rather urgent so I would appreciate any assistance. I need to create
    a recursive loop which I can't do and also some error checking module as
    well as some how linking these modules together so that they depend on each
    other etc... At the moment I have to run each target on its own.

    Please feel free to change my novice attempt at writing an ANT script.

    Thank you
    exec,exec,exec.. doesn't feel good IMO, i would try a JVM scripting
    language that works great with ant = Groovy

    here are some links to get you going =
    http://mauszeig.wordpress.com/2006/04/07/ant-groovy-and-the-database/
    http://groovy.codehaus.org/Database+features
    http://groovy.codehaus.org/api/groovy/sql/Sql.html
    http://www.ibm.com/developerworks/java/library/j-pg01115.html
    http://mrhaki.blogspot.com/2009/10/groovy-goodness-groovy-sql.html

    Regards, Gilbert





    ---------------------------------------------------------------------
    To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
    For additional commands, e-mail: user-help@ant.apache.org
  • Hoang-Vu PHUNG at Nov 8, 2010 at 9:48 am
    Hi,

    Select the right tool for the right job:
    For example scriptella http://scriptella.javaforge.com/
    is a good tool for upgrading database.

    And i think it does have an ant task also. Please check with the site.

    Regards,
    Vu

    --- En date de : Jeu 4.11.10, Java Jboss <apacheant@hotmail.co.uk> a écrit :
    De: Java Jboss <apacheant@hotmail.co.uk>
    Objet: How to script database upgrade using ANT script
    À: user@ant.apache.org
    Date: Jeudi 4 novembre 2010, 18h28

    Hi,

    I’m a complete newbie to ANT and I need to write an ant
    script which
    upgrades a database version from base level 1.00 to a given
    version number
    by running a series of mysql (ver 5.1.45) scripts.

    Using ANT script I need to:
    1) create a database
    2) import mysql database(s) dump to a new schema
    3) check current database version
    4) then check if sql script1 exists
    4.1) if "No" then check for next sql script until it has
    checked for all
    scripts then "end"
    4.2) if "yes" then run first sql script then check if
    second sql script
    exists - if "yes" then run second sql script and so on
    until the required
    database version is reached.
    5) check database version is at correct level then
    “end” or else start the
    loop again
    6) how can I script error checking also

    Thank you all in advance.

    --
    View this message in context: http://ant.1045680.n5.nabble.com/How-to-script-database-upgrade-using-ANT-script-tp3250375p3250375.html
    Sent from the Ant - Users mailing list archive at
    Nabble.com.

    ---------------------------------------------------------------------
    To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
    For additional commands, e-mail: user-help@ant.apache.org



    ---------------------------------------------------------------------
    To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
    For additional commands, e-mail: user-help@ant.apache.org
  • Java Jboss at Nov 8, 2010 at 3:19 pm
    Hi,

    Thank you to all for the suggestions. I have now got an ANT script that
    checks the current database version and then runs a set of mysql scripts to
    upgrade the database to the next version. What I am struggling with is when
    it goes through the list="${db.upgrade.path.list}. On the first run the
    folder exists and runs the scripts then on the second run it checks for the
    next folder(it doesn't exist at the moment) and it should drop out echoing
    the new current version but in my case it tries to run every occurrence as
    per the list="${db.upgrade.path.list}. I want it to stop when it can't meet
    the next condition. The output I am getting is as below:

    <!-- ***************************************
    Restore MySQL database
    **************************************** -->
    <target name="restore-db" >
    <!-- Clean up the database by deleting and then creating it. -->
    <antcall target="delete_db"/>
    <antcall target="create_db"/>
    <!-- Import data from the backup file. -->
    <exec executable="${my.sql}" input="db_dumps/db1.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    <exec executable="${my.sql}" input="db_dumps/db2.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema2>"/>
    </exec>
    <exec executable="${my.sql}" input="db_dumps/db3.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema3>"/>
    </exec>
    <exec executable="${my.sql}" input="db_dumps/db4.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema4>"/>
    </exec>
    </target>

    <target name="delete_db">
    <exec executable="${my.sql}">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="--force"/>
    <arg value="drop"/>
    <arg value="<newschema1>"/>
    <arg value="<newschema2>"/>
    <arg value="<newschema3>"/>
    <arg value="<newschema4>"/>
    </exec>
    </target>

    <target name="create_db">
    <exec executable="${my.sql}">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="--force"/>
    <arg value="create"/>
    <arg value="<newschema1>"/>
    <arg value="<newschema2>"/>
    <arg value="<newschema3>"/>
    <arg value="<newschema4>"/>
    </exec>
    </target>

    <!-- ***************************************
    Get database version
    **************************************** -->
    <target name="get-db-version" >
    <exec executable="${my.sql}" outputproperty="get-db-version.out"
    input="getdbver.sql">
    <arg line="--host=${server.ip}"/>
    <arg line="--user=root"/>
    <arg line="--password=<password>"/>
    <arg line="-D${newschema1}"/>
    </exec>
    <echo>Is at ${get-db-version.out}</echo>
    </target>

    <!-- ************************************************
    Check database upgrade scripts exists
    *********************************************** -->
    <target name="db-version" if="get-db-version.out" depends="get-db-version" >
    <echo>Running upgrade from ${get-db-version.out}</echo>

    <condition property="script1.exists">
    <available filepath="<directory>" file="script1.sql"/>
    </condition>
    <antcall target="run-script1"/>
    <condition property="script2.exists">
    <available filepath="<directory>" file="script2.sql"/>
    </condition>
    <antcall target="run-script2"/>
    <condition property="script3.exists">
    <available filepath="<directory>" file="script3.sql"/>
    </condition>
    <antcall target="run-script3"/>
    <condition property="script4.exists">
    <available filepath="<directory>" file="script4.sql"/>
    </condition>
    <antcall target="run-script4"/>
    </target>

    <!-- ***************************************
    Run database upgrade scripts
    **************************************** -->
    <target name="run-script1" if="script1.exists" >
    <echo>Running sql in dir <directory></echo>
    <exec executable="${my.sql}" input="<directory>/script1.sql"
    failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>
    <target name="run-script2" if="script2.exists" >
    <echo>Running sql in dir <directory></echo>
    <exec executable="${my.sql}" input="<directory>/script2.sql"
    failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>
    <target name="run-script3" if="script3.exists" >
    <echo>Running sql in dir <directory></echo>
    <exec executable="${my.sql}" input="<directory>/script3.sql"
    failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>
    <target name="run-script4" if="script4.exists" >
    <echo>Running sql in dir <directory></echo>
    <exec executable="${my.sql}" input="<directory>/script4.sql"
    failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>


    OUTPUT

    get-db-version:
    [echo] Current database version: 1.00

    db-upgrade:
    [echo] Current Version: 1.00
    [echo] Upgrade path : 1.0-2.0
    [echo] database......... mysql
    [echo] tx............... standalone
    [echo] mail.smtp.host... localhost

    get-db-version:
    [echo] Current database version: 1.01

    db-upgrade:
    [echo] Current Version: 1.01
    [echo] Upgrade path : 2.0-3.0
    [exec] Failed to open
    ../build/dir-upgrade-2.0-3.0/Upgrade-1.01-1-Pre.sql
    [exec] Failed to open
    ../build/dir-upgrade-2.0-3.0/Upgrade-1.01-2-Data.sql
    [exec] Failed to open
    ../build/dir-upgrade-2.0-3.0/Upgrade-1.01-3-Post.sql
    [exec] Failed to open
    ../build/dir-upgrade-2.0-3.0/Upgrade-1.01-4-Version.sql
    [echo] database......... mysql
    [echo] tx............... standalone
    [echo] mail.smtp.host... localhost

    get-db-version:
    [echo] Current database version: 1.01

    db-upgrade:
    [echo] Current Version: 1.01
    [echo] Upgrade path : 3.0-4.0
    [exec] Failed to open
    ../build/dir-upgrade-3.0-4.0/Upgrade-1.01-1-Pre.sql
    [exec] Failed to open
    ../build/dir-upgrade-3.0-4.0/Upgrade-1.01-2-Data.sql
    [exec] Failed to open
    ../build/dir-upgrade-3.0-4.0/Upgrade-1.01-3-Post.sql
    [exec] Failed to open
    ../build/dir-upgrade-3.0-4.0/Upgrade-1.01-4-Version.sql

    BUILD SUCCESSFUL



    --
    View this message in context: http://ant.1045680.n5.nabble.com/How-to-script-database-upgrade-using-ANT-script-tp3250375p3255191.html
    Sent from the Ant - Users mailing list archive at Nabble.com.

    ---------------------------------------------------------------------
    To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
    For additional commands, e-mail: user-help@ant.apache.org
  • Mugdha Acharya at Nov 8, 2010 at 4:51 pm
    We are using dbdeploy and are very happy with the results. You can read
    more about it at http://dbdeploy.com/

    <taskdef name="dbdeploy" classname="com.dbdeploy.AntTarget"
    classpathref="dbdeploy.classpath"/>

    <path id="dbdeploy-db-classpath">
    <fileset dir="${dbdeploy.dir}/dbjars">
    <include name="**/*.jar"/>
    </fileset>
    </path>

    <path id="dbdeploy.classpath">
    <fileset dir="${dbdeploy.dir}">
    <include name="dbdeploy-ant-*.jar"/>
    </fileset>
    <!-- the dbdeploy task also needs the database driver jar on the
    classpath -->
    <path refid="dbdeploy-db-classpath" />
    </path>

    <target name="updateDB" description="automatically runs the sql scripts
    on db" >
    <dbdeploy driver="${db.driver}" url="${db.url}"
    userid="${db.username}"
    password="${db.password}"
    dir="${deployJob}/sqlScripts"
    />
    </target>
    On Mon, 2010-11-08 at 07:19 -0800, Java Jboss wrote:
    Hi,

    Thank you to all for the suggestions. I have now got an ANT script that
    checks the current database version and then runs a set of mysql scripts to
    upgrade the database to the next version. What I am struggling with is when
    it goes through the list="${db.upgrade.path.list}. On the first run the
    folder exists and runs the scripts then on the second run it checks for the
    next folder(it doesn't exist at the moment) and it should drop out echoing
    the new current version but in my case it tries to run every occurrence as
    per the list="${db.upgrade.path.list}. I want it to stop when it can't meet
    the next condition. The output I am getting is as below:

    <!-- ***************************************
    Restore MySQL database
    **************************************** -->
    <target name="restore-db" >
    <!-- Clean up the database by deleting and then creating it. -->
    <antcall target="delete_db"/>
    <antcall target="create_db"/>
    <!-- Import data from the backup file. -->
    <exec executable="${my.sql}" input="db_dumps/db1.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    <exec executable="${my.sql}" input="db_dumps/db2.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema2>"/>
    </exec>
    <exec executable="${my.sql}" input="db_dumps/db3.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema3>"/>
    </exec>
    <exec executable="${my.sql}" input="db_dumps/db4.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema4>"/>
    </exec>
    </target>

    <target name="delete_db">
    <exec executable="${my.sql}">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="--force"/>
    <arg value="drop"/>
    <arg value="<newschema1>"/>
    <arg value="<newschema2>"/>
    <arg value="<newschema3>"/>
    <arg value="<newschema4>"/>
    </exec>
    </target>

    <target name="create_db">
    <exec executable="${my.sql}">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="--force"/>
    <arg value="create"/>
    <arg value="<newschema1>"/>
    <arg value="<newschema2>"/>
    <arg value="<newschema3>"/>
    <arg value="<newschema4>"/>
    </exec>
    </target>

    <!-- ***************************************
    Get database version
    **************************************** -->
    <target name="get-db-version" >
    <exec executable="${my.sql}" outputproperty="get-db-version.out"
    input="getdbver.sql">
    <arg line="--host=${server.ip}"/>
    <arg line="--user=root"/>
    <arg line="--password=<password>"/>
    <arg line="-D${newschema1}"/>
    </exec>
    <echo>Is at ${get-db-version.out}</echo>
    </target>

    <!-- ************************************************
    Check database upgrade scripts exists
    *********************************************** -->
    <target name="db-version" if="get-db-version.out" depends="get-db-version" >
    <echo>Running upgrade from ${get-db-version.out}</echo>

    <condition property="script1.exists">
    <available filepath="<directory>" file="script1.sql"/>
    </condition>
    <antcall target="run-script1"/>
    <condition property="script2.exists">
    <available filepath="<directory>" file="script2.sql"/>
    </condition>
    <antcall target="run-script2"/>
    <condition property="script3.exists">
    <available filepath="<directory>" file="script3.sql"/>
    </condition>
    <antcall target="run-script3"/>
    <condition property="script4.exists">
    <available filepath="<directory>" file="script4.sql"/>
    </condition>
    <antcall target="run-script4"/>
    </target>

    <!-- ***************************************
    Run database upgrade scripts
    **************************************** -->
    <target name="run-script1" if="script1.exists" >
    <echo>Running sql in dir <directory></echo>
    <exec executable="${my.sql}" input="<directory>/script1.sql"
    failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>
    <target name="run-script2" if="script2.exists" >
    <echo>Running sql in dir <directory></echo>
    <exec executable="${my.sql}" input="<directory>/script2.sql"
    failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>
    <target name="run-script3" if="script3.exists" >
    <echo>Running sql in dir <directory></echo>
    <exec executable="${my.sql}" input="<directory>/script3.sql"
    failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>
    <target name="run-script4" if="script4.exists" >
    <echo>Running sql in dir <directory></echo>
    <exec executable="${my.sql}" input="<directory>/script4.sql"
    failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>


    OUTPUT

    get-db-version:
    [echo] Current database version: 1.00

    db-upgrade:
    [echo] Current Version: 1.00
    [echo] Upgrade path : 1.0-2.0
    [echo] database......... mysql
    [echo] tx............... standalone
    [echo] mail.smtp.host... localhost

    get-db-version:
    [echo] Current database version: 1.01

    db-upgrade:
    [echo] Current Version: 1.01
    [echo] Upgrade path : 2.0-3.0
    [exec] Failed to open
    ../build/dir-upgrade-2.0-3.0/Upgrade-1.01-1-Pre.sql
    [exec] Failed to open
    ../build/dir-upgrade-2.0-3.0/Upgrade-1.01-2-Data.sql
    [exec] Failed to open
    ../build/dir-upgrade-2.0-3.0/Upgrade-1.01-3-Post.sql
    [exec] Failed to open
    ../build/dir-upgrade-2.0-3.0/Upgrade-1.01-4-Version.sql
    [echo] database......... mysql
    [echo] tx............... standalone
    [echo] mail.smtp.host... localhost

    get-db-version:
    [echo] Current database version: 1.01

    db-upgrade:
    [echo] Current Version: 1.01
    [echo] Upgrade path : 3.0-4.0
    [exec] Failed to open
    ../build/dir-upgrade-3.0-4.0/Upgrade-1.01-1-Pre.sql
    [exec] Failed to open
    ../build/dir-upgrade-3.0-4.0/Upgrade-1.01-2-Data.sql
    [exec] Failed to open
    ../build/dir-upgrade-3.0-4.0/Upgrade-1.01-3-Post.sql
    [exec] Failed to open
    ../build/dir-upgrade-3.0-4.0/Upgrade-1.01-4-Version.sql

    BUILD SUCCESSFUL



    ---------------------------------------------------------------------
    To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
    For additional commands, e-mail: user-help@ant.apache.org
  • Robert Kirkpatrick at Nov 8, 2010 at 5:23 pm
    You should really get rid of the exec task and use the Ant sql task instead. It just requires a
    JDBC driver.
    Suitable parameters exist for all your args.

    Robert.

    Le 8/11/2010 16:19, Java Jboss a écrit :
    Hi,

    Thank you to all for the suggestions. I have now got an ANT script that
    checks the current database version and then runs a set of mysql scripts to
    upgrade the database to the next version. What I am struggling with is when
    it goes through the list="${db.upgrade.path.list}. On the first run the
    folder exists and runs the scripts then on the second run it checks for the
    next folder(it doesn't exist at the moment) and it should drop out echoing
    the new current version but in my case it tries to run every occurrence as
    per the list="${db.upgrade.path.list}. I want it to stop when it can't meet
    the next condition. The output I am getting is as below:

    <!-- ***************************************
    Restore MySQL database
    **************************************** -->
    <target name="restore-db">
    <!-- Clean up the database by deleting and then creating it. -->
    <antcall target="delete_db"/>
    <antcall target="create_db"/>
    <!-- Import data from the backup file. -->
    <exec executable="${my.sql}" input="db_dumps/db1.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    <exec executable="${my.sql}" input="db_dumps/db2.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema2>"/>
    </exec>
    <exec executable="${my.sql}" input="db_dumps/db3.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema3>"/>
    </exec>
    <exec executable="${my.sql}" input="db_dumps/db4.sql">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema4>"/>
    </exec>
    </target>

    <target name="delete_db">
    <exec executable="${my.sql}">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="--force"/>
    <arg value="drop"/>
    <arg value="<newschema1>"/>
    <arg value="<newschema2>"/>
    <arg value="<newschema3>"/>
    <arg value="<newschema4>"/>
    </exec>
    </target>

    <target name="create_db">
    <exec executable="${my.sql}">
    <arg value="--user=root"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="--force"/>
    <arg value="create"/>
    <arg value="<newschema1>"/>
    <arg value="<newschema2>"/>
    <arg value="<newschema3>"/>
    <arg value="<newschema4>"/>
    </exec>
    </target>

    <!-- ***************************************
    Get database version
    **************************************** -->
    <target name="get-db-version">
    <exec executable="${my.sql}" outputproperty="get-db-version.out"
    input="getdbver.sql">
    <arg line="--host=${server.ip}"/>
    <arg line="--user=root"/>
    <arg line="--password=<password>"/>
    <arg line="-D${newschema1}"/>
    </exec>
    <echo>Is at ${get-db-version.out}</echo>
    </target>

    <!-- ************************************************
    Check database upgrade scripts exists
    *********************************************** -->
    <target name="db-version" if="get-db-version.out" depends="get-db-version">
    <echo>Running upgrade from ${get-db-version.out}</echo>

    <condition property="script1.exists">
    <available filepath="<directory>" file="script1.sql"/>
    </condition>
    <antcall target="run-script1"/>
    <condition property="script2.exists">
    <available filepath="<directory>" file="script2.sql"/>
    </condition>
    <antcall target="run-script2"/>
    <condition property="script3.exists">
    <available filepath="<directory>" file="script3.sql"/>
    </condition>
    <antcall target="run-script3"/>
    <condition property="script4.exists">
    <available filepath="<directory>" file="script4.sql"/>
    </condition>
    <antcall target="run-script4"/>
    </target>

    <!-- ***************************************
    Run database upgrade scripts
    **************************************** -->
    <target name="run-script1" if="script1.exists">
    <echo>Running sql in dir<directory></echo>
    <exec executable="${my.sql}" input="<directory>/script1.sql"
    failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>
    <target name="run-script2" if="script2.exists">
    <echo>Running sql in dir<directory></echo>
    <exec executable="${my.sql}" input="<directory>/script2.sql"
    failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>
    <target name="run-script3" if="script3.exists">
    <echo>Running sql in dir<directory></echo>
    <exec executable="${my.sql}" input="<directory>/script3.sql"
    failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>
    <target name="run-script4" if="script4.exists">
    <echo>Running sql in dir<directory></echo>
    <exec executable="${my.sql}" input="<directory>/script4.sql"
    failonerror="true">
    <arg value="--user=<username>"/>
    <arg value="--password=<password>"/>
    <arg value="--host=localhost" />
    <arg value="--port=3306" />
    <arg value="<newschema1>"/>
    </exec>
    </target>


    OUTPUT

    get-db-version:
    [echo] Current database version: 1.00

    db-upgrade:
    [echo] Current Version: 1.00
    [echo] Upgrade path : 1.0-2.0
    [echo] database......... mysql
    [echo] tx............... standalone
    [echo] mail.smtp.host... localhost

    get-db-version:
    [echo] Current database version: 1.01

    db-upgrade:
    [echo] Current Version: 1.01
    [echo] Upgrade path : 2.0-3.0
    [exec] Failed to open
    ../build/dir-upgrade-2.0-3.0/Upgrade-1.01-1-Pre.sql
    [exec] Failed to open
    ../build/dir-upgrade-2.0-3.0/Upgrade-1.01-2-Data.sql
    [exec] Failed to open
    ../build/dir-upgrade-2.0-3.0/Upgrade-1.01-3-Post.sql
    [exec] Failed to open
    ../build/dir-upgrade-2.0-3.0/Upgrade-1.01-4-Version.sql
    [echo] database......... mysql
    [echo] tx............... standalone
    [echo] mail.smtp.host... localhost

    get-db-version:
    [echo] Current database version: 1.01

    db-upgrade:
    [echo] Current Version: 1.01
    [echo] Upgrade path : 3.0-4.0
    [exec] Failed to open
    ../build/dir-upgrade-3.0-4.0/Upgrade-1.01-1-Pre.sql
    [exec] Failed to open
    ../build/dir-upgrade-3.0-4.0/Upgrade-1.01-2-Data.sql
    [exec] Failed to open
    ../build/dir-upgrade-3.0-4.0/Upgrade-1.01-3-Post.sql
    [exec] Failed to open
    ../build/dir-upgrade-3.0-4.0/Upgrade-1.01-4-Version.sql

    BUILD SUCCESSFUL

    ---------------------------------------------------------------------
    To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
    For additional commands, e-mail: user-help@ant.apache.org
  • Java Jboss at Nov 9, 2010 at 9:38 am
    Hi Michael,

    With my limited ANT knowledge I have got the script to work almost. I have
    had a look at the link that you sent but having difficulty scripting a
    routine to tell it that when a condition is not met it should exit.

    I would really appreciate a soultion for this. I need to get this
    implemented today and currently trying various things but not having any
    success.

    Thank you.


    --
    View this message in context: http://ant.1045680.n5.nabble.com/How-to-script-database-upgrade-using-ANT-script-tp3250375p3256512.html
    Sent from the Ant - Users mailing list archive at Nabble.com.

    ---------------------------------------------------------------------
    To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
    For additional commands, e-mail: user-help@ant.apache.org
  • Ludwig, Michael at Nov 9, 2010 at 10:56 am

    having difficulty scripting a
    routine to tell it that when a condition is not met it should exit.
    <fail unless="this.prop.must.be.true"/>

    Look at the docs for <condition> to see how to set the property.

    Michael

    ---------------------------------------------------------------------
    To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
    For additional commands, e-mail: user-help@ant.apache.org
  • Java Jboss at Nov 9, 2010 at 11:30 am
    Hi Michael,

    I have tried using the <fail unless..../> and other examples from Google but
    I can't get it to work. I just want the "db-upgrade:" bit to stop when it
    cant meet the next condition.

    At the moment it still tries to run the script looking for dir2 and dir3
    even though these don't exist at the moment but will do in the future.

    outputs:

    db-upgrade:
    [exec] Failed to open .../upgrade-2.0-3.0/Upgrade-1.01-1-Pre.sql
    [exec] Failed to open .../upgrade-2.0-3.0/Upgrade-1.01-2-Data.sql
    [exec] Failed to open .../upgrade-2.0-3.0/Upgrade-1.01-3-Post.sql
    [exec] Failed to open .../upgrade-2.0-3.0/Upgrade-1.01-4-Version.sql
    [echo] database......... mysql
    [echo] tx............... standalone
    [echo] mail.smtp.host... localhost

    db-upgrade:
    [exec] Failed to open .../upgrade-3.0-4.0/Upgrade-1.01-1-Pre.sql
    [exec] Failed to open .../upgrade-3.0-4.0/Upgrade-1.01-2-Data.sql
    [exec] Failed to open .../upgrade-3.0-4.0/Upgrade-1.01-3-Post.sql
    [exec] Failed to open .../upgrade-3.0-4.0/Upgrade-1.01-4-Version.sql

    Having seen my script can you please provide the bit of ant code that will
    allow this condition check and exit if doesn't meet it.
    --
    View this message in context: http://ant.1045680.n5.nabble.com/How-to-script-database-upgrade-using-ANT-script-tp3250375p3256644.html
    Sent from the Ant - Users mailing list archive at Nabble.com.

    ---------------------------------------------------------------------
    To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
    For additional commands, e-mail: user-help@ant.apache.org
  • Ludwig, Michael at Nov 9, 2010 at 1:06 pm

    I have tried using the <fail unless..../> and other examples
    from Google but I can't get it to work.
    Then you should try a little harder, it is not difficult,
    certainly way less difficult than anything involving JBoss.
    Start with a minimum script and the Ant manual. You shouldn't
    need more.

    Good luck,

    Michael

    ---------------------------------------------------------------------
    To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
    For additional commands, e-mail: user-help@ant.apache.org

Related Discussions