5 define('PSQL_SHOW_TABLES', "SELECT tablename FROM pg_tables WHERE schemaname='public';");
7 class SqlPgsql extends SqlBase
10 public $queryExtra = "--no-align --field-separator=\"\t\" --pset tuples_only=on";
12 public $queryFile = "--file";
14 private $password_file = null;
16 private function createPasswordFile()
18 $dbSpec = $this->getDbSpec();
19 if (null == ($this->getPasswordFile()) && isset($dbSpec['password'])) {
21 empty($dbSpec['host']) ? 'localhost' : $dbSpec['host'],
22 empty($dbSpec['port']) ? '5432' : $dbSpec['port'],
28 // Escape colon and backslash characters in entries.
29 // @see http://www.postgresql.org/docs/9.1/static/libpq-pgpass.html
30 array_walk($pgpass_parts, function (&$part) {
31 // The order of the replacements is important so that backslashes are
32 // not replaced twice.
33 $part = str_replace(['\\', ':'], ['\\\\', '\:'], $part);
35 $pgpass_contents = implode(':', $pgpass_parts);
36 $this->password_file = drush_save_data_to_temp_file($pgpass_contents);
37 chmod($this->password_file, 0600);
39 return $this->password_file;
42 public function command()
44 $environment = drush_is_windows() ? "SET " : "";
45 $pw_file = $this->createPasswordFile();
46 if (isset($pw_file)) {
47 $environment .= "PGPASSFILE={$pw_file} ";
49 return "{$environment}psql -q";
53 * @param $hide_password
54 * Not used in postgres. Use .pgpass file instead. See http://drupal.org/node/438828.
56 public function creds($hide_password = true)
58 $dbSpec = $this->getDbSpec();
59 // Some drush commands (e.g. site-install) want to connect to the
60 // server, but not the database. Connect to the built-in database.
61 $parameters['dbname'] = empty($dbSpec['database']) ? 'template1' : $dbSpec['database'];
63 // Host and port are optional but have defaults.
64 $parameters['host'] = empty($dbSpec['host']) ? 'localhost' : $dbSpec['host'];
65 $parameters['port'] = empty($dbSpec['port']) ? '5432' : $dbSpec['port'];
67 // Username is required.
68 $parameters['username'] = $dbSpec['username'];
70 // Don't set the password.
71 // @see http://drupal.org/node/438828
73 return $this->paramsToOptions($parameters);
76 public function createdbSql($dbname, $quoted = false)
79 $dbname = '"' . $dbname . '"';
81 $sql[] = sprintf('drop database if exists %s;', $dbname);
82 $sql[] = sprintf("create database %s ENCODING 'UTF8';", $dbname);
83 return implode(' ', $sql);
86 public function dbExists()
88 $dbSpec = $this->getDbSpec();
89 $database = $dbSpec['database'];
90 // Get a new class instance that has no 'database'.
91 $db_spec_no_db = $dbSpec;
92 unset($db_spec_no_db['database']);
93 $sql_no_db = new SqlPgsql($db_spec_no_db, $this->getOptions());
94 $query = "SELECT 1 AS result FROM pg_database WHERE datname='$database'";
95 drush_always_exec($sql_no_db->connect() . ' -t -c %s', $query);
96 $output = drush_shell_exec_output();
97 return (bool)$output[0];
100 public function queryFormat($query)
102 if (strtolower($query) == 'show tables;') {
103 return PSQL_SHOW_TABLES;
108 public function listTables()
110 $return = $this->alwaysQuery(PSQL_SHOW_TABLES);
111 $tables = drush_shell_exec_output();
112 if (!empty($tables)) {
118 public function dumpCmd($table_selection)
121 $skip_tables = $table_selection['skip'];
122 $structure_tables = $table_selection['structure'];
123 $tables = $table_selection['tables'];
126 $skip_tables = array_merge($structure_tables, $skip_tables);
127 $data_only = $this->getOption('data-only');
129 $create_db = $this->getOption('create-db');
132 $pw_file = $this->createPasswordFile();
133 if (isset($pw_file)) {
134 $environment = "PGPASSFILE={$pw_file} ";
136 $exec = "{$environment}pg_dump ";
137 // Unlike psql, pg_dump does not take a '--dbname=' before the database name.
138 $extra = str_replace('--dbname=', ' ', $this->creds());
140 $extra .= ' --data-only';
142 if ($option = $this->getOption('extra-dump')) {
143 $extra .= " $option";
146 $exec .= (!$create_db && !$data_only ? ' --clean' : '');
148 if (!empty($tables)) {
149 foreach ($tables as $table) {
150 $exec .= " --table=$table";
153 foreach ($skip_tables as $table) {
154 $ignores[] = "--exclude-table=$table";
156 $exec .= ' '. implode(' ', $ignores);
157 // Run pg_dump again and append output if we need some structure only tables.
158 if (!empty($structure_tables)) {
161 foreach ($structure_tables as $table) {
162 $schemaonlies[] = "--table=$table";
164 $exec .= " && pg_dump --schema-only " . implode(' ', $schemaonlies) . $extra;
165 $exec .= (!$create_db && !$data_only ? ' --clean' : '');
168 return $parens ? "($exec)" : $exec;
172 * @return string|null
174 public function getPasswordFile()
176 return $this->password_file;