liquid_feedback_core

annotate demo.sql @ 1:23092eb00e16

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

Serious bugfix in SQL function create_snapshot(...), which caused wrong counting of opinions on suggestions

lf_update now deletes expired sessions

Redundancy in SQL function check_everything() removed by using existent views
author jbe
date Tue Nov 03 12:00:00 2009 +0100 (2009-11-03)
parents 8d021cb5eaf4
children 3da35844c874
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@0 35 "admission_time", "discussion_time", "voting_time",
jbe@0 36 "issue_quorum_num", "issue_quorum_den",
jbe@0 37 "initiative_quorum_num", "initiative_quorum_den"
jbe@0 38 ) VALUES (
jbe@0 39 'Default policy',
jbe@0 40 '1 hour', '1 hour', '1 hour',
jbe@0 41 25, 100,
jbe@0 42 20, 100 );
jbe@0 43
jbe@0 44 INSERT INTO "area" ("name") VALUES
jbe@0 45 ('Area #1'), -- id 1
jbe@0 46 ('Area #2'), -- id 2
jbe@0 47 ('Area #3'), -- id 3
jbe@0 48 ('Area #4'); -- id 4
jbe@0 49
jbe@0 50 INSERT INTO "membership" ("area_id", "member_id", "autoreject") VALUES
jbe@0 51 (1, 9, FALSE),
jbe@0 52 (1, 19, FALSE),
jbe@0 53 (2, 9, TRUE),
jbe@0 54 (2, 10, TRUE),
jbe@0 55 (2, 17, TRUE),
jbe@0 56 (3, 9, FALSE),
jbe@0 57 (3, 11, FALSE),
jbe@0 58 (3, 12, TRUE),
jbe@0 59 (3, 14, FALSE),
jbe@0 60 (3, 20, FALSE),
jbe@0 61 (3, 21, TRUE),
jbe@0 62 (3, 22, TRUE),
jbe@0 63 (4, 6, FALSE),
jbe@0 64 (4, 9, FALSE),
jbe@0 65 (4, 13, FALSE),
jbe@0 66 (4, 22, TRUE);
jbe@0 67
jbe@0 68 -- global delegations
jbe@0 69 INSERT INTO "delegation"
jbe@0 70 ("truster_id", "trustee_id") VALUES
jbe@0 71 ( 1, 9),
jbe@0 72 ( 2, 11),
jbe@0 73 ( 3, 12),
jbe@0 74 ( 4, 13),
jbe@0 75 ( 5, 14),
jbe@0 76 ( 6, 7),
jbe@0 77 ( 7, 8),
jbe@0 78 ( 8, 6),
jbe@0 79 (10, 9),
jbe@0 80 (11, 9),
jbe@0 81 (12, 21),
jbe@0 82 (15, 10),
jbe@0 83 (16, 17),
jbe@0 84 (17, 19),
jbe@0 85 (18, 19),
jbe@0 86 (23, 22);
jbe@0 87
jbe@0 88 -- delegations for topics
jbe@0 89 INSERT INTO "delegation"
jbe@0 90 ("area_id", "truster_id", "trustee_id") VALUES
jbe@0 91 (1, 3, 17),
jbe@0 92 (2, 5, 10),
jbe@0 93 (2, 9, 10),
jbe@0 94 (3, 4, 14),
jbe@0 95 (3, 16, 20),
jbe@0 96 (3, 19, 20),
jbe@0 97 (4, 5, 13),
jbe@0 98 (4, 12, 22);
jbe@0 99
jbe@0 100 INSERT INTO "issue" ("area_id", "policy_id") VALUES
jbe@0 101 (3, 1); -- id 1
jbe@0 102
jbe@0 103 INSERT INTO "initiative" ("issue_id", "name") VALUES
jbe@0 104 (1, 'Initiative #1'), -- id 1
jbe@0 105 (1, 'Initiative #2'), -- id 2
jbe@0 106 (1, 'Initiative #3'), -- id 3
jbe@0 107 (1, 'Initiative #4'), -- id 4
jbe@0 108 (1, 'Initiative #5'), -- id 5
jbe@0 109 (1, 'Initiative #6'), -- id 6
jbe@0 110 (1, 'Initiative #7'); -- id 7
jbe@0 111
jbe@0 112 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
jbe@0 113 (1, 17, 'Lorem ipsum...'), -- id 1
jbe@0 114 (2, 20, 'Lorem ipsum...'), -- id 2
jbe@0 115 (3, 20, 'Lorem ipsum...'), -- id 3
jbe@0 116 (4, 20, 'Lorem ipsum...'), -- id 4
jbe@0 117 (5, 14, 'Lorem ipsum...'), -- id 5
jbe@0 118 (6, 11, 'Lorem ipsum...'), -- id 6
jbe@0 119 (7, 12, 'Lorem ipsum...'); -- id 7
jbe@0 120
jbe@0 121 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
jbe@0 122 (1, 17),
jbe@0 123 (1, 19),
jbe@0 124 (2, 20),
jbe@0 125 (3, 20),
jbe@0 126 (4, 20),
jbe@0 127 (5, 14),
jbe@0 128 (6, 11),
jbe@0 129 (7, 12);
jbe@0 130
jbe@0 131 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
jbe@0 132 ( 7, 4, 4),
jbe@0 133 ( 8, 2, 2),
jbe@0 134 (11, 6, 6),
jbe@0 135 (12, 7, 7),
jbe@0 136 (14, 1, 1),
jbe@0 137 (14, 2, 2),
jbe@0 138 (14, 3, 3),
jbe@0 139 (14, 4, 4),
jbe@0 140 (14, 5, 5),
jbe@0 141 (14, 6, 6),
jbe@0 142 (14, 7, 7),
jbe@0 143 (17, 1, 1),
jbe@0 144 (17, 3, 3),
jbe@0 145 (19, 1, 1),
jbe@0 146 (19, 2, 2),
jbe@0 147 (20, 1, 1),
jbe@0 148 (20, 2, 2),
jbe@0 149 (20, 3, 3),
jbe@0 150 (20, 4, 4),
jbe@0 151 (20, 5, 5);
jbe@0 152
jbe@0 153 INSERT INTO "suggestion" ("initiative_id", "author_id", "name", "description") VALUES
jbe@0 154 (1, 19, 'Suggestion #1', 'Lorem ipsum...'); -- id 1
jbe@0 155 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
jbe@0 156 (14, 1, 2, FALSE);
jbe@0 157 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
jbe@0 158 (19, 1, 2, FALSE);
jbe@0 159
jbe@0 160 UPDATE "issue" SET "created" = "created" - '3 hour 3 minute'::INTERVAL;
jbe@0 161 SELECT check_everything();
jbe@0 162 UPDATE "issue" SET "accepted" = "accepted" - '2 hour 2 minute'::INTERVAL;
jbe@0 163 SELECT check_everything();
jbe@0 164
jbe@0 165 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
jbe@0 166 ( 8, 1),
jbe@0 167 ( 9, 1),
jbe@0 168 (11, 1),
jbe@0 169 (12, 1),
jbe@0 170 (14, 1),
jbe@0 171 (19, 1),
jbe@0 172 (20, 1),
jbe@0 173 (21, 1);
jbe@0 174
jbe@0 175 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
jbe@0 176 ( 8, 1, 1, 1),
jbe@0 177 ( 8, 1, 2, 1),
jbe@0 178 ( 8, 1, 3, 1),
jbe@0 179 ( 8, 1, 4, 1),
jbe@0 180 ( 8, 1, 5, 1),
jbe@0 181 ( 8, 1, 6, -1),
jbe@0 182 ( 8, 1, 7, -1),
jbe@0 183 ( 9, 1, 1, -2),
jbe@0 184 ( 9, 1, 2, -3),
jbe@0 185 ( 9, 1, 3, -2),
jbe@0 186 ( 9, 1, 4, -2),
jbe@0 187 ( 9, 1, 5, -2),
jbe@0 188 ( 9, 1, 6, -1),
jbe@0 189 (11, 1, 1, -1),
jbe@0 190 (11, 1, 2, -1),
jbe@0 191 (11, 1, 3, -1),
jbe@0 192 (11, 1, 4, -1),
jbe@0 193 (11, 1, 5, -1),
jbe@0 194 (11, 1, 6, 2),
jbe@0 195 (11, 1, 7, 1),
jbe@0 196 (12, 1, 1, -1),
jbe@0 197 (12, 1, 3, -1),
jbe@0 198 (12, 1, 4, -1),
jbe@0 199 (12, 1, 5, -1),
jbe@0 200 (12, 1, 6, -2),
jbe@0 201 (12, 1, 7, 1),
jbe@0 202 (14, 1, 1, 1),
jbe@0 203 (14, 1, 2, 3),
jbe@0 204 (14, 1, 3, 1),
jbe@0 205 (14, 1, 4, 2),
jbe@0 206 (14, 1, 5, 1),
jbe@0 207 (14, 1, 6, 1),
jbe@0 208 (14, 1, 7, 1),
jbe@0 209 (19, 1, 1, 3),
jbe@0 210 (19, 1, 2, 4),
jbe@0 211 (19, 1, 3, 2),
jbe@0 212 (19, 1, 4, 2),
jbe@0 213 (19, 1, 5, 2),
jbe@0 214 (19, 1, 7, 1),
jbe@0 215 (20, 1, 1, 1),
jbe@0 216 (20, 1, 2, 2),
jbe@0 217 (20, 1, 3, 1),
jbe@0 218 (20, 1, 4, 1),
jbe@0 219 (20, 1, 5, 1),
jbe@0 220 (21, 1, 5, -1);
jbe@0 221
jbe@0 222 UPDATE "issue" SET
jbe@0 223 "snapshot" = "snapshot" - '1 hour 1 minute'::INTERVAL,
jbe@0 224 "frozen" = "frozen" - '1 hour 1 minute'::INTERVAL;
jbe@0 225 SELECT check_everything();
jbe@0 226
jbe@0 227 END;
jbe@0 228

Impressum / About Us