--
-- Copyright (C) 2004 Red Hat Inc. All Rights Reserved.
--
-- The contents of this file are subject to the CCM Public
-- License (the "License"); you may not use this file except in
-- compliance with the License. You may obtain a copy of the
-- License at http://www.redhat.com/licenses/ccmpl.html.
--
-- Software distributed under the License is distributed on an
-- "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express
-- or implied. See the License for the specific language governing
-- rights and limitations under the License.
--
-- $Id: //cms/dev/sql/ccm-cms/postgres/upgrade/6.0.1-6.1.0/triggers-cms_items_ancestors.sql#3 $
-- $DateTime: 2004/04/07 16:07:11 $

-- Author: Daniel Berrange (berrange@redhat.com)
-- Since:  2004-01-15
-- See:    https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=109718#c14
--         https://bugzilla.redhat.com/bugzilla/attachment.cgi?id=97029&action=view

create or replace function cms_items_in_fn() returns trigger as '
  declare
    parents_ancestors varchar;
  begin
    if new.parent_id isnull then
      new.ancestors := new.item_id || ''/'';
    else
      select ancestors into parents_ancestors
      from cms_items
      where item_id = new.parent_id;

      new.ancestors := parents_ancestors || new.item_id || ''/'';
    end if;
    return new;
  end;
' language 'plpgsql';


create trigger cms_items_in before insert on cms_items
  for each row execute procedure cms_items_in_fn();


create or replace function cms_items_up_fn() returns trigger as '
  declare
    parents_ancestors varchar;
  begin
    if new.parent_id isnull and old.parent_id isnull then
      return new;
    end if;
    if new.parent_id = old.parent_id then
      return new;
    end if;

    if new.parent_id isnull then
      new.ancestors := new.item_id || ''/'';
    else
      select ancestors into parents_ancestors
      from cms_items
      where item_id = new.parent_id;

      new.ancestors := parents_ancestors  || new.item_id  || ''/'';
    end if;

    -- Although this update does recursively trigger calls to
    -- cms_items_up_fn, the recursion is nipped in the bud, because we
    -- are not updating the parent_id column.  See above.

    -- use dynamic sql to force parser to evaluate stmt each time and 
    -- avoid seq_scan on cms_items
    EXECUTE
    ''update cms_items '' ||
    ''set ancestors = '''''' || new.ancestors || '''''' || substr(ancestors, char_length('''''' || old.ancestors || '''''')+1 ) '' ||
    ''where ancestors like '''''' || old.ancestors || ''%'''''' || '' and item_id <> '' || new.item_id || '''';

    return new;
  end;
' language 'plpgsql';



create trigger cms_items_up before update on cms_items
  for each row execute procedure cms_items_up_fn();
