[CalendarServer-changes] [8106] CalendarServer/trunk
source_changes at macosforge.org
source_changes at macosforge.org
Mon Sep 19 13:02:18 PDT 2011
Revision: 8106
http://trac.macosforge.org/projects/calendarserver/changeset/8106
Author: glyph at apple.com
Date: 2011-09-19 13:02:17 -0700 (Mon, 19 Sep 2011)
Log Message:
-----------
Fix several issues with schema parsing and translation.
- Parse primary keys consistently as lists, instead of sometimes individual columns and sometimes tuples of columns.
- Parse uniqueness constraints as lists, since order may be relevant for query optimization purposes.
- Translate multi-column primary keys.
- Translate multi-column uniqueness constraints.
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_tables.py
CalendarServer/trunk/txdav/common/datastore/test/test_sql_tables.py
Modified: CalendarServer/trunk/twext/enterprise/dal/model.py
===================================================================
--- CalendarServer/trunk/twext/enterprise/dal/model.py 2011-09-16 21:08:24 UTC (rev 8105)
+++ CalendarServer/trunk/twext/enterprise/dal/model.py 2011-09-19 20:02:17 UTC (rev 8106)
@@ -209,8 +209,6 @@
@type name: L{str}
"""
self.references = self.table.schema.tableNamed(name)
- if self.references.primaryKey.type != self.type:
- print 'Mismatch', self.references.primaryKey.type, self.type
@@ -224,6 +222,9 @@
@type descriptiveComment: C{str}
@ivar schema: a reference to the L{Schema} to which this table belongs.
+
+ @ivar primaryKey: a C{list} of L{Column} objects representing the primary
+ key of this table, or C{None} if no primary key has been specified.
"""
compareAttributes = 'schema name'.split()
@@ -321,14 +322,17 @@
def uniques(self):
"""
- @return: an iterable of C{set}s of C{Column}s which are unique within
+ Get the groups of unique columns for this L{Table}.
+
+ @return: an iterable of C{list}s of C{Column}s which are unique within
this table.
"""
for constraint in self.constraints:
if constraint.type is Constraint.UNIQUE:
- yield set(constraint.affectsColumns)
+ yield list(constraint.affectsColumns)
+
class Index(object):
"""
An L{Index} is an SQL index.
Modified: CalendarServer/trunk/twext/enterprise/dal/parseschema.py
===================================================================
--- CalendarServer/trunk/twext/enterprise/dal/parseschema.py 2011-09-16 21:08:24 UTC (rev 8105)
+++ CalendarServer/trunk/twext/enterprise/dal/parseschema.py 2011-09-19 20:02:17 UTC (rev 8106)
@@ -259,8 +259,7 @@
if constraintType.match(Keyword, 'PRIMARY'):
expect(self, ttype=Keyword, value='KEY')
names = self.namesInParens(expect(self, cls=Parenthesis))
- self.table.primaryKey = tuple(self.table.columnNamed(n)
- for n in names)
+ self.table.primaryKey = [self.table.columnNamed(n) for n in names]
elif constraintType.match(Keyword, 'UNIQUE'):
names = self.namesInParens(expect(self, cls=Parenthesis))
self.table.tableConstraint(Constraint.UNIQUE, names)
@@ -320,7 +319,7 @@
if val.match(Keyword, 'PRIMARY'):
expect(self, ttype=Keyword, value='KEY')
# XXX check to make sure there's no other primary key yet
- self.table.primaryKey = theColumn
+ self.table.primaryKey = [theColumn]
elif val.match(Keyword, 'UNIQUE'):
# XXX add UNIQUE constraint
oneConstraint(Constraint.UNIQUE)
Modified: CalendarServer/trunk/twext/enterprise/dal/test/test_parseschema.py
===================================================================
--- CalendarServer/trunk/twext/enterprise/dal/test/test_parseschema.py 2011-09-16 21:08:24 UTC (rev 8105)
+++ CalendarServer/trunk/twext/enterprise/dal/test/test_parseschema.py 2011-09-19 20:02:17 UTC (rev 8106)
@@ -25,8 +25,23 @@
from twisted.trial.unittest import TestCase
-class ParsingExampleTests(TestCase):
+class SchemaTestHelper(object):
"""
+ Mix-in that can parse a schema from a string.
+ """
+
+ def schemaFromString(self, string):
+ """
+ Createa a L{Schema}
+ """
+ s = Schema(self.id())
+ addSQLToSchema(s, string)
+ return s
+
+
+
+class ParsingExampleTests(TestCase, SchemaTestHelper):
+ """
Tests for parsing some sample schemas.
"""
@@ -34,8 +49,7 @@
"""
Parse an extremely simple schema with one table in it.
"""
- s = Schema()
- addSQLToSchema(s, "create table foo (bar integer);")
+ s = self.schemaFromString("create table foo (bar integer);")
self.assertEquals(len(s.tables), 1)
foo = s.tableNamed('foo')
self.assertEquals(len(foo.columns), 1)
@@ -48,8 +62,7 @@
"""
Table and column names should be byte strings.
"""
- s = Schema()
- addSQLToSchema(s, "create table foo (bar integer);")
+ s = self.schemaFromString("create table foo (bar integer);")
self.assertEquals(len(s.tables), 1)
foo = s.tableNamed('foo')
self.assertIsInstance(foo.name, str)
@@ -60,8 +73,7 @@
"""
Parse a type with a length.
"""
- s = Schema()
- addSQLToSchema(s, "create table foo (bar varchar(6543))")
+ s = self.schemaFromString("create table foo (bar varchar(6543))")
bar = s.tableNamed('foo').columnNamed('bar')
self.assertEquals(bar.type.name, "varchar")
self.assertEquals(bar.type.length, 6543)
@@ -72,8 +84,7 @@
Parsing a 'create sequence' statement adds a L{Sequence} to the
L{Schema}.
"""
- s = Schema()
- addSQLToSchema(s, "create sequence myseq;")
+ s = self.schemaFromString("create sequence myseq;")
self.assertEquals(len(s.sequences), 1)
self.assertEquals(s.sequences[0].name, "myseq")
@@ -86,14 +97,13 @@
will cause the L{Column} object to refer to the L{Sequence} and vice
versa.
"""
- s = Schema()
- addSQLToSchema(s,
- """
- create sequence thingy;
- create table thetable (
- thecolumn integer default nextval('thingy')
- );
- """)
+ s = self.schemaFromString(
+ """
+ create sequence thingy;
+ create table thetable (
+ thecolumn integer default nextval('thingy')
+ );
+ """)
self.assertEquals(len(s.sequences), 1)
self.assertEquals(s.sequences[0].name, "thingy")
self.assertEquals(s.tables[0].columns[0].default, s.sequences[0])
@@ -105,15 +115,14 @@
"""
Default sequence column.
"""
- s = Schema()
- addSQLToSchema(s,
- """
- create sequence alpha;
- create table foo (
- bar integer default nextval('alpha') not null,
- qux integer not null
- );
- """)
+ s = self.schemaFromString(
+ """
+ create sequence alpha;
+ create table foo (
+ bar integer default nextval('alpha') not null,
+ qux integer not null
+ );
+ """)
self.assertEquals(s.tableNamed("foo").columnNamed("bar").needsValue(),
False)
@@ -123,17 +132,16 @@
Parsing a 'default' column with an appropriate type in it will return
that type as the 'default' attribute of the Column object.
"""
- s = Schema()
- addSQLToSchema(s,
- """
- create table a (
- b integer default 4321,
- c boolean default false,
- d boolean default true,
- e varchar(255) default 'sample value',
- f varchar(255) default null
- );
- """)
+ s = self.schemaFromString(
+ """
+ create table a (
+ b integer default 4321,
+ c boolean default false,
+ d boolean default true,
+ e varchar(255) default 'sample value',
+ f varchar(255) default null
+ );
+ """)
table = s.tableNamed("a")
self.assertEquals(table.columnNamed("b").default, 4321)
self.assertEquals(table.columnNamed("c").default, False)
@@ -147,16 +155,15 @@
Columns with defaults, or with a 'not null' constraint don't need a
value; columns without one don't.
"""
- s = Schema()
- addSQLToSchema(s,
- """
- create table a (
- b integer default 4321 not null,
- c boolean default false,
- d integer not null,
- e integer
- )
- """)
+ s = self.schemaFromString(
+ """
+ create table a (
+ b integer default 4321 not null,
+ c boolean default false,
+ d integer not null,
+ e integer
+ )
+ """)
table = s.tableNamed("a")
# Has a default, NOT NULL.
self.assertEquals(table.columnNamed("b").needsValue(), False)
@@ -173,12 +180,9 @@
A column with a NOT NULL constraint in SQL will be parsed as a
constraint which returns False from its C{canBeNull()} method.
"""
- s = Schema()
- addSQLToSchema(s,
- """
- create table alpha (beta integer,
- gamma integer not null);
- """)
+ s = self.schemaFromString(
+ "create table alpha (beta integer, gamma integer not null);"
+ )
t = s.tableNamed('alpha')
self.assertEquals(True, t.columnNamed('beta').canBeNull())
self.assertEquals(False, t.columnNamed('gamma').canBeNull())
@@ -192,11 +196,10 @@
for identicalSchema in [
"create table sample (example integer unique);",
"create table sample (example integer, unique(example));"]:
- s = Schema()
- addSQLToSchema(s, identicalSchema)
+ s = self.schemaFromString(identicalSchema)
table = s.tableNamed('sample')
column = table.columnNamed('example')
- self.assertEquals(list(table.uniques()), [set([column])])
+ self.assertEquals(list(table.uniques()), [[column]])
def test_multiUnique(self):
@@ -204,28 +207,40 @@
A column with a UNIQUE constraint in SQL will result in the table
listing that column as a unique set.
"""
- s = Schema()
- addSQLToSchema(
- s,
- "create table a (b integer, c integer, unique(b, c), unique(c));")
+ s = self.schemaFromString(
+ "create table a (b integer, c integer, unique(b, c), unique(c));"
+ )
a = s.tableNamed('a')
b = a.columnNamed('b')
c = a.columnNamed('c')
- self.assertEquals(list(a.uniques()),
- [set([b, c]), set([c])])
+ self.assertEquals(list(a.uniques()), [[b, c], [c]])
+ def test_singlePrimaryKey(self):
+ """
+ A table with a multi-column PRIMARY KEY clause will be parsed as a list
+ of a single L{Column} object and stored as a C{primaryKey} attribute on
+ the L{Table} object.
+ """
+ s = self.schemaFromString(
+ "create table a (b integer primary key, c integer)"
+ )
+ a = s.tableNamed("a")
+ self.assertEquals(a.primaryKey, [a.columnNamed("b")])
+
+
def test_multiPrimaryKey(self):
"""
- A table with a multi-column PRIMARY KEY clause will be parsed as a tuple
- primaryKey attribute on the Table object.
+ A table with a multi-column PRIMARY KEY clause will be parsed as a list
+ C{primaryKey} attribute on the Table object.
"""
- s = Schema()
- addSQLToSchema(
- s, "create table a (b integer, c integer, primary key(b, c))")
+ s = self.schemaFromString(
+ "create table a (b integer, c integer, primary key(b, c))"
+ )
a = s.tableNamed("a")
- self.assertEquals(a.primaryKey,
- (a.columnNamed("b"), a.columnNamed("c")))
+ self.assertEquals(
+ a.primaryKey, [a.columnNamed("b"), a.columnNamed("c")]
+ )
def test_cascade(self):
@@ -233,9 +248,7 @@
A column with an 'on delete cascade' constraint will have its C{cascade}
attribute set to True.
"""
- s = Schema()
- addSQLToSchema(
- s,
+ s = self.schemaFromString(
"""
create table a (b integer primary key);
create table c (d integer references a on delete cascade);
@@ -249,9 +262,7 @@
A 'create index' statement will add an L{Index} object to a L{Schema}'s
C{indexes} list.
"""
- s = Schema()
- addSQLToSchema(
- s,
+ s = self.schemaFromString(
"""
create table q (b integer); -- noise
create table a (b integer primary key, c integer);
Modified: CalendarServer/trunk/txdav/common/datastore/sql_tables.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_tables.py 2011-09-16 21:08:24 UTC (rev 8105)
+++ CalendarServer/trunk/txdav/common/datastore/sql_tables.py 2011-09-19 20:02:17 UTC (rev 8106)
@@ -209,6 +209,12 @@
"""
+_translatedTypes = {
+ 'text': 'nclob',
+ 'boolean': 'integer',
+ 'varchar': 'nvarchar2',
+ 'char': 'nchar',
+}
def _translateSchema(out, schema=schema):
"""
@@ -236,18 +242,11 @@
else:
out.write(",\n")
typeName = column.model.type.name
- if typeName == 'text':
- typeName = 'nclob'
- if typeName == 'boolean':
- typeName = 'integer'
- if typeName == 'varchar':
- typeName = 'nvarchar2'
- if typeName == 'char':
- typeName = 'nchar'
+ typeName = _translatedTypes.get(typeName, typeName)
out.write(' "%s" %s' % (column.model.name, typeName))
if column.model.type.length:
out.write("(%s)" % (column.model.type.length,))
- if column.model is table.model.primaryKey:
+ if [column.model] == table.model.primaryKey:
out.write(' primary key')
default = column.model.default
if default is not NO_DEFAULT:
@@ -273,13 +272,28 @@
and typeName not in ('varchar', 'nclob', 'char', 'nchar',
'nvarchar', 'nvarchar2') ):
out.write(' not null')
- if set([column.model]) in list(table.model.uniques()):
+ if [column.model] in list(table.model.uniques()):
out.write(' unique')
if column.model.references is not None:
out.write(" references %s" % (column.model.references.name,))
if column.model.cascade:
out.write(" on delete cascade")
+ def writeConstraint(name, cols):
+ out.write(", \n") # the table has to have some preceding columns
+ out.write(" %s(%s)" % (
+ name, ", ".join('"' + col.name + '"' for col in cols)
+ ))
+
+ for uniqueColumns in table.model.uniques():
+ if len(uniqueColumns) == 1:
+ continue # already done inline, skip
+ writeConstraint("unique", uniqueColumns)
+
+ pk = table.model.primaryKey
+ if pk is not None and len(pk) > 1:
+ writeConstraint("primary key", pk)
+
out.write('\n);\n\n')
fakeMeta = FixedPlaceholder(ORACLE_DIALECT, '%s')
Modified: CalendarServer/trunk/txdav/common/datastore/test/test_sql_tables.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/test/test_sql_tables.py 2011-09-16 21:08:24 UTC (rev 8105)
+++ CalendarServer/trunk/txdav/common/datastore/test/test_sql_tables.py 2011-09-19 20:02:17 UTC (rev 8106)
@@ -25,20 +25,41 @@
from cStringIO import StringIO
-from txdav.common.datastore.sql_tables import schema, _translateSchema
-from txdav.common.datastore.sql_tables import SchemaBroken
-from twext.enterprise.dal.parseschema import addSQLToSchema
-from twext.enterprise.dal.model import Schema
-from twext.enterprise.dal.syntax import SchemaSyntax
from twisted.python.modules import getModule
from twisted.trial.unittest import TestCase
-class SampleSomeColumns(TestCase):
+from twext.enterprise.dal.syntax import SchemaSyntax
+
+from txdav.common.datastore.sql_tables import schema, _translateSchema
+from txdav.common.datastore.sql_tables import SchemaBroken
+
+from twext.enterprise.dal.test.test_parseschema import SchemaTestHelper
+
+class SampleSomeColumns(TestCase, SchemaTestHelper):
"""
Sample some columns from the tables defined by L{schema} and verify that
they look correct.
"""
+ def translated(self, *schema):
+ """
+ Translate the given schema (or the default schema if no schema given)
+ and return the resulting SQL as a string.
+ """
+ io = StringIO()
+ _translateSchema(io, *schema)
+ return io.getvalue()
+
+
+ def assertSortaEquals(self, a, b):
+ """
+ Assert that two strings are equals, modulo whitespace differences.
+ """
+ sortaA = " ".join(a.split())
+ sortaB = " ".join(b.split())
+ self.assertEquals(sortaA, sortaB)
+
+
def test_addressbookObjectResourceID(self):
ao = schema.ADDRESSBOOK_OBJECT
self.assertEquals(ao.RESOURCE_ID.model.name,
@@ -47,32 +68,130 @@
def test_schemaTranslation(self):
"""
- Basic integration test to make sure that the schema can be translated
- without exception.
+ Basic integration test to make sure that the current, production schema
+ can be translated without errors.
"""
- # TODO: better test coverage of the actual functionality here; there are
- # no unit tests.
- _translateSchema(StringIO())
+ self.translated()
def test_schemaTranslationIncludesVersion(self):
"""
_translateSchema includes 'insert' rows too.
"""
-
- pathObj = getModule(__name__).filePath.parent().sibling("sql_schema").child("current.sql")
+
+ pathObj = (
+ getModule(__name__).filePath
+ .parent().sibling("sql_schema").child("current.sql")
+ )
schema = pathObj.getContent()
pos = schema.find("('VERSION', '")
version = int(schema[pos+13])
-
- io = StringIO()
- _translateSchema(io)
-
self.assertIn("insert into CALENDARSERVER (NAME, VALUE) "
"values ('VERSION', '%s');" % version,
- io.getvalue())
+ self.translated())
+ def test_translateSingleUnique(self):
+ """
+ L{_translateSchema} translates single-column 'unique' statements inline.
+ """
+ self.assertSortaEquals(
+ self.translated(
+ SchemaSyntax(
+ self.schemaFromString(
+ "create table alpha (beta integer unique)"
+ )
+ )
+ ),
+ 'create table alpha ( "beta" integer unique );'
+ )
+
+
+ def test_translateSingleTableUnique(self):
+ """
+ L{_translateSchema} translates single-column 'unique' statements inline,
+ even if they were originally at the table level.
+ """
+ stx = SchemaSyntax(
+ self.schemaFromString(
+ "create table alpha (beta integer, unique(beta))"
+ )
+ )
+ self.assertSortaEquals(
+ self.translated(stx),
+ 'create table alpha ( "beta" integer unique );'
+ )
+
+
+ def test_multiTableUnique(self):
+ """
+ L{_translateSchema} translates multi-column 'unique' statements.
+ """
+
+ stx = SchemaSyntax(
+ self.schemaFromString(
+ "create table alpha ("
+ "beta integer, gamma text, unique(beta, gamma))"
+ )
+ )
+ self.assertSortaEquals(
+ self.translated(stx),
+ 'create table alpha ( "beta" integer, "gamma" nclob, '
+ 'unique("beta", "gamma") );'
+ )
+
+
+ def test_translateSinglePrimaryKey(self):
+ """
+ L{_translateSchema} translates single-column 'primary key' statements
+ inline.
+ """
+ self.assertSortaEquals(
+ self.translated(
+ SchemaSyntax(
+ self.schemaFromString(
+ "create table alpha (beta integer primary key)"
+ )
+ )
+ ),
+ 'create table alpha ( "beta" integer primary key );'
+ )
+
+
+ def test_translateSingleTablePrimaryKey(self):
+ """
+ L{_translateSchema} translates single-column 'primary key' statements
+ inline, even if they were originally at the table level.
+ """
+ stx = SchemaSyntax(
+ self.schemaFromString(
+ "create table alpha (beta integer, primary key(beta))"
+ )
+ )
+ self.assertSortaEquals(
+ self.translated(stx),
+ 'create table alpha ( "beta" integer primary key );'
+ )
+
+
+ def test_multiTablePrimaryKey(self):
+ """
+ L{_translateSchema} translates multi-column 'primary key' statements.
+ """
+
+ stx = SchemaSyntax(
+ self.schemaFromString(
+ "create table alpha ("
+ "beta integer, gamma text, primary key(beta, gamma))"
+ )
+ )
+ self.assertSortaEquals(
+ self.translated(stx),
+ 'create table alpha ( "beta" integer, "gamma" nclob, '
+ 'primary key("beta", "gamma") );'
+ )
+
+
def test_youBrokeTheSchema(self):
"""
Oracle table names have a 30-character limit. Our schema translator
@@ -83,16 +202,14 @@
happens.)
"""
# TODO: same thing for sequences.
- schema = Schema()
- addSQLToSchema(
- schema, """
+ schema = self.schemaFromString(
+ """
create table same_012345678012345678990123456789_1 (foo integer);
create table same_012345678012345678990123456789_2 (bar text);
"""
)
- io = StringIO()
self.assertRaises(
- SchemaBroken, _translateSchema, io, SchemaSyntax(schema)
+ SchemaBroken, self.translated, SchemaSyntax(schema)
)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20110919/418a7a71/attachment-0001.html>
More information about the calendarserver-changes
mailing list