added preserve_case option

[dbsrgits/DBIx-Class-Schema-Loader.git] / lib / DBIx / Class / Schema / Loader / DBI / MSSQL.pm

diff --git a/lib/DBIx/Class/Schema/Loader/DBI/MSSQL.pm b/lib/DBIx/Class/Schema/Loader/DBI/MSSQL.pm
index 8606a2e..7d65c44 100644 (file)
--- a/lib/DBIx/Class/Schema/Loader/DBI/MSSQL.pm
+++ b/lib/DBIx/Class/Schema/Loader/DBI/MSSQL.pm
@@ -6,11 +6,7 @@ use base 'DBIx::Class::Schema::Loader::DBI::Sybase::Common';
 use Carp::Clan qw/^DBIx::Class/;
 use Class::C3;
 
-__PACKAGE__->mk_group_accessors('simple', qw/
-    case_sensitive_collation
-/);
-
-our $VERSION = '0.06000';
+our $VERSION = '0.07000';
 
 =head1 NAME
 
@@ -27,18 +23,35 @@ L<DBD::ODBC>.
 See L<DBIx::Class::Schema::Loader> and L<DBIx::Class::Schema::Loader::Base> for
 usage information.
 
-=cut
+=head1 CASE SENSITIVITY
 
-sub _is_case_sensitive {
-    my $self = shift;
+Most MSSQL databases use C<CI> (case-insensitive) collation, for this reason
+generated column names are lower-cased as this makes them easier to work with
+in L<DBIx::Class>.
 
-    return $self->case_sensitive_collation ? 1 : 0;
-}
+We attempt to detect the database collation at startup, and set the column
+lowercasing behavior accordingly, as lower-cased column names do not work on
+case-sensitive databases.
+
+To manually control case-sensitive mode, put:
+
+    preserve_case => 1|0
+
+in your Loader options.
+
+See L<preserve_case|DBIx::Class::Schema::Loader::Base/preserve_case>.
+
+B<NOTE:> this option used to be called C<case_sensitive_collation>, but has
+been renamed to a more generic option.
+
+=cut
 
 sub _setup {
     my $self = shift;
 
-    $self->next::method;
+    $self->next::method(@_);
+
+    return if defined $self->preserve_case;
 
     my $dbh = $self->schema->storage->dbh;
 
@@ -47,24 +60,28 @@ sub _setup {
     # which does not work over DBD::ODBC with unixODBC+FreeTDS.
     #
     # XXX why does databasepropertyex() not work over DBD::ODBC ?
+    #
+    # more on collations here: http://msdn.microsoft.com/en-us/library/ms143515.aspx
     my ($collation_name) =
            eval { $dbh->selectrow_array('SELECT collation_name FROM sys.databases WHERE name = DB_NAME()') }
-        || eval { $dbh->selectrow_array("SELECT databasepropertyex(DB_NAME(), 'Collation')") };
+        || eval { $dbh->selectrow_array("SELECT CAST(databasepropertyex(DB_NAME(), 'Collation') AS VARCHAR)") };
 
     if (not $collation_name) {
-        $self->case_sensitive_collation(0); # most likely not
+        warn <<'EOF';
+
+WARNING: MSSQL Collation detection failed. Defaulting to case-insensitive mode.
+Override the 'preserve_case' attribute in your Loader options if needed.
+
+See 'preserve_case' in
+perldoc DBIx::Class::Schema::Loader::Base
+EOF
+        $self->preserve_case(0);
         return;
     }
 
     my $case_sensitive = $collation_name =~ /_(?:CS|BIN2?)(?:_|\z)/;
 
-    $self->case_sensitive_collation($case_sensitive ? 1 : 0);
-}
-
-sub _lc {
-    my ($self, $name) = @_;
-
-    return $self->case_sensitive_collation ? $name : lc($name);
+    $self->preserve_case($case_sensitive ? 1 : 0);
 }
 
 sub _tables_list {
@@ -160,6 +177,7 @@ sub _columns_info_for {
     while (my ($col, $info) = each %$result) {
         my $dbh = $self->schema->storage->dbh;
 
+# find identities
         my $sth = $dbh->prepare(qq{
 SELECT column_name 
 FROM INFORMATION_SCHEMA.COLUMNS
@@ -172,6 +190,78 @@ AND lower(table_name) = @{[ $dbh->quote(lc $table) ]} AND lower(column_name) = @
             delete $info->{size};
         }
 
+# fix types
+        if ($info->{data_type} eq 'int') {
+            $info->{data_type} = 'integer';
+        }
+        elsif ($info->{data_type} eq 'timestamp') {
+            $info->{inflate_datetime} = 0;
+        }
+        elsif ($info->{data_type} =~ /^(?:numeric|decimal)\z/) {
+            if (ref($info->{size}) && $info->{size}[0] == 18 && $info->{size}[1] == 0) {
+                delete $info->{size};
+            }
+        }
+        elsif ($info->{data_type} eq 'real') {
+            $info->{data_type} = 'float';
+            $info->{size}      = 24;
+        }
+        elsif ($info->{data_type} eq 'float') {
+            $info->{data_type} = 'double precision';
+        }
+        elsif ($info->{data_type} =~ /^(?:small)?datetime\z/) {
+            # fixup for DBD::Sybase
+            if ($info->{default_value} && $info->{default_value} eq '3') {
+                delete $info->{default_value};
+            }
+        }
+        elsif ($info->{data_type} eq 'datetimeoffset') {
+            $info->{size} = {
+                26 => 0,
+                28 => 1,
+                29 => 2,
+                30 => 3,
+                31 => 4,
+                32 => 5,
+                33 => 6,
+                34 => 7,
+            }->{$info->{size}};
+
+            delete $info->{size} if $info->{size} == 7;
+        }
+        elsif ($info->{data_type} eq 'datetime2') {
+            $info->{size} = {
+                19 => 0,
+                21 => 1,
+                22 => 2,
+                23 => 3,
+                24 => 4,
+                25 => 5,
+                26 => 6,
+                27 => 7,
+            }->{$info->{size}};
+
+            delete $info->{size} if $info->{size} == 7;
+        }
+        elsif ($info->{data_type} eq 'time') {
+            $info->{size} = {
+                 8 => 0,
+                10 => 1,
+                11 => 2,
+                12 => 3,
+                13 => 4,
+                14 => 5,
+                15 => 6,
+                16 => 7,
+            }->{$info->{size}};
+
+            delete $info->{size} if $info->{size} == 7;
+        }
+
+        if ($info->{data_type} !~ /^(?:n?char|n?varchar|binary|varbinary|numeric|decimal|float|datetime(?:2|offset)|time)\z/) {
+            delete $info->{size};
+        }
+
 # get default
         $sth = $dbh->prepare(qq{
 SELECT column_default
@@ -189,6 +279,10 @@ wHERE lower(table_name) = @{[ $dbh->quote(lc $table) ]} AND lower(column_name) =
             $info->{default_value} =
                 $default =~ /^['(] (.*) [)']\z/x ? $1 :
                     $default =~ /^\d/ ? $default : \$default;
+
+            if (eval { lc ${ $info->{default_value} } }||'' eq 'getdate()') {
+                ${ $info->{default_value} } = 'CURRENT_TIMESTAMP';
+            }
         }
     }