About advanced column filtering for translating

JDBC SQL -> Storage|
XML         <- Storage|

I have found some system tables for that. It looks that I will have to use Java classes, responsible for providing that statistical data, with paying attention to their position in storage architecture.
What is a tricky thing: I have to write code for updating that statistics from JDBC client side (actually, in the part of it, responsible for preparing SQLs), but usually that statistics is requested from the storage side (while Derby inner core/non-client part is processing SYS.* related SQL stuff). That is needed to ensure up-to-date state of responding data for SQL/XML client.

It looks that the *SERIALIZE isolation type is the main situation. But the XML responses for multiple clients (each having each own client session, remember? So, data must be consistent when binding & persisting retrieved XMLs between clients, due to needs of replicating user sessions, especially when users doing collaboration based things).

Because various tables, mentioned in SQL/XML request, must provide their column data for reflecting in XML response, and tag nesting would reflect primary key-to-foreign key relations, I figured out following:


SYSTABLES.TABLEID should refer to currently investigated table

SYSCOLUMNS table would provide basic information for columns

SYSCOLUMNS.COLUMNNAME
SYSCOLUMNS.COLUMNDATATYPE

Awsome. But I still not understood is this table would give info about columns against if those appear NOT BEING a constraint. I mean that in MySQL, for example, using EXPLAIN for a table will give information for all non-constraint & constraint tables in a single result set.

I need to figure columns for certain table, which are not reflected in SYS.SYSCONSTRAINTS, but exist in table schema. Main attention to those is because those are not under risk of braking persistence for clients (due to situations where one table columns' data would be transfered up-to-date, and another, which contains records, referred from first table foreign key(s), would not be up-to-date; that should be aware)

By the way - I still haven't found anything like EXPLAIN command in Derby refernce, ij says that there is no such SQL command ;))

Now about indexing:

... ON SYSTABLES.TABLEID = SYSCOLUMNS.TABLEID, ... ON SYSCONGLOMERATES.TABLEID = SYSTABLES.TABLEID
SYSCONGLOMERATES.ISCONSTRAINT - "whether or not conglomerate is a system-generated index enforcing a constraint": is where my strategy is most effective
SYSCONGLOMERATES.ISINDEX is a reasonable, extra preferable in case of XML oriented architecture, when XML tree must be populated from Derby table(s)

That must be under big priority for reflecting table columns to XML tags

Foreign key filtering:

SYS.SYSCONSTRAINTS.TABLEID  = SYSTABLES.TABLEID will refer to investigated tables' IDs
SYS.SYSCONSTRAINTS.TYPE IS 'F' - that will figure out which columns are native ones

CAUTION: Remember that reflected columns should not be (as also as tables) in cyclic dependencies! I do not say that such tables should not exist in database; I just say that such tables should not be involved in column-to-XML tag reflection. Otherwise you can get following:

<report id="132" reporter_id="12">
  <category id="2" report_id="132"/>
</report>

That could happen if table "report" is requested for XML response. Hence double mentioned data, id="132"

And the last thing. I need to know, from JDBC client side, if any transactions exist (in any state) for a certain table, in a single moment of time (actually, it is a small time period, while checking code is executed). It is a storage monitoring issue. But I haven't a Java class for that. And there is no system table for transaction statistics. I need that stuff, I mean it!

People, please help me. John