Index: openacs-4/contrib/packages/bcms/tcl/bcms-item-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/bcms/tcl/bcms-item-procs-postgresql.xql,v
diff -u -r1.1 -r1.2
--- openacs-4/contrib/packages/bcms/tcl/bcms-item-procs-postgresql.xql	14 May 2003 15:34:59 -0000	1.1
+++ openacs-4/contrib/packages/bcms/tcl/bcms-item-procs-postgresql.xql	4 Aug 2003 11:19:46 -0000	1.2
@@ -3,58 +3,81 @@
 <queryset>
    <rdbms><type>postgresql</type><version>7.2</version></rdbms>
 
-	<fullquery name="bcms::item::create_item.create_item">
-		<querytext>
-			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
-			)
+        <fullquery name="bcms::item::create_item.create_item">
+                <querytext>
+                        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
+                        )
 
-		</querytext>
-	</fullquery>
+                </querytext>
+        </fullquery>
 
-	<fullquery name="bcms::item::set_item.update_parent">
-		<querytext>
+        <fullquery name="bcms::item::set_item.update_parent">
+                <querytext>
             select content_item__move(:item_id, :parent_id)
-		</querytext>
-	</fullquery>
+                </querytext>
+        </fullquery>
 
-	<fullquery name="bcms::item::list_items.get_items">
-		<querytext>
-			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
-		</querytext>
-	</fullquery>
+        <fullquery name="bcms::item::list_items.get_items">
+                <querytext>
+                        select  i.item_id, 
+                                i.name, 
+                                i.live_revision, 
+                                i.latest_revision, 
+                                i.publish_status, 
+                                i.content_type, 
+                                i.storage_type,
+                                i.tree_sortkey
+                                $addtnl_select
+                        from cr_items i
+                                $addtnl_from
+                        where
+                                i.parent_id = :parent_id
+                                $addtnl_where
 
-	<fullquery name="bcms::item::tree_items.get_items">
-		<querytext>
+                        union
+
+                        select  i.item_id, 
+                                i.name, 
+                                i.live_revision, 
+                                i.latest_revision, 
+                                i.publish_status, 
+                                i.content_type, 
+                                i.storage_type,
+                                i.tree_sortkey,
+                                f.label as title,
+                                f.description
+                                $addtnl_select_folder
+                        from cr_items i, cr_folders f
+                        where
+                                i.parent_id = :parent_id
+                                and i.item_id = f.folder_id
+
+                        order by tree_sortkey
+                </querytext>
+        </fullquery>
+
+        <fullquery name="bcms::item::tree_items.get_items">
+                <querytext>
             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
+                                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 
@@ -69,8 +92,8 @@
                 o.tree_sortkey = tree_sortkey_trail
                 and tree_ancestor_p(parent_sortkey, o.tree_sortkey) 
             order by a.tree_sortkey
-		</querytext>
-	</fullquery>
+                </querytext>
+        </fullquery>
 
     <fullquery name="bcms::item::list_related_items.get_related_items">
         <querytext>
@@ -82,16 +105,16 @@
             where ir.item_id = :item_id
                 and ir.related_object_id = i.item_id
                 and ir.relation_tag = :relation_tag
-				$addtnl_where
+                                $addtnl_where
             order by order_n
         </querytext>
     </fullquery>
 
-	<fullquery name="bcms::item::delete_item.delete_item">
-		<querytext>
-			select content_item__delete(:item_id)
-		</querytext>
-	</fullquery>
+    <fullquery name="bcms::item::delete_item.delete_item">
+                <querytext>
+                        select content_item__delete(:item_id)
+                </querytext>
+    </fullquery>
         
     <fullquery name="bcms::item::delete_item.delete_all_revisions">
                <querytext>
@@ -133,16 +156,16 @@
         </querytext>
     </fullquery>
 
-	<fullquery name="bcms::item::get_item_best.get_1item">
-		<querytext>
-			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
-		</querytext>
-	</fullquery>
+        <fullquery name="bcms::item::get_item_best.get_1item">
+                <querytext>
+                        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
+                </querytext>
+        </fullquery>
 
     <fullquery name="bcms::item::list_pages.get_pages">
         <querytext>
@@ -153,11 +176,11 @@
         </querytext>
     </fullquery>
 
-	<fullquery name="bcms::item::get_item_by_url.get_item_id">
-		<querytext>
-			select content_item__get_id(:url, :root_id, :resolve_index);
-		</querytext>
-	</fullquery>
+        <fullquery name="bcms::item::get_item_by_url.get_item_id">
+                <querytext>
+                        select content_item__get_id(:url, :root_id, :resolve_index);
+                </querytext>
+        </fullquery>
 
     <fullquery name="bcms::item::get_sortkeys.get_currentkey">
         <querytext>
@@ -187,6 +210,20 @@
         </querytext>
     </fullquery>
 
+    <fullquery name="bcms::item::get_item_path.get_item_path">
+         <querytext>
+            select i.item_id, :prepend_path || content_item__get_path(i.item_id, r.item_id) as path, 
+                case when i.content_type = 'content_folder' then content_folder__get_label(i.item_id) else bcms__get_title(i.item_id, 'latest') end as title
+            from cr_items i,
+                (select tree_sortkey, item_id from cr_items where item_id = :root_id) r,
+                (select tree_ancestor_keys(tree_sortkey) as tree_sortkey from cr_items where item_id = :item_id) p
+            where   
+                i.tree_sortkey = p.tree_sortkey
+                and i.tree_sortkey > r.tree_sortkey
+            order by i.tree_sortkey
+        </querytext>
+    </fullquery>
+
 </queryset>