Filtering

[Ян Программист <we...@gmail.com>]

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

Re: Filtering

[Ян Программист <we...@gmail.com>]

Thanks. I will investigate that today. John

Re: Filtering

[Rick Hillegas <ri...@oracle.com>]

Ян Программист 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