BookStack Code Mirror - system-cli/blob - src/Services/MySqlRunner.php

   1 <?php declare(strict_types=1);
   2
   3 namespace Cli\Services;
   4
   5 use Exception;
   6
   7 class MySqlRunner
   8 {
   9     public function __construct(
  10         protected string $host,
  11         protected string $user,
  12         protected string $password,
  13         protected string $database,
  14         protected int $port = 3306
  15     ) {
  16     }
  17
  18     /**
  19      * @throws Exception
  20      */
  21     public function ensureOptionsSet(): void
  22     {
  23         $options = ['host', 'user', 'password', 'database'];
  24         foreach ($options as $option) {
  25             if (!$this->$option) {
  26                 throw new Exception("Could not find a valid value for the \"{$option}\" database option.");
  27             }
  28         }
  29     }
  30
  31     public function testConnection(): bool
  32     {
  33         $output = (new ProgramRunner('mysql', '/usr/bin/mysql'))
  34             ->withEnvironment(['MYSQL_PWD' => $this->password])
  35             ->withTimeout(240)
  36             ->withIdleTimeout(5)
  37             ->runCapturingStdErr([
  38                 '-h', $this->host,
  39                 '-P', $this->port,
  40                 '-u', $this->user,
  41                 '--protocol=TCP',
  42                 $this->database,
  43                 '-e', "show tables;"
  44             ]);
  45
  46         return !$output;
  47     }
  48
  49     public function importSqlFile(string $sqlFilePath): void
  50     {
  51         $output = (new ProgramRunner('mysql', '/usr/bin/mysql'))
  52             ->withEnvironment(['MYSQL_PWD' => $this->password])
  53             ->withTimeout(240)
  54             ->withIdleTimeout(5)
  55             ->runCapturingStdErr([
  56                 '-h', $this->host,
  57                 '-P', $this->port,
  58                 '-u', $this->user,
  59                 '--protocol=TCP',
  60                 $this->database,
  61                 '-e', "source {$sqlFilePath}"
  62             ]);
  63
  64         if ($output) {
  65             throw new Exception("Failed mysql file import with errors:\n" . $output);
  66         }
  67     }
  68
  69     public function dropTablesSql(): string
  70     {
  71         return <<<'HEREDOC'
  72 SET FOREIGN_KEY_CHECKS = 0;
  73 SET GROUP_CONCAT_MAX_LEN=32768;
  74 SET @tables = NULL;
  75 SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables
  76   FROM information_schema.tables
  77   WHERE table_schema = (SELECT DATABASE());
  78 SELECT IFNULL(@tables,'dummy') INTO @tables;
  79
  80 SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
  81 PREPARE stmt FROM @tables;
  82 EXECUTE stmt;
  83 DEALLOCATE PREPARE stmt;
  84 SET FOREIGN_KEY_CHECKS = 1;
  85 HEREDOC;
  86     }
  87
  88     public function runDumpToFile(string $filePath): string
  89     {
  90         $file = fopen($filePath, 'w');
  91         $errors = "";
  92         $warnings = "";
  93         $hasOutput = false;
  94
  95         try {
  96             (new ProgramRunner('mysqldump', '/usr/bin/mysqldump'))
  97                 ->withTimeout(240)
  98                 ->withIdleTimeout(15)
  99                 ->withEnvironment(['MYSQL_PWD' => $this->password])
 100                 ->runWithoutOutputCallbacks([
 101                     '-h', $this->host,
 102                     '-P', $this->port,
 103                     '-u', $this->user,
 104                     '--protocol=TCP',
 105                     '--single-transaction',
 106                     '--no-tablespaces',
 107                     $this->database,
 108                 ], function ($data) use (&$file, &$hasOutput) {
 109                     fwrite($file, $data);
 110                     $hasOutput = true;
 111                 }, function ($error) use (&$errors, &$warnings) {
 112                     $lines = explode("\n", $error);
 113                     foreach ($lines as $line) {
 114                         if (str_starts_with(strtolower($line), 'warning: ')) {
 115                             $warnings .= $line;
 116                         } else {
 117                             $errors .= $line . "\n";
 118                         }
 119                     }
 120                 });
 121         } catch (\Exception $exception) {
 122             fclose($file);
 123             if ($exception instanceof ProcessTimedOutException) {
 124                 if (!$hasOutput) {
 125                     throw new Exception("mysqldump operation timed-out.\nNo data has been received so the connection to your database may have failed.");
 126                 } else {
 127                     throw new Exception("mysqldump operation timed-out after data was received.");
 128                 }
 129             }
 130             throw new Exception($exception->getMessage());
 131         }
 132
 133         fclose($file);
 134
 135         if ($errors) {
 136             throw new Exception("Failed mysqldump with errors:\n" . $errors);
 137         }
 138
 139         return $warnings;
 140     }
 141
 142     public static function fromEnvOptions(array $env): static
 143     {
 144         $host = ($env['DB_HOST'] ?? '');
 145         $username = ($env['DB_USERNAME'] ?? '');
 146         $password = ($env['DB_PASSWORD'] ?? '');
 147         $database = ($env['DB_DATABASE'] ?? '');
 148         $port = intval($env['DB_PORT'] ?? 3306);
 149
 150         return new static($host, $username, $password, $database, $port);
 151     }
 152 }