-- Queries dealing with the tree widget


-- Get child folders of an item

select
  :mount_point as mount_point,
  r.name,
  r.item_id, 
  '' as children,
  r.is_symlink symlink, 
  0 update_time,
  nvl(
    (select 't' from dual where exists (
       select 
         1 
       from 
         cr_folders f_child, cr_resolved_items r_child
       where 
         r_child.parent_id = r.resolved_id
       and 
         f_child.folder_id = r_child.resolved_id)
      ), 'f') expandable
  from
    cr_folders f, cr_resolved_items r
  where
    r.parent_id = :id
  and
    r.resolved_id = f.folder_id
  order by
    name

-- Get sorted paths of all items under some id

select 
  item_id, 
  content_item.get_path(item_id, :sorted_paths_root_id) as item_path,
  content_type as item_type
from 
  cr_items
where
  item_id in ($sql_id_list)
order by item_path

-- Get all types in the database in a pretty tree

select
  lpad(' ', level, '-') || pretty_name as label,
  object_type as value
from
  acs_object_types t
onnect by
  supertype = prior object_type
start with
  object_type = 'content_revision'

-- Get all types under some subtype

select
  :module_name as mount_point,
  t.pretty_name, t.object_type, '' as children,
  NVL(
   (select 't' from dual 
      where exists (select 1 from acs_object_types
        where supertype = t.object_type)),
   'f'
  ) as expandable,
  'f' as symlink, 
  0 as update_time
from 
  acs_object_types t
where 
  supertype = :id
order by 
  t.pretty_name

-- Get all categories under some parent category

select 
  :module_name as mount_point,
  content_keyword.get_heading(keyword_id) as name, 
  keyword_id, 
  '' as children,
  NVL( (select 't' from dual 
    where exists (
       select 1 from cr_keywords k2
         where k2.parent_id = k.keyword_id
         and content_keyword.is_leaf(k2.keyword_id) = 'f')),
   'f'
  ) as expandable,
  'f' as symlink,
  0 as update_time           
from 
  cr_keywords k
where 
  k.parent_id = :parent_id
and
  content_keyword.is_leaf(keyword_id) = 'f'
order by 
  name

-- Get sorted paths to the keywords

select 
  keyword_id as item_id,
  content_keyword.get_path(keyword_id) as item_path,
  'content_keyword' as item_type
from
  cr_keywords
where 
  keyword_id in ($sql_id_list)

-- Get all top-level groups

select
  :module_name as mount_point,
  g.group_name as name, 
  g.group_id,
  '' as children,
  NVL(
    (select 't' from dual 
        where exists (
          select 1 from group_component_map m2
            where m2.group_id = g.group_id)),
    'f' 
  ) as expandable,
  'f' as symlink,
  0 as update_time
from 
  groups g 
where 
  not exists (select 1 from group_component_map m
               where m.component_id = g.group_id) 
order by 
  name

-- Get users in a group

select
  :module_name as mount_point,
  g.group_name as name, 
  g.group_id,
  '' as children,
  NVL(
    (select 't' from dual 
        where exists (
          select 1 from group_component_map m2
            where m2.group_id = g.group_id)),
    'f' 
  ) as expandable,
  'f' as symlink,
  0 as update_time
from 
  groups g , group_component_map m
where 
  m.group_id = :id and m.component_id = g.group_id
order by 
  name

-- Get sorted paths to users / groups

select 
  o.object_id as item_id,
  o.object_type || ': ' || acs_object.name(o.object_id) as item_path,
  o.object_type as item_type
from
  acs_objects o, parties p
where
  o.object_id = p.party_id
and
  o.object_id in ($sql_id_list)
order by
  item_path