K0SSesNf

1PRAGMA journal_mode=WAL;
2PRAGMA tmp_store = 2; -- push temporary tables to memory
3
4DROP TABLE IF EXISTS ondisk;
5DROP TABLE IF EXISTS ringbuffer;
6DROP TABLE IF EXISTS indexer;
7DROP VIEW  IF EXISTS ordered_ring;
8
9BEGIN IMMEDIATE;
10
11CREATE TABLE IF NOT EXISTS ondisk (
12    id INTEGER NOT NULL PRIMARY KEY,
13   val -- any type
14);
15
16
17CREATE TEMPORARY TABLE IF NOT EXISTS ringbuffer(
18   val -- any type 
19);
20
21
22CREATE TEMPORARY TABLE IF NOT EXISTS indexer(
23    ix INTEGER NOT NULL
24);
25
26
27CREATE TEMPORARY TRIGGER IF NOT EXISTS incr_indexer 
28BEFORE INSERT 
29    ON ringbuffer
30 BEGIN   
31       UPDATE indexer 
32          SET ix = ((SELECT ix from indexer)+1)%100;  -- 100 inserts ring size    
33   END
34;
35
36INSERT INTO indexer (ix) VALUES (0);  -- initialise ring index before trigger
37
38
39CREATE TEMPORARY VIEW IF NOT EXISTS ordered_ring 
40    AS
41SELECT val 
42  FROM ringbuffer, indexer
43 WHERE ringbuffer.rowID>ix   
44 UNION ALL 
45SELECT val 
46  FROM ringbuffer, indexer
47 WHERE ringbuffer.rowID<=ix
48;
49
50
51 CREATE TEMPORARY TRIGGER IF NOT EXISTS new_val 
52INSTEAD OF INSERT 
53     ON ordered_ring
54  BEGIN    
55        INSERT OR REPLACE INTO ringbuffer (rowID, val)
56        VALUES ((SELECT ix FROM indexer), NEW.val);
57    END
58;
59
60
61CREATE TEMPORARY TRIGGER IF NOT EXISTS spill_to_disk 
62 AFTER INSERT 
63    ON ringbuffer 
64  WHEN (SELECT ix from indexer) = 0
65 BEGIN
66       INSERT INTO ondisk(val) 
67       SELECT val FROM ordered_ring;
68  END
69; 
70
71COMMIT;
72
73/*
74INSERT INTO ordered_ring(val)
75VALUES(CURRENT_TIMESTAMP);
76*/