1 / 23

Unit Testing Postgres with pgTAP

Unit Testing Postgres with pgTAP. By: Lloyd Albin 10/1/2013. pgTAP.

mrinal
Download Presentation

Unit Testing Postgres with pgTAP

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Unit Testing Postgreswith pgTAP By: Lloyd Albin 10/1/2013

  2. pgTAP pgTAP is a suite of database functions that make it easy to write TAP-emitting unit tests in psql scripts or xUnit-style test functions. The TAP output is suitable for harvesting, analysis, and reporting by a TAP harness, such as those used in Perl applications. http://www.pgtap.org/

  3. Installing the extension The extension may be installed by the database owner or a Postgressuperuser aka DBA. The extensions may also be installed via the TAP script, so that it’s functions are not left as part of the database. CREATE EXTENSION pgtap;

  4. A simple tap script All TAP scripts must be run inside of a transaction and then get rolled back at the end so that any changes you may have made to the data get reversed, except for SERIAL sequences that may have been increased. -- Load the TAP functions. BEGIN; CREATE EXTENSION pgtap; -- Plan the tests. SELECT plan(1); --SELECT no_plan(); -- Run the tests SELECT pass('Passed one test'); -- Finish the tests and clean up. SELECT * FROM finish(); ROLLBACK;

  5. psql We will create a basic table with just a primary key. We don’t need any other fields for this example. % psql -h sqltest -d postgres -Xftest.sql (With Plan) 1..1 ok 1 - Passed one test (Without Plan) ok 1 - Passed one test 1..1

  6. pg_prove – single file or directory pg_prove is a Perl script that can be used to wrap around TAP scripts. You may also give a directory/*.sql to use every sql file in alphabetical order within the specified directory. Failed commands will show in red within pg_prove. % /usr/local/apps/perl/perl-current/bin/pg_prove -h sqltest -d postgres -v test.sql test.sql .. 1..1 ok 1 - Passed one test ok All tests successful. Files=1, Tests=1, 0 wallclocksecs ( 0.02 usr + 0.01 sys = 0.03 CPU) Result: PASS

  7. pg_prove - recursive Using the --recurse option pg_prove will look in directory and all sub directories for .pg files even if you specify *.sql. To fix this use the --extsql to change the default extension to the sql extension. I also prefer this so that editors like Eclipse will color code the sql files. % /usr/local/apps/perl/perl-current/bin/pg_prove -h sqltest -d postgres --recurse -v --extsql directory directory/testing/test.sql.. 1..1 ok 1 - Passed one test ok All tests successful. Files=1, Tests=1, 0 wallclocksecs ( 0.04 usr + 0.01 sys = 0.05 CPU) Result: PASS

  8. Setting Role for xapps owned databases – part 1 If xapps or df_mirror owns the database then, the extension needs to be installed by that user. But also many of the developer databases are owned by the individual developer. This inline function checks to see if the current user is the owner and if not tries to do a SET ROLE to the owner of the database. -- Load the TAP functions. BEGIN; -- Inline function to set the role for extension installation DO $BODY$ DECLARE db_owner record; BEGIN SELECT pg_user.usename INTO db_owner FROM pg_database LEFT JOIN pg_catalog.pg_user ON pg_database.datdba = pg_user.usesysid WHERE datname = current_database(); IF db_owner.usename <> current_user THEN EXECUTE 'SET ROLE ' || db_owner.usename; END IF; END $BODY$ LANGUAGE plpgsql;

  9. Setting Role for xapps owned databases – part 2 Now you will be able to install the pgTAP extension. Once that is done, you need to set the user to run the tests as. As long as you are a member of the role/group you with to SET ROLE, you may do this. -- Install the Extension CREATE EXTENSION pgtap; SET ROLE xapps; -- Run Tests

  10. Setting Role for non-xapps owned databases – part 1 The problem is for db.main, sqltest.main, atlassql.cpas, etc. Your devel copies will be owned by the developer and so we only need to do the SET ROLE for production servers. This means that these scripts will only be able to be run by a DBA on production databases. -- Load the TAP functions. BEGIN; -- Inline function to set the role for extension installation DO $BODY$ BEGIN IF current_database() = 'main' THEN SET ROLE dba; END IF; END $BODY$ LANGUAGE plpgsql; -- Install the Extension CREATE EXTENSION pgtap;

  11. Setting Role for non-xapps owned databases – part 2 For databases such as main, the developer owns their own copy. With this inline function, if the database is a production version, then we want to SET ROLE as the application otherwise continue to run as the database owner. You may wish to add more logic for staging and testing databases. … CREATE EXTENSION pgtap; DO $BODY$ BEGIN IF current_database() = 'main' THEN SET ROLE xapps; END IF; END $BODY$ LANGUAGE plpgsql;

  12. Setting Role for non-xapps owned databases – part 3 If you need to write even more complex logic using the host name, you must set the host name into a temporary table because the :’HOST’ variable is not accessible within inline functions. … CREATE EXTENSION pgtap; CREATE TEMP TABLE db_server(server text); INSERT INTO db_server (server) VALUES (:'HOST'); DO $BODY$ DECLARE server_name record; BEGIN SELECT server INTO server_name FROM db_server; IF server_name.server = 'sqltest' THEN SET ROLE xapps; END IF; END $BODY$ LANGUAGE plpgsql;

  13. Configuration Data Here is a sample set of configuration data that could be output at the start of a TAP script. This information could be useful to us/Quality to know which server/database the tests were run against. -- Configuration Data SELECT diag('Configuration'); SELECT diag('==========================='); SELECT diag('Postgres Version: ' || current_setting( 'server_version')); SELECT diag('pgTAP Version: ' || pgtap_version()); SELECT diag('pgTAPPostgres Version: ' || pg_version()); SELECT diag('Current Server: ' || :'HOST'); SELECT diag('Current Database: ' || current_database()); SELECT diag('Current Session User: ' || session_user); SELECT diag('Current User: ' || current_user); SELECT diag(''); SELECT diag('Tests'); SELECT diag('===========================');

  14. Configuration Data - Output Here is the output of the configuration data code. It lets us know the Postgres version that we are executing against and the version of pgTAP. The pgTAPPostgres Version is also important as that is the version of Postgres that pgTAP was compiled against. This should always be the same as the Postgres version but could be different. # Configuration # =============================== # Postgres Version: 9.2.4 # pgTAP Version: 0.93 # pgTAPPostgres Version: 9.2.4 # Current Server: sqltest # Current Database: postgres # Current Session User: postgres # Current User: dba # # Tests # ===============================

  15. Testing to make sure the correct Postgres Version This allows us to have a test to check the Postgres Version against the Postgres Version that pgTAP was compiled against. SELECT ok((SELECT CASE WHEN current_setting( 'server_version_num') = pg_version_num()::text THEN TRUE ELSE FALSE END), 'pgTAP is compiled against the correct Postgres Version');

  16. What happens when you don’t update plan Even though all your tests passed, you will still over all have a failure if you did not increase your plan to 2, pg_prove will show the extra line(s) in red and then complain about the number of test run vers the number of tests planed. ok 1 - Passed one test ok 2 - pgTAP is compiled against the correct PostgresVersion # Looks like you planned 1 test but ran 2 All 1 subtests passed Test Summary Report ------------------- test.sql (Wstat: 0 Tests: 2 Failed: 1) Failed test: 2 Parse errors: Bad plan. You planned 1 tests but ran 2.

  17. Testing for extensions If your application requires an extension installed, you can test to make sure that the extension is installed. Here are some examples for the template_restore database. In this example I am also checking to make sure that no extra extensions are installed. Remember the pgTAP extension will be automatically uninstalled at the end of the testing. SELECT is( (SELECT extname FROM pg_catalog.pg_extension WHERE extname = 'plpgsql') , 'plpgsql', 'Verifying extension plpgsql is installed'); SELECT is( (SELECT extname FROM pg_catalog.pg_extension WHERE extname = 'plperl') , 'plperl', 'Verifying extension plperl is installed'); SELECT is( (SELECT extname FROM pg_catalog.pg_extension WHERE extname = 'pgtap') , 'pgtap', 'Verifying extension pgtap is installed'); SELECT is( (SELECT count(*)::int FROM pg_catalog.pg_extension) , 3, 'Verifying no extra extensions are installed');

  18. Testing for languages The test to make sure that plperlu is not installed is redundant because we are testing the number of languages installed. But when testing the number of languages, it does not tell you the extra languages. SELECT has_language( 'c' ); SELECT has_language( 'internal' ); SELECT has_language( 'sql' ); SELECT has_language( 'plpgsql' ); SELECT has_language( 'plperl' ); SELECT hasnt_language( 'plperlu' ); SELECT is( (SELECT count(*)::int FROM pg_catalog.pg_language) , 5, 'Verifying no extra languages are installed');

  19. Many possible tests This shows testing database ownership, table exists, table structure, etc. In some cases you may want to skip over some number of tests. In this case user xapps does not exist on the atlas servers. collect_tap may be used to bundle more than one tap command together. SELECT db_owner_is( current_database(), 'postgres' ); SELECT has_table( 'dbreview' ); SELECT has_pk( 'dbreview' ); SELECT has_column('dbreview', 'datname', 'Verifying that table has field datname'); SELECT col_is_pk('dbreview', 'datname', 'Verifying that field datname is the primary key'); SELECT col_type_is('dbreview', 'datname', 'name', 'Verifying that field datname is of type NAME'); SELECT CASE WHEN :'HOST' = 'atlassql' THEN skip('Skipping xapps tests', 2) WHEN :'HOST' = 'atlassql-test' THEN skip('Skipping xapps tests', 2) ELSE collect_tap( has_user('xapps'), table_privs_are ('dbreview', 'xapps', ARRAY['SELECT'], 'Verifying xapps has SELECT privilages on dbreview') ) END; SELECT has_function('update_dboid');

  20. Testing Functions / Prepared Queries You may test queries / functions as prepared queries. You may check to see if it returns ok or if it performs within a specified amount of time. PREPARE test_delete AS DELETE FROM dbreview WHERE datname = 'postgres'; SELECT lives_ok('test_delete', 'Testing manual DELETE from dbreview'); PREPARE fast_query AS SELECT update_dboid(); SELECT performs_ok('fast_query', 25, 'Making sure update_dboid() runs in under 25ms');

  21. Testing single value query or function. is and isnt can use used to test single values. These values may be number, boolean, string, etc. SELECT is( (SELECT count(*)::int FROM pg_catalog.pg_language) , 5, 'Verifying no extra languages are installed'); SELECT is( (SELECT dumped FROM dbreview WHERE datname = 'postgres') , TRUE, 'Verifying is dumped for inserted database postgres'); SELECT isnt( (SELECT comments FROM dbreview WHERE datname = 'postgres') , 'None', 'Verifying inserted comment for database postgres');

  22. Testing multiple row/columns results results_eq allows you to test one query against a second query or against a set of static values. Using the array, each value is a row of data. Using the VALUES, each (x, y, …) is one row of data and can contain multiple columns of data. -- compare two queries with dynamic results SELECT results_eq( 'SELECT oid, datname, comments FROM dbreview WHERE deleted IS FALSE ORDER BY oid', 'SELECT a.oid, a.datname, b.description AS comments FROM pg_catalog.pg_database a LEFT JOIN pg_catalog.pg_shdescription b ON a.oid = b.objoid ORDER BY a.oid', 'Verifying that all current databases are listed in dbreview'); -- Comparing single column query to static results SELECT results_eq( 'SELECT datnameFROM dbreview WHERE deleted IS FALSE ORDER BY datname', ARRAY['clinical_grade', 'df_mirror', 'df_repository', …], 'Verifying that all current databases are listed in dbreview'); -- Comparing multi column query to static results SELECT results_eq( 'SELECT oid, datnameFROM dbreview WHERE deleted IS FALSE ORDER BY oid', $$VALUES (1, 'template1'), (11866, 'template0'), … $$, 'Verifying that all current databases are listed in dbreview');

  23. Demo Time Show demo of tests that we have written.

More Related