def post(self, database):
payload = json.loads(self.request.body.decode("utf8"))
from_date = payload['from_date']
to_date = payload['to_date']
indexes = []
for ind in payload['indexes']:
hypoind = HypoIndex(ind['nspname'],
ind['relname'],
ind['ams'])
hypoind._ddl = ind['ddl']
indexes.append(hypoind)
queryids = payload['queryids']
powa_conn = self.connect(database="powa")
conn = self.connect(database=database)
queries = list(powa_conn.execute(text("""
SELECT DISTINCT query, ps.queryid
FROM powa_statements ps
WHERE queryid IN :queryids
"""), queryids=tuple(queryids)))
# Create all possible indexes for this qual
hypo_version = self.has_extension("hypopg", database=database)
hypoplans = {}
indbyname = {}
if hypo_version and hypo_version >= "0.0.3":
# identify indexes
# create them
for ind in indexes:
indname = self.execute(
select(["*"])
.select_from(func.hypopg_create_index(ind.ddl)),
database=database).first()[1]
indbyname[indname] = ind
# Build the query and fetch the plans
for query in queries:
querystr = get_any_sample_query(self, database, query.queryid,
from_date,
to_date)
if querystr:
try:
hypoplans[query.queryid] = get_hypoplans(
self.connect(database=database), querystr,
indbyname.values())
except:
# TODO: stop ignoring the error
continue
# To value of a link is the the reduction in cost
self.render_json(hypoplans)
def post(self, database):
payload = json.loads(self.request.body.decode("utf8"))
from_date = payload['from_date']
to_date = payload['to_date']
indexes = []
for ind in payload['indexes']:
hypoind = HypoIndex(ind['nspname'], ind['relname'], ind['ams'])
hypoind._ddl = ind['ddl']
indexes.append(hypoind)
queryids = payload['queryids']
powa_conn = self.connect(database="powa")
conn = self.connect(database=database)
queries = list(
powa_conn.execute(text("""
SELECT DISTINCT query, ps.queryid
FROM powa_statements ps
WHERE queryid IN :queryids
"""),
queryids=tuple(queryids)))
# Create all possible indexes for this qual
hypo_version = self.has_extension("hypopg", database=database)
hypoplans = {}
indbyname = {}
if hypo_version and hypo_version >= "0.0.3":
# identify indexes
# create them
for ind in indexes:
indname = self.execute(select(["*"]).select_from(
func.hypopg_create_index(ind.ddl)),
database=database).first()[1]
indbyname[indname] = ind
# Build the query and fetch the plans
for query in queries:
querystr = get_any_sample_query(self, database, query.queryid,
from_date, to_date)
if querystr:
try:
hypoplans[query.queryid] = get_hypoplans(
self.connect(database=database), querystr,
indbyname.values())
except:
# TODO: stop ignoring the error
continue
# To value of a link is the the reduction in cost
self.render_json(hypoplans)
def hypo_ddl(self):
ddl = self.ddl
if ddl is not None:
return select([column("indexname")
]).select_from(func.hypopg_create_index(self.ddl))
def post(self, srvid, database):
try:
# Check remote access first
remote_conn = self.connect(srvid,
database=database,
remote_access=True)
except Exception as e:
raise HTTPError(501,
"Could not connect to remote server: %s" % str(e))
payload = json.loads(self.request.body.decode("utf8"))
from_date = payload['from_date']
to_date = payload['to_date']
indexes = []
for ind in payload['indexes']:
hypoind = HypoIndex(ind['nspname'], ind['relname'], ind['ams'])
hypoind._ddl = ind['ddl']
indexes.append(hypoind)
queryids = payload['queryids']
powa_conn = self.connect(database="powa")
queries = list(
powa_conn.execute(text("""
SELECT DISTINCT query, ps.queryid
FROM powa_statements ps
WHERE srvid = :srvid
AND queryid IN :queryids
"""),
srvid=srvid,
queryids=tuple(queryids)))
# Create all possible indexes for this qual
hypo_version = self.has_extension_version(srvid,
"hypopg",
"0.0.3",
database=database)
hypoplans = {}
indbyname = {}
inderrors = {}
if hypo_version:
# identify indexes
# create them
for ind in indexes:
try:
indname = remote_conn.execute(
select(["*"]).select_from(
func.hypopg_create_index(ind.ddl))).first()[1]
indbyname[indname] = ind
except DBAPIError as e:
inderrors[ind.ddl] = str(e.orig)
continue
except Exception:
# TODO handle other errors?
continue
# Build the query and fetch the plans
for query in queries:
querystr = get_any_sample_query(self, srvid, database,
query.queryid, from_date,
to_date)
if querystr:
try:
hypoplans[query.queryid] = get_hypoplans(
remote_conn, querystr, indbyname.values())
except Exception:
# TODO: stop ignoring the error
continue
# To value of a link is the the reduction in cost
result = {}
result["plans"] = hypoplans
result["inderrors"] = inderrors
self.render_json(result)
def hypo_ddl(self):
ddl = self.ddl
if ddl is not None:
return select([column("indexname")]).select_from(func.hypopg_create_index(self.ddl))
