X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FSchema%2FLoader%2FDBI%2FMSSQL.pm;h=7d65c44e87e383e8af6275f7373a7bf6409a6b35;hb=bc1cb85e84e6a30c75763edd478378a68009c722;hp=1023ebdbce950fdcb3c779bb8e93ac98c71b5a8a;hpb=43170884f059b9d068b133bcfdebf511c30420ba;p=dbsrgits%2FDBIx-Class-Schema-Loader.git

diff --git a/lib/DBIx/Class/Schema/Loader/DBI/MSSQL.pm b/lib/DBIx/Class/Schema/Loader/DBI/MSSQL.pm
index 1023ebd..7d65c44 100644
--- a/lib/DBIx/Class/Schema/Loader/DBI/MSSQL.pm
+++ b/lib/DBIx/Class/Schema/Loader/DBI/MSSQL.pm
@@ -6,7 +6,7 @@ use base 'DBIx::Class::Schema::Loader::DBI::Sybase::Common';
 use Carp::Clan qw/^DBIx::Class/;
 use Class::C3;
 
-our $VERSION = '0.05003';
+our $VERSION = '0.07000';
 
 =head1 NAME
 
@@ -23,15 +23,74 @@ L<DBD::ODBC>.
 See L<DBIx::Class::Schema::Loader> and L<DBIx::Class::Schema::Loader::Base> for
 usage information.
 
+=head1 CASE SENSITIVITY
+
+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>.
+
+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(@_);
+
+    return if defined $self->preserve_case;
+
+    my $dbh = $self->schema->storage->dbh;
+
+    # We use the sys.databases query for the general case, and fallback to
+    # databasepropertyex() if for some reason sys.databases is not available,
+    # 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 CAST(databasepropertyex(DB_NAME(), 'Collation') AS VARCHAR)") };
+
+    if (not $collation_name) {
+        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->preserve_case($case_sensitive ? 1 : 0);
+}
+
 sub _tables_list {
     my ($self, $opts) = @_;
 
     my $dbh = $self->schema->storage->dbh;
     my $sth = $dbh->prepare(<<'EOF');
 SELECT t.table_name
-FROM information_schema.tables t
+FROM INFORMATION_SCHEMA.TABLES t
 WHERE lower(t.table_schema) = ?
 EOF
     $sth->execute(lc $self->db_schema);
@@ -50,7 +109,7 @@ sub _table_pk_info {
     my @keydata;
 
     while (my $row = $sth->fetchrow_hashref) {
-        push @keydata, lc $row->{COLUMN_NAME};
+        push @keydata, $self->_lc($row->{COLUMN_NAME});
     }
 
     return \@keydata;
@@ -59,15 +118,17 @@ sub _table_pk_info {
 sub _table_fk_info {
     my ($self, $table) = @_;
 
-    my ($local_cols, $remote_cols, $remote_table, @rels);
+    my ($local_cols, $remote_cols, $remote_table, @rels, $sth);
     my $dbh = $self->schema->storage->dbh;
-    my $sth = $dbh->prepare(qq{sp_fkeys \@FKTABLE_NAME = '$table'});
-    $sth->execute;
+    eval {
+        $sth = $dbh->prepare(qq{sp_fkeys \@fktable_name = '$table'});
+        $sth->execute;
+    };
 
-    while (my $row = $sth->fetchrow_hashref) {
+    while (my $row = eval { $sth->fetchrow_hashref }) {
         my $fk = $row->{FK_NAME};
-        push @{$local_cols->{$fk}}, lc $row->{FKCOLUMN_NAME};
-        push @{$remote_cols->{$fk}}, lc $row->{PKCOLUMN_NAME};
+        push @{$local_cols->{$fk}}, $self->_lc($row->{FKCOLUMN_NAME});
+        push @{$remote_cols->{$fk}}, $self->_lc($row->{PKCOLUMN_NAME});
         $remote_table->{$fk} = $row->{PKTABLE_NAME};
     }
 
@@ -90,16 +151,16 @@ sub _table_uniq_info {
 
     my $sth = $dbh->prepare(qq{
 SELECT ccu.constraint_name, ccu.column_name
-FROM information_schema.constraint_column_usage ccu
-JOIN information_schema.table_constraints tc on (ccu.constraint_name = tc.constraint_name)
-JOIN information_schema.key_column_usage kcu on (ccu.constraint_name = kcu.constraint_name and ccu.column_name = kcu.column_name)
+FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
+JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc on (ccu.constraint_name = tc.constraint_name)
+JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on (ccu.constraint_name = kcu.constraint_name and ccu.column_name = kcu.column_name)
 wHERE lower(ccu.table_name) = @{[ $dbh->quote(lc $table) ]} AND constraint_type = 'UNIQUE' ORDER BY kcu.ordinal_position
     });
     $sth->execute;
     my $constraints;
     while (my $row = $sth->fetchrow_hashref) {
         my $name = $row->{constraint_name};
-        my $col  = lc $row->{column_name};
+        my $col  = $self->_lc($row->{column_name});
         push @{$constraints->{$name}}, $col;
     }
 
@@ -116,9 +177,10 @@ 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
+FROM INFORMATION_SCHEMA.COLUMNS
 WHERE columnproperty(object_id(@{[ $dbh->quote(lc $table) ]}, 'U'), @{[ $dbh->quote(lc $col) ]}, 'IsIdentity') = 1
 AND lower(table_name) = @{[ $dbh->quote(lc $table) ]} AND lower(column_name) = @{[ $dbh->quote(lc $col) ]}
         });
@@ -128,10 +190,82 @@ 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
-FROM information_schema.columns
+FROM INFORMATION_SCHEMA.COLUMNS
 wHERE lower(table_name) = @{[ $dbh->quote(lc $table) ]} AND lower(column_name) = @{[ $dbh->quote(lc $col) ]}
         });
         my ($default) = eval { $sth->execute; $sth->fetchrow_array };
@@ -145,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';
+            }
         }
     }