Yote-SQLObjectStore

A schema-enforced object persistence layer backed by SQL databases (SQLite, MariaDB, or Postgres).

Overview

Yote-SQLObjectStore provides typed object persistence with SQL storage. Unlike the schema-less Yote-ObjectStore, SQLObjectStore enforces column types defined in Perl packages and automatically generates appropriate SQL tables.

Key features:

• Typed schemas - Object fields are defined with SQL types or reference types
• Automatic table generation - Scans packages and creates/updates SQL tables
• Runtime type checking - Validates values match declared types
• Dirty tracking - Only saves modified objects
• Multiple backends - SQLite, MariaDB, and Postgres support
• Tied collections - Transparent persistence for arrays and hashes

Installation

perl Build.PL
./Build
./Build test
./Build install

Quick Start

use Yote::SQLObjectStore;

# Connect to database
my $store = Yote::SQLObjectStore->new(
    'SQLite',  # or 'MariaDB' or 'Postgres'
    dbname   => 'myapp.db',
    root     => 'MyApp::Root',
);

# Generate tables from package definitions
$store->make_all_tables(@INC) if $store->needs_table_updates(@INC);

$store->open;

my $root = $store->get_root;

# Create and store objects
my $user = $store->new_obj('MyApp::User',
    name  => 'alice',
    email => 'alice@example.com',
);
$root->get_users->{alice} = $user;

$store->save;

Defining Object Classes

Object classes inherit from the database-specific Obj base class and declare their schema via %cols:

package MyApp::User;
use base 'Yote::SQLObjectStore::SQLite::Obj';
# or 'Yote::SQLObjectStore::MariaDB::Obj'
# or 'Yote::SQLObjectStore::Postgres::Obj'

our %cols = (
    name     => 'VARCHAR(128)',
    email    => 'VARCHAR(256)',
    age      => 'INT',
    active   => 'BOOLEAN',
);

1;

Column Types

Scalar Types

Use standard SQL column definitions:

our %cols = (
    name    => 'VARCHAR(256)',
    count   => 'INT',
    amount  => 'DECIMAL(10,2)',
    notes   => 'TEXT',
);

Reference Types

Reference types begin with *:

| Syntax | Meaning | |--------|---------| | *PackageName | Reference to object of that type | | * | Reference to any object | | *ARRAY_TYPE | Array of TYPE values | | *HASH<N>_TYPE | Hash with max key size N, TYPE values |

TYPE can be:

• A SQL type (e.g., VARCHAR(100), INT)
• A reference type (e.g., *MyApp::User, *)

Examples

our %cols = (
    # Reference to specific object type
    manager => '*MyApp::User',

    # Reference to any object
    attachment => '*',

    # Array of integers
    scores => '*ARRAY_INT',

    # Array of User objects
    friends => '*ARRAY_*MyApp::User',

    # Hash (256-char keys) of strings
    attributes => '*HASH<256>_VARCHAR(1024)',

    # Hash of User objects
    users => '*HASH<128>_*MyApp::User',

    # Hash of arrays of any reference
    groups => '*HASH<256>_*ARRAY_*',
);

Table Generation

SQLObjectStore scans @INC for packages inheriting from the Obj base class and generates appropriate SQL tables:

# Check if tables need updating
if ($store->needs_table_updates(@INC)) {
    # Preview the SQL
    my @sql = $store->make_all_tables_sql(@INC);
    print join("\n", @sql);

    # Or execute directly
    $store->make_all_tables(@INC);
}

Tables are created for:

• Each object class (one row per object instance)
• Each array type (id, idx, val columns)
• Each hash type by key size and value type (id, hashkey, val columns)

Architecture

+------------------+     +------------------------------+
| Yote::SQLObject  |---->| SQLite, MariaDB, or Postgres |
| Store            |     | Backend                      |
+------------------+     +------------------------------+
        |
        v
+------------------+
| TableManager     |  Generates/updates SQL tables
+------------------+
        |
        v
+------------------+     +------------------+
| BaseObj          |<--->| TiedHash         |
| (object fields)  |     | TiedArray        |
+------------------+     +------------------+

Storage Model

• Objects: Stored in tables named after their package (e.g., MyApp_User)
• Arrays: Stored in tables like ARRAY_INT or ARRAY_REF with (id, idx, val)
• Hashes: Stored in tables like HASH_256_VARCHAR_100 with (id, hashkey, val)

Backend Notes

SQLite

• File-based, no server needed
• Uses INSERT OR REPLACE / INSERT OR IGNORE
• Uses file-based locking for multi-process safety
• Connect with BASE_DIRECTORY

MariaDB

• Uses REPLACE / INSERT IGNORE
• Uses BIGINT UNSIGNED for reference columns
• Connect with dbname, username, password, host, port

Postgres

• Uses BIGSERIAL / SERIAL for auto-increment primary keys
• Uses BIGINT (no UNSIGNED) for reference columns
• Postgres lowercases unquoted identifiers; the adapter handles this transparently
• Connect with dbname, username, password, host, port

Transactions

$store->begin_transaction;

eval {
    # Make changes
    $user->set_name('bob');
    $store->save;
    $store->commit_transaction;
};
if ($@) {
    $store->rollback_transaction;
}

Differences from Yote-ObjectStore

| Feature | ObjectStore | SQLObjectStore | |---------|-------------|----------------| | Storage | File-based (RecordStore) | SQL database | | Schema | Schema-less | Typed via %cols | | Queries | ID lookup only | ID lookup (SQL queries possible) | | Types | Runtime flexible | Compile-time defined | | Tables | Single data store | Table per type |

Requirements

• Perl 5.16+
• DBI
• DBD::SQLite, DBD::MariaDB, and/or DBD::Pg (depending on backend)
• Set::Scalar
• File::Grep
• Module::Load::Conditional

License

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

Author

Eric Wolf