Index: inc/class.db_oracle.inc.php =================================================================== RCS file: /cvsroot/phpgwapi/phpgwapi/inc/class.db_oracle.inc.php,v retrieving revision 1.6.2.1.2.3 diff -u -r1.6.2.1.2.3 class.db_oracle.inc.php --- inc/class.db_oracle.inc.php 10 Feb 2004 13:51:18 -0000 1.6.2.1.2.3 +++ inc/class.db_oracle.inc.php 17 Jun 2004 09:47:23 -0000 @@ -17,10 +17,8 @@ * @package phpgwapi * @subpackage database */ - class db + class db extends db_ { - var $Remote = 1; - var $ora_no_next_fetch = False; /* copied from db_mysql for completeness */ @@ -28,12 +26,12 @@ var $type = 'oracle'; var $revision = '1.2'; - /* public: constructor */ function db($query = '') { $this->db_($query); } + /* public: connection management */ function connect($Database = '', $Host = '', $User = '', $Password = '') { /* Handle defaults */ @@ -41,83 +39,77 @@ { $Database = $this->Database; } + if ($Host == '') { $Host = $this->Host; } + if ($User == '') { $User = $this->User; } + if ($Password == '') { $Password = $this->Password; } + /* establish connection, select database */ if (! $this->Link_ID) { - if($this->Debug) - { - printf('
Connecting to ' . $Database . "...
\n"); - } - if($this->Remote) - { - if($this->Debug) - { - printf("
connect() $User/******@$Database.world
\n"); - } - if($GLOBALS['phpgw_info']['server']['db_persistent']) - { - $this->Link_ID = ora_plogon("$User/$Password@$Database",""); - /************** (comment by SSilk) - this dosn't work on my system: - $this->Link_ID = ora_plogon("$User@$Database.world","$Password"); - ***************/ - } - else - { - $this->Link_ID = ora_logon("$User/$Password@$Database",""); - /************** (comment by SSilk) - this dosn't work on my system: - $this->Link_ID=ora_logon("$User@$Database.world","$Password"); - ***************/ - } - } - else + if ($GLOBALS['phpgw_info']['server']['db_persistent']) { - if($this->Debug) - { - printf("
connect() $User, $Password
\n"); - } - if($GLOBALS['phpgw_info']['server']['db_persistent']) - { - $this->Link_ID=ora_plogon("$User","$Password"); - /* (comment by SSilk: don't know how this could work, but I leave this untouched!) */ - } - else - { - $this->Link_ID=ora_logon("$User","$Password"); - } - } - if($this->Debug) - { - printf("
connect() Link_ID: $this->Link_ID
\n"); - } - if (!$this->Link_ID) - { - $this->halt('connect() Link-ID == false ' - . "($this->Link_ID), ora_".($GLOBALS['phpgw_info']['server']['db_persistent']?'p':'').'logon failed'); + $this->Link_ID = ora_plogon("$User@$Database","$Password"); } else { - //echo 'commit on

'; - ora_commiton($this->Link_ID); - } - if($this->Debug) - { - printf("
connect() Obtained the Link_ID: $this->Link_ID
\n"); + $this->Link_ID = ora_logon("$User@$Database","$Password"); } + + if (!$this->Link_ID) + { + $this->halt(($GLOBALS['phpgw_info']['server']['db_persistent']?'p':'')."logon($Host, $User, \$Password) failed."); + return 0; + } + + ora_commitoff($this->Link_ID); } + return $this->Link_ID; + } + + /* This only affects systems not using persistant connections */ + function disconnect() + { + if ($this->Link_ID <> 0) + { + @ora_logoff($this->Link_ID); + $this->Link_ID = 0; + return 1; + } + else + { + return 0; + } + } + + function to_timestamp($epoch) + { + return date('Y-m-d H:i:s',$epoch); + } + + function from_timestamp($timestamp) + { + ereg('([0-9]{4})-([0-9]{2})-([0-9]{2}) ([0-9]{2}):([0-9]{2}):([0-9]{2})',$timestamp,$parts); + + return mktime($parts[4],$parts[5],$parts[6],$parts[2],$parts[3],$parts[1]); + } + + /* public: discard the query result */ + function free() + { + @ora_close($this->Query_ID); + $this->Query_ID = 0; } ## In order to increase the # of cursors per system/user go edit the @@ -130,109 +122,151 @@ ## You can also make extensive use of ->disconnect()! ## The unused QueryIDs will be recycled sometimes. - function query($Query_String) + function query($Query_String, $line = '', $file = '') { /* No empty queries, please, since PHP4 chokes on them. */ + /* The empty query string is passed on from the constructor, + * when calling the class without a query, e.g. in situations + * like these: '$db = new db_Subclass;' + */ if ($Query_String == '') { - /* The empty query string is passed on from the constructor, - * when calling the class without a query, e.g. in situations - * like these: '$db = new DB_Sql_Subclass;' - */ return 0; } - $this->connect(); - $this->lastQuery=$Query_String; - if (!$this->Query_ID) + if (!$this->connect()) { - $this->Query_ID = ora_open($this->Link_ID); + $this->Errno = @ora_ErrorCode($this->Link_ID); + $this->Error = @ora_Error($this->Link_ID); + return 0; /* we already complained in connect() about that. */ + }; + + # New query, discard previous result. + if ($this->Query_ID) + { + $this->free(); } - if($this->Debug) + + $this->Query_ID = ora_open($this->Link_ID); + if (!$this->Query_ID) { - printf("Debug: query = %s
\n", $Query_String); - printf("
Debug: Query_ID: %d
\n", $this->Query_ID); + $this->Errno = @ora_ErrorCode($this->Link_ID); + $this->Error = @ora_Error($this->Link_ID); + return 0; } - if(!@ora_parse($this->Query_ID,$Query_String)) + if(!@ora_parse($this->Query_ID, $Query_String)) { - $this->Errno=ora_errorcode($this->Query_ID); - $this->Error=ora_error($this->Query_ID); - $this->halt("
ora_parse() failed:
$Query_String
Snap & paste this to sqlplus!"); + //$this->Errno = ora_errorcode($this->Query_ID); + //$this->Error = ora_error($this->Query_ID); + $this->halt("Invalid SQL(ora_parse): ".$Query_String, $line, $file); } elseif (!@ora_exec($this->Query_ID)) { - $this->Errno=ora_errorcode($this->Query_ID); - $this->Error=ora_error($this->Query_ID); - $this->halt("
\n$Query_String\n
Snap & paste this to sqlplus!"); + //$this->Errno = ora_errorcode($this->Query_ID); + //$this->Error = ora_error($this->Query_ID); + $this->halt("Invalid SQL(ora_exec): ".$Query_String, $line, $file); } - $this->Row=0; + $this->Row = 0; - if(!$this->Query_ID) + if ($this->Debug) { - $this->halt('Invalid SQL: '.$Query_String); + printf("Debug: query = %s
, line = %d, file = %s
\n", $Query_String, $line, $file); } return $this->Query_ID; } + // public: perform a query with limited result set + function limit_query($Query_String, $offset, $line = '', $file = '', $num_rows = 0) + { + $offset = intval($offset); + $num_rows = intval($num_rows); + + if ($num_rows == 0) + { + $maxmatches = $GLOBALS['phpgw_info']['user']['preferences']['common']['maxmatchs']; + $num_rows = (isset($maxmatches)?intval($maxmatches):15); + } + + if ($offset == 0) + { + $Query_String .= ' LIMIT ' . $num_rows; + } + else + { + $Query_String .= ' LIMIT ' . $offset . ',' . $num_rows; + } + + return $this->query($Query_String, $line, $file); + } + + /* public: walk result set */ function next_record() { - if (!$this->no_next_fetch && - 0 == ora_fetch($this->Query_ID)) + if (!$this->Query_ID) { - if ($this->Debug) - { - printf("
next_record(): ID: %d,Rows: %d
\n", - $this->Query_ID,$this->num_rows()); - } - $this->Row +=1; + $this->halt('next_record called with no query pending.'); + return 0; + } - $errno=ora_errorcode($this->Query_ID); - if(1403 == $errno) + if (!$this->no_next_fetch && !ora_fetch($this->Query_ID)) + { + $this->Row += 1; + + $errno = ora_errorcode($this->Query_ID); + if($errno == 1403) { # 1043 means no more records found $this->Errno = 0; $this->Error = ''; - $this->disconnect(); - $stat=0; + + if ($this->Auto_Free) + { + $this->free(); + } + + $stat = false; } else { - $this->Error=ora_error($this->Query_ID); - $this->Errno=$errno; - if($this->Debug) - { - printf('
%d Error: %s', - $this->Errno, - $this->Error); - } - $stat=0; + $this->Error = ora_error($this->Query_ID); + $this->Errno = $errno; + $stat = false; } } else { - $this->no_next_fetch=false; - for($ix=0;$ixQuery_ID);$ix++) - { - $col=strtolower(ora_columnname($this->Query_ID,$ix)); - $value=ora_getcolumn($this->Query_ID,$ix); - $this->Record[ "$col" ] = $value; - # echo"[$col]: $value
\n"; + $this->no_next_fetch = false; + $oranumcols = ora_numcols($this->Query_ID); + for($ix = 0; $ix < $oranumcols; $ix++) + { + $col = strtolower(ora_columnname($this->Query_ID, $ix)); + $value = ora_getcolumn($this->Query_ID, $ix); + if ($col != '') + { + $this->Record[$ix] = $value; + $this->Record["$col"] = $value; + } + else + { + $this->Record[$ix] = $value; + } } - $stat=1; + $stat = true; } + return $stat; } ## seek() works only for $pos - 1 and $pos ## Perhaps I make a own implementation, but my ## opinion is, that this should be done by PHP3 - function seek($pos) + function seek($pos = 0) { if($this->Row - 1 == $pos) { - $this->no_next_fetch=true; + $this->no_next_fetch = true; } elseif ($this->Row == $pos ) { @@ -242,33 +276,140 @@ { $this->halt("Invalid seek(): Position is cannot be handled by API.
". "Difference too big. Wanted: $pos Current pos: $this->Row"); + return false; + } + + $this->Row = $pos; + return true; + } + + function transaction_begin() + { + return true; + } + + function transaction_commit() + { + if (! $this->Errno) + { + return @ora_do($this->Link_ID, 'commit'); } - if($Debug) + else { - echo "
Debug: seek = $pos
"; + return false; } - $this->Row=$pos; + } + + function transaction_abort() + { + return @ora_do($this->Link_ID, 'rollback'); + } + + function get_last_insert_id($table, $field) + { + /* This will get the last insert ID created on the current connection. Should only be called + * after an insert query is run on a table that has an auto incrementing field. $table and + * $field are required, but unused here since it's unnecessary for mysql. For compatibility + * with pgsql, the params must be supplied. + */ + + if (!isset($table) || $table == '' || !isset($field) || $field == '') + { + return -1; + } + + return 0; /* ???? last id get */ } function lock($table, $mode = 'write') { + $this->connect(); + if ($mode == 'write') { - $result = ora_do($this->Link_ID, "lock table $table in row exclusive mode"); + if (is_array($table)) + { + while ($t = each($table)) + { + $result = ora_do($this->Link_ID, "lock table $t[1] in row exclusive mode"); + } + } + else + { + $result = ora_do($this->Link_ID, "lock table $table in row exclusive mode"); + } } else { - $result = 1; + $result = true; } + return $result; } function unlock() { - return ora_do($this->Link_ID, 'commit'); + return @ora_do($this->Link_ID, 'commit'); + } + + function affected_rows() + { + return @ora_numrows($this->Query_ID); } - function metadata($table,$full=false) + function num_rows() + { + return @ora_numrows($this->Query_ID); + } + + function num_fields() + { + return @ora_numcols($this->Query_ID); + } + + /* public: sequence number */ + function nextid($seq_name) + { + $this->connect(); + + /* Independent Query_ID */ + $Query_ID = ora_open($this->Link_ID); + + if(!@ora_parse($Query_ID,"SELECT $seq_name.NEXTVAL FROM DUAL")) + { + // There is no such sequence yet, then create it + if(!@ora_parse($Query_ID,"CREATE SEQUENCE $seq_name") || + !@ora_exec($Query_ID)) + { + $this->halt('
nextid() function - unable to create sequence'); + return 0; + } + @ora_parse($Query_ID,"SELECT $seq_name.NEXTVAL FROM DUAL"); + } + + if (!@ora_exec($Query_ID)) + { + $this->halt("
ora_exec() failed:
nextID function"); + } + + if (@ora_fetch($Query_ID) ) + { + $next_id = ora_getcolumn($Query_ID, 0); + } + else + { + $next_id = 0; + } + + if ($Query_ID <> 0) + { + ora_close($Query_ID); + } + + return $next_id; + } + + function metadata($table = '', $full = false) { $count = 0; $id = 0; @@ -322,29 +463,29 @@ " AND T.table_name=I.table_name (+)". " AND T.table_name=UPPER('$table') ORDER BY T.column_id"); - $i=0; + $i = 0; while ($this->next_record()) { - $res[$i]['table'] = $this->Record[table_name]; - $res[$i]['name'] = strtolower($this->Record[column_name]); - $res[$i]['type'] = $this->Record[data_type]; - $res[$i]['len'] = $this->Record[data_length]; - if ($this->Record[index_name]) + $res[$i]['table'] = $this->Record['table_name']; + $res[$i]['name'] = strtolower($this->Record['column_name']); + $res[$i]['type'] = $this->Record['data_type']; + $res[$i]['len'] = $this->Record['data_length']; + if ($this->Record['index_name']) { $res[$i]['flags'] = 'INDEX '; } - $res[$i]['flags'] .= ( $this->Record['nullable'] == 'N') ? '' : 'NOT NULL'; - $res[$i]['format']= (int)$this->Record['data_precision'].','. + $res[$i]['flags'] .= ($this->Record['nullable'] == 'N') ? '' : 'NOT NULL'; + $res[$i]['format'] = (int)$this->Record['data_precision'].','. (int)$this->Record[data_scale]; if ('0,0'==$res[$i]['format']) { $res[$i]['format']=''; } - $res[$i]['index'] = $this->Record[index_name]; - $res[$i]['chars'] = $this->Record[char_col_decl_length]; + $res[$i]['index'] = $this->Record['index_name']; + $res[$i]['chars'] = $this->Record['char_col_decl_length']; if ($full) { - $j=$res[$i]['name']; + $j = $res[$i]['name']; $res['meta'][$j] = $i; $res['meta'][strtoupper($j)] = $i; } @@ -356,131 +497,51 @@ } if ($full) { - $res['num_fields']=$i; + $res['num_fields'] = $i; } # $this->disconnect(); return $res; } - ## THIS FUNCTION IS UNSTESTED! - function affected_rows() - { - if ($Debug) - { - echo '
Debug: affected_rows='. ora_numrows($this->Query_ID).'
'; - } - return ora_numrows($this->Query_ID); - } - - ## Known bugs: It will not work for SELECT DISTINCT and any - ## other constructs which are depending on the resulting rows. - ## So you *really need* to check every query you make, if it - ## will work with it. - ## - ## Also, for a qualified replacement you need to parse the - ## selection, cause this will fail: 'SELECT id, from FROM ...'). - ## 'FROM' is - as far as I know a keyword in Oracle, so it can - ## only be used in this way. But you have been warned. - function num_rows() + /* private: error handling */ + function halt($msg, $line = '', $file = '') { - $curs=ora_open($this->Link_ID); + if ($this->Errno = @ora_ErrorCode($this->Link_ID) == 0) + { + $this->Errno = @ora_ErrorCode($this->Query_ID); + $this->Error = @ora_Error($this->Query_ID); + } + else + { + $this->Error = @ora_Error($this->Link_ID); + } - ## this is the important part and it is also the HACK! - if (eregi("^[[:space:]]*SELECT[[:space:]]",$this->lastQuery) ) + if ($this->Link_ID) // only if we have a link, else infinite loop { - $from_pos = strpos(strtoupper($this->lastQuery),'FROM'); - $q = 'SELECT count(*) '. substr($this->lastQuery, $from_pos); - - ORA_parse($curs,$q); - ORA_exec($curs); - ORA_fetch($curs); - if ($Debug) - { - echo '
Debug: num_rows='. ORA_getcolumn($curs,0).'
'; - } - return(ORA_getcolumn($curs,0)); + $this->unlock(); /* Just in case there is a table currently locked */ } - else - { - $this->halt("Last Query was not a SELECT: $this->lastQuery"); - } - } - function num_fields() - { - if ($Debug) + if ($this->Halt_On_Error == 'no') { - echo '
Debug: num_fields='. ora_numcols($this->Query_ID) . '
'; + return; } - return ora_numcols($this->Query_ID); - } - - /* public: sequence number */ - function nextid($seq_name) - { - $this->connect(); - - /* Independent Query_ID */ - $Query_ID = ora_open($this->Link_ID); - if(!@ora_parse($Query_ID,"SELECT $seq_name.NEXTVAL FROM DUAL")) - { - // There is no such sequence yet, then create it - if(!@ora_parse($Query_ID,"CREATE SEQUENCE $seq_name") || - !@ora_exec($Query_ID)) - { - $this->halt('
nextid() function - unable to create sequence'); - return 0; - } - @ora_parse($Query_ID,"SELECT $seq_name.NEXTVAL FROM DUAL"); - } - if (!@ora_exec($Query_ID)) - { - $this->halt("
ora_exec() failed:
nextID function"); - } - if (@ora_fetch($Query_ID) ) - { - $next_id = ora_getcolumn($Query_ID, 0); - } - else - { - $next_id = 0; - } - if ( Query_ID > 0 ) - { - ora_close(Query_ID); - } - return $next_id; - } + $this->haltmsg($msg); - function disconnect() - { - if($this->Debug) + if ($file) { - echo "Debug: Disconnecting $this->Query_ID...
\n"; + printf("
File: %s",$file); } - if ( $this->Query_ID < 1 ) - { - echo "Warning: disconnect(): Cannot free ID $this->Query_ID\n"; - # return(); - } - ora_close($this->Query_ID); - $this->Query_ID=0; - } - /* private: error handling */ - function halt($msg) - { - if ($this->Halt_On_Error == 'no') + if ($line) { - return; + printf("
Line: %s",$line); } - $this->haltmsg($msg); - if ($this->Halt_On_Error != 'report') { - die('Session halted.'); + echo "

Session halted."; + $GLOBALS['phpgw']->common->phpgw_exit(True); } } @@ -488,15 +549,25 @@ { printf("Database error: %s
\n", $msg); printf("Oracle Error: %s (%s)
\n", - $this->Errno, - $this->Error); + $this->Errno, + $this->Error + ); } function table_names() { - $this->connect(); + if (!$this->Link_ID) + { + $this->connect(); + } + + if (!$this->Link_ID) + { + return array(); + } + $this->query('SELECT table_name,tablespace_name FROM user_tables'); - $i=0; + $i = 0; while ($this->next_record()) { $info[$i]['table_name'] = strtolower($this->Record['table_name']); @@ -505,4 +576,9 @@ } return $info; } + + function create_database($adminname = '', $adminpasswd = '') + { + /* Not Implementation */ + } } Index: inc/class.schema_proc.inc.php =================================================================== RCS file: /cvsroot/phpgwapi/phpgwapi/inc/class.schema_proc.inc.php,v retrieving revision 1.2.2.9 diff -u -r1.2.2.9 class.schema_proc.inc.php --- inc/class.schema_proc.inc.php 12 Mar 2004 02:41:24 -0000 1.2.2.9 +++ inc/class.schema_proc.inc.php 17 Jun 2004 09:47:23 -0000 @@ -46,7 +46,8 @@ foreach ($this->m_aTables as $sTableName => $aTableDef) { $sSequenceSQL = ''; - if($this->_GetTableSQL($sTableName, $aTableDef, $sTableSQL, $sSequenceSQL)) + $sTriggerSQL = ''; + if($this->_GetTableSQL($sTableName, $aTableDef, $sTableSQL, $sSequenceSQL, $sTriggerSQL)) { $sTableSQL = "CREATE TABLE $sTableName (\n$sTableSQL\n)" . $this->m_oTranslator->m_sStatementTerminator; @@ -54,6 +55,12 @@ { $sAllTableSQL .= $sSequenceSQL . "\n"; } + + if($sTriggerSQL != '') + { + $sAllTableSQL .= $sTriggerSQL . "\n"; + } + $sAllTableSQL .= $sTableSQL . "\n\n"; } else @@ -258,7 +265,7 @@ return $this->m_odb->query($sQuery, $line, $file); } - function _GetTableSQL($sTableName, $aTableDef, &$sTableSQL, &$sSequenceSQL) + function _GetTableSQL($sTableName, $aTableDef, &$sTableSQL, &$sSequenceSQL, &$sTriggerSQL) { global $DEBUG; @@ -268,7 +275,11 @@ } $sTableSQL = ''; + $sSequenceSQL = ''; + $sTriggerSQL = ''; reset($aTableDef['fd']); + unset($sbufTriggerFD); + $sbufTriggerFD = array(); while(list($sFieldName, $aFieldAttr) = each($aTableDef['fd'])) { $sFieldSQL = ''; @@ -283,8 +294,8 @@ if($aFieldAttr['type'] == 'auto') { - $this->m_oTranslator->GetSequenceSQL($sTableName, $sSequenceSQL); - if($sSequenceSQL != '') + $sbufTriggerFD[] = $sFieldName; + if($this->m_oTranslator->GetSequenceSQL($sTableName, $sSequenceSQL)) { $sTableSQL .= sprintf(" DEFAULT nextval('seq_%s')", $sTableName); } @@ -375,6 +386,11 @@ $sTableSQL .= ",\n" . $sFKSQL; } + if (count($sbufTriggerFD) > 0) + { + $this->m_oTranslator->GetTriggerSQL($sTableName, $sbufTriggerFD, $sTriggerSQL); + } + return True; } @@ -419,11 +435,13 @@ } // Translate the type for the DBMS + $sBufNullable = ''; + $sBufDefault = ''; if($sFieldSQL = $this->m_oTranslator->TranslateType($sType, $iPrecision, $iScale)) { if($bNullable == False) { - $sFieldSQL .= ' NOT NULL'; + $sBufNullable = ' NOT NULL'; } if($sDefault != '') @@ -431,12 +449,22 @@ if($DEBUG) { echo'
_GetFieldSQL(): Calling TranslateDefault for "' . $sDefault . '"'; } // Get default DDL - useful for differences in date defaults (eg, now() vs. getdate()) $sTranslatedDefault = $this->m_oTranslator->TranslateDefault($sDefault); - $sFieldSQL .= " DEFAULT $sTranslatedDefault"; + $sBufDefault = " DEFAULT $sTranslatedDefault"; } elseif($sDefault == '0') { - $sFieldSQL .= " DEFAULT '0'"; + $sBufDefault = " DEFAULT '0'"; } + + if ($this->dbms == 'oracle') + { + $sFieldSQL .= $sBufDefault . $sBufNullable; + } + else + { + $sFieldSQL .= $sBufNullable . $sBufDefault; + } + if($DEBUG) { echo'
_GetFieldSQL(): Outgoing SQL: ' . $sFieldSQL; } return true; } Index: inc/class.schema_proc_mssql.inc.php =================================================================== RCS file: /cvsroot/phpgwapi/phpgwapi/inc/class.schema_proc_mssql.inc.php,v retrieving revision 1.2.2.5 diff -u -r1.2.2.5 class.schema_proc_mssql.inc.php --- inc/class.schema_proc_mssql.inc.php 10 Feb 2004 13:51:18 -0000 1.2.2.5 +++ inc/class.schema_proc_mssql.inc.php 17 Jun 2004 09:47:23 -0000 @@ -329,9 +329,15 @@ return true; } + function GetTriggerSQL($sTableName, $sColumnNames, &$sTriggerSQL) + { + $sTriggerSQL = ''; + return false; + } + function CreateTable($oProc, &$aTables, $sTableName, $aTableDef) { - if ($oProc->_GetTableSQL($sTableName, $aTableDef, $sTableSQL, $sSequenceSQL)) + if ($oProc->_GetTableSQL($sTableName, $aTableDef, $sTableSQL, $sSequenceSQL, $sTriggerSQL)) { // create sequence first since it will be needed for default if ($sSequenceSQL != '') Index: inc/class.schema_proc_mysql.inc.php =================================================================== RCS file: /cvsroot/phpgwapi/phpgwapi/inc/class.schema_proc_mysql.inc.php,v retrieving revision 1.6.2.8 diff -u -r1.6.2.8 class.schema_proc_mysql.inc.php --- inc/class.schema_proc_mysql.inc.php 25 Feb 2004 12:00:20 -0000 1.6.2.8 +++ inc/class.schema_proc_mysql.inc.php 17 Jun 2004 09:47:24 -0000 @@ -363,12 +363,18 @@ function GetSequenceSQL($sTableName, &$sSequenceSQL) { $sSequenceSQL = ''; - return true; + return false; + } + + function GetTriggerSQL($sTableName, $sColumnNames, &$sTriggerSQL) + { + $sTriggerSQL = ''; + return false; } function CreateTable($oProc, &$aTables, $sTableName, $aTableDef) { - if ($oProc->_GetTableSQL($sTableName, $aTableDef, $sTableSQL, $sSequenceSQL)) + if ($oProc->_GetTableSQL($sTableName, $aTableDef, $sTableSQL, $sSequenceSQL, $sTriggerSQL)) { /* create sequence first since it will be needed for default */ if ($sSequenceSQL != '') Index: inc/class.schema_proc_oracle.inc.php =================================================================== RCS file: inc/class.schema_proc_oracle.inc.php diff -N inc/class.schema_proc_oracle.inc.php --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ inc/class.schema_proc_oracle.inc.php 17 Jun 2004 09:47:24 -0000 @@ -0,0 +1,455 @@ + + * @copyright Portions Copyright (C) 2003,2004 Free Software Foundation, Inc. http://www.fsf.org/ + * @license http://www.fsf.org/licenses/gpl.html GNU General Public License + * @package phpgwapi + * @subpackage database + * @version $Id$ + */ + + /** + * Database schema abstraction class for Oracle + * + * @package phpgwapi + * @subpackage database + */ + class schema_proc_oracle + { + var $m_sStatementTerminator; + var $m_sSequencePrefix; + var $m_sIndexPrefix; + + /* Following added to convert sql to array */ + var $sCol = array(); + var $pk = array(); + var $fk = array(); + var $ix = array(); + var $uc = array(); + + function schema_proc_oracle() + { + $this->m_sStatementTerminator = ';'; + $this->m_sSequencePrefix = 'sq'; + $this->m_sIndexPrefix = 'ix'; + } + + /* Return a type suitable for DDL */ + function TranslateType($sType, $iPrecision = 0, $iScale = 0) + { + $sTranslated = ''; + switch($sType) + { + case 'auto': + $sTranslated = 'number(11)'; + break; + case 'blob': + $sTranslated = 'blob'; + break; + case 'char': + case 'varchar': + if ($iPrecision > 4000) + { + $sTranslated = 'clob'; + } + else + { + $sTranslated = sprintf("varchar2(%d)", $iPrecision); + } + break; + case 'date': + $sTranslated = 'date'; + break; + case 'decimal': + $sTranslated = sprintf("number(%d,%d)", $iPrecision, $iScale); + break; + case 'float': + $sTranslated = 'number'; + break; + case 'int': + $sTranslated = 'number(38)'; + break; + case 'longtext': + case 'text': + $sTranslated = 'varchar2(4000)'; + break; + case 'timestamp': + $sTranslated = 'timestamp'; + break; + } + return $sTranslated; + } + + function TranslateDefault($sDefault) + { + switch ($sDefault) + { + case 'current_date': + return "sysdate"; + case 'current_timestamp': + return "systimestamp"; + } + return "'" . $sDefault . "'"; + } + + /* Inverse of above, convert sql column types to array info */ + function rTranslateType($sType, $iPrecision = 0, $iScale = 0) + { + $sTranslated = ''; + if ($sType == 'int' || $sType == 'tinyint' || $sType == 'smallint' || $sType == 'bigint') + { + if ($iPrecision > 8) + { + $iPrecision = 8; + } + elseif($iPrecision > 4) + { + $iPrecision = 4; + } + else + { + $iPrecision = 2; + } + } + switch($sType) + { + case 'tinyint': + case 'smallint': + $sTranslated = "'type' => 'int', 'precision' => 2"; + break; + case 'int': + $sTranslated = "'type' => 'int', 'precision' => 4"; + break; + case 'bigint': + $sTranslated = "'type' => 'int', 'precision' => 8"; + case 'char': + if ($iPrecision > 0 && $iPrecision < 256) + { + $sTranslated = "'type' => 'char', 'precision' => $iPrecision"; + } + if ($iPrecision > 255) + { + $sTranslated = "'type' => 'text'"; + } + break; + case 'decimal': + $sTranslated = "'type' => 'decimal', 'precision' => $iPrecision, 'scale' => $iScale"; + break; + case 'float': + case 'double': + $sTranslated = "'type' => 'float', 'precision' => $iPrecision"; + break; + case 'datetime': + $sTranslated = "'type' => 'timestamp'"; + break; + case 'enum': + /* Here comes a nasty assumption */ + /* $sTranslated = "'type' => 'varchar', 'precision' => 255"; */ + $sTranslated = "'type' => 'varchar', 'precision' => $iPrecision"; + break; + case 'varchar': + if ($iPrecision > 0 && $iPrecision < 256) + { + $sTranslated = "'type' => 'varchar', 'precision' => $iPrecision"; + } + if ($iPrecision > 255) + { + $sTranslated = "'type' => 'text'"; + } + break; + case 'longtext': + case 'text': + case 'blob': + case 'date': + $sTranslated = "'type' => '$sType'"; + break; + } + return $sTranslated; + } + + function GetPKSQL($sFields) + { + return "PRIMARY KEY($sFields)"; + } + + function GetUCSQL($sFields) + { + return ""; + } + + function GetIXSQL($sFields) + { + $this->indexcount++; + $this->indexes_sql[$this->indexcount] = "CREATE INDEX __index_name__ ON __table_name__ ($sFields)"; + return ''; + } + + // foreign key supports needs MySQL 3.23.44 and up with InnoDB or MySQL 5.1 + // or other versions the syntax is parsed in table create commands + // see chapter 1.8.4.5 + function GetFKSQL($sFields) + { + if (ereg("\((.*)\)", $sFields, $regs)) + { + $ret = "FOREIGN KEY (".$regs[1].")\n" . + " REFERENCES ".$sFields; + return $ret; + } else + return ""; // incorrect FK declaration found + } + + function _GetColumns($oProc, $sTableName, &$sColumns, $sDropColumn = '') + { + $sColumns = ''; + $this->pk = array(); + $this->fk = array(); + $this->ix = array(); + $this->uc = array(); + + /* Field, Type, Null, Key, Default, Extra */ + $oProc->m_odb->query("describe $sTableName"); + while ($oProc->m_odb->next_record()) + { + $type = $default = $null = $nullcomma = $prec = $scale = $ret = $colinfo = $scales = ''; + if ($sColumns != '') + { + $sColumns .= ','; + } + $sColumns .= $oProc->m_odb->f(0); + + /* The rest of this is used only for SQL->array */ + $colinfo = explode('(',$oProc->m_odb->f(1)); + $prec = ereg_replace(')','',$colinfo[1]); + $scales = explode(',',$prec); + + if($colinfo[0] == 'enum') + { + /* set prec to length of longest enum-value */ + for($prec=0; list($nul,$name) = @each($scales);) + { + if($prec < (strlen($name) - 2)) + { + /* -2 as name is like "'name'" */ + $prec = (strlen($name) - 2); + } + } + } + elseif ($scales[1]) + { + $prec = $scales[0]; + $scale = $scales[1]; + } + $type = $this->rTranslateType($colinfo[0], $prec, $scale); + + if ($oProc->m_odb->f(2) == 'YES') + { + $null = "'nullable' => True"; + } + else + { + $null = "'nullable' => False"; + } + if ($oProc->m_odb->f(4) != '') + { + $default = "'default' => '".$oProc->m_odb->f(4)."'"; + $nullcomma = ','; + } + else + { + $default = ''; + $nullcomma = ''; + } + if ($oProc->m_odb->f(5)) + { + $type = "'type' => 'auto'"; + } + $this->sCol[] = "\t\t\t\t'" . $oProc->m_odb->f(0)."' => array(" . $type . ',' . $null . $nullcomma . $default . '),' . "\n"; + if ($oProc->m_odb->f(3) == 'PRI') + { + $this->pk[] = $oProc->m_odb->f(0); + } + if ($oProc->m_odb->f(3) == 'UNI') + { + $this->uc[] = $oProc->m_odb->f(0); + } + /* Hmmm, MUL could also mean unique, or not... */ + if ($oProc->m_odb->f(3) == 'MUL') + { + $this->ix[] = $oProc->m_odb->f(0); + } + } + /* ugly as heck, but is here to chop the trailing comma on the last element (for php3) */ + $this->sCol[count($this->sCol) - 1] = substr($this->sCol[count($this->sCol) - 1],0,-2) . "\n"; + + return false; + } + + function GetSequenceForTable($oProc, $table, &$sSequenceName) + { + $sSQL = sprintf("SELECT SEQUENCE_NAME FROM ALL_SEQUENCES WHERE SEQUENCE_NAME LIKE '%s_%s' ORDER BY SEQUENCE_NAME", + strtoupper($this->m_sSequencePrefix), + strtoupper($table) + ); + $oProc->m_odb->query($sSQL, __LINE__, __FILE__); + $oProc->m_odb->next_record(); + + if ($oProc->m_odb->f('sequence_name')) + { + $sSequenceName = $oProc->m_odb->f('sequence_name'); + } + return True; + } + + function DropSequenceForTable($oProc,$table) + { + $this->GetSequenceForTable($oProc, $table, $sSequenceName); + if ($sSequenceName) + { + $oProc->m_odb->query("DROP SEQUENCE " . $sSequenceName, __LINE__, __FILE__); + } + return True; + } + + function DropTable($oProc, &$aTables, $sTableName) + { + $this->DropSequenceForTable($oProc,$sTableName); + + return !!($oProc->m_odb->query("DROP TABLE " . $sTableName)); + } + + function DropColumn($oProc, &$aTables, $sTableName, $aNewTableDef, $sColumnName, $bCopyData = true) + { + return !!($oProc->m_odb->query("ALTER TABLE $sTableName DROP COLUMN $sColumnName")); + } + + function RenameTable($oProc, &$aTables, $sOldTableName, $sNewTableName) + { + return !!($oProc->m_odb->query("ALTER TABLE $sOldTableName RENAME $sNewTableName")); + } + + function RenameColumn($oProc, &$aTables, $sTableName, $sOldColumnName, $sNewColumnName, $bCopyData = true) + { + /* + TODO: This really needs testing - it can affect primary keys, and other table-related objects + like sequences and such + */ + if ($oProc->_GetFieldSQL($aTables[$sTableName]["fd"][$sNewColumnName], $sNewColumnSQL)) + { + return !!($oProc->m_odb->query("ALTER TABLE $sTableName CHANGE $sOldColumnName $sNewColumnName " . $sNewColumnSQL)); + } + return false; + } + + function AlterColumn($oProc, &$aTables, $sTableName, $sColumnName, &$aColumnDef, $bCopyData = true) + { + if ($oProc->_GetFieldSQL($aTables[$sTableName]["fd"][$sColumnName], $sNewColumnSQL)) + { + return !!($oProc->m_odb->query("ALTER TABLE $sTableName MODIFY $sColumnName " . $sNewColumnSQL)); + /* return !!($oProc->m_odb->query("ALTER TABLE $sTableName CHANGE $sColumnName $sColumnName " . $sNewColumnSQL)); */ + } + + return false; + } + + function AddColumn($oProc, &$aTables, $sTableName, $sColumnName, &$aColumnDef) + { + $oProc->_GetFieldSQL($aColumnDef, $sFieldSQL); + $query = "ALTER TABLE $sTableName ADD COLUMN $sColumnName $sFieldSQL"; + + return !!($oProc->m_odb->query($query)); + } + + function GetSequenceSQL($sTableName, &$sSequenceSQL) + { + $sSequenceSQL = sprintf("CREATE SEQUENCE %s_%s", strtoupper($this->m_sSequencePrefix), strtoupper($sTableName)); + return false; + } + + function GetTriggerSQL($sTableName, $sColumnNames, &$sTriggerSQL) + { + $sTriggerSQL = ''; + if(is_array($sColumnNames) && count($sColumnNames) > 0) + { + $sTriggerSQL = sprintf("CREATE OR REPLACE TRIGGER TRG_%s ", strtoupper($sTableName)); + $sTriggerSQL .= sprintf("BEFORE INSERT ON %s ", $sTableName); + $sTriggerSQL .= "FOR EACH ROW "; + $sTriggerSQL .= "BEGIN "; + + foreach($sColumnNames as $sColumnName) + { + $sTriggerSQL .= sprintf("IF :NEW.%s IS NULL ", $sColumnName); + $sTriggerSQL .= "THEN "; + $sTriggerSQL .= sprintf("SELECT %s_%s.NEXTVAL INTO :NEW.%s FROM DUAL; ", + strtoupper($this->m_sSequencePrefix), + $sTableName, + $sColumnName); + $sTriggerSQL .= "END IF; "; + } + + $sTriggerSQL .= "END; "; + + return true; + } + else + { + return false; + } + } + + function CreateTable($oProc, $aTables, $sTableName, $aTableDef, $bCreateSequence = true) + { + global $DEBUG; + unset($this->indexes_sql); + $this->indexcount = 0; + $sSequenceSQL = ''; + $sTriggerSQL = ''; + + if ($oProc->_GetTableSQL($sTableName, $aTableDef, $sTableSQL, $sSequenceSQL, $sTriggerSQL)) + { + if ($bCreateSequence && $sSequenceSQL != '') + { + if ($DEBUG) { echo '
Making sequence using: ' . $sSequenceSQL; } + $oProc->m_odb->query($sSequenceSQL); + } + + $query = "CREATE TABLE $sTableName ($sTableSQL)"; + + $result = !!($oProc->m_odb->query($query)); + if($result==True) + { + if ($DEBUG) + { + echo '

';
+						print_r($this->indexes_sql);
+						echo '
'; + } + + if(is_array($this->indexes_sql) && count($this->indexes_sql) > 0) + { + foreach($this->indexes_sql as $key => $sIndexSQL) + { + $ix_name = strtoupper($this->m_sIndexPrefix) . "_" . $sTableName . '_' . $key; + $IndexSQL = str_replace(array('__index_name__','__table_name__'), array($ix_name,$sTableName), $sIndexSQL); + $oProc->m_odb->query($IndexSQL); + } + } + + if ($sTriggerSQL != '') + { + if ($DEBUG) + { + echo '
';
+    						print_r($sTriggerSQL);
+    						echo '
'; + } + $oProc->m_odb->query($sTriggerSQL); + } + } + return $result; + } + + return false; + } + } +?> Index: inc/class.schema_proc_pgsql.inc.php =================================================================== RCS file: /cvsroot/phpgwapi/phpgwapi/inc/class.schema_proc_pgsql.inc.php,v retrieving revision 1.5.2.11 diff -u -r1.5.2.11 class.schema_proc_pgsql.inc.php --- inc/class.schema_proc_pgsql.inc.php 26 Feb 2004 20:20:35 -0000 1.5.2.11 +++ inc/class.schema_proc_pgsql.inc.php 17 Jun 2004 09:47:24 -0000 @@ -487,7 +487,7 @@ $this->DropTable($oProc, $aTables, $sTableName); - $oProc->_GetTableSQL($sTableName, $aNewTableDef, $sTableSQL, $sSequenceSQL); + $oProc->_GetTableSQL($sTableName, $aNewTableDef, $sTableSQL, $sSequenceSQL, $sTriggerSQL); if($sSequenceSQL) { $oProc->m_odb->query($sSequenceSQL); @@ -648,11 +648,17 @@ return true; } + function GetTriggerSQL($sTableName, $sColumnNames, &$sTriggerSQL) + { + $sTriggerSQL = ''; + return false; + } + function CreateTable($oProc, $aTables, $sTableName, $aTableDef, $bCreateSequence = true) { global $DEBUG; unset($this->indexes_sql); - if ($oProc->_GetTableSQL($sTableName, $aTableDef, $sTableSQL, $sSequenceSQL)) + if ($oProc->_GetTableSQL($sTableName, $aTableDef, $sTableSQL, $sSequenceSQL, $sTriggerSQL)) { /* create sequence first since it will be needed for default */ if ($bCreateSequence && $sSequenceSQL != '') Index: inc/class.schema_proc_sapdb.inc.php =================================================================== RCS file: /cvsroot/phpgwapi/phpgwapi/inc/class.schema_proc_sapdb.inc.php,v retrieving revision 1.1.2.3 diff -u -r1.1.2.3 class.schema_proc_sapdb.inc.php --- inc/class.schema_proc_sapdb.inc.php 10 Feb 2004 13:51:18 -0000 1.1.2.3 +++ inc/class.schema_proc_sapdb.inc.php 17 Jun 2004 09:47:25 -0000 @@ -485,6 +485,20 @@ } /** + * Get trigger SQL + * + * @param string $sTableName + * @param string $sColumnNames + * @param string $sTriggerSQL + * @return boolean + */ + function GetTriggerSQL($sTableName, $sColumnNames, &$sTriggerSQL) + { + $sTriggerSQL = ''; + return false; + } + + /** * Create table * * @param object $oProc @@ -497,7 +511,7 @@ function CreateTable($oProc, $aTables, $sTableName, $aTableDef, $bCreateSequence = true) { global $DEBUG; - if ($oProc->_GetTableSQL($sTableName, $aTableDef, $sTableSQL, $sSequenceSQL)) + if ($oProc->_GetTableSQL($sTableName, $aTableDef, $sTableSQL, $sSequenceSQL, $sTriggerSQL)) { /* create sequence first since it will be needed for default */ if ($bCreateSequence && $sSequenceSQL != '') Index: inc/class.setup.inc.php =================================================================== RCS file: /cvsroot/phpgwapi/phpgwapi/inc/class.setup.inc.php,v retrieving revision 1.15.2.16 diff -u -r1.15.2.16 class.setup.inc.php --- inc/class.setup.inc.php 21 May 2004 03:07:58 -0000 1.15.2.16 +++ inc/class.setup.inc.php 17 Jun 2004 09:47:25 -0000 @@ -324,6 +324,7 @@ Use old applications table if the currentver is less than 0.9.10pre8, but not if the currentver = '', which probably means new install. */ + $use_appid = false; if($this->alessthanb($setup_info['phpgwapi']['currentver'],'0.9.10pre8') && ($setup_info['phpgwapi']['currentver'] != '')) { $appstbl = 'applications'; Index: inc/class.setup_detection.inc.php =================================================================== RCS file: /cvsroot/phpgwapi/phpgwapi/inc/class.setup_detection.inc.php,v retrieving revision 1.6.2.8 diff -u -r1.6.2.8 class.setup_detection.inc.php --- inc/class.setup_detection.inc.php 10 Feb 2004 13:51:18 -0000 1.6.2.8 +++ inc/class.setup_detection.inc.php 17 Jun 2004 09:47:25 -0000 @@ -45,12 +45,17 @@ $tname = Array(); $GLOBALS['phpgw_setup']->db->Halt_On_Error = 'no'; $tables = $GLOBALS['phpgw_setup']->db->table_names(); - foreach($tables as $key => $val) - { - $tname[] = $val['table_name']; + $newapps = ''; + $oldapps = ''; + if(count($tables) > 0 && is_array($tables)) + { + foreach($tables as $key => $val) + { + $tname[] = $val['table_name']; + } + $newapps = in_array('phpgw_applications',$tname); + $oldapps = in_array('applications',$tname); } - $newapps = in_array('phpgw_applications',$tname); - $oldapps = in_array('applications',$tname); if((count($tables) > 0) && (is_array($tables)) && ($newapps || $oldapps)) { @@ -345,14 +350,16 @@ $langtbl = 'phpgw_lang'; $languagestbl = 'phpgw_languages'; } - $GLOBALS['phpgw_setup']->db->query($q = "SELECT DISTINCT lang FROM $langtbl",__LINE__,__FILE__); - if($GLOBALS['phpgw_setup']->db->num_rows() == 0) + $GLOBALS['phpgw_setup']->db->query($q = "SELECT COUNT(*) as langcount FROM $langtbl",__LINE__,__FILE__); + $GLOBALS['phpgw_setup']->db->next_record(); + if($GLOBALS['phpgw_setup']->db->f('langcount') == 0) { $GLOBALS['phpgw_info']['setup']['header_msg'] = 'Stage 3 (No languages installed)'; return 1; } else { + $GLOBALS['phpgw_setup']->db->query($q = "SELECT DISTINCT lang FROM $langtbl",__LINE__,__FILE__); while(@$GLOBALS['phpgw_setup']->db->next_record()) { $GLOBALS['phpgw_info']['setup']['installed_langs'][$GLOBALS['phpgw_setup']->db->f('lang')] = $GLOBALS['phpgw_setup']->db->f('lang'); Index: inc/class.sql_entity.inc.php =================================================================== RCS file: /cvsroot/phpgwapi/phpgwapi/inc/Attic/class.sql_entity.inc.php,v retrieving revision 1.1.2.12 diff -u -r1.1.2.12 class.sql_entity.inc.php --- inc/class.sql_entity.inc.php 10 Feb 2004 13:51:19 -0000 1.1.2.12 +++ inc/class.sql_entity.inc.php 17 Jun 2004 09:47:27 -0000 @@ -195,7 +195,7 @@ function get_identity() { return ($this->alias != $this->table)? $this->table - .' AS '.$this->alias : $this->table; + .' '.$this->alias : $this->table; } function get_alias() Index: inc/class.sql_oracle.inc.php =================================================================== RCS file: inc/class.sql_oracle.inc.php diff -N inc/class.sql_oracle.inc.php --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ inc/class.sql_oracle.inc.php 17 Jun 2004 09:47:27 -0000 @@ -0,0 +1,37 @@ + + * @copyright Copyright (C) 2003,2004 Free Software Foundation, Inc. http://www.fsf.org/ + * @license http://www.fsf.org/licenses/gpl.html GNU General Public License + * @package phpgwapi + * @subpackage database + * @version $Id$ + */ + + /** + * SQL Generator for Oracle + * + * @package phpgwapi + * @subpackage database + */ + class sql extends sql_ + { + function sql_() + { + } + + function concat($elements) + { + $str = implode(', ', $elements); + return ($str) ? 'concat('.$str.')' : ''; + + } + + function concat_null($elements) + { + $str = implode(', ', sql::safe_null($elements)); + return ($str) ? 'concat('.$str.')' : ''; + } + } +?> Index: setup/tables_current.inc.php =================================================================== RCS file: /cvsroot/phpgwapi/phpgwapi/setup/tables_current.inc.php,v retrieving revision 1.32.2.6.2.20 diff -u -r1.32.2.6.2.20 tables_current.inc.php --- setup/tables_current.inc.php 12 Mar 2004 02:42:18 -0000 1.32.2.6.2.20 +++ setup/tables_current.inc.php 17 Jun 2004 09:47:28 -0000 @@ -566,4 +566,21 @@ ); } } + + /* + * Only for oracle. + * + * ''(2 single quote) it mean same NULL. (not length zero charactor) + * nullable false fields is change to nullable true + */ + if(@$GLOBALS['phpgw_setup']->db->Type == 'oracle') + { + $phpgw_baseline['phpgw_applications']['fd']['app_id']['nullable'] = True; + $phpgw_baseline['phpgw_applications']['fd']['app_name']['nullable'] = True; + $phpgw_baseline['phpgw_applications']['fd']['app_enabled']['nullable'] = True; + $phpgw_baseline['phpgw_applications']['fd']['app_order']['nullable'] = True; + $phpgw_baseline['phpgw_applications']['fd']['app_tables']['nullable'] = True; + $phpgw_baseline['phpgw_applications']['fd']['app_version']['nullable'] = True; + + } ?>