1 package DBIx::Class::Schema::Loader::DBI;
5 use base qw/DBIx::Class::Schema::Loader::Base/;
8 use Scalar::Util 'blessed';
10 use Carp::Clan qw/^DBIx::Class/;
11 use Class::Method::Modifiers 'install_modifier';
14 use DBIx::Class::Schema::Loader::Table ();
16 our $VERSION = '0.07043';
18 __PACKAGE__->mk_group_accessors('simple', qw/
20 _disable_uniq_detection
29 DBIx::Class::Schema::Loader::DBI - DBIx::Class::Schema::Loader DBI Implementation.
33 See L<DBIx::Class::Schema::Loader::Base>
37 This is the base class for L<DBIx::Class::Schema::Loader::Base> classes for
38 DBI-based storage backends, and implements the common functionality between them.
40 See L<DBIx::Class::Schema::Loader::Base> for the available options.
46 Overlays L<DBIx::Class::Schema::Loader::Base/new> to do some DBI-specific
52 my $self = shift->next::method(@_);
54 # rebless to vendor-specific class if it exists and loads and we're not in a
56 if (not $self->loader_class) {
57 my $driver = $self->dbh->{Driver}->{Name};
59 my $subclass = 'DBIx::Class::Schema::Loader::DBI::' . $driver;
60 if ((not $self->isa($subclass)) && $self->load_optional_class($subclass)) {
61 bless $self, $subclass;
63 Class::C3::reinitialize() if $] < 5.009005;
67 # Set up the default quoting character and name separators
68 $self->quote_char($self->_build_quote_char);
69 $self->name_sep($self->_build_name_sep);
76 sub _build_quote_char {
79 my $quote_char = $self->dbh->get_info(29)
80 || $self->schema->storage->sql_maker->quote_char
83 # For our usage as regex matches, concatenating multiple quote_char
84 # values works fine (e.g. s/[\Q<>\E]// if quote_char was [ '<', '>' ])
85 if (ref $quote_char eq 'ARRAY') {
86 $quote_char = join '', @$quote_char;
94 return $self->dbh->get_info(41)
95 || $self->schema->storage->sql_maker->name_sep
99 # Override this in vendor modules to do things at the end of ->new()
104 _columns_info_for _table_columns
105 _table_pk_info _table_fk_info _table_uniq_info
107 $self->_setup_per_table_cache($method);
113 sub _setup_per_table_cache {
114 my ($self, $method) = @_;
115 my $class = blessed($self);
117 return if $has_cache_setup{$class}{$method}++;
119 install_modifier($class, around => $method => sub {
120 my ($orig, $self, $table) = @_;
121 $self->{_cache}{$method}{$table->sql_name} ||= $self->$orig($table);
126 # Override this in vendor module to load a subclass if necessary
129 sub _system_schemas {
130 return ('information_schema');
140 return $self->dbh->tables(undef, @_);
143 # default to be overridden in subclasses if necessary
144 sub _supports_db_schema { 1 }
146 # Returns an array of table objects
152 my $qt = qr/[\Q$self->{quote_char}\E"'`\[\]]/;
153 my $nqt = qr/[^\Q$self->{quote_char}\E"'`\[\]]/;
154 my $ns = qr/[\Q$self->{name_sep}\E]/;
155 my $nns = qr/[^\Q$self->{name_sep}\E]/;
157 foreach my $schema (@{ $self->db_schema || [undef] }) {
158 my @raw_table_names = $self->_dbh_tables($schema);
160 TABLE: foreach my $raw_table_name (@raw_table_names) {
161 my $quoted = $raw_table_name =~ /^$qt/;
163 # These regexes are not entirely correct, but hopefully they will work
164 # in most cases. RT reports welcome.
165 my ($schema_name, $table_name1, $table_name2) = $quoted ?
166 $raw_table_name =~ /^(?:${qt}(${nqt}+?)${qt}${ns})?(?:${qt}(.+?)${qt}|(${nns}+))\z/
168 $raw_table_name =~ /^(?:(${nns}+?)${ns})?(?:${qt}(.+?)${qt}|(${nns}+))\z/;
170 my $table_name = $table_name1 || $table_name2;
172 foreach my $system_schema ($self->_system_schemas) {
176 if (ref $system_schema) {
178 if $schema_name =~ $system_schema
179 && $schema !~ $system_schema;
183 if $schema_name eq $system_schema
184 && $schema ne $system_schema;
187 next TABLE if $matches;
191 foreach my $system_table ($self->_system_tables) {
194 if (ref $system_table) {
195 $matches = 1 if $table_name =~ $system_table;
198 $matches = 1 if $table_name eq $system_table
201 next TABLE if $matches;
204 $schema_name ||= $schema;
206 my $table = DBIx::Class::Schema::Loader::Table->new(
209 schema => $schema_name,
210 ($self->_supports_db_schema ? () : (
215 push @tables, $table;
219 return $self->_filter_tables(\@tables);
222 sub _recurse_constraint {
223 my ($constraint, @parts) = @_;
225 my $name = shift @parts;
227 # If there are any parts left, the constraint must be an arrayref
228 croak "depth of constraint/exclude array does not match length of moniker_parts"
229 unless !!@parts == !!(ref $constraint eq 'ARRAY');
231 # if ths is the last part, use the constraint directly
232 return $name =~ $constraint unless @parts;
234 # recurse into the first matching subconstraint
235 foreach (@{$constraint}) {
236 my ($re, $sub) = @{$_};
237 return _recurse_constraint($sub, @parts)
243 sub _check_constraint {
244 my ($include, $constraint, @tables) = @_;
246 return @tables unless defined $constraint;
248 return grep { !$include xor _recurse_constraint($constraint, @{$_}) } @tables
249 if ref $constraint eq 'ARRAY';
251 return grep { !$include xor /$constraint/ } @tables;
256 # apply constraint/exclude and ignore bad tables and views
258 my ($self, $tables) = @_;
260 my @tables = @$tables;
263 @tables = _check_constraint(1, $self->constraint, @tables);
264 @tables = _check_constraint(0, $self->exclude, @tables);
266 TABLE: for my $table (@tables) {
268 local $^W = 0; # for ADO
269 my $sth = $self->_sth_for($table, undef, \'1 = 0');
274 warn "Bad table or view '$table', ignoring: $_\n";
278 push @filtered_tables, $table;
281 return @filtered_tables;
286 We override L<DBIx::Class::Schema::Loader::Base/load> here to hook in our localized settings for C<$dbh> error handling.
293 local $self->dbh->{RaiseError} = 1;
294 local $self->dbh->{PrintError} = 0;
296 $self->next::method(@_);
300 my ($self, $table, $fields, $where) = @_;
302 my $sth = $self->dbh->prepare($self->schema->storage->sql_maker
303 ->select(\$table->sql_name, $fields, $where));
308 # Returns an arrayref of column names
310 my ($self, $table) = @_;
312 my $sth = $self->_sth_for($table, undef, \'1 = 0');
315 my $retval = [ map $self->_lc($_), @{$sth->{NAME}} ];
322 # Returns arrayref of pk col names
324 my ($self, $table) = @_;
326 return [] if $self->_disable_pk_detection;
329 $self->dbh->primary_key('', $table->schema, $table->name);
332 warn "Cannot find primary keys for this driver: $_";
333 $self->_disable_pk_detection(1);
337 return [] if not @primary;
339 @primary = map { $self->_lc($_) } @primary;
340 s/[\Q$self->{quote_char}\E]//g for @primary;
345 # Override this for vendor-specific uniq info
346 sub _table_uniq_info {
347 my ($self, $table) = @_;
349 return [] if $self->_disable_uniq_detection;
351 if (not $self->dbh->can('statistics_info')) {
352 warn "No UNIQUE constraint information can be gathered for this driver";
353 $self->_disable_uniq_detection(1);
358 my $sth = $self->dbh->statistics_info(undef, $table->schema, $table->name, 1, 1);
359 while(my $row = $sth->fetchrow_hashref) {
360 # skip table-level stats, conditional indexes, and any index missing
362 next if $row->{TYPE} eq 'table'
363 || defined $row->{FILTER_CONDITION}
364 || !$row->{INDEX_NAME}
365 || !defined $row->{ORDINAL_POSITION};
367 $indices{$row->{INDEX_NAME}}[$row->{ORDINAL_POSITION}] = $self->_lc($row->{COLUMN_NAME} || '');
372 foreach my $index_name (sort keys %indices) {
373 my (undef, @cols) = @{$indices{$index_name}};
374 # skip indexes with missing column names (e.g. expression indexes)
375 next unless @cols == grep $_, @cols;
376 push(@retval, [ $index_name => \@cols ]);
383 my ($self, $table) = @_;
384 my $dbh = $self->dbh;
386 my $comments_table = $table->clone;
387 $comments_table->name($self->table_comments_table);
390 (exists $self->_tables->{$comments_table->sql_name} || undef)
391 && try { $dbh->selectrow_array(<<"EOF") };
393 FROM @{[ $comments_table->sql_name ]}
394 WHERE table_name = @{[ $dbh->quote($table->name) ]}
397 # Failback: try the REMARKS column on table_info
399 my $info = $self->_dbh_table_info( $dbh, $table );
400 $comment = $info->{REMARKS} if $info;
406 sub _column_comment {
407 my ($self, $table, $column_number, $column_name) = @_;
408 my $dbh = $self->dbh;
410 my $comments_table = $table->clone;
411 $comments_table->name($self->column_comments_table);
414 (exists $self->_tables->{$comments_table->sql_name} || undef)
415 && try { $dbh->selectrow_array(<<"EOF") };
417 FROM @{[ $comments_table->sql_name ]}
418 WHERE table_name = @{[ $dbh->quote($table->name) ]}
419 AND column_name = @{[ $dbh->quote($column_name) ]}
422 # Failback: try the REMARKS column on column_info
423 if (!$comment && $dbh->can('column_info')) {
424 if (my $sth = try { $self->_dbh_column_info( $dbh, undef, $table->schema, $table->name, $column_name ) }) {
425 my $info = $sth->fetchrow_hashref();
426 $comment = $info->{REMARKS};
435 my ($self, $table) = @_;
437 return [] if $self->_disable_fk_detection;
440 $self->dbh->foreign_key_info( '', '', '',
441 '', ($table->schema || ''), $table->name );
444 warn "Cannot introspect relationships for this driver: $_";
445 $self->_disable_fk_detection(1);
461 my $i = 1; # for unnamed rels, which hopefully have only 1 column ...
462 REL: while(my $raw_rel = $sth->fetchrow_arrayref) {
463 my $uk_scm = $raw_rel->[1];
464 my $uk_tbl = $raw_rel->[2];
465 my $uk_col = $self->_lc($raw_rel->[3]);
466 my $fk_scm = $raw_rel->[5];
467 my $fk_col = $self->_lc($raw_rel->[7]);
468 my $key_seq = $raw_rel->[8] - 1;
469 my $relid = ($raw_rel->[11] || ( "__dcsld__" . $i++ ));
471 my $update_rule = $raw_rel->[9];
472 my $delete_rule = $raw_rel->[10];
474 $update_rule = $rules[$update_rule] if defined $update_rule;
475 $delete_rule = $rules[$delete_rule] if defined $delete_rule;
477 my $is_deferrable = $raw_rel->[13];
479 ($is_deferrable = $is_deferrable == 7 ? 0 : 1)
480 if defined $is_deferrable;
482 foreach my $var ($uk_scm, $uk_tbl, $uk_col, $fk_scm, $fk_col, $relid) {
483 $var =~ s/[\Q$self->{quote_char}\E]//g if defined $var;
486 if ($self->db_schema && $self->db_schema->[0] ne '%'
487 && (not any { $_ eq $uk_scm } @{ $self->db_schema })) {
492 $rels{$relid}{tbl} ||= DBIx::Class::Schema::Loader::Table->new(
496 ($self->_supports_db_schema ? () : (
501 $rels{$relid}{attrs}{on_delete} = $delete_rule if $delete_rule;
502 $rels{$relid}{attrs}{on_update} = $update_rule if $update_rule;
503 $rels{$relid}{attrs}{is_deferrable} = $is_deferrable if defined $is_deferrable;
505 # Add this data IN ORDER
506 $rels{$relid}{rcols}[$key_seq] = $uk_col;
507 $rels{$relid}{lcols}[$key_seq] = $fk_col;
512 foreach my $relid (keys %rels) {
514 remote_columns => [ grep defined, @{ $rels{$relid}{rcols} } ],
515 local_columns => [ grep defined, @{ $rels{$relid}{lcols} } ],
516 remote_table => $rels{$relid}->{tbl},
517 (exists $rels{$relid}{attrs} ?
518 (attrs => $rels{$relid}{attrs})
522 _constraint_name => $relid,
529 # ported in from DBIx::Class::Storage::DBI:
530 sub _columns_info_for {
531 my ($self, $table) = @_;
533 my $dbh = $self->schema->storage->dbh;
537 if (my $sth = try { $self->_dbh_column_info($dbh, undef, $table->schema, $table->name, '%' ) }) {
538 COL_INFO: while (my $info = try { $sth->fetchrow_hashref } catch { +{} }) {
539 next COL_INFO unless %$info;
541 my $column_info = {};
542 $column_info->{data_type} = lc $info->{TYPE_NAME};
544 my $size = $info->{COLUMN_SIZE};
546 if (defined $size && defined $info->{DECIMAL_DIGITS}) {
547 $column_info->{size} = [$size, $info->{DECIMAL_DIGITS}];
549 elsif (defined $size) {
550 $column_info->{size} = $size;
553 $column_info->{is_nullable} = $info->{NULLABLE} ? 1 : 0;
554 $column_info->{default_value} = $info->{COLUMN_DEF} if defined $info->{COLUMN_DEF};
555 my $col_name = $info->{COLUMN_NAME};
556 $col_name =~ s/^\"(.*)\"$/$1/;
558 my $extra_info = $self->_extra_column_info(
559 $table, $col_name, $column_info, $info
561 $column_info = { %$column_info, %$extra_info };
563 $result{$col_name} = $column_info;
568 my $sth = $self->_sth_for($table, undef, \'1 = 0');
571 my @columns = @{ $sth->{NAME} };
573 COL: for my $i (0 .. $#columns) {
574 next COL if %{ $result{ $columns[$i] }||{} };
576 my $column_info = {};
577 $column_info->{data_type} = lc $sth->{TYPE}[$i];
579 my $size = $sth->{PRECISION}[$i];
581 if (defined $size && defined $sth->{SCALE}[$i]) {
582 $column_info->{size} = [$size, $sth->{SCALE}[$i]];
584 elsif (defined $size) {
585 $column_info->{size} = $size;
588 $column_info->{is_nullable} = $sth->{NULLABLE}[$i] ? 1 : 0;
590 if ($column_info->{data_type} =~ m/^(.*?)\((.*?)\)$/) {
591 $column_info->{data_type} = $1;
592 $column_info->{size} = $2;
595 my $extra_info = $self->_extra_column_info($table, $columns[$i], $column_info, $sth) || {};
596 $column_info = { %$column_info, %$extra_info };
598 $result{ $columns[$i] } = $column_info;
602 foreach my $col (keys %result) {
603 my $colinfo = $result{$col};
604 my $type_num = $colinfo->{data_type};
606 if (defined $type_num && $type_num =~ /^-?\d+\z/ && $dbh->can('type_info')) {
607 my $type_name = $self->_dbh_type_info_type_name($type_num);
608 $colinfo->{data_type} = lc $type_name if $type_name;
612 # check for instances of the same column name with different case in preserve_case=0 mode
613 if (not $self->preserve_case) {
616 foreach my $col (keys %result) {
617 push @{ $lc_colnames{lc $col} }, $col;
620 if (keys %lc_colnames != keys %result) {
621 my @offending_colnames = map @$_, grep @$_ > 1, values %lc_colnames;
623 my $offending_colnames = join ", ", map "'$_'", @offending_colnames;
625 croak "columns $offending_colnames in table @{[ $table->sql_name ]} collide in preserve_case=0 mode. preserve_case=1 mode required";
631 while (my ($col, $info) = each %result) {
632 $lc_result{ $self->_lc($col) } = $info;
635 %result = %lc_result;
641 # Need to override this for the buggy Firebird ODBC driver.
642 sub _dbh_type_info_type_name {
643 my ($self, $type_num) = @_;
645 # We wrap it in a try block for MSSQL+DBD::Sybase, which can have issues.
646 # TODO investigate further
647 my $type_info = try { $self->dbh->type_info($type_num) };
649 return $type_info ? $type_info->{TYPE_NAME} : undef;
652 # do not use this, override _columns_info_for instead
653 sub _extra_column_info {}
655 # override to mask warnings if needed
656 sub _dbh_table_info {
657 my ($self, $dbh, $table) = (shift, shift, shift);
659 return undef if !$dbh->can('table_info');
660 my $sth = $dbh->table_info(undef, $table->schema, $table->name);
661 while (my $info = $sth->fetchrow_hashref) {
662 next if !$self->_table_info_matches($table, $info);
668 sub _table_info_matches {
669 my ($self, $table, $info) = @_;
671 no warnings 'uninitialized';
672 return $info->{TABLE_SCHEM} eq $table->schema
673 && $info->{TABLE_NAME} eq $table->name;
676 # override to mask warnings if needed (see mysql)
677 sub _dbh_column_info {
678 my ($self, $dbh) = (shift, shift);
680 return $dbh->column_info(@_);
683 # If a coderef uses DBI->connect, this should get its connect info.
684 sub _try_infer_connect_info_from_coderef {
685 my ($self, $code) = @_;
687 my ($dsn, $user, $pass, $params);
689 no warnings 'redefine';
691 local *DBI::connect = sub {
692 (undef, $dsn, $user, $pass, $params) = @_;
697 return ($dsn, $user, $pass, $params);
703 return $self->schema->storage->dbh;
707 my ($self, $table) = @_;
709 my $info = $self->_dbh_table_info($self->dbh, $table)
711 return $info->{TABLE_TYPE} eq 'VIEW';
716 L<DBIx::Class::Schema::Loader>
720 See L<DBIx::Class::Schema::Loader/AUTHORS>.
724 This library is free software; you can redistribute it and/or modify it under
725 the same terms as Perl itself.
730 # vim:et sts=4 sw=4 tw=0: