[Ruby-session-devel-cvs] CVS: ruby-session/src/sql postgresql.sql,NONE,1.1

[Sean C. <the...@us...>]

Update of /cvsroot/ruby-session/ruby-session/src/sql
In directory usw-pr-cvs1:/tmp/cvs-serv22003

Added Files:
	postgresql.sql 
Log Message:
*) Added schema and stored procedures for PostgreSQL support for
   ruby-session.  Session ID generation must be done on the client.  It
   could be done in the database, but isn't being done there now.


--- NEW FILE: postgresql.sql ---
/* $Id: postgresql.sql,v 1.1 2002/06/12 20:25:36 thetitan Exp $
 *
 * See the LICENSE file for copyright and distribution information
 */

/*
 * Please install Pl/PgSQL.  Can be installed via:
 *
 * createlang --dbname=template1 plgsql
 *
 * where template1 can be substituted for any other existing database.
 *
 *
 * Load this file into your database via:
 *
 * psql -f postgresql.sql db_name username
 */

DROP TABLE session;

CREATE TABLE session (
  session_id VARCHAR(64) NOT NULL,
  utc_start_time TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  utc_last_updated TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  active CHAR(1) DEFAULT 'y' NOT NULL,
  secure BOOL DEFAULT FALSE NOT NULL,
  rekey_time INTERVAL DEFAULT '5 min'::INTERVAL NOT NULL,
  expiration_time INTERVAL DEFAULT '60 min'::INTERVAL NOT NULL,
  session_data BYTEA NOT NULL
);

CREATE UNIQUE INDEX session_id_udx ON session (session_id);



DROP FUNCTION session_ins_opq();
CREATE FUNCTION session_ins_opq() RETURNS opaque AS '
	BEGIN
		NEW.utc_start_time := (SELECT NOW());
		NEW.utc_last_updated := (SELECT NOW());
		RETURN NEW;
	END;
' LANGUAGE 'plpgsql';

DROP FUNCTION session_upd_opq();
CREATE FUNCTION session_upd_opq() RETURNS opaque AS '
	BEGIN
		NEW.utc_start_time := OLD.utc_start_time;
		NEW.utc_last_updated := (SELECT NOW());
		RETURN NEW;
	END;
' LANGUAGE 'plpgsql';

DROP FUNCTION session_cleanup();
CREATE FUNCTION session_cleanup() RETURNS BOOL AS '
	BEGIN
		EXECUTE ''DELETE FROM session WHERE (utc_last_updated + expiration_time) < NOW()'';
		RETURN TRUE;
	END
' LANGUAGE 'plpgsql';

CREATE TRIGGER session_ins_opq BEFORE INSERT
ON session FOR EACH ROW EXECUTE
PROCEDURE session_ins_opq();

CREATE TRIGGER session_upd_opq BEFORE UPDATE
ON session FOR EACH ROW EXECUTE
PROCEDURE session_upd_opq();

/* SELECT session_cleanup(); */