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 Build a SQL string for dropping and creating a database.
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 Does specified database exist on target server
drush_sql_drop Drops all tables in the database.
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 Drop all tables (if DB exists) or CREATE target database.
drush_sql_expand_wildcard_tables Expand wildcard tables.
drush_sql_filter_tables Filters tables.
drush_sql_format_oracle
drush_sql_get_expanded_table_selection Get a list of all table names and expand input that may contain wildcards (`*`) if necessary so that the array returned only contains valid table names i.e. actual tables that exist, without a wildcard.
drush_sql_get_table_selection Construct an array that places table names in appropriate buckets based on whether the table is to be skipped, included for structure only, or have structure and data dumped. The keys of the array are:
drush_sql_query Command callback. Executes the given SQL query on the Drupal database.
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 Build DB connection array with superuser credentials if provided.
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 Generate CREATE DATABASE sql plus additonal preparations.
_drush_sql_drop
_drush_sql_expand_and_filter_tables Given the table names in the input array that may contain wildcards (`*`), expand the table names so that the array returned only contains table names that exist in the database.
_drush_sql_get_all_db_specs
_drush_sql_get_credentials Build a fragment containing credentials and other 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_db_table_list Extract the name of all existing tables in the given database.
_drush_sql_get_invalid_url_msg
_drush_sql_get_post_sync_messages Builds a confirmation message for all post-sync operations.
_drush_sql_get_raw_table_list Consult the specified options and return the list of tables specified.
_drush_sql_get_scheme
_drush_sql_get_spec_from_options
_drush_sql_query Execute a SQL query.

File

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