It is not possible to correlate DatabaseMetadata index info and primary key info for unnamed constraints

[prrvchr]

There are two methods in DatabaseMetadata (getPrimaryKeys() and getIndexInfo()) which do not give the same result if we create a primary key when creating the table or if we add a primary key after creation of the table.

If we add the primary key after creating the table by the command:
ALTER TABLE "Table1" ADD CONSTRAINT "PK_Table1_ID" PRIMARY KEY ("ID");
We can find the name of the constraint created (PK_Table1_ID) in:

• column 6 (PK_NAME) for the getPrimaryKeys() method.
• column 6 (INDEX_NAME) for the getIndexInfo() method.

If we create the primary key when creating the table, we will not be able to name the primary key and if we try to find it using the DatabaseMetadata we will obtain:

• INTEG_12 in the column 6 (PK_NAME) for the getPrimaryKeys() method.
• RDB$PRIMARY9 in column 6 (INDEX_NAME) for the getIndexInfo() method.

In this case, we are unable to find the index linked to the primary key using the methods offered by DatabaseMetadata.

[mrotteveel]

That is simply how Firebird names the index and the constraint if you don't provide an explicit constraint name. I can consider adding a custom column name to the result set of getIndexInfo(), or you should consider querying the system tables directly.

That said, you should be able to name the constraint directly when creating a table. E.g

create table some_table (
   id integer constraint pk_some_table primary key
);

[mrotteveel]

Adding an extra column with the backing index name to getPrimaryKeys() might actually be simpler.

[prrvchr]

That said, you should be able to name the constraint directly when creating a table. E.g

I think that for me this is the best way, if this way of doing things allows you to have uniform names between the primary key and its index. This allows JDBC compatibility at the DataBaseMetadata level.
I prefer having to manage differences in my DDL commands (in fact it is inevitable) rather than having to manage DataBaseMetaData in a specific way for a driver.

Besides, I noticed that you have to end DDL commands with a semicolon if you want the command to be committed...

[prrvchr]

So I'm closing since you gave me the solution, thank you.

[mrotteveel]

I'm reopening this, because I guess adding an extra column to the getPrimaryKeys result set should be simple. You might not need it anymore, but it might be helpful for someone else in the future. I'll look into this later and make a final decision then.

[prrvchr]

I managed to resolve this problem and the solution I adopted was to create the tables with index or primary key with two SQL commands: one which creates the table and another which creates the primary key or index.
In fact, this simplifies the creation of tables regardless of the underlying JDBC driver and I wanted to cite this solution because it seems the most suitable.

[prrvchr]

@mrotteveel I would like to express an opinion on the solution you are considering implementing.

It seems very important, to me, to follow the JDBC specification because any deviation will only create cases of non-functioning.

So if we want to follow this rule to the letter, the only solution envisaged, for me, would be to put a little intelligence in one of the getPrimaryKeys() or getIndexInfo() functions so that it returns the same name for a index linked to a primary key.

Moreover it seems to me (this needs to be verified) that the index linked to a primary key is only read-only (no SQL command will succeed if we try to modify it) only the primary key is editable? If this is indeed the case, renaming it has little impact...

And I can only encourage you in this direction because my problem is only apparently solved. If I create a table with an SQL command I am still unable to manage key and index in graphical mode in Base.

[mrotteveel]

@mrotteveel I would like to express an opinion on the solution you are considering implementing.

It seems very important, to me, to follow the JDBC specification because any deviation will only create cases of non-functioning.

The JDBC specification doesn't disallow adding extra information to metadata result sets.

So if we want to follow this rule to the letter, the only solution envisaged, for me, would be to put a little intelligence in one of the getPrimaryKeys() or getIndexInfo() functions so that it returns the same name for a index linked to a primary key.

That would violate the requirement that it reports the actual index name.

[prrvchr]

The JDBC specification doesn't disallow adding extra information to metadata result sets.

It's true but the most important thing is to follow the basic recommendations of JDBC and then it doesn't really make sense because it can only be used by specific code.

That would violate the requirement that it reports the actual index name.

I think that if we want to be able to follow the JDBC recommendations concerning the management of primary keys then the name of the indexes linked to the primary key should not necessarily represent the name in the underlying database but rather be managed upstream by the JDBC driver to make this possible.

Besides, if I look at how this was implemented in the MariaDB, Derby, HsqlDB, H2 and PostgreSQL JDBC drivers, it turns out that the name of the indexes is not necessary that of the underlying database but rather that of the primary key... (ie: in MariaDB the index name are PRIMARY for all index linked to a primary key...) In any case for all these drivers the index has the name of the primary key...

[mrotteveel]

I don't have an in-depth knowledge of those other systems and their driver implementation, and why they report the values they report, but your assumption that getPrimaryKeys and getIndexInfo must report the same name is simply incorrect. They are not related as far as JDBC is concerned.

[prrvchr]

I think that the JDBC specifications are not complete enough and pose implementation problems. Primary key management must be part of this. One of the solutions which allows you to circumvent these limitations of the JDBC specifications is to name the indexes linked to the primary keys with the same name. In any case, this is the option chosen by most JDBC drivers.

Now I don't know much about JDBC, but trying to make 8 different drivers work under Base I can notice certain things...

[mrotteveel]

As I said before, for using Firebird from LibreOffice Base, you should probably use the built-in Firebird support (Firebird External for remote connections, or Firebird Embedded for embedded support), not the JDBC driver.

[mrotteveel]

Also, my experience with OpenOffice and LibreOffice Base is that has some - in my opinion - odd interpretations of JDBC.

[prrvchr]

As I said before, for using Firebird from LibreOffice Base, you should probably use the built-in Firebird support (Firebird External for remote connections, or Firebird Embedded for embedded support), not the JDBC driver.

Yes I hope to do a benchmark between these two drivers soon...

Also, my experience with OpenOffice and LibreOffice Base is that has some - in my opinion - odd interpretations of JDBC.

My driver only uses the LibreOffice Base graphics part, all DDL commands are generated by my high-level driver jdbcDriverOOo. And the management of primary keys has nothing to do with Base but rather JDBC archives of the underlying drivers.

[mrotteveel]

My driver only uses the LibreOffice Base graphics part, all DDL commands are generated by my high-level driver jdbcDriverOOo. And the management of primary keys has nothing to do with Base but rather JDBC archives of the underlying drivers.

If this was purely about managing primary keys, you would only need to look at getPrimaryKeys(). However, I think this discussion goes way beyond the scope of the issue tracker (which is for reporting bugs/feature requests). If you want to discuss things further, please take it to firebird-java.

[prrvchr]

If it bothers you to talk about that, there's no problem, I'll stop...

[mrotteveel]

If it bothers you to talk about that, there's no problem, I'll stop...

I actually love discussing things like this, I just don't think the issue tracker is the right place :)

[prrvchr]

Hi Mark,

Adding an extra column with the backing index name to getPrimaryKeys() might actually be simpler.

I'm going back on my decision, it's an excellent idea.

On the other hand, it would be preferable to add a column to the ResultSet of getIndexInfo() since it is its name that must be modified and a simple swap of indexes on the ResultSet's columns will allow this...
To be exact, it would be good to provide, in this new column, the name of the key if the index is linked to a key (primary or foreign) or otherwise the name of the index.