Index: openacs-4/packages/acs-subsite/acs-subsite.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/acs-subsite.info,v diff -u -N -r1.9 -r1.10 --- openacs-4/packages/acs-subsite/acs-subsite.info 9 May 2001 22:32:25 -0000 1.9 +++ openacs-4/packages/acs-subsite/acs-subsite.info 15 May 2001 16:59:00 -0000 1.10 @@ -4,8 +4,8 @@ ACS Subsite ACS Subsite Services - f t + f @@ -57,120 +57,191 @@ - - - - + + - - + + - - + + - - + + - - + + - + + - - + - - + + - - + + + + + + - - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + @@ -181,80 +252,156 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + @@ -295,8 +442,11 @@ + + + @@ -336,6 +486,7 @@ + @@ -400,6 +551,8 @@ + + @@ -415,6 +568,7 @@ + @@ -425,6 +579,8 @@ + + Index: openacs-4/packages/acs-subsite/tcl/application-group-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/application-group-procs-postgresql.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/tcl/application-group-procs-postgresql.xql 6 May 2001 21:40:21 -0000 1.1 +++ openacs-4/packages/acs-subsite/tcl/application-group-procs-postgresql.xql 15 May 2001 16:59:00 -0000 1.2 @@ -3,7 +3,7 @@ postgresql7.1 - + select case when exists ( @@ -23,7 +23,7 @@ - + select case when exists ( @@ -43,7 +43,7 @@ - + select case when exists ( @@ -58,7 +58,7 @@ - + select case when exists ( @@ -73,74 +73,69 @@ - + --- FIX ME PLSQL --- begin + select application_group__group_id_from_package_id ( - :package_id, -- package_id - :no_complain_p -- no_complain_p - ); --- end; + :package_id, + :no_complain_p + ) - + --- FIX ME ROWNUM - select ag__group_id as parent_group_id + + select ag.group_id as parent_group_id from application_groups ag, apm_packages, - (select object_id, rownum as tree_rownum + (select object_id, 1 as tree_rownum from site_nodes - start with node_id = :node_id - connect by node_id = prior parent_id) nodes - where nodes__object_id = apm_packages.package_id + where tree_sortkey like (select tree_sortkey from site_nodes where node_id = :node_id) || '%') nodes + where nodes.object_id = apm_packages.package_id and apm_packages.package_id = ag.package_id - limit 1 - + limit 1 + - - + + --- FIX ME PLSQL --- begin - select application_group_new ( - :group_id, -- group_id - :group_type, -- object_type - :group_name, -- group_name - :package_id, -- package_id - :context_id, -- context_id - :creation_user, -- creation_user - :creation_ip, -- creation_ip - :email, -- email - :url -- url - ); --- end; + select application_group__new ( + :group_id, + :group_type, + now(), + :creation_user, + :creation_ip, + :email, + :url, + :group_name, + :package_id, + :context_id + ) + - + --- FIX ME PLSQL --- begin - select composition_rel__new ( - 'composition_rel', -- rel_type - :parent_group_id, -- object_id_one - :group_id, -- object_id_two - :creation_user, -- creation_user - :creation_ip -- creation_ip - ); --- end; + select composition_rel__new ( + null, + 'composition_rel', + :parent_group_id, + :group_id, + :creation_user, + :creation_ip + ) + - + Index: openacs-4/packages/acs-subsite/tcl/application-group-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/Attic/application-group-procs.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/tcl/application-group-procs.xql 6 May 2001 21:40:21 -0000 1.1 +++ openacs-4/packages/acs-subsite/tcl/application-group-procs.xql 15 May 2001 16:59:00 -0000 1.2 @@ -4,7 +4,7 @@ - select substr(instance_name from 1 for 90) + select substr(instance_name, 1, 90) from apm_packages where package_id = :package_id Index: openacs-4/packages/acs-subsite/tcl/attribute-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/attribute-procs-oracle.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/tcl/attribute-procs-oracle.xql 6 May 2001 21:40:21 -0000 1.1 +++ openacs-4/packages/acs-subsite/tcl/attribute-procs-oracle.xql 15 May 2001 16:59:00 -0000 1.2 @@ -35,4 +35,39 @@ + + + + select a.object_type, a.attribute_name, + case when a.storage = 'type_specific' then t.table_name else a.table_name end as table_name, + nvl(a.column_name, a.attribute_name) as column_name + from acs_attributes a, acs_object_types t + where a.attribute_id = :attribute_id + and t.object_type = a.object_type + + + + + + + + + select nvl(a.column_name, a.attribute_name) as name, + a.pretty_name, a.attribute_id, a.datatype, + v.enum_value, v.pretty_name as value_pretty_name + from acs_object_type_attributes a, + acs_enum_values v, + (select t.object_type, level as type_level + from acs_object_types t + start with t.object_type = :start_with + connect by prior t.object_type = t.supertype) t + where a.object_type = :object_type + and a.attribute_id = v.attribute_id(+) + and t.object_type = a.ancestor_type $storage_clause + order by type_level, a.sort_order + + + + + Index: openacs-4/packages/acs-subsite/tcl/attribute-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/attribute-procs-postgresql.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/tcl/attribute-procs-postgresql.xql 6 May 2001 21:40:21 -0000 1.1 +++ openacs-4/packages/acs-subsite/tcl/attribute-procs-postgresql.xql 15 May 2001 16:59:00 -0000 1.2 @@ -18,7 +18,7 @@ - + select case when exists (select 1 @@ -35,4 +35,36 @@ + + + + select coalesce(a.column_name, a.attribute_name) as name, + a.pretty_name, a.attribute_id, a.datatype, + v.enum_value, v.pretty_name as value_pretty_name + from acs_object_type_attributes a left outer join + acs_enum_values v using (attribute_id), + (select t.object_type, + tree_level(tree_sortkey) - + (select tree_level(tree_sortkey) from acs_object_types where object_type = :start_with) as type_level + from acs_object_types t + where tree_sortkey like (select tree_sortkey from acs_object_types where object_type = :start_with) || '%') t + where a.object_type = :object_type + and t.object_type = a.ancestor_type $storage_clause + order by type_level, a.sort_order + + + + + + + + + select * + from ($package_object_view) dummy + where object_id = :object_id + + + + + Index: openacs-4/packages/acs-subsite/tcl/attribute-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/attribute-procs.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/tcl/attribute-procs.xql 6 May 2001 21:40:21 -0000 1.1 +++ openacs-4/packages/acs-subsite/tcl/attribute-procs.xql 15 May 2001 16:59:00 -0000 1.2 @@ -26,9 +26,9 @@ --- FIX ME DECODE (USE SQL92 CASE) + select a.object_type, a.attribute_name, - decode(a.storage,'type_specific',t.table_name,a.table_name) as table_name, + case when a.storage = 'type_specific' then t.table_name else a.table_name end as table_name, coalesce(a.column_name, a.attribute_name) as column_name from acs_attributes a, acs_object_types t where a.attribute_id = :attribute_id @@ -73,27 +73,6 @@ - - - FIX ME OUTER JOIN - select coalesce(a.column_name, a.attribute_name) as name, - a.pretty_name, a.attribute_id, a.datatype, - v.enum_value, v.pretty_name as value_pretty_name - from acs_object_type_attributes a, - acs_enum_values v, - (select t.object_type, level as type_level - from acs_object_types t - start with t.object_type = :start_with - connect by prior t.object_type = t.supertype) t - where a.object_type = :object_type - and a.attribute_id = v.attribute_id(+) - and t.object_type = a.ancestor_type $storage_clause - order by type_level, a.sort_order - - - - - Index: openacs-4/packages/acs-subsite/tcl/group-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/group-procs-postgresql.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/tcl/group-procs-postgresql.xql 6 May 2001 21:40:21 -0000 1.1 +++ openacs-4/packages/acs-subsite/tcl/group-procs-postgresql.xql 15 May 2001 16:59:00 -0000 1.2 @@ -5,21 +5,21 @@ --- FIX ME PLSQL --- begin - select ${group_type}__new ( - 'Editor' -- group_name - ); --- end; + FIX ME PLSQL + begin + :1 := ${group_type}.new (group_name => 'Editor'); + end; + - - + + --- FIX ME PLSQL --- BEGIN + FIX ME PLSQL + + BEGIN -- the acs_group package takes care of segments referred -- to by rel_constraints.rel_segment. We delete the ones -- references by rel_constraints.required_rel_segment here. @@ -29,16 +29,16 @@ where segs.segment_id = cons.required_rel_segment and segs.group_id = :group_id) loop - rel_segment.delete(row.constraint_id); + rel_segment__delete(row.constraint_id); end loop; -- delete the actual group - ${package_name}.delete(:group_id); --- END; - + ${package_name}.delete(:group_id); + END; + - + Index: openacs-4/packages/acs-subsite/tcl/package-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/package-procs-oracle.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/tcl/package-procs-oracle.xql 6 May 2001 21:40:21 -0000 1.1 +++ openacs-4/packages/acs-subsite/tcl/package-procs-oracle.xql 15 May 2001 16:59:00 -0000 1.2 @@ -17,6 +17,38 @@ + + + + select upper(nvl(attr.table_name,t.table_name)) as attr_table_name, + upper(nvl(attr.column_name, attr.attribute_name)) as attr_column_name, + attr.ancestor_type, attr.min_n_values, attr.default_value + from acs_object_type_attributes attr, + (select t.object_type, t.table_name, level as type_level + from acs_object_types t + start with t.object_type = :object_type + connect by prior t.supertype = t.object_type) t + where attr.ancestor_type = t.object_type + and attr.object_type = :object_type + order by t.type_level + + + + + + + + + select t.object_type + from acs_object_types t + where t.dynamic_p = 't' + start with t.object_type = :object_type + connect by prior t.object_type = t.supertype + + + + + @@ -32,6 +64,30 @@ + + + + select t.object_type as ancestor_type + from acs_object_types t + start with t.object_type = :object_type + connect by prior t.supertype = t.object_type + + + + + + + + + select t.object_type as sub_type + from acs_object_types t + start with t.object_type = :object_type + connect by prior t.object_type = t.supertype + + + + + @@ -54,6 +110,51 @@ + + + + select a.attribute_id, + nvl(a.table_name, t.table_name) as table_name, + nvl(a.column_name, a.attribute_name) as attribute_name, + a.pretty_name, + a.datatype, + case when a.min_n_values = 0 then 'f' else 't' end as required_p, + a.default_value, + t.table_name as object_type_table_name, + t.id_column as object_type_id_column + from acs_object_type_attributes a, + (select t.object_type, t.table_name, t.id_column, level as type_level + from acs_object_types t + start with t.object_type=:start_with + connect by prior t.object_type = t.supertype) t + where a.object_type = :object_type + and t.object_type = a.ancestor_type $storage_clause + order by type_level + + + + + + + + select cols.table_name, cols.column_name + from user_tab_columns cols, + (select upper(t.table_name) as table_name + from acs_object_types t + start with t.object_type = :object_type + connect by prior t.supertype = t.object_type) t + where cols.column_name in + (select args.argument_name + from user_arguments args + where args.position > 0 + and args.object_name = upper(:object_name) + and args.package_name = upper(:package_name)) + and cols.table_name = t.table_name + + + + + Index: openacs-4/packages/acs-subsite/tcl/package-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/package-procs-postgresql.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/tcl/package-procs-postgresql.xql 6 May 2001 21:40:21 -0000 1.1 +++ openacs-4/packages/acs-subsite/tcl/package-procs-postgresql.xql 15 May 2001 16:59:00 -0000 1.2 @@ -17,6 +17,40 @@ + + + FIX ME CONNECT BY + + select upper(coalesce(attr.table_name,t.table_name)) as attr_table_name, + upper(coalesce(attr.column_name, attr.attribute_name)) as attr_column_name, + attr.ancestor_type, attr.min_n_values, attr.default_value + from acs_object_type_attributes attr, + (select t.object_type, t.table_name, level as type_level + from acs_object_types t + start with t.object_type = :object_type + connect by prior t.supertype = t.object_type) t + where attr.ancestor_type = t.object_type + and attr.object_type = :object_type + order by t.type_level + + + + + + + + FIX ME CONNECT BY + + select t.object_type + from acs_object_types t + where t.dynamic_p = 't' + start with t.object_type = :object_type + connect by prior t.object_type = t.supertype + + + + + @@ -32,11 +66,37 @@ + + + FIX ME CONNECT BY + + select t.object_type as ancestor_type + from acs_object_types t + start with t.object_type = :object_type + connect by prior t.supertype = t.object_type + + + + + + + + FIX ME CONNECT BY + + select t.object_type as sub_type + from acs_object_types t + start with t.object_type = :object_type + connect by prior t.object_type = t.supertype + + + + + - select acs_object.name(:user_id) as author, - current_time as creation_date + select acs_object__name(:user_id) as author, + current_timestamp as creation_date @@ -46,27 +106,74 @@ - select acs_object.name(:user_id) as author, - current_time as creation_date + select acs_object__name(:user_id) as author, + current_timestamp as creation_date + + + + select a.attribute_id, + coalesce(a.table_name, t.table_name) as table_name, + coalesce(a.column_name, a.attribute_name) as attribute_name, + a.pretty_name, + a.datatype, + case when a.min_n_values = 0 then 'f' else 't' end as required_p, + a.default_value, + t.table_name as object_type_table_name, + t.id_column as object_type_id_column + from acs_object_type_attributes a, + (select t.object_type, t.table_name, t.id_column, tree_level(t.tree_sortkey) as type_level + from acs_object_types t + where tree_sortkey like + (select tree_sortkey || '%' + from acs_object_types + where object_type = :start_with)) t + where a.object_type = :object_type + and t.object_type = a.ancestor_type $storage_clause + order by type_level + + + + + + + + select cols.table_name, cols.column_name + from user_tab_columns cols, + (select upper(t.table_name) as table_name + from acs_object_types t + where t.tree_sortkey like (select tree_sortkey || '%' from acs_object_types where object_type = :object_type)) t + where cols.column_name in + (select args.argument_name + from user_arguments args + where args.position > 0 + and args.object_name = upper(:object_name) + and args.package_name = upper(:package_name)) + and cols.table_name = t.table_name + + + + + --- FIX ME PLSQL --- BEGIN - select ${package_name}__new([plsql_utility::generate_attribute_parameter_call \ + FIX ME PLSQL + + BEGIN + :1 := ${package_name}.new([plsql_utility::generate_attribute_parameter_call \ -prepend ":" \ -indent [expr [string length $package_name] + 29] \ $pieces] ); --- END; - + END; + - + Index: openacs-4/packages/acs-subsite/tcl/package-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/package-procs.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/tcl/package-procs.xql 6 May 2001 21:40:21 -0000 1.1 +++ openacs-4/packages/acs-subsite/tcl/package-procs.xql 15 May 2001 16:59:00 -0000 1.2 @@ -12,38 +12,6 @@ - - - - select upper(coalesce(attr.table_name,t.table_name)) as attr_table_name, - upper(coalesce(attr.column_name, attr.attribute_name)) as attr_column_name, - attr.ancestor_type, attr.min_n_values, attr.default_value - from acs_object_type_attributes attr, - (select t.object_type, t.table_name, level as type_level - from acs_object_types t - start with t.object_type = :object_type - connect by prior t.supertype = t.object_type) t - where attr.ancestor_type = t.object_type - and attr.object_type = :object_type - order by t.type_level - - - - - - - - - select t.object_type - from acs_object_types t - where t.dynamic_p = 't' - start with t.object_type = :object_type - connect by prior t.object_type = t.supertype - - - - - @@ -100,30 +68,6 @@ - - - - select t.object_type as ancestor_type - from acs_object_types t - start with t.object_type = :object_type - connect by prior t.supertype = t.object_type - - - - - - - - - select t.object_type as sub_type - from acs_object_types t - start with t.object_type = :object_type - connect by prior t.object_type = t.supertype - - - - - @@ -135,30 +79,6 @@ - - --- FIX ME DECODE (USE SQL92 CASE) - select a.attribute_id, - coalesce(a.table_name, t.table_name) as table_name, - coalesce(a.column_name, a.attribute_name) as attribute_name, - a.pretty_name, - a.datatype, - decode(a.min_n_values,0,'f','t') as required_p, - a.default_value, - t.table_name as object_type_table_name, - t.id_column as object_type_id_column - from acs_object_type_attributes a, - (select t.object_type, t.table_name, t.id_column, level as type_level - from acs_object_types t - start with t.object_type=:start_with - connect by prior t.object_type = t.supertype) t - where a.object_type = :object_type - and t.object_type = a.ancestor_type $storage_clause - order by type_level - - - - @@ -170,33 +90,23 @@ - + - select cols.table_name, cols.column_name - from user_tab_columns cols, - (select upper(t.table_name) as table_name - from acs_object_types t - start with t.object_type = :object_type - connect by prior t.supertype = t.object_type) t - where cols.column_name in - (select args.argument_name - from user_arguments args - where args.position > 0 - and args.object_name = upper(:object_name) - and args.package_name = upper(:package_name)) - and cols.table_name = t.table_name - + select t.package_name + from acs_object_types t + where t.object_type = :object_type + - + - - select t.package_name - from acs_object_types t - where t.object_type = :object_type + + select t.table_name, t.id_column + from acs_object_types t + where t.object_type = :object_type Index: openacs-4/packages/acs-subsite/tcl/party-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/Attic/party-procs-oracle.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/tcl/party-procs-oracle.xql 6 May 2001 21:40:21 -0000 1.1 +++ openacs-4/packages/acs-subsite/tcl/party-procs-oracle.xql 15 May 2001 16:59:00 -0000 1.2 @@ -14,4 +14,39 @@ + + + + select + types.pretty_name, + types.object_type, + types.tree_level, + types.indent, + case when valid_types.object_type = null then 0 else 1 end as valid_p + from + (select + t.pretty_name, t.object_type, level as tree_level, + replace(lpad(' ', (level - 1) * 4), + ' ', ' ') as indent, + rownum as tree_rownum + from + acs_object_types t + connect by + prior t.object_type = t.supertype + start with + $start_with_clause ) types, + (select + object_type + from + rel_types_valid_obj_two_types + where + rel_type = :rel_type ) valid_types + where + types.object_type = valid_types.object_type(+) + order by tree_rownum + + + + + Index: openacs-4/packages/acs-subsite/tcl/party-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/Attic/party-procs-postgresql.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/tcl/party-procs-postgresql.xql 6 May 2001 21:40:21 -0000 1.1 +++ openacs-4/packages/acs-subsite/tcl/party-procs-postgresql.xql 15 May 2001 16:59:00 -0000 1.2 @@ -5,15 +5,52 @@ --- FIX ME PLSQL --- begin - select ${party_type}__new ( - 'joe@foo.com' -- email - ); --- end; + FIX ME PLSQL + + begin + :1 := ${party_type}.new (email => 'joe@foo.com'); + end; + + + FIX ME OUTER JOIN +FIX ME CONNECT BY +FIX ME ROWNUM + + select + types.pretty_name, + types.object_type, + types.tree_level, + types.indent, + case when valid_types.object_type = null then 0 else 1 end as valid_p + from + (select + t.pretty_name, t.object_type, level as tree_level, + replace(lpad(' ', (level - 1) * 4), + ' ', ' ') as indent, + rownum as tree_rownum + from + acs_object_types t + connect by + prior t.object_type = t.supertype + start with + $start_with_clause ) types, + (select + object_type + from + rel_types_valid_obj_two_types + where + rel_type = :rel_type ) valid_types + where + types.object_type = valid_types.object_type(+) + order by tree_rownum + + + + + Index: openacs-4/packages/acs-subsite/tcl/party-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/Attic/party-procs.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/tcl/party-procs.xql 6 May 2001 21:40:21 -0000 1.1 +++ openacs-4/packages/acs-subsite/tcl/party-procs.xql 15 May 2001 16:59:00 -0000 1.2 @@ -12,39 +12,4 @@ - - --- FIX ME DECODE (USE SQL92 CASE) - select - types.pretty_name, - types.object_type, - types.tree_level, - types.indent, - decode(valid_types.object_type, null, 0, 1) as valid_p - from - (select - t.pretty_name, t.object_type, level as tree_level, - replace(lpad(' ', (level - 1) * 4), - ' ', ' ') as indent, - rownum as tree_rownum - from - acs_object_types t - connect by - prior t.object_type = t.supertype - start with - $start_with_clause ) types, - (select - object_type - from - rel_types_valid_obj_two_types - where - rel_type = :rel_type ) valid_types - where - types.object_type = valid_types.object_type(+) - order by tree_rownum - - - - - Index: openacs-4/packages/acs-subsite/tcl/rel-segments-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/rel-segments-procs-postgresql.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/tcl/rel-segments-procs-postgresql.xql 6 May 2001 21:40:21 -0000 1.1 +++ openacs-4/packages/acs-subsite/tcl/rel-segments-procs-postgresql.xql 15 May 2001 16:59:00 -0000 1.2 @@ -5,39 +5,41 @@ --- FIX ME PLSQL --- declare --- begin - select rel_segment__new( - :segment_name, -- segment_name - :group_id, -- group_id - :context_id, -- context_id - :rel_type, -- rel_type - :creation_user, -- creation_user - :creation_ip -- creation_ip - ); --- end; + select rel_segment__new( + null, + 'rel_segment', + now(), + :creation_user, + :creation_ip, + null, + null, + :segment_name, + :group_id, + :rel_type, + :context_id +) + - - + + --- FIX ME PLSQL - select rel_segment__delete(:constraint_id); + select rel_segment__delete(:constraint_id) + - - + + --- FIX ME PLSQL - select rel_segment__delete(:segment_id); + select rel_segment__delete(:segment_id) + - + Index: openacs-4/packages/acs-subsite/tcl/rel-types-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/rel-types-procs-postgresql.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/tcl/rel-types-procs-postgresql.xql 6 May 2001 21:40:21 -0000 1.1 +++ openacs-4/packages/acs-subsite/tcl/rel-types-procs-postgresql.xql 15 May 2001 16:59:00 -0000 1.2 @@ -17,4 +17,20 @@ + + + + select case when exists (select 1 + from acs_object_types t1, acs_object_types t2 + where t2.object_type not in (select g.rel_type + from group_type_rels g + where g.group_type = :group_type) + and t1.object_type in ('membership_rel','composition_rel') + and t2.tree_sortkey like t1.tree_sortkey || '%') + then 1 else 0 end + + + + + Index: openacs-4/packages/acs-subsite/tcl/relation-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/relation-procs-oracle.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/tcl/relation-procs-oracle.xql 6 May 2001 21:40:21 -0000 1.1 +++ openacs-4/packages/acs-subsite/tcl/relation-procs-oracle.xql 15 May 2001 16:59:00 -0000 1.2 @@ -12,6 +12,41 @@ + + + + select s.segment_id, r.object_id_two as party_id, t.package_name + from rel_segments s, acs_rels r, acs_object_types t + where r.object_id_one = s.group_id(+) + and r.rel_type = s.rel_type(+) + and r.rel_type = t.object_type + and r.rel_id = :rel_id + + + + + + + + begin ${package_name}.delete(:rel_id); end; + + + + + + + + select s.segment_id, r.object_id_two as party_id, t.package_name + from rel_segments s, acs_rels r, acs_object_types t + where r.object_id_one = s.group_id(+) + and r.rel_type = s.rel_type(+) + and r.rel_type = t.object_type + and r.rel_id = :rel_id + + + + + @@ -38,4 +73,65 @@ + + + + select + pretty_name, object_type, level, indent, + case when valid_types.rel_type = null then 0 else 1 end as valid_p + from + (select + t.pretty_name, t.object_type, level, + replace(lpad(' ', (level - 1) * 4), + ' ', ' ') as indent, + rownum as tree_rownum + from + acs_object_types t + connect by + prior t.object_type = t.supertype + start with + t.object_type = :start_with ) types, + (select + rel_type + from + rc_valid_rel_types + where + group_id = :group_id ) valid_types + where + types.object_type = valid_types.rel_type(+) + order by tree_rownum + + + + + + + + + select distinct s.segment_id, s.group_id, s.rel_type, + g.group_name, g.join_policy, t.pretty_name as rel_type_pretty_name, + nvl(dl.dependency_level, 0) + from rc_all_constraints c, + (select rel_segment, required_rel_segment + from rc_segment_required_seg_map + where rel_side = 'two' + UNION ALL + select segment_id, segment_id + from rel_segments) map, + rel_segments s, + rc_segment_dependency_levels dl, + groups g, acs_object_types t + where c.group_id = :group_id + and c.rel_type = :rel_type + and c.required_rel_segment = map.rel_segment + and map.required_rel_segment = s.segment_id + and s.segment_id = dl.segment_id(+) + and g.group_id = s.group_id + and t.object_type = s.rel_type + order by nvl(dl.dependency_level, 0) + + + + + Index: openacs-4/packages/acs-subsite/tcl/relation-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/relation-procs-postgresql.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/tcl/relation-procs-postgresql.xql 6 May 2001 21:40:21 -0000 1.1 +++ openacs-4/packages/acs-subsite/tcl/relation-procs-postgresql.xql 15 May 2001 16:59:00 -0000 1.2 @@ -6,36 +6,78 @@ - select rel_constraint__violation(:rel_id) - + select rel_constraint__violation(:rel_id) + - - + + + begin ${package_name}.delete(:rel_id); end; + + + + + + select case when exists (select 1 from rc_violations_by_removing_rel r where r.rel_id = :rel_id) then 1 else 0 end - - + + - - + + - + select case when exists - (select 1 from rc_valid_rel_types r - where r.group_id = :group_id + (select 1 from rc_valid_rel_types r + where r.group_id = :group_id and r.rel_type = :rel_type) then 1 else 0 end + + + + + + + + FIX ME OUTER JOIN +FIX ME CONNECT BY +FIX ME ROWNUM + select + pretty_name, object_type, level, indent, + case when valid_types.rel_type = null then 0 else 1 end as valid_p + from + (select + t.pretty_name, t.object_type, level, + replace(lpad(' ', (level - 1) * 4), + ' ', ' ') as indent, + rownum as tree_rownum + from + acs_object_types t + connect by + prior t.object_type = t.supertype + start with + t.object_type = :start_with ) types, + (select + rel_type + from + rc_valid_rel_types + where + group_id = :group_id ) valid_types + where + types.object_type = valid_types.rel_type(+) + order by tree_rownum + - + Index: openacs-4/packages/acs-subsite/tcl/relation-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/relation-procs.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/tcl/relation-procs.xql 6 May 2001 21:40:21 -0000 1.1 +++ openacs-4/packages/acs-subsite/tcl/relation-procs.xql 15 May 2001 16:59:00 -0000 1.2 @@ -3,99 +3,44 @@ - FIX ME OUTER JOIN + select s.segment_id, r.object_id_two as party_id, t.package_name - from rel_segments s, acs_rels r, acs_object_types t - where r.object_id_one = s.group_id(+) - and r.rel_type = s.rel_type(+) - and r.rel_type = t.object_type + from acs_rels r + left outer join rel_segments s + on (r.object_id_one = s.group_id and r.rel_type = s.rel_type), + acs_object_types t + where r.rel_type = t.object_type and r.rel_id = :rel_id - + --- FIX ME - select ${package_name}__delete(:rel_id); - - - - - - FIX ME OUTER JOIN - select s.segment_id, r.object_id_two as party_id, t.package_name - from rel_segments s, acs_rels r, acs_object_types t - where r.object_id_one = s.group_id(+) - and r.rel_type = s.rel_type(+) - and r.rel_type = t.object_type - and r.rel_id = :rel_id - - - - - - - --- FIX ME DECODE (USE SQL92 CASE) - select - pretty_name, object_type, level, indent, - decode(valid_types.rel_type, null, 0, 1) as valid_p - from - (select - t.pretty_name, t.object_type, level, - replace(lpad(' ', (level - 1) * 4), - ' ', ' ') as indent, - rownum as tree_rownum - from - acs_object_types t - connect by - prior t.object_type = t.supertype - start with - t.object_type = :start_with ) types, - (select - rel_type - from - rc_valid_rel_types - where - group_id = :group_id ) valid_types - where - types.object_type = valid_types.rel_type(+) - order by tree_rownum - - - - - - - - FIX ME OUTER JOIN select distinct s.segment_id, s.group_id, s.rel_type, g.group_name, g.join_policy, t.pretty_name as rel_type_pretty_name, coalesce(dl.dependency_level, 0) - from rc_all_constraints c, + from rc_all_constraints c, (select rel_segment, required_rel_segment from rc_segment_required_seg_map where rel_side = 'two' UNION ALL select segment_id, segment_id from rel_segments) map, - rel_segments s, - rc_segment_dependency_levels dl, + rel_segments s left outer join rc_segment_dependency_levels dl using (segment_id), groups g, acs_object_types t where c.group_id = :group_id and c.rel_type = :rel_type and c.required_rel_segment = map.rel_segment and map.required_rel_segment = s.segment_id - and s.segment_id = dl.segment_id(+) and g.group_id = s.group_id and t.object_type = s.rel_type order by coalesce(dl.dependency_level, 0) - + - + Index: openacs-4/packages/acs-subsite/tcl/subsite-callback-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/subsite-callback-procs-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/tcl/subsite-callback-procs-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,21 @@ + + + + oracle8.1.6 + + + + + select distinct callback, callback_type + from subsite_callbacks + where object_type in (select t.object_type + from acs_object_types t + connect by prior t.supertype = t.object_type + start with t.object_type = :object_type) + and event_type = :event_type + + + + + + Index: openacs-4/packages/acs-subsite/tcl/subsite-callback-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/subsite-callback-procs-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/tcl/subsite-callback-procs-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,22 @@ + + + + postgresql7.1 + + + + FIX ME CONNECT BY + + select distinct callback, callback_type + from subsite_callbacks + where object_type in (select t.object_type + from acs_object_types t + connect by prior t.supertype = t.object_type + start with t.object_type = :object_type) + and event_type = :event_type + + + + + + Index: openacs-4/packages/acs-subsite/tcl/subsite-callback-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/subsite-callback-procs.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/tcl/subsite-callback-procs.xql 6 May 2001 21:40:21 -0000 1.1 +++ openacs-4/packages/acs-subsite/tcl/subsite-callback-procs.xql 15 May 2001 16:59:00 -0000 1.2 @@ -12,19 +12,4 @@ - - - - select distinct callback, callback_type - from subsite_callbacks - where object_type in (select t.object_type - from acs_object_types t - connect by prior t.supertype = t.object_type - start with t.object_type = :object_type) - and event_type = :event_type - - - - - Index: openacs-4/packages/acs-subsite/tcl/subsite-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/subsite-procs-oracle.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/tcl/subsite-procs-oracle.xql 6 May 2001 21:40:21 -0000 1.1 +++ openacs-4/packages/acs-subsite/tcl/subsite-procs-oracle.xql 15 May 2001 16:59:00 -0000 1.2 @@ -21,6 +21,18 @@ + + + + select t.pretty_name as package_name, acs_object.name(s.object_id) as object_name + from site_nodes s, apm_package_types t + where s.node_id = :node_id + and t.package_key = :package_key + + + + + @@ -36,4 +48,16 @@ + + + + select object_type + from acs_object_types + start with object_type = :object_type + connect by object_type = prior supertype + + + + + Index: openacs-4/packages/acs-subsite/tcl/subsite-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/subsite-procs-postgresql.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/tcl/subsite-procs-postgresql.xql 6 May 2001 21:40:21 -0000 1.1 +++ openacs-4/packages/acs-subsite/tcl/subsite-procs-postgresql.xql 15 May 2001 16:59:00 -0000 1.2 @@ -5,35 +5,62 @@ --- FIX ME PLSQL --- BEGIN - select rel_constraint__new( - :constraint_name, -- constraint_name - :segment_id, -- rel_segment - 'two', -- rel_side - rel_segment.get(:supersite_group_id, 'membership_rel'), -- required_rel_segment - :user_id, -- creation_user - :creation_ip -- creation_ip - ); --- END; + FIX ME PLSQL + BEGIN + :1 := rel_constraint__new( + constraint_name => :constraint_name, + rel_segment => :segment_id, + rel_side => 'two', + required_rel_segment => rel_segment__get(:supersite_group_id, 'membership_rel'), + creation_user => :user_id, + creation_ip => :creation_ip + ); + END; + - - + + + + select t.pretty_name as package_name, acs_object__name(s.object_id) as object_name + from site_nodes s, apm_package_types t + where s.node_id = :node_id + and t.package_key = :package_key + + + - select case - when exists (select 1 from acs_object_types + + + + + select case + when exists (select 1 from acs_object_types where supertype = :object_type) - then 1 - else 0 + then 1 + else 0 end + + + + + + + + select t2.object_type + from acs_object_types t1, acs_object_types t2 + where t1.object_type = :object_type + and t2.tree_sortkey <= t1.tree_sortkey + and t1.tree_sortkey like (t2.tree_sortkey || '%') + order by t2.tree_sortkey desc + - + Index: openacs-4/packages/acs-subsite/tcl/subsite-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/Attic/subsite-procs.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/tcl/subsite-procs.xql 6 May 2001 21:40:21 -0000 1.1 +++ openacs-4/packages/acs-subsite/tcl/subsite-procs.xql 15 May 2001 16:59:00 -0000 1.2 @@ -40,30 +40,6 @@ - - --- FIX ME should be in oracle and postgresql - select t.pretty_name as package_name, acs_object__name(s.object_id) as object_name - from site_nodes s, apm_package_types t - where s.node_id = :node_id - and t.package_key = :package_key - - - - - - - - - select object_type - from acs_object_types - start with object_type = :object_type - connect by object_type = prior supertype - - - - - Index: openacs-4/packages/acs-subsite/www/admin/attributes/add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/attributes/add.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/attributes/add.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,26 @@ + + + + + + + select t.pretty_name + from acs_object_types t + where t.object_type = :object_type + + + + + + + + + select d.datatype + from acs_datatypes d + order by lower(d.datatype) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/attributes/delete-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/attributes/delete-2.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/attributes/delete-2.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + + select attr.object_type + from acs_attributes attr + where attr.attribute_id = :attribute_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/attributes/delete.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/attributes/delete.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/attributes/delete.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + + select a.object_type, a.pretty_name as attribute_pretty_name + from acs_attributes a + where a.attribute_id = :attribute_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/attributes/edit-one-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/attributes/Attic/edit-one-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/attributes/edit-one-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,21 @@ + + + + oracle8.1.6 + + + + + select a.pretty_name as attribute_pretty_name, a.datatype, a.attribute_id, + nvl(a.column_name,a.attribute_name) as attribute_column, + t.id_column as type_column, t.table_name as type_table, t.object_type, + a.min_n_values + from acs_attributes a, acs_object_types t + where a.attribute_id = :attribute_id + and a.object_type = t.object_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/attributes/edit-one.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/attributes/edit-one.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/attributes/edit-one.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,51 @@ + + + + + + + select a.pretty_name as attribute_pretty_name, a.datatype, a.attribute_id, + coalesce(a.column_name,a.attribute_name) as attribute_column, + t.id_column as type_column, t.table_name as type_table, t.object_type, + a.min_n_values + from acs_attributes a, acs_object_types t + where a.attribute_id = :attribute_id + and a.object_type = t.object_type + + + + + + + + + select my_view.$attribute_column as current_value + from ([package_object_view $object_type]) my_view + where my_view.object_id = :id_column + + + + + + + + + select enum.pretty_name, enum.enum_value + from acs_enum_values enum + where enum.attribute_id = :attribute_id + order by enum.sort_order + + + + + + + + update $type_table + set $attribute_column = :attribute_value + where $type_column = :id_column + + + + + Index: openacs-4/packages/acs-subsite/www/admin/attributes/enum-add-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/attributes/Attic/enum-add-2-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/attributes/enum-add-2-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,33 @@ + + + + oracle8.1.6 + + + + + select nvl(max(v.sort_order),0) + from acs_enum_values v + where v.attribute_id = :attribute_id + + + + + + + + + insert into acs_enum_values v + (attribute_id, sort_order, enum_value, pretty_name) + select :attribute_id, :sort_order, :pretty_name, :pretty_name + from dual + where not exists (select 1 + from acs_enum_values v2 + where v2.pretty_name = :pretty_name + and v2.attribute_id = :attribute_id) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/attributes/enum-add-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/attributes/Attic/enum-add-2-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/attributes/enum-add-2-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,22 @@ + + + + postgresql7.1 + + + + + insert into acs_enum_values v + (attribute_id, sort_order, enum_value, pretty_name) + select :attribute_id, :sort_order, :pretty_name, :pretty_name + + where not exists (select 1 + from acs_enum_values v2 + where v2.pretty_name = :pretty_name + and v2.attribute_id = :attribute_id) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/attributes/enum-add-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/attributes/enum-add-2.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/attributes/enum-add-2.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,38 @@ + + + + + + + select coalesce(max(v.sort_order),0) + from acs_enum_values v + where v.attribute_id = :attribute_id + + + + + + + + + delete from acs_enum_values + where attribute_id = :attribute_id + and sort_order = :sort_order + + + + + + + + + update acs_enum_values v + set v.pretty_name = :pretty_name + where v.attribute_id = :attribute_id + and v.sort_order = :sort_order + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/attributes/enum-add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/attributes/enum-add.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/attributes/enum-add.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,38 @@ + + + + + + + select count(*) + from acs_enum_values v + where v.attribute_id = :attribute_id + + + + + + + + + select v.enum_value + from acs_enum_values v + where v.attribute_id = :attribute_id + order by v.sort_order + + + + + + + + + select a.pretty_name as attribute_pretty_name + from acs_attributes a + where a.attribute_id = :attribute_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/attributes/one.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/attributes/one.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/attributes/one.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,31 @@ + + + + + + + select a.attribute_id, a.object_type, a.table_name, a.attribute_name, + a.pretty_name, a.pretty_plural, a.sort_order, a.datatype, + a.default_value, a.min_n_values, a.max_n_values, a.storage, + a.static_p, a.column_name, t.dynamic_p + from acs_attributes a, acs_object_types t + where a.object_type = t.object_type + and a.attribute_id = :attribute_id + + + + + + + + + select v.enum_value, v.pretty_name + from acs_enum_values v + where v.attribute_id = :attribute_id + order by v.sort_order + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/attributes/value-delete.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/attributes/value-delete.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/attributes/value-delete.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,16 @@ + + + + + + + select v.pretty_name + from acs_enum_values v + where v.attribute_id = :attribute_id + and v.enum_value = :enum_value + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/change-join-policy-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/change-join-policy-2-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/change-join-policy-2-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,19 @@ + + + + oracle8.1.6 + + + + + select t.dynamic_p, + case when gt.group_type = null then 0 else 1 end as group_type_exists_p + from acs_object_types t, group_types gt + where t.object_type = :group_type + and t.object_type = gt.group_type(+) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/change-join-policy-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/Attic/change-join-policy-2-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/change-join-policy-2-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,20 @@ + + + + postgresql7.1 + + + + FIX ME OUTER JOIN + + select t.dynamic_p, + case when gt.group_type = null then 0 else 1 end as group_type_exists_p + from acs_object_types t, group_types gt + where t.object_type = :group_type + and t.object_type = gt.group_type(+) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/change-join-policy-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/change-join-policy-2.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/change-join-policy-2.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,41 @@ + + + + + + FIX ME OUTER JOIN + + select t.dynamic_p, + case when gt.group_type = null then 0 else 1 end as group_type_exists_p + from acs_object_types t, group_types gt + where t.object_type = :group_type + and t.object_type = gt.group_type(+) + + + + + + + + + insert into group_types + (group_type, default_join_policy) + values + (:group_type, :default_join_policy) + + + + + + + + + update group_types + set default_join_policy = :default_join_policy + where group_type = :group_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/change-join-policy-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/Attic/change-join-policy-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/change-join-policy-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,19 @@ + + + + oracle8.1.6 + + + + + select t.pretty_name as group_type_pretty_name, t.dynamic_p, + nvl(gt.default_join_policy, 'open') as default_join_policy + from acs_object_types t, group_types gt + where t.object_type = :group_type + and t.object_type = gt.group_type(+) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/change-join-policy-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/Attic/change-join-policy-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/change-join-policy-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,20 @@ + + + + postgresql7.1 + + + + FIX ME OUTER JOIN + + select t.pretty_name as group_type_pretty_name, t.dynamic_p, + coalesce(gt.default_join_policy, 'open') as default_join_policy + from acs_object_types t, group_types gt + where t.object_type = :group_type + and t.object_type = gt.group_type(+) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/change-join-policy.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/change-join-policy.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/change-join-policy.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,18 @@ + + + + + + FIX ME OUTER JOIN + + select t.pretty_name as group_type_pretty_name, t.dynamic_p, + coalesce(gt.default_join_policy, 'open') as default_join_policy + from acs_object_types t, group_types gt + where t.object_type = :group_type + and t.object_type = gt.group_type(+) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/delete-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/Attic/delete-2-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/delete-2-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,33 @@ + + + + oracle8.1.6 + + + + + select case when exists (select 1 + from user_objects o + where o.object_type='PACKAGE' + and o.object_name = upper(:package_name)) + then 1 else 0 end + from dual + + + + + + + + + select case when exists (select 1 from acs_object_types t where t.object_type = :group_type) + then 1 + else 0 + end + from dual + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/delete-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/Attic/delete-2-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/delete-2-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,33 @@ + + + + postgresql7.1 + + + + + select case when exists (select 1 + from user_objects o + where o.object_type='PACKAGE' + and o.object_name = upper(:package_name)) + then 1 else 0 end + + + + + + + + + + select case when exists (select 1 from acs_object_types t where t.object_type = :group_type) + then 1 + else 0 + end + + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/delete-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/Attic/delete-2.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/delete-2.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,29 @@ + + + + + + + select t.table_name, t.package_name + from acs_object_types t + where t.object_type=:group_type + + + + + + + + + select o.object_id + from acs_objects o, acs_object_party_privilege_map perm + where perm.object_id = o.object_id + and perm.party_id = :user_id + and perm.privilege = 'delete' + and o.object_type = :group_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/delete-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/delete-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/delete-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,33 @@ + + + + oracle8.1.6 + + + + + select case when exists (select 1 + from acs_object_types t + where t.supertype = :group_type) + then 1 else 0 end + from dual + + + + + + + + + select case when exists (select 1 + from acs_rel_types t + where t.object_type_one = :group_type + or t.object_type_two = :group_type) + then 1 else 0 end + from dual + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/delete-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/delete-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/delete-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,33 @@ + + + + postgresql7.1 + + + + + select case when exists (select 1 + from acs_object_types t + where t.supertype = :group_type) + then 1 else 0 end + + + + + + + + + + select case when exists (select 1 + from acs_rel_types t + where t.object_type_one = :group_type + or t.object_type_two = :group_type) + then 1 else 0 end + + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/delete.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/delete.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/delete.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,64 @@ + + + + + + + select t.pretty_name as group_type_pretty_name + from acs_object_types t + where t.object_type = :group_type + + + + + + + + + select t.object_type as group_type, t.pretty_name + from acs_object_types t + where t.supertype = :group_type + + + + + + + + + select rel.rel_type, t.pretty_name + from acs_rel_types rel, acs_object_types t + where (rel.object_type_one = :group_type + or rel.object_type_two = :group_type) + and rel.rel_type = t.object_type + + + + + + + + + select count(o.object_id) + from acs_objects o + where o.object_type = :group_type + + + + + + + + + select count(r.rel_id) + from acs_rels r + where r.rel_type in (select t.rel_type + from acs_rel_types t + where t.object_type_one = :group_type + or t.object_type_two = :group_type) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/groups-display.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/groups-display.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/groups-display.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + + select t.pretty_name as group_type_pretty_name + from acs_object_types t + where t.object_type = :group_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/groups-list.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/groups-list.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/groups-list.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,25 @@ + + + + + + + select DISTINCT g.group_id, g.group_name + from (select group_id, group_name + from groups g, acs_objects o + where g.group_id = o.object_id + and o.object_type = :group_type) g, + (select object_id + from all_object_party_privilege_map + where party_id = :user_id and privilege = 'read') perm, + application_group_element_map m + where perm.object_id = g.group_id + and m.package_id = :package_id + and m.element_id = g.group_id + order by lower(g.group_name) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/index-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/index-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/index-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,35 @@ + + + + oracle8.1.6 + + + + + select t.object_type as group_type, t.pretty_plural, + nvl(num.number_groups,0) as number_groups, t.indent + from (select t.object_type, t.pretty_plural, rownum as inner_rownum, + replace(lpad(' ', (level - 1) * 4), ' ', ' ') as indent + from acs_object_types t + connect by prior t.object_type = t.supertype + start with t.object_type = 'group' + order by lower(t.pretty_plural)) t, + (select o.object_type, count(*) number_groups + from groups g, acs_objects o, + acs_object_party_privilege_map perm, + application_group_element_map app_group + where perm.object_id = g.group_id + and perm.party_id = :user_id + and perm.privilege = 'read' + and o.object_id = g.group_id + and app_group.package_id = :package_id + and app_group.element_id = g.group_id + group by o.object_type) num + where t.object_type = num.object_type(+) + order by t.inner_rownum + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/index-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/index-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/index-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,37 @@ + + + + postgresql7.1 + + + + + select t.object_type as group_type, t.pretty_plural, + coalesce(num.number_groups,0) as number_groups, + repeat(' ', t.type_level * 4) as indent + from (select t2.object_type, t2.pretty_plural, t2.tree_sortkey, + tree_level(t2.tree_sortkey) - tree_level(t1.tree_sortkey) as type_level + from acs_object_types t1, + acs_object_types t2 + where t1.object_type = 'group' + and t2.tree_sortkey like t1.tree_sortkey || '%') t + left outer join + (select o.object_type, count(*) as number_groups + from groups g, acs_objects o, + acs_object_party_privilege_map perm, + application_group_element_map app_group + where perm.object_id = g.group_id + and perm.party_id = :user_id + and perm.privilege = 'read' + and o.object_id = g.group_id + and app_group.package_id = :package_id + and app_group.element_id = g.group_id + group by o.object_type) num + using (object_type) + order by t.tree_sortkey + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/new-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/new-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/new-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,28 @@ + + + + oracle8.1.6 + + + + + select case when exists (select 1 from acs_object_types t where t.pretty_name = :pretty_name) + then 1 else 0 end + from dual + + + + + + + + + select case when exists (select 1 from acs_object_types t where t.pretty_name = :pretty_name) + then 1 else 0 end + from dual + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/new-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/new-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/new-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,28 @@ + + + + postgresql7.1 + + + + + select case when exists (select 1 from acs_object_types t where t.pretty_name = :pretty_name) + then 1 else 0 end + + + + + + + + + + select case when exists (select 1 from acs_object_types t where t.pretty_name = :pretty_name) + then 1 else 0 end + + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/one-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/one-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/one-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,59 @@ + + + + oracle8.1.6 + + + + + select t.pretty_name as group_type_pretty_name, t.dynamic_p, + nvl(gt.default_join_policy, 'open') as default_join_policy + from acs_object_types t, group_types gt + where t.object_type = :group_type + and t.object_type = gt.group_type(+) + + + + + + + + + select my_view.group_name, my_view.group_id, rownum as num + from (select /*+ ORDERED */ DISTINCT g.group_name, g.group_id + from acs_objects o, groups g, + application_group_element_map app_group, + all_object_party_privilege_map perm + where perm.object_id = g.group_id + and perm.party_id = :user_id + and perm.privilege = 'read' + and g.group_id = o.object_id + and o.object_type = :group_type + and app_group.package_id = :package_id + and app_group.element_id = g.group_id + order by lower(g.group_name)) my_view + where rownum <= 26 + + + + + + + + + select a.attribute_id, a.pretty_name, + a.ancestor_type, t.pretty_name as ancestor_pretty_name + from acs_object_type_attributes a, + (select t.object_type, t.pretty_name, level as type_level + from acs_object_types t + start with t.object_type='group' + connect by prior t.object_type = t.supertype) t + where a.object_type = :group_type + and t.object_type = a.ancestor_type + order by type_level + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/one-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/one-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/one-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,47 @@ + + + + postgresql7.1 + + + + + select my_view.group_name, my_view.group_id, 1 as num + from (select DISTINCT g.group_name, g.group_id + from acs_objects o, groups g, + application_group_element_map app_group, + all_object_party_privilege_map perm + where perm.object_id = g.group_id + and perm.party_id = :user_id + and perm.privilege = 'read' + and g.group_id = o.object_id + and o.object_type = :group_type + and app_group.package_id = :package_id + and app_group.element_id = g.group_id + order by g.group_name, g.group_id) my_view + limit 26 + + + + + + + + + select a.attribute_id, a.pretty_name, + a.ancestor_type, t.pretty_name as ancestor_pretty_name + from acs_object_type_attributes a, + (select t2.object_type, t2.pretty_name, + tree_level(t2.tree_sortkey) - tree_level(t1.tree_sortkey) + 1 as type_level + from acs_object_types t1, acs_object_types t2 + where t1.object_type = 'group' + and t2.tree_sortkey like t1.tree_sortkey || '%') t + where a.object_type = :group_type + and t.object_type = a.ancestor_type + order by type_level + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/one.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/one.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/one.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,31 @@ + + + + + + + select t.pretty_name as group_type_pretty_name, t.dynamic_p, + coalesce(gt.default_join_policy, 'open') as default_join_policy + from acs_object_types t + left outer join group_types gt + on (t.object_type = gt.group_type) + where t.object_type = :group_type + + + + + + + + + select t.pretty_name, g.rel_type, g.group_rel_type_id + from acs_object_types t, group_type_rels g + where t.object_type = g.rel_type + and g.group_type = :group_type + order by lower(t.pretty_name) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/rel-type-add-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/rel-type-add-2-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/rel-type-add-2-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,19 @@ + + + + oracle8.1.6 + + + + + select count(*) + from acs_rel_types t + where (t.object_type_one = :group_type + or acs_object_type.is_subtype_p(t.object_type_one, :group_type) = 't') + and t.rel_type = :rel_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/rel-type-add-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/rel-type-add-2-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/rel-type-add-2-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,19 @@ + + + + postgresql7.1 + + + + + select count(*) + from acs_rel_types t + where (t.object_type_one = :group_type + or acs_object_type__is_subtype_p(t.object_type_one, :group_type) = 't') + and t.rel_type = :rel_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/rel-type-add-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/rel-type-add-2.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/rel-type-add-2.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select count(*) from group_type_rels where group_type = :group_type and rel_type = :rel_type + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/rel-type-add-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/rel-type-add-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/rel-type-add-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,27 @@ + + + + oracle8.1.6 + + + + + select replace(lpad(' ', (t.type_level - 1) * 4), ' ', ' ') as indent, + t.pretty_name, t.rel_type + from (select t.pretty_name, t.object_type as rel_type, level as type_level + from acs_object_types t + where t.object_type not in (select g.rel_type + from group_type_rels g + where g.group_type = :group_type) + connect by prior t.object_type = t.supertype + start with t.object_type in ('membership_rel', 'composition_rel')) t, + acs_rel_types rel_type + where t.rel_type = rel_type.rel_type + and (rel_type.object_type_one = :group_type + or acs_object_type.is_subtype_p(rel_type.object_type_one, :group_type) = 't') + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/rel-type-add-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/rel-type-add-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/rel-type-add-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,28 @@ + + + + postgresql7.1 + + + + FIX ME CONNECT BY + + select replace(lpad(' ', (t.type_level - 1) * 4), ' ', ' ') as indent, + t.pretty_name, t.rel_type + from (select t.pretty_name, t.object_type as rel_type, level as type_level + from acs_object_types t + where t.object_type not in (select g.rel_type + from group_type_rels g + where g.group_type = :group_type) + connect by prior t.object_type = t.supertype + start with t.object_type in ('membership_rel', 'composition_rel')) t, + acs_rel_types rel_type + where t.rel_type = rel_type.rel_type + and (rel_type.object_type_one = :group_type + or acs_object_type__is_subtype_p(rel_type.object_type_one, :group_type) = 't') + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/rel-type-remove-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/rel-type-remove-2.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/rel-type-remove-2.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,24 @@ + + + + + + + select g.group_type + from group_type_rels g + where g.group_rel_type_id = :group_rel_type_id + + + + + + + + + delete from group_type_rels where group_rel_type_id = :group_rel_type_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/group-types/rel-type-remove.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/group-types/rel-type-remove.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/group-types/rel-type-remove.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,18 @@ + + + + + + + select g.rel_type, g.group_type, + t.pretty_name as rel_pretty_name, t2.pretty_name as group_type_pretty_name + from acs_object_types t, acs_object_types t2, group_type_rels g + where g.group_rel_type_id = :group_rel_type_id + and t.object_type = g.rel_type + and t2.object_type = g.group_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/change-join-policy-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/change-join-policy-2.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/change-join-policy-2.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + + update groups + set join_policy = :join_policy + where group_id = :group_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/change-join-policy.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/change-join-policy.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/change-join-policy.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + + select g.group_name, g.join_policy + from groups g + where g.group_id = :group_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/constraints-create-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/constraints-create-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/constraints-create-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,18 @@ + + + + oracle8.1.6 + + + + + select acs_group.name(:group_id) as group_name, + t.pretty_name as rel_type_pretty_name + from acs_object_types t + where t.object_type = :rel_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/constraints-create-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/constraints-create-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/constraints-create-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,18 @@ + + + + postgresql7.1 + + + + + select acs_group__name(:group_id) as group_name, + t.pretty_name as rel_type_pretty_name + from acs_object_types t + where t.object_type = :rel_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/delete-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/Attic/delete-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/delete-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,28 @@ + + + + oracle8.1.6 + + + + select acs_object.name(:group_id) from dual + + + + + + + + select (select count(*) from group_element_map where group_id = :group_id) as elements, + (select count(*) from rel_segments where group_id = :group_id) as segments, + (select count(*) + from rel_constraints cons, rel_segments segs + where segs.segment_id in (cons.rel_segment,cons.required_rel_segment) + and segs.group_id = :group_id) as constraints + from dual + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/delete-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/Attic/delete-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/delete-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,28 @@ + + + + postgresql7.1 + + + + select acs_object__name(:group_id) + + + + + + + + select (select count(*) from group_element_map where group_id = :group_id) as elements, + (select count(*) from rel_segments where group_id = :group_id) as segments, + (select count(*) + from rel_constraints cons, rel_segments segs + where segs.segment_id in (cons.rel_segment,cons.required_rel_segment) + and segs.group_id = :group_id) as constraints + + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/elements-by-group-type-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/Attic/elements-by-group-type-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/elements-by-group-type-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,24 @@ + + + + oracle8.1.6 + + + + + select /*+ ORDERED */ + t.object_type, t.pretty_name, count(g.group_id) as number_groups + from groups g, acs_objects o, acs_object_types t, + application_group_element_map app_group + where o.object_id = g.group_id + and o.object_type = t.object_type + and app_group.package_id = :package_id + and app_group.element_id = g.group_id + group by t.object_type, t.pretty_name + order by lower(t.pretty_name) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/elements-by-group-type-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/Attic/elements-by-group-type-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/elements-by-group-type-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,24 @@ + + + + postgresql7.1 + + + + + select + t.object_type, t.pretty_name, count(g.group_id) as number_groups + from groups g, acs_objects o, acs_object_types t, + application_group_element_map app_group + where o.object_id = g.group_id + and o.object_type = t.object_type + and app_group.package_id = :package_id + and app_group.element_id = g.group_id + group by t.object_type, t.pretty_name + order by lower(t.pretty_name) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/elements-by-group-type.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/Attic/elements-by-group-type.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/elements-by-group-type.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,22 @@ + + + + + + + select + t.object_type, t.pretty_name, count(g.group_id) as number_groups + from groups g, acs_objects o, acs_object_types t, + application_group_element_map app_group + where o.object_id = g.group_id + and o.object_type = t.object_type + and app_group.package_id = :package_id + and app_group.element_id = g.group_id + group by t.object_type, t.pretty_name + order by lower(t.pretty_name) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/elements-by-rel-type-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/Attic/elements-by-rel-type-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/elements-by-rel-type-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,44 @@ + + + + oracle8.1.6 + + + + + select g.rel_type, g.group_rel_id, + acs_object_type.pretty_name(g.rel_type) as rel_type_pretty_name, + s.segment_id, s.segment_name, + acs_rel_type.role_pretty_plural(rel_types.role_two) as role_pretty_plural, + acs_rel_type.role_pretty_name(rel_types.role_two) as role_pretty_name, + rels.num_rels, + case when valid_types.group_id = null then 0 else 1 end as rel_type_valid_p + from group_rels g, + rel_segments s, + acs_rel_types rel_types, + (select rel_type, count(*) as num_rels + from group_component_map + where group_id = :group_id + and group_id = container_id + group by rel_type + UNION ALL + select rel_type, count(*) as num_rels + from group_approved_member_map + where group_id = :group_id + and group_id = container_id + group by rel_type) rels, + rc_valid_rel_types valid_types + where g.group_id = s.group_id(+) + and g.rel_type = s.rel_type(+) + and g.rel_type = rels.rel_type(+) + and g.rel_type = rel_types.rel_type + and g.group_id = :group_id + and g.group_id = valid_types.group_id(+) + and g.rel_type = valid_types.rel_type(+) + order by lower(g.rel_type) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/elements-by-rel-type-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/Attic/elements-by-rel-type-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/elements-by-rel-type-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,40 @@ + + + + postgresql7.1 + + + + + select g.rel_type, g.group_rel_id, + acs_object_type__pretty_name(g.rel_type) as rel_type_pretty_name, + s.segment_id, s.segment_name, + acs_rel_type__role_pretty_plural(rel_types.role_two) as role_pretty_plural, + acs_rel_type__role_pretty_name(rel_types.role_two) as role_pretty_name, + rels.num_rels, + case when valid_types.group_id = null then 0 else 1 end as rel_type_valid_p + from group_rels g + left outer join rel_segments s using (group_id, rel_type) + left outer join rc_valid_rel_types valid_types using (group_id, rel_type) + left outer join + (select rel_type, count(*) as num_rels + from group_component_map + where group_id = :group_id + and group_id = container_id + group by rel_type + UNION ALL + select rel_type, count(*) as num_rels + from group_approved_member_map + where group_id = :group_id + and group_id = container_id + group by rel_type) rels using (rel_type), + acs_rel_types rel_types + where g.rel_type = rel_types.rel_type + and g.group_id = :group_id + order by lower(g.rel_type) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/elements-display-list-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/elements-display-list-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/elements-display-list-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,42 @@ + + + + oracle8.1.6 + + + + + select object_type as ancestor_rel_type + from acs_object_types + where supertype = 'relationship' + and object_type in ( + select object_type from acs_object_types + start with object_type = :rel_type + connect by object_type = prior supertype + ) + + + + + + + + +select r.rel_id, + party_names.party_name as element_name +from (select /*+ ORDERED */ DISTINCT rels.rel_id, object_id_two + from $extra_tables acs_rels rels, all_object_party_privilege_map perm + where perm.object_id = rels.rel_id + and perm.party_id = :user_id + and perm.privilege = 'read' + and rels.rel_type = :rel_type + and rels.object_id_one = :group_id $extra_where_clauses) r, + party_names +where r.object_id_two = party_names.party_id +order by lower(element_name) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/elements-display-list-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/elements-display-list-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/elements-display-list-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,23 @@ + + + + postgresql7.1 + + + + FIX ME CONNECT BY + + select object_type as ancestor_rel_type + from acs_object_types + where supertype = 'relationship' + and object_type in ( + select object_type from acs_object_types + start with object_type = :rel_type + connect by object_type = prior supertype + ) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/elements-display-list.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/elements-display-list.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/elements-display-list.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,25 @@ + + + + + + FIX ME REMOVE OPTIMIZATION HINT + +select r.rel_id, + party_names.party_name as element_name +from (select /*+ ORDERED */ DISTINCT rels.rel_id, object_id_two + from $extra_tables acs_rels rels, all_object_party_privilege_map perm + where perm.object_id = rels.rel_id + and perm.party_id = :user_id + and perm.privilege = 'read' + and rels.rel_type = :rel_type + and rels.object_id_one = :group_id $extra_where_clauses) r, + party_names +where r.object_id_two = party_names.party_id +order by lower(element_name) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/elements-display-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/elements-display-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/elements-display-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,20 @@ + + + + oracle8.1.6 + + + + + select acs_group.name(:group_id) as group_name, + acs_object_type.pretty_name(:rel_type) as rel_type_pretty_name, + acs_rel_type.role_pretty_plural(rel_types.role_two) as role_pretty_plural, + acs_rel_type.role_pretty_name(rel_types.role_two) as role_pretty_name + from acs_rel_types rel_types + where rel_types.rel_type = :rel_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/elements-display-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/elements-display-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/elements-display-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,20 @@ + + + + postgresql7.1 + + + + + select acs_group__name(:group_id) as group_name, + acs_object_type__pretty_name(:rel_type) as rel_type_pretty_name, + acs_rel_type__role_pretty_plural(rel_types.role_two) as role_pretty_plural, + acs_rel_type__role_pretty_name(rel_types.role_two) as role_pretty_name + from acs_rel_types rel_types + where rel_types.rel_type = :rel_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/index.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/index.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,16 @@ + + + + + + + select ag.group_id as subsite_group_id, ap.instance_name + from application_groups ag, apm_packages ap + where ag.package_id = ap.package_id + and ag.package_id = :package_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/new-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/new-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/new-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,22 @@ + + + + oracle8.1.6 + + + + + select object_type as ancestor_rel_type + from acs_object_types + where supertype = 'relationship' + and object_type in ( + select object_type from acs_object_types + start with object_type = :add_with_rel_type + connect by object_type = prior supertype + ) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/new-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/new-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/new-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,24 @@ + + + + postgresql7.1 + + + + + select object_type as ancestor_rel_type + from acs_object_types + where supertype = 'relationship' + and object_type in ( + select t2.object_type + from acs_object_types t1, acs_object_types t2 + where t1.object_type = :add_with_rel_type + and t2.tree_sortkey <= t1.tree_sortkey + and t1.tree_sortkey like (t2.tree_sortkey || '%') + ) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/new.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/new.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/new.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,37 @@ + + + + + + + select count(*) from groups where group_id = :group_id + + + + + + + + + select group_name as add_to_group_name, + join_policy as add_to_group_join_policy + from groups + where group_id = :add_to_group_id + + + + + + + + + select t.pretty_name as group_type_pretty_name, + t.table_name + from acs_object_types t + where t.object_type = :group_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/one.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/Attic/one.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/one.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,18 @@ + + + + + + + select g.group_name, g.join_policy, + o.object_type as group_type + from groups g, acs_objects o, acs_object_types t + where g.group_id = o.object_id + and o.object_type = t.object_type + and g.group_id = :group_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/rel-type-add-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/rel-type-add-2-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/rel-type-add-2-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,47 @@ + + + + oracle8.1.6 + + + + + select count(*) + from acs_rel_types t + where (t.object_type_one = :group_type + or acs_object_type.is_subtype_p(t.object_type_one, :group_type) = 't') + and t.rel_type = :rel_type + + + + + + + + + select case when exists (select 1 + from group_rels + where group_id = :group_id + and rel_type = :rel_type) + then 1 else 0 end + from dual + + + + + + + + + select case when exists (select 1 + from rel_segments s + where s.group_id = :group_id + and s.rel_type = :rel_type) + then 1 else 0 end + from dual + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/rel-type-add-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/rel-type-add-2-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/rel-type-add-2-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,47 @@ + + + + postgresql7.1 + + + + + select count(*) + from acs_rel_types t + where (t.object_type_one = :group_type + or acs_object_type__is_subtype_p(t.object_type_one, :group_type) = 't') + and t.rel_type = :rel_type + + + + + + + + + select case when exists (select 1 + from group_rels + where group_id = :group_id + and rel_type = :rel_type) + then 1 else 0 end + + + + + + + + + + select case when exists (select 1 + from rel_segments s + where s.group_id = :group_id + and s.rel_type = :rel_type) + then 1 else 0 end + + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/rel-type-add-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/rel-type-add-2.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/rel-type-add-2.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + + select o.object_type as group_type + from acs_objects o + where o.object_id = :group_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/rel-type-add-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/rel-type-add-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/rel-type-add-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,27 @@ + + + + oracle8.1.6 + + + + + select replace(lpad(' ', (t.type_level - 1) * 4), ' ', ' ') as indent, + t.pretty_name, t.rel_type + from (select t.pretty_name, t.object_type as rel_type, level as type_level + from acs_object_types t + where t.object_type not in (select g.rel_type + from group_rels g + where g.group_id = :group_id) + connect by prior t.object_type = t.supertype + start with t.object_type in ('membership_rel', 'composition_rel')) t, + acs_rel_types rel_type + where t.rel_type = rel_type.rel_type + and (rel_type.object_type_one = :group_type + or acs_object_type.is_subtype_p(rel_type.object_type_one, :group_type) = 't') + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/rel-type-add-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/rel-type-add-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/rel-type-add-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,28 @@ + + + + postgresql7.1 + + + + FIX ME CONNECT BY + + select replace(lpad(' ', (t.type_level - 1) * 4), ' ', ' ') as indent, + t.pretty_name, t.rel_type + from (select t.pretty_name, t.object_type as rel_type, level as type_level + from acs_object_types t + where t.object_type not in (select g.rel_type + from group_rels g + where g.group_id = :group_id) + connect by prior t.object_type = t.supertype + start with t.object_type in ('membership_rel', 'composition_rel')) t, + acs_rel_types rel_type + where t.rel_type = rel_type.rel_type + and (rel_type.object_type_one = :group_type + or acs_object_type__is_subtype_p(rel_type.object_type_one, :group_type) = 't') + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/rel-type-add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/rel-type-add.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/rel-type-add.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + + select o.object_type as group_type + from acs_objects o + where o.object_id = :group_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/rel-type-remove-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/rel-type-remove-2.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/rel-type-remove-2.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,49 @@ + + + + + + + select g.group_id, g.rel_type + from group_rels g, acs_object_types t + where g.rel_type = t.object_type + and g.group_rel_id = :group_rel_id + + + + + + + + + select r.rel_id + from acs_rels r + where r.rel_type = :rel_type + and r.object_id_one = :group_id + + + + + + + + + select segment_id + from rel_segments + where group_id = :group_id + and rel_type = :rel_type + + + + + + + + + delete from group_rels where group_rel_id = :group_rel_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/rel-type-remove-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/Attic/rel-type-remove-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/rel-type-remove-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,19 @@ + + + + oracle8.1.6 + + + + + select g.rel_type, g.group_id, acs_object.name(g.group_id) as group_name, + t.pretty_name as rel_pretty_name + from acs_object_types t, group_rels g + where g.group_rel_id = :group_rel_id + and t.object_type = g.rel_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/groups/rel-type-remove-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/groups/Attic/rel-type-remove-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/groups/rel-type-remove-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,19 @@ + + + + postgresql7.1 + + + + + select g.rel_type, g.group_id, acs_object__name(g.group_id) as group_name, + t.pretty_name as rel_pretty_name + from acs_object_types t, group_rels g + where g.group_rel_id = :group_rel_id + and t.object_type = g.rel_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/object-types/alphabetical-index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/object-types/alphabetical-index.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/object-types/alphabetical-index.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,16 @@ + + + + + + + select object_type, + pretty_name + from acs_object_types + order by lower(pretty_name) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/object-types/one-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/object-types/one-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/object-types/one-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,22 @@ + + + + oracle8.1.6 + + + + + select utc.column_name, + utc.data_type, + ucc.comments + from user_tab_columns utc, + user_col_comments ucc + where utc.table_name = '[string toupper $table_name]' + and utc.table_name = ucc.table_name(+) + and utc.column_name = ucc.column_name(+) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/object-types/one.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/object-types/one.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/object-types/one.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,81 @@ + + + + + + + select supertype, + abstract_p, + pretty_name, + pretty_plural, + table_name, + id_column, + name_method, + type_extension_table, + package_name, + dynamic_p + from acs_object_types + where object_type = :object_type + + + + + + + + + select attribute_name, + pretty_name, + pretty_plural, + datatype, + default_value, + min_n_values, + max_n_values, + storage, + table_name as attr_table_name, + column_name + from acs_attributes + where object_type = :object_type + + + + + + + + select comments from user_tab_comments where table_name = '[string toupper $table_name]' + + + + + + + FIX ME OUTER JOIN + + select utc.column_name, + utc.data_type, + ucc.comments + from user_tab_columns utc, + user_col_comments ucc + where utc.table_name = '[string toupper $table_name]' + and utc.table_name = ucc.table_name(+) + and utc.column_name = ucc.column_name(+) + + + + + + + + + select replace (replace (text, ' ', ' '), chr(9), '    ') as text + from user_source + where lower(name) = :package_name + and type = 'PACKAGE BODY' + order by line + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/parties/new-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/parties/new-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/parties/new-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,22 @@ + + + + oracle8.1.6 + + + + + select object_type as ancestor_rel_type + from acs_object_types + where supertype = 'relationship' + and object_type in ( + select object_type from acs_object_types + start with object_type = :add_with_rel_type + connect by object_type = prior supertype + ) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/parties/new-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/parties/new-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/parties/new-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,24 @@ + + + + postgresql7.1 + + + + + select object_type as ancestor_rel_type + from acs_object_types + where supertype = 'relationship' + and object_type in ( + select t2.object_type + from acs_object_types t1, acs_object_types t2 + where t1.object_type = :add_with_rel_type + and t2.tree_sortkey <= t1.tree_sortkey + and t1.tree_sortkey like (t2.tree_sortkey || '%') + ) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/parties/new.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/parties/new.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/parties/new.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,28 @@ + + + + + + + select group_name as add_to_group_name, + join_policy as add_to_group_join_policy + from groups + where group_id = :add_to_group_id + + + + + + + + + select t.pretty_name as party_type_pretty_name, + t.table_name + from acs_object_types t + where t.object_type = :party_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/parties/one-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/parties/Attic/one-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/parties/one-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,18 @@ + + + + oracle8.1.6 + + + + + select acs_object.name(:party_id) as party_name, + object_type as party_type + from acs_objects + where object_id = :party_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/parties/one-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/parties/Attic/one-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/parties/one-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,18 @@ + + + + postgresql7.1 + + + + + select acs_object__name(:party_id) as party_name, + object_type as party_type + from acs_objects + where object_id = :party_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/delete-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/delete-2.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/delete-2.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + + select s.group_id from rel_segments s where s.segment_id = :segment_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/delete.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/delete.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/delete.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + + select s.segment_name + from rel_segments s + where s.segment_id = :segment_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/elements-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/elements-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/elements-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,19 @@ + + + + oracle8.1.6 + + + + + select s.segment_name, s.group_id, + acs_rel_type.role_pretty_plural(r.role_two) as role_pretty_plural + from rel_segments s, acs_rel_types r + where s.segment_id = :segment_id + and s.rel_type = r.rel_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/elements-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/elements-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/elements-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,19 @@ + + + + postgresql7.1 + + + + + select s.segment_name, s.group_id, + acs_rel_type__role_pretty_plural(r.role_two) as role_pretty_plural + from rel_segments s, acs_rel_types r + where s.segment_id = :segment_id + and s.rel_type = r.rel_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/index-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/index-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/index-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,27 @@ + + + + oracle8.1.6 + + + + + select s.segment_id, s.segment_name, s.group_id, acs_object.name(s.group_id) as group_name, + s.rel_type, t.pretty_name as rel_type_pretty_name + from acs_object_types t, + rel_segments s, + acs_object_party_privilege_map perm, + application_group_segments ags + where perm.object_id = s.segment_id + and perm.party_id = :user_id + and perm.privilege = 'read' + and t.object_type = s.rel_type + and s.segment_id = ags.segment_id + and ags.package_id = :package_id + order by lower(s.segment_name) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/index-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/index-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/index-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,27 @@ + + + + postgresql7.1 + + + + + select s.segment_id, s.segment_name, s.group_id, acs_object__name(s.group_id) as group_name, + s.rel_type, t.pretty_name as rel_type_pretty_name + from acs_object_types t, + rel_segments s, + acs_object_party_privilege_map perm, + application_group_segments ags + where perm.object_id = s.segment_id + and perm.party_id = :user_id + and perm.privilege = 'read' + and t.object_type = s.rel_type + and s.segment_id = ags.segment_id + and ags.package_id = :package_id + order by lower(s.segment_name) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/new-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/Attic/new-2-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/new-2-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,18 @@ + + + + oracle8.1.6 + + + + + select acs_group.name(:group_id) as group_name, + nvl(acs_rel_type.role_pretty_plural(t.role_two),'Elements') as role_pretty_plural + from acs_rel_types t + where t.rel_type = :rel_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/new-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/Attic/new-2-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/new-2-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,18 @@ + + + + postgresql7.1 + + + + + select acs_group__name(:group_id) as group_name, + coalesce(acs_rel_type__role_pretty_plural(t.role_two),'Elements') as role_pretty_plural + from acs_rel_types t + where t.rel_type = :rel_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/new-3-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/new-3-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/new-3-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,18 @@ + + + + oracle8.1.6 + + + + + select case when exists + (select 1 from rel_segments s where s.segment_id <> :segment_id) + then 1 else 0 end + from dual + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/new-3-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/new-3-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/new-3-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,18 @@ + + + + postgresql7.1 + + + + + select case when exists + (select 1 from rel_segments s where s.segment_id <> :segment_id) + then 1 else 0 end + + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/new-3.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/new-3.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/new-3.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,16 @@ + + + + + + + select s.segment_id + from rel_segments s + where s.group_id = :group_id + and s.rel_type = :rel_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/new-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/new-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/new-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,31 @@ + + + + oracle8.1.6 + + + + + select t.pretty_name, t.object_type as rel_type, + replace(lpad(' ', (level - 1) * 4), ' ', ' ') as indent + from acs_object_types t + where t.object_type not in (select s.rel_type from rel_segments s where s.group_id = :group_id) + connect by prior t.object_type = t.supertype + start with t.object_type in ('membership_rel', 'composition_rel') + order by lower(t.pretty_name) desc + + + + + + + + + select acs_group.name(:group_id) as group_name + from dual + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/new-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/new-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/new-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,32 @@ + + + + postgresql7.1 + + + + FIX ME CONNECT BY + + select t.pretty_name, t.object_type as rel_type, + replace(lpad(' ', (level - 1) * 4), ' ', ' ') as indent + from acs_object_types t + where t.object_type not in (select s.rel_type from rel_segments s where s.group_id = :group_id) + connect by prior t.object_type = t.supertype + start with t.object_type in ('membership_rel', 'composition_rel') + order by lower(t.pretty_name) desc + + + + + + + + + select acs_group__name(:group_id) as group_name + + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/one-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/one-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/one-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,20 @@ + + + + oracle8.1.6 + + + + + select s.segment_id, s.segment_name, s.group_id, acs_object.name(s.group_id) as group_name, + s.rel_type, acs_object_type.pretty_name(r.rel_type) as rel_type_pretty_name, + acs_rel_type.role_pretty_plural(r.role_two) as role_pretty_plural + from rel_segments s, acs_rel_types r + where s.segment_id = :segment_id + and s.rel_type = r.rel_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/one-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/one-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/one-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,20 @@ + + + + postgresql7.1 + + + + + select s.segment_id, s.segment_name, s.group_id, acs_object__name(s.group_id) as group_name, + s.rel_type, acs_object_type__pretty_name(r.rel_type) as rel_type_pretty_name, + acs_rel_type__role_pretty_plural(r.role_two) as role_pretty_plural + from rel_segments s, acs_rel_types r + where s.segment_id = :segment_id + and s.rel_type = r.rel_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/one.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/one.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/one.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,29 @@ + + + + + + + select c.constraint_id, c.constraint_name, c.rel_side + from rel_constraints c + where c.rel_segment = :segment_id + + + + + + + + + select count(*) as number_elements + from rel_segment_party_map map, acs_object_party_privilege_map perm + where perm.object_id = map.party_id + and perm.party_id = :user_id + and perm.privilege = 'read' + and map.segment_id = :segment_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/delete-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/delete-2-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/delete-2-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,15 @@ + + + + oracle8.1.6 + + + + + begin rel_constraint.delete(constraint_id => :constraint_id); end; + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/delete-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/delete-2-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/delete-2-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,16 @@ + + + + postgresql7.1 + + + + FIX ME PLSQL + + begin rel_constraint__delete(constraint_id => :constraint_id); end; + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/delete-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/delete-2.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/delete-2.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,29 @@ + + + + + + + select c.rel_segment as segment_id from rel_constraints c where c.constraint_id = :constraint_id + + + + + + + + + select 1 + from rel_constraints c, application_group_segments s, + application_group_segments s2 + where c.rel_segment = s.segment_id + and c.constraint_id = :constraint_id + and s.package_id = :package_id + and s2.segment_id = c.required_rel_segment + and s2.package_id = :package_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/delete.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/delete.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/delete.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,20 @@ + + + + + + + select c.constraint_name, s.segment_name + from rel_constraints c, application_group_segments s, + application_group_segments s2 + where c.rel_segment = s.segment_id + and c.constraint_id = :constraint_id + and s.package_id = :package_id + and s2.segment_id = c.required_rel_segment + and s2.package_id = :package_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/index.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/index.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,23 @@ + + + + + + + select c.constraint_id, c.constraint_name + from rel_constraints c, acs_object_party_privilege_map perm, + application_group_segments s1, application_group_segments s2 + where perm.object_id = c.constraint_id + and perm.party_id = :user_id + and perm.privilege = 'read' + and s1.segment_id = c.rel_segment + and s1.package_id = :package_id + and s2.segment_id = c.required_rel_segment + and s2.package_id = :package_id + order by lower(c.constraint_name) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/new-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/new-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/new-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,52 @@ + + + + oracle8.1.6 + + + + + select s.segment_name, + acs_rel_type.role_pretty_name(t.role_one) as role_one_name, + acs_rel_type.role_pretty_name(t.role_two) as role_two_name + from rel_segments s, acs_rel_types t + where s.rel_type = t.rel_type + and s.segment_id = :rel_segment + + + + + + + + + BEGIN + :1 := rel_constraint.new(constraint_name => :constraint_name, + rel_segment => :rel_segment, + rel_side => :rel_side, + required_rel_segment => :required_rel_segment, + creation_user => :creation_user, + creation_ip => :creation_ip + ); + END; + + + + + + + + + select viol.rel_id, acs_object.name(viol.party_id) as name + from rel_constraints_violated_one viol + where viol.constraint_id = :constraint_id + UNION ALL + select viol.rel_id, acs_object.name(viol.party_id) as name + from rel_constraints_violated_two viol + where viol.constraint_id = :constraint_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/new-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/new-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/new-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,53 @@ + + + + postgresql7.1 + + + + + select s.segment_name, + acs_rel_type__role_pretty_name(t.role_one) as role_one_name, + acs_rel_type__role_pretty_name(t.role_two) as role_two_name + from rel_segments s, acs_rel_types t + where s.rel_type = t.rel_type + and s.segment_id = :rel_segment + + + + + + + + FIX ME PLSQL + + BEGIN + :1 := rel_constraint__new(constraint_name => :constraint_name, + rel_segment => :rel_segment, + rel_side => :rel_side, + required_rel_segment => :required_rel_segment, + creation_user => :creation_user, + creation_ip => :creation_ip + ); + END; + + + + + + + + + select viol.rel_id, acs_object__name(viol.party_id) as name + from rel_constraints_violated_one viol + where viol.constraint_id = :constraint_id + UNION ALL + select viol.rel_id, acs_object__name(viol.party_id) as name + from rel_constraints_violated_two viol + where viol.constraint_id = :constraint_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/new.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/new.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/new.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,27 @@ + + + + + + + select s.segment_name, s.segment_id + from application_group_segments s + where s.segment_id <> :rel_segment + and s.package_id = :package_id + + order by lower(s.segment_name) + + + + + + + + + } on_error { + if + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/one-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/Attic/one-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/one-oracle.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,63 @@ + + + + oracle8.1.6 + + + + + select c.constraint_id, c.constraint_name, c.rel_side, + s.segment_id, s.segment_name, s.rel_type, acs_group.name(s.group_id) as group_name, + s2.segment_id as req_segment_id, s2.segment_name as req_segment_name, + s2.rel_type as req_rel_type, acs_group.name(s2.group_id) as req_group_name + from application_group_segments s, application_group_segments s2, + rel_constraints c + where s.segment_id = c.rel_segment + and s2.segment_id = c.required_rel_segment + and c.constraint_id = :constraint_id + and s.package_id = :package_id + and s2.package_id = :package_id + + + + + + + + + select role1.role as role_one, + nvl(role1.pretty_name,'Object on side one') as role_one_pretty_name, + nvl(role1.pretty_plural,'Objects on side one') as role_one_pretty_plural, + role2.role as role_two, + nvl(role2.pretty_name,'Object on side two') as role_two_pretty_name, + nvl(role2.pretty_plural,'Objects on side two') as role_two_pretty_plural, + acs_object_type.pretty_name(rel.rel_type) as rel_type_pretty_name + from acs_rel_types rel, acs_rel_roles role1, acs_rel_roles role2 + where rel.rel_type = :rel_type + and rel.role_one = role1.role(+) + and rel.role_two = role2.role(+) + + + + + + + + + select role1.role as role_one, + nvl(role1.pretty_name,'Object on side one') as role_one_pretty_name, + nvl(role1.pretty_plural,'Objects on side one') as role_one_pretty_plural, + role2.role as role_two, + nvl(role2.pretty_name,'Object on side two') as role_two_pretty_name, + nvl(role2.pretty_plural,'Objects on side two') as role_two_pretty_plural, + acs_object_type.pretty_name(rel.rel_type) as rel_type_pretty_name + from acs_rel_types rel, acs_rel_roles role1, acs_rel_roles role2 + where rel.rel_type = :rel_type + and rel.role_one = role1.role(+) + and rel.role_two = role2.role(+) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/one-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/Attic/one-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-segments/constraints/one-postgresql.xql 15 May 2001 16:59:00 -0000 1.1 @@ -0,0 +1,65 @@ + + + + postgresql7.1 + + + + + select c.constraint_id, c.constraint_name, c.rel_side, + s.segment_id, s.segment_name, s.rel_type, acs_group__name(s.group_id) as group_name, + s2.segment_id as req_segment_id, s2.segment_name as req_segment_name, + s2.rel_type as req_rel_type, acs_group__name(s2.group_id) as req_group_name + from application_group_segments s, application_group_segments s2, + rel_constraints c + where s.segment_id = c.rel_segment + and s2.segment_id = c.required_rel_segment + and c.constraint_id = :constraint_id + and s.package_id = :package_id + and s2.package_id = :package_id + + + + + + + + FIX ME OUTER JOIN + + select role1.role as role_one, + coalesce(role1.pretty_name,'Object on side one') as role_one_pretty_name, + coalesce(role1.pretty_plural,'Objects on side one') as role_one_pretty_plural, + role2.role as role_two, + coalesce(role2.pretty_name,'Object on side two') as role_two_pretty_name, + coalesce(role2.pretty_plural,'Objects on side two') as role_two_pretty_plural, + acs_object_type__pretty_name(rel.rel_type) as rel_type_pretty_name + from acs_rel_types rel, acs_rel_roles role1, acs_rel_roles role2 + where rel.rel_type = :rel_type + and rel.role_one = role1.role(+) + and rel.role_two = role2.role(+) + + + + + + + + FIX ME OUTER JOIN + + select role1.role as role_one, + coalesce(role1.pretty_name,'Object on side one') as role_one_pretty_name, + coalesce(role1.pretty_plural,'Objects on side one') as role_one_pretty_plural, + role2.role as role_two, + coalesce(role2.pretty_name,'Object on side two') as role_two_pretty_name, + coalesce(role2.pretty_plural,'Objects on side two') as role_two_pretty_plural, + acs_object_type__pretty_name(rel.rel_type) as rel_type_pretty_name + from acs_rel_types rel, acs_rel_roles role1, acs_rel_roles role2 + where rel.rel_type = :rel_type + and rel.role_one = role1.role(+) + and rel.role_two = role2.role(+) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/add-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/add-2.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/add-2.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,16 @@ + + + + + + + insert into group_type_allowed_rels + (constraint_id, group_type, rel_type) + values + (:constraint_id, :object_type, :rel_type) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/add.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/add.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,16 @@ + + + + + + + select o.object_type as rel_type, o.pretty_name + from acs_object_types o + where o.object_type in ('composition_rel','membership_rel') + and o.object_type not in (select g.rel_type from group_type_allowed_rels g where g.group_type = :object_type) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/delete-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/delete-2-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/delete-2-oracle.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,25 @@ + + + + oracle8.1.6 + + + + + BEGIN + acs_rel_type.drop_type( rel_type => :rel_type, + cascade_p => 't' ); + END; + + + + + + + + drop table $table_name + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/delete-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/delete-2-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/delete-2-postgresql.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,26 @@ + + + + postgresql7.1 + + + + + BEGIN + acs_rel_type__drop_type( rel_type => :rel_type, + cascade_p => 't' ); + END; + + + + + + + + FIX ME PLSQL +drop table $table_name + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/delete-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/delete-2.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/delete-2.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,43 @@ + + + + + + + select t.table_name + from acs_object_types t + where t.object_type = :rel_type + + + + + + + + + select r.rel_id + from acs_rels r, acs_object_party_privilege_map perm + where perm.object_id = r.rel_id + and perm.party_id = :user_id + and perm.privilege = 'delete' + and r.rel_type = :rel_type + + + + + + + + + select s.segment_id + from rel_segments s, acs_object_party_privilege_map perm + where perm.object_id = s.segment_id + and perm.party_id = :user_id + and perm.privilege = 'delete' + and s.rel_type = :rel_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/delete-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/delete-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/delete-oracle.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,30 @@ + + + + oracle8.1.6 + + + + + select case when exists (select 1 + from acs_object_types t + where t.supertype = :rel_type) + then 1 else 0 end + from dual + + + + + + + + + select (select count(*) from rel_segments where rel_type = :rel_type) as segments, + (select count(*) from acs_rels where rel_type = :rel_type) as rels + from dual + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/delete-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/delete-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/delete-postgresql.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,30 @@ + + + + postgresql7.1 + + + + + select case when exists (select 1 + from acs_object_types t + where t.supertype = :rel_type) + then 1 else 0 end + + + + + + + + + + select (select count(*) from rel_segments where rel_type = :rel_type) as segments, + (select count(*) from acs_rels where rel_type = :rel_type) as rels + + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/delete.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/delete.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/delete.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,26 @@ + + + + + + + select t.pretty_name + from acs_object_types t + where t.object_type = :rel_type + + + + + + + + + select t.object_type as rel_type, t.pretty_name + from acs_object_types t + where t.supertype = :rel_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/index-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/index-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/index-oracle.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,31 @@ + + + + oracle8.1.6 + + + + + select t.object_type as rel_type, t.pretty_name, t.indent, + nvl(num.number_relationships,0) as number_relationships + from (select t.pretty_name, t.object_type, rownum as inner_rownum, + replace(lpad(' ', (level - 1) * 4), ' ', ' ') as indent + from acs_object_types t + connect by prior t.object_type = t.supertype + start with t.object_type in ('membership_rel','composition_rel') + order by lower(t.pretty_name)) t, + (select r.rel_type, count(*) as number_relationships + from acs_objects o, acs_rel_types r, + app_group_distinct_rel_map m + where r.rel_type = o.object_type + and o.object_id = m.rel_id + and m.package_id = :package_id + group by r.rel_type) num + where t.object_type = num.rel_type(+) + order by t.inner_rownum + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/index-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/index-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/index-postgresql.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,34 @@ + + + + postgresql7.1 + + + + FIX ME OUTER JOIN +FIX ME CONNECT BY +FIX ME ROWNUM + + select t.object_type as rel_type, t.pretty_name, t.indent, + coalesce(num.number_relationships,0) as number_relationships + from (select t.pretty_name, t.object_type, rownum as inner_rownum, + replace(lpad(' ', (level - 1) * 4), ' ', ' ') as indent + from acs_object_types t + connect by prior t.object_type = t.supertype + start with t.object_type in ('membership_rel','composition_rel') + order by lower(t.pretty_name)) t, + (select r.rel_type, count(*) as number_relationships + from acs_objects o, acs_rel_types r, + app_group_distinct_rel_map m + where r.rel_type = o.object_type + and o.object_id = m.rel_id + and m.package_id = :package_id + group by r.rel_type) num + where t.object_type = num.rel_type(+) + order by t.inner_rownum + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/mapping-remove-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/mapping-remove-2.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/mapping-remove-2.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,24 @@ + + + + + + + delete from group_rels + where group_rel_id = :group_rel_id + + + + + + + + + delete from group_type_rels + where group_type_rel_id = :group_type_rel_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/mapping-remove.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/mapping-remove.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/mapping-remove.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,18 @@ + + + + + + + select g.rel_type, g.group_type, + t.pretty_name as rel_pretty_name, t2.pretty_name as group_type_pretty_name + from acs_object_types t, acs_object_types t2, group_type_rels g + where g.group_rel_type_id = :group_rel_type_id + and t.object_type = g.rel_type + and t2.object_type = g.group_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/new-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/new-2-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/new-2-oracle.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,54 @@ + + + + oracle8.1.6 + + + + + select replace(lpad(' ', (level - 1) * 4), ' ', ' ') || t.pretty_name, + t.object_type as rel_type + from acs_object_types t + connect by prior t.object_type = t.supertype + start with t.object_type=:max_object_type_one + + + + + + + + + select replace(lpad(' ', (level - 1) * 4), ' ', ' ') || t.pretty_name, + t.object_type as rel_type + from acs_object_types t + connect by prior t.object_type = t.supertype + start with t.object_type=:max_object_type_one + + + + + + + + + select case when exists (select 1 from acs_object_types t where t.pretty_name = :pretty_name) + then 1 else 0 end + from dual + + + + + + + + + select case when exists (select 1 from acs_object_types t where t.pretty_name = :pretty_name) + then 1 else 0 end + from dual + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/new-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/new-2-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/new-2-postgresql.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,56 @@ + + + + postgresql7.1 + + + + FIX ME CONNECT BY + + select replace(lpad(' ', (level - 1) * 4), ' ', ' ') || t.pretty_name, + t.object_type as rel_type + from acs_object_types t + connect by prior t.object_type = t.supertype + start with t.object_type=:max_object_type_one + + + + + + + + FIX ME CONNECT BY + + select replace(lpad(' ', (level - 1) * 4), ' ', ' ') || t.pretty_name, + t.object_type as rel_type + from acs_object_types t + connect by prior t.object_type = t.supertype + start with t.object_type=:max_object_type_one + + + + + + + + + select case when exists (select 1 from acs_object_types t where t.pretty_name = :pretty_name) + then 1 else 0 end + + + + + + + + + + select case when exists (select 1 from acs_object_types t where t.pretty_name = :pretty_name) + then 1 else 0 end + + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/new-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/new-2.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/new-2.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,34 @@ + + + + + + + select r.object_type_one as max_object_type_one, + r.object_type_two as max_object_type_two, + t.pretty_name as supertype_pretty_name, + r.role_one as supertype_role_one, r.role_two as supertype_role_two, + r.min_n_rels_one as supertype_min_n_rels_one, + r.max_n_rels_one as supertype_max_n_rels_one, + r.min_n_rels_two as supertype_min_n_rels_two, + r.max_n_rels_two as supertype_max_n_rels_two + from acs_object_types t, acs_rel_types r + where r.rel_type = :supertype + and r.rel_type = t.object_type + + + + + + + + + select r.pretty_name, r.role + from acs_rel_roles r + order by lower(r.role) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/new-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/new-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/new-oracle.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,19 @@ + + + + oracle8.1.6 + + + + + select replace(lpad(' ', (level - 1) * 4), ' ', ' ') || t.pretty_name as name, + t.object_type + from acs_object_types t + connect by prior t.object_type = t.supertype + start with t.object_type in ('membership_rel','composition_rel') + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/new-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/new-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/new-postgresql.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,20 @@ + + + + postgresql7.1 + + + + FIX ME CONNECT BY + + select replace(lpad(' ', (level - 1) * 4), ' ', ' ') || t.pretty_name as name, + t.object_type + from acs_object_types t + connect by prior t.object_type = t.supertype + start with t.object_type in ('membership_rel','composition_rel') + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/one-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/one-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/one-oracle.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,44 @@ + + + + oracle8.1.6 + + + + + select t1.pretty_name as object_type_one_pretty_name, + r.object_type_one, acs_rel_type.role_pretty_name(r.role_one) as role_one_pretty_name, + r.role_one, r.min_n_rels_one, r.max_n_rels_one, + t2.pretty_name as object_type_two_pretty_name, + r.object_type_two, acs_rel_type.role_pretty_name(r.role_two) as role_two_pretty_name, + r.role_two, r.min_n_rels_two, r.max_n_rels_two + from acs_rel_types r, acs_object_types t1, acs_object_types t2 + where r.rel_type = :rel_type + and r.object_type_one = t1.object_type + and r.object_type_two = t2.object_type + + + + + + + + + select inner.* + from (select r.rel_id, acs_object.name(r.object_id_one) || ' and ' || acs_object.name(r.object_id_two) as name + from acs_rels r, acs_object_party_privilege_map perm, + app_group_distinct_rel_map m + where perm.object_id = r.rel_id + and perm.party_id = :user_id + and perm.privilege = 'read' + and r.rel_type = :rel_type + and m.rel_id = r.rel_id + and m.package_id = :package_id + order by lower(acs_object.name(r.object_id_one)), lower(acs_object.name(r.object_id_two))) inner + where rownum <= 26 + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/one-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/one-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/one-postgresql.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,45 @@ + + + + postgresql7.1 + + + + + select t1.pretty_name as object_type_one_pretty_name, + r.object_type_one, acs_rel_type__role_pretty_name(r.role_one) as role_one_pretty_name, + r.role_one, r.min_n_rels_one, r.max_n_rels_one, + t2.pretty_name as object_type_two_pretty_name, + r.object_type_two, acs_rel_type__role_pretty_name(r.role_two) as role_two_pretty_name, + r.role_two, r.min_n_rels_two, r.max_n_rels_two + from acs_rel_types r, acs_object_types t1, acs_object_types t2 + where r.rel_type = :rel_type + and r.object_type_one = t1.object_type + and r.object_type_two = t2.object_type + + + + + + + + FIX ME ROWNUM + + select inner.* + from (select r.rel_id, acs_object__name(r.object_id_one) || ' and ' || acs_object__name(r.object_id_two) as name + from acs_rels r, acs_object_party_privilege_map perm, + app_group_distinct_rel_map m + where perm.object_id = r.rel_id + and perm.party_id = :user_id + and perm.privilege = 'read' + and r.rel_type = :rel_type + and m.rel_id = r.rel_id + and m.package_id = :package_id + order by lower(acs_object__name(r.object_id_one)), lower(acs_object__name(r.object_id_two))) inner + where rownum <= 26 + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/one.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/one.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/one.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,26 @@ + + + + + + + select t.pretty_name as rel_type_pretty_name, t.table_name, t.id_column, t.dynamic_p + from acs_object_types t + where t.object_type = :rel_type + + + + + + + + + select a.attribute_id, a.pretty_name + from acs_attributes a + where a.object_type = :rel_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/rels-list-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/rels-list-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/rels-list-oracle.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,24 @@ + + + + oracle8.1.6 + + + + + select r.rel_id, acs_object.name(r.object_id_one) || ' and ' || acs_object.name(r.object_id_two) as name + from acs_rels r, acs_object_party_privilege_map perm, + app_group_distinct_rel_map m + where perm.object_id = r.rel_id + and perm.party_id = :user_id + and perm.privilege = 'read' + and r.rel_type = :rel_type + and m.rel_id = r.rel_id + and m.package_id = :package_id + order by lower(name) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/rels-list-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/rels-list-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/rels-list-postgresql.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,24 @@ + + + + postgresql7.1 + + + + + select r.rel_id, acs_object__name(r.object_id_one) || ' and ' || acs_object__name(r.object_id_two) as name + from acs_rels r, acs_object_party_privilege_map perm, + app_group_distinct_rel_map m + where perm.object_id = r.rel_id + and perm.party_id = :user_id + and perm.privilege = 'read' + and r.rel_type = :rel_type + and m.rel_id = r.rel_id + and m.package_id = :package_id + order by lower(name) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/rels-list.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/rels-list.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/rels-list.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + + select t.pretty_name as rel_type_pretty_name + from acs_object_types t + where t.object_type = :rel_type + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/roles/delete-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/roles/delete-2-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/roles/delete-2-oracle.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,16 @@ + + + + oracle8.1.6 + + + + + select case when exists (select 1 from acs_rel_types where role_one = :role or role_two = :role) then 1 else 0 end + from dual + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/roles/delete-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/roles/delete-2-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/roles/delete-2-postgresql.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,16 @@ + + + + postgresql7.1 + + + + + select case when exists (select 1 from acs_rel_types where role_one = :role or role_two = :role) then 1 else 0 end + + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/roles/delete.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/roles/delete.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/roles/delete.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + + select r.pretty_name from acs_rel_roles r where r.role = :role + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/roles/edit.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/roles/edit.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/roles/edit.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,27 @@ + + + + + + + select r.pretty_name, r.pretty_plural + from acs_rel_roles r + where r.role = :role + + + + + + + + + update acs_rel_roles r + set r.pretty_name = :pretty_name, + r.pretty_plural = :pretty_plural + where r.role = :role + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/roles/index-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/roles/Attic/index-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/roles/index-oracle.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,25 @@ + + + + oracle8.1.6 + + + + + select r.role, r.pretty_name, nvl(num1.number_rels,0) + nvl(num2.number_rels,0) as number_rel_types + from (select t.role_one as role, count(*) as number_rels + from acs_rel_types t + group by t.role_one) num1, + (select t.role_two as role, count(*) as number_rels + from acs_rel_types t + group by t.role_two) num2, + acs_rel_roles r + where r.role = num1.role(+) + and r.role = num2.role(+) + order by lower(r.role) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/roles/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/roles/index.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/roles/index.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,24 @@ + + + + + + FIX ME OUTER JOIN + + select r.role, r.pretty_name, coalesce(num1.number_rels,0) + coalesce(num2.number_rels,0) as number_rel_types + from (select t.role_one as role, count(*) as number_rels + from acs_rel_types t + group by t.role_one) num1, + (select t.role_two as role, count(*) as number_rels + from acs_rel_types t + group by t.role_two) num2, + acs_rel_roles r + where r.role = num1.role(+) + and r.role = num2.role(+) + order by lower(r.role) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/roles/new-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/roles/Attic/new-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/roles/new-oracle.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,15 @@ + + + + oracle8.1.6 + + + + + begin acs_rel_type.create_role(role => :role, pretty_name => :pretty_name, pretty_plural => :pretty_plural); end; + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/roles/new-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/roles/Attic/new-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/roles/new-postgresql.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,16 @@ + + + + postgresql7.1 + + + + FIX ME PLSQL + + begin acs_rel_type__create_role(role => :role, pretty_name => :pretty_name, pretty_plural => :pretty_plural); end; + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/roles/new.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/roles/new.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/roles/new.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + + select count(r.role) from acs_rel_roles r where r.role = :role + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/rel-types/roles/one.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/rel-types/roles/one.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/rel-types/roles/one.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,29 @@ + + + + + + + select r.pretty_name, r.pretty_plural + from acs_rel_roles r + where r.role = :role + + + + + + + + + select r.rel_type as role, t.pretty_name, r.rel_type, + case when r.role_one = :role then 'Side one' else 'Side two' end as side + from acs_object_types t, acs_rel_types r + where t.object_type = r.rel_type + and (r.role_one = :role or r.role_two = :role) + order by side, t.pretty_name + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/relations/add-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/relations/add-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/relations/add-oracle.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,67 @@ + + + + oracle8.1.6 + + + + + select t.object_type_two, t.role_two as role, + acs_rel_type.role_pretty_name(t.role_two) as role_pretty_name, + acs_object_type.pretty_name(t.object_type_two) as object_type_two_name, + ancestor_rel_types.object_type as ancestor_rel_type + from acs_rel_types t, acs_object_types obj_types, + acs_object_types ancestor_rel_types + where t.rel_type = :rel_type + and t.rel_type = obj_types.object_type + and ancestor_rel_types.supertype = 'relationship' + and ancestor_rel_types.object_type in ( + select object_type from acs_object_types + start with object_type = :rel_type + connect by object_type = prior supertype + ) + + + + + + + + + select DISTINCT + decode(groups.group_id, + null, case when persons.person_id = null then 'INVALID' else persons.first_names || ' ' || persons.last_name end, + groups.group_name) as party_name, + p.party_id + from (select o.object_id as party_id + from acs_objects o, + (select object_type from acs_object_types + start with $start_with + connect by prior object_type = supertype) t + where o.object_type = t.object_type) p, + (select element_id + from group_element_map + where group_id = :group_id and rel_type = :rel_type + UNION ALL + select to_number(:group_id) from dual) m, + (select object_id + from all_object_party_privilege_map + where party_id = :user_id and privilege = 'read') perm, + (select party_id + from rc_parties_in_required_segs + where group_id = :group_id + and rel_type = :rel_type) pirs $scope_query, + groups, + persons + where p.party_id = m.element_id(+) + and m.element_id is null + and p.party_id = perm.object_id + and p.party_id = pirs.party_id $scope_clause + and p.party_id = groups.group_id(+) + and p.party_id = persons.person_id(+) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/relations/add-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/relations/add-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/relations/add-postgresql.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,71 @@ + + + + postgresql7.1 + + + + FIX ME CONNECT BY + + select t.object_type_two, t.role_two as role, + acs_rel_type__role_pretty_name(t.role_two) as role_pretty_name, + acs_object_type__pretty_name(t.object_type_two) as object_type_two_name, + ancestor_rel_types.object_type as ancestor_rel_type + from acs_rel_types t, acs_object_types obj_types, + acs_object_types ancestor_rel_types + where t.rel_type = :rel_type + and t.rel_type = obj_types.object_type + and ancestor_rel_types.supertype = 'relationship' + and ancestor_rel_types.object_type in ( + select object_type from acs_object_types + start with object_type = :rel_type + connect by object_type = prior supertype + ) + + + + + + + + FIX ME DECODE (USE SQL92 CASE) +FIX ME OUTER JOIN +FIX ME CONNECT BY + + select DISTINCT + decode(groups.group_id, + null, case when persons.person_id = null then 'INVALID' else persons.first_names || ' ' || persons.last_name end, + groups.group_name) as party_name, + p.party_id + from (select o.object_id as party_id + from acs_objects o, + (select object_type from acs_object_types + start with $start_with + connect by prior object_type = supertype) t + where o.object_type = t.object_type) p, + (select element_id + from group_element_map + where group_id = :group_id and rel_type = :rel_type + UNION ALL + select to_number(:group_id) ) m, + (select object_id + from all_object_party_privilege_map + where party_id = :user_id and privilege = 'read') perm, + (select party_id + from rc_parties_in_required_segs + where group_id = :group_id + and rel_type = :rel_type) pirs $scope_query, + groups, + persons + where p.party_id = m.element_id(+) + and m.element_id is null + and p.party_id = perm.object_id + and p.party_id = pirs.party_id $scope_clause + and p.party_id = groups.group_id(+) + and p.party_id = persons.person_id(+) + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/relations/add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/relations/Attic/add.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/relations/add.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + + select group_name, join_policy + from groups + where group_id = :group_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/relations/change-member-state.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/relations/change-member-state.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/relations/change-member-state.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + + update membership_rels + set member_state = :member_state + where rel_id = :rel_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/relations/one-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/relations/Attic/one-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/relations/one-oracle.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,24 @@ + + + + oracle8.1.6 + + + + + select r.rel_type, acs_object_type.pretty_name(t.rel_type) as rel_type_pretty_name, + acs_rel_type.role_pretty_name(t.role_one) as role_one_pretty_name, + acs_rel_type.role_pretty_name(t.role_two) as role_two_pretty_name, + t.object_type_two as object_type_two, + acs_object.name(r.object_id_one) as object_id_one_name, + r.object_id_one, + acs_object.name(r.object_id_two) as object_id_two_name, + r.object_id_two + from acs_rels r, acs_rel_types t + where r.rel_id = :rel_id + and r.rel_type = t.rel_type + + + + + Index: openacs-4/packages/acs-subsite/www/admin/relations/one-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/relations/Attic/one-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/relations/one-postgresql.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,24 @@ + + + + postgresql7.1 + + + + + select r.rel_type, acs_object_type__pretty_name(t.rel_type) as rel_type_pretty_name, + acs_rel_type__role_pretty_name(t.role_one) as role_one_pretty_name, + acs_rel_type__role_pretty_name(t.role_two) as role_two_pretty_name, + t.object_type_two as object_type_two, + acs_object__name(r.object_id_one) as object_id_one_name, + r.object_id_one, + acs_object__name(r.object_id_two) as object_id_two_name, + r.object_id_two + from acs_rels r, acs_rel_types t + where r.rel_id = :rel_id + and r.rel_type = t.rel_type + + + + + Index: openacs-4/packages/acs-subsite/www/admin/relations/one.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/relations/one.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/relations/one.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,14 @@ + + + + + + + select member_state from membership_rels + where rel_id = :rel_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/relations/remove-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/relations/remove-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/relations/remove-oracle.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,33 @@ + + + + oracle8.1.6 + + + + + select acs_object_type.pretty_name(r.rel_type) as rel_type_pretty_name, + acs_object.name(r.object_id_one) as object_id_one_name, + acs_object.name(r.object_id_two) as object_id_two_name, + r.object_id_two + from acs_rels r + where r.rel_id = :rel_id + + + + + + + + select r.viol_rel_id as rel_id, + acs_object_type.pretty_name(r.viol_rel_type) as rel_type_pretty_name, + acs_object.name(r.viol_object_id_one) as object_id_one_name, + acs_object.name(r.viol_object_id_two) as object_id_two_name + from rc_violations_by_removing_rel r + where r.rel_id = :rel_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/relations/remove-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/relations/remove-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/relations/remove-postgresql.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,33 @@ + + + + postgresql7.1 + + + + + select acs_object_type__pretty_name(r.rel_type) as rel_type_pretty_name, + acs_object__name(r.object_id_one) as object_id_one_name, + acs_object__name(r.object_id_two) as object_id_two_name, + r.object_id_two + from acs_rels r + where r.rel_id = :rel_id + + + + + + + + select r.viol_rel_id as rel_id, + acs_object_type__pretty_name(r.viol_rel_type) as rel_type_pretty_name, + acs_object__name(r.viol_object_id_one) as object_id_one_name, + acs_object__name(r.viol_object_id_two) as object_id_two_name + from rc_violations_by_removing_rel r + where r.rel_id = :rel_id + + + + + + Index: openacs-4/packages/acs-subsite/www/admin/users/new-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/users/new-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/users/new-oracle.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,41 @@ + + + + oracle8.1.6 + + + + + select object_type as ancestor_rel_type + from acs_object_types + where supertype = 'relationship' + and object_type in ( + select object_type from acs_object_types + start with object_type = :add_with_rel_type + connect by object_type = prior supertype + ) + + + + + + + + + select case when exists + (select 1 from users where user_id = :user_id) + then 1 else 0 end + from dual + + + + + + + + select rowid from users where user_id = :user_id + + + + + Index: openacs-4/packages/acs-subsite/www/admin/users/new-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/users/new-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/users/new-postgresql.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,42 @@ + + + + postgresql7.1 + + + + FIX ME CONNECT BY + + select object_type as ancestor_rel_type + from acs_object_types + where supertype = 'relationship' + and object_type in ( + select object_type from acs_object_types + start with object_type = :add_with_rel_type + connect by object_type = prior supertype + ) + + + + + + + + + select case when exists + (select 1 from users where user_id = :user_id) + then 1 else 0 end + + + + + + + + + select rowid from users where user_id = :user_id + + + + + Index: openacs-4/packages/acs-subsite/www/admin/users/new.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/users/Attic/new.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/admin/users/new.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,40 @@ + + + + + + + select group_name as add_to_group_name, + join_policy as add_to_group_join_policy + from groups + where group_id = :add_to_group_id + + + + + + + + + select t.pretty_name as user_type_pretty_name, + t.table_name + from acs_object_types t + where t.object_type = :user_type + + + + + + + + + select p.first_names || ' ' || p.last_name + || ' (' || pa.email || ')' + from persons p, parties pa + where p.person_id = pa.party_id and p.person_id = :creation_user + + + + + + Index: openacs-4/packages/acs-subsite/www/pvt/alerts-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/pvt/Attic/alerts-oracle.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/www/pvt/alerts-oracle.xql 1 May 2001 19:00:18 -0000 1.1 +++ openacs-4/packages/acs-subsite/www/pvt/alerts-oracle.xql 15 May 2001 16:59:01 -0000 1.2 @@ -3,6 +3,18 @@ oracle8.1.6 + + + + select bea.valid_p, bea.frequency, bea.keywords, bt.topic, bea.rowid + from bboard_email_alerts bea, bboard_topics bt + where bea.user_id = :user_id + and bea.topic_id = bt.topic_id + order by bea.frequency + + + + Index: openacs-4/packages/acs-subsite/www/pvt/alerts-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/pvt/Attic/alerts-postgresql.xql,v diff -u -N -r1.2 -r1.3 --- openacs-4/packages/acs-subsite/www/pvt/alerts-postgresql.xql 1 May 2001 19:00:18 -0000 1.2 +++ openacs-4/packages/acs-subsite/www/pvt/alerts-postgresql.xql 15 May 2001 16:59:01 -0000 1.3 @@ -3,6 +3,18 @@ postgresql7.1 + + + + select bea.valid_p, bea.frequency, bea.keywords, bt.topic, bea.rowid + from bboard_email_alerts bea, bboard_topics bt + where bea.user_id = :user_id + and bea.topic_id = bt.topic_id + order by bea.frequency + + + + @@ -17,7 +29,7 @@ from classified_email_alerts cea, ad_domains ad where user_id = :user_id and ad.domain_id = cea.domain_id - and now() <= expires + and current_timestamp <= expires order by expires desc Index: openacs-4/packages/acs-subsite/www/pvt/alerts.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/pvt/Attic/alerts.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/www/pvt/alerts.xql 1 May 2001 19:00:18 -0000 1.1 +++ openacs-4/packages/acs-subsite/www/pvt/alerts.xql 15 May 2001 16:59:01 -0000 1.2 @@ -8,16 +8,4 @@ - - - - select bea.valid_p, bea.frequency, bea.keywords, bt.topic, bea.rowid - from bboard_email_alerts bea, bboard_topics bt - where bea.user_id = :user_id - and bea.topic_id = bt.topic_id - order by bea.frequency - - - - Index: openacs-4/packages/acs-subsite/www/pvt/home.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/pvt/Attic/home.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/www/pvt/home.xql 1 May 2001 19:00:18 -0000 1.1 +++ openacs-4/packages/acs-subsite/www/pvt/home.xql 15 May 2001 16:59:01 -0000 1.2 @@ -5,7 +5,7 @@ select first_names, last_name, email, url, - coalesce(screen_name,'< none set up >') as screen_name + coalesce(screen_name,'< none set up >') as screen_name from cc_users where user_id=:user_id Index: openacs-4/packages/acs-subsite/www/pvt/unsubscribe-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/pvt/Attic/unsubscribe-2-postgresql.xql,v diff -u -N -r1.2 -r1.3 --- openacs-4/packages/acs-subsite/www/pvt/unsubscribe-2-postgresql.xql 1 May 2001 19:00:18 -0000 1.2 +++ openacs-4/packages/acs-subsite/www/pvt/unsubscribe-2-postgresql.xql 15 May 2001 16:59:01 -0000 1.3 @@ -15,9 +15,11 @@ + FIX ME PLSQL - select membership_rel__deleted( :rel_id ); - +begin + membership_rel__deleted( rel_id => :rel_id ); +end; Index: openacs-4/packages/acs-subsite/www/register/awaiting-email-verification-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/register/Attic/awaiting-email-verification-postgresql.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/www/register/awaiting-email-verification-postgresql.xql 1 May 2001 21:20:18 -0000 1.1 +++ openacs-4/packages/acs-subsite/www/register/awaiting-email-verification-postgresql.xql 15 May 2001 16:59:01 -0000 1.2 @@ -6,7 +6,7 @@ - select member_state, email, rowid + select member_state, email, oid as rowid from cc_users where user_id = :user_id and email_verified_p = 'f' Index: openacs-4/packages/acs-subsite/www/register/user-new-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/register/Attic/user-new-2-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/register/user-new-2-oracle.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + select rowid from users where user_id = :user_id + + + + + Index: openacs-4/packages/acs-subsite/www/register/user-new-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/register/Attic/user-new-2-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-subsite/www/register/user-new-2-postgresql.xql 15 May 2001 16:59:01 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + select rowid from users where user_id = :user_id + + + + + Index: openacs-4/packages/acs-subsite/www/register/user-new-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/register/Attic/user-new-2.xql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/www/register/user-new-2.xql 1 May 2001 21:20:18 -0000 1.1 +++ openacs-4/packages/acs-subsite/www/register/user-new-2.xql 15 May 2001 16:59:01 -0000 1.2 @@ -8,11 +8,4 @@ - - - select rowid from users where user_id = :user_id - - - -