1 package DBIx::DBSchema;
5 use DBIx::DBSchema::_util qw(_load_driver _dbh _parse_opt);
6 use DBIx::DBSchema::Table 0.12;
7 use DBIx::DBSchema::Index;
8 use DBIx::DBSchema::Column;
9 use DBIx::DBSchema::ForeignKey;
11 our $VERSION = '0.46_01';
12 $VERSION = eval $VERSION; # modperlstyle: convert the string into a number
20 DBIx::DBSchema - Database-independent schema objects
26 $schema = new DBIx::DBSchema @dbix_dbschema_table_objects;
27 $schema = new_odbc DBIx::DBSchema $dbh;
28 $schema = new_odbc DBIx::DBSchema $dsn, $user, $pass;
29 $schema = new_native DBIx::DBSchema $dbh;
30 $schema = new_native DBIx::DBSchema $dsn, $user, $pass;
32 $schema->save("filename");
33 $schema = load DBIx::DBSchema "filename" or die $DBIx::DBSchema::errstr;
35 $schema->addtable($dbix_dbschema_table_object);
37 @table_names = $schema->tables;
39 $DBIx_DBSchema_table_object = $schema->table("table_name");
41 @sql = $schema->sql($dbh);
42 @sql = $schema->sql($dsn, $username, $password);
43 @sql = $schema->sql($dsn); #doesn't connect to database - less reliable
45 $perl_code = $schema->pretty_print;
46 %hash = eval $perl_code;
47 use DBI qw(:sql_types); $schema = pretty_read DBIx::DBSchema \%hash;
51 DBIx::DBSchema objects are collections of DBIx::DBSchema::Table objects and
52 represent a database schema.
54 This module implements an OO-interface to database schemas. Using this module,
55 you can create a database schema with an OO Perl interface. You can read the
56 schema from an existing database. You can save the schema to disk and restore
57 it in a different process. You can write SQL CREATE statements statements for
58 different databases from a single source. You can transform one schema to
59 another, adding any necessary new columns, tables, indices and foreign keys.
61 Currently supported databases are MySQL, PostgreSQL and SQLite. Sybase and
62 Oracle drivers are partially implemented. DBIx::DBSchema will attempt to use
63 generic SQL syntax for other databases. Assistance adding support for other
64 databases is welcomed. See L<DBIx::DBSchema::DBD>, "Driver Writer's Guide and
71 =item new TABLE_OBJECT, TABLE_OBJECT, ...
73 Creates a new DBIx::DBSchema object.
78 my($proto, @tables) = @_;
79 my %tables = map { $_->name, $_ } @tables; #check for duplicates?
81 my $class = ref($proto) || $proto;
86 bless ($self, $class);
90 =item new_odbc DATABASE_HANDLE | DATA_SOURCE USERNAME PASSWORD [ ATTR ]
92 Creates a new DBIx::DBSchema object from an existing data source, which can be
93 specified by passing an open DBI database handle, or by passing the DBI data
94 source name, username, and password. This uses the experimental DBI type_info
95 method to create a schema with standard (ODBC) SQL column types that most
96 closely correspond to any non-portable column types. Use this to import a
97 schema that you wish to use with many different database engines. Although
98 primary key and (unique) index information will only be read from databases
99 with DBIx::DBSchema::DBD drivers (currently MySQL and PostgreSQL), import of
100 column names and attributes *should* work for any database. Note that this
101 method only uses "ODBC" column types; it does not require or use an ODBC
107 my($proto, $dbh) = ( shift, _dbh(@_) );
109 map { new_odbc DBIx::DBSchema::Table $dbh, $_ } _tables_from_dbh($dbh)
113 =item new_native DATABASE_HANDLE | DATA_SOURCE USERNAME PASSWORD [ ATTR ]
115 Creates a new DBIx::DBSchema object from an existing data source, which can be
116 specified by passing an open DBI database handle, or by passing the DBI data
117 source name, username and password. This uses database-native methods to read
118 the schema, and will preserve any non-portable column types. The method is
119 only available if there is a DBIx::DBSchema::DBD for the corresponding database engine (currently, MySQL and PostgreSQL).
124 my($proto, $dbh) = (shift, _dbh(@_) );
126 map { new_native DBIx::DBSchema::Table ( $dbh, $_ ) } _tables_from_dbh($dbh)
132 Loads a DBIx::DBSchema object from a file. If there is an error, returns
133 false and puts an error message in $DBIx::DBSchema::errstr;
138 my($proto,$file)=@_; #use $proto ?
143 eval { $self = Storable::retrieve($file); };
145 if ( $@ && $@ =~ /not.*storable/i ) { #then try FreezeThaw
148 eval "use FreezeThaw;";
153 or do { $errstr = "Can't open $file: $!"; return ''; };
154 my $string = join('',<FILE>);
156 or do { $errstr = "Can't close $file: $!"; return ''; };
157 ($self) = FreezeThaw::thaw($string);
171 Saves a DBIx::DBSchema object to a file.
176 #my($self, $file) = @_;
177 Storable::nstore(@_);
180 =item addtable TABLE_OBJECT
182 Adds the given DBIx::DBSchema::Table object to this DBIx::DBSchema.
188 $self->{'tables'}->{$table->name} = $table; #check for dupliates?
193 Returns a list of the names of all tables.
199 keys %{$self->{'tables'}};
202 =item table TABLENAME
204 Returns the specified DBIx::DBSchema::Table object.
210 $self->{'tables'}->{$table};
213 =item sql [ DATABASE_HANDLE | DATA_SOURCE [ USERNAME PASSWORD [ ATTR ] ] ]
215 Returns a list of SQL `CREATE' statements for this schema.
217 The data source can be specified by passing an open DBI database handle, or by
218 passing the DBI data source name, username and password.
220 Although the username and password are optional, it is best to call this method
221 with a database handle or data source including a valid username and password -
222 a DBI connection will be opened and used to check the database version as well
223 as for more reliable quoting and type mapping. Note that the database
224 connection will be used passively, B<not> to actually run the CREATE
227 If passed a DBI data source (or handle) such as `DBI:mysql:database' or
228 `DBI:Pg:dbname=database', will use syntax specific to that database engine.
229 Currently supported databases are MySQL and PostgreSQL.
231 If not passed a data source (or handle), or if there is no driver for the
232 specified database, will attempt to use generic SQL syntax.
237 my($self, $dbh) = ( shift, _dbh(@_) );
239 ( map { $self->table($_)->sql_create_table($dbh); } $self->tables ),
240 ( map { $self->table($_)->sql_add_constraints($dbh); } $self->tables ),
244 =item sql_update_schema [ OPTIONS_HASHREF, ] PROTOTYPE_SCHEMA [ DATABASE_HANDLE | DATA_SOURCE [ USERNAME PASSWORD [ ATTR ] ] ]
246 Returns a list of SQL statements to update this schema so that it is idential
247 to the provided prototype schema, also a DBIx::DBSchema object.
249 Right now this method knows how to add new tables and alter existing tables,
250 including indices. If specifically requested by passing an options hashref
251 with B<drop_tables> set true before all other arguments, it will also drop
254 See L<DBIx::DBSchema::Table/sql_alter_table>,
255 L<DBIx::DBSchema::Column/sql_add_column> and
256 L<DBIx::DBSchema::Column/sql_alter_column> for additional specifics and
259 The data source can be specified by passing an open DBI database handle, or by
260 passing the DBI data source name, username and password.
262 Although the username and password are optional, it is best to call this method
263 with a database handle or data source including a valid username and password -
264 a DBI connection will be opened and used to check the database version as well
265 as for more reliable quoting and type mapping. Note that the database
266 connection will be used passively, B<not> to actually run the CREATE
269 If passed a DBI data source (or handle) such as `DBI:mysql:database' or
270 `DBI:Pg:dbname=database', will use syntax specific to that database engine.
271 Currently supported databases are MySQL and PostgreSQL.
273 If not passed a data source (or handle), or if there is no driver for the
274 specified database, will attempt to use generic SQL syntax.
278 #gosh, false laziness w/DBSchema::Table::sql_alter_schema
280 sub sql_update_schema {
281 my($self, $opt, $new, $dbh) = ( shift, _parse_opt(\@_), shift, _dbh(@_) );
286 foreach my $table ( $new->tables ) {
288 if ( $self->table($table) ) {
290 warn "$table exists\n" if $DEBUG > 1;
293 $self->table($table)->sql_alter_table( $new->table($table),
296 $self->table($table)->sql_alter_constraints( $new->table($table),
301 warn "table $table does not exist.\n" if $DEBUG;
303 push @r, $new->table($table)->sql_create_table( $dbh );
304 push @later, $new->table($table)->sql_add_constraints( $dbh );
310 if ( $opt->{'drop_tables'} ) {
312 warn "drop_tables enabled\n" if $DEBUG;
314 # drop tables not in $new
315 foreach my $table ( grep !$new->table($_), $self->tables ) {
317 warn "table $table should be dropped.\n" if $DEBUG;
319 push @r, $self->table($table)->sql_drop_table( $dbh );
327 warn join("\n", @r). "\n"
334 =item update_schema [ OPTIONS_HASHREF, ] PROTOTYPE_SCHEMA, DATABASE_HANDLE | DATA_SOURCE [ USERNAME PASSWORD [ ATTR ] ]
336 Same as sql_update_schema, except actually runs the SQL commands to update
337 the schema. Throws a fatal error if any statement fails.
342 #my($self, $new, $dbh) = ( shift, shift, _dbh(@_) );
343 my($self, $opt, $new, $dbh) = ( shift, _parse_opt(\@_), shift, _dbh(@_) );
345 foreach my $statement ( $self->sql_update_schema( $opt, $new, $dbh ) ) {
346 $dbh->do( $statement )
347 or die "Error: ". $dbh->errstr. "\n executing: $statement";
354 Returns the data in this schema as Perl source, suitable for assigning to a
365 my $table = $self->table($tablename);
366 my %indices = $table->indices;
368 "'$tablename' => {\n".
371 #cant because -w complains about , in qw()
372 # (also biiiig problems with empty lengths)
374 #$table->column($_)->type. " ".
375 #( $table->column($_)->null ? 'NULL' : 0 ). " ".
376 #$table->column($_)->length. " ),\n"
378 "'". $table->column($_)->type. "', ".
379 "'". $table->column($_)->null. "', ".
380 "'". $table->column($_)->length. "', ".
382 ( ref($table->column($_)->default)
383 ? "\\'". ${ $table->column($_)->default }. "'"
384 : "'". $table->column($_)->default. "'"
387 "'". $table->column($_)->local. "',\n"
391 " 'primary_key' => '". $table->primary_key. "',\n".
393 #old style index representation..
396 $table->{'unique'} # $table->_unique
397 ? " 'unique' => [ ". join(', ',
398 map { "[ '". join("', '", @{$_}). "' ]" }
399 @{$table->_unique->lol_ref}
404 ( $table->{'index'} # $table->_index
405 ? " 'index' => [ ". join(', ',
406 map { "[ '". join("', '", @{$_}). "' ]" }
407 @{$table->_index->lol_ref}
413 " 'indices' => { ". join( ",\n ",
415 map { my $iname = $_;
416 my $index = $indices{$iname};
419 ? " 'using' => '". $index->using ."',\n"
422 " 'unique' => ". $index->unique .",\n".
424 join("', '", @{$index->columns} ).
433 " 'foreign_keys' => [ ". join( ",\n ",
435 map { my $name = $_->constraint;
449 =item pretty_read HASHREF
451 This method is B<not> recommended. If you need to load and save your schema
452 to a file, see the L</load> and L</save> methods.
454 Creates a schema as specified by a data structure such as that created by
455 B<pretty_print> method.
460 my($proto, $href) = @_;
462 my $schema = $proto->new( map {
465 my $info = $href->{$tablename};
468 while ( @{$info->{'columns'}} ) {
469 push @columns, DBIx::DBSchema::Column->new(
470 splice @{$info->{'columns'}}, 0, 6
474 DBIx::DBSchema::Table->new({
475 'name' => $tablename,
476 'primary_key' => $info->{'primary_key'},
477 'columns' => \@columns,
480 'indices' => [ map { my $idx_info = $info->{'indices'}{$_};
481 DBIx::DBSchema::Index->new({
484 'unique' => $idx_info->{'unique'},
485 'columns' => $idx_info->{'columns'},
488 keys %{ $info->{'indices'} }
496 # private subroutines
498 sub _tables_from_dbh {
500 my $driver = _load_driver($dbh);
502 scalar(eval "DBIx::DBSchema::DBD::$driver->default_db_catalog");
504 scalar(eval "DBIx::DBSchema::DBD::$driver->default_db_schema");
505 my $sth = $dbh->table_info($db_catalog, $db_schema, '', 'TABLE')
507 #map { $_->{TABLE_NAME} } grep { $_->{TABLE_TYPE} eq 'TABLE' }
508 # @{ $sth->fetchall_arrayref({ TABLE_NAME=>1, TABLE_TYPE=>1}) };
509 map { $_->[0] } grep { $_->[1] =~ /^TABLE$/i }
510 @{ $sth->fetchall_arrayref([2,3]) };
517 Ivan Kohler <ivan-dbix-dbschema@420.am>
519 Charles Shapiro <charles.shapiro@numethods.com> and Mitchell Friedman
520 <mitchell.friedman@numethods.com> contributed the start of a Sybase driver.
522 Daniel Hanks <hanksdc@about-inc.com> contributed the Oracle driver.
524 Jesse Vincent contributed the SQLite driver and fixes to quiet down
525 internal usage of the old API.
527 Slaven Rezic <srezic@cpan.org> contributed column and table dropping, Pg
532 Contributions are welcome! I'm especially keen on any interest in the top
533 items/projects below under BUGS.
537 The code is available from our public git repository:
539 git clone git://git.freeside.biz/DBIx-DBSchema.git
543 http://freeside.biz/gitweb/?p=DBIx-DBSchema.git
545 http://freeside.biz/gitlist/DBIx-DBSchema.git
549 Copyright (c) 2000-2007 Ivan Kohler
550 Copyright (c) 2000 Mail Abuse Prevention System LLC
551 Copyright (c) 2007-2015 Freeside Internet Services, Inc.
553 This program is free software; you can redistribute it and/or modify it under
554 the same terms as Perl itself.
558 Multiple primary keys are not yet supported.
560 Foreign keys: need to support dropping, NOT VALID, reverse engineering w/mysql
562 Need to port and test with additional databases
564 Each DBIx::DBSchema object should have a name which corresponds to its name
565 within the SQL database engine (DBI data source).
567 Need to support "using" index attribute in pretty_read and in reverse
570 sql CREATE TABLE output should convert integers
571 (i.e. use DBI qw(:sql_types);) to local types using DBI->type_info plus a hash
576 pretty_print is actually pretty ugly.
578 pretty_print isn't so good about quoting values... save/load is a much better
579 alternative to using pretty_print/pretty_read
581 pretty_read is pretty ugly too.
583 pretty_read should *not* create and pass in old-style unique/index indices
584 when nothing is given in the read.
586 Perhaps pretty_read should eval column types so that we can use DBI
587 qw(:sql_types) here instead of externally.
589 perhaps we should just get rid of pretty_read entirely. pretty_print is useful
590 for debugging, but pretty_read is pretty bunk.
594 L<DBIx::DBSchema::Table>, L<DBIx::DBSchema::Index>,
595 L<DBIx::DBSchema::Column>, L<DBIx::DBSchema::DBD>,
596 L<DBIx::DBSchema::DBD::mysql>, L<DBIx::DBSchema::DBD::Pg>, L<FS::Record>,