NAME

Database::Join::Catalog - List a database's tables and columns, and write a table as SQLite

VERSION

0.011.3

SYNOPSIS

use Database::Join::Catalog;

my $names = Database::Join::Catalog->tables(file => 'store.db');   # [ 'customers', 'orders' ]
my $all   = Database::Join::Catalog->schema(file => 'store.db');
# [ { name => 'customers', columns => [ 'entry', 'name' ] }, ... ]

my $info = Database::Join::Catalog->table_info(
    dsn => 'dbi:MariaDB:database=shop;host=db', username => 'ro', password => '...',
    table => 'orders',
);
# { columns => [ 'entry', 'item', 'qty' ], primary_key => [ 'entry' ] }

my $bytes = Database::Join::Catalog->sqlite_bytes(
    table => 'data', columns => [ 'id', 'name' ], rows => [ { id => 1, name => 'a' } ],
);

my $driver = Database::Join::Catalog->available_driver('MariaDB', 'mysql');   # or undef

DESCRIPTION

Database::Abstraction opens a table it is told the name of, and Database::Join reads across such tables. Neither says which tables a database holds, what a table's columns are in their declared order, or what its key is, and neither writes a file. This module does those, so that an application reads and describes its data through this distribution and needs no database driver of its own.

Every method is a class method. A database is named either by file, a SQLite file, which is opened read-only (so a name that is not there is not made into an empty database), or by dsn with optional username and password. Listing tables works for SQLite, MariaDB and MySQL.

A database that cannot be reached, or a query it refuses, croaks with a "Database::Join::Catalog::Error", which says which of the two it was. Arguments that are wrong croak with a plain message.

METHODS

tables

my $names = Database::Join::Catalog->tables(file => $path);

API SPECIFICATION

INPUT

file:     string, a SQLite file; or
dsn:      string, a DBI data source (SQLite, MariaDB, MySQL)
username: string, optional (dsn only)
password: string, optional (dsn only)

OUTPUT

An arrayref of the table names, sorted by name. For SQLite, the user tables (not sqlite_*); for MariaDB and MySQL, those of the connection's database. A file that is not a SQLite database croaks at stage query; one that is not there, at stage connect.

schema

my $tables = Database::Join::Catalog->schema(dsn => $dsn, username => $u, password => $p);

API SPECIFICATION

INPUT

As "tables".

OUTPUT

An arrayref of { name => $table, columns => [ $column, ... ] }, sorted by table name, each table's columns in their declared order. Croaks as "tables", and with error_driver_unsupported for another driver.

table_info

my $info = Database::Join::Catalog->table_info(dsn => $dsn, table => 'orders');

API SPECIFICATION

INPUT

file or dsn, username, password: as tables
table:    string, required

OUTPUT

{ columns => [ ... ], primary_key => [ ... ] }: the columns in the table's declared order (Database::Abstraction's columns() is alphabetical), and the primary key's columns, empty when it has none or the driver does not say. A database that cannot be reached croaks at stage connect; a table it does not have, at stage query.

write_sqlite

Database::Join::Catalog->write_sqlite(file => 'out.db', table => 'data',
    columns => [ 'id', 'name' ], rows => \@rows);

API SPECIFICATION

INPUT

file:    string, required; a name that does not exist yet
table:   string, optional (default 'data')
columns: arrayref of strings, required, at least one
rows:    arrayref of hashrefs, optional; each row's values by column name

OUTPUT

1. Makes a SQLite file with one table whose columns are TEXT, in the order given, and one row for each element of rows (a missing value is NULL). Names are quoted, so any column name is safe. The rows go in one transaction. Croaks with error_file_exists rather than replace a file, and leaves no file behind when the write fails.

sqlite_bytes

my $bytes = Database::Join::Catalog->sqlite_bytes(columns => \@columns, rows => \@rows);

API SPECIFICATION

INPUT

As "write_sqlite", without file.

OUTPUT

The SQLite file's contents, made in a temporary file that is removed afterwards, whether or not the write succeeds.

available_driver

my $driver = Database::Join::Catalog->available_driver('MariaDB', 'mysql');

API SPECIFICATION

INPUT

@names: DBD driver names, in order of preference

OUTPUT

The first whose DBD:: module loads, or undef. A name that is not a driver name is skipped, never loaded.

Database::Join::Catalog::Error

What a failed connect or query croaks with. It reads as the driver's message ("$err"), and:

$err->stage     # 'connect' or 'query'
$err->message   # the driver's message

MESSAGES

error_source_required    -- neither file nor dsn (or write_sqlite without file)
error_source_both        -- both file and dsn
error_driver_unsupported -- schema() or tables() for a driver other than SQLite, MariaDB, MySQL
error_table_required     -- table_info() without a table, or an empty table name
error_columns_required   -- write_sqlite() or sqlite_bytes() without columns
error_row_not_hash       -- an element of rows is not a hashref
error_file_exists        -- write_sqlite() to a name that exists
error_temp_file          -- sqlite_bytes() could not make its temporary file
error_no_reason          -- the driver failed without a message (connect returned undef)

SEE ALSO

Database::Join, Database::Abstraction

AUTHOR

Nigel Horne, <njh@nigelhorne.com>

LICENSE AND COPYRIGHT

Copyright (C) 2026 Nigel Horne.

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