PDA

View Full Version : [Mondrian] RE: Eigenbase perforce change 9104 for review



Julian Hyde
04-14-2007, 07:00 PM
> Sherman Wood wrote:
>
> DB2 is all screwed up. Quoting identifiers in DB2 is inconsistent.
>
> If I quote all identifiers in DB2 using " which is the quote character
> indicated by the DB2 JDBC driver, like:
>
> select "time_by_day"."the_year" as "c0" from "time_by_day" as
> "time_by_day" group by "time_by_day"."the_year" order by
> "time_by_day"."the_year" ASC
>
> ... the query fails with with things like:
>
> com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -204,
> SQLSTATE:
> 42704, SQLERRMC: DB2ADMIN.time_by_day)

There's a problem which occurs in Oracle: people create tables with
mixed-case names but they don't double-quote the table and column names,
so the names are converted to upper-case in the catalog. Then mondrian
submits queries with double-quotes and they get "ORA-00904: invalid
column name" or "ORA-00942: table or view does not exist". The solution
is not to remove double-quotes from the identifiers in the queries; it
is to add double-quotes to the DDL statements, so everything is stored
in mixed-case from the start.

>From this
(http://www-128.ibm.com/developerworks/db2/library/techarticle/0203adama
che/0203adamache.html), it looks like DB2's rules are identical to
Oracle:

Case sensitivity and object names

All database object names (tables, views, columns and so on) are stored
in the catalog tables in uppercase unless the identifier is delimited.
If you use a delimited name to create the identifier, the exact case of
the name is stored in the catalog tables.

An identifier, such as a column name or table name, is treated as case
insensitive when used in an SQL statement unless it is delimited. For
example, assume that the following statements are issued:




CREATE TABLE MyTable (id INTEGER)

CREATE TABLE "YourTable" (id INTEGER)




Two tables -- MYTABLE and YourTable -- will exist.

Now, the following two statements are equivalent:




SELECT * FROM MyTable

SELECT * FROM MYTABLE




However, the second statement below will fail with TABLE NOT FOUND since
there is no table named YOURTABLE:




SELECT * FROM "YourTable" // executes without error

SELECT * FROM YourTable // error, table not found





So, the solution for DB2 would be the same as for Oracle: fix your DDL.

Let me know whether this works. If so, we can unwind all of the stuff in
the dialect which treats DB2 identifiers differently (starting way back
in your change 4009).

> 1. The solution for DB2 drill through I did, works. It is not
> just about
> case sensitivity and embedded spaces, unfortunately. I would
> love it to be
> cleaner than this.

I need a better reason than 'it just works' to let the code into the
code base.

>
> 2. I take your point about not dealing with embedded "s. The
> rest of the
> code in SqlQuery.Dialect does not deal with it either. Is it a real
> problem?

Not true. SqlQuery.java, line 874:

String val2 = Util.replace(val, q, q + q);
buf.append(q);
buf.append(val2);
buf.append(q);

It's not very likely that an identifier would contain a double-quote,
but it doesn't take much effort to deal with that problem, and so we do.
The principle that SQL generation is handled in the dialect, and nowhere
else, is a very important one. Your code breaks that.

>
> 3. Drill through in Mondrian is left up to the caller of the
> Mondrian API.
> Mondrian hands back the SQL and it is up to the caller to run
> the SQL and
> deal with the result. Given everything Mondrian does to hide
> the database,
> this is messy. We should probably have a DrillThroughResult that wraps
> that does the SQL and manage the difference between the database
> structures and the schema metadata. There are issues with dealing with
> dealing with the potential data volumes (ie. 100s of 1,000s
> of rows) which
> is currently left up to the caller to deal with. Maybe the
> DrillThroughResult should appear as a ResultSet with an Iterator
> interface, so that the result set is not all in memory.

I agree that handing back SQL text breaks the abstraction, somewhat. But
it also gives them the control to execute the SQL with different cursor
options, or even to hand the SQL back and execute it on the client. We
should let the current mondrian API stand. We can re-open the discussion
in the context of the olap4j API.

Julian



_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian