liquid_feedback_core
changeset 183:ed2f94a397cd
Dropped session table (it is up to frontends to have their own session store)
author | jbe |
---|---|
date | Fri Jul 29 16:04:17 2011 +0200 (2011-07-29) |
parents | 4c03ef70dd92 |
children | af3d208e81be |
files | core.sql lf_update.c update/core-update.v1.4.0-v1.5.0.sql |
line diff
1.1 --- a/core.sql Fri Jul 29 15:23:42 2011 +0200 1.2 +++ b/core.sql Fri Jul 29 16:04:17 2011 +0200 1.3 @@ -280,22 +280,6 @@ 1.4 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored'; 1.5 1.6 1.7 -CREATE TABLE "session" ( 1.8 - "ident" TEXT PRIMARY KEY, 1.9 - "additional_secret" TEXT, 1.10 - "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours', 1.11 - "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL, 1.12 - "lang" TEXT ); 1.13 -CREATE INDEX "session_expiry_idx" ON "session" ("expiry"); 1.14 - 1.15 -COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend'; 1.16 - 1.17 -COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)'; 1.18 -COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks'; 1.19 -COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in'; 1.20 -COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language'; 1.21 - 1.22 - 1.23 CREATE TABLE "policy" ( 1.24 "id" SERIAL4 PRIMARY KEY, 1.25 "index" INT4 NOT NULL, 1.26 @@ -1958,16 +1942,6 @@ 1.27 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'; 1.28 1.29 1.30 -CREATE VIEW "expired_session" AS 1.31 - SELECT * FROM "session" WHERE now() > "expiry"; 1.32 - 1.33 -CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD 1.34 - DELETE FROM "session" WHERE "ident" = OLD."ident"; 1.35 - 1.36 -COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible'; 1.37 -COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"'; 1.38 - 1.39 - 1.40 CREATE VIEW "open_issue" AS 1.41 SELECT * FROM "issue" WHERE "closed" ISNULL; 1.42 1.43 @@ -3976,7 +3950,6 @@ 1.44 DECLARE 1.45 "issue_id_v" "issue"."id"%TYPE; 1.46 BEGIN 1.47 - DELETE FROM "expired_session"; 1.48 PERFORM "check_last_login"(); 1.49 PERFORM "calculate_member_counts"(); 1.50 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP 1.51 @@ -4089,7 +4062,6 @@ 1.52 DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 1.53 DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 1.54 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p"; 1.55 - DELETE FROM "session" WHERE "member_id" = "member_id_p"; 1.56 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; 1.57 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; 1.58 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p"; 1.59 @@ -4146,7 +4118,6 @@ 1.60 DELETE FROM "member_image"; 1.61 DELETE FROM "contact"; 1.62 DELETE FROM "ignored_member"; 1.63 - DELETE FROM "session"; 1.64 DELETE FROM "area_setting"; 1.65 DELETE FROM "issue_setting"; 1.66 DELETE FROM "ignored_initiative";
2.1 --- a/lf_update.c Fri Jul 29 15:23:42 2011 +0200 2.2 +++ b/lf_update.c Fri Jul 29 16:04:17 2011 +0200 2.3 @@ -54,22 +54,6 @@ 2.4 return 1; 2.5 } 2.6 2.7 - // delete expired sessions: 2.8 - status = PQexec(db, "DELETE FROM \"expired_session\""); 2.9 - if (!status) { 2.10 - fprintf(stderr, "Error in pqlib while sending SQL command deleting expired sessions\n"); 2.11 - err = 1; 2.12 - } else if ( 2.13 - PQresultStatus(status) != PGRES_COMMAND_OK && 2.14 - PQresultStatus(status) != PGRES_TUPLES_OK 2.15 - ) { 2.16 - fprintf(stderr, "Error while executing SQL command deleting expired sessions:\n%s", PQresultErrorMessage(status)); 2.17 - err = 1; 2.18 - PQclear(status); 2.19 - } else { 2.20 - PQclear(status); 2.21 - } 2.22 - 2.23 // check last login: 2.24 status = PQexec(db, "SELECT \"check_last_login\"()"); 2.25 if (!status) {
3.1 --- a/update/core-update.v1.4.0-v1.5.0.sql Fri Jul 29 15:23:42 2011 +0200 3.2 +++ b/update/core-update.v1.4.0-v1.5.0.sql Fri Jul 29 16:04:17 2011 +0200 3.3 @@ -26,6 +26,9 @@ 3.4 3.5 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)'; 3.6 3.7 +DROP VIEW "expired_session"; 3.8 +DROP TABLE "session"; 3.9 + 3.10 ALTER TABLE "policy" ADD COLUMN "direct_majority_num" INT4 NOT NULL DEFAULT 1; 3.11 ALTER TABLE "policy" ADD COLUMN "direct_majority_den" INT4 NOT NULL DEFAULT 2; 3.12 ALTER TABLE "policy" ADD COLUMN "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE; 3.13 @@ -171,6 +174,8 @@ 3.14 3.15 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)'; 3.16 3.17 +DROP TABLE "invite_code_unit"; 3.18 + 3.19 DROP VIEW "area_member_count"; 3.20 3.21 ALTER TABLE "membership" DROP COLUMN "autoreject"; 3.22 @@ -1039,6 +1044,130 @@ 3.23 END; 3.24 $$; 3.25 3.26 +CREATE OR REPLACE FUNCTION "check_everything"() 3.27 + RETURNS VOID 3.28 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.29 + DECLARE 3.30 + "issue_id_v" "issue"."id"%TYPE; 3.31 + BEGIN 3.32 + PERFORM "check_last_login"(); 3.33 + PERFORM "calculate_member_counts"(); 3.34 + FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP 3.35 + PERFORM "check_issue"("issue_id_v"); 3.36 + END LOOP; 3.37 + FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP 3.38 + PERFORM "calculate_ranks"("issue_id_v"); 3.39 + END LOOP; 3.40 + RETURN; 3.41 + END; 3.42 + $$; 3.43 + 3.44 +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) 3.45 + RETURNS VOID 3.46 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.47 + BEGIN 3.48 + UPDATE "member" SET 3.49 + "last_login" = NULL, 3.50 + "last_login_public" = NULL, 3.51 + "login" = NULL, 3.52 + "password" = NULL, 3.53 + "locked" = TRUE, 3.54 + "active" = FALSE, 3.55 + "notify_email" = NULL, 3.56 + "notify_email_unconfirmed" = NULL, 3.57 + "notify_email_secret" = NULL, 3.58 + "notify_email_secret_expiry" = NULL, 3.59 + "notify_email_lock_expiry" = NULL, 3.60 + "password_reset_secret" = NULL, 3.61 + "password_reset_secret_expiry" = NULL, 3.62 + "organizational_unit" = NULL, 3.63 + "internal_posts" = NULL, 3.64 + "realname" = NULL, 3.65 + "birthday" = NULL, 3.66 + "address" = NULL, 3.67 + "email" = NULL, 3.68 + "xmpp_address" = NULL, 3.69 + "website" = NULL, 3.70 + "phone" = NULL, 3.71 + "mobile_phone" = NULL, 3.72 + "profession" = NULL, 3.73 + "external_memberships" = NULL, 3.74 + "external_posts" = NULL, 3.75 + "statement" = NULL 3.76 + WHERE "id" = "member_id_p"; 3.77 + -- "text_search_data" is updated by triggers 3.78 + DELETE FROM "setting" WHERE "member_id" = "member_id_p"; 3.79 + DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; 3.80 + DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; 3.81 + DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 3.82 + DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 3.83 + DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p"; 3.84 + DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; 3.85 + DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; 3.86 + DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p"; 3.87 + DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; 3.88 + DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 3.89 + DELETE FROM "membership" WHERE "member_id" = "member_id_p"; 3.90 + DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 3.91 + DELETE FROM "non_voter" WHERE "member_id" = "member_id_p"; 3.92 + DELETE FROM "direct_voter" USING "issue" 3.93 + WHERE "direct_voter"."issue_id" = "issue"."id" 3.94 + AND "issue"."closed" ISNULL 3.95 + AND "member_id" = "member_id_p"; 3.96 + RETURN; 3.97 + END; 3.98 + $$; 3.99 + 3.100 +CREATE OR REPLACE FUNCTION "delete_private_data"() 3.101 + RETURNS VOID 3.102 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.103 + BEGIN 3.104 + UPDATE "member" SET 3.105 + "last_login" = NULL, 3.106 + "login" = NULL, 3.107 + "password" = NULL, 3.108 + "notify_email" = NULL, 3.109 + "notify_email_unconfirmed" = NULL, 3.110 + "notify_email_secret" = NULL, 3.111 + "notify_email_secret_expiry" = NULL, 3.112 + "notify_email_lock_expiry" = NULL, 3.113 + "password_reset_secret" = NULL, 3.114 + "password_reset_secret_expiry" = NULL, 3.115 + "organizational_unit" = NULL, 3.116 + "internal_posts" = NULL, 3.117 + "realname" = NULL, 3.118 + "birthday" = NULL, 3.119 + "address" = NULL, 3.120 + "email" = NULL, 3.121 + "xmpp_address" = NULL, 3.122 + "website" = NULL, 3.123 + "phone" = NULL, 3.124 + "mobile_phone" = NULL, 3.125 + "profession" = NULL, 3.126 + "external_memberships" = NULL, 3.127 + "external_posts" = NULL, 3.128 + "statement" = NULL; 3.129 + -- "text_search_data" is updated by triggers 3.130 + DELETE FROM "invite_code"; 3.131 + DELETE FROM "setting"; 3.132 + DELETE FROM "setting_map"; 3.133 + DELETE FROM "member_relation_setting"; 3.134 + DELETE FROM "member_image"; 3.135 + DELETE FROM "contact"; 3.136 + DELETE FROM "ignored_member"; 3.137 + DELETE FROM "area_setting"; 3.138 + DELETE FROM "issue_setting"; 3.139 + DELETE FROM "ignored_initiative"; 3.140 + DELETE FROM "initiative_setting"; 3.141 + DELETE FROM "suggestion_setting"; 3.142 + DELETE FROM "non_voter"; 3.143 + DELETE FROM "direct_voter" USING "issue" 3.144 + WHERE "direct_voter"."issue_id" = "issue"."id" 3.145 + AND "issue"."closed" ISNULL; 3.146 + RETURN; 3.147 + END; 3.148 + $$; 3.149 + 3.150 COMMIT; 3.151 3.152 BEGIN; 3.153 @@ -1052,7 +1181,6 @@ 3.154 FROM "invite_code" 3.155 WHERE "member"."id" = "invite_code"."member_id"; 3.156 3.157 -DROP TABLE "invite_code_unit"; 3.158 DROP TABLE "invite_code"; 3.159 3.160 UPDATE "initiative" SET