Обсуждение: How can I include sql file in pgTAP unittest?

On 03/02/2018 01:17 AM, Stéphane Klein wrote:
> Hi,
> 
> context: I would like to write UnitTest to test pgSQL triggers which use 
> postgres_fdw extension.
> I use pgTAP <http://pgtap.org/> to write this UnitTest (I use this 
> Docker environment poc-postgresql-pgTAP 
> <https://github.com/harobed/poc-postgresql-pgTAP>).
> 
> All works perfectly with this test file:
> 
> BEGIN;
>    SELECT plan(1);
> 
>    CREATE EXTENSION IF NOT EXISTS postgres_fdw;
>    DROP SERVER IF EXISTS db2 CASCADE;
>    CREATE SERVER kea FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 
> 'db2', dbname 'db2');
>    CREATE USER MAPPING FOR USER SERVER db2 OPTIONS (user 'db2', password 
> 'password');
> 
>    CREATE SCHEMA IF NOT EXISTS db2;
> 
>    IMPORT FOREIGN SCHEMA public FROM SERVER db2 INTO db2;
> 
>    SELECT ok(
>      (SELECT COUNT(host_id) FROM db2.hosts) = 1,
>      'foobar'
>    );
> 
>    -- ;
> ROLLBACK;
> 
> Now, I would like to extract db2 initialization in separate file 
> "/test/init.sql" with this content:
> 
>    CREATE EXTENSION IF NOT EXISTS postgres_fdw;
>    DROP SERVER IF EXISTS db2 CASCADE;
>    CREATE SERVER kea FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 
> 'db2', dbname 'db2');
>    CREATE USER MAPPING FOR USER SERVER db2 OPTIONS (user 'db2', password 
> 'password');
> 
>    CREATE SCHEMA IF NOT EXISTS db2;
> 
>    IMPORT FOREIGN SCHEMA public FROM SERVER db2 INTO db2;
> 
> Now, my test file is:
> 
> BEGIN;
>    SELECT plan(1);
> 
>    \i /test/init.sql
> 
>    SELECT ok(
>      (SELECT COUNT(host_id) FROM db2.hosts) = 1,
>      'foobar'
>    );
> 
> In log I see that "init.sql" file is loaded with success:
> 
> Running tests: /test/*.sql -v
> /test/init.sql ................... No subtests run
> 
> But I have this error:
> 
> ERROR:  user mapping not found for "db2"
> 
> Question: where is my mistake? How can I include some sql file in my test?

It is early morning here and I may be missing something, but I do not 
see where you actually create server db2. I do see you creating server kea.

> 
> Best regards,
> Stéphane
> -- 
> Stéphane Klein <contact@stephane-klein.info 
> <mailto:contact@stephane-klein.info>>
> blog: http://stephane-klein.info
> cv : http://cv.stephane-klein.info
> Twitter: http://twitter.com/klein_stephane


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 03/02/2018 06:46 AM, Stéphane Klein wrote:


> 
> Sorry, it is not kea but db2 in my example. I did a mistake when I 
> replace the db name in my example.
> 
> You need to read:
> 
>    CREATE EXTENSION IF NOT EXISTS postgres_fdw;
>    DROP SERVER IF EXISTS db2 CASCADE;
>    CREATE SERVER db2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 
> 'db2', dbname 'db2');
>    CREATE USER MAPPING FOR USER SERVER db2 OPTIONS (user 'db2', password 
> 'password');
> 
>    CREATE SCHEMA IF NOT EXISTS db2;
> 
>    IMPORT FOREIGN SCHEMA public FROM SERVER db2 INTO db2;

I don't know much about pgTap, but my first instinct would be to change:

BEGIN;
   SELECT plan(1);

   \i /test/init.sql

   SELECT ok(
     (SELECT COUNT(host_id) FROM db2.hosts) = 1,
     'foobar'
   );

to:


BEGIN;

   \i /test/init.sql

   SELECT plan(1);

   SELECT ok(
     (SELECT COUNT(host_id) FROM db2.hosts) = 1,
     'foobar'
   );


-- 
Adrian Klaver
adrian.klaver@aklaver.com