Is it possible to have some scripts journalled and some run in every time?

[Helen Emerson]

Hi,

I'm looking for a tool that can do database migrations so I've been having a play around with DBUp. 

I am quite a big fan of the idea of changes being idempotent where they can be, so I wanted to try dropping and recreating my procs and static tables each time. Is it possible to have some things journaled but some things not? 

I tried using the NullJournal, but it seems like you can't set a different journal for each script. Ordering of scripts is quite important so if I try to split the static ones from the versioned ones, I'm going to get into quite a mess. 

Is this just not the right way to be doing things in database land? 

Helen

[Darrell Tunnell]

Hi Helen,

I would typically not drop / recreate things everytime - usually you'd have your create script which is run once. To make changes you would then write an alter script rather than drop / recreate to apply the change. The idea is if there are no changes to an object in the database then it won't be touched. I have needed to run scripts in the past in every deployment but i have done this manually in the console application either before or after dbup does its thing. For example a script to check if the database exists and if not, create it. A script to ensure a user exists and if not create it..
Does this help?

Kind Regards
Darrell

--
You received this message because you are subscribed to the Google Groups "DbUp Discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dbup-discuss...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Corneliu I. Tusnea]

I know I'm late to this discussion but maybe it does help you.

We added a filter in DbUp to have all files marked as .rerun.sql run on every deployment. This allows us to have SP, Functions and Views and Triggers (mostly) re-run on every deployment.

This adds some time to running the upgrade but provides a massive value in having every SPs, Functions, Views and Trigger in it's own file that then keeps it's own history.

It makes it very easy to do a "View History" in source control to see how that evolved.

Unfortunately with the current design of DbUp you have no clue in 6 months time how an SP changed over time.

Our incremental running scripts are in a separate folder, numbered (001, 002.... 400+ now) and they only change schema changes (new tables, columns, deletes, data upgrades and indexes).

This is our folder structure and all upgrades run in this order:

- AA (some pre-deployment scripts we need to run every time)

- aspnet (all creates for default aspnet tables like user and sessions)

- AStaticFunctions - few functions with referential data used through upgrade scripts - e.g. "enums" 

- Changes100

- Changes200

... folders with 100 files for incremental changes

- Triggers

- XA-Views - all the views

- XB-UserDefinedFunctions

- XC-StoredProcedures

- ZPostChanges100

- ZPostChanges200

... few more set of files with folders generally doing data upgrades that use the stored procedures created earlier

This works quite well, allows us to start a DB from scratch or upgrade any DB from any version to "now" and allows me to have every file versioned so I can track it's history.

My 2 cents,

Corneliu

[Paweł Kmieć]

There is no API in DbUp to configure it directly. However one can trick DbUp to behave like that by plugging in custom journal like below:

 /// <summary>
 /// This journal tells DbUp to always run scripts from Programmability folder
 /// </summary>
 /// <remarks>
 /// The trick is following:
 /// - we tell DbUp in GetExecutedScripts that no script from Programmability has ever been run
 /// - (so DbUp runs them every time)
 /// - we don't change behaviour of "StoreExecutedScript", so each script run is still stored in Journal
 /// </remarks>
 public class ProgrammabilityReRunningJournal : IJournal
 {
 
 private readonly IJournal _innerJournal;
 
 public ProgrammabilityReRunningJournal(IJournal innerJournal)
 {
 _innerJournal = innerJournal;
 }
 
 public string[] GetExecutedScripts()
 {
 return _innerJournal.GetExecutedScripts().Where(x => !x.Contains("Programmability")).ToArray();
 }
 
 public void StoreExecutedScript(SqlScript script)
 {
 _innerJournal.StoreExecutedScript(script);
 }
 }

configure with something like below:

        public static UpgradeEngineBuilder WithProgrammabilityReRunningJournal(this UpgradeEngineBuilder builder, string schema, string table)
        {
            builder.Configure(c => c.Journal = new ProgrammabilityReRunningJournal(new SqlTableJournal(() => c.ConnectionManager, () => c.Log, schema, table)));
            return builder;
        }

Cheers,

Pawel