|
DBIx::Namespace - Provide directory-like table names |
dbh()disconnect()root()
DBIx::Namespace - Provide directory-like table names
One of the limitations of SQL is that the tables lie within a flat namespace. This module provides a directory-like partitioning, allowing identical table names to co-exist within the same database.
use DBIx::Namespace;
$db = new DBIx::Namespace(
dbsource => 'dbi:mysql:',
database => 'a_database',
user => 'a_user',
password => 'a_password',
);
$db->create($table, $sql_description);
$db->delete($table);
$db->replace($table, %field_data);
my $ar = $db->select($table, 'name, address',
'where country = ?', 'UK');
my $hr = $db->select_hash($table,
'where id = ?', $id);
my $phone = $db->select_one($table, 'phone',
'where name = ?', 'Jim');
$db->delete($table, 'where id = ?', $id);
my $sql_name = $db->table($namespace_name);
my $dbh = $db->dbh();
=head1 DESCRIPTION
This module provides more flexiblity in naming tables than SQL provides by default. They are identified using a '::' separated naming structure similar to Perl module names, e.g. 'my::deeply::nested::sql::table'.
These user names are mapped to SQL table names using index tables stored in the database. There is, therefore, a small speed penalty in using this module as an extra lookup is needed for each name partition. The index tables are of the form 'i<integer>' while user data is stored in tables named 't<integer>'. If these name patterns are avoided, there is no reason why this heirarchical name structure should not co-exist alongside standard table names.
A connection is made to the mysql server and the specified database is selected for use. An exeption will be
thrown if an error is encountered. options may be either a hash ref or a list of hash keys and values.
Recognized keys are as follows.
'DBI:mysql:host=localhost;port=3306;database=test'
Specifying a database is sometimes required. However, passing it as a seperate option enables it to be created if it does not yet exist, assuming the user has adequate permissions. The minimal string would be:
'DBI:mysql:'
=item user
The database user that is logging on. The environment variable DBI_USER may be used instead.
Creates an empty mysql table for name with the SQL specificion given. Returns the SQL name of the table created.
Example
$db->create('table_name', q(
userID INT(4) NOT NULL AUTO_INCREMENT,
serialNr INT(8) NOT NULL,
email VARCHAR(80),
PRIMARY KEY (userID),
UNIQUE (serialNr)
));
row(s) to be deleted.
where clause.
One or more rows are deleted from the specified table. An exception is raised if an problem is encountered.
An extremely useful method as it ensures the data is stored even if only a single field in the data hash has
changed.
=item data
All subsequent arguments should be in hash 'field => value' format. As well as entering these directly, data
can also be a prefilled array or hash.
Note that this uses the SQL REPLACE ... SET variant, and that any fields not specified in data will probably be
set to NULL. This can be overcome with a prior call to select_hash.
Example
my $db = new DBIx::Namespace(...);
my $table = 'addresses::my::family';
my $hr = $db->select_hash( $table,
'surname = ? and forename = ?',
'Smith', 'John'
);
$hr->{telephone} = '0191428991';
$db->replace( $table, %$hr );
The telephone number has been changed and the rest of the data remain as they were.
=cut
sub select { my ($o, $table, $columns, $clause, @values) = @_; my $tbl = $o->table($table); $clause = '' unless defined $clause; my $r = $o->{dbh}->selectall_arrayref(``select $columns from $tbl $clause'', undef, @values); return $r ? @$r : (); }
Perform a SQL SELECT query.
Although used most of the time, the 'WHERE...' clause is optional. For example, this might begin 'ORDER BY...', so the WHERE keyword cannot be added by the method. To avoid confusion it is also required by the other select variants.
clause.
In list context returns a list of arrayrefs, one for each record. In scalar context the number of rows is returned. An exception is thrown if an error is encountered.
If this is too limiting, sql_select allows complete flexibility. A call to table will be required to obtain the SQL table name needed.
Perform a SQL SELECT query to obtain all fields in a single record.
The keyword 'WHERE' is required as part of the argument to match the select method.
clause.
Returns a reference to a hash keyed by field names.
Perform a SQL SELECT query to obtain particular field value(s).
The keyword 'WHERE' is required as part of the argument to match the select method.
clause.
In list context, the requested fields of the first matching row are returned. In scalar context, only the first field is returned.
All methods beginning 'sql_' work on SQL table names, not the Namespace names used by the MAIN METHODS.
dbh()Return a handle to the underlying DBI object.
disconnect()Manual disconnection. Not usually required as this happens automatically once the DBIx::Namespace object is finished with.
Quote a string for passing to mysql. Any embedded quotes are suitably escaped as well.
root()Return the SQL name of the root table.
This looks up the user table name given and returns the SQL table name. If name is omitted, the root table
is returned. An exception is thrown if the table doesn't exist.
In list context returns a list of array refs. Each array item describes the field structure of the mysql table given:
[ field, type, null, key, default, extra ]
In scalar context, this is converted to a hash of hashes keyed initially by 'field'.
If column is given, only the data for that column is returned. For example, after
my $h = $db->sql_describe('table', 'address');
$h->{$address}{type} might be 'varchar(80)'.
Evaluate a SQL SELECT command. Called in scalar context it returns a single value.
Although intended for evaluating SQL functions that evaluate without accessing any tables, it will return a single
record if called in an array context. Tables must be SQL names and values are needed for any '?' placeholders.
The SELECT statement is not required.
Examples
my $day = $db->sql_eval( 'dayname(now())' );
my $day = $db->sql_eval( 'dayname(?)', $date );
Return 1 if the named table exists in the database, 0 if it does not. Note that table is a SQL table name and
therefore case sensitive.
Output the mapping of Namespace names and their corresponding SQL table names.
Returns a list of arrayrefs each of the following form.
[ Namespace_name, SQL_table, nesting_level ]
The nesting_level is included for printing indented output, e.g.
print ' ' x $nesting_level, $name, "\n";
=item data
All subsequent arguments should be in hash 'field => value' format. As well as entering these directly, data
can also be a prefilled array or hash.
Note that this uses the SQL REPLACE ... SET variant, and that any fields not specified in data will probably be
set to NULL.
Perform a SQL SELECT query. query should be everything after the SELECT keyword. If it includes any '?'
placeholders, the corresponding number of values should be passed.
In list context returns a list of arrayrefs, one for each record. In scalar context the number of rows is returned. An exception is thrown if an error is encountered.
Perform a SQL SHOW query. query should be everything after the SHOW keyword. If it includes any '?'
placeholders, the corresponding number of values should be passed.
In list context returns a list of arrayrefs, one for each record. In scalar context the number of rows is returned. An exception is thrown if an error is encountered.
Indexes are not removed when they become empty.
Chris Willmot, chris@willmot.org.uk
the Finance::Shares::MySQL manpage uses this as a base class. the Finance::Shares::CGI manpage make use of this directly.
|
DBIx::Namespace - Provide directory-like table names |