Creating DICTIONARY objects

DICTIONARY objects are the foundation of the LedgyX system. They represent structured data reference tables with typed fields and a relationship system.

Basic syntax

CREATE DICTIONARY IF NOT EXISTS table_name (
    field_name TYPE field [CONSTRAINTS],
    ...
);

Key features:

The field keyword is required after the data type
Support for relationships with other DICTIONARY objects
Automatic system fields (REF, ID, PARENT_REF, OWNER_REF, STATE)

Typed fields

Basic field types

CREATE DICTIONARY IF NOT EXISTS users (
    -- Text fields with length constraints
    name TEXT(100) field NOT NULL,
    email TEXT(255) field UNIQUE,
    phone TEXT(20) field,
    
    -- Numeric fields
    age INTEGER field DEFAULT 0,
    balance DECIMAL(15,2) field DEFAULT 0.00,
    score NUMERIC field DEFAULT 0,
    
    -- Date/time fields  
    created_at TIMESTAMP field NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP field DEFAULT NOW(),
    birth_date DATE field,
    
    -- JSON data
    metadata JSON field,
    settings JSON field DEFAULT '{}',
    
    -- Boolean fields
    is_active BOOLEAN field NOT NULL DEFAULT true,
    is_verified BOOLEAN field DEFAULT false
);

Special field types

CREATE DICTIONARY IF NOT EXISTS products (
    -- The required system fields can be overridden
    ref UUID field DEFAULT UUID() NOT NULL,
    
    -- Main fields
    name TEXT(200) field NOT NULL,
    description TEXT field,
    price DECIMAL(10,2) field NOT NULL,
    
    -- State fields
    state NUMERIC field NOT NULL DEFAULT 0,
    status TEXT(20) field DEFAULT 'draft',
    
    -- Specialized types
    barcode TEXT field,
    weight DECIMAL(8,3) field,
    dimensions JSON field,
    
    PRIMARY KEY (ref)
);

Relationships between DICTIONARY objects

Basic relationship syntax

-- Relationship with another DICTIONARY
field_name DICTIONARY.table_name field [CONSTRAINTS]

Relationship examples

CREATE DICTIONARY IF NOT EXISTS proxy_regions (
    name TEXT(40) field NOT NULL
);
 
CREATE DICTIONARY IF NOT EXISTS acc_project (
    name TEXT(60) field NOT NULL,
    -- Relationship with another DICTIONARY
    region DICTIONARY.proxy_regions field NOT NULL
);
 
CREATE DICTIONARY IF NOT EXISTS accounts (
    -- JSON fields
    acct JSON field NOT NULL,
    token JSON field NOT NULL,
    
    -- Date/time fields with default values
    created TIMESTAMP field NOT NULL DEFAULT NOW(),
    next_send TIMESTAMP field NOT NULL DEFAULT NOW(),
    blocked_to TIMESTAMP field NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMP field,
    updated TIMESTAMP field,
    
    -- Numeric fields with default values
    state NUMERIC field NOT NULL DEFAULT 0,
    block_counter NUMERIC field NOT NULL DEFAULT 0,
    send_counter NUMERIC field NOT NULL DEFAULT 0,
    send_total NUMERIC field NOT NULL DEFAULT 0,
    type NUMERIC field NOT NULL DEFAULT 0,
    
    -- Relationships with other DICTIONARY objects
    proxy DICTIONARY.proxy field,
    acc_project DICTIONARY.acc_project field,
    
    -- Plain text field
    email TEXT field
);

Complex relationships

CREATE DICTIONARY IF NOT EXISTS mail_sender_name (
    -- Relationship with projects
    project DICTIONARY.projects field NOT NULL,
    name TEXT(60) field NOT NULL,
    created TIMESTAMP field NOT NULL DEFAULT NOW()
);
 
CREATE DICTIONARY IF NOT EXISTS attachments (
    name TEXT(60) field NOT NULL,
    created TIMESTAMP field NOT NULL DEFAULT NOW(),
    
    -- Relationship with projects via ref
    project_ref DICTIONARY.projects field NOT NULL,
    
    send_counter NUMERIC field NOT NULL DEFAULT 0,
    state NUMERIC(2) field NOT NULL DEFAULT 0,
    updated TIMESTAMP field NOT NULL DEFAULT NOW()
);
 
CREATE DICTIONARY IF NOT EXISTS drive_links (
    -- Relationship with accounts (typo in the original - accaounts)
    account DICTIONARY.accounts field NOT NULL,
    created TIMESTAMP field NOT NULL DEFAULT NOW(),
    link TEXT field NOT NULL,
    file_name TEXT field NOT NULL,
    sent_count NUMERIC field NOT NULL DEFAULT 0
);

Constraints and indexes

Primary keys

CREATE DICTIONARY IF NOT EXISTS categories (
    ref UUID field DEFAULT UUID() NOT NULL,
    name TEXT(100) field UNIQUE NOT NULL,
    parent_ref UUID field,
    created_at TIMESTAMP field DEFAULT NOW(),
    
    -- Primary key on ref
    PRIMARY KEY (ref)
);

Unique constraints

CREATE DICTIONARY IF NOT EXISTS unique_codes (
    id INTEGER field UNIQUE NOT NULL,
    code TEXT(50) field UNIQUE NOT NULL,
    value JSON field,
    created_at TIMESTAMP field DEFAULT NOW()
);

Foreign keys via DICTIONARY relationships

CREATE DICTIONARY IF NOT EXISTS order_items (
    ref UUID field DEFAULT UUID() NOT NULL,
    
    -- Relationships with other objects
    order_ref DICTIONARY.orders field NOT NULL,
    product_ref DICTIONARY.products field NOT NULL,
    
    -- Order item data
    quantity INTEGER field NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) field NOT NULL,
    total_price DECIMAL(10,2) field NOT NULL,
    
    created_at TIMESTAMP field DEFAULT NOW(),
    PRIMARY KEY (ref)
);

Default values

System functions

CREATE DICTIONARY IF NOT EXISTS audit_log (
    ref UUID field DEFAULT UUID() NOT NULL,
    
    -- Automatic timestamps
    created_at TIMESTAMP field NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP field DEFAULT NOW(),
    
    -- System information
    version_info TEXT field DEFAULT VERSION(),
    
    -- Log data
    action TEXT(100) field NOT NULL,
    data JSON field,
    
    -- User
    user_ref DICTIONARY.users field,
    
    PRIMARY KEY (ref)
);

Numeric default values

CREATE DICTIONARY IF NOT EXISTS counters (
    ref UUID field DEFAULT UUID() NOT NULL,
    name TEXT(100) field NOT NULL,
    
    -- Various counter types with initial values
    int_counter INTEGER field NOT NULL DEFAULT 0,
    big_counter BIGINT field NOT NULL DEFAULT 0,
    small_counter SMALLINT field NOT NULL DEFAULT 0,
    decimal_counter DECIMAL(15,3) field NOT NULL DEFAULT 0.000,
    
    -- Settings
    increment_step NUMERIC field NOT NULL DEFAULT 1,
    max_value NUMERIC field,
    
    PRIMARY KEY (ref)
);

Complex DICTIONARY objects

Multi-level reference tables

-- Regions
CREATE DICTIONARY IF NOT EXISTS regions (
    name TEXT(100) field NOT NULL,
    code TEXT(10) field UNIQUE NOT NULL,
    country_code TEXT(2) field DEFAULT 'RU'
);
 
-- Projects linked to regions
CREATE DICTIONARY IF NOT EXISTS projects (
    name TEXT(200) field NOT NULL,
    created TIMESTAMP field NOT NULL DEFAULT NOW(),
    state NUMERIC field NOT NULL DEFAULT 0,
    region DICTIONARY.regions field NOT NULL
);
 
-- Project configurations
CREATE DICTIONARY IF NOT EXISTS config (
    name TEXT(60) field NOT NULL,
    params JSON field NOT NULL,
    total_sent BIGINT field NOT NULL DEFAULT 0,
    created TIMESTAMP field NULL DEFAULT NOW(),
    updated TIMESTAMP field NOT NULL DEFAULT NOW(),
    
    -- Relationship with a project
    acc_project DICTIONARY.projects field
);

Reference tables for messengers

CREATE DICTIONARY IF NOT EXISTS telegram_messages (
    -- Message IDs
    message_id INTEGER field NOT NULL,
    update_id INTEGER field NOT NULL,
    
    -- Chat information
    chat_id BIGINT field NOT NULL,
    chat_type TEXT(20) field,
    chat_username TEXT(100) field,
    chat_first_name TEXT(100) field,
    
    -- Timestamp
    msg_date INTEGER field NOT NULL,
    
    -- Sender information
    from_id BIGINT field NOT NULL,
    is_bot TEXT(5) field DEFAULT 'false',
    from_username TEXT(100) field,
    is_premium TEXT(5) field DEFAULT 'false',
    language_code TEXT(10) field,
    
    -- Message content
    msg_text TEXT field,
    
    -- System fields
    created_at TIMESTAMP field NOT NULL DEFAULT NOW()
);

Best practices

1. Field naming

-- ✅ Good: clear names
CREATE DICTIONARY IF NOT EXISTS users (
    full_name TEXT(200) field NOT NULL,
    email_address TEXT(255) field UNIQUE,
    phone_number TEXT(20) field,
    is_email_verified BOOLEAN field DEFAULT false
);
 
-- ❌ Bad: cryptic abbreviations  
CREATE DICTIONARY IF NOT EXISTS users (
    fn TEXT(200) field NOT NULL,
    eml TEXT(255) field UNIQUE,
    ph TEXT(20) field,
    ev BOOLEAN field DEFAULT false
);

2. Field typing

-- ✅ Good: correct types and sizes
CREATE DICTIONARY IF NOT EXISTS products (
    name TEXT(200) field NOT NULL,          -- Sufficient length
    price DECIMAL(10,2) field NOT NULL,     -- Precision for currency
    weight DECIMAL(8,3) field,              -- Precision for weight
    is_available BOOLEAN field DEFAULT true, -- Boolean type
    metadata JSON field                      -- JSON for flexible data
);

3. Relationships and constraints

-- ✅ Good: explicit relationships and constraints
CREATE DICTIONARY IF NOT EXISTS order_items (
    ref UUID field DEFAULT UUID() NOT NULL,
    
    -- Required relationships
    order_ref DICTIONARY.orders field NOT NULL,
    product_ref DICTIONARY.products field NOT NULL,
    
    -- Validated constraints
    quantity INTEGER field NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) field NOT NULL CHECK (unit_price >= 0),
    
    created_at TIMESTAMP field DEFAULT NOW(),
    PRIMARY KEY (ref)
);

Related sections:

System functions - ref(), parent_ref(), NULLREF()
Object types - Overview of all object types
INSERT operations - Inserting data into DICTIONARY

System Functions & Objects Nested Objects & Parameters