rob05c commented on a change in pull request #4901:
URL: https://github.com/apache/trafficcontrol/pull/4901#discussion_r460169388
##########
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 (
Review comment:
This SQL is pretty complex, it could definitely use some comments
##########
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'
Review comment:
Is this still correct? Are there cases it wouldn't be? E.g. if a
Topology has servers EDGE->MID->MID ?
##########
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)
Review comment:
panic, `log.Errorf`
##########
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:
Nitpicks: Complex SQL could use comments, and magic strings would be
better as constants (OFFLINE, use_reval_pending, global)
##########
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
Review comment:
GoDoc error, needs to be a complete sentence, trailing period.
##########
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.
Review comment:
GoDoc, `getServerUpdateStatusV2 is a special case believed to be used
nowhere.`
##########
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:
As above, is this correct?
##########
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 {
Review comment:
Nitpick: Go encourages short variable names in small scopes. Something
like `us := tc.ServerUpdateStatus{}` would make this much easier to read IMO
##########
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")
Review comment:
Should this have context like the function name?
##########
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)
Review comment:
Should be `log.Errorf`. The underlying `log.Error.Printf` should never
be used, because it will panic if `log.Error` is nil.
##########
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:
Nitpick: no reason to predeclare these, can be `rows, err := tx.Query`
below. Safer to never have them be invalid values
----------------------------------------------------------------
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]
