Missing column FKINDEX_NAME in I_S.CROSS_REFERENCES
Paigan Jadoth
DB from the INFORMATION_SCHEMA to build up a data structure
representing the DB.
Works all pretty well, except for one problem:
If more than one FOREIGN KEY use the same FKTABLE index, only the last
association can be read from the I_S while all previous associations
get suppressed.
This means important information is lost, which in turn leads to
errors (which is not desirable).
(see SQL batch below for an example)
The reason is that INFORMATION_SCHEMA.CROSS_REFERENCES is missing a
column FKTABLE_INDEX which provides the name of the associated index
for the particular FK.
The backtrack information provided by
INFORMATION_SCHEMA.INDEXES.CONSTRAINT_NAME sadly is not enough,
because it's only one value to represent a 1-to-n relationship (1
FKTable-index can be referenced by n FKs).
Hence the C_R entry itself must show which index it is linked to (just
like the already existing column PK_NAME does for the target table
unique constraint).
Is there another way of retrieving the missing information?
Is there a chance for the missing column to be added in a future
version of H2?
Thanks for answers :-)
Here is an example that demonstrates the problem:
-- DDL
CREATE TABLE A(a1 INT, a2 INT);
CREATE TABLE B(b1 INT, b2 INT);
CREATE TABLE C(c1 INT, c2 INT);
CREATE INDEX idx1 ON B(b1, b2);
CREATE INDEX idx2 ON B(b1, b2);
ALTER TABLE B ADD CONSTRAINT FK_b_a FOREIGN KEY (b1, b2) REFERENCES
A(a1, a2);
ALTER TABLE B ADD CONSTRAINT FK_b_c FOREIGN KEY (b1, b2) REFERENCES
C(c1, c2);
-- I_S query
SELECT
CRF.FKTABLE_NAME,
CRF.FK_NAME,
CRF.FKCOLUMN_NAME,
IDX.INDEX_NAME,
IDX.COLUMN_NAME AS IDXCOLUMN_NAME
FROM INFORMATION_SCHEMA.CROSS_REFERENCES CRF
LEFT JOIN INFORMATION_SCHEMA.INDEXES IDX ON IDX.NON_UNIQUE = TRUE
AND IDX.TABLE_CATALOG = CRF.FKTABLE_CATALOG
AND IDX.TABLE_SCHEMA = CRF.FKTABLE_SCHEMA
AND IDX.TABLE_NAME = CRF.FKTABLE_NAME
AND IDX.COLUMN_NAME = CRF.FKCOLUMN_NAME
AND IDX.CONSTRAINT_NAME = CRF.FK_NAME
Paigan Jadoth
details at once.
The issue simply is:
How can I query the information schema for the names of the FK-table
indices used by all foreign keys if potentially multiple FKs use the
same FK-table index?
Thank you for answers.
Paigan Jadoth
could be added very easily to the CROSS_REFERENCES table, I think.
Somewhere around here...
http://code.google.com/p/h2database/source/browse/trunk/h2/src/main/org/h2/table/MetaTable.java?r=3879#1299
... there would just have to a line...
Index index = ref.getIndex();
... and then below in the add(...) call an additional entry
//FKINDEX_NAME (or whatever)
identifier(index.getName()),
Of course ConstraintReferential#index would require a getter to be
created for that field
http://code.google.com/p/h2database/source/browse/trunk/h2/src/main/org/h2/constraint/ConstraintReferential.java?r=3879#63
(is there a particular reason that the FK instance's index is not
gettable?)
And the column name would have to be registered (http://
code.google.com/p/h2database/source/browse/trunk/h2/src/main/org/h2/
table/MetaTable.java?r=3879#392)
I surely don't have an overview over all the internals of H2,
nevertheless the code structure looks pretty clean so that such a
change should be no problem. Also existing usage of the I_S.C_R table
should not be influenced because nothing existing would change or be
removed.
It would be very helpful if such an extension would be considered for
a future version, because currently the information is really
missing :-(.
Maybe I can build H2 on my own during the holidays to test the change
myself and help reduce the effort :)
Paigan Jadoth
I've written some h2acks ( :D ) providing the missing feature as a SQL
procedure.
https://jadoth.svn.sourceforge.net/svnroot/jadoth/H2acks/src/net/jadoth/h2acks/SQLFunctions.java
(sourceforge home: http://sourceforge.net/projects/jadoth/)
This solution is sufficient for me for the time being, of course I'd
prefer to have a proper fix for the problem in some future version of
H2.
On Dec 10, 1:46 pm, Paigan Jadoth <jadoth.pai...@googlemail.com>
wrote:
> I did a little research in the H2 code base and found that the column
> could be added very easily to the CROSS_REFERENCES table, I think.
> Somewhere around here...http://code.google.com/p/h2database/source/browse/trunk/h2/src/main/o...
>
> ... there would just have to a line...
> Index index = ref.getIndex();
>
> ... and then below in the add(...) call an additional entry
> //FKINDEX_NAME (or whatever)
> identifier(index.getName()),
>
> Of course ConstraintReferential#index would require a getter to be
> created for that fieldhttp://code.google.com/p/h2database/source/browse/trunk/h2/src/main/o...
Thomas Mueller
> I am trying to automatically and generically read the structure of a
> DB from the INFORMATION_SCHEMA to build up a data structure
> representing the DB.
Is this just for H2 or for other databases as well?
Do other databases provide all the data you need? If yes, what feature
is missing in H2?
If it's just for H2, I suggest to use the SCRIPT statement.
Regards,
Thomas
Paigan Jadoth
and thanks for reading all that stuff :).
The problem in principle here is very similar to
http://groups.google.com/group/h2-database/t/5a3a1eccac322abc, so I
can cut most of the blabla this time.
It simply is about architecture: SCRIPT just dumps a structure-
reproduction DDL script. But what is required for proper architecture
is for the application to "understand" the DBMS's structures (= read
the meta data and build the structure abstractly from it to have
something to work on). Oh btw. still SCRIPT is a cool function, no
doubt :D.
To your question:
It's for potentially all DBMS (and an earlier version of my lib is
already in production use with adaptors for multiple DBMS), but H2 is
the first to be used with the extended features of handling
constraints etc.
Still I can already tell from experience: Some of them will provide
all the required data, some won't. As usual. For the bulky ancient
DBMS, I don't even bother to talk to their support about such things
but just say "not possible, blame the ancient DBMS". H2 in contrast is
modern and cleanly written enough to actually fix stuff like that, so
I try it :).
Also H2 shouldn't hide behind the deficiencies of ancient DBMSs. As
far as I understand it, H2 is meant to provide a better alternative,
not just say "well it doesn't work but the others don't, either".
The feature missing in H2 is:
As one INDEX can be used by n FOREIGN KEYS, it's not sufficient to
provide the name of one FK that uses the index, but of ALL FKs using
the index.
The easiest and cleanest way to do so is to extend the
I_S.CROSS_REFERENCES table with a column FKTABLE_INDEX_NAME (or so)
that provides the information "this FK uses that INDEX".
Similar to the already existing column PK_NAME.
As far as I could see into H2's internal structures and the working
workaround procedure, this should be very easily implemented.
Again of course only with low priority, fully understandable.
Regards
On Dec 16, 11:05 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:
Noel Grandin
Sorry I didn't look at this for so long.
Firstly, there is something wrong with your example. IDX1 and IDX2 are
not being used at all to enforce those foreign keys.
If those indexes were on table A and table C, then the foreign key
logic might use them as an optimisation to check those constraints,
but it is not required to do so.
Secondly, you can reconstruct constraints and indexes by looking at
the schema CONSTRAINTS and INDEXES tables. You don't need to involve
CROSS_REFERENCES.
Regards, Noel Grandin
> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to h2-database...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
>