On Mar 10, 2013, at 7:04 PM, Florian Apolloner <[email protected]> wrote:
> It's not always just SQL and even then, before formulating them in SQL it's > easier to just ask the Oracle users to take a look at the failing issues > and provide help there… Eg: https://code.djangoproject.com/ticket/20014 is > a perfect example where someone with Oracle knowledge can chime in, but > everyone else has probably hours in front of him to figure out how the > query should look like (and once he has the query the issue is solved ;)). Assuming this are the tests: https://github.com/django/django/blob/master/tests/introspection/tests.py Picking a random example: # The following test fails on Oracle due to #17202 (can't correctly # inspect the length of character columns). @expectedFailureOnOracle def test_get_table_description_col_lengths(self): cursor = connection.cursor() desc = connection.introspection.get_table_description(cursor, Reporter._meta.db_table) self.assertEqual( [r[3] for r in desc if datatype(r[1], r) == 'CharField'], [30, 30, 75] ) get_table_description is define as: def get_table_description(self, cursor, table_name): "Returns a description of the table, with the DB-API cursor.description interface." cursor.execute("SELECT * FROM %s WHERE ROWNUM < 2" % self.connection.ops.quote_name(table_name)) description = [] for desc in cursor.description: description.append(FieldInfo(*((desc[0].lower(),) + desc[1:]))) return description https://github.com/django/django/blob/master/django/db/backends/oracle/introspection.py#L46 In this case, two factors are playing against you: (1) Whereabout way to get table metadata (i.e. query the table to figure out its data to figure out its meta data). Instead, using the data dictionary directly would be more reliable and to the point, e.g. select owner, table_name, column_name, ... from [user|all]_tab_columns. (2) Distinction between char length vs. byte length. See DATA_LENGTH vs. CHAR_LENGTH vs. CHAR_USED. Related to NLS_LENGTH_SEMANTICS. The short of it, bytes != chars. Some other random comments: (A) def table_name_converter(self, name): "Table name comparison is case insensitive under Oracle" return name.lower() https://github.com/django/django/blob/master/django/db/backends/oracle/introspection.py#L54 That's not quite the case, even if it would appear so. Contrast "Fubar" vs. "FUBAR" vs FUBAR. (note the double quotes). See quoted identifier vs. nonquoted identifier. The short of it, identifier can be case sensitive, even though it's best to stay clear from such a deep rabbit hole. (B) oracle / introspection.py uses the USER_ flavor of the data dictionary (e.g. USER_TABLES). The USER_ flavor only shows objects which are directly owned by the schema. Which may be quite restrictive. You may be better off using the ALL_ flavor, which shows all the objects visible to the schema, irrespectively of ownership. ( C ) Try to formulate queries using the ANSI join syntax instead of the legacy Oracle one (i.e. left join vs. (+) ). The ANSI syntax is clearer, less error prone, and, well, more portable. As far as that test_get_key_columns failure goes, I couldn't track down the code for connection.introspection.get_key_columns… but I suspect it has something to do with point (1)... -- You received this message because you are subscribed to the Google Groups "Django developers" 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/django-developers?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
