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