You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Hugi Thordarson <hu...@karlmenn.is> on 2016/11/10 21:04:36 UTC
Schema generation fails in HSQLDB when generating foreign key
constraints targeting tables with more than one join
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
Re: Schema generation fails in HSQLDB when generating foreign key
constraints targeting tables with more than one join
Posted by Andrus Adamchik <an...@objectstyle.org>.
+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 <ar...@maniatis.org> 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
Re: Schema generation fails in HSQLDB when generating foreign key
constraints targeting tables with more than one join
Posted by Aristedes Maniatis <ar...@maniatis.org>.
On 11/11/16 9:07am, Hugi Thordarson wrote:
> Do you think it\u2019s 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
Re: Schema generation fails in HSQLDB when generating foreign key
constraints targeting tables with more than one join
Posted by Hugi Thordarson <hu...@karlmenn.is>.
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 <hu...@karlmenn.is> 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