- CREATE OR REPLACE FUNCTION public.create_plpgsql_language ()
- RETURNS TEXT
- AS $$
- CREATE LANGUAGE plpgsql;
- SELECT 'language plpgsql created'::TEXT;
- $$
- LANGUAGE 'sql';
-
- SELECT CASE WHEN
- (SELECT true::BOOLEAN
- FROM pg_language
- WHERE lanname='plpgsql')
- THEN
- (SELECT 'language already installed'::TEXT)
- ELSE
- (SELECT public.create_plpgsql_language())
- END;
-
- DROP FUNCTION public.create_plpgsql_language ();
-
- CREATE OR REPLACE FUNCTION node_ranking(node_id int, srch text) RETURNS float AS $$
- declare
- v tsvector;
- begin
- SELECT tsv INTO v FROM forum_node WHERE id = node_id;
- RETURN ts_rank_cd(v || children_tsv(node_id), plainto_tsquery(srch), 32);
- end
- $$ LANGUAGE plpgsql;
-
- CREATE OR REPLACE FUNCTION children_tsv(id int) RETURNS tsvector AS $$
- declare
- v tsvector := ''::tsvector;
- r record;
- begin
- FOR r IN SELECT * FROM forum_node WHERE parent_id = id LOOP
- v := v || r.tsv || children_tsv(r.id);
- END LOOP;
- RETURN v;
- end
- $$ LANGUAGE plpgsql;
-
- CREATE OR REPLACE FUNCTION set_node_tsv() RETURNS TRIGGER AS $$
- begin
- IF (tg_op = 'INSERT') THEN
- new.tsv :=
- setweight(to_tsvector('english', coalesce(new.tagnames,'')), 'A') ||
- setweight(to_tsvector('english', coalesce(new.title,'')), 'B') ||
- setweight(to_tsvector('english', coalesce(new.body,'')), 'C');
- ELSIF (new.active_revision_id <> old.active_revision_id) OR (new.tsv IS NULL) THEN
- new.tsv :=
- setweight(to_tsvector('english', coalesce(new.tagnames,'')), 'A') ||
- setweight(to_tsvector('english', coalesce(new.title,'')), 'B') ||
- setweight(to_tsvector('english', coalesce(new.body,'')), 'C');
+CREATE OR REPLACE FUNCTION public.create_plpgsql_language ()
+ RETURNS TEXT
+ AS $$
+ CREATE LANGUAGE plpgsql;
+ SELECT 'language plpgsql created'::TEXT;
+ $$
+LANGUAGE 'sql';
+
+SELECT CASE WHEN
+ (SELECT true::BOOLEAN
+ FROM pg_language
+ WHERE lanname='plpgsql')
+ THEN
+ (SELECT 'language already installed'::TEXT)
+ ELSE
+ (SELECT public.create_plpgsql_language())
+ END;
+
+DROP FUNCTION public.create_plpgsql_language ();
+
+CREATE OR REPLACE FUNCTION public.drop_tsv_noderevision_column () RETURNS VOID AS $$
+begin
+ ALTER TABLE forum_noderevision DROP COLUMN tsv;
+ DROP TRIGGER IF EXISTS tsvectorupdate ON forum_noderevision;
+end
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION public.tsv_noderevision_column_exists() RETURNS int AS $$
+ SELECT COUNT(attname)::int FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'forum_noderevision') AND attname = 'tsv';
+$$ LANGUAGE 'sql';
+
+select case when public.tsv_noderevision_column_exists()>0 then public.drop_tsv_noderevision_column()end;
+
+drop function drop_tsv_noderevision_column();
+drop function tsv_noderevision_column_exists();
+
+CREATE OR REPLACE FUNCTION set_doctable_tsv() RETURNS TRIGGER AS $$
+declare
+ root_id int;
+ doc tsvector;
+ rcount int;
+ cv tsvector;
+begin
+ SELECT abs_parent_id INTO root_id FROM forum_node WHERE id = new.node_id;
+
+ IF root_id IS NULL THEN
+ root_id := new.node_id;
+ END IF;
+
+ SELECT count(*)::int INTO rcount FROM forum_node WHERE id = root_id;
+
+ IF rcount = 0 THEN
+ return new;
END IF;
- RETURN new;
- end
- $$ LANGUAGE plpgsql;
- CREATE OR REPLACE FUNCTION public.create_tsv_node_column ()
- RETURNS TEXT
- AS $$
- ALTER TABLE forum_node ADD COLUMN tsv tsvector;
+ SELECT
+ setweight(to_tsvector('english', coalesce(tagnames,'')), 'A') ||
+ setweight(to_tsvector('english', coalesce(title,'')), 'B') ||
+ setweight(to_tsvector('english', coalesce(body,'')), 'C') INTO doc
+ FROM forum_node WHERE id = root_id;
+
+ SELECT count(*)::int INTO rcount FROM forum_node WHERE abs_parent_id = root_id AND (NOT state_string LIKE '%%deleted%%');
+
+ IF rcount > 0 THEN
+ FOR cv in SELECT setweight(to_tsvector('english', coalesce(body,'')), 'C') FROM forum_node WHERE abs_parent_id = root_id AND (NOT state_string LIKE '%%deleted%%') LOOP
+ doc :=(doc || cv);
+ END LOOP;
+ END IF;
+
+ SELECT count(*)::int INTO rcount FROM forum_rootnode_doc WHERE node_id = root_id;
+
+ IF rcount > 0 THEN
+ UPDATE forum_rootnode_doc SET document = doc WHERE node_id = root_id;
+ ELSE
+ INSERT INTO forum_rootnode_doc (node_id, document) VALUES (root_id, doc);
+ END IF;
+
+ RETURN new;
+end
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION public.build_doc_table() RETURNS VOID as $$
+ CREATE TABLE forum_rootnode_doc
+ (
+ node_id integer,
+ "document" tsvector,
+ PRIMARY KEY (node_id),
+ FOREIGN KEY (node_id) REFERENCES forum_node (id) ON UPDATE NO ACTION ON DELETE NO ACTION
+ ) WITH (OIDS=FALSE);
+
+ DROP TRIGGER IF EXISTS tsvectorupdate ON forum_noderevision;
+
+ CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
+ ON forum_noderevision FOR EACH ROW EXECUTE PROCEDURE set_doctable_tsv();
+
+ CREATE INDEX doctable_tsv ON forum_rootnode_doc USING gin(document);
+$$ LANGUAGE 'sql';
- CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
- ON forum_node FOR EACH ROW EXECUTE PROCEDURE set_node_tsv();
+CREATE OR REPLACE FUNCTION public.doc_table_exists() RETURNS int AS $$
+ SELECT COUNT(table_name)::int FROM information_schema.tables WHERE table_name = 'forum_rootnode_doc';
+$$ LANGUAGE 'sql';
- CREATE INDEX node_tsv ON forum_node USING gin(tsv);
+select case when public.doc_table_exists()=0 then public.build_doc_table()end;
- SELECT 'tsv column created'::TEXT;
- $$
- LANGUAGE 'sql';
+drop function build_doc_table();
+drop function doc_table_exists();
- SELECT CASE WHEN
- (SELECT true::BOOLEAN FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'forum_node') AND attname = 'tsv')
- THEN
- (SELECT 'Tsv column already exists'::TEXT)
- ELSE
- (SELECT public.create_tsv_node_column())
+CREATE OR REPLACE FUNCTION rank_exact_matches(rank float) RETURNS float AS $$
+begin
+ IF rank = 0 THEN
+ return 1;
+ ELSE
+ return rank;
+ END IF;
- END;
+end
+$$ LANGUAGE plpgsql;
- DROP FUNCTION public.create_tsv_node_column ();
+CREATE OR REPLACE FUNCTION public.rebuild_index() RETURNS VOID as $$
+ DECLARE
+ r integer;
+ BEGIN
+ FOR r IN SELECT active_revision_id FROM forum_node WHERE node_type = 'question' LOOP
+ UPDATE forum_noderevision SET id = id WHERE id = r;
+ END LOOP;
+ END
+$$ LANGUAGE 'plpgsql';
- UPDATE forum_node SET id=id WHERE TRUE;
+SELECT rebuild_index();
projects / osqa.git / blobdiff