fixed migration for user groups

2 files changed, 99 insertions, 1 deletions

diff --git a/askbot/migrations/0137_create_groups_from_relevant_tags.py b/askbot/migrations/0137_create_groups_from_relevant_tags.py
index 58a10f7f..b96e61b9 100644
--- a/askbot/migrations/0137_create_groups_from_relevant_tags.py
+++ b/askbot/migrations/0137_create_groups_from_relevant_tags.py
@@ -34,13 +34,18 @@ class Migration(DataMigration):
 
             group.save()
 
+            #update thread groups
             thread_groups = orm['askbot.ThreadToGroup'].objects
             thread_groups = thread_groups.filter(tag=group_tag)
             thread_groups.update(group=group)
-
+            #update post groups
             post_groups = orm['askbot.PostToGroup'].objects
             post_groups = post_groups.filter(tag=group_tag)
             post_groups.update(group=group)
+            #update user groups
+            memberships = group_tag.user_memberships.all()
+            for membership in memberships:
+                membership.user.groups.add(group)
 
         db_engine_name = askbot.get_database_engine_name()
         if 'postgresql_psycopg2' in db_engine_name:
diff --git a/askbot/search/postgresql/user_profile_search_08312012.plsql b/askbot/search/postgresql/user_profile_search_08312012.plsql
new file mode 100644
index 00000000..90b73148
--- /dev/null
+++ b/askbot/search/postgresql/user_profile_search_08312012.plsql
@@ -0,0 +1,93 @@
+/* 
+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 auth_group AS user_group ' ||
+        'INNER JOIN auth_user_groups 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 - reindex users when group membership
+ * changes */
+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 auth_user_groups;
+
+CREATE TRIGGER group_membership_tsv_update_trigger
+AFTER INSERT OR DELETE
+ON auth_user_groups
+FOR EACH ROW EXECUTE PROCEDURE group_membership_tsv_update_handler();
+
+/* todo: whenever group name changes - also 
+ * reindex users belonging to the group */
+
+DROP INDEX IF EXISTS auth_user_search_idx;
+
+CREATE INDEX auth_user_search_idx ON auth_user
+USING gin(text_search_vector);