System functions and objects

-- Getting object references
SELECT p.ref() AS object_reference, p.name
FROM dictionary.page_property AS p
WHERE p.state = 10;
 
-- Using it in conditions
SELECT sm.name, sm.template, sm.settings
FROM dictionary.sitemap AS sm 
WHERE sm.ref() = &params.ref::uuid;
 
-- Comparing references in a JOIN
SELECT p.name, u.name AS user_name
FROM dictionary.page_property AS p
INNER JOIN dictionary.user_property AS u 
  ON u.ref() = p.user_ref
WHERE u.name = 'ivan';

-- Searching by parent reference
SELECT p.ref(), p.name, p.user_ref
FROM dictionary.page_property AS p
INNER JOIN dictionary.user_property AS u 
  ON u.parent_ref() = (
    SELECT p.ref() 
    FROM dictionary.parents AS p 
    WHERE p.id = 100 
    LIMIT 1
  );
 
-- Root categories (no parent)
SELECT c.ref(), c.name
FROM dictionary.categories AS c
WHERE c.parent_ref() = NULLREF()
ORDER BY c.name
TYPE LIST;
 
-- Direct children of a given category
SELECT c.ref(), c.name, c.parent_ref()
FROM dictionary.categories AS c
WHERE c.parent_ref() = &parent_ref::uuid
ORDER BY c.name
TYPE LIST;

-- Filtering by owner
SELECT n.href, n.title, n.name, n.icon
FROM dictionary.nav AS n 
WHERE n.owner_ref() = &data.owner_ref::uuid;
 
-- Checking access rights
SELECT COUNT(*) AS accessible_objects
FROM dictionary.documents AS d
WHERE d.owner_ref() = &current_user_ref::uuid
  AND d.state = 'active';

-- Using system fields
SELECT 
    REF AS object_id,
    ID AS numeric_id,
    PARENT_REF AS parent,
    OWNER_REF AS owner,
    STATE AS status,
    name,
    email
FROM dictionary.users
WHERE STATE IN ('active', 'pending')
  AND OWNER_REF = &current_user::uuid
ORDER BY ID
TYPE LIST;

-- Finding objects without a parent
SELECT ref(), name, description
FROM dictionary.categories
WHERE parent_ref() = NULLREF()
TYPE LIST;
 
-- Setting an empty reference on creation
INSERT INTO dictionary.root_categories (name, parent_ref)
VALUES (&category_name, NULLREF());

-- Generating a new reference
INSERT INTO dictionary.objects (ref, name, created_at)
VALUES (UUID(), &object_name, NOW());
 
-- Deterministic generation based on a value
INSERT INTO Contract.erc_proxy (ref, hash, dst, src, wad)
SELECT UUID(tb.hash) AS ref, tb.hash, tb.parser.dst, tb.parser.src, tb.parser.wad
FROM (
    SELECT parse(&contract_address, trn.topics, trn.data) AS parser, trn.hash
    FROM Ethereum.transactions.log(
        WHERE "to" = &target_contract 
        LIMIT 1000
    ) AS trn
) AS tb;

-- Automatic filtering by rights
SELECT ALLOWED 
    p.ref() AS ref,
    p.name AS name
FROM dictionary.page_property AS p
INNER JOIN dictionary.user_property AS u 
  ON u.ref() = p.user_ref 
  AND u.name = "ivan"
WHERE p.state = 10.12
TYPE OBJECT;
 
-- Without ALLOWED - returns all objects
SELECT 
    p.ref() AS ref,
    p.name AS name  
FROM dictionary.page_property AS p
WHERE p.state = 10.12
TYPE LIST;

-- Manual rights check
SELECT p.name, p.settings, p.template
FROM dictionary.page_property AS p
WHERE p.object = (
    SELECT s.ref()
    FROM dictionary.sitemap AS s 
    WHERE s.name = &admin::TEXT
      AND s.owner_ref() = &current_user::uuid
)
AND p.state = 10
TYPE LIST;

-- States as numbers with decimal places
SELECT name, settings, state
FROM dictionary.page_property
WHERE state = 10.12  -- Specific state
   OR state BETWEEN 10.0 AND 10.99  -- Range of states
TYPE LIST;
 
-- Grouping by state
SELECT 
    FLOOR(state) AS state_group,
    COUNT(*) AS objects_count,
    AVG(state) AS avg_state
FROM dictionary.page_property
GROUP BY FLOOR(state)
ORDER BY state_group
TYPE LIST;

-- Working with text states
SELECT ref(), name, state
FROM dictionary.users
WHERE state IN ('active', 'pending', 'suspended')
ORDER BY 
  CASE state
    WHEN 'active' THEN 1
    WHEN 'pending' THEN 2 
    WHEN 'suspended' THEN 3
    ELSE 4
  END
TYPE LIST;

-- Getting the system version
SELECT VERSION() AS system_version, NOW() AS current_time;

-- Full information about system fields
SELECT 
    'Object Info' AS title,
    REF AS object_ref,
    ID AS object_id, 
    PARENT_REF AS parent_reference,
    OWNER_REF AS owner_reference,
    STATE AS object_state,
    VERSION() AS system_version
FROM dictionary.users 
WHERE ID = &user_id
TYPE OBJECT;

-- Comprehensive access check
SELECT ALLOWED
    e.ref(),
    e.name,
    e.user,
    e.avatar,
    e.logo,
    e.heading,
    e.title,
    e.body_class,
    (SELECT &params.ref AS "data" TYPE OBJECT) AS fields
FROM dictionary.page_property AS e 
WHERE e.ref() = &settings_ref::uuid
  AND e.owner_ref() = &current_user::uuid
  AND e.state IN (10, 10.12, 20)
TYPE OBJECT;

-- Navigation with an owner check
SELECT 
    n.href AS nav_href,
    n.title AS nav_title, 
    n.name AS nav_filter_tag,
    n.icon AS nav_icon,
    n.ref() AS nav_ref
FROM dictionary.nav AS n 
WHERE n.owner_ref() = &data.owner_ref::uuid
  AND n.parent_ref() = ISNULL(&parent_ref, NULLREF())
  AND n.state = 'active'
ORDER BY n.sort_order, n.title
TYPE LIST;

-- Creating an object with automatic references
INSERT INTO dictionary.user_sessions (
    ref,
    user_ref, 
    parent_ref,
    owner_ref,
    state,
    created_at,
    expires_at
)
VALUES (
    UUID(),
    &user_ref::uuid,
    &parent_session_ref::uuid,
    &user_ref::uuid,  -- Owner = user
    'active',
    NOW(),
    NOW() + INTERVAL '24 hours'
);