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 <h...@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
