NAME

DBIx::Namespace - Provide directory-like table names

ABSTRACT

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.

SYNOPSIS

    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();

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.

CONSTRUCTOR

new( [options] )

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.

dbsource

This is the DBD driver string string passed to DBI::connect(). It depends on the database driver being used. For mysql it would be of the form:

'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:'

user

The database user that is logging on. The environment variable DBI_USER may be used instead.

password

The user's password. The environment variable DBI_PASS may be used instead. If neither is provided, an attempt is made to read a password from the console.

database

The name of the database to be used.

MAIN METHODS

create( name, description )

name: A user defined name for the table to be created. It may be a compound name made of '::' seperated tokens, like Perl package names.
description: A string of comma seperated mysql field and index definitions. See the mysql documentation for more details (esp. 'CREATE TABLE Syntax' and 'Column Types').

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)
    ));

delete( table [, where [, values...] )

table: The user name of the table containing the record to be deleted.
where: A where clause identifying the row(s) to be deleted.
values: Zero or more values to fill any '?' placeholders specified in the where clause.

One or more rows are deleted from the specified table. An exception is raised if an problem is encountered.

replace( table, data... )

An extremely useful method as it ensures the data is stored even if only a single field in the data hash has changed.

table: A Namespace table name, case sensitive. Use sql_replace if you have the SQL table name.
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.

select( name, columns [, clause [, values...]] )

Perform a SQL SELECT query.

name

The Namespace name identifying the table to be searched.

columns

A comma seperated list of field names.

clause

The rest of the SELECT statement, typically beginning 'WHERE...'. If it includes any '?' placeholders, the corresponding number of values should be passed.

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.

values

Zero or more strings or numbers matching the '?' placeholders in 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.

select_hash( name , clause [, values...]] )

Perform a SQL SELECT query to obtain all fields in a single record.

name

The Namespace name identifying the table to be searched.

clause

The rest of the SELECT statement (beginning "WHERE..."). If it includes any '?' placeholders, the corresponding number of values should be passed.

The keyword 'WHERE' is required as part of the argument to match the select method.

values

Zero or more strings or numbers matching the '?' placeholders in clause.

Returns a reference to a hash keyed by field names.

select_one( name, column, clause [, values...]] )

Perform a SQL SELECT query to obtain particular field value(s).

name

The Namespace name identifying the table to be searched.

column

The column name (or a string of comma seperated names) required.

clause

The rest of the SELECT statement, beginning "WHERE...". If it includes any '?' placeholders, the corresponding number of values should be passed.

The keyword 'WHERE' is required as part of the argument to match the select method.

values

Zero or more strings or numbers matching the '?' placeholders in clause.

In list context, the requested fields of the first matching row are returned. In scalar context, only the first field is returned.

SUPPORT METHODS

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( value )

Quote a string for passing to mysql. Any embedded quotes are suitably escaped as well.

root()

Return the SQL name of the root table.

table( name )

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.

sql_describe( table [, column] )

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'.

field: The field name.
type: The data format e.g. 'int(11)', 'date' or 'varchar(255)'.
null: 'yes' if the field can be null.
key: Whether the field is a key field, and whether primary or secondary.
default: The default value, if any.
extra: Other stuff? I haven't come across a use for this.

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)'.

sql_eval( command [, values...] )

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 );

sql_exists( table )

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.

sql_names( [table [, prefix, level] )

Output the mapping of Namespace names and their corresponding SQL table names.

table: An optional mysql table name to start with. If omitted, the whole tree is assumed.
prefix: Used in recursive calls for building names.
level: Used in recursive calls for indenting output.

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";

sql_replace( table, data... )

table

The SQL table name, case sensitive. Note that this is not the user name for the table.

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.

sql_select( query [, values...] )

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.

sql_show( query [, values...] )

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.

BUGS

Indexes are not removed when they become empty.

AUTHOR

Chris Willmot, chris@willmot.org.uk

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

ABSTRACT

SYNOPSIS

DESCRIPTION

CONSTRUCTOR

new( [options] )

MAIN METHODS

create( name, description )

delete( table [, where [, values...] )

replace( table, data... )

select( name, columns [, clause [, values...]] )

select_hash( name , clause [, values...]] )

select_one( name, column, clause [, values...]] )

SUPPORT METHODS

dbh()

disconnect()

quote( value )

root()

table( name )

sql_describe( table [, column] )

sql_eval( command [, values...] )

sql_exists( table )

sql_names( [table [, prefix, level] )

sql_replace( table, data... )

sql_select( query [, values...] )

sql_show( query [, values...] )

BUGS

AUTHOR

SEE ALSO

Module Install Instructions