#native_company# #native_desc#
#native_cta#

ORACLE Session handaling mechanism.

By Ian Matyssik
on January 18, 2003

Version: 1.2

Type: Full Script

Category: Databases

License: BSD License

Description: If you want to store you sessions in the oracle DB and speed it up with Packages and stored procedures. Also locking is handaled correctly, not like other implimintations. Emulates file locking and implise correct session bihavier.

/*


 Author: Ian Matyssik. 2003/01/19


*/


 
 /*
  Oracle script you will need to handle Sessions in ORACLE DB.
 */

  DROP INDEX oci_sess_expires_idx;
  DROP TABLE oci_sess;

  CREATE TABLE oci_sess (
    session_id char(32) PRIMARY KEY NOT NULL,
    expires NUMBER,
    data CLOB
  );

  CREATE INDEX oci_sess_expires_idx ON oci_sess (expires);

  CREATE OR REPLACE PACKAGE php_sess AS 

	procedure sess_open ( sess_id IN CHAR, ro_trans IN BOOLEAN, sess_exp IN NUMBER );
	procedure sess_close ( ro_trans IN BOOLEAN );
	procedure sess_read ( sess_id IN CHAR, sess_data OUT CLOB );
	procedure sess_write ( sess_id IN CHAR, sess_data IN CLOB , sess_exp IN NUMBER );
	procedure sess_destroy ( sess_id IN CHAR );
	procedure sess_gc ( sess_exp IN NUMBER );

  END php_sess;
/
show errors;

  CREATE OR REPLACE PACKAGE BODY php_sess AS 

	/* Private Variables */
	l_rowid ROWID := NULL;
	/* Private DATA vars */
	l_sid OCI_SESS.session_id%TYPE;
	l_exp OCI_SESS.expires%TYPE;
	l_dtd OCI_SESS.data%TYPE;
	l_ro_trans BOOLEAN := FALSE;
	l_sess_open BOOLEAN := FALSE;


	/* Public Procedures. */
	-- Procedure to open "SESSION" session.
	procedure sess_open ( sess_id IN CHAR, ro_trans IN BOOLEAN, sess_exp IN NUMBER ) IS
	BEGIN
	  l_sess_open := TRUE;
	  l_ro_trans := ro_trans;
	  IF ro_trans THEN
		SELECT session_id,expires,data,rowid INTO l_sid,l_exp,l_dtd,l_rowid FROM OCI_SESS 
		WHERE session_id = sess_id AND expires > sess_exp AND ROWNUM <= 1;
	  ELSE
		SELECT session_id,expires,data,rowid INTO l_sid,l_exp,l_dtd,l_rowid FROM OCI_SESS 
		WHERE session_id = sess_id AND expires > sess_exp AND ROWNUM <= 1 FOR UPDATE;
	  END IF;
	EXCEPTION
	WHEN NO_DATA_FOUND THEN
		-- We found no data, so guess to make new one. Just to be safe
		-- we will delete first and then insert.
		DELETE FROM OCI_SESS WHERE session_id = sess_id;
		INSERT INTO OCI_SESS (session_id, expires, data) 
		VALUES ( sess_id, (sess_exp + 1440), EMPTY_CLOB() ) RETURNING rowid INTO l_rowid;
	  IF ro_trans THEN
		SELECT session_id,expires,data INTO l_sid,l_exp,l_dtd FROM OCI_SESS 
		WHERE rowid = l_rowid;
		COMMIT;
		-- No matter if we are in a read only session we will commit to reduce
		-- risk later on.
	  ELSE
		SELECT session_id,expires,data INTO l_sid,l_exp,l_dtd FROM OCI_SESS 
		WHERE rowid = l_rowid FOR UPDATE;
	  END IF;
	WHEN OTHERS THEN
		RAISE;
	RETURN;
	END sess_open;

	-- Procedure to close "SESSION" session.
	procedure sess_close ( ro_trans IN BOOLEAN ) IS
	BEGIN
	IF l_sess_open THEN
	  IF l_ro_trans THEN
		RETURN;
	  ELSE
		COMMIT;
	  END IF;
	RETURN;
	ELSE
	RETURN;
	END IF;
	END sess_close;

	-- Procedure to read "SESSION" data.
	procedure sess_read ( sess_id IN CHAR, sess_data OUT CLOB ) IS
	BEGIN
	IF l_sess_open THEN
	IF sess_id = l_sid THEN
		sess_data := l_dtd;
	ELSE
	  IF l_ro_trans THEN
		SELECT data INTO sess_data FROM OCI_SESS WHERE session_id = sess_id;
	  ELSE
		SELECT data INTO sess_data FROM OCI_SESS WHERE session_id = sess_id FOR UPDATE;
	  END IF;
	END IF;
	RETURN;
	ELSE
	RETURN;
	END IF;
	END sess_read;

	-- Procedure to write "SESSION" data.
	procedure sess_write ( sess_id IN CHAR, sess_data IN CLOB , sess_exp IN NUMBER ) IS
	BEGIN
	IF l_sess_open THEN
	  IF l_ro_trans THEN
		RETURN;
	  ELSE
		UPDATE OCI_SESS SET expires = sess_exp, data = sess_data WHERE rowid = l_rowid;
	  END IF;
	RETURN;
	ELSE
	RETURN;
	END IF;
	END sess_write;

	-- Procedure to destroy "SESSION" data.
	procedure sess_destroy ( sess_id IN CHAR ) IS
		/*PRAGMA*/
		-- PRAGMA AUTONOMOUS_TRANSACTION;
	BEGIN
	  IF l_ro_trans THEN
		RETURN;
	  ELSE
		DELETE FROM OCI_SESS WHERE session_id = sess_id;
		COMMIT;
		l_ro_trans := TRUE;
	  END IF;
	RETURN;
	END sess_destroy;

	-- Procedure to cleanup expired "SESSION" data.
	-- Some times it can take long time so that is why we background it.
	procedure sess_gc ( sess_exp IN NUMBER ) IS
		/*PRAGMA*/
		PRAGMA AUTONOMOUS_TRANSACTION;
		l_job_n NUMBER;
		l_job VARCHAR2(2000);
	BEGIN
		l_job := 'BEGIN DELETE FROM OCI_SESS WHERE expires <= '||sess_exp||'; COMMIT; END;';
		DBMS_JOB.SUBMIT(l_job_n, l_job);
		COMMIT;
	RETURN;
	END sess_gc;

  END php_sess;
/
show errors;

//------------------------------------------------ CUT --------------------------------------------

<?

// Set this settings corectly.
define("___OCI_SESS_DBUSER", 'php');
define("___OCI_SESS_DBPASS", 'php');
define("___OCI_SESS_DBNAME", 'oradb');

function ___oci_session_open($save_path, $name)
{
  global $___oci_session_db, $___oci_session_ro_trans;
  $expires = time();
  if(!$___oci_session_db = @OCIPLogon(___OCI_SESS_DBUSER,___OCI_SESS_DBPASS,___OCI_SESS_DBNAME)) {
  	return False;
  }
  if($___oci_session_ro_trans) {
  $stmt = @OCIParse($___oci_session_db, "BEGIN PHP_SESS.SESS_OPEN(:SESS_ID, TRUE, :SESS_EXP); END;");
  } else {
  $stmt = @OCIParse($___oci_session_db, "BEGIN PHP_SESS.SESS_OPEN(:SESS_ID, FALSE, :SESS_EXP); END;");
  }
  $sess_id = session_id();
  @OCIBindByName($stmt,":SESS_ID",$sess_id,-1);
  @OCIBindByName($stmt,":SESS_EXP",$expires,-1);
  if(@OCIExecute($stmt, OCI_DEFAULT)) {
	@OCIFreeStatement($stmt);
	return True;
  } else {
	@OCIRollback($stmt);
	@OCIFreeStatement($stmt);
	return False;
  }

}

function ___oci_session_close()
{
  global $___oci_session_db, $___oci_session_ro_trans;
  if($___oci_session_ro_trans) {
  $stmt = @OCIParse($___oci_session_db, "BEGIN PHP_SESS.SESS_CLOSE(TRUE); END;");
  } else {
  $stmt = @OCIParse($___oci_session_db, "BEGIN PHP_SESS.SESS_CLOSE(FALSE); END;");
  }
  if(@OCIExecute($stmt, OCI_DEFAULT)) {
	@OCIFreeStatement($stmt);
	return True;
  } else {
	@OCIRollback($stmt);
	@OCIFreeStatement($stmt);
	return False;
  }
}    

function ___oci_session_read($session_id)
{
  global $___oci_session_db;
  $d_data = @OCINewDescriptor($___oci_session_db,OCI_D_LOB);
  $stmt = @OCIParse($___oci_session_db, "BEGIN PHP_SESS.SESS_READ(:SESS_ID,:SESS_DATA); END;");
  @OCIBindByName($stmt,":SESS_ID",$session_id,-1);
  @OCIBindByName($stmt,":SESS_DATA",$d_data,-1, @OCI_B_CLOB);
  if(@OCIExecute($stmt, OCI_DEFAULT)) {
	$data = @$d_data->load();
	@$d_data->free();
	@OCIFreeStatement($stmt);
	return (string)$data;
  } else {
	@$d_data->free();
	@OCIRollback($stmt);
	@OCIFreeStatement($stmt);
	return False;
  }
}

function ___oci_session_write($session_id, $data)
{
  global $___oci_session_db;
  $expires = time() + get_cfg_var("session.gc_maxlifetime");
  //Prepare Statement.
  $stmt = @OCIParse($___oci_session_db, "BEGIN PHP_SESS.SESS_WRITE(:SESS_ID,:SESS_DATA,:SESS_EXP); END;");
  //Bind
  @OCIBindByName($stmt,":SESS_ID",$session_id,-1);
  @OCIBindByName($stmt,":SESS_DATA",$data,-1);
  @OCIBindByName($stmt,":SESS_EXP",$expires,-1);
  if(@OCIExecute($stmt, OCI_DEFAULT)) {
	@OCIFreeStatement($stmt);
	return True;
  } else {
	@OCIRollback($stmt);
	@OCIFreeStatement($stmt);
	return False;
  }
}

function ___oci_session_destroy($session_id)
{
  global $___oci_session_db;
  //Prepare Statement.
  $stmt = @OCIParse($___oci_session_db, "BEGIN PHP_SESS.SESS_DESTROY(:SESS_ID); END;");
  //Bind
  @OCIBindByName($stmt,":SESS_ID",$session_id,-1);
  if(@OCIExecute($stmt, OCI_DEFAULT)) {
	@OCIFreeStatement($stmt);
	return True;
  } else {
	@OCIRollback($stmt);
	@OCIFreeStatement($stmt);
	return False;
  }
}

function ___oci_session_gc($gc_maxlifetime)
{
  global $___oci_session_db;
  $ex_time = time();
  //Prepare Statement.
  $stmt = @OCIParse($___oci_session_db, "BEGIN PHP_SESS.SESS_GC(:SESS_EXP); END;" );
  //Bind
  @OCIBindByName($stmt,":SESS_EXP",$ex_time,-1);
  if(@OCIExecute($stmt, OCI_DEFAULT)) {
	@OCIFreeStatement($stmt);
	return True;
  } else {
	@OCIRollback($stmt);
	@OCIFreeStatement($stmt);
	return False;
  }
}

/*
 * Register callback functions with PHP.
 *
 * Note that session.save_handler must be 'user' for this to succeed.
 */
session_set_save_handler(
    "___oci_session_open",
    "___oci_session_close",
    "___oci_session_read",
    "___oci_session_write",
    "___oci_session_destroy",
    "___oci_session_gc");





?>