· 7 years ago · Dec 17, 2018, 02:34 PM
1--------------------------------------- RELATIONAL MODEL ---------------------------------------
2--
3-- Script by Lars Rönnbäck
4-- Based on the paper "Modeling Conflicting, Unreliable, and Varying Information"
5-- https://www.researchgate.net/publication/329352497_Modeling_Conflicting_Unreliable_and_Varying_Information
6--
7-- This script creates a relationally modeled
8--
9drop table if exists [PositType];
10drop function if exists [Information_in_Effect];
11drop view if exists [v_Assertion];
12drop view if exists [v_Posit];
13drop table if exists [Assertion];
14drop table if exists [Posit];
15drop table if exists [Dereference];
16drop table if exists [DereferencingSet];
17drop table if exists [Appearance];
18drop table if exists [Role];
19drop table if exists [Thing];
20
21-- A table to store the unique identifiers of things.
22-- Where a thing is that which is sufficiently distinguishable
23-- from something else to be told apart.
24create table [Thing] (
25 [UID] uniqueidentifier not null default NEWSEQUENTIALID(),
26 -- Enforce uniqueness of the unique identifiers.
27 -- Note that primary keys enforce uniqueness as well as cluster
28 -- the underlying table for better performance, and is needed
29 -- in order to reference these using foreign key references.
30 constraint [unique_and_referenceable_UID] primary key clustered (
31 [UID]
32 )
33);
34
35create table [Role] (
36 [RoleUID] uniqueidentifier not null,
37 [Role] varchar(555) not null,
38 constraint [Role_is_Thing] foreign key (
39 [RoleUID]
40 ) references [Thing]([UID]),
41 constraint [referenceable_RoleUID] primary key clustered (
42 [RoleUID]
43 ),
44 constraint [unique_Role] unique nonclustered (
45 [Role]
46 )
47);
48
49/*
50----------- Excerpt from: Modeling Conflicting, Unreliable, and Varying Information -----------
51def. of universal
52A body of information is said to be universal iff positors agree on all appearances.
53-----------------------------------------------------------------------------------------------
54
55In order to disagree you must first agree upon something upon which the difference in
56opinion lies. At the very least, positors must agree on appearances. In other words
57if two positors want to have an opinion about something, they must first come to the
58conclusion that this something boils down to the same unique identifier for both of them
59and that they mean the same when they talk about the roles it may appear in.
60
61We will assume that talk about "Archie's beard" by any positor means that it is the
62same Archie and the same property they are talking about.
63*/
64create table [Appearance] (
65 [AppearanceUID] uniqueidentifier not null,
66 [ThingUID] uniqueidentifier not null,
67 [RoleUID] uniqueidentifier not null,
68 constraint [Appearance_is_Thing] foreign key (
69 [AppearanceUID]
70 ) references [Thing]([UID]),
71 constraint [ensure_existing_Thing] foreign key (
72 [ThingUID]
73 ) references [Thing]([UID]),
74 constraint [ensure_existing_Role] foreign key (
75 [RoleUID]
76 ) references [Role]([RoleUID]),
77 constraint [referenceable_AppearanceUID] primary key clustered (
78 [AppearanceUID]
79 ),
80 constraint [unique_Appearance] unique nonclustered (
81 [ThingUID],
82 [RoleUID]
83 )
84);
85
86create table [DereferencingSet] (
87 [DereferencingSetUID] uniqueidentifier not null,
88 constraint [DereferencingSet_is_Thing] foreign key (
89 [DereferencingSetUID]
90 ) references [Thing]([UID]),
91 constraint [unique_and_referenceable_DereferencingSetUID] primary key clustered (
92 [DereferencingSetUID]
93 )
94);
95
96create table [Dereference] (
97 [DereferencingSetUID] uniqueidentifier not null,
98 [AppearanceUID] uniqueidentifier not null,
99 constraint [reference_to_DereferencingSet] foreign key (
100 [DereferencingSetUID]
101 ) references [DereferencingSet]([DereferencingSetUID]),
102 constraint [reference_to_Appearance] foreign key (
103 [AppearanceUID]
104 ) references [Appearance]([AppearanceUID]),
105 constraint [unique_Dereference] primary key clustered (
106 [DereferencingSetUID],
107 [AppearanceUID]
108 )
109);
110
111/*
112----------- Excerpt from: Modeling Conflicting, Unreliable, and Varying Information -----------
113def. of canonical
114A body of information is said to be canonical iff all assertions
115are made against posits without negated values.
116-----------------------------------------------------------------------------------------------
117
118In practice, the value of a posit may be of any data type, primitive or complex, but due to
119SQL Server lacking support for generics (looks like <T> in many programming languages) we
120have to limit the Value column to varchar(max). Appearance time may also be any time type,
121exact or fuzzy, in order to represent that the value appeared exactly since some specified
122time or inexactly within some period of time. Here the AppearanceTime column is limited
123to a datetime.
124
125Finally, in order to represent information, canonical form is used, which simply means that
126values are stored without negation, for example "red" is acceptable, and "not red" is not.
127Opposite opinions are instead handled using negative Reliability in assertions.
128*/
129create table [Posit] (
130 [PositUID] uniqueidentifier not null,
131 [DereferencingSetUID] uniqueidentifier not null,
132 [Value] varchar(max) null,
133 [AppearanceTime] datetime null,
134 constraint [ensure_existing_DereferencingSet] foreign key (
135 [DereferencingSetUID]
136 ) references [DereferencingSet]([DereferencingSetUID]),
137 constraint [Posit_is_Thing] foreign key (
138 [PositUID]
139 ) references [Thing]([UID]),
140 constraint [referenceable_PositUID] primary key clustered (
141 [PositUID]
142 )
143);
144
145/*
146----------- Excerpt from: Modeling Conflicting, Unreliable, and Varying Information -----------
147def. of a body of information
148A body of information is a set of true assertions.
149
150def. of exclusive
151A body of information is said to be exclusive iff no assertions
152in which only the reliability differs exist.
153-----------------------------------------------------------------------------------------------
154
155We will assume that any data inserted into this table represents true assertions. We will not
156constrain the Reliability column, but in practice it should be in the interval [-1, 1].
157The constraint [unique_Assertion] ensures that the stored assertions are exclusive, so that
158there is no ambiguity with respect to the assigned Reliability.
159*/
160create table [Assertion] (
161 [AssertionUID] uniqueidentifier not null,
162 [PositorUID] uniqueidentifier not null,
163 [PositUID] uniqueidentifier not null,
164 [Reliability] decimal(3,2) not null,
165 [AssertionTime] datetime not null,
166 constraint [Assertion_is_Thing] foreign key (
167 [AssertionUID]
168 ) references [Thing]([UID]),
169 constraint [ensure_existing_Positor] foreign key (
170 [PositorUID]
171 ) references [Thing]([UID]),
172 constraint [ensure_existing_Posit] foreign key (
173 [PositUID]
174 ) references [Posit]([PositUID]),
175 constraint [referenceable_AssertionUID] primary key nonclustered (
176 [AssertionUID]
177 ),
178 constraint [unique_Assertion] unique clustered (
179 [PositorUID],
180 [PositUID],
181 [AssertionTime]
182 )
183);
184
185go
186create or alter view [v_Posit]
187as
188select
189 p.[PositUID],
190 p.[DereferencingSetUID],
191 (
192 select
193 a.[ThingUID] as [@ThingUID],
194 r.[Role] as [@Role]
195 from
196 [DereferencingSet] s
197 join
198 [Dereference] d
199 on
200 d.[DereferencingSetUID] = s.[DereferencingSetUID]
201 join
202 [Appearance] a
203 on
204 a.[AppearanceUID] = d.[AppearanceUID]
205 join
206 [Role] r
207 on
208 r.[RoleUID] = a.[RoleUID]
209 where
210 s.[DereferencingSetUID] = p.[DereferencingSetUID]
211 order by
212 r.[RoleUID] asc
213 for xml path('Appearance'), type
214 ) as [DereferencingSet],
215 p.[Value],
216 p.[AppearanceTime]
217from
218 [Posit] p
219
220go
221create or alter view [v_Assertion]
222as
223select
224 a.[AssertionUID],
225 a.[PositorUID],
226 a.[PositUID],
227 p.[DereferencingSetUID],
228 p.[DereferencingSet],
229 p.[Value],
230 p.[AppearanceTime],
231 a.[Reliability],
232 a.[AssertionTime]
233from
234 [Assertion] a
235join
236 [v_Posit] p
237on
238 p.[PositUID] = a.[PositUID]
239go
240
241--------------------------------------- PROPERTIES ---------------------------------------
242
243-- we have to use tables since the OUTPUT clause only works for tables
244drop table if exists #A;
245drop table if exists #B;
246drop table if exists #M;
247drop table if exists #S;
248drop table if exists #D;
249create table #A([UID] uniqueidentifier not null primary key); -- Archie
250create table #B([UID] uniqueidentifier not null primary key); -- Bella
251create table #M([UID] uniqueidentifier not null primary key); -- Modeler
252create table #S([UID] uniqueidentifier not null primary key); -- Script
253create table #D([UID] uniqueidentifier not null primary key); -- Disagreer
254
255-- create the Archie thing
256insert into [Thing]([UID]) output inserted.[UID] into #A values (DEFAULT);
257-- create the Bella thing
258insert into [Thing]([UID]) output inserted.[UID] into #B values (DEFAULT);
259-- create the Modeler thing
260insert into [Thing]([UID]) output inserted.[UID] into #M values (DEFAULT);
261-- create the Script thing
262insert into [Thing]([UID]) output inserted.[UID] into #S values (DEFAULT);
263-- create the Disagreer thing
264insert into [Thing]([UID]) output inserted.[UID] into #D values (DEFAULT);
265
266-- we now have five things
267select * from [Thing];
268
269-- create a role thing and an appearance thing
270drop table if exists #Role1;
271drop table if exists #Appearance1;
272create table #Role1([UID] uniqueidentifier not null primary key);
273create table #Appearance1([UID] uniqueidentifier not null primary key);
274insert into [Thing]([UID]) output inserted.[UID] into #Role1 values (DEFAULT);
275insert into [Thing]([UID]) output inserted.[UID] into #Appearance1 values (DEFAULT);
276
277-- create a role
278insert into [Role]([RoleUID], [Role])
279select [UID], 'has beard'
280from #Role1;
281
282-- check what the role looks like
283select * from [Role];
284
285-- create an appearance
286insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
287select app.[UID], Archie.[UID], r.[RoleUID]
288from [Role] r, #Appearance1 app, #A Archie
289where r.[Role] = 'has beard';
290
291-- we now have one appearance
292select * from [Appearance];
293
294-- create a dereferencing set thing
295drop table if exists #DereferencingSet1;
296create table #DereferencingSet1([UID] uniqueidentifier not null primary key);
297insert into [Thing]([UID]) output inserted.[UID] into #DereferencingSet1 values (DEFAULT);
298
299-- create a dereferencing set
300insert into [DereferencingSet]([DereferencingSetUID])
301select [UID]
302from #DereferencingSet1;
303
304-- add the appearance to the dereferencing set
305insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
306select s.[UID], app.[UID]
307from #DereferencingSet1 s, #Appearance1 app;
308
309-- create a posit thing
310drop table if exists #Posit1;
311create table #Posit1([UID] uniqueidentifier not null primary key);
312insert into [Thing]([UID]) output inserted.[UID] into #Posit1 values (DEFAULT);
313
314-- create a posit
315insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
316select p.[UID], s.[UID], 'fluffy red', '2018-12-01 12:00'
317from #Posit1 p, #DereferencingSet1 s;
318
319-- we now have one posit
320select * from [Posit];
321
322-- now the posit is complete
323select * from [v_Posit];
324
325-- let Bella assert this, so first create an assertion thing
326drop table if exists #Assertion1;
327create table #Assertion1([UID] uniqueidentifier not null primary key);
328insert into [Thing]([UID]) output inserted.[UID] into #Assertion1 values (DEFAULT);
329
330-- create an assertion
331insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
332select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:30'
333from #Assertion1 a, #B Bella, #Posit1 p;
334
335-- now we have one assertion
336select * from [Assertion];
337
338-- now the assertion is complete
339select * from [v_Assertion]
340order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
341
342-- create another posit thing
343drop table if exists #Posit2;
344create table #Posit2([UID] uniqueidentifier not null primary key);
345insert into [Thing]([UID]) output inserted.[UID] into #Posit2 values (DEFAULT);
346
347-- create that posit
348insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
349select p.[UID], s.[UID], 'shaved clean', '2018-12-01 12:00'
350from #Posit2 p, #DereferencingSet1 s;
351
352-- we now have two posits
353select * from [Posit];
354
355-- let the Disagreer assert this, so first create another assertion thing
356drop table if exists #Assertion2;
357create table #Assertion2([UID] uniqueidentifier not null primary key);
358insert into [Thing]([UID]) output inserted.[UID] into #Assertion2 values (DEFAULT);
359
360-- create another assertion
361insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
362select a.[UID], Disagreer.[UID], p.[UID], 1, '2018-12-13 15:30'
363from #Assertion2 a, #D Disagreer, #Posit2 p;
364
365-- now that assertion is also complete
366-- so Bella and the Disagreer have conflicting views of Archie's beard
367select * from [v_Assertion]
368order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
369
370-- let the Disagreer disagree on the posit Bella asserted
371drop table if exists #Assertion3;
372create table #Assertion3([UID] uniqueidentifier not null primary key);
373insert into [Thing]([UID]) output inserted.[UID] into #Assertion3 values (DEFAULT);
374
375-- create another assertion
376insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
377select a.[UID], Disagreer.[UID], p.[UID], -1, '2018-12-13 15:35'
378from #Assertion3 a, #D Disagreer, #Posit1 p;
379
380-- so Bella and the Disagreer also disagree on the posit stating it is fluffy red
381select * from [v_Assertion]
382order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
383
384-- let Bella assert that there is a very small chance it actually was shaved clean
385-- first create an assertion thing
386drop table if exists #Assertion4;
387create table #Assertion4([UID] uniqueidentifier not null primary key);
388insert into [Thing]([UID]) output inserted.[UID] into #Assertion4 values (DEFAULT);
389
390-- create an assertion
391insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
392select a.[UID], Bella.[UID], p.[UID], 0.05, '2018-12-13 15:35'
393from #Assertion4 a, #B Bella, #Posit2 p;
394
395-- Bella asserts that it is very unlikely, but Archie's beard may have been shaved clean
396select * from [v_Assertion]
397order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
398
399-- create another posit thing
400drop table if exists #Posit3;
401create table #Posit3([UID] uniqueidentifier not null primary key);
402insert into [Thing]([UID]) output inserted.[UID] into #Posit3 values (DEFAULT);
403
404-- create that posit
405insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
406select p.[UID], s.[UID], 'shaved clean', '2018-12-01 13:00'
407from #Posit3 p, #DereferencingSet1 s;
408
409-- we now have three posits
410select * from [Posit];
411
412-- create another assertion thing
413drop table if exists #Assertion5;
414create table #Assertion5([UID] uniqueidentifier not null primary key);
415insert into [Thing]([UID]) output inserted.[UID] into #Assertion5 values (DEFAULT);
416
417-- create an assertion
418insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
419select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:35'
420from #Assertion5 a, #B Bella, #Posit3 p;
421
422-- Bella asserts that at 13:00 Archie had shaved
423select * from [v_Assertion]
424order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
425
426
427/*
428----------- Excerpt from: Modeling Conflicting, Unreliable, and Varying Information -----------
429def. of the information in effect
430Let A be a body of information. The information in effect is a
431subset A(T@, t@) ⊂ A given a bitemporal point in assertion
432and appearance time (T@, t@) ∈ T × t. Assuming P, α, T, D, v,
433and t are free variables, all assertions !(P, p, α, T) ∈ A(T@, t@)
434with p = [D, v, t] are found using the following selection
435criteria:
4361. Let A1 ⊂ A be the assertions in A satisfying T ≤ T@ and
437 t ≤ t@.
4382. Let A2 ⊂ A1 be the assertions in A1 with α 6= 0 and the
439 latest appearance time t for each combination of P and
440 D, excluding those assertions !(P, p, α, T) for which an
441 assertion !(P, p, 0, T0) exists with T ≤ T0 ≤ T@.
4423. Let A(T@, t@) ⊂ A2 be the assertions from A2 with the
443 latest assertion time T for each combination of P, D, and v.
444-----------------------------------------------------------------------------------------------
445
446A function for the information in effect is created below according
447to the selection criteria defined above.
448*/
449go
450create or alter function [Information_in_Effect] (
451 @appearanceTime datetime,
452 @assertionTime datetime
453)
454returns table as return
455with A1 as (
456 select
457 *
458 from
459 [v_Assertion]
460 where
461 [AppearanceTime] <= @appearanceTime
462 and
463 [AssertionTime] <= @assertionTime
464),
465A2 as (
466 select
467 a.*
468 from
469 A1 a
470 where
471 a.[Reliability] <> 0
472 and
473 a.[AppearanceTime] = (
474 select
475 max(s.[AppearanceTime])
476 from
477 A1 s
478 where
479 s.[PositorUID] = a.[PositorUID]
480 and
481 s.[DereferencingSetUID] = a.[DereferencingSetUID]
482 )
483 and not exists (
484 select
485 x.[AssertionUID]
486 from
487 A1 x
488 where
489 x.[PositorUID] = a.[PositorUID]
490 and
491 x.[PositUID] = a.[PositUID]
492 and
493 x.[Reliability] = 0
494 and
495 x.[AssertionTime] between a.AssertionTime and @assertionTime
496 )
497),
498A3 as (
499 select
500 a.*
501 from
502 A2 a
503 where
504 a.[AssertionTime] = (
505 select
506 max(s.[AssertionTime])
507 from
508 A2 s
509 where
510 s.[PositorUID] = a.[PositorUID]
511 and
512 s.[DereferencingSetUID] = a.[DereferencingSetUID]
513 and
514 s.[Value] = a.[Value]
515 )
516)
517select
518 *
519from
520 A3;
521go
522
523-- which information is in effect at 12 and 13 given what was asserted on or before 15:30?
524select * from [Information_in_Effect]('2018-12-01 12:00', '2018-12-13 15:30')
525order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
526select * from [Information_in_Effect]('2018-12-01 13:00', '2018-12-13 15:30')
527order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
528
529-- which information is in effect at 12 and 13 given what was asserted on or before 15:35?
530select * from [Information_in_Effect]('2018-12-01 12:00', '2018-12-13 15:35')
531order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
532select * from [Information_in_Effect]('2018-12-01 13:00', '2018-12-13 15:35')
533order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
534
535/*
536----------- Excerpt from: Modeling Conflicting, Unreliable, and Varying Information -----------
537def. of symmetric
538A body of information is said to be symmetric iff the assertions
539!(P, p, −α, T) and !(P, ̄p, α, T) are equivalent for a posit p and its opposite ̄p.
540
541def. of bounded
542A body of information is said to be bounded iff the reliabilities in the assertions
543!(P, p, α, T) and !(P, ̄p, β, T) satisfy ∣α + β∣ = ∣α + β∣^2.
544
545def. of non-contradictory
546A body of information is said to be non-contradictory iff for
547every information in effect the reliabilities, numbered 1 to n,
548in all positive and negative assertions made by the same positor
549for the same dereferencing set satisfy the inequality:
550 n n
5511/2 ∑ (1 - sign(αi)) + ∑ αi ≤ 1
552 i=1 i=1
553-----------------------------------------------------------------------------------------------
554
555The fact that we can talk about contradictions relies on the assumption that our body
556of information is symmetric. When a body of information is also bounded it is possible to
557transform all information into canonical form (negated values like "not red" are instead
558expressed using "red" and a negative Reliability).
559*/
560
561-- we create a view that checks for contradictions according to the inequality above
562go
563create or alter view [Check_for_Contradictions]
564as
565with bitemporalTimepoints as (
566 select
567 [AppearanceTime], [AssertionTime]
568 from
569 (select distinct [AppearanceTime] from Posit) p
570 cross join
571 (select distinct [AssertionTime] from Assertion) a
572)
573select
574 ineq.*,
575 case
576 when InequalitySum <= 1 then 'Non-Contradictory'
577 else 'Contradictory'
578 end as Result
579from (
580 select
581 t.[AppearanceTime],
582 t.[AssertionTime],
583 iie.[PositorUID],
584 iie.[DereferencingSetUID],
585 0.5 * sum(1 - sign(iie.[Reliability])) + sum(iie.[Reliability]) as InequalitySum
586 from
587 bitemporalTimepoints t
588 cross apply
589 [Information_in_Effect](t.[AppearanceTime], t.[AssertionTime]) iie
590 group by
591 t.[AppearanceTime],
592 t.[AssertionTime],
593 iie.[PositorUID],
594 iie.[DereferencingSetUID]
595) ineq;
596go
597
598-- have contradictory assertions been made?
599select * from [Check_for_Contradictions];
600
601-- which information is in effect at 12 and 13 given latest assertions?
602select * from [Information_in_Effect]('2018-12-01 12:00', getdate())
603order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
604select * from [Information_in_Effect]('2018-12-01 13:00', getdate())
605order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
606
607-- Bella realizes she will remain forever contradictory about 12:00, so she makes another assertion
608drop table if exists #Assertion6;
609create table #Assertion6([UID] uniqueidentifier not null primary key);
610insert into [Thing]([UID]) output inserted.[UID] into #Assertion6 values (DEFAULT);
611
612-- Bella revaluates her earlier assertion from 1 to 0.95
613insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
614select a.[UID], Bella.[UID], p.[UID], 0.95, '2018-12-13 15:40'
615from #Assertion6 a, #B Bella, #Posit1 p;
616
617-- which information is in effect at 12 and 13 given latest assertions?
618select * from [Information_in_Effect]('2018-12-01 12:00', getdate())
619order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
620select * from [Information_in_Effect]('2018-12-01 13:00', getdate())
621order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
622
623-- have contradictory assertions been made?
624select * from [Check_for_Contradictions];
625
626-- the Disagreer is now changing it's mind and retracts the previous statement
627drop table if exists #Assertion7;
628create table #Assertion7([UID] uniqueidentifier not null primary key);
629insert into [Thing]([UID]) output inserted.[UID] into #Assertion7 values (DEFAULT);
630
631-- a retraction has reliability = 0
632insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
633select a.[UID], Disagreer.[UID], p.[UID], 0, '2018-12-13 15:40'
634from #Assertion7 a, #D Disagreer, #Posit2 p;
635
636-- which information is now in effect at 12 and 13 at the time of the retraction?
637select * from [Information_in_Effect]('2018-12-01 12:00', '2018-12-13 15:40')
638order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
639select * from [Information_in_Effect]('2018-12-01 13:00', '2018-12-13 15:40')
640order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
641
642
643-- Bella is going to reiterate that Archie's beard was also shaved clean one hour later
644drop table if exists #Posit4;
645create table #Posit4([UID] uniqueidentifier not null primary key);
646insert into [Thing]([UID]) output inserted.[UID] into #Posit4 values (DEFAULT);
647
648insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
649select p.[UID], s.[UID], 'shaved clean', '2018-12-01 14:00'
650from #Posit4 p, #DereferencingSet1 s;
651
652drop table if exists #Assertion8;
653create table #Assertion8([UID] uniqueidentifier not null primary key);
654insert into [Thing]([UID]) output inserted.[UID] into #Assertion8 values (DEFAULT);
655
656insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
657select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:45'
658from #Assertion8 a, #B Bella, #Posit4 p;
659
660-- the latest assertion is called a "restatement", since the value is not changing
661select * from [v_Assertion]
662order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
663
664-- the information in effect will show the latest restatement
665select * from [Information_in_Effect]('2018-12-01 12:00', '2018-12-13 15:45')
666order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
667select * from [Information_in_Effect]('2018-12-01 13:00', '2018-12-13 15:45')
668order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
669select * from [Information_in_Effect]('2018-12-01 14:00', '2018-12-13 15:45')
670order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
671
672-- Bella is going to reassure that she still is certain Archie's beard was shaved clean at 14
673drop table if exists #Assertion9;
674create table #Assertion9([UID] uniqueidentifier not null primary key);
675insert into [Thing]([UID]) output inserted.[UID] into #Assertion9 values (DEFAULT);
676
677insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
678select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:50'
679from #Assertion9 a, #B Bella, #Posit4 p;
680
681-- the latest assertion is called a "reassertion", since only the assertion time changes
682select * from [v_Assertion]
683order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
684
685-- the information in effect will show the latest restatement and latest reassertion
686select * from [Information_in_Effect]('2018-12-01 12:00', '2018-12-13 15:45')
687order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
688select * from [Information_in_Effect]('2018-12-01 13:00', '2018-12-13 15:45')
689order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
690select * from [Information_in_Effect]('2018-12-01 14:00', '2018-12-13 15:45')
691order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
692select * from [Information_in_Effect]('2018-12-01 14:00', '2018-12-13 15:50')
693order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
694
695--------------------------------------- METADATA ---------------------------------------
696-- The good thing about posits, assertions, and so on being "things" in themselves
697-- is that it makes it possible to produce posits about them as well. This is
698-- usually called metadata.
699
700-- let the Script positor assert when what we have done so far was recorded in
701-- our database right now.
702
703-- we now have 21 things
704select * from [Thing];
705
706-- create another role thing
707drop table if exists #Role2;
708create table #Role2([UID] uniqueidentifier not null primary key);
709insert into [Thing]([UID]) output inserted.[UID] into #Role2 values (DEFAULT);
710
711-- create a new role
712insert into [Role]([RoleUID], [Role])
713select [UID], 'was recorded at'
714from #Role2;
715
716-- check what the role looks like
717select * from [Role];
718
719-- create an appearance thing for every thing we have created
720drop table if exists #Appearance2;
721create table #Appearance2([UID] uniqueidentifier not null primary key);
722merge [Thing] t
723using [Thing] src on 1 = 0 -- make sure everything is "not matched" below
724when not matched then insert ([UID]) values (DEFAULT)
725output inserted.[UID] into #Appearance2;
726
727-- create an equal number of appearances
728insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
729select app.[UID], t.[UID], r.[RoleUID]
730from (select [UID], row_number() over (order by [UID]) as _row from #Appearance2) app
731join (select [UID], row_number() over (order by [UID]) as _row from [Thing]) t
732on t._row = app._row
733cross apply (select [RoleUID] from [Role] where [Role] = 'was recorded at') r;
734
735-- we now have 23 appearances
736select * from [Appearance];
737
738-- create 22 dereferencing set things
739drop table if exists #DereferencingSet2;
740create table #DereferencingSet2([UID] uniqueidentifier not null primary key);
741merge [Thing] t
742using #Appearance2 src on 1 = 0 -- make sure everything is "not matched" below
743when not matched then insert ([UID]) values (DEFAULT)
744output inserted.[UID] into #DereferencingSet2;
745
746-- create 22 dereferencing sets
747insert into [DereferencingSet]([DereferencingSetUID])
748select [UID]
749from #DereferencingSet2;
750
751-- add the appearances to the dereferencing sets
752insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
753select s.[UID], app.[UID]
754from (select [UID], row_number() over (order by [UID]) as _row from #DereferencingSet2) s
755join (select [UID], row_number() over (order by [UID]) as _row from #Appearance2) app
756on s._row = app._row;
757
758-- create 22 posit things
759drop table if exists #Posit5;
760create table #Posit5([UID] uniqueidentifier not null primary key);
761merge [Thing] t
762using #DereferencingSet2 src on 1 = 0 -- make sure everything is "not matched" below
763when not matched then insert ([UID]) values (DEFAULT)
764output inserted.[UID] into #Posit5;
765
766-- create 22 posits (with the limitation that the date needs to be converted to a string)
767insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
768select p.[UID], s.[UID], cast(getdate() as varchar(max)), getdate()
769from (select [UID], row_number() over (order by [UID]) as _row from #Posit5) p
770join (select [UID], row_number() over (order by [UID]) as _row from #DereferencingSet2) s
771on p._row = s._row;
772
773-- we now have 26 posits, the last 22 of which intends to capture metadata
774select * from [v_Posit];
775
776-- finally let the Script positor assert these
777drop table if exists #Assertion10;
778create table #Assertion10([UID] uniqueidentifier not null primary key);
779merge [Thing] t
780using #Posit5 src on 1 = 0 -- make sure everything is "not matched" below
781when not matched then insert ([UID]) values (DEFAULT)
782output inserted.[UID] into #Assertion10;
783
784insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
785select a.[UID], Script.[UID], p.[UID], 1, getdate()
786from (select [UID], row_number() over (order by [UID]) as _row from #Assertion10) a
787join (select [UID], row_number() over (order by [UID]) as _row from #Posit5) p
788on a._row = p._row
789cross apply #S Script;
790
791-- there are now 31 assertions
792select * from [v_Assertion]
793order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
794
795-- note that the metadata is not in effect if we travel back in time
796select * from [Information_in_Effect]('2018-12-01 14:00', '2018-12-13 15:50')
797order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
798
799-- finding all (current) metadata can be done by a condition on our Script positor
800select * from [Information_in_Effect](getdate(), getdate())
801where [PositorUID] = (select top 1 [UID] from #S)
802order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
803
804--------------------------------------- RELATIONSHIPS ---------------------------------------
805-- we will start by marrying Archie and Bella
806
807-- create a few new role things
808drop table if exists #Role3;
809create table #Role3([UID] uniqueidentifier not null primary key);
810insert into [Thing]([UID]) output inserted.[UID] into #Role3 values (DEFAULT);
811drop table if exists #Role4;
812create table #Role4([UID] uniqueidentifier not null primary key);
813insert into [Thing]([UID]) output inserted.[UID] into #Role4 values (DEFAULT);
814drop table if exists #Role5;
815create table #Role5([UID] uniqueidentifier not null primary key);
816insert into [Thing]([UID]) output inserted.[UID] into #Role5 values (DEFAULT);
817
818-- create the new roles
819insert into [Role]([RoleUID], [Role])
820select [UID], 'husband'
821from #Role3;
822insert into [Role]([RoleUID], [Role])
823select [UID], 'wife'
824from #Role4;
825insert into [Role]([RoleUID], [Role])
826select [UID], 'church'
827from #Role5;
828
829-- what roles have got now?
830select * from [Role];
831
832-- we will need a church thing
833drop table if exists #C;
834create table #C([UID] uniqueidentifier not null primary key); -- Church
835
836-- create the Church thing
837insert into [Thing]([UID]) output inserted.[UID] into #C values (DEFAULT);
838
839-- we also need three appearance things
840drop table if exists #Appearance3;
841create table #Appearance3([UID] uniqueidentifier not null primary key);
842insert into [Thing]([UID]) output inserted.[UID] into #Appearance3 values (DEFAULT);
843drop table if exists #Appearance4;
844create table #Appearance4([UID] uniqueidentifier not null primary key);
845insert into [Thing]([UID]) output inserted.[UID] into #Appearance4 values (DEFAULT);
846drop table if exists #Appearance5;
847create table #Appearance5([UID] uniqueidentifier not null primary key);
848insert into [Thing]([UID]) output inserted.[UID] into #Appearance5 values (DEFAULT);
849
850-- create three appearances
851insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
852select app.[UID], Archie.[UID], r.[RoleUID]
853from [Role] r, #Appearance3 app, #A Archie
854where r.[Role] = 'husband';
855insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
856select app.[UID], Bella.[UID], r.[RoleUID]
857from [Role] r, #Appearance4 app, #B Bella
858where r.[Role] = 'wife';
859insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
860select app.[UID], Church.[UID], r.[RoleUID]
861from [Role] r, #Appearance5 app, #C Church
862where r.[Role] = 'church';
863
864-- create a dereferencing set thing
865drop table if exists #DereferencingSet3;
866create table #DereferencingSet3([UID] uniqueidentifier not null primary key);
867insert into [Thing]([UID]) output inserted.[UID] into #DereferencingSet3 values (DEFAULT);
868
869-- create a dereferencing set
870insert into [DereferencingSet]([DereferencingSetUID])
871select [UID]
872from #DereferencingSet3;
873
874-- add the created appearances to the (same) dereferencing set
875insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
876select s.[UID], app.[UID]
877from #DereferencingSet3 s, #Appearance3 app;
878insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
879select s.[UID], app.[UID]
880from #DereferencingSet3 s, #Appearance4 app;
881insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
882select s.[UID], app.[UID]
883from #DereferencingSet3 s, #Appearance5 app;
884
885-- create a posit thing
886drop table if exists #Posit6;
887create table #Posit6([UID] uniqueidentifier not null primary key);
888insert into [Thing]([UID]) output inserted.[UID] into #Posit6 values (DEFAULT);
889
890-- create a posit
891insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
892select p.[UID], s.[UID], 'married', '2004-06-19 15:00'
893from #Posit6 p, #DereferencingSet3 s;
894
895-- now the posit is complete (note that the set has three members now)
896select * from [v_Posit]
897where [DereferencingSetUID] = (select top 1 [UID] from #DereferencingSet3);
898
899-- let Bella assert this, so first create an assertion thing
900drop table if exists #Assertion11;
901create table #Assertion11([UID] uniqueidentifier not null primary key);
902insert into [Thing]([UID]) output inserted.[UID] into #Assertion11 values (DEFAULT);
903
904-- create an assertion
905insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
906select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:55'
907from #Assertion11 a, #B Bella, #Posit6 p;
908
909-- now Bella has asserted the marriage
910select * from [v_Assertion]
911where [DereferencingSetUID] = (select top 1 [UID] from #DereferencingSet3)
912order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
913
914-- let them divorce a while later (using the same dereferencing set)
915-- create a posit thing
916drop table if exists #Posit7;
917create table #Posit7([UID] uniqueidentifier not null primary key);
918insert into [Thing]([UID]) output inserted.[UID] into #Posit7 values (DEFAULT);
919
920-- create a posit
921insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
922select p.[UID], s.[UID], 'divorced', '2010-01-31 10:00'
923from #Posit7 p, #DereferencingSet3 s;
924
925-- the state of this dereferencing set has transitioned from 'married' to 'divorced'
926select * from [v_Posit]
927where [DereferencingSetUID] = (select top 1 [UID] from #DereferencingSet3);
928
929-- let Bella assert this as well, so first create an assertion thing
930drop table if exists #Assertion12;
931create table #Assertion12([UID] uniqueidentifier not null primary key);
932insert into [Thing]([UID]) output inserted.[UID] into #Assertion12 values (DEFAULT);
933
934-- create an assertion
935insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
936select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:55'
937from #Assertion12 a, #B Bella, #Posit7 p;
938
939-- now Bella has asserted the divorce
940select * from [v_Assertion]
941where [DereferencingSetUID] = (select top 1 [UID] from #DereferencingSet3)
942order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
943
944-- however, as it turns out, Bella remarried the Disagreer at a later time, but not in a church
945-- that means a new dereferencing set is needed and this time without the church
946
947-- first the Disagreer must appear as husband
948drop table if exists #Appearance6;
949create table #Appearance6([UID] uniqueidentifier not null primary key);
950insert into [Thing]([UID]) output inserted.[UID] into #Appearance6 values (DEFAULT);
951
952insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
953select app.[UID], Disagreer.[UID], r.[RoleUID]
954from [Role] r, #Appearance6 app, #D Disagreer
955where r.[Role] = 'husband';
956
957-- create a dereferencing set thing
958drop table if exists #DereferencingSet4;
959create table #DereferencingSet4([UID] uniqueidentifier not null primary key);
960insert into [Thing]([UID]) output inserted.[UID] into #DereferencingSet4 values (DEFAULT);
961
962-- create a dereferencing set
963insert into [DereferencingSet]([DereferencingSetUID])
964select [UID]
965from #DereferencingSet4;
966
967-- add the created appearances to the dereferencing set (with only two members)
968insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
969select s.[UID], app.[UID]
970from #DereferencingSet4 s, #Appearance6 app;
971insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
972select s.[UID], app.[UID]
973from #DereferencingSet4 s, #Appearance4 app;
974
975-- create a posit thing
976drop table if exists #Posit8;
977create table #Posit8([UID] uniqueidentifier not null primary key);
978insert into [Thing]([UID]) output inserted.[UID] into #Posit8 values (DEFAULT);
979
980-- create a posit
981insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
982select p.[UID], s.[UID], 'married', '2011-11-11 11:11'
983from #Posit8 p, #DereferencingSet4 s;
984
985-- let Bella assert this as well, so first create an assertion thing
986drop table if exists #Assertion13;
987create table #Assertion13([UID] uniqueidentifier not null primary key);
988insert into [Thing]([UID]) output inserted.[UID] into #Assertion13 values (DEFAULT);
989
990-- create an assertion
991insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
992select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:55'
993from #Assertion13 a, #B Bella, #Posit8 p;
994
995-- now Bella has asserted her second marriage
996-- we can find this from finding every assertion in which Bella has appeared in the wife role
997-- regardless if the relationship involves a church or not
998-- note: it is also possible to achieve the same result using joins instead of the XML query
999select a.* from [v_Assertion] a cross apply #B Bella
1000where DereferencingSet.exist('/Appearance[@ThingUID = sql:column("Bella.UID") and @Role = "wife"]') = 1
1001order by [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
1002
1003-- but, Bella made a mistake, the appearance time of her second marriage is not correct
1004-- so she first makes a retraction of the erroneous assertion
1005drop table if exists #Assertion14;
1006create table #Assertion14([UID] uniqueidentifier not null primary key);
1007insert into [Thing]([UID]) output inserted.[UID] into #Assertion14 values (DEFAULT);
1008
1009-- create an assertion (that is a retraction since Reliability = 0)
1010insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
1011select a.[UID], Bella.[UID], p.[UID], 0, '2018-12-13 16:00'
1012from #Assertion14 a, #B Bella, #Posit8 p;
1013
1014-- in the view of all assertions the retraction is now visible
1015select a.* from [v_Assertion] a cross apply #B Bella
1016where DereferencingSet.exist('/Appearance[@ThingUID = sql:column("Bella.UID") and @Role = "wife"]') = 1
1017order by [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
1018
1019-- which means that the latest information we have is that Bella is divorced
1020select a.* from [Information_in_Effect](getdate(), getdate()) a cross apply #B Bella
1021where DereferencingSet.exist('/Appearance[@ThingUID = sql:column("Bella.UID") and @Role = "wife"]') = 1
1022order by [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
1023
1024-- so Bella needs to assert a different posit, with the correct appearance time
1025drop table if exists #Posit9;
1026create table #Posit9([UID] uniqueidentifier not null primary key);
1027insert into [Thing]([UID]) output inserted.[UID] into #Posit9 values (DEFAULT);
1028
1029insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
1030select p.[UID], s.[UID], 'married', '2012-12-12 12:12'
1031from #Posit9 p, #DereferencingSet4 s;
1032
1033drop table if exists #Assertion15;
1034create table #Assertion15([UID] uniqueidentifier not null primary key);
1035insert into [Thing]([UID]) output inserted.[UID] into #Assertion15 values (DEFAULT);
1036
1037insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
1038select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 16:00'
1039from #Assertion15 a, #B Bella, #Posit9 p;
1040
1041-- in the view of all assertions the correction is now visible
1042select a.* from [v_Assertion] a cross apply #B Bella
1043where DereferencingSet.exist('/Appearance[@ThingUID = sql:column("Bella.UID") and @Role = "wife"]') = 1
1044order by [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
1045
1046-- new the latest information we have is that Bella is married, but note that the earlier divorce is also shown
1047-- due to it having a different dereferencing set
1048select a.* from [Information_in_Effect](getdate(), getdate()) a cross apply #B Bella
1049where DereferencingSet.exist('/Appearance[@ThingUID = sql:column("Bella.UID") and @Role = "wife"]') = 1
1050order by [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
1051
1052
1053------------------------------------------- MODELING ------------------------------------------
1054/*
1055----------- Excerpt from: Modeling Conflicting, Unreliable, and Varying Information -----------
1056def. of a classifier and a class
1057Let "is class" be a role reserved for the purpose of modeling.
1058A posit pc = [{(C, is class)}, c, t], defines the name
1059of a class through the string c and associates the unique
1060identifier C with it. A classifier is a relationship that binds
1061a thing to a class, expressed through posits on the form
1062pM = [{(i, thing),(C, class)}, v, t].
1063-----------------------------------------------------------------------------------------------
1064
1065It is time for the Modeler to step in and tell us what some of our things are.
1066*/
1067-- we need three new roles in order to create a model
1068drop table if exists #Role6;
1069create table #Role6([UID] uniqueidentifier not null primary key);
1070insert into [Thing]([UID]) output inserted.[UID] into #Role6 values (DEFAULT);
1071drop table if exists #Role7;
1072create table #Role7([UID] uniqueidentifier not null primary key);
1073insert into [Thing]([UID]) output inserted.[UID] into #Role7 values (DEFAULT);
1074drop table if exists #Role8;
1075create table #Role8([UID] uniqueidentifier not null primary key);
1076insert into [Thing]([UID]) output inserted.[UID] into #Role8 values (DEFAULT);
1077
1078-- create the new roles
1079insert into [Role]([RoleUID], [Role])
1080select [UID], 'is class'
1081from #Role6;
1082insert into [Role]([RoleUID], [Role])
1083select [UID], 'thing'
1084from #Role7;
1085insert into [Role]([RoleUID], [Role])
1086select [UID], 'class'
1087from #Role8;
1088
1089-- what roles have got now?
1090select * from [Role] order by RoleUID;
1091
1092-- create two class things
1093drop table if exists #Class1;
1094create table #Class1([UID] uniqueidentifier not null primary key);
1095insert into [Thing]([UID]) output inserted.[UID] into #Class1 values (DEFAULT);
1096drop table if exists #Class2;
1097create table #Class2([UID] uniqueidentifier not null primary key);
1098insert into [Thing]([UID]) output inserted.[UID] into #Class2 values (DEFAULT);
1099
1100-- the classes need names, so first they need to appear with the "is class" role
1101drop table if exists #Appearance7;
1102create table #Appearance7([UID] uniqueidentifier not null primary key);
1103insert into [Thing]([UID]) output inserted.[UID] into #Appearance7 values (DEFAULT);
1104drop table if exists #Appearance8;
1105create table #Appearance8([UID] uniqueidentifier not null primary key);
1106insert into [Thing]([UID]) output inserted.[UID] into #Appearance8 values (DEFAULT);
1107
1108insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
1109select app.[UID], c.[UID], r.[RoleUID]
1110from [Role] r, #Appearance7 app, #Class1 c
1111where r.[Role] = 'is class';
1112insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
1113select app.[UID], c.[UID], r.[RoleUID]
1114from [Role] r, #Appearance8 app, #Class2 c
1115where r.[Role] = 'is class';
1116
1117-- create a dereferencing set thing
1118drop table if exists #DereferencingSet5;
1119create table #DereferencingSet5([UID] uniqueidentifier not null primary key);
1120insert into [Thing]([UID]) output inserted.[UID] into #DereferencingSet5 values (DEFAULT);
1121drop table if exists #DereferencingSet6;
1122create table #DereferencingSet6([UID] uniqueidentifier not null primary key);
1123insert into [Thing]([UID]) output inserted.[UID] into #DereferencingSet6 values (DEFAULT);
1124
1125-- create dereferencing sets
1126insert into [DereferencingSet]([DereferencingSetUID])
1127select [UID]
1128from #DereferencingSet5;
1129insert into [DereferencingSet]([DereferencingSetUID])
1130select [UID]
1131from #DereferencingSet6;
1132
1133-- add the created appearances
1134insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
1135select s.[UID], app.[UID]
1136from #DereferencingSet5 s, #Appearance7 app;
1137insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
1138select s.[UID], app.[UID]
1139from #DereferencingSet6 s, #Appearance8 app;
1140
1141-- two posit things
1142drop table if exists #Posit10;
1143create table #Posit10([UID] uniqueidentifier not null primary key);
1144insert into [Thing]([UID]) output inserted.[UID] into #Posit10 values (DEFAULT);
1145drop table if exists #Posit11;
1146create table #Posit11([UID] uniqueidentifier not null primary key);
1147insert into [Thing]([UID]) output inserted.[UID] into #Posit11 values (DEFAULT);
1148
1149-- two posits
1150insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
1151select p.[UID], s.[UID], 'Person', '1901-01-01' -- will represent dawn of time
1152from #Posit10 p, #DereferencingSet5 s;
1153insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
1154select p.[UID], s.[UID], 'Cathedral', '1901-01-01' -- will represent dawn of time
1155from #Posit11 p, #DereferencingSet6 s;
1156
1157-- now, the Modeler must assert these
1158drop table if exists #Assertion16;
1159create table #Assertion16([UID] uniqueidentifier not null primary key);
1160insert into [Thing]([UID]) output inserted.[UID] into #Assertion16 values (DEFAULT);
1161drop table if exists #Assertion17;
1162create table #Assertion17([UID] uniqueidentifier not null primary key);
1163insert into [Thing]([UID]) output inserted.[UID] into #Assertion17 values (DEFAULT);
1164
1165insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
1166select a.[UID], Modeler.[UID], p.[UID], 1, '2018-12-01 00:00'
1167from #Assertion16 a, #M Modeler, #Posit10 p;
1168insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
1169select a.[UID], Modeler.[UID], p.[UID], 1, '2018-12-01 00:00'
1170from #Assertion17 a, #M Modeler, #Posit11 p;
1171
1172-- list all classes
1173select a.* from [v_Assertion] a
1174where DereferencingSet.exist('/Appearance[@Role = "is class"]') = 1
1175order by [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
1176
1177-- lot's of work, but two classes are defined
1178-- they have their own unique identifiers, so many more roles can be added to provide additional
1179-- information about the classes
1180
1181-- now it is time to associate some things with the classes
1182-- every such thing needs an appearance with the 'thing' role
1183drop table if exists #Appearance9;
1184create table #Appearance9([UID] uniqueidentifier not null primary key);
1185merge [Thing] t
1186using (
1187 select [UID] from #A
1188 union all
1189 select [UID] from #B
1190 union all
1191 select [UID] from #C
1192 union all
1193 select [UID] from #M
1194 union all
1195 select [UID] from #D
1196) src
1197on 1 = 0 -- make sure everything is "not matched" below
1198when not matched then insert ([UID]) values (DEFAULT)
1199output inserted.[UID] into #Appearance9;
1200
1201-- create an equal number of appearances
1202insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
1203select app.[UID], t.[UID], r.[RoleUID]
1204from (select [UID], row_number() over (order by [UID]) as _row from #Appearance9) app
1205join (select [UID], row_number() over (order by [UID]) as _row from (
1206 select [UID] from #A
1207 union all
1208 select [UID] from #B
1209 union all
1210 select [UID] from #C
1211 union all
1212 select [UID] from #M
1213 union all
1214 select [UID] from #D
1215) things ) t
1216on t._row = app._row
1217cross apply (select [RoleUID] from [Role] where [Role] = 'thing') r;
1218
1219-- then two more appearances (one for each associated class)
1220drop table if exists #Appearance10;
1221create table #Appearance10([UID] uniqueidentifier not null primary key);
1222insert into [Thing]([UID]) output inserted.[UID] into #Appearance10 values (DEFAULT);
1223drop table if exists #Appearance11;
1224create table #Appearance11([UID] uniqueidentifier not null primary key);
1225insert into [Thing]([UID]) output inserted.[UID] into #Appearance11 values (DEFAULT);
1226
1227insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
1228select app.[UID], c.[UID], r.[RoleUID]
1229from [Role] r, #Appearance10 app, #Class1 c
1230where r.[Role] = 'class';
1231insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
1232select app.[UID], c.[UID], r.[RoleUID]
1233from [Role] r, #Appearance11 app, #Class2 c
1234where r.[Role] = 'class';
1235
1236-- create dereferencing set things (equal in number to the things we want to classify)
1237drop table if exists #DereferencingSet7;
1238create table #DereferencingSet7([UID] uniqueidentifier not null primary key);
1239merge [Thing] t
1240using #Appearance9 src on 1 = 0 -- make sure everything is "not matched" below
1241when not matched then insert ([UID]) values (DEFAULT)
1242output inserted.[UID] into #DereferencingSet7;
1243
1244-- create 22 dereferencing sets
1245insert into [DereferencingSet]([DereferencingSetUID])
1246select [UID]
1247from #DereferencingSet7;
1248
1249-- add the appearances to the dereferencing sets
1250-- first the things
1251insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
1252select s.[UID], app.[UID]
1253from (select [UID], row_number() over (order by [UID]) as _row from #DereferencingSet7) s
1254join (select [UID], row_number() over (order by [UID]) as _row from #Appearance9) app
1255on s._row = app._row;
1256-- then the classes
1257insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
1258select
1259 s.[UID],
1260 case
1261 when exists (
1262 select top 1 d.[DereferencingSetUID]
1263 from [Dereference] d
1264 join [Appearance] a
1265 on a.[AppearanceUID] = d.[AppearanceUID]
1266 join (
1267 select [UID] from #A
1268 union all
1269 select [UID] from #B
1270 union all
1271 select [UID] from #M
1272 union all
1273 select [UID] from #D
1274 ) persons
1275 on persons.[UID] = a.[ThingUID]
1276 where d.[DereferencingSetUID] = s.[UID]
1277 ) then Person.[UID]
1278 else Cathedral.[UID]
1279 end
1280from #DereferencingSet7 s, #Appearance10 Person, #Appearance11 Cathedral;
1281
1282-- create posit things
1283drop table if exists #Posit12;
1284create table #Posit12([UID] uniqueidentifier not null primary key);
1285merge [Thing] t
1286using #DereferencingSet7 src on 1 = 0 -- make sure everything is "not matched" below
1287when not matched then insert ([UID]) values (DEFAULT)
1288output inserted.[UID] into #Posit12;
1289
1290-- create posits
1291insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
1292select p.[UID], s.[UID], 'active', '1901-01-01'
1293from (select [UID], row_number() over (order by [UID]) as _row from #Posit12) p
1294join (select [UID], row_number() over (order by [UID]) as _row from #DereferencingSet7) s
1295on p._row = s._row;
1296
1297-- finally let the Modeler positor assert these
1298drop table if exists #Assertion18;
1299create table #Assertion18([UID] uniqueidentifier not null primary key);
1300merge [Thing] t
1301using #Posit12 src on 1 = 0 -- make sure everything is "not matched" below
1302when not matched then insert ([UID]) values (DEFAULT)
1303output inserted.[UID] into #Assertion18;
1304
1305insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
1306select a.[UID], Modeler.[UID], p.[UID], 1, '2018-12-01 00:00'
1307from (select [UID], row_number() over (order by [UID]) as _row from #Assertion18) a
1308join (select [UID], row_number() over (order by [UID]) as _row from #Posit12) p
1309on a._row = p._row
1310cross apply #M Modeler;
1311
1312-- list all classes and classifiers
1313-- as can be seen there are four things of class Person and one thing of class Cathedral
1314select a.* from [v_Assertion] a
1315where DereferencingSet.exist('/Appearance[@Role = "is class" or @Role = "class"]') = 1
1316order by [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
1317
1318
1319/*
1320----------- Excerpt from: Modeling Conflicting, Unreliable, and Varying Information -----------
1321def. of a posit type
1322A posit type, Ï„(p) = [{(C1,r1), . . . ,(Cn,rn)}, Ï„(v), Ï„(t)], for a
1323posit p = [{(i1,r1), . . . ,(in,rn)}, v, t], is a structure constructed
1324by replacing unique identifiers, ij with the unique identifiers of their
1325class, Cj, the value, v, with its data type, Ï„(v), and the time point, t,
1326with its data type, Ï„(t).
1327-----------------------------------------------------------------------------------------------
1328
1329A posit type is a structure, but posits in the relational model can only have
1330values of type varchar(max), so it cannot hold such a structure. It could either
1331be expressed as serialized XML or JSON, or we could let the field contain a
1332reference to an identifier in a separate table that replicates the structure.
1333Since a posit type contains a set with variable number of members, it is
1334easier in this case to express it using XML.
1335*/
1336
1337-- an example type for the wedding relationship that involves a church
1338declare @type1 xml = '
1339<PositType>
1340 <DereferencingSet>
1341 <Appearance Class="' + (select cast([UID] as varchar(max)) from #Class1) + '" Role="husband"/>
1342 <Appearance Class="' + (select cast([UID] as varchar(max)) from #Class1) + '" Role="wife"/>
1343 <Appearance Class="' + (select cast([UID] as varchar(max)) from #Class2) + '" Role="church"/>
1344 </DereferencingSet>
1345 <Value Type="varchar(max)"/>
1346 <Time Type="datetime"/>
1347</PositType>
1348';