1 package DBIx::DBSchema::Column;
6 use DBIx::DBSchema::_util qw(_load_driver _dbh _parse_opt);
12 DBIx::DBSchema::Column - Column objects
16 use DBIx::DBSchema::Column;
18 #named params with a hashref (preferred)
19 $column = new DBIx::DBSchema::Column ( {
20 'name' => 'column_name',
29 $column = new DBIx::DBSchema::Column ( $name, $sql_type, $nullability, $length, $default, $local );
31 $name = $column->name;
32 $column->name( 'name' );
34 $sql_type = $column->type;
35 $column->type( 'sql_type' );
37 $null = $column->null;
38 $column->null( 'NULL' );
39 $column->null( 'NOT NULL' );
42 $length = $column->length;
43 $column->length( '10' );
44 $column->length( '8,2' );
46 $default = $column->default;
47 $column->default( 'Roo' );
49 $sql_line = $column->line;
50 $sql_line = $column->line($datasrc);
52 $sql_add_column = $column->sql_add_column;
53 $sql_add_column = $column->sql_add_column($datasrc);
57 DBIx::DBSchema::Column objects represent columns in tables (see
58 L<DBIx::DBSchema::Table>).
66 =item new [ name [ , type [ , null [ , length [ , default [ , local ] ] ] ] ] ]
68 Creates a new DBIx::DBSchema::Column object. Takes a hashref of named
69 parameters, or a list. B<name> is the name of the column. B<type> is the SQL
70 data type. B<null> is the nullability of the column (intrepreted using Perl's
71 rules for truth, with one exception: `NOT NULL' is false). B<length> is the
72 SQL length of the column. B<default> is the default value of the column.
73 B<local> is reserved for database-specific information.
75 Note: If you pass a scalar reference as the B<default> rather than a scalar value, it will be dereferenced and quoting will be forced off. This can be used to pass SQL functions such as C<now()> or explicit empty strings as C<''> as
82 my $class = ref($proto) || $proto;
88 #carp "Old-style $class creation without named parameters is deprecated!";
89 #croak "FATAL: old-style $class creation no longer supported;".
90 # " use named parameters";
92 $self = { map { $_ => shift } qw(name type null length default local) };
95 #croak "Illegal name: ". $self->{'name'}
96 # if grep $self->{'name'} eq $_, @reserved_words;
98 $self->{'null'} =~ s/^NOT NULL$//i;
99 $self->{'null'} = 'NULL' if $self->{'null'};
101 bless ($self, $class);
107 Returns or sets the column name.
113 if ( defined($value) ) {
114 #croak "Illegal name: $name" if grep $name eq $_, @reserved_words;
115 $self->{'name'} = $value;
123 Returns or sets the column type.
129 if ( defined($value) ) {
130 $self->{'type'} = $value;
138 Returns or sets the column null flag (the empty string is equivalent to
145 if ( defined($value) ) {
146 $value =~ s/^NOT NULL$//i;
147 $value = 'NULL' if $value;
148 $self->{'null'} = $value;
154 =item length [ LENGTH ]
156 Returns or sets the column length.
162 if ( defined($value) ) {
163 $self->{'length'} = $value;
169 =item default [ LOCAL ]
171 Returns or sets the default value.
177 if ( defined($value) ) {
178 $self->{'default'} = $value;
185 =item local [ LOCAL ]
187 Returns or sets the database-specific field.
193 if ( defined($value) ) {
194 $self->{'local'} = $value;
200 =item table_obj [ TABLE_OBJ ]
202 Returns or sets the table object (see L<DBIx::DBSchema::Table>). Typically
203 set internally when a column object is added to a table object.
209 if ( defined($value) ) {
210 $self->{'table_obj'} = $value;
212 $self->{'table_obj'};
218 Returns the table name, or the empty string if this column has not yet been
225 $self->{'table_obj'} ? $self->{'table_obj'}->name : '';
228 =item line [ DATABASE_HANDLE | DATA_SOURCE [ USERNAME PASSWORD [ ATTR ] ] ]
230 Returns an SQL column definition.
232 The data source can be specified by passing an open DBI database handle, or by
233 passing the DBI data source name, username and password.
235 Although the username and password are optional, it is best to call this method
236 with a database handle or data source including a valid username and password -
237 a DBI connection will be opened and the quoting and type mapping will be more
240 If passed a DBI data source (or handle) such as `DBI:mysql:database' or
241 `DBI:Pg:dbname=database', will use syntax specific to that database engine.
242 Currently supported databases are MySQL and PostgreSQL. Non-standard syntax
243 for other engines (if applicable) may also be supported in the future.
248 my($self, $dbh) = ( shift, _dbh(@_) );
250 my $driver = $dbh ? _load_driver($dbh) : '';
251 my $dbd = "DBIx::DBSchema::DBD::$driver";
258 %typemap = eval "\%${dbd}::typemap" if $driver;
259 my $type = defined( $typemap{uc($self->type)} )
260 ? $typemap{uc($self->type)}
264 # callback into the database-specific driver
267 my $hashref = $dbd->column_callback( $dbh, $self->table_name, $self );
269 $type = $hashref->{'effective_type'}
270 if $hashref->{'effective_type'};
272 my $null = $self->null;
274 #we seem to do this for mysql/Pg/SQLite, i think this should be the default
275 #add something to $hashref if drivers need to overrdide?
276 $null ||= "NOT NULL";
278 $null =~ s/^NULL$// unless $hashref->{'explicit_null'};
280 my $default = $hashref->{'effective_default'} || $self->quoted_default($dbh);
281 $default = "DEFAULT $default" if $default ne '';
283 my $local = $self->local;
284 $local = $hashref->{'effective_local'}
285 if $hashref->{'effective_local'};
293 $type. ( ( defined($self->length) && $self->length )
294 ? '('.$self->length.')'
299 ( defined($local) ? $local : ''),
304 =item quoted_default DATABASE_HANDLE
306 Returns this column's default value quoted for the database.
311 my($self, $dbh) = @_;
312 my $driver = $dbh ? _load_driver($dbh) : '';
314 return ${$self->default} if ref($self->default);
316 my $dbd = "DBIx::DBSchema::DBD::$driver";
318 return $dbh->quote($self->default)
319 if defined($self->default)
320 && $self->default ne ''
322 && $dbd->column_value_needs_quoting($self);
324 return $self->default;
328 =item sql_add_column [ DBH ]
330 Returns SQL to add this column to an existing table. (To create a new table,
331 see L<DBIx::DBSchema::Table/sql_create_table> instead.)
333 NOTE: This interface has changed in 0.41
335 Returns two listrefs. The first is a list of column alteration SQL fragments
336 for an ALTER TABLE statement. The second is a list of full SQL statements that
337 should be run after the ALTER TABLE statement.
339 The data source can be specified by passing an open DBI database handle, or by
340 passing the DBI data source name, username and password.
342 Although the username and password are optional, it is best to call this method
343 with a database handle or data source including a valid username and password -
344 a DBI connection will be opened and the quoting and type mapping will be more
347 If passed a DBI data source (or handle) such as `DBI:Pg:dbname=database', will
348 use PostgreSQL-specific syntax. Non-standard syntax for other engines (if
349 applicable) may also be supported in the future.
354 my($self, $dbh) = ( shift, _dbh(@_) );
356 die "$self: this column is not assigned to a table"
357 unless $self->table_name;
359 my $driver = $dbh ? _load_driver($dbh) : '';
361 my @alter_table = ();
363 my $table = $self->table_name;
365 my $dbd = "DBIx::DBSchema::DBD::$driver";
366 my $hashref = $dbd->add_column_callback( $dbh, $table, $self );
369 if ( $hashref->{'effective_type'} ) {
370 $real_type = $self->type;
371 $self->type($hashref->{'effective_type'});
374 my $real_null = undef;
375 if ( exists($hashref->{'effective_null'}) ) {
376 $real_null = $self->null;
377 $self->null($hashref->{'effective_null'});
380 push @alter_table, "ADD COLUMN ". $self->line($dbh);
382 push @sql, @{ $hashref->{'sql_after'} } if $hashref->{'sql_after'};
384 push @sql, "ALTER TABLE $table ADD PRIMARY KEY ( ".
385 $self->table_obj->primary_key. " )"
386 if $self->name eq $self->table_obj->primary_key;
388 $self->type($real_type) if $real_type;
389 $self->null($real_null) if defined $real_null;
391 (\@alter_table, \@sql);
395 =item sql_alter_column PROTOTYPE_COLUMN [ DATABASE_HANDLE | DATA_SOURCE [ USERNAME PASSWORD [ ATTR ] ] ]
397 Returns SQL to alter this column so that it is identical to the provided
398 prototype column, also a DBIx::DBSchema::Column object.
400 NOTE: This interface has changed in 0.41
402 Returns two listrefs. The first is a list of column alteration SQL fragments
403 for an ALTER TABLE statement. The second is a list of full SQL statements that
404 should be run after the ALTER TABLE statement.
406 Optionally, the data source can be specified by passing an open DBI database
407 handle, or by passing the DBI data source name, username and password.
409 If passed a DBI data source (or handle) such as `DBI:Pg:dbname=database', will
410 use PostgreSQL-specific syntax. Non-standard syntax for other engines (if
411 applicable) may also be supported in the future.
413 If not passed a data source (or handle), or if there is no driver for the
414 specified database, will attempt to use generic SQL syntax.
418 sub sql_alter_column {
419 my($self, $opt, $new, $dbh) = ( shift, _parse_opt(\@_), shift, _dbh(@_) );
421 my $table = $self->table_name;
422 die "$self: this column is not assigned to a table"
425 my $name = $self->name;
427 my $driver = $dbh ? _load_driver($dbh) : '';
429 my @alter_table = ();
432 my $dbd = "DBIx::DBSchema::DBD::$driver";
433 my $hashref = $dbd->alter_column_callback( $dbh, $table, $self, $new );
435 if ( $hashref->{'sql_alter'} ) {
437 push @sql, $hashref->{'sql_alter'};
442 # not yet implemented. how do we tell which old column it was?
445 if ( $hashref->{'sql_alter_type'} ) {
446 push @alter_table, $hashref->{'sql_alter_type'};
449 # change nullability...
451 if ( $hashref->{'sql_alter_null'} ) {
453 push @sql, $hashref->{'sql_alter_null'};
457 # change nullability from NOT NULL to NULL
458 if ( ! $self->null && $new->null ) {
460 push @alter_table, "ALTER COLUMN $name DROP NOT NULL";
464 # change nullability from NULL to NOT NULL...
465 # this one could be more complicated, need to set a DEFAULT value and update
467 if ( $self->null && ! $new->null ) {
469 push @alter_table, "ALTER COLUMN $name SET NOT NULL";
476 my $old_default = $self->quoted_default($dbh);
477 my $new_default = $new->quoted_default($dbh);
478 if ( $old_default ne $new_default
479 && ( uc($old_default) ne 'NOW()' || uc($new_default) ne 'NOW()' )
483 #warn "old default: $old_default / new default: $new_default\n";
485 my $alter = "ALTER COLUMN $name";
487 if ( $new_default ne '' ) {
488 #warn "changing from $old_default to $new_default\n";
489 push @alter_table, "$alter SET DEFAULT $new_default";
490 } elsif ( $old_default !~ /^nextval/i ) { #Pg-specific :(
491 push @alter_table, "$alter DROP DEFAULT";
493 push @sql, "UPDATE TABLE $table SET $name = NULL WHERE $name = ''"
494 if $opt->{'nullify_default'} && $old_default eq "''" && $new->null;
499 # change other stuff... (what next?)
503 (\@alter_table, \@sql);
507 =item sql_drop_column [ DBH ]
509 Returns SQL to drop this column from an existing table.
511 NOTE: This interface has changed in 0.41
513 Returns a list of column alteration SQL fragments for an ALTER TABLE statement.
515 The optional database handle or DBI data source/username/password is not yet
520 sub sql_drop_column {
521 my( $self, $dbh ) = ( shift, _dbh(@_) );
523 my $table = $self->table_name;
524 my $name = $self->name;
526 ("DROP COLUMN $name"); # XXX what about indexes???
533 Ivan Kohler <ivan-dbix-dbschema@420.am>
537 Copyright (c) 2000-2006 Ivan Kohler
538 Copyright (c) 2007-2013 Freeside Internet Services, Inc.
540 This program is free software; you can redistribute it and/or modify it under
541 the same terms as Perl itself.
545 The new() method should warn that
546 "Old-style $class creation without named parameters is deprecated!"
548 Better documentation is needed for sql_add_column
550 sql_alter_column() has database-specific foo that should be abstracted info
551 DBIx::DBSchema::DBD::Pg
553 nullify_default option should be documented
557 L<DBIx::DBSchema::Table>, L<DBIx::DBSchema>, L<DBIx::DBSchema::DBD>, L<DBI>