You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafficcontrol.apache.org by de...@apache.org on 2017/03/22 16:55:03 UTC
[4/4] incubator-trafficcontrol git commit: Switch stats endpoint to
raw sql query for performance.
Switch stats endpoint to raw sql query for performance.
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/commit/d0c08621
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/tree/d0c08621
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/diff/d0c08621
Branch: refs/heads/master
Commit: d0c086211120c4f409143957e9de1494d75392dc
Parents: c0de68c
Author: Chris Lemmons <al...@gmail.com>
Authored: Tue Mar 21 16:51:33 2017 -0600
Committer: Dewayne Richardson <de...@apache.org>
Committed: Wed Mar 22 10:54:54 2017 -0600
----------------------------------------------------------------------
traffic_ops/app/lib/UI/Server.pm | 184 ++++++++++++++++++++++++++--------
1 file changed, 143 insertions(+), 41 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/d0c08621/traffic_ops/app/lib/UI/Server.pm
----------------------------------------------------------------------
diff --git a/traffic_ops/app/lib/UI/Server.pm b/traffic_ops/app/lib/UI/Server.pm
index 8477162..3e13179 100644
--- a/traffic_ops/app/lib/UI/Server.pm
+++ b/traffic_ops/app/lib/UI/Server.pm
@@ -110,57 +110,159 @@ sub getserverdata {
my @data;
my $orderby = "host_name";
$orderby = $self->param('orderby') if ( defined $self->param('orderby') );
- my $rs_data = $self->db->resultset('Server')->search(
- undef, {
- prefetch => [ 'cdn', 'cachegroup', 'type', 'profile', 'status', 'phys_location' ],
- order_by => 'me.' . $orderby,
- }
+ my $dbh = $self->db->storage->dbh;
+ $orderby = $dbh->quote_identifier($orderby);
+ my $qry = 'SELECT
+cdn.name,
+sv.id,
+sv.host_name,
+sv.domain_name,
+sv.tcp_port,
+sv.https_port,
+sv.xmpp_id,
+sv.interface_name,
+sv.ip_address,
+sv.ip_netmask,
+sv.ip_gateway,
+sv.ip6_address,
+sv.ip6_gateway,
+sv.interface_mtu,
+cg.name,
+pl.name,
+sv.guid,
+sv.rack,
+tp.name,
+st.name,
+sv.offline_reason,
+pf.name,
+sv.mgmt_ip_address,
+sv.mgmt_ip_netmask,
+sv.mgmt_ip_gateway,
+sv.ilo_ip_address,
+sv.ilo_ip_netmask,
+sv.ilo_ip_gateway,
+sv.ilo_username,
+sv.router_host_name,
+sv.router_port_name,
+sv.last_updated
+FROM server sv
+LEFT JOIN cdn cdn ON cdn.id = sv.cdn_id
+LEFT JOIN type tp ON tp.id = sv.type
+LEFT JOIN status st ON st.id = sv.status
+LEFT JOIN cachegroup cg ON cg.id = sv.cachegroup
+LEFT JOIN profile pf ON pf.id = sv.profile
+LEFT JOIN phys_location pl ON pl.id = sv.phys_location
+ORDER BY sv.'.$orderby.';';
+ my $stmt = $dbh->prepare($qry);
+ $stmt->execute();
+
+ my $cdn_name;
+ my $sv_id;
+ my $sv_host_name;
+ my $sv_domain_name;
+ my $sv_tcp_port;
+ my $sv_https_port;
+ my $sv_xmpp_id;
+ my $sv_interface_name;
+ my $sv_ip_address;
+ my $sv_ip_netmask;
+ my $sv_ip_gateway;
+ my $sv_ip6_address;
+ my $sv_ip6_gateway;
+ my $sv_interface_mtu;
+ my $cg_name;
+ my $pl_name;
+ my $sv_guid;
+ my $sv_rack;
+ my $tp_name;
+ my $st_name;
+ my $sv_offline_reason;
+ my $pf_name;
+ my $sv_mgmt_ip_address;
+ my $sv_mgmt_ip_netmask;
+ my $sv_mgmt_ip_gateway;
+ my $sv_ilo_ip_address;
+ my $sv_ilo_ip_netmask;
+ my $sv_ilo_ip_gateway;
+ my $sv_ilo_username;
+ my $sv_router_host_name;
+ my $sv_router_port_name;
+ my $sv_last_updated;
+ $stmt->bind_columns(
+ \$cdn_name,
+ \$sv_id,
+ \$sv_host_name,
+ \$sv_domain_name,
+ \$sv_tcp_port,
+ \$sv_https_port,
+ \$sv_xmpp_id,
+ \$sv_interface_name,
+ \$sv_ip_address,
+ \$sv_ip_netmask,
+ \$sv_ip_gateway,
+ \$sv_ip6_address,
+ \$sv_ip6_gateway,
+ \$sv_interface_mtu,
+ \$cg_name,
+ \$pl_name,
+ \$sv_guid,
+ \$sv_rack,
+ \$tp_name,
+ \$st_name,
+ \$sv_offline_reason,
+ \$pf_name,
+ \$sv_mgmt_ip_address,
+ \$sv_mgmt_ip_netmask,
+ \$sv_mgmt_ip_gateway,
+ \$sv_ilo_ip_address,
+ \$sv_ilo_ip_netmask,
+ \$sv_ilo_ip_gateway,
+ \$sv_ilo_username,
+ \$sv_router_host_name,
+ \$sv_router_port_name,
+ \$sv_last_updated
);
- while ( my $row = $rs_data->next ) {
- my $cdn_name = defined( $row->cdn_id ) ? $row->cdn->name : "";
+ while ( my $row = $stmt->fetch() ) {
push(
@data, {
- "id" => $row->id,
- "host_name" => $row->host_name,
- "domain_name" => $row->domain_name,
- "tcp_port" => $row->tcp_port,
- "https_port" => $row->https_port,
- "xmpp_id" => $row->xmpp_id,
+ "id" => $sv_id,
+ "host_name" => $sv_host_name,
+ "domain_name" => $sv_domain_name,
+ "tcp_port" => $sv_tcp_port,
+ "https_port" => $sv_https_port,
+ "xmpp_id" => $sv_xmpp_id,
"xmpp_passwd" => "**********",
- "interface_name" => $row->interface_name,
- "ip_address" => $row->ip_address,
- "ip_netmask" => $row->ip_netmask,
- "ip_gateway" => $row->ip_gateway,
- "ip6_address" => $row->ip6_address,
- "ip6_gateway" => $row->ip6_gateway,
- "interface_mtu" => $row->interface_mtu,
+ "interface_name" => $sv_interface_name,
+ "ip_address" => $sv_ip_address,
+ "ip_netmask" => $sv_ip_netmask,
+ "ip_gateway" => $sv_ip_gateway,
+ "ip6_address" => $sv_ip6_address,
+ "ip6_gateway" => $sv_ip6_gateway,
+ "interface_mtu" => $sv_interface_mtu,
"cdn" => $cdn_name,
- "cachegroup" => $row->cachegroup->name,
- "phys_location" => $row->phys_location->name,
- "guid" => $row->guid,
- "rack" => $row->rack,
- "type" => $row->type->name,
- "status" => $row->status->name,
- "offline_reason" => $row->offline_reason,
- "profile" => $row->profile->name,
- "mgmt_ip_address" => $row->mgmt_ip_address,
- "mgmt_ip_netmask" => $row->mgmt_ip_netmask,
- "mgmt_ip_gateway" => $row->mgmt_ip_gateway,
- "ilo_ip_address" => $row->ilo_ip_address,
- "ilo_ip_netmask" => $row->ilo_ip_netmask,
- "ilo_ip_gateway" => $row->ilo_ip_gateway,
- "ilo_username" => $row->ilo_username,
+ "cachegroup" => $cg_name,
+ "phys_location" => $pl_name,
+ "guid" => $sv_guid,
+ "rack" => $sv_rack,
+ "type" => $tp_name,
+ "status" => $st_name,
+ "offline_reason" => $sv_offline_reason,
+ "profile" => $pf_name,
+ "mgmt_ip_address" => $sv_mgmt_ip_address,
+ "mgmt_ip_netmask" => $sv_mgmt_ip_netmask,
+ "mgmt_ip_gateway" => $sv_mgmt_ip_gateway,
+ "ilo_ip_address" => $sv_ilo_ip_address,
+ "ilo_ip_netmask" => $sv_ilo_ip_netmask,
+ "ilo_ip_gateway" => $sv_ilo_ip_gateway,
+ "ilo_username" => $sv_ilo_username,
"ilo_password" => "**********",
- "router_host_name" => $row->router_host_name,
- "router_port_name" => $row->router_port_name,
- "last_updated" => $row->last_updated,
-
+ "router_host_name" => $sv_router_host_name,
+ "router_port_name" => $sv_router_port_name,
+ "last_updated" => $sv_last_updated,
}
-
);
}
-
return ( \@data );
}