Script ordering

[David Atkinson]

I'm pondering how script ordering will work in DbUp.

Say I have the following scripts:

1.sql

2.sql

3.sql

and I want to insert a script between 1 and 2, which would naturally be 1.1.sql. What happens is that when running to a new database this will be run the 1.1 script before the 1 script. 

Also, if I add 10.sql, this gets run before 1.sql, not at the end, which is what I would hope.

Any thoughts or guidance on this?

David

[Darrell Tunnell]

I've tended to use padding in my naming scheme, i.e 0001-foo.sql, 0002-bar.sql so when you get to "0010-" it still works.

In your example I am not sure why you would need to "go back in time" and create a 1.1 script thats between 1 and 2. Typically scripts are additive because you are always rollimg forward to the next version and very rarely (if ever) rolling back.

I believe you can take control of script ordering so if you wanted to order the scripts using your own comparison (for example semver ordering?) you could do that.

---

From: dbup-d...@googlegroups.com <dbup-d...@googlegroups.com> on behalf of David Atkinson <dvdt...@gmail.com>
Sent: Monday, March 5, 2018 5:19:20 PM
To: DbUp Discuss
Subject: Script ordering

 

--
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.

[David Atkinson]

Yes, I figured that padding with zeros would be the best option. Or maybe timestamps? For the 1.1 case I'm thinking ahead to scenarios where there are two independent branches of the same database, and there's a situation where merged migration scripts need to be inserted in the right place to avoid dependency ordering issues. If I have 0001-foo.sql and 0002-bar.sql and my merged branch has a script that needs to be renamed to be between the two, what are my options? 

[Kenneth]

Just FYI, if you want to timestamp them, you can use my Visual Studio extension Add DbUp File to automatically do it.

[David Atkinson]

Thanks for this. Automatically generating the names seems like a sensible thing to do, as all I want as a user is for the next script to be added in the right place. 

I have a couple of questions.

1) What is the rationale behind "Filenames are automatically prefixed with ccyymmdd_hhmmss_ and restricted to one period"? (ie, the restricted to one period bit)
2) Have you ever had to insert a script between two other scripts? If so, I guess the beauty of using timestamps is that you'd just create one manually using a timestamp in-between. It's sort of breaks the use of the timestamp as a true audit of when changes were authored, but I guess they're just a device for correct ordering and version control is the true audit.

David

[Darrell Tunnell]

The period thing is probably due to embedded resource names. When you embed a file like a/b.c.foo its embedded resource name becomes a.b.c.foo - you have lost the original file nane information. If you know there is only one period in the file name you can get the filename back via convention.

Get Outlook for Android

---

From: dbup-d...@googlegroups.com <dbup-d...@googlegroups.com> on behalf of David Atkinson <dvdt...@gmail.com>

Sent: Thursday, March 15, 2018 10:40:25 AM
To: DbUp Discuss
Subject: Re: Script ordering

 

[Kenneth]

That is exactly the reason.  We have a couple of customizations in DbUp that essentially allow us to ignore the folder names when processing the files, so we need that convention.  By ignoring the folder names and only logging the filenames themselves (and being assured the filenames are unique), it allows us to move the scripts between folders if needed.  For example, we have a "Not Ready" folder that if a script gets pulled from a release at the last minute, we can just move the script to that folder and DbUp won't run it during the next migration.  Not a safe practice, but it does allow us to "cherry-pick" items for a release in times of emergency.

So yes, the timestamps are really about order and uniqueness - not really for audit purposes.