All,

We have a field in XSQLVAR named "aliasname". In fact, it returns a column
alias name and this is documented. An interesting fact is that the source
code comment says it's "relation's alias name". I don't know what was the
original design, but the question is why we don't have relation's alias name
in XSQLVAR?

Some of developers extend/generate SQL queries at runtime. Good examples are
adding an ORDER BY clause or adding extra predicates to the WHERE clause,
depending on some user actions (sorting, filtering, etc). Since they deal
with their own SQL queries they have all required information (relation and
column names/aliases) to generate a valid SQL code. So far so good.

There are also connectivity library developers. And the most powerful of
those libraries have abilities to do some SQL transformations similar to the
aforementioned stuff. But since the connectivity library has no clue on what
its user may pass into, the developers have only two choices: rely on the
API information (read: XSQLDA) to know the referenced relation/column
names/aliases or perform its own SQL parsing. Obviously, the latter choice
tends to be incomplete/buggy until the FB parser grammar/source is used. The
former choice is good, but the returned XSQLDA lacks some useful
information, as shown in the beginning of this message (there's other
missing information as well).

You may ask what is the issue here. Let's look at this perfectly valid SQL
code:

SELECT T1.COL, T2.COL
FROM TAB1 T1, TAB2 T2

Imagine that we need to add a WHERE clause to activate a filter:

SELECT T1.COL, T2.COL
FROM TAB1 T1, TAB2 T2
WHERE T1.COL = 0

But since our API doesn't report the relation alias, the library may do:

SELECT T1.COL, T2.COL
FROM TAB1 T1, TAB2 T2
WHERE COL = 0

Obviously, this is practically useless due to the possible ambiguity. So
libraries do:

SELECT T1.COL, T2.COL
FROM TAB1 T1, TAB2 T2
WHERE TAB1.COL = 0

Which worked quite well. But we have two problems here:

1) it doesn't work with two references to a single relation (unexpected
result is produced)
2) this query is no longer valid in FB 2.0 (SQL-99 rejects it as well)

Shortly said, FB 2.0 breaks a significant subset of the FIBPlus features.
IBO is also expected to be affected. Cannot say for others.

I don't want to discuss various workarounds, although if anyone has any
quick solution is mind, please speak. I just strongly feel that our API
should deliver all required information to the user. And these are our users
who decide whether a particular information item is useful or not.

We now lack a column precision in XSQLDA. We don't have relation aliases. If
we're going to extend metadata name length, we're required to change XSQLDA.
The same goes for introducing namespaces/schemas. I see a few possible ways
to deal with these issues:

1) Extend both isc_dsql_sql_info() and XSQLDA (introducing a new version)
every time. This is what Borland does now.
2) Freeze XSQLDA structure (for compatibility reasons) and extend only
isc_dsql_sql_info(). It doesn't allow us to use longer metadata names or
namespaces, but may satisfy the current needs.
3) Introduce new API which uses a new structure (or just simplified XSQLDA)
for input/output data only and returns all relevant describe information via
isc_dsql_sql_info().

Opinions?


Dmitry