C:/lib/adodb/datadict/datadict-postgres.inc.php Quellcode

datadict-postgres.inc.php
gehe zur Dokumentation dieser Datei
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 }
483 ?>




Korrekturen, Hinweise und Ergänzungen

Bitte scheuen Sie sich nicht und melden Sie, was auf dieser Seite sachlich falsch oder irreführend ist, was ergänzt werden sollte, was fehlt usw. Dazu bitte oben aus dem Menü Seite den Eintrag Support Forum wählen. Es ist eine kostenlose Anmeldung erforderlich, um Anmerkungen zu posten. Unpassende Postings, Spam usw. werden kommentarlos entfernt.
Erzeugt am Mon Feb 3 2014 14:29:52 für MyOOS [Die Shop-Lösung] von   doxygen 1.8.4