ops-Gazelle/app/DB/MysqlTable.php

<?php

declare(strict_types=1);

namespace Gazelle\DB;

class MysqlTable extends AbstractTable {
    protected Mysql $dbro;

    public function __construct(
        public readonly string $name,
    ) {
        $this->dbro = \Gazelle\DB::DB(readWrite: false);
    }

    public function location(): string {
        return "tools.php?action=db-mysql&table={$this->name}";
    }

    public function exists(): bool {
        return (bool)self::$db->scalar("
            SELECT 1
            FROM information_schema.tables t
            WHERE t.table_schema = ?
                AND t.table_name = ?
            ", MYSQL_DB, $this->name
        );
    }

    public function definition(): string {
        return self::$db->row("SHOW CREATE TABLE {$this->name}")[1];
    }

    /***
     * List the tables that have a foreign key reference to the specified table
     */
    public function foreignKeyList(): array {
        self::$db->prepared_query("
            SELECT kcu.table_name,
                kcu.column_name,
                kcu.constraint_name,
                rc.update_rule,
                rc.delete_rule
            FROM information_schema.key_column_usage kcu
            INNER JOIN information_schema.referential_constraints rc
                USING (constraint_schema, constraint_name, table_name)
            WHERE kcu.table_schema = ?
                AND kcu.referenced_table_name = ?
            ORDER BY kcu.table_name, kcu.column_name
            ", MYSQL_DB, $this->name
        );
        return self::$db->to_array(false, MYSQLI_ASSOC);
    }

    public function indexRead(): array {
        self::$db->prepared_query("
            SELECT s.INDEX_NAME           AS index_name,
                coalesce(si.ROWS_READ, 0) AS rows_read,
                group_concat(
                    concat(s.column_name, ' {', s.cardinality, '}')
                    ORDER BY s.seq_in_index
                    SEPARATOR ', '
                ) AS column_list
            FROM information_schema.statistics s
            LEFT JOIN information_schema.index_statistics si
                USING (TABLE_SCHEMA, TABLE_NAME, INDEX_NAME)
            WHERE s.TABLE_SCHEMA = ?
                AND s.TABLE_NAME = ?
            GROUP BY index_name,
                rows_read
            ORDER BY s.TABLE_NAME,
                s.INDEX_NAME = 'PRIMARY' DESC,
                coalesce(si.ROWS_READ, 0) DESC,
                s.INDEX_NAME
            ", MYSQL_DB, $this->name
        );
        return self::$db->to_array(false, MYSQLI_ASSOC);
    }

    public function tableRead(): array {
        return self::$db->rowAssoc("
            SELECT ROWS_READ, ROWS_CHANGED, ROWS_CHANGED_X_INDEXES
            FROM information_schema.table_statistics
            WHERE TABLE_SCHEMA = ?
                AND TABLE_NAME = ?
            ", MYSQL_DB, $this->name
        );
    }

    public function recentUpdate(int $seconds): bool {
        return (bool)self::$db->scalar("
            SELECT 1
            FROM information_schema.tables
            WHERE table_schema = ?
                AND table_name = ?
                AND update_time > now() - INTERVAL ? SECOND
            ", MYSQL_DB, $this->name, $seconds
        );
    }

    public function stats(): array {
        return self::$db->rowAssoc("
            SELECT t.TABLE_ROWS,
                t.AVG_ROW_LENGTH,
                t.DATA_LENGTH,
                t.INDEX_LENGTH,
                t.DATA_FREE,
                coalesce(ts.ROWS_READ, 0) AS ROWS_READ,
                coalesce(ts.ROWS_CHANGED, 0) AS ROWS_CHANGED,
                coalesce(ts.ROWS_CHANGED_X_INDEXES, 0) AS ROWS_CHANGED_X_INDEXES,
                CASE WHEN wsbt.COUNT_READ + wsbt.COUNT_WRITE + wsbt.COUNT_FETCH
                    + wsbt.COUNT_INSERT + wsbt.COUNT_UPDATE + wsbt.COUNT_DELETE = 0
                    THEN 0
                    ELSE (wsbt.COUNT_READ + wsbt.COUNT_WRITE + wsbt.COUNT_FETCH
                            + wsbt.COUNT_INSERT + wsbt.COUNT_UPDATE + wsbt.COUNT_DELETE)
                        / (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Uptime')
                    END AS iops
            FROM information_schema.tables t
            INNER JOIN performance_schema.table_io_waits_summary_by_table wsbt
                ON (wsbt.OBJECT_SCHEMA = t.TABLE_SCHEMA AND wsbt.OBJECT_NAME = t.TABLE_NAME)
            LEFT JOIN information_schema.table_statistics ts
                ON (ts.TABLE_SCHEMA = t.TABLE_SCHEMA AND ts.TABLE_NAME = t.TABLE_NAME)
            WHERE t.TABLE_SCHEMA = ?
                AND t.TABLE_NAME = ?
            ", MYSQL_DB, $this->name
        );
    }
}