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
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" (

Impressum / About Us