Index: openacs-4/packages/new-portal/tcl/portal-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/new-portal/tcl/portal-procs.xql,v diff -u -N -r1.39 -r1.40 --- openacs-4/packages/new-portal/tcl/portal-procs.xql 12 Jul 2002 21:26:48 -0000 1.39 +++ openacs-4/packages/new-portal/tcl/portal-procs.xql 13 Jul 2002 03:05:24 -0000 1.40 @@ -1,733 +1,787 @@ + - - - select impl_name - from acs_sc_impls, acs_sc_bindings, acs_sc_contracts - where acs_sc_impls.impl_id = acs_sc_bindings.impl_id - and acs_sc_contracts.contract_id= acs_sc_bindings.contract_id - and acs_sc_contracts.contract_name='portal_datasource' - - + + + select impl_name + from acs_sc_impls, + acs_sc_bindings, + acs_sc_contracts + where acs_sc_impls.impl_id = acs_sc_bindings.impl_id + and acs_sc_contracts.contract_id = acs_sc_bindings.contract_id + and acs_sc_contracts.contract_name = 'portal_datasource' + + - - - select datasource_id - from portal_datasource_avail_map - where portal_id = :portal_id - - + + + select datasource_id + from portal_datasource_avail_map + where portal_id = :portal_id + + - - - delete from acs_permissions where object_id= :portal_id - - + + + delete + from acs_permissions + where object_id = :portal_id + + - - - select name from portals where portal_id = :portal_id - - + + + select name + from portals + where portal_id = :portal_id + + - - - select p.name, p.portal_id, p.theme_id as theme_id, - pl.layout_id as layout_id, pl.filename as layout_filename, - pp.page_id as page_id - from portals p, portal_pages pp, portal_layouts pl - where pp.sort_key = :sort_key - and pp.portal_id = :portal_id - and pp.portal_id = p.portal_id - and pp.layout_id = pl.layout_id - - + + + select portals.name, + portals.portal_id, + portals.theme_id, + portal_layouts.layout_id, + portal_layouts.filename as layout_filename, + portal_pages.page_id + from portals, + portal_pages, + portal_layouts + where portal_pages.sort_key = :sort_key + and portal_pages.portal_id = :portal_id + and portal_pages.portal_id = portals.portal_id + and portal_pages.layout_id = portal_layouts.layout_id + + - - - select portal_element_map.element_id, - portal_element_map.region, - portal_element_map.sort_key - from portal_element_map, - portal_pages - where portal_pages.portal_id = :portal_id - and portal_element_map.page_id = :page_id - and portal_element_map.page_id = portal_pages.page_id - and portal_element_map.state != 'hidden' - order by portal_element_map.region, - portal_element_map.sort_key - - + + + select portal_element_map.element_id, + portal_element_map.region, + portal_element_map.sort_key + from portal_element_map, + portal_pages + where portal_pages.portal_id = :portal_id + and portal_element_map.page_id = :page_id + and portal_element_map.page_id = portal_pages.page_id + and portal_element_map.state != 'hidden' + order by portal_element_map.region, + portal_element_map.sort_key + + - - - update portals - set name = :new_name - where portal_id = :portal_id - - + + + update portals + set name = :new_name + where portal_id = :portal_id + + - - - select p.name, p.portal_id, - pl.filename as template, - pp.pretty_name as page_name, - pp.layout_id as layout_id - from portals p, portal_layouts pl, portal_pages pp - where pp.portal_id = :portal_id - and pp.page_id = :page_id - and pp.portal_id = p.portal_id - and pl.layout_id = pp.layout_id - - + + + select portals.name, + portals.portal_id, + portal_layouts.filename as template, + portal_pages.pretty_name as page_name, + portal_pages.layout_id as layout_id + from portals, + portal_layouts, + portal_pages + where portal_pages.portal_id = :portal_id + and portal_pages.page_id = :page_id + and portal_pages.portal_id = portals.portal_id + and portal_pages.layout_id = portal_layouts.layout_id + + - - - update portal_element_map - set state = 'full' - where element_id = :element_id - - + + + update portal_element_map + set state = 'full' + where element_id = :element_id + + - - - update portal_element_map - set state = 'hidden' - where element_id = :element_id - - + + + update portal_element_map + set state = 'hidden' + where element_id = :element_id + + - - - update portals set theme_id = :theme_id where portal_id = :portal_id - - + + + update portals + set theme_id = :theme_id + where portal_id = :portal_id + + - - - select max(page_id) from portal_pages where portal_id = :portal_id - - + + + select max(page_id) + from portal_pages + where portal_id = :portal_id + + - - - select min(page_id) from portal_pages where portal_id = :portal_id - - + + + select min(page_id) + from portal_pages + where portal_id = :portal_id + + - - - select element_id from portal_element_map where page_id = :max_page_id - - + + + select element_id + from portal_element_map + where page_id = :max_page_id + + - - - select pretty_name, layout_id, sort_key - from portal_pages - where page_id = :source_page_id - - + + + select pretty_name, + layout_id, + sort_key + from portal_pages + where page_id = :source_page_id + + - - - select page_id - from portal_pages - where portal_id = :portal_id - and sort_key = :sort_key - - + + + select page_id + from portal_pages + where portal_id = :portal_id + and sort_key = :sort_key + + - - - update portal_pages - set pretty_name = :pretty_name, - layout_id = :layout_id - where page_id = :target_page_id - - + + + update portal_pages + set pretty_name = :pretty_name, + layout_id = :layout_id + where page_id = :target_page_id + + - - - select region, sort_key, state, - pd.datasource_id as datasource_id, - pd.name as name, - pem.pretty_name as pretty_name - from portal_element_map pem, - portal_datasources pd - where pem.page_id = :source_page_id - and pem.datasource_id = pd.datasource_id - - + + + select region, + sort_key, + state, + pd.datasource_id as datasource_id, + pd.name as name, + pem.pretty_name as pretty_name + from portal_element_map pem, + portal_datasources pd + where pem.page_id = :source_page_id + and pem.datasource_id = pd.datasource_id + + - - - select element_id - from portal_element_map pem, portal_pages pp - where pp.portal_id = :portal_id - and pem.page_id = pp.page_id - and pem.datasource_id = :datasource_id - and pem.pretty_name = :pretty_name - - + + + select element_id + from portal_element_map pem, + portal_pages pp + where pp.portal_id = :portal_id + and pem.page_id = pp.page_id + and pem.datasource_id = :datasource_id + and pem.pretty_name = :pretty_name + + - - - update portal_element_map - set region = :region, - sort_key = :sort_key, - state = :state, - page_id = :target_page_id - where element_id = :target_element_id - - + + + update portal_element_map + set region = :region, + sort_key = :sort_key, + state = :state, + page_id = :target_page_id + where element_id = :target_element_id + + - - - update portals - set theme_id = :theme_id - where portal_id = :portal_id - - + + + update portals + set theme_id = :theme_id + where portal_id = :portal_id + + - - - update portals - set theme_id = :theme_id - where portal_id = :portal_id - - + + + update portals + set theme_id = :theme_id + where portal_id = :portal_id + + - - - select state - from portal_element_map pem - where element_id = :element_id - - + + + select state + from portal_element_map + where element_id = :element_id + + - - - update portal_element_map - set state = 'pinned' - where element_id = :element_id - - + + + update portal_element_map + set state = 'pinned' + where element_id = :element_id + + - - - update portal_element_map - set state = 'full' - where element_id = :element_id - - + + + update portal_element_map + set state = 'full' + where element_id = :element_id + + - - - select template_id - from portals - where portal_id = :portal_id - and template_id is not null - - + + + select template_id + from portals + where portal_id = :portal_id + and template_id is not null + + - - - select page_id - from portal_pages - where portal_id = :portal_id - and sort_key = :sort_key - - + + + select page_id + from portal_pages + where portal_id = :portal_id + and sort_key = :sort_key + + - - - select count(*) - from portal_pages - where portal_id = :portal_id - - + + + select count(*) + from portal_pages + where portal_id = :portal_id + + - - - select pretty_name - from portal_pages - where page_id = :page_id - - + + + select pretty_name + from portal_pages + where page_id = :page_id + + - - - update portal_pages set pretty_name = :pretty_name - where page_id = :page_id - - + + + update portal_pages + set pretty_name = :pretty_name + where page_id = :page_id + + - - - select page_id - from portal_pages - where portal_id = :portal_id - order by sort_key - - + + + select page_id + from portal_pages + where portal_id = :portal_id + order by sort_key + + - - - select pretty_name, sort_key as page_num from portal_pages where - portal_id = :portal_id - order by sort_key - - + + + select pretty_name, + sort_key as page_num + from portal_pages + where portal_id = :portal_id + order by sort_key + + - - - select count(*) as count - from portal_element_map pem, portal_pages pp - where pp.portal_id = :portal_id - and pem.region = :region - and pp.page_id = pem.page_id - - + + + select count(*) as count + from portal_element_map pem, + portal_pages pp + where pp.portal_id = :portal_id + and pem.region = :region + and pp.page_id = pem.page_id + + - - - delete from portal_element_map - where element_id= :element_id - - + + + delete + from portal_element_map + where element_id = :element_id + + - - - select pp.page_id as template_page_id, - pp.sort_key as template_page_sort_key, - pem.element_id as template_element_id, - pem.sort_key as template_element_sk, - pem.name as template_element_name, - pem.region as template_element_region - from portals p, portal_element_map pem, portal_pages pp - where p.portal_id = :portal_id - and p.template_id = pp.portal_id - and pp.page_id = pem.page_id - and pem.datasource_id = :ds_id - - + + + select pp.page_id as template_page_id, + pp.sort_key as template_page_sort_key, + pem.element_id as template_element_id, + pem.sort_key as template_element_sk, + pem.name as template_element_name, + pem.region as template_element_region + from portals p, + portal_element_map pem, + portal_pages pp + where p.portal_id = :portal_id + and p.template_id = pp.portal_id + and pp.page_id = pem.page_id + and pem.datasource_id = :ds_id + + - - - select page_id as target_page_id - from portal_pages pp - where pp.portal_id = :portal_id - and pp.sort_key = :template_page_sort_key - - + + + select page_id as target_page_id + from portal_pages pp + where pp.portal_id = :portal_id + and pp.sort_key = :template_page_sort_key + + - - - insert into portal_element_parameters - (parameter_id, element_id, config_required_p, configured_p, key, value) - select nextval('t_acs_object_id_seq'), :new_element_id, config_required_p, configured_p, key, value - from portal_element_parameters - where element_id = :template_element_id - - + + + insert into portal_element_map + (element_id, name, pretty_name, page_id, datasource_id, region, state, sort_key) + select + :new_element_id, :ds_name, :pretty_name, :target_page_id, :ds_id, region, state, sort_key + from portal_element_map pem + where pem.element_id = :template_element_id + + - - - insert into portal_element_parameters - (parameter_id, element_id, config_required_p, configured_p, key, value) - select nextval('t_acs_object_id_seq'), :new_element_id, config_required_p, configured_p, key, value - from portal_datasource_def_params where datasource_id= :ds_id - - + + + select sort_key as my_sort_key, + page_id as my_page_id + from portal_element_map + where element_id = :element_id + + - - - insert into portal_element_map - (element_id, name, pretty_name, page_id, datasource_id, region, state, sort_key) - select - :new_element_id, :ds_name, :pretty_name, :target_page_id, :ds_id, region, state, sort_key - from portal_element_map pem - where pem.element_id = :template_element_id - - + + + select sort_key as other_sort_key, + element_id as other_element_id + from (select pem.sort_key, + element_id + from portal_element_map pem, + portal_pages pp + where pp.portal_id = :portal_id + and pem.page_id = :my_page_id + and pp.page_id = pem.page_id + and region = :region + and pem.sort_key < :my_sort_key + and state != 'pinned' + order by pem.sort_key desc) + where rownum = 1 + + - - - select sort_key as my_sort_key, page_id as my_page_id - from portal_element_map - where element_id = :element_id - - + + + select sort_key as other_sort_key, + element_id as other_element_id + from (select pem.sort_key, + element_id + from portal_element_map pem, portal_pages pp + where pp.portal_id = :portal_id + and pem.page_id = :my_page_id + and pem.page_id = pp.page_id + and region = :region + and pem.sort_key > :my_sort_key + and state != 'pinned' + order by pem.sort_key) + where rownum = 1 + + - - - select sort_key as other_sort_key, element_id as other_element_id - from (select pem.sort_key, element_id - from portal_element_map pem, portal_pages pp - where pp.portal_id = :portal_id - and pem.page_id = :my_page_id - and pp.page_id = pem.page_id - and region = :region - and pem.sort_key < :my_sort_key - and state != 'pinned' - order by pem.sort_key desc) where rownum = 1 - - + + + update portal_element_map + set sort_key = :dummy_sort_key + where element_id = :element_id + + - - - select sort_key as other_sort_key, element_id as other_element_id - from (select pem.sort_key, element_id - from portal_element_map pem, portal_pages pp - where pp.portal_id = :portal_id - and pem.page_id = :my_page_id - and pem.page_id = pp.page_id - and region = :region - and pem.sort_key > :my_sort_key - and state != 'pinned' - order by pem.sort_key) where rownum = 1 - - + + + update portal_element_map + set sort_key = :my_sort_key + where element_id = :other_element_id + + - - - update portal_element_map set sort_key = :dummy_sort_key - where element_id = :element_id - - + + + update portal_element_map + set sort_key = :other_sort_key + where element_id = :element_id + + - - - update portal_element_map set sort_key = :my_sort_key - where element_id = :other_element_id - - + + + select region + from portal_element_map + where element_id = :element_id + + - - - update portal_element_map set sort_key = :other_sort_key - where element_id = :element_id - - + + + select value + from portal_element_parameters + where element_id = :element_id + and key = 'hideable_p' + + - - - select region from portal_element_map where element_id = :element_id - - + + + select element_id, + pem.pretty_name + from portal_element_map pem, + portal_pages pp + where pp.portal_id = :portal_id + and pp.page_id = pem.page_id + and pem.state = 'hidden' + order by name + + - - - select value from portal_element_parameters where element_id = :element_id and key = 'hideable_p' - - + + + select 1 + from dual + where exists (select 1 + from portal_element_map pem + where pem.page_id = :page_id + and pem.state != 'hidden') + + - - - select element_id, pem.pretty_name - from portal_element_map pem, portal_pages pp - where pp.portal_id = :portal_id - and pp.page_id = pem.page_id - and pem.state = 'hidden' - order by name - - + + + update portal_element_parameters + set value = :value + where element_id = :element_id + and key = :key + + - - - select 1 - from dual - where exists (select 1 - from portal_element_map pem - where pem.page_id = :page_id - and pem.state != 'hidden') - - + + + select page_id as my_page_id + from portal_element_map + where element_id = :element_id + + - - - update portal_element_parameters set value = :value - where element_id = :element_id and - key = :key - - + + + select value + from portal_element_parameters + where element_id = :element_id + and key = :key + + - - - select page_id as my_page_id - from portal_element_map - where element_id = :element_id - - + + + delete + from portal_element_parameters + where element_id = :element_id + and key = :key + and value= :value + + - - - select value - from portal_element_parameters - where element_id= :element_id - and key= :key - - + + + delete + from portal_element_parameters + where element_id = :element_id + and key = :key + + - - - insert into portal_element_parameters - (parameter_id, element_id, configured_p, key, value) - select nextval('t_acs_object_id_seq'), :element_id, 't', :key, :value - from dual - where not exists (select parameter_id from portal_element_parameters where - element_id= :element_id and key= :key and value= :value) - - + + + select value + from portal_element_parameters + where element_id = :element_id + and key = :key + + - - - - - + + + select pem.element_id, + pem.datasource_id, + pem.state, + pet.filename as filename, + pet.resource_dir as resource_dir, + pem.pretty_name as pretty_name, + pd.name as ds_name + from portal_element_map pem, + portal_element_themes pet, + portal_datasources pd + where pet.theme_id = :theme_id + and pem.element_id = :element_id + and pem.datasource_id = pd.datasource_id + + - - - delete from portal_element_parameters where - element_id= :element_id and - key= :key and - value= :value - - + + + select key, + value + from portal_element_parameters + where element_id = :element_id + + - - - delete from portal_element_parameters where - element_id= :element_id and - key= :key - - + + + select portal_id + from portal_pages + where page_id = (select page_id + from portal_element_map + where element_id= :element_id) + + - - - select value - from portal_element_parameters - where element_id = :element_id and - key = :key - - + + + select element_id, + datasource_id, + state + from portal_element_map + where element_id = :element_id + + - - - select pem.element_id, - pem.datasource_id, - pem.state, - pet.filename as filename, - pet.resource_dir as resource_dir, - pem.pretty_name as pretty_name, - pd.name as ds_name - from portal_element_map pem, portal_element_themes pet, portal_datasources pd - where pet.theme_id = :theme_id - and pem.element_id = :element_id - and pem.datasource_id = pd.datasource_id - - + + + select key, + value + from portal_element_parameters + where element_id = :element_id + + - - - select key, value - from portal_element_parameters - where element_id = :element_id - - + + + select portal_id, + datasource_id + from portal_element_map pem, + portal_pages pp + where element_id = :element_id + and pem.page_id = pp.page_id + + - - -select portal_id from portal_pages where page_id = (select page_id from portal_element_map where element_id= :element_id) - - + + + update portal_element_map + set state = 'hidden' + where element_id = :element_id + + - - - select pem.element_id, - pem.datasource_id, - pem.state - from portal_element_map pem - where pem.element_id = :element_id - - + + + select name + from portal_datasources + where datasource_id = :ds_id + + - - - select key, value - from portal_element_parameters - where element_id = :element_id - - + + + select datasource_id + from portal_datasources + where name = :ds_name + + - - - select portal_id, datasource_id - from portal_element_map pem, portal_pages pp - where element_id = :element_id - and pem.page_id = pp.page_id - - + + + insert into portal_datasource_avail_map + (portal_datasource_id, portal_id, datasource_id) + values + (:new_p_ds_id, :portal_id, :ds_id) + + - - - update portal_element_map - set state = 'hidden' - where element_id = :element_id - - + + + select count(*) + from portal_datasource_avail_map + where datasource_id = :datasource_id + and portal_id = :portal_id + + - - - select name from portal_datasources where datasource_id = :ds_id - - + + + delete + from portal_datasource_avail_map + where portal_id = :portal_id + and datasource_id = :ds_id + + - - - select datasource_id from portal_datasources where name = :ds_name - - + + + select 1 + from portal_datasource_avail_map + where portal_id = :portal_id and + datasource_id = :ds_id + + - - - insert into portal_datasource_avail_map - (portal_datasource_id, portal_id, datasource_id) - values - (:new_p_ds_id, :portal_id, :ds_id) - - - - - - select count(*) - from portal_datasource_avail_map - where datasource_id = :datasource_id - and portal_id = :portal_id - - - - - - delete from portal_datasource_avail_map - where portal_id = :portal_id - and datasource_id = :ds_id - - - - - - select 1 - from portal_datasource_avail_map - where portal_id = :portal_id and - datasource_id = :ds_id - - - - + select portal_element_map.element_id from portal_element_map, portal_pages - where portal_pages.portal_id = :portal_id + where portal_pages.portal_id = :portal_id and portal_element_map.datasource_id = :ds_id and portal_element_map.page_id = portal_pages.page_id - + - - - select element_id - from portal_element_map pem, portal_pages pp - where pp.portal_id= :portal_id - and pem.page_id = pp.page_id - and pem.pretty_name = :pretty_name - - + + + select element_id + from portal_element_map pem, + portal_pages pp + where pp.portal_id = :portal_id + and pem.page_id = pp.page_id + and pem.pretty_name = :pretty_name + + - - - select count(*) - from portal_supported_regions - where layout_id = :layout_id - - + + + select count(*) + from portal_supported_regions + where layout_id = :layout_id + + - - - select region - from portal_supported_regions - where layout_id = :layout_id - - + + + select region + from portal_supported_regions + where layout_id = :layout_id + + - - - select layout_id, name as layout_name, description as layout_description - from portal_layouts - - + + + select layout_id, + name as layout_name, + description as layout_description + from portal_layouts + + - - - update portal_pages set layout_id = :layout_id where page_id = :page_id - - + + + update portal_pages + set layout_id = :layout_id + where page_id = :page_id + + - - - select layout_id from portal_pages where portal_id = :portal_id and sort_key = :page_num - - + + + select layout_id + from portal_pages + where portal_id = :portal_id + and sort_key = :page_num + + - - - select layout_id from portal_pages where page_id = :page_id - - + + + select layout_id + from portal_pages + where page_id = :page_id + + - - - select layout_id from portal_layouts where name = :layout_name - - + + + select layout_id + from portal_layouts + where name = :layout_name + + - - - select 1 from portals where portal_id = :portal_id - - + + + select 1 + from portals + where portal_id = :portal_id + + - - - select theme_id from portal_element_themes where name = :theme_name - - + + + select theme_id + from portal_element_themes + where name = :theme_name + + - - - select theme_id from portals where portal_id = :portal_id - - + + + select theme_id + from portals + where portal_id = :portal_id + + - - - select theme_id, name, description - from portal_element_themes - order by name - - + + + select theme_id, + name, + description + from portal_element_themes + order by name + + - - - select page_id - from portal_pages - where portal_id = :portal_id - and pretty_name= :page_name - - + + + select page_id + from portal_pages + where portal_id = :portal_id + and pretty_name = :page_name + + -