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
old mode 100755 (executable)
new mode 100644 (file)
index 72eca51..9a6b60d
--- a/forum_modules/pgfulltext/pg_fts_install.sql
+++ b/forum_modules/pgfulltext/pg_fts_install.sql
@@ -1,38 +1,82 @@
-ALTER TABLE question ADD COLUMN tsv tsvector;\r
-\r
-CREATE OR REPLACE FUNCTION public.create_plpgsql_language ()\r
-    RETURNS TEXT\r
-    AS $$\r
-        CREATE LANGUAGE plpgsql;\r
-        SELECT 'language plpgsql created'::TEXT;\r
-    $$\r
-LANGUAGE 'sql';\r
-\r
-SELECT CASE WHEN\r
-      (SELECT true::BOOLEAN\r
-         FROM pg_language\r
-        WHERE lanname='plpgsql')\r
-    THEN\r
-      (SELECT 'language already installed'::TEXT)\r
-    ELSE\r
-      (SELECT public.create_plpgsql_language())\r
-    END;\r
-\r
-DROP FUNCTION public.create_plpgsql_language ();\r
-\r
-CREATE OR REPLACE FUNCTION set_question_tsv() RETURNS TRIGGER AS $$\r
-begin\r
-  new.tsv :=\r
-     setweight(to_tsvector('english', coalesce(new.tagnames,'')), 'A') ||\r
-     setweight(to_tsvector('english', coalesce(new.title,'')), 'B') ||\r
-     setweight(to_tsvector('english', coalesce(new.summary,'')), 'C');\r
-  RETURN new;\r
-end\r
-$$ LANGUAGE plpgsql;\r
-\r
-CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE\r
-ON question FOR EACH ROW EXECUTE PROCEDURE set_question_tsv();\r
-\r
-  CREATE INDEX question_tsv ON question USING gin(tsv);\r
-\r
-UPDATE question SET title = title;\r
+  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;
+     cv tsvector;
+     rev_id int;
+     child_count int;
+     r record;
+  begin
+     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_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 forum_noderevision FOR EACH ROW EXECUTE PROCEDURE set_node_tsv();
+
+             CREATE INDEX noderevision_tsv ON forum_noderevision USING gin(tsv);
+
+          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 = 'forum_noderevision') AND attname = 'tsv')
+  THEN
+     (SELECT 'Tsv column already exists'::TEXT)
+  ELSE
+     (SELECT public.create_tsv_noderevision_column())
+
+  END;
+
+  DROP FUNCTION public.create_tsv_noderevision_column();
+
+  UPDATE forum_noderevision SET id=id WHERE TRUE;
\ No newline at end of file