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)