search

google-code-export / dblinq2007

Automatically exported from code.google.com/p/dblinq2007
Other
0 stars 0 forks source link

Bug in association creation when there are 2 foreign keys from/to the same databases #125

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago 
If you have 2 tables with 2 foreign keys between the same two db's, it 
seems to create an invalid DBML which is not recognized by VS.

An example:

Table1: Order
ShippingUserID (foreign key into User table)
BillingUserID (foreign key into User table)

Table2: User
ID

The code that gets generated is as such:

<Association Name="User_Order1" Member="User" Storage="_user" 
ThisKey="ShippingUserID" OtherKey="ID" Type="User" IsForeignKey="true" />
      <Association Name="User_Order" Member="User" Storage="_user" 
ThisKey="BillingUserID" OtherKey="ID" Type="User" IsForeignKey="true" />

What *should* be generated (I believe, the difference is in the Member 
attribute):

<Association Name="User_Order1" Member="User" Storage="_user" 
ThisKey="ShippingUserID" OtherKey="ID" Type="User" IsForeignKey="true" />
      <Association Name="User_Order" Member="User1" Storage="_user" 
ThisKey="BillingUserID" OtherKey="ID" Type="User" IsForeignKey="true" />

Original issue reported on code.google.com by admin%yu...@gtempaccount.com on 15 Apr 2009 at 10:36

GoogleCodeExporter commented 9 years ago

Original comment by jonmpr...@gmail.com on 16 Jan 2010 at 4:28

GoogleCodeExporter commented 9 years ago 
As far as I see this problem has solved out but if a table has more than 2 
foreigh 
keys to same table again problem occurs.

An example

<Type Name="AirPort">
  <Association Name="fk_FlightPlans_Airports_PlanDeparture" Member="FlightPlans" 
Storage="_flightPlans" Type="FlightPlan" ThisKey="AirPortId" 
OtherKey="PlanDepartureId" Cardinality="One" DeleteRule="NO ACTION" />
  <Association Name="fk_FlightPlans_Airports_PlanDestination" Member="FlightPlans1" 
Storage="_flightPlans1" Type="FlightPlan" ThisKey="AirPortId" 
OtherKey="PlanDestinationId" Cardinality="One" DeleteRule="NO ACTION" />
  <Association Name="fk_FlightPlans_Airports_FlightDestination" Member="FlightPlans1" 
Storage="_flightPlans1" Type="FlightPlan" ThisKey="AirPortId" 
OtherKey="FlightDestinationId" Cardinality="One" DeleteRule="NO ACTION" />
  <Association Name="fk_FlightPlans_Airports_FlightDepature" Member="FlightPlans1" 
Storage="_flightPlans1" Type="FlightPlan" ThisKey="AirPortId" 
OtherKey="FlightDepartureId" Cardinality="One" DeleteRule="NO ACTION" />
  <Column Name="AirPortId" Member="AirPortId" Storage="_airPortId" 
Type="System.Int32" DbType="integer(32,0)" IsPrimaryKey="true" 
IsDbGenerated="true" 
CanBeNull="false" Expression="nextval('"AirPorts_AirPortId_seq"')" />
  <Column Name="AirPortName" Member="AirPortName" Storage="_airPortName" 
Type="System.String" DbType="character varying(255)" IsDbGenerated="false" 
CanBeNull="false" />
</Type>

As you can recognize there are more tha one "FlightPlans1" member.

I have changed the file "schemaloader.name.cs" file

before edit, code likes

int storageSuffix = 0;
if ( storageFields.TryGetValue(association.Storage, out storageSuffix) )
  association.Storage += storageSuffix;
storageFields[association.Storage] = storageSuffix + 1;

int memberSuffix = 0;
if ( memberFields.TryGetValue(association.Member, out memberSuffix) )
  association.Member += memberSuffix;
memberFields[association.Member] = memberSuffix + 1;

after edit code likes

if (!storageFields.ContainsKey(association.Storage)) 
storageFields.Add(association.Storage, -1);
storageFields[association.Storage] = storageFields[association.Storage] + 1;

int storageSuffix = storageFields[association.Storage];
association.Storage += (storageSuffix == 0 ? "" : storageSuffix.ToString());

if (!memberFields.ContainsKey(association.Member)) 
memberFields.Add(association.Member, -1);
memberFields[association.Member] = memberFields[association.Member] + 1;

int memberSuffix = memberFields[association.Member];
association.Member += (memberSuffix == 0 ? "" : memberSuffix.ToString());

Original comment by sese...@gmail.com on 28 Jan 2010 at 8:14

GoogleCodeExporter commented 9 years ago 
I believe this was fixed in r1309 (which fixed issue 196).

My reproduction case: take the following SQL:

-- SQLite SQL Start: (in i125.sql)
CREATE TABLE [Users](
    [ID] INTEGER NOT NULL, 
    [Name] VARCHAR(20),
    CONSTRAINT "PK_Users" PRIMARY KEY ("ID")
);
CREATE TABLE "Orders" (
    ID INTEGER NOT NULL, 
    ShippingUserID INTEGER NULL, 
    BillingUserID INTEGER NULL, 
    SomeOtherUserID INTEGER NULL, 
    YetAnotherUserID INTEGER NULL, 
    JustToRoundThingsOutUserID INTEGER NULL, 

    CONSTRAINT "PK_Orders" PRIMARY KEY ("ID"),
    CONSTRAINT "FK_Orders_ShippingUserID" FOREIGN KEY ([ShippingUserID]) 
REFERENCES [Users] ([ID]),
    CONSTRAINT "FK_Orders_BillingUserID" FOREIGN KEY ([BillingUserID]) REFERENCES 
[Users] ([ID]),
    CONSTRAINT "FK_Orders_SomeOtherUserID" FOREIGN KEY ([SomeOtherUserID]) 
REFERENCES [Users] ([ID]),
    CONSTRAINT "FK_Orders_YetAnotherUserID" FOREIGN KEY ([YetAnotherUserID]) 
REFERENCES [Users] ([ID]),
    CONSTRAINT "FK_Orders_JustToRoundThingsOutUserID" FOREIGN KEY 
([JustToRoundThingsOutUserID]) REFERENCES [Users] ([ID])
);
-- SQLite SQL End

So that creates a table Orders which has 5 FK references to the Users.ID column.

Generate the .db3 file:

$ sqlite3 i125.db3
sqlite> .read i125.sql
sqlite> .quit

Generate the .dbml file:

$ DbMetal /provider:Sqlite /dbml:i125.dbml /conn "Data Source=i125.db3"

The generated i125.dbml file contains:

  <Table Name="main.Orders" Member="Orders">
    <Type Name="Orders">
      <Association Name="fk_Orders_0" Member="Users" Storage="_users" Type="Users" 
ThisKey="JustToRoundThingsOutUserID" OtherKey="ID" IsForeignKey="true" 
Cardinality="Many" />
      <Association Name="fk_Orders_1" Member="Users1" Storage="_users1" Type="Users" 
ThisKey="YetAnotherUserID" OtherKey="ID" IsForeignKey="true" Cardinality="Many" 
/>
      <Association Name="fk_Orders_2" Member="Users2" Storage="_users2" Type="Users" 
ThisKey="SomeOtherUserID" OtherKey="ID" IsForeignKey="true" Cardinality="Many" 
/>
      <Association Name="fk_Orders_3" Member="Users3" Storage="_users3" Type="Users" 
ThisKey="BillingUserID" OtherKey="ID" IsForeignKey="true" Cardinality="Many" />
      <Association Name="fk_Orders_4" Member="Users4" Storage="_users4" Type="Users" 
ThisKey="ShippingUserID" OtherKey="ID" IsForeignKey="true" Cardinality="Many" />
      <Column Name="BillingUserID" Member="BillingUserID" Storage="_billingUserID" 
Type="System.Int32" DbType="INTEGER" IsPrimaryKey="false" IsDbGenerated="false" 
CanBeNull="true" />
      <Column Name="ID" Member="ID" Storage="_id" Type="System.Int32" 
DbType="INTEGER" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
      <Column Name="JustToRoundThingsOutUserID" Member="JustToRoundThingsOutUserID" 
Storage="_justToRoundThingsOutUserID" Type="System.Int32" DbType="INTEGER" 
IsPrimaryKey="false" IsDbGenerated="false" CanBeNull="true" />
      <Column Name="ShippingUserID" Member="ShippingUserID" Storage="_shippingUserID" 
Type="System.Int32" DbType="INTEGER" IsPrimaryKey="false" IsDbGenerated="false" 
CanBeNull="true" />
      <Column Name="SomeOtherUserID" Member="SomeOtherUserID" 
Storage="_someOtherUserID" Type="System.Int32" DbType="INTEGER" 
IsPrimaryKey="false" 
IsDbGenerated="false" CanBeNull="true" />
      <Column Name="YetAnotherUserID" Member="YetAnotherUserID" 
Storage="_yetAnotherUserID" Type="System.Int32" DbType="INTEGER" 
IsPrimaryKey="false" 
IsDbGenerated="false" CanBeNull="true" />
    </Type>
  </Table>

Furthermore, I can load i125.dbml into VS2008 without encountering an error.

Original comment by jonmpr...@gmail.com on 12 Mar 2010 at 4:59

GoogleCodeExporter commented 9 years ago 
i have experienced the exact same problem in ver 0.20.1

bool ValidateAssociations(Database database, Table table) fails to validate the 
type of association described above.

the culprit is 
"var otherAssociation = otherType.Associations.First(a => a.Type == 
table.Type.Name && a.ThisKey == association.OtherKey);"
to be precise.

Original comment by NeilNaid...@gmail.com on 27 Sep 2010 at 1:41