Ян Программист wrote:
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 ;))
Hi John,
The ij SHOW command may help you. You can also get the DatabaseMetaData
information by using the functions attached to
https://issues.apache.org/jira/browse/DERBY-3973
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!
The diagnostic VTIs may be useful. See
http://db.apache.org/derby/docs/10.5/ref/ref-single.html#rrefsyscsdialgtables
Hope this helps,
-Rick
People, please help me. John
