provision/db_server/provision_mysql.drush.inc

<?php
// $Id: provision_mysql.drush.inc,v 1.22 2009/08/26 01:13:44 anarcat Exp $
/**
 * @file
 *    Mysql provisioning module.
 *
 * The goal of this module is to create mysql databases and user accounts, for sites that are about to be created.
 * It uses the provision API to tie into the right places in the site creation work flow.
 */
include_once('provision.mysql.inc');

function provision_mysql_drush_init() {
  $command = drush_get_command();
  $command = explode(" ", $command['command']);
  if ($command[0] == 'provision') {
    drush_set_default('master_db', $GLOBALS['db_url']);
    $master_db = drush_get_option('master_db');
    $db = parse_url($master_db);
    drush_set_default('master_db_user', $db['user']);
    drush_set_default('master_db_passwd', $db['pass']);

    drush_set_default('master_db_host', $db['host']);
    drush_set_default('db_host', $db['host']);

    drush_set_default('master_db_type', $db['scheme']);
    drush_set_default('db_type', $db['scheme']);
  }
}

function provision_mysql_drush_exit() {
  provision_db_close();
}

function provision_mysql_drush_help($section) {
  switch ($section) {
    case 'error:PROVISION_CREATE_DB_FAILED' :
      return dt('Unable to create new databases.');
    case 'error:PROVISION_DROP_DB_FAILED' :
      return dt('Unable to drop database.');
  }

}
/**
 * Generate a new mysql database and user account for the specified credentials
 */
function _provision_mysql_new_site_db($db_name, $db_user, $db_passwd, $db_grant_host = NULL) {
  if (is_null($db_grant_host)) {
    $db_grant_host = _provision_mysql_grant_host(
      drush_get_option('db_host', ''), 
      drush_get_option('web_ip', null), 
      drush_get_option('web_host', 'localhost'));
  }
  if (!_provision_mysql_create_database($db_name) ||
      !_provision_mysql_database_exists($db_name) ) {
   drush_set_error('PROVISION_CREATE_DB_FAILED');
   drush_log("Database could not be created.", 'error');
   return FALSE;
  }
  
  drush_log(dt("Granting privileges to %user@%client on %database", array('%user' => $db_user, '%client' => $db_grant_host, '%database' => $db_name)));
  if (!_provision_mysql_grant($db_name, $db_user, $db_passwd, $db_grant_host)) {
    drush_log("Could not GRANT user access.", 'warning');
  }
  _provision_mysql_flush_privileges();

  $status = _provision_mysql_database_exists($db_name);

  if ($status) {
    drush_log(dt('Created @name database', array("@name" => $db_name)), 'success');
  }
  else {
    drush_set_error('PROVISION_CREATE_DB_FAILED', dt("Could not create @name database", array("@name" => $db_name)));
  }
  return $status; 
   //TODO : Test to confirm that the database is actually writeable. Taking this on faith for now.
}

/**
 * Remove the database and user account for the supplied credentials
 */
function _provision_mysql_destroy_site_db($db_name, $db_user, $db_passwd, $db_grant_host = NULL) {
  if (is_null($db_grant_host)) {
    $db_grant_host = _provision_mysql_grant_host(drush_get_option('db_host', ''), drush_get_option('web_ip'), drush_get_option('web_host'));
  }
  if ( _provision_mysql_database_exists($db_name) ) {
    drush_log(dt("Dropping database @dbname", array('@dbname' => $db_name)));
    if (!_provision_mysql_drop_database($db_name)) {
      drush_log(dt("Failed to drop database @dbname", array('@dbname' => $db_name)), 'warning');
    }
  }
  
  if ( _provision_mysql_database_exists($db_name) ) {
   drush_set_error('PROVISION_DROP_DB_FAILED');
   return FALSE;
  }
  
  drush_log(dt("Revoking privileges of %user@%client from %database", array('%user' => $db_user, '%client' => $db_grant_host, '%database' => $db_name)));
  _provision_mysql_flush_privileges();
  if (!_provision_mysql_revoke($db_name, $db_user, $db_grant_host)) {
      drush_log(dt("Failed to revoke user privileges"), 'warning');
  }
}


function _provision_mysql_database_exists($name) {
  return provision_db_result(provision_db_query("SHOW DATABASES LIKE '%s'", $name));
}

function _provision_mysql_drop_database($name) {
  return provision_db_query("DROP DATABASE `%s`", $name);
}

function _provision_mysql_create_database($name) {
  return provision_db_query("CREATE DATABASE %s", $name);  
}

function _provision_mysql_flush_privileges() {
  return provision_db_query("FLUSH PRIVILEGES");  
}

function _provision_mysql_can_create_database() {
  $test = 'provision_test';
  _provision_mysql_create_database($test);
  if (_provision_mysql_database_exists($test)) {
    if (!_provision_mysql_drop_database($test)) {
      drush_log(dt("Failed to drop database @dbname", array('@dbname' => $test)), 'warning');
    }
    return TRUE;
  }
  return FALSE;
}

function _provision_mysql_grant($name, $username, $password, $host = '') {
  $host = ($host) ? $host : '%';
  return provision_db_query("GRANT ALL PRIVILEGES ON `%s`.* TO `%s`@`%s` IDENTIFIED BY '%s'", $name, $username, $host, $password);
}

function _provision_mysql_revoke($name, $username, $host = '') {
  $host = ($host) ? $host : '%';
  $success = provision_db_query("REVOKE ALL PRIVILEGES ON `%s`.* FROM `%s`@`%s`", $name, $username, $host);
  
  // check if there are any privileges left for the user
  $grants = provision_db_query("SHOW GRANTS FOR `%s`@`%s`", $username, $host);
  $grant_found = FALSE;
  while ($grant = provision_db_fetch_array($grants)) {
    // those are empty grants: just the user line
    if (!preg_match("/^GRANT USAGE ON /", array_pop($grant))) {
      // real grant, we shouldn't remove the user
      $grant_found = TRUE;
      break;
    }
  }
  if (!$grant_found) {
    $success = provision_db_query("DROP USER `%s`@`%s`", $username, $host) && $success;
  }
  return $success;
}

function _provision_mysql_import_dump($dump_file, $db_name, $db_user, $db_passwd, $db_host) {
  $exists = provision_path("exists", $dump_file, TRUE,
    dt('Found database dump at @path.'),
    dt('No database dump was found at @path.'),
    'PROVISION_DB_DUMP_NOT_FOUND');
  if ($exists) {
    $readable = provision_path("readable", $dump_file, TRUE, dt('Database dump at @path is readable'), 
      dt('The database dump at @path could not be read.'), 
      'PROVISION_DB_DUMP_NOT_READABLE');
    if ($readable) {
      $cmd = sprintf("mysql --defaults-file=/dev/fd/3 %s", escapeshellcmd($db_name));
      drush_log(sprintf("Importing database using command: %s", $cmd));
      # pipe handling code, this is inspired by drush_provision_mysql_pre_provision_backup()
      # we go through all this trouble to hide the password from the commandline, it's the most secure way (apart from writing a temporary file, which would create conflicts in parallel runs)
     $mycnf = sprintf('[client]
host=%s
user=%s
password=%s
', $db_host, $db_user, $db_passwd);

      $descriptorspec = array(
        0 => array("file", $dump_file, "r"),
        1 => array("pipe", "w"),  // stdout is a pipe that the child will write to
        2 => array("pipe", "w"),  // stderr is a file to write to
        3 => array("pipe", "r"),  // fd3 is our special file descriptor where we pass credentials
      );
      $process = proc_open($cmd, $descriptorspec, $pipes);
      $output = "";
      if (is_resource($process)) {
        fwrite($pipes[3], $mycnf);
        fclose($pipes[3]);
    
        $output = stream_get_contents($pipes[1]) . stream_get_contents($pipes[2]);
        // "It is important that you close any pipes before calling
        // proc_close in order to avoid a deadlock"
        fclose($pipes[1]);
        fclose($pipes[2]);
        $return_value = proc_close($process);
      } else {
        // XXX: failed to execute? unsure when this happens
        $return_value = -1;
      }

      if ($return_value != 0) {
        drush_set_error('PROVISION_DB_IMPORT_FAILED', dt("Database import failed: %output", array('%output' => $output)));
      }
     }
   }
}

/**
 * Find a viable database name, based on available information.
 *
 * This function exists solely to work past mysql's database name restrictions.
 * As mysql also does not have the ability to rename databases, it is completely
 * possible that sites will be running with derivative names on the same server,
 * until the upgrade / restore process is completed.
 */ 
function _provision_mysql_suggest_db_name($url) {
  if ($sid = drush_get_option('site_id')) {
    $suggest_base = 'site_'. $sid;
  }
  elseif ($name = drush_get_option('db_name')) {
    // consider the verified database name if no site id was provided
    //
    // we strip out eventual _N suffixes before finding a new db name
    // this is necessary because we may already have gone through this
    // process (in a migration) and had a _N suffix added
    $suggest_base = preg_replace('/_\d+$/', '', $name);
  }
  else {
    // This is a last option, and not ideal: base the db name on the
    // site name
    //
    // Provision only users will trigger this mostly.
    $suggest_base = substr(str_replace(array(".", "-"), '' , ereg_replace("^www\.", "", $url)), 0, 14);
  }
  $suggest[] = $suggest_base;
  for ($i = 0; $i < 100; $i++) {
    $suggest[] = $suggest_base .'_'. $i;
  }

  foreach ($suggest as $option) {
    if (!_provision_mysql_database_exists($option)) {
      return $option;
    }
  }

  drush_set_error('PROVISION_CREATE_DB_FAILED', dt("Could not find a free database names after 100 attempts"));
  return false;

}

/**
 * Properly guess the host part of the MySQL username based on a given
 * database server host , web server IP and web server host.
 *
 * If the database and web server are the same machine, return a localhost
 * grant, which is a special and very common case which does not involve any
 * dns lookups and is thus faster and more secure.
 *
 * If the web server and database server are different machines, we prefer to
 * use the IP address, which is faster and more secure because fewer lookups
 * are done during connections.
 *
 */
function _provision_mysql_grant_host($db_host, $web_ip, $web_host) {
  // The database hostname is localhost, not defined or on the same ip/host as the webserver.
  if (in_array($db_host, array('127.0.0.1', 'localhost', '', $web_ip, $web_host))) {
    $grant = 'localhost';
  }
  // if we have the web ip, use that first.
  elseif ($web_ip) {
    $grant = $web_ip;
  } else {
    $grant = $web_host;
  }
  return $grant;
}
1	<?php
2	// $Id: provision_mysql.drush.inc,v 1.22 2009/08/26 01:13:44 anarcat Exp $
3	/**
4	* @file
5	* Mysql provisioning module.
6	*
7	* The goal of this module is to create mysql databases and user accounts, for sites that are about to be created.
8	* It uses the provision API to tie into the right places in the site creation work flow.
9	*/
10	include_once('provision.mysql.inc');
11
12	function provision_mysql_drush_init() {
13	$command = drush_get_command();
14	$command = explode(" ", $command['command']);
15	if ($command[0] == 'provision') {
16	drush_set_default('master_db', $GLOBALS['db_url']);
17	$master_db = drush_get_option('master_db');
18	$db = parse_url($master_db);
19	drush_set_default('master_db_user', $db['user']);
20	drush_set_default('master_db_passwd', $db['pass']);
21
22	drush_set_default('master_db_host', $db['host']);
23	drush_set_default('db_host', $db['host']);
24
25	drush_set_default('master_db_type', $db['scheme']);
26	drush_set_default('db_type', $db['scheme']);
27	}
28	}
29
30	function provision_mysql_drush_exit() {
31	provision_db_close();
32	}
33
34	function provision_mysql_drush_help($section) {
35	switch ($section) {
36	case 'error:PROVISION_CREATE_DB_FAILED' :
37	return dt('Unable to create new databases.');
38	case 'error:PROVISION_DROP_DB_FAILED' :
39	return dt('Unable to drop database.');
40	}
41
42	}
43	/**
44	* Generate a new mysql database and user account for the specified credentials
45	*/
46	function _provision_mysql_new_site_db($db_name, $db_user, $db_passwd, $db_grant_host = NULL) {
47	if (is_null($db_grant_host)) {
48	$db_grant_host = _provision_mysql_grant_host(
49	drush_get_option('db_host', ''),
50	drush_get_option('web_ip', null),
51	drush_get_option('web_host', 'localhost'));
52	}
53	if (!_provision_mysql_create_database($db_name) \|\|
54	!_provision_mysql_database_exists($db_name) ) {
55	drush_set_error('PROVISION_CREATE_DB_FAILED');
56	drush_log("Database could not be created.", 'error');
57	return FALSE;
58	}
59
60	drush_log(dt("Granting privileges to %user@%client on %database", array('%user' => $db_user, '%client' => $db_grant_host, '%database' => $db_name)));
61	if (!_provision_mysql_grant($db_name, $db_user, $db_passwd, $db_grant_host)) {
62	drush_log("Could not GRANT user access.", 'warning');
63	}
64	_provision_mysql_flush_privileges();
65
66	$status = _provision_mysql_database_exists($db_name);
67
68	if ($status) {
69	drush_log(dt('Created @name database', array("@name" => $db_name)), 'success');
70	}
71	else {
72	drush_set_error('PROVISION_CREATE_DB_FAILED', dt("Could not create @name database", array("@name" => $db_name)));
73	}
74	return $status;
75	//TODO : Test to confirm that the database is actually writeable. Taking this on faith for now.
76	}
77
78	/**
79	* Remove the database and user account for the supplied credentials
80	*/
81	function _provision_mysql_destroy_site_db($db_name, $db_user, $db_passwd, $db_grant_host = NULL) {
82	if (is_null($db_grant_host)) {
83	$db_grant_host = _provision_mysql_grant_host(drush_get_option('db_host', ''), drush_get_option('web_ip'), drush_get_option('web_host'));
84	}
85	if ( _provision_mysql_database_exists($db_name) ) {
86	drush_log(dt("Dropping database @dbname", array('@dbname' => $db_name)));
87	if (!_provision_mysql_drop_database($db_name)) {
88	drush_log(dt("Failed to drop database @dbname", array('@dbname' => $db_name)), 'warning');
89	}
90	}
91
92	if ( _provision_mysql_database_exists($db_name) ) {
93	drush_set_error('PROVISION_DROP_DB_FAILED');
94	return FALSE;
95	}
96
97	drush_log(dt("Revoking privileges of %user@%client from %database", array('%user' => $db_user, '%client' => $db_grant_host, '%database' => $db_name)));
98	_provision_mysql_flush_privileges();
99	if (!_provision_mysql_revoke($db_name, $db_user, $db_grant_host)) {
100	drush_log(dt("Failed to revoke user privileges"), 'warning');
101	}
102	}
103
104
105	function _provision_mysql_database_exists($name) {
106	return provision_db_result(provision_db_query("SHOW DATABASES LIKE '%s'", $name));
107	}
108
109	function _provision_mysql_drop_database($name) {
110	return provision_db_query("DROP DATABASE `%s`", $name);
111	}
112
113	function _provision_mysql_create_database($name) {
114	return provision_db_query("CREATE DATABASE %s", $name);
115	}
116
117	function _provision_mysql_flush_privileges() {
118	return provision_db_query("FLUSH PRIVILEGES");
119	}
120
121	function _provision_mysql_can_create_database() {
122	$test = 'provision_test';
123	_provision_mysql_create_database($test);
124	if (_provision_mysql_database_exists($test)) {
125	if (!_provision_mysql_drop_database($test)) {
126	drush_log(dt("Failed to drop database @dbname", array('@dbname' => $test)), 'warning');
127	}
128	return TRUE;
129	}
130	return FALSE;
131	}
132
133	function _provision_mysql_grant($name, $username, $password, $host = '') {
134	$host = ($host) ? $host : '%';
135	return provision_db_query("GRANT ALL PRIVILEGES ON `%s`.* TO `%s`@`%s` IDENTIFIED BY '%s'", $name, $username, $host, $password);
136	}
137
138	function _provision_mysql_revoke($name, $username, $host = '') {
139	$host = ($host) ? $host : '%';
140	$success = provision_db_query("REVOKE ALL PRIVILEGES ON `%s`.* FROM `%s`@`%s`", $name, $username, $host);
141
142	// check if there are any privileges left for the user
143	$grants = provision_db_query("SHOW GRANTS FOR `%s`@`%s`", $username, $host);
144	$grant_found = FALSE;
145	while ($grant = provision_db_fetch_array($grants)) {
146	// those are empty grants: just the user line
147	if (!preg_match("/^GRANT USAGE ON /", array_pop($grant))) {
148	// real grant, we shouldn't remove the user
149	$grant_found = TRUE;
150	break;
151	}
152	}
153	if (!$grant_found) {
154	$success = provision_db_query("DROP USER `%s`@`%s`", $username, $host) && $success;
155	}
156	return $success;
157	}
158
159	function _provision_mysql_import_dump($dump_file, $db_name, $db_user, $db_passwd, $db_host) {
160	$exists = provision_path("exists", $dump_file, TRUE,
161	dt('Found database dump at @path.'),
162	dt('No database dump was found at @path.'),
163	'PROVISION_DB_DUMP_NOT_FOUND');
164	if ($exists) {
165	$readable = provision_path("readable", $dump_file, TRUE, dt('Database dump at @path is readable'),
166	dt('The database dump at @path could not be read.'),
167	'PROVISION_DB_DUMP_NOT_READABLE');
168	if ($readable) {
169	$cmd = sprintf("mysql --defaults-file=/dev/fd/3 %s", escapeshellcmd($db_name));
170	drush_log(sprintf("Importing database using command: %s", $cmd));
171	# pipe handling code, this is inspired by drush_provision_mysql_pre_provision_backup()
172	# we go through all this trouble to hide the password from the commandline, it's the most secure way (apart from writing a temporary file, which would create conflicts in parallel runs)
173	$mycnf = sprintf('[client]
174	host=%s
175	user=%s
176	password=%s
177	', $db_host, $db_user, $db_passwd);
178
179	$descriptorspec = array(
180	0 => array("file", $dump_file, "r"),
181	1 => array("pipe", "w"), // stdout is a pipe that the child will write to
182	2 => array("pipe", "w"), // stderr is a file to write to
183	3 => array("pipe", "r"), // fd3 is our special file descriptor where we pass credentials
184	);
185	$process = proc_open($cmd, $descriptorspec, $pipes);
186	$output = "";
187	if (is_resource($process)) {
188	fwrite($pipes[3], $mycnf);
189	fclose($pipes[3]);
190
191	$output = stream_get_contents($pipes[1]) . stream_get_contents($pipes[2]);
192	// "It is important that you close any pipes before calling
193	// proc_close in order to avoid a deadlock"
194	fclose($pipes[1]);
195	fclose($pipes[2]);
196	$return_value = proc_close($process);
197	} else {
198	// XXX: failed to execute? unsure when this happens
199	$return_value = -1;
200	}
201
202	if ($return_value != 0) {
203	drush_set_error('PROVISION_DB_IMPORT_FAILED', dt("Database import failed: %output", array('%output' => $output)));
204	}
205	}
206	}
207	}
208
209	/**
210	* Find a viable database name, based on available information.
211	*
212	* This function exists solely to work past mysql's database name restrictions.
213	* As mysql also does not have the ability to rename databases, it is completely
214	* possible that sites will be running with derivative names on the same server,
215	* until the upgrade / restore process is completed.
216	*/
217	function _provision_mysql_suggest_db_name($url) {
218	if ($sid = drush_get_option('site_id')) {
219	$suggest_base = 'site_'. $sid;
220	}
221	elseif ($name = drush_get_option('db_name')) {
222	// consider the verified database name if no site id was provided
223	//
224	// we strip out eventual _N suffixes before finding a new db name
225	// this is necessary because we may already have gone through this
226	// process (in a migration) and had a _N suffix added
227	$suggest_base = preg_replace('/_\d+$/', '', $name);
228	}
229	else {
230	// This is a last option, and not ideal: base the db name on the
231	// site name
232	//
233	// Provision only users will trigger this mostly.
234	$suggest_base = substr(str_replace(array(".", "-"), '' , ereg_replace("^www\.", "", $url)), 0, 14);
235	}
236	$suggest[] = $suggest_base;
237	for ($i = 0; $i < 100; $i++) {
238	$suggest[] = $suggest_base .'_'. $i;
239	}
240
241	foreach ($suggest as $option) {
242	if (!_provision_mysql_database_exists($option)) {
243	return $option;
244	}
245	}
246
247	drush_set_error('PROVISION_CREATE_DB_FAILED', dt("Could not find a free database names after 100 attempts"));
248	return false;
249
250	}
251
252	/**
253	* Properly guess the host part of the MySQL username based on a given
254	* database server host , web server IP and web server host.
255	*
256	* If the database and web server are the same machine, return a localhost
257	* grant, which is a special and very common case which does not involve any
258	* dns lookups and is thus faster and more secure.
259	*
260	* If the web server and database server are different machines, we prefer to
261	* use the IP address, which is faster and more secure because fewer lookups
262	* are done during connections.
263	*
264	*/
265	function _provision_mysql_grant_host($db_host, $web_ip, $web_host) {
266	// The database hostname is localhost, not defined or on the same ip/host as the webserver.
267	if (in_array($db_host, array('127.0.0.1', 'localhost', '', $web_ip, $web_host))) {
268	$grant = 'localhost';
269	}
270	// if we have the web ip, use that first.
271	elseif ($web_ip) {
272	$grant = $web_ip;
273	} else {
274	$grant = $web_host;
275	}
276	return $grant;
277	}