thank you for your time and patience. Again the hints were very useful.
The dialect I am implementing is syntactically very similar to Oracle (this
was done on purpose) but the semantics are quite different. For example,
schema changing operations are transactional. Thus, a rollback will also
rollback a table create.
Am Dienstag, 29. Januar 2013 00:09:13 UTC+1 schrieb Michael Bayer:
>
>
> 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.
>
My has_table() function does indeed has a problem. EXASol does not have a
default schema, but not selecting a schema can cause unwanted results. Thus
I have decided to make 'SYS' the default schema for the dialect and I do
expect that every user of the dialect passes a non None schema. The test
cases is not consistently passing the schema name.
Again, recommendations on what is expected of a dialect would be useful. I
would prefer to always have a schema name passed. Otherwise schema
reflection functions like has_table() return unwanted results (as tables
with the same name can exist in two different schemas). Is that a valid
pre-condition for a dialect? Is the test suite expected to pass the schema
name in all test cases?
>
> 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.
>
I'll check. As the dialect is very similar to Oracle it might require the
same qouting, but I am not sure.
>
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
