Changing the Database Schema

// Declared by your module, called by the platform when the operator flips the switch.
// On "enable" activate() runs first when it exists, then enable(); on "disable" the
// deactivate()/disable() pair does the same. The new status is written ONLY if the last
// one returned a truthy value, so a falsy return or a thrown exception rejects the click
// rather than leaving the module half-installed.
public function enable(): bool;
public function disable(): bool;

// Your own steps, called from enable(). Both the create path and the upgrade path live
// here, so both run on every enable, on every re-enable and after every update.
private function check_database(): void;   // tables
private function check_columns(): void;    // columns added by a later version
private function seed(): void;             // starting rows, once

public static function hasTable($table = '');               // bool, via SHOW TABLES LIKE - no prefix is applied
public static function exec($arg = '');                     // int affected rows; 0 on failure, WITHOUT throwing
public static function query($statement, $isthis = false);  // PDOStatement, or false on failure - also without throwing
public static function getAssoc($statement = false);        // one row of that statement; false when there is none
public static function getErrorMessage();                   // why exec() or query() came back empty
public static function getPrefix(): string;                 // the schema prefix, when the installation configures one

// DDL runs through exec()/query() because the chained builder only writes DML. Neither
// of those two throws: a failed CREATE or ALTER returns 0 / false and says nothing.

public function enable(): bool
{
    $this->check_database();
    $this->check_columns();

    return true;
}

private function check_database(): void
{
    if (\WDB::hasTable(self::TABLE)) {
        // Already installed: this is the upgrade path, not the create path. Each step
        // is guarded by its own condition, so an installation that skipped a release
        // converges too.
        $old = \WDB::query("SHOW COLUMNS FROM `" . self::TABLE . "` LIKE 'ticket_id'");
        if ($old && \WDB::getAssoc($old))
            \WDB::exec("ALTER TABLE `" . self::TABLE . "` CHANGE `ticket_id` `owner_id` INT UNSIGNED NOT NULL DEFAULT 0");

        return;
    }

    $created = \WDB::exec('CREATE TABLE `' . self::TABLE . '` (
        `id`        INT UNSIGNED NOT NULL AUTO_INCREMENT,
        `owner_id`  INT UNSIGNED NOT NULL DEFAULT 0,
        `status`    VARCHAR(32)  NOT NULL DEFAULT "",
        `ctime`     DATETIME     NOT NULL,
        PRIMARY KEY (`id`),
        KEY `owner_id` (`owner_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');

    // exec() swallows the error, so ask for it rather than assuming success.
    if (!\WDB::hasTable(self::TABLE))
        throw new \Exception('Table could not be created: ' . \WDB::getErrorMessage());

    $this->seed();
}

private function check_columns(): void
{
    // Columns a later version introduced, table => column => definition.
    $columns = [
        self::TABLE => ['provider' => 'VARCHAR(30) NOT NULL DEFAULT ""'],
    ];

    foreach ($columns as $table => $definitions) {
        if (!\WDB::hasTable($table)) continue;

        foreach ($definitions as $column => $definition) {
            $stmt = \WDB::query("SHOW COLUMNS FROM `{$table}` LIKE '{$column}'");
            if ($stmt && \WDB::getAssoc($stmt)) continue;

            \WDB::exec("ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}");
        }
    }
}

private function seed(): void
{
    // Starting rows go in ONCE. An operator who disabled and re-enabled the module
    // must not get duplicates, and must not lose their own edits.
    $stmt  = \WDB::select('COUNT(id) AS total')->from(self::TABLE);
    $total = $stmt->build() ? (int) (($stmt->getAssoc() ?: [])['total'] ?? 0) : 0;
    if ($total > 0) return;

    \WDB::insert(self::TABLE, ['owner_id' => 0, 'status' => 'ready', 'ctime' => \DateManager::Now()]);
}

// A throwaway script: read the shape, run the step twice, read it again. The claim
// "it is safe to run again" is only worth anything once the two readings agree.
$module = Modules::getInstance('Addons', 'AcmeScanner');

$shape = static function (): array {
    $rows = WDB::query('SHOW COLUMNS FROM `Acme_scans`');
    $cols = $rows ? WDB::fetch_assoc($rows) : [];

    $count = WDB::select('COUNT(id) AS total')->from('Acme_scans');

    return [
        'columns' => array_column($cols, 'Field'),
        'rows'    => $count->build() ? (int) (($count->getAssoc() ?: [])['total'] ?? 0) : 0,
    ];
};

$module->enable();
$first = $shape();

$module->enable();
$second = $shape();

echo $first === $second ? "idempotent\n" : "DIVERGED\n";