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
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
|
-- Should have 8 lines.
SELECT * from testmulticorn ORDER BY filename;
-- Test the cost analysis
EXPLAIN (costs off) select color, size from testmulticorn where color = 'blue' and size = 'big' and name = 'square' and ext = 'txt';
EXPLAIN (costs off) select color, size from testmulticorn where color = 'blue' and size = 'big';
EXPLAIN (costs off) select color, size from testmulticorn where color = 'blue';
EXPLAIN (costs off) select color, size, data from testmulticorn where color = 'blue' and size = 'big' and name = 'square' and ext = 'txt';
-- Test insertion
-- Normal insertion
INSERT INTO testmulticorn (color, size, name, ext, data) VALUES ('yellow', 'big', 'square', 'text', 'Im a big yellow square') RETURNING filename;
-- Insertion with redundant filename/properties
INSERT INTO testmulticorn (color, size, name, ext, data, filename) VALUES ('yellow', 'small', 'square', 'txt', 'Im a small yellow square',
'yellow/small/square.txt');
-- Insertion with just a filename
INSERT INTO testmulticorn (data, filename) VALUES ('Im a big blue triangle', 'blue/big/triangle.txt') RETURNING color, size, name, ext;
-- Should have 11 lines by now.
SELECT * from testmulticorn ORDER BY filename;
-- Insertion with incoherent filename/properties (should fail)
INSERT INTO testmulticorn (color, size, name, ext, data, filename) VALUES ('blue', 'big', 'triangle', 'txt', 'Im a big blue triangle',
'blue/small/triangle.txt');
-- Insertion with missing keys (should fail)
INSERT INTO testmulticorn (color, size, name) VALUES ('blue', 'small', 'triangle');
-- Insertion with missing keys and filename (should fail)
INSERT INTO testmulticorn (color, size, name, filename) VALUES ('blue', 'small', 'triangle', 'blue/small/triangle.txt');
-- Insertion which would overwrite a file.
-- Normal insertion
INSERT INTO testmulticorn (color, size, name, ext, data) VALUES ('yellow', 'big', 'square', 'text', 'Im a duplicate big square');
-- Should still have 11 lines by now.
SELECT * from testmulticorn ORDER BY filename;
-- Test insertion in transaction
BEGIN;
INSERT INTO testmulticorn (data, filename) VALUES ('Im a big red triangle', 'red/big/triangle.txt');
SELECT * from testmulticorn where name = 'triangle' and color = 'red' ORDER BY filename;
ROLLBACK;
-- The file should not be persisted.
SELECT * from testmulticorn where name = 'triangle' and color = 'red' ORDER BY filename;
-- Test Update
WITH t as (
UPDATE testmulticorn set name = 'rectangle' where name = 'square' RETURNING filename
)
SELECT * from t order by filename;
-- O lines
SELECT count(1) from testmulticorn where name = 'square';
-- 6 lines
SELECT count(1) from testmulticorn where name = 'rectangle';
-- Update should not work if it would override an existing file.
UPDATE testmulticorn set filename = 'blue/big/triangle.txt' where filename = 'blue/big/rectangle.txt';
-- Update should not work when setting filename column to NULL
UPDATE testmulticorn set filename = NULL where filename = 'blue/big/rectangle.txt';
-- Update should not work when setting a property column to NULL
WITH t as (
UPDATE testmulticorn set color = NULL where filename = 'blue/big/rectangle.txt' RETURNING color
)
SELECT * from t ORDER BY color;
-- Content column update.
UPDATE testmulticorn set data = 'Im an updated rectangle' where filename = 'blue/big/rectangle.txt' RETURNING data;
SELECT * from testmulticorn where filename = 'blue/big/rectangle.txt';
-- Update in transactions
BEGIN;
UPDATE testmulticorn set name = 'square' where name = 'rectangle';
-- O lines
SELECT count(1) from testmulticorn where name = 'rectangle';
-- 6 lines
SELECT count(1) from testmulticorn where name = 'square';
ROLLBACK;
-- O lines
SELECT count(1) from testmulticorn where name = 'square';
-- 6 lines
SELECT count(1) from testmulticorn where name = 'rectangle';
BEGIN;
UPDATE testmulticorn set data = data || ' UPDATED!';
-- 11 lines
SELECT count(1) from testmulticorn where data ilike '% UPDATED!';
SELECT data from testmulticorn where data ilike '% UPDATED!' order by filename limit 1;
ROLLBACK;
-- 0 lines
SELECT count(1) from testmulticorn where data ilike '% UPDATED!';
BEGIN;
UPDATE testmulticorn set data = data || ' UPDATED!';
UPDATE testmulticorn set data = data || ' TWICE!';
SELECT data from testmulticorn order by filename;
ROLLBACK;
-- No 'UPDATED! or 'TWICE!'
SELECT data from testmulticorn order by filename;
-- Test successive update to the same files.
BEGIN;
UPDATE testmulticorn set color = 'cyan' where filename = 'blue/big/rectangle.txt';
-- There should be one line with cyan color, 0 with the old filename
SELECT filename, data from testmulticorn where color = 'cyan' order by filename;
SELECT filename, data from testmulticorn where filename = 'blue/big/rectangle.txt' order by filename;
-- There should be one line with magenta, and 0 with cyan and the old
-- filename
UPDATE testmulticorn set color = 'magenta' where color = 'cyan';
SELECT filename, data from testmulticorn where color = 'magenta' order by filename;
SELECT filename, data from testmulticorn where color = 'cyan' order by filename;
SELECT filename, data from testmulticorn where filename = 'blue/big/rectangle.txt' order by filename;
UPDATE testmulticorn set color = 'blue' where color = 'magenta';
-- There should be one line with the old filename, and zero with the rest
SELECT filename, data from testmulticorn where filename = 'blue/big/rectangle.txt' order by filename;
SELECT filename, data from testmulticorn where color = 'magenta' order by filename;
SELECT filename, data from testmulticorn where color = 'cyan' order by filename;
COMMIT;
-- Result should be the same than pre-commit
SELECT filename, data from testmulticorn where filename = 'blue/big/rectangle.txt' order by filename;
SELECT filename, data from testmulticorn where color = 'magenta' order by filename;
SELECT filename, data from testmulticorn where color = 'cyan' order by filename;
-- DELETE test
WITH t as (
DELETE from testmulticorn where color = 'yellow' returning filename
)
SELECT * from t order by filename;
-- Should have no rows
select count(1) from testmulticorn where color = 'yellow';
-- DELETE in transaction
BEGIN;
WITH t as (
DELETE from testmulticorn where color = 'red' returning filename
)
SELECT * from t order by filename;
select count(1) from testmulticorn where color = 'red';
ROLLBACK;
-- Should have 4 rows
select count(1) from testmulticorn where color = 'red';
-- Test various combinations of INSERT/UPDATE/DELETE
BEGIN;
INSERT INTO testmulticorn (color, size, name, ext, data) VALUES
('cyan', 'medium', 'triangle', 'jpg', 'Im a triangle');
INSERT INTO testmulticorn (color, size, name, ext, data) VALUES
('cyan', 'large', 'triangle', 'jpg', 'Im a triangle');
-- 2 lines
SELECT * from testmulticorn where color = 'cyan' order by filename;
UPDATE testmulticorn set color = 'magenta' where size = 'large' and color = 'cyan' returning filename;
-- 2 lines, one cyan, one magenta
SELECT * from testmulticorn where color in ('cyan', 'magenta') order by filename;
UPDATE testmulticorn set data = 'Im magenta' where color = 'magenta';
WITH t as (
DELETE from testmulticorn where color = 'cyan' returning filename
)
SELECT * from t order by filename;
-- One magenta line
SELECT * from testmulticorn where color in ('cyan', 'magenta') order by filename;
COMMIT;
-- Result should be the same as precommit
SELECT * from testmulticorn where color in ('cyan', 'magenta') order by filename;
DELETE from testmulticorn where color = 'magenta';
-- Same as before, but rollbacking
BEGIN;
INSERT INTO testmulticorn (color, size, name, ext, data) VALUES
('cyan', 'medium', 'triangle', 'jpg', 'Im a triangle');
INSERT INTO testmulticorn (color, size, name, ext, data) VALUES
('cyan', 'large', 'triangle', 'jpg', 'Im a triangle');
-- 2 lines
SELECT * from testmulticorn where color = 'cyan' order by filename;
UPDATE testmulticorn set color = 'magenta' where size = 'large' and color = 'cyan' returning filename;
-- 2 lines, one cyan, one magenta
SELECT * from testmulticorn where color in ('cyan', 'magenta') order by filename;
UPDATE testmulticorn set data = 'Im magenta' where color = 'magenta';
DELETE FROM testmulticorn where color = 'cyan' RETURNING filename;
-- One magenta line
SELECT * from testmulticorn where color in ('cyan', 'magenta') order by filename;
ROLLBACK;
SELECT * from testmulticorn where color in ('cyan', 'magenta') order by filename;
|
