[CalendarServer-changes] [8825] CalendarServer/trunk
source_changes at macosforge.org
source_changes at macosforge.org
Wed Mar 7 14:51:01 PST 2012
Revision: 8825
http://trac.macosforge.org/projects/calendarserver/changeset/8825
Author: cdaboo at apple.com
Date: 2012-03-07 14:51:01 -0800 (Wed, 07 Mar 2012)
Log Message:
-----------
Revert various bits of r8806 so that we no longer case fold in txdav. Also added some enhancements to calverify.
Revision Links:
--------------
http://trac.macosforge.org/projects/calendarserver/changeset/8806
Modified Paths:
--------------
CalendarServer/trunk/calendarserver/tools/calverify.py
CalendarServer/trunk/calendarserver/tools/dbinspect.py
CalendarServer/trunk/contrib/tools/sqldata_from_path.py
CalendarServer/trunk/twistedcaldav/sharing.py
CalendarServer/trunk/txdav/common/datastore/sql.py
CalendarServer/trunk/txdav/common/datastore/sql_legacy.py
CalendarServer/trunk/txdav/common/datastore/upgrade/sql/upgrade.py
Added Paths:
-----------
CalendarServer/trunk/txdav/common/datastore/sql_schema/current.sql
Removed Paths:
-------------
CalendarServer/trunk/txdav/common/datastore/sql_schema/current.sql
CalendarServer/trunk/txdav/common/datastore/sql_schema/old/v8.sql
CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_8_to_9.sql
CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_8_to_9.sql
Modified: CalendarServer/trunk/calendarserver/tools/calverify.py
===================================================================
--- CalendarServer/trunk/calendarserver/tools/calverify.py 2012-03-06 23:53:13 UTC (rev 8824)
+++ CalendarServer/trunk/calendarserver/tools/calverify.py 2012-03-07 22:51:01 UTC (rev 8825)
@@ -46,7 +46,7 @@
from pycalendar.datetime import PyCalendarDateTime
from pycalendar.exceptions import PyCalendarError
from pycalendar.period import PyCalendarPeriod
-from twext.enterprise.dal.syntax import Select, Parameter, Count, CaseFold
+from twext.enterprise.dal.syntax import Select, Parameter, Count
from twisted.application.service import Service
from twisted.internet.defer import inlineCallbacks, returnValue, succeed
from twisted.python import log
@@ -63,7 +63,6 @@
import sys
import time
-
def usage(e=None):
if e:
print e
@@ -100,12 +99,14 @@
['missing', 'm', "Show 'orphaned' homes."],
['fix', 'x', "Fix problems."],
['verbose', 'v', "Verbose logging."],
+ ['details', 'V', "Detailed logging."],
]
optParameters = [
['config', 'f', DEFAULT_CONFIG_FILE, "Specify caldavd.plist configuration path."],
['data', 'd', "./calverify-data", "Path where ancillary data is stored."],
['uuid', 'u', "", "Only check this user."],
+ ['uid', 'U', "", "Only this event UID."],
]
@@ -267,7 +268,7 @@
cb, type="inner", on=(ch.RESOURCE_ID == cb.CALENDAR_HOME_RESOURCE_ID).And(
cb.BIND_MODE == _BIND_MODE_OWN)).join(
co, type="inner", on=(cb.CALENDAR_RESOURCE_ID == co.CALENDAR_RESOURCE_ID)),
- Where=(ch.OWNER_UID == CaseFold(Parameter("UID")))
+ Where=(ch.OWNER_UID == Parameter("UID"))
).on(self.txn, **kwds))
returnValue(int(rows[0][0]) if rows else 0)
@@ -287,19 +288,27 @@
if self.options["verbose"]:
t = time.time()
+ descriptor = None
if ical:
if self.options["uuid"]:
rows = yield self.getAllResourceInfoWithUUID(self.options["uuid"])
+ descriptor = "getAllResourceInfoWithUUID"
else:
rows = yield self.getAllResourceInfo()
+ descriptor = "getAllResourceInfo"
else:
- rows = yield self.getAllResourceInfoTimeRange(self.start)
+ if self.options["uid"]:
+ rows = yield self.getAllResourceInfoWithUID(self.options["uid"])
+ descriptor = "getAllResourceInfoWithUID"
+ else:
+ rows = yield self.getAllResourceInfoTimeRange(self.start)
+ descriptor = "getAllResourceInfoTimeRange"
yield self.txn.commit()
self.txn = None
if self.options["verbose"]:
- print "getAllResourceInfoTimeRange time: %.1fs" % (time.time() - t,)
+ print "%s time: %.1fs" % (descriptor, time.time() - t,)
print "Number of events to process: %s" % (len(rows,))
# Split into organizer events and attendee events
@@ -308,13 +317,13 @@
self.attended = []
self.attended_byuid = collections.defaultdict(list)
self.matched_attendee_to_organizer = collections.defaultdict(set)
- for owner, resid, uid, md5, organizer in rows:
+ for owner, resid, uid, md5, organizer, created, modified in rows:
if organizer.startswith("urn:uuid:") and owner == organizer[9:]:
- self.organized.append((owner, resid, uid, md5, organizer,))
- self.organized_byuid[uid] = (owner, resid, uid, md5, organizer,)
+ self.organized.append((owner, resid, uid, md5, organizer, created, modified,))
+ self.organized_byuid[uid] = (owner, resid, uid, md5, organizer, created, modified,)
else:
- self.attended.append((owner, resid, uid, md5, organizer,))
- self.attended_byuid[uid].append((owner, resid, uid, md5, organizer,))
+ self.attended.append((owner, resid, uid, md5, organizer, created, modified,))
+ self.attended_byuid[uid].append((owner, resid, uid, md5, organizer, created, modified,))
print "Number of organizer events to process: %s" % (len(self.organized),)
print "Number of attendee events to process: %s" % (len(self.attended,))
@@ -335,13 +344,13 @@
ch = schema.CALENDAR_HOME
kwds = {}
rows = (yield Select(
- [ch.OWNER_UID, co.RESOURCE_ID, co.ICALENDAR_UID, co.MD5, co.ORGANIZER,],
+ [ch.OWNER_UID, co.RESOURCE_ID, co.ICALENDAR_UID, co.MD5, co.ORGANIZER, co.CREATED, co.MODIFIED],
From=ch.join(
cb, type="inner", on=(ch.RESOURCE_ID == cb.CALENDAR_HOME_RESOURCE_ID)).join(
co, type="inner", on=(cb.CALENDAR_RESOURCE_ID == co.CALENDAR_RESOURCE_ID).And(
cb.BIND_MODE == _BIND_MODE_OWN).And(
cb.CALENDAR_RESOURCE_NAME != "inbox")),
- GroupBy=(ch.OWNER_UID, co.RESOURCE_ID, co.ICALENDAR_UID, co.MD5, co.ORGANIZER,),
+ GroupBy=(ch.OWNER_UID, co.RESOURCE_ID, co.ICALENDAR_UID, co.MD5, co.ORGANIZER, co.CREATED, co.MODIFIED,),
).on(self.txn, **kwds))
returnValue(tuple(rows))
@@ -353,18 +362,18 @@
ch = schema.CALENDAR_HOME
kwds = {"uuid": uuid}
if len(uuid) != 36:
- where = (ch.OWNER_UID.StartsWith(CaseFold(Parameter("uuid"))))
+ where = (ch.OWNER_UID.StartsWith(Parameter("uuid")))
else:
- where = (ch.OWNER_UID == CaseFold(Parameter("uuid")))
+ where = (ch.OWNER_UID == Parameter("uuid"))
rows = (yield Select(
- [ch.OWNER_UID, co.RESOURCE_ID, co.ICALENDAR_UID, co.MD5, co.ORGANIZER,],
+ [ch.OWNER_UID, co.RESOURCE_ID, co.ICALENDAR_UID, co.MD5, co.ORGANIZER, co.CREATED, co.MODIFIED],
From=ch.join(
cb, type="inner", on=(ch.RESOURCE_ID == cb.CALENDAR_HOME_RESOURCE_ID)).join(
co, type="inner", on=(cb.CALENDAR_RESOURCE_ID == co.CALENDAR_RESOURCE_ID).And(
cb.BIND_MODE == _BIND_MODE_OWN).And(
cb.CALENDAR_RESOURCE_NAME != "inbox")),
Where=where,
- GroupBy=(ch.OWNER_UID, co.RESOURCE_ID, co.ICALENDAR_UID, co.MD5, co.ORGANIZER,),
+ GroupBy=(ch.OWNER_UID, co.RESOURCE_ID, co.ICALENDAR_UID, co.MD5, co.ORGANIZER, co.CREATED, co.MODIFIED,),
).on(self.txn, **kwds))
returnValue(tuple(rows))
@@ -380,7 +389,7 @@
"Max" : pyCalendarTodatetime(PyCalendarDateTime(1900, 1, 1, 0, 0, 0))
}
rows = (yield Select(
- [ch.OWNER_UID, co.RESOURCE_ID, co.ICALENDAR_UID, co.MD5, co.ORGANIZER,],
+ [ch.OWNER_UID, co.RESOURCE_ID, co.ICALENDAR_UID, co.MD5, co.ORGANIZER, co.CREATED, co.MODIFIED],
From=ch.join(
cb, type="inner", on=(ch.RESOURCE_ID == cb.CALENDAR_HOME_RESOURCE_ID)).join(
co, type="inner", on=(cb.CALENDAR_RESOURCE_ID == co.CALENDAR_RESOURCE_ID).And(
@@ -389,12 +398,33 @@
co.ORGANIZER != "")).join(
tr, type="left", on=(co.RESOURCE_ID == tr.CALENDAR_OBJECT_RESOURCE_ID)),
Where=(tr.START_DATE >= Parameter("Start")).Or(co.RECURRANCE_MAX == Parameter("Max")),
- GroupBy=(ch.OWNER_UID, co.RESOURCE_ID, co.ICALENDAR_UID, co.MD5, co.ORGANIZER,),
+ GroupBy=(ch.OWNER_UID, co.RESOURCE_ID, co.ICALENDAR_UID, co.MD5, co.ORGANIZER, co.CREATED, co.MODIFIED,),
).on(self.txn, **kwds))
returnValue(tuple(rows))
@inlineCallbacks
+ def getAllResourceInfoWithUID(self, uid):
+ co = schema.CALENDAR_OBJECT
+ cb = schema.CALENDAR_BIND
+ ch = schema.CALENDAR_HOME
+ kwds = {
+ "UID" : uid,
+ }
+ rows = (yield Select(
+ [ch.OWNER_UID, co.RESOURCE_ID, co.ICALENDAR_UID, co.MD5, co.ORGANIZER, co.CREATED, co.MODIFIED],
+ From=ch.join(
+ cb, type="inner", on=(ch.RESOURCE_ID == cb.CALENDAR_HOME_RESOURCE_ID)).join(
+ co, type="inner", on=(cb.CALENDAR_RESOURCE_ID == co.CALENDAR_RESOURCE_ID).And(
+ cb.BIND_MODE == _BIND_MODE_OWN).And(
+ cb.CALENDAR_RESOURCE_NAME != "inbox")),
+ Where=(co.ICALENDAR_UID == Parameter("UID")),
+ GroupBy=(ch.OWNER_UID, co.RESOURCE_ID, co.ICALENDAR_UID, co.MD5, co.ORGANIZER, co.CREATED, co.MODIFIED,),
+ ).on(self.txn, **kwds))
+ returnValue(tuple(rows))
+
+
+ @inlineCallbacks
def getAllResourceInfoForResourceID(self, resid):
co = schema.CALENDAR_OBJECT
cb = schema.CALENDAR_BIND
@@ -428,7 +458,7 @@
count = 0
total = len(rows)
badlen = 0
- for owner, resid, uid, _ignore_md5, _ignore_organizer in rows:
+ for owner, resid, uid, _ignore_md5, _ignore_organizer, _ignore_created, _ignore_modified in rows:
result, message = yield self.validCalendarData(resid)
if not result:
results_bad.append((owner, uid, resid, message))
@@ -591,7 +621,7 @@
self.txn = self.store.newTransaction()
# Get the organizer's view of attendee states
- organizer, resid, uid, _ignore_md5, _ignore_organizer = organizerEvent
+ organizer, resid, uid, _ignore_md5, _ignore_organizer, org_created, org_modified = organizerEvent
calendar = yield self.getCalendar(resid)
if calendar is None:
continue
@@ -607,12 +637,12 @@
# Get attendee states for matching UID
eachAttendeesOwnStatus = {}
for attendeeEvent in self.attended_byuid.get(uid, ()):
- owner, resid, uid, _ignore_md5, _ignore_organizer = attendeeEvent
- calendar = yield self.getCalendar(resid)
+ owner, attresid, uid, _ignore_md5, _ignore_organizer, att_created, att_modified = attendeeEvent
+ calendar = yield self.getCalendar(attresid)
if calendar is None:
continue
eachAttendeesOwnStatus[owner] = self.buildAttendeeStates(calendar, self.start, self.end, attendee_only=owner)
- attendeeResIDs[(owner, uid)] = resid
+ attendeeResIDs[(owner, uid)] = attresid
# Look at each attendee in the organizer's meeting
for organizerAttendee, organizerViewOfStatus in organizerViewOfAttendees.iteritems():
@@ -630,24 +660,35 @@
if organizerViewOfStatus != attendeeOwnStatus:
# Check that the difference is only cancelled or declined on the organizers side
- for _ignore_organizerInstance, partstat in organizerViewOfStatus.difference(attendeeOwnStatus):
+ for _organizerInstance, partstat in organizerViewOfStatus.difference(attendeeOwnStatus):
if partstat not in ("DECLINED", "CANCELLED"):
- results_mismatch.append((uid, resid, organizer, organizerAttendee))
+ results_mismatch.append((uid, resid, organizer, org_created, org_modified, organizerAttendee, att_created, att_modified))
broken = True
+ if self.options["details"]:
+ print "Mismatch: on Organizer's side:"
+ print " UID: %s" % (uid,)
+ print " Organizer: %s" % (organizer,)
+ print " Attendee: %s" % (organizerAttendee,)
+ print " Instance: %s" % (_organizerInstance,)
break
# Check that the difference is only cancelled on the attendees side
- for _ignore_attendeeInstance, partstat in attendeeOwnStatus.difference(organizerViewOfStatus):
+ for _attendeeInstance, partstat in attendeeOwnStatus.difference(organizerViewOfStatus):
if partstat not in ("CANCELLED",):
if not broken:
- results_mismatch.append((uid, resid, organizer, organizerAttendee))
+ results_mismatch.append((uid, resid, organizer, org_created, org_modified, organizerAttendee, att_created, att_modified))
broken = True
+ if self.options["details"]:
+ print "Mismatch: on Attendee's side:"
+ print " Organizer: %s" % (organizer,)
+ print " Attendee: %s" % (organizerAttendee,)
+ print " Instance: %s" % (_attendeeInstance,)
break
# Check that the status for this attendee is always declined which means a missing copy of the event is OK
else:
for _ignore_instance_id, partstat in organizerViewOfStatus:
if partstat not in ("DECLINED", "CANCELLED"):
- results_missing.append((uid, resid, organizer, organizerAttendee,))
+ results_missing.append((uid, resid, organizer, organizerAttendee, org_created, org_modified))
broken = True
break
@@ -662,9 +703,10 @@
# Print table of results
table = tables.Table()
- table.addHeader(("Organizer", "Attendee", "Event UID", "Organizer RID",))
+ table.addHeader(("Organizer", "Attendee", "Event UID", "Organizer RID", "Created", "Modified",))
+ results_missing.sort()
for item in results_missing:
- uid, resid, organizer, attendee = item
+ uid, resid, organizer, attendee, created, modified = item
organizer_record = self.directoryService().recordWithGUID(organizer)
attendee_record = self.directoryService().recordWithGUID(attendee)
table.addRow((
@@ -672,6 +714,8 @@
"%s/%s (%s)" % (attendee_record.recordType if attendee_record else "-", attendee_record.shortNames[0] if attendee_record else "-", attendee,),
uid,
resid,
+ created,
+ modified,
))
self.output.write("\n")
@@ -680,9 +724,10 @@
# Print table of results
table = tables.Table()
- table.addHeader(("Organizer", "Attendee", "Event UID", "Organizer RID", "Attendee RID",))
+ table.addHeader(("Organizer", "Attendee", "Event UID", "Organizer RID", "Created", "Modified", "Attendee RID", "Created", "Modified",))
+ results_mismatch.sort()
for item in results_mismatch:
- uid, org_resid, organizer, attendee = item
+ uid, org_resid, organizer, org_created, org_modified, attendee, att_created, att_modified = item
organizer_record = self.directoryService().recordWithGUID(organizer)
attendee_record = self.directoryService().recordWithGUID(attendee)
table.addRow((
@@ -690,7 +735,11 @@
"%s/%s (%s)" % (attendee_record.recordType if attendee_record else "-", attendee_record.shortNames[0] if attendee_record else "-", attendee,),
uid,
org_resid,
+ org_created,
+ org_modified,
attendeeResIDs[(attendee, uid)],
+ att_created,
+ att_modified,
))
self.output.write("\n")
@@ -729,7 +778,7 @@
yield self.txn.commit()
self.txn = self.store.newTransaction()
- attendee, resid, uid, _ignore_md5, organizer = attendeeEvent
+ attendee, resid, uid, _ignore_md5, organizer, att_created, att_modified = attendeeEvent
calendar = yield self.getCalendar(resid)
if calendar is None:
continue
@@ -752,7 +801,7 @@
if self.allCancelled(eachAttendeesOwnStatus):
continue
- missing.append((uid, attendee, organizer, resid,))
+ missing.append((uid, attendee, organizer, resid, att_created, att_modified,))
# If there is a miss we fix by removing the attendee data
if self.fix:
@@ -764,7 +813,7 @@
if self.allCancelled(eachAttendeesOwnStatus):
continue
- mismatched.append((uid, attendee, organizer, resid,))
+ mismatched.append((uid, attendee, organizer, resid, att_created, att_modified,))
# If there is a mismatch we fix by re-inviting the attendee
if self.fix:
@@ -776,11 +825,11 @@
# Print table of results
table = tables.Table()
- table.addHeader(("Organizer", "Attendee", "UID", "Attendee RID",))
+ table.addHeader(("Organizer", "Attendee", "UID", "Attendee RID", "Created", "Modified",))
missing.sort()
unique_set = set()
for item in missing:
- uid, attendee, organizer, resid = item
+ uid, attendee, organizer, resid, created, modified = item
unique_set.add(uid)
if organizer:
organizerRecord = self.directoryService().recordWithGUID(organizer)
@@ -791,6 +840,8 @@
"%s/%s (%s)" % (attendeeRecord.recordType if attendeeRecord else "-", attendeeRecord.shortNames[0] if attendeeRecord else "-", attendee,),
uid,
resid,
+ created,
+ modified,
))
self.output.write("\n")
@@ -799,10 +850,10 @@
# Print table of results
table = tables.Table()
- table.addHeader(("Organizer", "Attendee", "UID", "Organizer RID", "Attendee RID",))
+ table.addHeader(("Organizer", "Attendee", "UID", "Organizer RID", "Created", "Modified", "Attendee RID", "Created", "Modified",))
mismatched.sort()
for item in mismatched:
- uid, attendee, organizer, resid = item
+ uid, attendee, organizer, resid, att_created, att_modified = item
if organizer:
organizerRecord = self.directoryService().recordWithGUID(organizer)
organizer = "%s/%s (%s)" % (organizerRecord.recordType if organizerRecord else "-", organizerRecord.shortNames[0] if organizerRecord else "-", organizer,)
@@ -812,7 +863,11 @@
"%s/%s (%s)" % (attendeeRecord.recordType if attendeeRecord else "-", attendeeRecord.shortNames[0] if attendeeRecord else "-", attendee,),
uid,
self.organized_byuid[uid][1],
+ self.organized_byuid[uid][5],
+ self.organized_byuid[uid][6],
resid,
+ att_created,
+ att_modified,
))
self.output.write("\n")
@@ -897,7 +952,7 @@
all_cancelled = True
for _ignore_guid, states in attendeesStatus.iteritems():
for _ignore_instance_id, partstat in states:
- if partstat not in ("CANCELLED",):
+ if partstat not in ("CANCELLED", "DECLINED",):
all_cancelled = False
break
if not all_cancelled:
Modified: CalendarServer/trunk/calendarserver/tools/dbinspect.py
===================================================================
--- CalendarServer/trunk/calendarserver/tools/dbinspect.py 2012-03-06 23:53:13 UTC (rev 8824)
+++ CalendarServer/trunk/calendarserver/tools/dbinspect.py 2012-03-07 22:51:01 UTC (rev 8825)
@@ -43,7 +43,6 @@
import os
import sys
import traceback
-from twext.enterprise.dal.syntax import CaseFold
def usage(e=None):
if e:
@@ -521,7 +520,7 @@
cb, type="inner", on=(ch.RESOURCE_ID == cb.CALENDAR_HOME_RESOURCE_ID).And(
cb.BIND_MODE == _BIND_MODE_OWN)).join(
co, type="inner", on=(cb.CALENDAR_RESOURCE_ID == co.CALENDAR_RESOURCE_ID)),
- Where=(ch.OWNER_UID == CaseFold(Parameter("UID"))),
+ Where=(ch.OWNER_UID == Parameter("UID")),
).on(txn, **{"UID": uid}))
returnValue(tuple(rows))
@@ -572,7 +571,7 @@
cb, type="inner", on=(ch.RESOURCE_ID == cb.CALENDAR_HOME_RESOURCE_ID).And(
cb.BIND_MODE == _BIND_MODE_OWN)).join(
co, type="inner", on=(cb.CALENDAR_RESOURCE_ID == co.CALENDAR_RESOURCE_ID)),
- Where=((ch.OWNER_UID == CaseFold(Parameter("UID"))).And(cb.CALENDAR_RESOURCE_NAME == Parameter("NAME"))),
+ Where=((ch.OWNER_UID == Parameter("UID")).And(cb.CALENDAR_RESOURCE_NAME == Parameter("NAME"))),
).on(txn, **{"UID": uid, "NAME": name}))
returnValue(tuple(rows))
Modified: CalendarServer/trunk/contrib/tools/sqldata_from_path.py
===================================================================
--- CalendarServer/trunk/contrib/tools/sqldata_from_path.py 2012-03-06 23:53:13 UTC (rev 8824)
+++ CalendarServer/trunk/contrib/tools/sqldata_from_path.py 2012-03-07 22:51:01 UTC (rev 8825)
@@ -109,7 +109,7 @@
%(object_name)s = '%(resource)s' and %(object_bind_id)s = (
select %(bind_id)s from %(bind_table)s where
%(bind_name)s = '%(collection)s' and %(bind_home_id)s = (
- select RESOURCE_ID from %(home_table)s where OWNER_UID = lower('%(uid)s')
+ select RESOURCE_ID from %(home_table)s where OWNER_UID = '%(uid)s'
)
);""" % sqlstrings[datatype]
Modified: CalendarServer/trunk/twistedcaldav/sharing.py
===================================================================
--- CalendarServer/trunk/twistedcaldav/sharing.py 2012-03-06 23:53:13 UTC (rev 8824)
+++ CalendarServer/trunk/twistedcaldav/sharing.py 2012-03-07 22:51:01 UTC (rev 8825)
@@ -115,7 +115,7 @@
principalUID = principalURL.split("/")[3]
record = yield self.invitesDB().recordForInviteUID(inviteUID)
- if record is None or record.principalUID.lower() != principalUID.lower():
+ if record is None or record.principalUID != principalUID:
raise HTTPError(ErrorResponse(
responsecode.FORBIDDEN,
(customxml.calendarserver_namespace, "valid-request"),
Modified: CalendarServer/trunk/txdav/common/datastore/sql.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql.py 2012-03-06 23:53:13 UTC (rev 8824)
+++ CalendarServer/trunk/txdav/common/datastore/sql.py 2012-03-07 22:51:01 UTC (rev 8825)
@@ -71,7 +71,6 @@
from twext.enterprise.dal.syntax import SavepointAction
from twext.enterprise.dal.syntax import Select
from twext.enterprise.dal.syntax import Update
-from twext.enterprise.dal.syntax import CaseFold
from txdav.base.propertystore.base import PropertyName
from txdav.base.propertystore.none import PropertyStore as NonePropertyStore
@@ -843,7 +842,7 @@
def _resourceIDFromOwnerQuery(cls): #@NoSelf
home = cls._homeSchema
return Select([home.RESOURCE_ID],
- From=home, Where=home.OWNER_UID == CaseFold(Parameter("ownerUID")))
+ From=home, Where=home.OWNER_UID == Parameter("ownerUID"))
@classproperty
def _ownerFromFromResourceID(cls): #@NoSelf
@@ -910,7 +909,7 @@
try:
resourceid = (yield Insert(
{
- cls._homeSchema.OWNER_UID: CaseFold(uid),
+ cls._homeSchema.OWNER_UID: uid,
cls._homeSchema.DATAVERSION: cls._dataVersionValue,
},
Return=cls._homeSchema.RESOURCE_ID).on(txn))[0][0]
@@ -3043,11 +3042,11 @@
_resourceIDFromUIDQuery = Select(
[_homeSchema.RESOURCE_ID], From=_homeSchema,
- Where=_homeSchema.OWNER_UID == CaseFold(Parameter("uid")))
+ Where=_homeSchema.OWNER_UID == Parameter("uid"))
_provisionNewNotificationsQuery = Insert(
- {_homeSchema.OWNER_UID: CaseFold(Parameter("uid"))},
+ {_homeSchema.OWNER_UID: Parameter("uid")},
Return=_homeSchema.RESOURCE_ID
)
Modified: CalendarServer/trunk/txdav/common/datastore/sql_legacy.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_legacy.py 2012-03-06 23:53:13 UTC (rev 8824)
+++ CalendarServer/trunk/txdav/common/datastore/sql_legacy.py 2012-03-07 22:51:01 UTC (rev 8825)
@@ -56,7 +56,6 @@
from pycalendar.duration import PyCalendarDuration
-from twext.enterprise.dal.syntax import CaseFold
log = Logger()
@@ -221,8 +220,7 @@
inv = schema.INVITE
home = cls._homeSchema
return cls._allColumnsQuery(
- (inv.RESOURCE_ID == Parameter("resourceID"))
- .And(home.OWNER_UID == CaseFold(Parameter("principalUID")))
+ (inv.RESOURCE_ID == Parameter("resourceID")).And(home.OWNER_UID == Parameter("principalUID"))
)
Deleted: CalendarServer/trunk/txdav/common/datastore/sql_schema/current.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/current.sql 2012-03-06 23:53:13 UTC (rev 8824)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/current.sql 2012-03-07 22:51:01 UTC (rev 8825)
@@ -1,502 +0,0 @@
--- -*- test-case-name: txdav.caldav.datastore.test.test_sql,txdav.carddav.datastore.test.test_sql -*-
-
-----
--- Copyright (c) 2010-2012 Apple Inc. All rights reserved.
---
--- Licensed under the Apache License, Version 2.0 (the "License");
--- you may not use this file except in compliance with the License.
--- You may obtain a copy of the License at
---
--- http://www.apache.org/licenses/LICENSE-2.0
---
--- Unless required by applicable law or agreed to in writing, software
--- distributed under the License is distributed on an "AS IS" BASIS,
--- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--- See the License for the specific language governing permissions and
--- limitations under the License.
-----
-
------------------
--- Resource ID --
------------------
-
-create sequence RESOURCE_ID_SEQ;
-
-
--------------------
--- Calendar Home --
--------------------
-
-create table CALENDAR_HOME (
- RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
- OWNER_UID varchar(255) not null unique, -- implicit index
- DATAVERSION integer default 0 not null,
- constraint CALENDAR_HOME_CASE check(OWNER_UID = lower(OWNER_UID))
-);
-
-----------------------------
--- Calendar Home Metadata --
-----------------------------
-
-create table CALENDAR_HOME_METADATA (
- RESOURCE_ID integer primary key references CALENDAR_HOME on delete cascade, -- implicit index
- QUOTA_USED_BYTES integer default 0 not null,
- CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
-);
-
---------------
--- Calendar --
---------------
-
-create table CALENDAR (
- RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ') -- implicit index
-);
-
-
------------------------
--- Calendar Metadata --
------------------------
-
-create table CALENDAR_METADATA (
- RESOURCE_ID integer primary key references CALENDAR on delete cascade, -- implicit index
- SUPPORTED_COMPONENTS varchar(255) default null,
- CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
-);
-
-
-------------------------
--- Sharing Invitation --
-------------------------
-
-create table INVITE (
- INVITE_UID varchar(255) not null,
- NAME varchar(255) not null,
- RECIPIENT_ADDRESS varchar(255) not null,
- HOME_RESOURCE_ID integer not null,
- RESOURCE_ID integer not null
-
- -- Need primary key on (INVITE_UID, NAME, RECIPIENT_ADDRESS)?
-);
-
-create index INVITE_INVITE_UID on INVITE(INVITE_UID);
-create index INVITE_RESOURCE_ID on INVITE(RESOURCE_ID);
-create index INVITE_HOME_RESOURCE_ID on INVITE(HOME_RESOURCE_ID);
-
----------------------------
--- Sharing Notifications --
----------------------------
-
-create table NOTIFICATION_HOME (
- RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
- OWNER_UID varchar(255) not null unique, -- implicit index
- constraint NOTIFICATION_HOME_CASE check(OWNER_UID = lower(OWNER_UID))
-);
-
-create table NOTIFICATION (
- RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
- NOTIFICATION_HOME_RESOURCE_ID integer not null references NOTIFICATION_HOME,
- NOTIFICATION_UID varchar(255) not null,
- XML_TYPE varchar(255) not null,
- XML_DATA text not null,
- MD5 char(32) not null,
- CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
-
- unique(NOTIFICATION_UID, NOTIFICATION_HOME_RESOURCE_ID) -- implicit index
-);
-
-create index NOTIFICATION_NOTIFICATION_HOME_RESOURCE_ID on
- NOTIFICATION(NOTIFICATION_HOME_RESOURCE_ID);
-
--------------------
--- Calendar Bind --
--------------------
-
--- Joins CALENDAR_HOME and CALENDAR
-
-create table CALENDAR_BIND (
- CALENDAR_HOME_RESOURCE_ID integer not null references CALENDAR_HOME,
- CALENDAR_RESOURCE_ID integer not null references CALENDAR on delete cascade,
-
- -- An invitation which hasn't been accepted yet will not yet have a resource
- -- name, so this field may be null.
-
- CALENDAR_RESOURCE_NAME varchar(255),
- BIND_MODE integer not null, -- enum CALENDAR_BIND_MODE
- BIND_STATUS integer not null, -- enum CALENDAR_BIND_STATUS
- SEEN_BY_OWNER boolean not null,
- SEEN_BY_SHAREE boolean not null,
- MESSAGE text,
-
- primary key(CALENDAR_HOME_RESOURCE_ID, CALENDAR_RESOURCE_ID), -- implicit index
- unique(CALENDAR_HOME_RESOURCE_ID, CALENDAR_RESOURCE_NAME) -- implicit index
-);
-
-create index CALENDAR_BIND_RESOURCE_ID on CALENDAR_BIND(CALENDAR_RESOURCE_ID);
-
--- Enumeration of calendar bind modes
-
-create table CALENDAR_BIND_MODE (
- ID integer primary key,
- DESCRIPTION varchar(16) not null unique
-);
-
-insert into CALENDAR_BIND_MODE values (0, 'own' );
-insert into CALENDAR_BIND_MODE values (1, 'read' );
-insert into CALENDAR_BIND_MODE values (2, 'write');
-insert into CALENDAR_BIND_MODE values (3, 'direct');
-
--- Enumeration of statuses
-
-create table CALENDAR_BIND_STATUS (
- ID integer primary key,
- DESCRIPTION varchar(16) not null unique
-);
-
-insert into CALENDAR_BIND_STATUS values (0, 'invited' );
-insert into CALENDAR_BIND_STATUS values (1, 'accepted');
-insert into CALENDAR_BIND_STATUS values (2, 'declined');
-insert into CALENDAR_BIND_STATUS values (3, 'invalid');
-
-
----------------------
--- Calendar Object --
----------------------
-
-create table CALENDAR_OBJECT (
- RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
- CALENDAR_RESOURCE_ID integer not null references CALENDAR on delete cascade,
- RESOURCE_NAME varchar(255) not null,
- ICALENDAR_TEXT text not null,
- ICALENDAR_UID varchar(255) not null,
- ICALENDAR_TYPE varchar(255) not null,
- ATTACHMENTS_MODE integer default 0 not null, -- enum CALENDAR_OBJECT_ATTACHMENTS_MODE
- DROPBOX_ID varchar(255),
- ORGANIZER varchar(255),
- ORGANIZER_OBJECT integer references CALENDAR_OBJECT,
- RECURRANCE_MAX date, -- maximum date that recurrences have been expanded to.
- ACCESS integer default 0 not null,
- SCHEDULE_OBJECT boolean default false,
- SCHEDULE_TAG varchar(36) default null,
- SCHEDULE_ETAGS text default null,
- PRIVATE_COMMENTS boolean default false not null,
- MD5 char(32) not null,
- CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
-
- unique(CALENDAR_RESOURCE_ID, RESOURCE_NAME) -- implicit index
-
- -- since the 'inbox' is a 'calendar resource' for the purpose of storing
- -- calendar objects, this constraint has to be selectively enforced by the
- -- application layer.
-
- -- unique(CALENDAR_RESOURCE_ID, ICALENDAR_UID)
-);
-
-create index CALENDAR_OBJECT_CALENDAR_RESOURCE_ID_AND_ICALENDAR_UID on
- CALENDAR_OBJECT(CALENDAR_RESOURCE_ID, ICALENDAR_UID);
-
-create index CALENDAR_OBJECT_CALENDAR_RESOURCE_ID_RECURRANCE_MAX on
- CALENDAR_OBJECT(CALENDAR_RESOURCE_ID, RECURRANCE_MAX);
-
-create index CALENDAR_OBJECT_ORGANIZER_OBJECT on
- CALENDAR_OBJECT(ORGANIZER_OBJECT);
-
-create index CALENDAR_OBJECT_DROPBOX_ID on
- CALENDAR_OBJECT(DROPBOX_ID);
-
--- Enumeration of attachment modes
-
-create table CALENDAR_OBJECT_ATTACHMENTS_MODE (
- ID integer primary key,
- DESCRIPTION varchar(16) not null unique
-);
-
-insert into CALENDAR_OBJECT_ATTACHMENTS_MODE values (0, 'none' );
-insert into CALENDAR_OBJECT_ATTACHMENTS_MODE values (1, 'read' );
-insert into CALENDAR_OBJECT_ATTACHMENTS_MODE values (2, 'write');
-
-
--- Enumeration of calendar access types
-
-create table CALENDAR_ACCESS_TYPE (
- ID integer primary key,
- DESCRIPTION varchar(32) not null unique
-);
-
-insert into CALENDAR_ACCESS_TYPE values (0, '' );
-insert into CALENDAR_ACCESS_TYPE values (1, 'public' );
-insert into CALENDAR_ACCESS_TYPE values (2, 'private' );
-insert into CALENDAR_ACCESS_TYPE values (3, 'confidential' );
-insert into CALENDAR_ACCESS_TYPE values (4, 'restricted' );
-
------------------
--- Instance ID --
------------------
-
-create sequence INSTANCE_ID_SEQ;
-
-
-----------------
--- Time Range --
-----------------
-
-create table TIME_RANGE (
- INSTANCE_ID integer primary key default nextval('INSTANCE_ID_SEQ'), -- implicit index
- CALENDAR_RESOURCE_ID integer not null references CALENDAR on delete cascade,
- CALENDAR_OBJECT_RESOURCE_ID integer not null references CALENDAR_OBJECT on delete cascade,
- FLOATING boolean not null,
- START_DATE timestamp not null,
- END_DATE timestamp not null,
- FBTYPE integer not null,
- TRANSPARENT boolean not null
-);
-
-create index TIME_RANGE_CALENDAR_RESOURCE_ID on
- TIME_RANGE(CALENDAR_RESOURCE_ID);
-create index TIME_RANGE_CALENDAR_OBJECT_RESOURCE_ID on
- TIME_RANGE(CALENDAR_OBJECT_RESOURCE_ID);
-
-
--- Enumeration of free/busy types
-
-create table FREE_BUSY_TYPE (
- ID integer primary key,
- DESCRIPTION varchar(16) not null unique
-);
-
-insert into FREE_BUSY_TYPE values (0, 'unknown' );
-insert into FREE_BUSY_TYPE values (1, 'free' );
-insert into FREE_BUSY_TYPE values (2, 'busy' );
-insert into FREE_BUSY_TYPE values (3, 'busy-unavailable');
-insert into FREE_BUSY_TYPE values (4, 'busy-tentative' );
-
-
-------------------
--- Transparency --
-------------------
-
-create table TRANSPARENCY (
- TIME_RANGE_INSTANCE_ID integer not null references TIME_RANGE on delete cascade,
- USER_ID varchar(255) not null,
- TRANSPARENT boolean not null
-);
-
-create index TRANSPARENCY_TIME_RANGE_INSTANCE_ID on
- TRANSPARENCY(TIME_RANGE_INSTANCE_ID);
-
-----------------
--- Attachment --
-----------------
-
-create table ATTACHMENT (
- CALENDAR_HOME_RESOURCE_ID integer not null references CALENDAR_HOME,
- DROPBOX_ID varchar(255) not null,
- CONTENT_TYPE varchar(255) not null,
- SIZE integer not null,
- MD5 char(32) not null,
- CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- PATH varchar(1024) not null,
-
- primary key(DROPBOX_ID, PATH) --implicit index
-);
-
-create index ATTACHMENT_CALENDAR_HOME_RESOURCE_ID on
- ATTACHMENT(CALENDAR_HOME_RESOURCE_ID);
-
------------------------
--- Resource Property --
------------------------
-
-create table RESOURCE_PROPERTY (
- RESOURCE_ID integer not null, -- foreign key: *.RESOURCE_ID
- NAME varchar(255) not null,
- VALUE text not null, -- FIXME: xml?
- VIEWER_UID varchar(255),
-
- primary key(RESOURCE_ID, NAME, VIEWER_UID) -- implicit index
-);
-
-
-----------------------
--- AddressBook Home --
-----------------------
-
-create table ADDRESSBOOK_HOME (
- RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
- OWNER_UID varchar(255) not null unique, -- implicit index
- DATAVERSION integer default 0 not null,
- constraint ADDRESSBOOK_HOME_CASE check(OWNER_UID = lower(OWNER_UID))
-);
-
--------------------------------
--- AddressBook Home Metadata --
--------------------------------
-
-create table ADDRESSBOOK_HOME_METADATA (
- RESOURCE_ID integer primary key references ADDRESSBOOK_HOME on delete cascade, -- implicit index
- QUOTA_USED_BYTES integer default 0 not null,
- CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
-);
-
------------------
--- AddressBook --
------------------
-
-create table ADDRESSBOOK (
- RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ') -- implicit index
-);
-
-
---------------------------
--- AddressBook Metadata --
---------------------------
-
-create table ADDRESSBOOK_METADATA (
- RESOURCE_ID integer primary key references ADDRESSBOOK on delete cascade, -- implicit index
- CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
-);
-
-
-----------------------
--- AddressBook Bind --
-----------------------
-
--- Joins ADDRESSBOOK_HOME and ADDRESSBOOK
-
-create table ADDRESSBOOK_BIND (
- ADDRESSBOOK_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME,
- ADDRESSBOOK_RESOURCE_ID integer not null references ADDRESSBOOK on delete cascade,
-
- -- An invitation which hasn't been accepted yet will not yet have a resource
- -- name, so this field may be null.
-
- ADDRESSBOOK_RESOURCE_NAME varchar(255),
- BIND_MODE integer not null, -- enum CALENDAR_BIND_MODE
- BIND_STATUS integer not null, -- enum CALENDAR_BIND_STATUS
- SEEN_BY_OWNER boolean not null,
- SEEN_BY_SHAREE boolean not null,
- MESSAGE text, -- FIXME: xml?
-
- primary key(ADDRESSBOOK_HOME_RESOURCE_ID, ADDRESSBOOK_RESOURCE_ID), -- implicit index
- unique(ADDRESSBOOK_HOME_RESOURCE_ID, ADDRESSBOOK_RESOURCE_NAME) -- implicit index
-);
-
-create index ADDRESSBOOK_BIND_RESOURCE_ID on
- ADDRESSBOOK_BIND(ADDRESSBOOK_RESOURCE_ID);
-
-create table ADDRESSBOOK_OBJECT (
- RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
- ADDRESSBOOK_RESOURCE_ID integer not null references ADDRESSBOOK on delete cascade,
- RESOURCE_NAME varchar(255) not null,
- VCARD_TEXT text not null,
- VCARD_UID varchar(255) not null,
- MD5 char(32) not null,
- CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
-
- unique(ADDRESSBOOK_RESOURCE_ID, RESOURCE_NAME), -- implicit index
- unique(ADDRESSBOOK_RESOURCE_ID, VCARD_UID) -- implicit index
-);
-
----------------
--- Revisions --
----------------
-
-create sequence REVISION_SEQ;
-
-
----------------
--- Revisions --
----------------
-
-create table CALENDAR_OBJECT_REVISIONS (
- CALENDAR_HOME_RESOURCE_ID integer not null references CALENDAR_HOME,
- CALENDAR_RESOURCE_ID integer references CALENDAR,
- CALENDAR_NAME varchar(255) default null,
- RESOURCE_NAME varchar(255),
- REVISION integer default nextval('REVISION_SEQ') not null,
- DELETED boolean not null
-);
-
-create index CALENDAR_OBJECT_REVISIONS_HOME_RESOURCE_ID
- on CALENDAR_OBJECT_REVISIONS(CALENDAR_HOME_RESOURCE_ID);
-
-create index CALENDAR_OBJECT_REVISIONS_RESOURCE_ID_RESOURCE_NAME
- on CALENDAR_OBJECT_REVISIONS(CALENDAR_RESOURCE_ID, RESOURCE_NAME);
-
-create index CALENDAR_OBJECT_REVISIONS_RESOURCE_ID_REVISION
- on CALENDAR_OBJECT_REVISIONS(CALENDAR_RESOURCE_ID, REVISION);
-
--------------------------------
--- AddressBook Object Revisions --
--------------------------------
-
-create table ADDRESSBOOK_OBJECT_REVISIONS (
- ADDRESSBOOK_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME,
- ADDRESSBOOK_RESOURCE_ID integer references ADDRESSBOOK,
- ADDRESSBOOK_NAME varchar(255) default null,
- RESOURCE_NAME varchar(255),
- REVISION integer default nextval('REVISION_SEQ') not null,
- DELETED boolean not null
-);
-
-create index ADDRESSBOOK_OBJECT_REVISIONS_HOME_RESOURCE_ID
- on ADDRESSBOOK_OBJECT_REVISIONS(ADDRESSBOOK_HOME_RESOURCE_ID);
-
-create index ADDRESSBOOK_OBJECT_REVISIONS_RESOURCE_ID_RESOURCE_NAME
- on ADDRESSBOOK_OBJECT_REVISIONS(ADDRESSBOOK_RESOURCE_ID, RESOURCE_NAME);
-
-create index ADDRESSBOOK_OBJECT_REVISIONS_RESOURCE_ID_REVISION
- on ADDRESSBOOK_OBJECT_REVISIONS(ADDRESSBOOK_RESOURCE_ID, REVISION);
-
------------------------------------
--- Notification Object Revisions --
------------------------------------
-
-create table NOTIFICATION_OBJECT_REVISIONS (
- NOTIFICATION_HOME_RESOURCE_ID integer not null references NOTIFICATION_HOME on delete cascade,
- RESOURCE_NAME varchar(255),
- REVISION integer default nextval('REVISION_SEQ') not null,
- DELETED boolean not null,
-
- unique(NOTIFICATION_HOME_RESOURCE_ID, RESOURCE_NAME) -- implicit index
-);
-
-create index NOTIFICATION_OBJECT_REVISIONS_RESOURCE_ID_REVISION
- on NOTIFICATION_OBJECT_REVISIONS(NOTIFICATION_HOME_RESOURCE_ID, REVISION);
-
--------------------------------------------
--- Apple Push Notification Subscriptions --
--------------------------------------------
-
-create table APN_SUBSCRIPTIONS (
- TOKEN varchar(255) not null,
- RESOURCE_KEY varchar(255) not null,
- MODIFIED integer not null,
- SUBSCRIBER_GUID varchar(255) not null,
-
- primary key(TOKEN, RESOURCE_KEY) -- implicit index
-);
-
-create index APN_SUBSCRIPTIONS_RESOURCE_KEY
- on APN_SUBSCRIPTIONS(RESOURCE_KEY);
-
-
---------------------
--- Schema Version --
---------------------
-
-create table CALENDARSERVER (
- NAME varchar(255) primary key, -- implicit index
- VALUE varchar(255)
-);
-
-insert into CALENDARSERVER values ('VERSION', '9');
-insert into CALENDARSERVER values ('CALENDAR-DATAVERSION', '2');
-insert into CALENDARSERVER values ('ADDRESSBOOK-DATAVERSION', '1');
Copied: CalendarServer/trunk/txdav/common/datastore/sql_schema/current.sql (from rev 8824, CalendarServer/branches/users/cdaboo/txn-debugging/txdav/common/datastore/sql_schema/current.sql)
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/current.sql (rev 0)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/current.sql 2012-03-07 22:51:01 UTC (rev 8825)
@@ -0,0 +1,499 @@
+-- -*- test-case-name: txdav.caldav.datastore.test.test_sql,txdav.carddav.datastore.test.test_sql -*-
+
+----
+-- Copyright (c) 2010-2012 Apple Inc. All rights reserved.
+--
+-- Licensed under the Apache License, Version 2.0 (the "License");
+-- you may not use this file except in compliance with the License.
+-- You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+----
+
+-----------------
+-- Resource ID --
+-----------------
+
+create sequence RESOURCE_ID_SEQ;
+
+
+-------------------
+-- Calendar Home --
+-------------------
+
+create table CALENDAR_HOME (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ OWNER_UID varchar(255) not null unique, -- implicit index
+ DATAVERSION integer default 0 not null
+);
+
+----------------------------
+-- Calendar Home Metadata --
+----------------------------
+
+create table CALENDAR_HOME_METADATA (
+ RESOURCE_ID integer primary key references CALENDAR_HOME on delete cascade, -- implicit index
+ QUOTA_USED_BYTES integer default 0 not null,
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+
+--------------
+-- Calendar --
+--------------
+
+create table CALENDAR (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ') -- implicit index
+);
+
+
+-----------------------
+-- Calendar Metadata --
+-----------------------
+
+create table CALENDAR_METADATA (
+ RESOURCE_ID integer primary key references CALENDAR on delete cascade, -- implicit index
+ SUPPORTED_COMPONENTS varchar(255) default null,
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+
+
+------------------------
+-- Sharing Invitation --
+------------------------
+
+create table INVITE (
+ INVITE_UID varchar(255) not null,
+ NAME varchar(255) not null,
+ RECIPIENT_ADDRESS varchar(255) not null,
+ HOME_RESOURCE_ID integer not null,
+ RESOURCE_ID integer not null
+
+ -- Need primary key on (INVITE_UID, NAME, RECIPIENT_ADDRESS)?
+);
+
+create index INVITE_INVITE_UID on INVITE(INVITE_UID);
+create index INVITE_RESOURCE_ID on INVITE(RESOURCE_ID);
+create index INVITE_HOME_RESOURCE_ID on INVITE(HOME_RESOURCE_ID);
+
+---------------------------
+-- Sharing Notifications --
+---------------------------
+
+create table NOTIFICATION_HOME (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ OWNER_UID varchar(255) not null unique -- implicit index
+);
+
+create table NOTIFICATION (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ NOTIFICATION_HOME_RESOURCE_ID integer not null references NOTIFICATION_HOME,
+ NOTIFICATION_UID varchar(255) not null,
+ XML_TYPE varchar(255) not null,
+ XML_DATA text not null,
+ MD5 char(32) not null,
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+
+ unique(NOTIFICATION_UID, NOTIFICATION_HOME_RESOURCE_ID) -- implicit index
+);
+
+create index NOTIFICATION_NOTIFICATION_HOME_RESOURCE_ID on
+ NOTIFICATION(NOTIFICATION_HOME_RESOURCE_ID);
+
+-------------------
+-- Calendar Bind --
+-------------------
+
+-- Joins CALENDAR_HOME and CALENDAR
+
+create table CALENDAR_BIND (
+ CALENDAR_HOME_RESOURCE_ID integer not null references CALENDAR_HOME,
+ CALENDAR_RESOURCE_ID integer not null references CALENDAR on delete cascade,
+
+ -- An invitation which hasn't been accepted yet will not yet have a resource
+ -- name, so this field may be null.
+
+ CALENDAR_RESOURCE_NAME varchar(255),
+ BIND_MODE integer not null, -- enum CALENDAR_BIND_MODE
+ BIND_STATUS integer not null, -- enum CALENDAR_BIND_STATUS
+ SEEN_BY_OWNER boolean not null,
+ SEEN_BY_SHAREE boolean not null,
+ MESSAGE text,
+
+ primary key(CALENDAR_HOME_RESOURCE_ID, CALENDAR_RESOURCE_ID), -- implicit index
+ unique(CALENDAR_HOME_RESOURCE_ID, CALENDAR_RESOURCE_NAME) -- implicit index
+);
+
+create index CALENDAR_BIND_RESOURCE_ID on CALENDAR_BIND(CALENDAR_RESOURCE_ID);
+
+-- Enumeration of calendar bind modes
+
+create table CALENDAR_BIND_MODE (
+ ID integer primary key,
+ DESCRIPTION varchar(16) not null unique
+);
+
+insert into CALENDAR_BIND_MODE values (0, 'own' );
+insert into CALENDAR_BIND_MODE values (1, 'read' );
+insert into CALENDAR_BIND_MODE values (2, 'write');
+insert into CALENDAR_BIND_MODE values (3, 'direct');
+
+-- Enumeration of statuses
+
+create table CALENDAR_BIND_STATUS (
+ ID integer primary key,
+ DESCRIPTION varchar(16) not null unique
+);
+
+insert into CALENDAR_BIND_STATUS values (0, 'invited' );
+insert into CALENDAR_BIND_STATUS values (1, 'accepted');
+insert into CALENDAR_BIND_STATUS values (2, 'declined');
+insert into CALENDAR_BIND_STATUS values (3, 'invalid');
+
+
+---------------------
+-- Calendar Object --
+---------------------
+
+create table CALENDAR_OBJECT (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ CALENDAR_RESOURCE_ID integer not null references CALENDAR on delete cascade,
+ RESOURCE_NAME varchar(255) not null,
+ ICALENDAR_TEXT text not null,
+ ICALENDAR_UID varchar(255) not null,
+ ICALENDAR_TYPE varchar(255) not null,
+ ATTACHMENTS_MODE integer default 0 not null, -- enum CALENDAR_OBJECT_ATTACHMENTS_MODE
+ DROPBOX_ID varchar(255),
+ ORGANIZER varchar(255),
+ ORGANIZER_OBJECT integer references CALENDAR_OBJECT,
+ RECURRANCE_MAX date, -- maximum date that recurrences have been expanded to.
+ ACCESS integer default 0 not null,
+ SCHEDULE_OBJECT boolean default false,
+ SCHEDULE_TAG varchar(36) default null,
+ SCHEDULE_ETAGS text default null,
+ PRIVATE_COMMENTS boolean default false not null,
+ MD5 char(32) not null,
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+
+ unique(CALENDAR_RESOURCE_ID, RESOURCE_NAME) -- implicit index
+
+ -- since the 'inbox' is a 'calendar resource' for the purpose of storing
+ -- calendar objects, this constraint has to be selectively enforced by the
+ -- application layer.
+
+ -- unique(CALENDAR_RESOURCE_ID, ICALENDAR_UID)
+);
+
+create index CALENDAR_OBJECT_CALENDAR_RESOURCE_ID_AND_ICALENDAR_UID on
+ CALENDAR_OBJECT(CALENDAR_RESOURCE_ID, ICALENDAR_UID);
+
+create index CALENDAR_OBJECT_CALENDAR_RESOURCE_ID_RECURRANCE_MAX on
+ CALENDAR_OBJECT(CALENDAR_RESOURCE_ID, RECURRANCE_MAX);
+
+create index CALENDAR_OBJECT_ORGANIZER_OBJECT on
+ CALENDAR_OBJECT(ORGANIZER_OBJECT);
+
+create index CALENDAR_OBJECT_DROPBOX_ID on
+ CALENDAR_OBJECT(DROPBOX_ID);
+
+-- Enumeration of attachment modes
+
+create table CALENDAR_OBJECT_ATTACHMENTS_MODE (
+ ID integer primary key,
+ DESCRIPTION varchar(16) not null unique
+);
+
+insert into CALENDAR_OBJECT_ATTACHMENTS_MODE values (0, 'none' );
+insert into CALENDAR_OBJECT_ATTACHMENTS_MODE values (1, 'read' );
+insert into CALENDAR_OBJECT_ATTACHMENTS_MODE values (2, 'write');
+
+
+-- Enumeration of calendar access types
+
+create table CALENDAR_ACCESS_TYPE (
+ ID integer primary key,
+ DESCRIPTION varchar(32) not null unique
+);
+
+insert into CALENDAR_ACCESS_TYPE values (0, '' );
+insert into CALENDAR_ACCESS_TYPE values (1, 'public' );
+insert into CALENDAR_ACCESS_TYPE values (2, 'private' );
+insert into CALENDAR_ACCESS_TYPE values (3, 'confidential' );
+insert into CALENDAR_ACCESS_TYPE values (4, 'restricted' );
+
+-----------------
+-- Instance ID --
+-----------------
+
+create sequence INSTANCE_ID_SEQ;
+
+
+----------------
+-- Time Range --
+----------------
+
+create table TIME_RANGE (
+ INSTANCE_ID integer primary key default nextval('INSTANCE_ID_SEQ'), -- implicit index
+ CALENDAR_RESOURCE_ID integer not null references CALENDAR on delete cascade,
+ CALENDAR_OBJECT_RESOURCE_ID integer not null references CALENDAR_OBJECT on delete cascade,
+ FLOATING boolean not null,
+ START_DATE timestamp not null,
+ END_DATE timestamp not null,
+ FBTYPE integer not null,
+ TRANSPARENT boolean not null
+);
+
+create index TIME_RANGE_CALENDAR_RESOURCE_ID on
+ TIME_RANGE(CALENDAR_RESOURCE_ID);
+create index TIME_RANGE_CALENDAR_OBJECT_RESOURCE_ID on
+ TIME_RANGE(CALENDAR_OBJECT_RESOURCE_ID);
+
+
+-- Enumeration of free/busy types
+
+create table FREE_BUSY_TYPE (
+ ID integer primary key,
+ DESCRIPTION varchar(16) not null unique
+);
+
+insert into FREE_BUSY_TYPE values (0, 'unknown' );
+insert into FREE_BUSY_TYPE values (1, 'free' );
+insert into FREE_BUSY_TYPE values (2, 'busy' );
+insert into FREE_BUSY_TYPE values (3, 'busy-unavailable');
+insert into FREE_BUSY_TYPE values (4, 'busy-tentative' );
+
+
+------------------
+-- Transparency --
+------------------
+
+create table TRANSPARENCY (
+ TIME_RANGE_INSTANCE_ID integer not null references TIME_RANGE on delete cascade,
+ USER_ID varchar(255) not null,
+ TRANSPARENT boolean not null
+);
+
+create index TRANSPARENCY_TIME_RANGE_INSTANCE_ID on
+ TRANSPARENCY(TIME_RANGE_INSTANCE_ID);
+
+----------------
+-- Attachment --
+----------------
+
+create table ATTACHMENT (
+ CALENDAR_HOME_RESOURCE_ID integer not null references CALENDAR_HOME,
+ DROPBOX_ID varchar(255) not null,
+ CONTENT_TYPE varchar(255) not null,
+ SIZE integer not null,
+ MD5 char(32) not null,
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ PATH varchar(1024) not null,
+
+ primary key(DROPBOX_ID, PATH) --implicit index
+);
+
+create index ATTACHMENT_CALENDAR_HOME_RESOURCE_ID on
+ ATTACHMENT(CALENDAR_HOME_RESOURCE_ID);
+
+-----------------------
+-- Resource Property --
+-----------------------
+
+create table RESOURCE_PROPERTY (
+ RESOURCE_ID integer not null, -- foreign key: *.RESOURCE_ID
+ NAME varchar(255) not null,
+ VALUE text not null, -- FIXME: xml?
+ VIEWER_UID varchar(255),
+
+ primary key(RESOURCE_ID, NAME, VIEWER_UID) -- implicit index
+);
+
+
+----------------------
+-- AddressBook Home --
+----------------------
+
+create table ADDRESSBOOK_HOME (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ OWNER_UID varchar(255) not null unique, -- implicit index
+ DATAVERSION integer default 0 not null
+);
+
+-------------------------------
+-- AddressBook Home Metadata --
+-------------------------------
+
+create table ADDRESSBOOK_HOME_METADATA (
+ RESOURCE_ID integer primary key references ADDRESSBOOK_HOME on delete cascade, -- implicit index
+ QUOTA_USED_BYTES integer default 0 not null,
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+
+-----------------
+-- AddressBook --
+-----------------
+
+create table ADDRESSBOOK (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ') -- implicit index
+);
+
+
+--------------------------
+-- AddressBook Metadata --
+--------------------------
+
+create table ADDRESSBOOK_METADATA (
+ RESOURCE_ID integer primary key references ADDRESSBOOK on delete cascade, -- implicit index
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+
+
+----------------------
+-- AddressBook Bind --
+----------------------
+
+-- Joins ADDRESSBOOK_HOME and ADDRESSBOOK
+
+create table ADDRESSBOOK_BIND (
+ ADDRESSBOOK_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME,
+ ADDRESSBOOK_RESOURCE_ID integer not null references ADDRESSBOOK on delete cascade,
+
+ -- An invitation which hasn't been accepted yet will not yet have a resource
+ -- name, so this field may be null.
+
+ ADDRESSBOOK_RESOURCE_NAME varchar(255),
+ BIND_MODE integer not null, -- enum CALENDAR_BIND_MODE
+ BIND_STATUS integer not null, -- enum CALENDAR_BIND_STATUS
+ SEEN_BY_OWNER boolean not null,
+ SEEN_BY_SHAREE boolean not null,
+ MESSAGE text, -- FIXME: xml?
+
+ primary key(ADDRESSBOOK_HOME_RESOURCE_ID, ADDRESSBOOK_RESOURCE_ID), -- implicit index
+ unique(ADDRESSBOOK_HOME_RESOURCE_ID, ADDRESSBOOK_RESOURCE_NAME) -- implicit index
+);
+
+create index ADDRESSBOOK_BIND_RESOURCE_ID on
+ ADDRESSBOOK_BIND(ADDRESSBOOK_RESOURCE_ID);
+
+create table ADDRESSBOOK_OBJECT (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ ADDRESSBOOK_RESOURCE_ID integer not null references ADDRESSBOOK on delete cascade,
+ RESOURCE_NAME varchar(255) not null,
+ VCARD_TEXT text not null,
+ VCARD_UID varchar(255) not null,
+ MD5 char(32) not null,
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+
+ unique(ADDRESSBOOK_RESOURCE_ID, RESOURCE_NAME), -- implicit index
+ unique(ADDRESSBOOK_RESOURCE_ID, VCARD_UID) -- implicit index
+);
+
+---------------
+-- Revisions --
+---------------
+
+create sequence REVISION_SEQ;
+
+
+---------------
+-- Revisions --
+---------------
+
+create table CALENDAR_OBJECT_REVISIONS (
+ CALENDAR_HOME_RESOURCE_ID integer not null references CALENDAR_HOME,
+ CALENDAR_RESOURCE_ID integer references CALENDAR,
+ CALENDAR_NAME varchar(255) default null,
+ RESOURCE_NAME varchar(255),
+ REVISION integer default nextval('REVISION_SEQ') not null,
+ DELETED boolean not null
+);
+
+create index CALENDAR_OBJECT_REVISIONS_HOME_RESOURCE_ID
+ on CALENDAR_OBJECT_REVISIONS(CALENDAR_HOME_RESOURCE_ID);
+
+create index CALENDAR_OBJECT_REVISIONS_RESOURCE_ID_RESOURCE_NAME
+ on CALENDAR_OBJECT_REVISIONS(CALENDAR_RESOURCE_ID, RESOURCE_NAME);
+
+create index CALENDAR_OBJECT_REVISIONS_RESOURCE_ID_REVISION
+ on CALENDAR_OBJECT_REVISIONS(CALENDAR_RESOURCE_ID, REVISION);
+
+-------------------------------
+-- AddressBook Object Revisions --
+-------------------------------
+
+create table ADDRESSBOOK_OBJECT_REVISIONS (
+ ADDRESSBOOK_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME,
+ ADDRESSBOOK_RESOURCE_ID integer references ADDRESSBOOK,
+ ADDRESSBOOK_NAME varchar(255) default null,
+ RESOURCE_NAME varchar(255),
+ REVISION integer default nextval('REVISION_SEQ') not null,
+ DELETED boolean not null
+);
+
+create index ADDRESSBOOK_OBJECT_REVISIONS_HOME_RESOURCE_ID
+ on ADDRESSBOOK_OBJECT_REVISIONS(ADDRESSBOOK_HOME_RESOURCE_ID);
+
+create index ADDRESSBOOK_OBJECT_REVISIONS_RESOURCE_ID_RESOURCE_NAME
+ on ADDRESSBOOK_OBJECT_REVISIONS(ADDRESSBOOK_RESOURCE_ID, RESOURCE_NAME);
+
+create index ADDRESSBOOK_OBJECT_REVISIONS_RESOURCE_ID_REVISION
+ on ADDRESSBOOK_OBJECT_REVISIONS(ADDRESSBOOK_RESOURCE_ID, REVISION);
+
+-----------------------------------
+-- Notification Object Revisions --
+-----------------------------------
+
+create table NOTIFICATION_OBJECT_REVISIONS (
+ NOTIFICATION_HOME_RESOURCE_ID integer not null references NOTIFICATION_HOME on delete cascade,
+ RESOURCE_NAME varchar(255),
+ REVISION integer default nextval('REVISION_SEQ') not null,
+ DELETED boolean not null,
+
+ unique(NOTIFICATION_HOME_RESOURCE_ID, RESOURCE_NAME) -- implicit index
+);
+
+create index NOTIFICATION_OBJECT_REVISIONS_RESOURCE_ID_REVISION
+ on NOTIFICATION_OBJECT_REVISIONS(NOTIFICATION_HOME_RESOURCE_ID, REVISION);
+
+-------------------------------------------
+-- Apple Push Notification Subscriptions --
+-------------------------------------------
+
+create table APN_SUBSCRIPTIONS (
+ TOKEN varchar(255) not null,
+ RESOURCE_KEY varchar(255) not null,
+ MODIFIED integer not null,
+ SUBSCRIBER_GUID varchar(255) not null,
+
+ primary key(TOKEN, RESOURCE_KEY) -- implicit index
+);
+
+create index APN_SUBSCRIPTIONS_RESOURCE_KEY
+ on APN_SUBSCRIPTIONS(RESOURCE_KEY);
+
+
+--------------------
+-- Schema Version --
+--------------------
+
+create table CALENDARSERVER (
+ NAME varchar(255) primary key, -- implicit index
+ VALUE varchar(255)
+);
+
+insert into CALENDARSERVER values ('VERSION', '8');
+insert into CALENDARSERVER values ('CALENDAR-DATAVERSION', '2');
+insert into CALENDARSERVER values ('ADDRESSBOOK-DATAVERSION', '1');
Deleted: CalendarServer/trunk/txdav/common/datastore/sql_schema/old/v8.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/old/v8.sql 2012-03-06 23:53:13 UTC (rev 8824)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/old/v8.sql 2012-03-07 22:51:01 UTC (rev 8825)
@@ -1,499 +0,0 @@
--- -*- test-case-name: txdav.caldav.datastore.test.test_sql,txdav.carddav.datastore.test.test_sql -*-
-
-----
--- Copyright (c) 2010-2012 Apple Inc. All rights reserved.
---
--- Licensed under the Apache License, Version 2.0 (the "License");
--- you may not use this file except in compliance with the License.
--- You may obtain a copy of the License at
---
--- http://www.apache.org/licenses/LICENSE-2.0
---
--- Unless required by applicable law or agreed to in writing, software
--- distributed under the License is distributed on an "AS IS" BASIS,
--- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--- See the License for the specific language governing permissions and
--- limitations under the License.
-----
-
------------------
--- Resource ID --
------------------
-
-create sequence RESOURCE_ID_SEQ;
-
-
--------------------
--- Calendar Home --
--------------------
-
-create table CALENDAR_HOME (
- RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
- OWNER_UID varchar(255) not null unique, -- implicit index
- DATAVERSION integer default 0 not null
-);
-
-----------------------------
--- Calendar Home Metadata --
-----------------------------
-
-create table CALENDAR_HOME_METADATA (
- RESOURCE_ID integer primary key references CALENDAR_HOME on delete cascade, -- implicit index
- QUOTA_USED_BYTES integer default 0 not null,
- CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
-);
-
---------------
--- Calendar --
---------------
-
-create table CALENDAR (
- RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ') -- implicit index
-);
-
-
------------------------
--- Calendar Metadata --
------------------------
-
-create table CALENDAR_METADATA (
- RESOURCE_ID integer primary key references CALENDAR on delete cascade, -- implicit index
- SUPPORTED_COMPONENTS varchar(255) default null,
- CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
-);
-
-
-------------------------
--- Sharing Invitation --
-------------------------
-
-create table INVITE (
- INVITE_UID varchar(255) not null,
- NAME varchar(255) not null,
- RECIPIENT_ADDRESS varchar(255) not null,
- HOME_RESOURCE_ID integer not null,
- RESOURCE_ID integer not null
-
- -- Need primary key on (INVITE_UID, NAME, RECIPIENT_ADDRESS)?
-);
-
-create index INVITE_INVITE_UID on INVITE(INVITE_UID);
-create index INVITE_RESOURCE_ID on INVITE(RESOURCE_ID);
-create index INVITE_HOME_RESOURCE_ID on INVITE(HOME_RESOURCE_ID);
-
----------------------------
--- Sharing Notifications --
----------------------------
-
-create table NOTIFICATION_HOME (
- RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
- OWNER_UID varchar(255) not null unique -- implicit index
-);
-
-create table NOTIFICATION (
- RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
- NOTIFICATION_HOME_RESOURCE_ID integer not null references NOTIFICATION_HOME,
- NOTIFICATION_UID varchar(255) not null,
- XML_TYPE varchar(255) not null,
- XML_DATA text not null,
- MD5 char(32) not null,
- CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
-
- unique(NOTIFICATION_UID, NOTIFICATION_HOME_RESOURCE_ID) -- implicit index
-);
-
-create index NOTIFICATION_NOTIFICATION_HOME_RESOURCE_ID on
- NOTIFICATION(NOTIFICATION_HOME_RESOURCE_ID);
-
--------------------
--- Calendar Bind --
--------------------
-
--- Joins CALENDAR_HOME and CALENDAR
-
-create table CALENDAR_BIND (
- CALENDAR_HOME_RESOURCE_ID integer not null references CALENDAR_HOME,
- CALENDAR_RESOURCE_ID integer not null references CALENDAR on delete cascade,
-
- -- An invitation which hasn't been accepted yet will not yet have a resource
- -- name, so this field may be null.
-
- CALENDAR_RESOURCE_NAME varchar(255),
- BIND_MODE integer not null, -- enum CALENDAR_BIND_MODE
- BIND_STATUS integer not null, -- enum CALENDAR_BIND_STATUS
- SEEN_BY_OWNER boolean not null,
- SEEN_BY_SHAREE boolean not null,
- MESSAGE text,
-
- primary key(CALENDAR_HOME_RESOURCE_ID, CALENDAR_RESOURCE_ID), -- implicit index
- unique(CALENDAR_HOME_RESOURCE_ID, CALENDAR_RESOURCE_NAME) -- implicit index
-);
-
-create index CALENDAR_BIND_RESOURCE_ID on CALENDAR_BIND(CALENDAR_RESOURCE_ID);
-
--- Enumeration of calendar bind modes
-
-create table CALENDAR_BIND_MODE (
- ID integer primary key,
- DESCRIPTION varchar(16) not null unique
-);
-
-insert into CALENDAR_BIND_MODE values (0, 'own' );
-insert into CALENDAR_BIND_MODE values (1, 'read' );
-insert into CALENDAR_BIND_MODE values (2, 'write');
-insert into CALENDAR_BIND_MODE values (3, 'direct');
-
--- Enumeration of statuses
-
-create table CALENDAR_BIND_STATUS (
- ID integer primary key,
- DESCRIPTION varchar(16) not null unique
-);
-
-insert into CALENDAR_BIND_STATUS values (0, 'invited' );
-insert into CALENDAR_BIND_STATUS values (1, 'accepted');
-insert into CALENDAR_BIND_STATUS values (2, 'declined');
-insert into CALENDAR_BIND_STATUS values (3, 'invalid');
-
-
----------------------
--- Calendar Object --
----------------------
-
-create table CALENDAR_OBJECT (
- RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
- CALENDAR_RESOURCE_ID integer not null references CALENDAR on delete cascade,
- RESOURCE_NAME varchar(255) not null,
- ICALENDAR_TEXT text not null,
- ICALENDAR_UID varchar(255) not null,
- ICALENDAR_TYPE varchar(255) not null,
- ATTACHMENTS_MODE integer default 0 not null, -- enum CALENDAR_OBJECT_ATTACHMENTS_MODE
- DROPBOX_ID varchar(255),
- ORGANIZER varchar(255),
- ORGANIZER_OBJECT integer references CALENDAR_OBJECT,
- RECURRANCE_MAX date, -- maximum date that recurrences have been expanded to.
- ACCESS integer default 0 not null,
- SCHEDULE_OBJECT boolean default false,
- SCHEDULE_TAG varchar(36) default null,
- SCHEDULE_ETAGS text default null,
- PRIVATE_COMMENTS boolean default false not null,
- MD5 char(32) not null,
- CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
-
- unique(CALENDAR_RESOURCE_ID, RESOURCE_NAME) -- implicit index
-
- -- since the 'inbox' is a 'calendar resource' for the purpose of storing
- -- calendar objects, this constraint has to be selectively enforced by the
- -- application layer.
-
- -- unique(CALENDAR_RESOURCE_ID, ICALENDAR_UID)
-);
-
-create index CALENDAR_OBJECT_CALENDAR_RESOURCE_ID_AND_ICALENDAR_UID on
- CALENDAR_OBJECT(CALENDAR_RESOURCE_ID, ICALENDAR_UID);
-
-create index CALENDAR_OBJECT_CALENDAR_RESOURCE_ID_RECURRANCE_MAX on
- CALENDAR_OBJECT(CALENDAR_RESOURCE_ID, RECURRANCE_MAX);
-
-create index CALENDAR_OBJECT_ORGANIZER_OBJECT on
- CALENDAR_OBJECT(ORGANIZER_OBJECT);
-
-create index CALENDAR_OBJECT_DROPBOX_ID on
- CALENDAR_OBJECT(DROPBOX_ID);
-
--- Enumeration of attachment modes
-
-create table CALENDAR_OBJECT_ATTACHMENTS_MODE (
- ID integer primary key,
- DESCRIPTION varchar(16) not null unique
-);
-
-insert into CALENDAR_OBJECT_ATTACHMENTS_MODE values (0, 'none' );
-insert into CALENDAR_OBJECT_ATTACHMENTS_MODE values (1, 'read' );
-insert into CALENDAR_OBJECT_ATTACHMENTS_MODE values (2, 'write');
-
-
--- Enumeration of calendar access types
-
-create table CALENDAR_ACCESS_TYPE (
- ID integer primary key,
- DESCRIPTION varchar(32) not null unique
-);
-
-insert into CALENDAR_ACCESS_TYPE values (0, '' );
-insert into CALENDAR_ACCESS_TYPE values (1, 'public' );
-insert into CALENDAR_ACCESS_TYPE values (2, 'private' );
-insert into CALENDAR_ACCESS_TYPE values (3, 'confidential' );
-insert into CALENDAR_ACCESS_TYPE values (4, 'restricted' );
-
------------------
--- Instance ID --
------------------
-
-create sequence INSTANCE_ID_SEQ;
-
-
-----------------
--- Time Range --
-----------------
-
-create table TIME_RANGE (
- INSTANCE_ID integer primary key default nextval('INSTANCE_ID_SEQ'), -- implicit index
- CALENDAR_RESOURCE_ID integer not null references CALENDAR on delete cascade,
- CALENDAR_OBJECT_RESOURCE_ID integer not null references CALENDAR_OBJECT on delete cascade,
- FLOATING boolean not null,
- START_DATE timestamp not null,
- END_DATE timestamp not null,
- FBTYPE integer not null,
- TRANSPARENT boolean not null
-);
-
-create index TIME_RANGE_CALENDAR_RESOURCE_ID on
- TIME_RANGE(CALENDAR_RESOURCE_ID);
-create index TIME_RANGE_CALENDAR_OBJECT_RESOURCE_ID on
- TIME_RANGE(CALENDAR_OBJECT_RESOURCE_ID);
-
-
--- Enumeration of free/busy types
-
-create table FREE_BUSY_TYPE (
- ID integer primary key,
- DESCRIPTION varchar(16) not null unique
-);
-
-insert into FREE_BUSY_TYPE values (0, 'unknown' );
-insert into FREE_BUSY_TYPE values (1, 'free' );
-insert into FREE_BUSY_TYPE values (2, 'busy' );
-insert into FREE_BUSY_TYPE values (3, 'busy-unavailable');
-insert into FREE_BUSY_TYPE values (4, 'busy-tentative' );
-
-
-------------------
--- Transparency --
-------------------
-
-create table TRANSPARENCY (
- TIME_RANGE_INSTANCE_ID integer not null references TIME_RANGE on delete cascade,
- USER_ID varchar(255) not null,
- TRANSPARENT boolean not null
-);
-
-create index TRANSPARENCY_TIME_RANGE_INSTANCE_ID on
- TRANSPARENCY(TIME_RANGE_INSTANCE_ID);
-
-----------------
--- Attachment --
-----------------
-
-create table ATTACHMENT (
- CALENDAR_HOME_RESOURCE_ID integer not null references CALENDAR_HOME,
- DROPBOX_ID varchar(255) not null,
- CONTENT_TYPE varchar(255) not null,
- SIZE integer not null,
- MD5 char(32) not null,
- CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- PATH varchar(1024) not null,
-
- primary key(DROPBOX_ID, PATH) --implicit index
-);
-
-create index ATTACHMENT_CALENDAR_HOME_RESOURCE_ID on
- ATTACHMENT(CALENDAR_HOME_RESOURCE_ID);
-
------------------------
--- Resource Property --
------------------------
-
-create table RESOURCE_PROPERTY (
- RESOURCE_ID integer not null, -- foreign key: *.RESOURCE_ID
- NAME varchar(255) not null,
- VALUE text not null, -- FIXME: xml?
- VIEWER_UID varchar(255),
-
- primary key(RESOURCE_ID, NAME, VIEWER_UID) -- implicit index
-);
-
-
-----------------------
--- AddressBook Home --
-----------------------
-
-create table ADDRESSBOOK_HOME (
- RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
- OWNER_UID varchar(255) not null unique, -- implicit index
- DATAVERSION integer default 0 not null
-);
-
--------------------------------
--- AddressBook Home Metadata --
--------------------------------
-
-create table ADDRESSBOOK_HOME_METADATA (
- RESOURCE_ID integer primary key references ADDRESSBOOK_HOME on delete cascade, -- implicit index
- QUOTA_USED_BYTES integer default 0 not null,
- CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
-);
-
------------------
--- AddressBook --
------------------
-
-create table ADDRESSBOOK (
- RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ') -- implicit index
-);
-
-
---------------------------
--- AddressBook Metadata --
---------------------------
-
-create table ADDRESSBOOK_METADATA (
- RESOURCE_ID integer primary key references ADDRESSBOOK on delete cascade, -- implicit index
- CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
-);
-
-
-----------------------
--- AddressBook Bind --
-----------------------
-
--- Joins ADDRESSBOOK_HOME and ADDRESSBOOK
-
-create table ADDRESSBOOK_BIND (
- ADDRESSBOOK_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME,
- ADDRESSBOOK_RESOURCE_ID integer not null references ADDRESSBOOK on delete cascade,
-
- -- An invitation which hasn't been accepted yet will not yet have a resource
- -- name, so this field may be null.
-
- ADDRESSBOOK_RESOURCE_NAME varchar(255),
- BIND_MODE integer not null, -- enum CALENDAR_BIND_MODE
- BIND_STATUS integer not null, -- enum CALENDAR_BIND_STATUS
- SEEN_BY_OWNER boolean not null,
- SEEN_BY_SHAREE boolean not null,
- MESSAGE text, -- FIXME: xml?
-
- primary key(ADDRESSBOOK_HOME_RESOURCE_ID, ADDRESSBOOK_RESOURCE_ID), -- implicit index
- unique(ADDRESSBOOK_HOME_RESOURCE_ID, ADDRESSBOOK_RESOURCE_NAME) -- implicit index
-);
-
-create index ADDRESSBOOK_BIND_RESOURCE_ID on
- ADDRESSBOOK_BIND(ADDRESSBOOK_RESOURCE_ID);
-
-create table ADDRESSBOOK_OBJECT (
- RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
- ADDRESSBOOK_RESOURCE_ID integer not null references ADDRESSBOOK on delete cascade,
- RESOURCE_NAME varchar(255) not null,
- VCARD_TEXT text not null,
- VCARD_UID varchar(255) not null,
- MD5 char(32) not null,
- CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
-
- unique(ADDRESSBOOK_RESOURCE_ID, RESOURCE_NAME), -- implicit index
- unique(ADDRESSBOOK_RESOURCE_ID, VCARD_UID) -- implicit index
-);
-
----------------
--- Revisions --
----------------
-
-create sequence REVISION_SEQ;
-
-
----------------
--- Revisions --
----------------
-
-create table CALENDAR_OBJECT_REVISIONS (
- CALENDAR_HOME_RESOURCE_ID integer not null references CALENDAR_HOME,
- CALENDAR_RESOURCE_ID integer references CALENDAR,
- CALENDAR_NAME varchar(255) default null,
- RESOURCE_NAME varchar(255),
- REVISION integer default nextval('REVISION_SEQ') not null,
- DELETED boolean not null
-);
-
-create index CALENDAR_OBJECT_REVISIONS_HOME_RESOURCE_ID
- on CALENDAR_OBJECT_REVISIONS(CALENDAR_HOME_RESOURCE_ID);
-
-create index CALENDAR_OBJECT_REVISIONS_RESOURCE_ID_RESOURCE_NAME
- on CALENDAR_OBJECT_REVISIONS(CALENDAR_RESOURCE_ID, RESOURCE_NAME);
-
-create index CALENDAR_OBJECT_REVISIONS_RESOURCE_ID_REVISION
- on CALENDAR_OBJECT_REVISIONS(CALENDAR_RESOURCE_ID, REVISION);
-
--------------------------------
--- AddressBook Object Revisions --
--------------------------------
-
-create table ADDRESSBOOK_OBJECT_REVISIONS (
- ADDRESSBOOK_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME,
- ADDRESSBOOK_RESOURCE_ID integer references ADDRESSBOOK,
- ADDRESSBOOK_NAME varchar(255) default null,
- RESOURCE_NAME varchar(255),
- REVISION integer default nextval('REVISION_SEQ') not null,
- DELETED boolean not null
-);
-
-create index ADDRESSBOOK_OBJECT_REVISIONS_HOME_RESOURCE_ID
- on ADDRESSBOOK_OBJECT_REVISIONS(ADDRESSBOOK_HOME_RESOURCE_ID);
-
-create index ADDRESSBOOK_OBJECT_REVISIONS_RESOURCE_ID_RESOURCE_NAME
- on ADDRESSBOOK_OBJECT_REVISIONS(ADDRESSBOOK_RESOURCE_ID, RESOURCE_NAME);
-
-create index ADDRESSBOOK_OBJECT_REVISIONS_RESOURCE_ID_REVISION
- on ADDRESSBOOK_OBJECT_REVISIONS(ADDRESSBOOK_RESOURCE_ID, REVISION);
-
------------------------------------
--- Notification Object Revisions --
------------------------------------
-
-create table NOTIFICATION_OBJECT_REVISIONS (
- NOTIFICATION_HOME_RESOURCE_ID integer not null references NOTIFICATION_HOME on delete cascade,
- RESOURCE_NAME varchar(255),
- REVISION integer default nextval('REVISION_SEQ') not null,
- DELETED boolean not null,
-
- unique(NOTIFICATION_HOME_RESOURCE_ID, RESOURCE_NAME) -- implicit index
-);
-
-create index NOTIFICATION_OBJECT_REVISIONS_RESOURCE_ID_REVISION
- on NOTIFICATION_OBJECT_REVISIONS(NOTIFICATION_HOME_RESOURCE_ID, REVISION);
-
--------------------------------------------
--- Apple Push Notification Subscriptions --
--------------------------------------------
-
-create table APN_SUBSCRIPTIONS (
- TOKEN varchar(255) not null,
- RESOURCE_KEY varchar(255) not null,
- MODIFIED integer not null,
- SUBSCRIBER_GUID varchar(255) not null,
-
- primary key(TOKEN, RESOURCE_KEY) -- implicit index
-);
-
-create index APN_SUBSCRIPTIONS_RESOURCE_KEY
- on APN_SUBSCRIPTIONS(RESOURCE_KEY);
-
-
---------------------
--- Schema Version --
---------------------
-
-create table CALENDARSERVER (
- NAME varchar(255) primary key, -- implicit index
- VALUE varchar(255)
-);
-
-insert into CALENDARSERVER values ('VERSION', '8');
-insert into CALENDARSERVER values ('CALENDAR-DATAVERSION', '2');
-insert into CALENDARSERVER values ('ADDRESSBOOK-DATAVERSION', '1');
Deleted: CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_8_to_9.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_8_to_9.sql 2012-03-06 23:53:13 UTC (rev 8824)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_8_to_9.sql 2012-03-07 22:51:01 UTC (rev 8825)
@@ -1,36 +0,0 @@
-----
--- Copyright (c) 2012 Apple Inc. All rights reserved.
---
--- Licensed under the Apache License, Version 2.0 (the "License");
--- you may not use this file except in compliance with the License.
--- You may obtain a copy of the License at
---
--- http://www.apache.org/licenses/LICENSE-2.0
---
--- Unless required by applicable law or agreed to in writing, software
--- distributed under the License is distributed on an "AS IS" BASIS,
--- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--- See the License for the specific language governing permissions and
--- limitations under the License.
-----
-
--------------------------------------------------
--- Upgrade database schema from VERSION 8 to 9 --
--------------------------------------------------
-
-update CALENDAR_HOME set OWNER_UID = lower(OWNER_UID);
-
-alter table CALENDAR_HOME
-add constraint CALENDAR_HOME_CASE check(OWNER_UID = lower(OWNER_UID));
-
-update ADDRESSBOOK_HOME set OWNER_UID = lower(OWNER_UID);
-
-alter table ADDRESSBOOK_HOME
-add constraint ADDRESSBOOK_HOME_CASE check(OWNER_UID = lower(OWNER_UID));
-
-update NOTIFICATION_HOME set OWNER_UID = lower(OWNER_UID);
-
-alter table NOTIFICATION_HOME
-add constraint NOTIFICATION_HOME_CASE check(OWNER_UID = lower(OWNER_UID));
-
-update CALENDARSERVER set VALUE = '9' where NAME = 'VERSION';
Deleted: CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_8_to_9.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_8_to_9.sql 2012-03-06 23:53:13 UTC (rev 8824)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_8_to_9.sql 2012-03-07 22:51:01 UTC (rev 8825)
@@ -1,36 +0,0 @@
-----
--- Copyright (c) 2012 Apple Inc. All rights reserved.
---
--- Licensed under the Apache License, Version 2.0 (the "License");
--- you may not use this file except in compliance with the License.
--- You may obtain a copy of the License at
---
--- http://www.apache.org/licenses/LICENSE-2.0
---
--- Unless required by applicable law or agreed to in writing, software
--- distributed under the License is distributed on an "AS IS" BASIS,
--- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--- See the License for the specific language governing permissions and
--- limitations under the License.
-----
-
--------------------------------------------------
--- Upgrade database schema from VERSION 8 to 9 --
--------------------------------------------------
-
-update CALENDAR_HOME set OWNER_UID = lower(OWNER_UID);
-
-alter table CALENDAR_HOME
-add constraint CALENDAR_HOME_CASE check(OWNER_UID = lower(OWNER_UID));
-
-update ADDRESSBOOK_HOME set OWNER_UID = lower(OWNER_UID);
-
-alter table ADDRESSBOOK_HOME
-add constraint ADDRESSBOOK_HOME_CASE check(OWNER_UID = lower(OWNER_UID));
-
-update NOTIFICATION_HOME set OWNER_UID = lower(OWNER_UID);
-
-alter table NOTIFICATION_HOME
-add constraint NOTIFICATION_HOME_CASE check(OWNER_UID = lower(OWNER_UID));
-
-update CALENDARSERVER set VALUE = '9' where NAME = 'VERSION';
Modified: CalendarServer/trunk/txdav/common/datastore/upgrade/sql/upgrade.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/upgrade/sql/upgrade.py 2012-03-06 23:53:13 UTC (rev 8824)
+++ CalendarServer/trunk/txdav/common/datastore/upgrade/sql/upgrade.py 2012-03-07 22:51:01 UTC (rev 8825)
@@ -29,11 +29,6 @@
from twisted.internet.defer import inlineCallbacks, returnValue
from twisted.python.modules import getModule
from twisted.python.reflect import namedObject
-from txdav.common.datastore.sql_tables import schema
-from twext.enterprise.dal.syntax import Select
-from twext.enterprise.dal.syntax import CaseFold
-from twext.enterprise.dal.syntax import Update
-from twext.enterprise.dal.syntax import Max
class UpgradeDatabaseCoreService(Service, LoggingMixIn, object):
"""
@@ -191,44 +186,30 @@
self.log_warn("%s upgraded from version %d to %d." % (self.versionDescriptor.capitalize(), fromVersion, toVersion,))
-
def getPathToUpgrades(self, dialect):
"""
Return the path where appropriate upgrade files can be found.
"""
raise NotImplementedError
-
- def versionsFromFilename(self, filename):
- """
- Extract 'from' and 'to' versions from the given basename, if there are
- any.
- """
- regex = re.compile("upgrade_from_(\d)+_to_(\d)+%s" %
- (self.upgradeFileSuffix,))
- fromV = None
- toV = None
- matched = regex.match(filename)
- if matched is not None:
- fromV = int(matched.group(1))
- toV = int(matched.group(2))
- return fromV, toV
-
-
def scanForUpgradeFiles(self, dialect):
"""
Scan for upgrade files with the require name.
"""
+
fp = self.getPathToUpgrades(dialect)
upgrades = []
+ regex = re.compile("upgrade_from_(\d)+_to_(\d)+%s" % (self.upgradeFileSuffix,))
for child in fp.globChildren("upgrade_*%s" % (self.upgradeFileSuffix,)):
- fromV, toV = self.versionsFromFilename(child.basename())
- if fromV is not None:
+ matched = regex.match(child.basename())
+ if matched is not None:
+ fromV = int(matched.group(1))
+ toV = int(matched.group(2))
upgrades.append((fromV, toV, child))
+
upgrades.sort(key=lambda x:(x[0], x[1]))
return upgrades
-
def determineUpgradeSequence(self, fromVersion, toVersion, files, dialect):
"""
Determine the upgrade_from_X_to_Y(.sql|.py) files that cover the full range of upgrades.
@@ -262,11 +243,7 @@
Apply the supplied upgrade to the database. Always return an L{Deferred"
"""
raise NotImplementedError
-
-
-
-_CASE_DUPLICATE_PREFIX = "case-duplicate-old:"
-
+
class UpgradeDatabaseSchemaService(UpgradeDatabaseCoreService):
"""
Checks and upgrades the database schema. This assumes there are a bunch of
@@ -288,23 +265,19 @@
def __init__(self, sqlStore, service, uid=None, gid=None):
"""
Initialize the service.
-
- @param sqlStore: The store to operate on. Can be C{None} when doing
- unit tests.
-
- @param service: Wrapped service. Can be C{None} when doing unit tests.
+
+ @param sqlStore: The store to operate on. Can be C{None} when doing unit tests.
+ @param service: Wrapped service. Can be C{None} when doing unit tests.
"""
super(UpgradeDatabaseSchemaService, self).__init__(sqlStore, service, uid, gid)
-
+
self.versionKey = "VERSION"
self.versionDescriptor = "schema"
self.upgradeFileSuffix = ".sql"
-
def getPathToUpgrades(self, dialect):
return self.schemaLocation.child("upgrades").child(dialect)
-
@inlineCallbacks
def applyUpgrade(self, fp):
"""
@@ -314,134 +287,12 @@
sqlTxn = self.sqlStore.newTransaction()
try:
sql = fp.getContent()
- fromV, toV = self.versionsFromFilename(fp.basename())
- caseFix = False
- if fromV < 9 and toV >= 9:
- caseFix = True
- # If we're upgrading past version 9, look for calendar homes
- # that differ only by case and re-name one of them to 'x.old'.
- yield self.renameCaseDuplicates(sqlTxn, 'CALENDAR')
- yield self.renameCaseDuplicates(sqlTxn, 'ADDRESSBOOK')
- yield self.renameCaseDuplicates(sqlTxn, 'NOTIFICATION')
yield sqlTxn.execSQLBlock(sql)
- if caseFix:
- # This does not fit neatly into the existing upgrade machinery,
- # so it is just inline code here. It would be nicer if the
- # upgrade system could take something like this into account,
- # though.
- yield self.mergeCaseDuplicates(sqlTxn, 'CALENDAR')
- # yield self.mergeCaseDuplicates(sqlTxn, 'ADDRESSBOOK')
yield sqlTxn.commit()
except RuntimeError:
yield sqlTxn.abort()
raise
-
- @inlineCallbacks
- def renameCaseDuplicates(self, sqlTxn, type):
- """
- Re-name case duplicates.
-
- Prior to schema version 9, home UIDs were case-sensitive. This method
- re-names any names which are equivalent except for case differences, so
- that adding the uniform-case constraint will succeed.
-
- @param type: The type of home to scan; 'CALENDAR' or 'ADDRESSBOOK'
- @type type: C{str}
- """
- # This is using the most recent 'schema' object, which happens to work
- # for the moment, but will fail if the schema changes too radically.
- # Ideally this should be pointed at a schema object parsed from an older
- # version of the schema.
- home = getattr(schema, type + '_HOME')
- left = home.alias()
- right = home.alias()
- qry = Select(
- [left.OWNER_UID, right.OWNER_UID], From=left.join(right),
- Where=(CaseFold(left.OWNER_UID) == CaseFold(right.OWNER_UID))
- # Use > rather than != so that each duplicate only shows up
- # once.
- .And(left.OWNER_UID > right.OWNER_UID)
- )
- caseDupes = yield qry.on(sqlTxn)
- for (one, other) in caseDupes:
- both = []
- both.append([one, (yield determineNewest(one, type).on(sqlTxn))])
- both.append([other, (yield determineNewest(other, type).on(sqlTxn))])
- both.sort(key=lambda x: x[1])
- # Note: determineNewest may return None sometimes.
- older = both[0][0]
- self.log_warn("Moving aside case-duplicate " + repr(type.lower()) +
- " home " + repr(older))
- yield Update({home.OWNER_UID: _CASE_DUPLICATE_PREFIX + older},
- Where=home.OWNER_UID == older).on(sqlTxn)
-
-
- @inlineCallbacks
- def mergeCaseDuplicates(self, sqlTxn, type):
- """
- Merge together homes which were previously case-duplicates of each
- other, once the schema is upgraded.
- """
- home = getattr(schema, type + '_HOME')
- oldHomes = yield Select(
- [home.OWNER_UID], From=home,
- Where=home.OWNER_UID.StartsWith(_CASE_DUPLICATE_PREFIX)
- ).on(sqlTxn)
- for oldHomeUID in oldHomes:
- oldHomeUID = oldHomeUID[0]
- newHomeUID = oldHomeUID[len(_CASE_DUPLICATE_PREFIX):]
- if type == 'CALENDAR':
- from txdav.caldav.datastore.util import migrateHome
- self.log_warn("Merging case-duplicate home "
- + repr(newHomeUID) + "...")
- yield migrateHome(
- (yield sqlTxn.calendarHomeWithUID(oldHomeUID)),
- (yield sqlTxn.calendarHomeWithUID(newHomeUID)),
- merge=True
- )
- self.log_warn("Finished merging case-duplicate home "
- + repr(newHomeUID) + ".")
- # Addressbook migration is not (yet) implemented here, because
- # duplicate data will be somewhat harder to spot and more annoying
- # there. The data will still be kept in the database in the in
- # case-duplicate-prefix home though, so it is possible to retrieve.
-
-
-
-def determineNewest(uid, type):
- """
- Determine the modification time of the newest object in a given home.
-
- @param uid: the UID of the home to scan.
- @type uid: C{str}
-
- @param type: The type of home to scan; 'CALENDAR' or 'ADDRESSBOOK'
- @type type: C{str}
- """
- if type == 'NOTIFICATION':
- return Select(
- [Max(schema.NOTIFICATION.MODIFIED)],
- From=schema.NOTIFICATION_HOME.join(
- schema.NOTIFICATION,
- on=schema.NOTIFICATION_HOME.RESOURCE_ID ==
- schema.NOTIFICATION.NOTIFICATION_HOME_RESOURCE_ID),
- Where=schema.NOTIFICATION_HOME.OWNER_UID == uid
- )
- home = getattr(schema, type + "_HOME")
- bind = getattr(schema, type + "_BIND")
- child = getattr(schema, type)
- obj = getattr(schema, type + "_OBJECT")
- return Select(
- [Max(obj.MODIFIED)],
- From=home.join(bind, on=bind.HOME_RESOURCE_ID == home.RESOURCE_ID)
- .join(child, on=child.RESOURCE_ID == bind.RESOURCE_ID)
- .join(obj, on=obj.PARENT_RESOURCE_ID == child.RESOURCE_ID),
- Where=(bind.BIND_MODE == 0).And(home.OWNER_UID == uid)
- )
-
-
-
class UpgradeDatabaseDataService(UpgradeDatabaseCoreService):
"""
Checks and upgrades the database data. This assumes there are a bunch of
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20120307/fbf6e09f/attachment-0001.html>
More information about the calendarserver-changes
mailing list