summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--sql/Pg-database.sql1
-rw-r--r--sql/modules/Session.sql6
-rw-r--r--sql/modules/test/Base.sql4
-rw-r--r--sql/modules/test/Session.sql57
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;