Quick feedback - with your input I managed to get the test cases working. There was one path in the code where the denormalization was not applied, there was an issue with the default schema name, and there was an issue with code pages. As always an interesting bag of root causes. Again, thank you for your support.
Am Dienstag, 30. Mai 2017 09:22:26 UTC+2 schrieb jan.ka...@googlemail.com: > > Thank you so much for the detailed answer. Very much appreciated, this > should get me on the right track to fix this for the eXAsoL dialect. > > The ExaSolution database (this is the original product name, Exasol being > the company name - but no user cares and calls the DB Exasol) was > originally a drop-in replacement for Oracle DBs when extra performance on > OLTP is required. So yes, my assumption is that they emulated Oracle > behavior. > > But this is one of my next quests to figure out how closely they match > ORA. Again, a big thank you for the pointers into documentation and code! > > Am Freitag, 19. Mai 2017 22:03:47 UTC+2 schrieb Mike Bayer: >> >> >> >> On 05/18/2017 06:56 PM, jan.karstens via sqlalchemy wrote: >> > Upgrading a specific dialect (EXASOL) to 1.1.9 made me stumble across >> > this test (part of test_reflection.py): >> > >> > >> https://github.com/zzzeek/sqlalchemy/blob/rel_1_1_9/lib/sqlalchemy/testing/suite/test_reflection.py#L737 >> >> > >> > def test_get_table_names(self): >> > tablenames = [ >> > t for t in inspect(testing.db).get_table_names() >> > if t.lower() in ("t1", "t2")] >> > >> > eq_(tablenames[0].upper(), tablenames[0].lower()) >> > eq_(tablenames[1].upper(), tablenames[1].lower()) >> > >> > >> > The test case fails and this does not look too unexpected to me. The >> > tables T1 and T2 are added to the tablenames array, and u"t1".upper() >> is >> > always different from u"t1".lower(). >> > >> > Am I missing the purpose of the test case or is this always bound to >> fail? >> >> >> we have two provided dialects, Oracle and Firebird, for which the >> backend databases behave like this: >> >> 1. create a table called "table1": >> >> CREATE TABLE tablw1 (some_col integer) >> >> 2. ask the database what the name of "table1" is: >> >> SQL> SELECT table_name FROM <system catalog stuff, returns the name >> of "table1" >> TABLE_NAME >> ---------- >> >> TABLE1 >> >> >> Now what just happened. We created the table with the name "table1" and >> then it insisted we just named it "TABLE1". What's going on here? >> Basically, we didn't quote the name "table1" in our CREATE TABLE >> statement, which means in virtually all databases that the name is case >> insensitive. It means this table will respond to: >> >> SELECT * FROM TABLE1 >> SELECT * FROM table1 >> SELECT * FROM TaBLe1 >> >> etc. >> >> >> In SQLAlchemy, we first wrote things with SQLite, Mysql, Postgresql in >> mind. In these databases, if you don't quote the name, the system >> catalogs give you back the name *in lowercase*. Plus, lowercase names >> are much more of a Python thing. >> >> What all this means is that *SQLAlchemy considers an all lower case >> table name to be **case insensitive**, and any other combination casings >> to be **case sensitive**. This could really use a whole chapter in >> the docs, but for now you can see it at: >> >> >> http://docs.sqlalchemy.org/en/latest/core/metadata.html?highlight=table%20name#sqlalchemy.schema.Table.params.name >> >> >> "Names which contain no upper case characters will be treated as case >> insensitive names, and will not be quoted unless they are a reserved >> word or contain special characters. A name with any number of upper case >> characters is considered to be case sensitive, and will be sent as >> quoted." >> >> >> So now we have this round trip case that everyone expects to work: >> >> t = Table("table1", metadata, autoload_with=engine) >> >> t.create(some_other_engine) >> >> t2 = Table("table1", some_other_metadata, >> autoload_with=some_other_engine) >> >> That is, we can go back and forth, using "table1", and we get that same >> name back. If we got back "TABLE1", then it would create the table as >> follows: >> >> CREATE TABLE "TABLE1" (some_col integer) >> >> and now we have a **case sensitive** name. It means we can only SELECT >> from it like this: >> >> SELECT * FROM "TABLE1" >> >> These SQL statements OTOH will fail: >> >> SELECT * FROM table1 >> SELECT * FROM TaBlE1 >> SELECT * FROM "table1" >> >> this statement *might* fail depending on backend: >> >> SELECT * FROM TABLE1 >> >> >> >> So that's a lot of detail. Let's talk about EXASOL, which I have noted, >> you have called EXASOL, and not "Exasol", which while this is a database >> I know nothing about, leads to the impression that EXASOL LIKES THINGS >> TO BE CAPITALIZED, basically the way ORACLE and FIREBIRD SEEM TO THINK >> IS GREAT. >> >> If that's the case, then you need to take the steps that the Oracle / >> Firebird DBs take which is to "normalize" / "denormalize" the names that >> you get back from the server. To do that, start with the top-level >> dialect flag: >> >> class EXASOLDialect(default.DefaultDialect): >> # ... >> >> requires_name_normalize = True >> >> then you're going to want to denormalize names every time you pass them >> to a system catalog query, and normalize names every time you receive >> rows back from the system catalogs - look inside of >> lib/sqlalchemy/oracle/base.py for examples of this, such as: >> >> @reflection.cache >> def get_table_names(self, connection, schema=None, **kw): >> schema = self.denormalize_name(schema or >> self.default_schema_name) >> >> if schema is None: >> schema = self.default_schema_name >> >> sql_str = "SELECT table_name FROM all_tables WHERE " >> sql_str += ( >> "OWNER = :owner " >> "AND IOT_NAME IS NULL " >> "AND DURATION IS NULL") >> >> cursor = connection.execute(sql.text(sql_str), owner=schema) >> return [self.normalize_name(row[0]) for row in cursor] >> >> where you can see to get the table names, we denormalize the incoming >> schema name, so that if it is "myschema", we call it "MYSCHEMA", and if >> it is '"myschema"', we call it '"myschema"', so that it matches either >> case insensitively or sensitively per user request, and then when we get >> names back, we convert a name like "TABLE1" to "table1", that is, if we >> see an UPPERCASE name, we assume that is a case insensitive name and we >> convert to lowercase. >> >> In general, when you see a test that is nonsensical and is failing, just >> run it against the default dialect which is SQLite, and watch what it >> does, and use pdb.set_trace() to get in there and poke around at the >> state of things. That will give you direct insight into what's going on >> within a particular test. >> >> >> >> >> >> >> >> >> -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
