Scriptella - Spring Integration - DB Upgrade How To?

Question

I am currently using Scriptella ETL in Spring. I want to change the db schema and perform an upgrade of the db when i execute my program. I did not find any reference to do this except for a way using ANT. db-upgrade example

Wanted to know if we can write a separate sql like v1-v2.xml with the new alters and create statements and have it referenced in spring context.xml?

My current spring context xml referencing etl..

<bean id="etlProgress" class="scriptella.interactive.ConsoleProgressIndicator"/>
<bean id="etlExecutor" class="scriptella.driver.spring.EtlExecutorBean">
    <property name="configLocation" value="etl.xml"/>
    <property name="progressIndicator"><ref local="etlProgress"/></property>
</bean>

Any help here pls?

Answer 1

Found an answer myself. In etl.xml you can add a query that queries the "MetaInf" table. In my case, I had a metainf table like this.

    CREATE TABLE Metainf (
        buildnum int primary key
    );
    INSERT INTO Metainf VALUES (1);

That means, i have to query "buildnum" to check the version of the db. This can be done the following way

<query>
    <!-- Selects current DB build -->
    SELECT * FROM Metainf
    <!-- Check if upgrade is necessary -->
    <script if="buildnum lt 1">
        <!--Upgrades DB to build 1 -->
        <!--...-->
        <!-- Update Metainf to confirm successful upgrade -->
        UPDATE Metainf SET buildnum=1;
    </script>
    <script if="buildnum eq 1">
        <!-- upgrade scripts for subsequent builds -->

        UPDATE Metainf SET buildnum=2;
    </script>
</query>

I placed my old db file and ran after this change. Voila! I have my new changes in the database.

People who are looking for this answer, do try and let me know if it worked for you also.

Thanks