SHARE
TWEET

Transitional Modeling 20181217.2

anchormodeling Dec 18th, 2018 100 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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. --
  15. drop function if exists [Information_in_Effect];
  16. drop view if exists [v_Assertion];
  17. drop view if exists [v_Posit];
  18. drop table if exists [Assertion];
  19. drop table if exists [Posit];
  20. drop table if exists [Dereference];
  21. drop table if exists [DereferencingSet];
  22. drop table if exists [Appearance];
  23. drop table if exists [Role];
  24. drop 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.
  29. create 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.  
  40. create 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 -----------
  56. def. of universal
  57. A body of information is said to be universal iff positors agree on all appearances.
  58. -----------------------------------------------------------------------------------------------
  59.  
  60. In order to disagree you must first agree upon something upon which the difference in
  61. opinion lies. At the very least, positors must agree on appearances. In other words
  62. if two positors want to have an opinion about something, they must first come to the
  63. conclusion that this something boils down to the same unique identifier for both of them
  64. and that they mean the same when they talk about the roles it may appear in.
  65.  
  66. We will assume that talk about "Archie's beard" by any positor means that it is the
  67. same Archie and the same property they are talking about.
  68. */
  69. create 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.  
  91. create 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.  
  101. create 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 -----------
  118. def. of canonical
  119. A body of information is said to be canonical iff all assertions
  120. are made against posits without negated values.
  121. -----------------------------------------------------------------------------------------------
  122.  
  123. In practice, the value of a posit may be of any data type, primitive or complex, but due to
  124. SQL Server lacking support for generics (looks like <T> in many programming languages) we
  125. have to limit the Value column to varchar(max). Appearance time may also be any time type,
  126. exact or fuzzy, in order to represent that the value appeared exactly since some specified
  127. time or inexactly within some period of time. Here the AppearanceTime column is limited
  128. to a datetime.
  129.  
  130. Finally, in order to represent information, canonical form is used, which simply means that
  131. values are stored without negation, for example "red" is acceptable, and "not red" is not.
  132. Opposite opinions are instead handled using negative Reliability in assertions.
  133. */
  134. create 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 -----------
  152. def. of a body of information
  153. A body of information is a set of true assertions.
  154.  
  155. def. of exclusive
  156. A body of information is said to be exclusive iff no assertions
  157. in which only the reliability differs exist.
  158. -----------------------------------------------------------------------------------------------
  159.  
  160. We will assume that any data inserted into this table represents true assertions. We will not
  161. constrain the Reliability column, but in practice it should be in the interval [-1, 1].  
  162. The constraint [unique_Assertion] ensures that the stored assertions are exclusive, so that
  163. there is no ambiguity with respect to the assigned Reliability.
  164. */
  165. create 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.  
  190. go
  191. create or alter view [v_Posit]
  192. as
  193. select
  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]
  230. from
  231.     [Posit] p
  232.  
  233. go
  234. create or alter view [v_Assertion]
  235. as
  236. select
  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]
  254. from
  255.     [Assertion] a
  256. join
  257.     [v_Posit] p
  258. on
  259.     p.[PositUID] = a.[PositUID]
  260. go
  261.  
  262. --------------------------------------- PROPERTIES ---------------------------------------
  263.  
  264. -- we have to use tables since the OUTPUT clause only works for tables
  265. drop table if exists #A;
  266. drop table if exists #B;
  267. drop table if exists #M;
  268. drop table if exists #S;
  269. drop table if exists #D;
  270. create table #A([UID] uniqueidentifier not null primary key); -- Archie
  271. create table #B([UID] uniqueidentifier not null primary key); -- Bella
  272. create table #M([UID] uniqueidentifier not null primary key); -- Modeler
  273. create table #S([UID] uniqueidentifier not null primary key); -- Script
  274. create table #D([UID] uniqueidentifier not null primary key); -- Disagreer
  275.  
  276. -- create the Archie thing
  277. insert into [Thing]([UID]) output inserted.[UID] into #A values (DEFAULT);
  278. -- create the Bella thing
  279. insert into [Thing]([UID]) output inserted.[UID] into #B values (DEFAULT);
  280. -- create the Modeler thing
  281. insert into [Thing]([UID]) output inserted.[UID] into #M values (DEFAULT);
  282. -- create the Script thing
  283. insert into [Thing]([UID]) output inserted.[UID] into #S values (DEFAULT);
  284. -- create the Disagreer thing
  285. insert into [Thing]([UID]) output inserted.[UID] into #D values (DEFAULT);
  286.  
  287. -- we now have five things
  288. select * from [Thing];
  289.  
  290. -- create a role thing and an appearance thing
  291. drop table if exists #Role1;
  292. drop table if exists #Appearance1;
  293. create table #Role1([UID] uniqueidentifier not null primary key);
  294. create table #Appearance1([UID] uniqueidentifier not null primary key);
  295. insert into [Thing]([UID]) output inserted.[UID] into #Role1 values (DEFAULT);
  296. insert into [Thing]([UID]) output inserted.[UID] into #Appearance1 values (DEFAULT);
  297.  
  298. -- create a role
  299. insert into [Role]([RoleUID], [Role])
  300. select [UID], 'has beard'
  301. from #Role1;
  302.  
  303. -- check what the role looks like
  304. select * from [Role];
  305.  
  306. -- create an appearance
  307. insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
  308. select app.[UID], Archie.[UID], r.[RoleUID]
  309. from [Role] r, #Appearance1 app, #A Archie
  310. where r.[Role] = 'has beard';
  311.  
  312. -- we now have one appearance
  313. select * from [Appearance];
  314.  
  315. -- create a dereferencing set thing
  316. drop table if exists #DereferencingSet1;
  317. create table #DereferencingSet1([UID] uniqueidentifier not null primary key);
  318. insert into [Thing]([UID]) output inserted.[UID] into #DereferencingSet1 values (DEFAULT);
  319.  
  320. -- create a dereferencing set
  321. insert into [DereferencingSet]([DereferencingSetUID])
  322. select [UID]
  323. from #DereferencingSet1;
  324.  
  325. -- add the appearance to the dereferencing set
  326. insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
  327. select s.[UID], app.[UID]
  328. from #DereferencingSet1 s, #Appearance1 app;
  329.  
  330. -- create a posit thing
  331. drop table if exists #Posit1;
  332. create table #Posit1([UID] uniqueidentifier not null primary key);
  333. insert into [Thing]([UID]) output inserted.[UID] into #Posit1 values (DEFAULT);
  334.  
  335. -- create a posit
  336. insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
  337. select p.[UID], s.[UID], 'fluffy red', '2018-12-01 12:00'
  338. from #Posit1 p, #DereferencingSet1 s;
  339.  
  340. -- we now have one posit
  341. select * from [Posit];
  342.  
  343. -- now the posit is complete
  344. select * from [v_Posit];
  345.  
  346. -- let Bella assert this, so first create an assertion thing
  347. drop table if exists #Assertion1;
  348. create table #Assertion1([UID] uniqueidentifier not null primary key);
  349. insert into [Thing]([UID]) output inserted.[UID] into #Assertion1 values (DEFAULT);
  350.  
  351. -- create an assertion
  352. insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
  353. select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:30'
  354. from #Assertion1 a, #B Bella, #Posit1 p;
  355.  
  356. -- now we have one assertion
  357. select * from [Assertion];
  358.  
  359. -- now the assertion is complete
  360. select * from [v_Assertion]
  361. order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  362.  
  363. -- create another posit thing
  364. drop table if exists #Posit2;
  365. create table #Posit2([UID] uniqueidentifier not null primary key);
  366. insert into [Thing]([UID]) output inserted.[UID] into #Posit2 values (DEFAULT);
  367.  
  368. -- create that posit
  369. insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
  370. select p.[UID], s.[UID], 'shaved clean', '2018-12-01 12:00'
  371. from #Posit2 p, #DereferencingSet1 s;
  372.  
  373. -- we now have two posits
  374. select * from [Posit];
  375.  
  376. -- let the Disagreer assert this, so first create another assertion thing
  377. drop table if exists #Assertion2;
  378. create table #Assertion2([UID] uniqueidentifier not null primary key);
  379. insert into [Thing]([UID]) output inserted.[UID] into #Assertion2 values (DEFAULT);
  380.  
  381. -- create another assertion
  382. insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
  383. select a.[UID], Disagreer.[UID], p.[UID], 1, '2018-12-13 15:30'
  384. from #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
  388. select * from [v_Assertion]
  389. order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  390.  
  391. -- let the Disagreer disagree on the posit Bella asserted
  392. drop table if exists #Assertion3;
  393. create table #Assertion3([UID] uniqueidentifier not null primary key);
  394. insert into [Thing]([UID]) output inserted.[UID] into #Assertion3 values (DEFAULT);
  395.  
  396. -- create another assertion
  397. insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
  398. select a.[UID], Disagreer.[UID], p.[UID], -1, '2018-12-13 15:35'
  399. from #Assertion3 a, #D Disagreer, #Posit1 p;
  400.  
  401. -- so Bella and the Disagreer also disagree on the posit stating it is fluffy red
  402. select * from [v_Assertion]
  403. order 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
  407. drop table if exists #Assertion4;
  408. create table #Assertion4([UID] uniqueidentifier not null primary key);
  409. insert into [Thing]([UID]) output inserted.[UID] into #Assertion4 values (DEFAULT);
  410.  
  411. -- create an assertion
  412. insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
  413. select a.[UID], Bella.[UID], p.[UID], 0.05, '2018-12-13 15:35'
  414. from #Assertion4 a, #B Bella, #Posit2 p;
  415.  
  416. -- Bella asserts that it is very unlikely, but Archie's beard may have been shaved clean
  417. select * from [v_Assertion]
  418. order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  419.  
  420. -- create another posit thing
  421. drop table if exists #Posit3;
  422. create table #Posit3([UID] uniqueidentifier not null primary key);
  423. insert into [Thing]([UID]) output inserted.[UID] into #Posit3 values (DEFAULT);
  424.  
  425. -- create that posit
  426. insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
  427. select p.[UID], s.[UID], 'shaved clean', '2018-12-01 13:00'
  428. from #Posit3 p, #DereferencingSet1 s;
  429.  
  430. -- we now have three posits
  431. select * from [Posit];
  432.  
  433. -- create another assertion thing
  434. drop table if exists #Assertion5;
  435. create table #Assertion5([UID] uniqueidentifier not null primary key);
  436. insert into [Thing]([UID]) output inserted.[UID] into #Assertion5 values (DEFAULT);
  437.  
  438. -- create an assertion
  439. insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
  440. select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:35'
  441. from #Assertion5 a, #B Bella, #Posit3 p;
  442.  
  443. -- Bella asserts that at 13:00 Archie had shaved
  444. select * from [v_Assertion]
  445. order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  446.  
  447.  
  448. /*
  449. ----------- Excerpt from: Modeling Conflicting, Unreliable, and Varying Information -----------
  450. def. of the information in effect
  451. Let A be a body of information. The information in effect is a
  452. subset A(T@, t@) ⊂ A given a bitemporal point in assertion
  453. and appearance time (T@, t@) ∈ T × t. Assuming P, α, T, D, v,
  454. and t are free variables, all assertions !(P, p, α, T) ∈ A(T@, t@)
  455. with p = [D, v, t] are found using the following selection
  456. criteria:
  457. 1. Let A1 ⊂ A be the assertions in A satisfying T ≤ T@ and
  458.    t ≤ t@.
  459. 2. 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@.
  463. 3. 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.  
  467. A function for the information in effect is created below according
  468. to the selection criteria defined above.
  469. */
  470. go
  471. create or alter function [Information_in_Effect] (
  472.     @appearanceTime datetime,
  473.     @assertionTime datetime
  474. )
  475. returns table as return
  476. with A1 as (
  477.     select
  478.         *
  479.     from
  480.         [v_Assertion]
  481.     where
  482.         [AppearanceTime] <= @appearanceTime
  483.     and
  484.         [AssertionTime] <= @assertionTime
  485. ),
  486. A2 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. ),
  519. A3 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. )
  538. select
  539.     *
  540. from
  541.     A3;
  542. go
  543.  
  544. -- which information is in effect at 12 and 13 given what was asserted on or before 15:30?
  545. select * from [Information_in_Effect]('2018-12-01 12:00', '2018-12-13 15:30')
  546. order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  547. select * from [Information_in_Effect]('2018-12-01 13:00', '2018-12-13 15:30')
  548. order 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?
  551. select * from [Information_in_Effect]('2018-12-01 12:00', '2018-12-13 15:35')
  552. order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  553. select * from [Information_in_Effect]('2018-12-01 13:00', '2018-12-13 15:35')
  554. order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  555.  
  556. /*
  557. ----------- Excerpt from: Modeling Conflicting, Unreliable, and Varying Information -----------
  558. def. of symmetric
  559. A 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.  
  562. def. of bounded
  563. A 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.  
  566. def. of non-contradictory
  567. A body of information is said to be non-contradictory iff for
  568. every information in effect the reliabilities, numbered 1 to n,
  569. in all positive and negative assertions made by the same positor
  570. for the same dereferencing set satisfy the inequality:
  571.     n                  n
  572. 1/2 ∑ (1 - sign(αi)) + ∑ αi ≤ 1
  573.    i=1                i=1
  574. -----------------------------------------------------------------------------------------------
  575.  
  576. The fact that we can talk about contradictions relies on the assumption that our body
  577. of information is symmetric. When a body of information is also bounded it is possible to
  578. transform all information into canonical form (negated values like "not red" are instead
  579. expressed using "red" and a negative Reliability).
  580. */
  581.  
  582. -- we create a view that checks for contradictions according to the inequality above
  583. go
  584. create or alter view [Check_for_Contradictions]
  585. as
  586. with 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. )
  594. select
  595.     ineq.*,
  596.     case
  597.         when InequalitySum <= 1 then 'Non-Contradictory'
  598.         else 'Contradictory'
  599.     end as Result
  600. from (
  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;
  617. go
  618.  
  619. -- have contradictory assertions been made?
  620. select * from [Check_for_Contradictions];
  621.  
  622. -- which information is in effect at 12 and 13 given latest assertions?
  623. select * from [Information_in_Effect]('2018-12-01 12:00', getdate())
  624. order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  625. select * from [Information_in_Effect]('2018-12-01 13:00', getdate())
  626. order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  627.  
  628. -- Bella realizes she will remain forever contradictory about 12:00, so she makes another assertion
  629. drop table if exists #Assertion6;
  630. create table #Assertion6([UID] uniqueidentifier not null primary key);
  631. insert into [Thing]([UID]) output inserted.[UID] into #Assertion6 values (DEFAULT);
  632.  
  633. -- Bella revaluates her earlier assertion from 1 to 0.95
  634. insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
  635. select a.[UID], Bella.[UID], p.[UID], 0.95, '2018-12-13 15:40'
  636. from #Assertion6 a, #B Bella, #Posit1 p;
  637.  
  638. -- which information is in effect at 12 and 13 given latest assertions?
  639. select * from [Information_in_Effect]('2018-12-01 12:00', getdate())
  640. order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  641. select * from [Information_in_Effect]('2018-12-01 13:00', getdate())
  642. order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  643.  
  644. -- have contradictory assertions been made?
  645. select * from [Check_for_Contradictions];
  646.  
  647. -- the Disagreer is now changing it's mind and retracts the previous statement
  648. drop table if exists #Assertion7;
  649. create table #Assertion7([UID] uniqueidentifier not null primary key);
  650. insert into [Thing]([UID]) output inserted.[UID] into #Assertion7 values (DEFAULT);
  651.  
  652. -- a retraction has reliability = 0
  653. insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
  654. select a.[UID], Disagreer.[UID], p.[UID], 0, '2018-12-13 15:40'
  655. from #Assertion7 a, #D Disagreer, #Posit2 p;
  656.  
  657. -- which information is now in effect at 12 and 13 at the time of the retraction?
  658. select * from [Information_in_Effect]('2018-12-01 12:00', '2018-12-13 15:40')
  659. order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  660. select * from [Information_in_Effect]('2018-12-01 13:00', '2018-12-13 15:40')
  661. order 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
  665. drop table if exists #Posit4;
  666. create table #Posit4([UID] uniqueidentifier not null primary key);
  667. insert into [Thing]([UID]) output inserted.[UID] into #Posit4 values (DEFAULT);
  668.  
  669. insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
  670. select p.[UID], s.[UID], 'shaved clean', '2018-12-01 14:00'
  671. from #Posit4 p, #DereferencingSet1 s;
  672.  
  673. drop table if exists #Assertion8;
  674. create table #Assertion8([UID] uniqueidentifier not null primary key);
  675. insert into [Thing]([UID]) output inserted.[UID] into #Assertion8 values (DEFAULT);
  676.  
  677. insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
  678. select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:45'
  679. from #Assertion8 a, #B Bella, #Posit4 p;
  680.  
  681. -- the latest assertion is called a "restatement", since the value is not changing
  682. select * from [v_Assertion]
  683. order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  684.  
  685. -- the information in effect will show the latest restatement
  686. select * from [Information_in_Effect]('2018-12-01 12:00', '2018-12-13 15:45')
  687. order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  688. select * from [Information_in_Effect]('2018-12-01 13:00', '2018-12-13 15:45')
  689. order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  690. select * from [Information_in_Effect]('2018-12-01 14:00', '2018-12-13 15:45')
  691. order 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
  694. drop table if exists #Assertion9;
  695. create table #Assertion9([UID] uniqueidentifier not null primary key);
  696. insert into [Thing]([UID]) output inserted.[UID] into #Assertion9 values (DEFAULT);
  697.  
  698. insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
  699. select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:50'
  700. from #Assertion9 a, #B Bella, #Posit4 p;
  701.  
  702. -- the latest assertion is called a "reassertion", since only the assertion time changes
  703. select * from [v_Assertion]
  704. order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  705.  
  706. -- the information in effect will show the latest restatement and latest reassertion
  707. select * from [Information_in_Effect]('2018-12-01 12:00', '2018-12-13 15:45')
  708. order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  709. select * from [Information_in_Effect]('2018-12-01 13:00', '2018-12-13 15:45')
  710. order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  711. select * from [Information_in_Effect]('2018-12-01 14:00', '2018-12-13 15:45')
  712. order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  713. select * from [Information_in_Effect]('2018-12-01 14:00', '2018-12-13 15:50')
  714. order 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
  725. select * from [Thing];
  726.  
  727. -- create another role thing
  728. drop table if exists #Role2;
  729. create table #Role2([UID] uniqueidentifier not null primary key);
  730. insert into [Thing]([UID]) output inserted.[UID] into #Role2 values (DEFAULT);
  731.  
  732. -- create a new role
  733. insert into [Role]([RoleUID], [Role])
  734. select [UID], 'was recorded at'
  735. from #Role2;
  736.  
  737. -- check what the role looks like
  738. select * from [Role];
  739.  
  740. -- create an appearance thing for every thing we have created
  741. drop table if exists #Appearance2;
  742. create table #Appearance2([UID] uniqueidentifier not null primary key);
  743. merge [Thing] t
  744. using [Thing] src on 1 = 0 -- make sure everything is "not matched" below
  745. when not matched then insert ([UID]) values (DEFAULT)
  746. output inserted.[UID] into #Appearance2;
  747.  
  748. -- create an equal number of appearances
  749. insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
  750. select app.[UID], t.[UID], r.[RoleUID]
  751. from (select [UID], row_number() over (order by [UID]) as _row from #Appearance2) app
  752. join (select [UID], row_number() over (order by [UID]) as _row from [Thing]) t
  753. on t._row = app._row
  754. cross apply (select [RoleUID] from [Role] where [Role] = 'was recorded at') r;
  755.  
  756. -- we now have 23 appearances
  757. select * from [Appearance];
  758.  
  759. -- create 22 dereferencing set things
  760. drop table if exists #DereferencingSet2;
  761. create table #DereferencingSet2([UID] uniqueidentifier not null primary key);
  762. merge [Thing] t
  763. using #Appearance2 src on 1 = 0 -- make sure everything is "not matched" below
  764. when not matched then insert ([UID]) values (DEFAULT)
  765. output inserted.[UID] into #DereferencingSet2;
  766.  
  767. -- create 22 dereferencing sets
  768. insert into [DereferencingSet]([DereferencingSetUID])
  769. select [UID]
  770. from #DereferencingSet2;
  771.  
  772. -- add the appearances to the dereferencing sets
  773. insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
  774. select s.[UID], app.[UID]
  775. from (select [UID], row_number() over (order by [UID]) as _row from #DereferencingSet2) s
  776. join (select [UID], row_number() over (order by [UID]) as _row from #Appearance2) app
  777. on s._row = app._row;
  778.  
  779. -- create 22 posit things
  780. drop table if exists #Posit5;
  781. create table #Posit5([UID] uniqueidentifier not null primary key);
  782. merge [Thing] t
  783. using #DereferencingSet2 src on 1 = 0 -- make sure everything is "not matched" below
  784. when not matched then insert ([UID]) values (DEFAULT)
  785. output inserted.[UID] into #Posit5;
  786.  
  787. -- create 22 posits (with the limitation that the date needs to be converted to a string)
  788. insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
  789. select p.[UID], s.[UID], cast(getdate() as varchar(max)), getdate()
  790. from (select [UID], row_number() over (order by [UID]) as _row from #Posit5) p
  791. join (select [UID], row_number() over (order by [UID]) as _row from #DereferencingSet2) s
  792. on p._row = s._row;
  793.  
  794. -- we now have 26 posits, the last 22 of which intends to capture metadata
  795. select * from [v_Posit];
  796.  
  797. -- finally let the Script positor assert these
  798. drop table if exists #Assertion10;
  799. create table #Assertion10([UID] uniqueidentifier not null primary key);
  800. merge [Thing] t
  801. using #Posit5 src on 1 = 0 -- make sure everything is "not matched" below
  802. when not matched then insert ([UID]) values (DEFAULT)
  803. output inserted.[UID] into #Assertion10;
  804.  
  805. insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
  806. select a.[UID], Script.[UID], p.[UID], 1, getdate()
  807. from (select [UID], row_number() over (order by [UID]) as _row from #Assertion10) a
  808. join (select [UID], row_number() over (order by [UID]) as _row from #Posit5) p
  809. on a._row = p._row
  810. cross apply #S Script;
  811.  
  812. -- there are now 31 assertions
  813. select * from [v_Assertion]
  814. order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  815.  
  816. -- note that the metadata is not in effect if we travel back in time
  817. select * from [Information_in_Effect]('2018-12-01 14:00', '2018-12-13 15:50')
  818. order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  819.  
  820. -- finding all (current) metadata can be done by a condition on our Script positor
  821. select * from [Information_in_Effect](getdate(), getdate())
  822. where [PositorUID] = (select top 1 [UID] from #S)
  823. order 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
  829. drop table if exists #Role3;
  830. create table #Role3([UID] uniqueidentifier not null primary key);
  831. insert into [Thing]([UID]) output inserted.[UID] into #Role3 values (DEFAULT);
  832. drop table if exists #Role4;
  833. create table #Role4([UID] uniqueidentifier not null primary key);
  834. insert into [Thing]([UID]) output inserted.[UID] into #Role4 values (DEFAULT);
  835. drop table if exists #Role5;
  836. create table #Role5([UID] uniqueidentifier not null primary key);
  837. insert into [Thing]([UID]) output inserted.[UID] into #Role5 values (DEFAULT);
  838.  
  839. -- create the new roles
  840. insert into [Role]([RoleUID], [Role])
  841. select [UID], 'husband'
  842. from #Role3;
  843. insert into [Role]([RoleUID], [Role])
  844. select [UID], 'wife'
  845. from #Role4;
  846. insert into [Role]([RoleUID], [Role])
  847. select [UID], 'church'
  848. from #Role5;
  849.  
  850. -- what roles have got now?
  851. select * from [Role];
  852.  
  853. -- we will need a church thing
  854. drop table if exists #C;
  855. create table #C([UID] uniqueidentifier not null primary key); -- Church
  856.  
  857. -- create the Church thing
  858. insert into [Thing]([UID]) output inserted.[UID] into #C values (DEFAULT);
  859.  
  860. -- we also need three appearance things
  861. drop table if exists #Appearance3;
  862. create table #Appearance3([UID] uniqueidentifier not null primary key);
  863. insert into [Thing]([UID]) output inserted.[UID] into #Appearance3 values (DEFAULT);
  864. drop table if exists #Appearance4;
  865. create table #Appearance4([UID] uniqueidentifier not null primary key);
  866. insert into [Thing]([UID]) output inserted.[UID] into #Appearance4 values (DEFAULT);
  867. drop table if exists #Appearance5;
  868. create table #Appearance5([UID] uniqueidentifier not null primary key);
  869. insert into [Thing]([UID]) output inserted.[UID] into #Appearance5 values (DEFAULT);
  870.  
  871. -- create three appearances
  872. insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
  873. select app.[UID], Archie.[UID], r.[RoleUID]
  874. from [Role] r, #Appearance3 app, #A Archie
  875. where r.[Role] = 'husband';
  876. insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
  877. select app.[UID], Bella.[UID], r.[RoleUID]
  878. from [Role] r, #Appearance4 app, #B Bella
  879. where r.[Role] = 'wife';
  880. insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
  881. select app.[UID], Church.[UID], r.[RoleUID]
  882. from [Role] r, #Appearance5 app, #C Church
  883. where r.[Role] = 'church';
  884.  
  885. -- create a dereferencing set thing
  886. drop table if exists #DereferencingSet3;
  887. create table #DereferencingSet3([UID] uniqueidentifier not null primary key);
  888. insert into [Thing]([UID]) output inserted.[UID] into #DereferencingSet3 values (DEFAULT);
  889.  
  890. -- create a dereferencing set
  891. insert into [DereferencingSet]([DereferencingSetUID])
  892. select [UID]
  893. from #DereferencingSet3;
  894.  
  895. -- add the created appearances to the (same) dereferencing set
  896. insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
  897. select s.[UID], app.[UID]
  898. from #DereferencingSet3 s, #Appearance3 app;
  899. insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
  900. select s.[UID], app.[UID]
  901. from #DereferencingSet3 s, #Appearance4 app;
  902. insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
  903. select s.[UID], app.[UID]
  904. from #DereferencingSet3 s, #Appearance5 app;
  905.  
  906. -- create a posit thing
  907. drop table if exists #Posit6;
  908. create table #Posit6([UID] uniqueidentifier not null primary key);
  909. insert into [Thing]([UID]) output inserted.[UID] into #Posit6 values (DEFAULT);
  910.  
  911. -- create a posit
  912. insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
  913. select p.[UID], s.[UID], 'married', '2004-06-19 15:00'
  914. from #Posit6 p, #DereferencingSet3 s;
  915.  
  916. -- now the posit is complete (note that the set has three members now)
  917. select * from [v_Posit]
  918. where [DereferencingSetUID] = (select top 1 [UID] from #DereferencingSet3);
  919.  
  920. -- let Bella assert this, so first create an assertion thing
  921. drop table if exists #Assertion11;
  922. create table #Assertion11([UID] uniqueidentifier not null primary key);
  923. insert into [Thing]([UID]) output inserted.[UID] into #Assertion11 values (DEFAULT);
  924.  
  925. -- create an assertion
  926. insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
  927. select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:55'
  928. from #Assertion11 a, #B Bella, #Posit6 p;
  929.  
  930. -- now Bella has asserted the marriage
  931. select * from [v_Assertion]
  932. where [DereferencingSetUID] = (select top 1 [UID] from #DereferencingSet3)
  933. order 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
  937. drop table if exists #Posit7;
  938. create table #Posit7([UID] uniqueidentifier not null primary key);
  939. insert into [Thing]([UID]) output inserted.[UID] into #Posit7 values (DEFAULT);
  940.  
  941. -- create a posit
  942. insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
  943. select p.[UID], s.[UID], 'divorced', '2010-01-31 10:00'
  944. from #Posit7 p, #DereferencingSet3 s;
  945.  
  946. -- the state of this dereferencing set has transitioned from 'married' to 'divorced'
  947. select * from [v_Posit]
  948. where [DereferencingSetUID] = (select top 1 [UID] from #DereferencingSet3);
  949.  
  950. -- let Bella assert this as well, so first create an assertion thing
  951. drop table if exists #Assertion12;
  952. create table #Assertion12([UID] uniqueidentifier not null primary key);
  953. insert into [Thing]([UID]) output inserted.[UID] into #Assertion12 values (DEFAULT);
  954.  
  955. -- create an assertion
  956. insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
  957. select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:55'
  958. from #Assertion12 a, #B Bella, #Posit7 p;
  959.  
  960. -- now Bella has asserted the divorce
  961. select * from [v_Assertion]
  962. where [DereferencingSetUID] = (select top 1 [UID] from #DereferencingSet3)
  963. order 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
  969. drop table if exists #Appearance6;
  970. create table #Appearance6([UID] uniqueidentifier not null primary key);
  971. insert into [Thing]([UID]) output inserted.[UID] into #Appearance6 values (DEFAULT);
  972.  
  973. insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
  974. select app.[UID], Disagreer.[UID], r.[RoleUID]
  975. from [Role] r, #Appearance6 app, #D Disagreer
  976. where r.[Role] = 'husband';
  977.  
  978. -- create a dereferencing set thing
  979. drop table if exists #DereferencingSet4;
  980. create table #DereferencingSet4([UID] uniqueidentifier not null primary key);
  981. insert into [Thing]([UID]) output inserted.[UID] into #DereferencingSet4 values (DEFAULT);
  982.  
  983. -- create a dereferencing set
  984. insert into [DereferencingSet]([DereferencingSetUID])
  985. select [UID]
  986. from #DereferencingSet4;
  987.  
  988. -- add the created appearances to the dereferencing set (with only two members)
  989. insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
  990. select s.[UID], app.[UID]
  991. from #DereferencingSet4 s, #Appearance6 app;
  992. insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
  993. select s.[UID], app.[UID]
  994. from #DereferencingSet4 s, #Appearance4 app;
  995.  
  996. -- create a posit thing
  997. drop table if exists #Posit8;
  998. create table #Posit8([UID] uniqueidentifier not null primary key);
  999. insert into [Thing]([UID]) output inserted.[UID] into #Posit8 values (DEFAULT);
  1000.  
  1001. -- create a posit
  1002. insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
  1003. select p.[UID], s.[UID], 'married', '2011-11-11 11:11'
  1004. from #Posit8 p, #DereferencingSet4 s;
  1005.  
  1006. -- let Bella assert this as well, so first create an assertion thing
  1007. drop table if exists #Assertion13;
  1008. create table #Assertion13([UID] uniqueidentifier not null primary key);
  1009. insert into [Thing]([UID]) output inserted.[UID] into #Assertion13 values (DEFAULT);
  1010.  
  1011. -- create an assertion
  1012. insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
  1013. select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:55'
  1014. from #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
  1020. select a.* from [v_Assertion] a cross apply #B Bella
  1021. where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@UID = sql:column("Bella.UID") and @Role = "wife"]') = 1
  1022. order 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
  1026. drop table if exists #Assertion14;
  1027. create table #Assertion14([UID] uniqueidentifier not null primary key);
  1028. insert into [Thing]([UID]) output inserted.[UID] into #Assertion14 values (DEFAULT);
  1029.  
  1030. -- create an assertion (that is a retraction since Reliability = 0)
  1031. insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
  1032. select a.[UID], Bella.[UID], p.[UID], 0, '2018-12-13 16:00'
  1033. from #Assertion14 a, #B Bella, #Posit8 p;
  1034.  
  1035. -- in the view of all assertions the retraction is now visible
  1036. select a.* from [v_Assertion] a cross apply #B Bella
  1037. where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@UID = sql:column("Bella.UID") and @Role = "wife"]') = 1
  1038. order by [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  1039.  
  1040. -- which means that the latest information we have is that Bella is divorced
  1041. select a.* from [Information_in_Effect](getdate(), getdate()) a cross apply #B Bella
  1042. where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@UID = sql:column("Bella.UID") and @Role = "wife"]') = 1
  1043. order by [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  1044.  
  1045. -- so Bella needs to assert a different posit, with the correct appearance time
  1046. drop table if exists #Posit9;
  1047. create table #Posit9([UID] uniqueidentifier not null primary key);
  1048. insert into [Thing]([UID]) output inserted.[UID] into #Posit9 values (DEFAULT);
  1049.  
  1050. insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
  1051. select p.[UID], s.[UID], 'married', '2012-12-12 12:12'
  1052. from #Posit9 p, #DereferencingSet4 s;
  1053.  
  1054. drop table if exists #Assertion15;
  1055. create table #Assertion15([UID] uniqueidentifier not null primary key);
  1056. insert into [Thing]([UID]) output inserted.[UID] into #Assertion15 values (DEFAULT);
  1057.  
  1058. insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
  1059. select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 16:00'
  1060. from #Assertion15 a, #B Bella, #Posit9 p;
  1061.  
  1062. -- in the view of all assertions the correction is now visible
  1063. select a.* from [v_Assertion] a cross apply #B Bella
  1064. where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@UID = sql:column("Bella.UID") and @Role = "wife"]') = 1
  1065. order 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
  1069. select a.* from [Information_in_Effect](getdate(), getdate()) a cross apply #B Bella
  1070. where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@UID = sql:column("Bella.UID") and @Role = "wife"]') = 1
  1071. order by [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  1072.  
  1073.  
  1074. ------------------------------------------- MODELING ------------------------------------------
  1075. /*
  1076. ----------- Excerpt from: Modeling Conflicting, Unreliable, and Varying Information -----------
  1077. def. of a classifier and a class
  1078. Let "is class" be a role reserved for the purpose of modeling.
  1079. A posit pc = [{(C, is class)}, c, t], defines the name
  1080. of a class through the string c and associates the unique
  1081. identifier C with it. A classifier is a relationship that binds
  1082. a thing to a class, expressed through posits on the form
  1083. pM = [{(i, thing),(C, class)}, v, t].
  1084. -----------------------------------------------------------------------------------------------
  1085.  
  1086. It 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
  1089. drop table if exists #Role6;
  1090. create table #Role6([UID] uniqueidentifier not null primary key);
  1091. insert into [Thing]([UID]) output inserted.[UID] into #Role6 values (DEFAULT);
  1092. drop table if exists #Role7;
  1093. create table #Role7([UID] uniqueidentifier not null primary key);
  1094. insert into [Thing]([UID]) output inserted.[UID] into #Role7 values (DEFAULT);
  1095. drop table if exists #Role8;
  1096. create table #Role8([UID] uniqueidentifier not null primary key);
  1097. insert into [Thing]([UID]) output inserted.[UID] into #Role8 values (DEFAULT);
  1098.  
  1099. -- create the new roles
  1100. insert into [Role]([RoleUID], [Role])
  1101. select [UID], 'is class'
  1102. from #Role6;
  1103. insert into [Role]([RoleUID], [Role])
  1104. select [UID], 'thing'
  1105. from #Role7;
  1106. insert into [Role]([RoleUID], [Role])
  1107. select [UID], 'class'
  1108. from #Role8;
  1109.  
  1110. -- what roles have got now?
  1111. select * from [Role] order by RoleUID;
  1112.  
  1113. -- create two class things
  1114. drop table if exists #Class1;
  1115. create table #Class1([UID] uniqueidentifier not null primary key);
  1116. insert into [Thing]([UID]) output inserted.[UID] into #Class1 values (DEFAULT);
  1117. drop table if exists #Class2;
  1118. create table #Class2([UID] uniqueidentifier not null primary key);
  1119. insert 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
  1122. drop table if exists #Appearance7;
  1123. create table #Appearance7([UID] uniqueidentifier not null primary key);
  1124. insert into [Thing]([UID]) output inserted.[UID] into #Appearance7 values (DEFAULT);
  1125. drop table if exists #Appearance8;
  1126. create table #Appearance8([UID] uniqueidentifier not null primary key);
  1127. insert into [Thing]([UID]) output inserted.[UID] into #Appearance8 values (DEFAULT);
  1128.  
  1129. insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
  1130. select app.[UID], c.[UID], r.[RoleUID]
  1131. from [Role] r, #Appearance7 app, #Class1 c
  1132. where r.[Role] = 'is class';
  1133. insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
  1134. select app.[UID], c.[UID], r.[RoleUID]
  1135. from [Role] r, #Appearance8 app, #Class2 c
  1136. where r.[Role] = 'is class';
  1137.  
  1138. -- create a dereferencing set thing
  1139. drop table if exists #DereferencingSet5;
  1140. create table #DereferencingSet5([UID] uniqueidentifier not null primary key);
  1141. insert into [Thing]([UID]) output inserted.[UID] into #DereferencingSet5 values (DEFAULT);
  1142. drop table if exists #DereferencingSet6;
  1143. create table #DereferencingSet6([UID] uniqueidentifier not null primary key);
  1144. insert into [Thing]([UID]) output inserted.[UID] into #DereferencingSet6 values (DEFAULT);
  1145.  
  1146. -- create dereferencing sets
  1147. insert into [DereferencingSet]([DereferencingSetUID])
  1148. select [UID]
  1149. from #DereferencingSet5;
  1150. insert into [DereferencingSet]([DereferencingSetUID])
  1151. select [UID]
  1152. from #DereferencingSet6;
  1153.  
  1154. -- add the created appearances
  1155. insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
  1156. select s.[UID], app.[UID]
  1157. from #DereferencingSet5 s, #Appearance7 app;
  1158. insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
  1159. select s.[UID], app.[UID]
  1160. from #DereferencingSet6 s, #Appearance8 app;
  1161.  
  1162. -- two posit things
  1163. drop table if exists #Posit10;
  1164. create table #Posit10([UID] uniqueidentifier not null primary key);
  1165. insert into [Thing]([UID]) output inserted.[UID] into #Posit10 values (DEFAULT);
  1166. drop table if exists #Posit11;
  1167. create table #Posit11([UID] uniqueidentifier not null primary key);
  1168. insert into [Thing]([UID]) output inserted.[UID] into #Posit11 values (DEFAULT);
  1169.  
  1170. -- two posits
  1171. insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
  1172. select p.[UID], s.[UID], 'Person', '1901-01-01' -- will represent dawn of time
  1173. from #Posit10 p, #DereferencingSet5 s;
  1174. insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
  1175. select p.[UID], s.[UID], 'Cathedral', '1901-01-01' -- will represent dawn of time
  1176. from #Posit11 p, #DereferencingSet6 s;
  1177.  
  1178. -- now, the Modeler must assert these
  1179. drop table if exists #Assertion16;
  1180. create table #Assertion16([UID] uniqueidentifier not null primary key);
  1181. insert into [Thing]([UID]) output inserted.[UID] into #Assertion16 values (DEFAULT);
  1182. drop table if exists #Assertion17;
  1183. create table #Assertion17([UID] uniqueidentifier not null primary key);
  1184. insert into [Thing]([UID]) output inserted.[UID] into #Assertion17 values (DEFAULT);
  1185.  
  1186. insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
  1187. select a.[UID], Modeler.[UID], p.[UID], 1, '2018-12-01 00:00'
  1188. from #Assertion16 a, #M Modeler, #Posit10 p;
  1189. insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
  1190. select a.[UID], Modeler.[UID], p.[UID], 1, '2018-12-01 00:00'
  1191. from #Assertion17 a, #M Modeler, #Posit11 p;
  1192.  
  1193. -- list all classes
  1194. select a.* from [v_Assertion] a
  1195. where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@Role = "is class"]') = 1
  1196. order 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
  1204. drop table if exists #Appearance9;
  1205. create table #Appearance9([UID] uniqueidentifier not null primary key);
  1206. merge [Thing] t
  1207. using (
  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
  1218. on 1 = 0 -- make sure everything is "not matched" below
  1219. when not matched then insert ([UID]) values (DEFAULT)
  1220. output inserted.[UID] into #Appearance9;
  1221.  
  1222. -- create an equal number of appearances
  1223. insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
  1224. select app.[UID], t.[UID], r.[RoleUID]
  1225. from (select [UID], row_number() over (order by [UID]) as _row from #Appearance9) app
  1226. join (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
  1237. on t._row = app._row
  1238. cross apply (select [RoleUID] from [Role] where [Role] = 'thing') r;
  1239.  
  1240. -- then two more appearances (one for each associated class)
  1241. drop table if exists #Appearance10;
  1242. create table #Appearance10([UID] uniqueidentifier not null primary key);
  1243. insert into [Thing]([UID]) output inserted.[UID] into #Appearance10 values (DEFAULT);
  1244. drop table if exists #Appearance11;
  1245. create table #Appearance11([UID] uniqueidentifier not null primary key);
  1246. insert into [Thing]([UID]) output inserted.[UID] into #Appearance11 values (DEFAULT);
  1247.  
  1248. insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
  1249. select app.[UID], c.[UID], r.[RoleUID]
  1250. from [Role] r, #Appearance10 app, #Class1 c
  1251. where r.[Role] = 'class';
  1252. insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
  1253. select app.[UID], c.[UID], r.[RoleUID]
  1254. from [Role] r, #Appearance11 app, #Class2 c
  1255. where r.[Role] = 'class';
  1256.  
  1257. -- create dereferencing set things (equal in number to the things we want to classify)
  1258. drop table if exists #DereferencingSet7;
  1259. create table #DereferencingSet7([UID] uniqueidentifier not null primary key);
  1260. merge [Thing] t
  1261. using #Appearance9 src on 1 = 0 -- make sure everything is "not matched" below
  1262. when not matched then insert ([UID]) values (DEFAULT)
  1263. output inserted.[UID] into #DereferencingSet7;
  1264.  
  1265. -- create 22 dereferencing sets
  1266. insert into [DereferencingSet]([DereferencingSetUID])
  1267. select [UID]
  1268. from #DereferencingSet7;
  1269.  
  1270. -- add the appearances to the dereferencing sets
  1271. -- first the things
  1272. insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
  1273. select s.[UID], app.[UID]
  1274. from (select [UID], row_number() over (order by [UID]) as _row from #DereferencingSet7) s
  1275. join (select [UID], row_number() over (order by [UID]) as _row from #Appearance9) app
  1276. on s._row = app._row;
  1277. -- then the classes
  1278. insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
  1279. select
  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
  1301. from #DereferencingSet7 s, #Appearance10 Person, #Appearance11 Cathedral;
  1302.  
  1303. -- create posit things
  1304. drop table if exists #Posit12;
  1305. create table #Posit12([UID] uniqueidentifier not null primary key);
  1306. merge [Thing] t
  1307. using #DereferencingSet7 src on 1 = 0 -- make sure everything is "not matched" below
  1308. when not matched then insert ([UID]) values (DEFAULT)
  1309. output inserted.[UID] into #Posit12;
  1310.  
  1311. -- create posits
  1312. insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
  1313. select p.[UID], s.[UID], 'active', '1901-01-01'
  1314. from (select [UID], row_number() over (order by [UID]) as _row from #Posit12) p
  1315. join (select [UID], row_number() over (order by [UID]) as _row from #DereferencingSet7) s
  1316. on p._row = s._row;
  1317.  
  1318. -- finally let the Modeler positor assert these
  1319. drop table if exists #Assertion18;
  1320. create table #Assertion18([UID] uniqueidentifier not null primary key);
  1321. merge [Thing] t
  1322. using #Posit12 src on 1 = 0 -- make sure everything is "not matched" below
  1323. when not matched then insert ([UID]) values (DEFAULT)
  1324. output inserted.[UID] into #Assertion18;
  1325.  
  1326. insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
  1327. select a.[UID], Modeler.[UID], p.[UID], 1, '2018-12-01 00:00'
  1328. from (select [UID], row_number() over (order by [UID]) as _row from #Assertion18) a
  1329. join (select [UID], row_number() over (order by [UID]) as _row from #Posit12) p
  1330. on a._row = p._row
  1331. cross 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
  1335. select a.* from [v_Assertion] a
  1336. where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@Role = "is class" or @Role = "class"]') = 1
  1337. order by [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
  1338.  
  1339.  
  1340. /*
  1341. ----------- Excerpt from: Modeling Conflicting, Unreliable, and Varying Information -----------
  1342. def. of a posit type
  1343. A posit type, τ(p) = [{(C1,r1), . . . ,(Cn,rn)}, τ(v), τ(t)], for a
  1344. posit p = [{(i1,r1), . . . ,(in,rn)}, v, t], is a structure constructed
  1345. by replacing unique identifiers, ij with the unique identifiers of their
  1346. class, Cj, the value, v, with its data type, τ(v), and the time point, t,
  1347. with its data type, τ(t).
  1348. -----------------------------------------------------------------------------------------------
  1349.  
  1350. A posit type is a structure, but posits in the relational model can only have
  1351. values of type varchar(max), so it cannot hold such a structure. It could either
  1352. be expressed as serialized XML or JSON, or we could let the field contain a
  1353. reference to an identifier in a separate table that replicates the structure.
  1354. Since a posit type contains a set with variable number of members, it is
  1355. easier in this case to express it using XML.
  1356. */
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top