Oracle. Foreign Keys missing.

Liquibase Development
1

There is a bag in Oracle JDBC driver:
https://cwiki.apache.org/jira/browse/DDLUTILS-219

databaseMetaData.getImportedKeys() returns only these FKs, which targeted on PK columns. But Oracle lets create Foreign Key, linked on Unique Constraint.
So we need to create additional query to find all others correct Foreign Keys.

I’ll think about how to fix it.

2

Sorry of my stupidity ))
I should firstly exam new version of jdbc driver, and only after that rings an alert ))

3

On Oracle Database 11g Release 2 (11.2.0.1.0) JDBC Drivers there is a same trouble.
I have wrote my request to fix it on oracle’s support page. ;D
This is one really correct way to fix it :slight_smile:

BUT… :)))

that’s why we need to solve this locally ) I’ve only one idea now…
It is to override getForeignKeys method for oracle case and generate FK’s list by sql request through this method.
hell… it’s looks like botchery.

Damned… I do wrong understood purpose of getImportedKeys() method… It works correct.

Description:
Get a description of the primary key columns that are referenced by a table’s foreign key columns (the primary keys imported by a table).

So it should return only FK which referenced with PKs…

But there are no other tools for getting all FKs in jdbc…

4

Sorry )
I always forget about other database support…

It seems these FKs shouldn’t be generated.

I’ll think about. May be i’ll find how to solve it

5

If you are returning FKs that do not correspond to a PK, there is probably code throughout the diff tool that will get upset as well, assuming it can find a column and table that is a primary key.  Perhaps not, too. 

As you get to more database-specific things, you sometimes need to leave the jdbc metadata and query the database metadata tables directly.  Just be careful you keep it isolated to your database type.

Nathan 

6

I have improved it.

Please notice me if there are serious wrongs in my commit. <a href=‘mailto:boosta@mail.ru’ target=’_blank’>boosta@mail.ru</a>

I have decided to extract Foreign key generation process from JdbcDatabaseSnapshotGenerator.getForeignKeys to new method:

    generateForeignKey(fkInfo, database, fkList);
For passing all FK parameters into it I have created new class named ForeignKeyInfo. I think it's better then list all params in method's signature.  :) There was another way - to use Properties. But in this case some data will be in need of casting. This entity is only container of all needed data.

So now there are many ways to set up all these params and then try to create FK based on them.

Also I’ve added getAdditionalForeignKeys() method:

    public List getAdditionalForeignKeys(String schemaName, Database database) throws DatabaseException{ return new ArrayList(); }

It may be overrided and used for finding all specific FKs for current database.
Note that it have no need of tableName, that’s why I was forced to place it outside the loop and create new method instead of refactoring of JdbcDatabaseSnapshotGenerator.getForeignKeys method.

Of course, I have already override getAdditionalForeignKeys() method for oracle :slight_smile:
So it just generates directly sql-query to database and finds all FKs which have references only on unique columns.
Honestly, this query is so long… But i haven’t found another solution.
I’ve tested it on Oracle - it works fine :smiley:

========
Another Side

But there was still a problem. How it should be parsed by another databases?
I thought a lot about.

So, I’ve decided that the most correct way is to add one more attribute to xml element addForeignKeyConstraint.
I called it referencedToPrimary.
It’s true by default. If it is false - it means that our FK referenced on unique column.

And now our database may decide to create FK or not depends of this property and itself’s type.
I have set up only Oracle type, so if database instance of OracleDatabase - creation will be executed, else it will be skipped.

========
Test System

My editions have broke some tests. I’ve tried to fix them and I did it excepts of Integration Tests…
I can’t understand how it’s works, so I think it will be more useful if I won’t correct them by myself :slight_smile: