fitme3070.pt/shared/platform-tools/functions_schema.php

<?php
// ─── Schema export (shared) ──────────────────────────────────────────────────
//
// Generates a deterministic, structure-only SQL dump of the host database into
// <host_repo>/database/schema.sql, suitable for committing to git.
//
// "Deterministic" is the whole point: no generation timestamp, no server-version
// header, AUTO_INCREMENT counters stripped, view DEFINERs removed, and tables
// emitted in alphabetical order. Re-running against an unchanged schema produces
// a byte-identical file — so a `git diff` on schema.sql is a true changelog of
// structural changes, not export noise. (Contrast the hand-made phpMyAdmin dumps,
// whose "Generation Time" line and AUTO_INCREMENT counters churn on every export.)
//
// Built entirely over the existing PDO connection (SHOW FULL TABLES /
// SHOW CREATE TABLE / SHOW CREATE VIEW) — no mysqldump binary and no shell
// access required. That matters here: the platform hosts have no usable PHP or
// mysql CLI, so this runs web-triggered from the platform update page.
//
// This is schema only. Row data is out of scope by design — that's what the
// backup API (functions_backup.php) is for.
//
// Public API:
//   schema_dump_path(): string             — absolute path to schema.sql
//   schema_build(PDO): string              — full SQL text (does not write)
//   schema_dump_to_file(PDO): array        — {ok, path?, bytes?, tables?, views?, error?}

/**
 * Resolve the target schema.sql path. Reuses the migrations dir resolution so
 * the file lands next to migration_*.sql in the host's database/ folder.
 */
function schema_dump_path(): string {
    if (function_exists('migrations_dir')) {
        $dir = migrations_dir();
    } else {
        $dir = $GLOBALS['MIGRATIONS_DIR'] ?? (dirname(__DIR__, 2) . '/database');
        $dir = rtrim($dir, '/');
    }
    return $dir . '/schema.sql';
}

/** Strip the AUTO_INCREMENT counter so a changed row count isn't a schema diff. */
function schema_normalize_table_ddl(string $sql): string {
    return preg_replace('/\s+AUTO_INCREMENT=\d+\b/', '', $sql);
}

/**
 * Strip the per-host DEFINER (and its SQL SECURITY clause) from a view DDL.
 * The definer references a DB user that differs between dev/live and between
 * tenants, so leaving it in would make the dump non-deterministic and break
 * replay on a host where that user does not exist.
 */
function schema_normalize_view_ddl(string $sql): string {
    $sql = preg_replace('/\sDEFINER=`[^`]*`@`[^`]*`/', '', $sql, 1);
    $sql = preg_replace('/\sSQL SECURITY DEFINER/', '', $sql, 1);
    return $sql;
}

/** Quote an identifier for interpolation into a SHOW CREATE statement. */
function schema_quote_ident(string $name): string {
    return '`' . str_replace('`', '``', $name) . '`';
}

/**
 * Build the full schema dump as a string. Throws PDOException on query failure
 * (the caller turns that into a flash message).
 */
function schema_build(PDO $pdo): string {
    $dbName = (string) $pdo->query('SELECT DATABASE()')->fetchColumn();

    // Split base tables from views: views must be (re)created after the tables
    // they reference, and SHOW CREATE differs per object type.
    $tables = [];
    $views  = [];
    foreach ($pdo->query('SHOW FULL TABLES')->fetchAll(PDO::FETCH_NUM) as $row) {
        [$name, $type] = $row;
        if (strcasecmp((string) $type, 'VIEW') === 0) {
            $views[] = $name;
        } else {
            $tables[] = $name;
        }
    }
    sort($tables, SORT_STRING);
    sort($views,  SORT_STRING);

    $out   = [];
    $out[] = '-- Schema dump for database `' . $dbName . '`';
    $out[] = '-- Structure only. Generated by platform-tools (functions_schema.php).';
    $out[] = '-- Deterministic: no timestamp, AUTO_INCREMENT stripped, objects sorted.';
    $out[] = '-- Do NOT edit by hand — re-run the export from the platform update page.';
    $out[] = '';
    $out[] = 'SET FOREIGN_KEY_CHECKS = 0;';
    $out[] = '';

    foreach ($tables as $t) {
        $create = $pdo->query('SHOW CREATE TABLE ' . schema_quote_ident($t))
                      ->fetch(PDO::FETCH_NUM)[1] ?? '';
        $create = schema_normalize_table_ddl($create);
        $out[] = '-- -----------------------------------------------------------';
        $out[] = '-- Table `' . $t . '`';
        $out[] = '-- -----------------------------------------------------------';
        $out[] = 'DROP TABLE IF EXISTS ' . schema_quote_ident($t) . ';';
        $out[] = $create . ';';
        $out[] = '';
    }

    foreach ($views as $v) {
        $create = $pdo->query('SHOW CREATE VIEW ' . schema_quote_ident($v))
                      ->fetch(PDO::FETCH_NUM)[1] ?? '';
        $create = schema_normalize_view_ddl($create);
        $out[] = '-- -----------------------------------------------------------';
        $out[] = '-- View `' . $v . '`';
        $out[] = '-- -----------------------------------------------------------';
        $out[] = 'DROP VIEW IF EXISTS ' . schema_quote_ident($v) . ';';
        $out[] = $create . ';';
        $out[] = '';
    }

    $out[] = 'SET FOREIGN_KEY_CHECKS = 1;';
    $out[] = '';

    return implode("\n", $out);
}

/**
 * Build the dump and write it atomically to schema.sql. Never throws — returns
 * a result array the view can render directly.
 */
function schema_dump_to_file(PDO $pdo): array {
    try {
        $sql = schema_build($pdo);
    } catch (Throwable $e) {
        return ['ok' => false, 'error' => 'Erro ao ler o esquema: ' . $e->getMessage()];
    }

    $path = schema_dump_path();
    $dir  = dirname($path);
    if (!is_dir($dir) && !@mkdir($dir, 0775, true)) {
        return ['ok' => false, 'error' => 'Não foi possível criar a pasta ' . $dir . '.'];
    }

    // Write to a temp file then rename: a crash mid-write can't leave a
    // truncated schema.sql staged for commit.
    $tmp = $path . '.tmp';
    if (@file_put_contents($tmp, $sql, LOCK_EX) === false) {
        return ['ok' => false, 'error' => 'Falha ao escrever ' . basename($path) . ' (permissões?).'];
    }
    if (!@rename($tmp, $path)) {
        @unlink($tmp);
        return ['ok' => false, 'error' => 'Falha ao substituir ' . basename($path) . '.'];
    }

    return [
        'ok'     => true,
        'path'   => $path,
        'bytes'  => strlen($sql),
        'tables' => preg_match_all('/^CREATE TABLE /m', $sql),
        'views'  => preg_match_all('/^CREATE(?: ALGORITHM=\S+)? VIEW /m', $sql),
    ];
}