command.php

#!/usr/bin/php
<?php
require_once __DIR__.'/Console/CommandLine.php';
require_once __DIR__.'/Console/CommandLine/Option.php';
require_once __DIR__.'/Console/CommandLine/Command.php';
//require_once __DIR__.'/../Config.php';
//use SmartData\Config;

final class Config
{
const MYSQL_SEVERNAME = 'localhost';
const MYSQL_PORT      = 3306;
const MYSQL_DBNAME    = 'smartdata_v1';
const MYSQL_USERNAME  = 'smartdata';
const MYSQL_PASSWORD  = '1ol31lSh010mG9wY';

//const MYSQL_SUPERUSER = 'smartdata_admin';
//const MYSQL_SUPERPASS = '6wg5fbB5fOIuzXcW';
const MYSQL_SUPERUSER = 'root';
const MYSQL_SUPERPASS = 'MC7Q9UZOCdfcEb5a';

const CASSANDRA_SERVERNAME   = 'localhost';
const CASSANDRA_PORT         = 9042;
const CASSANDRA_KEYSPACE     = 'smartdata_v1';
const CASSANDRA_MAX_ROW_SIZE = 2000000000; //%(Two billion - The max is 2^31)

const CASSANDRA_SUPERUSER = 'cassandra';
const CASSANDRA_SUPERPASS = '12a06-fs';
}

$parser = new Console_CommandLine(array(
    'description' => 'description description description description.',
    'version'     => '1.0.0',
    //'subcommand_required' => true,
    //'add_help_option' => false,
    'add_version_option' => false,
    'force_options_defaults' => true,
    //'force_posix' => true,
));


/**
 * Create Command
 */
$create_cmd = $parser->addCommand('create', array('description' => 'create a new domain, or..', 'subcommand_required' => true));
$create_domain_cmd = $create_cmd->addCommand('domain');
$create_domain_cmd->addArgument('domain', array('description' => '', 'help_name' => 'DOMAIN'));
$create_domain_cmd->addOption('type', array('description'     => '(optional) versions to be created [default=both]',
                                            'short_name'      => '-t',
                                            'long_name'       => '--type',
                                            'help_name'       => 'TYPE',
                                            'default'         => 'both',
                                            'choices'         => array('si', 'digital', 'both'),
                                            'add_list_option' => true));

/**
 * Delete Command
 */
$delete_cmd = $parser->addCommand('delete', array('description' => 'delete a domain, or..', 'subcommand_required' => true));
$delete_domain_cmd = $delete_cmd->addCommand('domain');
$delete_domain_cmd->addArgument('domain', array('description' => '', 'help_name' => 'DOMAIN'));


/**
 * List Command
 */
$list_cmd = $parser->addCommand('list', array('description' => 'list domains, or..', 'subcommand_required' => true));
$list_domain_cmd = $list_cmd->addCommand('domains');
$list_domain_cmd->addOption('follow', array('short_name' => '-f',
                                            'long_name'  => '--follow',
                                            'action'     => 'StoreTrue'));

/**
 * Delete Command
 */
$clear_cmd = $parser->addCommand('clear', array('description' => 'clear a domain', 'subcommand_required' => true));
$clear_domain_cmd = $clear_cmd->addCommand('domain');
$clear_domain_cmd->addArgument('domain', array('description' => '', 'help_name' => 'DOMAIN'));


/**
 * Set Command
 */
$set_cmd = $parser->addCommand('set', array('description' => 'set configuration', 'subcommand_required' => true));
$set_cmd->addArgument('domain', array('description' => '', 'help_name' => 'DOMAIN'));
$set_cmd->addArgument('level', array('description' => '', 'help_name' => 'LEVEL'));

class Maintenance
{
    public function __construct(){}

    private static function _create_domain(string $name, string $type)
    {
        $ddl = file_get_contents('sql/domain.sql', FILE_TEXT);
        $ret = false;

        $_ddl = str_replace(":type", $type, $ddl);
        $_ddl = str_replace(":name", $name.'_'.$type, $_ddl);
        try {
            $session = self::_cassandraConnect();
            $stmt = $session->prepare($_ddl);
            $session->execute($stmt);
            $ret = true;
        } catch (Cassandra\Exception\AlreadyExistsException $e) {
            echo "The {$name} domain already exists.\n";
        } catch (\Exception $e) {
            die ($e);
        } finally {
            if (isset($session))
                $session->close();
        }
        return $ret;
    }

    public static function create_domain(string $name, string $type) {
        if(!self::is_valid_name($name))
            die ("Invalid name");

        self::_create_domain($name, 'v1_1_si');
        self::_create_domain($name, 'v1_1_digital');
        self::_create_domain($name, 'v1_2_si');
        self::_create_domain($name, 'v1_2_digital');
        self::create_counter($name);
    }

    private static function create_counter(string $name) {
        $dml = "INSERT INTO counters VALUES (:name, 0);";
        try {
            $conn = self::_mysqlConnect();
            $stmt = $conn->prepare($dml);
            $stmt->execute(array(':name' => $name));
        } catch (\Exception $e) {
            die ($e);
        } finally {
            $conn = null;
        }
    }

    public static function delete_domain(string $name) {
        if(!self::is_valid_name($name))
            die ("Invalid name");

        $ddl1 = 'DROP TABLE IF EXISTS :name_v1_1_si;';
        $ddl2 = 'DROP TABLE IF EXISTS :name_v1_1_digital;';
        $ddl3 = 'DROP TABLE IF EXISTS :name_v1_2_si;';
        $ddl4 = 'DROP TABLE IF EXISTS :name_v1_2_digital;';
        $ddl1 = str_replace(":name", $name, $ddl1);
        $ddl2 = str_replace(":name", $name, $ddl2);
        $ddl3 = str_replace(":name", $name, $ddl3);
        $ddl4 = str_replace(":name", $name, $ddl4);

        do {
            $r = strtoupper(readline("The [{$name}] domain will be deleted. Are you sure (Y/N)? "));
            if($r === 'N' | $r === 'n')
                return;
            else
                break;
        } while (true);

        try {
            $session = self::_cassandraConnect();
            $session->execute($session->prepare($ddl1));
            $session->execute($session->prepare($ddl2));
            $session->execute($session->prepare($ddl3));
            $session->execute($session->prepare($ddl4));
            self::delete_counter($name);
        } catch (\Exception $e){
            die ($e);
        } finally {
            if (isset($session))
                $session->close();
        }
    }

    public static function set_public(string $name) {
        if(!self::is_valid_name($name))
            die ("Invalid name");

        $ddl1 = 'GRANT SELECT ON :name_v1_1_si TO public;';
        $ddl2 = 'GRANT SELECT ON :name_v1_1_digital TO public;';
        $ddl3 = 'GRANT SELECT ON :name_v1_2_si TO public;';
        $ddl4 = 'GRANT SELECT ON :name_v1_2_digital TO public;';
        $ddl1 = str_replace(":name", $name, $ddl1);
        $ddl2 = str_replace(":name", $name, $ddl2);
        $ddl3 = str_replace(":name", $name, $ddl3);
        $ddl4 = str_replace(":name", $name, $ddl4);

        do {
            $r = strtoupper(readline("The [{$name}] domain will became public. Are you sure (Y/N)? "));
            if($r === 'N' | $r === 'n')
                return;
            else
                break;
        } while (true);

        try {
            $session = self::_cassandraConnect();
            $session->execute($session->prepare($ddl1));
            $session->execute($session->prepare($ddl2));
            $session->execute($session->prepare($ddl3));
            $session->execute($session->prepare($ddl4));
            self::delete_counter($name);
        } catch (\Exception $e){
            die ($e);
        } finally {
            if (isset($session))
                $session->close();
        }
    }

    public static function set_private(string $name) {
        if(!self::is_valid_name($name))
            die ("Invalid name");

        $ddl1 = 'REVOKE SELECT ON :name_v1_1_si FROM public;';
        $ddl2 = 'REVOKE SELECT ON :name_v1_1_digital FROM public;';
        $ddl3 = 'REVOKE SELECT ON :name_v1_2_si FROM public;';
        $ddl4 = 'REVOKE SELECT ON :name_v1_2_digital FROM public;';
        $ddl1 = str_replace(":name", $name, $ddl1);
        $ddl2 = str_replace(":name", $name, $ddl2);
        $ddl3 = str_replace(":name", $name, $ddl3);
        $ddl4 = str_replace(":name", $name, $ddl4);

        do {
            $r = strtoupper(readline("The [{$name}] domain will became private. Are you sure (Y/N)? "));
            if($r === 'N' | $r === 'n')
                return;
            else
                break;
        } while (true);

        try {
            $session = self::_cassandraConnect();
            $session->execute($session->prepare($ddl1));
            $session->execute($session->prepare($ddl2));
            $session->execute($session->prepare($ddl3));
            $session->execute($session->prepare($ddl4));
            self::delete_counter($name);
        } catch (\Exception $e){
            die ($e);
        } finally {
            if (isset($session))
                $session->close();
        }
    }

    private static function delete_counter(string $name) {
        $dml = "DELETE FROM counters WHERE domain = :name;";
        try {
            $conn = self::_mysqlConnect();
            $stmt = $conn->prepare($dml);
            $stmt->execute(array(':name' => $name));
        } catch (\Exception $e) {
            die ($e);
        } finally {
            $conn = null;
        }
    }

    private static function reset_counter(string $name) {
        $dml = "UPDATE counters SET count = 0 WHERE domain = :name;";
        try {
            $conn = self::_mysqlConnect();
            $stmt = $conn->prepare($dml);
            $stmt->execute(array(':name' => $name));
        } catch (\Exception $e) {
            die ($e);
        } finally {
            $conn = null;
        }
    }

    public static function list_domains($follow = false) {
        try {
            $conn = self::_mysqlConnect();
            $sth = $conn->prepare('SELECT domain as "Domain", max(updated_at) as "Last Update" FROM series GROUP BY domain;');
            do {
                $sth->execute();
                $result = $sth->fetchAll(PDO::FETCH_ASSOC);

                $table = self::table($result);

                if($follow) system('clear');
                echo $table;
                if($follow) sleep(1);

            } while($follow);
        } catch (\Exception $e) {
            die ($e);
        } finally {
            $conn = null;
        }
    }

    public static function clear_domain(string $name) {
        if(!self::is_valid_name($name))
            die ("Invalid name");

        $ddl1 = 'TRUNCATE TABLE :name;';
        $ddl2 = 'TRUNCATE TABLE :name_dig;';
        $ddl1 = str_replace(":name", $name, $ddl1);
        $ddl2 = str_replace(":name", $name, $ddl2);

        do {
            $r = strtoupper(readline("The [{$name}] domain will be cleaned. Are you sure (Y/N)? "));
            if($r === 'N' | $r === 'n')
                return;
            else
                break;
        } while (true);

        try {
            $session = self::_cassandraConnect();
            $stmt1 = $session->prepare($ddl1);
            $stmt2 = $session->prepare($ddl2);
            $session->execute($stmt1);
            $session->execute($stmt2);
            self::reset_counter($name);
        } catch (\Exception $e){
            die ($e);
        } finally {
            if (isset($session))
                $session->close();
        }
    }

    private static function is_valid_name($name) : bool {
        return preg_match('/^[a-zA-Z_][a-zA-Z0-9_]{3,39}$/', $name) ? true : false;
    }

    private static function table($data) {
        // Find longest string in each column
        $columns = [];
        foreach ($data as $row_key => $row) {
            foreach ($row as $cell_key => $cell) {
                $length = strlen($cell);
                if (empty($columns[$cell_key]) || $columns[$cell_key] < $length) {
                    $columns[$cell_key] = $length;
                }
            }
        }

        // Output table, padding columns
        $line = '+';
        foreach ($data[0] as $cell_key => $cell)
            $line .= str_pad('', $columns[$cell_key]+2, '-').'+';
        $line .= PHP_EOL;

        $table = $line;

        foreach ($data[0] as $cell_key => $cell)
            $table .= '| '.str_pad($cell_key, $columns[$cell_key]+1);
        $table .= '|'.PHP_EOL.$line;

        foreach ($data as $row_key => $row) {
            foreach ($row as $cell_key => $cell)
                $table .= '| '.str_pad($cell, $columns[$cell_key]+1);
            $table .= '|'.PHP_EOL;
        }
        $table .= $line;
        return $table;
    }

    private static function _mysqlConnect(string $servername=Config::MYSQL_SEVERNAME, int $port=Config::MYSQL_PORT, string $dbname=Config::MYSQL_DBNAME, string $username=Config::MYSQL_SUPERUSER, string $password=Config::MYSQL_SUPERPASS) : \PDO {
        $conn = new \PDO("mysql:host=$servername;port=$port;dbname=$dbname", $username, $password);
        // set the PDO error mode to exception
        $conn->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

        return $conn;
    }

    // Open a connection with Cassandra database
    private static function _cassandraConnect(string $servername=Config::CASSANDRA_SERVERNAME, int $port=Config::CASSANDRA_PORT,
                                                string $username=Config::CASSANDRA_SUPERUSER,    string $password=Config::CASSANDRA_SUPERPASS,
                                                string $keyspace=Config::CASSANDRA_KEYSPACE) : \Cassandra\DefaultSession {

        $cluster = \Cassandra::cluster()->withContactPoints($servername)
                                        ->withPort($port)
                                        ->withCredentials($username, $password)
                                        //->withDefaultPageSize(200)
                                        ->build();
        $session = $cluster->connect($keyspace);
        return $session;
    }

}

try {
    $result = $parser->parse();

    if($result->command_name){
        switch ($result->command_name) {
            case 'create':
		if($result->command->command_name){
                    switch ($result->command->command_name) {
                        case 'domain':
                            $domain = $result->command->command->args['domain'];
                            $type = $result->command->command->options['type'];
                            Maintenance::create_domain($domain, $type);
                        break;

                        default:
                        break;
                    }
                }
            break;
            case 'delete':
                if($result->command->command_name){
                    switch ($result->command->command_name) {
                        case 'domain': Maintenance::delete_domain($result->command->command->args['domain']);
                            break;
                        default:
                            break;
                    }
                }
            break;

            case 'list':
                if($result->command->command_name){
                    switch ($result->command->command_name) {
                        case 'domains':
                            Maintenance::list_domains((bool)$result->command->command->options['follow']);
                        break;

                        default:
                            break;
                    }
                }
            break;

            case 'clear':
                if($result->command->command_name){
                    switch ($result->command->command_name) {
                        case 'domain': Maintenance::clear_domain($result->command->command->args['domain']);
                            break;
                        default:
                            break;
                    }
                }
            break;

            case 'set':
                $domain = $result->command->args['domain'] ?? null;
                $level = $result->command->args['level'] ?? null;

                if($domain != null){
                    switch ($level) {
                        case 'public': Maintenance::set_public($domain);
                            break;
                        case 'private': Maintenance::set_private($domain);
                            break;
                        default:
                            break;
                    }
                }
            break;

            default:
            break;
        }
    } else {
        echo $parser->displayUsage(1);
    }

    //echo "\n-\n";
    //print_r($result);
    //print_r($result->options);
    //print_r($result->args);
} catch (Exception $exc) {
    $parser->displayError($exc->getMessage());
}