PHP code example of visorcraft / mongreldb-php

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

visorcraft / mongreldb-php example snippets

use Visorcraft\MongrelDB\Database;

// Connect to a running mongreldb-server daemon
$db = new Database('http://127.0.0.1:8453');

// Create a table with an enum column, a regex CHECK, and a server-side default
$db->getClient()->post('/kit/create_table', [
    'name' => 'orders',
    'columns' => [
        ['id' => 1, 'name' => 'id',             'ty' => 'int64',           'primary_key' => true,  'nullable' => false],
        ['id' => 2, 'name' => 'customer_email', 'ty' => 'varchar',         'primary_key' => false, 'nullable' => false],
        ['id' => 3, 'name' => 'amount',         'ty' => 'float64',         'primary_key' => false, 'nullable' => false],
        ['id' => 4, 'name' => 'status',         'ty' => 'enum',            'primary_key' => false, 'nullable' => false,
         'enum_variants' => ['new', 'paid', 'cancelled']],
        ['id' => 5, 'name' => 'created_at',     'ty' => 'timestamp_nanos', 'primary_key' => false, 'nullable' => false,
         'default_expr' => 'now'],
    ],
    'constraints' => [
        'checks' => [[
            'id' => 1,
            'name' => 'ck_customer_email',
            'expr' => ['Regex' => [
                'col' => 2,
                'pattern' => '^[^@\\s]+@[^@\\s]+\\.[^@\\s]+$',
                'negated' => false,
                'case_insensitive' => true,
            ]],
        ]],
    ],
]);

// Insert rows; created_at is filled by the default_value above
$db->put('orders', [1 => 1, 2 => '[email protected]', 3 => 99.50,  4 => 'new']);
$db->put('orders', [1 => 2, 2 => '[email protected]',   3 => 150.00, 4 => 'paid']);

// Upsert (insert or update on PK conflict)
$db->upsert('orders', [1 => 1, 2 => '[email protected]', 3 => 120.00, 4 => 'paid'], updateCells: [3 => 120.00, 4 => 'paid']);

// Query with a native index condition (learned-range index)
$rows = $db->query('orders')
    ->where('range', ['column' => 3, 'min' => 100.0])
    ->projection([1, 2, 4])
    ->limit(100)
    ->execute();

echo $db->count('orders'); // 2

// Run SQL
$db->sql("UPDATE orders SET amount = 200.0 WHERE customer_email = '[email protected]'");

// Bearer token (--auth-token mode)
$db = new Database('http://127.0.0.1:8453', token: 'my-secret-token');

// HTTP Basic (--auth-users mode)
$db = new Database('http://127.0.0.1:8453', username: 'admin', password: 's3cret');

$txn = $db->beginTransaction();
$txn->put('orders', [1 => 10, 2 => 'Dave', 3 => 50.00]);
$txn->put('orders', [1 => 11, 2 => 'Eve',  3 => 75.00]);
$txn->deleteByPk('orders', 2);

try {
    $txn->commit();                       // atomic - all or nothing
    echo "Staged {$txn->count()} operations\n";
} catch (\Visorcraft\MongrelDB\Exceptions\ConstraintException $e) {
    echo "Constraint violated: {$e->errorCode} - {$e->getMessage()}\n";
    $txn->rollback();
}

// Idempotent commit - safe to retry; daemon returns the original response
$txn = $db->beginTransaction();
$txn->put('orders', [1 => 20, 2 => 'Frank', 3 => 100.00]);
$txn->commit(idempotencyKey: 'order-20-create');

// Bitmap equality (low-cardinality columns)
$db->query('orders')->where('bitmap_eq', ['column' => 2, 'value' => 'Alice'])->execute();

// Range query (learned-range index)
$db->query('orders')
    ->where('range', ['column' => 3, 'min' => 50.0, 'max' => 150.0])
    ->limit(100)->execute();

// Full-text search (FM-index)
$db->query('documents')
    ->where('fm_contains', ['column' => 2, 'pattern' => 'database performance'])
    ->limit(10)->execute();

// Vector similarity search (HNSW)
$db->query('embeddings')
    ->where('ann', ['column' => 2, 'query' => [0.1, 0.2, 0.3], 'k' => 10])
    ->execute();

// Check whether a result was capped by the limit
$query = $db->query('orders')->where('range', ['column' => 3, 'min' => 0])->limit(100);
$rows = $query->execute();
if ($query->truncated()) {
    // result set hit the limit; more matches exist on the server
}

$db->sql("INSERT INTO orders (id, customer, amount) VALUES (99, 'Zoe', 999.0)");
$db->sql("CREATE TABLE archive AS SELECT * FROM orders WHERE amount > 500");

// Recursive CTEs and window functions
$db->sql("WITH RECURSIVE r(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM r WHERE n<10) SELECT n FROM r");
$db->sql("SELECT id, ROW_NUMBER() OVER (PARTITION BY customer ORDER BY amount DESC) FROM orders");

// DiskANN (in-memory Vamana graph)
$db->sql("CREATE INDEX orders_emb_diskann ON orders USING ann (embedding) WITH (algorithm = 'diskann', quantization = 'dense', diskann_l = 50, diskann_r = 64, beam_width = 8)");

// IVF with dense vectors (clustered)
$db->sql("CREATE INDEX orders_emb_ivf ON orders USING ann (embedding) WITH (algorithm = 'ivf', quantization = 'dense', nlist = 1024, nprobe = 16)");

// HNSW with product quantization (recall-tuned)
$db->sql("CREATE INDEX orders_emb_hnsw_pq ON orders USING ann (embedding) WITH (algorithm = 'hnsw', quantization = 'product', m = 16, ef_construction = 200, ef_search = 50, num_subvectors = 32, pq_training_samples = 50000, pq_rerank_factor = 8)");

$db->createUser('admin', 's3cret-pw');
$db->setUserAdmin('admin', true);

$db->createRole('analyst');
$db->grantPermission('analyst', 'select:orders');   // validated client-side
$db->grantRole('alice', 'analyst');

if ($db->verifyUser('alice', 'alice-pw')) { echo "Authenticated\n"; }

print_r($db->users());   // [['username' => 'admin'], ['username' => 'alice']]
print_r($db->roles());   // [['role' => 'analyst']]

// Enable with sensible defaults (DNS + TLS sessions + connection pool)
$db = new Database('http://127.0.0.1:8453', persistentSharing: true);

// Or pass an explicit list of CURL_LOCK_DATA_* constants
use Visorcraft\MongrelDB\Transport\CurlTransport;
$transport = new CurlTransport(persistentSharing: [\CURL_LOCK_DATA_DNS]);

use Visorcraft\MongrelDB\Transport\CurlTransport;

$transport = new CurlTransport(timeout: 60, connectTimeout: 5);
$client = new MongrelDB('http://127.0.0.1:8453', token: 'secret', transport: $transport);
$db = new Database(client: $client);

use Visorcraft\MongrelDB\Exceptions;

try {
    $db->put('orders', [1 => 1]); // duplicate PK
} catch (Exceptions\ConstraintException $e) {
    echo "Constraint: {$e->errorCode}\n";          // UNIQUE_VIOLATION
} catch (Exceptions\AuthException $e) {
    echo "Not authorized: {$e->getMessage()}\n";
} catch (Exceptions\NotFoundException $e) {
    echo "Not found: {$e->getMessage()}\n";
} catch (Exceptions\ConnectionException $e) {
    echo "Can't reach daemon: {$e->getMessage()}\n";
} catch (Exceptions\MongrelDBException $e) {
    echo "Error: {$e->getMessage()}\n";
}

$db->createTable('events', [
    ['id' => 1, 'name' => 'message', 'ty' => 'varchar', 'primary_key' => false, 'nullable' => false,
     'default_value' => 'none'],
    ['id' => 2, 'name' => 'count',   'ty' => 'int64',   'primary_key' => false, 'nullable' => false,
     'default_value' => 0],
    ['id' => 3, 'name' => 'active',  'ty' => 'bool',    'primary_key' => false, 'nullable' => false,
     'default_value' => true],
    ['id' => 4, 'name' => 'extra',   'ty' => 'varchar', 'primary_key' => false, 'nullable' => true,
     'default_value' => null],          // explicit JSON null
    ['id' => 5, 'name' => 'tag',     'ty' => 'varchar', 'primary_key' => false, 'nullable' => false,
     'default_value' => 'now'],         // static literal, not dynamic
    ['id' => 6, 'name' => 'created', 'ty' => 'timestamp', 'primary_key' => false, 'nullable' => false,
     'default_expr' => 'now'],          // dynamic default
]);

$db->createTable('orders', $columns, [
    'checks' => [[
        'id' => 1,
        'name' => 'ck_email',
        'expr' => ['Regex' => [
            'col' => 2,
            'pattern' => '^[^@\\s]+@[^@\\s]+\\.[^@\\s]+$',
            'negated' => false,
            'case_insensitive' => true,
        ]],
    ]],
]);

$window   = $db->historyRetentionEpochs();   // current retention window
$earliest = $db->earliestRetainedEpoch();    // oldest readable epoch

// Increase the window. Requires admin auth. Increasing retention cannot
// restore history already pruned past the previous earliest epoch.
$db->setHistoryRetentionEpochs($window + 10);

// Query historical state.
$rows = $db->sql('SELECT id FROM orders AS OF EPOCH ' . (int) $earliest);