1.1 --- a/core.sql	Fri Apr 03 14:49:54 2015 +0200
     1.2 +++ b/core.sql	Sat Jul 18 17:12:54 2015 +0200
     1.3 @@ -7,7 +7,7 @@
     1.4  BEGIN;
     1.5  
     1.6  CREATE VIEW "liquid_feedback_version" AS
     1.7 -  SELECT * FROM (VALUES ('3.0.4', 3, 0, 4))
     1.8 +  SELECT * FROM (VALUES ('3.0.5', 3, 0, 5))
     1.9    AS "subquery"("string", "major", "minor", "revision");
    1.10  
    1.11  
    1.12 @@ -460,6 +460,7 @@
    1.13          "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
    1.14          "name"                  TEXT            NOT NULL,
    1.15          "description"           TEXT            NOT NULL DEFAULT '',
    1.16 +        "external_reference"    TEXT,
    1.17          "member_count"          INT4,
    1.18          "text_search_data"      TSVECTOR );
    1.19  CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
    1.20 @@ -474,9 +475,10 @@
    1.21  
    1.22  COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
    1.23  
    1.24 -COMMENT ON COLUMN "unit"."parent_id"    IS 'Parent id of tree node; Multiple roots allowed';
    1.25 -COMMENT ON COLUMN "unit"."active"       IS 'TRUE means new issues can be created in areas of this unit';
    1.26 -COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
    1.27 +COMMENT ON COLUMN "unit"."parent_id"          IS 'Parent id of tree node; Multiple roots allowed';
    1.28 +COMMENT ON COLUMN "unit"."active"             IS 'TRUE means new issues can be created in areas of this unit';
    1.29 +COMMENT ON COLUMN "unit"."external_reference" IS 'Opaque data field to store an external reference';
    1.30 +COMMENT ON COLUMN "unit"."member_count"       IS 'Count of members as determined by column "voting_right" in table "privilege"';
    1.31  
    1.32  
    1.33  CREATE TABLE "unit_setting" (
    1.34 @@ -495,6 +497,7 @@
    1.35          "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
    1.36          "name"                  TEXT            NOT NULL,
    1.37          "description"           TEXT            NOT NULL DEFAULT '',
    1.38 +        "external_reference"    TEXT,
    1.39          "direct_member_count"   INT4,
    1.40          "member_weight"         INT4,
    1.41          "text_search_data"      TSVECTOR );
    1.42 @@ -510,6 +513,7 @@
    1.43  COMMENT ON TABLE "area" IS 'Subject areas';
    1.44  
    1.45  COMMENT ON COLUMN "area"."active"              IS 'TRUE means new issues can be created in this area';
    1.46 +COMMENT ON COLUMN "area"."external_reference"  IS 'Opaque data field to store an external reference';
    1.47  COMMENT ON COLUMN "area"."direct_member_count" IS 'Number of active members of that area (ignoring their weight), as calculated from view "area_member_count"';
    1.48  COMMENT ON COLUMN "area"."member_weight"       IS 'Same as "direct_member_count" but respecting delegations';
    1.49  
    1.50 @@ -559,6 +563,7 @@
    1.51          "area_id"               INT4            NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.52          "policy_id"             INT4            NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    1.53          "admin_notice"          TEXT,
    1.54 +        "external_reference"    TEXT,
    1.55          "state"                 "issue_state"   NOT NULL DEFAULT 'admission',
    1.56          "phase_finished"        TIMESTAMPTZ,
    1.57          "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
    1.58 @@ -626,6 +631,7 @@
    1.59  COMMENT ON TABLE "issue" IS 'Groups of initiatives';
    1.60  
    1.61  COMMENT ON COLUMN "issue"."admin_notice"            IS 'Public notice by admin to explain manual interventions, or to announce corrections';
    1.62 +COMMENT ON COLUMN "issue"."external_reference"      IS 'Opaque data field to store an external reference';
    1.63  COMMENT ON COLUMN "issue"."phase_finished"          IS 'Set to a value NOTNULL, if the current phase has finished, but calculations are pending; No changes in this issue shall be made by the frontend or API when this value is set';
    1.64  COMMENT ON COLUMN "issue"."accepted"                IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
    1.65  COMMENT ON COLUMN "issue"."half_frozen"             IS 'Point in time, when "discussion_time" has elapsed; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.';
    1.66 @@ -676,6 +682,7 @@
    1.67          "revoked"               TIMESTAMPTZ,
    1.68          "revoked_by_member_id"  INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    1.69          "suggested_initiative_id" INT4          REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.70 +        "external_reference"    TEXT,
    1.71          "admitted"              BOOLEAN,
    1.72          "supporter_count"                    INT4,
    1.73          "informed_supporter_count"           INT4,
    1.74 @@ -735,6 +742,7 @@
    1.75  COMMENT ON COLUMN "initiative"."discussion_url"         IS 'URL pointing to a discussion platform for this initiative';
    1.76  COMMENT ON COLUMN "initiative"."revoked"                IS 'Point in time, when one initiator decided to revoke the initiative';
    1.77  COMMENT ON COLUMN "initiative"."revoked_by_member_id"   IS 'Member, who decided to revoke the initiative';
    1.78 +COMMENT ON COLUMN "initiative"."external_reference"     IS 'Opaque data field to store an external reference';
    1.79  COMMENT ON COLUMN "initiative"."admitted"               IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
    1.80  COMMENT ON COLUMN "initiative"."supporter_count"                    IS 'Calculated from table "direct_supporter_snapshot"';
    1.81  COMMENT ON COLUMN "initiative"."informed_supporter_count"           IS 'Calculated from table "direct_supporter_snapshot"';
    1.82 @@ -802,6 +810,7 @@
    1.83          "author_id"             INT4            NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    1.84          "formatting_engine"     TEXT,
    1.85          "content"               TEXT            NOT NULL,
    1.86 +        "external_reference"    TEXT,
    1.87          "text_search_data"      TSVECTOR );
    1.88  CREATE INDEX "draft_created_idx" ON "draft" ("created");
    1.89  CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
    1.90 @@ -813,8 +822,9 @@
    1.91  
    1.92  COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues; Frontends must ensure that new drafts for initiatives of half_frozen, fully_frozen or closed issues can''t be created.';
    1.93  
    1.94 -COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
    1.95 -COMMENT ON COLUMN "draft"."content"           IS 'Text of the draft in a format depending on the field "formatting_engine"';
    1.96 +COMMENT ON COLUMN "draft"."formatting_engine"  IS 'Allows different formatting engines (i.e. wiki formats) to be used';
    1.97 +COMMENT ON COLUMN "draft"."content"            IS 'Text of the draft in a format depending on the field "formatting_engine"';
    1.98 +COMMENT ON COLUMN "draft"."external_reference" IS 'Opaque data field to store an external reference';
    1.99  
   1.100  
   1.101  CREATE TABLE "rendered_draft" (
   1.102 @@ -837,6 +847,7 @@
   1.103          "name"                  TEXT            NOT NULL,
   1.104          "formatting_engine"     TEXT,
   1.105          "content"               TEXT            NOT NULL DEFAULT '',
   1.106 +        "external_reference"    TEXT,
   1.107          "text_search_data"      TSVECTOR,
   1.108          "minus2_unfulfilled_count" INT4,
   1.109          "minus2_fulfilled_count"   INT4,
   1.110 @@ -859,6 +870,7 @@
   1.111  COMMENT ON TABLE "suggestion" IS 'Suggestions to initiators, to change the current draft; must not be deleted explicitly, as they vanish automatically if the last opinion is deleted';
   1.112  
   1.113  COMMENT ON COLUMN "suggestion"."draft_id"                 IS 'Draft, which the author has seen when composing the suggestion; should always be set by a frontend, but defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
   1.114 +COMMENT ON COLUMN "suggestion"."external_reference"       IS 'Opaque data field to store an external reference';
   1.115  COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
   1.116  COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count"   IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
   1.117  COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';