patch for quoted entities, attributes
John Lenton <[email protected]> Wed, 7 Jul 2004 07:36:00 -0300
| Newsgroups | gmane.comp.python.modeling |
|---|---|
| Message-ID | <[email protected]> |
Attached is a patch that quotes entites and attributes in the sql. It
has several problems, still:
- I haven't fixed the tests (and I'm not certain it's even possible
without rewriting them), so if you use postgres suddenly a whole
slew of tests fail.
- Schema generation *almost* works: I'm not quoting the drop
statements for the primary key indexes.
- I changed an unrelated bit: I set useAllCaps to default to 0 in
Entity. This makes the previous bug hide under the carpet, plus
you don't have to quote things when using 'psql' (the latter is the
reson for this change).
- No documentation.
- Only done for postgres (but the per-adapter changes are small)
- Some of the lines are way too long, especially when cutting a line
would mean creating an intermediate object---in those cases I
defer to Sébastien to decide if it's worth it---or when the original
was pretty long to start with :)
however, it's working for me (so far!), and I'm using it for
development.
--
John Lenton ([email protected]) -- Random fortune:
bash: fortune: command not found
quot.patch
(text/x-patch, 9.1 KB)
? Modeling/ModelMasons/Python_bricks/base_module.py
? Modeling/ModelMasons/Python_bricks/base_module.py_bak
? Modeling/ModelMasons/Python_bricks/init.py
? Modeling/ModelMasons/Python_bricks/init.py_bak
? Modeling/ModelMasons/Python_bricks/init_base.py
? Modeling/ModelMasons/Python_bricks/init_base.py_bak
? Modeling/ModelMasons/Python_bricks/model.py
? Modeling/ModelMasons/Python_bricks/model.py_bak
? Modeling/ModelMasons/Python_bricks/module_base.py
? Modeling/ModelMasons/Python_bricks/module_base.py_bak
? Modeling/ModelMasons/Python_bricks/module_compact.py
? Modeling/ModelMasons/Python_bricks/module_compact.py_bak
? Modeling/ModelMasons/Python_bricks/setup_tmpl.py
? Modeling/ModelMasons/Python_bricks/setup_tmpl.py_bak
Index: Modeling/Entity.py
===================================================================
RCS file: /cvsroot/modeling/ProjectModeling/Modeling/Entity.py,v
retrieving revision 1.23
diff -u -r1.23 Entity.py
--- Modeling/Entity.py 7 Mar 2004 17:36:39 -0000 1.23
+++ Modeling/Entity.py 7 Jul 2004 10:31:26 -0000
@@ -59,7 +59,7 @@
'subEntities()'
}
-def externalNameForInternalName(aName, separatorString='_', useAllCaps=1):
+def externalNameForInternalName(aName, separatorString='_', useAllCaps=0):
"""
Turns an entity name into a valid name for database schema.
Index: Modeling/SQLExpression.py
===================================================================
RCS file: /cvsroot/modeling/ProjectModeling/Modeling/SQLExpression.py,v
retrieving revision 1.28
diff -u -r1.28 SQLExpression.py
--- Modeling/SQLExpression.py 16 Feb 2004 20:01:06 -0000 1.28
+++ Modeling/SQLExpression.py 7 Jul 2004 10:31:30 -0000
@@ -322,7 +322,7 @@
SchemaGeneration.createTableStatementsForEntityGroup()
"""
if attribute.columnName() is None: return
- createClause='\n '+attribute.columnName()+' '
+ createClause='\n '+self.sqlStringForSchemaObjectName(attribute.columnName())+' '
createClause+=self.columnTypeStringForAttribute(attribute)+' '
createClause+=self.allowsNullClauseForConstraint(attribute.allowsNull())
self.appendItemToListString(createClause, self._listString)
@@ -371,7 +371,7 @@
See also: prepareUpdateExpressionWithRow(), listString()
"""
- str="%s = %s"%(attribute.columnName(),
+ str="%s = %s"%(self.sqlStringForSchemaObjectName(attribute.columnName()),
self.sqlStringForValue(value, attribute.name()))
self.appendItemToListString(str, self._listString)
@@ -425,7 +425,7 @@
def assembleDeleteStatementWithQualifier(self, aQualifier, tableList, whereClause):
"""
- Generates the SQL INSERT statement and assigns it to self's statement()
+ Generates the SQL DELETE statement and assigns it to self's statement()
The generated statement has the following format::
DELETE FROM <tableList> WHERE <whereClause>
@@ -592,11 +592,8 @@
def entityExternalNamesByAliases(self): ###############################
return self._internals.entityExternalNamesByAliases()
- def externalNameQuoteCharacter(self):
- """
- Unimplemented
- """
- __unimplemented__()
+ externalNameQuoteCharacter = ''
+ "JRL: document this"
def formatSQLString(self, sqlString, format):
"""
@@ -958,10 +955,11 @@
See also: useAliases(), aliasesByRelationshipPath(), Attribute.columnName()
"""
+ name = self.sqlStringForSchemaObjectName(attribute.columnName())
if self.useAliases():
- return 't0.'+attribute.columnName()
+ return 't0.'+name
else:
- return attribute.columnName()
+ return name
def sqlStringForAttributeNamed(self, name):
"""
@@ -1034,7 +1032,8 @@
path=self._entity.objectsPathForKeyPath(path)
relPath=self._internals.addRelPathForEntity(path, self._entity)
- return self._internals.aliasForRelPath(relPath)+'.'+path[-1].columnName()
+ return self._internals.aliasForRelPath(relPath)+'.'+ \
+ self.sqlStringForSchemaObjectName(path[-1].columnName())
def sqlStringForCaseInsensitiveLike(self, keyString, valueString):
"""
@@ -1323,9 +1322,9 @@
def sqlStringForSchemaObjectName(self, name):
"""
- Unimplemented
+ JRL: document this
"""
- __unimplemented__()
+ return self.externalNameQuoteCharacter+name+self.externalNameQuoteCharacter
def sqlStringForSelector(self, selector, value):
"""
@@ -1459,7 +1458,7 @@
res=res[:-2] # remove trailing comma
else: # does not use table aliases
- res=anEntity.externalName()
+ res=self.sqlStringForSchemaObjectName(anEntity.externalName())
return res
def _addTableJoinsForAlias(self, alias, str):
@@ -1485,7 +1484,7 @@
aliases=map(lambda rp, self=self: self._internals.aliasForRelPath(rp),
relPaths)
- str+=self._internals.entityExternalNameForAlias(alias)+' '+alias
+ str+=self.sqlStringForSchemaObjectName(self._internals.entityExternalNameForAlias(alias))+' '+alias
processedAliases_total=[]
for boundAlias in aliases:
@@ -1503,7 +1502,10 @@
dstKeys=self._internals.destinationKeysForRelPath(currentRelPath)
joinClause=''
for idx in range(len(srcKeys)):
- joinClause+='%s.%s=%s.%s AND '%(alias, srcKeys[idx], boundAlias, dstKeys[idx])
+ joinClause+='%s.%s=%s.%s AND '%(alias,
+ self.sqlStringForSchemaObjectName(srcKeys[idx]),
+ boundAlias,
+ self.sqlStringForSchemaObjectName(dstKeys[idx]))
joinClause=joinClause[:-5]
if self.SQL92_join:
Index: Modeling/SchemaGeneration.py
===================================================================
RCS file: /cvsroot/modeling/ProjectModeling/Modeling/SchemaGeneration.py,v
retrieving revision 1.10
diff -u -r1.10 SchemaGeneration.py
--- Modeling/SchemaGeneration.py 14 Feb 2004 18:27:04 -0000 1.10
+++ Modeling/SchemaGeneration.py 7 Jul 2004 10:31:34 -0000
@@ -178,7 +178,7 @@
attributes[attribute.columnName()]=attribute
for attribute in attributes.values():
sqlExpression.addCreateClauseForAttribute(attribute)
- statement='CREATE TABLE %s (%s)'%(firstEntity.externalName(),
+ statement='CREATE TABLE %s (%s)'%(sqlExpression.sqlStringForSchemaObjectName(firstEntity.externalName()),
sqlExpression.listString())
sqlExpression.setStatement(statement)
return (sqlExpression,)
@@ -332,7 +332,7 @@
#DROP TABLE <TABLE_NAME>
firstEntity=entityGroup[0]
sqlExpr=self._adaptor.expressionClass()(firstEntity)
- sqlExpr.setStatement('DROP TABLE %s'%firstEntity.externalName())
+ sqlExpr.setStatement('DROP TABLE %s'%sqlExpr.sqlStringForSchemaObjectName(firstEntity.externalName()))
return (sqlExpr,)
@@ -418,14 +418,15 @@
return ()
sqlExpression=self._adaptor.expressionClass()(srcEntity)
+ quoter=sqlExpression.sqlStringForSchemaObjectName
st='ALTER TABLE %(table)s ADD CONSTRAINT %(relName)s FOREIGN KEY '\
'(%(FKs)s) REFERENCES %(dst_table)s(%(PKs)s) INITIALLY DEFERRED'
from string import join
- vars={'table': srcEntity.externalName(),
- 'relName': relationship.name(),
- 'FKs': join([attr.columnName() for attr in relationship.sourceAttributes()], ','),
- 'dst_table': dstEntity.externalName(),
- 'PKs': join([attr.columnName() for attr in relationship.destinationAttributes()], ',')
+ vars={'table': quoter(srcEntity.externalName()),
+ 'relName': quoter(relationship.name()),
+ 'FKs': join([quoter(attr.columnName()) for attr in relationship.sourceAttributes()], ','),
+ 'dst_table': quoter(dstEntity.externalName()),
+ 'PKs': join([quoter(attr.columnName()) for attr in relationship.destinationAttributes()], ',')
}
sqlExpression.setStatement(st%vars)
return (sqlExpression,)
@@ -450,8 +451,9 @@
pks=tuple(firstEntity.primaryKeyAttributes())
if not pks:
return ()
- pks=map(lambda o: o.columnName(), pks)
- st='ALTER TABLE %s ADD PRIMARY KEY ('%firstEntity.externalName()
+ quoter=sqlExpression.sqlStringForSchemaObjectName
+ pks=map(lambda o: quoter(o.columnName()), pks)
+ st='ALTER TABLE %s ADD PRIMARY KEY ('%quoter(firstEntity.externalName())
for pk in pks:
st+=pk+', '
st=st[:-2]+')'
Index: Modeling/DatabaseAdaptors/PostgresqlAdaptorLayer/PostgresqlSQLExpression.py
===================================================================
RCS file: /cvsroot/modeling/ProjectModeling/Modeling/DatabaseAdaptors/PostgresqlAdaptorLayer/PostgresqlSQLExpression.py,v
retrieving revision 1.8
diff -u -r1.8 PostgresqlSQLExpression.py
--- Modeling/DatabaseAdaptors/PostgresqlAdaptorLayer/PostgresqlSQLExpression.py 31 Aug 2003 13:58:22 -0000 1.8
+++ Modeling/DatabaseAdaptors/PostgresqlAdaptorLayer/PostgresqlSQLExpression.py 7 Jul 2004 10:31:36 -0000
@@ -110,6 +110,8 @@
if self._statement[:len(select_distinct)]==select_distinct:
self._statement=self._statement+' AS DISTINCT_ROWS'
+ externalNameQuoteCharacter = '"'
+
def sqlEscapeChar(self):
"""
Postgresql interprets strings, hence the escape char is a double