Adding unit tests to Session.sql and correcting logic that causes it to fail.

Adding a 90 min timeout to the sessions by default

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2328 4979c152-3d1c-0410-bac9-87ea11338e46

4 files changed, 67 insertions, 1 deletions

diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql
index 1fe40ada..e81ee254 100644
--- a/sql/Pg-database.sql
+++ b/sql/Pg-database.sql
@@ -426,6 +426,7 @@ CREATE TABLE defaults (
 );
 
 \COPY defaults FROM stdin WITH DELIMITER |
+timeout|90 minutes
 sinumber|1
 sonumber|1
 yearend|1
diff --git a/sql/modules/Session.sql b/sql/modules/Session.sql
index e0547c22..158c8c2e 100644
--- a/sql/modules/Session.sql
+++ b/sql/modules/Session.sql
@@ -30,6 +30,10 @@ RETURNS session AS
 $$
 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 @@ BEGIN
 	       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
diff --git a/sql/modules/test/Base.sql b/sql/modules/test/Base.sql
new file mode 100644
index 00000000..8bb23b13
--- /dev/null
+++ b/sql/modules/test/Base.sql
@@ -0,0 +1,4 @@
+CREATE TEMPORARY TABLE test_result (
+	test_name text,
+	success bool
+);
diff --git a/sql/modules/test/Session.sql b/sql/modules/test/Session.sql
new file mode 100644
index 00000000..3a190344
--- /dev/null
+++ b/sql/modules/test/Session.sql
@@ -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;