Пример #1
0
    def to_bq(self, schema, not_null=False, boolean=False):
        value = BQLang[self.expr].partial_eval()
        missing_value = value.missing().partial_eval()

        if not is_op(missing_value, MissingOp):
            return missing_value.to_bq(schema)

        value_sql = value.to_bq(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 == "b":
                    acc.append(sql_iso(v) + SQL_IS_NULL)
                if t == "s":
                    acc.append(
                        sql_iso(sql_iso(v) + SQL_IS_NULL) + SQL_OR +
                        sql_iso(sql_iso(v) + "=" + SQL_EMPTY_STRING))
                if t == "n":
                    acc.append(sql_iso(v) + SQL_IS_NULL)

        if not acc:
            return wrap([{"name": ".", "sql": {"b": SQL_TRUE}}])
        else:
            return wrap([{"name": ".", "sql": {"b": SQL_AND.join(acc)}}])
Пример #2
0
def sql_eq(**item):
    """
    RETURN SQL FOR COMPARING VARIABLES TO VALUES (AND'ED TOGETHER)

    :param item: keyword parameters representing variable and value
    :return: SQL
    """
    return SQL_AND.join([
        ConcatSQL(quote_column(text(k)), SQL_EQ, quote_value(v))
        if v != None else ConcatSQL(quote_column(text(k)), SQL_IS_NULL)
        for k, v in item.items()
    ])
Пример #3
0
 def single(col, r):
     min = coalesce(r["gte"], r[">="])
     max = coalesce(r["lte"], r["<="])
     if min != None and max != None:
         # SPECIAL CASE (BETWEEN)
         sql = quote_column(col) + SQL(" BETWEEN ") + quote_value(
             min) + SQL_AND + quote_value(max)
     else:
         sql = SQL_AND.join(
             quote_column(col) + name2sign[sign] + quote_value(value)
             for sign, value in r.items())
     return sql
Пример #4
0
 def to_bq(self, schema, not_null=False, boolean=False):
     if not self.terms:
         return wrap([{"name": ".", "sql": {"b": SQL_TRUE}}])
     elif all(self.terms):
         return wrap([{
             "name": ".",
             "sql": {
                 "b":
                 SQL_AND.join([
                     sql_iso(BQLang[t].to_bq(schema, boolean=True)[0].sql.b)
                     for t in self.terms
                 ])
             },
         }])
     else:
         return wrap([{"name": ".", "sql": {"b": SQL_FALSE}}])
Пример #5
0
             sql_alias(quote_value(p.value), domain_name)
             for i, p in enumerate(query_edge.domain.partitions))
         if query_edge.allowNulls:
             domain += (SQL_UNION_ALL + SQL_SELECT + sql_alias(
                 quote_value(len(query_edge.domain.partitions)),
                 "rownum") + SQL_COMMA +
                        sql_alias(SQL_NULL, domain_name))
         where = None
         join_type = SQL_LEFT_JOIN if query_edge.allowNulls else SQL_INNER_JOIN
         on_clause = (SQL_OR.join(
             quote_column(edge_alias, k) + SQL_EQ + v
             for k, v in zip(domain_names, vals)) + SQL_OR +
                      sql_iso(
                          quote_column(edge_alias, domain_name) +
                          SQL_IS_NULL + SQL_AND +
                          SQL_AND.join(v + SQL_IS_NULL
                                       for v in vals)))
         null_on_clause = None
     else:
         domain = SQL_UNION_ALL.join(
             SQL_SELECT + sql_alias(quote_value(pp), domain_name)
             for pp, p in enumerate(query_edge.domain.partitions))
         where = None
         join_type = SQL_LEFT_JOIN if query_edge.allowNulls else SQL_INNER_JOIN
         on_clause = SQL_AND.join(
             quote_column(edge_alias, k) + SQL_EQ + sql
             for k, (t, sql) in zip(domain_names, edge_values))
         null_on_clause = None
 elif query_edge.domain.type == "range":
     domain_name = "d" + text(edge_index) + "c0"
     domain_names = [
         domain_name
Пример #6
0
    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
Пример #7
0
def _esfilter2sqlwhere(esfilter):
    """
    CONVERT ElassticSearch FILTER TO SQL FILTER
    db - REQUIRED TO PROPERLY QUOTE VALUES AND COLUMN NAMES
    """
    esfilter = wrap(esfilter)

    if esfilter is True:
        return SQL_TRUE
    elif esfilter["and"]:
        return sql_iso(
            SQL_AND.join([esfilter2sqlwhere(a) for a in esfilter["and"]]))
    elif esfilter["or"]:
        return sql_iso(
            SQL_OR.join([esfilter2sqlwhere(a) for a in esfilter["or"]]))
    elif esfilter["not"]:
        return SQL_NOT + sql_iso(esfilter2sqlwhere(esfilter["not"]))
    elif esfilter.term:
        return sql_iso(
            SQL_AND.join([
                quote_column(col) + SQL("=") + quote_value(val)
                for col, val in esfilter.term.items()
            ]))
    elif esfilter.eq:
        col, val = first(esfilter.eq.items())
        return ConcatSQL(quote_column(col), SQL_EQ, quote_value(val))
    elif esfilter.terms:
        for col, v in esfilter.terms.items():
            if len(v) == 0:
                return "FALSE"

            try:
                int_list = convert.value2intlist(v)
                has_null = any(vv == None for vv in v)
                if int_list:
                    filter = int_list_packer(col, int_list)
                    if has_null:
                        return esfilter2sqlwhere(
                            {"or": [{
                                "missing": col
                            }, filter]})
                    elif 'terms' in filter and set(filter['terms'].get(
                            col, [])) == set(int_list):
                        return quote_column(col) + " in " + quote_list(
                            int_list)
                    else:
                        return esfilter2sqlwhere(filter)
                else:
                    if has_null:
                        return esfilter2sqlwhere({"missing": col})
                    else:
                        return "false"
            except Exception as e:
                e = Except.wrap(e)
                pass
            return quote_column(col) + " in " + quote_list(v)
    elif esfilter.script:
        return sql_iso(esfilter.script)
    elif esfilter.gt:
        k, v = first(esfilter.gt.items())
        return ConcatSQL(quote_column(k), SQL_GT, quote_value(v))
    elif esfilter.range:
        name2sign = {
            "gt": SQL(">"),
            "gte": SQL(">="),
            "lte": SQL("<="),
            "lt": SQL("<")
        }

        def single(col, r):
            min = coalesce(r["gte"], r[">="])
            max = coalesce(r["lte"], r["<="])
            if min != None and max != None:
                # SPECIAL CASE (BETWEEN)
                sql = quote_column(col) + SQL(" BETWEEN ") + quote_value(
                    min) + SQL_AND + quote_value(max)
            else:
                sql = SQL_AND.join(
                    quote_column(col) + name2sign[sign] + quote_value(value)
                    for sign, value in r.items())
            return sql

        terms = [single(col, ranges) for col, ranges in esfilter.range.items()]
        if len(terms) == 1:
            output = terms[0]
        else:
            output = sql_iso(SQL_AND.join(terms))
        return output
    elif esfilter.missing:
        if isinstance(esfilter.missing, text):
            return sql_iso(quote_column(esfilter.missing) + SQL_IS_NULL)
        else:
            return sql_iso(quote_column(esfilter.missing.field) + SQL_IS_NULL)
    elif esfilter.exists:
        if isinstance(esfilter.exists, text):
            return sql_iso(quote_column(esfilter.exists) + SQL_IS_NOT_NULL)
        else:
            return sql_iso(
                quote_column(esfilter.exists.field) + SQL_IS_NOT_NULL)
    elif esfilter.match_all:
        return SQL_TRUE
    elif esfilter.instr:
        return sql_iso(
            SQL_AND.join([
                "instr" +
                sql_iso(quote_column(col) + ", " + quote_value(val)) + ">0"
                for col, val in esfilter.instr.items()
            ]))
    else:
        Log.error("Can not convert esfilter to SQL: {{esfilter}}",
                  esfilter=esfilter)
Пример #8
0
    def update(self, command):
        """
        :param command:  EXPECTING dict WITH {"set": s, "clear": c, "where": w} FORMAT
        """
        command = wrap(command)
        clear_columns = set(listwrap(command['clear']))

        # REJECT DEEP UPDATES
        touched_columns = command.set.keys() | clear_columns
        for c in self.schema.columns:
            if c.name in touched_columns and len(c.nested_path) > 1:
                Log.error("Deep update not supported")

        # ADD NEW COLUMNS
        where = jx_expression(command.where) or TRUE
        _vars = where.vars()
        _map = {
            v: c.es_column
            for v in _vars for c in self.columns.get(v, Null)
            if c.jx_type not in STRUCT
        }
        where_sql = where.map(_map).to_sql(self.schema)[0].sql.b
        new_columns = set(command.set.keys()) - set(
            c.name for c in self.schema.columns)
        for new_column_name in new_columns:
            nested_value = command.set[new_column_name]
            ctype = get_jx_type(nested_value)
            column = Column(name=new_column_name,
                            jx_type=ctype,
                            es_index=self.name,
                            es_type=json_type_to_sqlite_type(ctype),
                            es_column=typed_column(new_column_name, ctype),
                            last_updated=Date.now())
            self.add_column(column)

        # UPDATE THE NESTED VALUES
        for nested_column_name, nested_value in command.set.items():
            if get_jx_type(nested_value) == "nested":
                nested_table_name = concat_field(self.name, nested_column_name)
                nested_table = nested_tables[nested_column_name]
                self_primary_key = sql_list(
                    quote_column(c.es_column) for u in self.uid
                    for c in self.columns[u])
                extra_key_name = UID + text(len(self.uid))
                extra_key = [e
                             for e in nested_table.columns[extra_key_name]][0]

                sql_command = (
                    SQL_DELETE + SQL_FROM + quote_column(nested_table.name) +
                    SQL_WHERE + "EXISTS" +
                    sql_iso(SQL_SELECT + SQL_ONE + SQL_FROM +
                            sql_alias(quote_column(nested_table.name), "n") +
                            SQL_INNER_JOIN +
                            sql_iso(SQL_SELECT + self_primary_key + SQL_FROM +
                                    quote_column(abs_schema.fact) + SQL_WHERE +
                                    where_sql) + " t ON " +
                            SQL_AND.join(
                                quote_column("t", c.es_column) + SQL_EQ +
                                quote_column("n", c.es_column)
                                for u in self.uid for c in self.columns[u])))
                self.db.execute(sql_command)

                # INSERT NEW RECORDS
                if not nested_value:
                    continue

                doc_collection = {}
                for d in listwrap(nested_value):
                    nested_table.flatten(d,
                                         Data(),
                                         doc_collection,
                                         path=nested_column_name)

                prefix = SQL_INSERT + quote_column(nested_table.name) + sql_iso(
                    sql_list([self_primary_key] + [quote_column(extra_key)] + [
                        quote_column(c.es_column)
                        for c in doc_collection.get(".", Null).active_columns
                    ]))

                # BUILD THE PARENT TABLES
                parent = (SQL_SELECT + self_primary_key + SQL_FROM +
                          quote_column(abs_schema.fact) + SQL_WHERE +
                          jx_expression(command.where).to_sql(schema))

                # BUILD THE RECORDS
                children = SQL_UNION_ALL.join(
                    SQL_SELECT +
                    sql_alias(quote_value(i), extra_key.es_column) +
                    SQL_COMMA + sql_list(
                        sql_alias(quote_value(row[c.name]),
                                  quote_column(c.es_column))
                        for c in doc_collection.get(".", Null).active_columns)
                    for i, row in enumerate(
                        doc_collection.get(".", Null).rows))

                sql_command = (prefix + SQL_SELECT + sql_list([
                    quote_column("p", c.es_column) for u in self.uid
                    for c in self.columns[u]
                ] + [quote_column("c", extra_key)] + [
                    quote_column("c", c.es_column)
                    for c in doc_collection.get(".", Null).active_columns
                ]) + SQL_FROM + sql_iso(parent) + " p" + SQL_INNER_JOIN +
                               sql_iso(children) + " c" + SQL_ON + SQL_TRUE)

                self.db.execute(sql_command)

                # THE CHILD COLUMNS COULD HAVE EXPANDED
                # ADD COLUMNS TO SELF
                for n, cs in nested_table.columns.items():
                    for c in cs:
                        column = Column(name=c.name,
                                        jx_type=c.jx_type,
                                        es_type=c.es_type,
                                        es_index=c.es_index,
                                        es_column=c.es_column,
                                        nested_path=[nested_column_name] +
                                        c.nested_path,
                                        last_updated=Date.now())
                        if c.name not in self.columns:
                            self.columns[column.name] = {column}
                        elif c.jx_type not in [
                                c.jx_type for c in self.columns[c.name]
                        ]:
                            self.columns[column.name].add(column)

        command = ConcatSQL(
            SQL_UPDATE, quote_column(self.name), SQL_SET,
            sql_list([
                quote_column(c.es_column) + SQL_EQ +
                quote_value(get_if_type(v, c.jx_type))
                for c in self.schema.columns
                if c.jx_type != NESTED and len(c.nested_path) == 1
                for v in [command.set[c.name]] if v != None
            ] + [
                quote_column(c.es_column) + SQL_EQ + SQL_NULL
                for c in self.schema.columns
                if (c.name in clear_columns and command.set[c.name] != None
                    and c.jx_type != NESTED and len(c.nested_path) == 1)
            ]), SQL_WHERE, where_sql)

        with self.db.transaction() as t:
            t.execute(command)