liquid_feedback_core
changeset 7:69d84040fb93 beta8
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
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 | 3ea7a72ed7e7 |
children | e6faf5ff83af |
files | core.sql init.sql |
line diff
1.1 --- a/core.sql Wed Nov 18 12:00:00 2009 +0100 1.2 +++ b/core.sql Mon Nov 23 12:00:00 2009 +0100 1.3 @@ -6,33 +6,123 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('beta7', NULL, NULL, NULL)) 1.8 + SELECT * FROM (VALUES ('beta8', NULL, NULL, NULL)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 1.13 +---------------------- 1.14 +-- Full text search -- 1.15 +---------------------- 1.16 + 1.17 + 1.18 +CREATE FUNCTION "text_search_query"("query_text_p" TEXT) 1.19 + RETURNS TSQUERY 1.20 + LANGUAGE 'plpgsql' IMMUTABLE AS $$ 1.21 + BEGIN 1.22 + RETURN plainto_tsquery('pg_catalog.simple', "query_text_p"); 1.23 + END; 1.24 + $$; 1.25 + 1.26 +COMMENT ON FUNCTION "text_search_query"(TEXT) IS 'Usage: WHERE "text_search_data" @@ "text_search_query"(''<user query>'')'; 1.27 + 1.28 + 1.29 +CREATE FUNCTION "highlight" 1.30 + ( "body_p" TEXT, 1.31 + "query_text_p" TEXT ) 1.32 + RETURNS TEXT 1.33 + LANGUAGE 'plpgsql' IMMUTABLE AS $$ 1.34 + BEGIN 1.35 + RETURN ts_headline( 1.36 + 'pg_catalog.simple', 1.37 + replace(replace("body_p", '\\', '\\\\'), '*', '\\*'), 1.38 + "text_search_query"("query_text_p"), 1.39 + 'StartSel=* StopSel=* HighlightAll=TRUE' ); 1.40 + END; 1.41 + $$; 1.42 + 1.43 +COMMENT ON FUNCTION "highlight" 1.44 + ( "body_p" TEXT, 1.45 + "query_text_p" TEXT ) 1.46 + IS 'For a given a user query this function encapsulates all matches with asterisks. Asterisks and backslashes being already present are preceeded with one extra backslash.'; 1.47 + 1.48 + 1.49 + 1.50 ------------------------- 1.51 -- Tables and indicies -- 1.52 ------------------------- 1.53 1.54 - 1.55 CREATE TABLE "member" ( 1.56 "id" SERIAL4 PRIMARY KEY, 1.57 "login" TEXT NOT NULL UNIQUE, 1.58 "password" TEXT, 1.59 "active" BOOLEAN NOT NULL DEFAULT TRUE, 1.60 "admin" BOOLEAN NOT NULL DEFAULT FALSE, 1.61 - "name" TEXT, 1.62 - "ident_number" TEXT UNIQUE, 1.63 - "avatar" BYTEA ); 1.64 + "notify_email" TEXT, 1.65 + "notify_email_confirmed" BOOLEAN, 1.66 + "name" TEXT NOT NULL UNIQUE, 1.67 + "identification" TEXT UNIQUE, 1.68 + "organizational_unit" TEXT, 1.69 + "internal_posts" TEXT, 1.70 + "realname" TEXT, 1.71 + "birthday" DATE, 1.72 + "address" TEXT, 1.73 + "email" TEXT, 1.74 + "xmpp_address" TEXT, 1.75 + "website" TEXT, 1.76 + "phone" TEXT, 1.77 + "mobile_phone" TEXT, 1.78 + "profession" TEXT, 1.79 + "external_memberships" TEXT, 1.80 + "external_posts" TEXT, 1.81 + "statement" TEXT, 1.82 + "text_search_data" TSVECTOR, 1.83 + CONSTRAINT "notify_email_null_check" 1.84 + CHECK ("notify_email" NOTNULL = "notify_email_confirmed" NOTNULL) ); 1.85 CREATE INDEX "member_active_idx" ON "member" ("active"); 1.86 +CREATE TRIGGER "update_text_search_data" 1.87 + BEFORE INSERT OR UPDATE ON "member" 1.88 + FOR EACH ROW EXECUTE PROCEDURE 1.89 + tsvector_update_trigger('text_search_data', 'pg_catalog.simple', 1.90 + "name", "identification", "organizational_unit", "internal_posts", 1.91 + "realname", "external_memberships", "external_posts", "statement" ); 1.92 1.93 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization'; 1.94 1.95 -COMMENT ON COLUMN "member"."login" IS 'Login name'; 1.96 -COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)'; 1.97 -COMMENT ON COLUMN "member"."active" IS 'Inactive members can not login and their supports/votes are not counted by the system.'; 1.98 -COMMENT ON COLUMN "member"."ident_number" IS 'Additional information about the members idenficication number within the organization'; 1.99 +COMMENT ON COLUMN "member"."login" IS 'Login name'; 1.100 +COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)'; 1.101 +COMMENT ON COLUMN "member"."active" IS 'Inactive members can not login and their supports/votes are not counted by the system.'; 1.102 +COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas'; 1.103 +COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to'; 1.104 +COMMENT ON COLUMN "member"."notify_email_confirmed" IS 'TRUE, if "notify_email" has been confirmed'; 1.105 +COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member'; 1.106 +COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member'; 1.107 +COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to'; 1.108 +COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization'; 1.109 +COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"'; 1.110 +COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications'; 1.111 +COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in'; 1.112 +COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization'; 1.113 +COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system'; 1.114 + 1.115 + 1.116 +CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar'); 1.117 + 1.118 +COMMENT ON TYPE "member_image_type" IS 'Types of images for a member'; 1.119 + 1.120 + 1.121 + 1.122 +CREATE TABLE "member_image" ( 1.123 + PRIMARY KEY ("member_id", "image_type", "scaled"), 1.124 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.125 + "image_type" "member_image_type", 1.126 + "scaled" BOOLEAN, 1.127 + "content_type" TEXT, 1.128 + "data" BYTEA NOT NULL ); 1.129 + 1.130 +COMMENT ON TABLE "member_image" IS 'Images of members'; 1.131 + 1.132 +COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image'; 1.133 1.134 1.135 CREATE TABLE "member_count" ( 1.136 @@ -109,8 +199,14 @@ 1.137 "description" TEXT NOT NULL DEFAULT '', 1.138 "direct_member_count" INT4, 1.139 "member_weight" INT4, 1.140 - "autoreject_weight" INT4 ); 1.141 + "autoreject_weight" INT4, 1.142 + "text_search_data" TSVECTOR ); 1.143 CREATE INDEX "area_active_idx" ON "area" ("active"); 1.144 +CREATE TRIGGER "update_text_search_data" 1.145 + BEFORE INSERT OR UPDATE ON "area" 1.146 + FOR EACH ROW EXECUTE PROCEDURE 1.147 + tsvector_update_trigger('text_search_data', 'pg_catalog.simple', 1.148 + "name", "description" ); 1.149 1.150 COMMENT ON TABLE "area" IS 'Subject areas'; 1.151 1.152 @@ -183,10 +279,15 @@ 1.153 "positive_votes" INT4, 1.154 "negative_votes" INT4, 1.155 "rank" INT4, 1.156 + "text_search_data" TSVECTOR, 1.157 CONSTRAINT "revoked_initiatives_cant_be_admitted" 1.158 CHECK ("revoked" ISNULL OR "admitted" ISNULL), 1.159 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" 1.160 CHECK ("admitted" = TRUE OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "rank" ISNULL)) ); 1.161 +CREATE TRIGGER "update_text_search_data" 1.162 + BEFORE INSERT OR UPDATE ON "initiative" 1.163 + FOR EACH ROW EXECUTE PROCEDURE 1.164 + tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "name"); 1.165 1.166 COMMENT ON TABLE "initiative" IS 'Group of members publishing drafts for resolutions to be passed'; 1.167 1.168 @@ -207,7 +308,12 @@ 1.169 "id" SERIAL8 PRIMARY KEY, 1.170 "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.171 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.172 - "content" TEXT NOT NULL ); 1.173 + "content" TEXT NOT NULL, 1.174 + "text_search_data" TSVECTOR ); 1.175 +CREATE TRIGGER "update_text_search_data" 1.176 + BEFORE INSERT OR UPDATE ON "draft" 1.177 + FOR EACH ROW EXECUTE PROCEDURE 1.178 + tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content"); 1.179 1.180 COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues'; 1.181 1.182 @@ -220,6 +326,7 @@ 1.183 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.184 "name" TEXT NOT NULL, 1.185 "description" TEXT NOT NULL DEFAULT '', 1.186 + "text_search_data" TSVECTOR, 1.187 "minus2_unfulfilled_count" INT4, 1.188 "minus2_fulfilled_count" INT4, 1.189 "minus1_unfulfilled_count" INT4, 1.190 @@ -228,6 +335,11 @@ 1.191 "plus1_fulfilled_count" INT4, 1.192 "plus2_unfulfilled_count" INT4, 1.193 "plus2_fulfilled_count" INT4 ); 1.194 +CREATE TRIGGER "update_text_search_data" 1.195 + BEFORE INSERT OR UPDATE ON "suggestion" 1.196 + FOR EACH ROW EXECUTE PROCEDURE 1.197 + tsvector_update_trigger('text_search_data', 'pg_catalog.simple', 1.198 + "name", "description"); 1.199 1.200 COMMENT ON TABLE "suggestion" IS 'Suggestions to initiators, to change the current draft'; 1.201
2.1 --- a/init.sql Wed Nov 18 12:00:00 2009 +0100 2.2 +++ b/init.sql Mon Nov 23 12:00:00 2009 +0100 2.3 @@ -8,16 +8,14 @@ 2.4 "password", 2.5 "active", 2.6 "admin", 2.7 - "name", 2.8 - "ident_number" 2.9 + "name" 2.10 ) VALUES ( 2.11 DEFAULT, 2.12 'admin', 2.13 '', 2.14 TRUE, 2.15 TRUE, 2.16 - 'Administrator', 2.17 - DEFAULT ); 2.18 + 'Administrator' ); 2.19 2.20 INSERT INTO "policy" ( 2.21 "id", 2.22 @@ -26,6 +24,7 @@ 2.23 "description", 2.24 "admission_time", 2.25 "discussion_time", 2.26 + "verification_time", 2.27 "voting_time", 2.28 "issue_quorum_num", 2.29 "issue_quorum_den", 2.30 @@ -37,29 +36,32 @@ 2.31 'Extensive proceeding', 2.32 DEFAULT, 2.33 '1 month', 2.34 - '6 months', 2.35 + '5 months', 2.36 + '1 month', 2.37 '3 weeks', 2.38 - 5, 100, 2.39 - 1, 100 2.40 + 10, 100, 2.41 + 10, 100 2.42 ), ( 2.43 DEFAULT, 2.44 TRUE, 2.45 'Standard proceeding', 2.46 DEFAULT, 2.47 - '1 week', 2.48 + '1 month', 2.49 '1 month', 2.50 '1 week', 2.51 - 5, 100, 2.52 - 1, 100 2.53 + '1 week', 2.54 + 10, 100, 2.55 + 10, 100 2.56 ), ( 2.57 DEFAULT, 2.58 TRUE, 2.59 'Fast proceeding', 2.60 DEFAULT, 2.61 - '24 hours', 2.62 - '4 hours', 2.63 + '48 hours', 2.64 + '3 hours', 2.65 + '1 hour', 2.66 '20 hours', 2.67 - 5, 100, 2.68 + 1, 100, 2.69 1, 100 ); 2.70 2.71 INSERT INTO "area" (