| [ Index ] |
|
Code source de eGroupWare 1.2.106-2 |
1 <?php 2 3 /** 4 V4.65 22 July 2005 (c) 2000-2005 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 25 function MetaType($t,$len=-1,$fieldobj=false) 26 { 27 if (is_object($t)) { 28 $fieldobj = $t; 29 $t = $fieldobj->type; 30 $len = $fieldobj->max_length; 31 } 32 $is_serial = is_object($fieldobj) && $fieldobj->primary_key && $fieldobj->unique && 33 $fieldobj->has_default && substr($fieldobj->default_value,0,8) == 'nextval('; 34 35 switch (strtoupper($t)) { 36 case 'INTERVAL': 37 case 'CHAR': 38 case 'CHARACTER': 39 case 'VARCHAR': 40 case 'NAME': 41 case 'BPCHAR': 42 if ($len <= $this->blobSize) return 'C'; 43 44 case 'TEXT': 45 return 'X'; 46 47 case 'IMAGE': // user defined type 48 case 'BLOB': // user defined type 49 case 'BIT': // This is a bit string, not a single bit, so don't return 'L' 50 case 'VARBIT': 51 case 'BYTEA': 52 return 'B'; 53 54 case 'BOOL': 55 case 'BOOLEAN': 56 return 'L'; 57 58 case 'DATE': 59 return 'D'; 60 61 case 'TIME': 62 case 'DATETIME': 63 case 'TIMESTAMP': 64 case 'TIMESTAMPTZ': 65 return 'T'; 66 67 case 'INTEGER': return !$is_serial ? 'I' : 'R'; 68 case 'SMALLINT': 69 case 'INT2': return !$is_serial ? 'I2' : 'R'; 70 case 'INT4': return !$is_serial ? 'I4' : 'R'; 71 case 'BIGINT': 72 case 'INT8': return !$is_serial ? 'I8' : 'R'; 73 74 case 'OID': 75 case 'SERIAL': 76 return 'R'; 77 78 case 'FLOAT4': 79 case 'FLOAT8': 80 case 'DOUBLE PRECISION': 81 case 'REAL': 82 return 'F'; 83 84 default: 85 return 'N'; 86 } 87 } 88 89 function ActualType($meta) 90 { 91 switch($meta) { 92 case 'C': return 'VARCHAR'; 93 case 'XL': 94 case 'X': return 'TEXT'; 95 96 case 'C2': return 'VARCHAR'; 97 case 'X2': return 'TEXT'; 98 99 case 'B': return 'BYTEA'; 100 101 case 'D': return 'DATE'; 102 case 'T': return 'TIMESTAMP'; 103 104 case 'L': return 'BOOLEAN'; 105 case 'I': return 'INTEGER'; 106 case 'I1': return 'SMALLINT'; 107 case 'I2': return 'INT2'; 108 case 'I4': return 'INT4'; 109 case 'I8': return 'INT8'; 110 111 case 'F': return 'FLOAT8'; 112 case 'N': return 'NUMERIC'; 113 default: 114 return $meta; 115 } 116 } 117 118 /** 119 * Adding a new Column 120 * 121 * reimplementation of the default function as postgres does NOT allow to set the default in the same statement 122 * 123 * @param string $tabname table-name 124 * @param string $flds column-names and types for the changed columns 125 * @return array with SQL strings 126 */ 127 function AddColumnSQL($tabname, $flds) 128 { 129 $tabname = $this->TableName ($tabname); 130 $sql = array(); 131 list($lines,$pkey) = $this->_GenFields($flds); 132 $alter = 'ALTER TABLE ' . $tabname . $this->addCol . ' '; 133 foreach($lines as $v) { 134 if (($not_null = preg_match('/NOT NULL/i',$v))) { 135 $v = preg_replace('/NOT NULL/i','',$v); 136 } 137 if (preg_match('/^([^ ]+) .*DEFAULT ([^ ]+)/',$v,$matches)) { 138 list(,$colname,$default) = $matches; 139 $sql[] = $alter . str_replace('DEFAULT '.$default,'',$v); 140 $sql[] = 'UPDATE '.$tabname.' SET '.$colname.'='.$default; 141 $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET DEFAULT ' . $default; 142 } else { 143 $sql[] = $alter . $v; 144 } 145 if ($not_null) { 146 list($colname) = explode(' ',$v); 147 $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET NOT NULL'; 148 } 149 } 150 return $sql; 151 } 152 153 /** 154 * Change the definition of one column 155 * 156 * Postgres can't do that on it's own, you need to supply the complete defintion of the new table, 157 * to allow, recreating the table and copying the content over to the new table 158 * @param string $tabname table-name 159 * @param string $flds column-name and type for the changed column 160 * @param string $tableflds complete defintion of the new table, eg. for postgres, default '' 161 * @param array/ $tableoptions options for the new table see CreateTableSQL, default '' 162 * @return array with SQL strings 163 */ 164 function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='') 165 { 166 if (!$tableflds) { 167 if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL"); 168 return array(); 169 } 170 return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions); 171 } 172 173 /** 174 * Drop one column 175 * 176 * Postgres < 7.3 can't do that on it's own, you need to supply the complete defintion of the new table, 177 * to allow, recreating the table and copying the content over to the new table 178 * @param string $tabname table-name 179 * @param string $flds column-name and type for the changed column 180 * @param string $tableflds complete defintion of the new table, eg. for postgres, default '' 181 * @param array/ $tableoptions options for the new table see CreateTableSQL, default '' 182 * @return array with SQL strings 183 */ 184 function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='') 185 { 186 $has_drop_column = 7.3 <= (float) @$this->serverInfo['version']; 187 if (!$has_drop_column && !$tableflds) { 188 if ($this->debug) ADOConnection::outp("DropColumnSQL needs complete table-definiton for PostgreSQL < 7.3"); 189 return array(); 190 } 191 if ($has_drop_column) { 192 return ADODB_DataDict::DropColumnSQL($tabname, $flds); 193 } 194 return $this->_recreate_copy_table($tabname,$flds,$tableflds,$tableoptions); 195 } 196 197 /** 198 * Save the content into a temp. table, drop and recreate the original table and copy the content back in 199 * 200 * We also take care to set the values of the sequenz and recreate the indexes. 201 * All this is done in a transaction, to not loose the content of the table, if something went wrong! 202 * @internal 203 * @param string $tabname table-name 204 * @param string $dropflds column-names to drop 205 * @param string $tableflds complete defintion of the new table, eg. for postgres 206 * @param array/string $tableoptions options for the new table see CreateTableSQL, default '' 207 * @return array with SQL strings 208 */ 209 function _recreate_copy_table($tabname,$dropflds,$tableflds,$tableoptions='') 210 { 211 if ($dropflds && !is_array($dropflds)) $dropflds = explode(',',$dropflds); 212 $copyflds = array(); 213 foreach($this->MetaColumns($tabname) as $fld) { 214 if (!$dropflds || !in_array($fld->name,$dropflds)) { 215 // we need to explicit convert varchar to a number to be able to do an AlterColumn of a char column to a nummeric one 216 if (preg_match('/'.$fld->name.' (I|I2|I4|I8|N|F)/i',$tableflds,$matches) && 217 in_array($fld->type,array('varchar','char','text','bytea'))) { 218 $copyflds[] = "to_number($fld->name,'S9999999999999D99')"; 219 } else { 220 $copyflds[] = $fld->name; 221 } 222 // identify the sequence name and the fld its on 223 if ($fld->primary_key && $fld->has_default && 224 preg_match("/nextval\('([^']+)'::text\)/",$fld->default_value,$matches)) { 225 $seq_name = $matches[1]; 226 $seq_fld = $fld->name; 227 } 228 } 229 } 230 $copyflds = implode(', ',$copyflds); 231 232 $tempname = $tabname.'_tmp'; 233 $aSql[] = 'BEGIN'; // we use a transaction, to make sure not to loose the content of the table 234 $aSql[] = "SELECT * INTO TEMPORARY TABLE $tempname FROM $tabname"; 235 $aSql = array_merge($aSql,$this->DropTableSQL($tabname)); 236 $aSql = array_merge($aSql,$this->CreateTableSQL($tabname,$tableflds,$tableoptions)); 237 $aSql[] = "INSERT INTO $tabname SELECT $copyflds FROM $tempname"; 238 if ($seq_name && $seq_fld) { // if we have a sequence we need to set it again 239 $seq_name = $tabname.'_'.$seq_fld.'_seq'; // has to be the name of the new implicit sequence 240 $aSql[] = "SELECT setval('$seq_name',MAX($seq_fld)) FROM $tabname"; 241 } 242 $aSql[] = "DROP TABLE $tempname"; 243 // recreate the indexes, if they not contain one of the droped columns 244 foreach($this->MetaIndexes($tabname) as $idx_name => $idx_data) 245 { 246 if (substr($idx_name,-5) != '_pkey' && (!$dropflds || !count(array_intersect($dropflds,$idx_data['columns'])))) { 247 $aSql = array_merge($aSql,$this->CreateIndexSQL($idx_name,$tabname,$idx_data['columns'], 248 $idx_data['unique'] ? array('UNIQUE') : False)); 249 } 250 } 251 $aSql[] = 'COMMIT'; 252 return $aSql; 253 } 254 255 function DropTableSQL($tabname) 256 { 257 $sql = ADODB_DataDict::DropTableSQL($tabname); 258 259 $drop_seq = $this->_DropAutoIncrement($tabname); 260 if ($drop_seq) $sql[] = $drop_seq; 261 262 return $sql; 263 } 264 265 // return string must begin with space 266 function _CreateSuffix($fname, &$ftype, $fnotnull,$fdefault,$fautoinc,$fconstraint) 267 { 268 if ($fautoinc) { 269 $ftype = 'SERIAL'; 270 return ''; 271 } 272 $suffix = ''; 273 if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault"; 274 if ($fnotnull) $suffix .= ' NOT NULL'; 275 if ($fconstraint) $suffix .= ' '.$fconstraint; 276 return $suffix; 277 } 278 279 // search for a sequece for the given table (asumes the seqence-name contains the table-name!) 280 // if yes return sql to drop it 281 // this is still necessary if postgres < 7.3 or the SERIAL was created on an earlier version!!! 282 function _DropAutoIncrement($tabname) 283 { 284 $tabname = $this->connection->quote('%'.$tabname.'%'); 285 286 $seq = $this->connection->GetOne("SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relname LIKE $tabname AND relkind='S'"); 287 288 // check if a tables depends on the sequenz and it therefor cant and dont need to be droped separatly 289 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'")) { 290 return False; 291 } 292 return "DROP SEQUENCE ".$seq; 293 } 294 295 /* 296 CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( 297 { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ] 298 | table_constraint } [, ... ] 299 ) 300 [ INHERITS ( parent_table [, ... ] ) ] 301 [ WITH OIDS | WITHOUT OIDS ] 302 where column_constraint is: 303 [ CONSTRAINT constraint_name ] 304 { NOT NULL | NULL | UNIQUE | PRIMARY KEY | 305 CHECK (expression) | 306 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ] 307 [ ON DELETE action ] [ ON UPDATE action ] } 308 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] 309 and table_constraint is: 310 [ CONSTRAINT constraint_name ] 311 { UNIQUE ( column_name [, ... ] ) | 312 PRIMARY KEY ( column_name [, ... ] ) | 313 CHECK ( expression ) | 314 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] 315 [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] } 316 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] 317 */ 318 319 320 /* 321 CREATE [ UNIQUE ] INDEX index_name ON table 322 [ USING acc_method ] ( column [ ops_name ] [, ...] ) 323 [ WHERE predicate ] 324 CREATE [ UNIQUE ] INDEX index_name ON table 325 [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] ) 326 [ WHERE predicate ] 327 */ 328 function _IndexSQL($idxname, $tabname, $flds, $idxoptions) 329 { 330 $sql = array(); 331 332 if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) { 333 $sql[] = sprintf ($this->dropIndex, $idxname, $tabname); 334 if ( isset($idxoptions['DROP']) ) 335 return $sql; 336 } 337 338 if ( empty ($flds) ) { 339 return $sql; 340 } 341 342 $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : ''; 343 344 $s = 'CREATE' . $unique . ' INDEX ' . $idxname . ' ON ' . $tabname . ' '; 345 346 if (isset($idxoptions['HASH'])) 347 $s .= 'USING HASH '; 348 349 if ( isset($idxoptions[$this->upperName]) ) 350 $s .= $idxoptions[$this->upperName]; 351 352 if ( is_array($flds) ) 353 $flds = implode(', ',$flds); 354 $s .= '(' . $flds . ')'; 355 $sql[] = $s; 356 357 return $sql; 358 } 359 } 360 ?>
titre
Description
Corps
titre
Description
Corps
titre
Description
Corps
titre
Corps
| Généré le : Sun Feb 25 17:20:01 2007 | par Balluche grâce à PHPXref 0.7 |