AltSql DB 0.2: SQL and Direct Calls Wrote Byte-Identical Files Over 100,000 Random Steps

AltSql DB 0.2 makes the two ways into the database follow the same rules, and it makes sync cope with batches that arrive out of order. It also comes with a shell of its own.

It’s part of the v0.2.0-alpha release, which is open source under the Apache License 2.0. Every number below was measured in simulation on a PC, with the files held in memory and the link between devices and gateway simulated. Nothing has run on a gateway yet.

The test that sums up 0.2 runs one random workload twice, once as SQL text and once as direct row calls. In four runs, 100,000 steps in all, the two files it wrote came out the same every time, byte for byte.

The altsql-db shell: batch 2 is sent before batch 1 and gets a gap with position 0, batch 1 is then applied and the position moves to 7, and SELECT shows device 7’s three readings A gap in the new altsql-db shell: batch 2 arrives before batch 1, and nothing changes.

One Way to Write a Row

Every writer now goes through the same internal check and write: the direct row calls, SQL’s INSERT, UPDATE and DELETE, DROP TABLE, and sync. Whichever way a row comes in, the same rules apply to it.

The test suites from 0.1 print the same logs as before, apart from the version line. That goes for the database, row, SQL, crash and fault tests, and for the sync test’s 0.1 sections.

The Interface Test

The new test checks that the two interfaces really are one. It runs the same random workload on two files, as SQL text on one and as direct calls on the other. After every step the two must give the same answer. When a call is refused, say an INSERT of a key that’s already there, both must refuse it with the same error code. At the end the two files must match byte for byte, with a cache big enough to hold the whole run.

It ran 4 seeds of 25,000 steps, 100,000 in all: 52,259 writes, 15,092 reads and 32,331 refusals, with 1,944 tables created or dropped, 7,375 transactions and 2,996 whole-table comparisons. In every seed the two files were identical. The four pairs came out at 757,760, 1,499,136, 1,110,016 and 1,118,208 bytes, after 9,524, 9,638, 9,763 and 9,511 commits.

Sync in Order

A device sends its records to the gateway in batches. In 0.1 the gateway took it for granted that they arrived in order. A batch that arrived ahead of a lost one moved the gateway past the lost records, and they stayed lost.

Now each batch comes with the position it was read from. The gateway applies a batch only when nothing is missing before that position. If something is, it changes nothing and answers with a gap and its own position, and the device sends again from there.

To test it, four devices sent to one gateway over a link that lost batches, repeated some and shuffled the order. Each device sent up to four batches ahead without waiting for an answer. Over 8 runs of 150 rounds, 8,888 batches were sent and 8,908 delivered. The link lost 507, delivered 527 twice and took 9,312 out of order. The gateway refused 3,529 as gaps. Every record still arrived exactly once.

As a control, the same 8 runs with every batch claiming position 0, as 0.1 assumed, lost 16,109 records. So the link was hard enough to find the problem.

The altsql-db Shell

altsql-db opens a file, creating it when it’s missing, and runs SQL and dot commands. .sync applies a batch file that a device wrote, and .state shows how far the gateway has got with a device. In .sync 7 7 b2.bin, the first 7 is the device and the second is the position the batch was read from.

Here’s a session from the shell’s own test. A device wrote two batches, and the second one arrives first:

$ altsql-db gw.db \
  '.sync 7 7 b2.bin' '.state 7'
gap: send again from 0
0
$ altsql-db gw.db \
  '.sync 7 0 b1.bin' '.state 7'
applied, position 7
7
$ altsql-db gw.db \
  'SELECT * FROM temps;'
device|seq|time|machine|temp
7|3|1700000001|1|21.5
7|4|1700000002|2|22.25
7|5|1700000003|1|21.75

The gateway is at position 0 and batch 2 starts at 7, so it answers with a gap and changes nothing. Batch 1 goes in and the position moves to 7. Send batch 2 again and it applies, and the position moves to 12.

What Waits for 0.3

Two things wait for 0.3. The first is statement savepoints. Without them, an UPDATE or DELETE that fails partway inside your transaction fails the whole transaction, where a refused direct call leaves it usable. The second is secondary indexes. For now tables have primary keys only.

The code is on GitHub. There’s also the live demo, and the AltSql DB page says more about the database.