Foreign Key constraints are not created by schemify

[Julian Backes]

Hi,

I'm trying to get my first scala/lift app working and I have a problem:
Schemifier.schemify(...) creates everything, i.e. tables, primary keys,
indices but it does not create the foreign key constraints.

I've seen in Schemifier.scala SQL code for generating foreign key
constraints so I think it should work. Or am I wrong?

I tried Lift 1.0 and 1.1-M7

I also tested the pocketchangeapp from
http://github.com/tjweir/pocketchangeapp but still the same problem. I'm
using PostgreSQL (postgresql-8.4-701.jdbc4.jar) and Scala 2.7.7. Server
is PostgreSQL 8.3.7.
Here the SQL log when doing a "mvn jetty:run" in pocketchangeapp:

INFO - CREATE TABLE users (firstname VARCHAR(32) , lastname VARCHAR(32)
, email VARCHAR(48) , locale VARCHAR(16) , timezone VARCHAR(32) ,
password_pw VARCHAR(48) , password_slt VARCHAR(20) , id BIGSERIAL ,
superuser BOOLEAN , validated BOOLEAN , uniqueid VARCHAR(32))
INFO - ALTER TABLE users ADD CONSTRAINT users_PK PRIMARY KEY(id)
INFO - CREATE TABLE tag (name VARCHAR(64) , id BIGSERIAL , account BIGINT)
INFO - ALTER TABLE tag ADD CONSTRAINT tag_PK PRIMARY KEY(id)
INFO - CREATE TABLE account (name VARCHAR(100) , id BIGSERIAL ,
description VARCHAR(300) , owner BIGINT , externalaccount VARCHAR(300) ,
balance DECIMAL(16,2) , is_public BOOLEAN)
INFO - ALTER TABLE account ADD CONSTRAINT account_PK PRIMARY KEY(id)
INFO - CREATE TABLE accountadmin (id BIGSERIAL , administrator BIGINT ,
account BIGINT)
INFO - ALTER TABLE accountadmin ADD CONSTRAINT accountadmin_PK PRIMARY
KEY(id)
INFO - CREATE TABLE accountviewer (id BIGSERIAL , viewer BIGINT ,
account BIGINT)
INFO - ALTER TABLE accountviewer ADD CONSTRAINT accountviewer_PK PRIMARY
KEY(id)
INFO - CREATE TABLE accountnote (note TEXT , id BIGSERIAL , account BIGINT)
INFO - ALTER TABLE accountnote ADD CONSTRAINT accountnote_PK PRIMARY KEY(id)
INFO - CREATE TABLE expense (dateof TIMESTAMP , amount DECIMAL(16,2) ,
description VARCHAR(100) , notes VARCHAR(1000) , id BIGSERIAL ,
serialnumber BIGINT , account BIGINT , currentbalance DECIMAL(16,2) ,
receiptmime VARCHAR(100) , receipt BYTEA)
INFO - ALTER TABLE expense ADD CONSTRAINT expense_PK PRIMARY KEY(id)
INFO - CREATE TABLE expensetag (id BIGSERIAL , tag BIGINT , expense BIGINT)
INFO - ALTER TABLE expensetag ADD CONSTRAINT expensetag_PK PRIMARY KEY(id)
INFO - CREATE INDEX users_email ON users ( email )
INFO - CREATE INDEX users_uniqueid ON users ( uniqueid )
INFO - CREATE INDEX tag_account ON tag ( account )
INFO - CREATE INDEX account_owner ON account ( owner )
INFO - CREATE INDEX accountadmin_administrator ON accountadmin (
administrator )
INFO - CREATE INDEX accountadmin_account ON accountadmin ( account )
INFO - CREATE INDEX accountviewer_viewer ON accountviewer ( viewer )
INFO - CREATE INDEX accountviewer_account ON accountviewer ( account )
INFO - CREATE INDEX accountnote_account ON accountnote ( account )
INFO - CREATE INDEX expense_account ON expense ( account )
INFO - CREATE INDEX expensetag_tag ON expensetag ( tag )
INFO - CREATE INDEX expensetag_expense ON expensetag ( expense )
INFO - Bootstrap up

Thanks in advance,
Julian

[Julian Backes]

Hi Lift Community,

this is a reply to my own question:

> I'm trying to get my first scala/lift app working and I have a
> problem: Schemifier.schemify(...) creates everything, i.e. tables,
> primary keys, indices but it does not create the foreign key
> constraints.

After hours of compiling and testing, I stumbled across some messages
here on the mailing lists which discussed the same problem (I have no
idea why I haven't seen them before).

In one message, Derek said
> At this time we don't support it, but feel free to file an issue.
> I'll have time at some point to work on it, and I think that it would
> be useful to generate.

What does he mean by "we don't support it"? I've seen the code for
generating foreign key constraints in Schemifier.scala
I just needed to set supportsForeignKeys_? in the postgresql driver
class to true and... it works now. The generated foreign key constraints
are perfectly ok.

Is there a reason that this code is not used?

Julian

[Derek Chen-Becker]

It's been a long time since I looked at that particular code, so I may have misspoke. Having said that, if it's currently disabled in the driver I'm not sure why and I would want to review it before saying that it works properly in all cases.

Derek

Julian

--

You received this message because you are subscribed to the Google Groups "Lift" group.
To post to this group, send email to lif...@googlegroups.com.
To unsubscribe from this group, send email to liftweb+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/liftweb?hl=en.

[Julian Backes]

Hi Derek,

> It's been a long time since I looked at that particular code, so I may
> have misspoke. Having said that, if it's currently disabled in the
> driver I'm not sure why and I would want to review it before saying that
> it works properly in all cases.

I think the problem here is that the user expects (like I did) foreign
keys to be created if he uses mapper classes referencing other mapper
classes. This "behaviour" should at least be mentioned somewhere in the
documentation (btw, the documentation is in my opinion the biggest
problems of Lift at the moment).
I think, using a relational database without foreign keys is somehow not
very useful because you never really know whether you have referential
integrity...
It would be great if you looked at the code and enabled it. This would
really be an improvement for the mapper stuff in Lift 1.1

Julian

[Derek Chen-Becker]

I agree on both points (foreign keys and documentation). Please open a ticket asking for proper foreign key support and I'll work on it next week.

Derek

Julian

[David Pollak]

On Thu, Dec 3, 2009 at 1:47 PM, Derek Chen-Becker <dchen...@gmail.com> wrote:

I agree on both points (foreign keys and documentation). Please open a ticket asking for proper foreign key support and I'll work on it next week.

Please make sure it works on PG 8.0/8.1 as there is at least 1 Lift app in production against 8.0
 

--
Lift, the simply functional web framework http://liftweb.net
Beginning Scala http://www.apress.com/book/view/1430219890
Follow me: http://twitter.com/dpp
Surf the harmonics

[Julian Backes]

> I agree on both points (foreign keys and documentation). Please open a
> ticket asking for proper foreign key support and I'll work on it next
week.

http://github.com/dpp/liftweb/issues/#issue/224


Julian

[Derek Chen-Becker]

Absolutely. I have a PG 8.0, 8.1 and 8.3 instance set up for testing on my home box because of the last time I made a PG-related change.

Derek

[Derek Chen-Becker]

OK, I've run into a snag. Schemifier does support foreign key creation, as do most of the database vendors. The ManyToMany Mapper support, however, seems to expect no foreign key constraints, since it's explicitly testing broken refs in the ManyToManySpecs tests:

  "ManyToMany" should {
    "skip broken joins in children" in {
      setupDB
      val person = createPerson
      person.companies.joins.length must_== 10
      person.companies.all.length must_== 8
    }
    ...

I wonder if we should have FK generation be a configuration parameter for schemify. I could change the code so that the default schemify method continues to generate DDL without FK  constraints, but add a second schemify method that takes a boolean parameter controlling whether FKs are generated. That, coupled with the DriverType.supportsForeignKeys_?, would allow people to add FKs if they want. Thoughts?

Derek

[Julian Backes]

> OK, I've run into a snag. Schemifier does support foreign key creation,
> as do most of the database vendors. The ManyToMany Mapper support,
> however, seems to expect no foreign key constraints, since it's
> explicitly testing broken refs in the ManyToManySpecs tests:

I do not really know something about the ManyToMany stuff in mapper but
in my opinion, it should support foreign keys. You always have
situations where you change something in the database by hand. In such
cases, it's always good to have foreign keys...

> I wonder if we should have FK generation be a configuration parameter
> for schemify. I could change the code so that the default schemify
> method continues to generate DDL without FK constraints, but add a
> second schemify method that takes a boolean parameter controlling
> whether FKs are generated.

Sounds good to me

> That, coupled with the
> DriverType.supportsForeignKeys_?, would allow people to add FKs if they
> want. Thoughts?

I had the problem that when I wanted to test the foreign key support, I
had to recompile most parts of lift with
DriverType.supportsForeignKeys_? set to true (which was no fun because I
had to download many many dependencies from the incredible slow maven
repo). Wouldn't it make sense to allow setting
DriverType.supportsForeignKeys_? at runtime? This would be useful when,
for example, some database system does not support foreign keys when the
corresponding Lift version is released but starts supporting it two
weeks later. In such a situation, I do not want do recompile Lift just
to have DriverType.supportsForeignKeys_? set to true...

Julian

[Naftoli Gugenheim]

What about that spec I threw together in a hurry implies that foreign
keys would conflict with mapper?
See a recent (few weeks ago) thread discussing how many-to-many should
deal with broken joins.
Why would one not want them to be created by default?
P.S. Sorry I never made that ticket for H2 FK support.

>>>>> liftweb+u...@googlegroups.com<liftweb%2Bunsu...@googlegroups.com>
>>>>> .

>>>>> For more options, visit this group at
>>>>> http://groups.google.com/group/liftweb?hl=en.
>>>>>
>>>>>
>>>>>
>>>> --
>>>> You received this message because you are subscribed to the Google
>>>> Groups
>>>> "Lift" group.
>>>> To post to this group, send email to lif...@googlegroups.com.
>>>> To unsubscribe from this group, send email to

>>>> liftweb+u...@googlegroups.com<liftweb%2Bunsu...@googlegroups.com>
>>>> .

>>>> For more options, visit this group at
>>>> http://groups.google.com/group/liftweb?hl=en.
>>>>
>>>
>>>
>>>
>>> --
>>> Lift, the simply functional web framework http://liftweb.net
>>> Beginning Scala http://www.apress.com/book/view/1430219890
>>> Follow me: http://twitter.com/dpp
>>> Surf the harmonics
>>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "Lift" group.
>>>
>>> To post to this group, send email to lif...@googlegroups.com.
>>> To unsubscribe from this group, send email to

>>> liftweb+u...@googlegroups.com<liftweb%2Bunsu...@googlegroups.com>
>>> .

[Derek Chen-Becker]

If FKs are enabled then the spec fails because you're intentionally trying to break the joins by deleting the Companys that are on the other side of the many-to-many. I'm not sure that FKs should be enabled by default, but if I add the option to introduce them with schemify then I could write another spec that assumes FKs are enabled and expects an exception if you try to delete a joined entity.

Derek