JsonStore - SQLite as a Document Database using PDO PHP

JsonStore.php

<?php

/**
 * JsonStore - SQLite como Document Database em PHP
 *
 * Uma alternativa leve a MongoDB/CouchDB usando SQLite + JSON1 extension.
 * Ideal para aplicações pequenas/médias que precisam de flexibilidade NoSQL
 * sem abrir mão de ACID, índices e concorrência do SQLite.
 *
 * Requisitos: PHP 8.0+, PDO SQLite, libsqlite >= 3.38.0 (JSON1 built-in)
 *
 * Uso rápido:
 *   $db    = new JsonStore(__DIR__ . '/app.db');
 *   $users = $db->collection('users');
 *   $id    = $users->insert(['nome' => 'Ana', 'idade' => 29]);
 *   $users->where('idade', '>=', 25)->orderBy('nome')->get();
 *
 * @author  Daniel (github.com/affonso)
 * @license MIT
 * @version 1.0.0
 */

// ---------------------------------------------------------------------------
// JsonStore — Gerenciador de banco de dados
// ---------------------------------------------------------------------------

class JsonStore
{
    private PDO $pdo;

    /**
     * Abre (ou cria) um banco SQLite.
     *
     * @param string $path Caminho do arquivo .db ou ':memory:' para banco em memória
     */
    public function __construct(string $path = ':memory:')
    {
        $this->pdo = new PDO("sqlite:{$path}");
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        // WAL mode: permite leituras concorrentes durante escritas
        $this->pdo->exec('PRAGMA journal_mode=WAL');
        $this->pdo->exec('PRAGMA foreign_keys=ON');
    }

    /**
     * Retorna uma collection (cria a tabela automaticamente se não existir).
     *
     * @param string $name Nome da collection (será sanitizado)
     */
    public function collection(string $name): Collection
    {
        return new Collection($this->pdo, $name);
    }

    /**
     * Acesso direto ao PDO para queries avançadas.
     */
    public function getPdo(): PDO
    {
        return $this->pdo;
    }
}

// ---------------------------------------------------------------------------
// Collection — Representa uma "tabela" de documentos JSON
// ---------------------------------------------------------------------------

class Collection
{
    private PDO $pdo;
    private string $table;

    public function __construct(PDO $pdo, string $table)
    {
        $this->pdo   = $pdo;
        $this->table = preg_replace('/[^a-zA-Z0-9_]/', '', $table);

        // Cria a tabela com validação JSON automática e timestamps
        $this->pdo->exec("
            CREATE TABLE IF NOT EXISTS {$this->table} (
                id         INTEGER PRIMARY KEY AUTOINCREMENT,
                doc        TEXT NOT NULL CHECK(json_valid(doc)),
                created_at TEXT DEFAULT (datetime('now')),
                updated_at TEXT DEFAULT (datetime('now'))
            )
        ");
    }

    // -----------------------------------------------------------------------
    // CREATE
    // -----------------------------------------------------------------------

    /**
     * Insere um documento e retorna o ID gerado.
     *
     * @param array $data Dados do documento (aceita subdocumentos/arrays)
     * @return int ID do documento inserido
     */
    public function insert(array $data): int
    {
        $stmt = $this->pdo->prepare(
            "INSERT INTO {$this->table} (doc) VALUES (json(?))"
        );
        $stmt->execute([json_encode($data, JSON_UNESCAPED_UNICODE)]);

        return (int) $this->pdo->lastInsertId();
    }

    /**
     * Insere múltiplos documentos em uma única transação.
     * Se qualquer insert falhar, todos são revertidos.
     *
     * @param array $items Lista de documentos
     * @return array IDs dos documentos inseridos
     */
    public function insertMany(array $items): array
    {
        $ids = [];
        $this->pdo->beginTransaction();

        try {
            foreach ($items as $item) {
                $ids[] = $this->insert($item);
            }
            $this->pdo->commit();
        } catch (\Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }

        return $ids;
    }

    // -----------------------------------------------------------------------
    // READ
    // -----------------------------------------------------------------------

    /**
     * Busca um documento pelo ID.
     *
     * @return array|null Documento hidratado com _id, _created_at, _updated_at
     */
    public function find(int $id): ?array
    {
        $stmt = $this->pdo->prepare(
            "SELECT id, doc, created_at, updated_at FROM {$this->table} WHERE id = ?"
        );
        $stmt->execute([$id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        return $row ? $this->hydrate($row) : null;
    }

    /**
     * Retorna todos os documentos da collection.
     */
    public function all(): array
    {
        $stmt = $this->pdo->query(
            "SELECT id, doc, created_at, updated_at FROM {$this->table}"
        );

        return array_map([$this, 'hydrate'], $stmt->fetchAll(PDO::FETCH_ASSOC));
    }

    /**
     * Inicia uma query com filtro.
     * Suporta campos aninhados via dot notation: 'endereco.cidade'
     *
     * @param string $path  Caminho do campo (ex: 'nome', 'rg.numero')
     * @param string $op    Operador: =, !=, >, <, >=, <=, LIKE
     * @param mixed  $value Valor para comparação
     */
    public function where(string $path, string $op, mixed $value): QueryBuilder
    {
        return (new QueryBuilder($this->pdo, $this->table))->where($path, $op, $value);
    }

    // -----------------------------------------------------------------------
    // UPDATE
    // -----------------------------------------------------------------------

    /**
     * Atualiza campos específicos de um documento (merge parcial).
     * Suporta dot notation para campos aninhados.
     *
     * Exemplos:
     *   $col->update($id, ['idade' => 30]);                    // campo simples
     *   $col->update($id, ['rg.numero' => '999']);              // campo aninhado
     *   $col->update($id, ['rg' => ['num' => '1', 'uf' => 'AM']]); // subdocumento inteiro
     *
     * @param int   $id     ID do documento
     * @param array $fields Campos a atualizar (chave => valor)
     * @return bool true se algum registro foi afetado
     */
    public function update(int $id, array $fields): bool
    {
        $expression = "doc = json_set(doc";
        $params     = [];

        foreach ($fields as $key => $val) {
            $path = '$.' . $key;

            if (is_array($val)) {
                // Subdocumentos/arrays são inseridos como JSON válido
                $expression .= ", '{$path}', json(?)";
                $params[]    = json_encode($val, JSON_UNESCAPED_UNICODE);
            } else {
                $expression .= ", '{$path}', ?";
                $params[]    = $val;
            }
        }

        $expression .= ")";
        $params[]    = $id;

        $stmt = $this->pdo->prepare(
            "UPDATE {$this->table} SET {$expression}, updated_at = datetime('now') WHERE id = ?"
        );
        $stmt->execute($params);

        return $stmt->rowCount() > 0;
    }

    // -----------------------------------------------------------------------
    // DELETE
    // -----------------------------------------------------------------------

    /**
     * Remove um documento pelo ID.
     */
    public function delete(int $id): bool
    {
        $stmt = $this->pdo->prepare("DELETE FROM {$this->table} WHERE id = ?");
        $stmt->execute([$id]);

        return $stmt->rowCount() > 0;
    }

    // -----------------------------------------------------------------------
    // UTILITÁRIOS
    // -----------------------------------------------------------------------

    /**
     * Retorna o total de documentos na collection.
     */
    public function count(): int
    {
        return (int) $this->pdo->query(
            "SELECT COUNT(*) FROM {$this->table}"
        )->fetchColumn();
    }

    /**
     * Cria um índice em um campo JSON para acelerar queries.
     * Suporta dot notation para campos aninhados.
     *
     * Exemplo:
     *   $col->createIndex('email');
     *   $col->createIndex('rg.numero');
     *
     * @param string $field Caminho do campo
     */
    public function createIndex(string $field): void
    {
        $safe = preg_replace('/[^a-zA-Z0-9_]/', '', str_replace('.', '_', $field));

        $this->pdo->exec("
            CREATE INDEX IF NOT EXISTS idx_{$this->table}_{$safe}
            ON {$this->table}(json_extract(doc, '\$.{$field}'))
        ");
    }

    /**
     * Transforma uma row do SQLite em array com metadados.
     * Campos internos (_id, _created_at, _updated_at) são adicionados automaticamente.
     */
    private function hydrate(array $row): array
    {
        $data                = json_decode($row['doc'], true);
        $data['_id']         = (int) $row['id'];
        $data['_created_at'] = $row['created_at'];
        $data['_updated_at'] = $row['updated_at'];

        return $data;
    }
}

// ---------------------------------------------------------------------------
// QueryBuilder — Construtor de queries fluente
// ---------------------------------------------------------------------------

class QueryBuilder
{
    private PDO $pdo;
    private string $table;
    private array $conditions = [];
    private array $params     = [];
    private ?string $orderBy  = null;
    private ?int $limit       = null;
    private ?int $offset      = null;

    /** Operadores permitidos (whitelist contra SQL injection) */
    private const ALLOWED_OPS = ['=', '!=', '>', '<', '>=', '<=', 'LIKE'];

    public function __construct(PDO $pdo, string $table)
    {
        $this->pdo   = $pdo;
        $this->table = $table;
    }

    /**
     * Adiciona uma condição WHERE (encadeável).
     * Múltiplas chamadas são combinadas com AND.
     *
     * @param string $path  Campo JSON com dot notation (ex: 'endereco.cidade')
     * @param string $op    Operador SQL
     * @param mixed  $value Valor para comparação
     */
    public function where(string $path, string $op, mixed $value): self
    {
        if (!in_array(strtoupper($op), self::ALLOWED_OPS, true)) {
            throw new \InvalidArgumentException("Operador inválido: {$op}");
        }

        $this->conditions[] = "json_extract(doc, '\$.{$path}') {$op} ?";
        $this->params[]     = $value;

        return $this;
    }

    /**
     * Ordena resultados por campo JSON.
     *
     * @param string $path Campo JSON
     * @param string $dir  ASC ou DESC
     */
    public function orderBy(string $path, string $dir = 'ASC'): self
    {
        $dir           = strtoupper($dir) === 'DESC' ? 'DESC' : 'ASC';
        $this->orderBy = "json_extract(doc, '\$.{$path}') {$dir}";

        return $this;
    }

    /**
     * Limita a quantidade de resultados.
     */
    public function limit(int $limit, int $offset = 0): self
    {
        $this->limit  = $limit;
        $this->offset = $offset;

        return $this;
    }

    /**
     * Executa a query e retorna os documentos.
     *
     * @return array Lista de documentos hidratados
     */
    public function get(): array
    {
        $sql = "SELECT id, doc, created_at, updated_at FROM {$this->table}";

        if ($this->conditions) {
            $sql .= ' WHERE ' . implode(' AND ', $this->conditions);
        }
        if ($this->orderBy) {
            $sql .= " ORDER BY {$this->orderBy}";
        }
        if ($this->limit !== null) {
            $sql .= " LIMIT {$this->limit} OFFSET {$this->offset}";
        }

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($this->params);

        return array_map(function (array $row): array {
            $data                = json_decode($row['doc'], true);
            $data['_id']         = (int) $row['id'];
            $data['_created_at'] = $row['created_at'];
            $data['_updated_at'] = $row['updated_at'];
            return $data;
        }, $stmt->fetchAll(PDO::FETCH_ASSOC));
    }

    /**
     * Retorna apenas o primeiro resultado ou null.
     */
    public function first(): ?array
    {
        $this->limit = 1;
        $results     = $this->get();

        return $results[0] ?? null;
    }

    /**
     * Retorna a contagem de documentos que atendem os filtros.
     */
    public function count(): int
    {
        $sql = "SELECT COUNT(*) FROM {$this->table}";

        if ($this->conditions) {
            $sql .= ' WHERE ' . implode(' AND ', $this->conditions);
        }

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($this->params);

        return (int) $stmt->fetchColumn();
    }
}

usage.php

<?php

$db = new JsonStore(__DIR__ . '/meu.db');
$users = $db->collection('users');

// Insert
$id = $users->insert(['nome' => 'Ana', 'idade' => 29, 'email' => 'ana@email.com']);

// Batch
$users->insertMany([
    ['nome' => 'Bob', 'idade' => 35],
    ['nome' => 'Carol', 'idade' => 22],
]);

// Index para buscas frequentes
$users->createIndex('email');

// Query fluente
$results = $users->where('idade', '>=', 25)
    ->where('nome', 'LIKE', '%a%')
    ->orderBy('idade', 'DESC')
    ->limit(10)
    ->get();

// Update parcial
$users->update($id, ['idade' => 30, 'cidade' => 'Manaus']);

// Find by ID
$user = $users->find($id);

// Você também pode adicionar outro documento no campo de um documento

// Inserir doc com subdocumento
$id = $users->insert([
    'nome' => 'João',
    'idade' => 30,
    'rg' => ['numero' => '123456789', 'emitido' => '03/03/2026']
]);

// Buscar por campo aninhado
$users->where('rg.numero', '=', '123456789')->get();

// Atualizar só um campo interno
$users->update($id, ['rg.emitido' => '04/04/2026']);

// Substituir subdocumento inteiro
$users->update($id, ['rg' => ['numero' => '987', 'emitido' => '01/01/2027']]);

// Índice em campo aninhado
$users->createIndex('rg.numero');