Schema generation fails in HSQLDB when generating foreign key constraints targeting tables with more than one join

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Schema generation fails in HSQLDB when generating foreign key constraints targeting tables with more than one join

Hugi Thordarson
Hi all,

I’m working on a Cayenne issue (https://issues.apache.org/jira/browse/CAY-2137). To be able to create a test for the fix, I think I have to create a new cayenne testing project inside cayenne-server (to have a map and some entities to test on). I’ve done that and have gotten everything to work, apart from the SQL generation part. When the test is fired up, it will attempt to generate the schema for my map by generating and running the following SQL-statements

— CREATE CACHED TABLE invoice (invoice_number INTEGER NOT NULL, year INTEGER NOT NULL, PRIMARY KEY (invoice_number, year))
— CREATE CACHED TABLE invoice_line (invoice_number INTEGER NOT NULL, line_number INTEGER NOT NULL, year INTEGER NOT NULL, PRIMARY KEY (invoice_number, line_number, year))
— ALTER TABLE invoice_line ADD CONSTRAINT U_invoice_line_16819552 FOREIGN KEY (year, invoice_number) REFERENCES invoice (year, invoice_number) ON DELETE CASCADE

But the last line will fail with the following exception:
— java.sql.SQLException: Primary or unique constraint required on main table: INVOICE in statement [ALTER TABLE invoice_line ADD CONSTRAINT U_invoice_line_16819552 FOREIGN KEY (year, invoice_number) REFERENCES invoice (year, invoice_number) ON DELETE CASCADE]

This is reproducible in other projects using HSQLDB—and generating the schema from the same map in H2 works fine, so it seems it might be a problem with HSQLDB schema creation. But I’ve not seen in what way the schema generation is wrong, one would think just declaring the primary key columns should be enough, which cayenne is certainly doing.

If anyone cares to take a look, here’s a quick maven project that contains the model in question and demonstrates the problem (configurations for both H2 and HSQLDB).

https://github.com/hugith/rel-test/

Cheers,
- hugi
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Schema generation fails in HSQLDB when generating foreign key constraints targeting tables with more than one join

Hugi Thordarson
OK then, it seems the schema generation problem is bound to the HSQLDB version Cayenne is using (1.8.0.4). If I upgrade to the current version (2.3.4) or just anything >=2.0.0, schema generation works fine.

However, upgrading to 2.3.4 creates other problems in tests, for example the script “create-sp-aliases.sql” in cayenne-server is using syntax that’s no longer supported by HSQLDB 2 (CREATE ALIAS). But if I disable that script, other tests in cayenne-server seem to run just fine.

Do you think it’s okay for us to upgrade? Any HSQLDB expert care to comment?

- hugi


> On 10. nóv. 2016, at 21:04, Hugi Thordarson <[hidden email]> wrote:
>
> Hi all,
>
> I’m working on a Cayenne issue (https://issues.apache.org/jira/browse/CAY-2137). To be able to create a test for the fix, I think I have to create a new cayenne testing project inside cayenne-server (to have a map and some entities to test on). I’ve done that and have gotten everything to work, apart from the SQL generation part. When the test is fired up, it will attempt to generate the schema for my map by generating and running the following SQL-statements
>
> — CREATE CACHED TABLE invoice (invoice_number INTEGER NOT NULL, year INTEGER NOT NULL, PRIMARY KEY (invoice_number, year))
> — CREATE CACHED TABLE invoice_line (invoice_number INTEGER NOT NULL, line_number INTEGER NOT NULL, year INTEGER NOT NULL, PRIMARY KEY (invoice_number, line_number, year))
> — ALTER TABLE invoice_line ADD CONSTRAINT U_invoice_line_16819552 FOREIGN KEY (year, invoice_number) REFERENCES invoice (year, invoice_number) ON DELETE CASCADE
>
> But the last line will fail with the following exception:
> — java.sql.SQLException: Primary or unique constraint required on main table: INVOICE in statement [ALTER TABLE invoice_line ADD CONSTRAINT U_invoice_line_16819552 FOREIGN KEY (year, invoice_number) REFERENCES invoice (year, invoice_number) ON DELETE CASCADE]
>
> This is reproducible in other projects using HSQLDB—and generating the schema from the same map in H2 works fine, so it seems it might be a problem with HSQLDB schema creation. But I’ve not seen in what way the schema generation is wrong, one would think just declaring the primary key columns should be enough, which cayenne is certainly doing.
>
> If anyone cares to take a look, here’s a quick maven project that contains the model in question and demonstrates the problem (configurations for both H2 and HSQLDB).
>
> https://github.com/hugith/rel-test/
>
> Cheers,
> - hugi

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Schema generation fails in HSQLDB when generating foreign key constraints targeting tables with more than one join

Aristedes Maniatis-2
On 11/11/16 9:07am, Hugi Thordarson wrote:
> Do you think it’s okay for us to upgrade? Any HSQLDB expert care to comment?

Not an expert in HSQLDB (I tend to use Derby as my lightweight embedded db of choice). But I reckon few people are using HSQLDB for any production purposes, so it is really just a simple test environment and you should upgrade it without problem. At any rate, we may as well be testing against the latest version that has been out for many years.


Ari



--
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Schema generation fails in HSQLDB when generating foreign key constraints targeting tables with more than one join

Andrus Adamchik
+1 to upgrade. (as long as we figure out how to fix the failing SQL scripts)

> On Nov 11, 2016, at 4:02 AM, Aristedes Maniatis <[hidden email]> wrote:
>
> On 11/11/16 9:07am, Hugi Thordarson wrote:
>> Do you think it’s okay for us to upgrade? Any HSQLDB expert care to comment?
>
> Not an expert in HSQLDB (I tend to use Derby as my lightweight embedded db of choice). But I reckon few people are using HSQLDB for any production purposes, so it is really just a simple test environment and you should upgrade it without problem. At any rate, we may as well be testing against the latest version that has been out for many years.
>
>
> Ari
>
>
>
> --
> -------------------------->
> Aristedes Maniatis
> GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A

Loading...