On Jan 28, 2013, at 4:54 PM, jank wrote:
> On implementing a new dialect I am running into lots of issues. All caught by
> test cases part of the standard test suite - so thanks for this!
>
> I have an issue with a reserved word being used as column name: data.
>
> The test case HasTableTest.test_has_table creates a table using:
>
> Table('test_table', metadata,
> Column('id', Integer, primary_key=True),
> Column('data', String(50))
> )
>
> A few things are suspicious. Running the resulting create statement on the DB
> console I get an error. However, I do not see any error on test execution,
> but only the message that the table does not exist:
>
> Error emptying table test_table: ProgrammingError("(ProgrammingError)
> ('42000', '[42000] [EXASOL][EXASolution driver]object TEST_TABLE not found
> [line 1, column 13] (-1) (SQLExecDirectW)')",)
> ERROR
>
> I would have expected an error on CREATE TABLE.
running the tests with --log-debug=sqlalchemy.engine will help to see all the
SQL being emitted. The "not found" seems like your has_table() function might
not be working.
> I also added 'data' to the list of reserved_words
OK, make sure you use the latest default as of yesterday as I just had to
change that test so that it quotes the "data" term, apparently it is reserved
on DB2 as well.
> and added a _bindparam_requires_quotes method similar to the Oracle dialect.
that's a very unusual setting and unless exaDB is extremely similar to Oracle
it's unlikely you need it. what this means is that if you have a statement
like this:
INSERT INTO sometable (somecol) VALUES (:data)
above, we've named a bound parameter with the name "data". Because it is
syntaxed like a bound parameter, it's very unusual that a database client API
would choke on the fact that "data" is a reserved word. But Oracle OCI does
choke on this, so it wants this:
INSERT INTO sometable (somecol) VALUES (":data")
the logic to get the quotes in there on a bound parameter, while still keeping
the name of the parameter as "data" without the quotes, is very awkward but
that's what _bindparam_requires_quotes does. This is not usually what's needed
for quoting of reserved words.
Usually, quoting of reserved words means that a statement like this:
INSERT into sometable (data) VALUES (:param)
should instead be rendered like this, because "data" is reserved:
INSERT into sometable ("data") VALUES (:param)
All dialects will do this automatically assuming "data" is in the reserved
words registered with the IdentifierPreparer.
> But I am not sure if, how and when this set is being used. When running the
> test suite I do not see any prints I have added to check if my code is being
> executed (but adding syntax errors cause the expected error). So far I have
> not understood the test suite magic.
>
> Any help on understanding the test suite magic or how reserved words are
> being used is greatly appreciated.
my guess is based on the error message:
> object TEST_TABLE not found
the fact that the table name is being reported as UPPERCASE suggests that this
database may store and report on case insensitive names as UPPERCASE. In
SQLAlchemy, an all lower case name is considered to be case insensitive. So
when performing table inspection, an all lower case name on the SQLAlchemy side
must be "normalized" and "denormalized" as it is passed to the database for
schema inspection functions. The Oracle dialect includes this behavior, as
does the Firebird dialect as well as the DB2 dialect I've been working on. To
enable it looks like this (you can copy this from oracle/base.py):
class MyDialect(default.DefaultDialect):
# ...
requires_name_normalize = True
def normalize_name(self, name):
if name is None:
return None
# Py2K
if isinstance(name, str):
name = name.decode(self.encoding)
# end Py2K
if name.upper() == name and \
not self.identifier_preparer._requires_quotes(name.lower()):
return name.lower()
else:
return name
def denormalize_name(self, name):
if name is None:
return None
elif name.lower() == name and not
self.identifier_preparer._requires_quotes(name.lower()):
name = name.upper()
# Py2K
if not self.supports_unicode_binds:
name = name.encode(self.encoding)
else:
name = unicode(name)
# end Py2K
return name
you'll then want to call upon denormalize_name() as you query the databases
information schema for tables, schemas and columns - you can see this in
Oracle's has_table() method.
this is again just a hunch based on that UPPERCASE name in your error message.
Running with --log-debug=sqlalchemy.engine will show all queries being emitted
as well as results. I'd recommend running tests individually when that
setting is enabled.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
