#12614

pending completion

Build # #12614

Build Type

push

travis-ci

Committed by

web-flow

Commit Message

<a href="https://github.com/datajoint/datajoint-python/commit/<a class=hub.com/datajoint/datajoint-python/commit/17398fefd90410074675f6beef752a288c736b8c">17398fefd<a href="https://github.com/datajoint/datajoint-python/commit/17398fefd90410074675f6beef752a288c736b8c">">Merge </a><a class="double-link" href="https://github.com/datajoint/datajoint-python/commit/<a class="double-link" href="https://github.com/datajoint/datajoint-python/commit/5ef3de917963d82e802b60ebd98bd5eb3c97dc67">5ef3de917</a>">5ef3de917</a><a href="https://github.com/datajoint/datajoint-python/commit/17398fefd90410074675f6beef752a288c736b8c"> into 77477961b">77477961b</a>

Run Details

69 of 69 new or added lines in 10 files covered. (100.0%)

3041 of 3374 relevant lines covered (90.13%)

0.9 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

93.18

/datajoint/condition.py

""" methods for generating SQL WHERE clauses from datajoint restriction conditions """

import inspect
import collections
import re
import uuid
import datetime
import decimal
import numpy
import pandas
import json
from .errors import DataJointError

JSON_PATTERN = re.compile(
    r"^(?P<attr>\w+)(\.(?P<path>[\w.*\[\]]+))?(:(?P<type>[\w(,\s)]+))?$"
)


def translate_attribute(key):
    match = JSON_PATTERN.match(key)
    if match is None:
        return match, key
    match = match.groupdict()
    if match["path"] is None:
        return match, match["attr"]
    else:
        return match, "json_value(`{}`, _utf8mb4'$.{}'{})".format(
            *{
                k: ((f" returning {v}" if k == "type" else v) if v else "")
                for k, v in match.items()
            }.values()
        )


class PromiscuousOperand:
    """
    A container for an operand to ignore join compatibility
    """

    def __init__(self, operand):
        self.operand = operand


class AndList(list):
    """
    A list of conditions to by applied to a query expression by logical conjunction: the
    conditions are AND-ed. All other collections (lists, sets, other entity sets, etc) are
    applied by logical disjunction (OR).

    Example:
    expr2 = expr & dj.AndList((cond1, cond2, cond3))
    is equivalent to
    expr2 = expr & cond1 & cond2 & cond3
    """

    def append(self, restriction):
        if isinstance(restriction, AndList):
            # extend to reduce nesting
            self.extend(restriction)
        else:
            super().append(restriction)


class Not:
    """invert restriction"""

    def __init__(self, restriction):
        self.restriction = restriction


def assert_join_compatibility(expr1, expr2):
    """
    Determine if expressions expr1 and expr2 are join-compatible.  To be join-compatible,
    the matching attributes in the two expressions must be in the primary key of one or the
    other expression.
    Raises an exception if not compatible.

    :param expr1: A QueryExpression object
    :param expr2: A QueryExpression object
    """
    from .expression import QueryExpression, U

    for rel in (expr1, expr2):
        if not isinstance(rel, (U, QueryExpression)):
            raise DataJointError(
                "Object %r is not a QueryExpression and cannot be joined." % rel
            )
    if not isinstance(expr1, U) and not isinstance(
        expr2, U
    ):  # dj.U is always compatible
        try:
            raise DataJointError(
                "Cannot join query expressions on dependent attribute `%s`"
                % next(
                    r
                    for r in set(expr1.heading.secondary_attributes).intersection(
                        expr2.heading.secondary_attributes
                    )
                )
            )
        except StopIteration:
            pass  # all ok


def make_condition(query_expression, condition, columns):
    """
    Translate the input condition into the equivalent SQL condition (a string)

    :param query_expression: a dj.QueryExpression object to apply condition
    :param condition: any valid restriction object.
    :param columns: a set passed by reference to collect all column names used in the
        condition.
    :return: an SQL condition string or a boolean value.
    """
    from .expression import QueryExpression, Aggregation, U

    def prep_value(k, v):
        """prepare SQL condition"""
        key_match, k = translate_attribute(k)
        if key_match["path"] is None:
            k = f"`{k}`"
        if (
            query_expression.heading[key_match["attr"]].json
            and key_match["path"] is not None
            and isinstance(v, dict)
        ):
            return f"{k}='{json.dumps(v)}'"
        if v is None:
            return f"{k} IS NULL"
        if query_expression.heading[key_match["attr"]].uuid:
            if not isinstance(v, uuid.UUID):
                try:
                    v = uuid.UUID(v)
                except (AttributeError, ValueError):
                    raise DataJointError(
                        "Badly formed UUID {v} in restriction by `{k}`".format(k=k, v=v)
                    )
            return f"{k}=X'{v.bytes.hex()}'"
        if isinstance(
            v,
            (
                datetime.date,
                datetime.datetime,
                datetime.time,
                decimal.Decimal,
                list,
            ),
        ):
            return f'{k}="{v}"'
        if isinstance(v, str):
            return f'{k}="{v.replace("%", "%%")}"'
        return f"{k}={v}"

    def join_conditions(negate, restrictions, operator="AND"):
        return ("NOT (%s)" if negate else "%s") % (
            f"({f') {operator} ('.join(restrictions)})"
        )

    negate = False
    while isinstance(condition, Not):
        negate = not negate
        condition = condition.restriction

    # restrict by string
    if isinstance(condition, str):
        columns.update(extract_column_names(condition))
        return join_conditions(
            negate, restrictions=[condition.strip().replace("%", "%%")]
        )  # escape %, see issue #376

    # restrict by AndList
    if isinstance(condition, AndList):
        # omit all conditions that evaluate to True
        items = [
            item
            for item in (
                make_condition(query_expression, cond, columns) for cond in condition
            )
            if item is not True
        ]
        if any(item is False for item in items):
            return negate  # if any item is False, the whole thing is False
        if not items:
            return not negate  # and empty AndList is True
        return join_conditions(negate, restrictions=items)

    # restriction by dj.U evaluates to True
    if isinstance(condition, U):
        return not negate

    # restrict by boolean
    if isinstance(condition, bool):
        return negate != condition

    # restrict by a mapping/dict -- convert to an AndList of string equality conditions
    if isinstance(condition, collections.abc.Mapping):
        common_attributes = set(c.split(".", 1)[0] for c in condition).intersection(
            query_expression.heading.names
        )
        if not common_attributes:
            return not negate  # no matching attributes -> evaluates to True
        columns.update(common_attributes)
        return join_conditions(
            negate,
            restrictions=[
                prep_value(k, v)
                for k, v in condition.items()
                if k.split(".", 1)[0] in common_attributes
            ],
        )

    # restrict by a numpy record -- convert to an AndList of string equality conditions
    if isinstance(condition, numpy.void):
        common_attributes = set(condition.dtype.fields).intersection(
            query_expression.heading.names
        )
        if not common_attributes:
            return not negate  # no matching attributes -> evaluate to True
        columns.update(common_attributes)
        return join_conditions(
            negate,
            restrictions=[prep_value(k, condition[k]) for k in common_attributes],
        )

    # restrict by a QueryExpression subclass -- trigger instantiation and move on
    if inspect.isclass(condition) and issubclass(condition, QueryExpression):
        condition = condition()

    # restrict by another expression (aka semijoin and antijoin)
    check_compatibility = True
    if isinstance(condition, PromiscuousOperand):
        condition = condition.operand
        check_compatibility = False

    if isinstance(condition, QueryExpression):
        if check_compatibility:
            assert_join_compatibility(query_expression, condition)
        common_attributes = [
            q for q in condition.heading.names if q in query_expression.heading.names
        ]
        columns.update(common_attributes)
        if isinstance(condition, Aggregation):
            condition = condition.make_subquery()
        return (
            # without common attributes, any non-empty set matches everything
            (not negate if condition else negate)
            if not common_attributes
            else "({fields}) {not_}in ({subquery})".format(
                fields="`" + "`,`".join(common_attributes) + "`",
                not_="not " if negate else "",
                subquery=condition.make_sql(common_attributes),
            )
        )

    # restrict by pandas.DataFrames
    if isinstance(condition, pandas.DataFrame):
        condition = condition.to_records()  # convert to numpy.recarray and move on

    # if iterable (but not a string, a QueryExpression, or an AndList), treat as an OrList
    try:
        or_list = [make_condition(query_expression, q, columns) for q in condition]
    except TypeError:
        raise DataJointError("Invalid restriction type %r" % condition)
    else:
        or_list = [
            item for item in or_list if item is not False
        ]  # ignore False conditions
        if any(item is True for item in or_list):  # if any item is True, entirely True
            return not negate
        return (
            join_conditions(negate, restrictions=or_list, operator="OR")
            if or_list
            else negate
        )


def extract_column_names(sql_expression):
    """
    extract all presumed column names from an sql expression such as the WHERE clause,
    for example.

    :param sql_expression: a string containing an SQL expression
    :return: set of extracted column names
    This may be MySQL-specific for now.
    """
    assert isinstance(sql_expression, str)
    result = set()
    s = sql_expression  # for terseness
    # remove escaped quotes
    s = re.sub(r"(\\\")|(\\\')", "", s)
    # remove quoted text
    s = re.sub(r"'[^']*'", "", s)
    s = re.sub(r'"[^"]*"', "", s)
    # find all tokens in back quotes and remove them
    result.update(re.findall(r"`([a-z][a-z_0-9]*)`", s))
    s = re.sub(r"`[a-z][a-z_0-9]*`", "", s)
    # remove space before parentheses
    s = re.sub(r"\s*\(", "(", s)
    # remove tokens followed by ( since they must be functions
    s = re.sub(r"(\b[a-z][a-z_0-9]*)\(", "(", s)
    remaining_tokens = set(re.findall(r"\b[a-z][a-z_0-9]*\b", s))
    # update result removing reserved words
    result.update(
        remaining_tokens
        - {
            "is",
            "in",
            "between",
            "like",
            "and",
            "or",
            "null",
            "not",
            "interval",
            "second",
            "minute",
            "hour",
            "day",
            "month",
            "week",
            "year",
        }
    )
    return result

1	""" methods for generating SQL WHERE clauses from datajoint restriction conditions """
2
3	import inspect	1✔
4	import collections	1✔
5	import re	1✔
6	import uuid	1✔
7	import datetime	1✔
8	import decimal	1✔
9	import numpy	1✔
10	import pandas	1✔
11	import json	1✔
12	from .errors import DataJointError	1✔
13
14	JSON_PATTERN = re.compile(	1✔
15	r"^(?P<attr>\w+)(\.(?P<path>[\w.*\[\]]+))?(:(?P<type>[\w(,\s)]+))?$"
16	)
17
18
19	def translate_attribute(key):	1✔
20	match = JSON_PATTERN.match(key)	1✔
21	if match is None:	1✔
22	return match, key	1✔
23	match = match.groupdict()	1✔
24	if match["path"] is None:	1✔
25	return match, match["attr"]	1✔
26	else:
27	return match, "json_value(`{}`, _utf8mb4'$.{}'{})".format(	×
28	*{
29	k: ((f" returning {v}" if k == "type" else v) if v else "")
30	for k, v in match.items()
31	}.values()
32	)
33
34
35	class PromiscuousOperand:	1✔
36	"""
37	A container for an operand to ignore join compatibility
38	"""
39
40	def __init__(self, operand):	1✔
41	self.operand = operand	1✔
42
43
44	class AndList(list):	1✔
45	"""
46	A list of conditions to by applied to a query expression by logical conjunction: the
47	conditions are AND-ed. All other collections (lists, sets, other entity sets, etc) are
48	applied by logical disjunction (OR).
49
50	Example:
51	expr2 = expr & dj.AndList((cond1, cond2, cond3))
52	is equivalent to
53	expr2 = expr & cond1 & cond2 & cond3
54	"""
55
56	def append(self, restriction):	1✔
57	if isinstance(restriction, AndList):	1✔
58	# extend to reduce nesting
59	self.extend(restriction)	1✔
60	else:
61	super().append(restriction)	1✔
62
63
64	class Not:	1✔
65	"""invert restriction"""
66
67	def __init__(self, restriction):	1✔
68	self.restriction = restriction	1✔
69
70
71	def assert_join_compatibility(expr1, expr2):	1✔
72	"""
73	Determine if expressions expr1 and expr2 are join-compatible. To be join-compatible,
74	the matching attributes in the two expressions must be in the primary key of one or the
75	other expression.
76	Raises an exception if not compatible.
77
78	:param expr1: A QueryExpression object
79	:param expr2: A QueryExpression object
80	"""
81	from .expression import QueryExpression, U	1✔
82
83	for rel in (expr1, expr2):	1✔
84	if not isinstance(rel, (U, QueryExpression)):	1✔
85	raise DataJointError(	×
86	"Object %r is not a QueryExpression and cannot be joined." % rel
87	)
88	if not isinstance(expr1, U) and not isinstance(	1✔
89	expr2, U
90	): # dj.U is always compatible
91	try:	1✔
92	raise DataJointError(	1✔
93	"Cannot join query expressions on dependent attribute `%s`"
94	% next(
95	r
96	for r in set(expr1.heading.secondary_attributes).intersection(
97	expr2.heading.secondary_attributes
98	)
99	)
100	)
101	except StopIteration:	1✔
102	pass # all ok	1✔
103
104
105	def make_condition(query_expression, condition, columns):	1✔
106	"""
107	Translate the input condition into the equivalent SQL condition (a string)
108
109	:param query_expression: a dj.QueryExpression object to apply condition
110	:param condition: any valid restriction object.
111	:param columns: a set passed by reference to collect all column names used in the
112	condition.
113	:return: an SQL condition string or a boolean value.
114	"""
115	from .expression import QueryExpression, Aggregation, U	1✔
116
117	def prep_value(k, v):	1✔
118	"""prepare SQL condition"""
119	key_match, k = translate_attribute(k)	1✔
120	if key_match["path"] is None:	1✔
121	k = f"`{k}`"	1✔
122	if (	1✔
123	query_expression.heading[key_match["attr"]].json
124	and key_match["path"] is not None
125	and isinstance(v, dict)
126	):
127	return f"{k}='{json.dumps(v)}'"	×
128	if v is None:	1✔
129	return f"{k} IS NULL"	1✔
130	if query_expression.heading[key_match["attr"]].uuid:	1✔
131	if not isinstance(v, uuid.UUID):	1✔
132	try:	1✔
133	v = uuid.UUID(v)	1✔
134	except (AttributeError, ValueError):	1✔
135	raise DataJointError(	1✔
136	"Badly formed UUID {v} in restriction by `{k}`".format(k=k, v=v)
137	)
138	return f"{k}=X'{v.bytes.hex()}'"	1✔
139	if isinstance(	1✔
140	v,
141	(
142	datetime.date,
143	datetime.datetime,
144	datetime.time,
145	decimal.Decimal,
146	list,
147	),
148	):
149	return f'{k}="{v}"'	1✔
150	if isinstance(v, str):	1✔
151	return f'{k}="{v.replace("%", "%%")}"'	1✔
152	return f"{k}={v}"	1✔
153
154	def join_conditions(negate, restrictions, operator="AND"):	1✔
155	return ("NOT (%s)" if negate else "%s") % (	1✔
156	f"({f') {operator} ('.join(restrictions)})"
157	)
158
159	negate = False	1✔
160	while isinstance(condition, Not):	1✔
161	negate = not negate	1✔
162	condition = condition.restriction	1✔
163
164	# restrict by string
165	if isinstance(condition, str):	1✔
166	columns.update(extract_column_names(condition))	1✔
167	return join_conditions(	1✔
168	negate, restrictions=[condition.strip().replace("%", "%%")]
169	) # escape %, see issue #376
170
171	# restrict by AndList
172	if isinstance(condition, AndList):	1✔
173	# omit all conditions that evaluate to True
174	items = [	1✔
175	item
176	for item in (
177	make_condition(query_expression, cond, columns) for cond in condition
178	)
179	if item is not True
180	]
181	if any(item is False for item in items):	1✔
182	return negate # if any item is False, the whole thing is False	×
183	if not items:	1✔
184	return not negate # and empty AndList is True	1✔
185	return join_conditions(negate, restrictions=items)	1✔
186
187	# restriction by dj.U evaluates to True
188	if isinstance(condition, U):	1✔
189	return not negate	1✔
190
191	# restrict by boolean
192	if isinstance(condition, bool):	1✔
193	return negate != condition	×
194
195	# restrict by a mapping/dict -- convert to an AndList of string equality conditions
196	if isinstance(condition, collections.abc.Mapping):	1✔
197	common_attributes = set(c.split(".", 1)[0] for c in condition).intersection(	1✔
198	query_expression.heading.names
199	)
200	if not common_attributes:	1✔
201	return not negate # no matching attributes -> evaluates to True	1✔
202	columns.update(common_attributes)	1✔
203	return join_conditions(	1✔
204	negate,
205	restrictions=[
206	prep_value(k, v)
207	for k, v in condition.items()
208	if k.split(".", 1)[0] in common_attributes
209	],
210	)
211
212	# restrict by a numpy record -- convert to an AndList of string equality conditions
213	if isinstance(condition, numpy.void):	1✔
214	common_attributes = set(condition.dtype.fields).intersection(	1✔
215	query_expression.heading.names
216	)
217	if not common_attributes:	1✔
218	return not negate # no matching attributes -> evaluate to True	×
219	columns.update(common_attributes)	1✔
220	return join_conditions(	1✔
221	negate,
222	restrictions=[prep_value(k, condition[k]) for k in common_attributes],
223	)
224
225	# restrict by a QueryExpression subclass -- trigger instantiation and move on
226	if inspect.isclass(condition) and issubclass(condition, QueryExpression):	1✔
227	condition = condition()	1✔
228
229	# restrict by another expression (aka semijoin and antijoin)
230	check_compatibility = True	1✔
231	if isinstance(condition, PromiscuousOperand):	1✔
232	condition = condition.operand	1✔
233	check_compatibility = False	1✔
234
235	if isinstance(condition, QueryExpression):	1✔
236	if check_compatibility:	1✔
237	assert_join_compatibility(query_expression, condition)	1✔
238	common_attributes = [	1✔
239	q for q in condition.heading.names if q in query_expression.heading.names
240	]
241	columns.update(common_attributes)	1✔
242	if isinstance(condition, Aggregation):	1✔
243	condition = condition.make_subquery()	1✔
244	return (	1✔
245	# without common attributes, any non-empty set matches everything
246	(not negate if condition else negate)
247	if not common_attributes
248	else "({fields}) {not_}in ({subquery})".format(
249	fields="`" + "`,`".join(common_attributes) + "`",
250	not_="not " if negate else "",
251	subquery=condition.make_sql(common_attributes),
252	)
253	)
254
255	# restrict by pandas.DataFrames
256	if isinstance(condition, pandas.DataFrame):	1✔
257	condition = condition.to_records() # convert to numpy.recarray and move on	1✔
258
259	# if iterable (but not a string, a QueryExpression, or an AndList), treat as an OrList
260	try:	1✔
261	or_list = [make_condition(query_expression, q, columns) for q in condition]	1✔
262	except TypeError:	×
263	raise DataJointError("Invalid restriction type %r" % condition)	×
264	else:
265	or_list = [	1✔
266	item for item in or_list if item is not False
267	] # ignore False conditions
268	if any(item is True for item in or_list): # if any item is True, entirely True	1✔
269	return not negate	×
270	return (	1✔
271	join_conditions(negate, restrictions=or_list, operator="OR")
272	if or_list
273	else negate
274	)
275
276
277	def extract_column_names(sql_expression):	1✔
278	"""
279	extract all presumed column names from an sql expression such as the WHERE clause,
280	for example.
281
282	:param sql_expression: a string containing an SQL expression
283	:return: set of extracted column names
284	This may be MySQL-specific for now.
285	"""
286	assert isinstance(sql_expression, str)	1✔
287	result = set()	1✔
288	s = sql_expression # for terseness	1✔
289	# remove escaped quotes
290	s = re.sub(r"(\\\")\|(\\\')", "", s)	1✔
291	# remove quoted text
292	s = re.sub(r"'[^']*'", "", s)	1✔
293	s = re.sub(r'"[^"]*"', "", s)	1✔
294	# find all tokens in back quotes and remove them
295	result.update(re.findall(r"`([a-z][a-z_0-9]*)`", s))	1✔
296	s = re.sub(r"`[a-z][a-z_0-9]*`", "", s)	1✔
297	# remove space before parentheses
298	s = re.sub(r"\s*\(", "(", s)	1✔
299	# remove tokens followed by ( since they must be functions
300	s = re.sub(r"(\b[a-z][a-z_0-9]*)\(", "(", s)	1✔
301	remaining_tokens = set(re.findall(r"\b[a-z][a-z_0-9]*\b", s))	1✔
302	# update result removing reserved words
303	result.update(	1✔
304	remaining_tokens
305	- {
306	"is",
307	"in",
308	"between",
309	"like",
310	"and",
311	"or",
312	"null",
313	"not",
314	"interval",
315	"second",
316	"minute",
317	"hour",
318	"day",
319	"month",
320	"week",
321	"year",
322	}
323	)
324	return result	1✔

datajoint / datajoint-python / #12614

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous