X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FSchema%2FLoader%2FDBI%2FSQLAnywhere.pm;h=a53ca1ffbf8b9a41e28eee95e90af409cb35ffee;hb=46564a42215c5309753f3e0609ae1adddf68d083;hp=d5105bd31138ce930f9c5e0af58ed0dd6195fb34;hpb=8e6c80c9ded48d2f9450de4200c4490b13d0c942;p=dbsrgits%2FDBIx-Class-Schema-Loader.git diff --git a/lib/DBIx/Class/Schema/Loader/DBI/SQLAnywhere.pm b/lib/DBIx/Class/Schema/Loader/DBI/SQLAnywhere.pm index d5105bd..a53ca1f 100644 --- a/lib/DBIx/Class/Schema/Loader/DBI/SQLAnywhere.pm +++ b/lib/DBIx/Class/Schema/Loader/DBI/SQLAnywhere.pm @@ -2,16 +2,13 @@ package DBIx::Class::Schema::Loader::DBI::SQLAnywhere; use strict; use warnings; -use base qw/ - DBIx::Class::Schema::Loader::DBI::Component::QuotedDefault - DBIx::Class::Schema::Loader::DBI -/; +use base 'DBIx::Class::Schema::Loader::DBI::Component::QuotedDefault'; use mro 'c3'; -use List::MoreUtils 'any'; +use List::Util 'any'; use namespace::clean; use DBIx::Class::Schema::Loader::Table (); -our $VERSION = '0.07011'; +our $VERSION = '0.07048_01'; =head1 NAME @@ -51,7 +48,7 @@ sub _setup { } sub _tables_list { - my ($self, $opts) = @_; + my ($self) = @_; my @tables; @@ -76,7 +73,7 @@ EOF } } - return $self->_filter_tables(\@tables, $opts); + return $self->_filter_tables(\@tables); } sub _columns_info_for { @@ -94,7 +91,7 @@ sub _columns_info_for { $info->{is_auto_increment} = 1; } - my ($user_type) = $dbh->selectrow_array(<<'EOF', {}, $table->schema, $table->name, $col); + my ($user_type) = $dbh->selectrow_array(<<'EOF', {}, $table->schema, $table->name, lc($col)); SELECT ut.type_name FROM systabcol tc JOIN systab t @@ -103,7 +100,7 @@ JOIN sysuser u ON t.creator = u.user_id JOIN sysusertype ut ON tc.user_type = ut.type_id -WHERE u.user_name = ? AND t.table_name = ? AND tc.column_name = ? +WHERE u.user_name = ? AND t.table_name = ? AND lower(tc.column_name) = ? EOF $info->{data_type} = $user_type if defined $user_type; @@ -125,9 +122,9 @@ JOIN systab t ON t.table_id = tc.table_id JOIN sysuser u ON t.creator = u.user_id -WHERE u.user_name = ? AND t.table_name = ? AND tc.column_name = ? +WHERE u.user_name = ? AND t.table_name = ? AND lower(tc.column_name) = ? EOF - $sth->execute($table->schema, $table->name, $col); + $sth->execute($table->schema, $table->name, lc($col)); my ($width, $scale) = $sth->fetchrow_array; $sth->finish; @@ -168,13 +165,26 @@ sub _table_pk_info { return \@keydata; } +my %sqlany_rules = ( + C => 'CASCADE', + D => 'SET DEFAULT', + N => 'SET NULL', + R => 'RESTRICT', +); + sub _table_fk_info { my ($self, $table) = @_; - my ($local_cols, $remote_cols, $remote_table, @rels); + my ($local_cols, $remote_cols, $remote_table, $attrs, @rels); my $sth = $self->dbh->prepare(<<'EOF'); -SELECT fki.index_name fk_name, fktc.column_name local_column, pku.user_name remote_schema, pkt.table_name remote_table, pktc.column_name remote_column +SELECT fki.index_name fk_name, fktc.column_name local_column, pku.user_name remote_schema, pkt.table_name remote_table, pktc.column_name remote_column, on_delete.referential_action, on_update.referential_action FROM sysfkey fk +JOIN ( + select foreign_table_id, foreign_index_id, + row_number() over (partition by foreign_table_id order by foreign_index_id) foreign_key_num + from sysfkey +) fkid + ON fkid.foreign_table_id = fk.foreign_table_id and fkid.foreign_index_id = fk.foreign_index_id JOIN systab pkt ON fk.primary_table_id = pkt.table_id JOIN sysuser pku @@ -183,9 +193,9 @@ JOIN systab fkt ON fk.foreign_table_id = fkt.table_id JOIN sysuser fku ON fkt.creator = fku.user_id -JOIN sysidx pki +JOIN sysidx pki ON fk.primary_table_id = pki.table_id AND fk.primary_index_id = pki.index_id -JOIN sysidx fki +JOIN sysidx fki ON fk.foreign_table_id = fki.table_id AND fk.foreign_index_id = fki.index_id JOIN sysidxcol fkic ON fkt.table_id = fkic.table_id AND fki.index_id = fkic.index_id @@ -193,18 +203,39 @@ JOIN systabcol pktc ON pkt.table_id = pktc.table_id AND fkic.primary_column_id = pktc.column_id JOIN systabcol fktc ON fkt.table_id = fktc.table_id AND fkic.column_id = fktc.column_id +LEFT JOIN systrigger on_delete + ON on_delete.foreign_table_id = fkt.table_id AND on_delete.foreign_key_id = fkid.foreign_key_num + AND on_delete.event = 'D' +LEFT JOIN systrigger on_update + ON on_update.foreign_table_id = fkt.table_id AND on_update.foreign_key_id = fkid.foreign_key_num + AND on_update.event = 'C' WHERE fku.user_name = ? AND fkt.table_name = ? +ORDER BY fk.primary_table_id, pktc.column_id EOF $sth->execute($table->schema, $table->name); - while (my ($fk, $local_col, $remote_schema, $remote_tab, $remote_col) = $sth->fetchrow_array) { + while (my ($fk, $local_col, $remote_schema, $remote_tab, $remote_col, $on_delete, $on_update) + = $sth->fetchrow_array) { + push @{$local_cols->{$fk}}, $self->_lc($local_col); + push @{$remote_cols->{$fk}}, $self->_lc($remote_col); + $remote_table->{$fk} = DBIx::Class::Schema::Loader::Table->new( loader => $self, name => $remote_tab, schema => $remote_schema, ); + + $attrs->{$fk} ||= { + on_delete => $sqlany_rules{$on_delete||''} || 'RESTRICT', + on_update => $sqlany_rules{$on_update||''} || 'RESTRICT', +# We may be able to use the value of the 'CHECK ON COMMIT' option, as it seems +# to be some sort of workaround for lack of deferred constraints. Unclear on +# how good of a substitute it is, and it requires the 'RESTRICT' rule. Also it +# only works for INSERT and UPDATE, not DELETE. Will get back to this. + is_deferrable => 1, + }; } foreach my $fk (keys %$remote_table) { @@ -212,6 +243,7 @@ EOF local_columns => $local_cols->{$fk}, remote_columns => $remote_cols->{$fk}, remote_table => $remote_table->{$fk}, + attrs => $attrs->{$fk}, }; } return \@rels; @@ -242,8 +274,7 @@ EOF push @{$constraints->{$constraint_name}}, $self->_lc($column); } - my @uniqs = map { [ $_ => $constraints->{$_} ] } keys %$constraints; - return \@uniqs; + return [ map { [ $_ => $constraints->{$_} ] } sort keys %$constraints ]; } =head1 SEE ALSO @@ -251,9 +282,9 @@ EOF L, L, L -=head1 AUTHOR +=head1 AUTHORS -See L and L. +See L. =head1 LICENSE