[CalendarServer-changes] [6556] CalendarServer/trunk/contrib/tools/LO_DB_upgrade.py
source_changes at macosforge.org
source_changes at macosforge.org
Tue Nov 2 12:53:34 PDT 2010
Revision: 6556
http://trac.macosforge.org/projects/calendarserver/changeset/6556
Author: cdaboo at apple.com
Date: 2010-11-02 12:53:31 -0700 (Tue, 02 Nov 2010)
Log Message:
-----------
Upgrade existing SQL schema to new one during development.
Added Paths:
-----------
CalendarServer/trunk/contrib/tools/LO_DB_upgrade.py
Added: CalendarServer/trunk/contrib/tools/LO_DB_upgrade.py
===================================================================
--- CalendarServer/trunk/contrib/tools/LO_DB_upgrade.py (rev 0)
+++ CalendarServer/trunk/contrib/tools/LO_DB_upgrade.py 2010-11-02 19:53:31 UTC (rev 6556)
@@ -0,0 +1,214 @@
+#!/usr/bin/env python
+##
+# Copyright (c) 2010 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.
+##
+from txdav.base.propertystore.base import PropertyName
+from twistedcaldav.customxml import TwistedCalendarHasPrivateCommentsProperty,\
+ TwistedCalendarAccessProperty, TwistedSchedulingObjectResource,\
+ TwistedScheduleMatchETags
+import sys
+from twext.web2.dav.element.parser import WebDAVDocument
+from twistedcaldav.ical import Component
+from twext.web2.dav.resource import TwistedQuotaUsedProperty
+
+"""
+Tool to manage schema upgrade of SQL database during internal development phase as we don't have
+a "real" upgrade in place just yet.
+"""
+
+import pg
+import pgdb
+
+def query(db, sql, params=()):
+
+ cursor = db.cursor()
+ cursor.execute(sql, params)
+ return cursor
+
+def rowsForProperty(propelement):
+ pname = PropertyName.fromElement(propelement)
+ return query(db, """
+ select RESOURCE_ID, VALUE
+ from RESOURCE_PROPERTY
+ where NAME = %s
+ """, (pname.toString(),)
+ )
+
+def removeProperty(propelement):
+ pname = PropertyName.fromElement(propelement)
+ return query(db, """
+ delete from RESOURCE_PROPERTY
+ where NAME = %s
+ """, (pname.toString(),)
+ )
+
+if __name__ == "__main__":
+
+ db = pgdb.connect(database='caldav', host='localhost')
+
+ # Alter the CALENDAR_OBJECT table and add columns
+ print "Alter the CALENDAR_OBJECT table"
+ try:
+ query(db, """
+ alter table CALENDAR_OBJECT
+ add column
+ ACCESS integer default 0 not null,
+ add column
+ SCHEDULE_OBJECT boolean default false not null,
+ add column
+ SCHEDULE_ETAGS text default null,
+ add column
+ PRIVATE_COMMENTS boolean default false not null;
+
+ -- 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' );
+ """)
+
+ except pg.DatabaseError, e:
+ if str(e).find("already exists") == -1:
+ print "Fatal SQL error: %s" % (e,)
+ sys.exit(1)
+ db.commit()
+
+ # Copy and remove each dead property
+
+ # TwistedCalendarAccessProperty - copy string value into column.
+ print "Move TwistedCalendarAccessProperty"
+ for row in rowsForProperty(TwistedCalendarAccessProperty):
+ resource_id, value = row
+ prop = WebDAVDocument.fromString(value).root_element
+ mapped = {
+ "": 0,
+ Component.ACCESS_PUBLIC: 1,
+ Component.ACCESS_PRIVATE: 2,
+ Component.ACCESS_CONFIDENTIAL:3,
+ Component.ACCESS_RESTRICTED: 4,
+ }[str(prop)]
+ query(db, """
+ update CALENDAR_OBJECT
+ set ACCESS = %s
+ where RESOURCE_ID = %s
+ """, (mapped, resource_id,)
+ )
+ removeProperty(TwistedCalendarAccessProperty)
+ db.commit()
+
+ # TwistedSchedulingObjectResource - copy boolean value into column.
+ print "Move TwistedSchedulingObjectResource"
+ for row in rowsForProperty(TwistedSchedulingObjectResource):
+ resource_id, value = row
+ prop = WebDAVDocument.fromString(value).root_element
+ query(db, """
+ update CALENDAR_OBJECT
+ set SCHEDULE_OBJECT = %s
+ where RESOURCE_ID = %s
+ """, (str(prop), resource_id,)
+ )
+ removeProperty(TwistedSchedulingObjectResource)
+ db.commit()
+
+ # TwistedScheduleMatchETags - copy string-list value into column.
+ print "Move TwistedScheduleMatchETags"
+ for row in rowsForProperty(TwistedScheduleMatchETags):
+ resource_id, value = row
+ etags = [str(etag) for etag in WebDAVDocument.fromString(value).root_element.children]
+ query(db, """
+ update CALENDAR_OBJECT
+ set SCHEDULE_ETAGS = %s
+ where RESOURCE_ID = %s
+ """, (",".join(etags), resource_id,)
+ )
+ removeProperty(TwistedScheduleMatchETags)
+ db.commit()
+
+ # TwistedCalendarHasPrivateCommentsProperty - copy boolean true value into column.
+ print "Move TwistedCalendarHasPrivateCommentsProperty"
+ for row in rowsForProperty(TwistedCalendarHasPrivateCommentsProperty):
+ resource_id, value = row
+ prop = WebDAVDocument.fromString(value).root_element
+ query(db, """
+ update CALENDAR_OBJECT
+ set PRIVATE_COMMENTS = true
+ where RESOURCE_ID = %s
+ """, (resource_id,)
+ )
+ removeProperty(TwistedCalendarHasPrivateCommentsProperty)
+ db.commit()
+
+ # Alter the CALENDAR_HOME table and add columns
+ print "Alter the CALENDAR_HOME table"
+ try:
+ query(db, """
+ alter table CALENDAR_HOME
+ add column
+ QUOTA_USED_BYTES integer default 0 not null;
+ """)
+
+ except pg.DatabaseError, e:
+ if str(e).find("already exists") == -1:
+ print "Fatal SQL error: %s" % (e,)
+ sys.exit(1)
+ db.commit()
+
+ # Alter the ADDRESSBOOK_HOME table and add columns
+ print "Alter the ADDRESSBOOK_HOME table"
+ try:
+ query(db, """
+ alter table ADDRESSBOOK_HOME
+ add column
+ QUOTA_USED_BYTES integer default 0 not null;
+ """)
+
+ except pg.DatabaseError, e:
+ if str(e).find("already exists") == -1:
+ print "Fatal SQL error: %s" % (e,)
+ sys.exit(1)
+ db.commit()
+
+ # Copy and remove each dead property
+
+ # TwistedQuotaUsedProperty - copy string value into column.
+ print "Move TwistedQuotaUsedProperty"
+ for row in rowsForProperty(TwistedQuotaUsedProperty):
+ resource_id, value = row
+ prop = WebDAVDocument.fromString(value).root_element
+
+ # Since we don't know whether the resource-id is a calendar home or addressbook home
+ # just try updating both tables - the one that does not match will simply be ignored.
+ query(db, """
+ update CALENDAR_HOME
+ set QUOTA_USED_BYTES = %s
+ where RESOURCE_ID = %s
+ """, (int(str(prop)), resource_id,)
+ )
+ query(db, """
+ update ADDRESSBOOK_HOME
+ set QUOTA_USED_BYTES = %s
+ where RESOURCE_ID = %s
+ """, (int(str(prop)), resource_id,)
+ )
+ removeProperty(TwistedQuotaUsedProperty)
+ db.commit()
+
Property changes on: CalendarServer/trunk/contrib/tools/LO_DB_upgrade.py
___________________________________________________________________
Added: svn:executable
+ *
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20101102/a18a0d95/attachment-0001.html>
More information about the calendarserver-changes
mailing list