1: <?php
2: /**
3: * Zend Framework
4: *
5: * LICENSE
6: *
7: * This source file is subject to the new BSD license that is bundled
8: * with this package in the file LICENSE.txt.
9: * It is also available through the world-wide-web at this URL:
10: * http://framework.zend.com/license/new-bsd
11: * If you did not receive a copy of the license and are unable to
12: * obtain it through the world-wide-web, please send an email
13: * to license@zend.com so we can send you a copy immediately.
14: *
15: * @category Zend
16: * @package Zend_Db
17: * @subpackage Adapter
18: * @copyright Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
19: * @license http://framework.zend.com/license/new-bsd New BSD License
20: * @version $Id: Pgsql.php 22788 2010-08-03 18:29:55Z ramon $
21: */
22:
23:
24: /**
25: * @see Zend_Db_Adapter_Pdo_Abstract
26: */
27: require_once 'Zend/Db/Adapter/Pdo/Abstract.php';
28:
29:
30: /**
31: * Class for connecting to PostgreSQL databases and performing common operations.
32: *
33: * @category Zend
34: * @package Zend_Db
35: * @subpackage Adapter
36: * @copyright Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
37: * @license http://framework.zend.com/license/new-bsd New BSD License
38: */
39: class Zend_Db_Adapter_Pdo_Pgsql extends Zend_Db_Adapter_Pdo_Abstract
40: {
41:
42: /**
43: * PDO type.
44: *
45: * @var string
46: */
47: protected $_pdoType = 'pgsql';
48:
49: /**
50: * Keys are UPPERCASE SQL datatypes or the constants
51: * Zend_Db::INT_TYPE, Zend_Db::BIGINT_TYPE, or Zend_Db::FLOAT_TYPE.
52: *
53: * Values are:
54: * 0 = 32-bit integer
55: * 1 = 64-bit integer
56: * 2 = float or decimal
57: *
58: * @var array Associative array of datatypes to values 0, 1, or 2.
59: */
60: protected $_numericDataTypes = array(
61: Zend_Db::INT_TYPE => Zend_Db::INT_TYPE,
62: Zend_Db::BIGINT_TYPE => Zend_Db::BIGINT_TYPE,
63: Zend_Db::FLOAT_TYPE => Zend_Db::FLOAT_TYPE,
64: 'INTEGER' => Zend_Db::INT_TYPE,
65: 'SERIAL' => Zend_Db::INT_TYPE,
66: 'SMALLINT' => Zend_Db::INT_TYPE,
67: 'BIGINT' => Zend_Db::BIGINT_TYPE,
68: 'BIGSERIAL' => Zend_Db::BIGINT_TYPE,
69: 'DECIMAL' => Zend_Db::FLOAT_TYPE,
70: 'DOUBLE PRECISION' => Zend_Db::FLOAT_TYPE,
71: 'NUMERIC' => Zend_Db::FLOAT_TYPE,
72: 'REAL' => Zend_Db::FLOAT_TYPE
73: );
74:
75: /**
76: * Creates a PDO object and connects to the database.
77: *
78: * @return void
79: * @throws Zend_Db_Adapter_Exception
80: */
81: protected function _connect()
82: {
83: if ($this->_connection) {
84: return;
85: }
86:
87: parent::_connect();
88:
89: if (!empty($this->_config['charset'])) {
90: $sql = "SET NAMES '" . $this->_config['charset'] . "'";
91: $this->_connection->exec($sql);
92: }
93: }
94:
95: /**
96: * Returns a list of the tables in the database.
97: *
98: * @return array
99: */
100: public function listTables()
101: {
102: // @todo use a better query with joins instead of subqueries
103: $sql = "SELECT c.relname AS table_name "
104: . "FROM pg_class c, pg_user u "
105: . "WHERE c.relowner = u.usesysid AND c.relkind = 'r' "
106: . "AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname) "
107: . "AND c.relname !~ '^(pg_|sql_)' "
108: . "UNION "
109: . "SELECT c.relname AS table_name "
110: . "FROM pg_class c "
111: . "WHERE c.relkind = 'r' "
112: . "AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname) "
113: . "AND NOT EXISTS (SELECT 1 FROM pg_user WHERE usesysid = c.relowner) "
114: . "AND c.relname !~ '^pg_'";
115:
116: return $this->fetchCol($sql);
117: }
118:
119: /**
120: * Returns the column descriptions for a table.
121: *
122: * The return value is an associative array keyed by the column name,
123: * as returned by the RDBMS.
124: *
125: * The value of each array element is an associative array
126: * with the following keys:
127: *
128: * SCHEMA_NAME => string; name of database or schema
129: * TABLE_NAME => string;
130: * COLUMN_NAME => string; column name
131: * COLUMN_POSITION => number; ordinal position of column in table
132: * DATA_TYPE => string; SQL datatype name of column
133: * DEFAULT => string; default expression of column, null if none
134: * NULLABLE => boolean; true if column can have nulls
135: * LENGTH => number; length of CHAR/VARCHAR
136: * SCALE => number; scale of NUMERIC/DECIMAL
137: * PRECISION => number; precision of NUMERIC/DECIMAL
138: * UNSIGNED => boolean; unsigned property of an integer type
139: * PRIMARY => boolean; true if column is part of the primary key
140: * PRIMARY_POSITION => integer; position of column in primary key
141: * IDENTITY => integer; true if column is auto-generated with unique values
142: *
143: * @todo Discover integer unsigned property.
144: *
145: * @param string $tableName
146: * @param string $schemaName OPTIONAL
147: * @return array
148: */
149: public function describeTable($tableName, $schemaName = null)
150: {
151: $sql = "SELECT
152: a.attnum,
153: n.nspname,
154: c.relname,
155: a.attname AS colname,
156: t.typname AS type,
157: a.atttypmod,
158: FORMAT_TYPE(a.atttypid, a.atttypmod) AS complete_type,
159: d.adsrc AS default_value,
160: a.attnotnull AS notnull,
161: a.attlen AS length,
162: co.contype,
163: ARRAY_TO_STRING(co.conkey, ',') AS conkey
164: FROM pg_attribute AS a
165: JOIN pg_class AS c ON a.attrelid = c.oid
166: JOIN pg_namespace AS n ON c.relnamespace = n.oid
167: JOIN pg_type AS t ON a.atttypid = t.oid
168: LEFT OUTER JOIN pg_constraint AS co ON (co.conrelid = c.oid
169: AND a.attnum = ANY(co.conkey) AND co.contype = 'p')
170: LEFT OUTER JOIN pg_attrdef AS d ON d.adrelid = c.oid AND d.adnum = a.attnum
171: WHERE a.attnum > 0 AND c.relname = ".$this->quote($tableName);
172: if ($schemaName) {
173: $sql .= " AND n.nspname = ".$this->quote($schemaName);
174: }
175: $sql .= ' ORDER BY a.attnum';
176:
177: $stmt = $this->query($sql);
178:
179: // Use FETCH_NUM so we are not dependent on the CASE attribute of the PDO connection
180: $result = $stmt->fetchAll(Zend_Db::FETCH_NUM);
181:
182: $attnum = 0;
183: $nspname = 1;
184: $relname = 2;
185: $colname = 3;
186: $type = 4;
187: $atttypemod = 5;
188: $complete_type = 6;
189: $default_value = 7;
190: $notnull = 8;
191: $length = 9;
192: $contype = 10;
193: $conkey = 11;
194:
195: $desc = array();
196: foreach ($result as $key => $row) {
197: $defaultValue = $row[$default_value];
198: if ($row[$type] == 'varchar' || $row[$type] == 'bpchar' ) {
199: if (preg_match('/character(?: varying)?(?:\((\d+)\))?/', $row[$complete_type], $matches)) {
200: if (isset($matches[1])) {
201: $row[$length] = $matches[1];
202: } else {
203: $row[$length] = null; // unlimited
204: }
205: }
206: if (preg_match("/^'(.*?)'::(?:character varying|bpchar)$/", $defaultValue, $matches)) {
207: $defaultValue = $matches[1];
208: }
209: }
210: list($primary, $primaryPosition, $identity) = array(false, null, false);
211: if ($row[$contype] == 'p') {
212: $primary = true;
213: $primaryPosition = array_search($row[$attnum], explode(',', $row[$conkey])) + 1;
214: $identity = (bool) (preg_match('/^nextval/', $row[$default_value]));
215: }
216: $desc[$this->foldCase($row[$colname])] = array(
217: 'SCHEMA_NAME' => $this->foldCase($row[$nspname]),
218: 'TABLE_NAME' => $this->foldCase($row[$relname]),
219: 'COLUMN_NAME' => $this->foldCase($row[$colname]),
220: 'COLUMN_POSITION' => $row[$attnum],
221: 'DATA_TYPE' => $row[$type],
222: 'DEFAULT' => $defaultValue,
223: 'NULLABLE' => (bool) ($row[$notnull] != 't'),
224: 'LENGTH' => $row[$length],
225: 'SCALE' => null, // @todo
226: 'PRECISION' => null, // @todo
227: 'UNSIGNED' => null, // @todo
228: 'PRIMARY' => $primary,
229: 'PRIMARY_POSITION' => $primaryPosition,
230: 'IDENTITY' => $identity
231: );
232: }
233: return $desc;
234: }
235:
236:
237: /**
238: * Adds an adapter-specific LIMIT clause to the SELECT statement.
239: *
240: * @param string $sql
241: * @param integer $count
242: * @param integer $offset OPTIONAL
243: * @return string
244: */
245: public function limit($sql, $count, $offset = 0)
246: {
247: $count = intval($count);
248: if ($count <= 0) {
249: /**
250: * @see Zend_Db_Adapter_Exception
251: */
252: require_once 'Zend/Db/Adapter/Exception.php';
253: throw new Zend_Db_Adapter_Exception("LIMIT argument count=$count is not valid");
254: }
255:
256: $offset = intval($offset);
257: if ($offset < 0) {
258: /**
259: * @see Zend_Db_Adapter_Exception
260: */
261: require_once 'Zend/Db/Adapter/Exception.php';
262: throw new Zend_Db_Adapter_Exception("LIMIT argument offset=$offset is not valid");
263: }
264:
265: $sql .= " LIMIT $count";
266: if ($offset > 0) {
267: $sql .= " OFFSET $offset";
268: }
269:
270: return $sql;
271: }
272:
273: /**
274: * Return the most recent value from the specified sequence in the database.
275: * This is supported only on RDBMS brands that support sequences
276: * (e.g. Oracle, PostgreSQL, DB2). Other RDBMS brands return null.
277: *
278: * @param string $sequenceName
279: * @return string
280: */
281: public function lastSequenceId($sequenceName)
282: {
283: $this->_connect();
284: $sequenceName = str_replace($this->getQuoteIdentifierSymbol(), '', (string) $sequenceName);
285: $value = $this->fetchOne("SELECT CURRVAL("
286: . $this->quote($this->quoteIdentifier($sequenceName, true))
287: . ")");
288: return $value;
289: }
290:
291: /**
292: * Generate a new value from the specified sequence in the database, and return it.
293: * This is supported only on RDBMS brands that support sequences
294: * (e.g. Oracle, PostgreSQL, DB2). Other RDBMS brands return null.
295: *
296: * @param string $sequenceName
297: * @return string
298: */
299: public function nextSequenceId($sequenceName)
300: {
301: $this->_connect();
302: $sequenceName = str_replace($this->getQuoteIdentifierSymbol(), '', (string) $sequenceName);
303: $value = $this->fetchOne("SELECT NEXTVAL("
304: . $this->quote($this->quoteIdentifier($sequenceName, true))
305: . ")");
306: return $value;
307: }
308:
309: /**
310: * Gets the last ID generated automatically by an IDENTITY/AUTOINCREMENT column.
311: *
312: * As a convention, on RDBMS brands that support sequences
313: * (e.g. Oracle, PostgreSQL, DB2), this method forms the name of a sequence
314: * from the arguments and returns the last id generated by that sequence.
315: * On RDBMS brands that support IDENTITY/AUTOINCREMENT columns, this method
316: * returns the last value generated for such a column, and the table name
317: * argument is disregarded.
318: *
319: * @param string $tableName OPTIONAL Name of table.
320: * @param string $primaryKey OPTIONAL Name of primary key column.
321: * @return string
322: */
323: public function lastInsertId($tableName = null, $primaryKey = null)
324: {
325: if ($tableName !== null) {
326: $sequenceName = $tableName;
327: if ($primaryKey) {
328: $sequenceName .= "_$primaryKey";
329: }
330: $sequenceName .= '_seq';
331: return $this->lastSequenceId($sequenceName);
332: }
333: return $this->_connection->lastInsertId($tableName);
334: }
335:
336: }
337: