anchormodeling

Bitemporal Test Suite

Sep 17th, 2021 (edited)
564
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. delete [AC_NAM_Actor_Name_Annex];
  2. delete [AC_NAM_Actor_Name_Posit];
  3. DBCC CHECKIDENT ('AC_NAM_Actor_Name_Posit', RESEED, 555);
  4.  
  5. delete [AC_Actor];
  6. DBCC CHECKIDENT ('AC_Actor', RESEED, 1);
  7.  
  8. -- first row
  9. insert into lAC_Actor (
  10.     AC_NAM_Actor_Name,
  11.     AC_NAM_ChangedAt,
  12.     AC_NAM_PositedAt,
  13.     AC_NAM_Reliability,
  14.     Metadata_AC
  15. )
  16. values (
  17.     'Roenbaeck',
  18.     '2020-01-01',
  19.     '2021-02-03',
  20.     1,
  21.     42
  22. );
  23.  
  24. select * from AC_Actor;
  25. select * from AC_NAM_Actor_Name_Posit;
  26. select * from AC_NAM_Actor_Name;
  27. select
  28.     AC_ID,
  29.     AC_NAM_Actor_Name,
  30.     AC_NAM_ChangedAt,
  31.     AC_NAM_PositedAt,
  32.     AC_NAM_Reliability,
  33.     Metadata_AC
  34. from
  35.     lAC_Actor;
  36.  
  37. -- duplicate
  38. insert into lAC_Actor (
  39.     AC_ID,
  40.     AC_NAM_Actor_Name,
  41.     AC_NAM_ChangedAt,
  42.     AC_NAM_PositedAt,
  43.     AC_NAM_Reliability,
  44.     Metadata_AC
  45. )
  46. values (
  47.     2,
  48.     'Roenbaeck',
  49.     '2020-01-01',
  50.     '2021-02-03',
  51.     1,
  52.     42 
  53. );
  54.  
  55. select * from AC_Actor;
  56. select * from AC_NAM_Actor_Name_Posit;
  57. select * from AC_NAM_Actor_Name;
  58. select
  59.     AC_ID,
  60.     AC_NAM_Actor_Name,
  61.     AC_NAM_ChangedAt,
  62.     AC_NAM_PositedAt,
  63.     AC_NAM_Reliability,
  64.     Metadata_AC
  65. from
  66.     lAC_Actor;
  67.  
  68. -- reassertion
  69. insert into lAC_Actor (
  70.     AC_ID,
  71.     AC_NAM_Actor_Name,
  72.     AC_NAM_ChangedAt,
  73.     AC_NAM_PositedAt,
  74.     AC_NAM_Reliability,
  75.     Metadata_AC
  76. )
  77. values (
  78.     2,
  79.     'Roenbaeck',
  80.     '2020-01-01',
  81.     '2022-03-04',
  82.     1,
  83.     42 
  84. );
  85.  
  86. select * from AC_Actor;
  87. select * from AC_NAM_Actor_Name_Posit;
  88. select * from AC_NAM_Actor_Name;
  89. select
  90.     AC_ID,
  91.     AC_NAM_Actor_Name,
  92.     AC_NAM_ChangedAt,
  93.     AC_NAM_PositedAt,
  94.     AC_NAM_Reliability,
  95.     Metadata_AC
  96. from
  97.     lAC_Actor;
  98.  
  99. -- restatement (date clash)
  100. insert into lAC_Actor (
  101.     AC_ID,
  102.     AC_NAM_Actor_Name,
  103.     AC_NAM_ChangedAt,
  104.     AC_NAM_PositedAt,
  105.     AC_NAM_Reliability,
  106.     Metadata_AC
  107. )
  108. values (
  109.     2,
  110.     'Roenbaeck',
  111.     '2021-02-03',
  112.     '2021-02-03',
  113.     1,
  114.     42 
  115. );
  116.  
  117. select * from AC_Actor;
  118. select * from AC_NAM_Actor_Name_Posit;
  119. select * from AC_NAM_Actor_Name;
  120. select
  121.     AC_ID,
  122.     AC_NAM_Actor_Name,
  123.     AC_NAM_ChangedAt,
  124.     AC_NAM_PositedAt,
  125.     AC_NAM_Reliability,
  126.     Metadata_AC
  127. from
  128.     lAC_Actor;
  129.  
  130. -- restatement again (no date clash)
  131. insert into lAC_Actor (
  132.     AC_ID,
  133.     AC_NAM_Actor_Name,
  134.     AC_NAM_ChangedAt,
  135.     AC_NAM_PositedAt,
  136.     AC_NAM_Reliability,
  137.     Metadata_AC
  138. )
  139. values (
  140.     2,
  141.     'Roenbaeck',
  142.     '2021-02-03',
  143.     '2022-03-04',
  144.     1,
  145.     42 
  146. );
  147.  
  148. select * from AC_Actor;
  149. select * from AC_NAM_Actor_Name_Posit;
  150. select * from AC_NAM_Actor_Name;
  151. select
  152.     AC_ID,
  153.     AC_NAM_Actor_Name,
  154.     AC_NAM_ChangedAt,
  155.     AC_NAM_PositedAt,
  156.     AC_NAM_Reliability,
  157.     Metadata_AC
  158. from
  159.     lAC_Actor;
  160.  
  161.  
  162. -- deactivate non-existing
  163. insert into lAC_Actor (
  164.     AC_ID,
  165.     AC_NAM_Actor_Name,
  166.     AC_NAM_ChangedAt,
  167.     AC_NAM_PositedAt,
  168.     AC_NAM_Reliability,
  169.     Metadata_AC
  170. )
  171. values (
  172.     2,
  173.     'Roenbaeck',
  174.     '2021-02-03',
  175.     '2022-03-04',
  176.     0,
  177.     42 
  178. );
  179.  
  180. select * from AC_Actor;
  181. select * from AC_NAM_Actor_Name_Posit;
  182. select * from AC_NAM_Actor_Name;
  183. select
  184.     AC_ID,
  185.     AC_NAM_Actor_Name,
  186.     AC_NAM_ChangedAt,
  187.     AC_NAM_PositedAt,
  188.     AC_NAM_Reliability,
  189.     Metadata_AC
  190. from
  191.     lAC_Actor;
  192.  
  193. -- deactivate existing
  194. insert into lAC_Actor (
  195.     AC_ID,
  196.     AC_NAM_Actor_Name,
  197.     AC_NAM_ChangedAt,
  198.     AC_NAM_PositedAt,
  199.     AC_NAM_Reliability,
  200.     Metadata_AC
  201. )
  202. values (
  203.     2,
  204.     'Roenbaeck',
  205.     '2020-01-01',
  206.     '2022-03-04',
  207.     0,
  208.     42 
  209. );
  210.  
  211. select * from AC_Actor;
  212. select * from AC_NAM_Actor_Name_Posit;
  213. select * from AC_NAM_Actor_Name;
  214. select
  215.     AC_ID,
  216.     AC_NAM_Actor_Name,
  217.     AC_NAM_ChangedAt,
  218.     AC_NAM_PositedAt,
  219.     AC_NAM_Reliability,
  220.     Metadata_AC
  221. from
  222.     lAC_Actor;
  223.  
  224. -- prestatement
  225. insert into lAC_Actor (
  226.     AC_ID,
  227.     AC_NAM_Actor_Name,
  228.     AC_NAM_ChangedAt,
  229.     AC_NAM_PositedAt,
  230.     AC_NAM_Reliability,
  231.     Metadata_AC
  232. )
  233. values (
  234.     2,
  235.     'Roenbaeck',
  236.     '1972-08-20',
  237.     '2023-04-05',
  238.     1,
  239.     42 
  240. );
  241.  
  242. select * from AC_Actor;
  243. select * from AC_NAM_Actor_Name_Posit;
  244. select * from AC_NAM_Actor_Name;
  245. select
  246.     AC_ID,
  247.     AC_NAM_Actor_Name,
  248.     AC_NAM_ChangedAt,
  249.     AC_NAM_PositedAt,
  250.     AC_NAM_Reliability,
  251.     Metadata_AC
  252. from
  253.     lAC_Actor;
  254.  
  255. -- prestatement again (with later positing time)
  256. insert into lAC_Actor (
  257.     AC_ID,
  258.     AC_NAM_Actor_Name,
  259.     AC_NAM_ChangedAt,
  260.     AC_NAM_PositedAt,
  261.     AC_NAM_Reliability,
  262.     Metadata_AC
  263. )
  264. values (
  265.     2,
  266.     'Roenbaeck',
  267.     '1972-02-13',
  268.     '2024-05-06',
  269.     1,
  270.     42 
  271. );
  272.  
  273. select * from AC_Actor;
  274. select * from AC_NAM_Actor_Name_Posit;
  275. select * from AC_NAM_Actor_Name;
  276. select
  277.     AC_ID,
  278.     AC_NAM_Actor_Name,
  279.     AC_NAM_ChangedAt,
  280.     AC_NAM_PositedAt,
  281.     AC_NAM_Reliability,
  282.     Metadata_AC
  283. from
  284.     lAC_Actor;
  285.  
  286. -- prestatement again (with earlier positing time)
  287. insert into lAC_Actor (
  288.     AC_ID,
  289.     AC_NAM_Actor_Name,
  290.     AC_NAM_ChangedAt,
  291.     AC_NAM_PositedAt,
  292.     AC_NAM_Reliability,
  293.     Metadata_AC
  294. )
  295. values (
  296.     2,
  297.     'Roenbaeck',
  298.     '1971-01-01',
  299.     '1971-01-01',
  300.     1,
  301.     42 
  302. );
  303.  
  304. select * from AC_Actor;
  305. select * from AC_NAM_Actor_Name_Posit;
  306. select * from AC_NAM_Actor_Name;
  307. select
  308.     AC_ID,
  309.     AC_NAM_Actor_Name,
  310.     AC_NAM_ChangedAt,
  311.     AC_NAM_PositedAt,
  312.     AC_NAM_Reliability,
  313.     Metadata_AC
  314. from
  315.     lAC_Actor;
  316.  
  317. -- reactivate existing with clashing postiting time
  318. -- this would become a restatement...
  319. insert into lAC_Actor (
  320.     AC_ID,
  321.     AC_NAM_Actor_Name,
  322.     AC_NAM_ChangedAt,
  323.     AC_NAM_PositedAt,
  324.     AC_NAM_Reliability,
  325.     Metadata_AC
  326. )
  327. values (
  328.     2,
  329.     'Roenbaeck',
  330.     '2020-01-01',
  331.     '2023-04-05',
  332.     1,
  333.     42 
  334. );
  335.  
  336. select * from AC_Actor;
  337. select * from AC_NAM_Actor_Name_Posit;
  338. select * from AC_NAM_Actor_Name;
  339. select
  340.     AC_ID,
  341.     AC_NAM_Actor_Name,
  342.     AC_NAM_ChangedAt,
  343.     AC_NAM_PositedAt,
  344.     AC_NAM_Reliability,
  345.     Metadata_AC
  346. from
  347.     lAC_Actor;
  348.  
  349. -- reactivate existing later time
  350. -- also becomes a restatement...
  351. insert into lAC_Actor (
  352.     AC_ID,
  353.     AC_NAM_Actor_Name,
  354.     AC_NAM_ChangedAt,
  355.     AC_NAM_PositedAt,
  356.     AC_NAM_Reliability,
  357.     Metadata_AC
  358. )
  359. values (
  360.     2,
  361.     'Roenbaeck',
  362.     '2020-01-01',
  363.     '2024-05-06',
  364.     1,
  365.     42 
  366. );
  367.  
  368. select * from AC_Actor;
  369. select * from AC_NAM_Actor_Name_Posit;
  370. select * from AC_NAM_Actor_Name;
  371. select
  372.     AC_ID,
  373.     AC_NAM_Actor_Name,
  374.     AC_NAM_ChangedAt,
  375.     AC_NAM_PositedAt,
  376.     AC_NAM_Reliability,
  377.     Metadata_AC
  378. from
  379.     lAC_Actor;
  380.  
  381. -- reassert existing with later positing
  382. -- just a reassertion then
  383. insert into lAC_Actor (
  384.     AC_ID,
  385.     AC_NAM_Actor_Name,
  386.     AC_NAM_ChangedAt,
  387.     AC_NAM_PositedAt,
  388.     AC_NAM_Reliability,
  389.     Metadata_AC
  390. )
  391. values (
  392.     2,
  393.     'Roenbaeck',
  394.     '1972-02-13',
  395.     '2024-05-31',
  396.     1,
  397.     42 
  398. );
  399.  
  400. select * from AC_Actor;
  401. select * from AC_NAM_Actor_Name_Posit;
  402. select * from AC_NAM_Actor_Name;
  403. select
  404.     AC_ID,
  405.     AC_NAM_Actor_Name,
  406.     AC_NAM_ChangedAt,
  407.     AC_NAM_PositedAt,
  408.     AC_NAM_Reliability,
  409.     Metadata_AC
  410. from
  411.     lAC_Actor;
  412.  
  413. -- reassert existing with earlier positing
  414. -- just a reassertion then
  415. insert into lAC_Actor (
  416.     AC_ID,
  417.     AC_NAM_Actor_Name,
  418.     AC_NAM_ChangedAt,
  419.     AC_NAM_PositedAt,
  420.     AC_NAM_Reliability,
  421.     Metadata_AC
  422. )
  423. values (
  424.     2,
  425.     'Roenbaeck',
  426.     '1972-02-13',
  427.     '1972-02-13',
  428.     1,
  429.     42 
  430. );
  431.  
  432. select * from AC_Actor;
  433. select * from AC_NAM_Actor_Name_Posit;
  434. select * from AC_NAM_Actor_Name;
  435. select
  436.     AC_ID,
  437.     AC_NAM_Actor_Name,
  438.     AC_NAM_ChangedAt,
  439.     AC_NAM_PositedAt,
  440.     AC_NAM_Reliability,
  441.     Metadata_AC
  442. from
  443.     lAC_Actor;
  444.  
  445.  
  446. -- deactivate the oldest
  447. insert into lAC_Actor (
  448.     AC_ID,
  449.     AC_NAM_Actor_Name,
  450.     AC_NAM_ChangedAt,
  451.     AC_NAM_PositedAt,
  452.     AC_NAM_Reliability,
  453.     Metadata_AC
  454. )
  455. values (
  456.     2,
  457.     'Roenbaeck',
  458.     '1971-01-01',
  459.     '2021-01-01',
  460.     0,
  461.     42 
  462. );
  463.  
  464. select * from AC_Actor;
  465. select * from AC_NAM_Actor_Name_Posit;
  466. select * from AC_NAM_Actor_Name;
  467. select
  468.     AC_ID,
  469.     AC_NAM_Actor_Name,
  470.     AC_NAM_ChangedAt,
  471.     AC_NAM_PositedAt,
  472.     AC_NAM_Reliability,
  473.     Metadata_AC
  474. from
  475.     lAC_Actor;
  476.  
  477. -- deactivate a deactivated
  478.  
  479. insert into lAC_Actor (
  480.     AC_ID,
  481.     AC_NAM_Actor_Name,
  482.     AC_NAM_ChangedAt,
  483.     AC_NAM_PositedAt,
  484.     AC_NAM_Reliability,
  485.     Metadata_AC
  486. )
  487. values (
  488.     2,
  489.     'Roenbaeck',
  490.     '2020-01-01',
  491.     '2025-01-01',
  492.     0,
  493.     42 
  494. );
  495.  
  496. select * from AC_Actor;
  497. select * from AC_NAM_Actor_Name_Posit;
  498. select * from AC_NAM_Actor_Name;
  499. select
  500.     AC_ID,
  501.     AC_NAM_Actor_Name,
  502.     AC_NAM_ChangedAt,
  503.     AC_NAM_PositedAt,
  504.     AC_NAM_Reliability,
  505.     Metadata_AC
  506. from
  507.     lAC_Actor;
  508.  
  509. -- activate a deactivated
  510. insert into lAC_Actor (
  511.     AC_ID,
  512.     AC_NAM_Actor_Name,
  513.     AC_NAM_ChangedAt,
  514.     AC_NAM_PositedAt,
  515.     AC_NAM_Reliability,
  516.     Metadata_AC
  517. )
  518. values (
  519.     2,
  520.     'Roenbaeck',
  521.     '2020-01-01',
  522.     '2026-01-01',
  523.     1,
  524.     42 
  525. );
  526.  
  527. select * from AC_Actor;
  528. select * from AC_NAM_Actor_Name_Posit;
  529. select * from AC_NAM_Actor_Name;
  530. select
  531.     AC_ID,
  532.     AC_NAM_Actor_Name,
  533.     AC_NAM_ChangedAt,
  534.     AC_NAM_PositedAt,
  535.     AC_NAM_Reliability,
  536.     Metadata_AC
  537. from
  538.     lAC_Actor;
  539.  
RAW Paste Data