1 / 57

Using pgTap to unit test your functions and queries

Using pgTap to unit test your functions and queries. By Lloyd Albin. pgTAP. pgTAP : Unit Testing for PostgreSQL.

gcarmen
Download Presentation

Using pgTap to unit test your functions and queries

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. Using pgTap to unit test your functions and queries By Lloyd Albin pgTAP

  2. pgTAP pgTAP

  3. pgTAP: Unit Testing for PostgreSQL • 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://pgtap.org/ • Good for: • Application Development • Schema Validation • xUnit-Style Testing • Module Development pgTAP

  4. pgTAP pgTAP

  5. Installing pg_tap • Installation will require you to download the pgTAP source code and then compile and install pgTAP against your installed version of Postgres. • It is also a good idea to install pg_prove. pgTAP

  6. Test Directory • In our test directory we want to create some folders specific to the tests we are going to run. • Create a common directory. • Create a find_raster directory (Name of the function we are going to write.) mkdir commonmkdirfind_raster pgTAP

  7. Common Diagnostics pgTAP

  8. Common Diagnostics • I like to create a common diagnostics that I run against all databases. • common/diagnostic.sql -- Turn off echo and keep things quiet. \set ECHO \set QUIET 1-- Format the output for nice TAP. \pset format unaligned \psettuples_only true \pset pager -- Revert all changes on failure. \set ON_ERROR_ROLLBACK 1\set ON_ERROR_STOP true \set QUIET 1-- Begin the transaction. BEGIN; SETsearch_pathTOpublic; pgTAP

  9. Set user to test as • If you are not running as the owner of the database, switch to be the owner of the database. This is use full if you are testing as user postgres, otherwise this might be skipped. -- Inline function to set the role for extension installation DO $BODY$ DECLAREdb_owner record; BEGINSELECTpg_user.usenameINTOdb_ownerFROMpg_databaseLEFTJOINpg_catalog.pg_userONpg_database.datdba=pg_user.usesysidWHEREdatname=current_database(); IFdb_owner.usename<>current_userTHENEXECUTE'SET ROLE '||db_owner.usename; SETsearch_pathTOpublic; ENDIF; END$BODY$ LANGUAGE plpgsql; pgTAP

  10. Install pgtap extension • We want to install the pgtap extension if it is not already installed. Because this is inside a transaction if the extension gets installed it will be automatically uninstalled at the end of the tests. • Write certain psql variables to a temp table to test against. -- Install the TAP functions if it is not already installed. CREATE EXTENSION IFNOTEXISTSpgtap; -- Set the role to the user you wish to run the tests as. CREATETEMPTABLE __pgtap_db_server__ (server text, username text, production_databasetext); INSERTINTO __pgtap_db_server__ (server, username, production_database) VALUES (:'HOST', :'test_user', :'test_production_database'); pgTAP

  11. pg_prove • /usr/local/apps/perl/perl-current/bin/pg_prove-h sqltest-alt -d sandbox --recurse -v --extsqlfind_raster DO $BODY$ DECLAREserver_name record; BEGINSELECT server, username, production_databaseINTOserver_nameFROM __pgtap_db_server__; IFserver_name.production_database=current_database() THEN-- If production database, run as the owner of the database PERFORM 'SET ROLE '||server_name.username; SETsearch_pathTOpublic; --SELECT diag('Running on a production database'); ELSE-- If not a production database, run as the executing user aka developer RESET ROLE; SETsearch_pathTOpublic; ENDIF; END$BODY$ LANGUAGE plpgsql; pgTAP

  12. Validation Information • We need to say how many tests we are going to perform. • The diag command lets us generate output that is not a test so that we can show current variables, database, database version, timestamp, etc that is required for validation. -- Plan the tests. SELECTplan(:plan+16); -- Configuration Data SELECTdiag('Configuration'); SELECTdiag('================================='); SELECTdiag('Test Name: '|| :'test_name'); SELECTdiag('Date: '||current_timestamp); SELECTdiag('Current Server: '|| :'HOST'); SELECTdiag('Current Database: '||current_database()); SELECTdiag('Current Port: '|| :'PORT'); SELECTdiag(''); SELECTdiag('Current Session User: '||session_user); SELECTdiag('Current User: '||current_user); SELECTdiag('pgTAP Version: '||pgtap_version()); SELECTdiag('pgTAP Postgres Version: '||pg_version()); SELECTdiag('Postgres Version: '||current_setting( 'server_version')); SELECTdiag('OS: '||os_name()); SELECTdiag(''); SELECTdiag('Common Tests'); SELECTdiag('================================='); pgTAP

  13. Checking extensions • Ok tests true/false • SELECT ok( :boolean, :description ); • Is test equals • SELECT is( :have, :want, :description ); SELECT ok((SELECTCASEWHENcurrent_setting( 'server_version_num') =pg_version_num()::textTHEN TRUE ELSE FALSE END), 'pgTAP is compiled against the correct Postgres Version'); SELECTis(         (SELECTextnameFROMpg_catalog.pg_extensionWHEREextname='plpgsql')     , 'plpgsql', 'Verifying extension plpgsql is installed'); SELECTis(         (SELECTextnameFROMpg_catalog.pg_extensionWHEREextname='plperl')     , 'plperl', 'Verifying extension plperl is installed'); SELECTis(         (SELECTextnameFROMpg_catalog.pg_extensionWHEREextname='pgtap')     , 'pgtap', 'Verifying extension pgtap is installed'); pgTAP

  14. Testing Optional Extensions • If we are in the postgres database we want to check for two more extensions otherwise we want to skip testing for those extensions. • collect_tap performs multiple tests at once. • SELECT collect_tap(:lines); • Skip allows you to skip tests that you have allotted with plan(x). • SELECT skip( :why, :how_many ); SELECTCASEWHENcurrent_database() ='postgres'THENcollect_tap( is(                 (SELECTextnameFROMpg_catalog.pg_extensionWHEREextname='adminpack')             , 'adminpack', 'Verifying extension adminpack is installed'), is(                 (SELECTextnameFROMpg_catalog.pg_extensionWHEREextname='pg_buffercache')             , 'pg_buffercache', 'Verifying extension pgbuffercache is installed'), is(                 (SELECTcount(*)::intFROMpg_catalog.pg_extension)             , 5, 'Verifying only 5 extensions are installed')     ) ELSEcollect_tap(         skip('Skipping extenstion test for adminpack', 1),         skip('Skipping extenstion test for pg_buffercache', 1), is(                 (SELECTcount(*)::intFROMpg_catalog.pg_extension)             , 3, 'Verifying only 3 extensions are installed')     ) END; pgTAP

  15. Testing for Extra Extensions • This will list all the extra extensions installed. SELECTdiag('Extra Extension Installed: '||extname) FROMpg_catalog.pg_extensionWHERE ( current_database() ='postgres'ANDextname!='plpgsql'ANDextname!='plperl'ANDextname!='pgtap'ANDextname!='adminpack'ANDextname!='pg_buffercache') OR ( current_database() <>'postgres'ANDextname!='plpgsql'ANDextname!='plperl'ANDextname!='pgtap'); pgTAP

  16. Testing Languages installed • We are going to test that we have the correct languages installed and make sure that we do not have the un-secure perl installed. • has_language checks for the installed langauge. • SELECT has_language( :language ); • hasnt_language check to make sure it is not installed. • SELECT hasnt_language( :language ); SELECThas_language( 'c' ); SELECThas_language( 'internal' ); SELECThas_language( 'sql' ); SELECThas_language( 'plpgsql' ); SELECThas_language( 'plperl' ); SELECThasnt_language( 'plperlu' ); pgTAP

  17. Testing for Extra Languages • This test will fail if we have extra languages installed and then list the extra languages. SELECTis(         (SELECTcount(*)::intFROMpg_catalog.pg_language)     , 5, 'Verifying no extra languages are installed'); SELECTdiag('Extra Languages Installed: '||lanname) FROMpg_catalog.pg_languageWHERElanname!='c'ANDlanname!='internal'ANDlanname!='sql'ANDlanname!='plpgsql'ANDlanname!='plperl'; pgTAP

  18. Testing for perl bug. • We once ran into a bug with perl when upgrading. This tests that multiplicity is defined within the perl otherwise perl will not work properly with Postgres. • lives_ok tests a prepared statement or sql and makes sure that it does not error out. • SELECT lives_ok( :sql, :description ); SET ROLE postgres; SETsearch_pathTOpublic; CREATEFUNCTIONpublic.perl_test () RETURNSintegerAS$body$ return1; $body$ LANGUAGE 'plperl'VOLATILE CALLED ONNULL INPUT SECURITY INVOKER; GRANTEXECUTEONFUNCTIONpublic.perl_test() TOPUBLIC; PREPAREdba_perl_testASSELECT*FROMpublic.perl_test(); SELECTlives_ok('dba_perl_test','Testingplperl has multiplicity defined - Test 1'); SET ROLE dba; SETsearch_pathTOpublic; SELECTlives_ok('dba_perl_test','Testingplperl has multiplicity defined - Test 2'); RESET ROLE; pgTAP

  19. Change back to our test user and start the tests • Here we set the role back to our testing user and get ready to start testing. DO $BODY$ DECLAREserver_name record; BEGINSELECT server, username, production_databaseINTOserver_nameFROM __pgtap_db_server__; IFserver_name.production_database=current_database() THEN-- If production database, run as the owner of the database PERFORM 'SET ROLE '||server_name.username; --SELECT diag('Running on a production database'); ELSE-- If not a production database, run as the executing user aka developer RESET ROLE; ENDIF; END$BODY$ LANGUAGE plpgsql; SELECTdiag('Tests'); SELECTdiag('================================='); pgTAP

  20. Running the basic tests pgTAP

  21. pg_prove • Inside our find_raster directory we want to create a 01_find_raster.sql file. • This file right now is only going to test our common diagnostics file. -- Setup Test Variables \settest_name'find_raster'\settest_user'lalbin'\settest_production_database'sandbox'\setplan0-- Install pgTAP, show diagnostics, and start common tests \ir ../common/diagnostic.sql pgTAP

  22. pg_prove • pg_prove • -h host • -d database • --recurse Follow the directories recursively • -v Verbose mode, shows you each test • --extsql The extension of the files you will be using. • find_raster The name of the directory to start with. /usr/local/apps/perl/perl-current/bin/pg_prove-h sqltest-alt -d sandbox --recurse-v --extsqlfind_raster pgTAP

  23. Running our test • Here is the general information for validation. 1..16# Configuration# =================================# Test Name: find_raster# Date: 2017-01-03 15:26:37.274103-08# Current Server: sqltest-alt# Current Database: sandbox# Current Port: 5432## Current Session User: postgres# Current User: postgres# pgTAP Version: 0.97# pgTAP Postgres Version: 9.4.9# Postgres Version: 9.4.9# OS: linux# pgTAP

  24. Running our test • Here are the results of our general testing. # Common Tests# =================================ok 1 - pgTAP is compiled against the correct Postgres Versionok 2 - Verifying extension plpgsql is installedok 3 - Verifying extension plperl is installedok 4 - Verifying extension pgtap is installedok 5 # SKIP Skipping extenstion test for adminpackok 6 # SKIP Skipping extenstion test for pg_buffercacheok 7 - Verifying only 3 extensions are installedok 8 - Procedural language c should existok 9 - Procedural language internal should existok 10 - Procedural language sql should existok 11 - Procedural language plpgsql should existok 12 - Procedural language plperl should existok 13 - Procedural language plperlu should not existok 14 - Verifying no extra languages are installedok 15 - Testing plperl has multiplicity defined - Test 1ok 16 - Testing plperl has multiplicity defined - Test 2 pgTAP

  25. Running our test • Now we started our find_raster testing, for which we have not yet written any tests. • So everything passed ok so far. • Now we are ready to starting writing out tests and function. # Tests# =================================okAll tests successful.Files=1, Tests=16, 0 wallclock secs ( 0.02 usr 0.02 sys + 0.00 cusr 0.02 csys = 0.06 CPU)Result: PASS pgTAP

  26. find_raster pgTAP

  27. The Problem We receive faxes that are multi-page TIFF’s. The TIFF file name are called the raster id. We have data where we have the full path of the file name, the raster id and the raster id with page number. Examples: • 0000/000000 • 0000/0000001111 • /studydata/studyname/0000/000000 pgTAP

  28. Writing our tests • We now change the plan to 3 tests. • We write our basic three tests. • Now we are ready to write our function. \setplan3-- Install pgTAP, show diagnostics, and start common tests \ir ../common/diagnostic.sqlSELECTis((SELECTfind_raster('1234/567890')), '1234/567890', 'Testing find_raster using Raster ID'); SELECTis((SELECTfind_raster('1234/5678901234')), '1234/567890', 'Testing find_raster using Raster ID with Page Number'); SELECTis((SELECTfind_raster('/study_data/study_name/1234/567890')), '1234/567890', 'Testing find_raster using Directory path with File Name'); pgTAP

  29. find_raster function The first thing to do, is to be able to find the Raster ID, no matter which format is supplied. IMMUTABLE is required to be able to use this function as part of an index. This function was written to fit on this page. Some of the other items to put inside the real function would be: • We should check the return to make sure a ‘/’ is in the correct spot and through an error if it is not. “R” is also valid. • If the raster is less then 23 characters to return an error, except for when it is 11 or 15 in length. CREATEFUNCTIONfind_raster (raster varchar) RETURNSVARCHAR(11) AS$$ BEGINCASE length(raster) WHEN11THEN-- Format: 1234/567890-- Returns: 1234/567890 RETURN raster; WHEN15THEN-- Format: 1234/5678901234-- Returns: 1234/567890 RETURNsubstr(raster, 1, 11); ELSE-- Format: /study_data/study_name/1234/567890-- Returns: 1234/567890 RETURNsubstr(raster, length(raster)-10, 11); ENDCASE; END; $$ LANGUAGE plpgsqlIMMUTABLE RETURNSNULLONNULL INPUT; pgTAP

  30. Testing our new function The first thing to do, is to be able to find the Raster ID, no matter which format is supplied. IMMUTABLE is required to be able to use this function as part of an index. This function was written to fit on this page. Some of the other items to put inside the real function would be: • We should check the return to make sure a ‘/’ is in the correct spot and through an error if it is not. “R” is also valid. • If the raster is less then 23 characters to return an error, except for when it is 11 or 15 in length. # Tests # ================================= ok 17 - Testing find_raster using Raster ID ok 18 - Testing find_raster using Raster ID with Page Number ok 19 - Testing find_raster using Directory path with File Name ok All tests successful. Files=1, Tests=19, 0 wallclock secs ( 0.05 usr 0.01 sys + 0.00 cusr 0.02 csys = 0.08 CPU) Result: PASS pgTAP

  31. Now we run across bad data Lets say the ‘/’ ended up being an ‘X’ so we need to make the function fail and test for that failure. But ‘R’ is also a valid character to we need to make sure the function does not break with an ‘R’. So we now need to update our tests and code. • Update existing test comments to say ‘/’ and add test for ‘R’ and ‘X’. • throws_ok allows us to test for a specific error message. • SELECT throws_ok( :sql, :errmsg, :description ); \set plan 9 …SELECTis((SELECTfind_raster('/study_data/study_name/1234R567890')), '1234R567890', 'Testing R find_raster using Directory path with File Name'); SELECTthrows_ok(E'SELECTfind_raster(''1234X567890'')', 'Invalid Character "X"', 'Testing X find_raster using Raster ID'); SELECTthrows_ok(E'SELECTfind_raster(''1234X5678901234'')', 'Invalid Character "X"', 'Testing X find_raster using Raster ID with Page Number'); SELECTthrows_ok(E'SELECTfind_raster(''/study_data/study_name/1234X567890'')', 'Invalid Character "X"', 'Testing X find_raster using Directory path with File Name'); pgTAP

  32. Now if we test before updating the code If we run the tests before we update the code, we will see how the find_raster function does not fail for the X condition. So now we need to fix the code and test again. ok 17 - Testing / find_raster using Raster ID ok 18 - Testing / find_raster using Raster ID with Page Number ok 19 - Testing / find_raster using Directory path with File Name ok 20 - Testing R find_raster using Raster ID ok 21 - Testing R find_raster using Raster ID with Page Number ok 22 - Testing R find_raster using Directory path with File Name not ok 23 - Testing X find_raster using Raster ID # Failed test 23: "Testing X find_raster using Raster ID" # caught: no exception # wanted: an exception not ok 24 - Testing X find_raster using Raster ID with Page Number # Failed test 24: "Testing X find_raster using Raster ID with Page Number" # caught: no exception # wanted: an exception not ok 25 - Testing X find_raster using Directory path with File Name # Failed test 25: "Testing X find_raster using Directory path with File Name" # caught: no exception # wanted: an exception Failed 3/25 subtests         (less 2 skipped subtests: 20 okay) Test Summary Report ------------------- find_raster/01_find_raster.sql (Wstat: 0 Tests: 25 Failed: 3)   Failed tests: 23-25 Files=1, Tests=25, 1 wallclock secs ( 0.06 usr 0.01 sys + 0.00 cusr 0.02 csys = 0.09 CPU) Result: FAIL pgTAP

  33. Update the function We now test for ‘/’ and ‘R’ and for any other character we raise an exception. Ready to test again. DECLAREraster_idVARCHAR; BEGINCASElength(raster) WHEN11THENraster_id:= raster; WHEN15THENraster_id:=substr(raster, 1, 11); ELSEraster_id:=substr(raster, length(raster)-10, 11); ENDCASE; CASEsubstr(raster_id, 5, 1) WHEN'/'THENWHEN'R'THENELSE      RAISE EXCEPTION 'Invalid Character "%"', substr(raster_id, 5, 1); ENDCASE; RETURNraster_id; END; pgTAP

  34. Testing with the fixed function. Now it passes all the test. Finding the good raster id’s and failing for the bad raster id’s. ok 17 - Testing / find_raster using Raster ID ok 18 - Testing / find_raster using Raster ID with Page Number ok 19 - Testing / find_raster using Directory path with File Name ok 20 - Testing R find_raster using Raster ID ok 21 - Testing R find_raster using Raster ID with Page Number ok 22 - Testing R find_raster using Directory path with File Name ok 23 - Testing X find_raster using Raster ID ok 24 - Testing X find_raster using Raster ID with Page Number ok 25 - Testing X find_raster using Directory path with File Name ok All tests successful. Files=1, Tests=25, 0 wallclock secs ( 0.03 usr 0.01 sys + 0.00 cusr 0.01 csys = 0.05 CPU) Result: PASS pgTAP

  35. Now lets test for bad lengths If the raster is less then 23 characters to return an error, except for when it is 11 or 15 in length. SELECTthrows_ok(E'SELECT find_raster(''1234X5678'')', 'Invalid Raster Length: 9', 'Testing find_raster using bad Raster ID'); SELECTthrows_ok(E'SELECT find_raster(''1234X56789012'')', 'Invalid Raster Length: 13', 'Testing find_raster using bad Raster ID with Page Number'); SELECTthrows_ok(E'SELECT find_raster(''/a/z/1234X567890'')', 'Invalid Raster Length: 16', 'Testing find_raster using bad Directory path with File Name'); pgTAP

  36. Update the function, again If the raster is less then 23 characters to return an error, except for when it is 11 or 15 in length. BEGINCASEWHEN length(raster) =11THENWHEN length(raster) =15THENWHEN length(raster) <23THEN      RAISE EXCEPTION 'Invalid Raster Length: %', length(raster); ELSEENDCASE; pgTAP

  37. Testing with the fixed function. Now it passes all the test. Finding the good raster id’s and failing for the bad raster id’s. ok 17 - Testing / find_raster using Raster ID ok 18 - Testing / find_raster using Raster ID with Page Number ok 19 - Testing / find_raster using Directory path with File Name ok 20 - Testing R find_raster using Raster ID ok 21 - Testing R find_raster using Raster ID with Page Number ok 22 - Testing R find_raster using Directory path with File Name ok 23 - Testing X find_raster using Raster ID ok 24 - Testing X find_raster using Raster ID with Page Number ok 25 - Testing X find_raster using Directory path with File Name ok 26 - Testing find_raster using bad Raster ID ok 27 - Testing find_raster using bad Raster ID with Page Number ok 28 - Testing find_raster using bad Directory path with File Name ok All tests successful. Files=1, Tests=28, 0 wallclock secs ( 0.05 usr 0.03 sys + 0.01 cusr 0.02 csys = 0.11 CPU) Result: PASS pgTAP

  38. pgTAP and testing your queries pgTAP

  39. Lets setup some tables • Here we are setting up some tables to test with. CREATETABLEpublic.table_a (   id SERIAL,   name VARCHAR, PRIMARYKEY(id) ) WITH (oids= false); CREATETABLEpublic.table_b (   id SERIAL, a_idINTEGER,   status VARCHAR, action_dtTIMESTAMP WITHOUT TIME ZONE, PRIMARYKEY(id) ) WITH (oids= false); pgTAP

  40. Lets setup a poor view • We are wanting the employee’s current status for all currently employed employees. CREATEORREPLACEVIEWpublic.view_aASSELECT a.id,     a.name, b.status, b.action_dtFROMtable_a a LEFTJOIN ( SELECT table_b.id, table_b.a_id, table_b.status, table_b.action_dtFROMtable_bLEFTJOIN ( SELECTmax(table_b_1.id) AS id,             table_b_1.a_id FROMtable_b table_b_1 GROUPBY table_b_1.a_id) c USING (id, a_id) WHERE c.id ISNOTNULL) b ON a.id =b.a_idWHEREb.status!='Left' ORDER BY a.id; pgTAP

  41. Creating the initial tests. pgTAP

  42. Truncate the tables (17) • Make a directory called employee_test and create a file called 01_employee_test.sql • First we want to truncate all our tables. While this could have been just a normal SQL command, we can wrap it inside a lives_ok to verify that it worked without any errors. -- Setup Test Variables \settest_name'employee_status'\settest_user'lalbin'\settest_production_database'sandbox'\setplan4-- Install pgTAP, show diagnostics, and start common tests \ir ../common/diagnostic.sql--\i common/diagnostic.sqlSELECTlives_ok('TRUNCATE table_a, table_b', 'Truncating tables in preperation for testing'); pgTAP

  43. Insert the test data (18 & 19) • Next we need to insert our test records. SELECTlives_ok(E'INSERT INTO table_a VALUES (1, ''Lloyd''), (2, ''Judy''), (3, ''Gerald'')', 'Inserting Test Data into table_a'); SELECTlives_ok(E'INSERT INTO table_b VALUES (1, 1, ''Hired'', ''1/1/14''), (2, 2, ''Hired'', ''1/1/15''), (3, 3, ''Hired'', ''1/1/16''), (4, 2, ''Maternity'', ''4/5/16''), (5, 2, ''Normal'', ''5/5/16''), (6, 3, ''Left'', ''10/15/16'')', 'Inserting Test Data into table_b'); pgTAP

  44. Testing the data (20) • Use results_eq to test the output of the view. By default the values will have a field type of “unknown” and that is why we need to cast the variables in the first row to match the query output. • SELECT results_eq( :sql, :sql, :description ); SELECTresults_eq( 'SELECT id, name, status, action_dt FROM view_a;',     $$VALUES        (1,'Lloyd'::varchar,'Hired'::varchar,'2014-01-01'::timestamp),         (2,'Judy','Normal','2016-05-05')$$, 'Verify Data in view_a'); pgTAP

  45. Run the Initial Test • We get the results we expect. # Tests # ================================= ok 17 - Truncating tables in preperation for testing ok 18 - Inserting Test Data into table_aok 19 - Inserting Test Data into table_bok 20 - Verify Data in view_aok All tests successful. Files=1, Tests=20, 0 wallclock secs ( 0.06 usr 0.01 sys + 0.00 cusr 0.04 csys = 0.11 CPU) Result: PASS pgTAP

  46. Creating a failure test (21 & 22) • Now we need to write a new test to cause the bug that we wish to test. This is caused by entering the data out of order. SELECTlives_ok(E'INSERT INTO table_b VALUES (7, 2, ''Normal'', ''10/1/16''), (8, 2, ''Family Leave'', ''9/1/16'')', 'Inserting 2nd Test Data into table_b'); SELECTresults_eq( 'SELECT id, name, status, action_dt FROM view_a;',     $$VALUES        (1,'Lloyd'::varchar,'Hired'::varchar,'2014-01-01'::timestamp),         (2,'Judy','Normal','2016-10-01')$$, 'Verify Data in view_a'); pgTAP

  47. Run the Failure Test • Here we see where the view failed and the bad line vers the good line. # Tests # ================================= ok 17 - Truncating tables in preperation for testing ok 18 - Inserting Test Data into table_aok 19 - Inserting Test Data into table_bok 20 - Verify Data in view_aok 21 - Inserting 2nd Test Data into table_bnot ok 22 - Verify Data in view_a# Failed test 22: "Verify Data in view_a" # Results differ beginning at row 2: # have: (2,Judy,"Family Leave","2016-09-01 00:00:00") # want: (2,Judy,Normal,"2016-10-01 00:00:00") Failed 1/22 subtests         (less 2 skipped subtests: 19 okay) Test Summary Report ------------------- employee_test/01_employee_test.sql (Wstat: 0 Tests: 22 Failed: 1)   Failed test: 22 Files=1, Tests=22, 1 wallclock secs ( 0.06 usr 0.01 sys + 0.01 cusr 0.02 csys = 0.10 CPU) Result: FAIL pgTAP

  48. Refactoring the Query • Re-factoring the query to have the correct status and at the same time switching to a higher performing query, as long as you create the index needed by the ORDER BY statement. • After some use, you notice that this query starts slowing down and that sometimes when records are entered out of order that the status is wrong. • So now we need to re-factor this query, but we also need to validate the output of the query to make sure we do not write a new bad query. CREATEVIEWpublic.view_bASSELECT a.id,     a.name, b.status, b.action_dtFROMtable_a a LEFTJOIN ( SELECTDISTINCTON (table_b.a_id) table_b.id, table_b.a_id, table_b.status, table_b.action_dtFROMtable_bORDERBYtable_b.a_id, table_b.action_dtDESC    ) b ON a.id =b.a_idWHEREb.status!='Left'ORDERBY a.id; pgTAP

  49. Replace test (22) • Copy test 20 and update sql from using view_a to using view_b. • This will make sure with the basic data that we are still getting the same result with our re-factored view. SELECTresults_eq( 'SELECT id, name, status, action_dt FROM view_b;',     $$VALUES        (1,'Lloyd'::varchar,'Hired'::varchar,'2014-01-01'::timestamp),         (2,'Judy','Normal','2016-05-05')$$, 'Verify Data in view_b'); pgTAP

  50. Adding new test (23 & 24) • First we need to copy the second test for view_a and make it use view_b. • The we need to alter the second test for view_a to be a not equal test by using the results_ne command. • SELECT results_ne( :sql, :sql, :description ); • This will make sure with the more advanced test failed properly with view_a and works properly with view_b. SELECTresults_ne( 'SELECT id, name, status, action_dt FROM view_a;',     $$VALUES        (1,'Lloyd'::varchar,'Hired'::varchar,'2014-01-01'::timestamp),         (2,'Judy','Normal','2016-10-01')$$, 'Verify Bad Data in view_a (2nd run)'); SELECTresults_eq( 'SELECT id, name, status, action_dt FROM view_b;',     $$VALUES        (1,'Lloyd'::varchar,'Hired'::varchar,'2014-01-01'::timestamp),         (2,'Judy','Normal','2016-10-01')$$, 'Verify Good Data in view_b (2nd run)'); pgTAP

More Related