oracle smart php session handaling. PL/SQL

/*


 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");





?>