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                 $this->database,
  42                 '-e', "show tables;"
  43             ]);
  44
  45         return !$output;
  46     }
  47
  48     public function importSqlFile(string $sqlFilePath): void
  49     {
  50         $output = (new ProgramRunner('mysql', '/usr/bin/mysql'))
  51             ->withEnvironment(['MYSQL_PWD' => $this->password])
  52             ->withTimeout(240)
  53             ->withIdleTimeout(5)
  54             ->runCapturingStdErr([
  55                 '-h', $this->host,
  56                 '-P', $this->port,
  57                 '-u', $this->user,
  58                 $this->database,
  59                 '-e', "source {$sqlFilePath}"
  60             ]);
  61
  62         if ($output) {
  63             throw new Exception("Failed mysql file import with errors:\n" . $output);
  64         }
  65     }
  66
  67     public function dropTablesSql(): string
  68     {
  69         return <<<'HEREDOC'
  70 SET FOREIGN_KEY_CHECKS = 0;
  71 SET GROUP_CONCAT_MAX_LEN=32768;
  72 SET @tables = NULL;
  73 SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables
  74   FROM information_schema.tables
  75   WHERE table_schema = (SELECT DATABASE());
  76 SELECT IFNULL(@tables,'dummy') INTO @tables;
  77
  78 SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
  79 PREPARE stmt FROM @tables;
  80 EXECUTE stmt;
  81 DEALLOCATE PREPARE stmt;
  82 SET FOREIGN_KEY_CHECKS = 1;
  83 HEREDOC;
  84     }
  85
  86     public function runDumpToFile(string $filePath): void
  87     {
  88         $file = fopen($filePath, 'w');
  89         $errors = "";
  90         $hasOutput = false;
  91
  92         try {
  93             (new ProgramRunner('mysqldump', '/usr/bin/mysqldump'))
  94                 ->withTimeout(240)
  95                 ->withIdleTimeout(15)
  96                 ->withEnvironment(['MYSQL_PWD' => $this->password])
  97                 ->runWithoutOutputCallbacks([
  98                     '-h', $this->host,
  99                     '-P', $this->port,
 100                     '-u', $this->user,
 101                     '--single-transaction',
 102                     '--no-tablespaces',
 103                     $this->database,
 104                 ], function ($data) use (&$file, &$hasOutput) {
 105                     fwrite($file, $data);
 106                     $hasOutput = true;
 107                 }, function ($error) use (&$errors) {
 108                     $errors .= $error . "\n";
 109                 });
 110         } catch (\Exception $exception) {
 111             fclose($file);
 112             if ($exception instanceof ProcessTimedOutException) {
 113                 if (!$hasOutput) {
 114                     throw new Exception("mysqldump operation timed-out.\nNo data has been received so the connection to your database may have failed.");
 115                 } else {
 116                     throw new Exception("mysqldump operation timed-out after data was received.");
 117                 }
 118             }
 119             throw new Exception($exception->getMessage());
 120         }
 121
 122         fclose($file);
 123
 124         if ($errors) {
 125             throw new Exception("Failed mysqldump with errors:\n" . $errors);
 126         }
 127     }
 128
 129     public static function fromEnvOptions(array $env): static
 130     {
 131         $host = ($env['DB_HOST'] ?? '');
 132         $username = ($env['DB_USERNAME'] ?? '');
 133         $password = ($env['DB_PASSWORD'] ?? '');
 134         $database = ($env['DB_DATABASE'] ?? '');
 135         $port = intval($env['DB_PORT'] ?? 3306);
 136
 137         return new static($host, $username, $password, $database, $port);
 138     }
 139 }