diff options
author | Evgeny Fadeev <evgeny.fadeev@gmail.com> | 2012-08-31 11:45:57 -0400 |
---|---|---|
committer | Evgeny Fadeev <evgeny.fadeev@gmail.com> | 2012-08-31 11:45:57 -0400 |
commit | 3627bdaff5a12404216440d1f6c1228cbe251ef6 (patch) | |
tree | 69f490169bc1649ace1f898601754f1b15cd17e0 /askbot/search | |
parent | 53e7ad171920393e21d439aba7f7ce50ced42711 (diff) | |
download | askbot-3627bdaff5a12404216440d1f6c1228cbe251ef6.tar.gz askbot-3627bdaff5a12404216440d1f6c1228cbe251ef6.tar.bz2 askbot-3627bdaff5a12404216440d1f6c1228cbe251ef6.zip |
fixed migration for user groups
Diffstat (limited to 'askbot/search')
-rw-r--r-- | askbot/search/postgresql/user_profile_search_08312012.plsql | 93 |
1 files changed, 93 insertions, 0 deletions
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); |