SqlServerDialect.Default does not allow for non-quoted default functions

[GoogleCodeExporter]

(in refactorings-unstable branch)

SqlServerDialect.Default overrides the base method and adds quotes to the
default value. This prevents tables from being created like the following:

CREATE TABLE Test (
  [ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  [Name] NVARCHAR NOT NULL,
  [CreatedAt] DATETIME NOT NULL DEFAULT GETDATE(),
)

Instead, the "GETDATE()" has quotes around it and unfortunately SQL Server
will not raise an error on table creation, but rather the error will pop up
upon inserting or modifying the table later.

The MySQL dialect does the same - not sure if this is a problem for MySQL
or not.

Personally I'd prefer either using the base (old) way of doing things - or
adding another "unquoted" method to specify a default value that is a
function or similar.

Original issue reported on code.google.com by michael....@gmail.com on 2 Jun 2008 at 6:27

[GoogleCodeExporter]

Original comment by geoffl...@gmail.com on 2 Jun 2008 at 2:59

• Added labels: refacorings-unstable

[GoogleCodeExporter]

SVN-59

It needs to be overridden because SQL Server doesn't have a boolean type. So we 
use a
bit field and convert true/false to a 1/0. The client migration needs to make 
the
decision on whether it is a value that needs to be quoted or not now. "NULL" and
"GETDATE()" are passed through as is, so you need to do "'SOME DEFAULT'" for a
string. Int values and true/false are handled without quoting.

This could probably get changed with a series of value type objects to 
encapsulate
different kinds of defaults (Function, Null, Literal defaults).

e.g.
mapper.MapColumnProperties(new Column("foo", DbType.Boolean, 0, 
Default.Literal(false)));
mapper.MapColumnProperties(new Column("foo", DbType.Boolean, 0, 
Default.NullValue));
mapper.MapColumnProperties(new Column("foo", DbType.Boolean, 0,
Default.Function("GETDATE()")));

These could make determinations about quoting for example. Maybe more trouble 
than
it's worth?

Original comment by geoffl...@gmail.com on 2 Jun 2008 at 3:46

• Changed state: Fixed

[GoogleCodeExporter]

I agree it could get messy...

Considering that most cases you will want quoting, you could append a "bool
quoteDefault" to the Column constructor - that defaults to true:

new Column("foo", DbType.DateTime, ColumnProperty.NotNull, "GETDATE()", false)
new Column("foobar", DbType.String, ColumnProperty.NotNull, "i-will-be-quoted")

SQL Server seems to be fine with quoted literal ints/bools as defaults. If this 
is
not the case with other DBs, some sort of check would need to be done on the 
type.
Numbers/bools passed through, everything else quoted... Not sure.

I've attached a patch that achieves this - still can't decide if it's messy or 
not,
but works for me. (NB: Although I added a property to Column, I _didn't_ add 
one to
ColumnPropertiesMapper - I can't tell how the ColumnPropertiesMapper.Default 
property
is/would be used, so I wasn't sure what to do there).

Original comment by michael....@gmail.com on 3 Jun 2008 at 12:37

Attachments:

• add-quote-default-option.patch