1"""
2Global optimization widget
3"""
4from __future__ import absolute_import
5from powa.framework import AuthHandler
6from powa.dashboards import (
7    Widget, MetricGroupDef)
8
9from powa.sql import (resolve_quals, get_any_sample_query,
10                      get_hypoplans, HypoIndex)
11import json
12from powa.sql.compat import JSONB
13from powa.sql.views import qualstat_getstatdata
14from powa.sql.tables import powa_databases
15from sqlalchemy.sql import (bindparam, literal_column, join, select,
16                            alias, text, func, column, cast)
17from sqlalchemy.types import TEXT
18from sqlalchemy.exc import DBAPIError
19from tornado.web import HTTPError
20
21
22class IndexSuggestionHandler(AuthHandler):
23
24    def post(self, srvid, database):
25        try:
26            # Check remote access first
27            remote_conn = self.connect(srvid, database=database,
28                                       remote_access=True)
29        except Exception as e:
30            raise HTTPError(501, "Could not connect to remote server: %s" %
31                                 str(e))
32
33        payload = json.loads(self.request.body.decode("utf8"))
34        from_date = payload['from_date']
35        to_date = payload['to_date']
36        indexes = []
37        for ind in payload['indexes']:
38            hypoind = HypoIndex(ind['nspname'],
39                                ind['relname'],
40                                ind['ams'])
41            hypoind._ddl = ind['ddl']
42            indexes.append(hypoind)
43        queryids = payload['queryids']
44        powa_conn = self.connect(database="powa")
45        queries = list(powa_conn.execute(text("""
46            SELECT DISTINCT query, ps.queryid
47            FROM powa_statements ps
48            WHERE srvid = :srvid
49            AND queryid IN :queryids
50        """), srvid=srvid, queryids=tuple(queryids)))
51        # Create all possible indexes for this qual
52        hypo_version = self.has_extension_version(srvid, "hypopg",
53                                                  database=database)
54        hypoplans = {}
55        indbyname = {}
56        inderrors = {}
57        if hypo_version and hypo_version >= "0.0.3":
58            # identify indexes
59            # create them
60            for ind in indexes:
61                try:
62                    indname = remote_conn.execute(
63                            select(["*"])
64                            .select_from(func.hypopg_create_index(ind.ddl))
65                    ).first()[1]
66                    indbyname[indname] = ind
67                except DBAPIError as e:
68                    inderrors[ind.ddl] = str(e.orig)
69                    continue
70                except Exception:
71                    # TODO handle other errors?
72                    continue
73            # Build the query and fetch the plans
74            for query in queries:
75                querystr = get_any_sample_query(self, srvid, database,
76                                                query.queryid,
77                                                from_date,
78                                                to_date)
79                if querystr:
80                    try:
81                        hypoplans[query.queryid] = get_hypoplans(
82                            remote_conn, querystr, indbyname.values())
83                    except Exception:
84                        # TODO: stop ignoring the error
85                        continue
86            # To value of a link is the the reduction in cost
87        result = {}
88        result["plans"] = hypoplans
89        result["inderrors"] = inderrors
90        self.render_json(result)
91
92
93class WizardMetricGroup(MetricGroupDef):
94    """Metric group for the wizard."""
95    name = "wizard"
96    xaxis = "quals"
97    axis_type = "category"
98    data_url = r"/server/(\d+)/metrics/database/([^\/]+)/wizard/"
99    enabled = False
100
101    @property
102    def query(self):
103        pq = qualstat_getstatdata(bindparam("server"),
104                                  column("eval_type") == "f")
105        base = alias(pq)
106        query = (select([
107            # queryid in pg11+ is int64, so the value can exceed javascript's
108            # Number.MAX_SAFE_INTEGER, which mean that the value can get
109            # truncated by the browser, leading to looking for unexisting
110            # queryid when processing this data.  To avoid that, simply cast
111            # the value to text.
112            func.array_agg(cast(column("queryid"), TEXT)).label("queryids"),
113            column("qualid"),
114            cast(column("quals"), JSONB).label('quals'),
115            column("occurences"),
116            column("execution_count"),
117            func.array_agg(column("query")).label("queries"),
118            column("avg_filter"),
119            column("filter_ratio")
120        ]).select_from(
121            join(base, powa_databases,
122                 onclause=(
123                     powa_databases.c.oid == literal_column("dbid") and
124                     powa_databases.c.srvid == literal_column("srvid")
125                 )))
126            .where(powa_databases.c.datname == bindparam("database"))
127            .where(powa_databases.c.srvid == bindparam("server"))
128            .where(column("avg_filter") > 1000)
129            .where(column("filter_ratio") > 0.3)
130            .group_by(column("qualid"), column("execution_count"),
131                      column("occurences"),
132                      cast(column("quals"), JSONB),
133                     column("avg_filter"), column("filter_ratio"))
134            .order_by(column("occurences").desc())
135            .limit(200))
136        return query
137
138    def post_process(self, data, server, database, **kwargs):
139        conn = self.connect(server, database=database, remote_access=True)
140        data["data"] = resolve_quals(conn, data["data"])
141        return data
142
143
144class Wizard(Widget):
145
146    def __init__(self, title):
147        self.title = title
148
149    def parameterized_json(self, handler, **parms):
150        values = self.__dict__.copy()
151        values['metrics'] = []
152        values['type'] = 'wizard'
153        values['datasource'] = 'wizard'
154
155        # First check that we can connect on the remote server, otherwise we
156        # won't be able to do anything
157        try:
158            remote_conn = handler.connect(parms["server"],
159                                          database=parms["database"],
160                                          remote_access=True)
161        except Exception as e:
162            values['has_remote_conn'] = False
163            values['conn_error'] = str(e)
164            return values
165
166        values['has_remote_conn'] = True
167
168        hypover = handler.has_extension_version(parms["server"],
169                                                "hypopg",
170                                                database=parms["database"])
171        qsver = handler.has_extension_version(parms["server"], "pg_qualstats")
172        values['has_hypopg'] = hypover and hypover >= '0.0.3'
173        values['has_qualstats'] = qsver and qsver >= '0.0.7'
174        values['server'] = parms["server"]
175        values['database'] = parms["database"]
176        return values
177