| [ Index ] |
|
Code source de vtiger CRM 5.0.2 |
1 <?php 2 3 /** 4 V4.90 8 June 2006 (c) 2000-2006 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 * Change the definition of one column 156 * 157 * Postgres can't do that on it's own, you need to supply the complete defintion of the new table, 158 * to allow, recreating the table and copying the content over to the new table 159 * @param string $tabname table-name 160 * @param string $flds column-name and type for the changed column 161 * @param string $tableflds complete defintion of the new table, eg. for postgres, default '' 162 * @param array/ $tableoptions options for the new table see CreateTableSQL, default '' 163 * @return array with SQL strings 164 */ 165 function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='') 166 { 167 if (!$tableflds) { 168 if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL"); 169 return array(); 170 } 171 return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions); 172 } 173 174 /** 175 * Drop one column 176 * 177 * Postgres < 7.3 can't do that on it's own, you need to supply the complete defintion of the new table, 178 * to allow, recreating the table and copying the content over to the new table 179 * @param string $tabname table-name 180 * @param string $flds column-name and type for the changed column 181 * @param string $tableflds complete defintion of the new table, eg. for postgres, default '' 182 * @param array/ $tableoptions options for the new table see CreateTableSQL, default '' 183 * @return array with SQL strings 184 */ 185 function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='') 186 { 187 $has_drop_column = 7.3 <= (float) @$this->serverInfo['version']; 188 if (!$has_drop_column && !$tableflds) { 189 if ($this->debug) ADOConnection::outp("DropColumnSQL needs complete table-definiton for PostgreSQL < 7.3"); 190 return array(); 191 } 192 if ($has_drop_column) { 193 return ADODB_DataDict::DropColumnSQL($tabname, $flds); 194 } 195 return $this->_recreate_copy_table($tabname,$flds,$tableflds,$tableoptions); 196 } 197 198 /** 199 * Save the content into a temp. table, drop and recreate the original table and copy the content back in 200 * 201 * We also take care to set the values of the sequenz and recreate the indexes. 202 * All this is done in a transaction, to not loose the content of the table, if something went wrong! 203 * @internal 204 * @param string $tabname table-name 205 * @param string $dropflds column-names to drop 206 * @param string $tableflds complete defintion of the new table, eg. for postgres 207 * @param array/string $tableoptions options for the new table see CreateTableSQL, default '' 208 * @return array with SQL strings 209 */ 210 function _recreate_copy_table($tabname,$dropflds,$tableflds,$tableoptions='') 211 { 212 if ($dropflds && !is_array($dropflds)) $dropflds = explode(',',$dropflds); 213 $copyflds = array(); 214 foreach($this->MetaColumns($tabname) as $fld) { 215 if (!$dropflds || !in_array($fld->name,$dropflds)) { 216 // we need to explicit convert varchar to a number to be able to do an AlterColumn of a char column to a nummeric one 217 if (preg_match('/'.$fld->name.' (I|I2|I4|I8|N|F)/i',$tableflds,$matches) && 218 in_array($fld->type,array('varchar','char','text','bytea'))) { 219 $copyflds[] = "to_number($fld->name,'S9999999999999D99')"; 220 } else { 221 $copyflds[] = $fld->name; 222 } 223 // identify the sequence name and the fld its on 224 if ($fld->primary_key && $fld->has_default && 225 preg_match("/nextval\('([^']+)'::text\)/",$fld->default_value,$matches)) { 226 $seq_name = $matches[1]; 227 $seq_fld = $fld->name; 228 } 229 } 230 } 231 $copyflds = implode(', ',$copyflds); 232 233 $tempname = $tabname.'_tmp'; 234 $aSql[] = 'BEGIN'; // we use a transaction, to make sure not to loose the content of the table 235 $aSql[] = "SELECT * INTO TEMPORARY TABLE $tempname FROM $tabname"; 236 $aSql = array_merge($aSql,$this->DropTableSQL($tabname)); 237 $aSql = array_merge($aSql,$this->CreateTableSQL($tabname,$tableflds,$tableoptions)); 238 $aSql[] = "INSERT INTO $tabname SELECT $copyflds FROM $tempname"; 239 if ($seq_name && $seq_fld) { // if we have a sequence we need to set it again 240 $seq_name = $tabname.'_'.$seq_fld.'_seq'; // has to be the name of the new implicit sequence 241 $aSql[] = "SELECT setval('$seq_name',MAX($seq_fld)) FROM $tabname"; 242 } 243 $aSql[] = "DROP TABLE $tempname"; 244 // recreate the indexes, if they not contain one of the droped columns 245 foreach($this->MetaIndexes($tabname) as $idx_name => $idx_data) 246 { 247 if (substr($idx_name,-5) != '_pkey' && (!$dropflds || !count(array_intersect($dropflds,$idx_data['columns'])))) { 248 $aSql = array_merge($aSql,$this->CreateIndexSQL($idx_name,$tabname,$idx_data['columns'], 249 $idx_data['unique'] ? array('UNIQUE') : False)); 250 } 251 } 252 $aSql[] = 'COMMIT'; 253 return $aSql; 254 } 255 256 function DropTableSQL($tabname) 257 { 258 $sql = ADODB_DataDict::DropTableSQL($tabname); 259 260 $drop_seq = $this->_DropAutoIncrement($tabname); 261 if ($drop_seq) $sql[] = $drop_seq; 262 263 return $sql; 264 } 265 266 // return string must begin with space 267 function _CreateSuffix($fname, &$ftype, $fnotnull,$fdefault,$fautoinc,$fconstraint) 268 { 269 if ($fautoinc) { 270 $ftype = 'SERIAL'; 271 return ''; 272 } 273 $suffix = ''; 274 if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault"; 275 if ($fnotnull) $suffix .= ' NOT NULL'; 276 if ($fconstraint) $suffix .= ' '.$fconstraint; 277 return $suffix; 278 } 279 280 // search for a sequece for the given table (asumes the seqence-name contains the table-name!) 281 // if yes return sql to drop it 282 // this is still necessary if postgres < 7.3 or the SERIAL was created on an earlier version!!! 283 function _DropAutoIncrement($tabname) 284 { 285 $tabname = $this->connection->quote('%'.$tabname.'%'); 286 287 $seq = $this->connection->GetOne("SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relname LIKE $tabname AND relkind='S'"); 288 289 // check if a tables depends on the sequenz and it therefor cant and dont need to be droped separatly 290 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'")) { 291 return False; 292 } 293 return "DROP SEQUENCE ".$seq; 294 } 295 296 /* 297 CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( 298 { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ] 299 | table_constraint } [, ... ] 300 ) 301 [ INHERITS ( parent_table [, ... ] ) ] 302 [ WITH OIDS | WITHOUT OIDS ] 303 where column_constraint is: 304 [ CONSTRAINT constraint_name ] 305 { NOT NULL | NULL | UNIQUE | PRIMARY KEY | 306 CHECK (expression) | 307 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ] 308 [ ON DELETE action ] [ ON UPDATE action ] } 309 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] 310 and table_constraint is: 311 [ CONSTRAINT constraint_name ] 312 { UNIQUE ( column_name [, ... ] ) | 313 PRIMARY KEY ( column_name [, ... ] ) | 314 CHECK ( expression ) | 315 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] 316 [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] } 317 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] 318 */ 319 320 321 /* 322 CREATE [ UNIQUE ] INDEX index_name ON table 323 [ USING acc_method ] ( column [ ops_name ] [, ...] ) 324 [ WHERE predicate ] 325 CREATE [ UNIQUE ] INDEX index_name ON table 326 [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] ) 327 [ WHERE predicate ] 328 */ 329 function _IndexSQL($idxname, $tabname, $flds, $idxoptions) 330 { 331 $sql = array(); 332 333 if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) { 334 $sql[] = sprintf ($this->dropIndex, $idxname, $tabname); 335 if ( isset($idxoptions['DROP']) ) 336 return $sql; 337 } 338 339 if ( empty ($flds) ) { 340 return $sql; 341 } 342 343 $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : ''; 344 345 $s = 'CREATE' . $unique . ' INDEX ' . $idxname . ' ON ' . $tabname . ' '; 346 347 if (isset($idxoptions['HASH'])) 348 $s .= 'USING HASH '; 349 350 if ( isset($idxoptions[$this->upperName]) ) 351 $s .= $idxoptions[$this->upperName]; 352 353 if ( is_array($flds) ) 354 $flds = implode(', ',$flds); 355 $s .= '(' . $flds . ')'; 356 $sql[] = $s; 357 358 return $sql; 359 } 360 361 function _GetSize($ftype, $ty, $fsize, $fprec) 362 { 363 if (strlen($fsize) && $ty != 'X' && $ty != 'B' && $ty != 'I' && strpos($ftype,'(') === false) { 364 $ftype .= "(".$fsize; 365 if (strlen($fprec)) $ftype .= ",".$fprec; 366 $ftype .= ')'; 367 } 368 return $ftype; 369 } 370 } 371 ?>
titre
Description
Corps
titre
Description
Corps
titre
Description
Corps
titre
Corps
| Généré le : Sun Feb 25 10:22:19 2007 | par Balluche grâce à PHPXref 0.7 |