dangogh commented on a change in pull request #3163: Fix Traffic Ops Tenancy
and Activity Bugs, Fix TO API Test Framework to work with Tenancy
URL: https://github.com/apache/trafficcontrol/pull/3163#discussion_r244387065
##########
File path: traffic_ops/traffic_ops_golang/tenant/tenancy.go
##########
@@ -136,11 +136,28 @@ func GetUserTenantListTx(user auth.CurrentUser, tx
*sql.Tx) ([]tc.TenantNullable
return tenants, nil
}
+// GetUserTenantIDListTx returns a list of tenant IDs accessible to the given
tenant.
+// Note: If the given tenant or any of its parents are inactive, no IDs will
be returned. If child tenants are needed even if the current tenant is
inactive, use GetUserTenantListTx instead.
func GetUserTenantIDListTx(tx *sql.Tx, userTenantID int) ([]int, error) {
query := `
-WITH RECURSIVE q AS (SELECT id, name, active, parent_id FROM tenant WHERE id =
$1
-UNION SELECT t.id, t.name, t.active, t.parent_id FROM tenant t JOIN q ON q.id
= t.parent_id)
-SELECT id FROM q;
+WITH RECURSIVE
+user_tenant_id as (select $1::bigint as v),
+user_tenant_parents AS (
+ SELECT active, parent_id FROM tenant WHERE id = (select v from
user_tenant_id)
+ UNION
+ SELECT t.active, t.parent_id FROM TENANT t JOIN user_tenant_parents ON
user_tenant_parents.parent_id = t.id
+),
+user_tenant_active AS (
+ SELECT bool_and(active) as v FROM user_tenant_parents
+),
+user_tenant_children AS (
+ SELECT id, name, active, parent_id
+ FROM tenant WHERE id = (SELECT v FROM user_tenant_id) AND (SELECT v FROM
user_tenant_active)
+ UNION
+ SELECT t.id, t.name, t.active, t.parent_id
+ FROM tenant t JOIN user_tenant_children ON user_tenant_children.id =
t.parent_id
+)
+SELECT id FROM user_tenant_children;
Review comment:
+1 -- this is much clearer, although still pretty hairy. I wonder if it
would be worth creating this as an SQL function that can be tested/maintained
outside of the code?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
With regards,
Apache Git Services
