Maintaining data integrity
a data structure to ensure integrity through edits and updates
Friday, June 21, 2019
In a rapidly evolving data extraction environment, we need the ability to edit data already recorded while also offering a permanence of record. But these two roces are in opposing tension, so how to offer both? Fortunately, timestamps and the ability to INSERT … ON CONFLICT provide a way. Consider the following tables (all code using nodejs, but the underlying data principles would be the same for any programming language)
const createTable = function() {
// main table
db.prepare(`
CREATE TABLE IF NOT EXISTS treatments (
id INTEGER PRIMARY KEY,
tId TEXT UNIQUE,
tTitle TEXT,
author TEXT,
created INTEGER, -- created date unixtime ms
modified INTEGER, -- modified data unixtime ms
state INTEGER -- 0 deleted | 1 modified
)
`).run();
// related table via `tId` Foreign Key (FK)
db.prepare(`
CREATE TABLE IF NOT EXISTS materialsCitations (
id INTEGER PRIMARY KEY,
mId TEXT,
tId TEXT,
lat REAL,
lon REAL,
created INTEGER, -- created date unixtime ms
modified INTEGER, -- modified data unixtime ms
state INTEGER, -- 0 deleted | 1 modified
UNIQUE (mId, tId)
)
`).run();
};
createTable();
We get our database called test.sqlite.
$ sqlite3 test.sqlite
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE treatments (
id INTEGER PRIMARY KEY,
tId TEXT UNIQUE,
tTitle TEXT,
author TEXT,
created INTEGER, -- created date unixtime ms
modified INTEGER, -- modified data unixtime ms
state INTEGER -- 0 deleted | 1 modified
);
CREATE TABLE materialsCitations (
id INTEGER PRIMARY KEY,
mId TEXT,
tId TEXT,
lat REAL,
lon REAL,
created INTEGER, -- created date unixtime ms
modified INTEGER, -- modified data unixtime ms
state INTEGER, -- 0 deleted | 1 modified
UNIQUE (mId, tId)
);
This how the entity relationship diagram (ERD) looks like
┌──────────────────────────────┐ ┌──────────────────────────────┐
│██████████treatments██████████│ │██████materialsCitations██████│
├──────────────────────────────┤ ├──────────────────────────────┤
│ id INTEGER PRIMARY KEY │ │ id INTEGER PRIMARY KEY │
├──────────────────────────────┤ ├──────────────────────────────┤
│ tId TEXT NOT NULL UNIQUE ┼─────┐ │ mId TEXT NOT NULL UNIQUE │
├──────────────────────────────┤ │ ╱├──────────────────────────────┤
│ tTitle TEXT │ └───────│ tId TEXT │
├──────────────────────────────┤ ╲├──────────────────────────────┤
│ author TEXT │ │ lat REAL │
├──────────────────────────────┤ ├──────────────────────────────┤
│ created INTEGER │ │ lon REAL │
├──────────────────────────────┤ ├──────────────────────────────┤
│ modified INTEGER │ │ created INTEGER │
├──────────────────────────────┤ ├──────────────────────────────┤
│ state INTEGER │ │ modified INTEGER │
└──────────────────────────────┘ ├──────────────────────────────┤
│ state INTEGER │
├──────────────────────────────┤
│ UNIQUE (mId, tId) │
└──────────────────────────────┘
We start with the following data. data_t0 is an array of treatments (think, a table of treatments) whose every element is a record (a row in a table representing a single treatment). The fourth element in every record is actually a reference to a another array of materialsCitations for this treatment.
const data_t0 = [
{
tId: '7E9081',
tTitle: 'Neonersia fugax',
author: 'Song, Zhi-Shun',
created: 819167040000,
m: [
{ mId: '5EDEB3',
tId: '7E9081',
lat: '22.345',
lon: '21.546',
created: 819167040000 },
{ mId: '283B67',
tId: '7E9081',
lat: '81.432',
lon: '-17.532',
created: 819167400000 },
{ mId: 'B59511',
tId: '7E9081',
lat: '22.442',
lon: '21.687',
created: 819174600000 }
]
},
{
tId: '2F2B15',
tTitle: 'Polyrhachis',
author: 'Fr. Smith',
created: 819174660000,
m: [
{ mId: '07D267',
tId: '2F2B15',
lat: '123.542',
lon: '12.522',
created: 819174660000 }
]
},
{
tId: '0c74f1',
tTitle: 'Daptonema papillifera',
author: 'Sun, Yan, Huang',
created: 819174720000,
m: [
{ mId: 'b4b2fb',
tId: '0c74f1',
lat: '-56.967',
lon: '67.376',
created: 819174720000 },
]
}
];
Given the following program
const createInsertStatements = function() {
const insert_t = db.prepare(`
INSERT INTO treatments (tId, tTitle, author, created, modified, state)
VALUES(?, ?, ?, ?, ?, ?)
ON CONFLICT(tId) DO UPDATE SET
tTitle=excluded.tTitle,
author=excluded.author,
created=excluded.created,
modified=excluded.modified,
state=excluded.state`
);
const insert_m = db.prepare(`
INSERT INTO materialsCitations (mId, tId, lat, lon, created, modified, state)
VALUES(?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(mId, tId) DO UPDATE SET
lat=excluded.lat,
lon=excluded.lon,
created=excluded.created,
modified=excluded.modified,
state=excluded.state`
);
return [insert_t, insert_m];
};
const insertRows = function(data) {
const [insert_t, insert_m] = createInsertStatements();
data.forEach(function(t) {
if (t.state !== 0 && t.state !== 1) {
t.state = '';
}
insert_t.run(t.tId, t.tTitle, t.author, t.created, t.modified || '', t.state);
t.m.forEach(function(m) {
// if the parent treatment is deleted, then all children are deleted
if (t.state === 0) {
m.modified = t.modified;
m.state = 0;
}
if (m.state !== 0 && m.state !== 1) {
m.state = '';
}
insert_m.run(m.mId, m.tId, m.lat, m.lon, m.created, m.modified || '', m.state);
});
});
};
insertRows(data_t0);
We get the initial snapshot of our data in the the database
sqlite> .headers on
sqlite> .mode col
sqlite> SELECT * FROM treatments;
id tId tTitle author created modified state
---------- ------ -------------------------------- -------------- ------------ ------------ ----------
1 7E9081 Neonersia fugax Song, Zhi-Shun 819167040000
2 2F2B15 Polyrhachis Fr. Smith 819174660000
3 0c74f1 Daptonema papil Sun, Yan, Huan 819174720000
sqlite> SELECT * FROM materialsCitations;
id mId tId lat lon created modified state
---------- ------ ------ ---------- ---------- ------------ ---------- ----------
1 5EDEB3 7E9081 22.345 21.546 819167040000
2 283B67 7E9081 81.432 -17.532 819167400000
3 B59511 7E9081 22.442 21.687 819174600000
4 07D267 2F2B15 123.542 12.522 819174660000
5 b4b2fb 0c74f1 -56.967 67.376 819174720000
A few days later, we have some changes. One treatment has a few edits, and its related materialsCitations too have edits. In fact, a new one has been added as well, and one of them has been deleted. Also, another treatment has been completely deleted.
const data_t1 = [
{
// treatment edited
tId: '7E9081',
tTitle: 'Neonersia fugax (Melichar, 1912)',
author: 'Song, Zhi-Shun',
created: 819174660000,
modified: 820834320000,
state: 1,
m: [
// unchanged
{ mId: '5EDEB3',
tId: '7E9081',
lat: '22.345',
lon: '21.546',
created: 819167040000 },
// edited
{ mId: '283B67',
tId: '7E9081',
lat: '83.433',
lon: '-16.874',
created: 819167400000,
modified: 820834320000,
state: 1 },
// deleted
{ mId: 'B59511',
tId: '7E9081',
lat: '22.442',
lon: '21.687',
created: 819174600000,
modified: 820834320000,
state: 0 },
// added
{ mId: '677E2553DD4D43B09DA77414DB1EB8EA',
tId: '7E9081',
lat: '-45.348',
lon: '-121.998',
created: 821698320000 }
]
},
// entire treatment deleted
{
tId: '0c74f1',
tTitle: 'Daptonema papillifera',
author: 'Sun, Yan, Huang',
created: 819174720000,
modified: 821698320000,
state: 0,
m: [
{ mId: 'b4b2fb',
tId: '0c74f1',
lat: '-56.967',
lon: '67.376',
created: 819174720000 },
]
}
];
We run the program again with new data insertRows(data_t1) and we get the following
sqlite> SELECT * FROM treatments;
id tId tTitle author created modified state
---------- ------ -------------------------------- -------------- ------------ ------------ ----------
1 7E9081 Neonersia fugax (Melichar, 1912) Song, Zhi-Shun 819174660000 820834320000 1 <- edited
2 2F2B15 Polyrhachis Fr. Smith 819174660000
3 0c74f1 Daptonema papillifera Sun, Yan, Huan 819174720000 821698320000 0 <- deleted
sqlite> SELECT * FROM materialsCitations;
id mId tId lat lon created modified state
---------- ------ ------ ---------- ---------- ------------ ---------- ----------
1 5EDEB3 7E9081 22.345 21.546 819167040000
2 283B67 7E9081 83.433 -16.874 819167400000 8208343200 1 <- edited
3 B59511 7E9081 22.442 21.687 819174600000 8208343200 0 <- deleted
4 07D267 2F2B15 123.542 12.522 819174660000
5 b4b2fb 0c74f1 -56.967 67.376 819174720000 8216983200 0 <- deleted
6 677E25 7E9081 -45.348 -121.998 821698320000 <- added