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