Changeset 34 for trunk/phpgwapi/inc/adodb/datadict
- Timestamp:
- 06/29/07 15:17:46 (17 years ago)
- Location:
- trunk/phpgwapi/inc/adodb/datadict
- Files:
-
- 1 added
- 11 edited
-
datadict-access.inc.php (modified) (1 diff)
-
datadict-db2.inc.php (modified) (5 diffs)
-
datadict-firebird.inc.php (modified) (3 diffs)
-
datadict-generic.inc.php (modified) (1 diff)
-
datadict-ibase.inc.php (modified) (1 diff)
-
datadict-informix.inc.php (modified) (2 diffs)
-
datadict-mssql.inc.php (modified) (9 diffs)
-
datadict-mysql.inc.php (modified) (6 diffs)
-
datadict-oci8.inc.php (modified) (7 diffs)
-
datadict-postgres.inc.php (modified) (8 diffs)
-
datadict-sapdb.inc.php (added)
-
datadict-sybase.inc.php (modified) (3 diffs)
Legend:
- Unmodified
- Added
- Removed
-
trunk/phpgwapi/inc/adodb/datadict/datadict-access.inc.php
r2 r34 2 2 3 3 /** 4 V4. 51 29 July 2004 (c) 2000-2004 John Lim (jlim@natsoft.com.my). All rights reserved.4 V4.94 23 Jan 2007 (c) 2000-2007 John Lim (jlim#natsoft.com.my). All rights reserved. 5 5 Released under both BSD license and Lesser GPL library license. 6 6 Whenever there is any discrepancy between the two licenses, -
trunk/phpgwapi/inc/adodb/datadict/datadict-db2.inc.php
r2 r34 2 2 3 3 /** 4 V4. 51 29 July 2004 (c) 2000-2004 John Lim (jlim@natsoft.com.my). All rights reserved.4 V4.94 23 Jan 2007 (c) 2000-2007 John Lim (jlim#natsoft.com.my). All rights reserved. 5 5 Released under both BSD license and Lesser GPL library license. 6 6 Whenever there is any discrepancy between the two licenses, … … 17 17 var $databaseType = 'db2'; 18 18 var $seqField = false; 19 19 20 20 function ActualType($meta) 21 21 { … … 24 24 case 'XL': return 'CLOB'; 25 25 case 'X': return 'VARCHAR(3600)'; 26 26 27 27 case 'C2': return 'VARCHAR'; // up to 32K 28 28 case 'X2': return 'VARCHAR(3600)'; // up to 32000, but default page size too small 29 29 30 30 case 'B': return 'BLOB'; 31 31 32 32 case 'D': return 'DATE'; 33 33 case 'T': return 'TIMESTAMP'; 34 34 35 35 case 'L': return 'SMALLINT'; 36 36 case 'I': return 'INTEGER'; … … 39 39 case 'I4': return 'INTEGER'; 40 40 case 'I8': return 'BIGINT'; 41 41 42 42 case 'F': return 'DOUBLE'; 43 43 case 'N': return 'DECIMAL'; … … 71 71 } 72 72 73 74 function ChangeTableSQL($tablename, $flds, $tableoptions = false) 75 { 76 77 /** 78 Allow basic table changes to DB2 databases 79 DB2 will fatally reject changes to non character columns 80 81 */ 82 83 $validTypes = array("CHAR","VARC"); 84 $invalidTypes = array("BIGI","BLOB","CLOB","DATE", "DECI","DOUB", "INTE", "REAL","SMAL", "TIME"); 85 // check table exists 86 $cols = &$this->MetaColumns($tablename); 87 if ( empty($cols)) { 88 return $this->CreateTableSQL($tablename, $flds, $tableoptions); 89 } 90 91 // already exists, alter table instead 92 list($lines,$pkey) = $this->_GenFields($flds); 93 $alter = 'ALTER TABLE ' . $this->TableName($tablename); 94 $sql = array(); 95 96 foreach ( $lines as $id => $v ) { 97 if ( isset($cols[$id]) && is_object($cols[$id]) ) { 98 /** 99 If the first field of $v is the fieldname, and 100 the second is the field type/size, we assume its an 101 attempt to modify the column size, so check that it is allowed 102 $v can have an indeterminate number of blanks between the 103 fields, so account for that too 104 */ 105 $vargs = explode(' ' , $v); 106 // assume that $vargs[0] is the field name. 107 $i=0; 108 // Find the next non-blank value; 109 for ($i=1;$i<sizeof($vargs);$i++) 110 if ($vargs[$i] != '') 111 break; 112 113 // if $vargs[$i] is one of the following, we are trying to change the 114 // size of the field, if not allowed, simply ignore the request. 115 if (in_array(substr($vargs[$i],0,4),$invalidTypes)) 116 continue; 117 // insert the appropriate DB2 syntax 118 if (in_array(substr($vargs[$i],0,4),$validTypes)) { 119 array_splice($vargs,$i,0,array('SET','DATA','TYPE')); 120 } 121 122 // Now Look for the NOT NULL statement as this is not allowed in 123 // the ALTER table statement. If it is in there, remove it 124 if (in_array('NOT',$vargs) && in_array('NULL',$vargs)) { 125 for ($i=1;$i<sizeof($vargs);$i++) 126 if ($vargs[$i] == 'NOT') 127 break; 128 array_splice($vargs,$i,2,''); 129 } 130 $v = implode(' ',$vargs); 131 $sql[] = $alter . $this->alterCol . ' ' . $v; 132 } else { 133 $sql[] = $alter . $this->addCol . ' ' . $v; 134 } 135 } 136 137 return $sql; 138 } 139 73 140 } 74 141 -
trunk/phpgwapi/inc/adodb/datadict/datadict-firebird.inc.php
r2 r34 2 2 3 3 /** 4 V4. 51 29 July 2004 (c) 2000-2004 John Lim (jlim@natsoft.com.my). All rights reserved.4 V4.94 23 Jan 2007 (c) 2000-2007 John Lim (jlim#natsoft.com.my). All rights reserved. 5 5 Released under both BSD license and Lesser GPL library license. 6 6 Whenever there is any discrepancy between the two licenses, … … 11 11 */ 12 12 13 class ADODB2_firebird 15extends ADODB_DataDict {13 class ADODB2_firebird extends ADODB_DataDict { 14 14 15 var $databaseType = 'firebird 15';15 var $databaseType = 'firebird'; 16 16 var $seqField = false; 17 17 var $seqPrefix = 'gen_'; … … 22 22 switch($meta) { 23 23 case 'C': return 'VARCHAR'; 24 case 'XL': 24 case 'XL': return 'VARCHAR(32000)'; 25 25 case 'X': return 'VARCHAR(4000)'; 26 26 -
trunk/phpgwapi/inc/adodb/datadict/datadict-generic.inc.php
r2 r34 2 2 3 3 /** 4 V4. 51 29 July 2004 (c) 2000-2004 John Lim (jlim@natsoft.com.my). All rights reserved.4 V4.94 23 Jan 2007 (c) 2000-2007 John Lim (jlim#natsoft.com.my). All rights reserved. 5 5 Released under both BSD license and Lesser GPL library license. 6 6 Whenever there is any discrepancy between the two licenses, -
trunk/phpgwapi/inc/adodb/datadict/datadict-ibase.inc.php
r2 r34 2 2 3 3 /** 4 V4. 51 29 July 2004 (c) 2000-2004 John Lim (jlim@natsoft.com.my). All rights reserved.4 V4.94 23 Jan 2007 (c) 2000-2007 John Lim (jlim#natsoft.com.my). All rights reserved. 5 5 Released under both BSD license and Lesser GPL library license. 6 6 Whenever there is any discrepancy between the two licenses, -
trunk/phpgwapi/inc/adodb/datadict/datadict-informix.inc.php
r2 r34 2 2 3 3 /** 4 V4. 51 29 July 2004 (c) 2000-2004 John Lim (jlim@natsoft.com.my). All rights reserved.4 V4.94 23 Jan 2007 (c) 2000-2007 John Lim (jlim#natsoft.com.my). All rights reserved. 5 5 Released under both BSD license and Lesser GPL library license. 6 6 Whenever there is any discrepancy between the two licenses, … … 33 33 34 34 case 'D': return 'DATE'; 35 case 'T': return 'DATETIME ';35 case 'T': return 'DATETIME YEAR TO SECOND'; 36 36 37 37 case 'L': return 'SMALLINT'; -
trunk/phpgwapi/inc/adodb/datadict/datadict-mssql.inc.php
r2 r34 2 2 3 3 /** 4 V4. 51 29 July 2004 (c) 2000-2004 John Lim (jlim@natsoft.com.my). All rights reserved.4 V4.94 23 Jan 2007 (c) 2000-2007 John Lim (jlim#natsoft.com.my). All rights reserved. 5 5 Released under both BSD license and Lesser GPL library license. 6 6 Whenever there is any discrepancy between the two licenses, … … 11 11 */ 12 12 13 /* 14 In ADOdb, named quotes for MS SQL Server use ". From the MSSQL Docs: 15 16 Note Delimiters are for identifiers only. Delimiters cannot be used for keywords, 17 whether or not they are marked as reserved in SQL Server. 18 19 Quoted identifiers are delimited by double quotation marks ("): 20 SELECT * FROM "Blanks in Table Name" 21 22 Bracketed identifiers are delimited by brackets ([ ]): 23 SELECT * FROM [Blanks In Table Name] 24 25 Quoted identifiers are valid only when the QUOTED_IDENTIFIER option is set to ON. By default, 26 the Microsoft OLE DB Provider for SQL Server and SQL Server ODBC driver set QUOTED_IDENTIFIER ON 27 when they connect. 28 29 In Transact-SQL, the option can be set at various levels using SET QUOTED_IDENTIFIER, 30 the quoted identifier option of sp_dboption, or the user options option of sp_configure. 31 32 When SET ANSI_DEFAULTS is ON, SET QUOTED_IDENTIFIER is enabled. 33 34 Syntax 35 36 SET QUOTED_IDENTIFIER { ON | OFF } 37 38 39 */ 40 13 41 // security - hide paths 14 42 if (!defined('ADODB_DIR')) die(); … … 16 44 class ADODB2_mssql extends ADODB_DataDict { 17 45 var $databaseType = 'mssql'; 18 19 46 var $dropIndex = 'DROP INDEX %2$s.%1$s'; 47 var $renameTable = "EXEC sp_rename '%s','%s'"; 48 var $renameColumn = "EXEC sp_rename '%s.%s','%s'"; 49 50 var $typeX = 'TEXT'; ## Alternatively, set it to VARCHAR(4000) 51 var $typeXL = 'TEXT'; 52 53 //var $alterCol = ' ALTER COLUMN '; 20 54 21 55 function MetaType($t,$len=-1,$fieldobj=false) … … 29 63 $len = -1; // mysql max_length is not accurate 30 64 switch (strtoupper($t)) { 31 65 case 'R': 32 66 case 'INT': 33 67 case 'INTEGER': return 'I'; … … 46 80 { 47 81 switch(strtoupper($meta)) { 82 48 83 case 'C': return 'VARCHAR'; 49 case 'XL': 50 case 'X': return 'TEXT'; 51 84 case 'XL': return (isset($this)) ? $this->typeXL : 'TEXT'; 85 case 'X': return (isset($this)) ? $this->typeX : 'TEXT'; ## could be varchar(8000), but we want compat with oracle 52 86 case 'C2': return 'NVARCHAR'; 53 87 case 'X2': return 'NTEXT'; … … 59 93 case 'L': return 'BIT'; 60 94 95 case 'R': 61 96 case 'I': return 'INT'; 62 97 case 'I1': return 'TINYINT'; … … 82 117 $f[] = "\n $v"; 83 118 } 84 $s .= implode(', ',$f);119 $s .= implode(', ',$f); 85 120 $sql[] = $s; 86 121 return $sql; … … 109 144 $s = 'ALTER TABLE ' . $tabname; 110 145 foreach($flds as $v) { 111 $f[] = "\n$this->dropCol $v";112 } 113 $s .= implode(', ',$f);146 $f[] = "\n$this->dropCol ".$this->NameQuote($v); 147 } 148 $s .= implode(', ',$f); 114 149 $sql[] = $s; 115 150 return $sql; … … 240 275 return $ftype; 241 276 } 242 if (strlen($fsize) && $ty != 'X' && $ty != 'B' && strpos($ftype,'(') === false) { 243 $ftype .= "(".$fsize; 244 if (strlen($fprec)) $ftype .= ",".$fprec; 245 $ftype .= ')'; 246 } 247 return $ftype; 277 if ($ty == 'T') return $ftype; 278 return parent::_GetSize($ftype, $ty, $fsize, $fprec); 279 248 280 } 249 281 } -
trunk/phpgwapi/inc/adodb/datadict/datadict-mysql.inc.php
r2 r34 2 2 3 3 /** 4 V4. 51 29 July 2004 (c) 2000-2004 John Lim (jlim@natsoft.com.my). All rights reserved.4 V4.94 23 Jan 2007 (c) 2000-2007 John Lim (jlim#natsoft.com.my). All rights reserved. 5 5 Released under both BSD license and Lesser GPL library license. 6 6 Whenever there is any discrepancy between the two licenses, … … 21 21 22 22 var $dropIndex = 'DROP INDEX %s ON %s'; 23 var $renameColumn = 'ALTER TABLE %s CHANGE COLUMN %s %s %s'; // needs column-definition! 23 24 24 25 function MetaType($t,$len=-1,$fieldobj=false) … … 29 30 $len = $fieldobj->max_length; 30 31 } 32 $is_serial = is_object($fieldobj) && $fieldobj->primary_key && $fieldobj->auto_increment; 31 33 32 34 $len = -1; // mysql max_length is not accurate … … 66 68 67 69 case 'INT': 68 case 'INTEGER': return (!empty($fieldobj->primary_key))? 'R' : 'I';69 case 'TINYINT': return (!empty($fieldobj->primary_key))? 'R' : 'I1';70 case 'SMALLINT': return (!empty($fieldobj->primary_key))? 'R' : 'I2';71 case 'MEDIUMINT': return (!empty($fieldobj->primary_key))? 'R' : 'I4';72 case 'BIGINT': return (!empty($fieldobj->primary_key))? 'R' : 'I8';70 case 'INTEGER': return $is_serial ? 'R' : 'I'; 71 case 'TINYINT': return $is_serial ? 'R' : 'I1'; 72 case 'SMALLINT': return $is_serial ? 'R' : 'I2'; 73 case 'MEDIUMINT': return $is_serial ? 'R' : 'I4'; 74 case 'BIGINT': return $is_serial ? 'R' : 'I8'; 73 75 default: return 'N'; 74 76 } … … 79 81 switch(strtoupper($meta)) { 80 82 case 'C': return 'VARCHAR'; 81 case 'XL': 82 case 'X': return ' LONGTEXT';83 case 'XL':return 'LONGTEXT'; 84 case 'X': return 'TEXT'; 83 85 84 86 case 'C2': return 'VARCHAR'; … … 92 94 93 95 case 'R': 96 case 'I4': 94 97 case 'I': return 'INTEGER'; 95 98 case 'I1': return 'TINYINT'; 96 99 case 'I2': return 'SMALLINT'; 97 case 'I4': return 'MEDIUMINT';98 100 case 'I8': return 'BIGINT'; 99 101 -
trunk/phpgwapi/inc/adodb/datadict/datadict-oci8.inc.php
r2 r34 2 2 3 3 /** 4 V4. 51 29 July 2004 (c) 2000-2004 John Lim (jlim@natsoft.com.my). All rights reserved.4 V4.94 23 Jan 2007 (c) 2000-2007 John Lim (jlim#natsoft.com.my). All rights reserved. 5 5 Released under both BSD license and Lesser GPL library license. 6 6 Whenever there is any discrepancy between the two licenses, … … 21 21 var $dropTable = "DROP TABLE %s CASCADE CONSTRAINTS"; 22 22 var $trigPrefix = 'TRIG_'; 23 var $alterCol = ' MODIFY '; 24 var $typeX = 'VARCHAR(4000)'; 25 var $typeXL = 'CLOB'; 23 26 24 27 function MetaType($t,$len=-1) … … 70 73 switch($meta) { 71 74 case 'C': return 'VARCHAR'; 72 case 'X': return 'VARCHAR(4000)';73 case 'XL': return 'CLOB';74 75 case 'C2': return 'NVARCHAR ';76 case 'X2': return 'NVARCHAR (2000)';75 case 'X': return $this->typeX; 76 case 'XL': return $this->typeXL; 77 78 case 'C2': return 'NVARCHAR2'; 79 case 'X2': return 'NVARCHAR2(4000)'; 77 80 78 81 case 'B': return 'BLOB'; … … 114 117 } 115 118 116 $s .= implode(', ',$f).')';119 $s .= implode(', ',$f).')'; 117 120 $sql[] = $s; 118 121 return $sql; … … 127 130 $f[] = "\n $v"; 128 131 } 129 $s .= implode(', ',$f).')';132 $s .= implode(', ',$f).')'; 130 133 $sql[] = $s; 131 134 return $sql; … … 135 138 { 136 139 if (!is_array($flds)) $flds = explode(',',$flds); 140 foreach ($flds as $k => $v) $flds[$k] = $this->NameQuote($v); 141 137 142 $sql = array(); 138 143 $s = "ALTER TABLE $tabname DROP("; 139 $s .= implode(', ',$flds).') CASCADE COSTRAINTS';144 $s .= implode(', ',$flds).') CASCADE CONSTRAINTS'; 140 145 $sql[] = $s; 141 146 return $sql; … … 192 197 $trigname = $this->trigPrefix.$seqname; 193 198 } 199 200 if (strlen($seqname) > 30) { 201 $seqname = $this->seqPrefix.uniqid(''); 202 } // end if 203 if (strlen($trigname) > 30) { 204 $trigname = $this->trigPrefix.uniqid(''); 205 } // end if 206 194 207 if (isset($tableoptions['REPLACE'])) $sql[] = "DROP SEQUENCE $seqname"; 195 208 $seqCache = ''; -
trunk/phpgwapi/inc/adodb/datadict/datadict-postgres.inc.php
r2 r34 2 2 3 3 /** 4 V4. 51 29 July 2004 (c) 2000-2004 John Lim (jlim@natsoft.com.my). All rights reserved.4 V4.94 23 Jan 2007 (c) 2000-2007 John Lim (jlim#natsoft.com.my). All rights reserved. 5 5 Released under both BSD license and Lesser GPL library license. 6 6 Whenever there is any discrepancy between the two licenses, … … 21 21 var $addCol = ' ADD COLUMN'; 22 22 var $quote = '"'; 23 var $renameTable = 'ALTER TABLE %s RENAME TO %s'; // at least since 7.1 24 var $dropTable = 'DROP TABLE %s CASCADE'; 23 25 24 26 function MetaType($t,$len=-1,$fieldobj=false) … … 29 31 $len = $fieldobj->max_length; 30 32 } 33 $is_serial = is_object($fieldobj) && $fieldobj->primary_key && $fieldobj->unique && 34 $fieldobj->has_default && substr($fieldobj->default_value,0,8) == 'nextval('; 35 31 36 switch (strtoupper($t)) { 32 37 case 'INTERVAL': … … 61 66 return 'T'; 62 67 63 case 'INTEGER': return (empty($fieldobj->primary_key) && empty($fieldobj->unique))? 'I' : 'R';68 case 'INTEGER': return !$is_serial ? 'I' : 'R'; 64 69 case 'SMALLINT': 65 case 'INT2': return (empty($fieldobj->primary_key) && empty($fieldobj->unique))? 'I2' : 'R';66 case 'INT4': return (empty($fieldobj->primary_key) && empty($fieldobj->unique))? 'I4' : 'R';70 case 'INT2': return !$is_serial ? 'I2' : 'R'; 71 case 'INT4': return !$is_serial ? 'I4' : 'R'; 67 72 case 'BIGINT': 68 case 'INT8': return (empty($fieldobj->primary_key) && empty($fieldobj->unique))? 'I8' : 'R';73 case 'INT8': return !$is_serial ? 'I8' : 'R'; 69 74 70 75 case 'OID': … … 98 103 case 'T': return 'TIMESTAMP'; 99 104 100 case 'L': return ' SMALLINT';105 case 'L': return 'BOOLEAN'; 101 106 case 'I': return 'INTEGER'; 102 107 case 'I1': return 'SMALLINT'; … … 112 117 } 113 118 114 /* The following does not work in Pg 6.0 - does anyone want to contribute code? 115 116 //"ALTER TABLE table ALTER COLUMN column SET DEFAULT mydef" and 117 //"ALTER TABLE table ALTER COLUMN column DROP DEFAULT mydef" 118 //"ALTER TABLE table ALTER COLUMN column SET NOT NULL" and 119 //"ALTER TABLE table ALTER COLUMN column DROP NOT NULL"*/ 120 function AlterColumnSQL($tabname, $flds) 121 { 122 if ($this->debug) ADOConnection::outp("AlterColumnSQL not supported for PostgreSQL"); 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"); 123 190 return array(); 124 191 } 125 126 127 function DropColumnSQL($tabname, $flds) 128 { 129 if ($this->debug) ADOConnection::outp("DropColumnSQL only works with PostgreSQL 7.3+"); 130 return ADODB_DataDict::DropColumnSQL($tabname, $flds)."/* only works for PostgreSQL 7.3+ */"; 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; 131 264 } 132 265 … … 145 278 } 146 279 147 function _DropAutoIncrement($t) 148 { 149 return "drop sequence ".$t."_m_id_seq"; 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; 150 294 } 151 295 … … 214 358 return $sql; 215 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 } 216 370 } 217 371 ?> -
trunk/phpgwapi/inc/adodb/datadict/datadict-sybase.inc.php
r2 r34 2 2 3 3 /** 4 V4. 51 29 July 2004 (c) 2000-2004 John Lim (jlim@natsoft.com.my). All rights reserved.4 V4.94 23 Jan 2007 (c) 2000-2007 John Lim (jlim#natsoft.com.my). All rights reserved. 5 5 Released under both BSD license and Lesser GPL library license. 6 6 Whenever there is any discrepancy between the two licenses, … … 82 82 $f[] = "\n $v"; 83 83 } 84 $s .= implode(', ',$f);84 $s .= implode(', ',$f); 85 85 $sql[] = $s; 86 86 return $sql; … … 101 101 function DropColumnSQL($tabname, $flds) 102 102 { 103 $tabname = $this->TableName 103 $tabname = $this->TableName($tabname); 104 104 if (!is_array($flds)) $flds = explode(',',$flds); 105 105 $f = array(); 106 106 $s = "ALTER TABLE $tabname"; 107 107 foreach($flds as $v) { 108 $f[] = "\n$this->dropCol $v";109 } 110 $s .= implode(', ',$f);108 $f[] = "\n$this->dropCol ".$this->NameQuote($v); 109 } 110 $s .= implode(', ',$f); 111 111 $sql[] = $s; 112 112 return $sql;
Note: See TracChangeset
for help on using the changeset viewer.
