C.3. Common Mistakes with Snapshot Generation

The most common type of error when generating a snapshot is to ignore considerations pertaining to dates and active status. For example, it is reasonable to want to filter a view so that only active components are included. However, if you apply that filter in a way that restricts the substrate from which the snapshot is generated you will get incorrect results in which all components appear to be active even after they have been inactivated. An example of this error has been presented below.

Table C.3-1: Full Release as at 2019-01-31

id	effectiveTime	active	value
A	20170131	1	Red
B	20170131	1	Amber
C	20170131	1	Yellow
A	20180131	0	Red
B	20180131	1	Orange
D	20180131	1	Green
B	20190131	0	Orange
E	20190131	1	Blue

The following query when applied to the full release data shown in Table C.3-1 generates the erroneous snapshot view shown in Table C.3-2. This query excludes inactive rows at the same time as identifying the most recent effectiveTime. The result is that components A and B are shown as active because the later rows that inactivated those components were excluded.

Select * from `component` `c` where
 `c`.`effectiveTime`=(Select max(effectiveTime) from `component` where `id`=`c`.`id` and active=1)

Table C.3-2: Erroneous Snapshot view as at 2019-01-31

id	effectiveTime	active	value
A	20170131	1	Red
C	20170131	1	Yellow
B	20180131	1	Orange
D	20180131	1	Green
E	20190131	1	Blue

The following query when applied to the full release data shown in Table C.3-1 generates the correct snapshot view shown in Table C.3-3. The most recent versions of all the components A-E are included. In the case of components A and B these are both inactive.

Select * from `component` `c` 
 where `c`.`effectiveTime`=(Select max(effectiveTime) from `component` where `id`=`c`.`id`)

Table C.3-3: Correct Snapshot view as at 2019-01-31

id	effectiveTime	active	value
C	20170131	1	Yellow
A	20180131	0	Red
D	20180131	1	Green
B	20190131	0	Orange
E	20190131	1	Blue

The following query when applied to the full release data shown in Table C.3-1 generates the correct snapshot view shown in Table C.3-4 with inactive rows filtered out after generating the snapshot view. Only components C-E are included as the most recent versions of components A and B are inactive.

Select * from `component` `c` 
 where `c`.`active`=1
 and `c`.`effectiveTime`=(Select max(effectiveTime) from `component` where `id`=`c`.`id`)

Table C.3-4: Correct Snapshot view as at 2019-01-31 with inactive rows excluded

id	effectiveTime	active	value
C	20170131	1	Yellow
D	20180131	1	Green
E	20190131	1	Blue

Search

Page tree