Crazy idea for handling stored procedures, views, etc in sql scripts

[Mark Boltuc]

Hey everyone,

I've been struggling with how to deal handling migrations for stored
procedures, views, etc. that are stored in sql scripts. I don't want
to have create a migration for each version of the script where the
script is duplicated since we already have all of our scripts under
source control in Hg. So my idea is to leverage Hg to pull the right
version of the script.

So, for the first time you create it you have a migration like:

public override void Up()
{
// This would end up executing "hg cat spCreateUser.sql -r 180" and
running it against the db

Create.StoredProcedure().FromHg().Where().FileName("spCreateUser.sql").Revision( 180 );
}

public override void Down()
{
// This would execute the typical DROP STORED PROCEDURE command
Drop.StoredProcedure("spCreateUser");
}

When you alter the sproc you could do something like:

public override void Up()
{
// This would end up executing "hg cat spCreateUser.sql -r 181" and
running it against the db

Alter.StoredProcedure().FromHg().Where().FileName("spCreateUser.sql").Revision(181);
}

public override void Down()
{
// This would end up executing "hg cat spCreateUser.sql -r 180" and
running it against the db

Alter.StoredProcedure().FromHg().Where().FileName("spCreateUser.sql").Revision(180);
}

I ran a proof of concept to prove out executing the hg command from
a .NET app and capturing the output. So in theory this sounds like it
might work great for me. So my next question is should I try and add
this as a feature to FluentMigrator or roll my own helper classes to
do it?

Thoughts?

Thanks,
Mark

[Sean Chambers]

I like this idea. Can you push it to your github so we can take a look.
I might be wanting to pull this into the main repo.

Sean

Sent from my Windows Phone From: Mark Boltuc
Sent: Friday, April 08, 2011 10:01 AM
To: FluentMigrator Google Group
Subject: Crazy idea for handling stored procedures, views, etc in sql
scripts
Hey everyone,

Alter.StoredProcedure().FromHg().Where().FileName("spCreateUser.sql").Revision(181);
}

Alter.StoredProcedure().FromHg().Where().FileName("spCreateUser.sql").Revision(180);
}

Thoughts?

Thanks,
Mark

--
You received this message because you are subscribed to the Google
Groups "FluentMigrator Google Group" group.
To post to this group, send email to
fluentmigrato...@googlegroups.com.
To unsubscribe from this group, send email to
fluentmigrator-goog...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/fluentmigrator-google-group?hl=en.

[Justin Collum]

I like the idea. I'd rather it was .FromSourceControl(string
pathFromBaseOfRepo, int revision) and the source control is set up in
the MigrationOptions class. Because I could definitely see using this,
but we use SVN.

You might also want to read, it's relevant:
http://groups.google.com/group/fluentmigrator-google-group/browse_thread/thread/c07644a4ed9a28e0

[Mark Boltuc]

Sean,

I'm going to start on it this weekend. Before I get to deep into it,
do you think it would be best to create a generic CreateDbObject and
UpdateDbObject expression builder? Or one specific for each kind of
database object (procs, views, functions, etc)

Thanks,
Mark

[Mark Boltuc]

Justin,

I definitely like that naming better. Storing the repo location in
migration options sound good too.

Thanks,
Mark

On Apr 8, 1:15 pm, Justin Collum <jcol...@gmail.com> wrote:
> I like the idea. I'd rather it was .FromSourceControl(string
> pathFromBaseOfRepo, int revision) and the source control is set up in
> the MigrationOptions class. Because I could definitely see using this,
> but we use SVN.
>

> You might also want to read, it's relevant:http://groups.google.com/group/fluentmigrator-google-group/browse_thr...

[Sean Chambers]

I would possibly change the UpdateDbObject to AlterDbObject.

Other than that, I like that. repo location should definately be passed in the commandline/nant options

[Mark Boltuc]

Hey everyone,

I finally got around to getting this feature implemented. I only
wrote it to support Mercurial but adding additional VCS Providers
should be pretty easy. I created 2 flavors of the API so we can
decide which way we would like it to end up:

Option 1 - DbObject (https://github.com/mboltuc/fluentmigrator/tree/
DbObject)

This option allows the repo path to be specified from the
migration.

Syntax:


Create.DbObject().FromHg(pathToHgRepo).Script(pathToScriptFromRepoBase,
revision)

Alter.DbObject().FromHg(pathToHgRepo).Script(pathToScriptFromRepoBase,

Option 2 - FromSourceControl (https://github.com/mboltuc/
fluentmigrator/tree/FromSourceControl)

This option removes the DbObject and FromHg syntax in favor of
less noise and command line arguments: vcsProviderType and
vcsDirectory

Syntax:

Create.FromSourceControl(pathToScriptFromRepoBase, revision)
Alter.FromSourceControl(pathToScriptFromRepoBase, revision)


What are your guys' thoughts? I personally am leaning towards Option
2.

Thanks,
Mark

[Glenn Condron]

Option two means you can only have one source control type, where as option 1 would theoretically allow FromHg, FromGit, FromTFS, etc to all be used in the same migration...right?

So I think initially I liked option 2 better, but am swaying towards 1 the more I think about it.

[Mark Boltuc]

Right, option 1 would allow that. I had the same feeling at first,
however I felt that it would be super unlikely that your scripts would
be in more than one source control system.

[Justin Collum]

Having your migrations in more than one VCS seems confusing and
unlikely. Also, I want to just be able to execute a script from VCS.
Whether that's a CREATE or ALTER would be handled in the script I'd
think.

[Glenn Condron]

Ok, so that would make the syntax something like:

Execute.FromSourceControl(path, revision);

Right? Don't we already have an Execute.Script ability? Could we extend that to be able to handle the source control scenario as well as local file system, or do we only have the capability of executing SQL not a SQL file?

[Mark Boltuc]

I originally started by having just an Execute.FromSourceControl
expression. However, that seemed cumbersome for when writing your
Down() methods. Lets say you have the following set of scripts in
source control:

spUserGet.sql Revision 1 = CREATE PROCEDURE dbo.spUserGet ...
spUserGet.sql Revision 2 = ALTER PROCEDURE dbo.spUserGet ...

If we use the Execute from SourceControl scenario, we'd have 2
migrations:

Migration 1:
public void Up()
{
Execute.FromSourceControl("spUserGet.sql", "1");
}
public void Down()
{
Execute.Sql("DROP PROCEDURE spUserGet.sql");
}

Migration 2:
public void Up()
{
Execute.FromSourceControl("spUserGet.sql", "2");
}
public void Down()
{
// need to do a drop and execute since our rev 1 script is a CREATE
Execute.Sql("DROP PROCEDURE spUserGet.sql");
Execute.FromSourceControl("spUserGet.sql", "1");
}

If we move to a Create.FromSourceControl / Alter.FromSourceControl
syntax, the generator takes care of replacing the ALTER or CREATE
based on your migration. So the migrations would look something like:

Migration 1
// Same as above

Migration 2
public void Up()
{
Alter.FromSourceControl("spUserGet.sql", "2");
}
public void Down()
{
// generator replace the CREATE with an ALTER and runs your script
Alter.FromSourceControl("spUserGet.sql", "1");
}

Also, in the past I've run into some issues where dropping and create
a db object would screw up the internal object dependency tracking of
Sql Server.

Another reason I prefer is the Create/Alter syntax is that the
Migration is driving what your intent was. So if your sql script was
checked in as an ALTER the first time you wouldn't have to check it
out switch it to a CREATE, check it in, check it out again, switch it
back to an ALTER.

I could see the Execute.FromSourceControl syntax being useful for
scenarios where you may have seed data or something in source
control. Or anything that isn't creating / modifying db objects.

Thanks,
Mark

[Justin Collum]

You can get around a lot of these things by using "Drop / Create as
blank / Alter" pattern. You do lose your query plans though, so
there's a first hit effect. Seems minor.

But when you're building an API you have to keep in mind what people
would be doing... still, I think a straight exec from source control
would be fine without the ALTER. Because suddenly you have to consider
other DBs and figure out who all supports ALTER.

You're also mixing up intent possibly. What if my script is run as a
CREATE but the script itself contains ALTER?

Nope, definitely voting for Exec.FromSourceControl(script, version).

> > > > > > > > To post to...
>
> read more »

[Justin Collum]

>If we move to a Create.FromSourceControl / Alter.FromSourceControl
>syntax, the generator takes care of replacing the ALTER or CREATE
>based on your migration. So the migrations would look something like:

This seems like asking for a world of bugs. Editing the SQL script
directly seems like asking for trouble.

On Apr 14, 8:27 pm, Mark Boltuc <mbol...@gmail.com> wrote:

> > > > > > > > To post to...
>
> read more »

[Mark Boltuc]

I see value in both.

> > Because suddenly you have to consider
> > other DBs and figure out who all supports ALTER.

FM already accomodates this by having custom generators per DB. So I
don't see this as an issue.

> > You're also mixing up intent possibly. What if my script is run as a
> > CREATE but the script itself contains ALTER?

In my case, I want FM migration to drive the intent.

> > This seems like asking for a world of bugs. Editing the SQL script
> > directly seems like asking for trouble.

Possibly, but I think a simple Regex replace would be pretty darn
accurate.

My vote is to make both options available, this make it the most
flexible and would fit in nice with the current FM api.

> ...
>
> read more »

[an...@willand-it.com]

I have been following the conversation and there are some interesting issues being raised here. Both points of view seem valid but there is point being missed here in the api.

Script filles are general db specific so we either need some kind of convention base naming that allows for a scripts to be chosen for each db type. A script parser and converter (Bad idea), or formal method to name the scripts per db type from and anonymous object, etc.

Personally I think that the api should use the Execute method because that says, I have no idea what you are doing but I am just going to run it.

To answser a question earlir, we support execute.fromFile() and execute.fromEmbeddedResource(), so your source control system I guess should follow this.
Sent from my BlackBerry® wireless device

[Mark Boltuc]

Isn't it safe to assume that the script files that you'll be running have to match the database specified by the /provider param (I.e. /provider=sqlserver2008)?  

[Andrew Busby]

Yep, that was the essence of what I was saying.  Should it not be possible to have say a folder in source control called Sqlserver2008 and one call mysql and have FM choose the correct file based on the provider?

[Mark Boltuc]

No, i don't think FM should try and convert scripts to whatever database you are running against.  

[Andrew Busby]

I think that you are miss understanding the point that I am making.

 

I am not suggesting that FM should convert scripts, in fact I even said it was a bad idea in my original email. 

 

What I am saying that if a team creates a migration script for MySql and then a version of the same script for SQL Server.  So that is two scripts targeted at two different database that achieve the same result, that it should be possible for FM to choose NOT convert the correct script for the provider!

[Mark Boltuc]

Ah, ok I think I understand what your saying now.  Sorry! 

In that case wouldn't you have separate FM projects? One for the SQL Server and another for the MySQL?

I typically use the Console Runner to run my migrations and specify the database to target with the provider param.  It would seem weird to me if the migrations effected two different types of database.  

[Mark Boltuc]

I think this thread is getting a bit off topic.  

My original question was should we add support to FM to be able to run a script from source control?  If so, what's the best API for this?

I think we can all agree that this would be a useful feature just as Execute.Script is.

So my next questions are:

1) Should the Migration specify the Source Control provider or should it be specified as an Option (like dbType)?

2) Do we like the Create.FromSourceControl / Alter.FromSource control syntax?

    a.  If so, do we like having the GenericGenerator swapping the CREATE or ALTER in the script to match the calling syntax? I.e. If the script contained ALTER and your migration used Create.FromSourceControl it would replace the ALTER with a CREATE.

3) Do we also like the Execute.FromSourceControl syntax?

The original thought from Sean was to use the Create / Alter syntax with the Source Control Provider specified as an Option.

Thanks,

Mark

[Jeff Treuting]

I've been following along with the thread (on vacation actually but thought I'd chime in quickly).

1) I think it shoul dbe specified as an option so that you can change source control systems without having to recompile your migration as I can see this happening.  On a recent project we switched source control in the middle of it and I wouldn't have liked having to go back through and change the syntax in my migration code.

2) I don't like the Create.FromSourceControl and Alter.FromSourceControl because I think the Create, Alter and Update syntax should be database agnostic (as much as possible) and should work against different databases by changing that option on the runner.  While the Execute syntax are running scripts or files or whatever that are database specific by nature since they are actual SQL being run.

3) I like the Execute.FromSourceControl syntax for the reasons mentioned above.

That is my two cents anyways.  Now back to my vacation :)

Jeff Treuting
Truburn Solutions, LLC
(206) 393-0717
www.truburn.net
je...@truburn.net