[CalendarServer-changes] [11238] CalendarServer/trunk
source_changes at macosforge.org
source_changes at macosforge.org
Wed May 22 19:54:13 PDT 2013
Revision: 11238
http://trac.calendarserver.org//changeset/11238
Author: cdaboo at apple.com
Date: 2013-05-22 19:54:13 -0700 (Wed, 22 May 2013)
Log Message:
-----------
Allow Schema objects to be compared for missing tables, columns, indexes, and sequences. Use this in unit tests to
verify that an upgraded schema correctly matches the current full schema.
Modified Paths:
--------------
CalendarServer/trunk/twext/enterprise/dal/model.py
CalendarServer/trunk/twext/enterprise/dal/parseschema.py
CalendarServer/trunk/twext/enterprise/dal/test/test_parseschema.py
CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_19_to_20.sql
CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_19_to_20.sql
CalendarServer/trunk/txdav/common/datastore/test/test_sql_schema_files.py
CalendarServer/trunk/txdav/common/datastore/upgrade/sql/test/test_upgrade.py
Added Paths:
-----------
CalendarServer/trunk/txdav/common/datastore/sql_dump.py
Modified: CalendarServer/trunk/twext/enterprise/dal/model.py
===================================================================
--- CalendarServer/trunk/twext/enterprise/dal/model.py 2013-05-23 00:36:49 UTC (rev 11237)
+++ CalendarServer/trunk/twext/enterprise/dal/model.py 2013-05-23 02:54:13 UTC (rev 11238)
@@ -185,6 +185,24 @@
return '<Column (%s %r)>' % (self.name, self.type)
+ def compare(self, other):
+ """
+ Return the differences between two columns.
+
+ @param other: the column to compare with
+ @type other: L{Column}
+ """
+
+ results = []
+
+ # TODO: sql_dump does not do types write now - so ignore this
+# if self.type != other.type:
+# results.append("Table: %s, mismatched column type: %s" % (self.table.name, self.name))
+
+ # TODO: figure out how to compare default, references and deleteAction
+ return results
+
+
def canBeNull(self):
"""
Can this column ever be C{NULL}, i.e. C{None}? In other words, is it
@@ -266,6 +284,28 @@
return '<Table %r:%r>' % (self.name, self.columns)
+ def compare(self, other):
+ """
+ Return the differences between two tables.
+
+ @param other: the table to compare with
+ @type other: L{Table}
+ """
+
+ results = []
+
+ myColumns = dict([(item.name.lower(), item) for item in self.columns])
+ otherColumns = dict([(item.name.lower(), item) for item in other.columns])
+ for item in set(myColumns.keys()) ^ set(otherColumns.keys()):
+ results.append("Table: %s, missing column: %s" % (self.name, item,))
+
+ for name in set(myColumns.keys()) & set(otherColumns.keys()):
+ results.extend(myColumns[name].compare(otherColumns[name]))
+
+ # TODO: figure out how to compare schemaRows
+ return results
+
+
def columnNamed(self, name):
"""
Retrieve a column from this table with a given name.
@@ -369,9 +409,10 @@
An L{Index} is an SQL index.
"""
- def __init__(self, schema, name, table):
+ def __init__(self, schema, name, table, unique=False):
self.name = name
self.table = table
+ self.unique = unique
self.columns = []
schema.indexes.append(self)
@@ -381,6 +422,32 @@
+class PseudoIndex(object):
+ """
+ A class used to represent explicit and implicit indexes. An implicit index is one the
+ DB creates for primary key and unique columns in a table. An explicit index is one
+ created by a CREATE [UNIQUE] INDEX statement. Because the name of an implicit index
+ is implementation defined, instead we create a name based on the table name, uniqueness
+ and column names.
+ """
+
+ def __init__(self, table, columns, unique=False):
+ self.name = "%s%s:(%s)" % (table.name, "-unique" if unique else "", ",".join([col.name for col in columns]))
+
+
+ def compare(self, other):
+ """
+ Return the differences between two indexes.
+
+ @param other: the index to compare with
+ @type other: L{Index}
+ """
+
+ # Nothing to do as name comparison will catch differences
+ return []
+
+
+
class Sequence(FancyEqMixin, object):
"""
A sequence object.
@@ -399,7 +466,19 @@
return '<Sequence %r>' % (self.name,)
+ def compare(self, other):
+ """
+ Return the differences between two sequences.
+ @param other: the sequence to compare with
+ @type other: L{Sequence}
+ """
+
+ # TODO: figure out whether to compare referringColumns attribute
+ return []
+
+
+
def _namedFrom(name, sequence):
"""
Retrieve an item with a given name attribute from a given sequence, or raise
@@ -428,6 +507,56 @@
return '<Schema %r>' % (self.filename,)
+ def compare(self, other):
+ """
+ Return the differences between two schemas.
+
+ @param other: the schema to compare with
+ @type other: L{Schema}
+ """
+
+ results = []
+
+ def _compareLists(list1, list2, descriptor):
+ myItems = dict([(item.name.lower()[:63], item) for item in list1])
+ otherItems = dict([(item.name.lower()[:63], item) for item in list2])
+ for item in set(myItems.keys()) - set(otherItems.keys()):
+ results.append("Schema: %s, missing %s: %s" % (other.filename, descriptor, item,))
+ for item in set(otherItems.keys()) - set(myItems.keys()):
+ results.append("Schema: %s, missing %s: %s" % (self.filename, descriptor, item,))
+
+ for name in set(myItems.keys()) & set(otherItems.keys()):
+ results.extend(myItems[name].compare(otherItems[name]))
+
+ _compareLists(self.tables, other.tables, "table")
+ _compareLists(self.pseudoIndexes(), other.pseudoIndexes(), "index")
+ _compareLists(self.sequences, other.sequences, "sequence")
+
+ return results
+
+
+ def pseudoIndexes(self):
+ """
+ Return a set of indexes that include "implicit" indexes from table/column constraints. The name of the
+ index is formed from the table name and then list of columns.
+ """
+ results = []
+
+ # First add the list of explicit indexes we have
+ for index in self.indexes:
+ results.append(PseudoIndex(index.table, index.columns, index.unique))
+
+ # Now do implicit index for each table
+ for table in self.tables:
+ if table.primaryKey is not None:
+ results.append(PseudoIndex(table, table.primaryKey, True))
+ for constraint in table.constraints:
+ if constraint.type == Constraint.UNIQUE:
+ results.append(PseudoIndex(table, constraint.affectsColumns, True))
+
+ return results
+
+
def tableNamed(self, name):
return _namedFrom(name, self.tables)
Modified: CalendarServer/trunk/twext/enterprise/dal/parseschema.py
===================================================================
--- CalendarServer/trunk/twext/enterprise/dal/parseschema.py 2013-05-23 00:36:49 UTC (rev 11237)
+++ CalendarServer/trunk/twext/enterprise/dal/parseschema.py 2013-05-23 02:54:13 UTC (rev 11238)
@@ -95,6 +95,21 @@
+def schemaFromString(data):
+ """
+ Get a L{Schema}.
+
+ @param data: a C{str} containing SQL.
+
+ @return: a L{Schema} object with the contents of the given C{str} parsed
+ and added to it as L{Table} objects.
+ """
+ schema = Schema()
+ addSQLToSchema(schema, data)
+ return schema
+
+
+
def addSQLToSchema(schema, schemaData):
"""
Add new SQL to an existing schema.
@@ -124,14 +139,28 @@
elif createType == u'SEQUENCE':
Sequence(schema,
stmt.token_next(2, True).get_name().encode('utf-8'))
- elif createType == u'INDEX':
+ elif createType in (u'INDEX', u'UNIQUE'):
signifindex = iterSignificant(stmt)
expect(signifindex, ttype=Keyword.DDL, value='CREATE')
- expect(signifindex, ttype=Keyword, value='INDEX')
+ token = signifindex.next()
+ unique = False
+ if token.match(Keyword, "UNIQUE"):
+ unique = True
+ token = signifindex.next()
+ if not token.match(Keyword, "INDEX"):
+ raise ViolatedExpectation("INDEX or UNQIUE", token.value)
indexName = nameOrIdentifier(signifindex.next())
expect(signifindex, ttype=Keyword, value='ON')
- [tableName, columnArgs] = iterSignificant(expect(signifindex,
- cls=Function))
+ token = signifindex.next()
+ if isinstance(token, Function):
+ [tableName, columnArgs] = iterSignificant(token)
+ else:
+ tableName = token
+ token = signifindex.next()
+ if token.match(Keyword, "USING"):
+ [_ignore, columnArgs] = iterSignificant(expect(signifindex, cls=Function))
+ else:
+ raise ViolatedExpectation('USING', token)
tableName = nameOrIdentifier(tableName)
arggetter = iterSignificant(columnArgs)
@@ -143,7 +172,7 @@
valuelist = [valueOrValues]
expect(arggetter, ttype=Punctuation, value=u')')
- idx = Index(schema, indexName, schema.tableNamed(tableName))
+ idx = Index(schema, indexName, schema.tableNamed(tableName), unique)
for token in valuelist:
columnName = nameOrIdentifier(token)
idx.addColumn(idx.table.columnNamed(columnName))
Modified: CalendarServer/trunk/twext/enterprise/dal/test/test_parseschema.py
===================================================================
--- CalendarServer/trunk/twext/enterprise/dal/test/test_parseschema.py 2013-05-23 00:36:49 UTC (rev 11237)
+++ CalendarServer/trunk/twext/enterprise/dal/test/test_parseschema.py 2013-05-23 02:54:13 UTC (rev 11238)
@@ -348,7 +348,8 @@
create table z (c integer); -- make sure we get the right table
create index idx_a_b on a(b);
- create index idx_a_b_c on a(c, b);
+ create index idx_a_b_c on a (c, b);
+ create index idx_c on z using btree (c);
""")
a = s.tableNamed("a")
@@ -358,3 +359,25 @@
self.assertEquals(b.columns, [a.columnNamed("b")])
self.assertEquals(bc.table, a)
self.assertEquals(bc.columns, [a.columnNamed("c"), a.columnNamed("b")])
+
+
+ def test_pseudoIndexes(self):
+ """
+ A implicit and explicit indexes are listed.
+ """
+ s = self.schemaFromString(
+ """
+ create table q (b integer); -- noise
+ create table a (b integer primary key, c integer);
+ create table z (c integer, unique(c) );
+
+ create unique index idx_a_c on a(c);
+ create index idx_a_b_c on a (c, b);
+ """)
+
+ self.assertEqual(set([pseudo.name for pseudo in s.pseudoIndexes()]), set((
+ "a-unique:(c)",
+ "a:(c,b)",
+ "a-unique:(b)",
+ "z-unique:(c)",
+ )))
Added: CalendarServer/trunk/txdav/common/datastore/sql_dump.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_dump.py (rev 0)
+++ CalendarServer/trunk/txdav/common/datastore/sql_dump.py 2013-05-23 02:54:13 UTC (rev 11238)
@@ -0,0 +1,64 @@
+# -*- test-case-name: txdav.common.datastore.test.test_sql_tables -*-
+##
+# Copyright (c) 2010-2013 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 twisted.internet.defer import inlineCallbacks, returnValue
+from twext.enterprise.dal.model import Schema, Table, Column, Sequence
+from twext.enterprise.dal.parseschema import addSQLToSchema
+
+"""
+Dump a postgres DB into an L{Schema} model object.
+"""
+
+ at inlineCallbacks
+def dumpSchema(txn, title, schemaname="public"):
+ """
+ Generate the L{Schema}.
+ """
+
+ schemaname = schemaname.lower()
+
+ schema = Schema(title)
+ tables = {}
+
+ # Tables
+ rows = yield txn.execSQL("select table_name from information_schema.tables where table_schema = '%s';" % (schemaname,))
+ for row in rows:
+ name = row[0]
+ table = Table(schema, name)
+ tables[name] = table
+
+ # Columns
+ rows = yield txn.execSQL("select column_name from information_schema.columns where table_schema = '%s' and table_name = '%s';" % (schemaname, name,))
+ for row in rows:
+ name = row[0]
+ # TODO: figure out the type
+ column = Column(table, name, None)
+ table.columns.append(column)
+
+ # Indexes
+ # TODO: handle implicit indexes created via primary key() and unique() statements within CREATE TABLE
+ rows = yield txn.execSQL("select indexdef from pg_indexes where schemaname = '%s';" % (schemaname,))
+ for indexdef in rows:
+ addSQLToSchema(schema, indexdef[0].replace("%s." % (schemaname,), ""))
+
+ # Sequences
+ rows = yield txn.execSQL("select sequence_name from information_schema.sequences where sequence_schema = '%s';" % (schemaname,))
+ for row in rows:
+ name = row[0]
+ Sequence(schema, name)
+
+ returnValue(schema)
Modified: CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_19_to_20.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_19_to_20.sql 2013-05-23 00:36:49 UTC (rev 11237)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_19_to_20.sql 2013-05-23 02:54:13 UTC (rev 11238)
@@ -35,6 +35,7 @@
"ADDRESSBOOK_RESOURCE_NAME" nvarchar2(255),
"BIND_MODE" integer not null,
"BIND_STATUS" integer not null,
+ "BIND_REVISION" integer default 0 not null,
"MESSAGE" nclob,
primary key("ADDRESSBOOK_HOME_RESOURCE_ID", "OWNER_ADDRESSBOOK_HOME_RESOURCE_ID"),
unique("ADDRESSBOOK_HOME_RESOURCE_ID", "ADDRESSBOOK_RESOURCE_NAME")
@@ -57,6 +58,7 @@
"GROUP_ADDRESSBOOK_RESOURCE_NAME" nvarchar2(255),
"BIND_MODE" integer not null,
"BIND_STATUS" integer not null,
+ "BIND_REVISION" integer default 0 not null,
"MESSAGE" nclob,
primary key("ADDRESSBOOK_HOME_RESOURCE_ID", "GROUP_RESOURCE_ID"),
unique("ADDRESSBOOK_HOME_RESOURCE_ID", "GROUP_ADDRESSBOOK_RESOURCE_NAME")
@@ -178,7 +180,11 @@
"ADDRESSBOOK_HOME_RESOURCE_ID" not null)
drop ("ADDRESSBOOK_RESOURCE_ID");
-
+
+alter table ADDRESSBOOK_OBJECT
+ add unique ("ADDRESSBOOK_HOME_RESOURCE_ID", "RESOURCE_NAME")
+ unique ("ADDRESSBOOK_HOME_RESOURCE_ID", "VCARD_UID");
+
------------------------------------------
-- change ADDRESSBOOK_OBJECT_REVISIONS --
------------------------------------------
@@ -220,7 +226,23 @@
alter table ADDRESSBOOK_OBJECT_REVISIONS
drop ("ADDRESSBOOK_RESOURCE_ID");
+-- New indexes
+create index ADDRESSBOOK_OBJECT_RE_40cc2d73 on ADDRESSBOOK_OBJECT_REVISIONS (
+ ADDRESSBOOK_HOME_RESOURCE_ID,
+ OWNER_ADDRESSBOOK_HOME_RESOURCE_ID
+);
+create index ADDRESSBOOK_OBJECT_RE_980b9872 on ADDRESSBOOK_OBJECT_REVISIONS (
+ OWNER_ADDRESSBOOK_HOME_RESOURCE_ID,
+ RESOURCE_NAME
+);
+
+create index ADDRESSBOOK_OBJECT_RE_45004780 on ADDRESSBOOK_OBJECT_REVISIONS (
+ OWNER_ADDRESSBOOK_HOME_RESOURCE_ID,
+ REVISION
+);
+
+
-------------------------------
-- change RESOURCE_PROPERTY --
-------------------------------
Modified: CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_19_to_20.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_19_to_20.sql 2013-05-23 00:36:49 UTC (rev 11237)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_19_to_20.sql 2013-05-23 02:54:13 UTC (rev 11238)
@@ -35,6 +35,7 @@
ADDRESSBOOK_RESOURCE_NAME varchar(255) not null,
BIND_MODE integer not null, -- enum CALENDAR_BIND_MODE
BIND_STATUS integer not null, -- enum CALENDAR_BIND_STATUS
+ BIND_REVISION integer default 0 not null,
MESSAGE text, -- FIXME: xml?
primary key (ADDRESSBOOK_HOME_RESOURCE_ID, OWNER_ADDRESSBOOK_HOME_RESOURCE_ID), -- implicit index
@@ -57,6 +58,7 @@
GROUP_ADDRESSBOOK_RESOURCE_NAME varchar(255) not null,
BIND_MODE integer not null, -- enum CALENDAR_BIND_MODE
BIND_STATUS integer not null, -- enum CALENDAR_BIND_STATUS
+ BIND_REVISION integer default 0 not null,
MESSAGE text, -- FIXME: xml?
primary key (ADDRESSBOOK_HOME_RESOURCE_ID, GROUP_RESOURCE_ID), -- implicit index
@@ -175,7 +177,9 @@
alter table ADDRESSBOOK_OBJECT
alter column KIND set not null,
alter column ADDRESSBOOK_HOME_RESOURCE_ID set not null,
- drop column ADDRESSBOOK_RESOURCE_ID;
+ drop column ADDRESSBOOK_RESOURCE_ID,
+ add unique (ADDRESSBOOK_HOME_RESOURCE_ID, RESOURCE_NAME),
+ add unique (ADDRESSBOOK_HOME_RESOURCE_ID, VCARD_UID);
------------------------------------------
@@ -219,7 +223,17 @@
alter table ADDRESSBOOK_OBJECT_REVISIONS
drop column ADDRESSBOOK_RESOURCE_ID;
+-- New indexes
+create index ADDRESSBOOK_OBJECT_REVISIONS_HOME_RESOURCE_ID_OWNER_ADDRESSBOOK_HOME_RESOURCE_ID
+ on ADDRESSBOOK_OBJECT_REVISIONS(ADDRESSBOOK_HOME_RESOURCE_ID, OWNER_ADDRESSBOOK_HOME_RESOURCE_ID);
+create index ADDRESSBOOK_OBJECT_REVISIONS_OWNER_HOME_RESOURCE_ID_RESOURCE_NAME
+ on ADDRESSBOOK_OBJECT_REVISIONS(OWNER_ADDRESSBOOK_HOME_RESOURCE_ID, RESOURCE_NAME);
+
+create index ADDRESSBOOK_OBJECT_REVISIONS_OWNER_HOME_RESOURCE_ID_REVISION
+ on ADDRESSBOOK_OBJECT_REVISIONS(OWNER_ADDRESSBOOK_HOME_RESOURCE_ID, REVISION);
+
+
-------------------------------
-- change RESOURCE_PROPERTY --
-------------------------------
Modified: CalendarServer/trunk/txdav/common/datastore/test/test_sql_schema_files.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/test/test_sql_schema_files.py 2013-05-23 00:36:49 UTC (rev 11237)
+++ CalendarServer/trunk/txdav/common/datastore/test/test_sql_schema_files.py 2013-05-23 02:54:13 UTC (rev 11238)
@@ -14,6 +14,7 @@
# limitations under the License.
# #
+from twext.enterprise.dal.parseschema import schemaFromPath
from twisted.python.modules import getModule
from twisted.trial.unittest import TestCase
import re
@@ -100,3 +101,20 @@
current_oracle_version = self.versionFromSchema(currentOracleSchema)
self.assertEqual(current_version, current_oracle_version)
+
+
+ def test_schema_compare(self):
+
+ sqlSchema = getModule(__name__).filePath.parent().sibling("sql_schema")
+
+ # Test with same schema
+ currentSchema = schemaFromPath(sqlSchema.child("current.sql"))
+ duplicateSchema = schemaFromPath(sqlSchema.child("current.sql"))
+ mismatched = currentSchema.compare(duplicateSchema)
+ self.assertEqual(len(mismatched), 0)
+
+ # Test with same schema
+ v6Schema = schemaFromPath(sqlSchema.child("old").child("postgres-dialect").child("v6.sql"))
+ v5Schema = schemaFromPath(sqlSchema.child("old").child("postgres-dialect").child("v5.sql"))
+ mismatched = v6Schema.compare(v5Schema)
+ self.assertEqual(len(mismatched), 3, msg="\n".join(mismatched))
Modified: CalendarServer/trunk/txdav/common/datastore/upgrade/sql/test/test_upgrade.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/upgrade/sql/test/test_upgrade.py 2013-05-23 00:36:49 UTC (rev 11237)
+++ CalendarServer/trunk/txdav/common/datastore/upgrade/sql/test/test_upgrade.py 2013-05-23 02:54:13 UTC (rev 11238)
@@ -18,10 +18,12 @@
Tests for L{txdav.common.datastore.upgrade.sql.upgrade}.
"""
+from twext.enterprise.dal.parseschema import schemaFromPath
from twext.enterprise.ienterprise import ORACLE_DIALECT, POSTGRES_DIALECT
from twisted.internet.defer import inlineCallbacks, returnValue
from twisted.python.modules import getModule
from twisted.trial.unittest import TestCase
+from txdav.common.datastore.sql_dump import dumpSchema
from txdav.common.datastore.test.util import theStoreBuilder, StubNotifierFactory
from txdav.common.datastore.upgrade.sql.upgrade import UpgradeDatabaseSchemaStep, \
UpgradeDatabaseAddressBookDataStep, UpgradeDatabaseCalendarDataStep
@@ -166,6 +168,13 @@
returnValue(int(new_version[0][0]))
@inlineCallbacks
+ def _loadSchemaFromDatabase():
+ startTxn = store.newTransaction("test_dbUpgrades")
+ schema = yield dumpSchema(startTxn, "Upgraded from %s" % (child.basename(),), "test_dbUpgrades")
+ yield startTxn.commit()
+ returnValue(schema)
+
+ @inlineCallbacks
def _unloadOldSchema():
startTxn = store.newTransaction("test_dbUpgrades")
yield startTxn.execSQL("set search_path to public;")
@@ -189,6 +198,13 @@
yield _loadOldSchema(child)
yield upgrader.databaseUpgrade()
new_version = yield _loadVersion()
+
+ # Compare the upgraded schema with the expected current schema
+ new_schema = yield _loadSchemaFromDatabase()
+ currentSchema = schemaFromPath(test_upgrader.schemaLocation.child("current.sql"))
+ mismatched = currentSchema.compare(new_schema)
+ self.assertEqual(len(mismatched), 0, "Schema mismatch:\n" + "\n".join(mismatched))
+
yield _unloadOldSchema()
self.assertEqual(new_version, expected_version)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20130522/ba324873/attachment-0001.html>
More information about the calendarserver-changes
mailing list