def test_query_not_extended(self):
"""
Query test - two terms not anyof
"""
filter = caldavxml.Filter(
caldavxml.ComponentFilter(
*[
caldavxml.ComponentFilter(**{"name": ("VEVENT")}),
caldavxml.ComponentFilter(**{"name": ("VTODO")}),
], **{"name": "VCALENDAR"}))
filter = Filter(filter)
filter.child.settzinfo(Timezone(tzid="America/New_York"))
expression = buildExpression(filter, self._queryFields)
sql = CalDAVSQLQueryGenerator(expression, self, 1234)
select, args, usedtimerange = sql.generate()
self.assertEqual(
select.toSQL(),
SQLFragment(
"select distinct RESOURCE_NAME, ICALENDAR_UID, ICALENDAR_TYPE from CALENDAR_OBJECT where CALENDAR_RESOURCE_ID = ? and ICALENDAR_TYPE = ? and ICALENDAR_TYPE = ?",
[1234, "VEVENT", "VTODO"]))
self.assertEqual(args, {})
self.assertEqual(usedtimerange, False)
def test_query(self):
"""
Basic query test - no time range
"""
filter = caldavxml.Filter(
caldavxml.ComponentFilter(
*[
caldavxml.ComponentFilter(
**{"name": ("VEVENT", "VFREEBUSY", "VAVAILABILITY")})
], **{"name": "VCALENDAR"}))
filter = Filter(filter)
filter.child.settzinfo(Timezone(tzid="America/New_York"))
expression = buildExpression(filter, self._queryFields)
sql = CalDAVSQLQueryGenerator(expression, self, 1234)
select, args, usedtimerange = sql.generate()
self.assertEqual(
select.toSQL(),
SQLFragment(
"select distinct RESOURCE_NAME, ICALENDAR_UID, ICALENDAR_TYPE from CALENDAR_OBJECT where CALENDAR_RESOURCE_ID = ? and ICALENDAR_TYPE in (?, ?, ?)",
[1234, Parameter('arg1', 3)]))
self.assertEqual(args,
{"arg1": ("VEVENT", "VFREEBUSY", "VAVAILABILITY")})
self.assertEqual(usedtimerange, False)
def test_all_query(self):
expr = expression.allExpression()
resource = self.FakeHomeChild()
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 = ?",
[1234]))
self.assertEqual(args, {})
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 test_in_query(self):
resource = self.FakeHomeChild()
obj = resource._objectSchema
expr = expression.inExpression(obj.RESOURCE_NAME,
["1.ics", "2.ics", "3.ics"], 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 RESOURCE_NAME in (?, ?, ?)",
[1234, Parameter('arg1', 3)]))
self.assertEqual(args, {"arg1": ["1.ics", "2.ics", "3.ics"]})
def test_query(self):
"""
Basic query test - single term.
Only UID can be queried via sql.
"""
filter = carddavxml.Filter(
*[carddavxml.PropertyFilter(
carddavxml.TextMatch.fromString("Example"),
**{"name": "UID"}
)]
)
filter = Filter(filter)
expression = buildExpression(filter, self._queryFields)
sql = SQLQueryGenerator(expression, self, 1234)
select, args = sql.generate()
self.assertEqual(select.toSQL(), SQLFragment("select distinct RESOURCE_NAME, VCARD_UID from ADDRESSBOOK_OBJECT where ADDRESSBOOK_HOME_RESOURCE_ID = ? and VCARD_UID like (? || (? || ?))", [1234, "%", "Example", "%"]))
self.assertEqual(args, {})
def test_query_freebusy(self):
"""
Basic query test - with time range
"""
filter = caldavxml.Filter(
caldavxml.ComponentFilter(
*[
caldavxml.ComponentFilter(
*[
caldavxml.TimeRange(
**{
"start": "20060605T160000Z",
"end": "20060605T170000Z"
})
],
**{"name": ("VEVENT", "VFREEBUSY", "VAVAILABILITY")})
], **{"name": "VCALENDAR"}))
filter = Filter(filter)
filter.child.settzinfo(Timezone(tzid="America/New_York"))
expression = buildExpression(filter, self._queryFields)
sql = CalDAVSQLQueryGenerator(expression, self, 1234, "user01", True)
select, args, usedtimerange = sql.generate()
self.assertEqual(
select.toSQL(),
SQLFragment(
"select distinct RESOURCE_NAME, ICALENDAR_UID, ICALENDAR_TYPE, ORGANIZER, FLOATING, coalesce(ADJUSTED_START_DATE, START_DATE), coalesce(ADJUSTED_END_DATE, END_DATE), FBTYPE, TIME_RANGE.TRANSPARENT, PERUSER.TRANSPARENT from CALENDAR_OBJECT, TIME_RANGE left outer join PERUSER on INSTANCE_ID = TIME_RANGE_INSTANCE_ID and USER_ID = ? where ICALENDAR_TYPE in (?, ?, ?) and (FLOATING = ? and coalesce(ADJUSTED_START_DATE, START_DATE) < ? and coalesce(ADJUSTED_END_DATE, END_DATE) > ? or FLOATING = ? and coalesce(ADJUSTED_START_DATE, START_DATE) < ? and coalesce(ADJUSTED_END_DATE, END_DATE) > ?) and CALENDAR_OBJECT_RESOURCE_ID = RESOURCE_ID and TIME_RANGE.CALENDAR_RESOURCE_ID = ?",
[
'user01',
Parameter('arg1', 3), False,
datetime.datetime(2006, 6, 5, 17, 0),
datetime.datetime(2006, 6, 5, 16, 0), True,
datetime.datetime(2006, 6, 5, 13, 0),
datetime.datetime(2006, 6, 5, 12, 0), 1234
]))
self.assertEqual(args,
{"arg1": ("VEVENT", "VFREEBUSY", "VAVAILABILITY")})
self.assertEqual(usedtimerange, True)
def test_query_timerange(self):
"""
Basic query test - with time range
"""
filter = caldavxml.Filter(
caldavxml.ComponentFilter(
*[
caldavxml.ComponentFilter(
*[
caldavxml.TimeRange(
**{
"start": "20060605T160000Z",
"end": "20060605T170000Z"
})
],
**{"name": ("VEVENT", "VFREEBUSY", "VAVAILABILITY")})
], **{"name": "VCALENDAR"}))
filter = Filter(filter)
filter.child.settzinfo(Timezone(tzid="America/New_York"))
expression = buildExpression(filter, self._queryFields)
sql = CalDAVSQLQueryGenerator(expression, self, 1234)
select, args, usedtimerange = sql.generate()
self.assertEqual(
select.toSQL(),
SQLFragment(
"select distinct RESOURCE_NAME, ICALENDAR_UID, ICALENDAR_TYPE from CALENDAR_OBJECT, TIME_RANGE where ICALENDAR_TYPE in (?, ?, ?) and (FLOATING = ? and START_DATE < ? and END_DATE > ? or FLOATING = ? and START_DATE < ? and END_DATE > ?) and CALENDAR_OBJECT_RESOURCE_ID = RESOURCE_ID and TIME_RANGE.CALENDAR_RESOURCE_ID = ?",
[
Parameter('arg1', 3), False,
datetime.datetime(2006, 6, 5, 17, 0),
datetime.datetime(2006, 6, 5, 16, 0), True,
datetime.datetime(2006, 6, 5, 13, 0),
datetime.datetime(2006, 6, 5, 12, 0), 1234
]))
self.assertEqual(args,
{"arg1": ("VEVENT", "VFREEBUSY", "VAVAILABILITY")})
self.assertEqual(usedtimerange, True)
def test_query_extended(self):
"""
Extended query test - two terms with anyof
"""
filter = caldavxml.Filter(
caldavxml.ComponentFilter(
*[
caldavxml.ComponentFilter(
*[
caldavxml.TimeRange(**{
"start": "20060605T160000Z",
})
], **{"name": ("VEVENT")}),
caldavxml.ComponentFilter(**{"name": ("VTODO")}),
], **{
"name": "VCALENDAR",
"test": "anyof"
}))
filter = Filter(filter)
filter.child.settzinfo(Timezone(tzid="America/New_York"))
expression = buildExpression(filter, self._queryFields)
sql = CalDAVSQLQueryGenerator(expression, self, 1234)
select, args, usedtimerange = sql.generate()
self.assertEqual(
select.toSQL(),
SQLFragment(
"select distinct RESOURCE_NAME, ICALENDAR_UID, ICALENDAR_TYPE from CALENDAR_OBJECT, TIME_RANGE where (ICALENDAR_TYPE = ? and (FLOATING = ? and END_DATE > ? or FLOATING = ? and END_DATE > ?) or ICALENDAR_TYPE = ?) and CALENDAR_OBJECT_RESOURCE_ID = RESOURCE_ID and TIME_RANGE.CALENDAR_RESOURCE_ID = ?",
[
'VEVENT', False,
datetime.datetime(2006, 6, 5, 16, 0, tzinfo=tzutc()), True,
datetime.datetime(2006, 6, 5, 12, 0, tzinfo=tzutc()),
'VTODO', 1234
]))
self.assertEqual(args, {})
self.assertEqual(usedtimerange, True)