| [ Index ] |
PHP Cross Reference of moodle-2.8 |
[Summary view] [Print] [Text view]
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 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Fri Nov 28 20:29:05 2014 | Cross-referenced by PHPXref 0.7.1 |