libsql-wal 0.1.0-alpha.1

wal implementation for libsql
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125


  CREATE TABLE t1(a,b,c);
  INSERT INTO t1(a,b,c)
    VALUES(1,2,3),(7,8,9),(4,5,6),(10,11,12),(4,8,12),(1,11,111);
  CREATE INDEX t1a ON t1(a);
  CREATE INDEX t1b ON t1(b);
  ANALYZE;
  DELETE FROM sqlite_stat1;
  INSERT INTO sqlite_stat1(tbl,idx,stat)
    VALUES('t1','t1a','12345 2'),('t1','t1b','12345 4');
  ANALYZE sqlite_master;
  SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;


  EXPLAIN QUERY PLAN
  SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;


  SELECT c FROM t1 ORDER BY a;


  EXPLAIN QUERY PLAN
  SELECT c FROM t1 ORDER BY a;


  EXPLAIN QUERY PLAN
  SELECT c FROM t1 ORDER BY a;


  UPDATE sqlite_stat1 SET stat='12345 2 unordered' WHERE idx='t1a';
  ANALYZE sqlite_master;
  SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;


  EXPLAIN QUERY PLAN
  SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;


  SELECT c FROM t1 ORDER BY a;


  EXPLAIN QUERY PLAN
  SELECT c FROM t1 ORDER BY a;


  EXPLAIN QUERY PLAN
  SELECT c FROM t1 ORDER BY a;


  UPDATE sqlite_stat1 SET stat='12345 2 whatever=5 unordered xyzzy=11'
   WHERE idx='t1a';
  ANALYZE sqlite_master;
  SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;


  EXPLAIN QUERY PLAN
  SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;


  SELECT c FROM t1 ORDER BY a;


  EXPLAIN QUERY PLAN
  SELECT c FROM t1 ORDER BY a;


  EXPLAIN QUERY PLAN
  SELECT c FROM t1 ORDER BY a;


  DROP INDEX t1a;
  CREATE INDEX t1ab ON t1(a,b);
  CREATE INDEX t1ca ON t1(c,a);
  DELETE FROM sqlite_stat1;
  INSERT INTO sqlite_stat1(tbl,idx,stat)
    VALUES('t1','t1ab','12345 3 2 sz=10'),('t1','t1ca','12345 3 2 sz=20');
  ANALYZE sqlite_master;
  SELECT count(a) FROM t1;


  EXPLAIN QUERY PLAN
  SELECT count(a) FROM t1;


  DELETE FROM sqlite_stat1;
  INSERT INTO sqlite_stat1(tbl,idx,stat)
    VALUES('t1','t1ab','12345 3 2 sz=20'),('t1','t1ca','12345 3 2 sz=10');
  ANALYZE sqlite_master;
  SELECT count(a) FROM t1;


  EXPLAIN QUERY PLAN
  SELECT count(a) FROM t1;


  DROP TABLE IF EXISTS t44;
  CREATE TABLE t44(a PRIMARY KEY);
  INSERT INTO sqlite_stat1 VALUES('t44',null,'sz=0');
  ANALYZE sqlite_master;
  SELECT 0 FROM t44 WHERE a IN(1,2,3);


  DELETE FROM sqlite_stat1;
  INSERT INTO sqlite_stat1(tbl,idx,stat)
    VALUES('t1','t1ab','12345 3 2 x=5 sz=10 y=10'),
          ('t1','t1ca','12345 3 2 whatever sz=20 junk');
  ANALYZE sqlite_master;
  SELECT count(a) FROM t1;


  EXPLAIN QUERY PLAN
  SELECT count(a) FROM t1;


  DELETE FROM sqlite_stat1;
  INSERT INTO sqlite_stat1(tbl,idx,stat)
    VALUES('t1','t1ca','12345 3 2 x=5 sz=10 y=10'),
          ('t1','t1ab','12345 3 2 whatever sz=20 junk');
  ANALYZE sqlite_master;
  SELECT count(a) FROM t1;


  EXPLAIN QUERY PLAN
  SELECT count(a) FROM t1;