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 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 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, ))