/ 
Yote-SQLObjectStore-0.05
River stage zero No dependents
/ Yote::SQLObjectStore::BaseObj

COLUMN TYPES

Object fields are declared via the package-level %cols hash, mapping column names to type definitions.

package MyApp::User;
use base 'Yote::SQLObjectStore::SQLite::Obj';

our %cols = (
    name    => 'VARCHAR(100)',
    bio     => 'TEXT',
    age     => 'INTEGER',
    score   => 'DECIMAL(5,2)',
    active  => 'BOOLEAN',
    avatar  => '*MyApp::Image',
    tags    => '*ARRAY_VARCHAR(50)',
    friends => '*ARRAY_*MyApp::User',
    prefs   => '*HASH<256>_TEXT',
);

There are two categories of column types: scalar types for storing values directly in the object's table, and reference types for storing references to other objects or to collection objects (arrays and hashes).

Scalar Types

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

Text

TEXT            Variable-length text, no length limit
VARCHAR(N)      Variable-length text, max N characters

TEXT is the general-purpose string type. Use VARCHAR(N) when you want to express a maximum length. MariaDB and PostgreSQL enforce the limit; SQLite preserves the declaration but treats it as TEXT affinity.

Numeric

INTEGER         Standard integer (4 bytes on most backends)
BIGINT          Large integer (8 bytes)
SMALLINT        Small integer (2 bytes)
FLOAT           Single-precision floating point
DOUBLE          Double-precision floating point
DECIMAL(M,D)    Exact decimal with M total digits, D after the decimal point

SQLite maps all integer types to INTEGER and all floating-point types to REAL. MariaDB maps INTEGER to INT. PostgreSQL maps FLOAT to REAL and DOUBLE to DOUBLE PRECISION.

Other

BOOLEAN         True/false value
BLOB            Binary data
TIMESTAMP       Date and time
DATE            Date only

SQLite maps BOOLEAN to INTEGER and TIMESTAMP/DATE to TEXT. MariaDB maps BOOLEAN to TINYINT(1). PostgreSQL maps BLOB to BYTEA.

Backend Type Mapping Summary

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 start with * and store object IDs internally as BIGINT columns. The referenced objects live in their own tables.

Object References

*                   Reference to any Yote object
*Package::Name      Reference to a specific object class

A bare * allows any object type. A typed reference like *MyApp::User enforces that only objects of that class (or its subclasses) may be stored.

our %cols = (
    owner   => '*MyApp::User',      # must be a User
    misc    => '*',                  # any object
);

Arrays

*ARRAY_<type>       Array (ordered list) of values or references

The <type> after ARRAY_ specifies what the array holds. It can be a scalar type, * (any reference), or *Package::Name (typed reference).

our %cols = (
    scores   => '*ARRAY_INTEGER',               # array of integers
    tags     => '*ARRAY_VARCHAR(50)',             # array of short strings
    items    => '*ARRAY_*MyApp::Item',            # array of typed objects
    related  => '*ARRAY_*',                       # array of any objects
    grid     => '*ARRAY_*ARRAY_INTEGER',          # nested: array of arrays
);

Arrays are represented as tied Perl arrayrefs. They support standard array operations (push, pop, shift, unshift, splice, indexing). Each array is stored in a separate table keyed by the owning object's ID and the element index.

Hashes

*HASH<N>_<type>     Hash (key-value map) with max key size N

N is the maximum length of hash keys in characters. The <type> after the _ specifies what the hash values hold, same as for arrays.

our %cols = (
    settings  => '*HASH<256>_TEXT',                # string values
    counts    => '*HASH<128>_INTEGER',              # integer values
    children  => '*HASH<256>_*MyApp::Item',         # typed object values
    lookup    => '*HASH<256>_*',                    # any object values
);

Hashes are represented as tied Perl hashrefs. They support standard hash operations (assignment, delete, keys, values, exists). Each hash is stored in a separate table keyed by the owning object's ID and the hash key.

find_where( $store, %criteria )

Class method to find objects matching all criteria.

my @admins = MyApp::User->find_where($store,
    status => 'active',
    role   => 'admin'
);