postgresql7.2
select content_item__new (
:item_name, -- name
integer :parent_id, -- parent_id for some reason PG gets confused on this param, explicit typecasting was needed
null, -- item_id
null, -- locale
now(), -- creation_date
:creation_user_id, -- creation_user
:parent_id, -- context_id
:creation_ip, -- creation_ip
'content_revision', -- item_subtype
:content_type, -- content_type
null, -- title
null, -- description
null, -- mime_type
null, -- nls_language
null, -- data
:storage_type -- storage_type
)
select content_item__move(:item_id, :parent_id)
select i.item_id, i.name, i.live_revision, i.latest_revision, i.publish_status,
i.content_type, i.storage_type,
tree_level(i.tree_sortkey) as level,
tree_level(p.tree_sortkey) as parent_level
$addtnl_select
from cr_items i, (select tree_sortkey from cr_items where item_id = :parent_id) p
$addtnl_from
where
tree_ancestor_p(p.tree_sortkey,i.tree_sortkey)
$addtnl_where
order by i.tree_sortkey
select a.item_id, o.name, o.live_revision, o.latest_revision, o.publish_status,
o.content_type, o.storage_type,
tree_level(o.tree_sortkey) as level,
tree_level(parent_sortkey) as parent_level
$addtnl_select
from cr_items o,
(select tree_ancestor_keys(i.tree_sortkey) as tree_sortkey_trail, p.tree_sortkey as parent_sortkey, i.item_id, i.tree_sortkey
$addtnl_inner_select
from cr_items i,
(select tree_sortkey from cr_items where item_id = :parent_id) p
$addtnl_from
where
tree_ancestor_p(p.tree_sortkey, i.tree_sortkey)
$addtnl_where) a
where
o.tree_sortkey = tree_sortkey_trail
and tree_ancestor_p(parent_sortkey, o.tree_sortkey)
order by a.tree_sortkey
select i.item_id, i.name, i.live_revision, i.latest_revision, i.publish_status,
i.content_type, i.storage_type, ir.rel_id, ir.order_n
$addtnl_select
from cr_items i, cr_item_rels ir
$addtnl_from
where ir.item_id = :item_id
and ir.related_object_id = i.item_id
and ir.relation_tag = :relation_tag
$addtnl_where
order by order_n
select content_item__delete(:item_id)
select content_revision__delete(revision_id)
from cr_revisions where item_id = :item_id
select content_symlink__new(
:name, -- name
:label, -- label
:target_id, -- target_id
:parent_id, -- parent_id
null, -- symlink_id
now(), -- creation_date
:creation_user_id, -- creation_user_id
:creation_ip -- creation_ip
)
select content_item__relate(
:item_id, -- item_id
:related_object_id, -- related_object_id
:relation_type, -- relation_tag
null, -- order_n
'cr_item_rel' -- relation_type
)
select content_item__unrelate(:rel_id)
select name, parent_id, storage_type, content_type, storage_type, publish_status,
revision_id, title, content, description, publish_date, mime_type
from cr_items i, cr_revisions r where
i.item_id = r.item_id
and i.item_id = :item_id
and content_item__get_best_revision(:item_id) = r.revision_id
select i.item_id, i.name, i.publish_status, i.content_type, i.storage_type $addtnl_select
from cr_items i
where i.parent_id = :parent_id
order by i.tree_sortkey
select content_item__get_id(:url, :root_id, :resolve_index);
select tree_sortkey as current_sortkey, parent_id from cr_items where item_id = :item_id
select i.tree_sortkey as next_sortkey
from cr_items i, (select tree_sortkey from cr_items where item_id = :item_id) c
where i.parent_id = :parent_id
and i.tree_sortkey > c.tree_sortkey
order by i.tree_sortkey
limit 1
select i.tree_sortkey as prev_sortkey
from cr_items i, (select tree_sortkey from cr_items where item_id = :item_id) c
where i.parent_id = :parent_id
and i.tree_sortkey < c.tree_sortkey
order by i.tree_sortkey desc
limit 1