def get_sql(self, get_ids):
sql = self._compose_sql(get_ids)
# ORDERING
sort = []
ordering = []
for ci, c in enumerate(self.columns):
if c.sort:
sort.append(quote_column(c.column_alias) + SQL_IS_NOT_NULL)
sort.append(quote_column(c.column_alias))
ordering.append(ci)
union_all_sql = SQL_UNION_ALL.join(sql)
union_all_sql = ConcatSQL(
SQL_SELECT,
SQL_STAR,
SQL_FROM,
sql_alias(sql_iso(union_all_sql), "a"),
SQL_ORDERBY,
sql_list(sort),
)
if DEBUG:
Log.note("{{sql}}", sql=union_all_sql)
return union_all_sql
def _compose_sql(self, get_ids):
"""
:param get_ids: SQL to get the ids, and used to select the documents returned
:return:
"""
if not isinstance(get_ids, SQL):
Log.error("Expecting SQL to get some primary ids")
sql = []
for nested_path in self.all_nested_paths:
# MAKE THE REQUIRED JOINS
sql_joins = []
for i, curr_join in enumerate(
self.nested_path_to_join[nested_path[0]]):
curr_join = wrap(curr_join)
rel = curr_join.join_columns[0]
if i == 0:
sql_joins.append(
ConcatSQL(
SQL_FROM,
sql_alias(sql_iso(get_ids),
rel.referenced.table.alias),
))
elif curr_join.children:
full_name = quote_column(rel.table.schema, rel.table.name)
sql_joins.append(
ConcatSQL(
SQL_INNER_JOIN,
sql_alias(full_name, rel.table.alias),
SQL_ON,
SQL_AND.join(
ConcatSQL(
quote_column(rel.table.alias,
const_col.column.name),
SQL_EQ,
quote_column(
rel.referenced.table.alias,
const_col.referenced.column.name,
),
) for const_col in curr_join.join_columns),
))
else:
full_name = quote_column(rel.referenced.table.schema,
rel.referenced.table.name)
sql_joins.append(
ConcatSQL(
SQL_LEFT_JOIN,
sql_alias(full_name, rel.referenced.table.alias),
SQL_ON,
SQL_AND.join(
ConcatSQL(
quote_column(
rel.referenced.table.alias,
const_col.referenced.column.name,
),
SQL_EQ,
quote_column(rel.table.alias,
const_col.column.name),
) for const_col in curr_join.join_columns),
))
# ONLY SELECT WHAT WE NEED, NULL THE REST
selects = []
not_null_column_seen = False
for c in self.columns:
if (
c.column.table.name,
c.column.column.name,
) in self.settings.exclude_columns:
selects.append(sql_alias(SQL_NULL, c.column_alias))
elif c.nested_path[0] == nested_path[0]:
s = sql_alias(
quote_column(c.table_alias, c.column.column.name),
c.column_alias,
)
selects.append(s)
not_null_column_seen = True
elif startswith_field(nested_path[0], c.path):
# PARENT ID REFERENCES
if c.column.is_id:
s = sql_alias(
quote_column(c.table_alias, c.column.column.name),
c.column_alias,
)
selects.append(s)
not_null_column_seen = True
else:
selects.append(sql_alias(SQL_NULL, c.column_alias))
else:
selects.append(sql_alias(SQL_NULL, c.column_alias))
if not_null_column_seen:
sql.append(SQL_SELECT + sql_list(selects) +
SQL("").join(sql_joins))
return sql
def _aggop(self, query):
"""
SINGLE ROW RETURNED WITH AGGREGATES
"""
if isinstance(query.select, list):
# RETURN SINGLE OBJECT WITH AGGREGATES
for s in query.select:
if s.aggregate not in aggregates:
Log.error(
"Expecting all columns to have an aggregate: {{select}}",
select=s)
selects = FlatList()
for s in query.select:
selects.append(
sql_alias(
aggregates[s.aggregate].replace("{{code}}", s.value),
quote_column(s.name)))
sql = expand_template(
"""
SELECT
{{selects}}
FROM
{{table}}
{{where}}
""", {
"selects": SQL(",\n".join(selects)),
"table": self._subquery(query["from"])[0],
"where": self._where2sql(query.filter)
})
return sql, lambda sql: self.db.column(sql)[
0] # RETURNING SINGLE OBJECT WITH AGGREGATE VALUES
else:
# RETURN SINGLE VALUE
s0 = query.select
if s0.aggregate not in aggregates:
Log.error(
"Expecting all columns to have an aggregate: {{select}}",
select=s0)
select = sql_alias(
aggregates[s0.aggregate].replace("{{code}}", s0.value),
quote_column(s0.name))
sql = expand_template(
"""
SELECT
{{selects}}
FROM
{{table}}
{{where}}
""", {
"selects": SQL(select),
"table": self._subquery(query["from"])[0],
"where": self._where2sql(query.where)
})
def post(sql):
result = self.db.column_query(sql)
return result[0][0]
return sql, post # RETURN SINGLE VALUE
def _setop(self, query):
"""
NO AGGREGATION, SIMPLE LIST COMPREHENSION
"""
if isinstance(query.select, list):
# RETURN BORING RESULT SET
selects = FlatList()
for s in listwrap(query.select):
if isinstance(s.value, Mapping):
for k, v in s.value.items:
selects.append(
sql_alias(v, quote_column(s.name + "." + k)))
if isinstance(s.value, list):
for i, ss in enumerate(s.value):
selects.append(
sql_alias(s.value,
quote_column(s.name + "," + str(i))))
else:
selects.append(sql_alias(s.value, quote_column(s.name)))
sql = expand_template(
"""
SELECT
{{selects}}
FROM
{{table}}
{{where}}
{{sort}}
{{limit}}
""", {
"selects": SQL(",\n".join(selects)),
"table": self._subquery(query["from"])[0],
"where": self._where2sql(query.where),
"limit": self._limit2sql(query.limit),
"sort": self._sort2sql(query.sort)
})
def post_process(sql):
result = self.db.query(sql)
for s in listwrap(query.select):
if isinstance(s.value, Mapping):
for r in result:
r[s.name] = {}
for k, v in s.value:
r[s.name][k] = r[s.name + "." + k]
r[s.name + "." + k] = None
if isinstance(s.value, list):
# REWRITE AS TUPLE
for r in result:
r[s.name] = tuple(r[s.name + "," + str(i)]
for i, ss in enumerate(s.value))
for i, ss in enumerate(s.value):
r[s.name + "," + str(i)] = None
expand_json(result)
return result
return sql, post_process # RETURN BORING RESULT SET
else:
# RETURN LIST OF VALUES
if query.select.value == ".":
select = "*"
else:
name = query.select.name
select = sql_alias(query.select.value, quote_column(name))
sql = expand_template(
"""
SELECT
{{selects}}
FROM
{{table}}
{{where}}
{{sort}}
{{limit}}
""", {
"selects": SQL(select),
"table": self._subquery(query["from"])[0],
"where": self._where2sql(query.where),
"limit": self._limit2sql(query.limit),
"sort": self._sort2sql(query.sort)
})
if query.select.value == ".":
def post(sql):
result = self.db.query(sql)
expand_json(result)
return result
return sql, post
else:
return sql, lambda sql: [r[name] for r in self.db.query(sql)
] # RETURNING LIST OF VALUES
def _grouped(self, query, stacked=False):
select = listwrap(query.select)
# RETURN SINGLE OBJECT WITH AGGREGATES
for s in select:
if s.aggregate not in aggregates:
Log.error(
"Expecting all columns to have an aggregate: {{select}}",
select=s)
selects = FlatList()
groups = FlatList()
edges = query.edges
for e in edges:
if e.domain.type != "default":
Log.error("domain of type {{type}} not supported, yet",
type=e.domain.type)
groups.append(e.value)
selects.append(sql_alias(e.value, quote_column(e.name)))
for s in select:
selects.append(
sql_alias(aggregates[s.aggregate].replace("{{code}}", s.value),
quote_column(s.name)))
sql = expand_template(
"""
SELECT
{{selects}}
FROM
{{table}}
{{where}}
GROUP BY
{{groups}}
""", {
"selects": SQL(",\n".join(selects)),
"groups": SQL(",\n".join(groups)),
"table": self._subquery(query["from"])[0],
"where": self._where2sql(query.where)
})
def post_stacked(sql):
# RETURN IN THE USUAL DATABASE RESULT SET FORMAT
return self.db.query(sql)
def post(sql):
# FIND OUT THE default DOMAIN SIZES
result = self.db.column_query(sql)
num_edges = len(edges)
for e, edge in enumerate(edges):
domain = edge.domain
if domain.type == "default":
domain.type = "set"
parts = set(result[e])
domain.partitions = [{
"index": i,
"value": p
} for i, p in enumerate(parts)]
domain.map = {p: i for i, p in enumerate(parts)}
else:
Log.error("Do not know what to do here, yet")
# FILL THE DATA CUBE
maps = [(unwrap(e.domain.map), result[i])
for i, e in enumerate(edges)]
cubes = FlatList()
for c, s in enumerate(select):
data = Matrix(*[
len(e.domain.partitions) + (1 if e.allow_nulls else 0)
for e in edges
])
for rownum, value in enumerate(result[c + num_edges]):
coord = [m[r[rownum]] for m, r in maps]
data[coord] = value
cubes.append(data)
if isinstance(query.select, list):
return cubes
else:
return cubes[0]
return sql, post if not stacked else post_stacked
