manage_table_structure.php

<?php

declare(strict_types=1);

/**
 * Table structure editor: columns, indexes and destructive maintenance.
 */

require __DIR__ . '/lib/bootstrap.php';

use DbManager\Database;
use DbManager\Page;
use DbManager\Schema;
use DbManager\View;

[$pdo, $database, $table] = Page::table();

/** @var array<int, string> Column types offered by the editor. */
const STRUCTURE_TYPES = [
    'INT', 'BIGINT', 'SMALLINT', 'TINYINT', 'DECIMAL', 'FLOAT', 'DOUBLE',
    'VARCHAR', 'CHAR', 'TEXT', 'MEDIUMTEXT', 'LONGTEXT', 'JSON',
    'DATE', 'DATETIME', 'TIMESTAMP', 'TIME', 'YEAR', 'BLOB', 'BOOLEAN',
];

$tableUrl = url('manage_table_structure.php', ['db' => $database, 'table' => $table]);

if (($_SERVER['REQUEST_METHOD'] ?? 'GET') === 'POST') {
    $action = request_str('action');

    Page::handle(
        static function () use ($pdo, $table, $action): string {
            $quotedTable = Database::quoteIdentifier($table);

            switch ($action) {
                case 'add_column':
                    return fn_add_column($pdo, $quotedTable);

                case 'drop_column':
                    $column = request_str('column');
                    Schema::assertPlainIdentifier($column);
                    $pdo->exec('ALTER TABLE ' . $quotedTable . ' DROP COLUMN ' . Database::quoteIdentifier($column));

                    return __('structure.column_dropped', [':name' => $column]);

                case 'add_index':
                    return fn_add_index($pdo, $quotedTable);

                case 'drop_index':
                    $index = request_str('index');
                    Schema::assertPlainIdentifier($index);
                    $pdo->exec('ALTER TABLE ' . $quotedTable . ' DROP INDEX ' . Database::quoteIdentifier($index));

                    return __('structure.index_dropped', [':name' => $index]);

                case 'truncate':
                    Schema::maintain($pdo, $table, 'truncate');

                    return __('structure.truncated', [':name' => $table]);

                case 'optimize':
                    Schema::maintain($pdo, $table, 'optimize');

                    return __('tables.optimized', [':count' => 1]);

                default:
                    throw new RuntimeException(__('error.unknown_operation', [':name' => $action]));
            }
        },
        $tableUrl
    );
}

/**
 * Adds a column from the submitted form.
 *
 * @param PDO    $pdo         Live connection.
 * @param string $quotedTable Quoted table name.
 *
 * @throws RuntimeException When the submitted type is not supported.
 *
 * @return string Success message.
 */
function fn_add_column(PDO $pdo, string $quotedTable): string
{
    $name = request_str('column_name');
    $type = strtoupper(request_str('column_type'));

    Schema::assertPlainIdentifier($name);

    if (!in_array($type, STRUCTURE_TYPES, true)) {
        throw new RuntimeException(__('error.invalid_identifier', [':name' => $type]));
    }

    $length = request_str('column_length');

    if ($length !== '') {
        if (preg_match("/^[0-9]+(\s*,\s*[0-9]+)?$/", $length) !== 1) {
            throw new RuntimeException(__('error.invalid_identifier', [':name' => $length]));
        }

        $type .= '(' . preg_replace('/\s+/', '', $length) . ')';
    }

    $sql = 'ALTER TABLE ' . $quotedTable . ' ADD ' . Database::quoteIdentifier($name) . ' ' . $type
        . (request_str('nullable') === '1' ? ' NULL' : ' NOT NULL');

    $default = request_str('default_value');

    if ($default !== '') {
        $sql .= ' DEFAULT ' . (
            preg_match('/^(CURRENT_TIMESTAMP|NULL|[0-9.]+)$/i', $default) === 1
                ? $default
                : $pdo->quote($default)
        );
    }

    $after = request_str('after_column');

    if ($after !== '') {
        Schema::assertPlainIdentifier($after);
        $sql .= ' AFTER ' . Database::quoteIdentifier($after);
    }

    $pdo->exec($sql);

    return __('structure.column_added', [':name' => $name]);
}

/**
 * Adds an index from the submitted form.
 *
 * @param PDO    $pdo         Live connection.
 * @param string $quotedTable Quoted table name.
 *
 * @throws RuntimeException When no column was supplied.
 *
 * @return string Success message.
 */
function fn_add_index(PDO $pdo, string $quotedTable): string
{
    $name    = request_str('index_name');
    $columns = array_filter(array_map('trim', explode(',', request_str('index_columns'))));

    Schema::assertPlainIdentifier($name);

    if ($columns === []) {
        throw new RuntimeException(__('error.missing_parameter', [':name' => 'index_columns']));
    }

    foreach ($columns as $column) {
        Schema::assertPlainIdentifier($column);
    }

    $quoted = array_map(
        static fn (string $column): string => Database::quoteIdentifier($column),
        $columns
    );

    $pdo->exec(
        'ALTER TABLE ' . $quotedTable
        . ' ADD ' . (request_str('unique') === '1' ? 'UNIQUE ' : '') . 'INDEX '
        . Database::quoteIdentifier($name) . ' (' . implode(', ', $quoted) . ')'
    );

    return __('structure.index_added', [':name' => $name]);
}

$actions = '<a class="btn" href="' . e(url('view_table_data.php', ['db' => $database, 'table' => $table])) . '">'
    . icon('search') . '<span>' . e(__('common.browse')) . '</span></a>'
    . '<a class="btn" href="' . e(url('export.php', ['db' => $database, 'table' => $table])) . '">'
    . icon('download') . '<span>' . e(__('common.export')) . '</span></a>';

View::render(
    'table_structure',
    [
        'database'        => $database,
        'table'           => $table,
        'columns'         => Schema::columns($pdo, $table),
        'indexes'         => Schema::indexes($pdo, $table),
        'createStatement' => Schema::createStatement($pdo, $table),
        'types'           => STRUCTURE_TYPES,
    ],
    [
        'title'    => __('structure.title', [':table' => $table]),
        'subtitle' => $database,
        'active'   => 'databases',
        'actions'  => $actions,
    ]
);