database.php

来自「GForge 3.0 协作开发平台 支持CVS, mailing lists, 」· PHP 代码 · 共 507 行

<?php
//
// SourceForge: Breaking Down the Barriers to Open Source Development
// Copyright 1999-2000 (c) The SourceForge Crew
// http://sourceforge.net
//
// $Id: database.php,v 1.1 2001/05/09 15:59:47 tperdue Exp $
//

/*

This document summarize the changes I made to SourceForge 2.5 code that 
supports Oracle 8 (oci8) database replacing postgres. The change covers
the oracle layer, namely the script database-oci8.php which is an ALPHA
version as claimed in its header, the database schema that is converted
from the postgres schema (file SourceForge.sql), and some PHP code with
SQL statements that have wrong syntax, or with bugs.

Part 1: database-oci8.php
	
	The changes are made to correct the defects/bugs in this ALPHA 
	version of OCI8 support in SourceForge 2.5. I used PHP version 
	4 OCI functions and the working environment includes apache 
	1.3.14 and Sun Solaris 2.6

	Summary of changes:

	. Eliminated all @ in front of the OCI function calls;
	. Added ocicommit($conn) after ociexecute. OCI_COMMIT_ON_SUCCESS
	  should take care of that, but somehow certain INSERT/UPDATE
	  queries do not commit unless I call ocicommit.
	. I use column name instead of column index number to retrieve
	  column values here, though both should work due to the mode
	  OCI_ASSOC+OCI_NUM used in ocifetchinto. The reason I make
	  the change is that column names are used in most of PHP codes
	  of SourceForge (I recall that a few places use numbers though)
	  and I want to make it consistent. There is a defect here that
	  I did not fix: the db_query will return if the value of the
	  first column is null. This would cause problem if it happens
	  that the value is null, not because it is at the end of the
	  rows. The function could return less rows than actual result.
	  I do not have time to figure out a better way to detect if we
	  are at the end of the rows, considering that function db_query
	  applies to SELECT, INSERT, UPDATE, DELETE, all the queries.
	. The ALPHA code uses the returned value of ociexecute as the
	  array index of the returned result rows. Note that the returned
	  value is always 1 (if execution is successful) and if I have
 	  two db_query calls and the result of the second call will 
	  unexpectedly replace that of the first one. I use the returned
	  handle of ociparse as the index instead, since the handle will
	  be a different value for each db_query call. Then in all the 
	  code of calling db_query, it would be nice to free that handle
	  and the result array after the call. I guess it does not matter
	  much since each PHP script runs once as a CGI program and then
	  exit. If the PHP script is permanently loaded in web server
	  after the call, that would cause memory leak problem. I will 
	  try to clean the SourceForge PHP code that calls db_query later.

	. There was a problem with $sys_db_row_pointer, fixed that.

	. Oracle likes UPPER case names. If you try to SELECT rows from
	  an oracle table, most likely you will get a upper case column
	  name, which means you have to retrieve a column value like
	  $x[USER_ID] or $x['USER_ID'] or $x["USER_ID"], and $x[user_id]
	  or $x['user_id'] or $x["user_id"] will not work. I noticed that
	  almost all of the SourceForge codes use lower case names. 
	  A solution could be defining oracle tables like:

		create table users ("user_id" varchar2(20), ...)

	  In this case the field user_id is created in lower case in Oracle.
	  As a result $x["user_id"] is correct.

	  I personally does not like it due to that I have to do SELECT as:

		select "user_id" from users

	  Another solution is to make a copy of the returned rows and
	  replace the upper case names to lower case when copying.
	  Then make both result arrays available to the caller of db_query.
	  I prefer this generic approach even it costs a little bit of
	  memory and cpu. I have not done that though.

	  What I did here is accepting upper case column names and changed
	  all of the places in SourceForge that call db_query, or uses the
	  result arrays. Anyhow I want to find out the places and free the
	  arrays later when I have more time.

Part 2: Database Schema

	The schema enclosed in SF 2.5 is for postgres. I wrote a perl
	script that converts the postgres SQL statements to Oracle.

	The results are 3 separate files. File SourceForge_oci8.sql includes 
	all the tables, sequences, indices. Table session has been renamed 
	to session1, since it is a key word in Oracle DDL. Also all the 
	fields date has been renamed to date1. Corresponding changes need 
	to be made in places that refer to the table and/or fields.

	File Trigger_auto.sql are triggers that used to implement the auto 
	insertion of sequence numbers. Oracle does not allow 

		"bug_id" integer DEFAULT nextval('bug_pk_seq'::text) NOT NULL,

	So the triggers are necessary here. With these triggers, you can
	insert a record without specify the sequence number, and the
	trigger will get the next one and insert for you. If you do want
	to specify sequences in your INSERT/UPDATE queries, the trigger
	will take you number. 

	File Trigger_er.sql is integrity constraint triggers defining the 
	E-R among tables. I did not apply those in my case due to that I 
	would not be able to insert the default rows after that. I will 
	apply the constraints later.

	Many fields are defined as text in postgres and I had trouble 
	deciding what to do with it. There are a lot of limitation in 
	Oracle to LONG and LOB fields. I use varchar2() to replace text 
	even though the maximum bytes for varchar2() is 4000 (?). Most 
	likely we will not run that limit and if any case, I can simply 
	change it to LONG or LOB.

Part 3: Misc Changes Fixing SQL Syntax or Bugs (incomplete)

	File Name	Changes
	------------------------------------------------------------------

	account/login.php	

			added lines to set the hask cookie. Also added 
			the 3rd parameters to session_login_valid. 
			changed table name session to session1


	account/logout.php

			changed table name session to session1

	admin/lastlogins.php

			changed table name session to session1

	admin/search.php

			changed "distinctrow" to "unique"

	admin/userlist.php

			added select before insert statement

	developer/monitor.php

			changed variable user to user_id

	docman/doc_utils.php

			defined count(*) as cnt

	forum/forum.php

			changed table field date to date1

	forum/forum_utils.php

			changed table field date to date1. changed the
			SELECT nextval('forum_thread_seq') to 
			SELECT forum_thread_seq.nextval from dual

	include/User.class

			added select statement to get a user object

	include/cache.php

			comment out flock statements due to access rights.

	include/osdn.php

			image changed to /image. commented out some ads.

	include/session.php

			$allowingpending=0 changed to allowpending. added 
			UPDATE statement to activate pending user accounts.
			changed session to session1

	include/user_home.php

			changed user= to user_id=

	my/diary.php

			added a section to retrieve user object to set
			$G_SESSION
	
	news/news_utils.php

			changed date to date1. changed a few http to https.

	news/submit.php

			changed date to date1

	project/memberlist.php

			changed the join to outer join in query

	softwaremap/trove_list.php

			changed the LEFT JOIN to outer join in Oracle.


*/
$sys_db_oci_commit_mode='OCI_COMMIT_ON_SUCCESS';

/**
 *
 *  Connect to the database
 *  Notice the global vars that must be set up
 *  Sets up a global $conn variable which is used 
 *  in other functions in this library
 *
 */
function db_connect() {
	global $sys_dbuser,$sys_dbpasswd,$conn,$sys_dbname;
	$conn = ocilogon($sys_dbuser,$sys_dbpasswd,$sys_dbname);
	#return $conn;
}

/**
 *
 *  Query the database
 *
 *  @param qstring - SQL statement
 *  @param limit - how many rows do you want returned
 *  @param offset - of matching rows, return only rows starting here
 *
 *
 *  NOTE - the OCI version of this may be somewhat inefficient
 *  for large result sets (hundreds or thousands of rows selected)
 *  However - most queries are returning 25-50 rows
 *
 */

function db_query($qstring,$limit='-1',$offset=0) {

	global $conn,$QUERY_COUNT,$sys_db_results;
        global $sys_db_row_pointer,$sys_db_oci_commit_mode;

	$QUERY_COUNT++;

	$stmt=ociparse($conn,$qstring);

	if (!$stmt) {
		return 0;
	} else {
		if ($limit > 0) {
			if (!$offset || $offset < 0) {
				$offset=0;
			}
		}

		$res=ociexecute($stmt,$sys_db_oci_commit_mode);
                ocicommit($conn);

		if (!$res) {
			return 0;
		} else {
			//if offset, seek to starting point
			//potentially expensive if large offset
			//however there is no data_seek feature AFAICT
                        $col_name  = OCIColumnName($stmt,1);

			$more_data=true;
			if ($offset > 0) {
				for ($i=0; $i<$offset; $i++) {
					//burn them off
					ocifetchinto($stmt,&$x,OCI_ASSOC+OCI_NUM);
					if (!$x[$col_name]) {
						//if no data be returned
						//get out of loop
						$more_data=false;
						break;
					}
				}
			}

			$i=0;
			while ($more_data) {

                                unset($x);
				$ret = ocifetchinto($stmt,&$x,OCI_ASSOC+OCI_NUM);
				if (!$ret) {
                                  //if no data be returned
                                  //get out of loop
                                  $more_data=false;
                                  break;
                                }

				$i++;

				$sys_db_results[$stmt][$i-1]=$x;

				//see if data is being returned && we are 
				//still within the requested $limit
				if (count($x) < 1 || (($limit > 0) && 
                                   ($i >= $limit))) 
                                {
					$more_data=false;
				}
			}
			$sys_db_row_pointer[$stmt]=0;

			return $stmt;
		}
	}
}

/**
 *      db_begin()
 *
 *      begin a transaction
 */
function db_begin() {
	global $sys_db_oci_commit_mode;
	$sys_db_oci_commit_mode='OCI_DEFAULT';
}

/**
 *      db_commit()
 *
 *      commit a transaction
 */
function db_commit() {
	global $sys_db_oci_commit_mode,$conn;
	$sys_db_oci_commit_mode='OCI_COMMIT_ON_SUCCESS';
	return ocicommit($conn);
}

/**
 *      db_rollback()
 *
 *      rollback a transaction
 */
function db_rollback() {
	global $sys_db_oci_commit_mode,$conn;
	$sys_db_oci_commit_mode='OCI_COMMIT_ON_SUCCESS';
	return ocirollback($conn);
}

/**
 *
 *  Returns the number of rows in this result set
 *
 *  @param qhandle query result set handle
 *
 */
function db_numrows($qhandle) {
	global $sys_db_results;
	// return only if qhandle exists, otherwise 0

	if ($qhandle) {

		return count($sys_db_results[$qhandle]);
	} else {
		return 0;
	}
}

/**
 *
 *  Frees a database result properly 
 *
 *  @param qhandle query result set handle
 *
 */

function db_free_result($qhandle) {
	global $sys_db_results;
	unset($sys_db_results[$qhandle]);
	return ocifreestatement($qhandle);
}

/**
 *
 *  Reset is useful for db_fetch_array
 *  sometimes you need to start over
 *
 *  @param qhandle query result set handle
 *  @param row - integer row number
 *
 */

function db_reset_result($qhandle,$row=0) {
	global $sys_db_row_pointer;
	return $sys_db_row_pointer[$qhandle]=$row;
}

/**
 *
 *  Returns a field from a result set
 *
 *  @param qhandle query result set handle
 *  @param row - integer row number
 *  @param field - text field name
 *
 */

function db_result($qhandle,$row,$field) {
	global $sys_db_results;
        $fieldu = strtoupper($field);

	return $sys_db_results[$qhandle][$row][$fieldu];
}

/**
 *
 *  Returns the number of fields in this result set
 *
 *  @param qhandle query result set handle
 *
 */

function db_numfields($lhandle) {
	return ocinumcols($lhandle);
}

/**
 *
 *  Returns the number of rows changed in the last query
 *
 *  @param qhandle - query result set handle
 *  @param fnumber - column number
 *
 */

function db_fieldname($lhandle,$fnumber) {
	   return ocicolumnname($lhandle,$fnumber);
}

/**
 *
 *  Returns the number of rows changed in the last query
 *
 *  @param qhandle query result set handle
 *
 */

function db_affected_rows($qhandle) {
	return ocirowcount($qhandle);
}

/**
 *
 *  Returns an associative array from 
 *  the current row of this database result
 *  Use db_reset_result to seek a particular row
 *
 *  @param qhandle query result set handle
 *
 */

function db_fetch_array($qhandle) {
	global $sys_db_results,$sys_db_row_pointer;
        $row = $sys_db_row_pointer[$qhandle];
        $sys_db_row_pointer[$qhandle] = $sys_db_row_pointer[$qhandle] + 1;
        //$sys_db_row_pointer = $sys_db_row_pointer + 1;
	return $sys_db_results[$qhandle][$row];
}

/**
 *
 *  Returns the last primary key from an insert
 *
 *  @param qhandle query result set handle
 *  @param table_name is the name of the table you inserted into
 *  @param pkey_field_name is the field name of the primary key
 *
 */

function db_insertid($qhandle,$table_name,$pkey_field_name) {
	$res=db_query("SELECT max($pkey_field_name) AS id FROM $table_name");
	if ($res && db_numrows($res) > 0) {
		return db_result($res,0,'id');
	} else {
		return 0;
	}
}

/**
 *
 *  Returns the last error from the database
 *
 */

function db_error() {
	global $conn;
	$err= ocierror($conn);
	if ($err) {
		return $err['message'];
	} else {
		return false;
	}
}

?>