c101c3c47ef125b34b82a69ddcd549f3d0353341
[Packages/TYPO3.CMS.git] / typo3 / sysext / adodb / adodb / datadict / datadict-postgres.inc.php
1 <?php
2
3 /**
4 V4.93 10 Oct 2006 (c) 2000-2006 John Lim (jlim#natsoft.com.my). 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) && $fieldobj->primary_key && $fieldobj->unique &&
34 $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 'T': return 'TIMESTAMP';
104
105 case 'L': return 'BOOLEAN';
106 case 'I': return 'INTEGER';
107 case 'I1': return 'SMALLINT';
108 case 'I2': return 'INT2';
109 case 'I4': return 'INT4';
110 case 'I8': return 'INT8';
111
112 case 'F': return 'FLOAT8';
113 case 'N': return 'NUMERIC';
114 default:
115 return $meta;
116 }
117 }
118
119 /**
120 * Adding a new Column
121 *
122 * reimplementation of the default function as postgres does NOT allow to set the default in the same statement
123 *
124 * @param string $tabname table-name
125 * @param string $flds column-names and types for the changed columns
126 * @return array with SQL strings
127 */
128 function AddColumnSQL($tabname, $flds)
129 {
130 $tabname = $this->TableName ($tabname);
131 $sql = array();
132 list($lines,$pkey) = $this->_GenFields($flds);
133 $alter = 'ALTER TABLE ' . $tabname . $this->addCol . ' ';
134 foreach($lines as $v) {
135 if (($not_null = preg_match('/NOT NULL/i',$v))) {
136 $v = preg_replace('/NOT NULL/i','',$v);
137 }
138 if (preg_match('/^([^ ]+) .*DEFAULT ([^ ]+)/',$v,$matches)) {
139 list(,$colname,$default) = $matches;
140 $sql[] = $alter . str_replace('DEFAULT '.$default,'',$v);
141 $sql[] = 'UPDATE '.$tabname.' SET '.$colname.'='.$default;
142 $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET DEFAULT ' . $default;
143 } else {
144 $sql[] = $alter . $v;
145 }
146 if ($not_null) {
147 list($colname) = explode(' ',$v);
148 $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET NOT NULL';
149 }
150 }
151 return $sql;
152 }
153
154 /**
155 * Change the definition of one column
156 *
157 * Postgres can't do that on it's own, you need to supply the complete defintion of the new table,
158 * to allow, recreating the table and copying the content over to the new table
159 * @param string $tabname table-name
160 * @param string $flds column-name and type for the changed column
161 * @param string $tableflds complete defintion of the new table, eg. for postgres, default ''
162 * @param array/ $tableoptions options for the new table see CreateTableSQL, default ''
163 * @return array with SQL strings
164 */
165 function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
166 {
167 if (!$tableflds) {
168 if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL");
169 return array();
170 }
171 return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions);
172 }
173
174 /**
175 * Drop one column
176 *
177 * Postgres < 7.3 can't do that on it's own, you need to supply the complete defintion of the new table,
178 * to allow, recreating the table and copying the content over to the new table
179 * @param string $tabname table-name
180 * @param string $flds column-name and type for the changed column
181 * @param string $tableflds complete defintion of the new table, eg. for postgres, default ''
182 * @param array/ $tableoptions options for the new table see CreateTableSQL, default ''
183 * @return array with SQL strings
184 */
185 function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
186 {
187 $has_drop_column = 7.3 <= (float) @$this->serverInfo['version'];
188 if (!$has_drop_column && !$tableflds) {
189 if ($this->debug) ADOConnection::outp("DropColumnSQL needs complete table-definiton for PostgreSQL < 7.3");
190 return array();
191 }
192 if ($has_drop_column) {
193 return ADODB_DataDict::DropColumnSQL($tabname, $flds);
194 }
195 return $this->_recreate_copy_table($tabname,$flds,$tableflds,$tableoptions);
196 }
197
198 /**
199 * Save the content into a temp. table, drop and recreate the original table and copy the content back in
200 *
201 * We also take care to set the values of the sequenz and recreate the indexes.
202 * All this is done in a transaction, to not loose the content of the table, if something went wrong!
203 * @internal
204 * @param string $tabname table-name
205 * @param string $dropflds column-names to drop
206 * @param string $tableflds complete defintion of the new table, eg. for postgres
207 * @param array/string $tableoptions options for the new table see CreateTableSQL, default ''
208 * @return array with SQL strings
209 */
210 function _recreate_copy_table($tabname,$dropflds,$tableflds,$tableoptions='')
211 {
212 if ($dropflds && !is_array($dropflds)) $dropflds = explode(',',$dropflds);
213 $copyflds = array();
214 foreach($this->MetaColumns($tabname) as $fld) {
215 if (!$dropflds || !in_array($fld->name,$dropflds)) {
216 // we need to explicit convert varchar to a number to be able to do an AlterColumn of a char column to a nummeric one
217 if (preg_match('/'.$fld->name.' (I|I2|I4|I8|N|F)/i',$tableflds,$matches) &&
218 in_array($fld->type,array('varchar','char','text','bytea'))) {
219 $copyflds[] = "to_number($fld->name,'S9999999999999D99')";
220 } else {
221 $copyflds[] = $fld->name;
222 }
223 // identify the sequence name and the fld its on
224 if ($fld->primary_key && $fld->has_default &&
225 preg_match("/nextval\('([^']+)'::text\)/",$fld->default_value,$matches)) {
226 $seq_name = $matches[1];
227 $seq_fld = $fld->name;
228 }
229 }
230 }
231 $copyflds = implode(', ',$copyflds);
232
233 $tempname = $tabname.'_tmp';
234 $aSql[] = 'BEGIN'; // we use a transaction, to make sure not to loose the content of the table
235 $aSql[] = "SELECT * INTO TEMPORARY TABLE $tempname FROM $tabname";
236 $aSql = array_merge($aSql,$this->DropTableSQL($tabname));
237 $aSql = array_merge($aSql,$this->CreateTableSQL($tabname,$tableflds,$tableoptions));
238 $aSql[] = "INSERT INTO $tabname SELECT $copyflds FROM $tempname";
239 if ($seq_name && $seq_fld) { // if we have a sequence we need to set it again
240 $seq_name = $tabname.'_'.$seq_fld.'_seq'; // has to be the name of the new implicit sequence
241 $aSql[] = "SELECT setval('$seq_name',MAX($seq_fld)) FROM $tabname";
242 }
243 $aSql[] = "DROP TABLE $tempname";
244 // recreate the indexes, if they not contain one of the droped columns
245 foreach($this->MetaIndexes($tabname) as $idx_name => $idx_data)
246 {
247 if (substr($idx_name,-5) != '_pkey' && (!$dropflds || !count(array_intersect($dropflds,$idx_data['columns'])))) {
248 $aSql = array_merge($aSql,$this->CreateIndexSQL($idx_name,$tabname,$idx_data['columns'],
249 $idx_data['unique'] ? array('UNIQUE') : False));
250 }
251 }
252 $aSql[] = 'COMMIT';
253 return $aSql;
254 }
255
256 function DropTableSQL($tabname)
257 {
258 $sql = ADODB_DataDict::DropTableSQL($tabname);
259
260 $drop_seq = $this->_DropAutoIncrement($tabname);
261 if ($drop_seq) $sql[] = $drop_seq;
262
263 return $sql;
264 }
265
266 // return string must begin with space
267 function _CreateSuffix($fname, &$ftype, $fnotnull,$fdefault,$fautoinc,$fconstraint)
268 {
269 if ($fautoinc) {
270 $ftype = 'SERIAL';
271 return '';
272 }
273 $suffix = '';
274 if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
275 if ($fnotnull) $suffix .= ' NOT NULL';
276 if ($fconstraint) $suffix .= ' '.$fconstraint;
277 return $suffix;
278 }
279
280 // search for a sequece for the given table (asumes the seqence-name contains the table-name!)
281 // if yes return sql to drop it
282 // this is still necessary if postgres < 7.3 or the SERIAL was created on an earlier version!!!
283 function _DropAutoIncrement($tabname)
284 {
285 $tabname = $this->connection->quote('%'.$tabname.'%');
286
287 $seq = $this->connection->GetOne("SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relname LIKE $tabname AND relkind='S'");
288
289 // check if a tables depends on the sequenz and it therefor cant and dont need to be droped separatly
290 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'")) {
291 return False;
292 }
293 return "DROP SEQUENCE ".$seq;
294 }
295
296 /*
297 CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
298 { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
299 | table_constraint } [, ... ]
300 )
301 [ INHERITS ( parent_table [, ... ] ) ]
302 [ WITH OIDS | WITHOUT OIDS ]
303 where column_constraint is:
304 [ CONSTRAINT constraint_name ]
305 { NOT NULL | NULL | UNIQUE | PRIMARY KEY |
306 CHECK (expression) |
307 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
308 [ ON DELETE action ] [ ON UPDATE action ] }
309 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
310 and table_constraint is:
311 [ CONSTRAINT constraint_name ]
312 { UNIQUE ( column_name [, ... ] ) |
313 PRIMARY KEY ( column_name [, ... ] ) |
314 CHECK ( expression ) |
315 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
316 [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
317 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
318 */
319
320
321 /*
322 CREATE [ UNIQUE ] INDEX index_name ON table
323 [ USING acc_method ] ( column [ ops_name ] [, ...] )
324 [ WHERE predicate ]
325 CREATE [ UNIQUE ] INDEX index_name ON table
326 [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
327 [ WHERE predicate ]
328 */
329 function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
330 {
331 $sql = array();
332
333 if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
334 $sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
335 if ( isset($idxoptions['DROP']) )
336 return $sql;
337 }
338
339 if ( empty ($flds) ) {
340 return $sql;
341 }
342
343 $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
344
345 $s = 'CREATE' . $unique . ' INDEX "' . $idxname . '" ON "' . $tabname . '" ';
346
347 if (isset($idxoptions['HASH']))
348 $s .= 'USING HASH ';
349
350 if ( isset($idxoptions[$this->upperName]) )
351 $s .= $idxoptions[$this->upperName];
352
353 if ( is_array($flds) )
354 $flds = implode('", "',$flds);
355 $s .= '("' . $flds . '")';
356 $sql[] = $s;
357
358 return $sql;
359 }
360
361 function _GetSize($ftype, $ty, $fsize, $fprec)
362 {
363 if (strlen($fsize) && $ty != 'X' && $ty != 'B' && $ty != 'I' && strpos($ftype,'(') === false) {
364 $ftype .= "(".$fsize;
365 if (strlen($fprec)) $ftype .= ",".$fprec;
366 $ftype .= ')';
367 }
368 return $ftype;
369 }
370 }
371 ?>