diff options
author | Evgeny Fadeev <evgeny.fadeev@gmail.com> | 2012-10-16 22:04:44 -0300 |
---|---|---|
committer | Evgeny Fadeev <evgeny.fadeev@gmail.com> | 2012-10-16 22:04:44 -0300 |
commit | 5ebe59eca6b06119a986532a89666400da563001 (patch) | |
tree | 8ea5026b676da65ad752272f452ea428e28de0c5 /askbot/search | |
parent | 1a1d123f4555450b764fce41835c35055d0e84c4 (diff) | |
download | askbot-5ebe59eca6b06119a986532a89666400da563001.tar.gz askbot-5ebe59eca6b06119a986532a89666400da563001.tar.bz2 askbot-5ebe59eca6b06119a986532a89666400da563001.zip |
fixed a bug where user cannot be deleted with postgres db backend in the django admin
Diffstat (limited to 'askbot/search')
-rw-r--r-- | askbot/search/postgresql/user_profile_search_16102012.plsql | 98 |
1 files changed, 98 insertions, 0 deletions
diff --git a/askbot/search/postgresql/user_profile_search_16102012.plsql b/askbot/search/postgresql/user_profile_search_16102012.plsql new file mode 100644 index 00000000..ca00a5d3 --- /dev/null +++ b/askbot/search/postgresql/user_profile_search_16102012.plsql @@ -0,0 +1,98 @@ +/* +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 + IF (TG_OP = 'INSERT') THEN + user_query = 'UPDATE auth_user SET username=username WHERE ' || + 'id=' || new.user_id; + ELSE + user_query = 'UPDATE auth_user SET username=username WHERE ' || + 'id=' || old.user_id; + END IF; + /* just trigger the tsv update on user */ + EXECUTE user_query; + RETURN NULL; +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); |