sql.drush.inc

  1. 8.0.x commands/sql/sql.drush.inc
  2. 6.x commands/sql/sql.drush.inc
  3. 7.x commands/sql/sql.drush.inc
  4. 3.x commands/sql/sql.drush.inc
  5. 4.x commands/sql/sql.drush.inc
  6. 5.x commands/sql/sql.drush.inc
  7. master commands/sql/sql.drush.inc

Drush sql commands

Functions

Namesort descending Description
drush_sql_bootstrap_further Check whether further bootstrap is needed. If so, do it.
drush_sql_build_createdb_sql
drush_sql_build_dump_command Build a mysqldump/pg_dump/sqlite statement.
drush_sql_build_exec
drush_sql_cli
drush_sql_conf Command callback. Displays the Drupal site's database connection string.
drush_sql_connect
drush_sql_drop
drush_sql_dump Build a mysqldump/pg_dump/sqlite statement.
drush_sql_dump_execute Command callback. Outputs the entire Drupal database in SQL format using mysqldump.
drush_sql_dump_file Determine where to store an sql dump file. This function is called by sql-sync if the caller did not explicitly specify a dump file to use.
drush_sql_empty_db
drush_sql_get_table_selection
drush_sql_query Command callback. Executes the given SQL query on the Drupal database.
drush_sql_read_db_spec Return a db_spec based on supplied db_url/db_prefix options or an existing settings.php.
drush_sql_register_post_sync_op Call from a pre-sql-sync hook to register an sql query to be executed in the post-sql-sync hook.
drush_sql_sanitize Command callback. Run's the sanitization operations on the current database.
drush_sql_show_tables_pgsql
drush_sql_su
sql_drush_command Implementation of hook_drush_command().
sql_drush_help Implementation of hook_drush_help().
_drush_sql_connect Command callback. Emits a connect string for mysql or pgsql.
_drush_sql_drop
_drush_sql_get_all_db_specs
_drush_sql_get_credentials Build a fragment containing credentials and mysql-connection parameters.
_drush_sql_get_db_spec Get a database specification for the active DB connection. Honors the 'database' and 'target command' line options. If not in a Drupal site, honors a --db-url option.
_drush_sql_get_invalid_url_msg
_drush_sql_get_post_sync_messages Builds a confirmation message for all post-sync operations.
_drush_sql_get_scheme
_drush_sql_get_spec_from_options
_drush_sql_get_table_list Consult the specified options and return the list of tables specified.
_drush_sql_query

File

commands/sql/sql.drush.inc
View source
  1. <?php
  2. /**
  3. * @file Drush sql commands
  4. */
  5. /**
  6. * Implementation of hook_drush_help().
  7. */
  8. function sql_drush_help($section) {
  9. switch ($section) {
  10. case 'meta:sql:title':
  11. return dt('SQL commands');
  12. case 'meta:sql:summary':
  13. return dt('Examine and modify your Drupal database.');
  14. }
  15. }
  16. /**
  17. * Implementation of hook_drush_command().
  18. */
  19. function sql_drush_command() {
  20. $options['database'] = 'The DB connection key if using multiple connections in settings.php.';
  21. if (drush_drupal_major_version() >= 7) {
  22. $options['target'] = 'The name of a target within the specified database.';
  23. }
  24. $items['sql-drop'] = array(
  25. 'description' => 'Drop all tables in a given database.',
  26. 'arguments' => array(
  27. ),
  28. 'bootstrap' => DRUSH_BOOTSTRAP_DRUSH,
  29. 'options' => array(
  30. 'yes' => 'Skip confirmation and proceed.',
  31. 'result-file' => 'Save to a file. The file should be relative to Drupal root. Recommended.',
  32. ) + $options,
  33. 'topics' => array('docs-policy'),
  34. );
  35. $items['sql-conf'] = array(
  36. 'description' => 'Print database connection details using print_r().',
  37. 'hidden' => TRUE,
  38. 'arguments' => array(
  39. 'all' => 'Show all database connections, instead of just one.',
  40. 'show-passwords' => 'Show database password.',
  41. ),
  42. 'bootstrap' => DRUSH_BOOTSTRAP_DRUPAL_CONFIGURATION,
  43. 'options' => $options,
  44. );
  45. $items['sql-connect'] = array(
  46. 'description' => 'A string for connecting to the DB.',
  47. 'bootstrap' => DRUSH_BOOTSTRAP_DRUSH,
  48. 'options' => $options,
  49. 'examples' => array(
  50. '`drush sql-connect` < example.sql' => 'Import sql statements from a file into the current database.',
  51. ),
  52. );
  53. $items['sql-dump'] = array(
  54. 'callback' => 'drush_sql_dump_execute',
  55. 'description' => 'Exports the Drupal DB as SQL using mysqldump or equivalent.',
  56. 'bootstrap' => DRUSH_BOOTSTRAP_DRUSH,
  57. 'examples' => array(
  58. 'drush sql-dump --result-file=../18.sql' => 'Save SQL dump to the directory above Drupal root.',
  59. 'drush sql-dump --skip-tables-key=common' => 'Skip standard tables. @see example.drushrc.php',
  60. ),
  61. 'options' => array(
  62. 'result-file' => 'Save to a file. The file should be relative to Drupal root. If --result-file is provided with no value, then date based filename will be created under ~/drush-backups directory.',
  63. 'skip-tables-key' => 'A key in the $skip_tables array. @see example.drushrc.php. Optional.',
  64. 'structure-tables-key' => 'A key in the $structure_tables array. @see example.drushrc.php. Optional.',
  65. 'tables-key' => 'A key in the $tables array. Optional.',
  66. 'tables-list' => 'A comma-separated list of tables to transfer. Optional.',
  67. 'ordered-dump' => 'Use this option to output ordered INSERT statements in the sql-dump.Useful when backups are managed in a Version Control System. Optional.',
  68. 'create-db' => 'Wipe existing tables.',
  69. 'data-only' => 'Omit CREATE TABLE statements. Postgres only.',
  70. 'ordered-dump' => 'Order by primary key and add line breaks for efficient diff in revision control. Also, faster rsync. Slows down the dump. Mysql only.',
  71. 'gzip' => 'Compress the dump using the gzip program which must be in your $PATH.',
  72. ) + $options,
  73. );
  74. $items['sql-query'] = array(
  75. 'bootstrap' => DRUSH_BOOTSTRAP_DRUSH,
  76. 'description' => 'Execute a query against the site database.',
  77. 'examples' => array(
  78. 'drush sql-query "SELECT * FROM {users} WHERE uid=1"' => 'Browse user record. Table prefixes are honored.',
  79. '`drush sql-connect` < example.sql' => 'Import sql statements from a file into the current database.',
  80. 'drush sql-query --input-file=example.sql' => 'Alternate way to import sql statements from a file.',
  81. ),
  82. 'arguments' => array(
  83. 'query' => 'An SQL query. Ignored if \'file\' is provided.',
  84. ),
  85. 'options' => array(
  86. 'result-file' => 'Save to a file. The file should be relative to Drupal root. Optional.',
  87. 'input-file' => 'Path to a file containing the SQL to be run.',
  88. 'extra' => 'Add custom options to the mysql command.',
  89. ) + $options,
  90. 'aliases' => array('sqlq'),
  91. );
  92. $items['sql-sync'] = array(
  93. 'description' => 'Copy and import source database to target database. Transfers via rsync.',
  94. 'bootstrap' => DRUSH_BOOTSTRAP_DRUSH,
  95. 'drush dependencies' => array('core'), // core-rsync.
  96. 'examples' => array(
  97. 'drush sql-sync @dev @prod' => 'Copy the DB defined in sites/dev to the DB in sites/prod.',
  98. ),
  99. 'arguments' => array(
  100. 'from' => 'Name of subdirectory within /sites or a site-alias.',
  101. 'to' => 'Name of subdirectory within /sites or a site-alias.',
  102. ),
  103. 'options' => array(
  104. 'skip-tables-key' => 'A key in the $skip_tables array. @see example.drushrc.php. Optional.',
  105. 'structure-tables-key' => 'A key in the $structure_tables array. @see example.drushrc.php. Optional.',
  106. 'tables-key' => 'A key in the $tables array. Optional.',
  107. 'tables-list' => 'A comma-separated list of tables to transfer. Optional.',
  108. 'cache' => 'Skip dump if result file exists and is less than "cache" hours old. Optional; default is 24 hours.',
  109. 'no-cache' => 'Do not cache the sql-dump file.',
  110. 'no-dump' => 'Do not dump the sql database; always use an existing dump file.',
  111. 'source-db-url' => 'Database specification for source system to dump from.',
  112. 'source-remote-port' => 'Override sql database port number in source-db-url. Optional.',
  113. 'source-remote-host' => 'Remote machine to run sql-dump file on. Optional; default is local machine.',
  114. 'source-dump' => 'Path to dump file. Optional; default is to create a temporary file.',
  115. 'target-database' => 'A key in the $db_url (D6) or $databases (D7+) array which provides the data.',
  116. 'source-target' => 'Oy. A key within the --target_database identifying a particular server in the database group.',
  117. 'target-db-url' => '',
  118. 'target-remote-port' => '',
  119. 'target-remote-host' => '',
  120. 'target-dump' => '',
  121. 'target-database' => 'A key in the $db_url (D6) or $databases (D7+) array which shall receive the data.',
  122. 'target-target' => 'Oy. A key within the --target_database identifying a particular server in the database group.',
  123. 'temp' => 'Use a temporary file to hold dump files. Implies --no-cache.',
  124. 'dump-dir' => 'Directory to store sql dump files in when --source-dump or --target-dump are not used. Takes precedence over --temp.',
  125. 'create-db' => 'Create a new database before importing the database dump on the target machine.',
  126. 'db-su' => 'Account to use when creating a new database. Optional.',
  127. 'db-su-pw' => 'Password for the "db-su" account. Optional.',
  128. 'no-ordered-dump' => 'Do not pass --ordered-dump to sql-dump. sql-sync orders the dumpfile by default in order to increase the efficiency of rsync.',
  129. 'sanitize' => 'Obscure email addresses and reset passwords in the user table post-sync. Optional.',
  130. ),
  131. 'sub-options' => array(
  132. 'sanitize' => array(
  133. 'sanitize-password' => 'The password to assign to all accounts in the sanitization operation, or "no" to keep passwords unchanged. Default is "password".',
  134. 'sanitize-email' => 'The username for test email addresses in the sanitization operation, or "no" to keep email addresses unchanged. May contain replacement patterns %uid, %mail or %login. Default is "user+%uid@localhost".',
  135. 'confirm-sanitizations' => 'Prompt yes/no after importing the database, but before running the sanitizations',
  136. ),
  137. ),
  138. 'topics' => array('docs-aliases', 'docs-policy'),
  139. );
  140. if (drush_drupal_major_version() >= 7) {
  141. $items['sql-sync']['options'] += array(
  142. 'source-target' => 'The name of a target within the SOURCE database.',
  143. 'destination-target' => 'The name of a target within the specified DESTINATION database.',
  144. );
  145. }
  146. $items['sql-cli'] = array(
  147. 'description' => "Open a SQL command-line interface using Drupal's credentials.",
  148. 'bootstrap' => DRUSH_BOOTSTRAP_DRUSH,
  149. 'options' => $options,
  150. 'aliases' => array('sqlc'),
  151. );
  152. $items['sql-sanitize'] = array(
  153. 'description' => "Run sanitization operations on the current database.",
  154. 'bootstrap' => DRUSH_BOOTSTRAP_DRUSH,
  155. 'hidden' => TRUE,
  156. 'options' => array(
  157. 'sanitize-password' => 'The password to assign to all accounts in the sanitization operation, or "no" to keep passwords unchanged. Default is "password".',
  158. 'sanitize-email' => 'The username for test email addresses in the sanitization operation, or "no" to keep email addresses unchanged. May contain replacement patterns %uid, %mail or %login. Default is "user+%uid@localhost".',
  159. ),
  160. 'aliases' => array('sqlsan'),
  161. );
  162. return $items;
  163. }
  164. /**
  165. * Check whether further bootstrap is needed. If so, do it.
  166. */
  167. function drush_sql_bootstrap_further() {
  168. if (!drush_get_option('db-url')) {
  169. drush_bootstrap_max(DRUSH_BOOTSTRAP_DRUPAL_CONFIGURATION);
  170. }
  171. }
  172. /**
  173. * Command callback. Displays the Drupal site's database connection string.
  174. */
  175. function drush_sql_conf() {
  176. if (drush_get_option('db-url', FALSE)) {
  177. $db_spec['db-url'] = $GLOBALS['db_url'];
  178. }
  179. elseif (drush_get_option('all', FALSE)) {
  180. $db_spec = _drush_sql_get_all_db_specs();
  181. }
  182. if (!isset($db_spec)) {
  183. $db_spec = _drush_sql_get_db_spec();
  184. }
  185. drush_backend_set_result($db_spec);
  186. if (!drush_get_option('show-passwords', FALSE)) {
  187. drush_unset_recursive($db_spec, 'password');
  188. }
  189. drush_print_r($db_spec);
  190. }
  191. /**
  192. * Command callback. Emits a connect string for mysql or pgsql.
  193. */
  194. function _drush_sql_connect($db_spec = NULL) {
  195. switch (_drush_sql_get_scheme($db_spec)) {
  196. case 'mysql':
  197. $command = 'mysql';
  198. break;
  199. case 'pgsql':
  200. $command = 'psql';
  201. break;
  202. case 'sqlite':
  203. $command = 'sqlite3';
  204. break;
  205. }
  206. $command .= _drush_sql_get_credentials($db_spec);
  207. return $command;
  208. }
  209. function drush_sql_connect() {
  210. drush_sql_bootstrap_further();
  211. $connect = _drush_sql_connect();
  212. drush_print($connect);
  213. return $connect;
  214. }
  215. /**
  216. * Command callback. Outputs the entire Drupal database in SQL format using mysqldump.
  217. */
  218. function drush_sql_dump_execute() {
  219. drush_sql_bootstrap_further();
  220. list($exec, $file) = drush_sql_dump();
  221. // Avoid the php memory of the $output array in drush_shell_exec().
  222. if (!$return = drush_op_system($exec)) {
  223. if ($file) {
  224. drush_log(dt('Database dump saved to !path', array('!path' => $file)), 'success');
  225. }
  226. }
  227. else {
  228. return drush_set_error('DRUSH_SQL_DUMP_FAIL', 'Database dump failed');
  229. }
  230. }
  231. function drush_sql_get_table_selection() {
  232. // Skip large core tables if instructed. Also used by 'sql-sync' command.
  233. $skip_tables = _drush_sql_get_table_list('skip-tables');
  234. // Skip any structure-tables as well.
  235. $structure_tables = _drush_sql_get_table_list('structure-tables');
  236. // Dump only the specified tables. Takes precedence over skip-tables and structure-tables.
  237. $tables = _drush_sql_get_table_list('tables');
  238. return array('skip' => $skip_tables, 'structure' => $structure_tables, 'tables' => $tables);
  239. }
  240. /**
  241. * Build a mysqldump/pg_dump/sqlite statement.
  242. *
  243. * @param db_spec
  244. * For D5/D6, a $db_url. For D7, a target in the default DB connection.
  245. * @return array
  246. * An array with items.
  247. * 1. A mysqldump/pg_dump/sqlite statement that is ready for executing.
  248. * 2. The filepath where the dump will be saved.
  249. */
  250. function drush_sql_dump($db_spec = NULL) {
  251. return drush_sql_build_dump_command(drush_sql_get_table_selection(), $db_spec);
  252. }
  253. /**
  254. * Build a mysqldump/pg_dump/sqlite statement.
  255. *
  256. * @param array $table_selection
  257. * Supported keys: 'skip', 'structure', 'tables'.
  258. * @param db_spec
  259. * For D5/D6, a $db_url. For D7, a target in the default DB connection.
  260. * @return array
  261. * An array with items.
  262. * 1. A mysqldump/pg_dump/sqlite statement that is ready for executing.
  263. * 2. The filepath where the dump will be saved.
  264. */
  265. function drush_sql_build_dump_command($table_selection, $db_spec = NULL) {
  266. $skip_tables = $table_selection['skip'];
  267. $structure_tables = $table_selection['structure'];
  268. $tables = $table_selection['tables'];
  269. $ignores = array();
  270. $skip_tables = array_merge($structure_tables, $skip_tables);
  271. $data_only = drush_get_option('data-only');
  272. // The ordered-dump option is only supported by MySQL for now.
  273. // @todo add documention once a hook for drush_get_option_help() is available.
  274. // @see drush_get_option_help() in drush.inc
  275. $ordered_dump = drush_get_option('ordered-dump');
  276. if (is_null($db_spec)) {
  277. $db_spec = _drush_sql_get_db_spec();
  278. }
  279. $database = $db_spec['database'];
  280. // Get the setting of --result-file. If the user
  281. // has set $options['result-file'] = TRUE, then we
  282. // will generate an SQL dump file in the same backup
  283. // directory that pm-updatecode uses.
  284. $file = NULL;
  285. if ($file = drush_get_option('result-file', FALSE)) {
  286. if ($file === TRUE) {
  287. // User did not pass a specific value for --result-file. Make one.
  288. drush_include_engine('version_control', 'backup');
  289. $backup = new drush_pm_version_control_backup();
  290. $backup_dir = $backup->prepare_backup_dir($db_spec['database']);
  291. if (empty($backup_dir)) {
  292. $backup_dir = "/tmp";
  293. }
  294. $file = $backup_dir . '/@DATABASE_@DATE.sql';
  295. }
  296. $file = str_replace(array('@DATABASE', '@DATE'), array($database, gmdate('Ymd_his')), $file);
  297. }
  298. switch (_drush_sql_get_scheme($db_spec)) {
  299. case 'mysqli':
  300. case 'mysql':
  301. $exec = 'mysqldump';
  302. if ($file) {
  303. $exec .= ' --result-file '. $file;
  304. }
  305. // mysqldump wants 'databasename' instead of 'database=databasename' for no good reason.
  306. $extra = ' --no-autocommit --single-transaction --opt -Q' . str_replace('--database=', ' ', _drush_sql_get_credentials($db_spec));
  307. if (isset($data_only)) {
  308. $extra .= ' --no-create-info';
  309. }
  310. if (isset($ordered_dump)) {
  311. $extra .= ' --skip-extended-insert --order-by-primary';
  312. }
  313. $exec .= $extra;
  314. if (!empty($tables)) {
  315. $exec .= ' ' . implode(' ', $tables);
  316. }
  317. else {
  318. // Append the ignore-table options.
  319. foreach ($skip_tables as $table) {
  320. $ignores[] = "--ignore-table=$database.$table";
  321. }
  322. $exec .= ' '. implode(' ', $ignores);
  323. // Run mysqldump again and append output if we need some structure only tables.
  324. if (!empty($structure_tables)) {
  325. $exec .= " && mysqldump --no-data $extra " . implode(' ', $structure_tables);
  326. if ($file) {
  327. $exec .= " >> $file";
  328. }
  329. }
  330. }
  331. break;
  332. case 'pgsql':
  333. $create_db = drush_get_option('create-db');
  334. $exec = 'pg_dump ';
  335. if ($file) {
  336. $exec .= ' --file '. $file;
  337. }
  338. // Unlike psql, pg_dump does not take a '--dbname=' before the database name.
  339. $extra = str_replace('--dbname=', ' ', _drush_sql_get_credentials($db_spec));
  340. if (isset($data_only)) {
  341. $extra .= ' --data-only';
  342. }
  343. $exec .= $extra;
  344. $exec .= (!isset($create_db) && !isset($data_only) ? ' --clean' : '');
  345. if (!empty($tables)) {
  346. foreach ($tables as $table) {
  347. $exec .= " --table=$table";
  348. }
  349. }
  350. else {
  351. foreach ($skip_tables as $table) {
  352. $ignores[] = "--exclude-table=$table";
  353. }
  354. $exec .= ' '. implode(' ', $ignores);
  355. // Run pg_dump again and append output if we need some structure only tables.
  356. if (!empty($structure_tables)) {
  357. $schemaonlies = array();
  358. foreach ($structure_tables as $table) {
  359. $schemaonlies[] = "--table=$table";
  360. }
  361. $exec .= " && pg_dump --schema-only " . implode(' ', $schemaonlies) . $extra;
  362. if ($file) {
  363. $exec .= " >> $file";
  364. }
  365. }
  366. }
  367. break;
  368. case 'sqlite':
  369. // Dumping is usually not necessary in SQLite, since all database data
  370. // is stored in a single file on the filesystem which can be copied just
  371. // like any other file. But it still has a use in migration purposes and
  372. // building human-readable diffs and such, so let's do it anyway.
  373. $exec = _drush_sql_connect();
  374. // SQLite's dump command doesn't support many of the features of its
  375. // Postgres or MySQL equivalents. We may be able to fake some in the
  376. // future, but for now, let's just support simple dumps.
  377. $exec .= ' ".dump"';
  378. if ($file = drush_get_option('result-file')) {
  379. $exec .= ' > '. $file;
  380. }
  381. break;
  382. }
  383. if (drush_get_option('gzip')) {
  384. if ($file) {
  385. // Gzip the result-file
  386. $exec .= "; gzip $file";
  387. $file .= '.gz';
  388. }
  389. else {
  390. // gzip via pipe since user has not specified a file.
  391. $exec .= "| gzip";
  392. }
  393. }
  394. return array($exec, $file);
  395. }
  396. /**
  397. * Consult the specified options and return the list of tables
  398. * specified.
  399. *
  400. * @param option_name
  401. * The option name to check: skip-tables, structure-tables
  402. * or tables. This funciton will check both *-key and *-list,
  403. * and, in the case of sql-sync, will also check target-*
  404. * and source-*, to see if an alias set one of these options.
  405. * @returns array
  406. * Returns an array of tables based on the first option
  407. * found, or an empty array if there were no matches.
  408. */
  409. function _drush_sql_get_table_list($option_name) {
  410. foreach(array('' => 'cli', 'target-,,source-' => NULL) as $prefix_list => $context) {
  411. foreach(explode(',',$prefix_list) as $prefix) {
  412. $key_list = drush_get_option($prefix . $option_name . '-key', NULL, $context);
  413. foreach(explode(',', $key_list) as $key) {
  414. $all_tables = drush_get_option($option_name, array());
  415. if (array_key_exists($key, $all_tables)) {
  416. return $all_tables[$key];
  417. }
  418. if ($option_name != 'tables') {
  419. $all_tables = drush_get_option('tables', array());
  420. if (array_key_exists($key, $all_tables)) {
  421. return $all_tables[$key];
  422. }
  423. }
  424. }
  425. $table_list = drush_get_option($prefix . $option_name . '-list', NULL, $context);
  426. if (isset($table_list)) {
  427. return empty($table_list) ? array() : explode(',', $table_list);
  428. }
  429. }
  430. }
  431. return array();
  432. }
  433. /**
  434. * Command callback. Executes the given SQL query on the Drupal database.
  435. */
  436. function drush_sql_query($query, $filename = NULL) {
  437. drush_sql_bootstrap_further();
  438. return _drush_sql_query($query, NULL, $filename);
  439. }
  440. /*
  441. * Execute a SQL query.
  442. *
  443. * @param string $query
  444. * The SQL to be executed. Should be NULL if $file is provided.
  445. * @param array $db_spec
  446. * A database target.
  447. * @param string $filename
  448. * A path to a file containing the SQL to be executed.
  449. */
  450. function _drush_sql_query($query, $db_spec = NULL, $filename = NULL) {
  451. $scheme = _drush_sql_get_scheme($db_spec);
  452. // Inject table prefixes as needed.
  453. if (drush_has_boostrapped(DRUSH_BOOTSTRAP_DRUPAL_DATABASE)) {
  454. if ($filename) {
  455. $query = file_get_contents($filename);
  456. }
  457. if (drush_drupal_major_version() >= 7) {
  458. $query = Database::getConnection()->prefixTables($query);
  459. }
  460. else {
  461. $query = db_prefix_tables($query);
  462. }
  463. }
  464. // Convert mysql 'show tables;' query into something pgsql understands
  465. if (($scheme == 'pgsql') && ($query == 'show tables;')) {
  466. $query = drush_sql_show_tables_pgsql();
  467. }
  468. // Save $query to a tmp file if needed. We will redirect it in.
  469. if (!$filename) {
  470. $filename = drush_save_data_to_temp_file($query);
  471. }
  472. $exec = drush_sql_build_exec($db_spec, $filename);
  473. if ($output_file = drush_get_option('result-file')) {
  474. $exec .= ' > '. drush_escapeshellarg($output_file);
  475. }
  476. // In --simulate mode, drush_op will show the call to mysql or psql,
  477. // but the sql query itself is stored in a temp file and not displayed.
  478. // We will therefore show the query explicitly in the interest of full disclosure.
  479. if (drush_get_context('DRUSH_SIMULATE')) {
  480. drush_print('sql-query: ' . $query);
  481. }
  482. $return = drush_op_system($exec) == 0;
  483. return $return;
  484. }
  485. function drush_sql_drop() {
  486. if (!drush_confirm(dt('Do you really want to drop all tables?'))) {
  487. return drush_user_abort();
  488. }
  489. drush_sql_bootstrap_further();
  490. _drush_sql_drop();
  491. }
  492. // n.b. site-install uses _drush_sql_drop as a fallback technique if
  493. // drop database; create database fails. If _drush_sql_drop
  494. // is rewritten to also use that technique, it should maintain
  495. // the drop tables code here as a fallback.
  496. function _drush_sql_drop($db_spec = NULL) {
  497. // TODO: integrate with _drush_sql_get_table_list?
  498. $scheme = _drush_sql_get_scheme($db_spec);
  499. switch ($scheme) {
  500. case 'pgsql':
  501. $query = drush_sql_show_tables_pgsql();
  502. break;
  503. case 'sqlite':
  504. $query = '.tables';
  505. break;
  506. default:
  507. $query = 'SHOW TABLES;';
  508. }
  509. $filename = drush_save_data_to_temp_file($query);
  510. $exec = drush_sql_build_exec($db_spec, $filename);
  511. // Actually run this prep query no matter if in SIMULATE.
  512. $old = drush_get_context('DRUSH_SIMULATE');
  513. drush_set_context('DRUSH_SIMULATE', FALSE);
  514. drush_shell_exec($exec);
  515. drush_set_context('DRUSH_SIMULATE', $old);
  516. if ($tables = drush_shell_exec_output()) {
  517. if ($scheme === 'sqlite') {
  518. // SQLite's '.tables' command always outputs the table names in a column
  519. // format, like this:
  520. // table_alpha table_charlie table_echo
  521. // table_bravo table_delta table_foxtrot
  522. // …and there doesn't seem to be a way to fix that. So we need to do some
  523. // clean-up.
  524. // Since we're already doing iteration here, might as well build the SQL
  525. // too, since SQLite only wants one table per DROP TABLE command (so we have
  526. // to do "DROP TABLE foo; DROP TABLE bar;" instead of
  527. // "DROP TABLE foo, bar;").
  528. $sql = '';
  529. foreach ($tables as $line) {
  530. preg_match_all('/[^\s]+/', $line, $matches);
  531. if (!empty($matches[0])) {
  532. foreach ($matches[0] as $match) {
  533. $sql .= "DROP TABLE {$match};";
  534. }
  535. }
  536. }
  537. // We can't use drush_op('db_query', $sql) because it will only perform one
  538. // SQL command and we're technically performing several.
  539. $exec = _drush_sql_connect($db_spec);
  540. $exec .= " '{$sql}'";
  541. return drush_op_system($exec) == 0;
  542. }
  543. else {
  544. // Shift off the header of the column of data returned.
  545. array_shift($tables);
  546. $sql = 'DROP TABLE '. implode(', ', $tables);
  547. return _drush_sql_query($sql, $db_spec);
  548. }
  549. }
  550. else {
  551. drush_log(dt('No tables to drop.'), 'ok');
  552. }
  553. return TRUE;
  554. }
  555. function drush_sql_cli() {
  556. drush_sql_bootstrap_further();
  557. proc_close(proc_open(_drush_sql_connect(), array(0 => STDIN, 1 => STDOUT, 2 => STDERR), $pipes));
  558. }
  559. /**
  560. * Command callback. Run's the sanitization operations on the current database.
  561. */
  562. function drush_sql_sanitize() {
  563. if (!drush_confirm(dt('Do you really want to sanitize the current database?'))) {
  564. return drush_user_abort();
  565. }
  566. drush_sql_bootstrap_further();
  567. drush_include(DRUSH_BASE_PATH . '/commands/sql', 'sync.sql');
  568. drush_command_invoke_all('drush_sql_sync_sanitize', 'default');
  569. $options = drush_get_context('post-sync-ops');
  570. if (!empty($options)) {
  571. if (!drush_get_context('DRUSH_SIMULATE')) {
  572. $messages = _drush_sql_get_post_sync_messages();
  573. if ($messages) {
  574. drush_print();
  575. drush_print($messages);
  576. }
  577. }
  578. }
  579. $sanitize_query = '';
  580. foreach($options as $id => $data) {
  581. $sanitize_query .= $data['query'] . " ";
  582. }
  583. if ($sanitize_query) {
  584. if (!drush_get_context('DRUSH_SIMULATE')) {
  585. drush_sql_query($sanitize_query);
  586. }
  587. else {
  588. drush_print("Executing: $sanitize_query");
  589. }
  590. }
  591. }
  592. //////////////////////////////////////////////////////////////////////////////
  593. // SQL SERVICE HELPERS
  594. /**
  595. * Get a database specification for the active DB connection. Honors the
  596. * 'database' and 'target command' line options. If not in a Drupal site,
  597. * honors a --db-url option.
  598. *
  599. * @return
  600. * An info array describing a database target.
  601. */
  602. function _drush_sql_get_db_spec() {
  603. $database = drush_get_option('database', 'default');
  604. $target = drush_get_option('target', 'default');
  605. // Use --db-url if present.
  606. if ($url = drush_get_option('db-url')) {
  607. $url = is_array($url) ? $url[$database] : $url;
  608. $db_spec = drush_convert_db_from_db_url($url);
  609. $db_spec['db_prefix'] = drush_get_option('db-prefix');
  610. return $db_spec;
  611. }
  612. elseif (($databases = drush_get_option('databases')) && (array_key_exists($database, $databases)) && (array_key_exists($target, $databases[$database]))) {
  613. return $databases[$database][$target];
  614. }
  615. elseif (drush_bootstrap(DRUSH_BOOTSTRAP_DRUPAL_CONFIGURATION)) {
  616. switch (drush_drupal_major_version()) {
  617. case 5:
  618. case 6:
  619. $url = isset($GLOBALS['db_url']) ? $GLOBALS['db_url'] : drush_get_option('db-url', NULL);
  620. $url = is_array($url) ? $url[$database] : $url;
  621. $db_spec = drush_convert_db_from_db_url($url);
  622. $db_spec['db_prefix'] = isset($GLOBALS['db_prefix']) ? $GLOBALS['db_prefix'] : drush_get_option('db-prefix', NULL);
  623. return $db_spec;
  624. default:
  625. // We don't use DB API here `sql-sync` would have to messily addConnection.
  626. if (!isset($GLOBALS['databases']) || !array_key_exists($database, $GLOBALS['databases']) || !array_key_exists($target, $GLOBALS['databases'][$database])) {
  627. return NULL;
  628. }
  629. return $GLOBALS['databases'][$database][$target];
  630. }
  631. }
  632. }
  633. function _drush_sql_get_all_db_specs() {
  634. switch (drush_drupal_major_version()) {
  635. case 5:
  636. case 6:
  637. return drush_sitealias_convert_db_from_db_url($GLOBALS['db_url']);
  638. default:
  639. if (!isset($GLOBALS['databases'])) {
  640. return NULL;
  641. }
  642. return $GLOBALS['databases'];
  643. }
  644. }
  645. function _drush_sql_get_spec_from_options($prefix, $default_to_self = TRUE) {
  646. $db_spec = NULL;
  647. $databases = drush_get_option($prefix . 'databases');
  648. if (isset($databases) && !empty($databases)) {
  649. $database = drush_get_option($prefix . 'database', 'default');
  650. $target = drush_get_option($prefix . 'target', 'default');
  651. if (array_key_exists($database, $databases) && array_key_exists($target, $databases[$database])) {
  652. $db_spec = $databases[$database][$target];
  653. }
  654. }
  655. else {
  656. $db_url = drush_get_option($prefix . 'db-url');
  657. if (isset($db_url)) {
  658. $db_spec = drush_convert_db_from_db_url($db_url);
  659. }
  660. elseif ($default_to_self) {
  661. $db_spec = _drush_sql_get_db_spec();
  662. }
  663. }
  664. if (isset($db_spec)) {
  665. $remote_host = drush_get_option($prefix . 'remote-host');
  666. if (!drush_is_local_host($remote_host)) {
  667. $db_spec['remote-host'] = $remote_host;
  668. $db_spec['port'] = drush_get_option($prefix . 'remote-port', $db_spec['port']);
  669. }
  670. }
  671. return $db_spec;
  672. }
  673. /**
  674. * Determine where to store an sql dump file. This
  675. * function is called by sql-sync if the caller did
  676. * not explicitly specify a dump file to use.
  677. *
  678. * @param db_spec
  679. * Information about the database being dumped; used
  680. * to generate the filename.
  681. * @return string
  682. * The path to the dump file
  683. */
  684. function drush_sql_dump_file(&$db_spec, $prefix) {
  685. // Use an entry in the db spec to indicate whether the dump
  686. // file we use is temporary or not.
  687. $db_spec['dump-is-temp'] = FALSE;
  688. // Make a base filename pattern to use to name the dump file
  689. $filename_pattern = $db_spec['database'];
  690. if (isset($db_spec['remote-host'])) {
  691. $filename_pattern = $db_spec['remote-host'] . '_' . $filename_pattern;
  692. }
  693. // If the user has set the --{prefix}-dir option, then
  694. // use the exact name provided.
  695. $dump_file = drush_get_option($prefix . 'dump');
  696. if (!isset($dump_file)) {
  697. // If the user has set the --dump-dir option, then
  698. // store persistant sql dump files there.
  699. $dump_dir = drush_get_option(array($prefix . 'dump-dir', 'dump-dir'));
  700. if (isset($dump_dir)) {
  701. $dump_file = $dump_dir . '/' . $filename_pattern . '.sql';
  702. }
  703. // If the --dump-dir option is not set, then store
  704. // the sql dump in a temporary file.
  705. else {
  706. $dump_file = drush_tempnam($filename_pattern . '.sql.');
  707. $db_spec['dump-is-temp'] = TRUE;
  708. }
  709. }
  710. return $dump_file;
  711. }
  712. function _drush_sql_get_scheme($db_spec = NULL) {
  713. if (is_null($db_spec)) {
  714. $db_spec = _drush_sql_get_db_spec();
  715. }
  716. return $db_spec['driver'];
  717. }
  718. /**
  719. * Build a fragment containing credentials and mysql-connection parameters.
  720. *
  721. * @param $db_spec
  722. * @return string
  723. */
  724. function _drush_sql_get_credentials($db_spec = NULL) {
  725. if (is_null($db_spec)) {
  726. $db_spec = _drush_sql_get_db_spec();
  727. }
  728. // Build an array of key-value pairs for the parameters.
  729. $parameters = array();
  730. switch (_drush_sql_get_scheme($db_spec)) {
  731. case 'mysql':
  732. // Some drush commands (e.g. site-install) want to connect to the
  733. // server, but not the database. Connect to the built-in database.
  734. $parameters['database'] = empty($db_spec['database']) ? 'information_schema' : $db_spec['database'];
  735. // EMPTY host is not the same as NO host, and is valid (see unix_socket).
  736. if (isset($db_spec['host'])) {
  737. $parameters['host'] = $db_spec['host'];
  738. }
  739. if (!empty($db_spec['port'])) {
  740. $parameters['port'] = $db_spec['port'];
  741. }
  742. // User is required. Drupal calls it 'username'. MySQL calls it 'user'.
  743. $parameters['user'] = $db_spec['username'];
  744. // EMPTY password is not the same as NO password, and is valid.
  745. if (isset($db_spec['password'])) {
  746. $parameters['password'] = $db_spec['password'];
  747. }
  748. break;
  749. case 'pgsql':
  750. // Some drush commands (e.g. site-install) want to connect to the
  751. // server, but not the database. Connect to the built-in database.
  752. $parameters['dbname'] = empty($db_spec['database']) ? 'template1' : $db_spec['database'];
  753. // Host and port are optional but have defaults.
  754. $parameters['host'] = empty($db_spec['host']) ? 'localhost' : $db_spec['host'];
  755. $parameters['port'] = empty($db_spec['port']) ? '5432' : $db_spec['port'];
  756. // Username is required.
  757. $parameters['username'] = $db_spec['username'];
  758. // Don't set the password.
  759. // @see http://drupal.org/node/438828
  760. break;
  761. case 'sqlite':
  762. // SQLite doesn't do user management, instead relying on the filesystem
  763. // for that. So the only info we really need is the path to the database
  764. // file, and not as a "--key=value" parameter.
  765. return ' ' . $db_spec['database'];
  766. break;
  767. }
  768. // Turn each parameter into a valid parameter string.
  769. $parameter_strings = array();
  770. foreach ($parameters as $key => $value) {
  771. // Only escape the values, not the keys or the rest of the string.
  772. $value = drush_escapeshellarg($value);
  773. $parameter_strings[] = "--$key=$value";
  774. }
  775. // Join the parameters and return.
  776. return ' ' . implode(' ', $parameter_strings);
  777. }
  778. function _drush_sql_get_invalid_url_msg($db_spec = NULL) {
  779. if (is_null($db_spec)) {
  780. $db_spec = _drush_sql_get_db_spec();
  781. }
  782. switch (drush_drupal_major_version()) {
  783. case 5:
  784. case 6:
  785. return dt('Unable to parse DB connection string');
  786. default:
  787. return dt('Unable to parse DB connection array');
  788. }
  789. }
  790. /**
  791. * Call from a pre-sql-sync hook to register an sql
  792. * query to be executed in the post-sql-sync hook.
  793. * @see drush_sql_pre_sql_sync() and @see drush_sql_post_sql_sync().
  794. *
  795. * @param $id
  796. * String containing an identifier representing this
  797. * operation. This id is not actually used at the
  798. * moment, it is just used to fufill the contract
  799. * of drush contexts.
  800. * @param $message
  801. * String with the confirmation message that describes
  802. * to the user what the post-sync operation is going
  803. * to do. This confirmation message is printed out
  804. * just before the user is asked whether or not the
  805. * sql-sync operation should be continued.
  806. * @param $query
  807. * String containing the sql query to execute. If no
  808. * query is provided, then the confirmation message will
  809. * be displayed to the user, but no action will be taken
  810. * in the post-sync hook. This is useful for drush modules
  811. * that wish to provide their own post-sync hooks to fix
  812. * up the target database in other ways (e.g. through
  813. * Drupal APIs).
  814. */
  815. function drush_sql_register_post_sync_op($id, $message, $query = NULL) {
  816. $options = drush_get_context('post-sync-ops');
  817. $options[$id] = array('message' => $message, 'query' => $query);
  818. drush_set_context('post-sync-ops', $options);
  819. }
  820. /**
  821. * Builds a confirmation message for all post-sync operations.
  822. *
  823. * @return string
  824. * All post-sync operation messages concatenated together.
  825. */
  826. function _drush_sql_get_post_sync_messages() {
  827. $messages = FALSE;
  828. $options = drush_get_context('post-sync-ops');
  829. if (!empty($options)) {
  830. $messages = dt('The following post-sync operations will be done on the destination:') . "\n";
  831. foreach($options as $id => $data) {
  832. $messages .= " * " . $data['message'] . "\n";
  833. }
  834. }
  835. return $messages;
  836. }
  837. // Convert mysql 'show tables;' query into something pgsql understands.
  838. function drush_sql_show_tables_pgsql() {
  839. return "select tablename from pg_tables where schemaname='public';";
  840. }
  841. /*
  842. * Drop all tables or DROP+CREATE target database.
  843. *
  844. * return boolean
  845. * TRUE or FALSE depending on success.
  846. */
  847. function drush_sql_empty_db($db_spec = NULL) {
  848. if (is_null($db_spec)) {
  849. $db_spec = _drush_sql_get_db_spec();
  850. }
  851. $sql = drush_sql_build_createdb_sql($db_spec, TRUE);
  852. // Get credentials to connect to the server, but not the database which we
  853. // are about to DROP. @see _drush_sql_get_credentials().
  854. $create_db_spec = $db_spec;
  855. unset($create_db_spec['database']);
  856. $create_db_su = drush_sql_su($create_db_spec);
  857. if (!_drush_sql_query($sql, $create_db_su)) {
  858. // If we could not drop the database, try instead to drop all
  859. // of the tables in the database (presuming it exists...).
  860. // If we cannot do either operation, then fail with an error.
  861. if (!_drush_sql_drop($db_spec)) {
  862. return drush_set_error(dt('Could not drop and create database: @name', array('@name' => $db_name)));
  863. }
  864. }
  865. return TRUE;
  866. }
  867. /**
  868. * Return a db_spec based on supplied db_url/db_prefix options or an existing
  869. * settings.php.
  870. */
  871. function drush_sql_read_db_spec() {
  872. if ($db_url = drush_get_option('db-url')) {
  873. // We were passed a db_url. Usually a fresh site.
  874. $db_spec = drush_convert_db_from_db_url($db_url);
  875. $db_spec['db_prefix'] = drush_get_option('db-prefix');
  876. return $db_spec;
  877. }
  878. elseif (drush_bootstrap(DRUSH_BOOTSTRAP_DRUPAL_CONFIGURATION)) {
  879. // We have an existing settings.php.
  880. $db_spec = _drush_sql_get_db_spec();
  881. $db_spec['db_prefix'] = $GLOBALS['db_prefix'];
  882. return $db_spec;
  883. }
  884. else {
  885. return FALSE;
  886. }
  887. }
  888. /*
  889. * Build DB connection array with superuser credentials if provided.
  890. */
  891. function drush_sql_su($db_spec) {
  892. $create_db_target = $db_spec;
  893. $create_db_target['database'] = '';
  894. $db_superuser = drush_get_option(array('db-su', 'target-db-su'));
  895. if (isset($db_superuser)) {
  896. $create_db_target['username'] = $db_superuser;
  897. }
  898. $db_su_pw = drush_get_option(array('db-su-pw', 'target-db-su-pw'));
  899. if (isset($db_su_pw)) {
  900. $create_db_target['password'] = $db_su_pw;
  901. }
  902. return $create_db_target;
  903. }
  904. /*
  905. * Build a SQL string for dropping and creating a database.
  906. *
  907. * @param array $db_spec
  908. * A database specification array.
  909. *
  910. * @param boolean $quoted
  911. * Quote the database name. Mysql uses backticks to quote which can cause problems
  912. * in a Windows shell. Set TRUE if the CREATE is not running on the bash command line.
  913. */
  914. function drush_sql_build_createdb_sql($db_spec, $quoted = FALSE) {
  915. switch (_drush_sql_get_scheme($db_spec)) {
  916. case 'mysql':
  917. $dbname = $quoted ? '`' . $db_spec['database'] . '`' : $db_spec['database'];
  918. $sql[] = sprintf('DROP DATABASE IF EXISTS %s; ', $dbname);
  919. $sql[] = sprintf('CREATE DATABASE %s /*!40100 DEFAULT CHARACTER SET utf8 */;', $dbname);
  920. $sql[] = sprintf('GRANT ALL PRIVILEGES ON %s.* TO \'%s\'@\'%s\'', $dbname, $db_spec['username'], $db_spec['host']);
  921. $sql[] = sprintf("IDENTIFIED BY '%s';", $db_spec['password']);
  922. $sql[] = 'FLUSH PRIVILEGES;';
  923. break;
  924. case 'pgsql':
  925. $dbname = $quoted ? '"' . $db_spec['database'] . '"' : $db_spec['database'];
  926. $sql[] = sprintf('drop database if exists %s;', $dbname);
  927. $sql[] = sprintf("create database %s ENCODING 'UTF8';", $dbname);
  928. break;
  929. }
  930. return implode(' ', $sql);
  931. }
  932. function drush_sql_build_exec($db_spec, $filepath) {
  933. $scheme = _drush_sql_get_scheme($db_spec);
  934. switch ($scheme) {
  935. case 'mysql':
  936. $exec = 'mysql';
  937. $exec .= _drush_sql_get_credentials($db_spec);
  938. $exec .= ' ' . drush_get_option('extra');
  939. $exec .= " < $filepath";
  940. break;
  941. case 'pgsql':
  942. $exec = 'psql -q ';
  943. $exec .= _drush_sql_get_credentials($db_spec);
  944. $exec .= ' ' . (drush_get_option('extra') ? drush_get_option('extra') : "--no-align --field-separator='\t' --pset footer=off");
  945. $exec .= " --file $filepath";
  946. break;
  947. case 'sqlite':
  948. $exec = 'sqlite3';
  949. $exec .= ' ' . drush_get_option('extra');
  950. $exec .= _drush_sql_get_credentials($db_spec);
  951. $exec .= " < $filepath";
  952. break;
  953. }
  954. return $exec;
  955. }