Some fixes in the importer.

[osqa.git] / forum_modules / pgfulltext / pg_fts_install.sql

diff --git a/forum_modules/pgfulltext/pg_fts_install.sql b/forum_modules/pgfulltext/pg_fts_install.sql
index 60087894f8c947cd67cc292c1afafda1e0abcf77..9a6b60d20b49d5cd8affc3dd8dce6d8b508c97c5 100644 (file)
--- a/forum_modules/pgfulltext/pg_fts_install.sql
+++ b/forum_modules/pgfulltext/pg_fts_install.sql
@@ -18,37 +18,65 @@
 
   DROP FUNCTION public.create_plpgsql_language ();
 
-  CREATE OR REPLACE FUNCTION set_question_tsv() RETURNS TRIGGER AS $$
+  CREATE OR REPLACE FUNCTION node_ranking(node_id int, srch text) RETURNS float AS $$
+  declare
+     v tsvector;
+     cv tsvector;
+     rev_id int;
+     child_count int;
+     r record;
   begin
-    new.tsv :=
-       setweight(to_tsvector('english', coalesce(new.tagnames,'')), 'A') ||
-       setweight(to_tsvector('english', coalesce(new.title,'')), 'B') ||
-       setweight(to_tsvector('english', coalesce(new.html,'')), 'C');
+     SELECT active_revision_id INTO rev_id FROM forum_node WHERE id = node_id;
+     SELECT tsv INTO v FROM forum_noderevision WHERE id = rev_id;
+
+    SELECT  count(*) INTO child_count FROM forum_node WHERE abs_parent_id = node_id AND deleted_id IS NULL;
+
+    IF child_count > 0 THEN
+       FOR r in SELECT * FROM forum_node WHERE abs_parent_id = node_id  AND deleted_id IS NULL LOOP
+           SELECT tsv INTO cv FROM forum_noderevision WHERE id = r.active_revision_id;
+           v :=(v || cv);
+       END LOOP;
+     END IF;
+
+     RETURN ts_rank_cd(v, plainto_tsquery('english', srch), 32);
+  end
+  $$ LANGUAGE plpgsql;
+
+  CREATE OR REPLACE FUNCTION set_node_tsv() RETURNS TRIGGER AS $$
+  begin
+      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');
+
     RETURN new;
   end
   $$ LANGUAGE plpgsql;
 
-  CREATE OR REPLACE FUNCTION public.create_tsv_question_column ()
-      RETURNS TEXT
-      AS $$
-          ALTER TABLE question ADD COLUMN tsv tsvector;
-          
+  CREATE OR REPLACE FUNCTION public.create_tsv_noderevision_column () RETURNS TEXT AS $$
+  begin
+          ALTER TABLE forum_noderevision ADD COLUMN tsv tsvector;
+
+          DROP TRIGGER IF EXISTS tsvectorupdate ON forum_noderevision;
+
           CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
-               ON question FOR EACH ROW EXECUTE PROCEDURE set_question_tsv();
+               ON forum_noderevision FOR EACH ROW EXECUTE PROCEDURE set_node_tsv();
 
-             CREATE INDEX question_tsv ON question USING gin(tsv);
+             CREATE INDEX noderevision_tsv ON forum_noderevision USING gin(tsv);
 
-          SELECT 'tsv column created'::TEXT;
-      $$
-  LANGUAGE 'sql';
+          RETURN 'tsv column created'::TEXT;
+  end
+  $$ LANGUAGE plpgsql;
 
   SELECT CASE WHEN
-     (SELECT true::BOOLEAN FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'question') AND attname = 'tsv')
+     (SELECT true::BOOLEAN FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'forum_noderevision') AND attname = 'tsv')
   THEN
      (SELECT 'Tsv column already exists'::TEXT)
   ELSE
-     (SELECT public.create_tsv_question_column())
+     (SELECT public.create_tsv_noderevision_column())
 
   END;
 
-  DROP FUNCTION public.create_tsv_question_column ();
+  DROP FUNCTION public.create_tsv_noderevision_column();
+
+  UPDATE forum_noderevision SET id=id WHERE TRUE;
\ No newline at end of file