-- set serveroutput on
create table tst_ids (
       id    integer,
       name  varchar(400)
);

create function dump_cr_items() returns integer as '
declare
        v_rec   record;
        v_str   varchar default ''\\n'';
begin
        for v_rec in select repeat('' '',(tree_level(tree_sortkey)*2 - 1)) || name as name,
                            item_id, parent_id, 
                            tree_level(tree_sortkey) as level,
                            tree_sortkey
                       from cr_items
                     order by tree_sortkey
        LOOP
            v_str := v_str || rpad(v_rec.name,40) || rpad(v_rec.item_id,6) ||
                     rpad(v_rec.parent_id,6) || rpad(v_rec.level,6) || rpad(v_rec.tree_sortkey,30) || ''\\n'';
        end LOOP;

        raise notice ''%'', v_str;
        return null;

end;' language 'plpgsql';

create function content_test__put_line(text) returns integer as '
begin
        raise NOTICE ''%'', $1;
        return null;
end;' language 'plpgsql';

create function cast_char(boolean) returns char as '
begin
        return case when $1 then ''t''::char else ''f''::char end;
end;' language 'plpgsql';

create function test_content_create() returns integer as '
declare
  folder_id		cr_folders.folder_id%TYPE;
  folder_b_id		cr_folders.folder_id%TYPE;
  sub_folder_id		cr_folders.folder_id%TYPE;
  sub_sub_folder_id	cr_folders.folder_id%TYPE;
  item_id		cr_items.item_id%TYPE;
  simple_item_id	cr_items.item_id%TYPE;
  live_revision_id	cr_revisions.revision_id%TYPE;
  late_revision_id	cr_revisions.revision_id%TYPE;
  item_template_id	cr_templates.template_id%TYPE;
  type_template_id	cr_templates.template_id%TYPE;
  def_type_template_id	cr_templates.template_id%TYPE;
  dum_template_id	cr_templates.template_id%TYPE;

  symlink_a_id		cr_symlinks.symlink_id%TYPE;
  symlink_b_id		cr_symlinks.symlink_id%TYPE;

  found_folder_id	cr_folders.folder_id%TYPE;
begin

   -- create folders and an item

   folder_id         := content_folder__new(''grandpa'',''Grandpa'',NULL,-100);
   folder_b_id       := content_folder__new(''grandma'',''Grandma'',NULL,-100);

   sub_folder_id     := content_folder__new(''pa'',''Pa'',NULL,folder_id);

   sub_sub_folder_id := content_folder__new(''me'',''Me'',NULL,sub_folder_id);

   item_id           := content_item__new(''puppy'',sub_sub_folder_id);

   simple_item_id := content_item__new(
	''bunny'',
	sub_sub_folder_id,
	''Bugs Bunny'',
	''Simple (Revisionless) Item Test'',
	''Simple (Revisionless) Item Test Text''
   );

   live_revision_id := content_revision__new(
	''Live Revision of Puppy'',
	''Live Revision of Puppy Description'',
	to_date(''1999-08-12'',''YYYY-MM-DD''),
	''text/html'',
	''Text for Live Revision of Puppy'',
	item_id
   );

   late_revision_id := content_revision__new(
	''Latest Revision of Puppy'',
	''Latest Revision of Puppy Description'',
	to_date(''2001-09-22'',''YYYY-MM-DD''),
	''text/html'',
	''Text for Latest Revision of Puppy'',
	item_id
   );

   item_template_id := content_template__new(
        ''Item Template''
   );

   type_template_id := content_template__new(
	''Type Template''
   );

   def_type_template_id := content_template__new(
	''Dumb Default Type Template''
   );

   dum_template_id := content_template__new(
	''Default Type Template''
   );

   insert into tst_ids values (folder_id,''folder_id'');
   insert into tst_ids values (folder_b_id,''folder_b_id'');
   insert into tst_ids values (sub_folder_id,''sub_folder_id'');
   insert into tst_ids values (sub_sub_folder_id,''sub_sub_folder_id'');
   insert into tst_ids values (item_id,''item_id'');
   insert into tst_ids values (simple_item_id,''simple_item_id'');
   insert into tst_ids values (live_revision_id,''live_revision_id'');
   insert into tst_ids values (late_revision_id,''late_revision_id'');
   insert into tst_ids values (item_template_id,''item_template_id'');
   insert into tst_ids values (type_template_id,''type_template_id'');
   insert into tst_ids values (def_type_template_id,''def_type_template_id'');
   insert into tst_ids values (dum_template_id,''dum_template_id'');

   PERFORM content_test__put_line(''-------------------------------------'');
   PERFORM content_test__put_line(''CREATING CONTENT FOLDERS AND ITEMS...'');
   PERFORM content_test__put_line(''...all tests passed'');
   PERFORM content_test__put_line(''Folder grandpa is '' || folder_id);
   PERFORM content_test__put_line(''Folder grandma is '' || folder_b_id);
   PERFORM content_test__put_line(''Sub folder pa is '' || sub_folder_id);
   PERFORM content_test__put_line(''Sub sub folder me is '' || sub_sub_folder_id);
   PERFORM content_test__put_line(''Added item puppy to sub sub folder me at '' || item_id);
   PERFORM content_test__put_line(''Created simple item bunny to sub sub folder me at '' || simple_item_id);
   PERFORM content_test__put_line(''Added a revision to puppy at '' || live_revision_id);
   PERFORM content_test__put_line(''Added a revision to puppy at '' || late_revision_id);
   PERFORM content_test__put_line(''Created Item Template at '' || item_template_id);
   PERFORM content_test__put_line(''Created Type Template at '' || type_template_id);
   PERFORM content_test__put_line(''Created Def Type Template at '' || def_type_template_id);
   PERFORM content_test__put_line(''Created Dum Def Type Template at '' || dum_template_id);


   PERFORM content_test__put_line(''-----------------------------------'');
   PERFORM content_test__put_line(''FOLDERS AND EMPTY FOLDERS AND SUBFOLDERS'');
   PERFORM content_test__put_line(''...all tests passed'');
   PERFORM content_test__put_line(''Is folder '' || folder_id || '' empty? '' 
                                  || cast_char(content_folder__is_empty(folder_id))
           );

   PERFORM content_test__put_line(''Is folder '' || sub_sub_folder_id || 
                                  '' empty? '' ||
                                  cast_char(content_folder__is_empty(sub_sub_folder_id))
           );
   PERFORM content_test__put_line(''Is folder '' || sub_folder_id || 
                                  '' empty? '' ||
                                  cast_char(content_folder__is_empty(sub_folder_id))
           );
   PERFORM content_test__put_line(''Is folder '' || folder_b_id || 
                                  '' empty? '' ||
                                  cast_char(content_folder__is_empty(folder_b_id))
           );
   PERFORM content_test__put_line(''Is folder '' || folder_id || ''? '' ||
                                  cast_char(content_folder__is_folder(folder_id))
           );
   PERFORM content_test__put_line(''Is folder '' || item_id || ''? '' ||
                                  cast_char(content_folder__is_folder(item_id))
           );
   PERFORM content_test__put_line(''Is '' || folder_id || '' a subfolder of ''
                                  || sub_folder_id || ''? '' || 
                                  cast_char(content_folder__is_sub_folder(sub_folder_id,
                                                                folder_id
                                  ))
           );
   PERFORM content_test__put_line(''Is '' || sub_folder_id || 
                                  '' a subfolder of '' ||
                                  folder_id || ''? '' || 
                                  cast_char(content_folder__is_sub_folder(folder_id,
                                                                sub_folder_id
                                  ))
           );
   PERFORM content_test__put_line(''Is '' || sub_sub_folder_id || 
                                  '' a subfolder of '' ||
                                  folder_id || ''? '' || 
                                  cast_char(content_folder__is_sub_folder(folder_id,
                                                              sub_sub_folder_id
                                  ))
           );
   PERFORM content_test__put_line(''Is '' || sub_folder_id || 
                                  '' a subfolder of '' ||
                                  -1 || ''? '' || 
                                  cast_char(content_folder__is_sub_folder(-1,
                                                                sub_folder_id
                                  ))
           );

   PERFORM content_test__put_line(''-------------------------------------'');
   PERFORM content_test__put_line(''LIVE AND LATEST REVISIONS...'');
   PERFORM content_test__put_line(''...all tests passed'');
   PERFORM content_test__put_line(''Get live_revision_id for item puppy '' || 
                                  item_id ||
                                  '' is '' || 
                                  coalesce(content_item__get_live_revision(item_id)::varchar,''null'')
           );

   PERFORM content_item__set_live_revision(live_revision_id);

   PERFORM content_test__put_line(''Set '' || live_revision_id || 
                                  '' as the live revision for item puppy '' 
                                  || item_id);

   PERFORM content_test__put_line(''Get live_revision_id for item puppy '' 
                                  || item_id || '' is '' || 
                                  content_item__get_live_revision(item_id)
           );
   PERFORM content_test__put_line(''Get live_revision_id for item kitty '' || 
                                  simple_item_id || '' is '' || 
                                  coalesce(content_item__get_live_revision(simple_item_id)::varchar,''null'')
           );
   PERFORM content_test__put_line(''Get late_revision_id for item puppy '' || 
                                  item_id || '' is '' || 
                                  content_item__get_latest_revision(item_id)
           );
   PERFORM content_test__put_line(''Get late_revision_id for item bunny '' || 
                                  simple_item_id || '' is '' || 
                                  content_item__get_latest_revision(simple_item_id)
           );
/*
   PERFORM content_item__register_template(item_id,
                                           item_template_id,
                                           ''public''
           );
*/
   PERFORM content_type__register_template(''content_revision'',
                                           type_template_id,
                                           ''public'',
                                           ''f''
           );
   PERFORM content_type__register_template(''content_revision'',
                                           def_type_template_id,
                                           ''admin'',
                                           ''f''
           );
   PERFORM content_type__register_template(''content_revision'',
                                           dum_template_id,
                                           ''admin'',
                                           ''t''
           );
   PERFORM content_type__set_default_template(''content_revision'',
                                              def_type_template_id,
                                              ''admin''
           );

   PERFORM content_test__put_line(''-------------------------------------'');
   PERFORM content_test__put_line(''REGISTERING TEMPLATES TO ITEMS AND TYPES...'');
   PERFORM content_test__put_line(''...all tests passed'');
   PERFORM content_test__put_line(''Registered Item Template '' || 
                                  item_template_id || '' to item puppy '' || 
                                  item_id || '' with public context''
           );
   PERFORM content_test__put_line(''Registered Type Template '' || 
                                  type_template_id || '' to content_revision ''
                                  || item_id || '' with public context''
           );
   PERFORM content_test__put_line(''Registered Default Type Template '' || 
                                  def_type_template_id || 
                                  '' to content_revision '' || item_id || 
                                  '' with admin context''
           );
   PERFORM content_test__put_line(''Get template id for item puppy '' || 
                                  item_id || '' and context public is '' || 
                                  coalesce(content_item__get_template(item_id,
                                                             ''public''
                                  )::varchar,''null'')
           );
   PERFORM content_test__put_line(''Get template id for item puppy '' || 
                                  item_id || '' and context admin is '' || 
                                  content_item__get_template(item_id,
                                                             ''admin''
                                  )
           );

   found_folder_id := content_item__get_id(''grandpa/pa/me'', -100, ''f'');
   insert into tst_ids values (found_folder_id,''found_folder_id'');

   PERFORM content_test__put_line(''-------------------------------------'');
   PERFORM content_test__put_line(''LOCATING CONTENT FOLDERS AND ITEMS...'');
   PERFORM content_test__put_line(''...all tests passed!'');
   PERFORM content_test__put_line(''Found me at grandpa/pa/me: '' || 
                                  found_folder_id
           );
   PERFORM content_test__put_line(''Path for '' || found_folder_id || '' is ''
                                  || content_item__get_path(found_folder_id,null)
           );
   PERFORM content_test__put_line(''Path for puppy '' || item_id || '' is '' 
                                  || content_item__get_path(item_id,null)
           );
   PERFORM content_test__put_line(''Path for puppy '' || item_id || 
                                  '' from folder_id: '' || folder_id || 
                                  '' is '' || 
                                  content_item__get_path(item_id,folder_id)
           );
   PERFORM content_test__put_line(''Path for puppy '' || item_id ||
                                  '' from sub_folder_id: '' || 
                                  sub_folder_id || '' is '' || 
                                  content_item__get_path(item_id,
                                                        sub_folder_id
                                  )
           );
   PERFORM content_test__put_line(''Path for puppy '' || item_id
                                  || '' from sub_sub_folder_id: '' || 
                                  sub_sub_folder_id || '' is '' || 
                                  content_item__get_path(item_id,
                                                         sub_sub_folder_id
                                  )
           );
   PERFORM content_test__put_line(''Get id of item with invalid path - shouldn''''t return anything'');
   PERFORM content_test__put_line(''Found item at '' || 
                                  coalesce(content_item__get_id(''grandpa/me'', -200,''f'')::varchar,''null'')
           );
   PERFORM content_test__put_line(''Get id of item using subpath'');
   PERFORM content_test__put_line(''Found item at '' || 
                                  content_item__get_id(''pa/me/puppy'', 
                                                       folder_id,
                                                       ''f''
                                  )
           );
   PERFORM content_test__put_line(''This is the path to a folder from a subfolder'');
   PERFORM content_test__put_line(''Path for '' || sub_folder_id || 
                                  '' from sub_sub_folder_id: '' || 
                                  sub_sub_folder_id || '' is '' || 
                                  content_item__get_path(sub_folder_id,
                                                         sub_sub_folder_id
                                  )
           );
   PERFORM content_test__put_line(''This is a path to an item from a non-existant item'');
   PERFORM content_test__put_line(''Path for '' || item_id || 
                                  '' from nonexistant_id: '' || 
                                  -300 || '' is '' || 
                                  content_item__get_path(item_id,-300)
           );
   PERFORM content_test__put_line(''This is a path to an item from a non-related branch'');
   PERFORM content_test__put_line(''Path for '' || item_id || 
                                  '' from unrelated branch: '' || 
                                  folder_b_id || '' is '' || 
                                  content_item__get_path(item_id,folder_b_id)
           );

   return null;

end;' language 'plpgsql';

create function test_content_check1() returns integer as '
declare
  folder_id		cr_folders.folder_id%TYPE;
  folder_b_id		cr_folders.folder_id%TYPE;
  sub_folder_id		cr_folders.folder_id%TYPE;
  sub_sub_folder_id	cr_folders.folder_id%TYPE;
  item_id		cr_items.item_id%TYPE;
  simple_item_id	cr_items.item_id%TYPE;
  live_revision_id	cr_revisions.revision_id%TYPE;
  late_revision_id	cr_revisions.revision_id%TYPE;
  item_template_id	cr_templates.template_id%TYPE;
  type_template_id	cr_templates.template_id%TYPE;
  def_type_template_id	cr_templates.template_id%TYPE;
  dum_template_id	cr_templates.template_id%TYPE;

  symlink_a_id		cr_symlinks.symlink_id%TYPE;
  symlink_b_id		cr_symlinks.symlink_id%TYPE;

  found_folder_id	cr_folders.folder_id%TYPE;
begin
   select id into folder_id from tst_ids where name = ''folder_id'';
   select id into folder_b_id from tst_ids where name = ''folder_b_id'';
   select id into sub_folder_id from tst_ids where name = ''sub_folder_id'';
   select id into sub_sub_folder_id from tst_ids where name = ''sub_sub_folder_id'';
   select id into item_id from tst_ids where name = ''item_id'';
   select id into simple_item_id from tst_ids where name = ''simple_item_id'';
   select id into live_revision_id from tst_ids where name = ''live_revision_id'';
   select id into late_revision_id from tst_ids where name = ''late_revision_id'';
   select id into item_template_id from tst_ids where name = ''item_template_id'';
   select id into type_template_id from tst_ids where name = ''type_template_id'';
   select id into def_type_template_id from tst_ids where name = ''def_type_template_id'';
   select id into dum_template_id from tst_ids where name = ''dum_template_id'';
   select id into found_folder_id from tst_ids where name = ''found_folder_id'';
   PERFORM content_test__put_line(''-------------------------------------'');
   PERFORM content_test__put_line(''MOVING/RENAMING CONTENT FOLDERS...'');
   PERFORM content_test__put_line(''...all tests passed'');
   PERFORM content_test__put_line(''Moving me from under pa to under grandpa'');
   PERFORM content_item__move(sub_sub_folder_id, folder_id);

   return null;

end;' language 'plpgsql';

create function test_content_check2() returns integer as '
declare
  folder_id		cr_folders.folder_id%TYPE;
  folder_b_id		cr_folders.folder_id%TYPE;
  sub_folder_id		cr_folders.folder_id%TYPE;
  sub_sub_folder_id	cr_folders.folder_id%TYPE;
  item_id		cr_items.item_id%TYPE;
  simple_item_id	cr_items.item_id%TYPE;
  live_revision_id	cr_revisions.revision_id%TYPE;
  late_revision_id	cr_revisions.revision_id%TYPE;
  item_template_id	cr_templates.template_id%TYPE;
  type_template_id	cr_templates.template_id%TYPE;
  def_type_template_id	cr_templates.template_id%TYPE;
  dum_template_id	cr_templates.template_id%TYPE;

  symlink_a_id		cr_symlinks.symlink_id%TYPE;
  symlink_b_id		cr_symlinks.symlink_id%TYPE;

  found_folder_id	cr_folders.folder_id%TYPE;
begin
   select id into folder_id from tst_ids where name = ''folder_id'';
   select id into folder_b_id from tst_ids where name = ''folder_b_id'';
   select id into sub_folder_id from tst_ids where name = ''sub_folder_id'';
   select id into sub_sub_folder_id from tst_ids where name = ''sub_sub_folder_id'';
   select id into item_id from tst_ids where name = ''item_id'';
   select id into simple_item_id from tst_ids where name = ''simple_item_id'';
   select id into live_revision_id from tst_ids where name = ''live_revision_id'';
   select id into late_revision_id from tst_ids where name = ''late_revision_id'';
   select id into item_template_id from tst_ids where name = ''item_template_id'';
   select id into type_template_id from tst_ids where name = ''type_template_id'';
   select id into def_type_template_id from tst_ids where name = ''def_type_template_id'';
   select id into dum_template_id from tst_ids where name = ''dum_template_id'';
   select id into found_folder_id from tst_ids where name = ''found_folder_id'';

   PERFORM content_test__put_line(''Path for '' || item_id || '' is '' || 
                                  content_item__get_path(item_id,null)
           );
   -- PERFORM content_test__put_line(''Moving grandpa to pa - this shouldn\\\'t work'');
   -- PERFORM content_folder__move(folder_id, sub_folder_id);
   -- PERFORM content_test__put_line(''Path for '' || item_id || '' is '' || 
   --                               content_item__get_path(item_id,null)
   --        );

   PERFORM content_test__put_line(''Renaming puppy to kitty...'');
   PERFORM content_item__rename(item_id, ''kitty'');
   PERFORM content_test__put_line(''Renaming me to aunty...'');
   PERFORM content_folder__rename(sub_sub_folder_id, ''aunty'',null,null);
   PERFORM content_test__put_line(''Path for '' || item_id || '' is '' || 
                                  content_item__get_path(item_id,null)
           );
   PERFORM content_test__put_line(''Renaming kitty to pa -- this should work'');
   PERFORM content_item__rename(item_id, ''pa'');
   PERFORM content_test__put_line(''Path for '' || item_id || '' is '' || 
                                  content_item__get_path(item_id,null)
           );

   PERFORM content_test__put_line(''-------------------------------------'');
   PERFORM content_test__put_line(''SYMLINKS...'');
   PERFORM content_test__put_line(''...all tests passed'');

   symlink_a_id := content_symlink__new(''link_a'',
                                        null,
                                        sub_sub_folder_id,
                                        sub_folder_id,
                                        null,
                                        now(),
                                        null,
                                        null
                   );

   insert into tst_ids values (symlink_a_id,''symlink_a_id'');

   return null;

end;' language 'plpgsql';

create function test_content_check3() returns integer as '
declare
  folder_id		cr_folders.folder_id%TYPE;
  folder_b_id		cr_folders.folder_id%TYPE;
  sub_folder_id		cr_folders.folder_id%TYPE;
  sub_sub_folder_id	cr_folders.folder_id%TYPE;
  item_id		cr_items.item_id%TYPE;
  simple_item_id	cr_items.item_id%TYPE;
  live_revision_id	cr_revisions.revision_id%TYPE;
  late_revision_id	cr_revisions.revision_id%TYPE;
  item_template_id	cr_templates.template_id%TYPE;
  type_template_id	cr_templates.template_id%TYPE;
  def_type_template_id	cr_templates.template_id%TYPE;
  dum_template_id	cr_templates.template_id%TYPE;

  symlink_a_id		cr_symlinks.symlink_id%TYPE;
  symlink_b_id		cr_symlinks.symlink_id%TYPE;

  found_folder_id	cr_folders.folder_id%TYPE;
begin
   select id into folder_id from tst_ids where name = ''folder_id'';
   select id into folder_b_id from tst_ids where name = ''folder_b_id'';
   select id into sub_folder_id from tst_ids where name = ''sub_folder_id'';
   select id into sub_sub_folder_id from tst_ids where name = ''sub_sub_folder_id'';
   select id into item_id from tst_ids where name = ''item_id'';
   select id into simple_item_id from tst_ids where name = ''simple_item_id'';
   select id into live_revision_id from tst_ids where name = ''live_revision_id'';
   select id into late_revision_id from tst_ids where name = ''late_revision_id'';
   select id into item_template_id from tst_ids where name = ''item_template_id'';
   select id into type_template_id from tst_ids where name = ''type_template_id'';
   select id into def_type_template_id from tst_ids where name = ''def_type_template_id'';
   select id into dum_template_id from tst_ids where name = ''dum_template_id'';
   select id into found_folder_id from tst_ids where name = ''found_folder_id'';

   select id into symlink_a_id from tst_ids where name = ''symlink_a_id'';


   PERFORM content_test__put_line(''Create a link in pa to aunty: Symlink is '' || symlink_a_id);

   PERFORM content_test__put_line(''Is '' || symlink_a_id || '' a symlink?: ''
                                  || cast_char(content_symlink__is_symlink(symlink_a_id))
           );

   PERFORM content_test__put_line(''Is '' || folder_id || '' a symlink?: '' ||
                                  cast_char(content_symlink__is_symlink(folder_id))
           );

   PERFORM content_test__put_line(''Path for symlink '' || symlink_a_id ||
                                  '' is '' || 
                                  content_item__get_path(symlink_a_id,null)
           );

   PERFORM content_test__put_line(''Resolving symlink '' || symlink_a_id ||
                                  '' is '' || 
                                  content_symlink__resolve(symlink_a_id)
           );

   PERFORM content_test__put_line(''Resolved path for symlink '' || 
                                  symlink_a_id || '' is '' || 
             content_item__get_path(content_symlink__resolve(symlink_a_id),null)
           );

   PERFORM content_test__put_line(''Path to item '' || item_id || 
                                  '' from symlink '' ||
                                  symlink_a_id || '' is '' ||
                                  content_item__get_path(item_id, symlink_a_id)
           );
   PERFORM content_test__put_line(''Path to item '' || item_id || 
                                  '' from aunty '' ||
                                  sub_sub_folder_id || '' is '' ||
                          content_item__get_path(item_id, sub_sub_folder_id)
           );
   PERFORM content_test__put_line(''Path to pa '' || sub_folder_id || 
                                  '' from symlink '' ||
                                  symlink_a_id || '' is '' ||
                                  content_item__get_path(sub_folder_id, 
                                                         symlink_a_id
                                  )
           );

   PERFORM content_test__put_line(''Found item '' || item_id || '' at '' ||
                          content_item__get_id(''/grandpa/aunty/pa'',null,''f'')
           );
   PERFORM content_test__put_line(''Found item '' || item_id || '' at '' ||
                           content_item__get_id(''/grandpa/pa/link_a/pa'',null,''f'')
           );
   PERFORM content_test__put_line(''Found item '' || item_id || 
                                  '' starting at aunty '' ||
                                  sub_sub_folder_id || '' at '' ||
                                  content_item__get_id(''pa'',
                                                        sub_sub_folder_id,
                                                        ''f''
                                  )
           );
   PERFORM content_test__put_line(''Found item '' || item_id || 
                               '' starting at symlink '' ||
                               symlink_a_id || '' at '' ||
                               content_item__get_id(''pa'',symlink_a_id,''f'')
           );
   PERFORM content_test__put_line(''Found item '' || item_id || 
                                  '' starting at pa '' ||
                                  sub_folder_id || '' at '' ||
                                  content_item__get_id(''link_a/bunny'',
                                                       sub_folder_id,
                                                       ''f''
                                  )
           );

   PERFORM content_test__put_line(''--------------------------------'');


   PERFORM content_test__put_line(''Moving item '' || item_id || 
                                  '' to grandma '' || 
                                  folder_b_id
           );
   PERFORM content_item__move(item_id,folder_b_id);

   return null;

end;' language 'plpgsql';

create function test_content_check4() returns integer as '
declare
  folder_id		cr_folders.folder_id%TYPE;
  folder_b_id		cr_folders.folder_id%TYPE;
  sub_folder_id		cr_folders.folder_id%TYPE;
  sub_sub_folder_id	cr_folders.folder_id%TYPE;
  item_id		cr_items.item_id%TYPE;
  simple_item_id	cr_items.item_id%TYPE;
  live_revision_id	cr_revisions.revision_id%TYPE;
  late_revision_id	cr_revisions.revision_id%TYPE;
  item_template_id	cr_templates.template_id%TYPE;
  type_template_id	cr_templates.template_id%TYPE;
  def_type_template_id	cr_templates.template_id%TYPE;
  dum_template_id	cr_templates.template_id%TYPE;

  symlink_a_id		cr_symlinks.symlink_id%TYPE;
  symlink_b_id		cr_symlinks.symlink_id%TYPE;

  found_folder_id	cr_folders.folder_id%TYPE;
begin
   select id into folder_id from tst_ids where name = ''folder_id'';
   select id into folder_b_id from tst_ids where name = ''folder_b_id'';
   select id into sub_folder_id from tst_ids where name = ''sub_folder_id'';
   select id into sub_sub_folder_id from tst_ids where name = ''sub_sub_folder_id'';
   select id into item_id from tst_ids where name = ''item_id'';
   select id into simple_item_id from tst_ids where name = ''simple_item_id'';
   select id into live_revision_id from tst_ids where name = ''live_revision_id'';
   select id into late_revision_id from tst_ids where name = ''late_revision_id'';
   select id into item_template_id from tst_ids where name = ''item_template_id'';
   select id into type_template_id from tst_ids where name = ''type_template_id'';
   select id into def_type_template_id from tst_ids where name = ''def_type_template_id'';
   select id into dum_template_id from tst_ids where name = ''dum_template_id'';
   select id into found_folder_id from tst_ids where name = ''found_folder_id'';

   PERFORM content_test__put_line(''Path for item '' || item_id || '' is '' ||
                                  content_item__get_path(item_id,null)
           );     

   PERFORM content_test__put_line(''Moving folder '' || folder_b_id || 
                                  '' to aunty '' || sub_sub_folder_id
           );
   PERFORM content_item__move(folder_b_id,sub_sub_folder_id);
   return null;

end;' language 'plpgsql';

create function test_content_check5() returns integer as '
declare
  folder_id		cr_folders.folder_id%TYPE;
  folder_b_id		cr_folders.folder_id%TYPE;
  sub_folder_id		cr_folders.folder_id%TYPE;
  sub_sub_folder_id	cr_folders.folder_id%TYPE;
  item_id		cr_items.item_id%TYPE;
  simple_item_id	cr_items.item_id%TYPE;
  live_revision_id	cr_revisions.revision_id%TYPE;
  late_revision_id	cr_revisions.revision_id%TYPE;
  item_template_id	cr_templates.template_id%TYPE;
  type_template_id	cr_templates.template_id%TYPE;
  def_type_template_id	cr_templates.template_id%TYPE;
  dum_template_id	cr_templates.template_id%TYPE;

  symlink_a_id		cr_symlinks.symlink_id%TYPE;
  symlink_b_id		cr_symlinks.symlink_id%TYPE;

  found_folder_id	cr_folders.folder_id%TYPE;
begin
   select id into folder_id from tst_ids where name = ''folder_id'';
   select id into folder_b_id from tst_ids where name = ''folder_b_id'';
   select id into sub_folder_id from tst_ids where name = ''sub_folder_id'';
   select id into sub_sub_folder_id from tst_ids where name = ''sub_sub_folder_id'';
   select id into item_id from tst_ids where name = ''item_id'';
   select id into simple_item_id from tst_ids where name = ''simple_item_id'';
   select id into live_revision_id from tst_ids where name = ''live_revision_id'';
   select id into late_revision_id from tst_ids where name = ''late_revision_id'';
   select id into item_template_id from tst_ids where name = ''item_template_id'';
   select id into type_template_id from tst_ids where name = ''type_template_id'';
   select id into def_type_template_id from tst_ids where name = ''def_type_template_id'';
   select id into dum_template_id from tst_ids where name = ''dum_template_id'';
   select id into found_folder_id from tst_ids where name = ''found_folder_id'';
   PERFORM content_test__put_line(''Path for item '' || item_id || '' is '' ||
                                  content_item__get_path(item_id,null)
           );     

   PERFORM content_test__put_line(''--------------------------------'');

-- symlinks/revisions should be deleted automatically
   PERFORM content_item__delete(simple_item_id);
   PERFORM content_template__delete(item_template_id);
   PERFORM content_item__delete(item_id);
   PERFORM content_template__delete(type_template_id);
   PERFORM content_template__delete(def_type_template_id);
   PERFORM content_template__delete(dum_template_id);
   PERFORM content_folder__delete(sub_sub_folder_id);
   PERFORM content_folder__delete(sub_folder_id);
   PERFORM content_folder__delete(folder_id);
   PERFORM content_folder__delete(folder_b_id);

   return null;

end;' language 'plpgsql';


select test_content_create();
select test_content_check1();
select test_content_check2();
select dump_cr_items();
select test_content_check3();
select test_content_check4();
select test_content_check5();

drop function test_content_create();
drop function test_content_check1();
drop function test_content_check2();
drop function test_content_check3();
drop function test_content_check4();
drop function test_content_check5();
drop function content_test__put_line(text);
drop function cast_char(boolean);
drop function dump_cr_items();

drop table tst_ids;

