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

Name

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

Version

Version 0.46

Description

Database::Abstraction is a read-only ORM for Perl that gives a uniform interface over CSV, PSV, TSV, JSON, XML, SQLite, DBM::Deep, BerkeleyDB, and Excel (XLS/XLSX) files - local, remote (via SSH), or fetched from a URL - without writing any SQL. Effectively it allows you to access a database table, of many different database formats, as an object.

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.sqlite / foo.sqlite3 / foo.psv / foo.tsv / foo.csv / foo.xlsx / foo.xml / foo.json / 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. Pass url to fetch and slurp data from a remote source without a local directory. When the URL returns Content-Type: application/json or the URL path ends in .json, the response is parsed as JSON (see item 8 above). Otherwise the response is parsed as an HTML page (item 10).

Example - fetching CPAN Testers results:

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

my $db = Database::cpantesters->new(
    url      => 'https://www.cpantesters.org/show/Database-Abstraction.json',
    no_entry => 1,
);
my $passes = $db->selectall_arrayref(grade => 'PASS');

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

Set class-level defaults shared by all instances.

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

Accepts the same parameters as "new". Returns a reference to the current defaults hash, so you can read them back:

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' },
);

Pass limit => N and/or offset => M for pagination:

my $page = $db->selectall_arrayref(status => 'active', limit => 10, offset => 20);

Both values must be non-negative integers; invalid values are ignored with a carp warning. When offset is given without limit the SQL backend uses LIMIT -1 on SQLite (meaning "no upper bound") so the OFFSET clause is legal.

Pass sort_by => 'col' (ascending) or sort_by => ['col', 'DESC'] to request a specific sort column and direction instead of the default primary-key ordering:

my $rows = $db->selectall_arrayref(sort_by => 'name');
my $rows = $db->selectall_arrayref(sort_by => ['score', 'DESC']);

The column name is validated against the same identifier rules as all other column parameters. An unsafe name or an unrecognised direction (anything other than ASC or DESC, case-insensitive) is ignored with a carp warning and the default sort order is used instead. Sorting is applied before limit/offset pagination.

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

Note: this always returns all matching rows. Use "selectall_array" in scalar context, or $db->query->limit(1)->all(), to fetch just one row.

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.
   In all slurp cases: sort by sort_by column (if given), then apply offset/limit.
3. Otherwise build SQL: SELECT * FROM table [JOIN] [WHERE]
   ORDER BY sort_by [else id] [LIMIT] [OFFSET].
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.

Each_Row

$db->each_row(\&callback);
$db->each_row(\&callback, status => 'active');
$db->each_row(\&callback, sort_by => 'name', limit => 100, offset => 20);

Iterates over matching rows one at a time, calling \&callback once per row with the row hashref as the sole argument. Uses constant memory on the SQL path: rows are fetched from the database one at a time via fetchrow_hashref without materialising the full result array. On the slurp/in-memory path data is already in RAM, so memory use is equivalent to "selectall_arrayref".

Accepts the same criteria, join, sort_by, limit, and offset parameters as "selectall_arrayref".

Returns the number of rows passed to \&callback.

Exceptions raised inside \&callback abort iteration and propagate to the caller; the DBI statement handle is left in a valid state (finish() is called on exception).

Note: rows on the SQL path are not fixated (made read-only) because they are discarded after each callback invocation. Slurp-path rows are already fixated from the initial load.

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, join, limit, offset, and sort_by parameters as "selectall_arrayref". When limit is given in scalar context it overrides the implicit LIMIT 1.

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.

For file-based backends (CSV, XML, SQLite via directory), this is the mtime of the backing file, set at new() time.

For SQLite DSN connections (dbi:SQLite:dbname=...), the file path is extracted from the DSN and stat()-ed live on every call, so callers get a current mtime suitable for cache-invalidation even when the database was opened via a DSN rather than a directory.

For all other DSN-based connections (PostgreSQL, MySQL, etc.) and for URL-based backends, returns the Unix timestamp of the most recent new() call (connection time).

Columns

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

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

Column names are always returned in alphabetical (lexicographic) order, regardless of backend. This makes the result stable and portable when the same logical table is backed by different engines (CSV => SQLite, etc.).

The source of column names varies by 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:

my $schema = $db->schema();

for my $col (sort keys %{$schema}) {
    my $info = $schema->{$col};
    printf "%s  %s  %s\n",
        $col,
        $info->{type},
        $info->{nullable} ? 'NULL' : 'NOT NULL';
}

The schema is determined by the backend:

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

Dbi_Source

Returns a hashref { dbh => $dbh, table => $name } when the backend is a live SQLite connection, or undef for every other backend (slurp-mode CSV, JSON, XLSX, HTML URL, DBM::Deep, BerkeleyDB, PostgreSQL, MySQL, ...).

The hashref is consumed by Database::Join to perform a zero-copy ATTACH DATABASE so rows never pass through Perl. Nested Database::Join objects that are themselves SQLite-backed expose themselves as attachable sources to parent joins through the same interface.

Subclasses may override this method to expose non-SQLite DBI connections if their join layer supports them.

Api Specification

Arguments

None beyond the implicit invocant.

Returns

A hashref { dbh => DBI::db, table => Str } on a SQLite-backed instance, or undef on all other backends.

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

Copyright 2015-2026 Nigel Horne.

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