<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head><meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>[13367] twext/trunk</title>
</head>
<body>
<style type="text/css"><!--
#msg dl.meta { border: 1px #006 solid; background: #369; padding: 6px; color: #fff; }
#msg dl.meta dt { float: left; width: 6em; font-weight: bold; }
#msg dt:after { content:':';}
#msg dl, #msg dt, #msg ul, #msg li, #header, #footer, #logmsg { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; }
#msg dl a { font-weight: bold}
#msg dl a:link { color:#fc3; }
#msg dl a:active { color:#ff0; }
#msg dl a:visited { color:#cc6; }
h3 { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; font-weight: bold; }
#msg pre { overflow: auto; background: #ffc; border: 1px #fa0 solid; padding: 6px; }
#logmsg { background: #ffc; border: 1px #fa0 solid; padding: 1em 1em 0 1em; }
#logmsg p, #logmsg pre, #logmsg blockquote { margin: 0 0 1em 0; }
#logmsg p, #logmsg li, #logmsg dt, #logmsg dd { line-height: 14pt; }
#logmsg h1, #logmsg h2, #logmsg h3, #logmsg h4, #logmsg h5, #logmsg h6 { margin: .5em 0; }
#logmsg h1:first-child, #logmsg h2:first-child, #logmsg h3:first-child, #logmsg h4:first-child, #logmsg h5:first-child, #logmsg h6:first-child { margin-top: 0; }
#logmsg ul, #logmsg ol { padding: 0; list-style-position: inside; margin: 0 0 0 1em; }
#logmsg ul { text-indent: -1em; padding-left: 1em; }#logmsg ol { text-indent: -1.5em; padding-left: 1.5em; }
#logmsg > ul, #logmsg > ol { margin: 0 0 1em 0; }
#logmsg pre { background: #eee; padding: 1em; }
#logmsg blockquote { border: 1px solid #fa0; border-left-width: 10px; padding: 1em 1em 0 1em; background: white;}
#logmsg dl { margin: 0; }
#logmsg dt { font-weight: bold; }
#logmsg dd { margin: 0; padding: 0 0 0.5em 0; }
#logmsg dd:before { content:'\00bb';}
#logmsg table { border-spacing: 0px; border-collapse: collapse; border-top: 4px solid #fa0; border-bottom: 1px solid #fa0; background: #fff; }
#logmsg table th { text-align: left; font-weight: normal; padding: 0.2em 0.5em; border-top: 1px dotted #fa0; }
#logmsg table td { text-align: right; border-top: 1px dotted #fa0; padding: 0.2em 0.5em; }
#logmsg table thead th { text-align: center; border-bottom: 1px solid #fa0; }
#logmsg table th.Corner { text-align: left; }
#logmsg hr { border: none 0; border-top: 2px dashed #fa0; height: 1px; }
#header, #footer { color: #fff; background: #636; border: 1px #300 solid; padding: 6px; }
#patch { width: 100%; }
#patch h4 {font-family: verdana,arial,helvetica,sans-serif;font-size:10pt;padding:8px;background:#369;color:#fff;margin:0;}
#patch .propset h4, #patch .binary h4 {margin:0;}
#patch pre {padding:0;line-height:1.2em;margin:0;}
#patch .diff {width:100%;background:#eee;padding: 0 0 10px 0;overflow:auto;}
#patch .propset .diff, #patch .binary .diff {padding:10px 0;}
#patch span {display:block;padding:0 10px;}
#patch .modfile, #patch .addfile, #patch .delfile, #patch .propset, #patch .binary, #patch .copfile {border:1px solid #ccc;margin:10px 0;}
#patch ins {background:#dfd;text-decoration:none;display:block;padding:0 10px;}
#patch del {background:#fdd;text-decoration:none;display:block;padding:0 10px;}
#patch .lines, .info {color:#888;background:#fff;}
--></style>
<div id="msg">
<dl class="meta">
<dt>Revision</dt> <dd><a href="http://trac.calendarserver.org//changeset/13367">13367</a></dd>
<dt>Author</dt> <dd>cdaboo@apple.com</dd>
<dt>Date</dt> <dd>2014-04-24 09:10:35 -0700 (Thu, 24 Apr 2014)</dd>
</dl>
<h3>Log Message</h3>
<pre>Use latest sqlparse which supports Oracle syntax. Fix parsing and schema compare to work with more
complex postgres and Oracle behaviors.</pre>
<h3>Modified Paths</h3>
<ul>
<li><a href="#twexttrunksetuppy">twext/trunk/setup.py</a></li>
<li><a href="#twexttrunktwextenterprisedalmodelpy">twext/trunk/twext/enterprise/dal/model.py</a></li>
<li><a href="#twexttrunktwextenterprisedalparseschemapy">twext/trunk/twext/enterprise/dal/parseschema.py</a></li>
<li><a href="#twexttrunktwextenterprisedaltesttest_parseschemapy">twext/trunk/twext/enterprise/dal/test/test_parseschema.py</a></li>
</ul>
</div>
<div id="patch">
<h3>Diff</h3>
<a id="twexttrunksetuppy"></a>
<div class="modfile"><h4>Modified: twext/trunk/setup.py (13366 => 13367)</h4>
<pre class="diff"><span>
<span class="info">--- twext/trunk/setup.py 2014-04-24 04:27:28 UTC (rev 13366)
+++ twext/trunk/setup.py 2014-04-24 16:10:35 UTC (rev 13367)
</span><span class="lines">@@ -38,6 +38,7 @@
</span><span class="cx"> return modules + setuptools_find_packages()
</span><span class="cx">
</span><span class="cx">
</span><ins>+
</ins><span class="cx"> def version():
</span><span class="cx"> """
</span><span class="cx"> Compute the version number.
</span><span class="lines">@@ -142,7 +143,7 @@
</span><span class="cx">
</span><span class="cx"> extras_requirements = {
</span><span class="cx"> # Database Abstraction Layer
</span><del>- "DAL": ["sqlparse==0.1.2"],
</del><ins>+ "DAL": ["sqlparse>=0.1.11"],
</ins><span class="cx">
</span><span class="cx"> # LDAP
</span><span class="cx"> "LDAP": ["python-ldap"],
</span><span class="lines">@@ -177,6 +178,7 @@
</span><span class="cx"> pass
</span><span class="cx">
</span><span class="cx">
</span><ins>+
</ins><span class="cx"> #
</span><span class="cx"> # Run setup
</span><span class="cx"> #
</span></span></pre></div>
<a id="twexttrunktwextenterprisedalmodelpy"></a>
<div class="modfile"><h4>Modified: twext/trunk/twext/enterprise/dal/model.py (13366 => 13367)</h4>
<pre class="diff"><span>
<span class="info">--- twext/trunk/twext/enterprise/dal/model.py 2014-04-24 04:27:28 UTC (rev 13366)
+++ twext/trunk/twext/enterprise/dal/model.py 2014-04-24 16:10:35 UTC (rev 13367)
</span><span class="lines">@@ -59,11 +59,12 @@
</span><span class="cx">
</span><span class="cx"> def __eq__(self, other):
</span><span class="cx"> """
</span><del>- Compare equal to other L{SQLTypes} with matching name and length.
</del><ins>+ Compare equal to other L{SQLTypes} with matching name and length. The name is
+ normalized so we can compare schema from different types of DB implementations.
</ins><span class="cx"> """
</span><span class="cx"> if not isinstance(other, SQLType):
</span><span class="cx"> return NotImplemented
</span><del>- return (self.name, self.length) == (other.name, other.length)
</del><ins>+ return (self.normalizedName(), self.length) == (other.normalizedName(), other.length)
</ins><span class="cx">
</span><span class="cx">
</span><span class="cx"> def __ne__(self, other):
</span><span class="lines">@@ -87,7 +88,21 @@
</span><span class="cx"> return "<SQL Type: %r%s>" % (self.name, lendesc)
</span><span class="cx">
</span><span class="cx">
</span><ins>+ def normalizedName(self):
+ """
+ Map type names to standard names.
+ """
+ return {
+ "nchar": "char",
+ "varchar2": "varchar",
+ "nvarchar2": "varchar",
+ "clob": "text",
+ "nclob": "text",
+ "boolean": "integer",
+ }.get(self.name, self.name)
</ins><span class="cx">
</span><ins>+
+
</ins><span class="cx"> class Constraint(object):
</span><span class="cx"> """
</span><span class="cx"> A constraint on a set of columns.
</span><span class="lines">@@ -131,11 +146,13 @@
</span><span class="cx">
</span><span class="cx">
</span><span class="cx">
</span><del>-class ProcedureCall(object):
</del><ins>+class ProcedureCall(FancyEqMixin):
</ins><span class="cx"> """
</span><span class="cx"> An invocation of a stored procedure or built-in function.
</span><span class="cx"> """
</span><span class="cx">
</span><ins>+ compareAttributes = 'name args'.split()
+
</ins><span class="cx"> def __init__(self, name, args):
</span><span class="cx"> _checkstr(name)
</span><span class="cx"> self.name = name
</span><span class="lines">@@ -161,6 +178,19 @@
</span><span class="cx">
</span><span class="cx">
</span><span class="cx">
</span><ins>+def listIfNone(x):
+ return [] if x is None else x
+
+
+
+def stringIfNone(x, attr=None):
+ if attr:
+ return "" if x is None else getattr(x, attr)
+ else:
+ return "" if x is None else x
+
+
+
</ins><span class="cx"> class Column(FancyEqMixin, object):
</span><span class="cx"> """
</span><span class="cx"> A column from a table.
</span><span class="lines">@@ -215,14 +245,23 @@
</span><span class="cx">
</span><span class="cx"> results = []
</span><span class="cx">
</span><del>- # 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
</del><ins>+ if self.name != other.name:
+ results.append("Table: %s, column names %s and %s do not match" % (self.table.name, self.name, other.name,))
+ if self.type != other.type:
+ results.append("Table: %s, column name %s type mismatch" % (self.table.name, self.name,))
+ if self.default != other.default:
+ # Some DBs don't allow sequence as a default
+ if (
+ isinstance(self.default, Sequence) and other.default == NO_DEFAULT or
+ self.default == NO_DEFAULT and isinstance(other.default, Sequence)
+ ):
+ pass
+ else:
+ results.append("Table: %s, column name %s default mismatch" % (self.table.name, self.name,))
+ if stringIfNone(self.references, "name") != stringIfNone(other.references, "name"):
+ results.append("Table: %s, column name %s references mismatch" % (self.table.name, self.name,))
+ if self.deleteAction != other.deleteAction:
+ results.append("Table: %s, column name %s delete action mismatch" % (self.table.name, self.name,))
</ins><span class="cx"> return results
</span><span class="cx">
</span><span class="cx">
</span><span class="lines">@@ -333,7 +372,18 @@
</span><span class="cx"> for name in set(myColumns.keys()) & set(otherColumns.keys()):
</span><span class="cx"> results.extend(myColumns[name].compare(otherColumns[name]))
</span><span class="cx">
</span><del>- # TODO: figure out how to compare schemaRows
</del><ins>+ if not all([len(a.compare(b)) == 0 for a, b in zip(
+ listIfNone(self.primaryKey),
+ listIfNone(other.primaryKey),
+ )]):
+ results.append("Table: %s, mismatched primary key" % (self.name,))
+
+ for myRow, otherRow in zip(self.schemaRows, other.schemaRows):
+ myRows = dict([(column.name, value) for column, value in myRow.items()])
+ otherRows = dict([(column.name, value) for column, value in otherRow.items()])
+ if myRows != otherRows:
+ results.append("Table: %s, mismatched schema rows: %s" % (self.name, myRows))
+
</ins><span class="cx"> return results
</span><span class="cx">
</span><span class="cx">
</span><span class="lines">@@ -392,7 +442,7 @@
</span><span class="cx"> self.constraints.append(Check(protoExpression, name))
</span><span class="cx">
</span><span class="cx">
</span><del>- def insertSchemaRow(self, values):
</del><ins>+ def insertSchemaRow(self, values, columns=None):
</ins><span class="cx"> """
</span><span class="cx"> A statically-defined row was inserted as part of the schema itself.
</span><span class="cx"> This is used for tables that want to track static enumerations, for
</span><span class="lines">@@ -405,9 +455,12 @@
</span><span class="cx">
</span><span class="cx"> @param values: a C{list} of data items, one for each column in this
</span><span class="cx"> table's current list of L{Column}s.
</span><ins>+ @param columns: a C{list} of column names to insert into. If C{None}
+ then use all table columns.
</ins><span class="cx"> """
</span><span class="cx"> row = {}
</span><del>- for column, value in zip(self.columns, values):
</del><ins>+ columns = self.columns if columns is None else [self.columnNamed(name) for name in columns]
+ for column, value in zip(columns, values):
</ins><span class="cx"> row[column] = value
</span><span class="cx"> self.schemaRows.append(row)
</span><span class="cx">
</span></span></pre></div>
<a id="twexttrunktwextenterprisedalparseschemapy"></a>
<div class="modfile"><h4>Modified: twext/trunk/twext/enterprise/dal/parseschema.py (13366 => 13367)</h4>
<pre class="diff"><span>
<span class="info">--- twext/trunk/twext/enterprise/dal/parseschema.py 2014-04-24 04:27:28 UTC (rev 13366)
+++ twext/trunk/twext/enterprise/dal/parseschema.py 2014-04-24 16:10:35 UTC (rev 13367)
</span><span class="lines">@@ -209,17 +209,23 @@
</span><span class="cx"> idx.addColumn(idx.table.columnNamed(columnName))
</span><span class="cx">
</span><span class="cx"> elif createType == u"FUNCTION":
</span><del>- FunctionModel(
- schema,
- stmt.token_next(2, True).value.encode("utf-8")
- )
</del><ins>+ parseFunction(schema, stmt)
</ins><span class="cx">
</span><span class="cx"> elif stmt.get_type() == "INSERT":
</span><span class="cx"> insertTokens = iterSignificant(stmt)
</span><span class="cx"> expect(insertTokens, ttype=Keyword.DML, value="INSERT")
</span><span class="cx"> expect(insertTokens, ttype=Keyword, value="INTO")
</span><span class="cx">
</span><del>- tableName = expect(insertTokens, cls=Identifier).get_name()
</del><ins>+ token = insertTokens.next()
+
+ if isinstance(token, Function):
+ [tableName, columnArgs] = iterSignificant(token)
+ tableName = tableName.get_name()
+ columns = namesInParens(columnArgs)
+ else:
+ tableName = token.get_name()
+ columns = None
+
</ins><span class="cx"> expect(insertTokens, ttype=Keyword, value="VALUES")
</span><span class="cx">
</span><span class="cx"> values = expect(insertTokens, cls=Parenthesis)
</span><span class="lines">@@ -238,16 +244,13 @@
</span><span class="cx"> [ident.ttype](ident.value)
</span><span class="cx"> )
</span><span class="cx">
</span><del>- schema.tableNamed(tableName).insertSchemaRow(rowData)
</del><ins>+ schema.tableNamed(tableName).insertSchemaRow(rowData, columns=columns)
</ins><span class="cx">
</span><span class="cx"> elif stmt.get_type() == "CREATE OR REPLACE":
</span><span class="cx"> createType = stmt.token_next(1, True).value.upper()
</span><span class="cx">
</span><span class="cx"> if createType == u"FUNCTION":
</span><del>- FunctionModel(
- schema,
- stmt.token_next(2, True).token_first(True).token_first(True).value.encode("utf-8")
- )
</del><ins>+ parseFunction(schema, stmt)
</ins><span class="cx">
</span><span class="cx"> else:
</span><span class="cx"> print("unknown type:", stmt.get_type())
</span><span class="lines">@@ -256,6 +259,25 @@
</span><span class="cx">
</span><span class="cx">
</span><span class="cx">
</span><ins>+def parseFunction(schema, stmt):
+ """
+ A FUNCTION may or may not have an argument list, so we need to account for
+ both possibilities.
+ """
+ fn_name = stmt.token_next(2, True)
+ if isinstance(fn_name, Function):
+ [fn_name, _ignore_args] = iterSignificant(fn_name)
+ fn_name = fn_name.get_name()
+ else:
+ fn_name = fn_name.get_name()
+
+ FunctionModel(
+ schema,
+ fn_name.encode("utf-8"),
+ )
+
+
+
</ins><span class="cx"> class _ColumnParser(object):
</span><span class="cx"> """
</span><span class="cx"> Stateful parser for the things between commas.
</span><span class="lines">@@ -326,22 +348,6 @@
</span><span class="cx"> return self.parseConstraint(maybeIdent)
</span><span class="cx">
</span><span class="cx">
</span><del>- def namesInParens(self, parens):
- parens = iterSignificant(parens)
- expect(parens, ttype=Punctuation, value="(")
- idorids = parens.next()
-
- if isinstance(idorids, Identifier):
- idnames = [idorids.get_name()]
- elif isinstance(idorids, IdentifierList):
- idnames = [x.get_name() for x in idorids.get_identifiers()]
- else:
- raise ViolatedExpectation("identifier or list", repr(idorids))
-
- expect(parens, ttype=Punctuation, value=")")
- return idnames
-
-
</del><span class="cx"> def readExpression(self, parens):
</span><span class="cx"> """
</span><span class="cx"> Read a given expression from a Parenthesis object. (This is currently
</span><span class="lines">@@ -369,7 +375,7 @@
</span><span class="cx"> funcName = expect(parens, ttype=Keyword).value.encode("ascii")
</span><span class="cx"> rhs = FunctionSyntax(funcName)(*[
</span><span class="cx"> ColumnSyntax(self.table.columnNamed(x)) for x in
</span><del>- self.namesInParens(expect(parens, cls=Parenthesis))
</del><ins>+ namesInParens(expect(parens, cls=Parenthesis))
</ins><span class="cx"> ])
</span><span class="cx"> result = CompoundComparison(lhs, op, rhs)
</span><span class="cx">
</span><span class="lines">@@ -404,10 +410,10 @@
</span><span class="cx">
</span><span class="cx"> if constraintType.match(Keyword, "PRIMARY"):
</span><span class="cx"> expect(self, ttype=Keyword, value="KEY")
</span><del>- names = self.namesInParens(expect(self, cls=Parenthesis))
</del><ins>+ names = namesInParens(expect(self, cls=Parenthesis))
</ins><span class="cx"> self.table.primaryKey = [self.table.columnNamed(n) for n in names]
</span><span class="cx"> elif constraintType.match(Keyword, "UNIQUE"):
</span><del>- names = self.namesInParens(expect(self, cls=Parenthesis))
</del><ins>+ names = namesInParens(expect(self, cls=Parenthesis))
</ins><span class="cx"> self.table.tableConstraint(Constraint.UNIQUE, names)
</span><span class="cx"> elif constraintType.match(Keyword, "CHECK"):
</span><span class="cx"> self.table.checkConstraint(self.readExpression(self.next()), ident)
</span><span class="lines">@@ -519,7 +525,7 @@
</span><span class="cx"> defaultValue.referringColumns.append(theColumn)
</span><span class="cx"> else:
</span><span class="cx"> defaultValue = ProcedureCall(
</span><del>- thingo.encode("utf-8"), parens
</del><ins>+ thingo.encode("utf-8"), namesInParens(parens),
</ins><span class="cx"> )
</span><span class="cx">
</span><span class="cx"> elif theDefault.ttype == Number.Integer:
</span><span class="lines">@@ -546,6 +552,17 @@
</span><span class="cx"> elif theDefault.ttype == String.Single:
</span><span class="cx"> defaultValue = _destringify(theDefault.value)
</span><span class="cx">
</span><ins>+ # Oracle format for current timestamp mapped to postgres variant
+ elif (
+ theDefault.ttype == Keyword and
+ theDefault.value.lower() == "current_timestamp"
+ ):
+ expect(self, ttype=Keyword, value="at")
+ expect(self, ttype=None, value="time")
+ expect(self, ttype=None, value="zone")
+ expect(self, ttype=String.Single, value="'UTC'")
+ defaultValue = ProcedureCall("timezone", [u"UTC", u"CURRENT_TIMESTAMP"])
+
</ins><span class="cx"> else:
</span><span class="cx"> raise RuntimeError(
</span><span class="cx"> "not sure what to do: default %r"
</span><span class="lines">@@ -635,11 +652,35 @@
</span><span class="cx"> return token.get_name()
</span><span class="cx"> elif token.ttype == Name:
</span><span class="cx"> return token.value
</span><ins>+ elif token.ttype == String.Single:
+ return _destringify(token.value)
+ elif token.ttype == Keyword:
+ return token.value
</ins><span class="cx"> else:
</span><span class="cx"> raise ViolatedExpectation("identifier or name", repr(token))
</span><span class="cx">
</span><span class="cx">
</span><span class="cx">
</span><ins>+def namesInParens(parens):
+ parens = iterSignificant(parens)
+ expect(parens, ttype=Punctuation, value="(")
+ idorids = parens.next()
+
+ if isinstance(idorids, Identifier):
+ idnames = [idorids.get_name()]
+ elif isinstance(idorids, IdentifierList):
+ idnames = [nameOrIdentifier(x) for x in idorids.get_identifiers()]
+ elif idorids.ttype == String.Single:
+ idnames = [nameOrIdentifier(idorids)]
+ else:
+ expectSingle(idorids, ttype=Punctuation, value=")")
+ return []
+
+ expect(parens, ttype=Punctuation, value=")")
+ return idnames
+
+
+
</ins><span class="cx"> def expectSingle(nextval, ttype=None, value=None, cls=None):
</span><span class="cx"> """
</span><span class="cx"> Expect some properties from retrieved value.
</span></span></pre></div>
<a id="twexttrunktwextenterprisedaltesttest_parseschemapy"></a>
<div class="modfile"><h4>Modified: twext/trunk/twext/enterprise/dal/test/test_parseschema.py (13366 => 13367)</h4>
<pre class="diff"><span>
<span class="info">--- twext/trunk/twext/enterprise/dal/test/test_parseschema.py 2014-04-24 04:27:28 UTC (rev 13366)
+++ twext/trunk/twext/enterprise/dal/test/test_parseschema.py 2014-04-24 16:10:35 UTC (rev 13367)
</span><span class="lines">@@ -19,7 +19,7 @@
</span><span class="cx"> and L{twext.enterprise.dal.parseschema}.
</span><span class="cx"> """
</span><span class="cx">
</span><del>-from twext.enterprise.dal.model import Schema
</del><ins>+from twext.enterprise.dal.model import Schema, ProcedureCall
</ins><span class="cx"> from twext.enterprise.dal.syntax import CompoundComparison, ColumnSyntax
</span><span class="cx">
</span><span class="cx"> try:
</span><span class="lines">@@ -176,6 +176,29 @@
</span><span class="cx"> self.assertEquals(table.columnNamed("f").default, None)
</span><span class="cx">
</span><span class="cx">
</span><ins>+ def test_defaultFunctionColumns(self):
+ """
+ Parsing a 'default' column with a function call in it will return
+ that function as the 'default' attribute of the Column object.
+ """
+ s = self.schemaFromString(
+ """
+ create table a (
+ b1 integer default tz(),
+ b2 integer default tz('UTC'),
+ b3 integer default tz('UTC', 'GMT'),
+ b4 integer default timezone('UTC', CURRENT_TIMESTAMP),
+ b5 integer default CURRENT_TIMESTAMP at time zone 'UTC'
+ );
+ """)
+ table = s.tableNamed("a")
+ self.assertEquals(table.columnNamed("b1").default, ProcedureCall("tz", []))
+ self.assertEquals(table.columnNamed("b2").default, ProcedureCall("tz", ["UTC"]))
+ self.assertEquals(table.columnNamed("b3").default, ProcedureCall("tz", ["UTC", "GMT"]))
+ self.assertEquals(table.columnNamed("b4").default, ProcedureCall("timezone", ["UTC", "CURRENT_TIMESTAMP"]))
+ self.assertEquals(table.columnNamed("b5").default, ProcedureCall("timezone", ["UTC", "CURRENT_TIMESTAMP"]))
+
+
</ins><span class="cx"> def test_needsValue(self):
</span><span class="cx"> """
</span><span class="cx"> Columns with defaults, or with a 'not null' constraint don't need a
</span><span class="lines">@@ -333,7 +356,7 @@
</span><span class="cx"> """
</span><span class="cx"> create table a1 (b1 integer primary key);
</span><span class="cx"> create table c2 (d2 integer references a1 on delete cascade);
</span><del>- create table e3 (f3 integer references a1 on delete set null);
</del><ins>+ create table ee3 (f3 integer references a1 on delete set null);
</ins><span class="cx"> create table g4 (h4 integer references a1 on delete set default);
</span><span class="cx"> """
</span><span class="cx"> )
</span><span class="lines">@@ -346,7 +369,7 @@
</span><span class="cx"> "cascade"
</span><span class="cx"> )
</span><span class="cx"> self.assertEquals(
</span><del>- s.tableNamed("e3").columnNamed("f3").deleteAction,
</del><ins>+ s.tableNamed("ee3").columnNamed("f3").deleteAction,
</ins><span class="cx"> "set null"
</span><span class="cx"> )
</span><span class="cx"> self.assertEquals(
</span><span class="lines">@@ -388,7 +411,7 @@
</span><span class="cx"> """
</span><span class="cx"> create table q (b integer); -- noise
</span><span class="cx"> create table a (b integer primary key, c integer);
</span><del>- create table z (c integer, unique(c) );
</del><ins>+ create table z (c integer, unique (c) );
</ins><span class="cx">
</span><span class="cx"> create unique index idx_a_c on a(c);
</span><span class="cx"> create index idx_a_b_c on a (c, b);
</span><span class="lines">@@ -417,13 +440,47 @@
</span><span class="cx"> RETURN i + 1;
</span><span class="cx"> END;
</span><span class="cx"> $$ LANGUAGE plpgsql;
</span><ins>+CREATE FUNCTION autoincrement RETURNS integer AS $$
+BEGIN
+ RETURN 1;
+END;
+$$ LANGUAGE plpgsql;
</ins><span class="cx"> CREATE OR REPLACE FUNCTION decrement(i integer) RETURNS integer AS $$
</span><span class="cx"> BEGIN
</span><span class="cx"> RETURN i - 1;
</span><span class="cx"> END;
</span><span class="cx"> $$ LANGUAGE plpgsql;
</span><ins>+CREATE OR REPLACE FUNCTION autodecrement (i integer) RETURNS integer AS $$
+BEGIN
+ RETURN i - 1;
+END;
+$$ LANGUAGE plpgsql;
</ins><span class="cx"> """
</span><span class="cx"> )
</span><span class="cx"> self.assertTrue(s.functionNamed("increment") is not None)
</span><span class="cx"> self.assertTrue(s.functionNamed("decrement") is not None)
</span><span class="cx"> self.assertRaises(KeyError, s.functionNamed, "merge")
</span><ins>+
+
+ def test_insert(self):
+ """
+ An 'insert' statement will add an L{schemaRows} to an L{Table}.
+ """
+ s = self.schemaFromString(
+ """
+ create table alpha (beta integer, gamma integer not null);
+
+ insert into alpha values (1, 2);
+ insert into alpha (gamma, beta) values (3, 4);
+ """
+ )
+ self.assertTrue(s.tableNamed("alpha") is not None)
+ self.assertEqual(len(s.tableNamed("alpha").schemaRows), 2)
+ rows = [[(column.name, value) for column, value in sorted(row.items(), key=lambda x:x[0])] for row in s.tableNamed("alpha").schemaRows]
+ self.assertEqual(
+ rows,
+ [
+ [("beta", 1), ("gamma", 2)],
+ [("beta", 4), ("gamma", 3)],
+ ]
+ )
</ins></span></pre>
</div>
</div>
</body>
</html>