Hi Nathan,
My apologies for the late reply.
Yes, that's exactly what I meant.
As for the MD5 part, which I had forgotten in my first post, why wouldn't it
be possible? I guess you would compare the MD5 computed from the changelog
(which you have when the script is generated) with the one stored in the
databasechangelog table, and associated with the changeset's id and author.
You would not need to have the DB compute the hash itself; the "enhanced"
script would be like the following:
------------------
if not exists (select 1 from databasechangelog where id='id1' and
author='me') begin
create table XXX...
end
else begin
if 'abcdef0123456789' <> (select md5sum from databasechangelog where
id='id1' and author='me') raiserror 'Wrong hash'
end
------------------
Should a "wrong hash" error occur, no need to rollback the previous changes:
thanks to the if's, they would be ignored after the changelog is fixed and
the script generated again.
I agree that the preconditions would be much more difficult to implement,
though.
Thanks and regards
Thomas
On Thu, Apr 9, 2009 at 12:14 AM, Voxland, Nathan <
nvoxl...@intelligentinsites.com> wrote:
> The way I understand it, you guys are looking for a way to run updateSQL,
> but not pass it a database connection to check the databasechangelog table
> of, but instead output all SQL that would be run against an empty
> databasechangelog tablem with each changeset block having an “if row exists”
> switch. Is that right?
>
>
>
> The way I would implement it would be to allow you to call updateSQL but
> instead of –databaseUrl=jdbc:… use –databaseUrl:unknown-oracle
>
>
>
> Then we would create a new Database type that says that the changelog table
> does not exist, but uses the passed database type to generate the correct
> sql output. In the JdbcOutputTemplate, we would add the if block around the
> generated SQL.
>
>
>
> The resulting SQL would be database specific (like the updateSql is) and
> would not be possible to do for databases that do not support if statements
> in SQL, but we could probably just implement the functionality for Oracle,
> DB2, and MSSQL and hit all the databases that people would want to use this
> functionality for.
>
>
>
> It would not check for MD5Sum differences (unless we can do that in the if
> statement?) and it does not do changeSet level preconditions (like
> updateSql). Your resulting SQL file will also grow very large over time,
> though that may be manageable. These may not be a bad prices to pay,
> however, for people with this style of development. It may also help in
> situations where DBAs do not want to run liquibase as they are just getting
> an SQL file and all liquibase useage is hidden from them (although I’m not
> sure what the political battles are).
>
>
>
> Nathan
>
>
>
>
>
>
>
> *From:* Paul Keeble [mailto:cs...@yahoo.co.uk]
> *Sent:* Wednesday, April 08, 2009 4:49 PM
> *To:* liquibase-user@lists.sourceforge.net
> *Subject:* Re: [Liquibase-user] Generating idempotent SQL scripts
>
>
>
> Gents,
>
>
>
> I can see why we want it, I fight this battle with clients every week and
> its not a pretty one. This approach works only when you ignore about 90% of
> Liquibase's functionality, including ignoring its cross database
> compatibility. If you build a tool for Oracle or a couple of other Databases
> that can do something like this then its possible to create a simple SQL
> based tool that generates a system that self checks and can be run without a
> complex tool being present. I wrote one of these not more than 3 months ago
> for a client because politically they would not accept an open source
> solution. Infact all the clients that couldn't use liquibase did so for
> political, not technical reasons.
>
>
>
> Personally I don't think this is the right solution, I think a more
> functional tool is what is needed, this is really dark age approach to the
> problem. What you are asking for is akin to diff/patch to a VCS but rather
> than just patch you now want it to carry extra information so that it knows
> when the patch would fail by doing full history checks.As an approach that
> would never work because patch, being VCS agnostic and simple can't express
> it correctly for the different VCS's. Patch/diff is a workaround for a
> modern VCS to get changes across to set of source code when the normal
> connectivity is broken, but its highly limited as it doesn't know about
> tags, branches, common history, conflicts that aren't conflicts etc. The
> same is true of carrying around just SQL to do Liquibase's job, it doesn't
> know about preconditions, about variables, about different database types,
> about checksum mismatches etc etc. Expressing all that in database agnostic
> SQL is impossible. A solution for Oracle, while technically horrific, is
> probably possible, although its not a set of procedures I would particular
> want to write and maintain alongside a whole host of Java code that does the
> same thing, just so that this one use case works on Oracle.
>
>
>
> I think the tool you are talking about is really a workaround for bad
> organisation setup and bad development practice, because I've never heard a
> good reason for doing it. Fix the process and Liquibase is a great tool, do
> something absurd and bad and Liquibase's vision won't fit anymore.
>
>
>
> I stand by my original assertion that I think its a bad idea, but if a
> patch appears with this functionality and its done so in a DRY way then I'll
> help make it and review it. I just don't think it can be done without
> repeating 80% of what Liquibase does in Database specific procedures, and
> that is a solution I don't think is worth building.
>
>
>
> Paul
>
>
> ------------------------------
>
> *From:* Ben Wolfe <b...@openmrs.org>
> *To:* liquibase-user@lists.sourceforge.net
> *Sent:* Wednesday, 8 April, 2009 19:52:53
> *Subject:* Re: [Liquibase-user] Generating idempotent SQL scripts
>
>
> 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
>
>
>
> 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
>
>
--
..........................................................
Thomas VIAL
OCTO Technology
..........................................................
50, Avenue des Champs-Elysées
75008 Paris
Tél : (33) 1 58 56 10 00
Fax : (33) 1 58 56 10 01
GSM : (33) 6 28 50 07 64
Web : http://www.octo.com/
..........................................................
------------------------------------------------------------------------------
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
