Transitional Modeling 20181217.2

--------------------------------------- RELATIONAL MODEL ---------------------------------------
--
--   Script by Lars Rönnbäck
--   Based on the paper "Modeling Conflicting, Unreliable, and Varying Information"
--   https://www.researchgate.net/publication/329352497_Modeling_Conflicting_Unreliable_and_Varying_Information
--
--   This script creates a relationally modeled implementation of Transitional Modeling, with
--   some limitiations due to missing features in SQL Server.
--
--   Version: 20181217.2   Better XML in the views.
--
--   TODO: Add stored procedure that takes similar XML input and automagically populates the
--         underlying model.
--
drop function if exists [Information_in_Effect];
drop view if exists [v_Assertion];
drop view if exists [v_Posit];
drop table if exists [Assertion];
drop table if exists [Posit];
drop table if exists [Dereference];
drop table if exists [DereferencingSet];
drop table if exists [Appearance];
drop table if exists [Role];
drop table if exists [Thing];

-- A table to store the unique identifiers of things.
-- Where a thing is that which is sufficiently distinguishable
-- from something else to be told apart.
create table [Thing] (
    [UID] uniqueidentifier not null default NEWSEQUENTIALID(),
    -- Enforce uniqueness of the unique identifiers.
    -- Note that primary keys enforce uniqueness as well as cluster
    -- the underlying table for better performance, and is needed
    -- in order to reference these using foreign key references.
    constraint [unique_and_referenceable_UID] primary key clustered (
        [UID]
    )
);

create table [Role] (
    [RoleUID] uniqueidentifier not null,
    [Role] varchar(555) not null,
    constraint [Role_is_Thing] foreign key (
        [RoleUID]
    ) references [Thing]([UID]),
    constraint [referenceable_RoleUID] primary key clustered (
        [RoleUID]
    ),
    constraint [unique_Role] unique nonclustered (
        [Role]
    )
);

/*
----------- Excerpt from: Modeling Conflicting, Unreliable, and Varying Information -----------
def. of universal
A body of information is said to be universal iff positors agree on all appearances.
-----------------------------------------------------------------------------------------------

In order to disagree you must first agree upon something upon which the difference in
opinion lies. At the very least, positors must agree on appearances. In other words
if two positors want to have an opinion about something, they must first come to the
conclusion that this something boils down to the same unique identifier for both of them
and that they mean the same when they talk about the roles it may appear in.

We will assume that talk about "Archie's beard" by any positor means that it is the
same Archie and the same property they are talking about.
*/
create table [Appearance] (
    [AppearanceUID] uniqueidentifier not null,
    [ThingUID] uniqueidentifier not null,
    [RoleUID] uniqueidentifier not null,
    constraint [Appearance_is_Thing] foreign key (
        [AppearanceUID]
    ) references [Thing]([UID]),
    constraint [ensure_existing_Thing] foreign key (
        [ThingUID]
    ) references [Thing]([UID]),
    constraint [ensure_existing_Role] foreign key (
        [RoleUID]
    ) references [Role]([RoleUID]),
    constraint [referenceable_AppearanceUID] primary key clustered (
        [AppearanceUID]
    ),
    constraint [unique_Appearance] unique nonclustered (
        [ThingUID],
        [RoleUID]
    )
);

create table [DereferencingSet] (
    [DereferencingSetUID] uniqueidentifier not null,
    constraint [DereferencingSet_is_Thing] foreign key (
        [DereferencingSetUID]
    ) references [Thing]([UID]),
    constraint [unique_and_referenceable_DereferencingSetUID] primary key clustered (
        [DereferencingSetUID]
    )
);

create table [Dereference] (
    [DereferencingSetUID] uniqueidentifier not null,
    [AppearanceUID] uniqueidentifier not null,
    constraint [reference_to_DereferencingSet] foreign key (
        [DereferencingSetUID]
    ) references [DereferencingSet]([DereferencingSetUID]),
    constraint [reference_to_Appearance] foreign key (
        [AppearanceUID]
    ) references [Appearance]([AppearanceUID]),
    constraint [unique_Dereference] primary key clustered (
        [DereferencingSetUID],
        [AppearanceUID]
    )
);

/*
----------- Excerpt from: Modeling Conflicting, Unreliable, and Varying Information -----------
def. of canonical
A body of information is said to be canonical iff all assertions
are made against posits without negated values.
-----------------------------------------------------------------------------------------------

In practice, the value of a posit may be of any data type, primitive or complex, but due to
SQL Server lacking support for generics (looks like <T> in many programming languages) we
have to limit the Value column to varchar(max). Appearance time may also be any time type,
exact or fuzzy, in order to represent that the value appeared exactly since some specified
time or inexactly within some period of time. Here the AppearanceTime column is limited
to a datetime.

Finally, in order to represent information, canonical form is used, which simply means that
values are stored without negation, for example "red" is acceptable, and "not red" is not.
Opposite opinions are instead handled using negative Reliability in assertions.
*/
create table [Posit] (
    [PositUID] uniqueidentifier not null,
    [DereferencingSetUID] uniqueidentifier not null,
    [Value] varchar(max) null,
    [AppearanceTime] datetime null,
    constraint [ensure_existing_DereferencingSet] foreign key (
        [DereferencingSetUID]
    ) references [DereferencingSet]([DereferencingSetUID]),
    constraint [Posit_is_Thing] foreign key (
        [PositUID]
    ) references [Thing]([UID]),
    constraint [referenceable_PositUID] primary key clustered (
        [PositUID]
    )
);

/*
----------- Excerpt from: Modeling Conflicting, Unreliable, and Varying Information -----------
def. of a body of information
A body of information is a set of true assertions.

def. of exclusive
A body of information is said to be exclusive iff no assertions
in which only the reliability differs exist.
-----------------------------------------------------------------------------------------------

We will assume that any data inserted into this table represents true assertions. We will not
constrain the Reliability column, but in practice it should be in the interval [-1, 1].
The constraint [unique_Assertion] ensures that the stored assertions are exclusive, so that
there is no ambiguity with respect to the assigned Reliability.
*/
create table [Assertion] (
    [AssertionUID] uniqueidentifier not null,
    [PositorUID] uniqueidentifier not null,
    [PositUID] uniqueidentifier not null,
    [Reliability] decimal(3,2) not null,
    [AssertionTime] datetime not null,
    constraint [Assertion_is_Thing] foreign key (
        [AssertionUID]
    ) references [Thing]([UID]),
    constraint [ensure_existing_Positor] foreign key (
        [PositorUID]
    ) references [Thing]([UID]),
    constraint [ensure_existing_Posit] foreign key (
        [PositUID]
    ) references [Posit]([PositUID]),
    constraint [referenceable_AssertionUID] primary key nonclustered (
        [AssertionUID]
    ),
    constraint [unique_Assertion] unique clustered (
        [PositorUID],
        [PositUID],
        [AssertionTime]
    )
);

go
create or alter view [v_Posit]
as
select
    p.[PositUID],
    p.[DereferencingSetUID],
    p.[Value],
    p.[AppearanceTime],
    (
        select
            p.[PositUID] as [@UID],
            p.[DereferencingSetUID] as [DereferencingSet/@UID],
            (
                select
                    a.[ThingUID] as [@UID],
                    r.[Role] as [@Role]
                from
                    [DereferencingSet] s
                join
                    [Dereference] d
                on
                    d.[DereferencingSetUID] = s.[DereferencingSetUID]
                join
                    [Appearance] a
                on
                    a.[AppearanceUID] = d.[AppearanceUID]
                join
                    [Role] r
                on
                    r.[RoleUID] = a.[RoleUID]
                where
                    s.[DereferencingSetUID] = p.[DereferencingSetUID]
                order by
                    r.[RoleUID] asc
                for xml path('Appearance'), type
            ) as [DereferencingSet],
            p.[Value],
            p.[AppearanceTime]
        for xml path('Posit'), type
    ) as [PositXML]
from
    [Posit] p

go
create or alter view [v_Assertion]
as
select
    a.[AssertionUID],
    a.[PositorUID],
    a.[PositUID],
    p.[DereferencingSetUID],
    p.[Value],
    p.[AppearanceTime],
    a.[Reliability],
    a.[AssertionTime],
    (
        select
            a.[AssertionUID] as [@UID],
            a.[PositorUID] as [Positor/@UID],
            p.[PositXML].query('.'),
            a.[Reliability] as [Reliability],
            a.[AssertionTime] as [AssertionTime]
        for xml path('Assertion'), type
    ) as [AssertionXML]
from
    [Assertion] a
join
    [v_Posit] p
on
    p.[PositUID] = a.[PositUID]
go

--------------------------------------- PROPERTIES ---------------------------------------

-- we have to use tables since the OUTPUT clause only works for tables
drop table if exists #A;
drop table if exists #B;
drop table if exists #M;
drop table if exists #S;
drop table if exists #D;
create table #A([UID] uniqueidentifier not null primary key); -- Archie
create table #B([UID] uniqueidentifier not null primary key); -- Bella
create table #M([UID] uniqueidentifier not null primary key); -- Modeler
create table #S([UID] uniqueidentifier not null primary key); -- Script
create table #D([UID] uniqueidentifier not null primary key); -- Disagreer

-- create the Archie thing
insert into [Thing]([UID]) output inserted.[UID] into #A values (DEFAULT);
-- create the Bella thing
insert into [Thing]([UID]) output inserted.[UID] into #B values (DEFAULT);
-- create the Modeler thing
insert into [Thing]([UID]) output inserted.[UID] into #M values (DEFAULT);
-- create the Script thing
insert into [Thing]([UID]) output inserted.[UID] into #S values (DEFAULT);
-- create the Disagreer thing
insert into [Thing]([UID]) output inserted.[UID] into #D values (DEFAULT);

-- we now have five things
select * from [Thing];

-- create a role thing and an appearance thing
drop table if exists #Role1;
drop table if exists #Appearance1;
create table #Role1([UID] uniqueidentifier not null primary key);
create table #Appearance1([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Role1 values (DEFAULT);
insert into [Thing]([UID]) output inserted.[UID] into #Appearance1 values (DEFAULT);

-- create a role
insert into [Role]([RoleUID], [Role])
select [UID], 'has beard'
from #Role1;

-- check what the role looks like
select * from [Role];

-- create an appearance
insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
select app.[UID], Archie.[UID], r.[RoleUID]
from [Role] r, #Appearance1 app, #A Archie
where r.[Role] = 'has beard';

-- we now have one appearance
select * from [Appearance];

-- create a dereferencing set thing
drop table if exists #DereferencingSet1;
create table #DereferencingSet1([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #DereferencingSet1 values (DEFAULT);

-- create a dereferencing set
insert into [DereferencingSet]([DereferencingSetUID])
select [UID]
from #DereferencingSet1;

-- add the appearance to the dereferencing set
insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
select s.[UID], app.[UID]
from #DereferencingSet1 s, #Appearance1 app;

-- create a posit thing
drop table if exists #Posit1;
create table #Posit1([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Posit1 values (DEFAULT);

-- create a posit
insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
select p.[UID], s.[UID], 'fluffy red', '2018-12-01 12:00'
from #Posit1 p, #DereferencingSet1 s;

-- we now have one posit
select * from [Posit];

-- now the posit is complete
select * from [v_Posit];

-- let Bella assert this, so first create an assertion thing
drop table if exists #Assertion1;
create table #Assertion1([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Assertion1 values (DEFAULT);

-- create an assertion
insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:30'
from #Assertion1 a, #B Bella, #Posit1 p;

-- now we have one assertion
select * from [Assertion];

-- now the assertion is complete
select * from [v_Assertion]
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;

-- create another posit thing
drop table if exists #Posit2;
create table #Posit2([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Posit2 values (DEFAULT);

-- create that posit
insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
select p.[UID], s.[UID], 'shaved clean', '2018-12-01 12:00'
from #Posit2 p, #DereferencingSet1 s;

-- we now have two posits
select * from [Posit];

-- let the Disagreer assert this, so first create another assertion thing
drop table if exists #Assertion2;
create table #Assertion2([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Assertion2 values (DEFAULT);

-- create another assertion
insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
select a.[UID], Disagreer.[UID], p.[UID], 1, '2018-12-13 15:30'
from #Assertion2 a, #D Disagreer, #Posit2 p;

-- now that assertion is also complete
-- so Bella and the Disagreer have conflicting views of Archie's beard
select * from [v_Assertion]
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;

-- let the Disagreer disagree on the posit Bella asserted
drop table if exists #Assertion3;
create table #Assertion3([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Assertion3 values (DEFAULT);

-- create another assertion
insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
select a.[UID], Disagreer.[UID], p.[UID], -1, '2018-12-13 15:35'
from #Assertion3 a, #D Disagreer, #Posit1 p;

-- so Bella and the Disagreer also disagree on the posit stating it is fluffy red
select * from [v_Assertion]
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;

-- let Bella assert that there is a very small chance it actually was shaved clean
-- first create an assertion thing
drop table if exists #Assertion4;
create table #Assertion4([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Assertion4 values (DEFAULT);

-- create an assertion
insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
select a.[UID], Bella.[UID], p.[UID], 0.05, '2018-12-13 15:35'
from #Assertion4 a, #B Bella, #Posit2 p;

-- Bella asserts that it is very unlikely, but Archie's beard may have been shaved clean
select * from [v_Assertion]
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;

-- create another posit thing
drop table if exists #Posit3;
create table #Posit3([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Posit3 values (DEFAULT);

-- create that posit
insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
select p.[UID], s.[UID], 'shaved clean', '2018-12-01 13:00'
from #Posit3 p, #DereferencingSet1 s;

-- we now have three posits
select * from [Posit];

-- create another assertion thing
drop table if exists #Assertion5;
create table #Assertion5([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Assertion5 values (DEFAULT);

-- create an assertion
insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:35'
from #Assertion5 a, #B Bella, #Posit3 p;

-- Bella asserts that at 13:00 Archie had shaved
select * from [v_Assertion]
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;


/*
----------- Excerpt from: Modeling Conflicting, Unreliable, and Varying Information -----------
def. of the information in effect
Let A be a body of information. The information in effect is a
subset A(T@, t@) ⊂ A given a bitemporal point in assertion
and appearance time (T@, t@) ∈ T × t. Assuming P, α, T, D, v,
and t are free variables, all assertions !(P, p, α, T) ∈ A(T@, t@)
with p = [D, v, t] are found using the following selection
criteria:
1. Let A1 ⊂ A be the assertions in A satisfying T ≤ T@ and
   t ≤ t@.
2. Let A2 ⊂ A1 be the assertions in A1 with α 6= 0 and the
   latest appearance time t for each combination of P and
   D, excluding those assertions !(P, p, α, T) for which an
   assertion !(P, p, 0, T0) exists with T ≤ T0 ≤ T@.
3. Let A(T@, t@) ⊂ A2 be the assertions from A2 with the
   latest assertion time T for each combination of P, D, and v.
-----------------------------------------------------------------------------------------------

A function for the information in effect is created below according
to the selection criteria defined above.
*/
go
create or alter function [Information_in_Effect] (
    @appearanceTime datetime,
    @assertionTime datetime
)
returns table as return
with A1 as (
    select
        *
    from
        [v_Assertion]
    where
        [AppearanceTime] <= @appearanceTime
    and
        [AssertionTime] <= @assertionTime
),
A2 as (
    select
        a.*
    from
        A1 a
    where
        a.[Reliability] <> 0
    and
        a.[AppearanceTime] = (
            select
                max(s.[AppearanceTime])
            from
                A1 s
            where
                s.[PositorUID] = a.[PositorUID]
            and
                s.[DereferencingSetUID] = a.[DereferencingSetUID]
        )
    and not exists (
        select
            x.[AssertionUID]
        from
            A1 x
        where
            x.[PositorUID] = a.[PositorUID]
        and
            x.[PositUID] = a.[PositUID]
        and
            x.[Reliability] = 0
        and
            x.[AssertionTime] between a.AssertionTime and @assertionTime
    )
),
A3 as (
    select
        a.*
    from
        A2 a
    where
        a.[AssertionTime] = (
            select
                max(s.[AssertionTime])
            from
                A2 s
            where
                s.[PositorUID] = a.[PositorUID]
            and
                s.[DereferencingSetUID] = a.[DereferencingSetUID]
            and
                s.[Value] = a.[Value]
        )
)
select
    *
from
    A3;
go

-- which information is in effect at 12 and 13 given what was asserted on or before 15:30?
select * from [Information_in_Effect]('2018-12-01 12:00', '2018-12-13 15:30')
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
select * from [Information_in_Effect]('2018-12-01 13:00', '2018-12-13 15:30')
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;

-- which information is in effect at 12 and 13 given what was asserted on or before 15:35?
select * from [Information_in_Effect]('2018-12-01 12:00', '2018-12-13 15:35')
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
select * from [Information_in_Effect]('2018-12-01 13:00', '2018-12-13 15:35')
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;

/*
----------- Excerpt from: Modeling Conflicting, Unreliable, and Varying Information -----------
def. of symmetric
A body of information is said to be symmetric iff the assertions
!(P, p, −α, T) and !(P, ̄p, α, T) are equivalent for a posit p and its opposite ̄p.

def. of bounded
A body of information is said to be bounded iff the reliabilities in the assertions
!(P, p, α, T) and !(P, ̄p, β, T) satisfy ∣α + β∣ = ∣α + β∣^2.

def. of non-contradictory
A body of information is said to be non-contradictory iff for
every information in effect the reliabilities, numbered 1 to n,
in all positive and negative assertions made by the same positor
for the same dereferencing set satisfy the inequality:
    n                  n
1/2 ∑ (1 - sign(αi)) + ∑ αi ≤ 1
   i=1                i=1
-----------------------------------------------------------------------------------------------

The fact that we can talk about contradictions relies on the assumption that our body
of information is symmetric. When a body of information is also bounded it is possible to
transform all information into canonical form (negated values like "not red" are instead
expressed using "red" and a negative Reliability).
*/

-- we create a view that checks for contradictions according to the inequality above
go
create or alter view [Check_for_Contradictions]
as
with bitemporalTimepoints as (
    select
        [AppearanceTime], [AssertionTime]
    from
        (select distinct [AppearanceTime] from Posit) p
    cross join
        (select distinct [AssertionTime] from Assertion) a
)
select
    ineq.*,
    case
        when InequalitySum <= 1 then 'Non-Contradictory'
        else 'Contradictory'
    end as Result
from (
    select
        t.[AppearanceTime],
        t.[AssertionTime],
        iie.[PositorUID],
        iie.[DereferencingSetUID],
        0.5 * sum(1 - sign(iie.[Reliability])) + sum(iie.[Reliability]) as InequalitySum
    from
        bitemporalTimepoints t
    cross apply
        [Information_in_Effect](t.[AppearanceTime], t.[AssertionTime]) iie
    group by
        t.[AppearanceTime],
        t.[AssertionTime],
        iie.[PositorUID],
        iie.[DereferencingSetUID]
) ineq;
go

-- have contradictory assertions been made?
select * from [Check_for_Contradictions];

-- which information is in effect at 12 and 13 given latest assertions?
select * from [Information_in_Effect]('2018-12-01 12:00', getdate())
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
select * from [Information_in_Effect]('2018-12-01 13:00', getdate())
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;

-- Bella realizes she will remain forever contradictory about 12:00, so she makes another assertion
drop table if exists #Assertion6;
create table #Assertion6([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Assertion6 values (DEFAULT);

-- Bella revaluates her earlier assertion from 1 to 0.95
insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
select a.[UID], Bella.[UID], p.[UID], 0.95, '2018-12-13 15:40'
from #Assertion6 a, #B Bella, #Posit1 p;

-- which information is in effect at 12 and 13 given latest assertions?
select * from [Information_in_Effect]('2018-12-01 12:00', getdate())
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
select * from [Information_in_Effect]('2018-12-01 13:00', getdate())
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;

-- have contradictory assertions been made?
select * from [Check_for_Contradictions];

-- the Disagreer is now changing it's mind and retracts the previous statement
drop table if exists #Assertion7;
create table #Assertion7([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Assertion7 values (DEFAULT);

-- a retraction has reliability = 0
insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
select a.[UID], Disagreer.[UID], p.[UID], 0, '2018-12-13 15:40'
from #Assertion7 a, #D Disagreer, #Posit2 p;

-- which information is now in effect at 12 and 13 at the time of the retraction?
select * from [Information_in_Effect]('2018-12-01 12:00', '2018-12-13 15:40')
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
select * from [Information_in_Effect]('2018-12-01 13:00', '2018-12-13 15:40')
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;


-- Bella is going to reiterate that Archie's beard was also shaved clean one hour later
drop table if exists #Posit4;
create table #Posit4([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Posit4 values (DEFAULT);

insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
select p.[UID], s.[UID], 'shaved clean', '2018-12-01 14:00'
from #Posit4 p, #DereferencingSet1 s;

drop table if exists #Assertion8;
create table #Assertion8([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Assertion8 values (DEFAULT);

insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:45'
from #Assertion8 a, #B Bella, #Posit4 p;

-- the latest assertion is called a "restatement", since the value is not changing
select * from [v_Assertion]
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;

-- the information in effect will show the latest restatement
select * from [Information_in_Effect]('2018-12-01 12:00', '2018-12-13 15:45')
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
select * from [Information_in_Effect]('2018-12-01 13:00', '2018-12-13 15:45')
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
select * from [Information_in_Effect]('2018-12-01 14:00', '2018-12-13 15:45')
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;

-- Bella is going to reassure that she still is certain Archie's beard was shaved clean at 14
drop table if exists #Assertion9;
create table #Assertion9([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Assertion9 values (DEFAULT);

insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:50'
from #Assertion9 a, #B Bella, #Posit4 p;

-- the latest assertion is called a "reassertion", since only the assertion time changes
select * from [v_Assertion]
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;

-- the information in effect will show the latest restatement and latest reassertion
select * from [Information_in_Effect]('2018-12-01 12:00', '2018-12-13 15:45')
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
select * from [Information_in_Effect]('2018-12-01 13:00', '2018-12-13 15:45')
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
select * from [Information_in_Effect]('2018-12-01 14:00', '2018-12-13 15:45')
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
select * from [Information_in_Effect]('2018-12-01 14:00', '2018-12-13 15:50')
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;

--------------------------------------- METADATA ---------------------------------------
-- The good thing about posits, assertions, and so on being "things" in themselves
-- is that it makes it possible to produce posits about them as well. This is
-- usually called metadata.

-- let the Script positor assert when what we have done so far was recorded in
-- our database right now.

-- we now have 21 things
select * from [Thing];

-- create another role thing
drop table if exists #Role2;
create table #Role2([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Role2 values (DEFAULT);

-- create a new role
insert into [Role]([RoleUID], [Role])
select [UID], 'was recorded at'
from #Role2;

-- check what the role looks like
select * from [Role];

-- create an appearance thing for every thing we have created
drop table if exists #Appearance2;
create table #Appearance2([UID] uniqueidentifier not null primary key);
merge [Thing] t
using [Thing] src on 1 = 0 -- make sure everything is "not matched" below
when not matched then insert ([UID]) values (DEFAULT)
output inserted.[UID] into #Appearance2;

-- create an equal number of appearances
insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
select app.[UID], t.[UID], r.[RoleUID]
from (select [UID], row_number() over (order by [UID]) as _row from #Appearance2) app
join (select [UID], row_number() over (order by [UID]) as _row from [Thing]) t
on t._row = app._row
cross apply (select [RoleUID] from [Role] where [Role] = 'was recorded at') r;

-- we now have 23 appearances
select * from [Appearance];

-- create 22 dereferencing set things
drop table if exists #DereferencingSet2;
create table #DereferencingSet2([UID] uniqueidentifier not null primary key);
merge [Thing] t
using #Appearance2 src on 1 = 0 -- make sure everything is "not matched" below
when not matched then insert ([UID]) values (DEFAULT)
output inserted.[UID] into #DereferencingSet2;

-- create 22 dereferencing sets
insert into [DereferencingSet]([DereferencingSetUID])
select [UID]
from #DereferencingSet2;

-- add the appearances to the dereferencing sets
insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
select s.[UID], app.[UID]
from (select [UID], row_number() over (order by [UID]) as _row from #DereferencingSet2) s
join (select [UID], row_number() over (order by [UID]) as _row from #Appearance2) app
on s._row = app._row;

-- create 22 posit things
drop table if exists #Posit5;
create table #Posit5([UID] uniqueidentifier not null primary key);
merge [Thing] t
using #DereferencingSet2 src on 1 = 0 -- make sure everything is "not matched" below
when not matched then insert ([UID]) values (DEFAULT)
output inserted.[UID] into #Posit5;

-- create 22 posits (with the limitation that the date needs to be converted to a string)
insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
select p.[UID], s.[UID], cast(getdate() as varchar(max)), getdate()
from (select [UID], row_number() over (order by [UID]) as _row from #Posit5) p
join (select [UID], row_number() over (order by [UID]) as _row from #DereferencingSet2) s
on p._row = s._row;

-- we now have 26 posits, the last 22 of which intends to capture metadata
select * from [v_Posit];

-- finally let the Script positor assert these
drop table if exists #Assertion10;
create table #Assertion10([UID] uniqueidentifier not null primary key);
merge [Thing] t
using #Posit5 src on 1 = 0 -- make sure everything is "not matched" below
when not matched then insert ([UID]) values (DEFAULT)
output inserted.[UID] into #Assertion10;

insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
select a.[UID], Script.[UID], p.[UID], 1, getdate()
from (select [UID], row_number() over (order by [UID]) as _row from #Assertion10) a
join (select [UID], row_number() over (order by [UID]) as _row from #Posit5) p
on a._row = p._row
cross apply #S Script;

-- there are now 31 assertions
select * from [v_Assertion]
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;

-- note that the metadata is not in effect if we travel back in time
select * from [Information_in_Effect]('2018-12-01 14:00', '2018-12-13 15:50')
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;

-- finding all (current) metadata can be done by a condition on our Script positor
select * from [Information_in_Effect](getdate(), getdate())
where [PositorUID] = (select top 1 [UID] from #S)
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;

--------------------------------------- RELATIONSHIPS ---------------------------------------
-- we will start by marrying Archie and Bella

-- create a few new role things
drop table if exists #Role3;
create table #Role3([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Role3 values (DEFAULT);
drop table if exists #Role4;
create table #Role4([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Role4 values (DEFAULT);
drop table if exists #Role5;
create table #Role5([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Role5 values (DEFAULT);

-- create the new roles
insert into [Role]([RoleUID], [Role])
select [UID], 'husband'
from #Role3;
insert into [Role]([RoleUID], [Role])
select [UID], 'wife'
from #Role4;
insert into [Role]([RoleUID], [Role])
select [UID], 'church'
from #Role5;

-- what roles have got now?
select * from [Role];

-- we will need a church thing
drop table if exists #C;
create table #C([UID] uniqueidentifier not null primary key); -- Church

-- create the Church thing
insert into [Thing]([UID]) output inserted.[UID] into #C values (DEFAULT);

-- we also need three appearance things
drop table if exists #Appearance3;
create table #Appearance3([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Appearance3 values (DEFAULT);
drop table if exists #Appearance4;
create table #Appearance4([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Appearance4 values (DEFAULT);
drop table if exists #Appearance5;
create table #Appearance5([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Appearance5 values (DEFAULT);

-- create three appearances
insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
select app.[UID], Archie.[UID], r.[RoleUID]
from [Role] r, #Appearance3 app, #A Archie
where r.[Role] = 'husband';
insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
select app.[UID], Bella.[UID], r.[RoleUID]
from [Role] r, #Appearance4 app, #B Bella
where r.[Role] = 'wife';
insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
select app.[UID], Church.[UID], r.[RoleUID]
from [Role] r, #Appearance5 app, #C Church
where r.[Role] = 'church';

-- create a dereferencing set thing
drop table if exists #DereferencingSet3;
create table #DereferencingSet3([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #DereferencingSet3 values (DEFAULT);

-- create a dereferencing set
insert into [DereferencingSet]([DereferencingSetUID])
select [UID]
from #DereferencingSet3;

-- add the created appearances to the (same) dereferencing set
insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
select s.[UID], app.[UID]
from #DereferencingSet3 s, #Appearance3 app;
insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
select s.[UID], app.[UID]
from #DereferencingSet3 s, #Appearance4 app;
insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
select s.[UID], app.[UID]
from #DereferencingSet3 s, #Appearance5 app;

-- create a posit thing
drop table if exists #Posit6;
create table #Posit6([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Posit6 values (DEFAULT);

-- create a posit
insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
select p.[UID], s.[UID], 'married', '2004-06-19 15:00'
from #Posit6 p, #DereferencingSet3 s;

-- now the posit is complete (note that the set has three members now)
select * from [v_Posit]
where [DereferencingSetUID] = (select top 1 [UID] from #DereferencingSet3);

-- let Bella assert this, so first create an assertion thing
drop table if exists #Assertion11;
create table #Assertion11([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Assertion11 values (DEFAULT);

-- create an assertion
insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:55'
from #Assertion11 a, #B Bella, #Posit6 p;

-- now Bella has asserted the marriage
select * from [v_Assertion]
where [DereferencingSetUID] = (select top 1 [UID] from #DereferencingSet3)
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;

-- let them divorce a while later (using the same dereferencing set)
-- create a posit thing
drop table if exists #Posit7;
create table #Posit7([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Posit7 values (DEFAULT);

-- create a posit
insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
select p.[UID], s.[UID], 'divorced', '2010-01-31 10:00'
from #Posit7 p, #DereferencingSet3 s;

-- the state of this dereferencing set has transitioned from 'married' to 'divorced'
select * from [v_Posit]
where [DereferencingSetUID] = (select top 1 [UID] from #DereferencingSet3);

-- let Bella assert this as well, so first create an assertion thing
drop table if exists #Assertion12;
create table #Assertion12([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Assertion12 values (DEFAULT);

-- create an assertion
insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:55'
from #Assertion12 a, #B Bella, #Posit7 p;

-- now Bella has asserted the divorce
select * from [v_Assertion]
where [DereferencingSetUID] = (select top 1 [UID] from #DereferencingSet3)
order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;

-- however, as it turns out, Bella remarried the Disagreer at a later time, but not in a church
-- that means a new dereferencing set is needed and this time without the church

-- first the Disagreer must appear as husband
drop table if exists #Appearance6;
create table #Appearance6([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Appearance6 values (DEFAULT);

insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
select app.[UID], Disagreer.[UID], r.[RoleUID]
from [Role] r, #Appearance6 app, #D Disagreer
where r.[Role] = 'husband';

-- create a dereferencing set thing
drop table if exists #DereferencingSet4;
create table #DereferencingSet4([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #DereferencingSet4 values (DEFAULT);

-- create a dereferencing set
insert into [DereferencingSet]([DereferencingSetUID])
select [UID]
from #DereferencingSet4;

-- add the created appearances to the dereferencing set (with only two members)
insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
select s.[UID], app.[UID]
from #DereferencingSet4 s, #Appearance6 app;
insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
select s.[UID], app.[UID]
from #DereferencingSet4 s, #Appearance4 app;

-- create a posit thing
drop table if exists #Posit8;
create table #Posit8([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Posit8 values (DEFAULT);

-- create a posit
insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
select p.[UID], s.[UID], 'married', '2011-11-11 11:11'
from #Posit8 p, #DereferencingSet4 s;

-- let Bella assert this as well, so first create an assertion thing
drop table if exists #Assertion13;
create table #Assertion13([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Assertion13 values (DEFAULT);

-- create an assertion
insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:55'
from #Assertion13 a, #B Bella, #Posit8 p;

-- now Bella has asserted her second marriage
-- we can find this from finding every assertion in which Bella has appeared in the wife role
-- regardless if the relationship involves a church or not
-- note: it is also possible to achieve the same result using joins instead of the XML query
select a.* from [v_Assertion] a cross apply #B Bella
where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@UID = sql:column("Bella.UID") and @Role = "wife"]') = 1
order by [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;

-- but, Bella made a mistake, the appearance time of her second marriage is not correct
-- so she first makes a retraction of the erroneous assertion
drop table if exists #Assertion14;
create table #Assertion14([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Assertion14 values (DEFAULT);

-- create an assertion (that is a retraction since Reliability = 0)
insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
select a.[UID], Bella.[UID], p.[UID], 0, '2018-12-13 16:00'
from #Assertion14 a, #B Bella, #Posit8 p;

-- in the view of all assertions the retraction is now visible
select a.* from [v_Assertion] a cross apply #B Bella
where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@UID = sql:column("Bella.UID") and @Role = "wife"]') = 1
order by [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;

-- which means that the latest information we have is that Bella is divorced
select a.* from [Information_in_Effect](getdate(), getdate()) a cross apply #B Bella
where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@UID = sql:column("Bella.UID") and @Role = "wife"]') = 1
order by [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;

-- so Bella needs to assert a different posit, with the correct appearance time
drop table if exists #Posit9;
create table #Posit9([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Posit9 values (DEFAULT);

insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
select p.[UID], s.[UID], 'married', '2012-12-12 12:12'
from #Posit9 p, #DereferencingSet4 s;

drop table if exists #Assertion15;
create table #Assertion15([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Assertion15 values (DEFAULT);

insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 16:00'
from #Assertion15 a, #B Bella, #Posit9 p;

-- in the view of all assertions the correction is now visible
select a.* from [v_Assertion] a cross apply #B Bella
where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@UID = sql:column("Bella.UID") and @Role = "wife"]') = 1
order by [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;

-- new the latest information we have is that Bella is married, but note that the earlier divorce is also shown
-- due to it having a different dereferencing set
select a.* from [Information_in_Effect](getdate(), getdate()) a cross apply #B Bella
where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@UID = sql:column("Bella.UID") and @Role = "wife"]') = 1
order by [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;


------------------------------------------- MODELING ------------------------------------------
/*
----------- Excerpt from: Modeling Conflicting, Unreliable, and Varying Information -----------
def. of a classifier and a class
Let "is class" be a role reserved for the purpose of modeling.
A posit pc = [{(C, is class)}, c, t], defines the name
of a class through the string c and associates the unique
identifier C with it. A classifier is a relationship that binds
a thing to a class, expressed through posits on the form
pM = [{(i, thing),(C, class)}, v, t].
-----------------------------------------------------------------------------------------------

It is time for the Modeler to step in and tell us what some of our things are.
*/
-- we need three new roles in order to create a model
drop table if exists #Role6;
create table #Role6([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Role6 values (DEFAULT);
drop table if exists #Role7;
create table #Role7([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Role7 values (DEFAULT);
drop table if exists #Role8;
create table #Role8([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Role8 values (DEFAULT);

-- create the new roles
insert into [Role]([RoleUID], [Role])
select [UID], 'is class'
from #Role6;
insert into [Role]([RoleUID], [Role])
select [UID], 'thing'
from #Role7;
insert into [Role]([RoleUID], [Role])
select [UID], 'class'
from #Role8;

-- what roles have got now?
select * from [Role] order by RoleUID;

-- create two class things
drop table if exists #Class1;
create table #Class1([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Class1 values (DEFAULT);
drop table if exists #Class2;
create table #Class2([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Class2 values (DEFAULT);

-- the classes need names, so first they need to appear with the "is class" role
drop table if exists #Appearance7;
create table #Appearance7([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Appearance7 values (DEFAULT);
drop table if exists #Appearance8;
create table #Appearance8([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Appearance8 values (DEFAULT);

insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
select app.[UID], c.[UID], r.[RoleUID]
from [Role] r, #Appearance7 app, #Class1 c
where r.[Role] = 'is class';
insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
select app.[UID], c.[UID], r.[RoleUID]
from [Role] r, #Appearance8 app, #Class2 c
where r.[Role] = 'is class';

-- create a dereferencing set thing
drop table if exists #DereferencingSet5;
create table #DereferencingSet5([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #DereferencingSet5 values (DEFAULT);
drop table if exists #DereferencingSet6;
create table #DereferencingSet6([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #DereferencingSet6 values (DEFAULT);

-- create dereferencing sets
insert into [DereferencingSet]([DereferencingSetUID])
select [UID]
from #DereferencingSet5;
insert into [DereferencingSet]([DereferencingSetUID])
select [UID]
from #DereferencingSet6;

-- add the created appearances
insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
select s.[UID], app.[UID]
from #DereferencingSet5 s, #Appearance7 app;
insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
select s.[UID], app.[UID]
from #DereferencingSet6 s, #Appearance8 app;

-- two posit things
drop table if exists #Posit10;
create table #Posit10([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Posit10 values (DEFAULT);
drop table if exists #Posit11;
create table #Posit11([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Posit11 values (DEFAULT);

-- two posits
insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
select p.[UID], s.[UID], 'Person', '1901-01-01' -- will represent dawn of time
from #Posit10 p, #DereferencingSet5 s;
insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
select p.[UID], s.[UID], 'Cathedral', '1901-01-01' -- will represent dawn of time
from #Posit11 p, #DereferencingSet6 s;

-- now, the Modeler must assert these
drop table if exists #Assertion16;
create table #Assertion16([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Assertion16 values (DEFAULT);
drop table if exists #Assertion17;
create table #Assertion17([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Assertion17 values (DEFAULT);

insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
select a.[UID], Modeler.[UID], p.[UID], 1, '2018-12-01 00:00'
from #Assertion16 a, #M Modeler, #Posit10 p;
insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
select a.[UID], Modeler.[UID], p.[UID], 1, '2018-12-01 00:00'
from #Assertion17 a, #M Modeler, #Posit11 p;

-- list all classes
select a.* from [v_Assertion] a
where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@Role = "is class"]') = 1
order by [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;

-- lot's of work, but two classes are defined
-- they have their own unique identifiers, so many more roles can be added to provide additional
-- information about the classes

-- now it is time to associate some things with the classes
-- every such thing needs an appearance with the 'thing' role
drop table if exists #Appearance9;
create table #Appearance9([UID] uniqueidentifier not null primary key);
merge [Thing] t
using (
    select [UID] from #A
    union all
    select [UID] from #B
    union all
    select [UID] from #C
    union all
    select [UID] from #M
    union all
    select [UID] from #D
) src
on 1 = 0 -- make sure everything is "not matched" below
when not matched then insert ([UID]) values (DEFAULT)
output inserted.[UID] into #Appearance9;

-- create an equal number of appearances
insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
select app.[UID], t.[UID], r.[RoleUID]
from (select [UID], row_number() over (order by [UID]) as _row from #Appearance9) app
join (select [UID], row_number() over (order by [UID]) as _row from (
    select [UID] from #A
    union all
    select [UID] from #B
    union all
    select [UID] from #C
    union all
    select [UID] from #M
    union all
    select [UID] from #D
) things ) t
on t._row = app._row
cross apply (select [RoleUID] from [Role] where [Role] = 'thing') r;

-- then two more appearances (one for each associated class)
drop table if exists #Appearance10;
create table #Appearance10([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Appearance10 values (DEFAULT);
drop table if exists #Appearance11;
create table #Appearance11([UID] uniqueidentifier not null primary key);
insert into [Thing]([UID]) output inserted.[UID] into #Appearance11 values (DEFAULT);

insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
select app.[UID], c.[UID], r.[RoleUID]
from [Role] r, #Appearance10 app, #Class1 c
where r.[Role] = 'class';
insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
select app.[UID], c.[UID], r.[RoleUID]
from [Role] r, #Appearance11 app, #Class2 c
where r.[Role] = 'class';

-- create dereferencing set things (equal in number to the things we want to classify)
drop table if exists #DereferencingSet7;
create table #DereferencingSet7([UID] uniqueidentifier not null primary key);
merge [Thing] t
using #Appearance9 src on 1 = 0 -- make sure everything is "not matched" below
when not matched then insert ([UID]) values (DEFAULT)
output inserted.[UID] into #DereferencingSet7;

-- create 22 dereferencing sets
insert into [DereferencingSet]([DereferencingSetUID])
select [UID]
from #DereferencingSet7;

-- add the appearances to the dereferencing sets
-- first the things
insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
select s.[UID], app.[UID]
from (select [UID], row_number() over (order by [UID]) as _row from #DereferencingSet7) s
join (select [UID], row_number() over (order by [UID]) as _row from #Appearance9) app
on s._row = app._row;
-- then the classes
insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
select
    s.[UID],
        case
            when exists (
                    select top 1 d.[DereferencingSetUID]
                    from [Dereference] d
                    join [Appearance] a
                      on a.[AppearanceUID] = d.[AppearanceUID]
                    join (
                        select [UID] from #A
                        union all
                        select [UID] from #B
                        union all
                        select [UID] from #M
                        union all
                        select [UID] from #D
                    ) persons
                    on persons.[UID] = a.[ThingUID]
                    where d.[DereferencingSetUID] = s.[UID]
            ) then Person.[UID]
            else Cathedral.[UID]
        end
from #DereferencingSet7 s, #Appearance10 Person, #Appearance11 Cathedral;

-- create posit things
drop table if exists #Posit12;
create table #Posit12([UID] uniqueidentifier not null primary key);
merge [Thing] t
using #DereferencingSet7 src on 1 = 0 -- make sure everything is "not matched" below
when not matched then insert ([UID]) values (DEFAULT)
output inserted.[UID] into #Posit12;

-- create posits
insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
select p.[UID], s.[UID], 'active', '1901-01-01'
from (select [UID], row_number() over (order by [UID]) as _row from #Posit12) p
join (select [UID], row_number() over (order by [UID]) as _row from #DereferencingSet7) s
on p._row = s._row;

-- finally let the Modeler positor assert these
drop table if exists #Assertion18;
create table #Assertion18([UID] uniqueidentifier not null primary key);
merge [Thing] t
using #Posit12 src on 1 = 0 -- make sure everything is "not matched" below
when not matched then insert ([UID]) values (DEFAULT)
output inserted.[UID] into #Assertion18;

insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
select a.[UID], Modeler.[UID], p.[UID], 1, '2018-12-01 00:00'
from (select [UID], row_number() over (order by [UID]) as _row from #Assertion18) a
join (select [UID], row_number() over (order by [UID]) as _row from #Posit12) p
on a._row = p._row
cross apply #M Modeler;

-- list all classes and classifiers
-- as can be seen there are four things of class Person and one thing of class Cathedral
select a.* from [v_Assertion] a
where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@Role = "is class" or @Role = "class"]') = 1
order by [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;


/*
----------- Excerpt from: Modeling Conflicting, Unreliable, and Varying Information -----------
def. of a posit type
A posit type, τ(p) = [{(C1,r1), . . . ,(Cn,rn)}, τ(v), τ(t)], for a
posit p = [{(i1,r1), . . . ,(in,rn)}, v, t], is a structure constructed
by replacing unique identifiers, ij with the unique identifiers of their
class, Cj, the value, v, with its data type, τ(v), and the time point, t,
with its data type, τ(t).
-----------------------------------------------------------------------------------------------

A posit type is a structure, but posits in the relational model can only have
values of type varchar(max), so it cannot hold such a structure. It could either
be expressed as serialized XML or JSON, or we could let the field contain a
reference to an identifier in a separate table that replicates the structure.
Since a posit type contains a set with variable number of members, it is
easier in this case to express it using XML.
*/