-- Setup the test
CREATE EXTENSION multicorn;
CREATE server multicorn_srv foreign data wrapper multicorn options (
    wrapper 'multicorn.fsfdw.FilesystemFdw'
);
CREATE language plpythonu;
CREATE TABLE temp_dir (dirname varchar);
-- Create a table with the filesystem fdw in a temporary directory,
-- and store the dirname in the temp_dir table.
CREATE OR REPLACE FUNCTION create_table() RETURNS VOID AS $$
    import plpy
    import tempfile
    import os
    dir = tempfile.mkdtemp()
    plpy.execute("""
        INSERT INTO temp_dir(dirname) VALUES ('%s')
    """ % str(dir))
    plpy.execute("""
    CREATE foreign table testmulticorn (
        color varchar,
        size varchar,
        name varchar,
        ext varchar,
        filename varchar,
        data varchar
    ) server multicorn_srv options (
        filename_column 'filename',
        content_column 'data',
        pattern '{color}/{size}/{name}.{ext}',
        root_dir '%s'
    );
    """ % dir)
    for color in ('blue', 'red'):
        for size in ('big', 'small'):
            dirname  = os.path.join(dir, color, size)
            os.makedirs(dirname)
            for name, ext in (('square', 'txt'), ('round', 'ini')):
                with open(os.path.join(dirname, '.'.join([name, ext])), 'a') as fd:
                    fd.write('Im a %s %s %s\n' % (size, color, name))
$$ language plpythonu;
select create_table();
 create_table 
--------------
 
(1 row)

-- End of Setup
\i test-common/multicorn_testfilesystem.include
-- Should have 8 lines.
SELECT * from testmulticorn ORDER BY filename;
 color | size  |  name  | ext |       filename        |          data          
-------+-------+--------+-----+-----------------------+------------------------
 blue  | big   | round  | ini | blue/big/round.ini    | Im a big blue round   +
       |       |        |     |                       | 
 blue  | big   | square | txt | blue/big/square.txt   | Im a big blue square  +
       |       |        |     |                       | 
 blue  | small | round  | ini | blue/small/round.ini  | Im a small blue round +
       |       |        |     |                       | 
 blue  | small | square | txt | blue/small/square.txt | Im a small blue square+
       |       |        |     |                       | 
 red   | big   | round  | ini | red/big/round.ini     | Im a big red round    +
       |       |        |     |                       | 
 red   | big   | square | txt | red/big/square.txt    | Im a big red square   +
       |       |        |     |                       | 
 red   | small | round  | ini | red/small/round.ini   | Im a small red round  +
       |       |        |     |                       | 
 red   | small | square | txt | red/small/square.txt  | Im a small red square +
       |       |        |     |                       | 
(8 rows)

-- Test the cost analysis
EXPLAIN select color, size from testmulticorn where color = 'blue' and size = 'big' and name = 'square' and ext = 'txt';
                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on testmulticorn  (cost=20.00..120.00 rows=1 width=64)
   Filter: (((color)::text = 'blue'::text) AND ((size)::text = 'big'::text) AND ((name)::text = 'square'::text) AND ((ext)::text = 'txt'::text))
(2 rows)

EXPLAIN select color, size from testmulticorn where color = 'blue' and size = 'big';
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Foreign Scan on testmulticorn  (cost=20.00..6000.00 rows=100 width=64)
   Filter: (((color)::text = 'blue'::text) AND ((size)::text = 'big'::text))
(2 rows)

EXPLAIN select color, size from testmulticorn where color = 'blue';
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Foreign Scan on testmulticorn  (cost=20.00..600000.00 rows=10000 width=64)
   Filter: ((color)::text = 'blue'::text)
(2 rows)

EXPLAIN select color, size, data from testmulticorn where color = 'blue' and size = 'big' and name = 'square' and ext = 'txt';
                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on testmulticorn  (cost=20.00..1000150.00 rows=1 width=96)
   Filter: (((color)::text = 'blue'::text) AND ((size)::text = 'big'::text) AND ((name)::text = 'square'::text) AND ((ext)::text = 'txt'::text))
(2 rows)

-- Test insertion
-- Normal insertion
INSERT INTO testmulticorn (color, size, name, ext, data) VALUES ('yellow', 'big', 'square', 'text', 'Im a big yellow square') RETURNING filename;
        filename        
------------------------
 yellow/big/square.text
(1 row)

-- 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;
 color | size |   name   | ext 
-------+------+----------+-----
 blue  | big  | triangle | txt
(1 row)

-- Should have 11 lines by now.
SELECT * from testmulticorn ORDER BY filename;
 color  | size  |   name   | ext  |        filename         |           data           
--------+-------+----------+------+-------------------------+--------------------------
 blue   | big   | round    | ini  | blue/big/round.ini      | Im a big blue round     +
        |       |          |      |                         | 
 blue   | big   | square   | txt  | blue/big/square.txt     | Im a big blue square    +
        |       |          |      |                         | 
 blue   | big   | triangle | txt  | blue/big/triangle.txt   | Im a big blue triangle
 blue   | small | round    | ini  | blue/small/round.ini    | Im a small blue round   +
        |       |          |      |                         | 
 blue   | small | square   | txt  | blue/small/square.txt   | Im a small blue square  +
        |       |          |      |                         | 
 red    | big   | round    | ini  | red/big/round.ini       | Im a big red round      +
        |       |          |      |                         | 
 red    | big   | square   | txt  | red/big/square.txt      | Im a big red square     +
        |       |          |      |                         | 
 red    | small | round    | ini  | red/small/round.ini     | Im a small red round    +
        |       |          |      |                         | 
 red    | small | square   | txt  | red/small/square.txt    | Im a small red square   +
        |       |          |      |                         | 
 yellow | big   | square   | text | yellow/big/square.text  | Im a big yellow square
 yellow | small | square   | txt  | yellow/small/square.txt | Im a small yellow square
(11 rows)

-- 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');
psql:test-common/multicorn_testfilesystem.include:28: ERROR:  The columns inferred from the filename do not match the supplied columns.
HINT:  Remove either the filename column or the properties column from your  statement, or ensure they match
-- Insertion with missing keys (should fail)
INSERT INTO testmulticorn (color, size, name) VALUES ('blue', 'small', 'triangle');
psql:test-common/multicorn_testfilesystem.include:31: ERROR:  The following columns are necessary: set([u'ext'])
HINT:  You can also insert an item by providing only the filename and content columns
-- Insertion with missing keys and filename (should fail)
INSERT INTO testmulticorn (color, size, name, filename) VALUES ('blue', 'small', 'triangle', 'blue/small/triangle.txt');
psql:test-common/multicorn_testfilesystem.include:34: ERROR:  The following columns are necessary: set([u'ext'])
HINT:  You can also insert an item by providing only the filename and content columns
-- 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');
psql:test-common/multicorn_testfilesystem.include:38: ERROR:  Duplicate key value violates filesystem integrity.
DETAIL:  Key (color, ext, name, size)=(yellow, text, square, big) already exists
-- Should still have 11 lines by now.
SELECT * from testmulticorn ORDER BY filename;
 color  | size  |   name   | ext  |        filename         |           data           
--------+-------+----------+------+-------------------------+--------------------------
 blue   | big   | round    | ini  | blue/big/round.ini      | Im a big blue round     +
        |       |          |      |                         | 
 blue   | big   | square   | txt  | blue/big/square.txt     | Im a big blue square    +
        |       |          |      |                         | 
 blue   | big   | triangle | txt  | blue/big/triangle.txt   | Im a big blue triangle
 blue   | small | round    | ini  | blue/small/round.ini    | Im a small blue round   +
        |       |          |      |                         | 
 blue   | small | square   | txt  | blue/small/square.txt   | Im a small blue square  +
        |       |          |      |                         | 
 red    | big   | round    | ini  | red/big/round.ini       | Im a big red round      +
        |       |          |      |                         | 
 red    | big   | square   | txt  | red/big/square.txt      | Im a big red square     +
        |       |          |      |                         | 
 red    | small | round    | ini  | red/small/round.ini     | Im a small red round    +
        |       |          |      |                         | 
 red    | small | square   | txt  | red/small/square.txt    | Im a small red square   +
        |       |          |      |                         | 
 yellow | big   | square   | text | yellow/big/square.text  | Im a big yellow square
 yellow | small | square   | txt  | yellow/small/square.txt | Im a small yellow square
(11 rows)

-- 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;
 color | size |   name   | ext |       filename       |         data          
-------+------+----------+-----+----------------------+-----------------------
 red   | big  | triangle | txt | red/big/triangle.txt | Im a big red triangle
(1 row)

ROLLBACK;
-- The file should not be persisted.
SELECT * from testmulticorn where name = 'triangle' and color = 'red' ORDER BY filename;
 color | size | name | ext | filename | data 
-------+------+------+-----+----------+------
(0 rows)

-- Test Update
WITH t as (
  UPDATE testmulticorn set name = 'rectangle' where name = 'square' RETURNING filename
)
SELECT * from t order by filename;
          filename          
----------------------------
 blue/big/rectangle.txt
 blue/small/rectangle.txt
 red/big/rectangle.txt
 red/small/rectangle.txt
 yellow/big/rectangle.text
 yellow/small/rectangle.txt
(6 rows)

-- O lines
SELECT count(1) from testmulticorn where name = 'square';
 count 
-------
     0
(1 row)

-- 6 lines
SELECT count(1) from testmulticorn where name = 'rectangle';
 count 
-------
     6
(1 row)

-- 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';
psql:test-common/multicorn_testfilesystem.include:65: ERROR:  Duplicate key value violates filesystem integrity.
DETAIL:  Key (color, ext, name, size)=(blue, txt, triangle, big) already exists
-- Update should not work when setting filename column to NULL
UPDATE testmulticorn set filename = NULL where filename = 'blue/big/rectangle.txt';
psql:test-common/multicorn_testfilesystem.include:68: ERROR:  The filename, or all pattern columns are needed.
-- 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;
psql:test-common/multicorn_testfilesystem.include:73: ERROR:  Null value in columns (color) are not allowed
DETAIL:  Failing row contains (NULL, big, rectangle, txt)
-- Content column update.
UPDATE testmulticorn set data = 'Im an updated rectangle' where filename = 'blue/big/rectangle.txt' RETURNING data;
          data           
-------------------------
 Im an updated rectangle
(1 row)

SELECT * from testmulticorn where filename = 'blue/big/rectangle.txt';
 color | size |   name    | ext |        filename        |          data           
-------+------+-----------+-----+------------------------+-------------------------
 blue  | big  | rectangle | txt | blue/big/rectangle.txt | Im an updated rectangle
(1 row)

-- Update in transactions
BEGIN;
    UPDATE testmulticorn set name = 'square' where name = 'rectangle';
    -- O lines
    SELECT count(1) from testmulticorn where name = 'rectangle';
 count 
-------
     0
(1 row)

    -- 6 lines
    SELECT count(1) from testmulticorn where name = 'square';
 count 
-------
     6
(1 row)

ROLLBACK;
-- O lines
SELECT count(1) from testmulticorn where name = 'square';
 count 
-------
     0
(1 row)

-- 6 lines
SELECT count(1) from testmulticorn where name = 'rectangle';
 count 
-------
     6
(1 row)

BEGIN;
    UPDATE testmulticorn set data = data || ' UPDATED!';
    -- 11 lines
    SELECT count(1) from testmulticorn where data ilike '% UPDATED!';
 count 
-------
    11
(1 row)

    SELECT data from testmulticorn where data ilike '% UPDATED!' order by filename limit 1;
               data               
----------------------------------
 Im an updated rectangle UPDATED!
(1 row)

ROLLBACK;
-- 0 lines
SELECT count(1) from testmulticorn where data ilike '% UPDATED!';
 count 
-------
     0
(1 row)

BEGIN;
    UPDATE testmulticorn set data = data || ' UPDATED!';
    UPDATE testmulticorn set data = data || ' TWICE!';
    SELECT data from testmulticorn order by filename; 
                   data                   
------------------------------------------
 Im an updated rectangle UPDATED! TWICE!
 Im a big blue round                     +
  UPDATED! TWICE!
 Im a big blue triangle UPDATED! TWICE!
 Im a small blue square                  +
  UPDATED! TWICE!
 Im a small blue round                   +
  UPDATED! TWICE!
 Im a big red square                     +
  UPDATED! TWICE!
 Im a big red round                      +
  UPDATED! TWICE!
 Im a small red square                   +
  UPDATED! TWICE!
 Im a small red round                    +
  UPDATED! TWICE!
 Im a big yellow square UPDATED! TWICE!
 Im a small yellow square UPDATED! TWICE!
(11 rows)

ROLLBACK;
-- No 'UPDATED! or 'TWICE!'
SELECT data from testmulticorn order by filename;
           data           
--------------------------
 Im an updated rectangle
 Im a big blue round     +
 
 Im a big blue triangle
 Im a small blue square  +
 
 Im a small blue round   +
 
 Im a big red square     +
 
 Im a big red round      +
 
 Im a small red square   +
 
 Im a small red round    +
 
 Im a big yellow square
 Im a small yellow square
(11 rows)

-- 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;
        filename        |          data           
------------------------+-------------------------
 cyan/big/rectangle.txt | Im an updated rectangle
(1 row)

    SELECT filename, data from testmulticorn where filename = 'blue/big/rectangle.txt' order by filename;
 filename | data 
----------+------
(0 rows)

    -- 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;  
         filename          |          data           
---------------------------+-------------------------
 magenta/big/rectangle.txt | Im an updated rectangle
(1 row)

    SELECT filename, data from testmulticorn where color = 'cyan' order by filename;
 filename | data 
----------+------
(0 rows)

    SELECT filename, data from testmulticorn where filename = 'blue/big/rectangle.txt' order by filename;
 filename | data 
----------+------
(0 rows)

    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;
        filename        |          data           
------------------------+-------------------------
 blue/big/rectangle.txt | Im an updated rectangle
(1 row)

    SELECT filename, data from testmulticorn where color = 'magenta' order by filename;
 filename | data 
----------+------
(0 rows)

    SELECT filename, data from testmulticorn where color = 'cyan' order by filename;
 filename | data 
----------+------
(0 rows)

COMMIT;
-- Result should be the same than pre-commit
SELECT filename, data from testmulticorn where filename = 'blue/big/rectangle.txt' order by filename;
        filename        |          data           
------------------------+-------------------------
 blue/big/rectangle.txt | Im an updated rectangle
(1 row)

SELECT filename, data from testmulticorn where color = 'magenta' order by filename;
 filename | data 
----------+------
(0 rows)

SELECT filename, data from testmulticorn where color = 'cyan' order by filename;
 filename | data 
----------+------
(0 rows)

-- DELETE test
WITH t as (
  DELETE from testmulticorn where color = 'yellow' returning filename
)
SELECT * from t order by filename;
          filename          
----------------------------
 yellow/big/rectangle.text
 yellow/small/rectangle.txt
(2 rows)

-- Should have no rows
select count(1) from testmulticorn where color = 'yellow';
 count 
-------
     0
(1 row)

-- DELETE in transaction
BEGIN;
  WITH t as (
    DELETE from testmulticorn where color = 'red' returning filename
  )
  SELECT * from t order by filename;
        filename         
-------------------------
 red/big/rectangle.txt
 red/big/round.ini
 red/small/rectangle.txt
 red/small/round.ini
(4 rows)

  select count(1) from testmulticorn where color = 'red';
 count 
-------
     0
(1 row)

ROLLBACK;
-- Should have 4 rows
select count(1) from testmulticorn where color = 'red';
 count 
-------
     4
(1 row)

-- 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;
 color |  size  |   name   | ext |         filename         |     data      
-------+--------+----------+-----+--------------------------+---------------
 cyan  | large  | triangle | jpg | cyan/large/triangle.jpg  | Im a triangle
 cyan  | medium | triangle | jpg | cyan/medium/triangle.jpg | Im a triangle
(2 rows)

    UPDATE testmulticorn set color = 'magenta' where size = 'large' and color = 'cyan' returning filename;
          filename          
----------------------------
 magenta/large/triangle.jpg
(1 row)

    -- 2 lines, one cyan, one magenta
    SELECT * from testmulticorn where color in ('cyan', 'magenta') order by filename;
  color  |  size  |   name   | ext |          filename          |     data      
---------+--------+----------+-----+----------------------------+---------------
 cyan    | medium | triangle | jpg | cyan/medium/triangle.jpg   | Im a triangle
 magenta | large  | triangle | jpg | magenta/large/triangle.jpg | Im a triangle
(2 rows)

    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;
         filename         
--------------------------
 cyan/medium/triangle.jpg
(1 row)

    -- One magenta line
    SELECT * from testmulticorn where color in ('cyan', 'magenta') order by filename;
  color  | size  |   name   | ext |          filename          |    data    
---------+-------+----------+-----+----------------------------+------------
 magenta | large | triangle | jpg | magenta/large/triangle.jpg | Im magenta
(1 row)

COMMIT;
-- Result should be the same as precommit
SELECT * from testmulticorn where color in ('cyan', 'magenta') order by filename;
  color  | size  |   name   | ext |          filename          |    data    
---------+-------+----------+-----+----------------------------+------------
 magenta | large | triangle | jpg | magenta/large/triangle.jpg | Im magenta
(1 row)

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;
 color |  size  |   name   | ext |         filename         |     data      
-------+--------+----------+-----+--------------------------+---------------
 cyan  | large  | triangle | jpg | cyan/large/triangle.jpg  | Im a triangle
 cyan  | medium | triangle | jpg | cyan/medium/triangle.jpg | Im a triangle
(2 rows)

    UPDATE testmulticorn set color = 'magenta' where size = 'large' and color = 'cyan' returning filename;
          filename          
----------------------------
 magenta/large/triangle.jpg
(1 row)

    -- 2 lines, one cyan, one magenta
    SELECT * from testmulticorn where color in ('cyan', 'magenta') order by filename;
  color  |  size  |   name   | ext |          filename          |     data      
---------+--------+----------+-----+----------------------------+---------------
 cyan    | medium | triangle | jpg | cyan/medium/triangle.jpg   | Im a triangle
 magenta | large  | triangle | jpg | magenta/large/triangle.jpg | Im a triangle
(2 rows)

    UPDATE testmulticorn set data = 'Im magenta' where color = 'magenta';
    DELETE FROM testmulticorn where color = 'cyan' RETURNING filename;
         filename         
--------------------------
 cyan/medium/triangle.jpg
(1 row)

    -- One magenta line
    SELECT * from testmulticorn where color in ('cyan', 'magenta') order by filename;
  color  | size  |   name   | ext |          filename          |    data    
---------+-------+----------+-----+----------------------------+------------
 magenta | large | triangle | jpg | magenta/large/triangle.jpg | Im magenta
(1 row)

ROLLBACK;
SELECT * from testmulticorn where color in ('cyan', 'magenta') order by filename;
 color | size | name | ext | filename | data 
-------+------+------+-----+----------+------
(0 rows)

-- Cleanup everything we've done
CREATE OR REPLACE FUNCTION cleanup_dir() RETURNS VOID AS $$
    import shutil
    root_dir = plpy.execute("""SELECT dirname from temp_dir;""")[0]['dirname']
    shutil.rmtree(root_dir)
$$ language plpythonu;
select cleanup_dir();
 cleanup_dir 
-------------
 
(1 row)

DROP FUNCTION cleanup_dir();
DROP TABLE temp_dir;
DROP FUNCTION create_table();
DROP EXTENSION multicorn cascade;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to server multicorn_srv
drop cascades to foreign table testmulticorn
DROP LANGUAGE plpythonu;