The Iceberg Exercise

/*
    Lars Rönnbäck
    [email protected]

    HISTORY
    2015-09-16 Created the script.
    2015-09-20 Added uni- and bi-temporally independent
               and dependent joins.
    2015-10-02 Updated to show positive assertions.
               Added section on indecisiveness.


-------------------------------------------------------
    Create two different positors that may differ
    in opinions about icebergs.
    Note that a 'default' positor has already
    been created with identity 0.
-------------------------------------------------------
*/
insert into _Positor
values (1);

/*
-------------------------------------------------------
    Populate the knot values.
-------------------------------------------------------
*/
insert into ATT_Attachment (
    ATT_ID, ATT_Attachment
) values
(0, 'Not attached'),
(1, 'Attached');

insert into FRZ_Frozen (
    FRZ_ID, FRZ_Frozen
) values
(0, 'Not frozen'),
(1, 'Frozen');

insert into MIA_MissingInAction (
    MIA_ID, MIA_MissingInAction
) values
(0, 'Missing in action'),
(1, 'Known whereabouts');

insert into SOM_SplitOrMerge (
    SOM_ID, SOM_SplitOrMerge
) values
(1, 'Split'),
(2, 'Merge');

insert into SUB_Submerged (
    SUB_ID, SUB_Submerged
) values
(0, 'Not submerged'),
(1, 'Submerged');

insert into EOE_EnteredOrExited (
    EOE_ID, EOE_EnteredOrExited
) values
(0, 'Exited'),
(1, 'Entered');

/*
-------------------------------------------------------
    Insert a signal from one of the transmitters.
    Resulting in a TR_ID = 1 being created.
-------------------------------------------------------
*/
insert into lTR_Transmitter (
    Positor,
    TR_LOC_ChangedAt, TR_LOC_PositedAt, TR_LOC_Reliability, TR_LOC_Transmitter_Location,
    TR_MIA_ChangedAt, TR_MIA_PositedAt, TR_MIA_Reliability, TR_MIA_MIA_MissingInAction,
                      TR_FSG_PositedAt, TR_FSG_Reliability, TR_FSG_Transmitter_FirstSignal,
    TR_LSG_ChangedAt, TR_LSG_PositedAt, TR_LSG_Reliability, TR_LSG_Transmitter_LastSignal
)
values (
    0,
    '1912-04-15', '2015-01-01',  50, geography::STPointFromText('POINT(41.7325 49.9469)', 4326),
    '1912-04-15', '2015-01-01', 100, 'Known whereabouts',
                  '2015-01-01', 100, '1912-04-01',
    '1912-04-15', '2015-01-01', 100, '1912-04-15 23:40'
);

/*
-------------------------------------------------------
    Update, since we got another signal, but the
    location remained the same.
    Only historized attributes can be updated.
-------------------------------------------------------
*/
update lTR_Transmitter
set
    TR_LSG_ChangedAt = '1912-04-16',
    TR_LSG_PositedAt = '2015-01-01',
    TR_LSG_Reliability = 80,
    TR_LSG_Transmitter_LastSignal = '1912-04-16 01:01'
where
    TR_ID = 1
and
    Positor = 0;

/*
-------------------------------------------------------
    Check the data.
    The last signal should be different between the
    two days.
-------------------------------------------------------
*/
select TR_LSG_Transmitter_LastSignal from pTR_Transmitter('1912-04-15') where Positor = 0 and TR_ID = 1;
select TR_LSG_Transmitter_LastSignal from pTR_Transmitter('1912-04-16') where Positor = 0 and TR_ID = 1;

/*
-------------------------------------------------------
    Correct, that the first signal was in fact
    received a day later.
    All attributes can be corrected.
    In order to retract an assertion a new assertion
    is made on the same posit but with zero
    reliability.
-------------------------------------------------------
*/
insert into lTR_Transmitter (
    Positor, TR_ID,
    TR_FSG_PositedAt, TR_FSG_Reliability, TR_FSG_Transmitter_FirstSignal
)
values (
    0, 1,
   '2015-01-02',  0, '1912-04-01' -- set reliability to zero
), (
    0, 1,
   '2015-01-02', 80, '1912-04-02' -- assert another posit
);

/*
-------------------------------------------------------
    Check the data.
    The default positor should have made no assertion
    about the first signal before 2015, and was 100%
    sure about the signal on the first, but retracted
    that and is 80% sure of another posit on the
    second of February, 2015.
-------------------------------------------------------
*/
select TR_FSG_Transmitter_FirstSignal from tTR_Transmitter(0, DEFAULT, '2014-12-31', '+') where TR_ID = 1;
select TR_FSG_Transmitter_FirstSignal from tTR_Transmitter(0, DEFAULT, '2015-01-01', '+') where TR_ID = 1;
select TR_FSG_Transmitter_FirstSignal from tTR_Transmitter(0, DEFAULT, '2015-01-02', '+') where TR_ID = 1;

/*
-------------------------------------------------------
    Insert an iceberg adrift.
    Resulting in a IB_ID = 1 being created.
-------------------------------------------------------
*/
insert into lIB_Iceberg (
    Positor,
                      IB_FDA_PositedAt, IB_FDA_Reliability, IB_FDA_Iceberg_FormationDate,
    IB_FRZ_ChangedAt, IB_FRZ_PositedAt, IB_FRZ_Reliability, IB_FRZ_FRZ_Frozen,
    IB_LOC_ChangedAt, IB_LOC_PositedAt, IB_LOC_Reliability, IB_LOC_Iceberg_Location,
    IB_SUB_ChangedAt, IB_SUB_PositedAt, IB_SUB_Reliability, IB_SUB_SUB_Submerged,
    IB_SIZ_ChangedAt, IB_SIZ_PositedAt, IB_SIZ_Reliability, IB_SIZ_Iceberg_Size,
                      IB_DDA_PositedAt, IB_DDA_Reliability, IB_DDA_Iceberg_DisintegrationDate
)
values (
    0,
                  '2015-01-01', 100, '1912-02-13',
    '1912-02-13', '2015-01-01', 100, 'Not frozen',
    '1912-04-15', '2015-01-01',  50, geography::STPointFromText('POINT(42.7325 49.9469)', 4326),
    '1912-02-13', '2015-01-01', 100, 'Not submerged',
    '1912-02-13', '2015-01-01',  50, 555,
                  '2015-01-01', 100, null -- not yet disintegrated
);

/*
-------------------------------------------------------
    Let another positor (1, a boat) have a different
    opinion about the whereabouts of the iceberg.
    Note that this must use IB_ID = 1 to refer to
    the same iceberg.
-------------------------------------------------------
*/
insert into lIB_Iceberg (
    Positor, IB_ID,
                      IB_FDA_PositedAt, IB_FDA_Reliability, IB_FDA_Iceberg_FormationDate,
    IB_FRZ_ChangedAt, IB_FRZ_PositedAt, IB_FRZ_Reliability, IB_FRZ_FRZ_Frozen,
    IB_LOC_ChangedAt, IB_LOC_PositedAt, IB_LOC_Reliability, IB_LOC_Iceberg_Location,
    IB_SUB_ChangedAt, IB_SUB_PositedAt, IB_SUB_Reliability, IB_SUB_SUB_Submerged,
    IB_SIZ_ChangedAt, IB_SIZ_PositedAt, IB_SIZ_Reliability, IB_SIZ_Iceberg_Size,
                      IB_DDA_PositedAt, IB_DDA_Reliability, IB_DDA_Iceberg_DisintegrationDate
)
values (
    1, 1,
                  '2015-01-01', 100, '1912-02-13',
    '1912-02-13', '2015-01-01', 100, 'Frozen', -- the boat claims the iceberg is stuck at another location
    '1912-04-15', '2015-01-01',  50, geography::STPointFromText('POINT(41.7325 49.9469)', 4326),
    '1912-02-13', '2015-01-01', 100, 'Not submerged',
    '1912-02-13', '2015-01-01',  50, 911, -- and quite much larger
                  '2015-01-01', 100, null -- not yet disintegrated
);

/*
-------------------------------------------------------
    Check the data.
    The positors differ in their opinion about the
    size of the iceberg, whether it is frozen or not,
    and its location.
-------------------------------------------------------
*/
select
    Positor,
    IB_SIZ_Iceberg_Size,
    IB_FRZ_FRZ_Frozen,
    IB_LOC_Iceberg_Location.STBuffer(IB_SIZ_Iceberg_Size)
from
    lIB_Iceberg where IB_ID = 1;

/*
-------------------------------------------------------
    Connect the transmitter to the iceberg.
-------------------------------------------------------
*/
insert into lIB_to_TR_attached_ATT_currently (
    Positor,
    IB_to_TR_attached_ATT_currently_ChangedAt,
    IB_to_TR_attached_ATT_currently_PositedAt,
    IB_to_TR_attached_ATT_currently_Reliability,
    IB_ID_to,
    TR_ID_attached,
    currently_ATT_Attachment
) values (
    0,
    '1912-02-13',
    '2015-01-01',
    100,
    1, -- IB_ID = 1 (our only iceberg)
    1, -- TR_ID = 1 (our only transmitter)
    'Attached'
);

/*
-------------------------------------------------------
    Check the data.
    Perform a join over the latest available data in
    the two iceberg and transmitter anchors adjoined
    by the tie modeling the attachment relationship.
    Note that the boat (positor 1) has no concept
    of attached transmitters.

    Temporally this corresponds to a join in a
    regular non-temporal database, which can only
    ever hold the latest available information.
-------------------------------------------------------
*/
select
    *
from
    lIB_Iceberg ib
left join
    lIB_to_TR_attached_ATT_currently ibtr
on
    ibtr.IB_ID_to = ib.IB_ID
and
    ibtr.Positor = ib.Positor
left join
    lTR_Transmitter tr
on
    tr.TR_ID = ibtr.TR_ID_attached
and
    tr.Positor = ibtr.Positor
where
    ib.IB_ID = 1;

/*
-------------------------------------------------------
    Check the data.
    Change to a temporally _independent_ join, a join
    in which a temporal "snapshot" of each
    participating _table_ can be eastablished _before_
    joining the rows.

    The query below answers:
    Where were the iceberg and attached transmitter on
    the 13th of February 1912?

    Most use-cases of temporal data are covered by
    this type of join.
-------------------------------------------------------
*/
select
    *
from
    pIB_Iceberg('1912-02-13') ib
left join
    pIB_to_TR_attached_ATT_currently('1912-02-13') ibtr
on
    ibtr.IB_ID_to = ib.IB_ID
and
    ibtr.Positor = ib.Positor
left join
    pTR_Transmitter('1912-02-13') tr
on
    tr.TR_ID = ibtr.TR_ID_attached
and
    tr.Positor = ibtr.Positor
where
    ib.IB_ID = 1;

/*
-------------------------------------------------------
    Check the data.
    Change to a temporally _dependent_ join, a join
    in which a temporal "snapshot" of each
    participating _instance_ must be eastablished
    _during _ the join.

    The query below answers:
    Where were the iceberg and attached transmitter at
    the time of formation?

    All use-cases of joins can be expressed through
    temporally independent or dependent joins.
-------------------------------------------------------
*/
select
    *
from
    IB_FDA_Iceberg_FormationDate fda
cross apply (
    select
        *
    from
        pIB_Iceberg(fda.IB_FDA_Iceberg_FormationDate) ib
    where
        ib.IB_ID = fda.IB_FDA_IB_ID
    and
        ib.Positor = fda.IB_FDA_Positor
) ib
outer apply (
    select
        *
    from
        pIB_to_TR_attached_ATT_currently(fda.IB_FDA_Iceberg_FormationDate) ibtr
    where
        ibtr.IB_ID_to = ib.IB_ID
    and
        ibtr.Positor = ib.Positor
) ibtr
outer apply (
    select
        *
    from
        pTR_Transmitter(fda.IB_FDA_Iceberg_FormationDate) tr
    where
        tr.TR_ID = ibtr.TR_ID_attached
    and
        tr.Positor = ibtr.Positor
) tr
where
    ib.IB_ID = 1;

/*
-------------------------------------------------------
    Check the data.
    A bi-temporally dependent/independent join, with
    the same conditions as in the join above.

    Note that changing time has a temporally dependent
    join and that positing time is independent. Since
    positing time is independent it is possible to
    create a variable that holds the positing time
    we are interested int.
-------------------------------------------------------
*/
declare @positingTimepoint date = '2015-01-01';

select
    *
from
    IB_FDA_Iceberg_FormationDate fda
cross apply (
    select
        *
    from
        tIB_Iceberg(fda.IB_FDA_Positor, fda.IB_FDA_Iceberg_FormationDate, @positingTimepoint, '+') ib
    where
        ib.IB_ID = fda.IB_FDA_IB_ID
) ib
outer apply (
    select
        *
    from
        tIB_to_TR_attached_ATT_currently(ib.IB_FDA_Positor, fda.IB_FDA_Iceberg_FormationDate, @positingTimepoint, '+') ibtr
    where
        ibtr.IB_ID_to = ib.IB_ID
) ibtr
outer apply (
    select
        *
    from
        tTR_Transmitter(ib.IB_FDA_Positor, fda.IB_FDA_Iceberg_FormationDate, @positingTimepoint, '+') tr
    where
        tr.TR_ID = ibtr.TR_ID_attached
) tr
where
    ib.IB_ID = 1;

/*
-------------------------------------------------------
    Check the data.
    A bi-temporally dependent/dependent join.

    The added condition is that we want to see the
    information as we knew it at time of when we
    knew the formation date, for each instance.
-------------------------------------------------------
*/
select
    *
from
    IB_FDA_Iceberg_FormationDate fda
cross apply (
    select
        *
    from
        tIB_Iceberg(fda.IB_FDA_Positor, fda.IB_FDA_Iceberg_FormationDate, fda.IB_FDA_PositedAt, '+') ib
    where
        ib.IB_ID = fda.IB_FDA_IB_ID
) ib
outer apply (
    select
        *
    from
        tIB_to_TR_attached_ATT_currently(ib.IB_FDA_Positor, fda.IB_FDA_Iceberg_FormationDate, fda.IB_FDA_PositedAt, '+') ibtr
    where
        ibtr.IB_ID_to = ib.IB_ID
) ibtr
outer apply (
    select
        *
    from
        tTR_Transmitter(ib.IB_FDA_Positor, fda.IB_FDA_Iceberg_FormationDate, fda.IB_FDA_PositedAt, '+') tr
    where
        tr.TR_ID = ibtr.TR_ID_attached
) tr
where
    ib.IB_ID = 1;


/*
-------------------------------------------------------
    Update of a static attribute is not done
    using an update-statement, since being a
    correction it involves both a retraction and
    a new assertion.

    Note the resulting warning message.
-------------------------------------------------------
*/
update lTR_Transmitter
set
    TR_FSG_Transmitter_FirstSignal = '1912-01-31'
where
    TR_ID = 1;


/*
-------------------------------------------------------
    Change the reliability of an existing posit.
    Sometimes the degree of belief in a posit will
    change over time. In this case the belief is
    lowered from 80 (previous value) to 10.
-------------------------------------------------------
*/
insert into lTR_Transmitter (
    Positor, TR_ID,
    TR_FSG_PositedAt, TR_FSG_Reliability, TR_FSG_Transmitter_FirstSignal
)
values (
    0, 1,
   '2015-01-03', 10, '1912-04-02' -- set reliability 10
);

/*
-------------------------------------------------------
    Check the data.
    We now see the updated reliability, since this
    is the latest available with respect to the
    positing time.
-------------------------------------------------------
*/

select TR_FSG_Reliability from lTR_Transmitter where Positor = 0 and TR_ID = 1;

/*
-------------------------------------------------------
    Add a second belief with the same positing time.
    This can only be done if indecisiveness is allowed
    in the implementation.

    Along with the fact that we believe with 10%
    certainty that the first signal was received
    on the 2nd of April, we at the same positing
    time believe that it was receive on the 1st
    of April with 90% certainty.
-------------------------------------------------------
*/
insert into lTR_Transmitter (
    Positor, TR_ID,
    TR_FSG_PositedAt, TR_FSG_Reliability, TR_FSG_Transmitter_FirstSignal
)
values (
    0, 1,
   '2015-01-03', 90, '1912-04-01' -- set reliability 90
);

/*
-------------------------------------------------------
    Check the data.
    Note that in order to always retrieve a single
    row per attribute, the views select the one
    with the highest reliability.

    Our best guess for the first signal is
    on April 1st, with 90% certainty. Note that
    the database still keeps a record of the
    fact that another posit exists at the same
    time, but with only 10% certainty.
-------------------------------------------------------
*/
select TR_FSG_Reliability from lTR_Transmitter where Positor = 0 and TR_ID = 1;

/*
-------------------------------------------------------
    Kill the transmitter.
    This uses the overall Reliability column
    to clear out the reliability of the columns
    in the view.
-------------------------------------------------------
*/
update lTR_Transmitter
set
    Reliability = 0
where
    TR_ID = 1
and
    Positor = 0;

/*
-------------------------------------------------------
    Check the data.
    Note that due to historization the previous
    value in effect now became visible for the
    last received signal.
-------------------------------------------------------
*/
select * from lTR_Transmitter where Positor = 0;

/*
-------------------------------------------------------
    Kill the transmitter (again).
    This instead uses a delete on the view.
-------------------------------------------------------
*/
delete lTR_Transmitter
where
    TR_ID = 1;

/*
-------------------------------------------------------
    Check the data.
    This removed the last version we had for the
    last received signal. However, had there been
    more versions we would have had to run more
    deletes to get rid of them.

    The identity will, however, still live in the
    database, since we are keeping its memory.
    Therefore we usually have WHERE conditions in
    CRT that checks for non-null values of the
    particular columns we are interested in.
-------------------------------------------------------
*/
select * from lTR_Transmitter where Positor = 0;