search

Roenbaeck / anchor

Anchor Modeler is a database modeling tool for creating database models that handles temporalization using the sixth normal form.
http://www.anchormodeling.com
Other
91 stars 24 forks source link

Issue with inserts on static attributes with assertiveness checked #52

Closed reidfrasier closed 9 years ago

reidfrasier commented 9 years ago

I am doing some unit testing on the features in general and in that case on assertiveness. Below I will provide some code to replicate this issue but first let me summarize my understanding of the assertiveness feature. I understand it to mean that when checked the annex table for a static or historized attribute will only allow an insert (or update) of a new (old) row if the positing time is different from the any previous or following positing time. And if it is unchecked, then the same condition holds with the additional constraint that the reliability must also be different in order for an insert or update to be recorded. Does that sound correct? If not please ignore the rest of the message and correct my understanding.

Many thanks and below is my XML schema and some code to unit test these inserts:

Here is the XML schema:

<?xml version="1.0" encoding="utf-8"?>
<schema format="0.99" date="2015-10-14" time="10:31:45">
  <metadata changingRange="datetime" encapsulation="dbo" identity="int" metadataPrefix="Metadata" metadataType="int" metadataUsage="false" changingSuffix="ChangedAt" identitySuffix="ID" positIdentity="int" positGenerator="true" positingRange="datetime" positingSuffix="PositedAt" positorRange="tinyint" positorSuffix="Positor" reliabilityRange="tinyint" reliabilitySuffix="Reliability" deleteReliability="0" assertionSuffix="Assertion" partitioning="false" entityIntegrity="true" restatability="false" idempotency="true" assertiveness="true" naming="improved" positSuffix="Posit" annexSuffix="Annex" chronon="datetime2(7)" now="sysdatetime()" dummySuffix="Dummy" versionSuffix="Version" statementTypeSuffix="StatementType" checksumSuffix="Checksum" businessViews="false" decisiveness="true" equivalence="false" equivalentSuffix="EQ" equivalentRange="tinyint" databaseTarget="SQLServer" temporalization="crt"/>
  <anchor mnemonic="HT" descriptor="HistorizedThing" identity="int">
    <metadata capsule="dbo" generator="true"/>
    <attribute mnemonic="NRN" descriptor="NRNIA" identity="int" timeRange="datetime" dataRange="int">
      <metadata capsule="dbo" generator="true" assertive="true" restatable="false" idempotent="false"/>
      <layout x="943.00" y="570.00" fixed="false"/>
    </attribute>
    <attribute mnemonic="NRI" descriptor="NRIA" identity="int" timeRange="datetime" dataRange="int">
      <metadata capsule="dbo" generator="true" assertive="true" restatable="false" idempotent="true"/>
      <layout x="822.91" y="591.04" fixed="false"/>
    </attribute>
    <attribute mnemonic="RIA" descriptor="RIA" identity="int" timeRange="datetime" dataRange="int">
      <metadata capsule="dbo" generator="true" assertive="true" restatable="true" idempotent="true"/>
      <layout x="896.70" y="699.18" fixed="false"/>
    </attribute>
    <attribute mnemonic="RNI" descriptor="RNIA" identity="int" timeRange="datetime" dataRange="int">
      <metadata capsule="dbo" generator="true" assertive="true" restatable="true" idempotent="false"/>
      <layout x="855.31" y="556.96" fixed="false"/>
    </attribute>
    <layout x="894.53" y="602.37" fixed="false"/>
  </anchor>
  <anchor mnemonic="ST" descriptor="StaticThing" identity="int">
    <metadata capsule="dbo" generator="true"/>
    <attribute mnemonic="AXX" descriptor="A" identity="int" dataRange="int">
      <metadata capsule="dbo" generator="true" assertive="true"/>
      <layout x="1993.65" y="231.89" fixed="false"/>
    </attribute>
    <attribute mnemonic="NAX" descriptor="NA" identity="int" dataRange="int">
      <metadata capsule="dbo" generator="true" assertive="false"/>
      <layout x="2074.89" y="306.36" fixed="false"/>
    </attribute>
    <layout x="2011.71" y="299.09" fixed="false"/>
  </anchor>
</schema>

Here are the inserts:

------------------------------------
-- test assertiveness on latest view
------------------------------------

-- Insert an initial value for the assertive attribute 'A' into the latest view for 'ST_StaticThing'.
INSERT INTO [dbo].[lST_StaticThing] (Positor,   ST_AXX_PositedAt,   ST_AXX_Reliability, ST_AXX_StaticThing_A) 
VALUES                              (0,         '2015-10-14',       100,                1000);

-- Insert the same value for the assertive attribute 'A' which has different positing time into the latest view for 'ST_StaticThing'.
-- The expected behavior is that the insert should succeed without any errors.
INSERT INTO [dbo].[lST_StaticThing] (Positor,   ST_AXX_PositedAt,   ST_AXX_Reliability, ST_AXX_StaticThing_A) 
VALUES                              (0,         '2015-10-15',       100,                1000);

-- Insert the same value for the assertive attribute 'A' which now has the same positing time into the latest view for 'ST_StaticThing'.
-- The expected behavior is that the insert should return errors because the positing time is the same as another posit and so the annex table
-- will not be updated.
INSERT INTO [dbo].[lST_StaticThing] (Positor,   ST_AXX_PositedAt,   ST_AXX_Reliability, ST_AXX_StaticThing_A) 
VALUES                              (0,         '2015-10-15',       100,                1000);
Roenbaeck commented 9 years ago

Just a heads up, but you are correct in your assumptions. Assertiveness allows you to reassert the reliability that a positor holds towards a posit, even if the reliability has not changed. Disallowing this should require a revaluation of the reliability (a different one) or the insert will be ignored. I am going to look into this now.

Roenbaeck commented 9 years ago

As it turned out, your usage of the insert trigger was not correct. However, when I corrected the script I found another bug nevertheless, which has now been fixed. See the corrected script below, which now works in the test version:

------------------------------------
-- test assertiveness on latest view
------------------------------------

-- Insert an initial value for the assertive attribute 'A' into the latest view for 'ST_StaticThing'.
INSERT INTO [dbo].[lST_StaticThing] (Positor,   ST_AXX_PositedAt,   ST_AXX_Reliability, ST_AXX_StaticThing_A) 
VALUES                              (0,         '2015-10-14',       100,                1000);

-- The statement above will now have created an instance with ST_ID = 1, this need to be referred to in the 
-- statments below, or new instances will be created for each of them.
SELECT * FROM [dbo].[lST_StaticThing]

-- Insert the same value for the assertive attribute 'A' which has different positing time into the latest view for 'ST_StaticThing'.
-- The expected behavior is that the insert should succeed without any errors.
INSERT INTO [dbo].[lST_StaticThing] (ST_ID,     Positor,   ST_AXX_PositedAt,   ST_AXX_Reliability, ST_AXX_StaticThing_A) 
VALUES                              (1,         0,         '2015-10-15',       100,                1000);

-- The column ST_AXX_ST_ID indicates that we are talking about the same instance. The column ST_AXX_ID indicates that we are also 
-- talking about the same posit (again).
SELECT * FROM [dbo].[ST_AXX_StaticThing_A];

-- Insert the same value for the assertive attribute 'A' which now has the same positing time into the latest view for 'ST_StaticThing'.
-- The expected behavior is that the insert should return errors because the positing time is the same as another posit and so the annex table
-- will not be updated.
INSERT INTO [dbo].[lST_StaticThing] (ST_ID,     Positor,   ST_AXX_PositedAt,   ST_AXX_Reliability, ST_AXX_StaticThing_A) 
VALUES                              (1,         0,         '2015-10-15',       100,                1000);