| 1 | <?php
|
|---|
| 2 |
|
|---|
| 3 | /**
|
|---|
| 4 | V4.94 23 Jan 2007 (c) 2000-2007 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 | ?> |
|---|
