improved FTS. make fts indexes update faster and take the load out of the node table.

git-svn-id: http://svn.osqa.net/svnroot/osqa/trunk@56 0cfe37f9-358a-4d5e-be75-b63607b5c754

diff --git a/forum/migrations/0021_create_new_pg_fts_triggers.py b/forum/migrations/0021_create_new_pg_fts_triggers.py
index bb5e04f171c44ae66c4b302069da8b08869194ba..16ef25b684a25710aa373bd9789a36011894a163 100644 (file)
--- a/forum/migrations/0021_create_new_pg_fts_triggers.py
+++ b/forum/migrations/0021_create_new_pg_fts_triggers.py
@@ -12,7 +12,6 @@ class Migration(SchemaMigration):
             try:\r
                 import forum_modules.pgfulltext.management\r
             except Exception, e:\r
             try:\r
                 import forum_modules.pgfulltext.management\r
             except Exception, e:\r

-                print e\r

                 pass\r
     \r
     \r
                 pass\r
     \r
     \r

diff --git a/forum/settings/__init__.py b/forum/settings/__init__.py
index e7f3e39b5759eb6f2df95ec31f1c8d5f56ecafba..2446496edf195c94707f70c01b9896bd9dc0bc43 100644 (file)
--- a/forum/settings/__init__.py
+++ b/forum/settings/__init__.py
@@ -5,7 +5,7 @@ from forms import ImageFormWidget
 from django.forms.widgets import Textarea
 from django.utils.translation import ugettext_lazy as _
 
 from django.forms.widgets import Textarea
 from django.utils.translation import ugettext_lazy as _
 

-INTERNAL_VERSION = Setting('INTERNAL_VERSION', "201003270")
+INTERNAL_VERSION = Setting('INTERNAL_VERSION', "56")

 SETTINGS_PACK = Setting('SETTINGS_PACK', "default")
 
 from basic import *
 SETTINGS_PACK = Setting('SETTINGS_PACK', "default")
 
 from basic import *

diff --git a/forum/startup.py b/forum/startup.py
index d8d0cd57f568192fc21ef71a0c3765237192f7d6..71e33476fb34ded78fd9e96183d081d37441dc41 100644 (file)
--- a/forum/startup.py
+++ b/forum/startup.py
@@ -5,4 +5,5 @@ import forum.subscriptions
 
 from forum.modules import get_modules_script
 
 
 from forum.modules import get_modules_script
 

-get_modules_script('settings')
\ No newline at end of file
+get_modules_script('settings')
+get_modules_script('startup')
\ No newline at end of file

diff --git a/forum_modules/pgfulltext/management.py b/forum_modules/pgfulltext/management.py
deleted file mode 100644 (file)
index 60baecd..0000000
--- a/forum_modules/pgfulltext/management.py
+++ /dev/null
@@ -1,31 +0,0 @@
-import os
-
-from django.db import connection, transaction
-from django.conf import settings
-
-import forum.models
-
-def install_pg_fts(**kwargs):
-    f = open(os.path.join(os.path.dirname(__file__), 'pg_fts_install.sql'), 'r')
-
-    try:
-        cursor = connection.cursor()
-        cursor.execute(f.read())
-        transaction.commit_unless_managed()
-    except:
-        pass
-    finally:
-        cursor.close()
-
-    f.close()
-
-if settings.DATABASE_ENGINE in ('postgresql_psycopg2', 'postgresql', ):
-    from django.db.models.signals import post_syncdb
-    post_syncdb.connect(install_pg_fts, sender=forum.models, weak=False)
-
-
-try:
-    from south.signals import post_migrate
-    post_migrate.connect(install_pg_fts, weak=False)
-except:
-    pass
\ No newline at end of file

diff --git a/forum_modules/pgfulltext/pg_fts_install.sql b/forum_modules/pgfulltext/pg_fts_install.sql
index 21cbf959efd766504284700523291abc7658ef69..9ac5db9fef998183019a8144ecfb85e1800b5d7d 100644 (file)
--- a/forum_modules/pgfulltext/pg_fts_install.sql
+++ b/forum_modules/pgfulltext/pg_fts_install.sql
@@ -21,64 +21,66 @@
   CREATE OR REPLACE FUNCTION node_ranking(node_id int, srch text) RETURNS float AS $$
   declare
      v tsvector;
   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
   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);
+     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 NOT deleted;
+
+    IF child_count > 0 THEN
+       FOR r in SELECT * FROM forum_node WHERE abs_parent_id = node_id  AND NOT deleted LOOP
+           SELECT tsv INTO cv FROM forum_noderevision WHERE id = r.active_revision_id;
+           v :=(v || cv);
+       END LOOP;
+     END IF;
+     RAISE NOTICE '%', v;
+
+     RETURN ts_rank_cd(v, plainto_tsquery('english', srch), 32);

   end
   $$ LANGUAGE plpgsql;
 
   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;
+select node_ranking(50, 'free');
+
+

 
   CREATE OR REPLACE FUNCTION set_node_tsv() RETURNS TRIGGER AS $$
   begin
 
   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');
       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'); 
-    END IF;
+

     RETURN new;
   end
   $$ LANGUAGE plpgsql;
 
     RETURN new;
   end
   $$ LANGUAGE plpgsql;
 

-  CREATE OR REPLACE FUNCTION public.create_tsv_node_column ()
+  CREATE OR REPLACE FUNCTION public.create_tsv_noderevision_column ()

       RETURNS TEXT
       AS $$
       RETURNS TEXT
       AS $$

-          ALTER TABLE forum_node ADD COLUMN tsv tsvector;
+          ALTER TABLE forum_noderevision ADD COLUMN tsv tsvector;

 
           CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
 
           CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE

-               ON forum_node FOR EACH ROW EXECUTE PROCEDURE set_node_tsv();
+               ON forum_noderevision FOR EACH ROW EXECUTE PROCEDURE set_node_tsv();

 
 

-             CREATE INDEX node_tsv ON forum_node USING gin(tsv);
+             CREATE INDEX noderevision_tsv ON forum_noderevision USING gin(tsv);

 
           SELECT 'tsv column created'::TEXT;
       $$
   LANGUAGE 'sql';
 
   SELECT CASE WHEN
 
           SELECT 'tsv column created'::TEXT;
       $$
   LANGUAGE 'sql';
 
   SELECT CASE WHEN

-     (SELECT true::BOOLEAN FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'forum_node') 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
   THEN
      (SELECT 'Tsv column already exists'::TEXT)
   ELSE

-     (SELECT public.create_tsv_node_column())
+     (SELECT public.create_tsv_noderevision_column())

 
   END;
 
 
   END;
 

-  DROP FUNCTION public.create_tsv_node_column ();
+  DROP FUNCTION public.create_tsv_noderevision_column();

 
 

-  UPDATE forum_node SET id=id WHERE TRUE;
+  UPDATE forum_noderevision SET id=id WHERE TRUE;
\ No newline at end of file

diff --git a/forum_modules/pgfulltext/startup.py b/forum_modules/pgfulltext/startup.py
new file mode 100644 (file)
index 0000000..14eea8e
--- /dev/null
+++ b/forum_modules/pgfulltext/startup.py
@@ -0,0 +1,38 @@
+import os\r
+from forum.models import KeyValue\r
+from django.db import connection, transaction\r
+\r
+KEY = 'PG_FTSTRIGGERS_VERSION'\r
+VERSION = 2\r
+install = False\r
+\r
+try:\r
+    version = KeyValue.objects.get(key=KEY).value\r
+    if version < VERSION:\r
+        install = True\r
+except:\r
+    install = True\r
+\r
+\r
+if install:\r
+    f = open(os.path.join(os.path.dirname(__file__), 'pg_fts_install.sql'), 'r')\r
+\r
+    try:\r
+        cursor = connection.cursor()\r
+        cursor.execute(f.read())\r
+        transaction.commit_unless_managed()\r
+    except:\r
+        pass\r
+    finally:\r
+        cursor.close()\r
+\r
+    f.close()\r
+\r
+    try:\r
+        kv = KeyValue.objects.get(key=KEY)\r
+    except:\r
+        kv = KeyValue(key=KEY)\r
+\r
+    kv.value = VERSION\r
+    kv.save()\r
+        
\ No newline at end of file