summaryrefslogtreecommitdiff
path: root/sql/modules/Session.sql
blob: 9dcf378fbb6228f78cbc3e66f5be7bd3c500ad6b (plain)
  1. CREATE OR REPLACE FUNCTION form_close(in_session_id int, in_form_id int)
  2. RETURNS BOOL AS
  3. $$
  4. BEGIN
  5. SELECT * FROM open_forms
  6. WHERE session_id = in_session_id AND id = in_form_id;
  7. IF FOUND THEN
  8. DELETE FROM open_forms
  9. WHERE session_id = in_session_id AND id = in_form_id;
  10. RETURN TRUE;
  11. ELSE RETURN FALSE;
  12. END IF;
  13. END;
  14. $$ language plpgsql;
  15. CREATE OR REPLACE FUNCTION form_open(in_session_id int)
  16. RETURNS INT AS
  17. $$
  18. BEGIN
  19. INSERT INTO open_forms (session_id) VALUES (in_session_id);
  20. RETURN currval('form_id_seq');
  21. END;
  22. $$ LANGUAGE PLPGSQL;
  23. CREATE OR REPLACE FUNCTION session_check(in_session_id int, in_token text)
  24. RETURNS session AS
  25. $$
  26. DECLARE out_row session%ROWTYPE;
  27. BEGIN
  28. DELETE FROM session
  29. WHERE last_used < now() - coalesce((SELECT value FROM defaults
  30. WHERE setting_key = 'timeout')::interval,
  31. '90 minutes'::interval);
  32. UPDATE session
  33. SET last_used = now()
  34. WHERE session_id = in_session_id
  35. AND token = in_token
  36. AND last_used > now() - (SELECT value FROM defaults
  37. WHERE setting_key = 'timeout')::interval
  38. AND users_id = (select id from users
  39. where username = SESSION_USER);
  40. IF FOUND THEN
  41. SELECT * INTO out_row FROM session WHERE session_id = in_session_id;
  42. ELSE
  43. DELETE FROM SESSION
  44. WHERE users_id IN (select id from users
  45. where username = SESSION_USER);
  46. -- the above query also releases all discretionary locks by the
  47. -- session
  48. IF NOT FOUND THEN
  49. PERFORM id FROM users WHERE username = SESSION_USER;
  50. IF NOT FOUND THEN
  51. RAISE EXCEPTION 'User Not Known';
  52. END IF;
  53. END IF;
  54. INSERT INTO session(users_id, token, last_used, transaction_id)
  55. SELECT id, md5(random()::text), now(), 0
  56. FROM users WHERE username = SESSION_USER;
  57. -- TODO-- remove transaction_id field from session table
  58. SELECT * INTO out_row FROM session
  59. WHERE session_id = currval('session_session_id_seq');
  60. END IF;
  61. RETURN out_row;
  62. END;
  63. $$ LANGUAGE PLPGSQL;
  64. COMMENT ON FUNCTION session_check(int, text) IS
  65. $$ Return code is 0 for failure, 1 for success. $$;
  66. CREATE OR REPLACE FUNCTION unlock_all() RETURNS BOOL AS
  67. $$
  68. BEGIN
  69. UPDATE transactions SET locked_by = NULL
  70. where locked_by IN
  71. (select session_id from session WHERE users_id =
  72. (SELECT id FROM users WHERE username = SESSION_USER));
  73. RETURN FOUND;
  74. END;
  75. $$ LANGUAGE PLPGSQL;
  76. CREATE OR REPLACE FUNCTION unlock(in_id int) RETURNS BOOL AS $$
  77. BEGIN
  78. UPDATE transactions SET locked_by = NULL WHERE id = in_id
  79. AND locked_by IN (SELECT session_id FROM session WHERE users_id =
  80. (SELECT id FROM users WHERE username = SESSION_USER));
  81. RETURN FOUND;
  82. END;
  83. $$ LANGUAGE PLPGSQL;