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  ------------------------------------------------------

Impressum / About Us