Adding SolidBase to an existing Database

[Chris]

Is there any documentation on adding SolidBase to an existing
production database? I am not concerned about wiping out all the data
from dev and qa deployments to apply the database, but is there any
way I can take a snapshot (ie: mysqldump) of the current production
database, apply that as the baseline version (1.1) and then apply
upgrade paths from there?

[René de Bloois]

Chris,

The problem you are facing is that the database is already in
production and contains data that you do not want to include in your
upgrade script. What I would do is dump the structure and data of the
database and manually remove the excess data, or find some other way
to exclude this excess data. This script can then be added to the
upgrade script as the first initial version.

The resulting upgrade script can then be used on development and qa
databases (after dropping all tables and other objects from them).

For the production database you have to do one extra step. You need to
execute the following SQL on it:

CREATE TABLE DBVERSION
(
SPEC VARCHAR(5) NOT NULL,
VERSION VARCHAR(20),
TARGET VARCHAR(20),
STATEMENTS INTEGER 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 );

INSERT INTO DBVERSION ( SPEC, VERSION, STATEMENTS )
VALUES ( '1.1', '<your version number>', 1 );

This will create the SolidBase control tables and insert one record
with the correct information in the DBVERSION table. When you then run
SolidBase on the production database, it will recognize the version of
the database and will skip the initial creation.

You should always backup the production database before doing things
like this, of course.

-/-

Regarding the script you generate, if there is a lot of data inserted
in this script you would probably be better off using the IMPORT
command with CSV data, that's much faster. Or, you could use the ANSI
SQL syntax for INSERT to insert multiple records at once:

INSERT INTO <table> ( <columns> )
VALUES ( <values> ),
VALUES ( <more values> ),
... etc

You might even send multiple INSERTs as one command like this:

--* SET DELIMITER ISOLATED GO
INSERT ...;
INSERT ...;
INSERT ...
GO

I have seen this work on Derby or HSQLDB. I don't know if this works
on MySQL however.

But the CSV option would be the fastest by far.

-/-

If you are interested in a generic script that drops all tables from
the database, I have one for you if you're interested.

Regards,
René de Bloois

[René de Bloois]

Chris,

Don't forget to add a COMMIT after the INSERT INTO DBVERSION :-)

René

[Chris]

So basically having a script that mirrors this basic format:

--* DEFINITION
--* SETUP "" --> "1.66"
--* /DEFINITION

--* SETUP "" --> "1.66"

--* SECTION "Creating Solidbase Control Tables"

USE ${hibernate.mysql.schema};

CREATE TABLE DBVERSION
(
SPEC VARCHAR(5),
VERSION VARCHAR(20),
TARGET VARCHAR(20),
STATEMENTS DECIMAL(4) NOT NULL

);

CREATE TABLE DBVERSIONLOG
(
TYPE VARCHAR(1) NOT NULL,
SOURCE VARCHAR(20),
TARGET VARCHAR(20) NOT NULL,

STATEMENT DECIMAL(4) NOT NULL,

STAMP TIMESTAMP NOT NULL,
COMMAND VARCHAR(4000),
RESULT VARCHAR(4000)
);

CREATE INDEX DBVERSIONLOG_INDEX1 ON DBVERSIONLOG ( TYPE, TARGET );

--* SECTION "Creating Structures and Seeding Data"

< all my existing data and structure goes here >

INSERT INTO DBVERSION ( SPEC, VERSION, STATEMENTS )

VALUES ( '1.1', '1.66', 1 );

--* /SETUP


This would accomplish it for the most part, right?


I was also thinking what if I just created the control tables and
inserted the row into DBVERSION without dropping or modifying any of
the existing data *manually* in production. In other words (1.66 is
the current production version in this case):

1. Backup prod :D
2. Connect to prod manually
3. Create control tables and index
4. INSERT INTO DBVERSION(SPEC,VERSION,STATEMENTS) VALUES ('1.1','1.66',
1);

Then the change script would look like this:

--* DEFINITION
--* SETUP "" --> "1.66"
--* UPGRADE OPEN "1.66" --> "1.67"
--* /DEFINITION

--* SETUP "" --> "1.66"

--* SECTION "Creating Solidbase Control Tables"

USE ${hibernate.mysql.schema};

CREATE TABLE DBVERSION
(
SPEC VARCHAR(5),
VERSION VARCHAR(20),
TARGET VARCHAR(20),
STATEMENTS DECIMAL(4) NOT NULL

);

CREATE TABLE DBVERSIONLOG
(
TYPE VARCHAR(1) NOT NULL,
SOURCE VARCHAR(20),
TARGET VARCHAR(20) NOT NULL,

STATEMENT DECIMAL(4) NOT NULL,

STAMP TIMESTAMP NOT NULL,
COMMAND VARCHAR(4000),
RESULT VARCHAR(4000)
);

CREATE INDEX DBVERSIONLOG_INDEX1 ON DBVERSIONLOG ( TYPE, TARGET );

INSERT INTO DBVERSION ( SPEC, VERSION, STATEMENTS )

VALUES ( '1.1', '1.66', 1 );

--* /SETUP

--* UPGRADE "1.66" --> "1.67"

<do some stuff that isn't in production yet>

--* /UPGRADE

Does this also solve the problem?

[René de Bloois]

Chris,

The first part of your mail has some problems:

1. The SETUP *must* be done with version number "1.1". It is something SolidBase needs. It needs it to determine how the control tables look like. That version number is stored in the SPEC column of the DBVERSION table. Maybe you have not read the Getting Started pages yet.

2. USE ${} does not work. You have to configure that in the build.xml (Ant) or pom.xml (Maven) or on the command line or properties file (which are you using?).

3. In a SolidBase upgrade file you do not need to insert a record into DBVERSION, SolidBase does that for you.

The second part of your mail has one extra problem:

4. Now you are missing the < all my existing data and structure goes here > part, which means that you can't create a dev/qa database from scratch.

This is what I meant in my previous posts:

--* DEFINITION

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

--*   UPGRADE "" --> "1.66"

--*   UPGRADE OPEN "1.66" --> "1.67"

--* /DEFINITION

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

CREATE TABLE DBVERSION

(

   SPEC VARCHAR(5),

   VERSION VARCHAR(20),

   TARGET VARCHAR(20),

   STATEMENTS DECIMAL(4) NOT NULL

);

CREATE TABLE DBVERSIONLOG

(

   TYPE VARCHAR(1) NOT NULL,

   SOURCE VARCHAR(20),

   TARGET VARCHAR(20) NOT NULL,

   STATEMENT DECIMAL(4) NOT NULL,

   STAMP TIMESTAMP NOT NULL,

   COMMAND VARCHAR(4000),

   RESULT VARCHAR(4000)

);

CREATE INDEX DBVERSIONLOG_INDEX1 ON DBVERSIONLOG ( TYPE, TARGET );

--* /SETUP

--* UPGRADE "" --> "1.66"

--* SECTION "Creating Structures and Seeding Data"

< all my existing data and structure goes here >

--* /UPGRADE

--* UPGRADE "1.66" --> "1.67"

< changes >

--* /UPGRADE

This script will now work on empty (clean) databases, and it will also work on existing databases that have been created by SolidBase. Existing dev & qa database need to be cleared out first. And I would advise to do so, because you probably can't be sure at this time if they all are correctly created.

If you run this on the production database however, you get errors because tables and other objects already exist. So you have to fool SolidBase by adding the control tables and 1 record by hand:

CREATE TABLE DBVERSION

(

       SPEC VARCHAR(5) NOT NULL,

       VERSION VARCHAR(20),

       TARGET VARCHAR(20),

       STATEMENTS INTEGER 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 );

INSERT INTO DBVERSION ( SPEC, VERSION, STATEMENTS )

VALUES ( '1.1', '1.66', 1 );

COMMIT;

Now SolidBase knows that the production database hase version 1.66, and it will only apply the 1.67 changes.

Hope this makes it more clear.

I would advise against only adding the incremental changes to the SolidBase upgrade file. But if you really want that, let me know.

Regards,

René