php script to update table row format

update_row_format.php

#!/usr/bin/env php
<?php
/* *
 * Database Row format update script
 * 
 * Description: This script only serves the purpose to update all tables inside a specified database to the ROW_FORMAT=DYNAMIC
 * 
 * @author Aperture Development <[email protected]>
 * @version 0.0.1
 * @license by-sa 4.0
*/
$arguments = getopt('d:u:h::', array('database:', 'username:', 'host::', 'help::'));

if(isset($arguments['help'])) {
    echo <<<HELP
DB Row format updater

Usage: update_row_format.php [options]

Options:
 -d, --database     One or more databases to be updated
 -u, --username     Username to use to connect to the database ( needs access to information_shema )
 -h, --host         Database connection string ( e.g. 127.0.0.1:3306 ) 
                    Defaults to 127.0.0.1:3306 is not specified
 --help             Displays this help message
HELP;
}

// Define update process function
function updateDatabase(PDO $infoShema, string $host, string $port, string $username, string $password, $database = null) {
    // for recursive functionality
    if(!isset($database)){return true;}

    // determine is a array or string has been provided
    if(gettype($database) === 'array') {
        if(count($database) === 0){return true;}
        $updateDb = array_pop($database);
    } else {
        $updateDb = $database;
        $database = null;
    }

    // Connect to database and load tables in
    $tempPdo = new PDO('mysql:dbname=' . $updateDb . ';host=' . $host . ';port=' . $port, $username, $password);

    $tables_q = $infoShema->prepare('SELECT table_name FROM tables WHERE table_schema=:database AND ROW_FORMAT=\'compact\'');
    $tables_q->bindParam(':database', $updateDb);
    if($tables_q->execute()) {
        $tables = $tables_q->fetchAll();
    }
    
    // Try to safely update the databases tables
    try {
        $tempPdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $tempPdo->beginTransaction();
        foreach($tables as $table) {
            echo 'Updating ' . $updateDb . '.' . $table['table_name'] . PHP_EOL;
            // Sadly due to MySQL not allowing it, I am unable to escape the table name, because it would place quotes around it which break the sql query
            $updateTable_q = $tempPdo->query('ALTER TABLE ' . $table['table_name'] . ' ROW_FORMAT=DYNAMIC');
            $updateTable_q->execute();
        }
        $tempPdo->commit();
    } catch(Exception $e) {
        echo 'Error occured, Rolling back changes' . PHP_EOL;
        echo $e->getMessage() . PHP_EOL;
        $tempPdo->rollBack();
        throw $e;
    } finally {
        $tempPdo = null;
        return updateDatabase($infoShema, $host, $port, $username, $password, $database);
    }
}

// Define function to get password
function getPassword() {
    echo 'Password: ';
    system('stty -echo');
    $password = trim(fgets(STDIN));
    system('stty echo');
    echo PHP_EOL;

    return $password;
}

try {
    // Check for required parameters
    if(!(isset($arguments['d']) || isset($arguments['database'])) || !(isset($arguments['u']) || isset($arguments['username']))) {
        throw new Exception('Required Parameters \'d\' and \'u\' have not been specified');
    }

    // Load parameters into variables
    $databases = isset($arguments['d']) ? $arguments['d'] : $arguments['database'];
    $username = isset($arguments['u']) ? $arguments['u'] : $arguments['username'];
    $dbHost = isset($arguments['h']) ? $arguments['h'] : (isset($arguments['host']) ? $arguments['host'] : '127.0.0.1:3306');

    // Check validity of data
    if(gettype($username) === 'array') {
        throw new Exception('You can not provide more then one username');
    }

    if(gettype($dbHost) === 'array') {
        throw new Exception('You can not provide more then one database host');
    } else {
        if(strlen($dbHost) === 0){
            throw new Exception('Host option appears to be empty, make sure there is no whitespace between -h/--host and your database host ( e.g. -h\'127.0.0.1:3306\' )');
        }
        $tempConn = explode(':', $dbHost);
        $host = $tempConn[0];
        $port = $tempConn[1];
    }
    
    $password = getPassword();
    $infoShema = new PDO('mysql:dbname=information_schema;host=' . $host . ';port=' . $port, $username, $password);
    $infoShema->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    exit(updateDatabase($infoShema, $host, $port, $username, $password, $databases) ? 0 : 1);
} catch(Exception $e) {
    echo $e->getMessage() . PHP_EOL;
    exit(1);
}