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  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

[Aristedes Maniatis]

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

[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  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