liquid_feedback_core
changeset 508:e9a6f63612da
Added SQL COMMENTs
author | jbe |
---|---|
date | Sat Apr 16 18:14:09 2016 +0200 (2016-04-16) |
parents | 7bc72214ecc9 |
children | 3b684315c724 |
files | core.sql |
line diff
1.1 --- a/core.sql Sat Apr 16 14:38:44 2016 +0200 1.2 +++ b/core.sql Sat Apr 16 18:14:09 2016 +0200 1.3 @@ -187,7 +187,12 @@ 1.4 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"'; 1.5 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"'; 1.6 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)'; 1.7 -COMMENT ON COLUMN "member"."disable_notifications" IS 'TRUE if member does not want to receive notifications'; 1.8 +COMMENT ON COLUMN "member"."disable_notifications" IS 'TRUE if member does not want to receive notifications'; 1.9 +COMMENT ON COLUMN "member"."notification_counter" IS 'Sequential number of next scheduled notification message (used as a seed for pseudo-random initiative selection algorithm)'; 1.10 +COMMENT ON COLUMN "member"."notification_sample_size" IS 'Number of featured initiatives per issue in scheduled notification messages'; 1.11 +COMMENT ON COLUMN "member"."notification_dow" IS 'Day of week for scheduled notifications (NULL to receive a daily digest)'; 1.12 +COMMENT ON COLUMN "member"."notification_hour" IS 'Time of day when scheduled notifications are sent out'; 1.13 +COMMENT ON COLUMN "member"."notification_sent" IS 'Timestamp of last scheduled notification mail that has been sent out'; 1.14 COMMENT ON COLUMN "member"."login_recovery_expiry" IS 'Date/time after which another login recovery attempt is allowed'; 1.15 COMMENT ON COLUMN "member"."password_reset_secret" IS 'Secret string sent via e-mail for password recovery'; 1.16 COMMENT ON COLUMN "member"."password_reset_secret_expiry" IS 'Date/time until the password recovery secret is valid, and date/time after which another password recovery attempt is allowed'; 1.17 @@ -792,7 +797,7 @@ 1.18 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.19 CREATE INDEX "ignored_initiative_initiative_id_idx" ON "ignored_initiative" ("initiative_id"); 1.20 1.21 -COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives'; 1.22 +COMMENT ON TABLE "ignored_initiative" IS 'An entry in this table denotes that the member does not wish to receive notifications for the given initiative; 1.23 1.24 1.25 CREATE TABLE "initiative_setting" ( 1.26 @@ -1275,6 +1280,11 @@ 1.27 "last_suggestion_id" INT8 ); 1.28 CREATE INDEX "notification_initiative_sent_initiative_idx" ON "notification_initiative_sent" ("initiative_id"); 1.29 1.30 +COMMENT ON TABLE "notification_initiative_sent" IS 'Information which initiatives have been promoted to a member in a scheduled notification mail'; 1.31 + 1.32 +COMMENT ON COLUMN "notification_initiative_sent"."last_draft_id" IS 'Current (i.e. last) draft_id when initiative had been promoted'; 1.33 +COMMENT ON COLUMN "notification_initiative_sent"."last_suggestion_id" IS 'Current (i.e. last) draft_id when initiative had been promoted'; 1.34 + 1.35 1.36 CREATE TABLE "newsletter" ( 1.37 "id" SERIAL4 PRIMARY KEY, 1.38 @@ -1288,6 +1298,15 @@ 1.39 CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL; 1.40 CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published"); 1.41 1.42 +COMMENT ON TABLE "newsletter" IS 'Contains newsletters created by administrators to be sent out and for further reference'; 1.43 + 1.44 +COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter is to be sent out (and made available in the frontend)'; 1.45 +COMMENT ON COLUMN "newsletter"."unit_id" IS 'If set, only members with voting right in the given unit are considered to be recipients'; 1.46 +COMMENT ON COLUMN "newsletter"."include_all_members" IS 'TRUE = include all members regardless of their ''disable_notifications'' setting'; 1.47 +COMMENT ON COLUMN "newsletter"."sent" IS 'Timestamp when the newsletter has been mailed out'; 1.48 +COMMENT ON COLUMN "newsletter"."subject" IS 'Subject line (e.g. to be used for the email)'; 1.49 +COMMENT ON COLUMN "newsletter"."content" IS 'Plain text content of the newsletter'; 1.50 + 1.51 1.52 1.53 ---------------------------------------------- 1.54 @@ -2366,7 +2385,9 @@ 1.55 ( "event"."event" = 'initiative_revoked'::"event_type" AND 1.56 "supporter"."member_id" NOTNULL ) ); 1.57 1.58 -COMMENT ON VIEW "event_for_notification" IS 'TODO: documentation'; 1.59 +COMMENT ON VIEW "event_for_notification" IS 'Entries of the "event" table which are of interest for a particular notification mail recipient'; 1.60 + 1.61 +COMMENT ON COLUMN "event_for_notification"."recipient_id" IS 'member_id of the recipient of a notification mail'; 1.62 1.63 1.64 CREATE VIEW "updated_initiative" AS 1.65 @@ -2410,6 +2431,9 @@ 1.66 ) 1.67 ); 1.68 1.69 +COMMENT ON VIEW "updated_initiative" IS 'Helper view for view "updated_or_featured_initiative"'; 1.70 + 1.71 + 1.72 CREATE FUNCTION "featured_initiative" 1.73 ( "recipient_id_p" "member"."id"%TYPE, 1.74 "area_id_p" "area"."id"%TYPE ) 1.75 @@ -2501,6 +2525,12 @@ 1.76 END; 1.77 $$; 1.78 1.79 +COMMENT ON FUNCTION "featured_initiative" 1.80 + ( "recipient_id_p" "member"."id"%TYPE, 1.81 + "area_id_p" "area"."id"%TYPE ) 1.82 + IS 'Helper function for view "updated_or_featured_initiative"'; 1.83 + 1.84 + 1.85 CREATE VIEW "updated_or_featured_initiative" AS 1.86 SELECT 1.87 "subquery".*, 1.88 @@ -2527,6 +2557,14 @@ 1.89 ) AS "subquery" 1.90 JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"; 1.91 1.92 +COMMENT ON VIEW "updated_or_featured_initiative" IS 'Initiatives to be included in a scheduled notification mail because (a) they have been updated or (b) they are featured'; 1.93 + 1.94 +COMMENT ON COLUMN "updated_or_featured_initiative"."recipient_id" IS '"id" of the member who receives the notification mail'; 1.95 +COMMENT ON COLUMN "updated_or_featured_initiative"."featured" IS 'TRUE if the initiative has been included because it was selected by the "featured_initiative" algorithm (see source of function "featured_initiative")'; 1.96 +COMMENT ON COLUMN "updated_or_featured_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail'; 1.97 +COMMENT ON COLUMN "updated_or_featured_initiative"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue'; 1.98 + 1.99 + 1.100 CREATE VIEW "leading_complement_initiative" AS 1.101 SELECT * FROM ( 1.102 SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id") 1.103 @@ -2552,6 +2590,12 @@ 1.104 AND "other"."initiative_id" = "subquery"."initiative_id" 1.105 ); 1.106 1.107 +COMMENT ON VIEW "leading_complement_initiative" IS 'Helper view for view "unfiltered_initiative_for_notification" in order to always include the most supported initiative of an issue'; 1.108 +COMMENT ON COLUMN "leading_complement_initiative"."featured" IS 'Always FALSE in this view'; 1.109 +COMMENT ON COLUMN "leading_complement_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail'; 1.110 +COMMENT ON COLUMN "leading_complement_initiative"."leading" IS 'Always TRUE in this view'; 1.111 + 1.112 + 1.113 CREATE VIEW "unfiltered_initiative_for_notification" AS 1.114 SELECT 1.115 "subquery".*, 1.116 @@ -2596,6 +2640,13 @@ 1.117 "sent"."member_id" = "subquery"."recipient_id" AND 1.118 "sent"."initiative_id" = "subquery"."initiative_id"; 1.119 1.120 +COMMENT ON VIEW "unfiltered_initiative_for_notification" IS 'Helper view which simply combines the views "updated_or_featured_initiative" and "leading_complement_initiative" and adds columns "supported", "new_draft", and "new_suggestion_count'; 1.121 + 1.122 +COMMENT ON COLUMN "unfiltered_initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient'; 1.123 +COMMENT ON COLUMN "unfiltered_initiative_for_notification"."new_draft" IS 'TRUE if a new draft exists (using the "draft_id" column of the "supporter" table in case of "supported" initiatives and the "last_draft_id" column of the "notification_initiative_sent" table in all other cases)'; 1.124 +COMMENT ON COLUMN "unfiltered_initiative_for_notification"."new_suggestion_count" IS 'Number of new suggestions (using the "last_suggestion_id" column of the "notification_initiative_sent" table while ignoring suggestions with an "opinion")'; 1.125 + 1.126 + 1.127 CREATE VIEW "initiative_for_notification" AS 1.128 SELECT "unfiltered1".* 1.129 FROM "unfiltered_initiative_for_notification" "unfiltered1" 1.130 @@ -2613,6 +2664,17 @@ 1.131 AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 ) 1.132 ); 1.133 1.134 +COMMENT ON VIEW "initiative_for_notification" IS 'Initiatives to be included in a scheduled notification mail'; 1.135 + 1.136 +COMMENT ON COLUMN "initiative_for_notification"."recipient_id" IS '"id" of the member who receives the notification mail'; 1.137 +COMMENT ON COLUMN "initiative_for_notification"."featured" IS 'TRUE if the initiative has been included because it was selected by the "featured_initiative" algorithm (see source of function "featured_initiative")'; 1.138 +COMMENT ON COLUMN "initiative_for_notification"."initiative_id" IS '"id" of the initiative to be included in the notification mail'; 1.139 +COMMENT ON COLUMN "initiative_for_notification"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue'; 1.140 +COMMENT ON COLUMN "initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient'; 1.141 +COMMENT ON COLUMN "initiative_for_notification"."new_draft" IS 'TRUE if a new draft exists (using the "draft_id" column of the "supporter" table in case of "supported" initiatives and the "last_draft_id" column of the "notification_initiative_sent" table in all other cases)'; 1.142 +COMMENT ON COLUMN "initiative_for_notification"."new_suggestion_count" IS 'Number of new suggestions (using the "last_suggestion_id" column of the "notification_initiative_sent" table while ignoring suggestions with an "opinion")'; 1.143 + 1.144 + 1.145 CREATE VIEW "scheduled_notification_to_send" AS 1.146 SELECT * FROM ( 1.147 SELECT 1.148 @@ -2651,6 +2713,12 @@ 1.149 ) AS "subquery2" 1.150 WHERE "pending" > '0'::INTERVAL; 1.151 1.152 +COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending'; 1.153 + 1.154 +COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail'; 1.155 +COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending'; 1.156 + 1.157 + 1.158 CREATE VIEW "newsletter_to_send" AS 1.159 SELECT 1.160 "member"."id" AS "recipient_id", 1.161 @@ -2674,6 +2742,8 @@ 1.162 "privilege"."member_id" NOTNULL OR 1.163 "subscription"."member_id" NOTNULL ); 1.164 1.165 +COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out'; 1.166 + 1.167 1.168 1.169 ------------------------------------------------------