# HG changeset patch
# User jbe
# Date 1331658293 -3600
# Node ID 2a6984869ba3f00efba2895b277655af220dd67e
# Parent  876f366b2311d9d70c4018c606ef159fbefd9cbc
Delete sessions:
- when they expire
- in function "delete_private_data" (security relevant in certain setups)
- in function "delete_member"

(Sessions had been removed in changeset ed2f94a397cd and re-added in changeset 4e3d8f1ca3a9 without mechanisms for deletion of sessions)

diff -r 876f366b2311 -r 2a6984869ba3 core.sql
--- a/core.sql	Sun Mar 11 18:48:51 2012 +0100
+++ b/core.sql	Tue Mar 13 18:04:53 2012 +0100
@@ -2013,6 +2013,16 @@
 COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative (or status-quo) to another initiative (or status-quo); Used to fill "battle" table';
 
 
+CREATE VIEW "expired_session" AS
+  SELECT * FROM "session" WHERE now() > "expiry";
+
+CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
+  DELETE FROM "session" WHERE "ident" = OLD."ident";
+
+COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
+COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
+
+
 CREATE VIEW "open_issue" AS
   SELECT * FROM "issue" WHERE "closed" ISNULL;
 
@@ -4034,6 +4044,7 @@
     DECLARE
       "issue_id_v" "issue"."id"%TYPE;
     BEGIN
+      DELETE FROM "expired_session";
       PERFORM "check_activity"();
       PERFORM "calculate_member_counts"();
       FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
@@ -4145,6 +4156,7 @@
       DELETE FROM "member_image"       WHERE "member_id" = "member_id_p";
       DELETE FROM "contact"            WHERE "member_id" = "member_id_p";
       DELETE FROM "ignored_member"     WHERE "member_id" = "member_id_p";
+      DELETE FROM "session"            WHERE "member_id" = "member_id_p";
       DELETE FROM "area_setting"       WHERE "member_id" = "member_id_p";
       DELETE FROM "issue_setting"      WHERE "member_id" = "member_id_p";
       DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
@@ -4204,6 +4216,7 @@
       DELETE FROM "member_image";
       DELETE FROM "contact";
       DELETE FROM "ignored_member";
+      DELETE FROM "session";
       DELETE FROM "area_setting";
       DELETE FROM "issue_setting";
       DELETE FROM "ignored_initiative";
diff -r 876f366b2311 -r 2a6984869ba3 lf_update.c
--- a/lf_update.c	Sun Mar 11 18:48:51 2012 +0100
+++ b/lf_update.c	Tue Mar 13 18:04:53 2012 +0100
@@ -54,6 +54,22 @@
     return 1;
   }
 
+  // delete expired sessions:
+  status = PQexec(db, "DELETE FROM \"expired_session\"");
+  if (!status) {
+    fprintf(stderr, "Error in pqlib while sending SQL command deleting expired sessions\n");
+    err = 1;
+  } else if (
+    PQresultStatus(status) != PGRES_COMMAND_OK &&
+    PQresultStatus(status) != PGRES_TUPLES_OK
+  ) {
+    fprintf(stderr, "Error while executing SQL command deleting expired sessions:\n%s", PQresultErrorMessage(status));
+    err = 1;
+    PQclear(status);
+  } else {
+    PQclear(status);
+  }
+ 
   // check member activity:
   status = PQexec(db, "SELECT \"check_activity\"()");
   if (!status) {