Index: openacs.org-dev/packages/acs-content-repository/sql/postgresql/content-search.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs.org-dev/packages/acs-content-repository/sql/postgresql/content-search.sql,v
diff -u -N -r1.1 -r1.2
--- openacs.org-dev/packages/acs-content-repository/sql/postgresql/content-search.sql	9 Jul 2002 17:34:56 -0000	1.1
+++ openacs.org-dev/packages/acs-content-repository/sql/postgresql/content-search.sql	28 Oct 2002 20:13:30 -0000	1.2
@@ -1,3 +1,4 @@
+-- @cvs-id $Id$ 
 ------------------------------------------------------------
 -- declare CR as a content provider for search/indexing interface
 ------------------------------------------------------------
@@ -74,34 +75,69 @@
 -- triggers queue search interface to modify search index after content
 -- changes.
 
+-- DaveB: We only want to index live_revisions 2002-09-26
+
 create function content_search__itrg ()
 returns opaque as '
 begin
-    perform search_observer__enqueue(new.revision_id,''INSERT'');
+if (select live_revision from cr_items where item_id=new.item_id) = new.revision_id then	
+	perform search_observer__enqueue(new.revision_id,''INSERT'');
+    end if;
     return new;
 end;' language 'plpgsql';
 
 create function content_search__dtrg ()
 returns opaque as '
+
+declare
+    v_live_revision integer;
+
 begin
-    perform search_observer__enqueue(old.revision_id,''DELETE'');
+    select into v_live_revision live_revision from
+	cr_items where item_id=old.item_id;
+    if old.revision_id=v_live_revision then
+	perform search_observer__enqueue(old.revision_id,''DELETE'');
+    end if;
     return old;
 end;' language 'plpgsql';
 
 create function content_search__utrg ()
 returns opaque as '
+declare
+    v_live_revision     integer;
 begin
-    insert into search_observer_queue (
-        object_id,
-	event
-    ) values (
-        old.revision_id,
-        ''UPDATE''
-    );
+    select into v_live_revision live_revision from
+	cr_items where item_id=old.revision_id;
+    if old.revision_id=v_live_revision then
+	insert into search_observer_queue (
+            object_id,
+	    event
+        ) values (
+old.revision_id,
+            ''UPDATE''
+        );
+    end if;
     return new;
 end;' language 'plpgsql';
 
 
+-- we need new triggers on cr_items to index when a live revision
+-- changes -DaveB 2002-09-26
+
+create function content_item_search__utrg ()
+returns opaque as '
+begin
+    if new.live_revision is not null and coalesce(old.live_revision,0) <> new.live_revision then
+	perform search_observer__enqueue(new.live_revision,''INSERT'');		
+    end if;
+
+    if old.live_revision is not null and old.live_revision <> coalesce(new.live_revision,0) then
+	perform search_observer__enqueue(old.live_revision,''DELETE'');
+    end if;
+
+    return new;
+end;' language 'plpgsql';
+
 create trigger content_search__itrg after insert on cr_revisions
 for each row execute procedure content_search__itrg (); 
 
@@ -111,6 +147,7 @@
 create trigger content_search__utrg after update on cr_revisions
 for each row execute procedure content_search__utrg (); 
 
-
-
-
+-- JCD: on create this trigger should not exist so lets not drop it
+-- drop trigger content_item_search__utrg on cr_items;
+create trigger content_item_search__utrg before update on cr_items
+for each row execute procedure content_item_search__utrg ();