def insert_list(self, table_name, records):
if not records:
return
columns = set()
for r in records:
columns |= set(r.keys())
columns = jx.sort(columns)
try:
self.execute(
"DELETE FROM " + self.quote_column(table_name) + SQL_WHERE +
"_id IN {{ids}}",
{"ids": self.quote_column([r["_id"] for r in records])})
command = (SQL_INSERT + self.quote_column(table_name) +
sql_iso(sql_list(self.quote_column(k)
for k in columns)) + SQL_VALUES +
sql_iso(
sql_list(
self.quote_value(r.get(k, None))
for k in columns for r in records)))
self.execute(command)
except Exception as e:
Log.error("problem with insert", e)
def to_sql(self, schema, not_null=False, boolean=False):
lhs = SQLang[self.lhs].partial_eval()
rhs = SQLang[self.rhs].partial_eval()
lhs_sql = lhs.to_sql(schema, not_null=True)
rhs_sql = rhs.to_sql(schema, not_null=True)
if is_literal(rhs) and lhs_sql[0].sql.b != None and rhs.value in ("T",
"F"):
rhs_sql = BooleanOp(rhs).to_sql(schema)
if is_literal(lhs) and rhs_sql[0].sql.b != None and lhs.value in ("T",
"F"):
lhs_sql = BooleanOp(lhs).to_sql(schema)
if len(lhs_sql) != len(rhs_sql):
Log.error("lhs and rhs have different dimensionality!?")
acc = []
for l, r in zip(lhs_sql, rhs_sql):
for t in "bsnj":
if r.sql[t] == None:
if l.sql[t] == None:
pass
else:
acc.append(ConcatSQL(l.sql[t], SQL_IS_NULL))
elif l.sql[t] == None:
acc.append(ConcatSQL(r.sql[t], SQL_IS_NULL))
else:
acc.append(
ConcatSQL(sql_iso(l.sql[t]), SQL_EQ,
sql_iso(r.sql[t])))
if not acc:
return FALSE.to_sql(schema)
else:
return wrap([{"name": ".", "sql": {"b": JoinSQL(SQL_OR, acc)}}])
def to_sql(self, schema, not_null=False, boolean=False):
value = SQLang[self.expr].partial_eval()
missing_value = value.missing().partial_eval()
if not is_op(missing_value, MissingOp):
return missing_value.to_sql(schema)
value_sql = value.to_sql(schema)
if len(value_sql) > 1:
return wrap([{"name": ".", "sql": {"b": SQL_FALSE}}])
acc = []
for c in value_sql:
for t, v in c.sql.items():
if t in "bn":
acc.append(ConcatSQL(sql_iso(v), SQL_IS_NULL))
if t == "s":
acc.append(ConcatSQL(
sql_iso(sql_iso(v), SQL_IS_NULL),
SQL_OR,
sql_iso(sql_iso(v), SQL_EQ, SQL_EMPTY_STRING)
))
if not acc:
return wrap([{"name": ".", "sql": {"b": SQL_TRUE}}])
else:
return wrap([{"name": ".", "sql": {"b": SQL_AND.join(acc)}}])
def sql_create(table, properties, primary_key=None, unique=None):
"""
:param table: NAME OF THE TABLE TO CREATE
:param properties: DICT WITH {name: type} PAIRS (type can be plain text)
:param primary_key: COLUMNS THAT MAKE UP THE PRIMARY KEY
:param unique: COLUMNS THAT SHOULD BE UNIQUE
:return:
"""
acc = [
SQL_CREATE,
quote_column(table),
SQL_OP,
sql_list([quote_column(k) + SQL(v) for k, v in properties.items()]),
]
if primary_key:
acc.append(SQL_COMMA),
acc.append(SQL(" PRIMARY KEY ")),
acc.append(
sql_iso(sql_list([quote_column(c)
for c in listwrap(primary_key)])))
if unique:
acc.append(SQL_COMMA),
acc.append(SQL(" UNIQUE ")),
acc.append(
sql_iso(sql_list([quote_column(c) for c in listwrap(unique)])))
acc.append(SQL_CP)
return ConcatSQL(*acc)
def to_bq(self, schema, not_null=False, boolean=False):
lhs = BQLang[self.lhs].to_bq(schema)[0].sql.n
rhs = BQLang[self.rhs].to_bq(schema)[0].sql.n
d = BQLang[self.default].to_bq(schema)[0].sql.n
if lhs and rhs:
if d == None:
return wrap(
[{"name": ".", "sql": {"n": sql_iso(lhs) + " / " + sql_iso(rhs)}}]
)
else:
return wrap(
[
{
"name": ".",
"sql": {
"n": sql_coalesce(
[sql_iso(lhs) + " / " + sql_iso(rhs), d]
)
},
}
]
)
else:
return Null
def _insert(self, collection):
for nested_path, details in collection.items():
active_columns = wrap(list(details.active_columns))
rows = details.rows
num_rows = len(rows)
table_name = concat_field(self.name, nested_path)
if table_name == self.name:
# DO NOT REQUIRE PARENT OR ORDER COLUMNS
meta_columns = [GUID, UID]
else:
meta_columns = [UID, PARENT, ORDER]
all_columns = meta_columns + active_columns.es_column # ONLY THE PRIMITIVE VALUE COLUMNS
command = ConcatSQL(
SQL_INSERT,
quote_column(table_name),
sql_iso(sql_list(map(quote_column, all_columns))),
SQL_VALUES,
sql_list(
sql_iso(sql_list(quote_value(row.get(c)) for c in all_columns))
for row in unwrap(rows)
)
)
with self.db.transaction() as t:
t.execute(command)
def _nest_column(self, column):
new_path, type_ = untyped_column(column.es_column)
if type_ != SQL_NESTED_TYPE:
Log.error("only nested types can be nested")
destination_table = concat_field(self.fact_name, new_path)
existing_table = concat_field(self.fact_name, column.nested_path[0])
# FIND THE INNER COLUMNS WE WILL BE MOVING
moving_columns = []
for c in self.columns:
if destination_table != column.es_index and column.es_column == c.es_column:
moving_columns.append(c)
c.nested_path = new_path
# TODO: IF THERE ARE CHILD TABLES, WE MUST UPDATE THEIR RELATIONS TOO?
# LOAD THE COLUMNS
data = self.namespace.db.about(destination_table)
if not data:
# DEFINE A NEW TABLE
command = (
SQL_CREATE + quote_column(destination_table) + sql_iso(sql_list([
quoted_UID + "INTEGER",
quoted_PARENT + "INTEGER",
quoted_ORDER + "INTEGER",
"PRIMARY KEY " + sql_iso(quoted_UID),
"FOREIGN KEY " + sql_iso(quoted_PARENT) + " REFERENCES " + quote_column(existing_table) + sql_iso(quoted_UID)
]))
)
with self.namespace.db.transaction() as t:
t.execute(command)
self.add_table([new_path]+column.nested_path)
# TEST IF THERE IS ANY DATA IN THE NEW NESTED ARRAY
if not moving_columns:
return
column.es_index = destination_table
with self.namespace.db.transaction() as t:
t.execute(
"ALTER TABLE " + quote_column(destination_table) +
" ADD COLUMN " + quote_column(column.es_column) + " " + column.es_type
)
# Deleting parent columns
for col in moving_columns:
column = col.es_column
tmp_table = "tmp_" + existing_table
columns = list(map(text, t.query(SQL_SELECT + SQL_STAR + SQL_FROM + quote_column(existing_table) + SQL_LIMIT + SQL_ZERO).header))
t.execute(
"ALTER TABLE " + quote_column(existing_table) +
" RENAME TO " + quote_column(tmp_table)
)
t.execute(
SQL_CREATE + quote_column(existing_table) + SQL_AS +
SQL_SELECT + sql_list([quote_column(c) for c in columns if c != column]) +
SQL_FROM + quote_column(tmp_table)
)
t.execute("DROP TABLE " + quote_column(tmp_table))
def to_bq(self, schema, not_null=False, boolean=False):
value = self.value.to_bq(schema, not_null=True)[0].sql.s
start = self.start.to_bq(schema, not_null=True)[0].sql.n
if self.length is NULL:
sql = "SUBSTR" + sql_iso(sql_list([value, start]))
else:
length = self.length.to_bq(schema, not_null=True)[0].sql.n
sql = "SUBSTR" + sql_iso(sql_list([value, start, length]))
return wrap([{"name": ".", "sql": sql}])
def sql_insert(table, records):
records = listwrap(records)
keys = list({k for r in records for k in r.keys()})
return ConcatSQL(
SQL_INSERT,
quote_column(table),
sql_iso(sql_list(map(quote_column, keys))),
SQL_VALUES,
sql_list(
sql_iso(sql_list([quote_value(r[k]) for k in keys]))
for r in records),
)
def insert(self, table_name, record):
keys = list(record.keys())
try:
command = (
SQL_INSERT + quote_column(table_name) +
sql_iso(sql_list([quote_column(k) for k in keys])) +
SQL_VALUES +
sql_iso(sql_list([quote_value(record[k]) for k in keys]))
)
self.execute(command)
except Exception as e:
Log.error("problem with record: {{record}}", record=record, cause=e)
def _inequality_to_bq(self, schema, not_null=False, boolean=False, many=True):
op, identity = _sql_operators[self.op]
lhs = NumberOp(self.lhs).partial_eval().to_bq(schema, not_null=True)[0].sql.n
rhs = NumberOp(self.rhs).partial_eval().to_bq(schema, not_null=True)[0].sql.n
sql = sql_iso(lhs) + op + sql_iso(rhs)
output = BQLScript(
data_type=BOOLEAN,
expr=sql,
frum=self,
miss=OrOp([self.lhs.missing(), self.rhs.missing()]),
schema=schema,
)
return output
def _load_functions(self):
global _load_extension_warning_sent
library_loc = File.new_instance(sys.modules[__name__].__file__,
"../..")
full_path = File.new_instance(
library_loc, "vendor/sqlite/libsqlitefunctions.so").abspath
try:
trace = get_stacktrace(0)[0]
if self.upgrade:
if os.name == "nt":
file = File.new_instance(
trace["file"],
"../../vendor/sqlite/libsqlitefunctions.so")
else:
file = File.new_instance(
trace["file"],
"../../vendor/sqlite/libsqlitefunctions")
full_path = file.abspath
self.db.enable_load_extension(True)
self.db.execute(
text(SQL_SELECT + "load_extension" +
sql_iso(quote_value(full_path))))
except Exception as e:
if not _load_extension_warning_sent:
_load_extension_warning_sent = True
Log.warning(
"Could not load {{file}}, doing without. (no SQRT for you!)",
file=full_path,
cause=e,
)
def insert_new(self, table_name, candidate_key, new_record):
candidate_key = listwrap(candidate_key)
condition = sql_eq(**{k: new_record[k] for k in candidate_key})
command = (
SQL_INSERT + quote_column(table_name) +
sql_iso(sql_list(quote_column(k) for k in new_record.keys())) +
SQL_SELECT + "a.*" + SQL_FROM + sql_iso(SQL_SELECT + sql_list([
quote_value(v) + " " + quote_column(k)
for k, v in new_record.items()
]) + SQL_FROM + "DUAL") + " a" + SQL_LEFT_JOIN +
sql_iso(SQL_SELECT + "'dummy' exist " + SQL_FROM +
quote_column(table_name) + SQL_WHERE + condition +
SQL_LIMIT + SQL_ONE) + " b ON " + SQL_TRUE + SQL_WHERE +
" exist " + SQL_IS_NULL)
self.execute(command, {})
def to_bq(self, schema, not_null=False, boolean=False):
value = self.value.to_bq(schema, not_null=True)[0].sql.s
find = self.find.to_bq(schema, not_null=True)[0].sql.s
return wrap(
[{"name": ".", "sql": {"n": "INSTR" + sql_iso(sql_list([value, find]))}}]
)
def to_bq(self, schema, not_null=False, boolean=False):
acc = []
for term in self.terms:
sqls = BQLang[term].to_bq(schema)
if len(sqls) > 1:
acc.append(SQL_TRUE)
else:
for t, v in sqls[0].sql.items():
if t in ["b", "s", "n"]:
acc.append(
ConcatSQL(
SQL_CASE,
SQL_WHEN,
sql_iso(v),
SQL_IS_NULL,
SQL_THEN,
SQL_ZERO,
SQL_ELSE,
SQL_ONE,
SQL_END,
))
else:
acc.append(SQL_TRUE)
if not acc:
return wrap([{}])
else:
return wrap([{"nanme": ".", "sql": {"n": SQL("+").join(acc)}}])
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 to_bq(self, schema, not_null=False, boolean=False):
default = self.default.to_bq(schema)
if len(self.terms) == 0:
return default
default = coalesce(default[0].sql.s, SQL_NULL)
sep = BQLang[self.separator].to_bq(schema)[0].sql.s
acc = []
for t in self.terms:
t = BQLang[t]
missing = t.missing().partial_eval()
term = t.to_bq(schema, not_null=True)[0].sql
if term.s:
term_sql = term.s
elif term.n:
term_sql = "cast(" + term.n + " as text)"
else:
term_sql = (SQL_CASE + SQL_WHEN + term.b + SQL_THEN +
quote_value("true") + SQL_ELSE +
quote_value("false") + SQL_END)
if isinstance(missing, TrueOp):
acc.append(SQL_EMPTY_STRING)
elif missing:
acc.append(
SQL_CASE + SQL_WHEN +
sql_iso(missing.to_bq(schema, boolean=True)[0].sql.b) +
SQL_THEN + SQL_EMPTY_STRING + SQL_ELSE +
sql_iso(sql_concat_text([sep, term_sql])) + SQL_END)
else:
acc.append(sql_concat_text([sep, term_sql]))
expr_ = "SUBSTR" + sql_iso(
sql_list([
sql_concat_text(acc),
LengthOp(self.separator).to_bq(schema)[0].sql.n + SQL("+1"),
]))
return BQLScript(
expr=expr_,
data_type=STRING,
frum=self,
miss=self.missing(),
many=False,
schema=schema,
)
def to_bq(self, schema, not_null=False, boolean=False):
v = self.value.to_bq(schema)[0].sql
return wrap([{
"name": ".",
"sql": {
"n": "UNIX_TIMESTAMP" + sql_iso(v.n)
}
}])
def _window_op(self, query, window):
# http://www2.sqlite.org/cvstrac/wiki?p=UnsupportedSqlAnalyticalFunctions
if window.value == "rownum":
return ("ROW_NUMBER()-1 OVER (" + " PARTITION BY " +
sql_iso(sql_list(window.edges.values)) + SQL_ORDERBY +
sql_iso(sql_list(window.edges.sort)) + ") AS " +
quote_column(window.name))
range_min = text(coalesce(window.range.min, "UNBOUNDED"))
range_max = text(coalesce(window.range.max, "UNBOUNDED"))
return (sql_aggs[window.aggregate] +
sql_iso(window.value.to_sql(schema)) + " OVER (" +
" PARTITION BY " + sql_iso(sql_list(window.edges.values)) +
SQL_ORDERBY + sql_iso(sql_list(window.edges.sort)) +
" ROWS BETWEEN " + range_min + " PRECEDING AND " + range_max +
" FOLLOWING " + ") AS " + quote_column(window.name))
def basic_multiop_to_bq(self,
schema,
not_null=False,
boolean=False,
many=False):
op, identity = _sql_operators[self.op.split("basic.")[1]]
sql = op.join(
sql_iso(BQLang[t].to_bq(schema)[0].sql.n) for t in self.terms)
return wrap([{"name": ".", "sql": {"n": sql}}])
def about(self, table_name):
"""
:param table_name: TABLE IF INTEREST
:return: SOME INFORMATION ABOUT THE TABLE
(cid, name, dtype, notnull, dfft_value, pk) tuples
"""
details = self.query("PRAGMA table_info" +
sql_iso(quote_column(table_name)))
return details.data
def to_bq(self, schema, not_null=False, boolean=False):
value = BQLang[self.value].to_bq(schema, not_null=True)[0].sql.s
start = (AddOp([self.start, Literal(1)
]).partial_eval().to_bq(schema,
not_null=True)[0].sql.n)
length = (SubOp([self.end, self.start
]).partial_eval().to_bq(schema,
not_null=True)[0].sql.n)
sql = "SUBSTR" + sql_iso(value + "," + start + ", " + length)
return wrap([{"name": ".", "sql": {"s": sql}}])
def to_bq(self, schema, not_null=False, boolean=False):
terms = [
BQLang[t].partial_eval().to_bq(schema)[0].sql.n for t in self.terms
]
return wrap([{
"name": ".",
"sql": {
"n": "min" + sql_iso((sql_list(terms)))
}
}])
def _binaryop_to_bq(self, schema, not_null=False, boolean=False, many=True):
op, identity = _sql_operators[self.op]
lhs = NumberOp(self.lhs).partial_eval().to_bq(schema,
not_null=True)[0].sql.n
rhs = NumberOp(self.rhs).partial_eval().to_bq(schema,
not_null=True)[0].sql.n
script = sql_iso(lhs) + op + sql_iso(rhs)
if not_null:
sql = script
else:
missing = OrOp([self.lhs.missing(), self.rhs.missing()]).partial_eval()
if missing is FALSE:
sql = script
else:
sql = ("CASE WHEN " +
missing.to_bq(schema, boolean=True)[0].sql.b +
" THEN NULL ELSE " + script + " END")
return wrap([{"name": ".", "sql": {"n": sql}}])
def insert_list(self, table_name, records):
if not records:
return
keys = set()
for r in records:
keys |= set(r.keys())
keys = jx.sort(keys)
try:
command = (SQL_INSERT + quote_column(table_name) +
sql_iso(sql_list([quote_column(k) for k in keys])) +
SQL_VALUES + sql_list(
sql_iso(sql_list([quote_value(r[k]) for k in keys]))
for r in records))
self.execute(command)
except Exception as e:
Log.error("problem with record: {{record}}",
record=records,
cause=e)
def to_sql(self, schema, not_null=False, boolean=False):
field = self.field.to_sql(schema)[0].sql
acc = []
for t, v in field.items():
if t in "bns":
acc.append(sql_iso(v + SQL_IS_NOT_NULL))
if not acc:
return wrap([{"name": ".", "sql": {"b": SQL_FALSE}}])
else:
return wrap([{"name": ".", "sql": {"b": SQL_OR.join(acc)}}])
def to_bq(self, schema, not_null=False, boolean=False):
lhs = BQLang[self.lhs].to_bq(schema)
rhs = BQLang[self.rhs].to_bq(schema)
acc = []
if len(lhs) != len(rhs):
Log.error("lhs and rhs have different dimensionality!?")
for l, r in zip(lhs, rhs):
for t in "bsnj":
if l.sql[t] == None:
if r.sql[t] == None:
pass
else:
acc.append(sql_iso(r.sql[t]) + SQL_IS_NULL)
elif l.sql[t] is ZERO:
if r.sql[t] == None:
acc.append(SQL_FALSE)
elif r.sql[t] is ZERO:
Log.error(
"Expecting expression to have been simplified already"
)
else:
acc.append(r.sql[t])
else:
if r.sql[t] == None:
acc.append(sql_iso(l.sql[t]) + SQL_IS_NULL)
elif r.sql[t] is ZERO:
acc.append(l.sql[t])
else:
acc.append(
sql_iso(l.sql[t]) + " = " + sql_iso(r.sql[t]))
if not acc:
return FALSE.to_bq(schema)
else:
return BQLScript(
expr=SQL_OR.join(acc),
frum=self,
data_type=BOOLEAN,
miss=FALSE,
schema=schema,
)
def to_bq(self, schema, not_null=False, boolean=False):
if not is_op(self.superset, Literal):
Log.error("Not supported")
j_value = json2value(self.superset.json)
if j_value:
var = BQLang[self.value].to_bq(schema)
sql = SQL_OR.join(
sql_iso(ConcatSQL(v, SQL_IN, quote_list(j_value)))
for t, v in var[0].sql.items())
else:
sql = SQL_FALSE
return wrap([{"name": ".", "sql": {"b": sql}}])
def to_bq(self, schema, not_null=False, boolean=False):
test = BQLang[self.term].missing().to_bq(schema, boolean=True)[0].sql.b
value = BQLang[self.term].to_bq(schema, not_null=True)[0].sql
acc = []
for t, v in value.items():
if t == "b":
acc.append(SQL_CASE + SQL_WHEN + sql_iso(test) + SQL_THEN +
SQL_NULL + SQL_WHEN + sql_iso(v) + SQL_THEN +
"'true'" + SQL_ELSE + "'false'" + SQL_END)
elif t == "s":
acc.append(v)
else:
acc.append("RTRIM(RTRIM(CAST" +
sql_iso(v + " as TEXT), " + quote_value("0")) +
", " + quote_value(".") + ")")
if not acc:
return wrap([{}])
elif len(acc) == 1:
return wrap([{"name": ".", "sql": {"s": acc[0]}}])
else:
return wrap([{"name": ".", "sql": {"s": sql_coalesce(acc)}}])
def to_sql(self, schema, not_null=False, boolean=False):
not_expr = NotOp(BooleanOp(self.term)).partial_eval()
if is_op(not_expr, NotOp):
return wrap([{
"name": ".",
"sql": {
"b":
"NOT " + sql_iso(not_expr.term.to_sql(schema)[0].sql.b)
},
}])
else:
return not_expr.to_sql(schema)