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
182       Nathan Anderson <http://1id.com/=nathan.anderson> contribued updates to
183       the SQLite and Sybase drivers.
184

CONTRIBUTIONS

186       Contributions are welcome!  I'm especially keen on any interest in the
187       top items/projects below under BUGS.
188

REPOSITORY

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

COPYRIGHT

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

BUGS AND TODO

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