1""" 2Dashboard for the qual page 3""" 4from sqlalchemy.sql import text, bindparam 5from tornado.web import HTTPError 6from powa.dashboards import ( 7 Dashboard, Graph, Grid, 8 MetricGroupDef, MetricDef, 9 DashboardPage, ContentWidget) 10from powa.sql import qual_constants, resolve_quals 11from powa.sql.utils import inner_cc 12from powa.query import QueryOverview 13from powa.sql.views import qualstat_getstatdata 14 15 16class QualConstantsMetricGroup(MetricGroupDef): 17 """ 18 Metric group used for the qual charts. 19 """ 20 name = "QualConstants" 21 data_url = r"/server/(\d+)/metrics/database/([^\/]+)/query/(-?\d+)/qual/(\d+)/constants" 22 xaxis = "rownumber" 23 occurences = MetricDef(label="<%=group%>") 24 grouper = "constants" 25 26 @property 27 def query(self): 28 query = (qual_constants(bindparam("server"), "most_used", 29 bindparam("from"), 30 bindparam("to"), 31 text(""" 32 datname = :database AND 33 coalesce_range && tstzrange(:from, :to)"""), ":query", ":qual", top=10)) 34 base = qualstat_getstatdata(bindparam("server")) 35 c = inner_cc(base) 36 base = base.where(c.queryid == bindparam("query")).alias() 37 totals = (base.select() 38 .where((c.qualid == bindparam("qual")) & 39 (c.queryid == bindparam("query")))).alias() 40 return (query.alias().select() 41 .column(totals.c.occurences.label('total_occurences')) 42 .correlate(query)) 43 44 def add_params(self, params): 45 params['queryids'] = [int(params['query'])] 46 return params 47 48 def post_process(self, data, server, database, query, qual, **kwargs): 49 if not data['data']: 50 return data 51 max_rownumber = 0 52 total_top10 = 0 53 total = None 54 d = {'total_occurences': 0} 55 for d in data['data']: 56 max_rownumber = max(max_rownumber, d['rownumber']) 57 total_top10 += d['occurences'] 58 else: 59 total = d['total_occurences'] 60 data['data'].append({'occurences': total - total_top10, 61 'rownumber': max_rownumber + 1, 62 'constants': 'Others'}) 63 return data 64 65 66class QualDetail(ContentWidget): 67 """ 68 Content widget showing detail for a specific qual. 69 """ 70 title = "Detail for this Qual" 71 data_url = r"/server/(\d+)/database/([^\/]+)/query/(-?\d+)/qual/(\d+)/detail" 72 73 def get(self, server, database, query, qual): 74 try: 75 # Check remote access first 76 remote_conn = self.connect(server, database=database, 77 remote_access=True) 78 except Exception as e: 79 raise HTTPError(501, "Could not connect to remote server: %s" % 80 str(e)) 81 stmt = qualstat_getstatdata(server) 82 c = inner_cc(stmt) 83 stmt = stmt.alias() 84 stmt = (stmt.select() 85 .where((c.qualid == bindparam("qualid"))) 86 .where(stmt.c.occurences > 0) 87 .column((stmt.c.queryid == bindparam("query")).label("is_my_query"))) 88 quals = list(self.execute( 89 stmt, 90 params={"server": server, 91 "query": query, 92 "from": self.get_argument("from"), 93 "to": self.get_argument("to"), 94 "queryids": [query], 95 "qualid": qual})) 96 97 my_qual = None 98 other_queries = {} 99 100 for qual in quals: 101 if qual['is_my_query']: 102 my_qual = resolve_quals(remote_conn, [qual])[0] 103 104 if my_qual is None: 105 self.render("xhr.html", content="No data") 106 return 107 108 self.render("database/query/qualdetail.html", 109 qual=my_qual, 110 database=database, 111 server=server) 112 113class OtherQueriesMetricGroup(MetricGroupDef): 114 """Metric group showing other queries for this qual.""" 115 name = "other_queries" 116 xaxis = "queryid" 117 axis_type = "category" 118 data_url = r"/server/(\d+)/metrics/database/([^\/]+)/query/(-?\d+)/qual/(\d+)/other_queries" 119 query_str = MetricDef(label="Query", type="query", url_attr="url") 120 121 122 @property 123 def query(self): 124 return text(""" 125 SELECT distinct queryid, query, 126 query as query_str, pd.srvid 127 FROM powa_qualstats_quals pqs 128 JOIN powa_statements USING (queryid, dbid, srvid, userid) 129 JOIN powa_databases pd ON pd.oid = pqs.dbid AND pd.srvid = 130 pqs.srvid 131 WHERE qualid = :qual 132 AND pqs.queryid != :query 133 AND pd.srvid = :server 134 AND pd.datname = :database""") 135 136 def process(self, val, database=None, **kwargs): 137 val = dict(val) 138 val["url"] = self.reverse_url( 139 "QueryOverview", val["srvid"], database, val["queryid"]) 140 return val 141 142 143class QualOverview(DashboardPage): 144 """ 145 Dashboard page for a specific qual. 146 """ 147 148 base_url = r"/server/(\d+)/database/([^\/]+)/query/(-?\d+)/qual/(\d+)" 149 params = ["server", "database", "query", "qual"] 150 datasources = [QualDetail, OtherQueriesMetricGroup, QualConstantsMetricGroup] 151 parent = QueryOverview 152 title = 'Predicate Overview' 153 154 def dashboard(self): 155 # This COULD be initialized in the constructor, but tornado < 3 doesn't 156 # call it 157 if getattr(self, '_dashboard', None) is not None: 158 return self._dashboard 159 160 self._dashboard = Dashboard( 161 "Qual %(qual)s", 162 [[QualDetail], 163 [Grid("Other queries", 164 metrics=OtherQueriesMetricGroup.all(), 165 columns=[])], 166 [Graph("Most executed values", 167 metrics=[QualConstantsMetricGroup.occurences], 168 x_label_attr="constants", 169 renderer="pie")]]) 170 171 return self._dashboard 172