Rollback to specific upgrade path

[Ajay Sharma]

Hi,

How we can do rollback in the solidbase.

Thanks
Ajay

[René de Bloois]

Ajay,

I assume your question is about the rollback of a database upgrade and not rollback in the transactional sense (the opposite of commit)?

Regards,

René

[Ajay Sharma]

Hi Rene,

Thanks for the reply.

Yes my question is about rollback of a database upgrade.

Thanks
Ajay

> > Ajay- Hide quoted text -
>
> - Show quoted text -

[René de Bloois]

Ajay,

In a sense, it works exactly the same as normal upgrade paths and switch paths. But to protect production databases, you need to explicitly enable this downgrade feature in development or test environments.

Here are 2 links to unit test files which give an example of its use:

https://solidbase.googlecode.com/svn/branches/releases/1.6/test/testpatch-downgrade-1.sql

https://solidbase.googlecode.com/svn/branches/releases/1.6/test/test-upgradetask.xml

The 'downgradeallowed' option is also available in the Maven plugin, and the command line version of SolidBase.

With Ant for example you would normally put the downgradeallowed property in a separate properties file which only exists in development or test environments, for example the developers home folder.

The downgrade feature is not really documented yet, because it is an experimental feature, and not used a lot (as far as I know).

Most of the time a database drop script is enough to recreate the development and test databases.

And a failed upgrade of a production system can be fixed with a restore from a backup.

If you are using Oracle, I have a nice drop script if you want it. It drops all objects from a schema.

Let me know if you need more info, and I am also very interested to know if it works for you.

Regards,

René

[Ajay Sharma]

Hi Rene,

I am not able to run Downgrade feature.

I need to specify any entry in the pom file ?

Please suggest me.

Thanks
Ajay

On May 11, 4:25 pm, René de Bloois <rene.de.blo...@gmail.com> wrote:
> Ajay,
>

> In a sense, it works exactly the same as normal upgrade paths and switch
> paths. But to protect production databases, you need to explicitly enable
> this downgrade feature in development or test environments.
>
> Here are 2 links to unit test files which give an example of its use:
>

> https://solidbase.googlecode.com/svn/branches/releases/1.6/test/testp...https://solidbase.googlecode.com/svn/branches/releases/1.6/test/test-...

[René de Bloois]

Ajay,

Here an example fragment of a pom.xml. Its the 'downgradeallowed' element that you need to enable downgrades for developers.

<plugin>

<groupId>solidbase</groupId>

<artifactId>solidbase</artifactId>

<version>2.0.0-beta1</version>

<dependencies>

<dependency>

<groupId>hsqldb</groupId>

<artifactId>hsqldb</artifactId>

<version>1.8.0.7</version>

</dependency>

</dependencies>

<configuration>

<driver>org.hsqldb.jdbcDriver</driver>

<url>jdbc:hsqldb:mem:testplugin</url>

<username>sa</username>

<password></password>

<upgradefile>upgrade-hsqldb-example.sql</upgradefile>

<target>1.0.*</target>

<downgradeallowed>true</downgradeallowed>

</configuration>

</plugin>

Regards,

René

[Ajay Sharma]

Hi Rene,

I have tried downgrade feature but it seems its not working.any Idea ?

Thanks
Ajay

On May 15, 12:56 pm, René de Bloois <rene.de.blo...@gmail.com> wrote:
> Ajay,
>
> Here an example fragment of a pom.xml. Its the 'downgradeallowed' element
> that you need to enable downgrades for developers.
>
> <plugin>
> <groupId>solidbase</groupId>
> <artifactId>solidbase</artifactId>
> <version>2.0.0-beta1</version>
> <dependencies>
> <dependency>
> <groupId>hsqldb</groupId>
> <artifactId>hsqldb</artifactId>
> <version>1.8.0.7</version>
> </dependency>
> </dependencies>
> <configuration>
> <driver>org.hsqldb.jdbcDriver</driver>
> <url>jdbc:hsqldb:mem:testplugin</url>
> <username>sa</username>
> <password></password>
> <upgradefile>upgrade-hsqldb-example.sql</upgradefile>
> <target>1.0.*</target>

> *<downgradeallowed>true</downgradeallowed>*
> </configuration>
> </plugin>
>
> Regards,
> René

[René de Bloois]

Ajay,

You have to give me more information.

Any error messages? Can you share some parts of your project, so that I can analyse what's wrong?

René

[Ajay Sharma]

Hi Rene,

I have added block in my sql file as per the
https://solidbase.googlecode.com/svn/branches/releases/1.6/test/testpatch-downgrade-1.sql
URL.

--* DEFINITION
--* INIT "" --> "1.1"
--* UPGRADE "" --> "1.0.1"
--* UPGRADE "1.0.1" --> "1.0.2"
--* UPGRADE "1.0.2" --> "1.0.3"
--* SWITCH "1.0.3" --> "1.1.0"
--* UPGRADE "1.0.2" --> "1.1.0"
--* UPGRADE "1.1.0" --> "1.1.1"
--* DOWNGRADE "1.1.1" --> "1.0.2"
--* /DEFINITION

I am getting following message.
[INFO] Connecting to database...
[INFO] The database is unmanaged.
[INFO] Setting up control tables to "1.1"
[INFO] Upgrading to "1.0.1"
[INFO] Upgrading "1.0.1" to "1.0.2"
[INFO] Upgrading "1.0.2" to "1.0.3"
[INFO]
[INFO] Current database version is "1.0.3".
[INFO] Upgrade complete.

I also added <downgradeallowed>true</downgradeallowed> option in my
pom.xml
Please suggest.
Thanks
Ajay

[René de Bloois]

Ajay,

Here is an example:

Create an empty folder, and put the 2 attachments in it.

Open a command prompt and do the following steps:

1. Execute 'mvn install';
2. Change the 'target' property in the pom.xml to '2.0.*'
3. Execute 'mvn install';
4. Change the 'target' property in the pom.xml back to '1.0.*'
   
5. Execute 'mvn install', this fails;
   
6. Change the 'downgradeallowed' property in the pom.xml to 'true';
7. Execute 'mvn install', no it succeeds.

Example output:

Executing 'mvn install' gives:

G:\_WORK\downgrade-example>mvn install

[INFO] Scanning for projects...

[INFO]

[INFO] ------------------------------------------------------------------------

[INFO] Building mavenplugintest 1.0

[INFO] ------------------------------------------------------------------------

[INFO]

[INFO] --- solidbase:2.0.0-beta1:upgrade (solidbase-upgrade) @ mavenplugintest ---

[INFO] SolidBase v2.0.0-beta1-rev797 (http://solidbase.org)

[INFO]

[INFO] Opening file 'G:/_WORK/downgrade-example/downgrade-example.sql'

[INFO]     Encoding is 'ISO-8859-1'

[INFO] Connecting to database...

[INFO] The database is unmanaged.

[INFO] Setting up control tables to "1.1"

[INFO] Upgrading to "1.0.1"

[INFO] Upgrading "1.0.1" to "1.0.2"

[INFO] Upgrading "1.0.2" to "1.0.3"

[INFO]

[INFO] Current database version is "1.0.3".

[INFO] Upgrade complete.

[INFO]

[INFO] --- maven-install-plugin:2.3.1:install (default-install) @ mavenplugintest ---

[INFO] Installing G:\_WORK\downgrade-example\pom.xml to 

[INFO] ------------------------------------------------------------------------

[INFO] BUILD SUCCESS

[INFO] ------------------------------------------------------------------------

[INFO] Total time: 6.504s

[INFO] Finished at: Wed May 16 13:54:13 CEST 2012

[INFO] Final Memory: 4M/15M

[INFO] ------------------------------------------------------------------------

After changing the target property (in the pom.xml) from 1.0.* to 2.0.* :

G:\_WORK\downgrade-example>mvn install

[INFO] Scanning for projects...

[INFO]

[INFO] ------------------------------------------------------------------------

[INFO] Building mavenplugintest 1.0

[INFO] ------------------------------------------------------------------------

[INFO]

[INFO] --- solidbase:2.0.0-beta1:upgrade (solidbase-upgrade) @ mavenplugintest ---

[INFO] SolidBase v2.0.0-beta1-rev797 (http://solidbase.org)

[INFO]

[INFO] Opening file 'G:/_WORK/downgrade-example/downgrade-example.sql'

[INFO]     Encoding is 'ISO-8859-1'

[INFO] Connecting to database...

[INFO] Current database version is "1.0.3".

[INFO] Switching "1.0.3" to "2.0.1"

[INFO] Upgrading "2.0.1" to "2.0.2"

[INFO]

[INFO] Current database version is "2.0.2".

[INFO] Upgrade complete.

[INFO]

[INFO] --- maven-install-plugin:2.3.1:install (default-install) @ mavenplugintest ---

[INFO] Installing G:\_WORK\downgrade-example\pom.xml to 

[INFO] ------------------------------------------------------------------------

[INFO] BUILD SUCCESS

[INFO] ------------------------------------------------------------------------

[INFO] Total time: 1.636s

[INFO] Finished at: Wed May 16 13:54:29 CEST 2012

[INFO] Final Memory: 3M/15M

[INFO] ------------------------------------------------------------------------

After changing the target property from 2.0.* to 1.0.* :

G:\_WORK\downgrade-example>mvn install

[INFO] Scanning for projects...

[INFO]

[INFO] ------------------------------------------------------------------------

[INFO] Building mavenplugintest 1.0

[INFO] ------------------------------------------------------------------------

[INFO]

[INFO] --- solidbase:2.0.0-beta1:upgrade (solidbase-upgrade) @ mavenplugintest ---

[INFO] SolidBase v2.0.0-beta1-rev797 (http://solidbase.org)

[INFO]

[INFO] Opening file 'G:/_WORK/downgrade-example/downgrade-example.sql'

[INFO]     Encoding is 'ISO-8859-1'

[INFO] Connecting to database...

[INFO] Current database version is "2.0.2".

[INFO] Upgrade aborted.

[INFO] ------------------------------------------------------------------------

[INFO] BUILD FAILURE

[INFO] ------------------------------------------------------------------------

[INFO] Total time: 1.235s

[INFO] Finished at: Wed May 16 13:54:39 CEST 2012

[INFO] Final Memory: 2M/15M

[INFO] ------------------------------------------------------------------------

[ERROR] Failed to execute goal solidbase:solidbase:2.0.0-beta1:upgrade (solidbase-upgrade) on project mavenplugintest: Target 1.0.* is not reachable from version 2.0.2 -> [Help 1]

[ERROR]

[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.

[ERROR] Re-run Maven using the -X switch to enable full debug logging.

[ERROR]

[ERROR] For more information about the errors and possible solutions, please read the following articles:

[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoFailureException

After changing the downgradeallowed property from false to true:

G:\_WORK\downgrade-example>mvn install

[INFO] Scanning for projects...

[INFO]

[INFO] ------------------------------------------------------------------------

[INFO] Building mavenplugintest 1.0

[INFO] ------------------------------------------------------------------------

[INFO]

[INFO] --- solidbase:2.0.0-beta1:upgrade (solidbase-upgrade) @ mavenplugintest ---

[INFO] SolidBase v2.0.0-beta1-rev797 (http://solidbase.org)

[INFO]

[INFO] Opening file 'G:/_WORK/downgrade-example/downgrade-example.sql'

[INFO]     Encoding is 'ISO-8859-1'

[INFO] Connecting to database...

[INFO] Current database version is "2.0.2".

[INFO] Downgrading "2.0.2" to "1.0.1"

[INFO] Upgrading "1.0.1" to "1.0.2"

[INFO] Upgrading "1.0.2" to "1.0.3"

[INFO]

[INFO] Current database version is "1.0.3".

[INFO] Upgrade complete.

[INFO]

[INFO] --- maven-install-plugin:2.3.1:install (default-install) @ mavenplugintest ---

[INFO] Installing G:\_WORK\downgrade-example\pom.xml to 

[INFO] ------------------------------------------------------------------------

[INFO] BUILD SUCCESS

[INFO] ------------------------------------------------------------------------

[INFO] Total time: 1.343s

[INFO] Finished at: Wed May 16 13:55:34 CEST 2012

[INFO] Final Memory: 3M/15M

[INFO] ------------------------------------------------------------------------

[René de Bloois]

Ajay,

I posted another reply to your previous message: https://groups.google.com/d/msg/solidbase-user/4iBxNdG0Hhg/6rNGvIPH0c8J

That one contains an example.

René

[Ajay Sharma]

Hi Rene,

I have looked your example.But my example not working.

pom file.

<plugin>
<groupId>solidbase</groupId>
<artifactId>solidbase</artifactId>
<version>2.0.0-beta1</version>
<dependencies>
<dependency>

<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.16</version>
</dependency>
</dependencies>
<configuration>
<driver>com.mysql.jdbc.Driver</driver>
<username>test_user</username>
<password>T83c0!90v</password>

<target>1.0.*</target>
<downgradeallowed>true</downgradeallowed>
</configuration>

<executions>
<execution>
<id>f2_main_test</id>
<phase>pre-integration-test</phase>
<goals><goal>upgrade</goal></goals>
<configuration>
<url>jdbc:mysql://localhost:
3306/f2_main_test</url>
<upgradefile>src/main/sql/
f2_biz/f2_main_db_versioning.sql</upgradefile>
</configuration>
</execution>
</executions>
</plugin>

I have set downgradeallowed true.

my f2_main_db_versioning.sql file.

--* ENCODING "ISO-8859-1"

--* // Copyright 2006 René M. de Bloois

--* // Licensed under the Apache License, Version 2.0 (the "License");
--* // you may not use this file except in compliance with the
License.
--* // You may obtain a copy of the License at

--* // http://www.apache.org/licenses/LICENSE-2.0

--* // Unless required by applicable law or agreed to in writing,
software
--* // distributed under the License is distributed on an "AS IS"
BASIS,
--* // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
implied.
--* // See the License for the specific language governing permissions
and
--* // limitations under the License.

--* //
========================================================================



--* DEFINITION
--* SETUP "" --> "1.1"

--* UPGRADE "" --> "1.0.1"
--* UPGRADE "1.0.1" --> "1.0.2"
--* UPGRADE "1.0.2" --> "1.0.3"

--* DOWNGRADE "1.0.3" --> "1.0.1"
--* /DEFINITION

--* SETUP "" --> "1.1"
--* SECTION "Creating SolidBase control tables"
CREATE TABLE DBVERSION
(
VERSION VARCHAR(20),
TARGET VARCHAR(20),
STATEMENTS INTEGER NOT NULL,
SPEC VARCHAR(5) NOT NULL
);

CREATE TABLE DBVERSIONLOG
(
TYPE VARCHAR(1) NOT NULL,
SOURCE VARCHAR(20),
TARGET VARCHAR(20) NOT NULL,
STATEMENT INTEGER NOT NULL,
STAMP TIMESTAMP NOT NULL,
COMMAND VARCHAR(4000),
RESULT VARCHAR(4000)
);

CREATE INDEX DBVERSIONLOG_INDEX1 ON DBVERSIONLOG ( TYPE, TARGET );
--* /SETUP

--* UPGRADE "" --> "1.0.1"
--* SECTION "initial schema setup (should already created using sql
dump) "
--* /UPGRADE

--* UPGRADE "1.0.1" --> "1.0.2"

--* SECTION "Creating table AdgroupDailyRollUp"
--* INCLUDE "upgrade1.0.2.sql"
--* /UPGRADE

--* UPGRADE "1.0.2" --> "1.0.3"

--* SECTION "Creating table Category"
--* INCLUDE "upgrade1.0.3.sql"
--* /UPGRADE

--* DOWNGRADE "1.0.3" --> "1.0.1"
--* SECTION "drop Category"
--* INCLUDE "rollback1.0.3.sql"
--* /DOWNGRADE

I want to go from 1.0.3 to 1.0.1.Is it possible ?

I need to change target property in the pom file.

Thanks
Ajay

[Ajay Sharma]

Hi Rene,

I am able to use the DOWNGRADE functionality but I am listing my
requirement.

For example :

--* DEFINITION
--* SETUP "" --> "1.1"
--* UPGRADE "" --> "1.0.1"
--* UPGRADE "1.0.1" --> "1.0.2"
--* UPGRADE "1.0.2" --> "1.0.3"

--* UPGRADE "1.0.3" --> "1.0.4"
--* DOWNGRADE "1.0.4" --> "1.0.3"

--* /DEFINITION

As per above definition I have upgrade up-to 1.0.4 level and downgrade
from 1.0.4 to 1.0.3.

If I want to use DOWNGRADE then I need to specify in target property
to 1.0.3.This is working fine.

But suppose I have below definition :

--* DEFINITION
--* SETUP "" --> "1.1"
--* UPGRADE "" --> "1.0.1"
--* UPGRADE "1.0.1" --> "1.0.2"
--* UPGRADE "1.0.2" --> "1.0.3"

--* DOWNGRADE "1.0.3" --> "1.0.2"
--* UPGRADE "1.0.3" --> "1.0.4"
--* DOWNGRADE "1.0.4" --> "1.0.3"
--* /DEFINITION

Whether its possible to have 2 downgrade ? because when I am using it
I am getting below message and build fails.
Expecting not more than 1 record to be updated, not 2 -> [Help 1]

Please suggest what should I do .

Thanks
Ajay

[Arvind Kumar]

Hi,

Can you please provide me compiled solidstack jar file?

Regards,
Arvind Kumar