db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Filtering
Date Wed, 07 Apr 2010 16:06:35 GMT
Ян Программист 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


Mime
View raw message