ASSIGNED - # 8472: Update table with PostgreSQL fails
[tine20] / tine20 / Setup / Backend / Pgsql.php
1 <?php
2 /**
3  * Tine 2.0
4  *
5  * @package     Setup
6  * @license     http://www.gnu.org/licenses/agpl.html AGPL Version 3
7  * @author      Fl├ívio Gomes da Silva Lisboa <flavio.lisboa@serpro.gov.br>
8  * @copyright   Copyright (c) 2011-2012 Metaways Infosystems GmbH (http://www.metaways.de)
9  *
10  */
11
12 /**
13  * setup backend class for PostgreSQL 8.3 +
14  * based on class Setup_Backend_Mysql
15  * @package     Setup
16  */
17 class Setup_Backend_Pgsql extends Setup_Backend_Abstract
18 {
19     /**
20      * Define how database agnostic data types get mapped to postgresql data types
21      * @todo reviews data type
22      * @var array
23      */
24     protected $_typeMappings = array(
25         'integer' => array(
26             'lengthTypes' => array(
27                 4 => 'smallint',
28                 19 => 'integer',
29                 64 => 'bigint'),
30             'defaultType' => 'integer',
31             'defaultLength' => Setup_Backend_Abstract::INTEGER_DEFAULT_LENGTH),
32         'boolean' => array(
33             'defaultType' => 'NUMERIC',
34             'defaultScale' => 0,
35             'defaultLength' => 1),
36         'text' => array(
37             'lengthTypes' => array(
38                 256 => 'character varying', //@todo this should be 255 indeed but we have 256 in our setup.xml files
39                 65535 => 'character varying',
40                 16777215 => 'character varying',
41                 4294967295 => 'character varying'),
42             'defaultType' => 'text',
43             'defaultLength' => null),
44         'float' => array(
45             'defaultType' => 'double precision',
46             'defaultLength' => null),
47         'decimal' => array(
48             'defaultType' => 'numeric',
49             'defaultLength' => null),
50         'datetime' => array(
51             'defaultType' => 'timestamp',
52             'defaultLength' => null),
53         'time' => array(
54             'defaultType' => 'time',
55             'defaultLength' => null),
56         'date' => array(
57             'defaultType' => 'date',
58             'defaultLength' => null),
59         'blob' => array(
60             'defaultType' => 'text',
61             'defaultLength' => null),
62         'clob' => array(
63             'defaultType' => 'text',
64             'defaultLength' => null),
65         'enum' => array(
66             'defaultType' => 'enum',
67             'defaultLength' => null)
68     );
69
70     /**
71      * Generates an SQL CREATE STATEMENT
72      * @param Setup_Backend_Schema_Table_Abstract $_table
73      * @return array CREATE TABLE statement, sequence, indexes
74      * @throws Setup_Exception_NotFound
75      */
76     public function getCreateStatement(Setup_Backend_Schema_Table_Abstract $_table) 
77     {
78         $enums = array();
79         $statement = "CREATE TABLE " . $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . $_table->name) . " (\n";
80         $statementSnippets = array();
81
82         // get primary key now because it is necessary in two places
83         $primaryKey = $this->_getPrimaryKeyName($_table);
84
85         foreach ($_table->fields as $field) {
86             if (isset($field->name)) {
87                 $fieldDeclarations = $this->getFieldDeclarations($field, $_table->name);
88
89                 if (strpos($primaryKey, $field->name) !== false) {
90                     // replaces integer auto_increment with serial
91                     $sequence = SQL_TABLE_PREFIX . $_table->name . "_{$primaryKey}_seq";
92                     // don't create sequence if is field is not auto_increment
93                     $primaryKey = (strpos($fieldDeclarations, 'auto_increment') !== false) ? $primaryKey : null;
94                     $fieldDeclarations = str_replace('integer NOT NULL auto_increment', 'integer NOT NULL DEFAULT nextval(' . $this->_db->quote($sequence) . ')', $fieldDeclarations);
95                 }
96
97                 $statementSnippets[] = $fieldDeclarations;
98             }
99         }
100
101         $createIndexStatement = '';
102
103         foreach ($_table->indices as $index) {
104             if ($index->foreign) {
105                 $statementSnippets[] = $this->getForeignKeyDeclarations($index);
106
107             } else {
108                 $statementSnippet = $this->getIndexDeclarations($index, $_table->name);
109                 if (strpos($statementSnippet, 'CREATE INDEX') !== false) {
110                     $createIndexStatement = $statementSnippet;
111                 } else {
112                     $statementSnippets[] = $statementSnippet;
113                 }
114             }
115         }
116
117         $statement .= implode(",\n", $statementSnippets) . "\n)";
118         
119         return array('table' => $statement, 'index' => $createIndexStatement, 'primary' => $primaryKey);
120     }
121
122     /**
123      *
124      * Gets the primary key name
125      * @param Setup_Backend_Schema_Table_Abstract $table
126      */
127     private function _getPrimaryKeyName($table)
128     {
129         $primaryKeyName = '';
130
131         foreach ($table->indices as $index) {
132             if ($index->primary === 'true') {
133                 foreach ($index->field as $field) {
134                     $primaryKeyName .= $field . '-';
135                 }
136                 $primaryKeyName = substr($primaryKeyName, 0, strlen($primaryKeyName) - 1);
137
138                 return $primaryKeyName;
139             }
140         }
141
142         return null;
143     }
144     
145     /**
146      * check if given constraint exists 
147      *
148      * @param string $_tableName
149      * @return boolean|string "version" if the table exists, otherwise false
150      */
151     protected function _constraintExists($_name)
152     {
153         $select = $this->_db->select()
154             ->from('pg_constraint')
155             ->where($this->_db->quoteIdentifier('conname') . ' = ?', $_name);
156         
157         $stmt = $select->query();
158         $constraint = $stmt->fetch();
159         
160         return ($constraint === false) ? false : true;
161     }
162     
163     /**
164      * (non-PHPdoc)
165      * @see Setup_Backend_Interface::getExistingForeignKeys()
166      */
167     public function getExistingForeignKeys($tableName)
168     {
169         $select = $this->_db->select()
170             ->from(array('table_constraints' => 'information_schema.table_constraints'), array('table_name', 'constraint_name'))
171             ->join(
172                 array('constraint_column_usage' => 'information_schema.constraint_column_usage'), 
173                 $this->_db->quoteIdentifier('table_constraints.constraint_name') . '=' . $this->_db->quoteIdentifier('constraint_column_usage.constraint_name'),
174                 array()
175             )
176             ->where($this->_db->quoteIdentifier('table_constraints.constraint_catalog') . ' = ?', $this->_config->database->dbname)
177             ->where($this->_db->quoteIdentifier('table_constraints.constraint_type')    . ' = ?', 'FOREIGN KEY')
178             ->where($this->_db->quoteIdentifier('constraint_column_usage.table_name')   . ' = ?', SQL_TABLE_PREFIX . $tableName);
179
180         $foreignKeyNames = array();
181         
182         $stmt = $select->query();
183         while ($row = $stmt->fetch()) {
184             $foreignKeyNames[$row['constraint_name']] = array(
185                 'table_name'      => str_replace(SQL_TABLE_PREFIX, '', $row['table_name']), 
186                 'constraint_name' => str_replace(SQL_TABLE_PREFIX, '', $row['constraint_name']));
187         }
188         
189         return $foreignKeyNames;
190     }
191     
192     /**
193      * Get schema of existing table
194      *
195      * @param String $_tableName
196      *
197      * @return Setup_Backend_Schema_Table_Pgsql
198      */
199     public function getExistingSchema($_tableName)
200     {
201         // Get common table information
202         $select = $this->_db->select()
203             ->from('information_schema.tables')
204             ->where($this->_db->quoteIdentifier('TABLE_SCHEMA') . ' = ?', $this->_config->database->dbname)
205             ->where($this->_db->quoteIdentifier('TABLE_NAME') . ' = ?', SQL_TABLE_PREFIX . $_tableName);
206
207
208         $stmt = $select->query();
209         $tableInfo = $stmt->fetchObject();
210
211         //$existingTable = new Setup_Backend_Schema_Table($tableInfo);
212         $existingTable = Setup_Backend_Schema_Table_Factory::factory('Pgsql', $tableInfo);
213         // get field informations
214         $select = $this->_db->select()
215             ->from('information_schema.COLUMNS')
216             ->where($this->_db->quoteIdentifier('TABLE_NAME') . ' = ?', SQL_TABLE_PREFIX . $_tableName);
217
218         $stmt = $select->query();
219         $tableColumns = $stmt->fetchAll();
220
221         foreach ($tableColumns as $tableColumn) {
222             $field = Setup_Backend_Schema_Field_Factory::factory('Pgsql', $tableColumn);
223             $existingTable->addField($field);
224
225             if ($field->primary === 'true' || $field->unique === 'true' || $field->mul === 'true') {
226                 $index = Setup_Backend_Schema_Index_Factory::factory('Pgsql', $tableColumn);
227
228                 // get foreign keys
229                 $select = $this->_db->select()
230                         ->from('information_schema.KEY_COLUMN_USAGE')
231                         ->where($this->_db->quoteIdentifier('TABLE_NAME') . ' = ?', SQL_TABLE_PREFIX . $_tableName)
232                         ->where($this->_db->quoteIdentifier('COLUMN_NAME') . ' = ?', $tableColumn['COLUMN_NAME']);
233
234                 $stmt = $select->query();
235                 $keyUsage = $stmt->fetchAll();
236
237                 foreach ($keyUsage as $keyUse) {
238                     if ($keyUse['REFERENCED_TABLE_NAME'] != NULL) {
239                         $index->setForeignKey($keyUse);
240                     }
241                 }
242
243                 $existingTable->addIndex($index);
244             }
245         }
246
247         return $existingTable;
248     }
249
250     /**
251      * add column/field to database table
252      *
253      * @param string tableName
254      * @param Setup_Backend_Schema_Field_Abstract declaration
255      * @param int position of future column
256      */
257     public function addCol($_tableName, Setup_Backend_Schema_Field_Abstract $_declaration, $_position = NULL) 
258     {
259         $statement = 'ALTER TABLE ' . $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . $_tableName) . ' ADD COLUMN ';
260
261         $statement .= $this->getFieldDeclarations($_declaration);
262
263         $this->execQueryVoid($statement);
264     }
265
266     /**
267      * rename or redefines column/field in database table
268      *
269      * @param string tableName
270      * @param Setup_Backend_Schema_Field_Abstract declaration
271      * @param string old column/field name
272      */
273     public function alterCol($_tableName, Setup_Backend_Schema_Field_Abstract $_declaration, $_oldName = NULL) 
274     {
275         $quotedName = $this->_db->quoteIdentifier($_declaration->name);
276         
277         $baseStatement = 'ALTER TABLE ' . $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . $_tableName);
278
279         // rename the column if needed
280         if ($_oldName !== NULL) {
281             $statement = $baseStatement . ' RENAME COLUMN ' . $this->_db->quoteIdentifier($_oldName) . ' TO ' . $quotedName;
282             $this->execQueryVoid($statement);
283         }
284
285         $fieldDeclaration = $this->getFieldDeclarations($_declaration);
286         
287         // strip of column name from the beginning
288         $type      = trim(str_replace($quotedName, null, $fieldDeclaration));
289         // cut of NOT NULL and DEFAULT from the end
290         $type      = preg_replace(array('/ (NOT NULL|DEFAULT .*)/'), null, $type);
291         
292         $statement = $baseStatement . ' ALTER COLUMN ' . $quotedName . ' TYPE ' . $type . ' USING CAST(' . $quotedName . ' AS ' . $type . ')';
293         $this->execQueryVoid($statement);
294         
295         if (preg_match('/NOT NULL/', $fieldDeclaration)) {
296             $statement = $baseStatement . ' ALTER COLUMN ' . $quotedName . ' SET NOT NULL ';
297             $this->execQueryVoid($statement);
298         }
299         
300         if (preg_match('/(?P<DEFAULT>DEFAULT .*)/', $fieldDeclaration, $matches)) {
301             $statement = $baseStatement . ' ALTER COLUMN ' . $quotedName . ' SET ' . $matches['DEFAULT'];
302             $this->execQueryVoid($statement);
303         }
304     }
305
306     /**
307      * add a key to database table
308      *
309      * @param string tableName
310      * @param Setup_Backend_Schema_Index_Abstract declaration
311      */
312     public function addIndex($_tableName, Setup_Backend_Schema_Index_Abstract $_declaration) 
313     {
314         if (!empty($_declaration->primary)) {
315             $identifier = SQL_TABLE_PREFIX . $_tableName . '_pkey';
316         } elseif (!empty($_declaration->unique)) {
317             $identifier = SQL_TABLE_PREFIX . $_tableName . '_' . $_key->name . '_key';
318         } else {
319             $identifier = SQL_TABLE_PREFIX . $_tableName . '_' . $_key->name;
320         }
321         
322         if ($this->_constraintExists($identifier)) {
323             throw new Zend_Db_Statement_Exception('index does exist already');
324         }
325         
326         $indexSnippet = $this->getIndexDeclarations($_declaration, $_tableName);
327         
328         if (strpos($indexSnippet, 'CREATE INDEX') !== false) {
329             $statement = $indexSnippet;
330         } else {
331             $statement = 'ALTER TABLE ' . $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . $_tableName) . ' ADD ' . $indexSnippet;
332         }
333
334         $this->execQueryVoid($statement);
335     }
336
337     /**
338      * create the right pgsql-statement-snippet for keys.
339      * return constraints to add to create table statement or
340      * return create index statement
341      * @param   Setup_Backend_Schema_Index_Abstract $_key
342      * @param String | optional $_tableName [is not used in this Backend (PgSQL)]
343      * @return  string
344      * @throws  Setup_Exception_NotFound
345      */
346     public function getIndexDeclarations(Setup_Backend_Schema_Index_Abstract $_key, $_tableName = '')
347     {
348         if (!empty($_key->primary)) {
349             $identifier = $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . $_tableName . '_pkey');
350             $snippet = " CONSTRAINT $identifier PRIMARY KEY";
351         } elseif (!empty($_key->unique)) {
352             $identifier  = $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . $_tableName . '_' . $_key->name . '_key');
353             $snippet = "CONSTRAINT $identifier UNIQUE";
354         } else {
355             $identifier = $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . $_tableName . '_' . $_key->name);
356             $snippet = "CREATE INDEX $identifier ON " . $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . $_tableName);
357         }
358         
359         $keys = array();
360         
361         foreach ($_key->field as $keyfield) {
362             $keys[] = $this->_db->quoteIdentifier((string) $keyfield);
363         }
364
365         if (empty($keys)) {
366             throw new Setup_Exception_NotFound('no keys for index found');
367         }
368
369         $snippet .= ' (' . implode(',', $keys) . ')';
370
371         return $snippet;
372     }
373
374     /**
375      *  create the right mysql-statement-snippet for foreign keys
376      *
377      * @param object $_key the xml index definition
378      * @return string
379      */
380     public function getForeignKeyDeclarations(Setup_Backend_Schema_Index_Abstract $_key)
381     {
382         $snippet = ' CONSTRAINT ' . $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . $_key->name) . 
383             ' FOREIGN KEY (' . $this->_db->quoteIdentifier($_key->field) . ')' . 
384             ' REFERENCES ' . $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . $_key->referenceTable) . 
385                 ' (' . $this->_db->quoteIdentifier($_key->referenceField) . ')';
386
387         if (!empty($_key->referenceOnDelete)) {
388             $snippet .= ' ON DELETE ' . strtoupper($_key->referenceOnDelete);
389         }
390         if (!empty($_key->referenceOnUpdate)) {
391             $snippet .= ' ON UPDATE ' . strtoupper($_key->referenceOnUpdate);
392         }
393
394         return $snippet;
395     }
396
397     /**
398      * enable/disabled foreign key checks
399      *
400      * @param integer|string|boolean $_value
401      */
402     public function setForeignKeyChecks($_value)
403     {
404         // does nothing
405     }
406
407     /**
408      * takes the xml stream and creates a table
409      *
410      * @param object $_table xml stream
411      */
412     public function createTable(Setup_Backend_Schema_Table_Abstract $_table)
413     {
414         // receives an array with CREATE TABLE and CREATE INDEX statements
415         $statements = $this->getCreateStatement($_table);
416
417         try {
418             // creates sequence
419             if (!empty($statements['primary'])) {
420                 $sequence = $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . $_table->name . '_' . $statements['primary'] . '_seq');
421
422                 $createSequence = 'CREATE SEQUENCE ' . $sequence;
423
424                 $this->execQueryVoid($createSequence);
425             }
426
427             // creates table
428             $this->execQueryVoid($statements['table']);
429
430             // creates indexes
431             if (!empty($statements['index']))
432                 $this->execQueryVoid($statements['index']);
433
434             // alters sequence
435             if (!empty($statements['primary'])) {
436                 $alterSequence = 'ALTER SEQUENCE ' . $sequence . ' OWNED BY ' . SQL_TABLE_PREFIX . $_table->name . '.' . $statements['primary'];
437                 $this->execQueryVoid($alterSequence);
438             }
439
440         } catch (Exception $e) {
441             Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Exception: ' . $e->getMessage() . ' Trace: ' . $e->getTraceAsString());
442         }
443     }
444     
445     /**
446      * removes a foreign key from database table
447      * 
448      * @param string tableName
449      * @param string foreign key name
450      */     
451     public function dropForeignKey($_tableName, $_name)
452     {
453         if (! $this->_constraintExists(SQL_TABLE_PREFIX . $_name)) {
454             throw new Zend_Db_Statement_Exception('foreign key does not exist');
455         }
456         
457         $statement = "ALTER TABLE " . $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . $_tableName) 
458             . " DROP CONSTRAINT " . $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . $_name);
459         
460         $this->execQueryVoid($statement);
461     }
462     
463     /**
464      * removes a key from database table
465      * 
466      * @param string tableName 
467      * @param string key name
468      */    
469     public function dropIndex($_tableName, $_indexName)
470     {
471         $indexName = SQL_TABLE_PREFIX . $_tableName . '_' . $_indexName;
472         if ($this->_constraintExists($indexName)) {
473             $statement = "DROP INDEX " . $this->_db->quoteIdentifier($indexName);
474     
475             $this->execQueryVoid($statement);
476             
477             return;
478         }
479         
480         $indexName = SQL_TABLE_PREFIX . $_tableName . '_' . $_indexName . '_key';
481         if ($this->_constraintExists($indexName)) {
482             $statement = "ALTER TABLE " . $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . $_tableName) . " DROP CONSTRAINT " . $this->_db->quoteIdentifier($indexName);
483     
484             $this->execQueryVoid($statement);
485             
486             return;
487         }
488         
489         throw new Zend_Db_Statement_Exception("index $_indexName does not exist");
490     }
491     
492     /**
493      * removes a primary key from database table
494      * 
495      * @param string tableName (there is just one primary key...)
496      */         
497     public function dropPrimaryKey($_tableName)
498     {
499         $indexName = SQL_TABLE_PREFIX . $_tableName . '_pkey';
500         
501         if (! $this->_constraintExists($indexName)) {
502             throw new Zend_Db_Statement_Exception("primary key for table $_tableName does not exist");
503         }
504         
505         $statement = "ALTER TABLE " . $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . $_tableName) . " DROP CONSTRAINT " . $this->_db->quoteIdentifier($indexName);
506         $this->execQueryVoid($statement);
507     }
508     
509     /**
510      * create the right postgreSql-statement-snippet for columns/fields
511      * PostgreSQL has not unsigned modifier
512      *
513      * @param Setup_Backend_Schema_Field_Abstract field / column
514      * @param String | optional $_tableName [Not used in this backend (PostgreSQL)]
515      * @return string
516      */
517     public function getFieldDeclarations(Setup_Backend_Schema_Field_Abstract $_field, $_tableName = '')
518     {
519         $_field->unsigned = false;
520
521         $fieldDeclarations = parent::getFieldDeclarations($_field, $_tableName);
522         
523         $fieldTypes = array ('tinyint', 'mediumint', 'bigint', 'int', 'integer');
524         foreach ($fieldTypes as $fieldType) {
525             $fieldDeclarations = preg_replace('/ ' . $fieldType . '\(\d*\)/', ' integer', $fieldDeclarations);
526         }
527         
528         $fieldDeclarations = preg_replace('/ smallint\(\d*\)/', ' smallint', $fieldDeclarations);
529         
530         return $fieldDeclarations;
531     }
532 }