[Modeling-users] quoting entity and attribute names

[John Lenton]

Sender: John Lenton <***@martina.fsl.org.ar>

Hi all.

In a project I'm currently working on there is a class that we'd like
to call "User", but we can't call it that and use Modeling in its
current state: it's not quoting the names of entities (nor
attributes), and user is a reserved word.

I looked into it, and I actually got it working, but I then saw that
in SQLExpression there's a method `externalNameQuoteCharacter'; is the
purpose of this method to be used (something like) this:

[ in _addTableJoinsForAlias ]

quoteChar = self.externalNameQuoteCharacter()
str += quoteChar + self._internals.entityExternalNameForAlias(alias) + quoteChar + ' ' + alias

?

What I did was actually add a static method called quotedExternalName,
thus:

def quotedExternalName(name):
return name

which I then overrode in PostgresqlSQLExpression,

def quotedExternalName(name):
return '"%s"' % (name,)

and in MySQLSQLExpression,

def quotedExternalName(name):
return '`%s`' % (name,)

(no idea how sqlite nor oracle do this)

this and the appropriate modifications where the SQL is computed got
me to where I could use the objects in Modeling once the database was
created (I didn't run extensive tests, however); on looking into what
it took to make mdl_generate_DB_schema quote its chars I came across
that method that I'd overlooked before, which gave me pause, because
obviously Sébastien has already thought about this, so he probably has
a preferred way of doing it.

So... rounding up what shouldn't've become such a long mail:
Sébastien, how had you thought you'd do this?

[Sebastien Bigaret]

That's it, you got it right: the intention was to use
externalNameQuoteCharacter() into sqlStringForSchemaObjectName(), the
latter being the method responsible for quoting (or not).

I think we can make externalNameQuoteCharacter a class variable
(possibly the empty string), and then sqlStringForSchemaObjectName(name)
return the "quoted" name if variable is set, or otherwise the name,
intact.

Now of course sqlStringForSchemaObjectName() should be called wherever
tables', columns' names are referenced...

If by now you only need to quote tables' names, I'd suggest you quickly
patch the assembleDelete|Select|UpdateStatement...().

Please note this is a quick answer that needs some more thinking, at
least for me ;), to determine where and when the quoting should be
applied (without too much overhead)

-- Sébastien.

Post by John Lenton
Hi all.
In a project I'm currently working on there is a class that we'd like
to call "User", but we can't call it that and use Modeling in its
current state: it's not quoting the names of entities (nor
attributes), and user is a reserved word.
I looked into it, and I actually got it working, but I then saw that
in SQLExpression there's a method `externalNameQuoteCharacter'; is the
[ in _addTableJoinsForAlias ]
quoteChar = self.externalNameQuoteCharacter()
str += quoteChar + self._internals.entityExternalNameForAlias(alias) + quoteChar + ' ' + alias
?
What I did was actually add a static method called quotedExternalName,
return name
which I then overrode in PostgresqlSQLExpression,
return '"%s"' % (name,)
and in MySQLSQLExpression,
return '`%s`' % (name,)
(no idea how sqlite nor oracle do this)
this and the appropriate modifications where the SQL is computed got
me to where I could use the objects in Modeling once the database was
created (I didn't run extensive tests, however); on looking into what
it took to make mdl_generate_DB_schema quote its chars I came across
that method that I'd overlooked before, which gave me pause, because
obviously Sébastien has already thought about this, so he probably has
a preferred way of doing it.
Sébastien, how had you thought you'd do this?
--
Ir contra la corriente, casi nunca es conveniente.

[Sebastien Bigaret]

Post by John Lenton
In a project I'm currently working on there is a class that we'd like
to call "User", but we can't call it that and use Modeling in its
current state: it's not quoting the names of entities (nor
attributes), and user is a reserved word.
I looked into it, and I actually got it working, but I then saw that
in SQLExpression there's a method `externalNameQuoteCharacter'; is the
[ in _addTableJoinsForAlias ]
quoteChar = self.externalNameQuoteCharacter()
str += quoteChar + self._internals.entityExternalNameForAlias(alias) + quoteChar + ' ' + alias
?

And to answer this very precisely, I do not feel really comfortable with
quoting external names when unneeded; the quoting should IMHO be done
only when a whole SQL statement is returned, not during intermediate
steps. This makes assembleDelete|Select|UpdateStatement() methods good
candidates, doesn't it?

-- Sébastien.

[John Lenton]

Post by Sebastien Bigaret

Post by John Lenton
In a project I'm currently working on there is a class that we'd like
to call "User", but we can't call it that and use Modeling in its
current state: it's not quoting the names of entities (nor
attributes), and user is a reserved word.
I looked into it, and I actually got it working, but I then saw that
in SQLExpression there's a method `externalNameQuoteCharacter'; is the
[ in _addTableJoinsForAlias ]
quoteChar = self.externalNameQuoteCharacter()
str += quoteChar + self._internals.entityExternalNameForAlias(alias) + quoteChar + ' ' + alias
?

And to answer this very precisely, I do not feel really comfortable with
quoting external names when unneeded; the quoting should IMHO be done
only when a whole SQL statement is returned, not during intermediate
steps. This makes assembleDelete|Select|UpdateStatement() methods good
candidates, doesn't it?

agreed on the first point, but not on the second: assembleFoo takes a
'tableList' parameter that is already a (list of) table name(s). For
the singular case this wouldn't be a problem, but in the case of
select at least it very much is (IMVHO). I think the place to do this
would be, for table names, tableListWithRootEntity (possibly via a
'quoted' parameter?); however, this implies that
_addTableJoinsForAlias must behave similarly. For attribute names it
seems the functionality should be in sqlStringForAttribute, but that
might be too generic.

comments? I'm moving ahead with this implementation, but I understand
it might have to be redone in some other way (time presses *now* :) )

[Sebastien Bigaret]

Hi John,

Since it is needed *now* then go for it :)

Put differently, this means: I'm sorry, but I won't have the time to look at
this tonite, so just make it the way you feel it should be done, and then I'll
argue if I think it needs to be discussed !-)

-- Sébastien.

Post by John Lenton

Post by Sebastien Bigaret

Post by John Lenton
In a project I'm currently working on there is a class that we'd like
to call "User", but we can't call it that and use Modeling in its
current state: it's not quoting the names of entities (nor
attributes), and user is a reserved word.
I looked into it, and I actually got it working, but I then saw that
in SQLExpression there's a method `externalNameQuoteCharacter'; is the
[ in _addTableJoinsForAlias ]
quoteChar = self.externalNameQuoteCharacter()
str += quoteChar + self._internals.entityExternalNameForAlias(alias)

+ quoteChar + ' ' + alias

Post by Sebastien Bigaret

Post by John Lenton
?

And to answer this very precisely, I do not feel really comfortable with
quoting external names when unneeded; the quoting should IMHO be done
only when a whole SQL statement is returned, not during intermediate
steps. This makes assembleDelete|Select|UpdateStatement() methods good
candidates, doesn't it?

agreed on the first point, but not on the second: assembleFoo takes a
'tableList' parameter that is already a (list of) table name(s). For
the singular case this wouldn't be a problem, but in the case of
select at least it very much is (IMVHO). I think the place to do this
would be, for table names, tableListWithRootEntity (possibly via a
'quoted' parameter?); however, this implies that
_addTableJoinsForAlias must behave similarly. For attribute names it
seems the functionality should be in sqlStringForAttribute, but that
might be too generic.
comments? I'm moving ahead with this implementation, but I understand
it might have to be redone in some other way (time presses *now* :) )
--
A vacuum is a hell of a lot better than some of the stuff that nature
replaces it with.
-- Tennessee Williams