db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ян Программист <webautoma...@gmail.com>
Subject Filtering
Date Wed, 07 Apr 2010 15:36:33 GMT
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

Mime
View raw message