5 define('PSQL_SHOW_TABLES', "SELECT tablename FROM pg_tables WHERE schemaname='public';");
7 class Sqlpgsql extends SqlBase {
9 public $query_extra = "--no-align --field-separator=\"\t\" --pset tuples_only=on";
11 public $query_file = "--file";
13 private $password_file = NULL;
15 private function password_file() {
16 if (!isset($password_file) && isset($this->db_spec['password'])) {
17 $pgpass_parts = array(
18 empty($this->db_spec['host']) ? 'localhost' : $this->db_spec['host'],
19 empty($this->db_spec['port']) ? '5432' : $this->db_spec['port'],
22 $this->db_spec['username'],
23 $this->db_spec['password']
25 // Escape colon and backslash characters in entries.
26 // @see http://www.postgresql.org/docs/9.1/static/libpq-pgpass.html
27 array_walk($pgpass_parts, function (&$part) {
28 // The order of the replacements is important so that backslashes are
29 // not replaced twice.
30 $part = str_replace(array('\\', ':'), array('\\\\', '\:'), $part);
32 $pgpass_contents = implode(':', $pgpass_parts);
33 $password_file = drush_save_data_to_temp_file($pgpass_contents);
34 chmod($password_file, 0600);
36 return $password_file;
39 public function command() {
41 $pw_file = $this->password_file();
42 if (isset($pw_file)) {
43 $environment = "PGPASSFILE={$pw_file} ";
45 return "{$environment}psql -q";
49 * @param $hide_password
50 * Not used in postgres. Use .pgpass file instead. See http://drupal.org/node/438828.
52 public function creds($hide_password = TRUE) {
53 // Some drush commands (e.g. site-install) want to connect to the
54 // server, but not the database. Connect to the built-in database.
55 $parameters['dbname'] = empty($this->db_spec['database']) ? 'template1' : $this->db_spec['database'];
57 // Host and port are optional but have defaults.
58 $parameters['host'] = empty($this->db_spec['host']) ? 'localhost' : $this->db_spec['host'];
59 $parameters['port'] = empty($this->db_spec['port']) ? '5432' : $this->db_spec['port'];
61 // Username is required.
62 $parameters['username'] = $this->db_spec['username'];
64 // Don't set the password.
65 // @see http://drupal.org/node/438828
67 return $this->params_to_options($parameters);
70 public function createdb_sql($dbname, $quoted = FALSE) {
72 $dbname = '`' . $dbname . '`';
74 $sql[] = sprintf('drop database if exists %s;', $dbname);
75 $sql[] = sprintf("create database %s ENCODING 'UTF8';", $dbname);
76 return implode(' ', $sql);
79 public function db_exists() {
80 $database = $this->db_spec['database'];
81 // Get a new class instance that has no 'database'.
82 $db_spec_no_db = $this->db_spec;
83 unset($db_spec_no_db['database']);
84 $sql_no_db = drush_sql_get_class($db_spec_no_db);
85 $query = "SELECT 1 AS result FROM pg_database WHERE datname='$database'";
86 drush_shell_exec($sql_no_db->connect() . ' -t -c %s', $query);
87 $output = drush_shell_exec_output();
88 return (bool)$output[0];
91 public function query_format($query) {
92 if (strtolower($query) == 'show tables;') {
93 return PSQL_SHOW_TABLES;
98 public function listTables() {
99 $return = $this->query(PSQL_SHOW_TABLES);
100 $tables = drush_shell_exec_output();
101 if (!empty($tables)) {
107 public function dumpCmd($table_selection) {
109 $skip_tables = $table_selection['skip'];
110 $structure_tables = $table_selection['structure'];
111 $tables = $table_selection['tables'];
114 $skip_tables = array_merge($structure_tables, $skip_tables);
115 $data_only = drush_get_option('data-only');
117 $create_db = drush_get_option('create-db');
119 // Unlike psql, pg_dump does not take a '--dbname=' before the database name.
120 $extra = str_replace('--dbname=', ' ', $this->creds());
121 if (isset($data_only)) {
122 $extra .= ' --data-only';
124 if ($option = drush_get_option('extra', $this->query_extra)) {
125 $extra .= " $option";
128 $exec .= (!isset($create_db) && !isset($data_only) ? ' --clean' : '');
130 if (!empty($tables)) {
131 foreach ($tables as $table) {
132 $exec .= " --table=$table";
136 foreach ($skip_tables as $table) {
137 $ignores[] = "--exclude-table=$table";
139 $exec .= ' '. implode(' ', $ignores);
140 // Run pg_dump again and append output if we need some structure only tables.
141 if (!empty($structure_tables)) {
143 $schemaonlies = array();
144 foreach ($structure_tables as $table) {
145 $schemaonlies[] = "--table=$table";
147 $exec .= " && pg_dump --schema-only " . implode(' ', $schemaonlies) . $extra;
148 $exec .= (!isset($create_db) && !isset($data_only) ? ' --clean' : '');
151 return $parens ? "($exec)" : $exec;