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: 527: 528: 529: 530: 531:
<?php declare(strict_types=1);
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
{
const DEFAULT_CONCURRENCY_LIMIT = 20;
const MIN_PASSWORD_LENGTH = 5;
const MIN_PREFIX_LENGTH = 3;
const MASTER_USER = 'root';
const DB_BIN2TXT_MULT = 1.5;
private static $mysql_admin_pass;
protected $_tempUsers = [];
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;
}
protected static function pedantize($name): string
{
return DatabaseCommon::canonicalizeBrand($name);
}
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;
}
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;
}
protected function installDatabaseService(string $svc): bool
{
if ($svc !== 'pgsql' && $svc !== 'mysql') {
return error("unknown database service `%s'", $svc);
}
$conf = $this->getAuthContext()->getAccount()->new;
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;
}
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) {
$hosts = $helper::hasHosts() ? array_keys($tmp) : ['localhost'];
if ($user === $admin) {
$delete = $hosts;
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) {
$helper::uninstallPostHook($admin, $host);
}
return true;
}
protected function _register_temp_user($user)
{
$this->_tempUsers[] = $user;
return true;
}
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;
}
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;
}
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;
}
$tmpdir = tempnam($this->domain_fs_path() . '/tmp', 'db');
unlink($tmpdir);
$ret = $this->file_extract($file, $this->file_unmake_path($tmpdir), true);
if (!$ret) {
return error("failed to extract archive `%s'", $file);
}
Filesystem::chogp($tmpdir, 'root', 'root', 0600);
$files = Filesystem::readdir($tmpdir, static function ($f) use ($tmpdir) {
return filetype($tmpdir .'/' . $f) === 'file' ? $f : null;
});
if (!$files) {
return error('empty archive');
}
if (\count($files) > 1) {
warn("Multiple files found in archive - using first found: %s", $files[0]);
}
$unlink = $this->file_unmake_path($tmpdir);
return $tmpdir . '/' . array_pop($files);
}
protected function _postImport($file)
{
if (!\is_null($file)) {
return $this->file_delete($file, true);
}
return true;
}
}