-- Should have 8 lines. SELECT * from testmulticorn ORDER BY filename; -- Test the cost analysis EXPLAIN select color, size from testmulticorn where color = 'blue' and size = 'big' and name = 'square' and ext = 'txt'; EXPLAIN select color, size from testmulticorn where color = 'blue' and size = 'big'; EXPLAIN select color, size from testmulticorn where color = 'blue'; EXPLAIN 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;