C.3. Common Mistakes with Snapshot Generation

The most common type of error when generating a snapshot is to ignore rule 3 and/or 4. 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 is shown on 

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

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

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: Erroneous Snapshot view as at 2019-01-31

The following query when applied to the full release data shown in  Table C.3-1 generates the correct snapshot view shown in  <span style="color:red;">Error: Referenced caption id not found!</span><div class="macroHelp"><b>Captions on current page:</b><br/> - Table: with ID: <b>full-eg</b> <br/> - Table: with ID: <b>snap-err</b> <br/> - Table: with ID: <b>snap-ok</b> <br/> - Table: with ID: <b>snap-ok</b> <br/>  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-3: Correct Snapshot view as at 2019-01-31 with inactive rows excluded