1: <?php
2: /**
3: * Copyright 2007 Maintainable Software, LLC
4: * Copyright 2008-2012 Horde LLC (http://www.horde.org/)
5: *
6: * @author Mike Naberezny <mike@maintainable.com>
7: * @author Derek DeVries <derek@maintainable.com>
8: * @author Chuck Hagenbuch <chuck@horde.org>
9: * @license http://www.horde.org/licenses/bsd
10: * @category Horde
11: * @package Db
12: * @subpackage Adapter
13: */
14:
15: /**
16: * PDO_PostgreSQL Horde_Db_Adapter
17: *
18: * @author Mike Naberezny <mike@maintainable.com>
19: * @author Derek DeVries <derek@maintainable.com>
20: * @author Chuck Hagenbuch <chuck@horde.org>
21: * @license http://www.horde.org/licenses/bsd
22: * @category Horde
23: * @package Db
24: * @subpackage Adapter
25: */
26: class Horde_Db_Adapter_Pdo_Pgsql extends Horde_Db_Adapter_Pdo_Base
27: {
28: /**
29: * @var string
30: */
31: protected $_schemaClass = 'Horde_Db_Adapter_Postgresql_Schema';
32:
33: /**
34: * @return string
35: */
36: public function adapterName()
37: {
38: return 'PDO_PostgreSQL';
39: }
40:
41: /**
42: * @return boolean
43: */
44: public function supportsMigrations()
45: {
46: return true;
47: }
48:
49: /**
50: * Does PostgreSQL support standard conforming strings?
51: * @return boolean
52: */
53: public function supportsStandardConformingStrings()
54: {
55: // Temporarily set the client message level above error to prevent unintentional
56: // error messages in the logs when working on a PostgreSQL database server that
57: // does not support standard conforming strings.
58: $clientMinMessagesOld = $this->getClientMinMessages();
59: $this->setClientMinMessages('panic');
60:
61: $hasSupport = $this->selectValue('SHOW standard_conforming_strings');
62:
63: $this->setClientMinMessages($clientMinMessagesOld);
64: return $hasSupport;
65: }
66:
67: public function supportsInsertWithReturning()
68: {
69: return $this->postgresqlVersion() >= 80200;
70: }
71:
72:
73: /*##########################################################################
74: # Connection Management
75: ##########################################################################*/
76:
77: /**
78: * Connect to the db.
79: *
80: * @throws Horde_Db_Exception
81: */
82: public function connect()
83: {
84: if ($this->_active) {
85: return;
86: }
87:
88: parent::connect();
89:
90: $this->_lastQuery = $sql = "SET datestyle TO 'iso'";
91: $retval = $this->_connection->exec($sql);
92: if ($retval === false) {
93: $error = $this->_connection->errorInfo();
94: throw new Horde_Db_Exception($error[2]);
95: }
96:
97: // Money type has a fixed precision of 10 in PostgreSQL 8.2 and below, and as of
98: // PostgreSQL 8.3 it has a fixed precision of 19. PostgreSQLColumn.extract_precision
99: // should know about this but can't detect it there, so deal with it here.
100: Horde_Db_Adapter_Postgresql_Column::$moneyPrecision = ($this->postgresqlVersion() >= 80300) ? 19 : 10;
101:
102: $this->_configureConnection();
103: }
104:
105:
106: /*##########################################################################
107: # Database Statements
108: ##########################################################################*/
109:
110: /**
111: * Inserts a row into a table.
112: *
113: * @param string $sql SQL statement.
114: * @param array|string $arg1 Either an array of bound parameters or a
115: * query name.
116: * @param string $arg2 If $arg1 contains bound parameters, the
117: * query name.
118: * @param string $pk The primary key column.
119: * @param integer $idValue The primary key value. This parameter is
120: * required if the primary key is inserted
121: * manually.
122: * @param string $sequenceName The sequence name.
123: *
124: * @return integer Last inserted ID.
125: * @throws Horde_Db_Exception
126: */
127: public function insert($sql, $arg1 = null, $arg2 = null, $pk = null,
128: $idValue = null, $sequenceName = null)
129: {
130: // Extract the table from the insert sql. Yuck.
131: $temp = explode(' ', $sql, 4);
132: $table = str_replace('"', '', $temp[2]);
133:
134: // Try an insert with 'returning id' if available (PG >= 8.2)
135: if ($this->supportsInsertWithReturning()) {
136: if (!$pk) {
137: list($pk, $sequenceName) = $this->pkAndSequenceFor($table);
138: }
139: if ($pk) {
140: $id = $this->selectValue($sql . ' RETURNING ' . $this->quoteColumnName($pk), $arg1, $arg2);
141: $this->resetPkSequence($table, $pk, $sequenceName);
142: return $id;
143: }
144: }
145:
146: // If neither pk nor sequence name is given, look them up.
147: if (!($pk || $sequenceName)) {
148: list($pk, $sequenceName) = $this->pkAndSequenceFor($table);
149: }
150:
151: // Otherwise, insert then grab last_insert_id.
152: if ($insertId = parent::insert($sql, $arg1, $arg2, $pk, $idValue, $sequenceName)) {
153: $this->resetPkSequence($table, $pk, $sequenceName);
154: return $insertId;
155: }
156:
157: // If a pk is given, fallback to default sequence name.
158: // Don't fetch last insert id for a table without a pk.
159: if ($pk &&
160: ($sequenceName ||
161: $sequenceName = $this->defaultSequenceName($table, $pk))) {
162: $this->resetPkSequence($table, $pk, $sequenceName);
163: return $this->_lastInsertId($table, $sequenceName);
164: }
165: }
166:
167: /**
168: * Appends LIMIT and OFFSET options to a SQL statement.
169: *
170: * @param string $sql SQL statement.
171: * @param array $options Hash with 'limit' and (optional) 'offset' values.
172: *
173: * @return string
174: */
175: public function addLimitOffset($sql, $options)
176: {
177: if (isset($options['limit']) && $limit = $options['limit']) {
178: $sql .= " LIMIT $limit";
179: }
180: if (isset($options['offset']) && $offset = $options['offset']) {
181: $sql .= " OFFSET $offset";
182: }
183: return $sql;
184: }
185:
186:
187: /*##########################################################################
188: # Protected
189: ##########################################################################*/
190:
191: /**
192: * Parse configuration array into options for PDO constructor.
193: *
194: * @throws Horde_Db_Exception
195: * @return array [dsn, username, password]
196: */
197: protected function _parseConfig()
198: {
199: $this->_config['adapter'] = 'pgsql';
200:
201: // PDO for PostgreSQL does not accept a socket argument
202: // in the connection string; the location can be set via the
203: // "host" argument instead.
204: if (!empty($this->_config['socket'])) {
205: $this->_config['host'] = $this->_config['socket'];
206: unset($this->_config['socket']);
207: }
208:
209: return parent::_parseConfig();
210: }
211:
212: /**
213: * Configures the encoding, verbosity, and schema search path of the connection.
214: * This is called by connect() and should not be called manually.
215: */
216: protected function _configureConnection()
217: {
218: if (!empty($this->_config['charset'])) {
219: $this->_lastQuery = $sql = 'SET client_encoding TO '.$this->quoteString($this->_config['charset']);
220: $this->execute($sql);
221: }
222:
223: if (!empty($this->_config['client_min_messages'])) $this->setClientMinMessages($this->_config['client_min_messages']);
224: $this->setSchemaSearchPath(!empty($this->_config['schema_search_path']) || !empty($this->_config['schema_order']));
225: }
226:
227: /**
228: * @TODO
229: */
230: protected function _selectRaw($sql, $arg1=null, $arg2=null)
231: {
232: $result = $this->execute($sql, $arg1, $arg2);
233: if (!$result) return array();
234:
235: $moneyFields = array();
236: for ($i = 0, $i_max = $result->columnCount(); $i < $i_max; $i++) {
237: $f = $result->getColumnMeta($i);
238: if (!empty($f['pgsql:oid']) && $f['pgsql:oid'] == Horde_Db_Adapter_Postgresql_Column::MONEY_COLUMN_TYPE_OID) {
239: $moneyFields[] = $i;
240: $moneyFields[] = $f['name'];
241: }
242: }
243:
244: foreach ($result as $row) {
245: // If this is a money type column and there are any currency
246: // symbols, then strip them off. Indeed it would be prettier to do
247: // this in Horde_Db_Adapter_Postgres_Column::stringToDecimal but
248: // would break form input fields that call valueBeforeTypeCast.
249: foreach ($moneyFields as $f) {
250: // Because money output is formatted according to the locale, there are two
251: // cases to consider (note the decimal separators):
252: // (1) $12,345,678.12
253: // (2) $12.345.678,12
254: if (preg_match('/^-?\D+[\d,]+\.\d{2}$/', $row[$f])) { // #1
255: $row[$f] = preg_replace('/[^-\d\.]/', '', $row[$f]) . "\n";
256: } elseif (preg_match('/^-?\D+[\d\.]+,\d{2}$/', $row[$f])) { // #2
257: $row[$f] = str_replace(',', '.', preg_replace('/[^-\d,]/', '', $row[$f])) . "\n";
258: }
259: }
260: $rows[] = $row;
261: }
262:
263: $result->closeCursor();
264: return $rows;
265: }
266:
267: /**
268: * Returns the current ID of a table's sequence.
269: */
270: protected function _lastInsertId($table, $sequenceName)
271: {
272: return (int)$this->selectValue('SELECT currval('.$this->quoteSequenceName($sequenceName).')');
273: }
274: }
275: