| [ Index ] |
PHP Cross Reference of Moodle 1.9.3 [Build 15-Oct-2008] |
[Summary view] [Print] [Text view]
1 <?php 2 3 /** 4 V4.98 13 Feb 2008 (c) 2000-2008 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 function DropIndexSQL ($idxname, $tabname = NULL) 156 { 157 return array(sprintf($this->dropIndex, $this->TableName($idxname), $this->TableName($tabname))); 158 } 159 160 /** 161 * Change the definition of one column 162 * 163 * Postgres can't do that on it's own, you need to supply the complete defintion of the new table, 164 * to allow, recreating the table and copying the content over to the new table 165 * @param string $tabname table-name 166 * @param string $flds column-name and type for the changed column 167 * @param string $tableflds complete defintion of the new table, eg. for postgres, default '' 168 * @param array/ $tableoptions options for the new table see CreateTableSQL, default '' 169 * @return array with SQL strings 170 */ 171 /*function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='') 172 { 173 if (!$tableflds) { 174 if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL"); 175 return array(); 176 } 177 return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions); 178 }*/ 179 180 function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='') 181 { 182 // Check if alter single column datatype available - works with 8.0+ 183 $has_alter_column = 8.0 <= (float) @$this->serverInfo['version']; 184 185 if ($has_alter_column) { 186 $tabname = $this->TableName($tabname); 187 $sql = array(); 188 list($lines,$pkey) = $this->_GenFields($flds); 189 $alter = 'ALTER TABLE ' . $tabname . $this->alterCol . ' '; 190 foreach($lines as $v) { 191 if ($not_null = preg_match('/NOT NULL/i',$v)) { 192 $v = preg_replace('/NOT NULL/i','',$v); 193 } 194 // this next block doesn't work - there is no way that I can see to 195 // explicitly ask a column to be null using $flds 196 else if ($set_null = preg_match('/NULL/i',$v)) { 197 // if they didn't specify not null, see if they explicitely asked for null 198 $v = preg_replace('/\sNULL/i','',$v); 199 } 200 201 if (preg_match('/^([^ ]+) .*DEFAULT ([^ ]+)/',$v,$matches)) { 202 list(,$colname,$default) = $matches; 203 $v = preg_replace('/^' . preg_quote($colname) . '\s/', '', $v); 204 $sql[] = $alter . $colname . ' TYPE ' . str_replace('DEFAULT '.$default,'',$v); 205 $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET DEFAULT ' . $default; 206 } 207 else { 208 // drop default? 209 preg_match ('/^\s*(\S+)\s+(.*)$/',$v,$matches); 210 list (,$colname,$rest) = $matches; 211 $sql[] = $alter . $colname . ' TYPE ' . $rest; 212 } 213 214 list($colname) = explode(' ',$v); 215 if ($not_null) { 216 // this does not error out if the column is already not null 217 $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET NOT NULL'; 218 } 219 if ($set_null) { 220 // this does not error out if the column is already null 221 $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' DROP NOT NULL'; 222 } 223 } 224 return $sql; 225 } 226 227 // does not have alter column 228 if (!$tableflds) { 229 if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL"); 230 return array(); 231 } 232 return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions); 233 } 234 235 /** 236 * Drop one column 237 * 238 * Postgres < 7.3 can't do that on it's own, you need to supply the complete defintion of the new table, 239 * to allow, recreating the table and copying the content over to the new table 240 * @param string $tabname table-name 241 * @param string $flds column-name and type for the changed column 242 * @param string $tableflds complete defintion of the new table, eg. for postgres, default '' 243 * @param array/ $tableoptions options for the new table see CreateTableSQL, default '' 244 * @return array with SQL strings 245 */ 246 function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='') 247 { 248 $has_drop_column = 7.3 <= (float) @$this->serverInfo['version']; 249 if (!$has_drop_column && !$tableflds) { 250 if ($this->debug) ADOConnection::outp("DropColumnSQL needs complete table-definiton for PostgreSQL < 7.3"); 251 return array(); 252 } 253 if ($has_drop_column) { 254 return ADODB_DataDict::DropColumnSQL($tabname, $flds); 255 } 256 return $this->_recreate_copy_table($tabname,$flds,$tableflds,$tableoptions); 257 } 258 259 /** 260 * Save the content into a temp. table, drop and recreate the original table and copy the content back in 261 * 262 * We also take care to set the values of the sequenz and recreate the indexes. 263 * All this is done in a transaction, to not loose the content of the table, if something went wrong! 264 * @internal 265 * @param string $tabname table-name 266 * @param string $dropflds column-names to drop 267 * @param string $tableflds complete defintion of the new table, eg. for postgres 268 * @param array/string $tableoptions options for the new table see CreateTableSQL, default '' 269 * @return array with SQL strings 270 */ 271 function _recreate_copy_table($tabname,$dropflds,$tableflds,$tableoptions='') 272 { 273 if ($dropflds && !is_array($dropflds)) $dropflds = explode(',',$dropflds); 274 $copyflds = array(); 275 foreach($this->MetaColumns($tabname) as $fld) { 276 if (!$dropflds || !in_array($fld->name,$dropflds)) { 277 // we need to explicit convert varchar to a number to be able to do an AlterColumn of a char column to a nummeric one 278 if (preg_match('/'.$fld->name.' (I|I2|I4|I8|N|F)/i',$tableflds,$matches) && 279 in_array($fld->type,array('varchar','char','text','bytea'))) { 280 $copyflds[] = "to_number($fld->name,'S9999999999999D99')"; 281 } else { 282 $copyflds[] = $fld->name; 283 } 284 // identify the sequence name and the fld its on 285 if ($fld->primary_key && $fld->has_default && 286 preg_match("/nextval\('([^']+)'::text\)/",$fld->default_value,$matches)) { 287 $seq_name = $matches[1]; 288 $seq_fld = $fld->name; 289 } 290 } 291 } 292 $copyflds = implode(', ',$copyflds); 293 294 $tempname = $tabname.'_tmp'; 295 $aSql[] = 'BEGIN'; // we use a transaction, to make sure not to loose the content of the table 296 $aSql[] = "SELECT * INTO TEMPORARY TABLE $tempname FROM $tabname"; 297 $aSql = array_merge($aSql,$this->DropTableSQL($tabname)); 298 $aSql = array_merge($aSql,$this->CreateTableSQL($tabname,$tableflds,$tableoptions)); 299 $aSql[] = "INSERT INTO $tabname SELECT $copyflds FROM $tempname"; 300 if ($seq_name && $seq_fld) { // if we have a sequence we need to set it again 301 $seq_name = $tabname.'_'.$seq_fld.'_seq'; // has to be the name of the new implicit sequence 302 $aSql[] = "SELECT setval('$seq_name',MAX($seq_fld)) FROM $tabname"; 303 } 304 $aSql[] = "DROP TABLE $tempname"; 305 // recreate the indexes, if they not contain one of the droped columns 306 foreach($this->MetaIndexes($tabname) as $idx_name => $idx_data) 307 { 308 if (substr($idx_name,-5) != '_pkey' && (!$dropflds || !count(array_intersect($dropflds,$idx_data['columns'])))) { 309 $aSql = array_merge($aSql,$this->CreateIndexSQL($idx_name,$tabname,$idx_data['columns'], 310 $idx_data['unique'] ? array('UNIQUE') : False)); 311 } 312 } 313 $aSql[] = 'COMMIT'; 314 return $aSql; 315 } 316 317 function DropTableSQL($tabname) 318 { 319 $sql = ADODB_DataDict::DropTableSQL($tabname); 320 321 $drop_seq = $this->_DropAutoIncrement($tabname); 322 if ($drop_seq) $sql[] = $drop_seq; 323 324 return $sql; 325 } 326 327 // return string must begin with space 328 function _CreateSuffix($fname, &$ftype, $fnotnull,$fdefault,$fautoinc,$fconstraint) 329 { 330 if ($fautoinc) { 331 $ftype = 'SERIAL'; 332 return ''; 333 } 334 $suffix = ''; 335 if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault"; 336 if ($fnotnull) $suffix .= ' NOT NULL'; 337 if ($fconstraint) $suffix .= ' '.$fconstraint; 338 return $suffix; 339 } 340 341 // search for a sequece for the given table (asumes the seqence-name contains the table-name!) 342 // if yes return sql to drop it 343 // this is still necessary if postgres < 7.3 or the SERIAL was created on an earlier version!!! 344 function _DropAutoIncrement($tabname) 345 { 346 $tabname = $this->connection->quote('%'.$tabname.'%'); 347 348 $seq = $this->connection->GetOne("SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relname LIKE $tabname AND relkind='S'"); 349 350 // check if a tables depends on the sequenz and it therefor cant and dont need to be droped separatly 351 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'")) { 352 return False; 353 } 354 return "DROP SEQUENCE ".$seq; 355 } 356 357 function RenameTableSQL($tabname,$newname) 358 { 359 if (!empty($this->schema)) { 360 $rename_from = $this->TableName($tabname); 361 $schema_save = $this->schema; 362 $this->schema = false; 363 $rename_to = $this->TableName($newname); 364 $this->schema = $schema_save; 365 return array (sprintf($this->renameTable, $rename_from, $rename_to)); 366 } 367 368 return array (sprintf($this->renameTable, $this->TableName($tabname),$this->TableName($newname))); 369 } 370 371 /* 372 CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( 373 { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ] 374 | table_constraint } [, ... ] 375 ) 376 [ INHERITS ( parent_table [, ... ] ) ] 377 [ WITH OIDS | WITHOUT OIDS ] 378 where column_constraint is: 379 [ CONSTRAINT constraint_name ] 380 { NOT NULL | NULL | UNIQUE | PRIMARY KEY | 381 CHECK (expression) | 382 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ] 383 [ ON DELETE action ] [ ON UPDATE action ] } 384 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] 385 and table_constraint is: 386 [ CONSTRAINT constraint_name ] 387 { UNIQUE ( column_name [, ... ] ) | 388 PRIMARY KEY ( column_name [, ... ] ) | 389 CHECK ( expression ) | 390 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] 391 [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] } 392 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] 393 */ 394 395 396 /* 397 CREATE [ UNIQUE ] INDEX index_name ON table 398 [ USING acc_method ] ( column [ ops_name ] [, ...] ) 399 [ WHERE predicate ] 400 CREATE [ UNIQUE ] INDEX index_name ON table 401 [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] ) 402 [ WHERE predicate ] 403 */ 404 function _IndexSQL($idxname, $tabname, $flds, $idxoptions) 405 { 406 $sql = array(); 407 408 if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) { 409 $sql[] = sprintf ($this->dropIndex, $idxname, $tabname); 410 if ( isset($idxoptions['DROP']) ) 411 return $sql; 412 } 413 414 if ( empty ($flds) ) { 415 return $sql; 416 } 417 418 $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : ''; 419 420 $s = 'CREATE' . $unique . ' INDEX ' . $idxname . ' ON ' . $tabname . ' '; 421 422 if (isset($idxoptions['HASH'])) 423 $s .= 'USING HASH '; 424 425 if ( isset($idxoptions[$this->upperName]) ) 426 $s .= $idxoptions[$this->upperName]; 427 428 if ( is_array($flds) ) 429 $flds = implode(', ',$flds); 430 $s .= '(' . $flds . ')'; 431 $sql[] = $s; 432 433 return $sql; 434 } 435 436 function _GetSize($ftype, $ty, $fsize, $fprec) 437 { 438 if (strlen($fsize) && $ty != 'X' && $ty != 'B' && $ty != 'I' && strpos($ftype,'(') === false) { 439 $ftype .= "(".$fsize; 440 if (strlen($fprec)) $ftype .= ",".$fprec; 441 $ftype .= ')'; 442 } 443 return $ftype; 444 } 445 } 446 ?>
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Wed Jan 14 11:33:29 2009 | Cross-referenced by PHPXref 0.7 |