added postgres search configuration for the user profiles

1 files changed, 89 insertions, 0 deletions

diff --git a/askbot/search/postgresql/user_profile_search_051312.plsql b/askbot/search/postgresql/user_profile_search_051312.plsql
new file mode 100644
index 00000000..99e3121d
--- /dev/null
+++ b/askbot/search/postgresql/user_profile_search_051312.plsql
@@ -0,0 +1,89 @@
+/* 
+Script depends on functions defined for general askbot full text search.
+to_tsvector(), add_tsvector_column()
+
+calculates text search vector for the user profile
+the searched fields are: 
+1) user name
+2) user profile
+3) group names - for groups to which user belongs
+*/
+CREATE OR REPLACE FUNCTION get_auth_user_tsv(user_id integer)
+RETURNS tsvector AS
+$$
+DECLARE
+    group_query text;
+    user_query text;
+    onerow record;
+    tsv tsvector;
+BEGIN
+    group_query = 
+        'SELECT user_group.name as group_name ' ||
+        'FROM tag AS user_group ' ||
+        'INNER JOIN askbot_groupmembership AS gm ' ||
+        'ON gm.user_id= ' || user_id || ' AND gm.group_id=user_group.id';
+
+    tsv = to_tsvector('');
+    FOR onerow in EXECUTE group_query LOOP
+        tsv = tsv || to_tsvector(onerow.group_name);
+    END LOOP;
+
+    user_query = 'SELECT username, about FROM auth_user WHERE id=' || user_id;
+    FOR onerow in EXECUTE user_query LOOP
+        tsv = tsv || to_tsvector(onerow.username) || to_tsvector(onerow.about);
+    END LOOP;
+    RETURN tsv;
+END;
+$$ LANGUAGE plpgsql;
+
+/* create tsvector columns in the content tables */
+SELECT add_tsvector_column('text_search_vector', 'auth_user');
+
+/* populate tsvectors with data */
+UPDATE auth_user SET text_search_vector = get_auth_user_tsv(id);
+
+/* one trigger per table for tsv updates */
+
+/* set up auth_user triggers */
+CREATE OR REPLACE FUNCTION auth_user_tsv_update_handler()
+RETURNS trigger AS
+$$
+BEGIN
+    new.text_search_vector = get_auth_user_tsv(new.id);
+    RETURN new;
+END;
+$$ LANGUAGE plpgsql;
+DROP TRIGGER IF EXISTS auth_user_tsv_update_trigger ON auth_user;
+
+CREATE TRIGGER auth_user_tsv_update_trigger
+BEFORE INSERT OR UPDATE ON auth_user 
+FOR EACH ROW EXECUTE PROCEDURE auth_user_tsv_update_handler();
+
+/* group membership trigger */
+CREATE OR REPLACE FUNCTION group_membership_tsv_update_handler()
+RETURNS trigger AS
+$$
+DECLARE
+    tsv tsvector;
+    user_query text;
+BEGIN
+    user_query = 'UPDATE auth_user SET username=username WHERE ' ||
+        'id=' || new.user_id;
+    /* just trigger the tsv update on user */
+    EXECUTE user_query;
+    RETURN new;
+END;
+$$ LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS group_membership_tsv_update_trigger
+ON askbot_groupmembership;
+
+CREATE TRIGGER group_membership_tsv_update_trigger
+AFTER INSERT OR DELETE
+ON askbot_groupmembership
+FOR EACH ROW EXECUTE PROCEDURE group_membership_tsv_update_handler();
+
+DROP INDEX IF EXISTS auth_user_search_idx;
+
+CREATE INDEX auth_user_search_idx ON auth_user
+USING gin(text_search_vector);