DBIx::DBSchema(3pm)

1DBSchema(3)           User Contributed Perl Documentation          DBSchema(3)
2
3
4

NAME

6       DBIx::DBSchema - Database-independent schema objects
7

SYNOPSIS

9         use DBIx::DBSchema;
10
11         $schema = new DBIx::DBSchema @dbix_dbschema_table_objects;
12         $schema = new_odbc DBIx::DBSchema $dbh;
13         $schema = new_odbc DBIx::DBSchema $dsn, $user, $pass;
14         $schema = new_native DBIx::DBSchema $dbh;
15         $schema = new_native DBIx::DBSchema $dsn, $user, $pass;
16
17         $schema->save("filename");
18         $schema = load DBIx::DBSchema "filename" or die $DBIx::DBSchema::errstr;
19
20         $schema->addtable($dbix_dbschema_table_object);
21
22         @table_names = $schema->tables;
23
24         $DBIx_DBSchema_table_object = $schema->table("table_name");
25
26         @sql = $schema->sql($dbh);
27         @sql = $schema->sql($dsn, $username, $password);
28         @sql = $schema->sql($dsn); #doesn't connect to database - less reliable
29
30         $perl_code = $schema->pretty_print;
31         %hash = eval $perl_code;
32         use DBI qw(:sql_types); $schema = pretty_read DBIx::DBSchema \%hash;
33

DESCRIPTION

35       DBIx::DBSchema objects are collections of DBIx::DBSchema::Table objects
36       and represent a database schema.
37
38       This module implements an OO-interface to database schemas.  Using this
39       module, you can create a database schema with an OO Perl interface.
40       You can read the schema from an existing database.  You can save the
41       schema to disk and restore it in a different process.  You can write
42       SQL CREATE statements statements for different databases from a single
43       source.  You can transform one schema to another, adding any necessary
44       new columns, tables, indices and foreign keys.
45
46       Currently supported databases are MySQL, PostgreSQL and SQLite.  Sybase
47       and Oracle drivers are partially implemented.  DBIx::DBSchema will
48       attempt to use generic SQL syntax for other databases.  Assistance
49       adding support for other databases is welcomed.  See
50       DBIx::DBSchema::DBD, "Driver Writer's Guide and Base Class".
51

METHODS

53       new TABLE_OBJECT, TABLE_OBJECT, ...
54           Creates a new DBIx::DBSchema object.
55
56       new_odbc DATABASE_HANDLE | DATA_SOURCE USERNAME PASSWORD [ ATTR ]
57           Creates a new DBIx::DBSchema object from an existing data source,
58           which can be specified by passing an open DBI database handle, or
59           by passing the DBI data source name, username, and password.  This
60           uses the experimental DBI type_info method to create a schema with
61           standard (ODBC) SQL column types that most closely correspond to
62           any non-portable column types.  Use this to import a schema that
63           you wish to use with many different database engines.  Although
64           primary key and (unique) index information will only be read from
65           databases with DBIx::DBSchema::DBD drivers (currently MySQL and
66           PostgreSQL), import of column names and attributes *should* work
67           for any database.  Note that this method only uses "ODBC" column
68           types; it does not require or use an ODBC driver.
69
70       new_native DATABASE_HANDLE | DATA_SOURCE USERNAME PASSWORD [ ATTR ]
71           Creates a new DBIx::DBSchema object from an existing data source,
72           which can be specified by passing an open DBI database handle, or
73           by passing the DBI data source name, username and password.  This
74           uses database-native methods to read the schema, and will preserve
75           any non-portable column types.  The method is only available if
76           there is a DBIx::DBSchema::DBD for the corresponding database
77           engine (currently, MySQL and PostgreSQL).
78
79       load FILENAME
80           Loads a DBIx::DBSchema object from a file.  If there is an error,
81           returns false and puts an error message in $DBIx::DBSchema::errstr;
82
83       save FILENAME
84           Saves a DBIx::DBSchema object to a file.
85
86       addtable TABLE_OBJECT
87           Adds the given DBIx::DBSchema::Table object to this DBIx::DBSchema.
88
89       tables
90           Returns a list of the names of all tables.
91
92       table TABLENAME
93           Returns the specified DBIx::DBSchema::Table object.
94
95       sql [ DATABASE_HANDLE | DATA_SOURCE [ USERNAME PASSWORD [ ATTR ] ] ]
96           Returns a list of SQL `CREATE' statements for this schema.
97
98           The data source can be specified by passing an open DBI database
99           handle, or by passing the DBI data source name, username and
100           password.
101
102           Although the username and password are optional, it is best to call
103           this method with a database handle or data source including a valid
104           username and password - a DBI connection will be opened and used to
105           check the database version as well as for more reliable quoting and
106           type mapping.  Note that the database connection will be used
107           passively, not to actually run the CREATE statements.
108
109           If passed a DBI data source (or handle) such as
110           `DBI:mysql:database' or `DBI:Pg:dbname=database', will use syntax
111           specific to that database engine.  Currently supported databases
112           are MySQL and PostgreSQL.
113
114           If not passed a data source (or handle), or if there is no driver
115           for the specified database, will attempt to use generic SQL syntax.
116
117       sql_update_schema [ OPTIONS_HASHREF, ] PROTOTYPE_SCHEMA [
118       DATABASE_HANDLE | DATA_SOURCE [ USERNAME PASSWORD [ ATTR ] ] ]
119           Returns a list of SQL statements to update this schema so that it
120           is idential to the provided prototype schema, also a DBIx::DBSchema
121           object.
122
123           Right now this method knows how to add new tables and alter
124           existing tables, including indices.  If specifically requested by
125           passing an options hashref with drop_tables set true before all
126           other arguments, it will also drop tables.
127
128           See "sql_alter_table" in DBIx::DBSchema::Table, "sql_add_column" in
129           DBIx::DBSchema::Column and "sql_alter_column" in
130           DBIx::DBSchema::Column for additional specifics and limitations.
131
132           The data source can be specified by passing an open DBI database
133           handle, or by passing the DBI data source name, username and
134           password.
135
136           Although the username and password are optional, it is best to call
137           this method with a database handle or data source including a valid
138           username and password - a DBI connection will be opened and used to
139           check the database version as well as for more reliable quoting and
140           type mapping.  Note that the database connection will be used
141           passively, not to actually run the CREATE statements.
142
143           If passed a DBI data source (or handle) such as
144           `DBI:mysql:database' or `DBI:Pg:dbname=database', will use syntax
145           specific to that database engine.  Currently supported databases
146           are MySQL and PostgreSQL.
147
148           If not passed a data source (or handle), or if there is no driver
149           for the specified database, will attempt to use generic SQL syntax.
150
151       update_schema [ OPTIONS_HASHREF, ] PROTOTYPE_SCHEMA, DATABASE_HANDLE |
152       DATA_SOURCE [ USERNAME PASSWORD [ ATTR ] ]
153           Same as sql_update_schema, except actually runs the SQL commands to
154           update the schema.  Throws a fatal error if any statement fails.
155
156       pretty_print
157           Returns the data in this schema as Perl source, suitable for
158           assigning to a hash.
159
160       pretty_read HASHREF
161           This method is not recommended.  If you need to load and save your
162           schema to a file, see the "load" and "save" methods.
163
164           Creates a schema as specified by a data structure such as that
165           created by pretty_print method.
166

AUTHORS

168       Ivan Kohler <ivan-dbix-dbschema@420.am>
169
170       Charles Shapiro <charles.shapiro@numethods.com> and Mitchell Friedman
171       <mitchell.friedman@numethods.com> contributed the start of a Sybase
172       driver.
173
174       Daniel Hanks <hanksdc@about-inc.com> contributed the Oracle driver.
175
176       Jesse Vincent contributed the SQLite driver and fixes to quiet down
177       internal usage of the old API.
178
179       Slaven Rezic <srezic@cpan.org> contributed column and table dropping,
180       Pg bugfixes and more.
181

CONTRIBUTIONS

183       Contributions are welcome!  I'm especially keen on any interest in the
184       top items/projects below under BUGS.
185

REPOSITORY

187       The code is available from our public git repository:
188
189         git clone git://git.freeside.biz/DBIx-DBSchema.git
190
191       Or on the web:
192
193         http://freeside.biz/gitweb/?p=DBIx-DBSchema.git
194         Or:
195         http://freeside.biz/gitlist/DBIx-DBSchema.git
196

COPYRIGHT

198       Copyright (c) 2000-2007 Ivan Kohler Copyright (c) 2000 Mail Abuse
199       Prevention System LLC Copyright (c) 2007-2015 Freeside Internet
200       Services, Inc.  All rights reserved.  This program is free software;
201       you can redistribute it and/or modify it under the same terms as Perl
202       itself.
203

BUGS AND TODO

205       Multiple primary keys are not yet supported.
206
207       Foreign keys: need to support dropping, NOT VALID, reverse engineering
208       w/mysql
209
210       Need to port and test with additional databases
211
212       Each DBIx::DBSchema object should have a name which corresponds to its
213       name within the SQL database engine (DBI data source).
214
215       Need to support "using" index attribute in pretty_read and in reverse
216       engineering
217
218       sql CREATE TABLE output should convert integers (i.e. use DBI
219       qw(:sql_types);) to local types using DBI->type_info plus a hash to
220       fudge things
221
222   PRETTY_ BUGS
223       pretty_print is actually pretty ugly.
224
225       pretty_print isn't so good about quoting values...  save/load is a much
226       better alternative to using pretty_print/pretty_read
227
228       pretty_read is pretty ugly too.
229
230       pretty_read should *not* create and pass in old-style unique/index
231       indices when nothing is given in the read.
232
233       Perhaps pretty_read should eval column types so that we can use DBI
234       qw(:sql_types) here instead of externally.
235
236       perhaps we should just get rid of pretty_read entirely.  pretty_print
237       is useful for debugging, but pretty_read is pretty bunk.
238