liquid_feedback_core

annotate demo.sql @ 7:69d84040fb93

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

More attibutes in member table

Renamed column ident_number of member table to identification

Images of members are now stored in extra table member_image

Minor bugfix in init.sql: Added missing verification_time column

Full text index search support using PostgreSQL's TSVECTOR and TSQUERY datatypes

New function highlight(...), which helps to highlight matching words in search results
author jbe
date Mon Nov 23 12:00:00 2009 +0100 (2009-11-23)
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