Having this kind of tooling would be great. There are many users that
are running, say, database version 1.4 and want to upgrade to the
latest. If they were running our war file things would be fine, but
they are just using our database model. Our liquibase xml file has
changesets from version 1.2 all the way to version 1.5. Liquibase can
export a sql file currently, but it expects to run all of the
statements. This won't work because the user has run all statements
between 1.2-1.4 and only needs 1.4-1.5.
In the past we would keep our database updates in a mysql script that
checked their database version and only ran the "changesets" that hadn't
been run yet. In mysql you have to do this with procedures to be able
to do the "if" statements. See below.
Surely liquibase could export something like this just modified to look
at the databasechangelog instead of our database_version. I don't know
what this looks like in other databases, but it probably is similar.
update-to-latest.sql:
#----------------------------------------
# OpenMRS Datamodel version 1.1.10
# Ben Wolfe May 31st 2007
# Adding township_division, region, and
# subregion attributes to patient_address
# and location tables
#----------------------------------------
DROP PROCEDURE IF EXISTS diff_procedure;
delimiter //
CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10))
BEGIN
IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version,
'.', '0') FROM global_property WHERE property = 'database_version') THEN
SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:'
FROM dual;
ALTER TABLE `person_address` ADD COLUMN `region` varchar(50) default
NULL;
ALTER TABLE `person_address` ADD COLUMN `subregion` varchar(50) default
NULL;
ALTER TABLE `person_address` ADD COLUMN `township_division` varchar(50)
default NULL;
ALTER TABLE `location` ADD COLUMN `region` varchar(50) default NULL;
ALTER TABLE `location` ADD COLUMN `subregion` varchar(50) default NULL;
ALTER TABLE `location` ADD COLUMN `township_division` varchar(50)
default NULL;
UPDATE `global_property` SET property_value=new_db_version WHERE
property = 'database_version';
END IF;
END;
//
delimiter ;
call diff_procedure('1.1.10');
#----------------------------------------
# OpenMRS Datamodel version 1.1.11
# Ben Wolfe Dec 21st 2007
# Removing the unneeded auto increment values
# on patient_id and user_id.
#----------------------------------------
DROP PROCEDURE IF EXISTS diff_procedure;
delimiter //
CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10))
BEGIN
IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version,
'.', '0') FROM global_property WHERE property = 'database_version') THEN
SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:'
FROM dual;
ALTER TABLE `patient` MODIFY COLUMN `patient_id` int(11) NOT NULL;
ALTER TABLE `users` MODIFY COLUMN `user_id` int(11) NOT NULL;
UPDATE `global_property` SET property_value=new_db_version WHERE
property = 'database_version';
END IF;
END;
//
delimiter ;
call diff_procedure('1.1.11');
etc etc etc.....
Ben
Thomas Vial wrote:
Hi all!
I am considering introducing LiquiBase into a project I'm currently
working on. After thinking about the process we usually follow wrt
staging from development to Q&A and then to production, I feel that a
very useful feature would be the ability to update any existing
database to the most recent version, whatever changes have been
performed on that particular DB before -- the same, unique script
would run whatsoever and achieve the desired result.
I have reviewed various threads on the mailing list, particularly this
one:
http://sourceforge.net/mailarchive/forum.php?thread_name=4ef551420902140711j5a434eacg7250ec84af2c7b04%40mail.gmail.com&forum_name=liquibase-user
<http://sourceforge.net/mailarchive/forum.php?thread_name=4ef551420902140711j5a434eacg7250ec84af2c7b04%40mail.gmail.com&forum_name=liquibase-user>
I understand that translating all of elaborated LB's functionalities
into some dialect-dependant of SQL is virtually impossible (see Paul's
answer in the aforementioned thread).
However, in my mind it would be easy to add a test for every
changeset, with SQL code like this one:
--------------------------------------------
if exists (select 1 from databasechangelog where id='id1' and
author='me' and filename='...') begin
create table XXX... // SQL generated for changeset 1
end
if exists (select 1 from databasechangelog where id='id2' and
author='me' and filename='...') begin
create table XXX... // SQL generated for changeset 2
end
--------------------------------------------
The id's, author's and filename's, which I understand make up the
unique key of the changeset, would be pulled from the changelog just
like the contents of their corresponding changesets. The syntax for
the if's would be dialect-dependant of course, hopefully not a big deal.
I guess the functionality, if used correctly (i.e. with staging
environments undergoing the same changes in the same order, only at
different rates), could have some strong benefits, some of which were
pointed by others before:
* being able to generate the SQL from the changelog only -- no
connection to the target DB required
* packaging a unique SQL script with the artifacts of the project
(e.g. maven assembly); that same script could be run confidently on
all environments, with no adjustment like removing snippets that are
irrelevant in some environments
* thus having the package validated as a whole, enforcing consistency
of all its components
* having all production staff pull their stuff from the same, unique
package: SQL goes to the DBA, WAR goes to the web team, and so on
* making scripts idempotents: if the DBA's procedure requires to
re-run the script for some reason, all parts that were not executed
the first time, if any, would have no side effect
* not forcing DBAs into running LB themselves against the databases.
These folks usually have their own set of tools and don't like to have
to adopt others (especially all that Java mumbo-jumbo, we real men
only know of SQL, perl and ksh ;-)
Of course, making independant changes by hand on the production DB
would break it all, just as it would break the result of an updateSQL
command. Using the suggested functionality would require just as much
discipline. This is why I think it would make sense, as an optional
feature of course. I guess some care may be necessary with transaction
management and ability to rollback (like knowing if a changeset was
interrupted in the middle of a previous run).
Any thoughts?
Sorry if you feel this topic is just another avatar of the thread I
mentioned above. I wanted to point out a cheap, possible solution, and
some benefits beyond all those that were already identified.
Thomas
------------------------------------------------------------------------
------------------------------------------------------------------------------
This SF.net email is sponsored by:
High Quality Requirements in a Collaborative Environment.
Download a free trial of Rational Requirements Composer Now!
http://p.sf.net/sfu/www-ibm-com
------------------------------------------------------------------------
_______________________________________________
Liquibase-user mailing list
Liquibase-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/liquibase-user
------------------------------------------------------------------------------
This SF.net email is sponsored by:
High Quality Requirements in a Collaborative Environment.
Download a free trial of Rational Requirements Composer Now!
http://p.sf.net/sfu/www-ibm-com
_______________________________________________
Liquibase-user mailing list
Liquibase-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/liquibase-user
