liquid_feedback_core

annotate demo.sql @ 8:e6faf5ff83af

find changesets by author, revision, files, or words in the commit message
Version beta9

Added missing indicies on TSVECTOR fields

New field latest_snapshot_event in table issue specifying the type of the latest snapshot taken

Added weight field also for (intermediate) delegating members in snapshot and voter tables

Possibility for an initiative to specify a URL where an external discussion takes place (discussion_url)

Export concept for creating a database dump, without some non-public information (e.g. private contact data), including:
- Shell script lf_export
- Database function delete_private_data()

Field in member table to be used by a frontend to store information about hints being hidden by the user

Minor cleanup / New comments
author jbe
date Mon Nov 30 12:00:00 2009 +0100 (2009-11-30)
parents 3da35844c874
children 4af4df1415f9
rev   line source
jbe@0 1 -- NOTE: This file requires that sequence generators have not been used.
jbe@0 2 -- (All new rows need to start with id '1'.)
jbe@0 3
jbe@0 4 BEGIN;
jbe@0 5
jbe@0 6 INSERT INTO "member" ("login", "name") VALUES
jbe@0 7 ('user1', 'User #1'), -- id 1
jbe@0 8 ('user2', 'User #2'), -- id 2
jbe@0 9 ('user3', 'User #3'), -- id 3
jbe@0 10 ('user4', 'User #4'), -- id 4
jbe@0 11 ('user5', 'User #5'), -- id 5
jbe@0 12 ('user6', 'User #6'), -- id 6
jbe@0 13 ('user7', 'User #7'), -- id 7
jbe@0 14 ('user8', 'User #8'), -- id 8
jbe@0 15 ('user9', 'User #9'), -- id 9
jbe@0 16 ('user10', 'User #10'), -- id 10
jbe@0 17 ('user11', 'User #11'), -- id 11
jbe@0 18 ('user12', 'User #12'), -- id 12
jbe@0 19 ('user13', 'User #13'), -- id 13
jbe@0 20 ('user14', 'User #14'), -- id 14
jbe@0 21 ('user15', 'User #15'), -- id 15
jbe@0 22 ('user16', 'User #16'), -- id 16
jbe@0 23 ('user17', 'User #17'), -- id 17
jbe@0 24 ('user18', 'User #18'), -- id 18
jbe@0 25 ('user19', 'User #19'), -- id 19
jbe@0 26 ('user20', 'User #20'), -- id 20
jbe@0 27 ('user21', 'User #21'), -- id 21
jbe@0 28 ('user22', 'User #22'), -- id 22
jbe@0 29 ('user23', 'User #23'); -- id 23
jbe@0 30
jbe@0 31 UPDATE "member" SET "password" = "login";
jbe@0 32
jbe@0 33 INSERT INTO "policy" (
jbe@0 34 "name",
jbe@3 35 "admission_time",
jbe@3 36 "discussion_time",
jbe@3 37 "verification_time",
jbe@3 38 "voting_time",
jbe@0 39 "issue_quorum_num", "issue_quorum_den",
jbe@0 40 "initiative_quorum_num", "initiative_quorum_den"
jbe@0 41 ) VALUES (
jbe@0 42 'Default policy',
jbe@3 43 '1 hour', '1 hour', '1 hour', '1 hour',
jbe@0 44 25, 100,
jbe@0 45 20, 100 );
jbe@0 46
jbe@3 47 CREATE FUNCTION "time_warp"() RETURNS VOID
jbe@3 48 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@3 49 BEGIN
jbe@3 50 UPDATE "issue" SET
jbe@3 51 "snapshot" = "snapshot" - '1 hour 1 minute'::INTERVAL,
jbe@3 52 "created" = "created" - '1 hour 1 minute'::INTERVAL,
jbe@3 53 "accepted" = "accepted" - '1 hour 1 minute'::INTERVAL,
jbe@3 54 "half_frozen" = "half_frozen" - '1 hour 1 minute'::INTERVAL,
jbe@3 55 "fully_frozen" = "fully_frozen" - '1 hour 1 minute'::INTERVAL;
jbe@3 56 PERFORM "check_everything"();
jbe@3 57 RETURN;
jbe@3 58 END;
jbe@3 59 $$;
jbe@3 60
jbe@0 61 INSERT INTO "area" ("name") VALUES
jbe@0 62 ('Area #1'), -- id 1
jbe@0 63 ('Area #2'), -- id 2
jbe@0 64 ('Area #3'), -- id 3
jbe@0 65 ('Area #4'); -- id 4
jbe@0 66
jbe@0 67 INSERT INTO "membership" ("area_id", "member_id", "autoreject") VALUES
jbe@0 68 (1, 9, FALSE),
jbe@0 69 (1, 19, FALSE),
jbe@0 70 (2, 9, TRUE),
jbe@0 71 (2, 10, TRUE),
jbe@0 72 (2, 17, TRUE),
jbe@0 73 (3, 9, FALSE),
jbe@0 74 (3, 11, FALSE),
jbe@0 75 (3, 12, TRUE),
jbe@0 76 (3, 14, FALSE),
jbe@0 77 (3, 20, FALSE),
jbe@0 78 (3, 21, TRUE),
jbe@0 79 (3, 22, TRUE),
jbe@0 80 (4, 6, FALSE),
jbe@0 81 (4, 9, FALSE),
jbe@0 82 (4, 13, FALSE),
jbe@0 83 (4, 22, TRUE);
jbe@0 84
jbe@0 85 -- global delegations
jbe@0 86 INSERT INTO "delegation"
jbe@0 87 ("truster_id", "trustee_id") VALUES
jbe@0 88 ( 1, 9),
jbe@0 89 ( 2, 11),
jbe@0 90 ( 3, 12),
jbe@0 91 ( 4, 13),
jbe@0 92 ( 5, 14),
jbe@0 93 ( 6, 7),
jbe@0 94 ( 7, 8),
jbe@0 95 ( 8, 6),
jbe@0 96 (10, 9),
jbe@0 97 (11, 9),
jbe@0 98 (12, 21),
jbe@0 99 (15, 10),
jbe@0 100 (16, 17),
jbe@0 101 (17, 19),
jbe@0 102 (18, 19),
jbe@0 103 (23, 22);
jbe@0 104
jbe@0 105 -- delegations for topics
jbe@0 106 INSERT INTO "delegation"
jbe@0 107 ("area_id", "truster_id", "trustee_id") VALUES
jbe@0 108 (1, 3, 17),
jbe@0 109 (2, 5, 10),
jbe@0 110 (2, 9, 10),
jbe@0 111 (3, 4, 14),
jbe@0 112 (3, 16, 20),
jbe@0 113 (3, 19, 20),
jbe@0 114 (4, 5, 13),
jbe@0 115 (4, 12, 22);
jbe@0 116
jbe@0 117 INSERT INTO "issue" ("area_id", "policy_id") VALUES
jbe@0 118 (3, 1); -- id 1
jbe@0 119
jbe@0 120 INSERT INTO "initiative" ("issue_id", "name") VALUES
jbe@0 121 (1, 'Initiative #1'), -- id 1
jbe@0 122 (1, 'Initiative #2'), -- id 2
jbe@0 123 (1, 'Initiative #3'), -- id 3
jbe@0 124 (1, 'Initiative #4'), -- id 4
jbe@0 125 (1, 'Initiative #5'), -- id 5
jbe@0 126 (1, 'Initiative #6'), -- id 6
jbe@0 127 (1, 'Initiative #7'); -- id 7
jbe@0 128
jbe@0 129 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
jbe@0 130 (1, 17, 'Lorem ipsum...'), -- id 1
jbe@0 131 (2, 20, 'Lorem ipsum...'), -- id 2
jbe@0 132 (3, 20, 'Lorem ipsum...'), -- id 3
jbe@0 133 (4, 20, 'Lorem ipsum...'), -- id 4
jbe@0 134 (5, 14, 'Lorem ipsum...'), -- id 5
jbe@0 135 (6, 11, 'Lorem ipsum...'), -- id 6
jbe@0 136 (7, 12, 'Lorem ipsum...'); -- id 7
jbe@0 137
jbe@0 138 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
jbe@0 139 (1, 17),
jbe@0 140 (1, 19),
jbe@0 141 (2, 20),
jbe@0 142 (3, 20),
jbe@0 143 (4, 20),
jbe@0 144 (5, 14),
jbe@0 145 (6, 11),
jbe@0 146 (7, 12);
jbe@0 147
jbe@0 148 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
jbe@0 149 ( 7, 4, 4),
jbe@0 150 ( 8, 2, 2),
jbe@0 151 (11, 6, 6),
jbe@0 152 (12, 7, 7),
jbe@0 153 (14, 1, 1),
jbe@0 154 (14, 2, 2),
jbe@0 155 (14, 3, 3),
jbe@0 156 (14, 4, 4),
jbe@0 157 (14, 5, 5),
jbe@0 158 (14, 6, 6),
jbe@0 159 (14, 7, 7),
jbe@0 160 (17, 1, 1),
jbe@0 161 (17, 3, 3),
jbe@0 162 (19, 1, 1),
jbe@0 163 (19, 2, 2),
jbe@0 164 (20, 1, 1),
jbe@0 165 (20, 2, 2),
jbe@0 166 (20, 3, 3),
jbe@0 167 (20, 4, 4),
jbe@0 168 (20, 5, 5);
jbe@0 169
jbe@0 170 INSERT INTO "suggestion" ("initiative_id", "author_id", "name", "description") VALUES
jbe@0 171 (1, 19, 'Suggestion #1', 'Lorem ipsum...'); -- id 1
jbe@0 172 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
jbe@0 173 (14, 1, 2, FALSE);
jbe@0 174 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
jbe@0 175 (19, 1, 2, FALSE);
jbe@0 176
jbe@3 177 SELECT "time_warp"();
jbe@3 178 SELECT "time_warp"();
jbe@3 179 SELECT "time_warp"();
jbe@0 180
jbe@0 181 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
jbe@0 182 ( 8, 1),
jbe@0 183 ( 9, 1),
jbe@0 184 (11, 1),
jbe@0 185 (12, 1),
jbe@0 186 (14, 1),
jbe@0 187 (19, 1),
jbe@0 188 (20, 1),
jbe@0 189 (21, 1);
jbe@0 190
jbe@0 191 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
jbe@0 192 ( 8, 1, 1, 1),
jbe@0 193 ( 8, 1, 2, 1),
jbe@0 194 ( 8, 1, 3, 1),
jbe@0 195 ( 8, 1, 4, 1),
jbe@0 196 ( 8, 1, 5, 1),
jbe@0 197 ( 8, 1, 6, -1),
jbe@0 198 ( 8, 1, 7, -1),
jbe@0 199 ( 9, 1, 1, -2),
jbe@0 200 ( 9, 1, 2, -3),
jbe@0 201 ( 9, 1, 3, -2),
jbe@0 202 ( 9, 1, 4, -2),
jbe@0 203 ( 9, 1, 5, -2),
jbe@0 204 ( 9, 1, 6, -1),
jbe@0 205 (11, 1, 1, -1),
jbe@0 206 (11, 1, 2, -1),
jbe@0 207 (11, 1, 3, -1),
jbe@0 208 (11, 1, 4, -1),
jbe@0 209 (11, 1, 5, -1),
jbe@0 210 (11, 1, 6, 2),
jbe@0 211 (11, 1, 7, 1),
jbe@0 212 (12, 1, 1, -1),
jbe@0 213 (12, 1, 3, -1),
jbe@0 214 (12, 1, 4, -1),
jbe@0 215 (12, 1, 5, -1),
jbe@0 216 (12, 1, 6, -2),
jbe@0 217 (12, 1, 7, 1),
jbe@0 218 (14, 1, 1, 1),
jbe@0 219 (14, 1, 2, 3),
jbe@0 220 (14, 1, 3, 1),
jbe@0 221 (14, 1, 4, 2),
jbe@0 222 (14, 1, 5, 1),
jbe@0 223 (14, 1, 6, 1),
jbe@0 224 (14, 1, 7, 1),
jbe@0 225 (19, 1, 1, 3),
jbe@0 226 (19, 1, 2, 4),
jbe@0 227 (19, 1, 3, 2),
jbe@0 228 (19, 1, 4, 2),
jbe@0 229 (19, 1, 5, 2),
jbe@0 230 (19, 1, 7, 1),
jbe@0 231 (20, 1, 1, 1),
jbe@0 232 (20, 1, 2, 2),
jbe@0 233 (20, 1, 3, 1),
jbe@0 234 (20, 1, 4, 1),
jbe@0 235 (20, 1, 5, 1),
jbe@0 236 (21, 1, 5, -1);
jbe@0 237
jbe@3 238 SELECT "time_warp"();
jbe@3 239
jbe@3 240 DROP FUNCTION "time_warp"();
jbe@0 241
jbe@0 242 END;
jbe@0 243

Impressum / About Us