X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FSchema%2FLoader%2FDBI%2FMSSQL.pm;h=1b9ce7d31a21e04fde5d490fe492a97c074345c8;hb=8c41c3ce6f1db7b202d3465f84e0ba3e4d55e820;hp=0fe0dad3ddb620ce35d228ea42094c5c67d8dff8;hpb=9c9197d6bacd063465ef4a3a5194ff2e292ae22f;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 0fe0dad..1b9ce7d 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,22 +23,81 @@ 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 _tables_list {
+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
-where t.table_schema = ?
+SELECT t.table_name
+FROM INFORMATION_SCHEMA.TABLES t
+WHERE t.table_schema = ?
 EOF
     $sth->execute($self->db_schema);
 
-    my @tables = map lc $_, map @$_, @{ $sth->fetchall_arrayref };
+    my @tables = map @$_, @{ $sth->fetchall_arrayref };
 
-    return $self->_filter_tables(@tables);
+    return $self->_filter_tables(\@tables, $opts);
 }
 
 sub _table_pk_info {
@@ -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};
     }
 
@@ -86,15 +147,20 @@ sub _table_uniq_info {
     my ($self, $table) = @_;
 
     my $dbh = $self->schema->storage->dbh;
-    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)
-                               WHERE CCU.TABLE_NAME = @{[ $dbh->quote($table) ]} AND CONSTRAINT_TYPE = 'UNIQUE' ORDER BY KCU.ORDINAL_POSITION});
+    local $dbh->{FetchHashKeyName} = 'NAME_lc';
+
+    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)
+wHERE ccu.table_name = @{[ $dbh->quote($table) ]} AND constraint_type = 'UNIQUE' ORDER BY kcu.ordinal_position
+    });
     $sth->execute;
     my $constraints;
     while (my $row = $sth->fetchrow_hashref) {
-        my $name = lc $row->{CONSTRAINT_NAME};
-        my $col  = lc $row->{COLUMN_NAME};
+        my $name = $row->{constraint_name};
+        my $col  = $self->_lc($row->{column_name});
         push @{$constraints->{$name}}, $col;
     }
 
@@ -110,28 +176,95 @@ 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
-            WHERE COLUMNPROPERTY(object_id(@{[ $dbh->quote($table) ]}, 'U'), @{[ $dbh->quote($col) ]}, 'IsIdentity') = 1
-              AND TABLE_NAME = @{[ $dbh->quote($table) ]} AND COLUMN_NAME = @{[ $dbh->quote($col) ]}
+SELECT column_name 
+FROM INFORMATION_SCHEMA.COLUMNS
+WHERE columnproperty(object_id(@{[ $dbh->quote($table) ]}, 'U'), @{[ $dbh->quote($col) ]}, 'IsIdentity') = 1
+AND table_name = @{[ $dbh->quote($table) ]} AND column_name = @{[ $dbh->quote($col) ]}
         });
-        $sth->execute();
-
-        if ($sth->fetchrow_array) {
+        if (eval { $sth->execute; $sth->fetchrow_array }) {
             $info->{is_auto_increment} = 1;
             $info->{data_type} =~ s/\s*identity//i;
             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 '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
-            WHERE TABLE_NAME = @{[ $dbh->quote($table) ]} AND COLUMN_NAME = @{[ $dbh->quote($col) ]}
+SELECT column_default
+FROM INFORMATION_SCHEMA.COLUMNS
+wHERE table_name = @{[ $dbh->quote($table) ]} AND column_name = @{[ $dbh->quote($col) ]}
         });
-        $sth->execute;
-        my ($default) = $sth->fetchrow_array;
+        my ($default) = eval { $sth->execute; $sth->fetchrow_array };
 
         if (defined $default) {
             # strip parens
@@ -142,6 +275,10 @@ sub _columns_info_for {
             $info->{default_value} =
                 $default =~ /^['(] (.*) [)']\z/x ? $1 :
                     $default =~ /^\d/ ? $default : \$default;
+
+            if (eval { lc ${ $info->{default_value} } }||'' eq 'getdate()') {
+                ${ $info->{default_value} } = 'current_timestamp';
+            }
         }
     }
 
@@ -167,3 +304,4 @@ the same terms as Perl itself.
 =cut
 
 1;
+# vim:et sts=4 sw=4 tw=0: