def quote_value(self, value):
"""
convert values to mysql code for the same
mostly delegate directly to the mysql lib, but some exceptions exist
"""
try:
if value == None:
return SQL_NULL
elif isinstance(value, SQL):
return self.quote_sql(value.template, value.param)
elif isinstance(value, text_type):
return SQL("'" + value.replace("'", "''") + "'")
elif isinstance(value, Mapping):
return self.quote_value(json_encode(value))
elif Math.is_number(value):
return SQL(text_type(value))
elif isinstance(value, datetime):
return SQL("str_to_date('" +
value.strftime("%Y%m%d%H%M%S.%f") +
"', '%Y%m%d%H%i%s.%f')")
elif isinstance(value, Date):
return SQL("str_to_date('" + value.format("%Y%m%d%H%M%S.%f") +
"', '%Y%m%d%H%i%s.%f')")
elif hasattr(value, '__iter__'):
return self.quote_value(json_encode(value))
else:
return self.quote_value(text_type(value))
except Exception as e:
Log.error("problem quoting SQL {{value}}",
value=repr(value),
cause=e)
def quote_value(value):
"""
convert values to mysql code for the same
mostly delegate directly to the mysql lib, but some exceptions exist
"""
try:
if value == None:
return SQL_NULL
elif isinstance(value, SQL):
return value
elif is_text(value):
return SQL("'" + "".join(ESCAPE_DCT.get(c, c)
for c in value) + "'")
elif is_data(value):
return quote_value(json_encode(value))
elif isinstance(value, datetime):
return SQL("str_to_date('" + value.strftime("%Y%m%d%H%M%S.%f") +
"', '%Y%m%d%H%i%s.%f')")
elif isinstance(value, Date):
return SQL("str_to_date('" + value.format("%Y%m%d%H%M%S.%f") +
"', '%Y%m%d%H%i%s.%f')")
elif is_number(value):
return SQL(text(value))
elif hasattr(value, '__iter__'):
return quote_value(json_encode(value))
else:
return quote_value(text(value))
except Exception as e:
Log.error("problem quoting SQL {{value}}", value=repr(value), cause=e)
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 quote_column(column_name, table=None):
if not isinstance(column_name, unicode):
Log.error("expecting a name")
if table != None:
return SQL(quote(table) + "." + quote(column_name))
else:
if _no_need_to_quote.match(column_name):
return SQL(column_name)
return SQL(quote(column_name))
def quote_value(self, value):
if value == None:
return SQL("NULL")
if isinstance(value, list):
json = convert.value2json(value)
return self.quote_value(json)
if isinstance(value, basestring) and len(value) > 256:
value = value[:256]
return SQL(adapt(value))
def single(col, r):
min = coalesce(r["gte"], r[">="])
max = coalesce(r["lte"], r["<="])
if min and max:
# SPECIAL CASE (BETWEEN)
return db.quote_column(col) + SQL(
" BETWEEN ") + db.quote_value(min) + SQL(
" AND ") + db.quote_value(max)
else:
return " AND ".join(
db.quote_column(col) + name2sign[sign] +
db.quote_value(value) for sign, value in r.items())
def quote_column(column_name, table=None):
if isinstance(column_name, SQL):
return column_name
if not is_text(column_name):
Log.error("expecting a name")
if table != None:
return SQL(" d" + quote(table) + "." + quote(column_name) + " ")
else:
if _no_need_to_quote.match(column_name):
return SQL(" " + column_name + " ")
return SQL(" " + quote(column_name) + " ")
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(aggregates[s.aggregate].replace("{{code}}", s.value) + " AS " + self.db.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 = aggregates[s0.aggregate].replace("{{code}}", s0.value) + " AS " + self.db.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 quote_column(column_name, table=None):
if column_name == None:
Log.error("missing column_name")
elif isinstance(column_name, text_type):
if table:
return SQL(
quote_column(table).rstrip() + "." +
quote_column(column_name).lstrip())
elif _no_need_to_quote.match(column_name):
return SQL(" " + column_name + " ")
else:
return SQL(" `" + column_name.replace("`", "``") +
"` ") # MY SQL QUOTE OF COLUMN NAMES
Log.error("no other forms expected")
def quote_value(self, value):
"""
convert values to mysql code for the same
mostly delegate directly to the mysql lib, but some exceptions exist
"""
try:
if value == None:
return SQL("NULL")
elif isinstance(value, SQL):
if not value.param:
# value.template CAN BE MORE THAN A TEMPLATE STRING
return self.quote_sql(value.template)
param = {k: self.quote_sql(v) for k, v in value.param.items()}
return SQL(expand_template(value.template, param))
elif isinstance(value, basestring):
return SQL(self.db.literal(value))
elif isinstance(value, Mapping):
return SQL(self.db.literal(json_encode(value)))
elif Math.is_number(value):
return SQL(text_type(value))
elif isinstance(value, datetime):
return SQL("str_to_date('" +
value.strftime("%Y%m%d%H%M%S.%f") +
"', '%Y%m%d%H%i%s.%f')")
elif isinstance(value, Date):
return SQL("str_to_date('" + value.format("%Y%m%d%H%M%S.%f") +
"', '%Y%m%d%H%i%s.%f')")
elif hasattr(value, '__iter__'):
return SQL(self.db.literal(json_encode(value)))
else:
return self.db.literal(value)
except Exception as e:
Log.error("problem quoting SQL", e)
def quote_column(self, column_name, table=None):
if isinstance(column_name, basestring):
if table:
column_name = table + "." + column_name
return SQL("`" + column_name.replace(".", "`.`") +
"`") # MY SQL QUOTE OF COLUMN NAMES
elif isinstance(column_name, list):
if table:
return SQL(", ".join(
[self.quote_column(table + "." + c) for c in column_name]))
return SQL(", ".join([self.quote_column(c) for c in column_name]))
else:
# ASSUME {"name":name, "value":value} FORM
return SQL(column_name.value + " AS " +
self.quote_column(column_name.name))
def insert(self, table_name, record):
keys = record.keys()
try:
command = (
"INSERT INTO " + self.quote_column(table_name) + "(" +
SQL(",").join([self.quote_column(k)
for k in keys]) + ") VALUES (" +
SQL(",").join([self.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 _build_list_sql(self, db, first, batch_size):
# TODO: ENSURE THE LAST COLUMN IS THE id
if first:
dim = len(self._extract.field)
where = SQL_OR.join(
sql_iso(
sql_and(
quote_column(f) + ineq(i, e, dim) +
db.quote_value(Date(v) if t == "time" else v)
for e, (f, v, t) in enumerate(
zip(self._extract.field[0:i + 1:], first,
self._extract.type[0:i + 1:]))))
for i in range(dim))
else:
where = SQL_TRUE
selects = []
for t, f in zip(self._extract.type, self._extract.field):
if t == "time":
selects.append(
"CAST" +
sql_iso(sql_alias(quote_column(f), SQL("DATETIME(6)"))))
else:
selects.append(quote_column(f))
sql = (SQL_SELECT + sql_list(selects) + SQL_FROM +
self.settings.snowflake.fact_table + SQL_WHERE + where +
SQL_ORDERBY +
sql_list(quote_column(f) for f in self._extract.field) +
SQL_LIMIT + db.quote_value(batch_size))
return sql
def _make_digits_table(self):
existence = self.db.query("PRAGMA table_info(__digits__)")
if not existence.data:
self.db.execute("CREATE TABLE __digits__(value INTEGER)")
self.db.execute("INSERT INTO __digits__ " +
SQL_UNION_ALL.join(SQL_SELECT + SQL(quote_value(i))
for i in range(10)))
def to_sql(self, schema, not_null=False, boolean=False):
acc = []
for term in self.terms:
sqls = SQLang[term].to_sql(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 _sort2sql(self, sort):
"""
RETURN ORDER BY CLAUSE
"""
if not sort:
return ""
return SQL("ORDER BY " + ",\n".join([self.db.quote_column(o.field) + (" DESC" if o.sort == -1 else "") for o in sort]))
def where(self, filter):
"""
WILL NOT PULL WHOLE OBJECT, JUST TOP-LEVEL PROPERTIES
:param filter: jx_expression filter
:return: list of objects that match
"""
select = []
column_names = []
for cname, cs in self.columns.items():
cs = [c for c in cs if c.type not in STRUCT and len(c.nested_path) == 1]
if len(cs) == 0:
continue
column_names.append(cname)
if len(cs) == 1:
select.append(quote_column(c.es_column) + " " + quote_column(c.name))
else:
select.append(
"coalesce(" +
sql_list(quote_column(c.es_column) for c in cs) +
") " + quote_column(c.name)
)
result = self.db.query(
SQL_SELECT + SQL("\n,").join(select) +
SQL_FROM + quote_column(self.sf.fact) +
SQL_WHERE + jx_expression(filter).to_sql()
)
return wrap([{c: v for c, v in zip(column_names, r)} for r in result.data])
def _make_range_domain(self, domain, column_name):
width = (domain.max - domain.min) / domain.interval
digits = mo_math.floor(mo_math.log10(width - 1))
if digits == 0:
value = "a.value"
else:
value = SQL("+").join("1" + ("0" * j) + "*" +
text_type(chr(ord(b'a') + j)) + ".value"
for j in range(digits + 1))
if domain.interval == 1:
if domain.min == 0:
domain = (SQL_SELECT + value + column_name + SQL_FROM +
"__digits__ a")
else:
domain = (SQL_SELECT + sql_iso(value) + " + " +
quote_value(domain.min) + column_name + SQL_FROM +
"__digits__ a")
else:
if domain.min == 0:
domain = (SQL_SELECT + value + " * " +
quote_value(domain.interval) + column_name +
SQL_FROM + "__digits__ a")
else:
domain = (
SQL_SELECT +
sql_iso(value + " * " + quote_value(domain.interval)) +
" + " + quote_value(domain.min) + column_name + SQL_FROM +
"__digits__ a")
for j in range(digits):
domain += SQL_INNER_JOIN + "__digits__" + text_type(
chr(ord(b'a') + j + 1)) + " ON " + SQL_TRUE
domain += SQL_WHERE + value + " < " + quote_value(width)
return domain
def quote_column(self, column_name, table=None):
if column_name == None:
Log.error("missing column_name")
elif isinstance(column_name, text_type):
if table:
column_name = join_column(table, column_name)
return SQL("`" + column_name.replace(".", "`.`") +
"`") # MY SQL QUOTE OF COLUMN NAMES
elif isinstance(column_name, list):
if table:
return sql_list(join_column(table, c) for c in column_name)
return sql_list(self.quote_column(c) for c in column_name)
else:
# ASSUME {"name":name, "value":value} FORM
return SQL(
sql_alias(column_name.value,
self.quote_column(column_name.name)))
def quote_value(value):
if isinstance(value, (Mapping, list)):
return SQL(".")
elif isinstance(value, Date):
return SQL(text_type(value.unix))
elif isinstance(value, Duration):
return SQL(text_type(value.seconds))
elif is_text(value):
return SQL("'" + value.replace("'", "''") + "'")
elif value == None:
return SQL_NULL
elif value is True:
return SQL_TRUE
elif value is False:
return SQL_FALSE
else:
return SQL(text_type(value))
def quote_column(*path):
if not path:
Log.error("expecting a name")
if any(not is_text(p) for p in path):
Log.error("expecting strings, not SQL")
try:
return ConcatSQL((SQL_SPACE, JoinSQL(SQL_DOT, [SQL(quote(p)) for p in path]), SQL_SPACE))
except Exception as e:
Log.error("Not expacted", cause=e)
def quote_column(column_name, table=None):
if column_name == None:
Log.error("missing column_name")
elif is_text(column_name):
if table:
return join_column(table, column_name)
else:
return SQL("`" + '`.`'.join(split_field(column_name)) +
"`") # MYSQL QUOTE OF COLUMN NAMES
elif is_binary(column_name):
return quote_column(column_name.decode('utf8'), table)
elif is_list(column_name):
if table:
return sql_list(join_column(table, c) for c in column_name)
return sql_list(quote_column(c) for c in column_name)
else:
# ASSUME {"name":name, "value":value} FORM
return SQL(sql_alias(column_name.value,
quote_column(column_name.name)))
def quote_value(self, value):
if value ==None:
return SQL_NULL
if isinstance(value, list):
json = value2json(value)
return self.quote_value(json)
if isinstance(value, text_type) and len(value) > 256:
value = value[:256]
return SQL(adapt(value))
def quote_value(self, value):
if value == None:
return SQL_NULL
if is_list(value):
json = value2json(value)
return self.quote_value(json)
if is_text(value) and len(value) > 256:
value = value[:256]
return SQL(adapt(value))
def quote_sql(value, param=None):
"""
USED TO EXPAND THE PARAMETERS TO THE SQL() OBJECT
"""
try:
if isinstance(value, SQL):
if not param:
return value
param = {k: quote_sql(v) for k, v in param.items()}
return SQL(expand_template(value, param))
elif is_text(value):
return SQL(value)
elif is_data(value):
return quote_value(json_encode(value))
elif hasattr(value, '__iter__'):
return quote_list(value)
else:
return text(value)
except Exception as e:
Log.error("problem quoting SQL", e)
def single(col, r):
min = coalesce(r["gte"], r[">="])
max = coalesce(r["lte"], r["<="])
if min != None and max != None:
# SPECIAL CASE (BETWEEN)
sql = db.quote_column(col) + SQL(" BETWEEN ") + db.quote_value(min) + SQL_AND + db.quote_value(max)
else:
sql = SQL_AND.join(
db.quote_column(col) + name2sign[sign] + db.quote_value(value)
for sign, value in r.items()
)
return sql
def _isolate(separator, list):
try:
if len(list) > 1:
return "(\n" + indent(
SQL(" " + separator + "\n").join(list)) + "\n)"
else:
return list[0]
except Exception as e:
Log.error("Programming problem: separator={{separator}}, list={{list}",
list=list,
separator=separator,
cause=e)
def to_sql(self, schema, not_null=False, boolean=False):
term = SQLang[self.term].partial_eval()
if is_literal(term):
val = term.value
if isinstance(val, text):
sql = quote_value(len(val))
elif isinstance(val, (float, int)):
sql = quote_value(len(convert.value2json(val)))
else:
return Null
else:
value = term.to_sql(schema, not_null=not_null)[0].sql.s
sql = ConcatSQL((SQL("LENGTH"), sql_iso(value)))
return wrap([{"name": ".", "sql": {"n": sql}}])
def to_sql(self, schema, not_null=False, boolean=False):
acc = []
for term in self.terms:
sqls = term.to_sql(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(SQL_CASE+SQL_WHEN + sql_iso(v) + SQL_IS_NULL + SQL_THEN+"0"+SQL_ELSE+"1"+SQL_END)
else:
acc.append(SQL_TRUE)
if not acc:
return wrap([{}])
else:
return wrap([{"nanme": ".", "sql": {"n": SQL("+").join(acc)}}])
def to_sql(self, schema, not_null=False, boolean=False):
default = self.default.to_sql(schema)
if len(self.terms) == 0:
return default
default = coalesce(default[0].sql.s, SQL_NULL)
sep = SQLang[self.separator].to_sql(schema)[0].sql.s
acc = []
for t in self.terms:
t = SQLang[t]
missing = t.missing().partial_eval()
term = t.to_sql(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_sql(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_sql(schema)[0].sql.n + SQL("+1"),
]))
return SQLScript(expr=expr_,
data_type=STRING,
frum=self,
miss=self.missing(),
many=False,
schema=schema)
