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_create Command callback. Create a database.
drush_sql_db_exists
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.
drush_sql_empty_db
drush_sql_format_oracle
drush_sql_get_table_selection
drush_sql_query
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().
sql_drush_help_alter Implements hook_drush_help_alter().
sql_sql_sync_complete Command argument complete callback.
_drush_sql_connect Command callback. Emits a connect string for mysql or pgsql.
_drush_sql_create
_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. 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'] = array(
  21. 'description' => 'The DB connection key if using multiple connections in settings.php.',
  22. 'example-value' => 'extra',
  23. );
  24. $options['target'] = array(
  25. 'description' => 'The name of a target within the specified database.',
  26. 'example-value' => 'key',
  27. // Gets unhidden in help_alter(). We only want to show this to D7 users but have to
  28. // declare it here since some commands do not bootstrap fully.
  29. 'hidden' => TRUE,
  30. );
  31. $db_url['db-url'] = array(
  32. 'description' => 'A Drupal 6 style database URL. Only required for initial install - not re-install.',
  33. 'example-value' => 'mysql://root:pass@127.0.0.1/db',
  34. );
  35. $items['sql-drop'] = array(
  36. 'description' => 'Drop all tables in a given database.',
  37. 'arguments' => array(
  38. ),
  39. 'bootstrap' => DRUSH_BOOTSTRAP_DRUSH,
  40. 'options' => array(
  41. 'yes' => 'Skip confirmation and proceed.',
  42. 'result-file' => array(
  43. 'description' => 'Save to a file. The file should be relative to Drupal root. Recommended.',
  44. 'example-value' => '/path/to/file',
  45. ),
  46. ) + $options + $db_url,
  47. 'topics' => array('docs-policy'),
  48. );
  49. $items['sql-conf'] = array(
  50. 'description' => 'Print database connection details using print_r().',
  51. 'hidden' => TRUE,
  52. 'bootstrap' => DRUSH_BOOTSTRAP_DRUSH,
  53. 'options' => array(
  54. 'all' => 'Show all database connections, instead of just one.',
  55. 'show-passwords' => 'Show database password.',
  56. ) + $options,
  57. );
  58. $items['sql-connect'] = array(
  59. 'description' => 'A string for connecting to the DB.',
  60. 'bootstrap' => DRUSH_BOOTSTRAP_DRUSH,
  61. 'options' => $options + $db_url,
  62. 'examples' => array(
  63. '`drush sql-connect` < example.sql' => 'Import sql statements from a file into the current database.',
  64. ),
  65. );
  66. $items['sql-create'] = array(
  67. 'description' => 'Create a database.',
  68. 'bootstrap' => DRUSH_BOOTSTRAP_DRUSH,
  69. 'examples' => array(
  70. 'drush sql-create' => 'Create the database for the current site.',
  71. 'drush @site.test sql-create' => 'Create the database as specified for @site.test.',
  72. 'drush sql-create --db-su=root --db-su-pw=rootpassword --db-url="mysql://drupal_db_user:drupal_db_password@127.0.0.1/drupal_db"' =>
  73. 'Create the database as specified in the db-url option.'
  74. ),
  75. 'options' => array(
  76. 'db-su' => 'Account to use when creating a new database. Optional.',
  77. 'db-su-pw' => 'Password for the "db-su" account. Optional.',
  78. ) + $options + $db_url,
  79. );
  80. $items['sql-dump'] = array(
  81. 'callback' => 'drush_sql_dump_execute',
  82. 'description' => 'Exports the Drupal DB as SQL using mysqldump or equivalent.',
  83. 'bootstrap' => DRUSH_BOOTSTRAP_DRUSH,
  84. 'examples' => array(
  85. 'drush sql-dump --result-file=../18.sql' => 'Save SQL dump to the directory above Drupal root.',
  86. 'drush sql-dump --skip-tables-key=common' => 'Skip standard tables. @see example.drushrc.php',
  87. ),
  88. 'options' => array(
  89. 'result-file' => array(
  90. 'description' => '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.',
  91. 'example-value' => '/path/to/file',
  92. 'value' => 'optional',
  93. ),
  94. 'skip-tables-key' => 'A key in the $skip_tables array. @see example.drushrc.php. Optional.',
  95. 'structure-tables-key' => 'A key in the $structure_tables array. @see example.drushrc.php. Optional.',
  96. 'tables-key' => 'A key in the $tables array. Optional.',
  97. 'skip-tables-list' => 'A comma-separated list of tables to exclude completely. Optional.',
  98. 'structure-tables-list' => 'A comma-separated list of tables to include for structure, but not data. Optional.',
  99. 'tables-list' => 'A comma-separated list of tables to transfer. Optional.',
  100. '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.',
  101. 'create-db' => array('hidden' => TRUE, 'description' => 'Omit DROP TABLE statements. Postgres and Oracle only. Used by sql-sync, since including the DROP TABLE statements interfere with the import when the database is created.'),
  102. 'data-only' => 'Dump data without statements to create any of the schema.',
  103. '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.',
  104. 'gzip' => 'Compress the dump using the gzip program which must be in your $PATH.',
  105. ) + $options + $db_url,
  106. );
  107. $items['sql-query'] = array(
  108. 'bootstrap' => DRUSH_BOOTSTRAP_DRUSH,
  109. 'description' => 'Execute a query against the site database.',
  110. 'examples' => array(
  111. 'drush sql-query "SELECT * FROM users WHERE uid=1"' => 'Browse user record. Table prefixes, if used, must be added to table names by hand.',
  112. 'drush sql-query --db-prefix "SELECT * FROM {users} WHERE uid=1"' => 'Browse user record. Table prefixes are honored. Caution: curly-braces will be stripped from all portions of the query.',
  113. '`drush sql-connect` < example.sql' => 'Import sql statements from a file into the current database.',
  114. 'drush sql-query --file=example.sql' => 'Alternate way to import sql statements from a file.',
  115. ),
  116. 'arguments' => array(
  117. 'query' => 'An SQL query. Ignored if \'file\' is provided.',
  118. ),
  119. 'options' => array(
  120. 'result-file' => array(
  121. 'description' => 'Save to a file. The file should be relative to Drupal root. Optional.',
  122. 'example-value' => '/path/to/file',
  123. ),
  124. 'file' => 'Path to a file containing the SQL to be run.',
  125. 'extra' => 'Add custom options to the mysql command.',
  126. 'db-prefix' => 'Enable replacement of braces in your query.',
  127. ) + $options + $db_url,
  128. 'aliases' => array('sqlq'),
  129. );
  130. $items['sql-sync'] = array(
  131. 'description' => 'Copy and import source database to target database. Transfers via rsync.',
  132. 'bootstrap' => DRUSH_BOOTSTRAP_DRUSH,
  133. 'drush dependencies' => array('core'), // core-rsync.
  134. 'examples' => array(
  135. 'drush sql-sync @prod @dev' => 'Copy the DB defined in sites/prod to the DB in sites/dev.',
  136. ),
  137. 'arguments' => array(
  138. 'from' => 'Name of subdirectory within /sites or a site-alias.',
  139. 'to' => 'Name of subdirectory within /sites or a site-alias.',
  140. ),
  141. 'options' => array(
  142. 'skip-tables-key' => 'A key in the $skip_tables array. @see example.drushrc.php. Optional.',
  143. 'skip-tables-list' => 'A comma-separated list of tables to exclude completely. Optional.',
  144. 'structure-tables-key' => 'A key in the $structure_tables array. @see example.drushrc.php. Optional.',
  145. 'structure-tables-list' => 'A comma-separated list of tables to include for structure, but not data. Optional.',
  146. 'tables-key' => 'A key in the $tables array. Optional.',
  147. 'tables-list' => 'A comma-separated list of tables to transfer. Optional.',
  148. 'cache' => 'Skip dump if result file exists and is less than "cache" hours old. Optional; default is 24 hours.',
  149. 'no-cache' => 'Do not cache the sql-dump file.',
  150. 'no-dump' => 'Do not dump the sql database; always use an existing dump file.',
  151. 'source-db-url' => 'Database specification for source system to dump from.',
  152. 'source-remote-port' => 'Override sql database port number in source-db-url. Optional.',
  153. 'source-remote-host' => 'Remote machine to run sql-dump file on. Optional; default is local machine.',
  154. 'source-dump' => 'Path to dump file. Optional; default is to create a temporary file.',
  155. 'source-database' => 'A key in the $db_url (D6) or $databases (D7+) array which provides the data.',
  156. 'source-target' => array(
  157. 'description' => 'A key within the SOURCE database identifying a particular server in the database group.',
  158. 'example-value' => 'key',
  159. // Gets unhidden in help_alter(). We only want to show to D7 users but have to
  160. // declare it here since this command does not bootstrap fully.
  161. 'hidden' => TRUE,
  162. ),
  163. 'target-db-url' => '',
  164. 'target-remote-port' => '',
  165. 'target-remote-host' => '',
  166. 'target-dump' => '',
  167. 'target-database' => 'A key in the $db_url (D6) or $databases (D7+) array which shall receive the data.',
  168. 'target-target' => array(
  169. 'description' => 'Oy. A key within the TARGET database identifying a particular server in the database group.',
  170. 'example-value' => 'key',
  171. // Gets unhidden in help_alter(). We only want to show to D7 users but have to
  172. // declare it here since this command does not bootstrap fully.
  173. 'hidden' => TRUE,
  174. ),
  175. 'temp' => 'Use a temporary file to hold dump files. Implies --no-cache.',
  176. 'dump-dir' => 'Directory to store sql dump files in when --source-dump or --target-dump are not used. Takes precedence over --temp.',
  177. 'create-db' => 'Create a new database before importing the database dump on the target machine.',
  178. 'db-su' => array(
  179. 'description' => 'Account to use when creating a new database. Optional.',
  180. 'example-value' => 'root',
  181. ),
  182. 'db-su-pw' => array(
  183. 'description' => 'Password for the "db-su" account. Optional.',
  184. 'example-value' => 'pass',
  185. ),
  186. '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.',
  187. 'sanitize' => 'Obscure email addresses and reset passwords in the user table post-sync. Optional.',
  188. ),
  189. 'sub-options' => array(
  190. 'sanitize' => array(
  191. 'sanitize-password' => 'The password to assign to all accounts in the sanitization operation, or "no" to keep passwords unchanged. Default is "password".',
  192. 'sanitize-email' => 'The pattern for test email addresses in the sanitization operation, or "no" to keep email addresses unchanged. May contain replacement patterns %uid, %mail or %name. Default is "user+%uid@localhost".',
  193. 'confirm-sanitizations' => 'Prompt yes/no after importing the database, but before running the sanitizations',
  194. ),
  195. ),
  196. 'topics' => array('docs-aliases', 'docs-policy', 'docs-example-sync-via-http', 'docs-example-sync-extension'),
  197. );
  198. $items['sql-cli'] = array(
  199. 'description' => "Open a SQL command-line interface using Drupal's credentials.",
  200. 'bootstrap' => DRUSH_BOOTSTRAP_DRUSH,
  201. 'options' => array(
  202. 'A' => 'Skip reading table information. This gives a quicker start of mysql.',
  203. ) + $db_url,
  204. 'aliases' => array('sqlc'),
  205. 'examples' => array(
  206. 'drush sql-cli' => "Open a SQL command-line interface using Drupal's credentials.",
  207. 'drush sql-cli -A' => "Open a SQL command-line interface using Drupal's credentials and skip reading table information.",
  208. ),
  209. );
  210. $items['sql-sanitize'] = array(
  211. 'description' => "Run sanitization operations on the current database.",
  212. 'bootstrap' => DRUSH_BOOTSTRAP_DRUSH,
  213. 'hidden' => TRUE,
  214. 'options' => array(
  215. 'sanitize-password' => 'The password to assign to all accounts in the sanitization operation, or "no" to keep passwords unchanged. Default is "password".',
  216. 'sanitize-email' => 'The pattern for test email addresses in the sanitization operation, or "no" to keep email addresses unchanged. May contain replacement patterns %uid, %mail or %name. Default is "user+%uid@localhost".',
  217. ) + $db_url,
  218. 'aliases' => array('sqlsan'),
  219. );
  220. return $items;
  221. }
  222. /**
  223. * Implements hook_drush_help_alter().
  224. */
  225. function sql_drush_help_alter(&$command) {
  226. // Drupal 7+ only options.
  227. if (drush_drupal_major_version() >= 7) {
  228. if ($command['command'] == 'sql-sync') {
  229. unset($command['options']['source-target']['hidden'], $command['options']['target-target']['hidden']);
  230. }
  231. elseif ($command['commandfile'] == 'sql') {
  232. unset($command['options']['target']['hidden']);
  233. }
  234. }
  235. }
  236. /**
  237. * Command argument complete callback.
  238. *
  239. * @return
  240. * Array of available site aliases.
  241. */
  242. function sql_sql_sync_complete() {
  243. return array('values' => array_keys(_drush_sitealias_all_list()));
  244. }
  245. /**
  246. * Check whether further bootstrap is needed. If so, do it.
  247. */
  248. function drush_sql_bootstrap_further() {
  249. if (!drush_get_option('db-url')) {
  250. drush_bootstrap_max(DRUSH_BOOTSTRAP_DRUPAL_CONFIGURATION);
  251. }
  252. }
  253. /**
  254. * Command callback. Displays the Drupal site's database connection string.
  255. */
  256. function drush_sql_conf() {
  257. // Under Drupal 7, if the database is configured but empty, then
  258. // DRUSH_BOOTSTRAP_DRUPAL_CONFIGURATION will throw an exception.
  259. // If this happens, we'll just catch it and continue.
  260. // TODO: Fix this in the bootstrap, per http://drupal.org/node/1996004
  261. try {
  262. drush_bootstrap_max(DRUSH_BOOTSTRAP_DRUPAL_CONFIGURATION);
  263. }
  264. catch (Exception $e) {
  265. }
  266. if (drush_get_option('db-url', FALSE)) {
  267. $db_spec['db-url'] = $GLOBALS['db_url'];
  268. }
  269. elseif (drush_get_option('all', FALSE)) {
  270. $db_spec = _drush_sql_get_all_db_specs();
  271. }
  272. if (!isset($db_spec)) {
  273. $db_spec = _drush_sql_get_db_spec();
  274. }
  275. $return = $db_spec;
  276. if (!drush_get_option('show-passwords', FALSE)) {
  277. drush_unset_recursive($db_spec, 'password');
  278. }
  279. drush_print_r($db_spec);
  280. return $return;
  281. }
  282. /**
  283. * Command callback. Emits a connect string for mysql or pgsql.
  284. */
  285. function _drush_sql_connect($db_spec = NULL) {
  286. switch (_drush_sql_get_scheme($db_spec)) {
  287. case 'mysql':
  288. $command = 'mysql';
  289. if (drush_get_option('A', FALSE)) {
  290. $command .= ' -A';
  291. }
  292. break;
  293. case 'pgsql':
  294. $command = 'psql';
  295. break;
  296. case 'sqlite':
  297. $command = 'sqlite3';
  298. break;
  299. case 'sqlsrv':
  300. $command = 'sqlcmd';
  301. break;
  302. case 'oracle':
  303. // use rlwrap if available for readline support
  304. if ($handle = popen('rlwrap -v', 'r')) {
  305. $command = 'rlwrap sqlplus';
  306. pclose($handle);
  307. }
  308. else {
  309. $command = 'sqlplus';
  310. }
  311. break;
  312. }
  313. $command .= _drush_sql_get_credentials($db_spec);
  314. return $command;
  315. }
  316. function drush_sql_connect() {
  317. drush_sql_bootstrap_further();
  318. $connect = _drush_sql_connect();
  319. drush_print($connect);
  320. return $connect;
  321. }
  322. /**
  323. * Command callback. Create a database.
  324. */
  325. function drush_sql_create() {
  326. $db_spec = _drush_sql_get_db_spec();
  327. // Prompt for confirmation.
  328. if (!drush_get_context('DRUSH_SIMULATE')) {
  329. $txt_destination = (isset($db_spec['remote-host']) ? $db_spec['remote-host'] . '/' : '') . $db_spec['database'];
  330. drush_print(dt("Creating database !target. Any possible existing database will be dropped!", array('!target' => $txt_destination)));
  331. if (!drush_confirm(dt('Do you really want to continue?'))) {
  332. return drush_user_abort();
  333. }
  334. }
  335. return _drush_sql_create($db_spec);
  336. }
  337. function _drush_sql_create($db_spec) {
  338. $sql = drush_sql_build_createdb_sql($db_spec, TRUE);
  339. // Get credentials to connect to the server, but not the database which we
  340. // are about to DROP. @see _drush_sql_get_credentials().
  341. $create_db_spec = $db_spec;
  342. unset($create_db_spec['database']);
  343. $create_db_su = drush_sql_su($create_db_spec);
  344. return _drush_sql_query($sql, $create_db_su);
  345. }
  346. /**
  347. * Command callback. Outputs the entire Drupal database in SQL format using mysqldump.
  348. */
  349. function drush_sql_dump_execute() {
  350. drush_sql_bootstrap_further();
  351. list($exec, $file) = drush_sql_dump();
  352. // Avoid the php memory of the $output array in drush_shell_exec().
  353. if (!$return = drush_op_system($exec)) {
  354. if ($file) {
  355. drush_log(dt('Database dump saved to !path', array('!path' => $file)), 'success');
  356. }
  357. }
  358. else {
  359. return drush_set_error('DRUSH_SQL_DUMP_FAIL', 'Database dump failed');
  360. }
  361. }
  362. function drush_sql_get_table_selection() {
  363. // Skip large core tables if instructed. Also used by 'sql-sync' command.
  364. $skip_tables = _drush_sql_get_table_list('skip-tables');
  365. // Skip any structure-tables as well.
  366. $structure_tables = _drush_sql_get_table_list('structure-tables');
  367. // Dump only the specified tables. Takes precedence over skip-tables and structure-tables.
  368. $tables = _drush_sql_get_table_list('tables');
  369. return array('skip' => $skip_tables, 'structure' => $structure_tables, 'tables' => $tables);
  370. }
  371. /**
  372. * Build a mysqldump/pg_dump/sqlite statement.
  373. *
  374. * @param db_spec
  375. * For /D6, a $db_url. For D7+, a target in the default DB connection.
  376. * @return array
  377. * An array with items.
  378. * 1. A mysqldump/pg_dump/sqlite statement that is ready for executing.
  379. * 2. The filepath where the dump will be saved.
  380. */
  381. function drush_sql_dump($db_spec = NULL) {
  382. return drush_sql_build_dump_command(drush_sql_get_table_selection(), $db_spec, drush_get_option('result-file', FALSE));
  383. }
  384. /**
  385. * Build a mysqldump/pg_dump/sqlite statement.
  386. *
  387. * @param array $table_selection
  388. * Supported keys: 'skip', 'structure', 'tables'.
  389. * @param db_spec
  390. * For D5/D6, a $db_url. For D7, a target in the default DB connection.
  391. * @param file
  392. * Destination for the dump file.
  393. * @return array
  394. * An array with items.
  395. * 1. A mysqldump/pg_dump/sqlite statement that is ready for executing.
  396. * 2. The filepath where the dump will be saved.
  397. */
  398. function drush_sql_build_dump_command($table_selection, $db_spec = NULL, $file = FALSE) {
  399. $skip_tables = $table_selection['skip'];
  400. $structure_tables = $table_selection['structure'];
  401. $tables = $table_selection['tables'];
  402. $ignores = array();
  403. $skip_tables = array_merge($structure_tables, $skip_tables);
  404. $data_only = drush_get_option('data-only');
  405. // The ordered-dump option is only supported by MySQL for now.
  406. // @todo add documention once a hook for drush_get_option_help() is available.
  407. // @see drush_get_option_help() in drush.inc
  408. $ordered_dump = drush_get_option('ordered-dump');
  409. if (is_null($db_spec)) {
  410. $db_spec = _drush_sql_get_db_spec();
  411. }
  412. $database = $db_spec['database'];
  413. // $file is passed in to us usually via --result-file. If the user
  414. // has set $options['result-file'] = TRUE, then we
  415. // will generate an SQL dump file in the same backup
  416. // directory that pm-updatecode uses.
  417. if ($file) {
  418. if ($file === TRUE) {
  419. // User did not pass a specific value for --result-file. Make one.
  420. $backup = drush_include_engine('version_control', 'backup');
  421. $backup_dir = $backup->prepare_backup_dir($db_spec['database']);
  422. if (empty($backup_dir)) {
  423. $backup_dir = "/tmp";
  424. }
  425. $file = $backup_dir . '/@DATABASE_@DATE.sql';
  426. }
  427. $file = str_replace(array('@DATABASE', '@DATE'), array($database, gmdate('Ymd_His')), $file);
  428. }
  429. switch (_drush_sql_get_scheme($db_spec)) {
  430. case 'mysqli':
  431. case 'mysql':
  432. $exec = 'mysqldump';
  433. if ($file) {
  434. $exec .= ' --result-file '. $file;
  435. }
  436. // mysqldump wants 'databasename' instead of 'database=databasename' for no good reason.
  437. // We had --skip-add-locks here for a while to help people with insufficient permissions,
  438. // but removed it because it slows down the import a lot. See http://drupal.org/node/1283978
  439. $extra = ' --no-autocommit --single-transaction --opt -Q' . str_replace('--database=', ' ', _drush_sql_get_credentials($db_spec));
  440. if (isset($data_only)) {
  441. $extra .= ' --no-create-info';
  442. }
  443. if (isset($ordered_dump)) {
  444. $extra .= ' --skip-extended-insert --order-by-primary';
  445. }
  446. $exec .= $extra;
  447. if (!empty($tables)) {
  448. $exec .= ' ' . implode(' ', $tables);
  449. }
  450. else {
  451. // Append the ignore-table options.
  452. foreach ($skip_tables as $table) {
  453. $ignores[] = "--ignore-table=$database.$table";
  454. }
  455. $exec .= ' '. implode(' ', $ignores);
  456. // Run mysqldump again and append output if we need some structure only tables.
  457. if (!empty($structure_tables)) {
  458. $exec .= " && mysqldump --no-data $extra " . implode(' ', $structure_tables);
  459. if ($file) {
  460. $exec .= " >> $file";
  461. }
  462. }
  463. }
  464. break;
  465. case 'pgsql':
  466. $create_db = drush_get_option('create-db');
  467. $exec = 'pg_dump ';
  468. if ($file) {
  469. $exec .= ' --file '. $file;
  470. }
  471. // Unlike psql, pg_dump does not take a '--dbname=' before the database name.
  472. $extra = str_replace('--dbname=', ' ', _drush_sql_get_credentials($db_spec));
  473. if (isset($data_only)) {
  474. $extra .= ' --data-only';
  475. }
  476. $exec .= $extra;
  477. $exec .= (!isset($create_db) && !isset($data_only) ? ' --clean' : '');
  478. if (!empty($tables)) {
  479. foreach ($tables as $table) {
  480. $exec .= " --table=$table";
  481. }
  482. }
  483. else {
  484. foreach ($skip_tables as $table) {
  485. $ignores[] = "--exclude-table=$table";
  486. }
  487. $exec .= ' '. implode(' ', $ignores);
  488. // Run pg_dump again and append output if we need some structure only tables.
  489. if (!empty($structure_tables)) {
  490. $schemaonlies = array();
  491. foreach ($structure_tables as $table) {
  492. $schemaonlies[] = "--table=$table";
  493. }
  494. $exec .= " && pg_dump --schema-only " . implode(' ', $schemaonlies) . $extra;
  495. if ($file) {
  496. $exec .= " >> $file";
  497. }
  498. }
  499. }
  500. break;
  501. case 'sqlite':
  502. // Dumping is usually not necessary in SQLite, since all database data
  503. // is stored in a single file on the filesystem which can be copied just
  504. // like any other file. But it still has a use in migration purposes and
  505. // building human-readable diffs and such, so let's do it anyway.
  506. $exec = _drush_sql_connect($db_spec);
  507. // SQLite's dump command doesn't support many of the features of its
  508. // Postgres or MySQL equivalents. We may be able to fake some in the
  509. // future, but for now, let's just support simple dumps.
  510. $exec .= ' ".dump"';
  511. if ($file) {
  512. $exec .= ' > '. $file;
  513. }
  514. break;
  515. case 'sqlsrv':
  516. // Change variable '$file' by reference in order to get drush_log() to report.
  517. if (!$file) {
  518. $file = $db_spec['database'] . '_' . date('Ymd_His') . '.bak';
  519. }
  520. $exec = "sqlcmd -U \"" . $db_spec['username'] . "\" -P \"" . $db_spec['password'] . "\" -S \"" . $db_spec['host'] . "\" -Q \"BACKUP DATABASE [" . $db_spec['database'] . "] TO DISK='" . $file . "'\"";
  521. break;
  522. case 'oracle':
  523. $create_db = drush_get_option('create-db');
  524. $exec = 'exp ' . _drush_sql_get_credentials($db_spec);
  525. // Change variable '$file' by reference in order to get drush_log() to report.
  526. if (!$file) {
  527. $file = $db_spec['username'] . '.dmp';
  528. }
  529. $exec .= ' file=' . $file;
  530. if (!empty($tables))
  531. $exec .= ' tables="(' . implode(',', $tables) . ')"';
  532. else
  533. $exec .= ' owner=' . $db_spec['username'];
  534. break;
  535. }
  536. if (drush_get_option('gzip')) {
  537. if ($file) {
  538. $escfile = drush_escapeshellarg($file);
  539. if (drush_get_context('DRUSH_AFFIRMATIVE')) {
  540. // Gzip the result-file without Gzip confirmation
  541. $exec .= " && gzip -f $escfile";
  542. $file .= '.gz';
  543. }
  544. else {
  545. // Gzip the result-file
  546. $exec .= " && gzip $escfile";
  547. $file .= '.gz';
  548. }
  549. }
  550. else {
  551. // gzip via pipe since user has not specified a file.
  552. $exec .= "| gzip";
  553. }
  554. }
  555. return array($exec, $file);
  556. }
  557. /**
  558. * Consult the specified options and return the list of tables
  559. * specified.
  560. *
  561. * @param option_name
  562. * The option name to check: skip-tables, structure-tables
  563. * or tables. This function will check both *-key and *-list,
  564. * and, in the case of sql-sync, will also check target-*
  565. * and source-*, to see if an alias set one of these options.
  566. * @returns array
  567. * Returns an array of tables based on the first option
  568. * found, or an empty array if there were no matches.
  569. */
  570. function _drush_sql_get_table_list($option_name) {
  571. foreach(array('' => 'cli', 'target-,,source-' => NULL) as $prefix_list => $context) {
  572. foreach(explode(',',$prefix_list) as $prefix) {
  573. $key_list = drush_get_option($prefix . $option_name . '-key', NULL, $context);
  574. foreach(explode(',', $key_list) as $key) {
  575. $all_tables = drush_get_option($option_name, array());
  576. if (array_key_exists($key, $all_tables)) {
  577. return $all_tables[$key];
  578. }
  579. if ($option_name != 'tables') {
  580. $all_tables = drush_get_option('tables', array());
  581. if (array_key_exists($key, $all_tables)) {
  582. return $all_tables[$key];
  583. }
  584. }
  585. }
  586. $table_list = drush_get_option($prefix . $option_name . '-list', NULL, $context);
  587. if (isset($table_list)) {
  588. return empty($table_list) ? array() : explode(',', $table_list);
  589. }
  590. }
  591. }
  592. return array();
  593. }
  594. /**
  595. * Command callback. Executes the given SQL query on the Drupal database.
  596. */
  597. function drush_sql_query($query = NULL) {
  598. drush_sql_bootstrap_further();
  599. $filename = drush_get_option('file', NULL);
  600. // Enable prefix processing when db-prefix option is used.
  601. if (drush_get_option('db-prefix')) {
  602. drush_bootstrap_max(DRUSH_BOOTSTRAP_DRUPAL_DATABASE);
  603. }
  604. $result = _drush_sql_query($query, NULL, $filename);
  605. if (!$result) {
  606. return drush_set_error('DRUSH_SQL_NO_QUERY', dt('Query failed.'));
  607. }
  608. return TRUE;
  609. }
  610. /*
  611. * Execute a SQL query.
  612. *
  613. * @param string $query
  614. * The SQL to be executed. Should be NULL if $file is provided.
  615. * @param array $db_spec
  616. * A database target.
  617. * @param string $filename
  618. * A path to a file containing the SQL to be executed.
  619. */
  620. function _drush_sql_query($query, $db_spec = NULL, $filename = NULL) {
  621. $suffix = '';
  622. $scheme = _drush_sql_get_scheme($db_spec);
  623. // Inject table prefixes as needed.
  624. if (drush_has_boostrapped(DRUSH_BOOTSTRAP_DRUPAL_DATABASE)) {
  625. if ($filename) {
  626. $query = file_get_contents($filename);
  627. }
  628. // Enable prefix processing which can be dangerous so off by default. See http://drupal.org/node/1219850.
  629. if (drush_get_option('db-prefix')) {
  630. if (drush_drupal_major_version() >= 7) {
  631. $query = Database::getConnection()->prefixTables($query);
  632. }
  633. else {
  634. $query = db_prefix_tables($query);
  635. }
  636. }
  637. }
  638. // is this an oracle query
  639. if ($scheme == 'oracle') {
  640. $query = drush_sql_format_oracle($query);
  641. $suffix = '.sql';
  642. }
  643. // Convert mysql 'show tables;' query into something pgsql understands
  644. if (($scheme == 'pgsql') && ($query == 'show tables;')) {
  645. $query = drush_sql_show_tables_pgsql();
  646. }
  647. // Save $query to a tmp file if needed. We will redirect it in.
  648. if (!$filename) {
  649. $filename = drush_save_data_to_temp_file($query, $suffix);
  650. }
  651. $exec = drush_sql_build_exec($db_spec, $filename);
  652. if ($output_file = drush_get_option('result-file')) {
  653. $exec .= ' > '. drush_escapeshellarg($output_file);
  654. }
  655. // In --simulate mode, drush_op will show the call to mysql or psql,
  656. // but the sql query itself is stored in a temp file and not displayed.
  657. // We will therefore show the query explicitly in the interest of full disclosure.
  658. if (drush_get_context('DRUSH_SIMULATE')) {
  659. drush_print('sql-query: ' . $query);
  660. if (!empty($exec)) {
  661. drush_print('exec: ' . $exec);
  662. }
  663. return TRUE;
  664. }
  665. if (empty($scheme)) {
  666. return drush_set_error('DRUSH_SQL_NO_DATABASE', dt("No database to operate on."));
  667. }
  668. if (empty($exec)) {
  669. return drush_set_error('DRUSH_SQL_NO_QUERY', 'No query provided');
  670. }
  671. return (drush_op_system($exec) == 0);
  672. }
  673. function drush_sql_drop() {
  674. drush_sql_bootstrap_further();
  675. $db_spec = _drush_sql_get_db_spec();
  676. if (!$db_spec) {
  677. return drush_set_error('DRUSH_SQL_NO_DATABASE', dt("No database to operate on."));
  678. }
  679. if (!drush_confirm(dt('Do you really want to drop all tables in the database !db?', array('!db' => $db_spec['database'])))) {
  680. return drush_user_abort();
  681. }
  682. _drush_sql_drop($db_spec);
  683. }
  684. // n.b. site-install uses _drush_sql_drop as a fallback technique if
  685. // drop database; create database fails. If _drush_sql_drop
  686. // is rewritten to also use that technique, it should maintain
  687. // the drop tables code here as a fallback.
  688. function _drush_sql_drop($db_spec = NULL) {
  689. // TODO: integrate with _drush_sql_get_table_list?
  690. $suffix = '';
  691. $scheme = _drush_sql_get_scheme($db_spec);
  692. switch ($scheme) {
  693. case 'pgsql':
  694. $query = drush_sql_show_tables_pgsql();
  695. break;
  696. case 'sqlite':
  697. $query = '.tables';
  698. break;
  699. case 'sqlsrv':
  700. $query = 'SELECT TABLE_NAME FROM information_schema.tables';
  701. break;
  702. case 'oracle':
  703. $query = "SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME NOT IN ('BLOBS','LONG_IDENTIFIERS')";
  704. $suffix = '.sql';
  705. break;
  706. default:
  707. $query = 'SHOW TABLES;';
  708. }
  709. $filename = drush_save_data_to_temp_file($query, $suffix);
  710. $exec = drush_sql_build_exec($db_spec, $filename);
  711. // Actually run this prep query no matter if in SIMULATE.
  712. $old = drush_get_context('DRUSH_SIMULATE');
  713. drush_set_context('DRUSH_SIMULATE', FALSE);
  714. drush_shell_exec($exec);
  715. drush_set_context('DRUSH_SIMULATE', $old);
  716. if ($tables = drush_shell_exec_output()) {
  717. if ($scheme === 'sqlite') {
  718. // SQLite's '.tables' command always outputs the table names in a column
  719. // format, like this:
  720. // table_alpha table_charlie table_echo
  721. // table_bravo table_delta table_foxtrot
  722. // …and there doesn't seem to be a way to fix that. So we need to do some
  723. // clean-up.
  724. // Since we're already doing iteration here, might as well build the SQL
  725. // too, since SQLite only wants one table per DROP TABLE command (so we have
  726. // to do "DROP TABLE foo; DROP TABLE bar;" instead of
  727. // "DROP TABLE foo, bar;").
  728. $sql = '';
  729. foreach ($tables as $line) {
  730. preg_match_all('/[^\s]+/', $line, $matches);
  731. if (!empty($matches[0])) {
  732. foreach ($matches[0] as $match) {
  733. $sql .= "DROP TABLE {$match};";
  734. }
  735. }
  736. }
  737. // We can't use drush_op('db_query', $sql) because it will only perform one
  738. // SQL command and we're technically performing several.
  739. $exec = _drush_sql_connect($db_spec);
  740. $exec .= " '{$sql}'";
  741. return drush_op_system($exec) == 0;
  742. }
  743. elseif ($scheme === 'sqlsrv') {
  744. // Shift off the header of the column of data returned.
  745. array_pop($tables);
  746. array_pop($tables);
  747. $sql = 'DROP TABLE '. implode(', ', $tables);
  748. return _drush_sql_query($sql, $db_spec);
  749. }
  750. else {
  751. // Shift off the header of the column of data returned.
  752. array_shift($tables);
  753. $sql = 'DROP TABLE '. implode(', ', $tables);
  754. return _drush_sql_query($sql, $db_spec);
  755. }
  756. }
  757. else {
  758. drush_log(dt('No tables to drop.'), 'ok');
  759. }
  760. return TRUE;
  761. }
  762. function drush_sql_cli() {
  763. drush_sql_bootstrap_further();
  764. drush_shell_proc_open(_drush_sql_connect());
  765. }
  766. /**
  767. * Command callback. Run's the sanitization operations on the current database.
  768. */
  769. function drush_sql_sanitize() {
  770. if (!drush_confirm(dt('Do you really want to sanitize the current database?'))) {
  771. return drush_user_abort();
  772. }
  773. drush_sql_bootstrap_further();
  774. drush_include(DRUSH_BASE_PATH . '/commands/sql', 'sync.sql');
  775. drush_command_invoke_all('drush_sql_sync_sanitize', 'default');
  776. $options = drush_get_context('post-sync-ops');
  777. if (!empty($options)) {
  778. if (!drush_get_context('DRUSH_SIMULATE')) {
  779. $messages = _drush_sql_get_post_sync_messages();
  780. if ($messages) {
  781. drush_print();
  782. drush_print($messages);
  783. }
  784. }
  785. }
  786. $sanitize_query = '';
  787. foreach($options as $id => $data) {
  788. $sanitize_query .= $data['query'] . " ";
  789. }
  790. if ($sanitize_query) {
  791. if (!drush_get_context('DRUSH_SIMULATE')) {
  792. drush_sql_query($sanitize_query);
  793. }
  794. else {
  795. drush_print("Executing: $sanitize_query");
  796. }
  797. }
  798. }
  799. //////////////////////////////////////////////////////////////////////////////
  800. // SQL SERVICE HELPERS
  801. /**
  802. * Get a database specification for the active DB connection. Honors the
  803. * 'database' and 'target command' line options. Honors a --db-url option.
  804. *
  805. * @return
  806. * An info array describing a database target.
  807. */
  808. function _drush_sql_get_db_spec() {
  809. $database = drush_get_option('database', 'default');
  810. $target = drush_get_option('target', 'default');
  811. if ($url = drush_get_option('db-url')) {
  812. $url = is_array($url) ? $url[$database] : $url;
  813. $db_spec = drush_convert_db_from_db_url($url);
  814. $db_spec['db_prefix'] = drush_get_option('db-prefix');
  815. return $db_spec;
  816. }
  817. elseif (($databases = drush_get_option('databases')) && (array_key_exists($database, $databases)) && (array_key_exists($target, $databases[$database]))) {
  818. return $databases[$database][$target];
  819. }
  820. elseif (drush_bootstrap_max(DRUSH_BOOTSTRAP_DRUPAL_CONFIGURATION)) {
  821. switch (drush_drupal_major_version()) {
  822. case 6:
  823. if ($url = isset($GLOBALS['db_url']) ? $GLOBALS['db_url'] : drush_get_option('db-url', NULL)) {
  824. $url = is_array($url) ? $url[$database] : $url;
  825. $db_spec = drush_convert_db_from_db_url($url);
  826. $db_spec['db_prefix'] = isset($GLOBALS['db_prefix']) ? $GLOBALS['db_prefix'] : drush_get_option('db-prefix', NULL);
  827. return $db_spec;
  828. }
  829. return NULL;
  830. default:
  831. // We don't use DB API here `sql-sync` would have to messily addConnection.
  832. if (!isset($GLOBALS['databases']) || !array_key_exists($database, $GLOBALS['databases']) || !array_key_exists($target, $GLOBALS['databases'][$database])) {
  833. return NULL;
  834. }
  835. return $GLOBALS['databases'][$database][$target];
  836. }
  837. }
  838. }
  839. function _drush_sql_get_all_db_specs() {
  840. switch (drush_drupal_major_version()) {
  841. case 6:
  842. if (!isset($GLOBALS['db_url'])) {
  843. return NULL;
  844. }
  845. return drush_sitealias_convert_db_from_db_url($GLOBALS['db_url']);
  846. default:
  847. if (!isset($GLOBALS['databases'])) {
  848. return NULL;
  849. }
  850. return $GLOBALS['databases'];
  851. }
  852. }
  853. function _drush_sql_get_spec_from_options($prefix, $default_to_self = TRUE) {
  854. $db_spec = NULL;
  855. $databases = drush_get_option($prefix . 'databases');
  856. if (isset($databases) && !empty($databases)) {
  857. $database = drush_get_option($prefix . 'database', 'default');
  858. $target = drush_get_option($prefix . 'target', 'default');
  859. if (array_key_exists($database, $databases) && array_key_exists($target, $databases[$database])) {
  860. $db_spec = $databases[$database][$target];
  861. }
  862. }
  863. else {
  864. $db_url = drush_get_option($prefix . 'db-url');
  865. if (isset($db_url)) {
  866. $db_spec = drush_convert_db_from_db_url($db_url);
  867. }
  868. elseif ($default_to_self) {
  869. $db_spec = _drush_sql_get_db_spec();
  870. }
  871. }
  872. if (isset($db_spec)) {
  873. $remote_host = drush_get_option($prefix . 'remote-host');
  874. if (!drush_is_local_host($remote_host)) {
  875. $db_spec['remote-host'] = $remote_host;
  876. $db_spec['port'] = drush_get_option($prefix . 'remote-port', (isset($db_spec['port']) ? $db_spec['port'] : NULL));
  877. }
  878. }
  879. return $db_spec;
  880. }
  881. /**
  882. * Determine where to store an sql dump file. This
  883. * function is called by sql-sync.
  884. */
  885. function drush_sql_dump_file(&$site_record) {
  886. $site_record['dump-is-temp'] = FALSE;
  887. // If the user has set the --{prefix}-dump option, then
  888. // use the exact name provided.
  889. $dump_file = drush_sitealias_get_path_option($site_record, 'dump');
  890. if (!isset($dump_file)) {
  891. $databases = sitealias_get_databases_from_record($site_record);
  892. if (isset($databases)) {
  893. $db_spec = $databases['default']['default'];
  894. // Make a base filename pattern to use to name the dump file
  895. $filename_pattern = $db_spec['database'];
  896. if (isset($db_spec['remote-host'])) {
  897. $filename_pattern = $db_spec['remote-host'] . '_' . $filename_pattern;
  898. }
  899. }
  900. // If the user has set the --dump-dir option, then
  901. // store persistant sql dump files there.
  902. $dump_dir = drush_sitealias_get_path_option($site_record, 'dump-dir');
  903. $use_temp_file = drush_sitealias_get_path_option($site_record, 'temp') || !$dump_dir;
  904. $remote = isset($site_record['remote-host']);
  905. // If this is a remote site, try to find a writable tmpdir.
  906. if (!isset($dump_dir) && $remote) {
  907. // If the alias is remote, we'll add on the 'sql' suffix.
  908. // As mentioned in drush_tempnam, providing a suffix removes
  909. // the guarentee of tempnam to return a unique filename;
  910. // however, when the file is going to be used on a remote
  911. // system, there is no such guarentee anyway, so we might
  912. // as well include it.
  913. $suffix = '.sql';
  914. $remote_site = $site_record;
  915. unset($remote_site['root']);
  916. unset($remote_site['uri']);
  917. $result = drush_invoke_process($site_record, 'php-eval', array('return drush_find_tmp();'), array(), array('integrate' => FALSE, 'override-simulated' => TRUE));
  918. // If the call to invoke process does not work for some reason
  919. // (e.g. drush not installed on the target machine),
  920. // then we will just presume that the tmp dir is '/tmp'.
  921. if (!$result || !empty($result['error_status']) || empty($result['object'])) {
  922. $dump_dir = '/tmp';
  923. }
  924. else {
  925. $dump_dir = trim($result['output']);
  926. }
  927. }
  928. if ($use_temp_file) {
  929. $dump_file = drush_tempnam($filename_pattern, $dump_dir, $remote ? '.sql' : '');
  930. // If $dump_dir does not exist, tempname will use the system
  931. // directory instead. That is the behavior we want on the local
  932. // system, but on remote systems, force the result back to the
  933. // specified directory.
  934. if ($remote) {
  935. $dump_file = $dump_dir . '/' . basename($dump_file);
  936. }
  937. $site_record['dump-is-temp'] = TRUE;
  938. }
  939. else {
  940. $dump_file = $dump_dir . '/' . $filename_pattern . '.sql';
  941. }
  942. }
  943. return $dump_file;
  944. }
  945. function _drush_sql_get_scheme($db_spec = NULL) {
  946. if (is_null($db_spec)) {
  947. $db_spec = _drush_sql_get_db_spec();
  948. }
  949. return $db_spec['driver'];
  950. }
  951. /**
  952. * Build a fragment containing credentials and mysql-connection parameters.
  953. *
  954. * @param $db_spec
  955. * @return string
  956. */
  957. function _drush_sql_get_credentials($db_spec = NULL) {
  958. if (is_null($db_spec)) {
  959. $db_spec = _drush_sql_get_db_spec();
  960. }
  961. // Build an array of key-value pairs for the parameters.
  962. $parameters = array();
  963. switch (_drush_sql_get_scheme($db_spec)) {
  964. case 'mysql':
  965. // Some drush commands (e.g. site-install) want to connect to the
  966. // server, but not the database. Connect to the built-in database.
  967. $parameters['database'] = empty($db_spec['database']) ? 'information_schema' : $db_spec['database'];
  968. // Default to unix socket if configured.
  969. if (!empty($db_spec['unix_socket'])) {
  970. $parameters['socket'] = $db_spec['unix_socket'];
  971. }
  972. // EMPTY host is not the same as NO host, and is valid (see unix_socket).
  973. elseif (isset($db_spec['host'])) {
  974. $parameters['host'] = $db_spec['host'];
  975. }
  976. if (!empty($db_spec['port'])) {
  977. $parameters['port'] = $db_spec['port'];
  978. }
  979. // User is required. Drupal calls it 'username'. MySQL calls it 'user'.
  980. $parameters['user'] = $db_spec['username'];
  981. // EMPTY password is not the same as NO password, and is valid.
  982. if (isset($db_spec['password'])) {
  983. $parameters['password'] = $db_spec['password'];
  984. }
  985. break;
  986. case 'pgsql':
  987. // Some drush commands (e.g. site-install) want to connect to the
  988. // server, but not the database. Connect to the built-in database.
  989. $parameters['dbname'] = empty($db_spec['database']) ? 'template1' : $db_spec['database'];
  990. // Host and port are optional but have defaults.
  991. $parameters['host'] = empty($db_spec['host']) ? 'localhost' : $db_spec['host'];
  992. $parameters['port'] = empty($db_spec['port']) ? '5432' : $db_spec['port'];
  993. // Username is required.
  994. $parameters['username'] = $db_spec['username'];
  995. // Don't set the password.
  996. // @see http://drupal.org/node/438828
  997. break;
  998. case 'sqlite':
  999. // SQLite doesn't do user management, instead relying on the filesystem
  1000. // for that. So the only info we really need is the path to the database
  1001. // file, and not as a "--key=value" parameter.
  1002. return ' ' . $db_spec['database'];
  1003. break;
  1004. case 'sqlsrv':
  1005. // Some drush commands (e.g. site-install) want to connect to the
  1006. // server, but not the database. Connect to the built-in database.
  1007. $database = empty($db_spec['database']) ? 'master' : $db_spec['database'];
  1008. // Host and port are optional but have defaults.
  1009. $host = empty($db_spec['host']) ? '.\SQLEXPRESS' : $db_spec['host'];
  1010. return ' -S ' . $host . ' -d ' . $database . ' -U ' . $db_spec['username'] . ' -P ' . $db_spec['password'];
  1011. break;
  1012. case 'oracle':
  1013. // Return an Oracle connection string
  1014. return ' ' . $db_spec['username'] .'/' . $db_spec['password'] . ($db_spec['host']=='USETNS' ? '@' . $db_spec['database'] : '@//' . $db_spec['host'] . ':' . ($db_spec['port'] ? $db_spec['port'] : '1521') . '/' . $db_spec['database']);
  1015. break;
  1016. }
  1017. // Turn each parameter into a valid parameter string.
  1018. $parameter_strings = array();
  1019. foreach ($parameters as $key => $value) {
  1020. // Only escape the values, not the keys or the rest of the string.
  1021. $value = drush_escapeshellarg($value);
  1022. $parameter_strings[] = "--$key=$value";
  1023. }
  1024. // Join the parameters and return.
  1025. return ' ' . implode(' ', $parameter_strings);
  1026. }
  1027. function _drush_sql_get_invalid_url_msg($db_spec = NULL) {
  1028. if (is_null($db_spec)) {
  1029. $db_spec = _drush_sql_get_db_spec();
  1030. }
  1031. switch (drush_drupal_major_version()) {
  1032. case 6:
  1033. return dt('Unable to parse DB connection string');
  1034. default:
  1035. return dt('Unable to parse DB connection array');
  1036. }
  1037. }
  1038. /**
  1039. * Call from a pre-sql-sync hook to register an sql
  1040. * query to be executed in the post-sql-sync hook.
  1041. * @see drush_sql_pre_sql_sync() and @see drush_sql_post_sql_sync().
  1042. *
  1043. * @param $id
  1044. * String containing an identifier representing this
  1045. * operation. This id is not actually used at the
  1046. * moment, it is just used to fufill the contract
  1047. * of drush contexts.
  1048. * @param $message
  1049. * String with the confirmation message that describes
  1050. * to the user what the post-sync operation is going
  1051. * to do. This confirmation message is printed out
  1052. * just before the user is asked whether or not the
  1053. * sql-sync operation should be continued.
  1054. * @param $query
  1055. * String containing the sql query to execute. If no
  1056. * query is provided, then the confirmation message will
  1057. * be displayed to the user, but no action will be taken
  1058. * in the post-sync hook. This is useful for drush modules
  1059. * that wish to provide their own post-sync hooks to fix
  1060. * up the target database in other ways (e.g. through
  1061. * Drupal APIs).
  1062. */
  1063. function drush_sql_register_post_sync_op($id, $message, $query = NULL) {
  1064. $options = drush_get_context('post-sync-ops');
  1065. $options[$id] = array('message' => $message, 'query' => $query);
  1066. drush_set_context('post-sync-ops', $options);
  1067. }
  1068. /**
  1069. * Builds a confirmation message for all post-sync operations.
  1070. *
  1071. * @return string
  1072. * All post-sync operation messages concatenated together.
  1073. */
  1074. function _drush_sql_get_post_sync_messages() {
  1075. $messages = FALSE;
  1076. $options = drush_get_context('post-sync-ops');
  1077. if (!empty($options)) {
  1078. $messages = dt('The following post-sync operations will be done on the destination:') . "\n";
  1079. foreach($options as $id => $data) {
  1080. $messages .= " * " . $data['message'] . "\n";
  1081. }
  1082. }
  1083. return $messages;
  1084. }
  1085. // Convert mysql 'show tables;' query into something pgsql understands.
  1086. function drush_sql_show_tables_pgsql() {
  1087. return "select tablename from pg_tables where schemaname='public';";
  1088. }
  1089. // Format queries to work with Oracle and SqlPlus
  1090. function drush_sql_format_oracle($query) {
  1091. // remove trailing semicolon from query if we have it
  1092. $query = preg_replace('/\;$/', '', $query);
  1093. // some sqlplus settings
  1094. $settings[] = "set TRIM ON";
  1095. $settings[] = "set FEEDBACK OFF";
  1096. $settings[] = "set UNDERLINE OFF";
  1097. $settings[] = "set PAGES 0";
  1098. $settings[] = "set PAGESIZE 50000";
  1099. // are we doing a describe ?
  1100. if (!preg_match('/^ *desc/i', $query)) {
  1101. $settings[] = "set LINESIZE 32767";
  1102. }
  1103. // are we doing a show tables ?
  1104. if (preg_match('/^ *show tables/i', $query)) {
  1105. $settings[] = "set HEADING OFF";
  1106. $query = "select object_name from user_objects where object_type='TABLE' order by object_name asc";
  1107. }
  1108. // create settings string
  1109. $sqlp_settings = implode("\n", $settings)."\n";
  1110. // important for sqlplus to exit correctly
  1111. return "${sqlp_settings}${query};\nexit;\n";
  1112. }
  1113. /*
  1114. * Drop all tables (if DB exists) or CREATE target database.
  1115. *
  1116. * return boolean
  1117. * TRUE or FALSE depending on success.
  1118. */
  1119. function drush_sql_empty_db($db_spec = NULL) {
  1120. if (is_null($db_spec)) {
  1121. $db_spec = _drush_sql_get_db_spec();
  1122. }
  1123. if (drush_sql_db_exists($db_spec)) {
  1124. _drush_sql_drop($db_spec);
  1125. }
  1126. else {
  1127. _drush_sql_create($db_spec);
  1128. }
  1129. }
  1130. /*
  1131. * Build DB connection array with superuser credentials if provided.
  1132. *
  1133. * The options 'db-su' and 'db-su-pw' will be retreived from the
  1134. * specified site alias record, if it exists and contains those items.
  1135. * If it does not, they will be fetched via drush_get_option.
  1136. *
  1137. * Note that in the context of sql-sync, the site alias record will
  1138. * be taken from the target alias (e.g. `drush sql-sync @source @target`),
  1139. * which will be overlayed with any options that begin with 'target-';
  1140. * therefore, the commandline options 'target-db-su' and 'target-db-su-pw'
  1141. * may also affect the operation of this function.
  1142. */
  1143. function drush_sql_su($db_spec, $site_alias_record = NULL) {
  1144. $create_db_target = $db_spec;
  1145. $create_db_target['database'] = '';
  1146. $db_superuser = drush_sitealias_get_option($site_alias_record, 'db-su');
  1147. if (isset($db_superuser)) {
  1148. $create_db_target['username'] = $db_superuser;
  1149. }
  1150. $db_su_pw = drush_sitealias_get_option($site_alias_record, 'db-su-pw');
  1151. // If --db-su-pw is not provided and --db-su is, default to empty password.
  1152. // This way db cli command will take password from .my.cnf or .pgpass.
  1153. if (!empty($db_su_pw)) {
  1154. $create_db_target['password'] = $db_su_pw;
  1155. }
  1156. elseif (isset($db_superuser)) {
  1157. unset($create_db_target['password']);
  1158. }
  1159. return $create_db_target;
  1160. }
  1161. /*
  1162. * Build a SQL string for dropping and creating a database.
  1163. *
  1164. * @param array $db_spec
  1165. * A database specification array.
  1166. *
  1167. * @param boolean $quoted
  1168. * Quote the database name. Mysql uses backticks to quote which can cause problems
  1169. * in a Windows shell. Set TRUE if the CREATE is not running on the bash command line.
  1170. */
  1171. function drush_sql_build_createdb_sql($db_spec, $quoted = FALSE) {
  1172. $sql = array();
  1173. switch (_drush_sql_get_scheme($db_spec)) {
  1174. case 'mysql':
  1175. $dbname = $quoted ? '`' . $db_spec['database'] . '`' : $db_spec['database'];
  1176. $sql[] = sprintf('DROP DATABASE IF EXISTS %s;', $dbname);
  1177. $sql[] = sprintf('CREATE DATABASE %s /*!40100 DEFAULT CHARACTER SET utf8 */;', $dbname);
  1178. $sql[] = sprintf('GRANT ALL PRIVILEGES ON %s.* TO \'%s\'@\'%s\'', $dbname, $db_spec['username'], $db_spec['host']);
  1179. $sql[] = sprintf("IDENTIFIED BY '%s';", $db_spec['password']);
  1180. $sql[] = 'FLUSH PRIVILEGES;';
  1181. break;
  1182. case 'pgsql':
  1183. $dbname = $quoted ? '"' . $db_spec['database'] . '"' : $db_spec['database'];
  1184. $sql[] = sprintf('drop database if exists %s;', $dbname);
  1185. $sql[] = sprintf("create database %s ENCODING 'UTF8';", $dbname);
  1186. break;
  1187. }
  1188. return implode(' ', $sql);
  1189. }
  1190. /*
  1191. * Does specified database exist on target server
  1192. *
  1193. * @return boolean
  1194. */
  1195. function drush_sql_db_exists($db_spec) {
  1196. if ($db_spec['driver'] == 'sqlite') {
  1197. return file_exists($db_spec['database']);
  1198. }
  1199. $connect_yes_db = _drush_sql_connect($db_spec);
  1200. $database = $db_spec['database'];
  1201. unset($db_spec['database']);
  1202. $connect_no_db = _drush_sql_connect($db_spec);
  1203. // We need the output back so we can't use drush_sql_query().
  1204. switch ($db_spec['driver']) {
  1205. case 'mysql':
  1206. $sql = "SELECT 1;";
  1207. // Suppress ugly output. Redirect STDERR and STDOUT. We just need exit code.
  1208. $bit_bucket = drush_bit_bucket();
  1209. return drush_shell_exec("$connect_yes_db -e \"$sql\" 2> $bit_bucket > $bit_bucket");
  1210. case 'pgsql':
  1211. $sql = "SELECT 1 AS result FROM pg_database WHERE datname='$database'";
  1212. drush_shell_exec("$connect_no_db -t -c \"$sql\"");
  1213. $output = drush_shell_exec_output();
  1214. return (bool)$output[0];
  1215. case 'sqlsrv':
  1216. // TODO: untested, but the gist is here.
  1217. $sql = "if db_id('$database') IS NOT NULL print 1";
  1218. drush_shell_exec("$connect_no_db -Q \"$sql\"");
  1219. $output = drush_shell_exec_output();
  1220. return $output[0] == 1;
  1221. }
  1222. }
  1223. function drush_sql_build_exec($db_spec, $filepath) {
  1224. $scheme = _drush_sql_get_scheme($db_spec);
  1225. $exec = '';
  1226. switch ($scheme) {
  1227. case 'mysql':
  1228. $exec = 'mysql';
  1229. $exec .= _drush_sql_get_credentials($db_spec);
  1230. $exec .= ' ' . drush_get_option('extra');
  1231. $exec .= " < " . drush_escapeshellarg($filepath);
  1232. break;
  1233. case 'pgsql':
  1234. $exec = 'psql -q ';
  1235. $exec .= _drush_sql_get_credentials($db_spec);
  1236. $exec .= ' ' . (drush_get_option('extra') ? drush_get_option('extra') : "--no-align --field-separator='\t' --pset footer=off");
  1237. $exec .= " --file " . drush_escapeshellarg($filepath);
  1238. break;
  1239. case 'sqlite':
  1240. $exec = 'sqlite3';
  1241. $exec .= ' ' . drush_get_option('extra');
  1242. $exec .= _drush_sql_get_credentials($db_spec);
  1243. $exec .= " < " . drush_escapeshellarg($filepath);
  1244. break;
  1245. case 'sqlsrv':
  1246. $exec = 'sqlcmd';
  1247. $exec .= ' ' . drush_get_option('extra');
  1248. $exec .= _drush_sql_get_credentials($db_spec);
  1249. $exec .= ' -h -1 -i "' . $filepath . '"';
  1250. break;
  1251. case 'oracle':
  1252. $exec = 'sqlplus';
  1253. $exec .= ' ' . drush_get_option('extra');
  1254. $exec .= _drush_sql_get_credentials($db_spec);
  1255. $exec .= " @" . drush_escapeshellarg($filepath);
  1256. break;
  1257. }
  1258. return $exec;
  1259. }