def _subquery(self, query, isolate=True, stacked=False):
"""
RETURN (sql, post), WHERE post IS A FUNCTION TO CALL ON ROWS TO GET THE RESULT
:param query:
:param isolate:
:param stacked:
:return:
"""
if isinstance(query, text):
return quote_column(query), None
if query.name: # IT WOULD BE SAFER TO WRAP TABLE REFERENCES IN A TYPED OBJECT (Cube, MAYBE?)
return quote_column(query.name), None
if query.edges:
# RETURN A CUBE
sql, post = self._grouped(query, stacked)
else:
select = listwrap(query.select)
if select[0].aggregate != "none":
sql, post = self._aggop(query)
else:
sql, post = self._setop(query)
if isolate:
return "(\n" + sql + "\n) a\n", post
else:
return sql, post
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 _sort2sql(self, sort):
"""
RETURN ORDER BY CLAUSE
"""
if not sort:
return ""
return SQL_ORDERBY + sql_list([
quote_column(o.field) + (" DESC" if o.sort == -1 else "")
for o in sort
])
def update(self, query):
self.db.execute(
"""
UPDATE {{table_name}}
SET {{assignment}}
{{where}}
""", {
"table_name":
query["from"],
"assignment":
",".join(
quote_column(k) + "=" + quote_value(v)
for k, v in query.set),
"where":
self._where2sql(query.where)
})
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 follow_paths(position, path, nested_path, done_relations,
no_nested_docs):
if position.name in self.settings.exclude:
return
if self.path_not_allowed(path):
return
if DEBUG:
Log.note("Trace {{path}}", path=path)
if position.name != "__ids__":
# USED TO CONFIRM WE CAN ACCESS THE TABLE (WILL THROW ERROR WHEN IF IT FAILS)
self.db.query(
ConcatSQL(
SQL_SELECT,
SQL_STAR,
SQL_FROM,
quote_column(position.schema, position.name),
SQL_LIMIT,
SQL_ONE,
))
if position.name in reference_all_tables:
no_nested_docs = True
if position.name in reference_only_tables:
return
curr_join_list = copy(nested_path_to_join[nested_path[0]])
###############################################################################
# INNER OBJECTS
###############################################################################
referenced_tables = list(
sort_using_key(
jx.groupby(
jx.filter(
relations,
{
"eq": {
"table.name": position.name,
"table.schema": position.schema,
}
},
),
"constraint.name",
),
key=lambda p: first(p[1]).column.name,
))
for g, constraint_columns in referenced_tables:
g = unwrap(g)
constraint_columns = deepcopy(constraint_columns)
if g["constraint.name"] in done_relations:
continue
if any(cc for cc in constraint_columns
if cc.referenced.table.name in self.settings.exclude):
continue
done_relations.add(g["constraint.name"])
many_to_one_joins = nested_path_to_join[nested_path[0]]
index = len(many_to_one_joins)
alias = "t" + text(index)
for c in constraint_columns:
c.referenced.table.alias = alias
c.table = position
many_to_one_joins.append({
"join_columns": constraint_columns,
"path": path,
"nested_path": nested_path,
})
# HANDLE THE COMMON *id SUFFIX
name = []
for cname, tname in zip(
constraint_columns.column.name,
constraint_columns.referenced.table.name,
):
if cname.startswith(tname):
name.append(tname)
elif cname.endswith("_id"):
name.append(cname[:-3])
else:
name.append(cname)
relation_string = many_to_one_string(constraint_columns[0])
step = "/".join(name)
if len(constraint_columns) == 1:
step = self.name_relations.get(relation_string, step)
referenced_column_path = concat_field(path, step)
if self.path_not_allowed(referenced_column_path):
continue
if referenced_column_path in reference_only_tables:
continue
col_pointer_name = relative_field(referenced_column_path,
nested_path[0])
for col in columns:
if (col.table.name
== constraint_columns[0].referenced.table.name
and col.table.schema
== constraint_columns[0].referenced.table.schema):
col_full_name = concat_field(
col_pointer_name, literal_field(col.column.name))
if (col.is_id and len(nested_path) == 1
and col.table.name == fact_table.name
and col.table.schema == fact_table.schema):
# ALWAYS SHOW THE ID OF THE FACT
c_index = len(output_columns)
output_columns.append({
"table_alias":
alias,
"column_alias":
"c" + text(c_index),
"column":
col,
"sort":
True,
"path":
referenced_column_path,
"nested_path":
nested_path,
"put":
col_full_name,
})
elif col.column.name == constraint_columns[
0].column.name:
c_index = len(output_columns)
output_columns.append({
"table_alias":
alias,
"column_alias":
"c" + text(c_index),
"column":
col,
"sort":
False,
"path":
referenced_column_path,
"nested_path":
nested_path,
"put":
col_full_name
if self.settings.show_foreign_keys else None,
})
elif col.is_id:
c_index = len(output_columns)
output_columns.append({
"table_alias":
alias,
"column_alias":
"c" + text(c_index),
"column":
col,
"sort":
False,
"path":
referenced_column_path,
"nested_path":
nested_path,
"put":
col_full_name
if self.settings.show_foreign_keys else None,
})
elif col.reference:
c_index = len(output_columns)
output_columns.append({
"table_alias":
alias,
"column_alias":
"c" + text(c_index),
"column":
col,
"sort":
False,
"path":
referenced_column_path,
"nested_path":
nested_path,
"put":
col_pointer_name
if not self.settings.show_foreign_keys else
col_full_name, # REFERENCE FIELDS CAN REPLACE THE WHOLE OBJECT BEING REFERENCED
})
elif col.include:
c_index = len(output_columns)
output_columns.append({
"table_alias":
alias,
"column_alias":
"c" + text(c_index),
"column":
col,
"sort":
False,
"path":
referenced_column_path,
"nested_path":
nested_path,
"put":
col_full_name,
})
if position.name in reference_only_tables:
continue
todo.append(
Data(
position=copy(constraint_columns[0].referenced.table),
path=referenced_column_path,
nested_path=nested_path,
done_relations=copy(done_relations),
no_nested_docs=no_nested_docs,
))
###############################################################################
# NESTED OBJECTS
###############################################################################
if not no_nested_docs:
nesting_tables = list(
sort_using_key(
jx.groupby(
jx.filter(
relations,
{
"eq": {
"referenced.table.name": position.name,
"referenced.table.schema":
position.schema,
}
},
),
"constraint.name",
),
key=lambda p: [(r.table.name, r.column.name)
for r in [first(p[1])]][0],
))
for g, constraint_columns in nesting_tables:
g = unwrap(g)
constraint_columns = deepcopy(constraint_columns)
if g["constraint.name"] in done_relations:
continue
done_relations.add(g["constraint.name"])
many_table = set(constraint_columns.table.name)
if not (many_table - self.settings.exclude):
continue
relation_string = one_to_many_string(constraint_columns[0])
step = "/".join(many_table)
if len(constraint_columns) == 1:
step = self.name_relations.get(relation_string, step)
referenced_column_path = concat_field(path, step)
if self.path_not_allowed(referenced_column_path):
continue
new_nested_path = [referenced_column_path] + nested_path
all_nested_paths.append(new_nested_path)
if referenced_column_path in nested_path_to_join:
Log.error(
"{{path}} already exists, try adding entry to name_relations",
path=referenced_column_path,
)
one_to_many_joins = nested_path_to_join[
referenced_column_path] = copy(curr_join_list)
index = len(one_to_many_joins)
alias = "t" + text(index)
for c in constraint_columns:
c.table.alias = alias
c.referenced.table = position
one_to_many_joins.append(
set_default(
{},
g,
{
"children": True,
"join_columns": constraint_columns,
"path": path,
"nested_path": nested_path,
},
))
for col in columns:
if (col.table.name == constraint_columns[0].table.name
and col.table.schema
== constraint_columns[0].table.schema):
col_full_name = join_field(
split_field(referenced_column_path)
[len(split_field(new_nested_path[0])):] +
[literal_field(col.column.name)])
if col.column.name == constraint_columns[
0].column.name:
c_index = len(output_columns)
output_columns.append({
"table_alias":
alias,
"column_alias":
"c" + text(c_index),
"column":
col,
"sort":
col.is_id,
"path":
referenced_column_path,
"nested_path":
new_nested_path,
"put":
col_full_name if
self.settings.show_foreign_keys else None,
})
elif col.is_id:
c_index = len(output_columns)
output_columns.append({
"table_alias":
alias,
"column_alias":
"c" + text(c_index),
"column":
col,
"sort":
col.is_id,
"path":
referenced_column_path,
"nested_path":
new_nested_path,
"put":
col_full_name if
self.settings.show_foreign_keys else None,
})
else:
c_index = len(output_columns)
output_columns.append({
"table_alias":
alias,
"column_alias":
"c" + text(c_index),
"column":
col,
"sort":
col.is_id,
"path":
referenced_column_path,
"nested_path":
new_nested_path,
"put":
col_full_name if col.include else None,
})
todo.append(
Data(
position=constraint_columns[0].table,
path=referenced_column_path,
nested_path=new_nested_path,
done_relations=copy(done_relations),
no_nested_docs=no_nested_docs,
))
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 _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 _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
