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