-- 
-- procedure content_type__refresh_view/1
-- 
CREATE OR REPLACE FUNCTION content_type__refresh_view(
   refresh_view__content_type varchar
) RETURNS integer AS $$
DECLARE
  cols                                 varchar default '';
  tabs                                 varchar default '';
  joins                                varchar default '';
  v_table_name                         varchar;
  join_rec                             record;
BEGIN

  for join_rec in select ot2.table_name, ot2.id_column, tree_level(ot2.tree_sortkey) as level
                  from acs_object_types ot1, acs_object_types ot2
                  where ot2.object_type <> 'acs_object'
                    and ot2.object_type <> 'content_revision'
                    and lower(ot2.table_name) <> 'acs_objects'
                    and lower(ot2.table_name) <> 'cr_revisions'
                    and ot1.object_type = refresh_view__content_type
                    and ot1.tree_sortkey between ot2.tree_sortkey and tree_right(ot2.tree_sortkey)
                  order by ot2.tree_sortkey desc
  LOOP
    if join_rec.table_name is not null then
        cols := cols || ', ' || join_rec.table_name || '.*';
        tabs := tabs || ', ' || join_rec.table_name;
        joins := joins || ' and acs_objects.object_id = ' ||
                 join_rec.table_name || '.' || join_rec.id_column;
    end if;
  end loop;

  -- Since we allow null table name use object type if table name is null so
  -- we still can have a view.
  select coalesce(table_name,object_type) into v_table_name from acs_object_types
    where object_type = refresh_view__content_type;

  if length(v_table_name) > 57 then
      raise exception 'Table name cannot be longer than 57 characters, because that causes conflicting rules when we create the views.';
  end if;

  -- create the input view (includes content columns)

  if table_exists(v_table_name || 'i') then
     execute 'drop view ' || v_table_name || 'i' || ' CASCADE';
  end if;

  -- FIXME:  need to look at content_revision__get_content.  Since the CR
  -- can store data in a lob, a text field or in an external file, getting
  -- the data attribute for this view will be problematic.

  execute 'create view ' || v_table_name ||
    'i as select  acs_objects.object_id,
 acs_objects.object_type,
 acs_objects.title as object_title,
 acs_objects.package_id as object_package_id,
 acs_objects.context_id,
 acs_objects.security_inherit_p,
 acs_objects.creation_user,
 acs_objects.creation_date,
 acs_objects.creation_ip,
 acs_objects.last_modified,
 acs_objects.modifying_user,
 acs_objects.modifying_ip,
 cr.revision_id, cr.title, cr.item_id,
    content_revision__get_content(cr.revision_id) as data,
    cr_text.text_data as text,
    cr.description, cr.publish_date, cr.mime_type, cr.nls_language' ||
    cols ||
    ' from acs_objects, cr_revisions cr, cr_text' || tabs || ' where
    acs_objects.object_id = cr.revision_id ' || joins;

  -- create the output view (excludes content columns to enable SELECT *)

  if table_exists(v_table_name || 'x') then
     execute 'drop view ' || v_table_name || 'x cascade';
  end if;

  execute 'create view ' || v_table_name ||
    'x as select  acs_objects.object_id,
 acs_objects.object_type,
 acs_objects.title as object_title,
 acs_objects.package_id as object_package_id,
 acs_objects.context_id,
 acs_objects.security_inherit_p,
 acs_objects.creation_user,
 acs_objects.creation_date,
 acs_objects.creation_ip,
 acs_objects.last_modified,
 acs_objects.modifying_user,
 acs_objects.modifying_ip,
 cr.revision_id, cr.title, cr.item_id,
    cr.description, cr.publish_date, cr.mime_type, cr.nls_language,
    i.name, i.parent_id' ||
    cols ||
    ' from acs_objects, cr_revisions cr, cr_items i, cr_text' || tabs ||
    ' where acs_objects.object_id = cr.revision_id
      and cr.item_id = i.item_id' || joins;

  PERFORM content_type__refresh_trigger(refresh_view__content_type);

-- exception
--   when others then
--     dbms_output.put_line('Error creating attribute view or trigger for'
--  || content_type);

  return 0;
END;
$$ LANGUAGE plpgsql;


-- upgrade types

SELECT t2.object_type, content_type__refresh_view(t2.object_type)
from acs_object_types t1, acs_object_types t2
where t2.tree_sortkey between t1.tree_sortkey and
tree_right(t1.tree_sortkey) and t1.object_type = 'content_revision';