def generate(self):
"""
Generate the actual SQL statement from the passed in expression tree.
@return: a C{tuple} of (C{str}, C{list}), where the C{str} is the partial SQL statement,
and the C{list} is the list of argument substitutions to use with the SQL API execute method.
"""
# Init state
self.arguments = {}
self.argcount = 0
obj = self.collection._objectSchema
columns = [obj.RESOURCE_NAME, obj.UID]
# For SQL data DB we need to restrict the query to just the targeted collection resource-id if provided
if self.whereid:
# AND the whole thing
test = expression.isExpression(obj.PARENT_RESOURCE_ID, self.whereid, True)
self.expression = test if isinstance(self.expression, expression.allExpression) else test.andWith(self.expression)
# Generate ' where ...' partial statement
where = self.generateExpression(self.expression)
select = Select(
columns,
From=obj,
Where=where,
Distinct=True,
)
return select, self.arguments
def generate(self):
"""
Generate the actual SQL statement from the passed in expression tree.
@return: a C{tuple} of (C{str}, C{list}), where the C{str} is the partial SQL statement,
and the C{list} is the list of argument substitutions to use with the SQL API execute method.
"""
# Init state
self.arguments = {}
self.argcount = 0
obj = self.collection._objectSchema
columns = [obj.RESOURCE_NAME, obj.UID]
# For SQL data DB we need to restrict the query to just the targeted collection resource-id if provided
if self.whereid:
# AND the whole thing
test = expression.isExpression(obj.PARENT_RESOURCE_ID,
self.whereid, True)
self.expression = test if isinstance(
self.expression, expression.allExpression) else test.andWith(
self.expression)
# Generate ' where ...' partial statement
where = self.generateExpression(self.expression)
select = Select(
columns,
From=obj,
Where=where,
Distinct=True,
)
return select, self.arguments
def compfilterExpression(compfilter, fields):
"""
Create an expression for a single comp-filter element.
@param compfilter: the L{ComponentFilter} element.
@return: a L{baseExpression} for the expression tree.
"""
# Handle is-not-defined case
if not compfilter.defined:
# Test for TYPE != <<component-type name>>
return expression.isnotExpression(fields["TYPE"], compfilter.filter_name, True)
# Determine logical expression grouping
logical = expression.andExpression if compfilter.filter_test == "allof" else expression.orExpression
expressions = []
if isinstance(compfilter.filter_name, str) or isinstance(compfilter.filter_name, unicode):
expressions.append(expression.isExpression(fields["TYPE"], compfilter.filter_name, True))
else:
expressions.append(expression.inExpression(fields["TYPE"], compfilter.filter_name, True))
# Handle time-range
if compfilter.qualifier and isinstance(compfilter.qualifier, TimeRange):
start, end, startfloat, endfloat = getTimerangeArguments(compfilter.qualifier)
expressions.append(expression.timerangeExpression(start, end, startfloat, endfloat))
# Handle properties - we can only do UID right now
props = []
for p in [x for x in compfilter.filters if isinstance(x, PropertyFilter)]:
props.append(propfilterExpression(p, fields))
if len(props) > 1:
propsExpression = logical(props)
elif len(props) == 1:
propsExpression = props[0]
else:
propsExpression = None
# Handle embedded components - we do not right now as our Index does not handle them
comps = []
for _ignore in [x for x in compfilter.filters if isinstance(x, ComponentFilter)]:
raise ValueError
if len(comps) > 1:
compsExpression = logical(comps)
elif len(comps) == 1:
compsExpression = comps[0]
else:
compsExpression = None
# Now build compound expression
if ((propsExpression is not None) and (compsExpression is not None)):
expressions.append(logical([propsExpression, compsExpression]))
elif propsExpression is not None:
expressions.append(propsExpression)
elif compsExpression is not None:
expressions.append(compsExpression)
# Now build return expression
return expression.andExpression(expressions)
def compfilterExpression(compfilter, fields):
"""
Create an expression for a single comp-filter element.
@param compfilter: the L{ComponentFilter} element.
@return: a L{baseExpression} for the expression tree.
"""
# Handle is-not-defined case
if not compfilter.defined:
# Test for TYPE != <<component-type name>>
return expression.isnotExpression(fields["TYPE"], compfilter.filter_name, True)
# Determine logical expression grouping
logical = expression.andExpression if compfilter.filter_test == "allof" else expression.orExpression
expressions = []
if isinstance(compfilter.filter_name, str) or isinstance(compfilter.filter_name, unicode):
expressions.append(expression.isExpression(fields["TYPE"], compfilter.filter_name, True))
else:
expressions.append(expression.inExpression(fields["TYPE"], compfilter.filter_name, True))
# Handle time-range
if compfilter.qualifier and isinstance(compfilter.qualifier, TimeRange):
start, end, startfloat, endfloat = getTimerangeArguments(compfilter.qualifier)
expressions.append(expression.timerangeExpression(start, end, startfloat, endfloat))
# Handle properties - we can only do UID right now
props = []
for p in [x for x in compfilter.filters if isinstance(x, PropertyFilter)]:
props.append(propfilterExpression(p, fields))
if len(props) > 1:
propsExpression = logical(props)
elif len(props) == 1:
propsExpression = props[0]
else:
propsExpression = None
# Handle embedded components - we do not right now as our Index does not handle them
comps = []
for _ignore in [x for x in compfilter.filters if isinstance(x, ComponentFilter)]:
raise ValueError
if len(comps) > 1:
compsExpression = logical(comps)
elif len(comps) == 1:
compsExpression = comps[0]
else:
compsExpression = None
# Now build compound expression
if ((propsExpression is not None) and (compsExpression is not None)):
expressions.append(logical([propsExpression, compsExpression]))
elif propsExpression is not None:
expressions.append(propsExpression)
elif compsExpression is not None:
expressions.append(compsExpression)
# Now build return expression
return expression.andExpression(expressions)
def test_uid_query(self):
resource = self.FakeHomeChild()
obj = resource._objectSchema
expr = expression.isExpression(obj.UID, 5678, False)
select, args = SQLQueryGenerator(expr, resource, resource.id()).generate()
self.assertEqual(select.toSQL(), SQLFragment("select distinct RESOURCE_NAME, ICALENDAR_UID from CALENDAR_OBJECT where CALENDAR_RESOURCE_ID = ? and ICALENDAR_UID = ?", [1234, 5678]))
self.assertEqual(args, {})
def propfilterExpression(propfilter, fields):
"""
Create an expression for a single prop-filter element.
@param propfilter: the L{PropertyFilter} element.
@return: a L{baseExpression} for the expression tree.
"""
# Only handle UID right now
if propfilter.filter_name != "UID":
raise ValueError
# Handle is-not-defined case
if not propfilter.defined:
# Test for <<field>> != "*"
return expression.isExpression(fields["UID"], "", True)
# Determine logical expression grouping
logical = expression.andExpression if propfilter.filter_test == "allof" else expression.orExpression
# Handle time-range - we cannot do this with our Index right now
if propfilter.qualifier and isinstance(propfilter.qualifier, TimeRange):
raise ValueError
# Handle text-match
tm = None
if propfilter.qualifier and isinstance(propfilter.qualifier, TextMatch):
if propfilter.qualifier.match_type == "equals":
tm = expression.isnotExpression if propfilter.qualifier.negate else expression.isExpression
elif propfilter.qualifier.match_type == "contains":
tm = expression.notcontainsExpression if propfilter.qualifier.negate else expression.containsExpression
elif propfilter.qualifier.match_type == "starts-with":
tm = expression.notstartswithExpression if propfilter.qualifier.negate else expression.startswithExpression
elif propfilter.qualifier.match_type == "ends-with":
tm = expression.notendswithExpression if propfilter.qualifier.negate else expression.endswithExpression
tm = tm(fields[propfilter.filter_name], propfilter.qualifier.text,
propfilter.qualifier.caseless)
# Handle embedded parameters - we do not right now as our Index does not handle them
params = []
for _ignore in propfilter.filters:
raise ValueError
if len(params) > 1:
paramsExpression = logical(params)
elif len(params) == 1:
paramsExpression = params[0]
else:
paramsExpression = None
# Now build return expression
if (tm is not None) and (paramsExpression is not None):
return logical([tm, paramsExpression])
elif tm is not None:
return tm
elif paramsExpression is not None:
return paramsExpression
else:
return None
def propfilterExpression(propfilter, fields):
"""
Create an expression for a single prop-filter element.
@param propfilter: the L{PropertyFilter} element.
@return: a L{baseExpression} for the expression tree.
"""
# Only handle UID right now
if propfilter.filter_name != "UID":
raise ValueError
# Handle is-not-defined case
if not propfilter.defined:
# Test for <<field>> != "*"
return expression.isExpression(fields["UID"], "", True)
# Determine logical expression grouping
logical = expression.andExpression if propfilter.filter_test == "allof" else expression.orExpression
# Handle time-range - we cannot do this with our Index right now
if propfilter.qualifier and isinstance(propfilter.qualifier, TimeRange):
raise ValueError
# Handle text-match
tm = None
if propfilter.qualifier and isinstance(propfilter.qualifier, TextMatch):
if propfilter.qualifier.match_type == "equals":
tm = expression.isnotExpression if propfilter.qualifier.negate else expression.isExpression
elif propfilter.qualifier.match_type == "contains":
tm = expression.notcontainsExpression if propfilter.qualifier.negate else expression.containsExpression
elif propfilter.qualifier.match_type == "starts-with":
tm = expression.notstartswithExpression if propfilter.qualifier.negate else expression.startswithExpression
elif propfilter.qualifier.match_type == "ends-with":
tm = expression.notendswithExpression if propfilter.qualifier.negate else expression.endswithExpression
tm = tm(fields[propfilter.filter_name], propfilter.qualifier.text, propfilter.qualifier.caseless)
# Handle embedded parameters - we do not right now as our Index does not handle them
params = []
for _ignore in propfilter.filters:
raise ValueError
if len(params) > 1:
paramsExpression = logical(params)
elif len(params) == 1:
paramsExpression = params[0]
else:
paramsExpression = None
# Now build return expression
if (tm is not None) and (paramsExpression is not None):
return logical([tm, paramsExpression])
elif tm is not None:
return tm
elif paramsExpression is not None:
return paramsExpression
else:
return None
def test_uid_query(self):
resource = self.FakeHomeChild()
obj = resource._objectSchema
expr = expression.isExpression(obj.UID, 5678, False)
select, args = SQLQueryGenerator(expr, resource,
resource.id()).generate()
self.assertEqual(
select.toSQL(),
SQLFragment(
"select distinct RESOURCE_NAME, ICALENDAR_UID from CALENDAR_OBJECT where CALENDAR_RESOURCE_ID = ? and ICALENDAR_UID = ?",
[1234, 5678]))
self.assertEqual(args, {})
def propfilterExpression(propfilter, fields):
"""
Create an expression for a single prop-filter element.
@param propfilter: the L{PropertyFilter} element.
@return: a L{baseExpression} for the expression tree.
"""
# Only handle UID right now
if propfilter.filter_name != "UID":
raise ValueError
# Handle is-not-defined case
if not propfilter.defined:
# Test for <<field>> != "*"
return expression.isExpression(fields["UID"], "", True)
# Handle embedded parameters/text-match
params = []
for filter in propfilter.filters:
if isinstance(filter, TextMatch):
if filter.match_type == "equals":
tm = expression.isnotExpression if filter.negate else expression.isExpression
elif filter.match_type == "contains":
tm = expression.notcontainsExpression if filter.negate else expression.containsExpression
elif filter.match_type == "starts-with":
tm = expression.notstartswithExpression if filter.negate else expression.startswithExpression
elif filter.match_type == "ends-with":
tm = expression.notendswithExpression if filter.negate else expression.endswithExpression
params.append(
tm(fields[propfilter.filter_name], str(filter.text), True))
else:
# No embedded parameters - not right now as our Index does not handle them
raise ValueError
# Now build return expression
if len(params) > 1:
if propfilter.propfilter_test == "anyof":
return expression.orExpression(params)
else:
return expression.andExpression(params)
elif len(params) == 1:
return params[0]
else:
return None
def propfilterExpression(propfilter, fields):
"""
Create an expression for a single prop-filter element.
@param propfilter: the L{PropertyFilter} element.
@return: a L{baseExpression} for the expression tree.
"""
# Only handle UID right now
if propfilter.filter_name != "UID":
raise ValueError
# Handle is-not-defined case
if not propfilter.defined:
# Test for <<field>> != "*"
return expression.isExpression(fields["UID"], "", True)
# Handle embedded parameters/text-match
params = []
for filter in propfilter.filters:
if isinstance(filter, TextMatch):
if filter.match_type == "equals":
tm = expression.isnotExpression if filter.negate else expression.isExpression
elif filter.match_type == "contains":
tm = expression.notcontainsExpression if filter.negate else expression.containsExpression
elif filter.match_type == "starts-with":
tm = expression.notstartswithExpression if filter.negate else expression.startswithExpression
elif filter.match_type == "ends-with":
tm = expression.notendswithExpression if filter.negate else expression.endswithExpression
params.append(tm(fields[propfilter.filter_name], str(filter.text), True))
else:
# No embedded parameters - not right now as our Index does not handle them
raise ValueError
# Now build return expression
if len(params) > 1:
if propfilter.propfilter_test == "anyof":
return expression.orExpression(params)
else:
return expression.andExpression(params)
elif len(params) == 1:
return params[0]
else:
return None
def generate(self):
"""
Generate the actual SQL statement from the passed in expression tree.
@return: a C{tuple} of (C{str}, C{list}), where the C{str} is the partial SQL statement,
and the C{list} is the list of argument substitutions to use with the SQL API execute method.
"""
# Init state
self.arguments = {}
self.argcount = 0
obj = self.collection._objectSchema
columns = [obj.RESOURCE_NAME, obj.ICALENDAR_UID, obj.ICALENDAR_TYPE]
if self.freebusy:
columns.extend([
obj.ORGANIZER,
self._timerange.FLOATING,
Coalesce(self._peruser.ADJUSTED_START_DATE, self._timerange.START_DATE),
Coalesce(self._peruser.ADJUSTED_END_DATE, self._timerange.END_DATE),
self._timerange.FBTYPE,
self._timerange.TRANSPARENT,
self._peruser.TRANSPARENT,
])
# For SQL data DB we need to restrict the query to just the targeted calendar resource-id if provided
if self.whereid:
test = expression.isExpression(obj.CALENDAR_RESOURCE_ID, self.whereid, True)
# Since timerange expression already have the calendar resource-id test in them, do not
# add the additional term to those. When the additional term is added, add it as the first
# component in the AND expression to hopefully get the DB to use its index first
# Top-level timerange expression already has calendar resource-id restriction in it
if isinstance(self.expression, expression.timerangeExpression):
pass
# Top-level OR - check each component
elif isinstance(self.expression, expression.orExpression):
def _hasTopLevelTimerange(testexpr):
if isinstance(testexpr, expression.timerangeExpression):
return True
elif isinstance(testexpr, expression.andExpression):
return any([isinstance(expr, expression.timerangeExpression) for expr in testexpr.expressions])
else:
return False
hasTimerange = any([_hasTopLevelTimerange(expr) for expr in self.expression.expressions])
if hasTimerange:
# timerange expression forces a join on calendarid
pass
else:
# AND the whole thing with calendarid
self.expression = test.andWith(self.expression)
# Top-level AND - only add additional expression if timerange not present
elif isinstance(self.expression, expression.andExpression):
hasTimerange = any([isinstance(expr, expression.timerangeExpression) for expr in self.expression.expressions])
if not hasTimerange:
# AND the whole thing
self.expression = test.andWith(self.expression)
# Just use the id test
elif isinstance(self.expression, expression.allExpression):
self.expression = test
# Just AND the entire thing
else:
self.expression = test.andWith(self.expression)
# Generate ' where ...' partial statement
where = self.generateExpression(self.expression)
if self.usedtimerange:
where = where.And(self._timerange.CALENDAR_OBJECT_RESOURCE_ID == obj.RESOURCE_ID).And(self._timerange.CALENDAR_RESOURCE_ID == self.whereid)
# Set of tables depends on use of timespan and fb use
if self.usedtimerange:
if self.freebusy:
tables = obj.join(
self._timerange.join(
self._peruser,
on=(self._timerange.INSTANCE_ID == self._peruser.TIME_RANGE_INSTANCE_ID).And(self._peruser.USER_ID == self.userid),
type="left outer"
),
type=","
)
else:
tables = obj.join(self._timerange, type=",")
else:
tables = obj
select = Select(
columns,
From=tables,
Where=where,
Distinct=True,
)
return select, self.arguments, self.usedtimerange
def generate(self):
"""
Generate the actual SQL statement from the passed in expression tree.
@return: a C{tuple} of (C{str}, C{list}), where the C{str} is the partial SQL statement,
and the C{list} is the list of argument substitutions to use with the SQL API execute method.
"""
# Init state
self.arguments = {}
self.argcount = 0
obj = self.collection._objectSchema
columns = [obj.RESOURCE_NAME, obj.ICALENDAR_UID, obj.ICALENDAR_TYPE]
if self.freebusy:
columns.extend([
obj.ORGANIZER,
self._timerange.FLOATING,
Coalesce(self._peruser.ADJUSTED_START_DATE,
self._timerange.START_DATE),
Coalesce(self._peruser.ADJUSTED_END_DATE,
self._timerange.END_DATE),
self._timerange.FBTYPE,
self._timerange.TRANSPARENT,
self._peruser.TRANSPARENT,
])
# For SQL data DB we need to restrict the query to just the targeted calendar resource-id if provided
if self.whereid:
test = expression.isExpression(obj.CALENDAR_RESOURCE_ID,
self.whereid, True)
# Since timerange expression already have the calendar resource-id test in them, do not
# add the additional term to those. When the additional term is added, add it as the first
# component in the AND expression to hopefully get the DB to use its index first
# Top-level timerange expression already has calendar resource-id restriction in it
if isinstance(self.expression, expression.timerangeExpression):
pass
# Top-level OR - check each component
elif isinstance(self.expression, expression.orExpression):
def _hasTopLevelTimerange(testexpr):
if isinstance(testexpr, expression.timerangeExpression):
return True
elif isinstance(testexpr, expression.andExpression):
return any([
isinstance(expr, expression.timerangeExpression)
for expr in testexpr.expressions
])
else:
return False
hasTimerange = any([
_hasTopLevelTimerange(expr)
for expr in self.expression.expressions
])
if hasTimerange:
# timerange expression forces a join on calendarid
pass
else:
# AND the whole thing with calendarid
self.expression = test.andWith(self.expression)
# Top-level AND - only add additional expression if timerange not present
elif isinstance(self.expression, expression.andExpression):
hasTimerange = any([
isinstance(expr, expression.timerangeExpression)
for expr in self.expression.expressions
])
if not hasTimerange:
# AND the whole thing
self.expression = test.andWith(self.expression)
# Just use the id test
elif isinstance(self.expression, expression.allExpression):
self.expression = test
# Just AND the entire thing
else:
self.expression = test.andWith(self.expression)
# Generate ' where ...' partial statement
where = self.generateExpression(self.expression)
if self.usedtimerange:
where = where.And(
self._timerange.CALENDAR_OBJECT_RESOURCE_ID == obj.RESOURCE_ID
).And(self._timerange.CALENDAR_RESOURCE_ID == self.whereid)
# Set of tables depends on use of timespan and fb use
if self.usedtimerange:
if self.freebusy:
tables = obj.join(self._timerange.join(
self._peruser,
on=(self._timerange.INSTANCE_ID ==
self._peruser.TIME_RANGE_INSTANCE_ID).And(
self._peruser.USER_ID == self.userid),
type="left outer"),
type=",")
else:
tables = obj.join(self._timerange, type=",")
else:
tables = obj
select = Select(
columns,
From=tables,
Where=where,
Distinct=True,
)
return select, self.arguments, self.usedtimerange
def test_orWith(self):
tests = (
(
expression.isExpression("A", "1", True),
expression.isExpression("B", "2", True),
"(is(A, 1, True) OR is(B, 2, True))"
),
(
expression.isExpression("A", "1", True),
expression.andExpression((
expression.isExpression("B", "2", True),
)),
"(is(A, 1, True) OR is(B, 2, True))"
),
(
expression.isExpression("A", "1", True),
expression.andExpression((
expression.isExpression("B", "2", True),
expression.isExpression("C", "3", True),
)),
"(is(A, 1, True) OR (is(B, 2, True) AND is(C, 3, True)))"
),
(
expression.isExpression("A", "1", True),
expression.orExpression((
expression.isExpression("B", "2", True),
)),
"(is(A, 1, True) OR is(B, 2, True))"
),
(
expression.isExpression("A", "1", True),
expression.orExpression((
expression.isExpression("B", "2", True),
expression.isExpression("C", "3", True),
)),
"(is(A, 1, True) OR is(B, 2, True) OR is(C, 3, True))"
),
(
expression.andExpression((
expression.isExpression("A", "1", True),
)),
expression.isExpression("B", "2", True),
"(is(A, 1, True) OR is(B, 2, True))"
),
(
expression.andExpression((
expression.isExpression("A", "1", True),
expression.isExpression("B", "2", True),
)),
expression.isExpression("C", "3", True),
"((is(A, 1, True) AND is(B, 2, True)) OR is(C, 3, True))"
),
(
expression.orExpression((
expression.isExpression("A", "1", True),
)),
expression.isExpression("B", "2", True),
"(is(A, 1, True) OR is(B, 2, True))"
),
(
expression.orExpression((
expression.isExpression("A", "1", True),
expression.isExpression("B", "2", True),
)),
expression.isExpression("C", "3", True),
"(is(A, 1, True) OR is(B, 2, True) OR is(C, 3, True))"
),
)
for expr1, expr2, result in tests:
self.assertEqual(str(expr1.orWith(expr2)), result, msg="Failed on %s" % (result,))
def test_orWith(self):
tests = (
(expression.isExpression("A", "1", True),
expression.isExpression("B", "2", True),
"(is(A, 1, True) OR is(B, 2, True))"),
(expression.isExpression("A", "1", True),
expression.andExpression((expression.isExpression("B", "2",
True), )),
"(is(A, 1, True) OR is(B, 2, True))"),
(expression.isExpression("A", "1", True),
expression.andExpression((
expression.isExpression("B", "2", True),
expression.isExpression("C", "3", True),
)), "(is(A, 1, True) OR (is(B, 2, True) AND is(C, 3, True)))"),
(expression.isExpression("A", "1", True),
expression.orExpression((expression.isExpression("B", "2",
True), )),
"(is(A, 1, True) OR is(B, 2, True))"),
(expression.isExpression("A", "1", True),
expression.orExpression((
expression.isExpression("B", "2", True),
expression.isExpression("C", "3", True),
)), "(is(A, 1, True) OR is(B, 2, True) OR is(C, 3, True))"),
(expression.andExpression((expression.isExpression("A", "1",
True), )),
expression.isExpression("B", "2", True),
"(is(A, 1, True) OR is(B, 2, True))"),
(expression.andExpression((
expression.isExpression("A", "1", True),
expression.isExpression("B", "2", True),
)), expression.isExpression("C", "3", True),
"((is(A, 1, True) AND is(B, 2, True)) OR is(C, 3, True))"),
(expression.orExpression((expression.isExpression("A", "1",
True), )),
expression.isExpression("B", "2", True),
"(is(A, 1, True) OR is(B, 2, True))"),
(expression.orExpression((
expression.isExpression("A", "1", True),
expression.isExpression("B", "2", True),
)), expression.isExpression("C", "3", True),
"(is(A, 1, True) OR is(B, 2, True) OR is(C, 3, True))"),
)
for expr1, expr2, result in tests:
self.assertEqual(str(expr1.orWith(expr2)),
result,
msg="Failed on %s" % (result, ))
