PHP code example of detain / dbrel-data-php

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

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

    

detain / dbrel-data-php example snippets

use DbRel\Data\RelationshipSchema;
use DbRel\Data\DataCollector;
use DbRel\Data\DataProvider;

DbRel\Data\RelationshipSchema;
use DbRel\Data\DataCollector;
use DbRel\Data\DataProvider;

// 1. Load your schema
$schema = new RelationshipSchema(__DIR__ . '/config/db_relationships.json');

// 2. Your existing database handle (must implement DbInterface — see below)
$db = new MyDb\Mysqli\Db();       // or any wrapper you already use
$db->connect('localhost', 'root', '...', 'my');

// 3. Collect rows
$custid    = (int) ($_GET['custid'] ?? 0);
$collector = new DataCollector();

$collector->collect($db, 'my', 'accounts',
    "SELECT * FROM accounts WHERE account_id = {$custid}", 1);

$collector->collect($db, 'my', 'vps',
    "SELECT * FROM vps WHERE vps_custid = {$custid}", 50);

$collector->collect($db, 'my', 'domains',
    "SELECT * FROM domains WHERE domain_custid = {$custid}", 50);

$collector->collect($db, 'my', 'invoices_charges',
    "SELECT * FROM invoices_charges WHERE invoice_custid = {$custid}", 50);

// 4. Build the response
$provider = new DataProvider($schema);
$payload  = $provider->build($collector, [
    'custid'      => $custid,
    'primaryKeys' => [ 'accounts' => 'account_id',  'vps' => 'vps_id' ],
    'prefixes'    => [ 'accounts' => 'account_',    'vps' => 'vps_'   ],
    'hiddenFields'=> ['password', 'api_token'],
]);

header('Content-Type: application/json');
echo json_encode($payload);

$schema = new RelationshipSchema($pathOrArray);

$collector = new DataCollector();

$matcher = new RelationshipMatcher();
$active  = $matcher->compute($tablesData, $rules);

$provider = new DataProvider($schema, /* optional */ $matcher);
$payload  = $provider->build($collector, $options);

[
    'custid' => 12345,
    'tables' => [
        'my.accounts' => [
            'rows'      => [[...]],
            'columns'   => ['account_id', ...],
            'total'     => 1,
            'truncated' => false
        ],
        // ...
    ],
    'relationships' => [
        [
            'source'       => 'my.accounts',
            'source_field' => 'account_id',
            'target'       => 'my.vps',
            'target_field' => 'vps_custid',
            'type'         => 'direct',
            'cardinality'  => '1:N',
            'label'        => 'Account → VPS',
            'matches'      => [[0, [0, 1, 2]]]
        ],
    ],
    'metadata' => [
        'databases'          => ['my', 'kayako_v4', 'pdns'],
        'table_count'        => 14,
        'total_rows'         => 42,
        'relationship_count' => 9,
        'query_time_ms'      => 127.4,
        'custid'             => 12345,
        'pivot_table'        => null,
        'pivot_id'           => null
    ],
    'prefixes'     => [...],
    'primaryKeys'  => [...],
    'hiddenFields' => [...]
]

namespace DbRel\Data;

interface DbInterface
{
    public function query($sql, $line = 0, $file = '');
    public function next_record($mode = 1);        // 1 = associative
    public function num_rows();
    public function real_escape($value);
    public function getLastInsertId($table = '', $column = '');
}

// public/api/db-relationships.php

bRel\Data\DataCollector;
use DbRel\Data\DataProvider;

header('Content-Type: application/json');

$custid = (int) ($_GET['custid'] ?? 0);
if ($custid <= 0) {
    http_response_code(400);
    echo json_encode(['error' => 'custid connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);

    $collector = new DataCollector();

    // Core tables
    $collector->collect($db, 'my', 'accounts',
        "SELECT * FROM accounts WHERE account_id = {$custid}", 1);

    $tables = ['vps', 'domains', 'websites', 'backups', 'licenses',
               'scrub_ips', 'floating_ips', 'mail'];
    foreach ($tables as $t) {
        $col = "{$t}_custid";
        // Probe the column's real prefix from schema.modules if needed.
        $collector->collect($db, 'my', $t,
            "SELECT * FROM {$t} WHERE {$col} = {$custid}", 100);
    }

    // Billing
    $collector->collect($db, 'my', 'invoices_charges',
        "SELECT * FROM invoices_charges WHERE invoice_custid = {$custid}", 100);

    // Helpdesk (cross-DB)
    $helpdesk = new MyDb\Mysqli\Db();
    $helpdesk->connect(KAYAKO_HOST, KAYAKO_USER, KAYAKO_PASS, 'kayako_v4');
    $collector->collect($helpdesk, 'kayako_v4', 'swusers',
        "SELECT * FROM swusers WHERE externalid = {$custid}", 5);

    $provider = new DataProvider($schema);
    echo json_encode($provider->build($collector, [
        'custid'      => $custid,
        'pivotTable'  => $pivotTable,
        'pivotId'     => $pivotId,
        'primaryKeys' => ['accounts' => 'account_id', 'vps' => 'vps_id', /* ... */],
        'prefixes'    => ['accounts' => 'account_',   'vps' => 'vps_',   /* ... */],
        'hiddenFields'=> ['password', 'api_token'],
    ]));
} catch (Throwable $e) {
    http_response_code(500);
    echo json_encode(['error' => $e->getMessage()]);
}

use DbRel\Data\DbInterface;

class PdoAdapter implements DbInterface
{
    /** @var \PDO */
    private $pdo;
    /** @var \PDOStatement|null */
    private $stmt;
    /** @var int */
    private $rowCount = 0;

    public function __construct(\PDO $pdo) { $this->pdo = $pdo; }

    public function query($sql, $line = 0, $file = '')
    {
        $this->stmt = $this->pdo->query($sql);
        if ($this->stmt === false) {
            throw new \RuntimeException("Query failed at {$file}:{$line}");
        }
        $this->rowCount = $this->stmt->rowCount();
        return $this->stmt;
    }

    public function next_record($mode = 1)
    {
        if (!$this->stmt) return false;
        $row = $this->stmt->fetch(\PDO::FETCH_ASSOC);
        if ($row === false) return false;
        // DbInterface expects rows on a `Record` property OR via a `getRecord()` method
        $this->Record = $row;
        return true;
    }

    public function num_rows()            { return $this->rowCount; }
    public function real_escape($value)   { return substr($this->pdo->quote($value), 1, -1); }
    public function getLastInsertId($t = '', $c = '') { return (int) $this->pdo->lastInsertId(); }

    /** @var array */
    public $Record = [];
}

bash
git clone https://github.com/detain/dbrel-data-php.git
cd dbrel-data-php
composer install
composer test