What are the steps to create foreign key constraints in a PostgreSql 8.4 system?

[Strom]

I'd like to know what steps I need to take to enable foreign keys to
be created in my database. I have PostgreSql 8.4, and I noticed talk
of setting supportsForeignKeys_? to true, but I'm not sure where I do
that. It seems I need to override the PostgreSqlDriver object, but I
would like to know where and how to set this in Boot.scala. I
currently only have assigned a DBVendor and not a driver type.

Thanks,
Strom

[Naftoli Gugenheim]

MapperRules.createForeignKeys_?

Incidentally, am I the only using H2 that sees foreign keys being created on every schemify?

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

[Strom]

And how do I assign MapperRules.createForeignKeys_?

I see it's a ConnectionInterface => Boolean, but I'm not sure how to
obtain the ConnectionInterface for Postgres

Thanks for the response.

On Jul 9, 1:11 pm, Naftoli Gugenheim <naftoli...@gmail.com> wrote:
> MapperRules.createForeignKeys_?
>
> Incidentally, am I the only using H2 that sees foreign keys being created on
> every schemify?
>

> On Fri, Jul 9, 2010 at 4:04 PM, Strom <strommo...@gmail.com> wrote:
> > I'd like to know what steps I need to take to enable foreign keys to
> > be created in my database. I have PostgreSql 8.4, and I noticed talk
> > of setting supportsForeignKeys_? to true, but I'm not sure where I do
> > that. It seems I need to override the PostgreSqlDriver object, but I
> > would like to know where and how to set this in Boot.scala. I
> > currently only have assigned a DBVendor and not a driver type.
>
> > Thanks,
> > Strom
>
> > --
> > 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]

Unless you're actually working with different connections (the ConnectionInterface arg would allow you to do different behaviors for different connections), this should do:

MapperRules.createForeignKeys_? = c => true

Derek

To unsubscribe from this group, send email to liftweb+u...@googlegroups.com.

[Strom]

Thanks Derek. That did the trick.

On Jul 9, 3:56 pm, Derek Chen-Becker <dchenbec...@gmail.com> wrote:
> Unless you're actually working with different connections (the
> ConnectionInterface arg would allow you to do different behaviors for
> different connections), this should do:
>
> MapperRules.createForeignKeys_? = c => true
>
> Derek
>

> > <liftweb%2Bunsu...@googlegroups.com<liftweb%252Buns...@googlegroups.com>

[Naftoli Gugenheim]

Personally I like to write that as

MapperRules.createForeignKeys_? = _ => true

[Strom]

Am I supposed to remove the call to MapperRules after the keys have
been created? It seems as though the createForeignKeys method is
creating FK constraints every time I restart my app. Not sure if this
is a bug, known issue, or what. I have duplicate foreign keys in my DB
from not commenting out the call.

On Jul 10, 10:14 pm, Naftoli Gugenheim <naftoli...@gmail.com> wrote:
> Personally I like to write that as

> MapperRules.createForeignKeys_? = *_* => true

>
> On Fri, Jul 9, 2010 at 6:56 PM, Derek Chen-Becker <dchenbec...@gmail.com>wrote:
>
> > Unless you're actually working with different connections (the
> > ConnectionInterface arg would allow you to do different behaviors for
> > different connections), this should do:
>
> > MapperRules.createForeignKeys_? = c => true
>
> > Derek
>

> >> <liftweb%2Bunsu...@googlegroups.com<liftweb%252Buns...@googlegroups.com>

[Naftoli Gugenheim]

As I mentioned earlier in the thread, I experienced it too. Actually I really hope H2 didn't create a zillion foreign keys by now! Something is apparently very wrong with Schemifier's detection of existing foreign keys, at least in H2 and PG.

To unsubscribe from this group, send email to liftweb+u...@googlegroups.com.

[Derek Chen-Becker]

Schemifier uses the JDBC DatabaseMetaData interface to query a DB for things like tables, columns, indices (Schemifier.ensureConstraints). With two different databases it could definitely be something in Schemifier, although it could also be a driver problem as well. I have a test project using H2 (but only a single instance, currently). Let me set up some FKs in it and see if I can track the issue down.

[Naftoli Gugenheim]

Thanks, I appreciate it. I wish I had the time to volunteer instead.

[Derek Chen-Becker]

Well, I'm not sure why, but the getExportedKeys method on MetaData isn't returning any rows for H2. I'm going to dig into this more tomorrow.

Derek

[Naftoli Gugenheim]

scala> res4.getMetaData.getExportedKeys(null,null, "REQUEST")

res21: java.sql.ResultSet = rs242155: columns: 14 rows: 84 pos: -1

scala> res4.getMetaData.getExportedKeys(null,"PUBLIC", "REQUEST")

res22: java.sql.ResultSet = rs242156: columns: 14 rows: 84 pos: -1

scala> res4.getMetaData.getExportedKeys(null,"PUBLIc", "REQUEST")

res23: java.sql.ResultSet = rs242157: columns: 14 rows: 0 pos: -1

scala> res4.getMetaData.getExportedKeys(null,"public", "REQUEST")

res24: java.sql.ResultSet = rs242158: columns: 14 rows: 0 pos: -1

scala> res4.getMetaData.getExportedKeys(null,"PUBLIC", "REQUESt")

res25: java.sql.ResultSet = rs242159: columns: 14 rows: 0 pos: -1

scala> res4.getMetaData.getExportedKeys(null,"PUBLIC", "request")

res26: java.sql.ResultSet = rs242160: columns: 14 rows: 0 pos: -1

Sounds like it's a bit case sensitive. Could that be the issue?

[Derek Chen-Becker]

That's what I thought, but I only tested against the table name:

scala> val keys = md.getExportedKeys(null, null, "stringitem")      
keys: java.sql.ResultSet = rs0: columns: 14 rows: 0 pos: -1

scala> val keys = md.getExportedKeys(null, null, "dateitem")
keys: java.sql.ResultSet = rs1: columns: 14 rows: 0 pos: -1

scala> val keys = md.getExportedKeys(null, null, "DateItem")
keys: java.sql.ResultSet = rs1: columns: 14 rows: 0 pos: -1

From your results, though, it definitely looks like case. I would guess that this is related to PG, as well. I seem to remember having some case-sensitivity issues in Mapper before, but it's been a long time.

Derek

[Naftoli Gugenheim]

It looks like it wants all caps.

[Derek Chen-Becker]

Yup, that appears to be how H2 is storing it inside the DB. Interestingly, using the table name inside a query is case-insensitive. The following queries all return the same data for me:

select * from DateItem;
select * from DATEITEM;
select * from DaTeItEm;

Schemifier creates the tables with lower-case names, so this may be something we have to work around in a driver-specific fashion:

CREATE TABLE dateitem (date_c DATE , time_c TIME , timestamp TIMESTAMP , id BIGINT NOT NULL AUTO_INCREMENT)

I'll have to check to see if PostgreSQL is doing something similar.

Derek

[Derek Chen-Becker]

Argh. I checked PostgreSQL and it looks like Schemifier may be using the wrong call to determine keys. From the API docs for getExportedKeys (emphasis mine):

Retrieves a description of the foreign key columns that reference the given table's primary key columns (the foreign keys exported by a table).

Compare that to getImportedKeys:

Retrieves a description of the primary key columns that are referenced by the given table's foreign key columns (the primary keys imported by a table)

Schemifier is checking the table that has the FK (as opposed to the table with the PK) using getExportedKeys, which from the docs seems to be backward. Checking with H2 and PostgreSQL, getImportedKeys returns the correct data. I wonder if other drivers (MySQL, MS SQL, etc) reverse the meaning. I've been looking at this code so much this morning that I'm to the point where it's starting to not make sense. I'm going to take a stab at reworking the code and I'll test against MySQL, PostgreSQL, H2 and Oracle.

Derek

[Derek Chen-Becker]

Actually, just testing on H2, changing to getImportedKeys fixes it without dealing with case.

[Naftoli Gugenheim]

All right! Thanks!

But could it still be an H2 bug?

Maybe I'm misunderstanding something, but given tables and fields (random setup)

A (id, b_id, c_id)

B (id, c_id)

C (id, a_id)

what's the difference if call an API that gives us

A -> [A.b_id, A.c_id]

B -> [B.c_id]

C -> [C.a_id]

or one that gives us

A -> [C.a_id]

B -> [A.b_id]

C -> [A.c_id, B.c_id]

after all, shouldn't we have all the same FKs?

[Derek Chen-Becker]

The problem is that we're only checking the table that holds the FK, so the method called is important. For example, in my test app, I have two entities:

DateItem
StringItem

StringItem has a "timestamp" field that references DateItem, but DateItem has no FK fields. Because DateItem holds no FK fields it doesn't even get checked.

Derek

[Strom]

Hey Derek,
Does the Postgres still have the FK issue, or did the fix you made for
H2 fix the others?

On Jul 13, 10:24 pm, Derek Chen-Becker <dchenbec...@gmail.com> wrote:
> The problem is that we're only checking the table that holds the FK, so the
> method called is important. For example, in my test app, I have two
> entities:
>
> DateItem
> StringItem
>
> StringItem has a "timestamp" field that references DateItem, but DateItem
> has no FK fields. Because DateItem holds no FK fields it doesn't even get
> checked.
>
> Derek
>

> On Tue, Jul 13, 2010 at 7:02 PM, Naftoli Gugenheim <naftoli...@gmail.com>wrote:
>
> > All right! Thanks!
> > But could it still be an H2 bug?
> > Maybe I'm misunderstanding something, but given tables and fields (random
> > setup)
> > A (id, b_id, c_id)
> > B (id, c_id)
> > C (id, a_id)
> > what's the difference if call an API that gives us
> > A -> [A.b_id, A.c_id]
> > B -> [B.c_id]
> > C -> [C.a_id]
> > or one that gives us
> > A -> [C.a_id]
> > B -> [A.b_id]
> > C -> [A.c_id, B.c_id]
> > after all, shouldn't we have all the same FKs?
>

> > On Tue, Jul 13, 2010 at 11:47 AM, Derek Chen-Becker <dchenbec...@gmail.com

> > > wrote:
>
> >> Actually, just testing on H2, changing to getImportedKeys fixes it without
> >> dealing with case.
>

> >> On Tue, Jul 13, 2010 at 9:36 AM, Derek Chen-Becker <dchenbec...@gmail.com

> >> > wrote:
>
> >>> Argh. I checked PostgreSQL and it looks like Schemifier may be using the
> >>> wrong call to determine keys. From the API docs for getExportedKeys
> >>> (emphasis mine):
>

> >>> Retrieves a description of the foreign key columns that reference the *given
> >>> table's primary key columns* (the foreign keys exported by a table).
>
> >>> Compare that to getImportedKeys:
>
> >>> Retrieves a description of the primary key columns that are * referenced
> >>> by the given table's foreign key columns* (the primary keys imported by

> ...
>
> read more »

[Derek Chen-Becker]

I haven't committed the fix yet because I wanted to discuss it with others first. Modifying schemifier at this level is a non-trivial change, so I want to be super careful here.

[Strom]

No worries, just thought I'd ask. Thanks for the update!

On Jul 27, 3:20 pm, Derek Chen-Becker <dchenbec...@gmail.com> wrote:
> I haven't committed the fix yet because I wanted to discuss it with others
> first. Modifying schemifier at this level is a non-trivial change, so I want
> to be super careful here.
>

> ...
>
> read more »