/ 
Database-Abstraction-0.36
⭐ Starred 0 / README.md

NAME

Database::Abstraction - Read-only Database Abstraction Layer (ORM)

VERSION

Version 0.36

DESCRIPTION

Database::Abstraction is a read-only ORM for Perl that gives a uniform interface over CSV, PSV, XML, SQLite, and BerkeleyDB files — without writing any SQL.

Key features:

SYNOPSIS

# 1. Create a thin subclass for your table (e.g. Database/Foo.pm)
package Database::Foo;
use parent 'Database::Abstraction';

# 2. Open the database — file is auto-detected from the class name
#    (looks for foo.sql / foo.psv / foo.csv / foo.xml / foo.db)
my $db = Database::Foo->new(directory => '/path/to/data');

# 3. Simple lookups -----------------------------------------------

# Fetch one row
my $row = $db->fetchrow_hashref(entry => 'key1');

# Fetch all rows matching a criterion
my $rows = $db->selectall_arrayref(status => 'active');

# Column shortcut via AUTOLOAD
my $name = $db->name(entry => 'key1');

# 4. Rich criteria ------------------------------------------------

# Comparison operators
my $high = $db->selectall_arrayref(score => { '>' => 90 });

# Set membership
my $selected = $db->selectall_arrayref(
    name => { -in => ['Alice', 'Bob'] }
);

# Range
my $mid = $db->selectall_arrayref(
    score => { -between => [60, 80] }
);

# OR grouping
my $either = $db->selectall_arrayref(
    -or => [
        { status => 'active'    },
        { score  => { '>' => 95 } },
    ]
);

# 5. Joins --------------------------------------------------------

my $joined = $db->selectall_arrayref(
    join => { table => 'dept', on => 'foo.dept_id = dept.id', type => 'LEFT' }
);

# 6. Chained query builder ----------------------------------------

my $results = $db->query
    ->where(status => 'active')
    ->where(score  => { '>=' => 80 })
    ->order_by('score DESC')
    ->limit(10)
    ->all();

my $first = $db->query->where(name => 'Alice')->first();
my $count = $db->query->where(status => 'active')->count();

# 7. Connect via DSN (PostgreSQL, MySQL, SQLite, …) ---------------

my $db2 = Database::Foo->new(
    dsn      => 'dbi:Pg:dbname=mydb;host=db.example.com',
    username => 'myuser',
    password => 's3cret',
);

# 8. Schema introspection -----------------------------------------

my $cols   = $db->columns();  # ['entry', 'name', 'score', …]
my $schema = $db->schema();   # { name => { type=>'TEXT', nullable=>1, … }, … }

QUICK START EXAMPLE

If /var/dat/foo.csv contains:

"customer_id","name"
"plugh","John"
"xyzzy","Jane"

Create a driver in .../Database/foo.pm:

package Database::foo;
use parent 'Database::Abstraction';

# Regular CSV: no entry column, comma-separated
sub new {
    my ($class, %args) = @_;
    return $class->SUPER::new(no_entry => 1, sep_char => ',', %args);
}

Then query it:

my $foo = Database::foo->new(directory => '/var/dat');

# Prints "John"
print 'Customer: ', $foo->name(customer_id => 'plugh'), "\n";

# Returns { customer_id => 'xyzzy', name => 'Jane' }
my $row = $foo->fetchrow_hashref(customer_id => 'xyzzy');

FILE FORMATS

The module probes the directory for files in this priority order:

Pass dsn to bypass file detection entirely and connect via any DBI driver.

QUERY CRITERIA

All select methods (selectall_arrayref, selectall_array, fetchrow_hashref, count) accept the same criteria syntax.

Plain value

status => 'active'          # status = 'active'
name   => undef             # name IS NULL

Values containing % or _ are matched with LIKE:

name => 'A%'                # name LIKE 'A%'

Comparison operator hashref

score => { '>'  => 90  }   # score > 90
score => { '<'  => 50  }   # score < 50
score => { '>=' => 80  }   # score >= 80
score => { '<=' => 100 }   # score <= 100
score => { '!=' => 0   }   # score != 0

Multiple operators on one column are ANDed:

score => { '>' => 60, '<' => 90 }   # 60 < score < 90

Pattern matching

name => { -like     => 'A%'  }   # name LIKE 'A%'
name => { -not_like => 'Z%'  }   # name NOT LIKE 'Z%'

Set membership

name => { -in     => ['Alice', 'Bob'] }   # name IN (…)
name => { -not_in => ['Alice', 'Bob'] }   # name NOT IN (…)

Range

score => { -between => [60, 90] }   # score BETWEEN 60 AND 90

Logical groupings

-or and -and take an arrayref of condition hashrefs:

-or => [
    { status => 'active'        },
    { score  => { '>' => 95 }   },
]

-and => [
    { status => 'active'        },
    { score  => { '>=' => 80 }  },
]

Joins

Any select method accepts a join key with a hashref (or arrayref of hashrefs) describing the join:

join => {
    table => 'dept',
    on    => 'employees.dept_id = dept.id',
    type  => 'LEFT',    # INNER (default) | LEFT | RIGHT | FULL | CROSS
}

# Multiple joins
join => [
    { table => 'dept',    on => 'e.dept_id   = dept.id'   },
    { table => 'country', on => 'e.country_id = country.id' },
]

SUBROUTINES/METHODS

init

Initializes the abstraction class and its subclasses with optional arguments for configuration.

Database::Abstraction::init(directory => '../data');

See the documentation for new to see what variables can be set.

Returns a reference to a hash of the current values. Therefore when given with no arguments you can get the current default values:

my $defaults = Database::Abstraction::init();
print $defaults->{'directory'}, "\n";

import

The module can be initialised by the use directive.

use Database::Abstraction 'directory' => '/etc/data';

or

use Database::Abstraction { 'directory' => '/etc/data' };

new

Create an object pointing to a read-only database.

Accepts arguments as a hash, a hashref, or — as a shortcut — a single bare string which is taken to be directory.

Connection parameters

Behaviour parameters

Caching and logging

Notes

set_logger

Sets the class, code reference, or file that will be used for logging.

selectall_arrayref

Returns a reference to an array of hash references for every row that matches the given criteria, or undef when there are no matches.

my $rows = $db->selectall_arrayref();                    # all rows
my $rows = $db->selectall_arrayref(status => 'active');  # exact match
my $rows = $db->selectall_arrayref(score => { '>' => 8 });  # operator

The full criteria syntax is described in "QUERY CRITERIA".

Pass a join key to combine with another table:

my $rows = $db->selectall_arrayref(
    dept_name => 'Engineering',
    join      => { table => 'dept', on => 'e.dept_id = dept.id' },
);

Results are returned in the cache (if configured) and the returned array reference is made read-only unless no_fixate was set.

Note: because this returns an array reference, no LIMIT is applied. Use "selectall_array" in scalar context, or "query" with ->limit(), when you want LIMIT 1.

PSEUDOCODE

1. Parse criteria; extract and build any JOIN clause.
2. If data is slurped AND no joins AND criteria are simple:
   a. No criteria → return all rows as arrayref.
   b. entry-only lookup → return [$data{entry}].
   c. Otherwise → scan rows in-memory with _match_criterion.
3. Otherwise build SQL: SELECT * FROM table [JOIN] [WHERE] ORDER BY id.
4. Check cache; return cached arrayref on HIT.
5. prepare_cached + execute; fetch all rows.
6. Store result in cache; fixate the array; return arrayref.

selectall_hashref

Deprecated alias for "selectall_arrayref". Use selectall_arrayref in new code.

selectall_array

Similar to "selectall_arrayref" but returns a list of hash references rather than a reference to an array.

my @rows = $db->selectall_array(status => 'active');

In scalar context it applies LIMIT 1 and returns just the first matching hash reference — making it more efficient than selectall_arrayref when you only need one row. In list context all matching rows are returned.

Accepts the same criteria and join parameter as "selectall_arrayref".

selectall_hash

Deprecated alias for "selectall_array". Use selectall_array in new code.

count

Returns the number of rows matching the given criteria.

my $total  = $db->count();
my $active = $db->count(status => 'active');
my $high   = $db->count(score  => { '>' => 90 });

Accepts the full criteria syntax described in "QUERY CRITERIA".

fetchrow_hashref

Returns a hash reference for the first row matching the given criteria, or undef when there is no match. Always applies LIMIT 1.

my $row = $db->fetchrow_hashref(entry => 'key1');
my $row = $db->fetchrow_hashref(score => { '>=' => 10 });

When no_entry is not set you may pass a single bare value and it is used as the entry key:

my $row = $db->fetchrow_hashref('key1');    # same as entry => 'key1'

Accepts the full criteria syntax described in "QUERY CRITERIA", including the join parameter:

my $row = $db->fetchrow_hashref(
    name => 'Alice',
    join => { table => 'dept', on => 'e.dept_id = dept.id' },
);

Pass table => $other_table to query a table other than the one derived from the class name.

execute

Execute a raw SQL query on the underlying database.

# Scalar context: returns the first row as a hashref
my $row = $db->execute(query => 'SELECT * FROM foo WHERE id = 1');

# List context: returns all rows as a list of hashrefs
my @rows = $db->execute(query => 'SELECT * FROM foo WHERE score > ?',
                        args  => [80]);

The FROM <table> clause is appended automatically if omitted.

On CSV tables without no_entry it may help to add WHERE entry IS NOT NULL AND entry NOT LIKE '#%' to filter comment rows.

If the data have been slurped into memory this method still hits the actual database file directly.

args is an arrayref of bind values (see "execute" in DBI).

updated

Returns the Unix timestamp of the last database update (mtime for file-based backends, or the time of the most recent new() call for DSN-based connections).

columns

Returns an array reference of column names for the current table.

my $cols = $db->columns();    # e.g. ['entry', 'name', 'score', 'status']

The column list is determined by the backend:

The result is cached inside the object after the first call.

schema

Returns a hash reference describing the schema of the current table. Each key is a column name; each value is a hash reference with these keys:

The schema is determined by the backend:

The result is cached inside the object after the first call.

query

Returns a new Database::Abstraction::Query builder object bound to this database instance, for fluent method-chaining queries.

# All active rows with high scores, newest first, max 10
my $rows = $db->query
    ->where(status => 'active')
    ->where(score  => { '>' => 80 })
    ->order_by('score DESC')
    ->limit(10)
    ->all();

# Single row
my $row = $db->query->where(name => 'Alice')->first();

# Just a count
my $n = $db->query->where(status => 'active')->count();

See Database::Abstraction::Query for the full API.

AUTOLOAD — column shortcut

Calling an unknown method whose name matches a column name performs a column lookup. The method name is the column you want; the arguments are criteria.

# Scalar context: return the first match
my $name = $db->name(entry => 'key1');

# List context: return all matching values
my @names = $db->name();

# Shortcut when the table has an 'entry' key column
my $name = $db->name('key1');    # same as name(entry => 'key1')

# Unique/distinct values
my @statuses = $db->status(distinct => 1);

In list context the full column is returned (all rows), ordered by the column value. In scalar context only the first match is returned (LIMIT 1).

Results come from the slurp cache when available.

Throws an error if the column does not exist (slurp mode) or if AUTOLOAD has been disabled with auto_load => 0.

PSEUDOCODE

1. Extract column name from $AUTOLOAD; guard on DESTROY.
2. Croak if auto_load => 0.
3. Validate $column against /^[a-zA-Z_][a-zA-Z0-9_]*$/.
4. If data is slurped:
   a. List context, no params → map column over all rows (exists guard).
   b. entry-only param → direct hash lookup (exists guard).
   c. No params, scalar → first value in hash.
   d. no_entry set → scan array for matching key/value pair.
   e. Other params → scan keyed hash for matching column.
5. If not slurped, build SQL:
   - List:   SELECT column FROM table [WHERE ...] ORDER BY column
   - Scalar: SELECT DISTINCT column FROM table [WHERE ...] LIMIT 1
6. Check cache; return on HIT.
7. prepare_cached + execute; fetch result.
8. Store in cache; fixate; return.

AUTHOR

Nigel Horne, <njh at nigelhorne.com>

SUPPORT

This module is provided as-is without any warranty.

Please report any bugs or feature requests to bug-database-abstraction at rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=Database-Abstraction. I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.

MESSAGES

The table below lists every error that the module can croak or carp, what triggers it, and how to resolve it.

KNOWN LIMITATIONS

SEE ALSO

LICENSE AND COPYRIGHT

Copyright 2015-2026 Nigel Horne.

Usage is subject to the GPL2 licence terms. If you use it, please let me know.