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_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_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_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, tzinfo=tzutc()), datetime.datetime(2006, 6, 5, 16, 0, tzinfo=tzutc()), True, datetime.datetime(2006, 6, 5, 13, 0, tzinfo=tzutc()), datetime.datetime(2006, 6, 5, 12, 0, tzinfo=tzutc()), 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)
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, tzinfo=tzutc()), datetime.datetime(2006, 6, 5, 16, 0, tzinfo=tzutc()), True, datetime.datetime(2006, 6, 5, 13, 0, tzinfo=tzutc()), datetime.datetime(2006, 6, 5, 12, 0, tzinfo=tzutc()), 1234]
))
self.assertEqual(args, {"arg1": ("VEVENT", "VFREEBUSY", "VAVAILABILITY")})
self.assertEqual(usedtimerange, True)
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)
