/ 
Yote-SQLObjectStore-0.04
/ README.md

Yote-SQLObjectStore v0.03

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:

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      => 'INTEGER',
    active   => 'BOOLEAN',
);

1;

Column Types

Scalar Types

Scalar types use ANSI SQL standard names. Each backend automatically translates these to native database types, so the same %cols definition works across SQLite, MariaDB, and PostgreSQL.

| Standard Type | Description | |---------------|-------------| | TEXT | Variable-length text, no length limit | | VARCHAR(N) | Variable-length text, max N characters | | INTEGER | Standard integer | | BIGINT | Large integer | | SMALLINT | Small integer | | FLOAT | Single-precision floating point | | DOUBLE | Double-precision floating point | | DECIMAL(M,D) | Exact decimal, M total digits, D after decimal | | BOOLEAN | True/false value | | BLOB | Binary data | | TIMESTAMP | Date and time | | DATE | Date only |

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

Backend Type Mapping

Each backend maps these standard types to native equivalents:

| Standard | SQLite | MariaDB | PostgreSQL | |----------|--------|---------|------------| | TEXT | TEXT | TEXT | TEXT | | VARCHAR(N) | VARCHAR(N) | VARCHAR(N) | VARCHAR(N) | | INTEGER | INTEGER | INT | INTEGER | | BIGINT | INTEGER | BIGINT | BIGINT | | SMALLINT | INTEGER | SMALLINT | SMALLINT | | FLOAT | REAL | FLOAT | REAL | | DOUBLE | REAL | DOUBLE | DOUBLE PRECISION | | DECIMAL(M,D) | NUMERIC | DECIMAL(M,D) | DECIMAL(M,D) | | BOOLEAN | INTEGER | TINYINT(1) | BOOLEAN | | BLOB | BLOB | BLOB | BYTEA | | TIMESTAMP | TEXT | TIMESTAMP | TIMESTAMP | | DATE | TEXT | DATE | DATE |

Reference Types

Reference types begin with * and store object IDs internally:

| Syntax | Meaning | |--------|---------| | *PackageName | Reference to object of that type | | * | Reference to any object | | *ARRAY_<type> | Array of values or references | | *HASH<N>_<type> | Hash with max key size N |

<type> can be:

Examples

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

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

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

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

Architecture

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

Storage Model

Backend Notes

SQLite

MariaDB

Postgres

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

License

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

Author

Eric Wolf