Duplicated and incorrect mapping using composite foreign key

[GoogleCodeExporter]

What steps will reproduce the problem?
1.  A schema with composite foreign key
2.  The Foreign key serves as the primary key in the foreign table
3.

What is the expected output? What do you see instead?

Was expecting mapping with each key in the composite keys identified as such 
but the mapping file duplicate the first key though it maps to the right 
relation.

What version of the product are you using?

Am using codesmith 6.5, PLINQ Nhibernate 1.2

Please provide any additional information below.
Am using postgres database, having tried with MS SQL though.

I have attached a sample schema and the resultant map file  for the School 
table in the schema.

Please how do i overcome this problem.
Thanks

Original issue reported on code.google.com by danielkw...@gmail.com on 14 Jul 2012 at 4:19

Attachments:

• testdb.sql
• sample_school_map.txt

[GoogleCodeExporter]

Hello,

I narrowed this down to a bug in the Postgres Schema Provider where the 
referencing keys are returning duplicate + incorrect results. If you run the 
query below, you can see that 4 records are returned when it should only be 2.

Is there any way you could take a look at the following query and see if you 
can get it to return the correct results?

-- SchemaProvider implementation..
SELECT px.conname as constrname FROM pg_constraint px left join pg_class fore 
on fore.oid = px.confrelid where fore.relname = 'school'
go
SELECT px.conname as constrname, fatt.attname as colname, home.relname as 
reftabname, att.attname as refcolname, 
CASE px.confupdtype WHEN 'a' THEN 'NO ACTION' WHEN 'r' THEN 'RESTRICT' WHEN 'c' 
THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' END AS 
on_update, 
CASE px.confdeltype WHEN 'a' THEN 'NO ACTION' WHEN 'r' THEN 'RESTRICT' WHEN 'c' 
THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' END AS 
on_delete, 
CASE px.contype WHEN 'p' THEN true WHEN 'f' THEN false END as IsPrimaryKey 
from pg_constraint px left join pg_class home on (home.oid = px.conrelid) left 
join pg_class fore on (fore.oid = px.confrelid) left join pg_attribute att on 
(att.attrelid = px.conrelid AND att.attnum = ANY(px.conkey)) left join 
pg_attribute fatt on (fatt.attrelid = px.confrelid AND fatt.attnum = 
ANY(px.confkey)) where (fore.relname = 'school') order by constrname
go

-- A second query I was playing with to try and get back the correct data.
SELECT rc.constraint_name as constrname,
       ccu.column_name as colname,
       kcu.table_name as reftabname,
       kcu.column_name as refcolname,
       update_rule AS on_update,
       delete_rule AS on_delete,
       CASE tc.constraint_type WHEN 'FOREIGN KEY' THEN false WHEN 'PRIMARY KEY' THEN true END as IsPrimaryKey 
FROM information_schema.referential_constraints AS rc
    JOIN information_schema.table_constraints AS tc USING(constraint_catalog,constraint_schema,constraint_name)
    JOIN information_schema.key_column_usage AS kcu USING(constraint_catalog,constraint_schema,constraint_name)
    JOIN information_schema.key_column_usage AS ccu ON(ccu.constraint_catalog=rc.unique_constraint_catalog AND ccu.constraint_schema=rc.unique_constraint_schema AND ccu.constraint_name=rc.unique_constraint_name)
where ccu.table_name = 'school'

Original comment by bniemyjski on 16 Jul 2012 at 9:27

[GoogleCodeExporter]

Original comment by bniemyjski on 23 Aug 2012 at 3:42

• Changed state: AwaitingInfo
• Added labels: Framework-NHibernate