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
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140


  CREATE TABLE t12nc(id INTEGER, x TEXT UNIQUE COLLATE nocase);
  INSERT INTO t12nc VALUES(1,'abcde'),(2,'uvwxy'),(3,'ABCDEF');
  CREATE TABLE t12b(id INTEGER, x TEXT UNIQUE COLLATE binary);
  INSERT INTO t12b VALUES(1,'abcde'),(2,'uvwxy'),(3,'ABCDEF');
  SELECT id FROM t12nc WHERE x LIKE 'abc%' ORDER BY +id;


  SELECT id FROM t12b WHERE x LIKE 'abc%' ORDER BY +id;


  SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id;


  SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id;


  SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE nocase ORDER BY +id;


  SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE nocase ORDER BY +id;


  EXPLAIN QUERY PLAN
  SELECT id FROM t12nc WHERE x LIKE 'abc%' ORDER BY +id;


  EXPLAIN QUERY PLAN
  SELECT id FROM t12b WHERE x LIKE 'abc%' ORDER BY +id;


  EXPLAIN QUERY PLAN
  SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE nocase ORDER BY +id;


  EXPLAIN QUERY PLAN
  SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE nocase ORDER BY +id;


  EXPLAIN QUERY PLAN
  SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id;


  EXPLAIN QUERY PLAN
  SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id;


  SELECT char(0x304d) LIKE char(0x306d);


  SELECT char(0x4d) LIKE char(0x306d);


  SELECT char(0x304d) LIKE char(0x6d);


  SELECT char(0x4d) LIKE char(0x6d);


  CREATE TABLE t15(x TEXT COLLATE nocase, y, PRIMARY KEY(x));
  INSERT INTO t15(x,y) VALUES
    ('abcde',1), ('ab%de',2), ('a_cde',3),
    ('uvwxy',11),('uvwx%',12),('uvwx_',13),
    ('_bcde',21),('%bcde',22),
    ('abcd_',31),('abcd%',32),
    ('ab%xy',41);
  SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '/';


  EXPLAIN QUERY PLAN
  SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '/';


  EXPLAIN QUERY PLAN
  SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '//';


  EXPLAIN QUERY PLAN
  SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '';


  SELECT y FROM t15 WHERE x LIKE 'abcdx%%' ESCAPE 'x';


  SELECT y FROM t15 WHERE x LIKE 'abx%%' ESCAPE 'x' ORDER BY +y


  EXPLAIN QUERY PLAN
  SELECT y FROM t15 WHERE x LIKE 'abx%%' ESCAPE 'x' ORDER BY +y


  SELECT y FROM t15 WHERE x LIKE '/%bc%' ESCAPE '/';


  EXPLAIN QUERY PLAN
  SELECT y FROM t15 WHERE x LIKE '/%bc%' ESCAPE '/';


  CREATE TABLE t1(a INTEGER COLLATE NOCASE);
  CREATE INDEX i1 ON t1(a);
  INSERT INTO t1 VALUES(' 1x');
  INSERT INTO t1 VALUES(' 1-');


  SELECT * FROM t1 WHERE a LIKE ' 1%';


  SELECT * FROM t1 WHERE a LIKE ' 1-';


  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(id INTEGER PRIMARY KEY, x TEXT);
  INSERT INTO t1 VALUES
    (1,'abcde'),
    (2,'abc_'),
    (3,'abc__'),
    (4,'abc%'),
    (5,'abc%%');
  SELECT id FROM t1 WHERE x LIKE 'abc%%' ESCAPE '%';


  SELECT id FROM t1 WHERE x LIKE 'abc__' ESCAPE '_';


  SELECT 'x' LIKE '%' ESCAPE '_';


  CREATE TABLE t1(x INT, y TEXT);
  INSERT INTO t1 VALUES(1,'abc'),(2,'ABC'),(3,'Abc');
  CREATE VIEW t2 AS SELECT * FROM t1 WHERE y LIKE 'a%';
  SELECT * FROM t2;


  PRAGMA case_sensitive_like=OFF;
  SELECT * FROM t2;


  PRAGMA case_sensitive_like=ON;
  SELECT * FROM t2;