965314dbc4422ce9d21a46ff32c04590a8d7a3fd
[Packages/TYPO3.CMS.git] / typo3 / sysext / adodb / adodb / datadict / datadict-postgres.inc.php
1 <?php
2
3 /**
4 V5.19 23-Apr-2014 (c) 2000-2014 John Lim (jlim#natsoft.com). All rights reserved.
5 Released under both BSD license and Lesser GPL library license.
6 Whenever there is any discrepancy between the two licenses,
7 the BSD license will take precedence.
8
9 Set tabs to 4 for best viewing.
10
11 */
12
13 // security - hide paths
14 if (!defined('ADODB_DIR')) die();
15
16 class ADODB2_postgres extends ADODB_DataDict {
17
18 var $databaseType = 'postgres';
19 var $seqField = false;
20 var $seqPrefix = 'SEQ_';
21 var $addCol = ' ADD COLUMN';
22 var $quote = '"';
23 var $renameTable = 'ALTER TABLE %s RENAME TO %s'; // at least since 7.1
24 var $dropTable = 'DROP TABLE %s CASCADE';
25
26 function MetaType($t,$len=-1,$fieldobj=false)
27 {
28 if (is_object($t)) {
29 $fieldobj = $t;
30 $t = $fieldobj->type;
31 $len = $fieldobj->max_length;
32 }
33 $is_serial = is_object($fieldobj) && !empty($fieldobj->primary_key) && !empty($fieldobj->unique) &&
34 !empty($fieldobj->has_default) && substr($fieldobj->default_value,0,8) == 'nextval(';
35
36 switch (strtoupper($t)) {
37 case 'INTERVAL':
38 case 'CHAR':
39 case 'CHARACTER':
40 case 'VARCHAR':
41 case 'NAME':
42 case 'BPCHAR':
43 if ($len <= $this->blobSize) return 'C';
44
45 case 'TEXT':
46 return 'X';
47
48 case 'IMAGE': // user defined type
49 case 'BLOB': // user defined type
50 case 'BIT': // This is a bit string, not a single bit, so don't return 'L'
51 case 'VARBIT':
52 case 'BYTEA':
53 return 'B';
54
55 case 'BOOL':
56 case 'BOOLEAN':
57 return 'L';
58
59 case 'DATE':
60 return 'D';
61
62 case 'TIME':
63 case 'DATETIME':
64 case 'TIMESTAMP':
65 case 'TIMESTAMPTZ':
66 return 'T';
67
68 case 'INTEGER': return !$is_serial ? 'I' : 'R';
69 case 'SMALLINT':
70 case 'INT2': return !$is_serial ? 'I2' : 'R';
71 case 'INT4': return !$is_serial ? 'I4' : 'R';
72 case 'BIGINT':
73 case 'INT8': return !$is_serial ? 'I8' : 'R';
74
75 case 'OID':
76 case 'SERIAL':
77 return 'R';
78
79 case 'FLOAT4':
80 case 'FLOAT8':
81 case 'DOUBLE PRECISION':
82 case 'REAL':
83 return 'F';
84
85 default:
86 return 'N';
87 }
88 }
89
90 function ActualType($meta)
91 {
92 switch($meta) {
93 case 'C': return 'VARCHAR';
94 case 'XL':
95 case 'X': return 'TEXT';
96
97 case 'C2': return 'VARCHAR';
98 case 'X2': return 'TEXT';
99
100 case 'B': return 'BYTEA';
101
102 case 'D': return 'DATE';
103 case 'TS':
104 case 'T': return 'TIMESTAMP';
105
106 case 'L': return 'BOOLEAN';
107 case 'I': return 'INTEGER';
108 case 'I1': return 'SMALLINT';
109 case 'I2': return 'INT2';
110 case 'I4': return 'INT4';
111 case 'I8': return 'INT8';
112
113 case 'F': return 'FLOAT8';
114 case 'N': return 'NUMERIC';
115 default:
116 return $meta;
117 }
118 }
119
120 /**
121 * Adding a new Column
122 *
123 * reimplementation of the default function as postgres does NOT allow to set the default in the same statement
124 *
125 * @param string $tabname table-name
126 * @param string $flds column-names and types for the changed columns
127 * @return array with SQL strings
128 */
129 function AddColumnSQL($tabname, $flds)
130 {
131 $tabname = $this->TableName ($tabname);
132 $sql = array();
133 $not_null = false;
134 list($lines,$pkey) = $this->_GenFields($flds);
135 $alter = 'ALTER TABLE ' . $tabname . $this->addCol . ' ';
136 foreach($lines as $v) {
137 if (($not_null = preg_match('/NOT NULL/i',$v))) {
138 $v = preg_replace('/NOT NULL/i','',$v);
139 }
140 if (preg_match('/^([^ ]+) .*DEFAULT (\'[^\']+\'|\"[^\"]+\"|[^ ]+)/',$v,$matches)) {
141 list(,$colname,$default) = $matches;
142 $sql[] = $alter . str_replace('DEFAULT '.$default,'',$v);
143 $sql[] = 'UPDATE '.$tabname.' SET '.$colname.'='.$default;
144 $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET DEFAULT ' . $default;
145 } else {
146 $sql[] = $alter . $v;
147 }
148 if ($not_null) {
149 list($colname) = explode(' ',$v);
150 $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET NOT NULL';
151 }
152 }
153 return $sql;
154 }
155
156
157 function DropIndexSQL ($idxname, $tabname = NULL)
158 {
159 return array(sprintf($this->dropIndex, $this->TableName($idxname), $this->TableName($tabname)));
160 }
161
162 /**
163 * Change the definition of one column
164 *
165 * Postgres can't do that on it's own, you need to supply the complete defintion of the new table,
166 * to allow, recreating the table and copying the content over to the new table
167 * @param string $tabname table-name
168 * @param string $flds column-name and type for the changed column
169 * @param string $tableflds complete defintion of the new table, eg. for postgres, default ''
170 * @param array/ $tableoptions options for the new table see CreateTableSQL, default ''
171 * @return array with SQL strings
172 */
173 /*
174 function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
175 {
176 if (!$tableflds) {
177 if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL");
178 return array();
179 }
180 return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions);
181 }*/
182
183 function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
184 {
185 // Check if alter single column datatype available - works with 8.0+
186 $has_alter_column = 8.0 <= (float) @$this->serverInfo['version'];
187
188 if ($has_alter_column) {
189 $tabname = $this->TableName($tabname);
190 $sql = array();
191 list($lines,$pkey) = $this->_GenFields($flds);
192 $set_null = false;
193 $alter = 'ALTER TABLE ' . $tabname . $this->alterCol . ' ';
194 foreach($lines as $v) {
195 if ($not_null = preg_match('/NOT NULL/i',$v)) {
196 $v = preg_replace('/NOT NULL/i','',$v);
197 }
198 // this next block doesn't work - there is no way that I can see to
199 // explicitly ask a column to be null using $flds
200 else if ($set_null = preg_match('/NULL/i',$v)) {
201 // if they didn't specify not null, see if they explicitely asked for null
202 // Lookbehind pattern covers the case 'fieldname NULL datatype DEFAULT NULL'
203 // only the first NULL should be removed, not the one specifying
204 // the default value
205 $v = preg_replace('/(?<!DEFAULT)\sNULL/i','',$v);
206 }
207
208 if (preg_match('/^([^ ]+) .*DEFAULT (\'[^\']+\'|\"[^\"]+\"|[^ ]+)/',$v,$matches)) {
209 $existing = $this->MetaColumns($tabname);
210 list(,$colname,$default) = $matches;
211 $alter .= $colname;
212 if ($this->connection) {
213 $old_coltype = $this->connection->MetaType($existing[strtoupper($colname)]);
214 }
215 else {
216 $old_coltype = $t;
217 }
218 $v = preg_replace('/^' . preg_quote($colname) . '\s/', '', $v);
219 $t = trim(str_replace('DEFAULT '.$default,'',$v));
220
221 // Type change from bool to int
222 if ( $old_coltype == 'L' && $t == 'INTEGER' ) {
223 $sql[] = $alter . ' DROP DEFAULT';
224 $sql[] = $alter . " TYPE $t USING ($colname::BOOL)::INT";
225 $sql[] = $alter . " SET DEFAULT $default";
226 }
227 // Type change from int to bool
228 else if ( $old_coltype == 'I' && $t == 'BOOLEAN' ) {
229 if( strcasecmp('NULL', trim($default)) != 0 ) {
230 $default = $this->connection->qstr($default);
231 }
232 $sql[] = $alter . ' DROP DEFAULT';
233 $sql[] = $alter . " TYPE $t USING CASE WHEN $colname = 0 THEN false ELSE true END";
234 $sql[] = $alter . " SET DEFAULT $default";
235 }
236 // Any other column types conversion
237 else {
238 $sql[] = $alter . " TYPE $t";
239 $sql[] = $alter . " SET DEFAULT $default";
240 }
241
242 }
243 else {
244 // drop default?
245 preg_match ('/^\s*(\S+)\s+(.*)$/',$v,$matches);
246 list (,$colname,$rest) = $matches;
247 $alter .= $colname;
248 $sql[] = $alter . ' TYPE ' . $rest;
249 }
250
251 # list($colname) = explode(' ',$v);
252 if ($not_null) {
253 // this does not error out if the column is already not null
254 $sql[] = $alter . ' SET NOT NULL';
255 }
256 if ($set_null) {
257 // this does not error out if the column is already null
258 $sql[] = $alter . ' DROP NOT NULL';
259 }
260 }
261 return $sql;
262 }
263
264 // does not have alter column
265 if (!$tableflds) {
266 if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL");
267 return array();
268 }
269 return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions);
270 }
271
272 /**
273 * Drop one column
274 *
275 * Postgres < 7.3 can't do that on it's own, you need to supply the complete defintion of the new table,
276 * to allow, recreating the table and copying the content over to the new table
277 * @param string $tabname table-name
278 * @param string $flds column-name and type for the changed column
279 * @param string $tableflds complete defintion of the new table, eg. for postgres, default ''
280 * @param array/ $tableoptions options for the new table see CreateTableSQL, default ''
281 * @return array with SQL strings
282 */
283 function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
284 {
285 $has_drop_column = 7.3 <= (float) @$this->serverInfo['version'];
286 if (!$has_drop_column && !$tableflds) {
287 if ($this->debug) ADOConnection::outp("DropColumnSQL needs complete table-definiton for PostgreSQL < 7.3");
288 return array();
289 }
290 if ($has_drop_column) {
291 return ADODB_DataDict::DropColumnSQL($tabname, $flds);
292 }
293 return $this->_recreate_copy_table($tabname,$flds,$tableflds,$tableoptions);
294 }
295
296 /**
297 * Save the content into a temp. table, drop and recreate the original table and copy the content back in
298 *
299 * We also take care to set the values of the sequenz and recreate the indexes.
300 * All this is done in a transaction, to not loose the content of the table, if something went wrong!
301 * @internal
302 * @param string $tabname table-name
303 * @param string $dropflds column-names to drop
304 * @param string $tableflds complete defintion of the new table, eg. for postgres
305 * @param array/string $tableoptions options for the new table see CreateTableSQL, default ''
306 * @return array with SQL strings
307 */
308 function _recreate_copy_table($tabname,$dropflds,$tableflds,$tableoptions='')
309 {
310 if ($dropflds && !is_array($dropflds)) $dropflds = explode(',',$dropflds);
311 $copyflds = array();
312 foreach($this->MetaColumns($tabname) as $fld) {
313 if (!$dropflds || !in_array($fld->name,$dropflds)) {
314 // we need to explicit convert varchar to a number to be able to do an AlterColumn of a char column to a nummeric one
315 if (preg_match('/'.$fld->name.' (I|I2|I4|I8|N|F)/i',$tableflds,$matches) &&
316 in_array($fld->type,array('varchar','char','text','bytea'))) {
317 $copyflds[] = "to_number($fld->name,'S9999999999999D99')";
318 } else {
319 $copyflds[] = $fld->name;
320 }
321 // identify the sequence name and the fld its on
322 if ($fld->primary_key && $fld->has_default &&
323 preg_match("/nextval\('([^']+)'::text\)/",$fld->default_value,$matches)) {
324 $seq_name = $matches[1];
325 $seq_fld = $fld->name;
326 }
327 }
328 }
329 $copyflds = implode(', ',$copyflds);
330
331 $tempname = $tabname.'_tmp';
332 $aSql[] = 'BEGIN'; // we use a transaction, to make sure not to loose the content of the table
333 $aSql[] = "SELECT * INTO TEMPORARY TABLE $tempname FROM $tabname";
334 $aSql = array_merge($aSql,$this->DropTableSQL($tabname));
335 $aSql = array_merge($aSql,$this->CreateTableSQL($tabname,$tableflds,$tableoptions));
336 $aSql[] = "INSERT INTO $tabname SELECT $copyflds FROM $tempname";
337 if ($seq_name && $seq_fld) { // if we have a sequence we need to set it again
338 $seq_name = $tabname.'_'.$seq_fld.'_seq'; // has to be the name of the new implicit sequence
339 $aSql[] = "SELECT setval('$seq_name',MAX($seq_fld)) FROM $tabname";
340 }
341 $aSql[] = "DROP TABLE $tempname";
342 // recreate the indexes, if they not contain one of the droped columns
343 foreach($this->MetaIndexes($tabname) as $idx_name => $idx_data)
344 {
345 if (substr($idx_name,-5) != '_pkey' && (!$dropflds || !count(array_intersect($dropflds,$idx_data['columns'])))) {
346 $aSql = array_merge($aSql,$this->CreateIndexSQL($idx_name,$tabname,$idx_data['columns'],
347 $idx_data['unique'] ? array('UNIQUE') : False));
348 }
349 }
350 $aSql[] = 'COMMIT';
351 return $aSql;
352 }
353
354 function DropTableSQL($tabname)
355 {
356 $sql = ADODB_DataDict::DropTableSQL($tabname);
357
358 $drop_seq = $this->_DropAutoIncrement($tabname);
359 if ($drop_seq) $sql[] = $drop_seq;
360
361 return $sql;
362 }
363
364 // return string must begin with space
365 function _CreateSuffix($fname, &$ftype, $fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned)
366 {
367 if ($fautoinc) {
368 $ftype = 'SERIAL';
369 return '';
370 }
371 $suffix = '';
372 if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
373 if ($fnotnull) $suffix .= ' NOT NULL';
374 if ($fconstraint) $suffix .= ' '.$fconstraint;
375 return $suffix;
376 }
377
378 // search for a sequece for the given table (asumes the seqence-name contains the table-name!)
379 // if yes return sql to drop it
380 // this is still necessary if postgres < 7.3 or the SERIAL was created on an earlier version!!!
381 function _DropAutoIncrement($tabname)
382 {
383 $tabname = $this->connection->quote('%'.$tabname.'%');
384
385 $seq = $this->connection->GetOne("SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relname LIKE $tabname AND relkind='S'");
386
387 // check if a tables depends on the sequenz and it therefor cant and dont need to be droped separatly
388 if (!$seq || $this->connection->GetOne("SELECT relname FROM pg_class JOIN pg_depend ON pg_class.relfilenode=pg_depend.objid WHERE relname='$seq' AND relkind='S' AND deptype='i'")) {
389 return False;
390 }
391 return "DROP SEQUENCE ".$seq;
392 }
393
394 function RenameTableSQL($tabname,$newname)
395 {
396 if (!empty($this->schema)) {
397 $rename_from = $this->TableName($tabname);
398 $schema_save = $this->schema;
399 $this->schema = false;
400 $rename_to = $this->TableName($newname);
401 $this->schema = $schema_save;
402 return array (sprintf($this->renameTable, $rename_from, $rename_to));
403 }
404
405 return array (sprintf($this->renameTable, $this->TableName($tabname),$this->TableName($newname)));
406 }
407
408 /*
409 CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
410 { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
411 | table_constraint } [, ... ]
412 )
413 [ INHERITS ( parent_table [, ... ] ) ]
414 [ WITH OIDS | WITHOUT OIDS ]
415 where column_constraint is:
416 [ CONSTRAINT constraint_name ]
417 { NOT NULL | NULL | UNIQUE | PRIMARY KEY |
418 CHECK (expression) |
419 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
420 [ ON DELETE action ] [ ON UPDATE action ] }
421 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
422 and table_constraint is:
423 [ CONSTRAINT constraint_name ]
424 { UNIQUE ( column_name [, ... ] ) |
425 PRIMARY KEY ( column_name [, ... ] ) |
426 CHECK ( expression ) |
427 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
428 [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
429 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
430 */
431
432
433 /*
434 CREATE [ UNIQUE ] INDEX index_name ON table
435 [ USING acc_method ] ( column [ ops_name ] [, ...] )
436 [ WHERE predicate ]
437 CREATE [ UNIQUE ] INDEX index_name ON table
438 [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
439 [ WHERE predicate ]
440 */
441 function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
442 {
443 $sql = array();
444
445 if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
446 $sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
447 if ( isset($idxoptions['DROP']) )
448 return $sql;
449 }
450
451 if ( empty ($flds) ) {
452 return $sql;
453 }
454
455 $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
456
457 $s = 'CREATE' . $unique . ' INDEX ' . $idxname . ' ON ' . $tabname . ' ';
458
459 if (isset($idxoptions['HASH']))
460 $s .= 'USING HASH ';
461
462 if ( isset($idxoptions[$this->upperName]) )
463 $s .= $idxoptions[$this->upperName];
464
465 if ( is_array($flds) )
466 $flds = implode(', ',$flds);
467 $s .= '(' . $flds . ')';
468 $sql[] = $s;
469
470 return $sql;
471 }
472
473 function _GetSize($ftype, $ty, $fsize, $fprec)
474 {
475 if (strlen($fsize) && $ty != 'X' && $ty != 'B' && $ty != 'I' && strpos($ftype,'(') === false) {
476 $ftype .= "(".$fsize;
477 if (strlen($fprec)) $ftype .= ",".$fprec;
478 $ftype .= ')';
479 }
480 return $ftype;
481 }
482 }