zrhoffman commented on a change in pull request #4901:
URL: https://github.com/apache/trafficcontrol/pull/4901#discussion_r463158681
##########
File path: traffic_ops/traffic_ops_golang/server/servers_update_status.go
##########
@@ -46,18 +46,148 @@ func GetServerUpdateStatusHandler(w http.ResponseWriter, r
*http.Request) {
}
func getServerUpdateStatus(tx *sql.Tx, cfg *config.Config, hostName string)
([]tc.ServerUpdateStatus, error) {
- baseSelectStatement :=
- `WITH parentservers AS (SELECT ps.id, ps.cachegroup, ps.cdn_id,
ps.upd_pending, ps.reval_pending FROM server ps
- LEFT JOIN status AS pstatus ON pstatus.id = ps.status
- WHERE pstatus.name != 'OFFLINE' ),
- use_reval_pending AS (SELECT value::boolean FROM parameter WHERE name
= 'use_reval_pending' AND config_file = 'global' UNION ALL SELECT FALSE FETCH
FIRST 1 ROW ONLY)
- SELECT s.id, s.host_name, type.name AS type,
(s.reval_pending::boolean) as server_reval_pending, use_reval_pending.value,
s.upd_pending, status.name AS status, COALESCE(bool_or(ps.upd_pending), FALSE)
AS parent_upd_pending, COALESCE(bool_or(ps.reval_pending), FALSE) AS
parent_reval_pending FROM use_reval_pending, server s
- LEFT JOIN status ON s.status = status.id
- LEFT JOIN cachegroup cg ON s.cachegroup = cg.id
- LEFT JOIN type ON type.id = s.type
- LEFT JOIN parentservers ps ON ps.cachegroup = cg.parent_cachegroup_id
AND ps.cdn_id = s.cdn_id AND type.name = 'EDGE'` //remove the EDGE reference if
other server types should have their parents processed
-
- groupBy := ` GROUP BY s.id, s.host_name, type.name,
server_reval_pending, use_reval_pending.value, s.upd_pending, status.name ORDER
BY s.id;`
+
+ updateStatuses := []tc.ServerUpdateStatus{}
+ var rows *sql.Rows
+ var err error
+
+ selectQuery := `
+WITH RECURSIVE topology_ancestors AS (
+ SELECT tcp.child parent, tc.cachegroup
+ FROM "server" s
+ JOIN cachegroup c ON s.cachegroup = c.id
+ JOIN topology_cachegroup tc ON c."name" = tc.cachegroup
+ JOIN topology_cachegroup_parents tcp ON tc.id = tcp.child
+ WHERE s.host_name = $1
+UNION ALL
+ SELECT tcp.parent, tc.cachegroup
+ FROM topology_ancestors ta, topology_cachegroup_parents tcp
+ JOIN topology_cachegroup tc ON tcp.parent = tc.id
+ WHERE ta.parent = tcp.child
+), server_topology_ancestors AS (
+SELECT s.id, s.cachegroup, s.cdn_id, s.upd_pending, s.reval_pending, s.status
+FROM server s
+JOIN cachegroup c ON s.cachegroup = c.id
+JOIN topology_ancestors ta ON c."name" = ta.cachegroup
+WHERE s.host_name != $1
+), parentservers AS (
+ SELECT ps.id, ps.cachegroup, ps.cdn_id, ps.upd_pending,
ps.reval_pending, ps.status
+ FROM server ps
+ LEFT JOIN status AS pstatus ON pstatus.id = ps.status
+ WHERE pstatus.name != 'OFFLINE'
+), use_reval_pending AS (
+ SELECT value::BOOLEAN
+ FROM parameter
+ WHERE name = 'use_reval_pending'
+ AND config_file = 'global'
+ UNION ALL SELECT FALSE FETCH FIRST 1 ROW ONLY
+)
+SELECT
+ s.id,
+ s.host_name,
+ type.name AS type,
+ (s.reval_pending::BOOLEAN) AS server_reval_pending,
+ use_reval_pending.value,
+ s.upd_pending,
+ status.name AS status,
+ COALESCE(BOOL_OR(ps.upd_pending),
+ TRUE IN (
+ SELECT sta.upd_pending FROM server_topology_ancestors
sta)
+ ) AS parent_upd_pending,
+ COALESCE(BOOL_OR(ps.reval_pending),
+ TRUE IN (
+ SELECT sta.reval_pending FROM
server_topology_ancestors sta)
+ ) AS parent_reval_pending
+ FROM use_reval_pending,
+ server s
+LEFT JOIN status ON s.status = status.id
+LEFT JOIN cachegroup cg ON s.cachegroup = cg.id
+LEFT JOIN type ON type.id = s.type
+LEFT JOIN parentservers ps ON ps.cachegroup = cg.parent_cachegroup_id
+ AND ps.cdn_id = s.cdn_id
+ AND type.name = 'EDGE'
+WHERE s.host_name = $1
+GROUP BY s.id, s.host_name, type.name, server_reval_pending,
use_reval_pending.value, s.upd_pending, status.name
+ORDER BY s.id
+` // remove the type.name = 'EDGE' condition if other server types should have
their parents processed
+
+ rows, err = tx.Query(selectQuery, hostName)
+ if err != nil {
+ log.Error.Printf("could not execute query: %s\n", err)
+ return nil, tc.DBError
+ }
+ defer log.Close(rows, "unable to close db connection")
+
+ for rows.Next() {
+ var serverUpdateStatus tc.ServerUpdateStatus
+ var serverType string
+ if err := rows.Scan(&serverUpdateStatus.HostId,
&serverUpdateStatus.HostName, &serverType, &serverUpdateStatus.RevalPending,
&serverUpdateStatus.UseRevalPending, &serverUpdateStatus.UpdatePending,
&serverUpdateStatus.Status, &serverUpdateStatus.ParentPending,
&serverUpdateStatus.ParentRevalPending); err != nil {
+ log.Error.Printf("could not scan server update status:
%s\n", err)
+ return nil, tc.DBError
+ }
+ updateStatuses = append(updateStatuses, serverUpdateStatus)
+ }
+ return updateStatuses, nil
+}
+
+func GetServerUpdateStatusHandlerV2(w http.ResponseWriter, r *http.Request) {
+ inf, userErr, sysErr, errCode := api.NewInfo(r, []string{"host_name"},
nil)
+ if userErr != nil || sysErr != nil {
+ api.HandleErr(w, r, inf.Tx.Tx, errCode, userErr, sysErr)
+ return
+ }
+ defer inf.Close()
+
+ serverUpdateStatus, err := getServerUpdateStatusV2(inf.Tx.Tx,
inf.Config, inf.Params["host_name"])
+ if err != nil {
+ api.HandleErr(w, r, inf.Tx.Tx, http.StatusInternalServerError,
nil, err)
+ return
+ }
+ api.WriteRespRaw(w, r, serverUpdateStatus)
+}
+
+// getServerUpdateStatusV2 supports /servers/all/update_status in addition to
/servers/{host_name}/update_status
+// This special case is believed to be used nowhere.
+func getServerUpdateStatusV2(tx *sql.Tx, cfg *config.Config, hostName string)
([]tc.ServerUpdateStatus, error) {
+ // language=SQL
+ baseSelectStatement := `
+WITH parentservers AS (
+ SELECT ps.id, ps.cachegroup, ps.cdn_id, ps.upd_pending, ps.reval_pending
+ FROM server ps
+ LEFT JOIN status AS pstatus ON pstatus.id = ps.status
+ WHERE pstatus.name != 'OFFLINE'
+), use_reval_pending AS (
+ SELECT value::BOOLEAN
+ FROM parameter
+ WHERE name = 'use_reval_pending'
+ AND config_file = 'global'
+ UNION ALL SELECT FALSE FETCH FIRST 1 ROW ONLY
+)
+SELECT
+ s.id,
+ s.host_name,
+ type.name AS type,
+ (s.reval_pending::BOOLEAN) AS server_reval_pending,
+ use_reval_pending.value,
+ s.upd_pending,
+ status.name AS status,
+ COALESCE(BOOL_OR(ps.upd_pending), FALSE) AS parent_upd_pending,
+ COALESCE(BOOL_OR(ps.reval_pending), FALSE) AS parent_reval_pending
+ FROM use_reval_pending,
+ server s
+LEFT JOIN status ON s.status = status.id
+LEFT JOIN cachegroup cg ON s.cachegroup = cg.id
+LEFT JOIN type ON type.id = s.type
+LEFT JOIN parentservers ps ON ps.cachegroup = cg.parent_cachegroup_id
+ AND ps.cdn_id = s.cdn_id
+ AND type.name = 'EDGE'
Review comment:
It is for API version 2. IMO we should only let mids be child
cachegroups in API version 3 and up and preserve the behavior of deprecated
handlers.
##########
File path: traffic_ops/traffic_ops_golang/server/servers_update_status.go
##########
@@ -46,18 +46,148 @@ func GetServerUpdateStatusHandler(w http.ResponseWriter, r
*http.Request) {
}
func getServerUpdateStatus(tx *sql.Tx, cfg *config.Config, hostName string)
([]tc.ServerUpdateStatus, error) {
- baseSelectStatement :=
- `WITH parentservers AS (SELECT ps.id, ps.cachegroup, ps.cdn_id,
ps.upd_pending, ps.reval_pending FROM server ps
- LEFT JOIN status AS pstatus ON pstatus.id = ps.status
- WHERE pstatus.name != 'OFFLINE' ),
- use_reval_pending AS (SELECT value::boolean FROM parameter WHERE name
= 'use_reval_pending' AND config_file = 'global' UNION ALL SELECT FALSE FETCH
FIRST 1 ROW ONLY)
- SELECT s.id, s.host_name, type.name AS type,
(s.reval_pending::boolean) as server_reval_pending, use_reval_pending.value,
s.upd_pending, status.name AS status, COALESCE(bool_or(ps.upd_pending), FALSE)
AS parent_upd_pending, COALESCE(bool_or(ps.reval_pending), FALSE) AS
parent_reval_pending FROM use_reval_pending, server s
- LEFT JOIN status ON s.status = status.id
- LEFT JOIN cachegroup cg ON s.cachegroup = cg.id
- LEFT JOIN type ON type.id = s.type
- LEFT JOIN parentservers ps ON ps.cachegroup = cg.parent_cachegroup_id
AND ps.cdn_id = s.cdn_id AND type.name = 'EDGE'` //remove the EDGE reference if
other server types should have their parents processed
-
- groupBy := ` GROUP BY s.id, s.host_name, type.name,
server_reval_pending, use_reval_pending.value, s.upd_pending, status.name ORDER
BY s.id;`
+
+ updateStatuses := []tc.ServerUpdateStatus{}
+ var rows *sql.Rows
+ var err error
+
+ selectQuery := `
+WITH RECURSIVE topology_ancestors AS (
+ SELECT tcp.child parent, tc.cachegroup
+ FROM "server" s
+ JOIN cachegroup c ON s.cachegroup = c.id
+ JOIN topology_cachegroup tc ON c."name" = tc.cachegroup
+ JOIN topology_cachegroup_parents tcp ON tc.id = tcp.child
+ WHERE s.host_name = $1
+UNION ALL
+ SELECT tcp.parent, tc.cachegroup
+ FROM topology_ancestors ta, topology_cachegroup_parents tcp
+ JOIN topology_cachegroup tc ON tcp.parent = tc.id
+ WHERE ta.parent = tcp.child
+), server_topology_ancestors AS (
+SELECT s.id, s.cachegroup, s.cdn_id, s.upd_pending, s.reval_pending, s.status
+FROM server s
+JOIN cachegroup c ON s.cachegroup = c.id
+JOIN topology_ancestors ta ON c."name" = ta.cachegroup
+WHERE s.host_name != $1
+), parentservers AS (
+ SELECT ps.id, ps.cachegroup, ps.cdn_id, ps.upd_pending,
ps.reval_pending, ps.status
+ FROM server ps
+ LEFT JOIN status AS pstatus ON pstatus.id = ps.status
+ WHERE pstatus.name != 'OFFLINE'
+), use_reval_pending AS (
+ SELECT value::BOOLEAN
+ FROM parameter
+ WHERE name = 'use_reval_pending'
+ AND config_file = 'global'
+ UNION ALL SELECT FALSE FETCH FIRST 1 ROW ONLY
+)
+SELECT
+ s.id,
+ s.host_name,
+ type.name AS type,
+ (s.reval_pending::BOOLEAN) AS server_reval_pending,
+ use_reval_pending.value,
+ s.upd_pending,
+ status.name AS status,
+ COALESCE(BOOL_OR(ps.upd_pending),
+ TRUE IN (
+ SELECT sta.upd_pending FROM server_topology_ancestors
sta)
+ ) AS parent_upd_pending,
+ COALESCE(BOOL_OR(ps.reval_pending),
+ TRUE IN (
+ SELECT sta.reval_pending FROM
server_topology_ancestors sta)
+ ) AS parent_reval_pending
+ FROM use_reval_pending,
+ server s
+LEFT JOIN status ON s.status = status.id
+LEFT JOIN cachegroup cg ON s.cachegroup = cg.id
+LEFT JOIN type ON type.id = s.type
+LEFT JOIN parentservers ps ON ps.cachegroup = cg.parent_cachegroup_id
+ AND ps.cdn_id = s.cdn_id
+ AND type.name = 'EDGE'
+WHERE s.host_name = $1
+GROUP BY s.id, s.host_name, type.name, server_reval_pending,
use_reval_pending.value, s.upd_pending, status.name
+ORDER BY s.id
+` // remove the type.name = 'EDGE' condition if other server types should have
their parents processed
+
+ rows, err = tx.Query(selectQuery, hostName)
+ if err != nil {
+ log.Error.Printf("could not execute query: %s\n", err)
+ return nil, tc.DBError
+ }
+ defer log.Close(rows, "unable to close db connection")
+
+ for rows.Next() {
+ var serverUpdateStatus tc.ServerUpdateStatus
+ var serverType string
+ if err := rows.Scan(&serverUpdateStatus.HostId,
&serverUpdateStatus.HostName, &serverType, &serverUpdateStatus.RevalPending,
&serverUpdateStatus.UseRevalPending, &serverUpdateStatus.UpdatePending,
&serverUpdateStatus.Status, &serverUpdateStatus.ParentPending,
&serverUpdateStatus.ParentRevalPending); err != nil {
+ log.Error.Printf("could not scan server update status:
%s\n", err)
+ return nil, tc.DBError
+ }
+ updateStatuses = append(updateStatuses, serverUpdateStatus)
+ }
+ return updateStatuses, nil
+}
+
+func GetServerUpdateStatusHandlerV2(w http.ResponseWriter, r *http.Request) {
+ inf, userErr, sysErr, errCode := api.NewInfo(r, []string{"host_name"},
nil)
+ if userErr != nil || sysErr != nil {
+ api.HandleErr(w, r, inf.Tx.Tx, errCode, userErr, sysErr)
+ return
+ }
+ defer inf.Close()
+
+ serverUpdateStatus, err := getServerUpdateStatusV2(inf.Tx.Tx,
inf.Config, inf.Params["host_name"])
+ if err != nil {
+ api.HandleErr(w, r, inf.Tx.Tx, http.StatusInternalServerError,
nil, err)
+ return
+ }
+ api.WriteRespRaw(w, r, serverUpdateStatus)
+}
+
+// getServerUpdateStatusV2 supports /servers/all/update_status in addition to
/servers/{host_name}/update_status
+// This special case is believed to be used nowhere.
+func getServerUpdateStatusV2(tx *sql.Tx, cfg *config.Config, hostName string)
([]tc.ServerUpdateStatus, error) {
+ // language=SQL
+ baseSelectStatement := `
Review comment:
>Complex SQL could use comments
* Commented query additions in f97f121267, this one is the legacy query.
> magic strings would be better as constants (OFFLINE, use_reval_pending,
global)
* Using `tc.CacheStatusOffline` instead of 'OFFLINE' in 5ef79b21cb
* Using `tc.UseRevalPendingParameterName` instead of `'use_reval_pending'`
ccb41948c2
* Use `tc.GlobalConfigFileName` instead of hard-coded `'global'` in
b1e912424d
##########
File path: traffic_ops/traffic_ops_golang/server/servers_update_status.go
##########
@@ -46,18 +46,148 @@ func GetServerUpdateStatusHandler(w http.ResponseWriter, r
*http.Request) {
}
func getServerUpdateStatus(tx *sql.Tx, cfg *config.Config, hostName string)
([]tc.ServerUpdateStatus, error) {
- baseSelectStatement :=
- `WITH parentservers AS (SELECT ps.id, ps.cachegroup, ps.cdn_id,
ps.upd_pending, ps.reval_pending FROM server ps
- LEFT JOIN status AS pstatus ON pstatus.id = ps.status
- WHERE pstatus.name != 'OFFLINE' ),
- use_reval_pending AS (SELECT value::boolean FROM parameter WHERE name
= 'use_reval_pending' AND config_file = 'global' UNION ALL SELECT FALSE FETCH
FIRST 1 ROW ONLY)
- SELECT s.id, s.host_name, type.name AS type,
(s.reval_pending::boolean) as server_reval_pending, use_reval_pending.value,
s.upd_pending, status.name AS status, COALESCE(bool_or(ps.upd_pending), FALSE)
AS parent_upd_pending, COALESCE(bool_or(ps.reval_pending), FALSE) AS
parent_reval_pending FROM use_reval_pending, server s
- LEFT JOIN status ON s.status = status.id
- LEFT JOIN cachegroup cg ON s.cachegroup = cg.id
- LEFT JOIN type ON type.id = s.type
- LEFT JOIN parentservers ps ON ps.cachegroup = cg.parent_cachegroup_id
AND ps.cdn_id = s.cdn_id AND type.name = 'EDGE'` //remove the EDGE reference if
other server types should have their parents processed
-
- groupBy := ` GROUP BY s.id, s.host_name, type.name,
server_reval_pending, use_reval_pending.value, s.upd_pending, status.name ORDER
BY s.id;`
+
+ updateStatuses := []tc.ServerUpdateStatus{}
+ var rows *sql.Rows
+ var err error
Review comment:
Removed predeclaration in b8ddc95d5a
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
