Add support for HISTORY data

[wardle]

dm+d includes HISTORY data which looks like this

<?xml version="1.0" encoding="utf-8" ?><HISTORY xsi:noNamespaceSchemaLocation="history_v1_0.xsd" xmlns="" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  ><!-- Generated by NHSBSA -->
<VTMS>
<VTM><IDCURRENT>6028009</IDCURRENT><IDPREVIOUS>6028009</IDPREVIOUS><STARTDT>2004-12-20</STARTDT></VTM>
<VTM><IDCURRENT>13132007</IDCURRENT><IDPREVIOUS>13132007</IDPREVIOUS><STARTDT>2005-07-01</STARTDT></VTM>
<VTM><IDCURRENT>14728000</IDCURRENT><IDPREVIOUS>14728000</IDPREVIOUS><STARTDT>2004-12-15</STARTDT></VTM>
<VTM><IDCURRENT>28426008</IDCURRENT><IDPREVIOUS>28426008</IDPREVIOUS><STARTDT>2004-12-23</STARTDT></VTM>
...
</VTMS>
<VMPS>
<VMP><IDCURRENT>68461003</IDCURRENT><IDPREVIOUS>3485311000001105</IDPREVIOUS><STARTDT>2002-10-02</STARTDT><ENDDT>2006-01-03</ENDDT></VMP>
<VMP><IDCURRENT>68461003</IDCURRENT><IDPREVIOUS>68461003</IDPREVIOUS><STARTDT>2006-01-04</STARTDT></VMP>
<VMP><IDCURRENT>134619001</IDCURRENT><IDPREVIOUS>134619001</IDPREVIOUS><STARTDT>2003-09-05</STARTDT></VMP>
<VMP><IDCURRENT>134754002</IDCURRENT><IDPREVIOUS>134754002</IDPREVIOUS><STARTDT>2003-01-15</STARTDT></VMP>
<VMP><IDCURRENT>318076002</IDCURRENT><IDPREVIOUS>318076002</IDPREVIOUS><STARTDT>2007-07-05</STARTDT></VMP>
...
</VMPS>
<INGS>
<ING><IDCURRENT>187006</IDCURRENT><IDPREVIOUS>187006</IDPREVIOUS><STARTDT>2005-08-11</STARTDT></ING>
<ING><IDCURRENT>261000</IDCURRENT><IDPREVIOUS>261000</IDPREVIOUS><STARTDT>1948-01-01</STARTDT></ING>
<ING><IDCURRENT>698006</IDCURRENT><IDPREVIOUS>698006</IDPREVIOUS><STARTDT>2003-07-16</STARTDT></ING>
<ING><IDCURRENT>699003</IDCURRENT><IDPREVIOUS>699003</IDPREVIOUS><STARTDT>2002-10-30</STARTDT></ING>
...
</INGS>
<SUPPS>
<SUPP><IDCURRENT>107101000001108</IDCURRENT><IDPREVIOUS>107101000001108</IDPREVIOUS><STARTDT>1948-01-01</STARTDT></SUPP>
<SUPP><IDCURRENT>108301000001106</IDCURRENT><IDPREVIOUS>108301000001106</IDPREVIOUS><STARTDT>1948-01-01</STARTDT></SUPP>
<SUPP><IDCURRENT>108501000001104</IDCURRENT><IDPREVIOUS>108501000001104</IDPREVIOUS><STARTDT>1948-01-01</STARTDT></SUPP>
<SUPP><IDCURRENT>109801000001109</IDCURRENT><IDPREVIOUS>109801000001109</IDPREVIOUS><STARTDT>1948-01-01</STARTDT></SUPP>
...
</SUPPS>
<FORMS>
<FORM><IDCURRENT>66076007</IDCURRENT><IDPREVIOUS>3089311000001104</IDPREVIOUS><STARTDT>1948-01-01</STARTDT><ENDDT>2008-01-10</ENDDT></FORM>
<FORM><IDCURRENT>66076007</IDCURRENT><IDPREVIOUS>66076007</IDPREVIOUS><STARTDT>2008-01-11</STARTDT></FORM>
<FORM><IDCURRENT>70409003</IDCURRENT><IDPREVIOUS>3093811000001107</IDPREVIOUS><STARTDT>1948-01-01</STARTDT><ENDDT>2008-01-10</ENDDT></FORM>
<FORM><IDCURRENT>70409003</IDCURRENT><IDPREVIOUS>70409003</IDPREVIOUS><STARTDT>2008-01-11</STARTDT></FORM>
...
</FORMS>
<ROUTES>
<ROUTE><IDCURRENT>10547007</IDCURRENT><IDPREVIOUS>10547007</IDPREVIOUS><STARTDT>1948-01-01</STARTDT></ROUTE>
<ROUTE><IDCURRENT>12130007</IDCURRENT><IDPREVIOUS>12130007</IDPREVIOUS><STARTDT>1948-01-01</STARTDT></ROUTE>
<ROUTE><IDCURRENT>16857009</IDCURRENT><IDPREVIOUS>16857009</IDPREVIOUS><STARTDT>1948-01-01</STARTDT></ROUTE>
<ROUTE><IDCURRENT>26643006</IDCURRENT><IDPREVIOUS>26643006</IDPREVIOUS><STARTDT>1948-01-01</STARTDT></ROUTE>
...
</ROUTES>
<UOMS>
<UOM><IDCURRENT>229034000</IDCURRENT><IDPREVIOUS>229034000</IDPREVIOUS><STARTDT>1948-01-01</STARTDT></UOM>
<UOM><IDCURRENT>258669008</IDCURRENT><IDPREVIOUS>258669008</IDPREVIOUS><STARTDT>1948-01-01</STARTDT></UOM>
<UOM><IDCURRENT>258672001</IDCURRENT><IDPREVIOUS>258672001</IDPREVIOUS><STARTDT>1948-01-01</STARTDT></UOM>
<UOM><IDCURRENT>258673006</IDCURRENT><IDPREVIOUS>258673006</IDPREVIOUS><STARTDT>1948-01-01</STARTDT></UOM>
...
</UOMS>
</HISTORY>

[wardle]

The history data was included from 2023 onwards. This is important as dm+d has switched from using SNOMED identifiers from the International release of SNOMED to dm+d managed codes, which is a good thing. It may be possible to use the SNOMED relationships, and the historical associations contained within that dataset, to properly handle legacy data using older dm+d codes. However, it might not cover all cases, and dmd should probably add support for looking up identifiers and handling 'legacy' data.

From the technical specifications. See https://www.nhsbsa.nhs.uk/pharmacies-gp-practices-and-appliance-contractors/dictionary-medicines-and-devices-dmd and https://www.nhsbsa.nhs.uk/sites/default/files/2023-01/Technical%20Specification%20of%20data%20files%20R2%20v3.2%20Jan%202023%20%28213KB%29.docx

On occasions dm+d identifiers change, to cope with the change in identifier dm+d has a previous identifier field. If an identifier changes more than once the oldest code is “lost” from the latest release data. The HISTORY data contains all the previous IDs for current dm+d concept identifiers and a start and end data for when they were the active identifier. If no end date exists, then the identifier is the current code and the ID current and ID previous fields will be the same. Only the dm+d Drug classes that have previous identifiers are contained in the HISTORY data, these are VTM; VMP; Ingredient; Form; Route; Supplier and Units of Measure.

[wardle]

It is unclear to me how best to model this usefully.

The usual approach for dmd is to be a very lightweight wrapper around the original data product. With that approach, one might have individual tables for each product supported - e.g. VMP__HISTORY and VTM__HISTORY. That would assume one knows the product type ahead of time, which is not usually correct when processing data from legacy clinical systems in which we might end up with only a code.

It would therefore seem more appropriate to have a single SQLite table with the contents of the HISTORY dataset. This would mean we would be able to add a simple step to expand a code list with historical identifiers and take a historical identifier and find its modern replacement.

[wardle]

The 'version' would have to be bumped unless the library could check for the existence of the new table before use and gracefully fallback to a NOP to handle data files created before its implementation.