SQL generation bug on 3-ary tie table

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL generation bug on 3-ary tie table

Tim Schiettekatte
Generate SQL code from the Online Modeller generates one comma too many for a 3-ary tie create table script. As workaround I can deletee the extra comma by hand in 2 places, which lets me create the Anchor Model database schema.

------------------------------------- [Tie Table] ------------------------------------
-- DE_has_DA_deliverydate_DA_salesdate table (3-ary)
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'DE_has_DA_deliverydate_DA_salesdate' and type LIKE '%U%')
CREATE TABLE [dbo].[DE_has_DA_deliverydate_DA_salesdate] (
   DE_ID_has int not null foreign key references [dbo].[DE_Delivery](DE_ID),
   DA_ID_deliverydate int not null foreign key references [dbo].[DA_Date](DA_ID),
   DA_ID_salesdate int not null foreign key references [dbo].[DA_Date](DA_ID),
   Metadata_DE_has_DA_deliverydate_DA_salesdate int not null,
   constraint pkDE_has_DA_deliverydate_DA_salesdate primary key (
      DE_ID_has asc
   ),
   unique (
      DA_ID_deliverydate asc
   ),
,
   unique (
      DA_ID_salesdate asc
   )
) ON [PRIMARY];
GO

Maybe this is already fixed in the new testversion.

Kind regards,
Tim
Reply | Threaded
Open this post in threaded view
|

Re: SQL generation bug on 3-ary tie table

roenbaeck
Administrator
I tried to reproduce the bug with the following schema:

<schema>
<metadata format="0.97" temporalization="uni" date="2013-11-29" time="11:01:01"/>
<anchor mnemonic="DE" descriptor="Delivery" identity="int">
<metadata capsule="dbo" generator="true"/>
<layout x="707.11" y="380.08" fixed="false"/>
</anchor>
<anchor mnemonic="DA" descriptor="Date" identity="int">
<metadata capsule="dbo" generator="true"/>
<layout x="727.00" y="267.00" fixed="false"/>
</anchor>
<tie>
<anchorRole role="has" type="DE" identifier="true"/>
<anchorRole role="salesdate" type="DA" identifier="false"/>
<anchorRole role="deliverydate" type="DA" identifier="false"/>
<metadata capsule="dbo"/>
<layout x="767.00" y="335.00" fixed="true"/>
</tie>
</schema>

There are no extra commas in the test version. Is this the way the tie looks, with respect to cardinalities? If so, this bug will get little priority since we are moving the test version to stable soon (we already have one DW in production using that code base).
Reply | Threaded
Open this post in threaded view
|

Re: SQL generation bug on 3-ary tie table

Tim Schiettekatte
Hi Lars,

These 2 triary ties generate the extra comma in the SQL script. Both triary ties have 1 as multiplicity between the roles, representing 1:1:1 relationships.

<tie>
<anchorRole role="has" type="DE" identifier="false"/>
<anchorRole role="deliverydate" type="DA" identifier="false"/>
<anchorRole role="salesdate" type="DA" identifier="false"/>
<metadata capsule="dbo"/>
<layout x="460.25" y="563.05" fixed="true"/>
</tie>

<tie>
<anchorRole role="target" type="DE" identifier="false"/>
<anchorRole role="source" type="DE" identifier="false"/>
<anchorRole role="incomingBatch" type="BA" identifier="false"/>
<metadata capsule="dbo"/>
<layout x="412.25" y="371.55" fixed="true"/>
</tie>

Does this comply with your testcase?

Best regards,
Tim