Microsoft SQL Server 2008 Engine

[Brian mcKeen]

I like the look of sqitch but our database of choice at work is Microsoft SQL Server 2008.

Is there anyone else who would like an sqitch engine for MS SQL Server?

I would be willing to help with writing one. But I would need some help.

Brian

[Brian mcKeen]

I decided to do this on my own and I have created a fork of sqitch at https://github.com/brianmckeen/sqitch as a first step to possibly adding it to theory/sqitch.

This works well, but I made a few changes to DBIEngine.pm to get it to work, and this file would have to be reworked, if this fork was to be merged back into theory/sqitch.

Changes I made to DBIEngine.pm include:

Changed LIMIT to TOP
Added all tables in the registry database to a sqitch schema
Added an order_id identity field to the registry database tables changes, events and tags as committed_at field could not separate the order of changes
Changed all placeholders to ? to prevent 'Can't mix placeholder styles' error
Use dbh odbc_force_bind_type DBI::SQL_VARCHAR to prevent error with parameters

I am still to add changes to the README and I have converted the tutorial for SQL Server but not added it.

I used Win32-SqlServer-2.008 (https://metacpan.org/release/Win32-SqlServer) to connect via ODBC to SQL Server and this has to be added to Perl.

Can anyone help with adding this to theory/sqitch?

Thanks

[David E. Wheeler]

On Sep 18, 2014, at 3:34 AM, Brian mcKeen <brian....@nhs.net> wrote:

> I decided to do this on my own and I have created a fork of sqitch at https://github.com/brianmckeen/sqitch as a first step to possibly adding it to theory/sqitch.

Ooh, great, thank you. I was asked about SQL Server in the FLOSS Weekly interview yesterday, so it’s great to see movement on this.

> This works well, but I made a few changes to DBIEngine.pm to get it to work, and this file would have to be reworked, if this fork was to be merged back into theory/sqitch.
>
> Changes I made to DBIEngine.pm include:
>
> Changed LIMIT to TOP

Yeah, if you need to do this, you’ll have to override those methods in the engine class. Oracle had a similar issue where LIMIT and OFFSET aren't supported, so Engine::oracle reimplements the methods that use them.

> Added all tables in the registry database to a sqitch schema

Is there no way to set a search path so that it becomes the default schema? If not, perhaps follow the Oracle model and just put it into the default schema:

http://technet.microsoft.com/en-us/library/dd283095(v=sql.100).aspx

Which is the schema for the user that is doing the deployment. Kind of sucks that if the user has no default schema specified that it defaults to "dbo".

http://stackoverflow.com/a/4942223/79202

Seems a bit hinky. So maybe we need to modify DBIEngine so that subclasses can specify a schema, if necessary. The default will be no schema, but the SQL Server engine would specify it.

> Added an order_id identity field to the registry database tables changes, events and tags as committed_at field could not separate the order of changes

Why not? Looks like the datetime2 has more than sufficient precision, no?

http://msdn.microsoft.com/en-us/library/bb677335.aspx

> Changed all placeholders to ? to prevent 'Can't mix placeholder styles' error
> Use dbh odbc_force_bind_type DBI::SQL_VARCHAR to prevent error with parameters

Yeah, but the other engines all specify a database function to fill in the timestamp value. For the SQL Server engine, I think it should, too, probably using GETUTCDATE().

http://msdn.microsoft.com/en-us/library/ms178635.aspx

That will get you the nanosecond precision that ordering should work properly. In fact, if you can specify it as the default value in the CREATE TABLE statement, then the default engine _ts_default() value 'DEFAULT' should “just work.”

> I am still to add changes to the README and I have converted the tutorial for SQL Server but not added it.

Oh, nice! The tutorials take a lot of work to get right; I appreciate you taking the time on that.

> I used Win32-SqlServer-2.008 (https://metacpan.org/release/Win32-SqlServer) to connect via ODBC to SQL Server and this has to be added to Perl.

Hrm. Does DBD::Sybase work? I had assumed that’s what we would use.

http://www.perlmonks.org/?node_id=392385

Another question: Why is the engine called "sqlcmd"? I assumed it would be called "sqlserver" or "mssql". The engine should be named for the RDMBS, not the command-line client.

> Can anyone help with adding this to theory/sqitch?

I can. :-) I will add some other comments on the diff towards getting things toward a merge sometime soon.

https://github.com/brianmckeen/sqitch/compare/theory:master...master

Best,

David

[Dan Muey]

I am interested in this and would like to help you Brian. I'll dive into your fork when I can.

I need to look into it more but I *think* the biggest hurdle** to an mssql driver would be to set the DBH up w/ the right driver (and the drivers are sort of weird in regards to prereqs):

if ($^O eq 'MSWin32') {
      # make dbh either DBD::ODBC or DBD::ADO based on environment and/or config
}
else {
      # make dbh DBD::Sybase (possibly DBD::ODBC if its available based on environment and/or config) 

      # DBD::Sybase requires freetds
}

[**] http://dolio.lh.net/~apw/doc/HOWTO/HOWTO-Connect_Perl_to_SQL_Server.pdf

[David E. Wheeler]

On Mar 6, 2015, at 8:48 AM, Dan Muey <drm...@gmail.com> wrote:

> I am interested in this and would like to help you Brian. I'll dive into your fork when I can.
>
> I need to look into it more but I *think* the biggest hurdle** to an mssql driver would be to set the DBH up w/ the right driver (and the drivers are sort of weird in regards to prereqs):
>
> if ($^O eq 'MSWin32') {
> # make dbh either DBD::ODBC or DBD::ADO based on environment and/or config
> }
> else {
> # make dbh DBD::Sybase (possibly DBD::ODBC if its available based on environment and/or config)
> # DBD::Sybase requires freetds
> }

Agreed. URI::mssql currently uses ODBC, but you can read its attributes to make the decisions as above. Something like:

if ($^O eq 'MSWin32' && try { require DBD::ADO }) {
# Connect via ADO.
} elsif (try { require DBD::ODBC }) {
# Connect via ODBC.
} elsif (try { require DBD::Sybase }) {
# Connect via DBD::Sybase.
} else {
hurl $self->key => __x(
'Need one of {drivers} to manage {engine}',
drivers => 'DBD::ADO, DBD::ODBC, DBD::Sybase'
engine => $self->name,
);
}

Best,

David

[Dan Muey]

Very slick, thanks!

[Dan Muey]

Howdy Brian,

I wanted to touch base with you on this and see if I can help. Which branch of your fork would contain the mssql changes?

Thanks!

--

Dan Muey

[Dan Muey]

Hey David,

Quick question for you to make sure I am on the right track w/ this before I get too far down the rabbit hole:

In order to make the Engine’s dbh() do the choose-mssql-driver logic I'd need to:

1. patch URI::mssql by adding a dbi_driver() that does the logic (Still need to use base _odbc or just _db?)
   
2. patch the Engine module to have a driver() returns whatever the logic would

or 

1. patch URI::mssql by adding a dbi_driver() that does the logic (Still need to use base _odbc or just _db?)
   
2. patch the Engine module to have a use_driver() that does the logic
   
3. patch the Engine module to have a driver() returns whatever the logic would

Is that correct/complete? I prefer the first one since its less change but figured the second one might  be more appropriate.

Thanks!

On Friday, March 6, 2015 at 11:35:12 AM UTC-6, David Wheeler wrote:

[David E. Wheeler]

On Jan 8, 2016, at 10:12 AM, Dan Muey <drm...@gmail.com> wrote:

> Hey David,

Hey Dan.

> Quick question for you to make sure I am on the right track w/ this before I get too far down the rabbit hole:
>
> In order to make the Engine’s dbh() do the choose-mssql-driver logic I'd need to:
> • patch URI::mssql by adding a dbi_driver() that does the logic (Still need to use base _odbc or just _db?)

I don’t think URI::mssql needs to be patched, though I might be wrong. Rather, I think that Engine::mssql won’t use the dbi_dsn() method, but construct the DSN on its own.

> • patch the Engine module to have a driver() returns whatever the logic would

> or
> • patch URI::mssql by adding a dbi_driver() that does the logic (Still need to use base _odbc or just _db?)

No, I don’t think that URI::mssql should try to load drivers. No URI::db modules load the DBI at all.

> • patch the Engine module to have a use_driver() that does the logic

> • patch the Engine module to have a driver() returns whatever the logic would
> Is that correct/complete? I prefer the first one since its less change but figured the second one might be more appropriate.

I don’t think Engine needs patching either, though I might be wrong. I think what you want to do is have mssql figure everything out. So add an attribute for the driver, and have driver() figure out what it should be. Then have dbh() look at that attribute and construct the proper DSN.

Does that make sense?

Best,

David

[Dan Muey]

Does that make sense?

Absolutely, thank you! I reread my note and I think I was ambiguous on part of it: the Engine module I am referring to is not Engine.pm but rather Engine/mssql.pm 

So basically it sounds like I need to approach it as a patch to Engine/mssql.pm that may not use all the methods typically involved.

I'll work on it a bit and see what comes out :) thanks again for the quick feedback!

[Dan Muey]

Hey David, 

Quick question for you.

I have a branch of Brian's fork that is using the mssql engine/URI nicely (i.e. DBH is able to setup the sqitch tables).

The problem I have now is that the deploy uses sqlmcd.exe and I'd like to change the use of sqlcmd.exe to the DBH so that it can run on any computer and not just Windows.

I see that other engines also use a program. So the question is: would it cause any problems to use DBH entirely?

[David E. Wheeler]

On Mar 4, 2016, at 9:22 AM, Dan Muey <drm...@gmail.com> wrote:

> I have a branch of Brian's fork that is using the mssql engine/URI nicely (i.e. DBH is able to setup the sqitch tables).
>
> The problem I have now is that the deploy uses sqlmcd.exe and I'd like to change the use of sqlcmd.exe to the DBH so that it can run on any computer and not just Windows.
>
> I see that other engines also use a program. So the question is: would it cause any problems to use DBH entirely?

Yes. One of the key designs of Sqitch is the ability to use your preferred client to do deployments, with its syntax. That’s not possible using the DBH. What you could do, however, is create a Perl program that takes the same arguments as sqlcmd.exe but have it use the Perl DBI. The DBI::Shell module might be useful for that, though I’ve never used it.

http://search.cpan.org/dist/DBI-Shell/

The thing is, though, if you have an SQL script with multiple SQL commands, there has to be some way for the command-line tool to tell them apart. The command-line clients that come with most RDMBSes are great, since they already understand their own syntaxes and can do the right thing. You’d have to fake it with a DBH shell-style implementation.

That said, is there no `sqlcmd` on other systems? If you’re using UnixODBC, for example, it offers an isql client. We’d probably want to set up some way for the mssql engine to use appropriate command-line arguments for different clients.

HTH,

David

[Dan Muey]

Quick update on this:

I am ready to start writing tests once Brian reviews my pull request and merges it into his branch, per my last comment: https://github.com/brianmckeen/sqitch/pull/7#issuecomment-201394902

[Dan Muey]

Quick update, it appears Brian's fork is abandoned so I've done the work in my fork and will continue it there. 

So if you want to monitor progress you can check https://github.com/drmuey/sqitch/wiki/MSSQL-TODO

Brian, if you want to continue via your fork just let me know by merging those 2 pull requests and I'll revamp mine to match.

Thanks!

[David E. Wheeler]

On May 3, 2016, at 5:47 AM, Dan Muey <drm...@gmail.com> wrote:

> Quick update, it appears Brian's fork is abandoned so I've done the work in my fork and will continue it there.
>
> So if you want to monitor progress you can check https://github.com/drmuey/sqitch/wiki/MSSQL-TODO

Wow, so organized! +100.

Best,

David

[Miles McLenon]

How can I get started?

[David E. Wheeler]

On Jul 16, 2019, at 11:14, Miles McLenon <miles....@gmail.com> wrote:

> How can I get started?

Probably by reviewing this old fork:

https://github.com/brianmckeen/sqitch/commits/master

Based on discussion here:

https://github.com/sqitchers/sqitch/issues/149

The most recently-added engine is Snowflake; you might find the merge commit a useful reference for much of the stuff that goes into creating a new engine:

https://github.com/sqitchers/sqitch/commit/86cff56

Feel free to post any questions here. Good luck!

Best,

David