\set ECHO none
--------------------------------------------------------------------------------

-- postgresql-table_version - PostgreSQL table versioning extension
--
-- Copyright 2016 Crown copyright (c)
-- Land Information New Zealand and the New Zealand Government.
-- All rights reserved
--
-- This software is released under the terms of the new BSD license. See the 
-- LICENSE file for more information.
--
--------------------------------------------------------------------------------
-- Provide unit testing for table versioning system using pgTAP
--------------------------------------------------------------------------------

\i test/sql/preparedb

BEGIN;

SELECT plan(161);

SELECT has_schema( 'table_version' );
SELECT has_table( 'table_version', 'revision', 'Should have revision table' );
SELECT has_table( 'table_version', 'tables_changed', 'Should have tables_changed table' );
SELECT has_table( 'table_version', 'versioned_tables', 'Should have versioned_tables table' );
SELECT has_function( 'table_version', 'ver_complete_revision'::name );
SELECT has_function( 'table_version', 'ver_create_revision', ARRAY['text','timestamp without time zone','boolean'] );
SELECT has_function( 'table_version', 'ver_create_table_functions', ARRAY['name','name','name'] );
SELECT has_function( 'table_version', 'ver_create_version_trigger', ARRAY['name','name','name'] );
SELECT has_function( 'table_version', 'ver_delete_revision', ARRAY['integer'] );
SELECT has_function( 'table_version', 'ver_enable_versioning', ARRAY['name','name'] );
SELECT has_function( 'table_version', 'ver_disable_versioning', ARRAY['name','name'] );
SELECT has_function( 'table_version', 'ver_expandtemplate', ARRAY['text','text[]'] );
SELECT has_function( 'table_version', 'ver_get_last_revision'::name );
SELECT has_function( 'table_version', 'ver_get_modified_tables', ARRAY['integer'] );
SELECT has_function( 'table_version', 'ver_get_modified_tables', ARRAY['integer','integer'] );
SELECT has_function( 'table_version', 'ver_get_revision', ARRAY['timestamp without time zone'] );
SELECT has_function( 'table_version', 'ver_get_revision', ARRAY['integer'] );
SELECT has_function( 'table_version', 'ver_get_revisions', ARRAY['integer[]'] );
SELECT has_function( 'table_version', 'ver_get_revisions', ARRAY['timestamp without time zone','timestamp without time zone'] );
SELECT has_function( 'table_version', 'ver_get_table_base_revision', ARRAY['name','name'] );
SELECT has_function( 'table_version', 'ver_get_table_last_revision', ARRAY['name','name'] );
SELECT has_function( 'table_version', 'ver_get_version_table', ARRAY['name','name'] );
SELECT has_function( 'table_version', 'ver_get_version_table_full', ARRAY['name','name'] );
SELECT has_function( 'table_version', 'ver_get_versioned_table_key', ARRAY['name','name'] );
SELECT has_function( 'table_version', 'ver_get_versioned_tables'::name );
SELECT has_function( 'table_version', 'ver_is_table_versioned', ARRAY['name','name'] );
SELECT has_function( 'table_version', 'ver_versioned_table_change_column_type', ARRAY['name','name', 'name', 'text'] );
SELECT has_function( 'table_version', 'ver_versioned_table_add_column', ARRAY['name','name', 'name', 'text'] );
SELECT has_function( 'table_version', 'ver_versioned_table_drop_column', ARRAY['name', 'name', 'name'] );
SELECT has_function( 'table_version', 'ver_fix_revision_disorder'::name );

CREATE SCHEMA foo;

CREATE TABLE foo.bar (
    id INTEGER NOT NULL PRIMARY KEY,
    d1 TEXT
);

INSERT INTO foo.bar (id, d1) VALUES
(1, 'foo bar 1'),
(2, 'foo bar 2'),
(3, 'foo bar 3');

SELECT ok(table_version.ver_enable_versioning('foo', 'bar'), 'Enable versioning on foo.bar');
SELECT ok(table_version.ver_is_table_versioned('foo', 'bar'), 'Check table is revisioned versioning on foo.bar');
SELECT is(table_version.ver_get_versioned_table_key('foo', 'bar'), 'id', 'Check table foo.bar table key');
SELECT is(table_version.ver_get_table_base_revision('foo', 'bar'), 1001, 'Check table base revision');

SELECT has_function( 'table_version', 'ver_get_foo_bar_revision', ARRAY['integer'] );
SELECT has_function( 'table_version', 'ver_get_foo_bar_diff', ARRAY['integer','integer'] );

SELECT set_eq(
    'SELECT * FROM table_version.ver_get_foo_bar_revision(1001)',
    $$VALUES (1, 'foo bar 1'),(2, 'foo bar 2'),(3, 'foo bar 3')$$,
    'Check get table revision function API'
);

SELECT results_eq(
    'SELECT * FROM table_version.ver_get_foo_bar_diff(1000, 1001) ORDER BY id',
    $$VALUES ('I'::char, 1, 'foo bar 1'),
             ('I'::char, 2, 'foo bar 2'),
             ('I'::char, 3, 'foo bar 3')$$,
    'Foo bar diff for table creation'
);

-- Edit 1 insert, update and delete
SELECT is(table_version.ver_create_revision('Foo bar edit 1'), 1002, 'Create edit 1 revision');

INSERT INTO foo.bar (id, d1) VALUES (4, 'foo bar 4');

UPDATE foo.bar
SET    d1 = 'foo bar 1 edit'
WHERE  id = 1;

DELETE FROM foo.bar
WHERE id = 3;

SELECT ok(table_version.ver_complete_revision(), 'Complete edit 1 revision');

SELECT results_eq(
    'SELECT * FROM table_version.ver_get_foo_bar_diff(1001, 1002) ORDER BY id',
    $$VALUES ('U'::char, 1, 'foo bar 1 edit'),
             ('D'::char, 3, 'foo bar 3'),
             ('I'::char, 4, 'foo bar 4')$$,
    'Foo bar diff for edit 1'
);

-- Edit 2 insert, update and delete
SELECT is(table_version.ver_create_revision('Foo bar edit 2'), 1003, 'Create edit 2 revision');

INSERT INTO foo.bar (id, d1) VALUES (5, 'foo bar 5');

UPDATE foo.bar
SET    d1 = 'foo bar 2 edit'
WHERE  id = 2;

UPDATE foo.bar
SET    d1 = 'foo bar 4 edit'
WHERE  id = 4;

DELETE FROM foo.bar
WHERE id = 1;

SELECT ok(table_version.ver_complete_revision(), 'Complete edit 2 revision');

SELECT results_eq(
    'SELECT * FROM table_version.ver_get_foo_bar_diff(1001, 1002) ORDER BY id',
    $$VALUES ('U'::char, 1, 'foo bar 1 edit'),
             ('D'::char, 3, 'foo bar 3'),
             ('I'::char, 4, 'foo bar 4')$$,
    'Foo bar diff for edit 1 (recheck)'
);


SELECT results_eq(
    'SELECT * FROM table_version.ver_get_foo_bar_diff(1002, 1003) ORDER BY id',
    $$VALUES ('D'::char, 1, 'foo bar 1 edit'),
             ('U'::char, 2, 'foo bar 2 edit'),
             ('U'::char, 4, 'foo bar 4 edit'),
             ('I'::char, 5, 'foo bar 5')$$,
    'Foo bar diff range for edit 1-2'
);

SELECT results_eq(
    'SELECT * FROM table_version.ver_get_foo_bar_diff(1001, 1003) ORDER BY id',
    $$VALUES ('D'::char, 1, 'foo bar 1 edit'),
             ('U'::char, 2, 'foo bar 2 edit'),
             ('D'::char, 3, 'foo bar 3'),
             ('I'::char, 4, 'foo bar 4 edit'),
             ('I'::char, 5, 'foo bar 5')$$,
    'Foo bar diff for edit 2'
);

SELECT results_eq(
    'SELECT * FROM table_version.ver_get_foo_bar_diff(1001, 1010) ORDER BY id',
    $$VALUES ('D'::char, 1, 'foo bar 1 edit'),
             ('U'::char, 2, 'foo bar 2 edit'),
             ('D'::char, 3, 'foo bar 3'),
             ('I'::char, 4, 'foo bar 4 edit'),
             ('I'::char, 5, 'foo bar 5')$$,
    'Foo bar diff for edit 2 (larger range parameter)'
);

-- Edit 3 delete that was added in test edit 2.
SELECT is(table_version.ver_create_revision('Foo bar edit 3'), 1004, 'Create edit 3 revision');

DELETE FROM foo.bar
WHERE id = 4;

SELECT ok(table_version.ver_complete_revision(), 'Complete edit 3 revision');

SELECT results_eq(
    'SELECT * FROM table_version.ver_get_foo_bar_diff(1001, 1004) ORDER BY id',
    $$VALUES ('D'::char, 1, 'foo bar 1 edit'),
             ('U'::char, 2, 'foo bar 2 edit'),
             ('D'::char, 3, 'foo bar 3'),
             ('I'::char, 5, 'foo bar 5')$$,
    'Foo bar diff check to ensure a row created and deleted in between revisions is not returned'
);

-- Edit 4 add create another revision to check create/delete feature in between revision work
-- the delete is NOT the last revision.
SELECT is(table_version.ver_create_revision('Foo bar edit 4'), 1005, 'Create edit 4 revision');

INSERT INTO foo.bar (id, d1) VALUES (6, 'foo bar 6');

SELECT ok(table_version.ver_complete_revision(), 'Complete edit 4 revision');

SELECT results_eq(
    'SELECT * FROM table_version.ver_get_foo_bar_diff(1004, 1005) ORDER BY id',
    $$VALUES ('I'::char, 6, 'foo bar 6')$$,
    'Foo bar diff for edit 4'
);

SELECT results_eq(
    'SELECT * FROM table_version.ver_get_foo_bar_diff(1001, 1005) ORDER BY id',
    $$VALUES ('D'::char, 1, 'foo bar 1 edit'), 
             ('U'::char, 2, 'foo bar 2 edit'), 
             ('D'::char, 3, 'foo bar 3'), 
             ('I'::char, 5, 'foo bar 5'), 
             ('I'::char, 6, 'foo bar 6')$$,
    'Foo bar diff check to ensure a feature created and delete in between revision does not show (delete does not occur in last revision)'
);

-- Edit 5 re-insert a delete row
SELECT is(table_version.ver_create_revision('Foo bar edit 4'), 1006, 'Create edit 4 revision');

INSERT INTO foo.bar (id, d1) VALUES (1, 'foo bar 1 (re-insert)');

SELECT ok(table_version.ver_complete_revision(), 'Complete edit 4 revision');

SELECT results_eq(
    'SELECT * FROM table_version.ver_get_foo_bar_diff(1005, 1006) ORDER BY id',
    $$VALUES ('I'::char, 1, 'foo bar 1 (re-insert)')$$,
    'Foo bar diff check re-insert a prevoiusly deleted row #1'
);

SELECT results_eq(
    'SELECT * FROM table_version.ver_get_foo_bar_diff(1001, 1006) ORDER BY id',
    $$VALUES ('U'::char, 1, 'foo bar 1 (re-insert)'), 
             ('U'::char, 2, 'foo bar 2 edit'), 
             ('D'::char, 3, 'foo bar 3'), 
             ('I'::char, 5, 'foo bar 5'), 
             ('I'::char, 6, 'foo bar 6')$$,
    'Foo bar diff check re-insert a prevoiusly deleted row #2'
);

SELECT is(table_version.ver_versioned_table_change_column_type('foo', 'bar', 'd1', 'VARCHAR(100)'), TRUE, 'Change column datatype');

-- TODO: check that the column was changed both in table and revision table

SELECT is(table_version.ver_versioned_table_add_column('foo', 'bar', 'baz', 'TEXT'), TRUE, 'Add column datatype');

-- check that the column was added in both in table and revision table
SELECT has_column( 'foo', 'bar', 'baz',
    'table foo.bar has column baz' );
SELECT col_type_is( 'foo', 'bar', 'baz', 'text',
    'foo.bar.baz is of type text' );
SELECT has_column( 'table_version', 'foo_bar_revision', 'baz',
    'revision table for foo.bar has column baz' );
SELECT col_type_is( 'table_version', 'foo_bar_revision',
    'baz', 'text',
    'foo_bar_revision.baz is of type text' );

-- Verify the new column can be used
SELECT is(table_version.ver_create_revision('Foo bar edit 6'), 1007, 'Create edit 6 revision');
SELECT lives_ok($$INSERT INTO foo.bar (id,baz) VALUES (4,'baz')$$);
SELECT ok(table_version.ver_complete_revision(), 'Complete edit 5 revision');

SELECT results_eq(
    'SELECT * FROM table_version.ver_get_foo_bar_diff(1006, 1007) ORDER BY baz',
    $$VALUES ('I'::char, 4, null::varchar, 'baz'::text)$$,
    'Foo bar diff for new record after column added'
);

SELECT is(table_version.ver_versioned_table_drop_column('foo', 'bar', 'baz'), TRUE, 'Drop column');

-- check that the column was removed both in table and revision table
SELECT hasnt_column( 'foo', 'bar', 'baz',
    'table foo.bar had column baz removed' );
SELECT hasnt_column( 'table_version', 'foo_bar_revision', 'baz',
    'revision table for foo.bar had column baz removed' );

SELECT ok(table_version.ver_disable_versioning('foo', 'bar'), 'Disable versioning on foo.bar');

-- test versioning of table with text primary key
CREATE TABLE foo.bar2 (
    baz TEXT NOT NULL PRIMARY KEY,
    qux TEXT NOT NULL
);

INSERT INTO foo.bar2 (baz, qux) VALUES
('foo bar 1', 'qux1'),
('foo bar 2', 'qux2'),
('foo bar 3', 'qux3');

SELECT ok(table_version.ver_enable_versioning('foo', 'bar2'), 'Enable versioning of text primary key on foo.bar2');
SELECT ok(table_version.ver_is_table_versioned('foo', 'bar2'), 'Check table is revisioned versioning on foo.bar2');
SELECT is(table_version.ver_get_versioned_table_key('foo', 'bar2'), 'baz', 'Check table foo.bar table key');

-- Edit 1 insert, update and delete for text primary key
SELECT is(table_version.ver_create_revision('Foo bar2 edit'), 1009, 'Create edit text PK');

INSERT INTO foo.bar2 (baz, qux) VALUES ('foo bar 4', 'qux4');

UPDATE foo.bar2
SET    qux = 'qux1 edit'
WHERE  baz = 'foo bar 1';

DELETE FROM foo.bar2
WHERE baz = 'foo bar 3';

SELECT ok(table_version.ver_complete_revision(), 'Complete edit text PK');

SELECT results_eq(
    'SELECT * FROM table_version.ver_get_foo_bar2_diff(1008, 1009) ORDER BY baz',
    $$VALUES ('U'::char, 'foo bar 1', 'qux1 edit'),
             ('D'::char, 'foo bar 3', 'qux3'),
             ('I'::char, 'foo bar 4', 'qux4')$$,
    'Foo bar2 diff for text PK edit'
);

SELECT ok(table_version.ver_disable_versioning('foo', 'bar2'), 'Disable versioning on foo.bar2');

CREATE TABLE foo.bar3 (
    id INTEGER NOT NULL PRIMARY KEY,
    d1 TEXT
);

INSERT INTO foo.bar3 (id, d1) VALUES
(1, 'foo bar 1'),
(2, 'foo bar 2'),
(3, 'foo bar 3');

CREATE TABLE foo.bar4 (
    id INTEGER NOT NULL PRIMARY KEY,
    d1 TEXT
);

INSERT INTO foo.bar4 (id, d1) VALUES
(1, 'foo bar 1a'),
(2, 'foo bar 2a'),
(4, 'foo bar 4'),
(5, 'foo bar 5'),
(6, 'foo bar 6');

SELECT results_eq(
    $$SELECT * FROM
table_version.ver_get_table_differences('foo.bar3', 'foo.bar4', 'id') AS (action CHAR(1), ID INTEGER) ORDER BY 2$$,
    $$VALUES ('U'::CHAR, 1),
             ('U'::CHAR, 2),
             ('D'::CHAR, 3),
             ('I'::CHAR, 4),
             ('I'::CHAR, 5),
             ('I'::CHAR, 6)$$,
    'Diff function between foo3 and foo4'
);

SELECT results_eq(
    $$SELECT number_inserts, number_updates, number_deletes FROM table_version.ver_apply_table_differences('foo.bar3', 'foo.bar4', 'id')$$,
    $$VALUES (3::BIGINT, 2::BIGINT, 1::BIGINT)$$,
    'Apply diff to table function between foo3 and foo4'
);

CREATE ROLE test_owner;
GRANT USAGE on SCHEMA foo to test_owner;

CREATE ROLE test_user;
GRANT USAGE on SCHEMA foo to test_user;

CREATE TABLE foo.bar5 (
    baz INTEGER NOT NULL PRIMARY KEY,
    qux TEXT
);

ALTER TABLE foo.bar5 OWNER TO test_owner;
GRANT SELECT, INSERT, UPDATE, DELETE ON foo.bar5 TO test_user;

SELECT ok(table_version.ver_enable_versioning('foo', 'bar5'), 'Enable versioning on table with permissions');

SELECT table_owner_is(
    'table_version', 'foo_bar5_revision', 'test_owner',
    'Test foo_bar5_revision ownership'
);

-- Test for https://github.com/linz/postgresql-tableversion/issues/99
SELECT function_owner_is(
    'table_version', 'foo_bar5_revision', ARRAY[]::text[],
    ( SELECT r.rolname FROM pg_proc p, pg_roles r
      WHERE p.oid = 'table_version.ver_version'::regproc
        AND r.oid = p.proowner ),
    'Owner of foo_bar5_revision function should be extension owner'
);

SELECT table_privs_are(
    'table_version', 'foo_bar5_revision', 'test_user', ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE'],
    'Test foo_bar5_revision permission for test_user'
);

SELECT ok(table_version.ver_disable_versioning('foo', 'bar5'), 'Disable versioning on foo.bar5');

SELECT is_empty('SELECT * FROM table_version.tables_changed', 'tables_changed is empty after ver_disable_version');
SELECT is_empty('SELECT * FROM table_version.versioned_tables', 'versioned_tables is empty after ver_disable_version');

-- Now try again as the owner user

SET SESSION AUTHORIZATION test_owner;

SELECT ok(table_version.ver_enable_versioning('foo', 'bar5'),
    'Enable versioning on table as the unprivileged table owner');

SELECT table_owner_is(
    'table_version', 'foo_bar5_revision', 'test_owner',
    'Test foo_bar5_revision ownership'
);

SELECT table_privs_are(
    'table_version', 'foo_bar5_revision', 'test_user',
    ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE'],
    'Test foo_bar5_revision permission for test_user'
);

-- Check that owner can add / drop columns
-- See https://github.com/linz/postgresql-tableversion/issues/113
SELECT is(table_version.ver_versioned_table_add_column('foo', 'bar5', 'c2', 'TEXT'),
    TRUE, 'Revisioned table owner can add column');
SELECT is(table_version.ver_versioned_table_drop_column('foo', 'bar5', 'c2'),
    TRUE, 'Revisioned table owner can drop column');

-- Check that owner can change column type
-- See https://github.com/linz/postgresql-tableversion/pull/125
SELECT is(table_version.ver_versioned_table_add_column('foo', 'bar5', 'c3', 'int2'),
    TRUE, 'Revisioned table owner can add column (again)');
SELECT is(table_version.ver_versioned_table_change_column_type('foo', 'bar5', 'c3', 'int4'),
    TRUE, 'Revisioned table owner can change column type');

-- Check that owner can regenerate version triggers
SELECT is(table_version.ver_create_version_trigger('foo', 'bar5', 'baz'),
    TRUE, 'Table owner can regenerate version triggers');
SELECT is(table_version.ver_create_version_trigger('foo.bar5', 'baz'),
    TRUE, 'Table owner can regenerate version triggers (regclass version)');

SET SESSION AUTHORIZATION test_user;

SELECT throws_like($$
  SELECT table_version.ver_disable_versioning('foo', 'bar5') $$,
  '% table owner role %',
  'Unexpected exception from non-owner attempt at unversioning table');

-- Check that non-owner can NOT add / drop / change columns
SELECT throws_like($$ SELECT
    table_version.ver_versioned_table_add_column('foo', 'bar5', 'c2', 'TEXT')
    $$,
    '% table owner role %',
    'Revisioned table non-owner can not add column');
SELECT throws_like($$ SELECT
    table_version.ver_versioned_table_drop_column('foo', 'bar5', 'c2')
    $$,
    '% table owner role %',
    'Revisioned table non-owner can not drop column');
SELECT throws_like($$ SELECT
    table_version.ver_versioned_table_change_column_type('foo', 'bar5', 'c3', 'int8')
    $$,
    '% table owner role %',
    'Revisioned table non-owner can not change column type');

-- Check that non-owner can NOT add version triggers
SELECT throws_like($$ SELECT
    table_version.ver_create_version_trigger('foo', 'bar5', 'baz')
    $$,
    '% table owner role %',
    'Table non-owner can not create version triggers');

RESET SESSION AUTHORIZATION;

SELECT ok(table_version.ver_disable_versioning('foo', 'bar5'),
    'Disable versioning on foo.bar5 as table owner');

-- Check that non-owner user cannot version/unversion the table
SET SESSION AUTHORIZATION test_user;

SELECT throws_like($$
  SELECT table_version.ver_enable_versioning('foo', 'bar5') $$,
  '% table owner role %',
  'Unexpected exception from non-owner attempt at versioning table');

RESET SESSION AUTHORIZATION;



DROP TABLE foo.bar;
DROP TABLE foo.bar2;
DROP TABLE foo.bar3;
DROP TABLE foo.bar4;
DROP TABLE foo.bar5;

-- See
-- https://github.com/linz/postgresql-tableversion/pull/32#issuecomment-319019821

CREATE SCHEMA "fOo";
CREATE SCHEMA "foO";

CREATE TABLE "fOo"."Bar3"("order","B") AS VALUES
(1, 'foo bar 1'),
(2, 'foo bar 2'),
(3, 'foo bar 3');
ALTER TABLE "fOo"."Bar3" ADD PRIMARY KEY("order");

CREATE TABLE "foO"."bAr4" ("order", "B") AS VALUES
(1, 'foo bar 1'),
(2, 'foo bar 2a'),
(4, 'foo bar 4');
ALTER TABLE "foO"."bAr4" ADD PRIMARY KEY("order");

SELECT results_eq(
    $$SELECT * FROM
table_version.ver_get_table_differences('"fOo"."Bar3"', '"foO"."bAr4"', 'order')
AS (action CHAR(1), ID INTEGER)$$,
    $$VALUES ('U'::CHAR, 2),
             ('D'::CHAR, 3),
             ('I'::CHAR, 4)$$,
    'Diff function between fOo.Bar3 and foO.bAr4'
);

ALTER TABLE "fOo"."Bar3" ADD u numeric unique;
ALTER TABLE "foO"."bAr4" ADD u numeric unique;

SELECT results_eq(
    $$SELECT * FROM
table_version.ver_get_table_differences('"fOo"."Bar3"', '"foO"."bAr4"', 'order')
AS (action CHAR(1), ID INTEGER)$$,
    $$VALUES ('U'::CHAR, 2),
             ('D'::CHAR, 3),
             ('I'::CHAR, 4)$$,
    'Diff function between fOo.Bar3 and foO.bAr4 (with unique column)'
);

DROP SCHEMA "fOo" CASCADE;
DROP SCHEMA "foO" CASCADE;

-- Test effects on dropping table

CREATE TABLE foo.dropme (id INTEGER NOT NULL PRIMARY KEY, d1 TEXT);
SELECT ok(table_version.ver_enable_versioning('foo', 'dropme'),
  'enable versioning on foo.dropme');
SELECT ok(table_version.ver_is_table_versioned('foo', 'dropme'),
  'foo.dropme is versioned');
SELECT set_has($$
  SELECT schema_name,table_name
  FROM table_version.ver_get_versioned_tables()
  $$, $$ VALUES ('foo','dropme') $$,
  'foo.dropme is returned by ver_get_versioned_tables'
);
SELECT is(table_version.ver_get_versioned_table_key('foo','dropme'),
  'id', 'foo.dropme versioned table key is "id"');
SELECT throws_like('DROP TABLE foo.dropme',
  'cannot drop%depend on it',
  'foo.dropme can only be drop with CASCADE') ;
DROP TABLE foo.dropme CASCADE;
SELECT ok(NOT table_version.ver_is_table_versioned('foo', 'dropme'),
  'foo.dropme is not versioned after drop cascade');
SELECT set_hasnt($$
  SELECT schema_name,table_name
  FROM table_version.ver_get_versioned_tables()
  $$, $$ VALUES ('foo','dropme') $$,
  'foo.dropme is not returned by ver_get_versioned_tables after drop'
);
SELECT is(table_version.ver_get_versioned_table_key('foo','dropme'),
  NULL, 'foo.dropme versioned table key is null after drop');
CREATE TABLE foo.dropme (id INTEGER NOT NULL PRIMARY KEY, d1 TEXT);
SELECT ok(NOT table_version.ver_is_table_versioned('foo', 'dropme'),
  'foo.dropme is not versioned after re-create');
SELECT set_hasnt($$
  SELECT schema_name,table_name
  FROM table_version.ver_get_versioned_tables()
  $$, $$ VALUES ('foo','dropme') $$,
  'foo.dropme is not returned by ver_get_versioned_tables after re-create'
);
SELECT is(table_version.ver_get_versioned_table_key('foo','dropme'),
  NULL, 'foo.dropme versioned table key is null after re-create');
SELECT ok(table_version.ver_enable_versioning('foo','dropme'),
  'can enable versioning on drop-recreated table foo.dropme');
SELECT ok(table_version.ver_is_table_versioned('foo', 'dropme'),
  'foo.dropme is versioned after re-create and ver_enable_versioning');
SELECT set_has($$
  SELECT schema_name,table_name
  FROM table_version.ver_get_versioned_tables()
  $$, $$ VALUES ('foo','dropme') $$,
  'foo.dropme is returned by ver_get_versioned_tables after re-create and ver_enable_versioning'
);
SELECT is(table_version.ver_get_versioned_table_key('foo','dropme'),
  'id', 'foo.dropme versioned table key is "id" after re-create and ver_enable_versioning');
-- TODO: test changing key !
SELECT throws_like(
  $$ SELECT table_version.ver_enable_versioning('foo','dropme') $$,
  'Table % is already versioned',
  'ver_enable_versioning throws when called on already-versioned table');

-- New in 1.3

SELECT has_function( 'table_version', 'ver_version'::name );

-- New in 1.4

SELECT has_function( 'table_version', 'ver_enable_versioning', ARRAY['regclass'] );

-- New in 1.5

SELECT has_function( 'table_version', 'ver_disable_versioning', ARRAY['regclass'] );
SELECT has_function( 'table_version', 'ver_versioned_table_add_column', ARRAY['regclass', 'name', 'text'] );
SELECT has_function( 'table_version', 'ver_versioned_table_drop_column', ARRAY['regclass', 'name'] );
SELECT has_function( 'table_version', 'ver_versioned_table_change_column_type', ARRAY['regclass', 'name', 'text'] );
SELECT has_function( 'table_version', 'ver_create_version_trigger', ARRAY['regclass', 'name'] );


-- Added after problem found in admin_bouldaries_uploader

CREATE schema test_schema;
CREATE TABLE test_schema.table1_with_int_pk (
    id INTEGER NOT NULL PRIMARY KEY,
    description VARCHAR
);
INSERT INTO test_schema.table1_with_int_pk VALUES
    (100,'AAA'),
    (200,'BBB'),
    (300,'CCC');

CREATE TABLE test_schema.table2_with_int_pk (
    id INTEGER NOT NULL PRIMARY KEY,
    description VARCHAR
);
INSERT INTO test_schema.table2_with_int_pk VALUES
    (100,'AAA'),
    (200,'YYY'),
    (300,'ZZZ');

CREATE TABLE test_schema.table1_with_varchar_pk (
id varchar not null primary key,
description varchar
);
INSERT INTO test_schema.table1_with_varchar_pk values('100','AAA');
INSERT INTO test_schema.table1_with_varchar_pk values('200','BBB');
INSERT INTO test_schema.table1_with_varchar_pk values('300','CCC');

CREATE TABLE test_schema.table2_with_varchar_pk (
    id VARCHAR NOT NULL PRIMARY KEY,
    description VARCHAR
);
INSERT INTO test_schema.table2_with_varchar_pk VALUES
    ('100','AAA'),
    ('200','YYY'),
    ('300','ZZZ');


PREPARE "test1" AS SELECT T.id, T.code 
	from table_version.ver_get_table_differences(
	'test_schema.table1_with_int_pk',
	'test_schema.table2_with_int_pk',
	'id') 
	AS T(code char(1), id int);

PREPARE "test2" AS SELECT T.id, T.code 
	from table_version.ver_get_table_differences(
	'test_schema.table1_with_varchar_pk',
	'test_schema.table2_with_varchar_pk',
	'id') 
	AS T(code char(1), id varchar);

SELECT lives_ok('"test1"','1. Request char/integer set result');
SELECT lives_ok('"test2"','2. Request char/varchar set result');

---------------------------------------
-- Test for ver_fix_revision_disorder
---------------------------------------

SELECT is(table_version.ver_fix_revision_disorder(), 0::bigint,
  'no revision disorder in normal situation');

SELECT lives_ok('CREATE TABLE t (k int primary key, v text)');
SELECT lives_ok($$ SELECT table_version.ver_enable_versioning('public','t') $$);

SELECT lives_ok($$ SELECT setval('table_version.revision_id_seq', 2, true) $$);

SELECT lives_ok($$ SELECT table_version.ver_create_revision('r1') $$); -- 2
SELECT lives_ok($$ INSERT INTO t VALUES (1, 'a') $$);
SELECT lives_ok($$ SELECT table_version.ver_complete_revision() $$);

SELECT lives_ok($$ SELECT setval('table_version.revision_id_seq', 1, true) $$);

SELECT lives_ok($$ SELECT table_version.ver_create_revision('r2') $$); -- 1
SELECT lives_ok($$ UPDATE t set v = 'a2' WHERE k = 1 $$);
SELECT lives_ok($$ SELECT table_version.ver_complete_revision() $$);

SELECT lives_ok($$ SELECT setval('table_version.revision_id_seq', 3, true) $$);

SELECT lives_ok($$ SELECT table_version.ver_create_revision('r3') $$); -- 3
SELECT lives_ok($$ UPDATE t set v = 'a3' WHERE k = 1 $$);
SELECT lives_ok($$ SELECT table_version.ver_complete_revision() $$);

SELECT isnt_empty($$
  SELECT * FROM table_version.public_t_revision
  WHERE _revision_expired < _revision_created
$$, 'Disordered revisions are found in public_t_revision');

SELECT is(table_version.ver_fix_revision_disorder(), 3::bigint,
  'three revision renamed when needed');

SELECT is_empty($$
  SELECT * FROM table_version.public_t_revision
  WHERE _revision_expired < _revision_created;
$$, 'No disordered revisions are found in public_t_revision');

SELECT isnt_empty($$
  SELECT count(*) FROM table_version.public_t_revision
  WHERE _revision_expired > _revision_created;
$$, 'Ordered revisions are found in public_t_revision');


SELECT is(table_version.ver_fix_revision_disorder(), 0::bigint,
  'no revision moved after fix');

---------------------------------------
-- Test for table triggers
---------------------------------------

SELECT lives_ok('CREATE TABLE "index" (k int primary key, "desc" text, "table" int)');
SELECT lives_ok($$ SELECT table_version.ver_enable_versioning('public','index') $$);
SELECT lives_ok($$ SELECT table_version.ver_create_revision('r1') $$);
SELECT lives_ok($$ INSERT INTO "index" VALUES (1, 'a', '23') $$);
-- reorder the columns of the revision table
-- See https://github.com/linz/postgresql-tableversion/issues/109
SELECT lives_ok($$ ALTER TABLE table_version.public_index_revision DROP column "desc"; $$);
-- insert new value, testing effects of a missing column
SELECT throws_like($$ INSERT INTO "index" VALUES (3, 'b', '24') $$,
$$column "desc" of relation "public_index_revision" does not exist$$);
SELECT lives_ok($$ ALTER TABLE table_version.public_index_revision ADD column "desc" text; $$);
-- insert new value
SELECT lives_ok($$ INSERT INTO "index" VALUES (2, 'b', '24') $$);
-- Add additional column
SELECT lives_ok($$ ALTER TABLE table_version.public_index_revision ADD column "desc2" text; $$);
-- insert new value
SELECT lives_ok($$ INSERT INTO "index" VALUES (4, 'b', '24') $$);

---------------------------------------
-- End of tests
---------------------------------------

SELECT * FROM finish();

ROLLBACK;