3 // SourceForge: Breaking Down the Barriers to Open Source Development
4 // Copyright 1999-2000 (c) The SourceForge Crew
5 // http://sourceforge.net
12 This document summarize the changes I made to SourceForge 2.5 code that
13 supports Oracle 8 (oci8) database replacing postgres. The change covers
14 the oracle layer, namely the script database-oci8.php which is an ALPHA
15 version as claimed in its header, the database schema that is converted
16 from the postgres schema (file SourceForge.sql), and some PHP code with
17 SQL statements that have wrong syntax, or with bugs.
19 Part 1: database-oci8.php
21 The changes are made to correct the defects/bugs in this ALPHA
22 version of OCI8 support in SourceForge 2.5. I used PHP version
23 4 OCI functions and the working environment includes apache
24 1.3.14 and Sun Solaris 2.6
28 . Eliminated all @ in front of the OCI function calls;
29 . Added ocicommit($conn) after ociexecute. OCI_COMMIT_ON_SUCCESS
30 should take care of that, but somehow certain INSERT/UPDATE
31 queries do not commit unless I call ocicommit.
32 . I use column name instead of column index number to retrieve
33 column values here, though both should work due to the mode
34 OCI_ASSOC+OCI_NUM used in ocifetchinto. The reason I make
35 the change is that column names are used in most of PHP codes
36 of SourceForge (I recall that a few places use numbers though)
37 and I want to make it consistent. There is a defect here that
38 I did not fix: the db_query will return if the value of the
39 first column is null. This would cause problem if it happens
40 that the value is null, not because it is at the end of the
41 rows. The function could return less rows than actual result.
42 I do not have time to figure out a better way to detect if we
43 are at the end of the rows, considering that function db_query
44 applies to SELECT, INSERT, UPDATE, DELETE, all the queries.
45 . The ALPHA code uses the returned value of ociexecute as the
46 array index of the returned result rows. Note that the returned
47 value is always 1 (if execution is successful) and if I have
48 two db_query calls and the result of the second call will
49 unexpectedly replace that of the first one. I use the returned
50 handle of ociparse as the index instead, since the handle will
51 be a different value for each db_query call. Then in all the
52 code of calling db_query, it would be nice to free that handle
53 and the result array after the call. I guess it does not matter
54 much since each PHP script runs once as a CGI program and then
55 exit. If the PHP script is permanently loaded in web server
56 after the call, that would cause memory leak problem. I will
57 try to clean the SourceForge PHP code that calls db_query later.
59 . There was a problem with $sys_db_row_pointer, fixed that.
61 . Oracle likes UPPER case names. If you try to SELECT rows from
62 an oracle table, most likely you will get a upper case column
63 name, which means you have to retrieve a column value like
64 $x[USER_ID] or $x['USER_ID'] or $x["USER_ID"], and $x[user_id]
65 or $x['user_id'] or $x["user_id"] will not work. I noticed that
66 almost all of the SourceForge codes use lower case names.
67 A solution could be defining oracle tables like:
69 create table users ("user_id" varchar2(20), ...)
71 In this case the field user_id is created in lower case in Oracle.
72 As a result $x["user_id"] is correct.
74 I personally does not like it due to that I have to do SELECT as:
76 select "user_id" from users
78 Another solution is to make a copy of the returned rows and
79 replace the upper case names to lower case when copying.
80 Then make both result arrays available to the caller of db_query.
81 I prefer this generic approach even it costs a little bit of
82 memory and cpu. I have not done that though.
84 What I did here is accepting upper case column names and changed
85 all of the places in SourceForge that call db_query, or uses the
86 result arrays. Anyhow I want to find out the places and free the
87 arrays later when I have more time.
89 Part 2: Database Schema
91 The schema enclosed in SF 2.5 is for postgres. I wrote a perl
92 script that converts the postgres SQL statements to Oracle.
94 The results are 3 separate files. File SourceForge_oci8.sql includes
95 all the tables, sequences, indices. Table session has been renamed
96 to session1, since it is a key word in Oracle DDL. Also all the
97 fields date has been renamed to date1. Corresponding changes need
98 to be made in places that refer to the table and/or fields.
100 File Trigger_auto.sql are triggers that used to implement the auto
101 insertion of sequence numbers. Oracle does not allow
103 "bug_id" integer DEFAULT nextval('bug_pk_seq'::text) NOT NULL,
105 So the triggers are necessary here. With these triggers, you can
106 insert a record without specify the sequence number, and the
107 trigger will get the next one and insert for you. If you do want
108 to specify sequences in your INSERT/UPDATE queries, the trigger
109 will take you number.
111 File Trigger_er.sql is integrity constraint triggers defining the
112 E-R among tables. I did not apply those in my case due to that I
113 would not be able to insert the default rows after that. I will
114 apply the constraints later.
116 Many fields are defined as text in postgres and I had trouble
117 deciding what to do with it. There are a lot of limitation in
118 Oracle to LONG and LOB fields. I use varchar2() to replace text
119 even though the maximum bytes for varchar2() is 4000 (?). Most
120 likely we will not run that limit and if any case, I can simply
121 change it to LONG or LOB.
123 Part 3: Misc Changes Fixing SQL Syntax or Bugs (incomplete)
126 ------------------------------------------------------------------
130 added lines to set the hask cookie. Also added
131 the 3rd parameters to session_login_valid.
132 changed table name session to session1
137 changed table name session to session1
141 changed table name session to session1
145 changed "distinctrow" to "unique"
149 added select before insert statement
151 developer/monitor.php
153 changed variable user to user_id
157 defined count(*) as cnt
161 changed table field date to date1
163 forum/forum_utils.php
165 changed table field date to date1. changed the
166 SELECT nextval('forum_thread_seq') to
167 SELECT forum_thread_seq.nextval from dual
171 added select statement to get a user object
175 comment out flock statements due to access rights.
179 image changed to /image. commented out some ads.
183 $allowingpending=0 changed to allowpending. added
184 UPDATE statement to activate pending user accounts.
185 changed session to session1
187 include/user_home.php
189 changed user= to user_id=
193 added a section to retrieve user object to set
198 changed date to date1. changed a few http to https.
202 changed date to date1
204 project/memberlist.php
206 changed the join to outer join in query
208 softwaremap/trove_list.php
210 changed the LEFT JOIN to outer join in Oracle.
214 $sys_db_oci_commit_mode='OCI_COMMIT_ON_SUCCESS';
218 * Connect to the database
219 * Notice the global vars that must be set up
220 * Sets up a global $conn variable which is used
221 * in other functions in this library
224 function db_connect() {
225 global $sys_dbuser,$sys_dbpasswd,$conn,$sys_dbname;
226 $conn = ocilogon($sys_dbuser,$sys_dbpasswd,$sys_dbname);
234 * @param qstring - SQL statement
235 * @param limit - how many rows do you want returned
236 * @param offset - of matching rows, return only rows starting here
239 * NOTE - the OCI version of this may be somewhat inefficient
240 * for large result sets (hundreds or thousands of rows selected)
241 * However - most queries are returning 25-50 rows
245 function db_query($qstring,$limit='-1',$offset=0) {
247 global $conn,$QUERY_COUNT,$sys_db_results;
248 global $sys_db_row_pointer,$sys_db_oci_commit_mode;
252 $stmt=ociparse($conn,$qstring);
258 if (!$offset || $offset < 0) {
263 $res=ociexecute($stmt,$sys_db_oci_commit_mode);
269 //if offset, seek to starting point
270 //potentially expensive if large offset
271 //however there is no data_seek feature AFAICT
272 $col_name = OCIColumnName($stmt,1);
276 for ($i=0; $i<$offset; $i++) {
278 ocifetchinto($stmt,&$x,OCI_ASSOC+OCI_NUM);
279 if (!$x[$col_name]) {
280 //if no data be returned
292 $ret = ocifetchinto($stmt,&$x,OCI_ASSOC+OCI_NUM);
294 //if no data be returned
302 $sys_db_results[$stmt][$i-1]=$x;
304 //see if data is being returned && we are
305 //still within the requested $limit
306 if (count($x) < 1 || (($limit > 0) &&
312 $sys_db_row_pointer[$stmt]=0;
322 * begin a transaction
324 function db_begin() {
325 global $sys_db_oci_commit_mode;
326 $sys_db_oci_commit_mode='OCI_DEFAULT';
332 * commit a transaction
334 function db_commit() {
335 global $sys_db_oci_commit_mode,$conn;
336 $sys_db_oci_commit_mode='OCI_COMMIT_ON_SUCCESS';
337 return ocicommit($conn);
343 * rollback a transaction
345 function db_rollback() {
346 global $sys_db_oci_commit_mode,$conn;
347 $sys_db_oci_commit_mode='OCI_COMMIT_ON_SUCCESS';
348 return ocirollback($conn);
353 * Returns the number of rows in this result set
355 * @param qhandle query result set handle
358 function db_numrows($qhandle) {
359 global $sys_db_results;
360 // return only if qhandle exists, otherwise 0
364 return count($sys_db_results[$qhandle]);
372 * Frees a database result properly
374 * @param qhandle query result set handle
378 function db_free_result($qhandle) {
379 global $sys_db_results;
380 unset($sys_db_results[$qhandle]);
381 return ocifreestatement($qhandle);
386 * Reset is useful for db_fetch_array
387 * sometimes you need to start over
389 * @param qhandle query result set handle
390 * @param row - integer row number
394 function db_reset_result($qhandle,$row=0) {
395 global $sys_db_row_pointer;
396 return $sys_db_row_pointer[$qhandle]=$row;
401 * Returns a field from a result set
403 * @param qhandle query result set handle
404 * @param row - integer row number
405 * @param field - text field name
409 function db_result($qhandle,$row,$field) {
410 global $sys_db_results;
411 $fieldu = strtoupper($field);
413 return $sys_db_results[$qhandle][$row][$fieldu];
418 * Returns the number of fields in this result set
420 * @param qhandle query result set handle
424 function db_numfields($lhandle) {
425 return ocinumcols($lhandle);
430 * Returns the number of rows changed in the last query
432 * @param qhandle - query result set handle
433 * @param fnumber - column number
437 function db_fieldname($lhandle,$fnumber) {
438 return ocicolumnname($lhandle,$fnumber);
443 * Returns the number of rows changed in the last query
445 * @param qhandle query result set handle
449 function db_affected_rows($qhandle) {
450 return ocirowcount($qhandle);
455 * Returns an associative array from
456 * the current row of this database result
457 * Use db_reset_result to seek a particular row
459 * @param qhandle query result set handle
463 function db_fetch_array($qhandle) {
464 global $sys_db_results,$sys_db_row_pointer;
465 $row = $sys_db_row_pointer[$qhandle];
466 $sys_db_row_pointer[$qhandle] = $sys_db_row_pointer[$qhandle] + 1;
467 //$sys_db_row_pointer = $sys_db_row_pointer + 1;
468 return $sys_db_results[$qhandle][$row];
473 * Returns the last primary key from an insert
475 * @param qhandle query result set handle
476 * @param table_name is the name of the table you inserted into
477 * @param pkey_field_name is the field name of the primary key
481 function db_insertid($qhandle,$table_name,$pkey_field_name) {
482 $res=db_query("SELECT max($pkey_field_name) AS id FROM $table_name");
483 if ($res && db_numrows($res) > 0) {
484 return db_result($res,0,'id');
492 * Returns the last error from the database
496 function db_error() {
498 $err= ocierror($conn);
500 return $err['message'];
projects / fusionforge / fusionforge.git / blob