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
--- openacs-4/packages/acs-subsite/www/admin/relations/add-oracle.xql 20 Apr 2004 21:12:59 -0000 1.4
+++ /dev/null 1 Jan 1970 00:00:00 -0000
@@ -1,69 +0,0 @@
-
-
-
- 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 element_id from application_group_element_map
- where package_id = :package_id) app_elements
-
-
-
-
-
-
- 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 ot
- start with $start_with
- connect by prior ot.object_type = ot.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 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 = 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
--- openacs-4/packages/acs-subsite/www/admin/relations/add-postgresql.xql 20 Apr 2004 21:12:59 -0000 1.6
+++ /dev/null 1 Jan 1970 00:00:00 -0000
@@ -1,69 +0,0 @@
-
-
-
- postgresql7.1
-
-
-
- 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 t2.object_type from
- acs_object_types t1, acs_object_types t2
- where t1.object_type= :rel_type
- and t1.tree_sortkey between t2.tree_sortkey and tree_right(t2.tree_sortkey)
- )
-
-
-
-
-
-
-
-cross join (select element_id from application_group_element_map
- where package_id = :package_id) app_elements
-
-
-
-
-
- select DISTINCT
- case when groups.group_id is null then
- case when persons.person_id is null then 'INVALID'
- else persons.first_names || ' ' || persons.last_name
- end else
- groups.group_name end as party_name,
- p.party_id
- from (select o.object_id as party_id
- from acs_objects o,
- (select ot2.object_type from acs_object_types ot, acs_object_types ot2
- where ot2.tree_sortkey between ot.tree_sortkey and tree_right(ot.tree_sortkey)
- and $start_with) t
- where o.object_type = t.object_type) p left join
- (select element_id
- from group_element_map
- where group_id = :group_id and rel_type = :rel_type
- UNION ALL
- select :group_id::integer ) m on (p.party_id = m.element_id) cross join
- (select party_id
- from rc_parties_in_required_segs
- where group_id = :group_id
- and rel_type = :rel_type) pirs $scope_query left join
- groups on (p.party_id = groups.group_id)
- left join persons on (p.party_id = persons.person_id)
- where
- m.element_id is null
- and p.party_id = pirs.party_id $scope_clause
-
-
-
-
-
-
Index: openacs-4/packages/acs-subsite/www/admin/relations/add.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/relations/add.tcl,v
diff -u -N -r1.17.2.1 -r1.17.2.2
--- openacs-4/packages/acs-subsite/www/admin/relations/add.tcl 16 May 2019 09:54:29 -0000 1.17.2.1
+++ openacs-4/packages/acs-subsite/www/admin/relations/add.tcl 3 Jan 2020 15:07:36 -0000 1.17.2.2
@@ -50,12 +50,21 @@
group::get -group_id $group_id -array group_info
# We assume the group is on side 1...
-db_1row rel_type_info {}
+db_1row rel_type_info {
+ select t.object_type_two,
+ t.role_two as role,
+ (select pretty_name from acs_rel_roles
+ where role = t.role_two) as role_pretty_name,
+ (select pretty_name from acs_object_types
+ where object_type = t.object_type_two) as object_type_two_name
+ from acs_rel_types t
+ where rel_type = :rel_type
+}
# The role pretty names can be message catalog keys that need
# to be localized before they are displayed
set role_pretty_name [lang::util::localize $role_pretty_name]
-
+set exact_p true
if { $exact_p == "f"
&& [subsite::util::sub_type_exists_p $rel_type] } {
@@ -150,18 +159,13 @@
-widget "inform" -value "$party_name" -label "$role_pretty_name"
} else {
- if {$object_type_two eq "party"} {
- # We special case 'party' because we don't want to include
- # parties whose direct object_type is:
- # 'rel_segment' - users will get confused by segments here.
- # 'party' - this is an abstract type and should have no objects,
- # but the system creates party -1 which users
- # shouldn't see.
- set start_with "ot.object_type = 'group' or ot.object_type = 'person'"
- } else {
- set start_with "ot.object_type = :object_type_two"
- }
+ # We special case 'party' because we don't want to include
+ # parties whose direct object_type is:
+ # 'rel_segment' - users will get confused by segments here.
+ # 'party' - this is an abstract type and should have no objects,
+ # but the system creates party -1 which users
+ # shouldn't see.
# The $allow_out_of_scope_p flag controls whether or not we limit
# the list of parties to those that belong to the current subsite
@@ -170,23 +174,54 @@
# the list of parties that can be added to $group_id with a relation
# of type $rel_type.
- if {$allow_out_of_scope_p == "f"} {
- set scope_query [db_map select_parties_scope_query]
-
- set scope_clause "
- and p.party_id = app_elements.element_id"
-
- } else {
- set scope_query ""
- set scope_clause ""
- }
-
# SENSITIVE PERFORMANCE - this comment tag is here to make it
# easy for us to find all the queries that we know may be unscalable.
# This query has been tuned as well as possible given development
# time constraints, but more tuning may be necessary.
- set party_option_list [db_list_of_lists select_parties {}]
+ set party_option_list [db_list_of_lists select_parties {
+ with recursive subtypes as (
+ select object_type
+ from acs_object_types
+ where (object_type = :object_type_two
+ and :object_type_two <> 'party') or
+ (object_type in ('person', 'group')
+ and :object_type_two = 'party')
+ union all
+
+ select t.object_type
+ from acs_object_types t,
+ subtypes s
+ where t.supertype = s.object_type
+ )
+ select DISTINCT
+ case when groups.group_id is null then
+ case when persons.person_id is null then 'INVALID'
+ else persons.first_names || ' ' || persons.last_name end
+ else groups.group_name end as party_name,
+ p.object_id as party_id
+ from acs_objects p
+ left join groups on groups.group_id = p.object_id
+ left join persons on persons.person_id = p.object_id,
+ subtypes s,
+ rc_parties_in_required_segs pirs
+ where p.object_type = s.object_type
+ -- do not list the group as a possible member
+ and p.object_id <> :group_id
+ -- do not list parties that are already members
+ and not exists (select 1 from group_element_map
+ where element_id = p.object_id
+ and group_id = :group_id
+ and rel_type = :rel_type)
+ and pirs.rel_type = :rel_type
+ and pirs.group_id = :group_id
+ and pirs.party_id = p.object_id
+ and (:allow_out_of_scope_p <> 'f' or
+ exists (select 1 from application_group_element_map
+ where package_id = :package_id
+ and element_id = p.object_id))
+ }]
+
if { [llength $party_option_list] == 0 } {
ad_return_template add-no-valid-parties
return