command.php

#!/usr/bin/php
<?php
require_once './Console/CommandLine.php';
require_once './Console/CommandLine/Option.php';
require_once './Console/CommandLine/Command.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 CASSANDRA_SERVERNAME   = 'localhost';
 const CASSANDRA_PORT         = 9042;

 const CASSANDRA_SUPERUSER = 'cassandra';
 const CASSANDRA_SUPERPASS = 'Pdx#W63$B7-pNv'; //'K#dM841-lwx';

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

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


/**
 * Create Command
 */
$create_cmd = $parser->addCommand('create', array('description' => 'create a new domain or user, 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));
$create_domain_cmd->addOption('cert', array('description'     => '(optional) certificate name (without extension)',
                                            'short_name'      => '-c',
                                            'long_name'       => '--cert',
                                            'help_name'       => 'CERTIFICATE',
                                            'default'         => ''));

$create_domain_cmd->addOption('descr', array('description'     => '(optional) description of the domain',
                                            'short_name'      => '-d',
                                            'long_name'       => '--descr',
                                            'help_name'       => 'DESCRIPTION',
                                            'default'         => ''));

$create_user_cmd = $create_cmd->addCommand('user');
$create_user_cmd->addArgument('name', array('description' => 'user name for login', 'help_name' => 'DOMAIN'));

$create_user_cmd->addOption('domain', array('description'     => '(optional) domain in which user can manipulate smartdata',
                                            'short_name'      => '-d',
                                            'long_name'       => '--domain',
                                            'help_name'       => 'DOMAIN',
                                            'default'         => ''));
$create_user_cmd->addOption('password', array('description'     => '(required) password for authentication',
                                            'short_name'      => '-p',
                                            'long_name'       => '--password',
                                            'help_name'       => 'PASSWORD',
                                            'default'         => ''));

/**
 * 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, string $cert, string $descr) {
        if(!self::is_valid_name($name))
             die ("Invalid name");

        self::_create_domain($name, 'v1_1_si');
        self::_create_domain($name, 'v1_1_si_blob');
        self::_create_domain($name, 'v1_1_digital');
        self::_create_domain($name, 'v1_2_si');
        self::_create_domain($name, 'v1_2_si_blob');
        self::_create_domain($name, 'v1_2_digital');
        self::create_user($name, $cert, $descr);
        self::create_counter($name);
    }

    public static function create_cassandra_user(string $name, string $domain, string $pwd) {
        try {
            $perms = array('SELECT','ALTER','DROP','MODIFY','AUTHORIZE');
            $crt = "CREATE ROLE :name WITH LOGIN = true AND PASSWORD = ':pwd'";
            $crt = str_replace(":name", $name, $crt);
            $crt = str_replace(":pwd", $pwd, $crt);
            $session = self::_cassandraConnect();
            $session->execute($session->prepare($crt));
            $ddl1 = 'GRANT :perm ON smartdata_v1.:domain_v1_1_si to :name;';
            $ddl2 = 'GRANT :perm ON smartdata_v1.:domain_v1_1_si_blob to :name;';
            $ddl3 = 'GRANT :perm ON smartdata_v1.:domain_v1_1_digital to :name;';
            $ddl4 = 'GRANT :perm ON smartdata_v1.:domain_v1_2_si to :name;';
            $ddl5 = 'GRANT :perm ON smartdata_v1.:domain_v1_2_si_blob to :name;';
            $ddl6 = 'GRANT :perm ON smartdata_v1.:domain_v1_2_digital to :name;';
            foreach ($perms as $prm) {
                $c1 = str_replace(':perm', $prm, $ddl1);
                $c1 = str_replace(':domain', $domain, $c1);
                $c1 = str_replace(':name', $name, $c1);

                $c2 = str_replace(':perm', $prm, $ddl2);
                $c2 = str_replace(':domain', $domain, $c2);
                $c2 = str_replace(':name', $name, $c2);

                $c3 = str_replace(':perm', $prm, $ddl3);
                $c3 = str_replace(':domain', $domain, $c3);
                $c3 = str_replace(':name', $name, $c3);

                $c4 = str_replace(':perm', $prm, $ddl4);
                $c4 = str_replace(':domain', $domain, $c4);
                $c4 = str_replace(':name', $name, $c4);

                $c5 = str_replace(':perm', $prm, $ddl5);
                $c5 = str_replace(':domain', $domain, $c5);
                $c5 = str_replace(':name', $name, $c5);

                $c6 = str_replace(':perm', $prm, $ddl6);
                $c6 = str_replace(':domain', $domain, $c6);
                $c6 = str_replace(':name', $name, $c6);

                $session->execute($session->prepare($c1));
                $session->execute($session->prepare($c2));
                $session->execute($session->prepare($c3));
                $session->execute($session->prepare($c4));
                $session->execute($session->prepare($c5));
                $session->execute($session->prepare($c6));
           }
           return true;
           
	} catch (\Exceptions $ex) {
		echo  "Error creating user.";
                var_dump($ex);
                return false;
	}
    }


    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;
        }
    }

    private static function create_user(string $name, string $cert, string $descr) {
        $dml = "INSERT INTO clients (certificate, name, domain, level, enable) VALUES (:cert, :name, :domain, 'rw', 1);";
        try {
            $conn = self::_mysqlConnect();
            $stmt = $conn->prepare($dml);
            $stmt->execute(array(':name' => $descr, ':cert' => $cert, ':domain' => $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'];
                            $cert_file = $result->command->command->options['cert'];
                            $domain_descr = $result->command->command->options['descr'];
                            Maintenance::create_domain($domain, $type, $cert_file, $domain_descr);
                        break;

                        case 'user':
                            $name = $result->command->command->args['name'];
                            $domain = $result->command->command->options['domain'];
                            $pwd = $result->command->command->options['password'];
                            if (Maintenance::create_cassandra_user($name, $domain, $pwd)) {
                                echo "\nUser ".$name." created on domain ".$domain.".\n\n";
                            } else {
                                echo "\nAn error occured while creating user.\n\n";
                            }
                        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);
    }
} catch (Exception $exc) {
    $parser->displayError($exc->getMessage());
}