TYPO3 CMS  TYPO3_6-2
•All Classes Namespaces Files Functions Variables Pages
datadict-postgres.inc.php
Go to the documentation of this file.
1 <?php
2 
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 
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 
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 
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 
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 }
ADODB2_postgres\ActualType
ActualType($meta)
Definition: datadict-postgres.inc.php:90
ADODB_DataDict\_GenFields
_GenFields($flds, $widespacing=false)
Definition: adodb-datadict.inc.php:595
ADODB_DataDict\MetaColumns
MetaColumns($tab, $upper=true, $schema=false)
Definition: adodb-datadict.inc.php:200
ADODB2_postgres\$dropTable
$dropTable
Definition: datadict-postgres.inc.php:24
ADODB2_postgres\DropIndexSQL
DropIndexSQL($idxname, $tabname=NULL)
Definition: datadict-postgres.inc.php:157
$sql
$sql
Definition: server.php:82
ADODB_DataDict\MetaIndexes
MetaIndexes($table, $primary=false, $owner=false)
Definition: adodb-datadict.inc.php:212
ADODB2_postgres\$seqField
$seqField
Definition: datadict-postgres.inc.php:19
ADODB2_postgres
Definition: datadict-postgres.inc.php:16
ADODB2_postgres\MetaType
MetaType($t, $len=-1, $fieldobj=false)
Definition: datadict-postgres.inc.php:26
ADODB_DataDict\CreateIndexSQL
CreateIndexSQL($idxname, $tabname, $flds, $idxoptions=false)
Definition: adodb-datadict.inc.php:428
ADODB_DataDict\DropColumnSQL
DropColumnSQL($tabname, $flds, $tableflds='', $tableoptions='')
Definition: adodb-datadict.inc.php:538
ADODB_DataDict\TableName
TableName($name)
Definition: adodb-datadict.inc.php:361
die
die
Definition: index.php:6
ADODB2_postgres\_DropAutoIncrement
_DropAutoIncrement($tabname)
Definition: datadict-postgres.inc.php:381
ADODB2_postgres\_recreate_copy_table
_recreate_copy_table($tabname, $dropflds, $tableflds, $tableoptions='')
Definition: datadict-postgres.inc.php:308
ADODB2_postgres\AddColumnSQL
AddColumnSQL($tabname, $flds)
Definition: datadict-postgres.inc.php:129
ADODB2_postgres\$seqPrefix
$seqPrefix
Definition: datadict-postgres.inc.php:20
ADODB2_postgres\$addCol
$addCol
Definition: datadict-postgres.inc.php:21
ADODB2_postgres\_GetSize
_GetSize($ftype, $ty, $fsize, $fprec)
Definition: datadict-postgres.inc.php:473
ADODB_DataDict
Definition: adodb-datadict.inc.php:164
ADODB2_postgres\AlterColumnSQL
AlterColumnSQL($tabname, $flds, $tableflds='', $tableoptions='')
Definition: datadict-postgres.inc.php:183
ADODB2_postgres\DropColumnSQL
DropColumnSQL($tabname, $flds, $tableflds='', $tableoptions='')
Definition: datadict-postgres.inc.php:283
ADODB_DataDict\CreateTableSQL
CreateTableSQL($tabname, $flds, $tableoptions=array())
Definition: adodb-datadict.inc.php:563
ADODB2_postgres\$quote
$quote
Definition: datadict-postgres.inc.php:22
ADODB2_postgres\RenameTableSQL
RenameTableSQL($tabname, $newname)
Definition: datadict-postgres.inc.php:394
ADODB2_postgres\$databaseType
$databaseType
Definition: datadict-postgres.inc.php:18
ADODB2_postgres\_IndexSQL
_IndexSQL($idxname, $tabname, $flds, $idxoptions)
Definition: datadict-postgres.inc.php:441
debug
debug($variable='', $name=' *variable *', $line=' *line *', $file=' *file *', $recursiveDepth=3, $debugLevel=E_DEBUG)
Definition: GlobalDebugFunctions.php:13
ADODB2_postgres\DropTableSQL
DropTableSQL($tabname)
Definition: datadict-postgres.inc.php:354
ADODB2_postgres\$renameTable
$renameTable
Definition: datadict-postgres.inc.php:23
ADODB_DataDict\$schema
$schema
Definition: adodb-datadict.inc.php:176
ADODB_DataDict\DropTableSQL
DropTableSQL($tabname)
Definition: adodb-datadict.inc.php:550
ADODB2_postgres\_CreateSuffix
_CreateSuffix($fname, &$ftype, $fnotnull, $fdefault, $fautoinc, $fconstraint, $funsigned)
Definition: datadict-postgres.inc.php:365