How to script database upgrade using ANT script

classic Classic list List threaded Threaded
22 messages Options
12
Reply | Threaded
Open this post in threaded view
|

How to script database upgrade using ANT script

Java Jboss
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.
Reply | Threaded
Open this post in threaded view
|

RE: How to script database upgrade using ANT script

Ludwig, Michael
> 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
Reply | Threaded
Open this post in threaded view
|

Re: How to script database upgrade using ANT script

Antoine Levy Lambert
In reply to this post by Java Jboss
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]

Reply | Threaded
Open this post in threaded view
|

RE: How to script database upgrade using ANT script

Java Jboss
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: [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.

Reply | Threaded
Open this post in threaded view
|

Re: How to script database upgrade using ANT script

Antoine Levy Lambert
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]

Reply | Threaded
Open this post in threaded view
|

RE: How to script database upgrade using ANT script

Java Jboss
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: [hidden email]
To: [hidden email]
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.

Reply | Threaded
Open this post in threaded view
|

RE: How to script database upgrade using ANT script

Bailey, Darragh
 

> -----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]

Reply | Threaded
Open this post in threaded view
|

Re: How to script database upgrade using ANT script

Java Jboss
In reply to this post by Java Jboss
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
 
 
<target name="restore-db" >
   
    <antcall target="delete_db"/>
    <antcall target="create_db"/>

    <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>

 
<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> 


<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>

 
<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>

Reply | Threaded
Open this post in threaded view
|

Re: How to script database upgrade using ANT script

Antoine Levy Lambert
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

RE: How to script database upgrade using ANT script

Java Jboss
In reply to this post by Bailey, Darragh
Hi Darragh,
 
can you please provide an example of how I could do this?
 

Date: Fri, 5 Nov 2010 03:49:57 -0700
From: [hidden email]
To: [hidden email]
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.

Reply | Threaded
Open this post in threaded view
|

RE: How to script database upgrade using ANT script

Ludwig, Michael
> 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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: How to script database upgrade using ANT script

Brian Pontarelli
In reply to this post by Java Jboss
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: [hidden email]
> For additional commands, e-mail: [hidden email]
>


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

Reply | Threaded
Open this post in threaded view
|

Re: How to script database upgrade using ANT script

Gilbert Rebhan-2
In reply to this post by Java Jboss
-------- Original Message  --------
Subject: Re: How to script database upgrade using ANT script
From: Java Jboss <[hidden email]>
To: [hidden email]
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re : How to script database upgrade using ANT script

Hoang-Vu PHUNG
In reply to this post by Java Jboss
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 <[hidden email]> a écrit :

> De: Java Jboss <[hidden email]>
> Objet: How to script database upgrade using ANT script
> À: [hidden email]
> 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: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>




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

Reply | Threaded
Open this post in threaded view
|

Re: How to script database upgrade using ANT script

Java Jboss
In reply to this post by Java Jboss
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:

   
<target name="restore-db" > 
     
    <antcall target="delete_db"/> 
    <antcall target="create_db"/> 
 
    <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> 

   
<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> 

 
<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> 

   
<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


Reply | Threaded
Open this post in threaded view
|

Re: How to script database upgrade using ANT script

Mugdha Acharya
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: How to script database upgrade using ANT script

Robert Kirkpatrick
In reply to this post by Java Jboss

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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

RE: How to script database upgrade using ANT script

Ludwig, Michael
In reply to this post by Ludwig, Michael
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: How to script database upgrade using ANT script

Java Jboss
In reply to this post by Java Jboss
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.

Reply | Threaded
Open this post in threaded view
|

RE: How to script database upgrade using ANT script

Ludwig, Michael
> 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: [hidden email]
For additional commands, e-mail: [hidden email]

12