5 use Drupal\Core\Database\Database;
7 use Drush\Log\LogLevel;
8 use Drush\Utils\FsUtils;
9 use Robo\Common\ConfigAwareTrait;
10 use Robo\Contract\ConfigAwareInterface;
11 use Webmozart\PathUtil\Path;
13 class SqlBase implements ConfigAwareInterface
16 use SqlTableSelectionTrait;
19 // An Drupal style array containing specs for connecting to database.
22 // Default code appended to sql connections.
23 public $queryExtra = '';
25 // The way you pass a sql file when issueing a query.
26 public $queryFile = '<';
32 * Typically, SqlBase instances are constructed via SqlBase::create($options).
34 public function __construct($db_spec, $options)
36 $this->dbSpec = $db_spec;
37 $this->options = $options;
41 * Get a driver specific instance of this class.
44 * An options array as handed to a command callback.
47 public static function create($options = [])
49 // Set defaults in the unfortunate event that caller doesn't provide values.
51 'database' => 'default',
52 'target' => 'default',
57 $database = $options['database'];
58 $target = $options['target'];
60 if ($url = $options['db-url']) {
61 $url = is_array($url) ? $url[$database] : $url;
62 $db_spec = self::dbSpecFromDbUrl($url);
63 $db_spec['db_prefix'] = $options['db-prefix'];
64 return self::getInstance($db_spec, $options);
65 } elseif (($databases = $options['databases']) && (array_key_exists($database, $databases)) && (array_key_exists($target, $databases[$database]))) {
66 // @todo 'databases' option is not declared anywhere?
67 $db_spec = $databases[$database][$target];
68 return self::getInstance($db_spec, $options);
69 } elseif ($info = Database::getConnectionInfo($database)) {
70 $db_spec = $info[$target];
71 return self::getInstance($db_spec, $options);
73 throw new \Exception(dt('Unable to load Drupal settings. Check your --root, --uri, etc.'));
77 public static function getInstance($db_spec, $options)
79 $driver = $db_spec['driver'];
80 $class_name = 'Drush\Sql\Sql'. ucfirst($driver);
81 $instance = new $class_name($db_spec, $options);
83 $instance->setConfig(Drush::config());
88 * Get the current $db_spec.
90 public function getDbSpec()
96 * Set the current db spec.
98 * @param array $dbSpec
100 public function setDbSpec($dbSpec)
102 $this->dbSpec = $dbSpec;
106 * The unix command used to connect to the database.
109 public function command()
114 * A string for connecting to a database.
116 * @param bool $hide_password
117 * If TRUE, DBMS should try to hide password from process list.
118 * On mysql, that means using --defaults-file to supply the user+password.
122 public function connect($hide_password = true)
124 return trim($this->command() . ' ' . $this->creds($hide_password) . ' ' . $this->getOption('extra', $this->queryExtra));
129 * Execute a SQL dump and return the path to the resulting dump file.
132 * Returns null, or false on failure.
134 public function dump()
136 /** @var string|bool $file Path where dump file should be stored. If TRUE, generate a path based on usual backup directory and current date.*/
137 $file = $this->getOption('result-file');
139 $table_selection = $this->getExpandedTableSelection($this->getOptions(), $this->listTables());
140 $file = $this->dumpFile($file);
141 $cmd = $this->dumpCmd($table_selection);
142 // Gzip the output from dump command(s) if requested.
143 if ($this->getOption('gzip')) {
144 $cmd .= ' | gzip -f';
145 $file_suffix .= '.gz';
148 $file .= $file_suffix;
149 $cmd .= ' > ' . drush_escapeshellarg($file);
152 // Avoid the php memory of the $output array in drush_shell_exec().
153 if (!$return = drush_op_system($cmd)) {
155 drush_log(dt('Database dump saved to !path', ['!path' => $file]), LogLevel::SUCCESS);
156 drush_backend_set_result($file);
159 return drush_set_error('DRUSH_SQL_DUMP_FAIL', 'Database dump failed');
164 * Build bash for dumping a database.
166 * @param array $table_selection
167 * Supported keys: 'skip', 'structure', 'tables'.
169 * One or more mysqldump/pg_dump/sqlite3/etc statements that are ready for executing.
170 * If multiple statements are needed, enclose in parenthesis.
172 public function dumpCmd($table_selection)
177 * Generate a path to an output file for a SQL dump when needed.
179 * @param string|bool @file
180 * If TRUE, generate a path based on usual backup directory and current date.
181 * Otherwise, just return the path that was provided.
183 public function dumpFile($file)
185 $database = $this->dbSpec['database'];
187 // $file is passed in to us usually via --result-file. If the user
188 // has set $options['result-file'] = 'auto', then we
189 // will generate an SQL dump file in the backup directory.
191 if ($file === 'auto') {
192 $backup_dir = FsUtils::prepareBackupDir($database);
193 if (empty($backup_dir)) {
194 $backup_dir = $this->getConfig()->tmp();
196 $file = Path::join($backup_dir, '@DATABASE_@DATE.sql');
198 $file = str_replace(['@DATABASE', '@DATE'], [$database, gmdate('Ymd_His')], $file);
204 * Execute a SQL query. Respect simulate mode.
206 * If you don't want to query results to print during --debug then
207 * provide a $result_file whose value can be drush_bit_bucket().
209 * @param string $query
210 * The SQL to be executed. Should be NULL if $input_file is provided.
211 * @param string $input_file
212 * A path to a file containing the SQL to be executed.
213 * @param string $result_file
214 * A path to save query results to. Can be drush_bit_bucket() if desired.
217 * TRUE on success, FALSE on failure
219 public function query($query, $input_file = null, $result_file = '')
221 if (!Drush::simulate()) {
222 return $this->alwaysQuery($query, $input_file, $result_file);
224 $this->logQueryInDebugMode($query, $input_file);
228 * Execute a SQL query. Always execute it regardless of simulate mode.
230 * If you don't want query results to print during --debug then
231 * provide a $result_file whose value can be drush_bit_bucket().
233 * @param string $query
234 * The SQL to be executed. Should be null if $input_file is provided.
235 * @param string $input_file
236 * A path to a file containing the SQL to be executed.
237 * @param string $result_file
238 * A path to save query results to. Can be drush_bit_bucket() if desired.
241 * TRUE on success, FALSE on failure
243 public function alwaysQuery($query, $input_file = null, $result_file = '')
245 $input_file_original = $input_file;
246 if ($input_file && drush_file_is_tarball($input_file)) {
247 if (drush_always_exec('gzip -d %s', $input_file)) {
248 $input_file = trim($input_file, '.gz');
250 return drush_set_error(dt('Failed to decompress input file.'));
254 // Save $query to a tmp file if needed. We will redirect it in.
256 $query = $this->queryPrefix($query);
257 $query = $this->queryFormat($query);
258 $input_file = drush_save_data_to_temp_file($query);
264 $this->silent(), // This removes column header and various helpful things in mysql.
265 $this->getOption('extra', $this->queryExtra),
267 drush_escapeshellarg($input_file),
269 $exec = implode(' ', $parts);
272 $exec .= ' > '. drush_escapeshellarg($result_file);
275 // In --verbose mode, drush_shell_exec() will show the call to mysql/psql/sqlite,
276 // but the sql query itself is stored in a temp file and not displayed.
277 // We show the query when --debug is used and this function created the temp file.
278 $this->logQueryInDebugMode($query, $input_file_original);
280 $success = drush_always_exec($exec);
282 if ($success && $this->getOption('file-delete')) {
283 drush_delete_dir($input_file);
290 * Show the query in debug mode and simulate mode
292 protected function logQueryInDebugMode($query, $input_file_original)
294 // In --verbose mode, drush_shell_exec() will show the call to mysql/psql/sqlite,
295 // but the sql query itself is stored in a temp file and not displayed.
296 // We show the query when --debug is used and this function created the temp file.
297 if ((Drush::debug() || Drush::simulate()) && empty($input_file_original)) {
298 drush_log('sql-query: ' . $query, LogLevel::INFO);
303 * A string to add to the command when queries should not print their results.
305 public function silent()
310 public function queryPrefix($query)
312 // Inject table prefixes as needed.
313 if (Drush::bootstrapManager()->hasBootstrapped(DRUSH_BOOTSTRAP_DRUPAL_DATABASE)) {
314 // Enable prefix processing which can be dangerous so off by default. See http://drupal.org/node/1219850.
315 if ($this->getOption('db-prefix')) {
316 $query = Database::getConnection()->prefixTables($query);
323 public function queryFormat($query)
329 * Drop specified database.
331 * @param array $tables
332 * An array of table names
334 * True if successful, FALSE if failed.
336 public function drop($tables)
340 $sql = 'DROP TABLE '. implode(', ', $tables);
341 $return = $this->query($sql);
347 * Build a SQL string for dropping and creating a database.
349 * @param string dbname
351 * @param boolean $quoted
352 * Quote the database name. Mysql uses backticks to quote which can cause problems
353 * in a Windows shell. Set TRUE if the CREATE is not running on the bash command line.
356 public function createdbSql($dbname, $quoted = false)
361 * Create a new database.
363 * @param boolean $quoted
364 * Quote the database name. Mysql uses backticks to quote which can cause problems
365 * in a Windows shell. Set TRUE if the CREATE is not running on the bash command line.
367 * True if successful, FALSE otherwise.
369 public function createdb($quoted = false)
371 $dbname = $this->getDbSpec()['database'];
372 $sql = $this->createdbSql($dbname, $quoted);
373 // Adjust connection to allow for superuser creds if provided.
375 return $this->query($sql);
379 * Drop all tables (if DB exists) or CREATE target database.
382 * TRUE or FALSE depending on success.
384 public function dropOrCreate()
386 if ($this->dbExists()) {
387 return $this->drop($this->listTables());
389 return $this->createdb();
394 * Determine if the specified DB already exists.
398 public function dbExists()
402 public function delete()
407 * Build a fragment connection parameters.
409 * @param bool $hide_password
410 * If TRUE, DBMS should try to hide password from process list.
411 * On mysql, that means using --defaults-file to supply the user+password.
414 public function creds($hide_password = true)
419 * The active database driver.
422 public function scheme()
424 return $this->dbSpec['driver'];
428 * Extract the name of all existing tables in the given database.
431 * An array of table names which exist in the current database.
433 public function listTables()
438 * Helper method to turn associative array into options with values.
443 public function paramsToOptions($parameters)
445 // Turn each parameter into a valid parameter string.
446 $parameter_strings = [];
447 foreach ($parameters as $key => $value) {
448 // Only escape the values, not the keys or the rest of the string.
449 $value = drush_escapeshellarg($value);
450 $parameter_strings[] = "--$key=$value";
453 // Join the parameters and return.
454 return implode(' ', $parameter_strings);
458 * Adjust DB connection with superuser credentials if provided.
460 * The options 'db-su' and 'db-su-pw' will be retrieved from the
461 * specified site alias record.
467 $create_db_target = $this->getDbSpec();
469 $create_db_target['database'] = '';
470 $db_superuser = $this->getConfig()->get('sql.db-su');
471 if (!empty($db_superuser)) {
472 $create_db_target['username'] = $db_superuser;
474 $db_su_pw = $this->getOption('db-su-pw');
475 // If --db-su-pw is not provided and --db-su is, default to empty password.
476 // This way db cli command will take password from .my.cnf or .pgpass.
477 if (!empty($db_su_pw)) {
478 $create_db_target['password'] = $db_su_pw;
479 } elseif (!empty($db_superuser)) {
480 unset($create_db_target['password']);
482 $this->setDbSpec($create_db_target);
488 public function getOptions()
490 return $this->options;
493 public function getOption($name, $default = null)
495 $options = $this->getOptions();
496 return array_key_exists($name, $options) && !is_null($options[$name]) ? $options[$name] : $default;
502 public function db_spec() // @codingStandardsIgnoreLine
504 return $this->getDbSpec();
508 * Convert from an old-style database URL to an array of database settings.
511 * A Drupal 6 db url string to convert, or an array with a 'default' element.
513 * An array of database values containing only the 'default' element of
514 * the db url. If the parse fails the array is empty.
516 public static function dbSpecFromDbUrl($db_url)
520 if (is_array($db_url)) {
521 $db_url_default = $db_url['default'];
523 $db_url_default = $db_url;
526 // If it's a sqlite database, pick the database path and we're done.
527 if (strpos($db_url_default, 'sqlite://') === 0) {
529 'driver' => 'sqlite',
530 'database' => substr($db_url_default, strlen('sqlite://')),
533 $url = parse_url($db_url_default);
535 // Fill in defaults to prevent notices.
544 $url = (object)array_map('urldecode', $url);
546 'driver' => $url->scheme == 'mysqli' ? 'mysql' : $url->scheme,
547 'username' => $url->user,
548 'password' => $url->pass,
549 'host' => $url->host,
550 'port' => $url->port,
551 'database' => ltrim($url->path, '/'),