1:   2:   3:   4:   5:   6:   7:   8:   9:  10:  11:  12:  13:  14:  15:  16:  17:  18:  19:  20:  21:  22:  23:  24:  25:  26:  27:  28:  29:  30:  31:  32:  33:  34:  35:  36:  37:  38:  39:  40:  41:  42:  43:  44:  45:  46:  47:  48:  49:  50:  51:  52:  53:  54:  55:  56:  57:  58:  59:  60:  61:  62:  63:  64:  65:  66:  67:  68:  69:  70:  71:  72:  73:  74:  75:  76:  77:  78:  79:  80:  81:  82:  83:  84:  85:  86:  87:  88:  89:  90:  91:  92:  93:  94:  95:  96:  97:  98:  99: 100: 101: 102: 103: 104: 105: 106: 107: 108: 109: 110: 111: 112: 113: 114: 115: 116: 117: 118: 119: 120: 121: 122: 123: 124: 125: 126: 127: 128: 129: 130: 131: 132: 133: 134: 135: 136: 137: 138: 139: 140: 141: 142: 143: 144: 145: 146: 147: 148: 149: 150: 151: 152: 153: 154: 155: 156: 157: 158: 159: 160: 161: 162: 163: 164: 165: 166: 167: 168: 169: 170: 171: 172: 173: 174: 175: 176: 177: 178: 179: 180: 181: 182: 183: 184: 185: 186: 187: 188: 189: 190: 191: 192: 193: 194: 195: 196: 197: 198: 199: 200: 201: 202: 203: 204: 205: 206: 207: 208: 209: 210: 211: 212: 213: 214: 215: 216: 217: 218: 219: 220: 221: 222: 223: 224: 225: 226: 227: 228: 229: 230: 231: 232: 233: 234: 235: 236: 237: 238: 239: 240: 241: 242: 243: 244: 245: 246: 247: 248: 249: 250: 251: 252: 253: 254: 255: 256: 257: 258: 259: 260: 261: 262: 263: 264: 265: 266: 267: 268: 269: 270: 271: 272: 273: 274: 275: 276: 277: 278: 279: 280: 281: 282: 283: 284: 285: 286: 287: 288: 289: 290: 291: 292: 293: 294: 295: 296: 297: 298: 299: 300: 301: 302: 303: 304: 305: 306: 307: 308: 309: 310: 311: 312: 313: 314: 315: 316: 317: 318: 319: 320: 321: 322: 323: 324: 325: 326: 327: 328: 329: 330: 331: 332: 333: 334: 335: 336: 337: 338: 339: 340: 341: 342: 343: 344: 345: 346: 347: 348: 349: 350: 351: 352: 353: 354: 355: 356: 357: 358: 359: 360: 361: 362: 363: 364: 365: 366: 367: 368: 369: 370: 371: 372: 373: 374: 375: 376: 377: 378: 379: 380: 381: 382: 383: 384: 385: 386: 387: 388: 389: 390: 391: 392: 393: 394: 395: 396: 397: 398: 399: 400: 401: 402: 403: 404: 405: 406: 407: 408: 409: 410: 411: 412: 413: 414: 415: 416: 417: 418: 419: 420: 421: 422: 423: 424: 425: 426: 427: 428: 429: 430: 431: 432: 433: 434: 435: 436: 437: 438: 439: 440: 441: 442: 443: 444: 445: 446: 447: 448: 449: 450: 451: 452: 453: 454: 455: 456: 457: 458: 459: 460: 461: 462: 463: 464: 465: 466: 467: 468: 469: 470: 471: 472: 473: 474: 475: 476: 477: 478: 479: 480: 481: 482: 483: 484: 485: 486: 487: 488: 489: 490: 491: 492: 493: 494: 495: 496: 497: 498: 499: 500: 501: 502: 503: 504: 505: 506: 507: 508: 509: 510: 511: 512: 513: 514: 515: 516: 517: 518: 519: 520: 521: 522: 523: 524: 525: 526: 
<?php declare(strict_types=1);

/**
 * Copyright (C) Apis Networks, Inc - All Rights Reserved.
 *
 * Unauthorized copying of this file, via any medium, is
 * strictly prohibited without consent. Any dissemination of
 * material herein is prohibited.
 *
 * For licensing inquiries email <licensing@apisnetworks.com>
 *
 * Written by Matt Saladna <matt@apisnetworks.com>, May 2017
 */

namespace Module\Support;

use ArgumentError;
use Exception;
use Module_Skeleton;
use MySQL;
use Opcenter\Database\DatabaseCommon;
use Opcenter\Filesystem;
use Regex;
use Util_Process_Safe;

abstract class Sql extends Module_Skeleton implements \Opcenter\Contracts\Hookable
{
    // default number of concurrent connections to permit per user

    const DEFAULT_CONCURRENCY_LIMIT = 20;

    const MIN_PASSWORD_LENGTH = 5;

    /**
     * @var int minimum db prefix length, to reduce collisions on server xfers
     */
    const MIN_PREFIX_LENGTH = 3;

    const MASTER_USER = 'root';

    /**
     * a bullshit constant to decide whether to
     * up the ulimit fsize or not before exporting a db
     */
    const DB_BIN2TXT_MULT = 1.5;

    // @var array Temporarily created users
    private static $mysql_admin_pass;
    protected $_tempUsers = [];

    /**
     * Rename databases on prefix change
     *
     * @param string $prefixold
     * @param string $prefixnew
     * @return int|bool # databases changed or false on failure
     */
    protected function renameDatabasePrefix($prefixold, $prefixnew)
    {
        $ctr = $this->renameDatabasePrefixByType('mysql', $prefixold, $prefixnew);
        if ($ctr !== false && $this->enabled('pgsql')) {
            $ctr = $this->renameDatabasePrefixByType('pgsql', $prefixold, $prefixnew);
        }

        return $ctr;
    }

    protected function renameDatabasePrefixByType($type, $prefixold, $prefixnew): bool
    {
        $class = '\Opcenter\Database\\' . self::pedantize($type);
        $changed = 0;
        $dbs = $this->{"${type}_list_databases"}();
        foreach ($dbs as $db) {
            $new = DatabaseCommon::convertPrefix($db, $prefixold, $prefixnew);
            if ($new === $db) {
                continue;
            }
            if ($class::moveDatabase($db, $new)) {
                $changed++;
            }
        }

        return (bool)$changed;
    }

    /**
     * Convert shorthand to formal name
     *
     * @param string $name
     * @return string pedantic name of database
     */
    protected static function pedantize($name): string
    {
        return DatabaseCommon::canonicalizeBrand($name);
    }

    /**
     * Rename secondary database users
     *
     * @param $prefixold
     * @param $prefixnew
     * @return int|bool # users changed or false on failure
     */
    protected function renameUserPrefix($prefixold, $prefixnew)
    {
        return $this->sqlWrapper('renameUserByType', $prefixold, $prefixnew);
    }

    private function sqlWrapper(string $fn, ...$args)
    {
        foreach (['mysql', 'pgsql'] as $sql) {
            if (!$this->enabled($sql)) {
                continue;
            }
            if (!$this->$fn($sql, ...$args)) {
                return false;
            }
        }

        return true;
    }

    protected function renameUser(string $old, string $new): bool
    {
        return (bool)$this->sqlWrapper('renameUserByType', $old, $new);
    }

    protected function renameUserPrefixByType($type, $prefixold, $prefixnew): int
    {
        $changed = 0;
        $users = $this->{"list_${type}_users"}();
        foreach (array_keys($users) as $user) {
            $new = $new = DatabaseCommon::convertPrefix($user, $prefixold, $prefixnew);
            if ($new === $user) {
                continue;
            }
            if ($this->renameUserByType($type, $user, $new)) {
                $changed++;
            }
        }

        return $changed;
    }

    /**
     * Rename database user
     *
     * @param string $type
     * @param string $old
     * @param string $new
     * @return bool
     */
    protected function renameUserByType(string $type, string $old, string $new): bool
    {
        $class = '\Opcenter\Database\\' . self::pedantize($type);
        $ret = true;
        if ($class::userExists($old)) {
            $ret = $class::renameUser($old, $new);
            if ($type === 'pgsql' && $new === ($user = $this->getServiceValue('pgsql', 'dbaseadmin'))) {
                info("Updating PostgreSQL password for user `%s'", $user);
            }
        }

        return $ret;
    }

    /**
     * Install database service
     *
     * @param string $svc
     * @return null|bool
     */
    protected function installDatabaseService(string $svc): bool
    {
        if ($svc !== 'pgsql' && $svc !== 'mysql') {
            return error("unknown database service `%s'", $svc);
        }

        $conf = $this->getAuthContext()->getAccount()->new;
        // @todo separate mysql/pgsql configuration
        if (null === ($passwd = array_get($conf, 'mysql.passwd'))) {
            if ($tmp = $this->mysql_get_option('password', 'client')) {
                $passwd = $tmp;
            } else {
                $passwd = \Opcenter\Auth\Password::generate(32);
            }
        }
        $domain = $conf['siteinfo']['domain'];
        $proc = new Util_Process_Safe();
        $proc->setEnvironment('HOME', '/root');
        $ret = $proc->run('/usr/local/sbin/add%s-nodb.sh %s %s',
            $svc, $domain, $passwd);
        if (!$ret['success']) {
            return error("failed to add %s for site `%s'",
                $svc, $domain);
        }

        return true;
    }

    /**
     * Delete all databases from account
     *
     * @param string $svc service
     * @return bool
     */
    protected function uninstallDatabaseService(string $svc): ?bool
    {
        if (!$this->enabled($svc)) {
            return error("database service `%s' not enabled", $svc);
        }
        $helper = '\\Opcenter\\Database\\' . self::pedantize($svc);
        $dbs = $this->{$svc . '_list_databases'}();
        foreach ($dbs as $db) {
            if ($this->{$svc. '_database_exists'}($db)) {
                $fn = "${svc}_delete_database";
                $this->$fn($db);
            }
        }
        $users = $this->{$svc . '_list_users'}();
        $admin = $this->getServiceValue('mysql', 'dbaseadmin');
        $delete = [];
        foreach ($users as $user => $tmp) {
            /**
             * @todo mysql and postgresql should return a consistent
             */
            $hosts = $helper::hasHosts() ? array_keys($tmp) : ['localhost'];
            if ($user === $admin) {
                $delete = $hosts;
                /**
                 * admin user is a special case and needs to be removed last
                 */
                continue;
            }
            foreach ($hosts as $host) {
                if (!$this->{$svc . '_user_exists'}($user, $host)) {
                    continue;
                }
                $fn = "${svc}_delete_user";
                $this->$fn($user, $host);
            }
        }

        foreach ($delete as $host) {
            $helper::deleteMainUser($admin, $host);
        }

        if ($delete) {
            // if we enumerate and come across the admin user as properly provisioned
            // then let's call delete otherwise skip
            $helper::uninstallPostHook($admin, $host);
        }

        return true;
    }

    /**
     * Add temporary *sql user for cleanup
     *
     * @param string $user
     * @return bool
     */

    protected function _register_temp_user($user)
    {
        $this->_tempUsers[] = $user;

        return true;
    }

    /**
     * Establish privileged connection to MySQL server
     *
     * @param bool $pdo return as PDO
     * @return \MySQL|\PDO
     */
    protected function _connect_root(bool $pdo = false)
    {
        if (!$pdo) {
            $conn = new \MySQL('localhost', self::MASTER_USER, $this->_get_elevated_password());
            $conn->select_db('mysql');
            $conn->set_charset("utf8mb4");
            return $conn;
        }

        return new \PDO('mysql:host=localhost;dbname=mysql;charset=utf8mb4', self::MASTER_USER, $this->_get_elevated_password());
    }

    protected function _get_elevated_password()
    {
        if (self::$mysql_admin_pass === null) {
            self::$mysql_admin_pass = $this->query('mysql_get_elevated_password_backend');
        }

        return self::$mysql_admin_pass;
    }

    protected function add_backup_real($type, $db, $extension, $span, $preserve, $email)
    {
        if (!$preserve) {
            $preserve = '0';
        }
        if ($type !== 'mysql' && $type !== 'pgsql') {
            return error("invalid datanase type `%s'", $type);
        }
        $fn = "${type}_list_databases";
        $dbs = $this->$fn();

        if (!in_array($db, $dbs, true)) {
            return error('invalid database ' . $db);
        }
        if (!in_array($extension, array('gz', 'bz', 'zip', 'none'))) {
            return error('Invalid extension');
        }

        if (intval($span) != $span || intval($preserve) != $preserve) {
            return error('Non-numeric type for day span/preservation amount');
        } else if ($span < 1) {
            return error('Day span value must be > 0');
        } else if (intval($email) != $email && !preg_match(Regex::EMAIL, $email)) {
            return error('Invalid e-mail address');
        }
        $dbconn = MySQL::initialize();
        $q = 'INSERT INTO
                    sql_dumps
                        (site_id,
                         db_type,
                         db_name,
                         day_span,
                         extension,
                         preserve,
                         next_date,

                         email)
                 VALUES
                        (' . $this->site_id . ",
                         '" . $type . "',
                         '" . str_replace('\\', '', $db) . "',
                         " . $span . ",
                         '" . $extension . "',
                         " . $preserve . ",
                         NOW(),
                         '" . $email . "')
                 ON DUPLICATE KEY UPDATE
                    day_span  = " . $span . ",
                    extension = '" . $extension . "',
                    preserve  =  " . $preserve . ",
                    email     =  '" . $email . "';";
        try {
            $q = $dbconn->query($q);
        } catch (Exception $e) {
            return error('general error setting backup routine');
        }

        return true;
    }

    /**
     * Verify if service is enabled
     *
     * @param $type
     * @return mixed|NULL
     */
    protected function svc_enabled($type)
    {
        return $this->getServiceValue($type, 'enabled');
    }

    protected function edit_backup_real($type, $db, $extension, $span, $preserve, $email)
    {
        if (!$preserve) {
            $preserve = '0';
        }
        if ($type !== 'mysql' && $type !== 'pgsql') {
            return new ArgumentError('Invalid type ' . $type);
        }
        if ($type === 'mysql') {
            $dbs = $this->list_databases();
        } else {
            $dbs = $this->list_pgsql_databases();
        }
        if (!in_array($db, $dbs, true)) {
            return error("invalid database `%s'", $db);
        }

        if (!in_array($extension, array('gz', 'bz', 'zip', 'none'))) {
            return error("unrecognized extension `%s'", $extension);
        } else if (!ctype_digit((string)$span) || !ctype_digit((string)$preserve)) {
            return error('non-numeric type for day span/preservation amount');
        } else if ($email && !preg_match(Regex::EMAIL, $email)) {
            return error('invalid e-mail address');
        }
        $dbconn = MySQL::initialize();
        $q = $dbconn->query("UPDATE
                                    sql_dumps
                                SET
                                    extension = '" . $extension . "',
                                    email = '" . $email . "',
                                    day_span = " . (!$span ? 'NULL' : $span) . ',
                                    preserve = ' . $preserve . ',
                                    next_date = ' . ($span > 1 ? 'DATE_ADD(NOW(), INTERVAL ".$span." DAY)' : 'NOW()') . "
                                WHERE
                                        db_type = '" . $type . "'
                                    AND
                                        db_name = '" . $db . "'
                                    AND
                                        site_id = " . $this->site_id . ';');

        return $dbconn->affected_rows() > 0;

    }

    /**
     * Wrapper to list backups for db type
     *
     * @param string $type
     * @return ArgumentError|array
     */
    protected function list_backups_real(string $type)
    {
        if ($type !== 'pgsql' && $type !== 'mysql') {
            fatal("Invalid database type `%s'" . $type);
        }

        $backups = array();
        $fn = "${type}_list_databases";
        foreach ($this->$fn() as $db) {
            $task = $this->get_backup_config_real($type, $db);
            if (!$task) {
                continue;
            }
            $backups[$db] = $task;

        }

        return $backups;
    }

    protected function get_backup_config_real($type, $db)
    {
        if ($type !== 'mysql' && $type !== 'pgsql') {
            return error($type . ': unknown type');
        }
        $dbconn = MySQL::initialize();
        $q = $dbconn->query("SELECT day_span,
                                             preserve,
                                             UNIX_TIMESTAMP(next_date) AS next_date,
                                             extension,
                                             email
                                     FROM sql_dumps
                                     WHERE db_type = '" . $type . "'
                                     AND site_id = " . $this->site_id . "
                                     AND db_name = '" . $db . "'");

        if ($q->num_rows < 1) {
            return false;
        }

        $row = $q->fetch_object();

        return array(
            'span'      => $row->day_span,
            'hold'      => $row->preserve,
            'next'      => $row->next_date,
            'extension' => $row->extension,
            'email'     => $row->email
        );
    }

    protected function delete_backup_real($type, $db)
    {
        if ($type !== 'mysql' && $type !== 'pgsql') {
            return error('Invalid type ' . $type);
        }
        $dbconn = \MySQL::initialize();
        $rs = $dbconn->query('DELETE FROM sql_dumps WHERE site_id = ' . $this->site_id
            . " AND db_type = '" . $type . "' AND db_name = '"
            . $dbconn->escape_string($db) . "';"
        );

        return $rs && $dbconn->affected_rows > 0;
    }

    protected function _escape($str)
    {
        return str_replace('_', '\_', $str);
    }

    protected function _preImport($file, &$unlink)
    {
        $realfile = $this->file_make_path($file);
        if (!file_exists($realfile)) {
            return error("file `%s' does not exist", $file);
        }

        if (!$this->file_is_compressed($file)) {
            return $realfile;
        }
        $fname = tempnam($this->domain_fs_path() . '/tmp', 'db');
        unlink($fname);
        $ret = $this->file_extract($file, $this->file_unmake_path($fname), true);
        if (!$ret) {
            return error("failed to extract archive `%s'", $file);
        }
        $files = glob($fname . '/*');
        if (!$files) {
            return error('empty archive');
        }
        // shouldn't be necessary to kill open file handles; import is non-privileged user
        // definer remap must match user on account
        Filesystem::chogp($fname, 'root', 'root', 0600);
        $unlink = $this->file_unmake_path($fname);

        return array_pop($files);

    }

    protected function _postImport($file)
    {
        if (!is_null($file)) {
            return $this->file_delete($file, true);
        }

        return true;
    }
}