3 // SourceForge: Breaking Down the Barriers to Open Source Development
4 // Copyright 1999-2000 (c) The SourceForge Crew
5 // http://sourceforge.net
12 This is the Oracle 8 version of our
13 database connection/querying layer.
15 It has been seriously overhauled and tested to create
16 this working version by our friends at http://www.pssonline.com.
21 $sys_db_oci_commit_mode=OCI_COMMIT_ON_SUCCESS;
25 * Connect to the database
26 * Notice the global vars that must be set up
27 * Sets up a global $conn variable which is used
28 * in other functions in this library
32 function db_connect() {
33 global $sys_dbhost,$sys_dbuser,$sys_dbpasswd,$conn,$sys_dbname;
34 $conn = ocilogon($sys_dbuser,$sys_dbpasswd,$sys_dbname);
39 * Does any preprocessing require on the sql to convert to ORACLE sql syntax
43 function db_query_preprocess($qstring) {
44 $qstring = db_replace_words($qstring);
45 $qstring = db_convert_outer_joins($qstring);
46 $qstring = db_convert_nextvals($qstring);
52 * Replaces oracle reserved words with sf_[reserved_word]
56 function db_replace_words($qstring) {
57 /* replace oracle reserved words in $qstring with sf_[reserved_word] */
58 $qstring = str_replace('session ','sf_session ',$qstring);
59 $qstring = str_replace('session.','sf_session.',$qstring);
60 $qstring = str_replace('session,','sf_session,',$qstring);
61 $qstring = str_replace('.date','.sf_date',$qstring);
62 $qstring = str_replace(',date ',',sf_date ',$qstring);
63 $qstring = str_replace(',date,',',sf_date,',$qstring);
64 $qstring = str_replace(' date ',' sf_date ',$qstring);
65 $qstring = str_replace(';','',$qstring); // a semicolon at end of oci sql causes errors
72 * Replaces "SELECT nexval('sequence_name')" with "SELECT sequence_name.nextval FROM dual"
76 function db_convert_nextvals($qstring) {
77 if (stristr($qstring,'nextval')) {
78 $seq_array = explode("'",$qstring);
79 $qstring = "SELECT " . $seq_array[1] . ".nextval FROM dual";
85 How are ANSI SQL outer joins migrated to Oracle outer joins?
86 Outer joins may not be converted correctly.
88 Convert an ANSI SQL inner join to an Oracle join as illustrated in the following code example:
89 select * from a inner join b on a.col1=b.col2;
90 select * from a , b where a.col1=b.col2;
91 Convert a left join and right join in ANSI SQL to Oracle as indicated below:
92 select * from a left join b on a.col1=b.col2;
93 // remember the table to the right of left join and add (+) to it's columns.
94 select * from a, b where a.col1=b.col2(+);
95 select * from a right join b on a.col1=b.col2;
96 // remember the table to the left of right join and add (+) to it's columns.
97 select * from a, b where a.col1(+)=b.col2;
99 How can I convert full outer joins from ANSI SQL Server to Oracle?
100 There are several ways to express a full outer join within Oracle.
101 For example, in the following query the predicate a.col1 (+) = b.col1 (+)
102 is pseudo-Oracle notation for a full outer join although this predicate
103 is not currently supported in Oracle:
105 select a.col2 acol2, a.col1 acol1, b.col1 bcol1, b.col2 bcol2
106 from a,b where a.col1 (+) = b.col1 (+);
107 The most efficient way of executing this query is to use a UNION ALL of
108 a left outer join and a right outer join, with an additional predicate,
109 as illustrated below:
110 select a.col2 acol2, a.col1 acol1, b.col1 bcol1, b.col2 bcol2
111 from a,b where a.col1=b.col1(+)
113 select a.col2 acol2, a.col1 acol1, b.col1 bcol1, b.col2 bcol2
114 from a,b where a.col1(+)=b.col1 and a.col1 is null;
118 Right now, only "LEFT JOIN" and "RIGHT JOIN" syntax are converted.
119 And it has only been tested on the sql statements found in
120 SourceForge version 2.5. It may need to be modified for future
124 function db_convert_outer_joins($qstring) {
125 if ( stristr($qstring,"SELECT") && stristr($qstring,"JOIN") ) {
126 $select = substr($qstring,0,strpos(strtoupper($qstring),"FROM")-1);
127 $from = stristr($qstring,"FROM");
128 $from = substr($from,0,strpos(strtoupper($from),"WHERE")-1);
129 if (stristr($qstring,"GROUP BY")) {
130 $where = stristr($qstring,"WHERE");
131 $where = substr($where,0,strpos(strtoupper($where),"GROUP BY")-1);
132 $group_order = stristr($qstring,"GROUP BY");
134 else if (stristr($qstring,"ORDER BY")) {
135 $where = stristr($qstring,"WHERE");
136 $where = substr($where,0,strpos(strtoupper($where),"ORDER BY")-1);
137 $group_order = stristr($qstring,"ORDER BY");
140 $where = stristr($qstring,"WHERE");
142 // loop through each TABLE [ALIAS] in the from clause
143 $from_array = explode(",",substr($from,strlen("FROM ")));
145 for ($i=0; $i<count($from_array); $i++) {
146 if ( stristr($from_array[$i],"JOIN") ) {
147 if ( stristr($from_array[$i],"LEFT") ) {
148 $join_str = "LEFT JOIN";
151 else if ( stristr($from_array[$i],"RIGHT") ) {
152 $join_str = "RIGHT JOIN";
155 $left_table = trim(substr($from_array[$i],0,strpos(strtoupper($from_array[$i]),$join_str)));
156 $right_table = substr(stristr($from_array[$i],$join_str),strlen($join_str));
157 $right_table = trim(substr($right_table,0,strpos(strtoupper($right_table),"USING")));
158 $from = $from . $left_table . ', ' . $right_table;
159 $left_table_alias = strrchr(trim($left_table),' ');
160 $right_table_alias = strrchr(trim($right_table),' ');
161 if ( strlen($left_table_alias) == 0 ) {
162 $left_table_alias = $left_table;
165 $left_table = strchr(trim($left_table),' ');
167 if ( strlen($right_table_alias) == 0 ) {
168 $right_table_alias = $right_table;
171 $right_table = strrchr(trim($right_table),' ');
173 $using_column = trim(substr(stristr($from_array[$i],"USING"),strlen("USING")));
174 $using_column = substr($using_column,1,-1); // strip parens
176 $where = $where . "\nAND " .
177 $left_table_alias . '.' . $using_column . ' = ' .
178 $right_table_alias . '.' . $using_column . ' (+) ';
181 $where = $where . "\nAND " .
182 $left_table_alias . '.' . $using_column . ' (+) = ' .
183 $right_table_alias . '.' . $using_column . ' ';
187 $from = $from . $from_array[$i] . ' ';
190 return $select.' '.$from.' '.$where.' '.$group_order;
202 * @param qstring - SQL statement
203 * @param limit - how many rows do you want returned
204 * @param offset - of matching rows, return only rows starting here
207 * NOTE - the oci version of this may be somewhat inefficient
208 * for large result sets (hundreds or thousands of rows selected)
209 * However - most queries are returning 25-50 rows
213 function db_query($qstring,$limit='-1',$offset=0) {
215 global $QUERY_COUNT,$conn,$sys_db_oci_commit_mode,
216 $sys_db_results,$sys_db_row_pointer,$sys_db_fieldnames;
220 $qstring = db_query_preprocess($qstring);
222 $stmt=ociparse($conn,$qstring);
225 echo $qstring.'<br />';
230 if (!$offset || $offset < 0) {
235 $res = ociexecute($stmt,$sys_db_oci_commit_mode);
238 echo $qstring.'<br />';
241 else if ( strcmp(ocistatementtype($stmt),"SELECT")==0 ) {
243 //store fieldnames for use in db_fieldname and db_numfields
244 for ($i=0;$i<ocinumcols($stmt);$i++) {
245 $sys_db_fieldnames[$stmt][$i] = ocicolumnname($stmt,$i+1);
248 //if offset, seek to starting point
249 //potentially expensive if large offset
250 //however there is no data_seek feature AFAICT
253 for ($i=0; $i<$offset; $i++) {
255 if (!ocifetchinto($stmt,$res)) {
256 //if no data be returned
264 //store the data into $sys_db_results[$stmt]
265 //this is needed since there is the possiblity of calling
266 //db_result($qhandle,$row,$field) with any $row value
267 //db_result and db_fetch_array could be rewritten to
268 //eliminate the need for fetching all of the data up front
271 $more = ocifetchinto($stmt,$res,OCI_RETURN_NULLS+OCI_RETURN_LOBS);
273 //see if data is being returned && we are
274 //still within the requested $limit
275 if ( !$more || (($limit >= 0) && ($row >= $limit))) {
279 //populate sys_db_results with an array that can be indexed
280 //by field number or field name
281 for ($col=0;$col<db_numfields($stmt);$col++) {
282 $sys_db_results[$stmt][$row][$col] = $res[$col];
283 $fieldname = strtolower($sys_db_fieldnames[$stmt][$col]);
284 $sys_db_results[$stmt][$row][$fieldname] = $res[$col];
289 $sys_db_row_pointer[$stmt]=0;
302 * begin a transaction
304 function db_begin() {
305 global $sys_db_oci_commit_mode;
306 $sys_db_oci_commit_mode=OCI_DEFAULT;
312 * commit a transaction
314 function db_commit() {
315 global $sys_db_oci_commit_mode,$conn;
316 $sys_db_oci_commit_mode=OCI_COMMIT_ON_SUCCESS;
317 return ocicommit($conn);
323 * rollback a transaction
325 function db_rollback() {
326 global $sys_db_oci_commit_mode,$conn;
327 $sys_db_oci_commit_mode=OCI_COMMIT_ON_SUCCESS;
328 return ocirollback($conn);
333 * Returns the number of rows in this result set
335 * param qhandle query result set handle
338 function db_numrows($qhandle) {
339 global $sys_db_results;
340 // return only if qhandle exists, otherwise 0
342 return count($sys_db_results[$qhandle]);
350 * Frees a database result properly
352 * param qhandle query result set handle
356 function db_free_result($qhandle) {
357 global $sys_db_results;
359 unset($sys_db_results[$qhandle]);
360 unset($sys_db_row_pointer[$qhandle]);
361 unset($sys_db_fieldnames[$qhandle]);
362 return ocifreestatement($qhandle);
371 * Reset is useful for db_fetch_array
372 * sometimes you need to start over
374 * param qhandle query result set handle
375 * param row - integer row number
379 function db_reset_result($qhandle,$row=0) {
380 global $sys_db_row_pointer;
381 return $sys_db_row_pointer[$qhandle]=$row;
386 * Returns a field from a result set
388 * param qhandle query result set handle
389 * param row - integer row number
390 * param field - text field name
394 function db_result($qhandle,$row,$field) {
395 global $sys_db_results;
396 return $sys_db_results[$qhandle][$row][$field];
401 * Returns the number of fields in this result set
403 * param qhandle query result set handle
407 function db_numfields($qhandle) {
408 global $sys_db_fieldnames;
409 // return only if qhandle exists, otherwise 0
411 return count($sys_db_fieldnames[$qhandle]);
419 * Returns the number of rows changed in the last query
421 * param qhandle - query result set handle
422 * param fnumber - column number
426 function db_fieldname($lhandle,$fnumber) {
427 global $sys_db_fieldnames;
428 return $sys_db_fieldnames[$lhandle][$fnumber];
433 * Returns the number of rows changed in the last query
435 * param qhandle query result set handle
439 function db_affected_rows($qhandle) {
440 return ocirowcount($qhandle);
445 * Returns an associative array from
446 * the current row of this database result
447 * Use db_reset_result to seek a particular row
449 * param qhandle query result set handle
453 function db_fetch_array($qhandle) {
454 global $sys_db_results,$sys_db_row_pointer;
455 if ($sys_db_row_pointer[$qhandle] < db_numrows($qhandle)) {
456 $result = $sys_db_results[$qhandle][$sys_db_row_pointer[$qhandle]];
457 $sys_db_row_pointer[$qhandle]++;
467 * Returns the last primary key from an insert
469 * param qhandle query result set handle
470 * param table_name is the name of the table you inserted into
471 * param pkey_field_name is the field name of the primary key
475 function db_insertid($qhandle,$table_name,$pkey_field_name) {
476 $qstring = db_query_preprocess("SELECT max($pkey_field_name) AS id FROM $table_name");
477 $res=db_query($qstring);
478 if ($res && (db_numrows($res) > 0) ) {
479 return db_result($res,0,0);
487 * Returns the last error from the database
491 function db_error() {
494 $err= ocierror($stmt);
497 $err= ocierror($conn);
503 return $err['message'];
projects / fusionforge / fusionforge.git / blob