This is Info file pm.info, produced by Makeinfo version 1.68 from the
input file bigpm.texi.
File: pm.info, Node: DBIx/AnyDBD, Next: DBIx/Broker, Prev: DBIx/Abstract, Up: Module List
DBD independant class
*********************
NAME
====
DBIx::AnyDBD - DBD independant class
DESCRIPTION
===========
This class provides application developers with an abstraction class a
level away from DBI, that allows them to write an application that works
on multiple database platforms. The idea isn't to take away the
responsibility for coding different SQL on different platforms, but to
simply provide a platform that uses the right class at the right time for
whatever DB is currently in use.
SYNOPSIS
========
use DBIx::AnyDBD;
my $db = DBIx::AnyDBD->connect("dbi:Oracle:sid1",
"user", "pass", {}, "MyClass");
my $foo = $db->foo;
my $blee = $db->blee;
That doesn't really tell you much... Because you have to implement a
bit more than that. Underneath you have to have a module MyClass::Oracle
that has methods foo() and blee in it. If those methods don't exist in
MyClass::Oracle, it will check in MyClass::Default, allowing you to
implement code that doesn't need to be driver dependant in the same
module. The foo() and blee() methods will recieve the DBIx::AnyDBD
instance as thier first parameter, and any parameters you pass just go as
parameters.
See the example Default.pm and Sybase.pm classes in the AnyDBD directory
for an example.
Implementation
==============
Underneath it's all implemented using the ISA hierarchy, which is
modified when you connect to your database. The inheritance tree ensures
that the right functions get called at the right time. There is also an
AUTOLOADer that steps in if the function doesn't exist and tries to call
the function on the database handle (i.e. in the DBI class). The
sub-classing uses `ucfirst($dbh-'{Driver}->{Name})> (along with some
clever fiddling for ODBC and ADO) to get the super-class, so if you don't
know what to name your class (see the list below first) then check that.
API
===
new( ... )
----------
dsn => $dsn,
user => $user,
pass => $pass,
attr => $attr,
package => $package
new() is a named parameter call that connects and creates a new db
object for your use. The named parameters are dsn, user, pass, attr and
package. The first 4 are just the parameters passed to DBI->connect, and
package contains the package prefix for your database dependant modules,
for example, if package was "MyPackage", the AUTOLOADer would look for
MyPackage::Oracle::func, and then MyPackage::Default::func. Beware that the
DBD driver will be ucfirst'ed, because lower case package names are
reserved as pragmas in perl. See the known DBD package mappings below.
If attr is undefined then the default attributes are:
AutoCommit => 1
PrintError => 0
RaiseError => 1
So be aware if you don't want your application dying to either eval{}
all db sections and catch the exception, or pass in a different attr
parameter.
After re-blessing the object into the database specific object,
DBIx::AnyDBD will call the _init() method on the object, if it exists.
This allows you to perform some driver specific post-initialization.
connect($dsn, $user, $pass, $attr, $package)
--------------------------------------------
connect() is very similar to DBI->connect, taking exactly the same first
4 parameters. The 5th parameter is the package prefix, as above.
connect() doesn't try and default attributes for you if you don't pass
them.
After re-blessing the object into the database specific object,
DBIx::AnyDBD will call the _init() method on the object, if it exists.
This allows you to perform some driver specific post-initialization.
$db->get_dbh()
--------------
This method is mainly for the DB dependant modules to use, it returns
the underlying DBI database handle. There will probably have code added
here to check the db is still connected, so it may be wise to always use
this method rather than trying to retrieve $self->{dbh} directly.
Known DBD Package Mappings
==========================
The following are the known DBD driver name mappings, including
ucfirst'ing them:
DBD::Oracle => Oracle.pm
DBD::Sybase => Sybase.pm
DBD::Pg => Pg.pm
DBD::mysql => Mysql.pm
DBD::Informix => Informix.pm
If you use this on other platforms, let me know what the mappings are.
ODBC
----
ODBC needed special support, so when run with DBD::ODBC, we call GetInfo
to find out what database we're connecting to, and then map to a known
package. The following are the known package mappings for ODBC:
Microsoft SQL Server (7.0 and MSDE) => MSSQL.pm
Microsoft SQL Server (6.5 and below) => Sybase.pm (sorry!)
Sybase (ASE and ASA) => Sybase.pm
Microsoft Access => Access.pm
Informix => Informix.pm
Oracle => Oracle.pm
Anything that isn't listed above will get mapped using the following
rule:
Get rdbms name using: $dbh->func(17, GetInfo);
Change whitespace to a single underscore
Add .pm on the end.
So if you need to know what your particular database will map to,
simply run the $dbh->func(17, GetInfo) method to find out.
ODBC also inserts `$package::ODBC.pm' into the hierarchy if it exists,
so the hierarchy will look like:
DBIx::AnyDBD <= ODBC.pm <= Informix.pm
(given that the database you're connecting to would be Informix). This
is useful because ODBC provides its own SQL abstraction layer.
ADO
---
ADO uses the same semantics as ODBC for determining the right driver or
module to load. However in extension to that, it inserts an ADO.pm into
the inheritance hierarchy if it exists, so the hierarchy would look like:
DBIx::AnyDBD <= ODBC.pm <= ADO.pm <= Informix.pm
I do understand that this is not fundamentally correct, as not all ADO
connections go through ODBC, but if you're doing some of that funky stuff
with ADO (such as queries on MS Index Server) then you're not likely to
need this module!
LICENCE
=======
This module is free software, and you may distribute it under the same
terms as Perl itself.
SUPPORT
=======
Commercial support for this module is available on a pay per incident
basis from Fastnet Software Ltd. Contact matt@sergeant.org for further
details. Alternatively join the DBI-Users mailing list, where I'll help
you out for free!
File: pm.info, Node: DBIx/Broker, Next: DBIx/CGITables, Prev: DBIx/AnyDBD, Up: Module List
a little layer somewhere between top-level code and raw DBI calls
*****************************************************************
NAME
====
DBIx::Broker - a little layer somewhere between top-level code and raw
DBI calls
SYNOPSIS
========
use DBIx::Broker;
$db = DBIx::Broker->new( $DBI_driver, $database, $hostname, $port, $user, $password );
$db = DBIx::Broker->new( );
$db->is_active( );
$db->set_db_handle( $classic_dbi_handle );
$classic_dbi_handle = $db->get_db_handle( );
$another_db_obj = $db->clone();
$db->debug_on( \*DEBUG_OUTPUT_HANDLE );
$db->debug_off( );
@query_results = $db->select( \@desired_fields, \@desired_tables, $stipulations, $hash_or_not );
@query_results = $db->select( \@desired_fields, $desired_table, $stipulations, $hash_or_not );
[..etc..]
@query_results = $db->select_all( \@desired_tables, $stipulations, $hash_or_not );
$db->select_incrementally( \@desired_fields, \@desired_tables, $stipulations );
$db->select_all_incrementally( \@desired_tables, $stipulations );
$next_row_ref = $db->get_next_row( $hash_or_not );
$number_of_rows = $db->count( $desired_table, $stipulations );
$a_single_value = $db->select_one_value( $desired_field, $desired_table, $stipulations );
@scalar_query_results = $db->select_one_column( $desired_field, \@desired_tables, $stipulations );
$single_row_ref = $db->select_one_row( \@desired_fields, \@desired_tables, $stipulations, $hash_or_not );
$db->delete( $desired_table, $stipulations );
$db->delete_all( $desired_table );
$db->insert( $desired_table, \%new_data );
$insert_id = $db->insert( $desired_table, \%new_data ); # MySQL only!!
$db->update( $desired_table, \%new_data, $stipulations );
$db->use_db( "another_database" );
$db->execute_sql( $some_raw_sql );
%table_schema = $db->get_table_schema( $table );
$primary_key = $db->get_primary_key( $table );
@auto_increment_fields = $db->get_auto_increments( $table );
# Oracle users may find this one handy..
$db->force_lowercase_fields( );
# this is just a wrapper around the corresponding DBI function
$db->func( @func_arguments, $func_name );
$db->disconnect( );
$db->finish( );
DESCRIPTION
===========
DBIx::Broker does what it says, it breaks databases (using DBI!).
Or else you can use it to unclutter your code of its annoying
and ugly ->execute()s and ->prepare()s and the like. It will
work using any Perl DBI driver (via ->new()) or database handle
(via ->set_db_handle()). The most common usage is to store the
query results in an array of references, each corresponding to
a row of results. You may retrieve the results as array refs or
hash refs, depending upon whether you supplied 0 or 1,
respectively, as the $hash_or_not parameter. For almost all
operations, you are able to supply the desired fields and
relevant tables either as a scalar (if there is just one value)
or as an array reference. i.e., you can say
$db->select( 'login', 'mail_accounts', 'WHERE status > 0', 1 )
or else set up something more complicated with field and table
arrays, like
@desired_fields = ( "c.firstName", "c.lastName", "c.customerID", "m.login" );
@desired_tables = ( "customers c", "mail_accounts m" );
$stipulations = 'WHERE m.status > 0 AND m.assoc_customerID = c.customerID';
@query_results = $db->select( \@desired_fields, \@desired_tables, $stipulations, 1 );
For inserting and updating rows, you send a hashref whose keys are
the table field names and whose values are the new entries. You may
also retrieve the insert ID for a new row upon $db->insert(); however,
this feature is currently only available with MySQL databases.
The *_incrementally() routines retrieve the same results as their
counterparts, but rather than returning all rows at once in an
array, the statement handle is left hanging and rows may be
retrieved one at a time, like
$next_row_ref = $db->get_next_row( $hash_or_not ).
It is recommended that you almost always use C<$hash_or_not = 1>,
for calling-level code readability, as well as extensibility.
Array references are supported only to avoid the inevitable
complaints that they are not supported.
The most common usage of $db->debug_on( ) is to send it \*STDERR
or \*STDOUT, but you can always have some fun and use a file
handle or a named pipe or something. While debugging is on, all
SQL statements are printed to the debugging output handle for
examination. This can be very handy.
Most of the time you\'ll be using this module something like
@customers = $db->select_all( 'customers', "WHERE age < 30", 1 );
foreach my $customer ( @customers ) {
print "Customer $customer->{'customerID'}: ";
print "$customer->{'last_name'}, $customer->{'first_name'}";
}
And if none of the existing functions are adequate, you can send
a raw SQL statement if you\'d like, by using
$db->execute_sql( "SELCET name FORM mailbox_tabel WHEER login = 'binkler'" );
You may retrieve table schema information in the form of a
hashtable, whose keys are the field names and whose values are
hashrefs to the various characteristics of each field, such as
'Type', 'Key', etc. For convenience, the ->get_primary_key()
and ->get_auto_increments() methods have also been added.
AUTHOR
======
xomina@bitstream.net
SEE ALSO
========
perl(1), DBI(3).
File: pm.info, Node: DBIx/CGITables, Next: DBIx/CGITables/MakeTemplates, Prev: DBIx/Broker, Up: Module List
Easy DB access from a CGI
*************************
NAME
====
DBIx::CGITables 0.001 - Easy DB access from a CGI
SYNOPSIS
========
use DBIx::CGITables;
my %parameters=();
my $query=DBIx::CGITables->new(\%parameters));
$query->search_execute_and_do_everything_even_parse_the_template();
DESCRIPTION
===========
This module is under development - and this version is not tested very
well. The documentation might not be completely in sync with the latest
changes, and the documentation is not optimized for easy understanding at
the moment. Sorry.
DBIx::CGITables is made for making database access through CGIs really
easy.
It's completely template-oriented. The templates are in HTML::Template
format. Some templates might be set up quickly by using
DBIx::CGITables::MakeTemplates; see the doc for this module. Some web
designer should fix the templates a bit.
The template approach might make the system a bit "static". Gerald
Richter has another approach based upon HTML::Embperl (not published at
the time I write this), if you'd better like a more dynamic system.
Anyway, I find it quite important not to mix HTML and code - the HTML
documents should be easy to manipulate by non-technical web editors.
Ideally, HTML and (language-dependent) content should be splitted. I
think such things might be done better by using Zope than cgis.
The database handling is done by DBIx::Recordset - this module gets its
parametres first from a CGI query, then it might be overridden or
completed by a parameter file, and the caller (your .cgi script or
whatever) is also free to modify or add parameters.
I'm hoping that anybody should get a working (though, probably ugly)
CGI interface to any kind of database simply:
1. Run DBIx::CGITables::MakeTemplates (see the pod)
2. Create the script given above at SYNOPSIS
3. Set up the webserver correct.
4. If you're not satisfied with the look, try to edit the html
templates.
5. If you're not satisfied with the functionality, try reading the
rest of this documentation, and the DBIx::Recordset documentation.
6. If you're still not satisfied with the functionality and/or you
find bugs, hack the code and submit the patches to the mailinglist
and/or me and/or (if DBIx::Recordset is affected) Gerhard Richter. If
you're not a perl hacker, or if you don't have time, send a mail about
what's wrong and/or what's missing to the mailinglist anyway. Or
privately to me if you don't want to write to a mailinglist.
PARAMETERS
==========
How to feed the script with parameters
--------------------------------------
Firstly, parameters are taken from the query.
Then, parameters on are taken from the *parameter file*. This file is
either located in the same folder as the templates, or in a folder as
specified in the parameters. The parameter file contains options to
DBIx::CGITables and to DBIx::Recordsets. I'm more or less trying to
follow up the parameter style DBIx::Recordset is using - with one special
character in front of the parameter suggesting what kind of parameter it
is. It is quite a bit kludgy, but it works. The parameters should be at
the key=value form, i.e.:
!Table=tablename !DataSource=... !NoRGV !NoT
CGITables will recognize a key starting with =, so it's possible to put
up '=execute=1' at a line. The default value will be 1, so '=execute'
should be equivalent.
If the line contains more than one equal sign which is not at the start
of the line, the other equal signs will be threated as a part of the value.
Eventually conflicts will appear as the param keys are duplicated in
the query and in the param file. The default is that the param file
overrides the query. This might be changed by a special code inserted in
front of the key=value-pair to override this behaviour. (I think too many
special codes might be a bit hairy ... but I hope this will work out
anyway). Those codes start with '%' since this character is not used by
DBIx::Recordset, and they're separated from the key=value couple by one
space. In addition to suggesting how collitions should be
%= or %! Always override options set other places
%+, Add new stuff to a comma separated list. The comma might be
replaced by any other character, and to \t and \n, or simply
removed. Use '\ ' for a blank and '\\' for a backslash.
%^, Prepend, separate from existing value with a comma (same rules
as above)
%? Yield - use this with default values that should only be set
if no other values are set.
%!() Override or ignore - that is, if the key exists, overwrite,
if not, ignore.
%() Ignore option (but keep it in template outputs)
%T Template option. The key=value is given the Template Class.
%RGV Recordset Global Variable, most important ones are
PreserveCase and Debug.
For =execute parameters (see DBIx::Recordset), %= or %! will `override'
other =execute by deleting those. %+ and %^ will execute things in order.
The default is to use the priority set by DBIx::Recordset.
In my older system, I had something called dependent and independent
subqueries. A `dependent subquery' is a link in the DBIx::Recordset
terminology. An `independent subquery' would be an independent
DBIx::Recordset object, i.e. for fetching data for a HTML select box. I
also had an option to only `resolve' links when the select returned only
one row - not to waste time fetching too much data when a long list was
fetched. I guess Recordset handles this more or less automagically.
`dependent subqueries' (or links, if you'd like) might be handed over
like this:
!Links/-street/!Table=street !Links/-street/!LinkedField=id
!Links/-street/!MainField=street_id
Drop the first `!Links' to create an independent subquery. The primary
subquery has the tag "default" without a starting minus. The tags should
better start with minus, to avoid inprobable though potential clashes with
other output template substitutions.
My earlier systems had some weird syntax for creating misc lists from
the parameters. DBIx::Recordset uses string splitting.
First characters
----------------
The first character in a parameter key or a line in the parameter file
is often special. It's a bit messy, but I think it's the easiest way to
do it, anyway - if the rules are obeyed there shouldn't be any
ambiguisities. Here's a complete list of the first characters:
% - reserved for a `special handling' code putted in front of the
real key/param. This special code is usually describing how to
handle parameter collitions, but also to tell that the key/param
should be ignored, or belongs somewhere else (i.e. the
PreserveCase option is a global variable that might need
modification)
/ - reserved for extra named Recordset objects.
! - reserved for Recordset initialization and important parameters to
CGITables.
- - reserved for the name of a named Recordset object.
See the DBIx::Recordset manual for those: ' - reserved for a DB column
key = value that needs quoting # - ...... numeric value \ -
...... value that should not be quoted (i.e. SQL function) + -
...... value with multiple fields * - ...... Operator for DB column
key
$ - misc options to Recordset and CGITables.
= - execute commands to Recordset
? - extra output (typically ?id and ?count)
; - If you need extra parameters for containing state, start the
variable name with one of those "comment" signs to avoid possible
clashes.
Supported and future parameters
-------------------------------
Supported parameters:
!!Filename - defaults to $ENV{PATH_TRANSLATED}
!!ParamFileDir - Default directory for finding ParamFile
!!ParamFile - See below for default. Ignores ParamFileDir.
!!QueryClass - defaults to 'CGI', but I'm intending to head for
CGI::Fast
!!Query - defaults to new !!QueryClass
!TemplateClass - defaults to 'HTML::Template'
!RecordsetClass - defaults to 'DBIx::Recordset'
!DoNothing
!SearchForm specifies that a search form should be printed. No
searching is done, and the default loop is returned with one empty
element. This is typically useful when viewing the data in a
looped form, then this one will create an empty form.
The parameters starting with '!!' must be set before the query and
parameter file is parsed.
!Mod2 should give an output variable ?mod2 which indicates whether the
row count is an odd or not. This is a very popular feature demand, though
not possible to implement in a nice way in this scheme. I don't think it
belongs to the template engine, but it would be quite ugly putting it
elsewhere, I think. I've putted a hack into the sub faenskopiering which
really shouldn't be called in the first place. This hack ignores !Mod2
and inserts ?mod2 into the tables.
$substring_search=column[,column..] will enable substring search for
the selected attribute. This means *column will be set to " LIKE " and
the value will be set to "%value%" in searches. This should be used with
care, as it might strain on most databases. Anyway, mysql handles it
well. Case sensitivity might be an issue, searches in mysql are always
case insensitive.
Possible future parameters: !IncludeParamfile !ParamMacro !Ooups
!OtherTemplate !SetSelected
For !Links: $fetch_always (default) $fetch_when_found_one
$fetch_when_found_more $fetch_when_found_none
Unfortunately I don't have the time writing more docs due to deadlines.
Output to the templates
-----------------------
Unfortunately I don't have the time writing more docs due to deadlines.
?count (at the top level)
The number of elements returned. Nice to use in constructs
like
Sorry, didn't find
anything
?mod2 (in all recordset loops)
0 for even, 1 for odd (0 beeing "odd")
any parameter key (at the top level) will give the corresponding
value.
a loop called cgi_query with the variables key and value will give
the query - nice for forwarding a query in hidden input tags.
Each database query have a loop with _only_ the database values
(except ?mod2 which really is a hack). It would have helped a lot
to have access to the outer scope variables in the inner loop, but
it seems like the author of the template engine in use disagrees.
TEMPLATE NAMING CONVENTIONS
===========================
The templates should have an extention matching /\.(\w+)$/ - typically
sth like mydatabase.CGITables or mydatabase.db or mydatabase.db_html. The
script will then search for the param file mydatabase.param.$1, i.e.
mydatabase.param.db. It might also use another template if found,
mydatabase.$status.$1, where $status might be one of (in prioritied order):
error
update_ok
delete_ok
add_ok
found_$n
found_more
(this is not completely implemented yet)
TODO
====
Probably a lot of important features are missing. It might also be
slow as it's costly to build Recordsets, and they're discarded after each
call to the cgi; I'm intending to solve this by optimizing for CGI::Fast
and storing the frequently used recordsets in a cache. Eventually such a
cache should be kept in shared memory. I haven't looked at it yet, but
Sam Tregar has announced some general module for keeping caches in shared
memory.
KNOWN BUGS
==========
The code contains this line several places: die "stub!";
When this code is executed with the warning flag, there might be some
warnings popping up from the DBIx::Recordset module. Those will
eventually disappear in newer versions of DBIx::Recordset.
This is UNDER DEVELOPMENT and ABSOLUTELY NOT GOOD ENOUGH TESTED. The
number of unknown bugs is probably high.
HISTORY
=======
Version 0.
This started as some template cgi system that needed database access.
To allow better flexibility, I made some hacks to allow SQL code to be
inserted into the template. This was ... ugly, hairy and hacky. I
expanded it, so the SQL code could be in separate files. It still was
ugly, hairy and hacky.
Version 1.
I started more or less from scratch, and made a new system, where SQL
code and other parameters to the script could be inserted into a special
parameter file. The script would "automagically" generate SQL to select,
update, insert and delete rows from tables. It started out a lot better
than version 0, but it was still hairy, and it certainly only became worse
and worse as more and more features was to be added.
Version 2.
I started from scratch again, this time with object oriented modules -
DBIx::Access and DBIx::CGIAccess. This time I aimed for cleanliness. But
I think it has grown more messy as more features was to be crammed in.
I'm currently merging from the Solid database to MySQL - and I'm a bit
horrified because MySQL is case sensitive, and Solid wasn't - which might
mean that I will have to redesign some of the parameter syntax (I've
chosen UPPERCASE for database column names, and lowercase for misc
options).
Version 3.
I registered at CPAN and got a "go" for DBIx::Tables and
DBIx::CGITables.
I scratched my head in a week. Then I started from scratch again,
discarding DBIx::Tables for DBIx::Recordset.
Some of the uglyness from earlier versions remain - a quite "ugly"
symbol usage in the param file/query, like "%() !Table=foo". Maybe I
would have tried doing it in a better way if it wasn't for Recordset
already having parameters prepended by a special character.
HACKING
=======
Feel free to submit patches, but also normal feedback, bugfixes and
wishlists.
SECURITY
========
It's up to you to provide proper security. I think the Right Way to do
it is to let the .cgi do the authentication (i.e. by SSL certificates or
transmitting the DB login and password encrypted) and then let the DBMS
control what privilegies the user should have.
Another way is to override all potentially harmful parameters to the
DBIx::Recordset, either by the param file or by a hash to sub
search_execute_and_do_everything_even_parse_the_template
AUTHOR
======
Tobias Brox
Feedback is appreciated - even flames. I will eventually put up a
mailing list if I notice any interesst about this module.
This module was written partly in my worktime. Shameless plug for my
employer:
Check our upcoming MMORPG at http://www.anarchy-online.com/ - Linux
client will be available.
Play multiplayer Spades, Backgammon, Poker and more for free at
http://www.funcom.com/ (Java)
File: pm.info, Node: DBIx/CGITables/MakeTemplates, Next: DBIx/Copy, Prev: DBIx/CGITables, Up: Module List
creates templates for CGITables.
********************************
NAME
====
DBIx::CGITables::MakeTemplates - creates templates for CGITables.
SYNOPSIS
========
cd /path/to/webserver/document_root/wherever/the/templates/should/be
mysqldump -d mydatabase > /tmp/schema.dd
echo '-- END' >> /tmp/schema.dd
vi /tmp/schema.dd
perl -MDBIx::CGITables::MakeTemplates -e 'make "dbi:mysql:test:localhost"'< /tmp/schema.dd
DESCRIPTION
===========
This is under construction, there might be code stubs, some of the
announced features might be missing, and if you're more unlucky it might
not work at all. Feedback will probably speed up my work on this.
This module takes its input from a set of data definitions (SQL `create
table' statements), and generates templates and parameter files for the
tables. The data definition file should be edited slightly before running
MakeTemplates.
The most important thing about the hand-editing is to deal with the
foreign keys. Mysql doesn't have them at all, so they will eventually
have to be manually added. If there is a "Foreign key
(foreign_id)..."-line, then the attribute line (typically something like
"foreign_id integer") should be removed - eventually the other way.
Attributes might be added to describe how to handle attributes and
foreign keys - unfortunately I don't have time documenting them at the
moment.
Eventually, the module should work without any editing of the DD at
all, just reading the eventual extra configuration information from an
extra file.
The module will produce three files; the form template
($dbname.$tablename.dbt), the list template
($dbname.$tablename.found_more.dbt) and the parameter file
($dbname.$tablename.param.dbt).
About foreign keys (not implemented yet);
Mysql doesn't have them - so if links are needed, the dd code needs to
be modified. The dd code should be modified anyway. In a create table
statement, the attribute should first be declared (i.e. `user_id
integer'), and later it should be declared that it's a foreign key. One of
those lines should be deleted - you will probably want to remove the
attribute declaration (the foreign key will be respected), alternatively
the foreign key declaration (letting the foreign key be threated as just
another attribute).
KNOWN BUGS
==========
This is just some loose hacks. This is under construction (maybe it
would be even better to start from scratch redoing it). This is
absolutely not tested very well. It is not a SQL parser, and it's only
tested for the output from mysql and solid. It's linebreak sensitive, so
it might not be compatible with other DBMSs. Foreign keys are not
supported yet. This documentation sucks. The code sucks. Well.
AUTHOR
======
Tobias Brox
File: pm.info, Node: DBIx/Copy, Next: DBIx/DBSchema, Prev: DBIx/CGITables/MakeTemplates, Up: Module List
For copying database content from one db to another
***************************************************
NAME
====
DBIx::Copy 0.02 - For copying database content from one db to another
SYNOPSIS
========
use DBIx::Copy; use DBI;
my $dbh_source=DBI->connect(...); my $dbh_target=DBI->connect(...);
my %options=();
my $copy_handler= DBIx::Copy->new($dbh_source, $dbh_target,
\%options);
$copy_handler->copy (['tabletobecopied', 'anothertabletobecopied',
...]);
DESCRIPTION
===========
For copying a DB. Future versions might handle mirroring as well, but
it's generally better if the source might send over a transaction log
somehow.
The current version takes a crude "select * from table" from the source
table, and crudely puts it all into the destination table, with a "delete
from table" and "insert into table values (?, ?, ?, ...)". Eventually
column names are specified (option column_translation_table). There might
be problems with this approach for all I know. Anyway, I think I can
promise that the synopsis above will behave the same way also for future
versions of DBIx::Copy.
Currently the module can only copy data content. Data definitions
might be handled in a future version.
OPTIONS
=======
table_translation_table
-----------------------
If a table called FOO in the source database should be called Bar in
the destination database, it's possible to specify it in the options:
$options{'table_tanslation_table'}={'FOO' => 'Bar'};
column_translation_table
------------------------
If the table FOO contains a column with the name
"LASTUPDATEDTIMESTAMP", and we want to call it "LastUpdated" in the table
Bar, it's possible to specify it:
$options{'column_translation_table'}->{FOO}={
'ID' => 'id',
'TITLE' => 'Title',
'LASTUPDATEDTIMESTAMP' => 'LastUpdated'
# _All_ columns you want to copy must be mentionated here. I
# will eventually fix a sub for initializing such a hash
# reference.
}
ERROR HANDLING AND STABILITY
============================
DBIx::Copy->new will die unless it's feeded with at least two arguments.
copy will just return undef if it fails somewhere.
The exception will probably be raised within the DBI. By adjusting
settings within the DBI object, the error handling might be improved.
Check the options AutoCommit, PrintError and RaiseError.
Currently the module is optimisticly lacking all kind of locking. This
will have to be done outside the module.
Be aware that the testing script following this package is NOT GOOD
ENOUGH, it should be tested by hand using the synopsis above.
TODO
====
data_definitions
----------------
The module should also be capable of copying data definitions
avoid_deletion
--------------
It should be possible to avoid deleting the old table before inserting
a new. The module should (by first running a select call) avoid inserting
duplicates. This option will only insert new rows, not update old ones.
replace
-------
Do not delete old data in the destination table unless it is to be
replaced by new data from the source table.
max_timestamp / mirror
----------------------
Check the timestamps and only import rows that has been modified after
max_timestamp (typically the timestamp for the last import).
mirror will try to fetch the last timestamp from the database, and
store the new timestamp in the database.
merge
-----
If the same row is edited in both databases, try to merge the result.
locking
-------
Locking should be supported somehow.
KNOWN BUGS
==========
Except for all the "buts" and "ifs" and missing features above - none
yet - but this module is very poorly tested!
LICENSE
=======
Copyright (c) 2000 Tobias Brox. All rights reserved. This program is
released under the GNU Public License. It might also be released under
The Artistic License if you send me a mail why GPL is insufficient.
AUTHOR
======
Tobias Brox . Comments, bug reports, patches and
flames are appreciated.
File: pm.info, Node: DBIx/DBSchema, Next: DBIx/DBSchema/ColGroup, Prev: DBIx/Copy, Up: Module List
Database-independent schema objects
***********************************
NAME
====
DBIx::DBSchema - Database-independent schema objects
SYNOPSIS
========
use DBIx::DBSchema;
$schema = new DBIx::DBSchema @dbix_dbschema_table_objects;
$schema = new_odbc DBIx::DBSchema $dbh;
$schema = new_odbc DBIx::DBSchema $dsn, $user, $pass;
$schema = new_native DBIx::DBSchema $dbh;
$schema = new_native DBIx::DBSchema $dsn, $user, $pass;
$schema->save("filename");
$schema = load DBIx::DBSchema "filename";
$schema->addtable($dbix_dbschema_table_object);
@table_names = $schema->tables;
$DBIx_DBSchema_table_object = $schema->table("table_name");
@sql = $schema->sql($dbh);
@sql = $schema->sql($dsn, $username, $password);
@sql = $schema->sql($dsn); #doesn't connect to database - less reliable
$perl_code = $schema->pretty_print;
%hash = eval $perl_code;
use DBI qw(:sql_types); $schema = pretty_read DBIx::DBSchema \%hash;
DESCRIPTION
===========
DBIx::DBSchema objects are collections of DBIx::DBSchema::Table objects
and represent a database schema.
This module implements an OO-interface to database schemas. Using this
module, you can create a database schema with an OO Perl interface. You
can read the schema from an existing database. You can save the schema to
disk and restore it a different process. Most importantly, DBIx::DBSchema
can write SQL CREATE statements statements for different databases from a
single source.
Currently supported databases are MySQL and PostgreSQL. DBIx::DBSchema
will attempt to use generic SQL syntax for other databases. Assistance
adding support for other databases is welcomed.
METHODS
=======
new TABLE_OBJECT, TABLE_OBJECT, ...
Creates a new DBIx::DBSchema object.
new_odbc DATABASE_HANDLE | DATA_SOURCE USERNAME PASSWORD [ ATTR ]
Creates a new DBIx::DBSchema object from an existing data source,
which can be specified by passing an open DBI database handle, or by
passing the DBI data source name, username, and password. This uses
the experimental DBI type_info method to create a schema with
standard (ODBC) SQL column types that most closely correspond to any
non-portable column types. Use this to import a schema that you wish
to use with many different database engines. Although primary key
and (unique) index information will only be read from databases with
DBIx::DBSchema::DBD drivers (currently MySQL and PostgreSQL), import
of column names and attributes *should* work for any database.
new_native DATABASE_HANDLE | DATA_SOURCE USERNAME PASSWORD [ ATTR ]
Creates a new DBIx::DBSchema object from an existing data source,
which can be specified by passing an open DBI database handle, or by
passing the DBI data source name, username and password. This uses
database-native methods to read the schema, and will preserve any
non-portable column types. The method is only available if there is
a DBIx::DBSchema::DBD for the corresponding database engine
(currently, MySQL and PostgreSQL).
load FILENAME
Loads a DBIx::DBSchema object from a file.
save FILENAME
Saves a DBIx::DBSchema object to a file.
addtable TABLE_OBJECT
Adds the given DBIx::DBSchema::Table object to this DBIx::DBSchema.
tables
Returns a list of the names of all tables.
table TABLENAME
Returns the specified DBIx::DBSchema::Table object.
sql [ DATABASE_HANDLE | DATA_SOURCE [ USERNAME PASSWORD [ ATTR ] ] ]
Returns a list of SQL `CREATE' statements for this schema.
The data source can be specified by passing an open DBI database
handle, or by passing the DBI data source name, username and password.
Although the username and password are optional, it is best to call
this method with a database handle or data source including a valid
username and password - a DBI connection will be opened and the
quoting and type mapping will be more reliable.
If passed a DBI data source (or handle) such as `DBI:mysql:database'
or `DBI:Pg:dbname=database', will use syntax specific to that
database engine. Currently supported databases are MySQL and
PostgreSQL.
If not passed a data source (or handle), or if there is no driver for
the specified database, will attempt to use generic SQL syntax.
pretty_print
Returns the data in this schema as Perl source, suitable for
assigning to a hash.
pretty_read HASHREF
Creates a schema as specified by a data structure such as that
created by pretty_print method.
AUTHOR
======
Ivan Kohler
COPYRIGHT
=========
Copyright (c) 2000 Ivan Kohler Copyright (c) 2000 Mail Abuse Prevention
System LLC All rights reserved. This program is free software; you can
redistribute it and/or modify it under the same terms as Perl itself.
BUGS
====
Each DBIx::DBSchema object should have a name which corresponds to its
name within the SQL database engine (DBI data source).
pretty_print is actually pretty ugly.
Perhaps pretty_read should eval column types so that we can use DBI
qw(:sql_types) here instead of externally.
SEE ALSO
========
*Note DBIx/DBSchema/Table: DBIx/DBSchema/Table,, *Note
DBIx/DBSchema/ColGroup: DBIx/DBSchema/ColGroup,, *Note
DBIx/DBSchema/ColGroup/Unique: DBIx/DBSchema/ColGroup/Unique,, *Note
DBIx/DBSchema/ColGroup/Index: DBIx/DBSchema/ColGroup/Index,, *Note
DBIx/DBSchema/Column: DBIx/DBSchema/Column,, *Note DBIx/DBSchema/DBD:
DBIx/DBSchema/DBD,, `DBIx::DBSchema::mysql' in this node,
`DBIx::DBSchema::Pg' in this node, `FS::Record' in this node, `DBI' in
this node
File: pm.info, Node: DBIx/DBSchema/ColGroup, Next: DBIx/DBSchema/ColGroup/Index, Prev: DBIx/DBSchema, Up: Module List
Column group objects
********************
NAME
====
DBIx::DBSchema::ColGroup - Column group objects
SYNOPSIS
========
use DBIx::DBSchema::ColGroup;
$colgroup = new DBIx::DBSchema::ColGroup ( $lol_ref );
$colgroup = new DBIx::DBSchema::ColGroup ( \@lol );
$colgroup = new DBIx::DBSchema::ColGroup (
[
[ 'single_column' ],
[ 'multiple_columns', 'another_column', ],
]
);
$lol_ref = $colgroup->lol_ref;
@sql_lists = $colgroup->sql_list;
@singles = $colgroup->singles;
DESCRIPTION
===========
DBIx::DBSchema::ColGroup objects represent sets of sets of columns.
(IOW a "list of lists" - see *Note Perllol: (perl.info)perllol,.)
METHODS
=======
new [ LOL_REF ]
Creates a new DBIx::DBSchema::ColGroup object. Pass a reference to a
list of lists of column names.
lol_ref
Returns a reference to a list of lists of column names.
sql_list
Returns a flat list of comma-separated values, for SQL statements.
For example:
@lol = (
[ 'single_column' ],
[ 'multiple_columns', 'another_column', ],
);
$colgroup = new DBIx::DBSchema::ColGroup ( \@lol );
print join("\n", $colgroup->sql_list), "\n";
Will print:
single_column
multiple_columns, another_column
singles
Returns a flat list of all single item lists.
AUTHOR
======
Ivan Kohler
COPYRIGHT
=========
Copyright (c) 2000 Ivan Kohler Copyright (c) 2000 Mail Abuse Prevention
System LLC All rights reserved. This program is free software; you can
redistribute it and/or modify it under the same terms as Perl itself.
BUGS
====
SEE ALSO
========
*Note DBIx/DBSchema/Table: DBIx/DBSchema/Table,, *Note
DBIx/DBSchema/ColGroup/Unique: DBIx/DBSchema/ColGroup/Unique,, *Note
DBIx/DBSchema/ColGroup/Index: DBIx/DBSchema/ColGroup/Index,, *Note
DBIx/DBSchema: DBIx/DBSchema,, *Note Perllol: (perl.info)perllol,, *Note
Perldsc: (perl.info)perldsc,, `DBI' in this node
File: pm.info, Node: DBIx/DBSchema/ColGroup/Index, Next: DBIx/DBSchema/ColGroup/Unique, Prev: DBIx/DBSchema/ColGroup, Up: Module List
Index column group object
*************************
NAME
====
DBIx::DBSchema::ColGroup::Index - Index column group object
SYNOPSIS
========
use DBIx::DBSchema::ColGroup::Index;
# see DBIx::DBSchema::ColGroup methods
DESCRIPTION
===========
DBIx::DBSchema::ColGroup::Index objects represent the (non-unique)
indices of a database table (*Note DBIx/DBSchema/Table:
DBIx/DBSchema/Table,). DBIx::DBSchema::ColGroup::Index inherits from
DBIx::DBSchema::ColGroup.
BUGS
====
Is this empty subclass needed?
SEE ALSO
========
*Note DBIx/DBSchema/ColGroup: DBIx/DBSchema/ColGroup,, *Note
DBIx/DBSchema/ColGroup/Unique: DBIx/DBSchema/ColGroup/Unique,, *Note
DBIx/DBSchema/Table: DBIx/DBSchema/Table,, *Note DBIx/DBSchema:
DBIx/DBSchema,, `FS::Record' in this node
File: pm.info, Node: DBIx/DBSchema/ColGroup/Unique, Next: DBIx/DBSchema/Column, Prev: DBIx/DBSchema/ColGroup/Index, Up: Module List
Unique column group object
**************************
NAME
====
DBIx::DBSchema::ColGroup::Unique - Unique column group object
SYNOPSIS
========
use DBIx::DBSchema::ColGroup::Unique;
# see DBIx::DBSchema::ColGroup methods
DESCRIPTION
===========
DBIx::DBSchema::ColGroup::Unique objects represent the unique indices
of a database table (*Note DBIx/DBSchema/Table: DBIx/DBSchema/Table,).
DBIx::DBSchema::ColGroup:Unique inherits from DBIx::DBSchema::ColGroup.
BUGS
====
Is this empty subclass needed?
SEE ALSO
========
*Note DBIx/DBSchema/ColGroup: DBIx/DBSchema/ColGroup,, *Note
DBIx/DBSchema/ColGroup/Index: DBIx/DBSchema/ColGroup/Index,, *Note
DBIx/DBSchema/Table: DBIx/DBSchema/Table,, *Note DBIx/DBSchema:
DBIx/DBSchema,, `FS::Record' in this node
File: pm.info, Node: DBIx/DBSchema/Column, Next: DBIx/DBSchema/DBD, Prev: DBIx/DBSchema/ColGroup/Unique, Up: Module List
Column objects
**************
NAME
====
DBIx::DBSchema::Column - Column objects
SYNOPSIS
========
use DBIx::DBSchema::Column;
#named params with a hashref (preferred)
$column = new DBIx::DBSchema::Column ( {
'name' => 'column_name',
'type' => 'varchar'
'null' => 'NOT NULL',
'length' => 64,
'default' => '
'local' => '',
} );
#list
$column = new DBIx::DBSchema::Column ( $name, $sql_type, $nullability, $length, $default, $local );
$name = $column->name;
$column->name( 'name' );
$sql_type = $column->type;
$column->sql_type( 'sql_type' );
$null = $column->null;
$column->null( 'NULL' );
$column->null( 'NOT NULL' );
$column->null( '' );
$length = $column->length;
$column->length( '10' );
$column->length( '8,2' );
$default = $column->default;
$column->default( 'Roo' );
$sql_line = $column->line;
$sql_line = $column->line($datasrc);
DESCRIPTION
===========
DBIx::DBSchema::Column objects represent columns in tables (see *Note
DBIx/DBSchema/Table: DBIx/DBSchema/Table,).
METHODS
=======
new HASHREF
new [ name [ , type [ , null [ , length [ , default [ , local ] ] ] ] ] ]
Creates a new DBIx::DBSchema::Column object. Takes a hashref of named
parameters, or a list. name is the name of the column. type is the
SQL data type. null is the nullability of the column (intrepreted
using Perl's rules for truth, with one exception: `NOT NULL' is
false). length is the SQL length of the column. default is the
default value of the column. local is reserved for database-specific
information.
name [ NAME ]
Returns or sets the column name.
type [ TYPE ]
Returns or sets the column type.
null [ NULL ]
Returns or sets the column null flag (the empty string is equivalent
to `NOT NULL')
length [ LENGTH ]
Returns or sets the column length.
default [ LOCAL ]
Returns or sets the default value.
local [ LOCAL ]
Returns or sets the database-specific field.
line [ DATABASE_HANDLE | DATA_SOURCE [ USERNAME PASSWORD [ ATTR ] ] ]
Returns an SQL column definition.
The data source can be specified by passing an open DBI database
handle, or by passing the DBI data source name, username and password.
Although the username and password are optional, it is best to call
this method with a database handle or data source including a valid
username and password - a DBI connection will be opened and the
quoting and type mapping will be more reliable.
If passed a DBI data source (or handle) such as `DBI:mysql:database'
or `DBI:Pg:dbname=database', will use syntax specific to that
database engine. Currently supported databases are MySQL and
PostgreSQL. Non-standard syntax for other engines (if applicable)
may also be supported in the future.
AUTHOR
======
Ivan Kohler
COPYRIGHT
=========
Copyright (c) 2000 Ivan Kohler Copyright (c) 2000 Mail Abuse Prevention
System LLC All rights reserved. This program is free software; you can
redistribute it and/or modify it under the same terms as Perl itself.
BUGS
====
line() has database-specific foo that probably ought to be abstracted
into the DBIx::DBSchema:DBD:: modules.
SEE ALSO
========
*Note DBIx/DBSchema/Table: DBIx/DBSchema/Table,, *Note DBIx/DBSchema:
DBIx/DBSchema,, *Note DBIx/DBSchema/DBD: DBIx/DBSchema/DBD,, `DBI' in this
node
File: pm.info, Node: DBIx/DBSchema/DBD, Next: DBIx/DBSchema/DBD/Pg, Prev: DBIx/DBSchema/Column, Up: Module List
DBIx::DBSchema Driver Writer's Guide and Base Class
***************************************************
NAME
====
DBIx::DBSchema::DBD - DBIx::DBSchema Driver Writer's Guide and Base
Class
SYNOPSIS
========
perldoc DBIx::DBSchema::DBD
package DBIx::DBSchema::DBD::FooBase
use DBIx::DBSchmea::DBD;
@ISA = qw(DBIx::DBSchema::DBD);
DESCRIPTION
===========
Drivers should be named DBIx::DBSchema::DBD::DatabaseName, where
DatabaseName is the same as the DBD:: driver for this database. Drivers
should implement the following class methods:
columns CLASS DBI_DBH TABLE
Given an active DBI database handle, return a listref of listrefs (see
*Note Perllol: (perl.info)perllol,), each containing six elements:
column name, column type, nullability, column length, column default,
and a field reserved for driver-specific use.
column CLASS DBI_DBH TABLE COLUMN
Same as columns above, except return the listref for a single column.
You can inherit from DBIx::DBSchema::DBD to provide this function.
primary_key CLASS DBI_DBH TABLE
Given an active DBI database handle, return the primary key for the
specified table.
unique CLASS DBI_DBH TABLE
Given an active DBI database handle, return a hashref of unique
indices. The keys of the hashref are index names, and the values are
arrayrefs which point a list of column names for each. See `"HASHES
OF LISTS"', *Note Perldsc: (perl.info)perldsc, and `"HASHES OF
LISTS"', *Note DBIx/DBSchema/ColGroup: DBIx/DBSchema/ColGroup,.
index CLASS DBI_DBH TABLE
Given an active DBI database handle, return a hashref of (non-unique)
indices. The keys of the hashref are index names, and the values are
arrayrefs which point a list of column names for each. See `"HASHES
OF LISTS"', *Note Perldsc: (perl.info)perldsc, and `"HASHES OF
LISTS"', *Note DBIx/DBSchema/ColGroup: DBIx/DBSchema/ColGroup,.
AUTHOR
======
Ivan Kohler
COPYRIGHT
=========
Copyright (c) 2000 Ivan Kohler Copyright (c) 2000 Mail Abuse Prevention
System LLC All rights reserved. This program is free software; you can
redistribute it and/or modify it under the same terms as Perl itself.
BUGS
====
%typemap needs to be documented.
SEE ALSO
========
*Note DBIx/DBSchema: DBIx/DBSchema,, *Note DBIx/DBSchema/DBD/mysql:
DBIx/DBSchema/DBD/mysql,, *Note DBIx/DBSchema/DBD/Pg:
DBIx/DBSchema/DBD/Pg,, *Note DBIx/DBSchema/ColGroup:
DBIx/DBSchema/ColGroup,, `DBI' in this node, `DBI::DBD' in this node,
*Note Perllol: (perl.info)perllol,, `"HASHES OF LISTS"', *Note Perldsc:
(perl.info)perldsc,