# HG changeset patch # User jbe # Date 1311948257 -7200 # Node ID ed2f94a397cd7f8ddfa3fa41e74bbde55d6a8c03 # Parent 4c03ef70dd92f47be5f37b877375a5908479e36b Dropped session table (it is up to frontends to have their own session store) diff -r 4c03ef70dd92 -r ed2f94a397cd core.sql --- a/core.sql Fri Jul 29 15:23:42 2011 +0200 +++ b/core.sql Fri Jul 29 16:04:17 2011 +0200 @@ -280,22 +280,6 @@ COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored'; -CREATE TABLE "session" ( - "ident" TEXT PRIMARY KEY, - "additional_secret" TEXT, - "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours', - "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL, - "lang" TEXT ); -CREATE INDEX "session_expiry_idx" ON "session" ("expiry"); - -COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend'; - -COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)'; -COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks'; -COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in'; -COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language'; - - CREATE TABLE "policy" ( "id" SERIAL4 PRIMARY KEY, "index" INT4 NOT NULL, @@ -1958,16 +1942,6 @@ 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; @@ -3976,7 +3950,6 @@ DECLARE "issue_id_v" "issue"."id"%TYPE; BEGIN - DELETE FROM "expired_session"; PERFORM "check_last_login"(); PERFORM "calculate_member_counts"(); FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP @@ -4089,7 +4062,6 @@ 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"; @@ -4146,7 +4118,6 @@ 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 4c03ef70dd92 -r ed2f94a397cd lf_update.c --- a/lf_update.c Fri Jul 29 15:23:42 2011 +0200 +++ b/lf_update.c Fri Jul 29 16:04:17 2011 +0200 @@ -54,22 +54,6 @@ 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 last login: status = PQexec(db, "SELECT \"check_last_login\"()"); if (!status) { diff -r 4c03ef70dd92 -r ed2f94a397cd update/core-update.v1.4.0-v1.5.0.sql --- a/update/core-update.v1.4.0-v1.5.0.sql Fri Jul 29 15:23:42 2011 +0200 +++ b/update/core-update.v1.4.0-v1.5.0.sql Fri Jul 29 16:04:17 2011 +0200 @@ -26,6 +26,9 @@ COMMENT ON TABLE "rendered_member_statement" IS 'This table may be used by frontends to cache "rendered" member statements (e.g. HTML output generated from wiki text)'; +DROP VIEW "expired_session"; +DROP TABLE "session"; + ALTER TABLE "policy" ADD COLUMN "direct_majority_num" INT4 NOT NULL DEFAULT 1; ALTER TABLE "policy" ADD COLUMN "direct_majority_den" INT4 NOT NULL DEFAULT 2; ALTER TABLE "policy" ADD COLUMN "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE; @@ -171,6 +174,8 @@ COMMENT ON TABLE "rendered_suggestion" IS 'This table may be used by frontends to cache "rendered" drafts (e.g. HTML output generated from wiki text)'; +DROP TABLE "invite_code_unit"; + DROP VIEW "area_member_count"; ALTER TABLE "membership" DROP COLUMN "autoreject"; @@ -1039,6 +1044,130 @@ END; $$; +CREATE OR REPLACE FUNCTION "check_everything"() + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + DECLARE + "issue_id_v" "issue"."id"%TYPE; + BEGIN + PERFORM "check_last_login"(); + PERFORM "calculate_member_counts"(); + FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP + PERFORM "check_issue"("issue_id_v"); + END LOOP; + FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP + PERFORM "calculate_ranks"("issue_id_v"); + END LOOP; + RETURN; + END; + $$; + +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + BEGIN + UPDATE "member" SET + "last_login" = NULL, + "last_login_public" = NULL, + "login" = NULL, + "password" = NULL, + "locked" = TRUE, + "active" = FALSE, + "notify_email" = NULL, + "notify_email_unconfirmed" = NULL, + "notify_email_secret" = NULL, + "notify_email_secret_expiry" = NULL, + "notify_email_lock_expiry" = NULL, + "password_reset_secret" = NULL, + "password_reset_secret_expiry" = NULL, + "organizational_unit" = NULL, + "internal_posts" = NULL, + "realname" = NULL, + "birthday" = NULL, + "address" = NULL, + "email" = NULL, + "xmpp_address" = NULL, + "website" = NULL, + "phone" = NULL, + "mobile_phone" = NULL, + "profession" = NULL, + "external_memberships" = NULL, + "external_posts" = NULL, + "statement" = NULL + WHERE "id" = "member_id_p"; + -- "text_search_data" is updated by triggers + DELETE FROM "setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; + DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; + 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 "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"; + DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "membership" WHERE "member_id" = "member_id_p"; + DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; + DELETE FROM "non_voter" WHERE "member_id" = "member_id_p"; + DELETE FROM "direct_voter" USING "issue" + WHERE "direct_voter"."issue_id" = "issue"."id" + AND "issue"."closed" ISNULL + AND "member_id" = "member_id_p"; + RETURN; + END; + $$; + +CREATE OR REPLACE FUNCTION "delete_private_data"() + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + BEGIN + UPDATE "member" SET + "last_login" = NULL, + "login" = NULL, + "password" = NULL, + "notify_email" = NULL, + "notify_email_unconfirmed" = NULL, + "notify_email_secret" = NULL, + "notify_email_secret_expiry" = NULL, + "notify_email_lock_expiry" = NULL, + "password_reset_secret" = NULL, + "password_reset_secret_expiry" = NULL, + "organizational_unit" = NULL, + "internal_posts" = NULL, + "realname" = NULL, + "birthday" = NULL, + "address" = NULL, + "email" = NULL, + "xmpp_address" = NULL, + "website" = NULL, + "phone" = NULL, + "mobile_phone" = NULL, + "profession" = NULL, + "external_memberships" = NULL, + "external_posts" = NULL, + "statement" = NULL; + -- "text_search_data" is updated by triggers + DELETE FROM "invite_code"; + DELETE FROM "setting"; + DELETE FROM "setting_map"; + DELETE FROM "member_relation_setting"; + DELETE FROM "member_image"; + DELETE FROM "contact"; + DELETE FROM "ignored_member"; + DELETE FROM "area_setting"; + DELETE FROM "issue_setting"; + DELETE FROM "ignored_initiative"; + DELETE FROM "initiative_setting"; + DELETE FROM "suggestion_setting"; + DELETE FROM "non_voter"; + DELETE FROM "direct_voter" USING "issue" + WHERE "direct_voter"."issue_id" = "issue"."id" + AND "issue"."closed" ISNULL; + RETURN; + END; + $$; + COMMIT; BEGIN; @@ -1052,7 +1181,6 @@ FROM "invite_code" WHERE "member"."id" = "invite_code"."member_id"; -DROP TABLE "invite_code_unit"; DROP TABLE "invite_code"; UPDATE "initiative" SET