SF.net SVN: ledger-smb:[2328] trunk/sql

[..hidden..]

Revision: 2328
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2328&view=rev
Author:   einhverfr
Date:     2008-09-18 18:38:09 +0000 (Thu, 18 Sep 2008)

Log Message:
-----------
Adding unit tests to Session.sql and correcting logic that causes it to fail.
Adding a 90 min timeout to the sessions by default

Modified Paths:
--------------
    trunk/sql/Pg-database.sql
    trunk/sql/modules/Session.sql

Added Paths:
-----------
    trunk/sql/modules/test/
    trunk/sql/modules/test/Base.sql
    trunk/sql/modules/test/Session.sql

Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql	2008-09-17 23:22:02 UTC (rev 2327)
+++ trunk/sql/Pg-database.sql	2008-09-18 18:38:09 UTC (rev 2328)
@@ -426,6 +426,7 @@
 );
 
 \COPY defaults FROM stdin WITH DELIMITER |
+timeout|90 minutes
 sinumber|1
 sonumber|1
 yearend|1

Modified: trunk/sql/modules/Session.sql
===================================================================
--- trunk/sql/modules/Session.sql	2008-09-17 23:22:02 UTC (rev 2327)
+++ trunk/sql/modules/Session.sql	2008-09-18 18:38:09 UTC (rev 2328)
@@ -30,6 +30,10 @@
 $$
 DECLARE out_row session%ROWTYPE;
 BEGIN
+	DELETE FROM session
+	 WHERE last_used < now() - coalesce((SELECT value FROM defaults
+                                    WHERE setting_key = 'timeout')::interval,
+	                            '90 minutes'::interval);
         UPDATE session 
            SET last_used = now()
          WHERE session_id = in_session_id
@@ -39,7 +43,7 @@
 	       AND users_id = (select id from users 
 			where username = SESSION_USER);
 	IF FOUND THEN
-		SELECT * INTO out_row WHERE session_id = in_session_id;
+		SELECT * INTO out_row FROM session WHERE session_id = in_session_id;
 	ELSE
 		DELETE FROM SESSION 
 		WHERE users_id IN (select id from users

Added: trunk/sql/modules/test/Base.sql
===================================================================
--- trunk/sql/modules/test/Base.sql	                        (rev 0)
+++ trunk/sql/modules/test/Base.sql	2008-09-18 18:38:09 UTC (rev 2328)
@@ -0,0 +1,4 @@
+CREATE TEMPORARY TABLE test_result (
+	test_name text,
+	success bool
+);

Added: trunk/sql/modules/test/Session.sql
===================================================================
--- trunk/sql/modules/test/Session.sql	                        (rev 0)
+++ trunk/sql/modules/test/Session.sql	2008-09-18 18:38:09 UTC (rev 2328)
@@ -0,0 +1,57 @@
+BEGIN;
+\i Base.sql
+
+INSERT INTO test_result (test_name, success)
+values ('timeout set', 
+(select count(*) from defaults where setting_key = 'timeout') = 1);
+
+INSERT INTO entity (name, entity_class, control_code)
+VALUES ('Testing.....', 3, '_TESTING.....');
+
+INSERT INTO users (entity_id, username)
+SELECT currval('entity_id_seq'), CURRENT_USER;
+
+INSERT INTO session (users_id, last_used, token, transaction_id)
+SELECT 	currval('users_id_seq'), 
+now() - coalesce((select value from defaults where setting_key = 'timeout')::interval, 
+         '90 minutes'::interval), 
+md5('test2'), 2;
+
+
+INSERT INTO session (users_id, last_used, token, transaction_id)
+SELECT currval('users_id_seq'), 
+now() - coalesce((select value from defaults where setting_key = 'timeout')::interval, 
+         '2 days'::interval), 
+md5('test3'), 3;
+
+select * from session_check(currval('session_session_id_seq')::int, md5('test1'));
+
+INSERT INTO session (users_id, last_used, token, transaction_id)
+SELECT currval('users_id_seq'), now(), md5('test1'), 1;
+
+
+INSERT INTO test_result (test_name, success)
+values ('session1 retrieved', 
+(select t.token = md5('test1') 
+FROM session_check(
+	currval('session_session_id_seq')::int, 
+	md5('test1')
+) t )
+);
+
+INSERT INTO test_result (test_name, success)
+VALUES ('session 2 removed', 
+(select count(*) from session where token = md5('test2') AND users_id = currval('users_id_seq')) = 0);
+
+DELETE FROM session WHERE users_id = currval('users_id_seq');
+DELETE FROM entity WHERE control_code = '_TESTING.....';
+
+SELECT * FROM test_result;
+
+SELECT (select count(*) from test_result where success is true) 
+|| ' tests passed and ' 
+|| (select count(*) from test_result where success is not true) 
+|| ' failed' as message;
+
+DROP TABLE test_result;
+COMMIT;


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.