4ebybxjr

1PRAGMA journal_mode=WAL;
2PRAGMA tmp_store = 2; -- push temporary tables into memory
3
4DROP TABLE IF EXISTS ondisk;
5DROP TABLE IF EXISTS timedbuffer;
6DROP TABLE IF EXISTS timekeeper;
7
8BEGIN IMMEDIATE;
9
10CREATE TABLE IF NOT EXISTS ondisk (
11    id INTEGER NOT NULL PRIMARY KEY,
12   val -- any type
13);
14
15
16CREATE TEMPORARY TABLE IF NOT EXISTS timedbuffer(
17   val -- any type
18);
19
20
21CREATE TEMPORARY TABLE timekeeper(
22   idx INTEGER UNIQUE DEFAULT 1,
23   ts0 REAL DEFAULT CURRENT_TIMESTAMP,
24 spill INTEGER DEFAULT 0
25);
26
27
28CREATE TEMPORARY TRIGGER IF NOT EXISTS upsert_timekeeper
29 AFTER INSERT
30    ON timedbuffer
31 BEGIN
32  INSERT INTO timekeeper(spill)
33  VALUES (0)
34      ON CONFLICT (idx)
35      DO
36  UPDATE
37     SET spill = 
38         CASE
39           WHEN julianday('now')-(
40                  SELECT julianday(ts0) AS j 
41                  FROM timekeeper
42                )> 0.0006               -- one minute buffer size
43           THEN 1
44           ELSE 0
45         END
46   WHERE idx = 1
47  ;
48   END
49;
50
51
52CREATE TEMPORARY TRIGGER IF NOT EXISTS spill_to_disk 
53 AFTER UPDATE 
54    OF spill
55    ON timekeeper 
56  WHEN (SELECT spill FROM timekeeper WHERE idx=1) = 1
57 BEGIN
58       INSERT INTO ondisk(val) 
59       SELECT val FROM timedbuffer
60       ;       
61       DELETE FROM timedbuffer
62       ;
63       DELETE FROM timekeeper
64       ;
65   END
66; 
67
68COMMIT;
69
70/*
71INSERT INTO timedbuffer(val)
72VALUES(CURRENT_TIMESTAMP);
73*/