def to_sql(self, schema, not_null=False, boolean=False, many=True):
var_name = self.var
if var_name == GUID:
return wrap([{
"name": ".",
"sql": {
"s": quoted_GUID
},
"nested_path": ROOT_PATH
}])
cols = schema.leaves(var_name)
if not cols:
return self.lang[NULL].to_sql(schema)
acc = {}
if boolean:
for col in cols:
cname = relative_field(col.name, var_name)
nested_path = col.nested_path[0]
if col.type == OBJECT:
value = SQL_TRUE
elif col.type == BOOLEAN:
value = quote_column(col.es_column)
else:
value = quote_column(col.es_column) + SQL_IS_NOT_NULL
tempa = acc.setdefault(nested_path, {})
tempb = tempa.setdefault(get_property_name(cname), {})
tempb["b"] = value
else:
for col in cols:
cname = relative_field(col.name, var_name)
if col.jx_type == OBJECT:
prefix = self.var + "."
for cn, cs in schema.items():
if cn.startswith(prefix):
for child_col in cs:
tempa = acc.setdefault(
child_col.nested_path[0], {})
tempb = tempa.setdefault(
get_property_name(cname), {})
tempb[json_type_to_sql_type[
col.type]] = quote_column(
child_col.es_column)
else:
nested_path = col.nested_path[0]
tempa = acc.setdefault(nested_path, {})
tempb = tempa.setdefault(get_property_name(cname), {})
tempb[json_type_to_sql_type[col.jx_type]] = quote_column(
col.es_column)
return wrap([{
"name": cname,
"sql": types,
"nested_path": nested_path
} for nested_path, pairs in acc.items()
for cname, types in pairs.items()])
def to_sql(self, schema, not_null=False, boolean=False):
cols = [
Data({cname: c}) for cname, cs in schema.map_to_sql(self.var).items()
for c in cs
]
if not cols:
# DOES NOT EXIST
return wrap([{
"name": ".",
"sql": {
"0": "NULL"
},
"nested_path": ROOT_PATH
}])
acc = {}
if boolean:
for col in cols:
cname, col = col.items()[0]
nested_path = col.nested_path[0]
if col.type == OBJECT:
value = "1"
elif col.type == "boolean":
value = quote_column(col.es_column).sql
else:
value = "(" + quote_column(col.es_column).sql + ") IS NOT NULL"
tempa = acc.setdefault(nested_path, {})
tempb = tempa.setdefault(get_property_name(cname), {})
tempb['b'] = value
else:
for col in cols:
cname, col = col.items()[0]
if col.type == OBJECT:
prefix = self.var + "."
for cn, cs in schema.items():
if cn.startswith(prefix):
for child_col in cs:
tempa = acc.setdefault(child_col.nested_path[0],
{})
tempb = tempa.setdefault(get_property_name(cname),
{})
tempb[json_type_to_sql_type[
col.type]] = quote_column(
child_col.es_column).sql
else:
nested_path = col.nested_path[0]
tempa = acc.setdefault(nested_path, {})
tempb = tempa.setdefault(get_property_name(cname), {})
tempb[json_type_to_sql_type[col.type]] = quote_column(
col.es_column).sql
return wrap([{
"name": relative_field(cname, self.var),
"sql": types,
"nested_path": nested_path
} for nested_path, pairs in acc.items() for cname, types in pairs.items()])
def map_to_sql(self, var=""):
"""
RETURN A MAP FROM THE RELATIVE AND ABSOLUTE NAME SPACE TO COLUMNS
"""
origin = self.nested_path[0]
if startswith_field(var, origin) and origin != var:
var = relative_field(var, origin)
fact_dict = {}
origin_dict = {}
for k, cs in self.namespace.items():
for c in cs:
if c.jx_type in STRUCT:
continue
if startswith_field(get_property_name(k), var):
origin_dict.setdefault(relative_field(c.name, origin),
[]).append(c)
if origin != c.nested_path[0]:
fact_dict.setdefault(c.name, []).append(c)
elif origin == var:
origin_dict.setdefault(
concat_field(var, relative_field(c.name, origin)),
[]).append(c)
if origin != c.nested_path[0]:
fact_dict.setdefault(concat_field(var, c.name),
[]).append(c)
return set_default(origin_dict, fact_dict)
def get_column_name(self, column):
"""
RETURN THE COLUMN NAME, FROM THE PERSPECTIVE OF THIS SCHEMA
:param column:
:return: NAME OF column
"""
return get_property_name(column.names[self.nested_path[0]])
def to_sql(self, schema, not_null=False, boolean=False):
if self.var == GUID:
return wrap([{"name": ".", "sql": {"s": quoted_GUID}, "nested_path": ROOT_PATH}])
vars = schema[self.var]
if not vars:
# DOES NOT EXIST
return wrap([{"name": ".", "sql": {"0": SQL_NULL}, "nested_path": ROOT_PATH}])
var_name = list(set(listwrap(vars).name))
if len(var_name) > 1:
Log.error("do not know how to handle")
var_name = var_name[0]
cols = schema.leaves(self.var)
acc = {}
if boolean:
for col in cols:
cname = relative_field(col.name, var_name)
nested_path = col.nested_path[0]
if col.type == OBJECT:
value = SQL_TRUE
elif col.type == BOOLEAN:
value = quote_column(col.es_column)
else:
value = quote_column(col.es_column) + SQL_IS_NOT_NULL
tempa = acc.setdefault(nested_path, {})
tempb = tempa.setdefault(get_property_name(cname), {})
tempb['b'] = value
else:
for col in cols:
cname = relative_field(col.name, var_name)
if col.type == OBJECT:
prefix = self.var + "."
for cn, cs in schema.items():
if cn.startswith(prefix):
for child_col in cs:
tempa = acc.setdefault(child_col.nested_path[0], {})
tempb = tempa.setdefault(get_property_name(cname), {})
tempb[json_type_to_sql_type[col.type]] = quote_column(child_col.es_column)
else:
nested_path = col.nested_path[0]
tempa = acc.setdefault(nested_path, {})
tempb = tempa.setdefault(get_property_name(cname), {})
tempb[json_type_to_sql_type[col.type]] = quote_column(col.es_column)
return wrap([
{"name": cname, "sql": types, "nested_path": nested_path}
for nested_path, pairs in acc.items() for cname, types in pairs.items()
])
def get_column_name(self, column):
"""
RETURN THE COLUMN NAME, FROM THE PERSPECTIVE OF THIS SCHEMA
:param column:
:return: NAME OF column
"""
relative_name = relative_field(column.name, self.nested_path[0])
return get_property_name(relative_name)
def map_to_sql(self, var=""):
"""
RETURN A MAP FROM THE RELATIVE AND ABSOLUTE NAME SPACE TO COLUMNS
"""
origin = self.nested_path[0]
if startswith_field(var, origin) and origin != var:
var = relative_field(var, origin)
fact_dict = {}
origin_dict = {}
for k, cs in self.map.items():
for c in cs:
if c.type not in STRUCT:
if (startswith_field(get_property_name(k), var)):
if c.names[origin] in origin_dict:
origin_dict[c.names[origin]].append(c)
else:
origin_dict[c.names[origin]] = [c]
if origin != c.nested_path[0]:
if c.names["."] in fact_dict:
fact_dict[c.names["."]].append(c)
else:
fact_dict[c.names["."]] = [c]
elif origin == var:
if concat_field(var, c.names[origin]) in origin_dict:
origin_dict[concat_field(
var, c.names[origin])].append(c)
else:
origin_dict[concat_field(var,
c.names[origin])] = [c]
if origin != c.nested_path[0]:
if c.names["."] in fact_dict:
fact_dict[concat_field(var,
c.names["."])].append(c)
else:
fact_dict[concat_field(var,
c.names["."])] = [c]
return set_default(origin_dict, fact_dict)
def _set_op(self, query):
# GET LIST OF SELECTED COLUMNS
vars_ = UNION([
v.var for select in listwrap(query.select)
for v in select.value.vars()
])
schema = self.schema
known_vars = schema.keys()
active_columns = {".": set()}
for v in vars_:
for c in schema.leaves(v):
nest = c.nested_path[0]
active_columns.setdefault(nest, set()).add(c)
# ANY VARS MENTIONED WITH NO COLUMNS?
for v in vars_:
if not any(startswith_field(cname, v) for cname in known_vars):
active_columns["."].add(
Column(name=v,
jx_type=IS_NULL,
es_column=".",
es_index=".",
es_type='NULL',
nested_path=["."],
last_updated=Date.now()))
# EVERY COLUMN, AND THE INDEX IT TAKES UP
index_to_column = {} # MAP FROM INDEX TO COLUMN (OR SELECT CLAUSE)
index_to_uid = {} # FROM NESTED PATH TO THE INDEX OF UID
sql_selects = [
] # EVERY SELECT CLAUSE (NOT TO BE USED ON ALL TABLES, OF COURSE)
nest_to_alias = {
nested_path[0]: "__" + unichr(ord('a') + i) + "__"
for i, nested_path in enumerate(self.snowflake.query_paths)
}
sorts = []
if query.sort:
for select in query.sort:
col = SQLang[select.value].to_sql(schema)[0]
for t, sql in col.sql.items():
json_type = sql_type_to_json_type[t]
if json_type in STRUCT:
continue
column_number = len(sql_selects)
# SQL HAS ABS TABLE REFERENCE
column_alias = _make_column_name(column_number)
sql_selects.append(sql_alias(sql, column_alias))
if select.sort == -1:
sorts.append(quote_column(column_alias) + SQL_IS_NULL)
sorts.append(quote_column(column_alias) + " DESC")
else:
sorts.append(quote_column(column_alias) + SQL_IS_NULL)
sorts.append(quote_column(column_alias))
primary_doc_details = Data()
# EVERY SELECT STATEMENT THAT WILL BE REQUIRED, NO MATTER THE DEPTH
# WE WILL CREATE THEM ACCORDING TO THE DEPTH REQUIRED
nested_path = []
for step, sub_table in self.snowflake.tables:
nested_path.insert(0, step)
nested_doc_details = {
"sub_table": sub_table,
"children": [],
"index_to_column": {},
"nested_path": nested_path
}
# INSERT INTO TREE
if not primary_doc_details:
primary_doc_details = nested_doc_details
else:
def place(parent_doc_details):
if startswith_field(step,
parent_doc_details['nested_path'][0]):
for c in parent_doc_details['children']:
if place(c):
return True
parent_doc_details['children'].append(
nested_doc_details)
place(primary_doc_details)
alias = nested_doc_details['alias'] = nest_to_alias[step]
# WE ALWAYS ADD THE UID
column_number = index_to_uid[step] = nested_doc_details[
'id_coord'] = len(sql_selects)
sql_select = quote_column(alias, UID)
sql_selects.append(
sql_alias(sql_select, _make_column_name(column_number)))
if step != ".":
# ID AND ORDER FOR CHILD TABLES
index_to_column[column_number] = ColumnMapping(
sql=sql_select,
type="number",
nested_path=nested_path,
column_alias=_make_column_name(column_number))
column_number = len(sql_selects)
sql_select = quote_column(alias, ORDER)
sql_selects.append(
sql_alias(sql_select, _make_column_name(column_number)))
index_to_column[column_number] = ColumnMapping(
sql=sql_select,
type="number",
nested_path=nested_path,
column_alias=_make_column_name(column_number))
# WE DO NOT NEED DATA FROM TABLES WE REQUEST NOTHING FROM
if step not in active_columns:
continue
# ADD SQL SELECT COLUMNS FOR EACH jx SELECT CLAUSE
si = 0
for select in listwrap(query.select):
try:
column_number = len(sql_selects)
select.pull = get_column(column_number)
db_columns = SQLang[select.value].partial_eval().to_sql(
schema)
for column in db_columns:
for t, unsorted_sql in column.sql.items():
json_type = sql_type_to_json_type[t]
if json_type in STRUCT:
continue
column_number = len(sql_selects)
column_alias = _make_column_name(column_number)
sql_selects.append(
sql_alias(unsorted_sql, column_alias))
if startswith_field(schema.path, step) and is_op(
select.value, LeavesOp):
# ONLY FLATTEN primary_nested_path AND PARENTS, NOT CHILDREN
index_to_column[
column_number] = nested_doc_details[
'index_to_column'][
column_number] = ColumnMapping(
push_name=literal_field(
get_property_name(
concat_field(
select.name,
column.name))),
push_child=".",
push_column_name=
get_property_name(
concat_field(
select.name,
column.name)),
push_column=si,
pull=get_column(column_number),
sql=unsorted_sql,
type=json_type,
column_alias=column_alias,
nested_path=nested_path)
si += 1
else:
index_to_column[
column_number] = nested_doc_details[
'index_to_column'][
column_number] = ColumnMapping(
push_name=select.name,
push_child=column.name,
push_column_name=select.name,
push_column=si,
pull=get_column(column_number),
sql=unsorted_sql,
type=json_type,
column_alias=column_alias,
nested_path=nested_path)
finally:
si += 1
where_clause = BooleanOp(query.where).partial_eval().to_sql(
schema, boolean=True)[0].sql.b
unsorted_sql = self._make_sql_for_one_nest_in_set_op(
".", sql_selects, where_clause, active_columns, index_to_column)
for n, _ in self.snowflake.tables:
sorts.append(quote_column(COLUMN + text(index_to_uid[n])))
ordered_sql = ConcatSQL(
(SQL_SELECT, SQL_STAR, SQL_FROM,
sql_iso(unsorted_sql), SQL_ORDERBY, sql_list(sorts), SQL_LIMIT,
quote_value(query.limit)))
result = self.db.query(ordered_sql)
def _accumulate_nested(rows, row, nested_doc_details, parent_doc_id,
parent_id_coord):
"""
:param rows: REVERSED STACK OF ROWS (WITH push() AND pop())
:param row: CURRENT ROW BEING EXTRACTED
:param nested_doc_details: {
"nested_path": wrap_nested_path(nested_path),
"index_to_column": map from column number to column details
"children": all possible direct decedents' nested_doc_details
}
:param parent_doc_id: the id of the parent doc (for detecting when to step out of loop)
:param parent_id_coord: the column number for the parent id (so we ca extract from each row)
:return: the nested property (usually an array)
"""
previous_doc_id = None
doc = Null
output = []
id_coord = nested_doc_details['id_coord']
while True:
doc_id = row[id_coord]
if doc_id == None or (parent_id_coord is not None and
row[parent_id_coord] != parent_doc_id):
rows.append(
row
) # UNDO PREVIOUS POP (RECORD IS NOT A NESTED RECORD OF parent_doc)
return output
if doc_id != previous_doc_id:
previous_doc_id = doc_id
doc = Null
curr_nested_path = nested_doc_details['nested_path'][0]
index_to_column = nested_doc_details[
'index_to_column'].items()
for i, c in index_to_column:
value = row[i]
if is_list(query.select) or is_op(
query.select.value, LeavesOp):
# ASSIGN INNER PROPERTIES
relative_field = concat_field(
c.push_name, c.push_child)
else: # FACT IS EXPECTED TO BE A SINGLE VALUE, NOT AN OBJECT
relative_field = c.push_child
if relative_field == ".":
if exists(value):
doc = value
elif exists(value):
if doc is Null:
doc = Data()
doc[relative_field] = value
for child_details in nested_doc_details['children']:
# EACH NESTED TABLE MUST BE ASSEMBLED INTO A LIST OF OBJECTS
child_id = row[child_details['id_coord']]
if child_id is not None:
nested_value = _accumulate_nested(
rows, row, child_details, doc_id, id_coord)
if nested_value != None:
push_name = child_details['nested_path'][0]
if is_list(query.select) or is_op(
query.select.value, LeavesOp):
# ASSIGN INNER PROPERTIES
relative_field = relative_field(
push_name, curr_nested_path)
else: # FACT IS EXPECTED TO BE A SINGLE VALUE, NOT AN OBJECT
relative_field = "."
if relative_field == ".":
doc = unwraplist(nested_value)
else:
doc[relative_field] = unwraplist(nested_value)
output.append(doc)
try:
row = rows.pop()
except IndexError:
return output
cols = tuple(
[i for i in index_to_column.values() if i.push_name != None])
rows = list(reversed(unwrap(result.data)))
if rows:
row = rows.pop()
data = _accumulate_nested(rows, row, primary_doc_details, None,
None)
else:
data = result.data
if query.format == "cube":
# for f, full_name in self.snowflake.tables:
# if f != '.' or (test_dots(cols) and is_list(query.select)):
# num_rows = len(result.data)
# num_cols = MAX([c.push_column for c in cols]) + 1 if len(cols) else 0
# map_index_to_name = {c.push_column: c.push_column_name for c in cols}
# temp_data = [[None] * num_rows for _ in range(num_cols)]
# for rownum, d in enumerate(result.data):
# for c in cols:
# if c.push_child == ".":
# temp_data[c.push_column][rownum] = c.pull(d)
# else:
# column = temp_data[c.push_column][rownum]
# if column is None:
# column = temp_data[c.push_column][rownum] = {}
# column[c.push_child] = c.pull(d)
# output = Data(
# meta={"format": "cube"},
# data={n: temp_data[c] for c, n in map_index_to_name.items()},
# edges=[{
# "name": "rownum",
# "domain": {
# "type": "rownum",
# "min": 0,
# "max": num_rows,
# "interval": 1
# }
# }]
# )
# return output
if is_list(query.select) or is_op(query.select.value, LeavesOp):
num_rows = len(data)
temp_data = {
c.push_column_name: [None] * num_rows
for c in cols
}
for rownum, d in enumerate(data):
for c in cols:
temp_data[c.push_column_name][rownum] = d[c.push_name]
return Data(meta={"format": "cube"},
data=temp_data,
edges=[{
"name": "rownum",
"domain": {
"type": "rownum",
"min": 0,
"max": num_rows,
"interval": 1
}
}])
else:
num_rows = len(data)
map_index_to_name = {
c.push_column: c.push_column_name
for c in cols
}
temp_data = [data]
return Data(meta={"format": "cube"},
data={
n: temp_data[c]
for c, n in map_index_to_name.items()
},
edges=[{
"name": "rownum",
"domain": {
"type": "rownum",
"min": 0,
"max": num_rows,
"interval": 1
}
}])
elif query.format == "table":
# for f, _ in self.snowflake.tables:
# if frum.endswith(f):
# num_column = MAX([c.push_column for c in cols]) + 1
# header = [None] * num_column
# for c in cols:
# header[c.push_column] = c.push_column_name
#
# output_data = []
# for d in result.data:
# row = [None] * num_column
# for c in cols:
# set_column(row, c.push_column, c.push_child, c.pull(d))
# output_data.append(row)
#
# return Data(
# meta={"format": "table"},
# header=header,
# data=output_data
# )
if is_list(query.select) or is_op(query.select.value, LeavesOp):
column_names = [None] * (max(c.push_column for c in cols) + 1)
for c in cols:
column_names[c.push_column] = c.push_column_name
temp_data = []
for rownum, d in enumerate(data):
row = [None] * len(column_names)
for c in cols:
row[c.push_column] = d[c.push_name]
temp_data.append(row)
return Data(meta={"format": "table"},
header=column_names,
data=temp_data)
else:
column_names = listwrap(query.select).name
return Data(meta={"format": "table"},
header=column_names,
data=[[d] for d in data])
else:
# for f, _ in self.snowflake.tables:
# if frum.endswith(f) or (test_dots(cols) and is_list(query.select)):
# data = []
# for d in result.data:
# row = Data()
# for c in cols:
# if c.push_child == ".":
# row[c.push_name] = c.pull(d)
# elif c.num_push_columns:
# tuple_value = row[c.push_name]
# if not tuple_value:
# tuple_value = row[c.push_name] = [None] * c.num_push_columns
# tuple_value[c.push_child] = c.pull(d)
# else:
# row[c.push_name][c.push_child] = c.pull(d)
#
# data.append(row)
#
# return Data(
# meta={"format": "list"},
# data=data
# )
if is_list(query.select) or is_op(query.select.value, LeavesOp):
temp_data = []
for rownum, d in enumerate(data):
row = {}
for c in cols:
row[c.push_column_name] = d[c.push_name]
temp_data.append(row)
return Data(meta={"format": "list"}, data=temp_data)
else:
return Data(meta={"format": "list"}, data=data)
def _set_op(self, query, frum):
# GET LIST OF COLUMNS
frum_path = split_field(frum)
primary_nested_path = join_field(frum_path[1:])
vars_ = UNION([s.value.vars() for s in listwrap(query.select)])
schema = self.sf.tables[primary_nested_path].schema
nest_to_alias = {
nested_path: "__" + unichr(ord('a') + i) + "__"
for i, (nested_path, sub_table) in enumerate(self.sf.tables.items())
}
active_columns = {".": []}
for cname, cols in schema.items():
if any(startswith_field(cname, v) for v in vars_):
for c in cols:
if c.type in STRUCT:
continue
nest = c.nested_path[0]
active = active_columns.get(nest)
if not active:
active = active_columns[nest] = []
active.append(c)
for nested_path, s in self.sf.tables.items():
for cname, cols in s.schema.items():
if not any(startswith_field(cname, c.names[c.nested_path[0]]) for n, cc in active_columns.items() for c in cc):
for c in cols:
if c.type in STRUCT:
continue
nest = c.nested_path[0]
active = active_columns.get(nest)
if not active:
active = active_columns[nest] = []
active.append(c)
# ANY VARS MENTIONED WITH NO COLUMNS?
for v in vars_:
if not any(startswith_field(cname, v) for cname in schema.keys()):
active_columns["."].append(Column(
names={".": v},
type="null",
es_column=".",
es_index=".",
nested_path=["."]
))
# EVERY COLUMN, AND THE INDEX IT TAKES UP
index_to_column = {} # MAP FROM INDEX TO COLUMN (OR SELECT CLAUSE)
index_to_uid = {} # FROM NESTED PATH TO THE INDEX OF UID
sql_selects = [] # EVERY SELECT CLAUSE (NOT TO BE USED ON ALL TABLES, OF COURSE)
nest_to_alias = {
nested_path: "__" + unichr(ord('a') + i) + "__"
for i, (nested_path, sub_table) in enumerate(self.sf.tables.items())
}
sorts = []
if query.sort:
for s in query.sort:
col = s.value.to_sql(schema)[0]
for t, sql in col.sql.items():
json_type = sql_type_to_json_type[t]
if json_type in STRUCT:
continue
column_number = len(sql_selects)
# SQL HAS ABS TABLE REFERENCE
column_alias = _make_column_name(column_number)
sql_selects.append(sql + " AS " + column_alias)
if s.sort == -1:
sorts.append(column_alias + " IS NOT NULL")
sorts.append(column_alias + " DESC")
else:
sorts.append(column_alias + " IS NULL")
sorts.append(column_alias)
selects = []
primary_doc_details = Data()
# EVERY SELECT STATEMENT THAT WILL BE REQUIRED, NO MATTER THE DEPTH
# WE WILL CREATE THEM ACCORDING TO THE DEPTH REQUIRED
for nested_path, sub_table in self.sf.tables.items():
nested_doc_details = {
"sub_table": sub_table,
"children": [],
"index_to_column": {},
"nested_path": [nested_path] # fake the real nested path, we only look at [0] anyway
}
# INSERT INTO TREE
if not primary_doc_details:
primary_doc_details = nested_doc_details
else:
def place(parent_doc_details):
if startswith_field(nested_path, parent_doc_details['nested_path'][0]):
for c in parent_doc_details['children']:
if place(c):
return True
parent_doc_details['children'].append(nested_doc_details)
place(primary_doc_details)
alias = nested_doc_details['alias'] = nest_to_alias[nested_path]
if nested_path=="." and quoted_GUID in vars_:
column_number = index_to_uid[nested_path] = nested_doc_details['id_coord'] = len(sql_selects)
sql_select = alias + "." + quoted_GUID
sql_selects.append(sql_select + " AS " + _make_column_name(column_number))
index_to_column[column_number] = nested_doc_details['index_to_column'][column_number] = ColumnMapping(
push_name="_id",
push_column_name="_id",
push_column=0,
push_child=".",
sql=sql_select,
pull=get_column(column_number),
type="string",
column_alias=_make_column_name(column_number),
nested_path=[nested_path] # fake the real nested path, we only look at [0] anyway
)
query.select = [s for s in listwrap(query.select) if s.name!="_id"]
# WE ALWAYS ADD THE UID AND ORDER
column_number = index_to_uid[nested_path] = nested_doc_details['id_coord'] = len(sql_selects)
sql_select = alias + "." + quoted_UID
sql_selects.append(sql_select + " AS " + _make_column_name(column_number))
if nested_path !=".":
index_to_column[column_number]=ColumnMapping(
sql=sql_select,
type="number",
nested_path=[nested_path], # fake the real nested path, we only look at [0] anyway
column_alias=_make_column_name(column_number)
)
column_number = len(sql_selects)
sql_select = alias + "." + quote_table(ORDER)
sql_selects.append(sql_select + " AS " + _make_column_name(column_number))
index_to_column[column_number]=ColumnMapping(
sql=sql_select,
type="number",
nested_path=[nested_path], # fake the real nested path, we only look at [0] anyway
column_alias=_make_column_name(column_number)
)
# WE DO NOT NEED DATA FROM TABLES WE REQUEST NOTHING FROM
if nested_path not in active_columns:
continue
if len(active_columns[nested_path]) != 0:
# ADD SQL SELECT COLUMNS FOR EACH jx SELECT CLAUSE
si = 0
for s in listwrap(query.select):
try:
column_number = len(sql_selects)
s.pull = get_column(column_number)
db_columns = s.value.to_sql(schema)
if isinstance(s.value, LeavesOp):
for column in db_columns:
if isinstance(column.nested_path, list):
column.nested_path=column.nested_path[0]
if column.nested_path and column.nested_path!=nested_path:
continue
for t, unsorted_sql in column.sql.items():
json_type = sql_type_to_json_type[t]
if json_type in STRUCT:
continue
column_number = len(sql_selects)
# SQL HAS ABS TABLE REFERENCE
column_alias = _make_column_name(column_number)
if concat_field(alias, unsorted_sql) in selects and len(unsorted_sql.split())==1:
continue
selects.append(concat_field(alias, unsorted_sql))
sql_selects.append(alias + "." + unsorted_sql + " AS " + column_alias)
index_to_column[column_number] = nested_doc_details['index_to_column'][column_number] = ColumnMapping(
push_name=literal_field(get_property_name(concat_field(s.name, column.name))),
push_column_name=get_property_name(concat_field(s.name, column.name)),
push_column=si,
push_child=".",
pull=get_column(column_number),
sql=unsorted_sql,
type=json_type,
column_alias=column_alias,
nested_path=[nested_path] # fake the real nested path, we only look at [0] anyway
)
si += 1
else:
for column in db_columns:
if isinstance(column.nested_path, list):
column.nested_path=column.nested_path[0]
if column.nested_path and column.nested_path!=nested_path:
continue
for t, unsorted_sql in column.sql.items():
json_type = sql_type_to_json_type[t]
if json_type in STRUCT:
continue
column_number = len(sql_selects)
# SQL HAS ABS TABLE REFERENCE
column_alias = _make_column_name(column_number)
if concat_field(alias, unsorted_sql) in selects and len(unsorted_sql.split())==1:
continue
selects.append(concat_field(alias, unsorted_sql))
sql_selects.append(alias + "." + unsorted_sql + " AS " + column_alias)
index_to_column[column_number] = nested_doc_details['index_to_column'][column_number] = ColumnMapping(
push_name=s.name,
push_column_name=s.name,
push_column=si,
push_child=column.name,
pull=get_column(column_number),
sql=unsorted_sql,
type=json_type,
column_alias=column_alias,
nested_path=[nested_path]
# fake the real nested path, we only look at [0] anyway
)
finally:
si += 1
elif startswith_field(nested_path, primary_nested_path):
# ADD REQUIRED COLUMNS, FOR DEEP STUFF
for ci, c in enumerate(active_columns[nested_path]):
if c.type in STRUCT:
continue
column_number = len(sql_selects)
nested_path = c.nested_path
unsorted_sql = nest_to_alias[nested_path[0]] + "." + quote_table(c.es_column)
column_alias = _make_column_name(column_number)
if concat_field(alias, unsorted_sql) in selects and len(unsorted_sql.split())==1:
continue
selects.append(concat_field(alias, unsorted_sql))
sql_selects.append(alias + "." + unsorted_sql + " AS " + column_alias)
index_to_column[column_number] = nested_doc_details['index_to_column'][column_number] = ColumnMapping(
push_name=s.name,
push_column_name=s.name,
push_column=si,
push_child=relative_field(c.names["."], s.name),
pull=get_column(column_number),
sql=unsorted_sql,
type=c.type,
column_alias=column_alias,
nested_path=nested_path
)
where_clause = query.where.to_sql(schema, boolean=True)[0].sql.b
unsorted_sql = self._make_sql_for_one_nest_in_set_op(
".",
sql_selects,
where_clause,
active_columns,
index_to_column
)
for n, _ in self.sf.tables.items():
sorts.append(COLUMN + text_type(index_to_uid[n]))
ordered_sql = (
"SELECT * FROM (\n" +
unsorted_sql +
"\n)" +
"\nORDER BY\n" + ",\n".join(sorts) +
"\nLIMIT " + quote_value(query.limit)
)
self.db.create_new_functions() #creating new functions: regexp
result = self.db.query(ordered_sql)
def _accumulate_nested(rows, row, nested_doc_details, parent_doc_id, parent_id_coord):
"""
:param rows: REVERSED STACK OF ROWS (WITH push() AND pop())
:param row: CURRENT ROW BEING EXTRACTED
:param nested_doc_details: {
"nested_path": wrap_nested_path(nested_path),
"index_to_column": map from column number to column details
"children": all possible direct decedents' nested_doc_details
}
:param parent_doc_id: the id of the parent doc (for detecting when to step out of loop)
:param parent_id_coord: the column number for the parent id (so we ca extract from each row)
:return: the nested property (usually an array)
"""
previous_doc_id = None
doc = Null
output = []
id_coord = nested_doc_details['id_coord']
while True:
doc_id = row[id_coord]
if doc_id == None or (parent_id_coord is not None and row[parent_id_coord] != parent_doc_id):
rows.append(row) # UNDO PREVIOUS POP (RECORD IS NOT A NESTED RECORD OF parent_doc)
return output
if doc_id != previous_doc_id:
previous_doc_id = doc_id
doc = Null
curr_nested_path = nested_doc_details['nested_path'][0]
index_to_column = nested_doc_details['index_to_column'].items()
if index_to_column:
for i, c in index_to_column:
value = row[i]
if value == None:
continue
if value == '':
continue
if isinstance(query.select, list) or isinstance(query.select.value, LeavesOp):
# ASSIGN INNER PROPERTIES
relative_path=join_field([c.push_name]+split_field(c.push_child))
else: # FACT IS EXPECTED TO BE A SINGLE VALUE, NOT AN OBJECT
relative_path=c.push_child
if relative_path == ".":
doc = value
elif doc is Null:
doc = Data()
doc[relative_path] = value
else:
doc[relative_path] = value
for child_details in nested_doc_details['children']:
# EACH NESTED TABLE MUST BE ASSEMBLED INTO A LIST OF OBJECTS
child_id = row[child_details['id_coord']]
if child_id is not None:
nested_value = _accumulate_nested(rows, row, child_details, doc_id, id_coord)
if nested_value:
push_name = child_details['nested_path'][0]
if isinstance(query.select, list) or isinstance(query.select.value, LeavesOp):
# ASSIGN INNER PROPERTIES
relative_path=relative_field(push_name, curr_nested_path)
else: # FACT IS EXPECTED TO BE A SINGLE VALUE, NOT AN OBJECT
relative_path="."
if relative_path == "." and doc is Null:
doc = nested_value
elif relative_path == ".":
doc[push_name] = unwraplist([v[push_name] for v in nested_value])
elif doc is Null:
doc = Data()
doc[relative_path] = unwraplist(nested_value)
else:
doc[relative_path] = unwraplist(nested_value)
output.append(doc)
try:
row = rows.pop()
except IndexError:
return output
cols = tuple([i for i in index_to_column.values() if i.push_name != None])
rows = list(reversed(unwrap(result.data)))
if rows:
row = rows.pop()
data = _accumulate_nested(rows, row, primary_doc_details, None, None)
else:
data = result.data
if query.format == "cube":
for f, _ in self.sf.tables.items():
if frum.endswith(f) or (test_dots(cols) and isinstance(query.select, list)):
num_rows = len(result.data)
num_cols = MAX([c.push_column for c in cols]) + 1 if len(cols) else 0
map_index_to_name = {c.push_column: c.push_column_name for c in cols}
temp_data = [[None]*num_rows for _ in range(num_cols)]
for rownum, d in enumerate(result.data):
for c in cols:
if c.push_child == ".":
temp_data[c.push_column][rownum] = c.pull(d)
else:
column = temp_data[c.push_column][rownum]
if column is None:
column = temp_data[c.push_column][rownum] = Data()
column[c.push_child] = c.pull(d)
output = Data(
meta={"format": "cube"},
data={n: temp_data[c] for c, n in map_index_to_name.items()},
edges=[{
"name": "rownum",
"domain": {
"type": "rownum",
"min": 0,
"max": num_rows,
"interval": 1
}
}]
)
return output
if isinstance(query.select, list) or isinstance(query.select.value, LeavesOp):
num_rows = len(data)
map_index_to_name = {c.push_column: c.push_column_name for c in cols}
temp_data = Data()
for rownum, d in enumerate(data):
for k, v in d.items():
if temp_data[k] == None:
temp_data[k] = [None] * num_rows
temp_data[k][rownum] = v
return Data(
meta={"format": "cube"},
data={n: temp_data[literal_field(n)] for c, n in map_index_to_name.items()},
edges=[{
"name": "rownum",
"domain": {
"type": "rownum",
"min": 0,
"max": num_rows,
"interval": 1
}
}]
)
else:
num_rows = len(data)
map_index_to_name = {c.push_column: c.push_column_name for c in cols}
temp_data = [data]
return Data(
meta={"format": "cube"},
data={n: temp_data[c] for c, n in map_index_to_name.items()},
edges=[{
"name": "rownum",
"domain": {
"type": "rownum",
"min": 0,
"max": num_rows,
"interval": 1
}
}]
)
elif query.format == "table":
for f, _ in self.sf.tables.items():
if frum.endswith(f):
num_column = MAX([c.push_column for c in cols])+1
header = [None]*num_column
for c in cols:
header[c.push_column] = c.push_column_name
output_data = []
for d in result.data:
row = [None] * num_column
for c in cols:
set_column(row, c.push_column, c.push_child, c.pull(d))
output_data.append(row)
return Data(
meta={"format": "table"},
header=header,
data=output_data
)
if isinstance(query.select, list) or isinstance(query.select.value, LeavesOp):
num_rows = len(data)
column_names= [None]*(max(c.push_column for c in cols) + 1)
for c in cols:
column_names[c.push_column] = c.push_column_name
temp_data = []
for rownum, d in enumerate(data):
row =[None] * len(column_names)
for i, (k, v) in enumerate(sorted(d.items())):
for c in cols:
if k==c.push_name:
row[c.push_column] = v
temp_data.append(row)
return Data(
meta={"format": "table"},
header=column_names,
data=temp_data
)
else:
column_names = listwrap(query.select).name
return Data(
meta={"format": "table"},
header=column_names,
data=[[d] for d in data]
)
else:
for f, _ in self.sf.tables.items():
if frum.endswith(f) or (test_dots(cols) and isinstance(query.select, list)):
data = []
for d in result.data:
row = Data()
for c in cols:
if c.push_child == ".":
row[c.push_name] = c.pull(d)
elif c.num_push_columns:
tuple_value = row[c.push_name]
if not tuple_value:
tuple_value = row[c.push_name] = [None] * c.num_push_columns
tuple_value[c.push_child] = c.pull(d)
elif not isinstance(query.select, list): # select is value type
row[c.push_child]=c.pull(d)
else:
row[c.push_name][c.push_child] = c.pull(d)
data.append(row)
return Data(
meta={"format": "list"},
data=data
)
if isinstance(query.select, list) or isinstance(query.select.value, LeavesOp):
temp_data=[]
for rownum, d in enumerate(data):
row = {}
for k, v in d.items():
for c in cols:
if c.push_name==c.push_column_name==k:
row[c.push_column_name] = v
elif c.push_name==k and c.push_column_name!=k:
row[c.push_column_name] = v
temp_data.append(row)
return Data(
meta={"format": "list"},
data=temp_data
)
else:
return Data(
meta={"format": "list"},
data=data
)
def __init__(self, var):
Expression.__init__(self, "", None)
if not is_variable_name(var):
Log.error("Expecting a variable name")
self.var = get_property_name(var)
