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