tests unitaires pour postgresql avec pgtap

pgTap, tests unitaires pour PostgreSQL

Rodolphe Quiédeville

Meetup PostgreSQL Nantes

22 juin 2016

#mylife

I Découvert Internet à 28.kbits avec Netscape NavigatorI Utilise et produit du logiciel libre exclusivementI PostgreSQL depuis ... la 6.X ?I Consultant en performance des SI(G)I Data architect @PeopleDocI Formateur Upstream University

Intro

I suite de fonctions pour faciliter l’écriture de tests auprotocole TAP

I écrit en Perl et PL/pgSQLI PostgreSQL 8.4I David E. WheelerI BSD likeI http://pgtap.org/

Tap

TAP, test anything protocolInitialement écrit pour Perl (1987), avec des implémentationsaujourd’hui en C, C++, Python, PHP, Java, ....

1..4ok 1 - Input file openednot ok 2 - First line of the input validok 3 - Read the rest of the filenot ok 4 - Summarized correctly # TODO Not written yet

Installation

# apt-get install pgtap# apt-get install postgresql-9.1-pgtap

~# CREATE EXTENSION pgtap;

Installation

I 873 fonctionsI 2 vuesI 1 type composite

Fonctions

Test basique

SELECT ok ( 9 ^ 2 = 81 , ’ s imple exponent ia l ’ ) ;

Fonctions

Résultat de fonction

SELECT i s ( u l t imate_answer ( ) , 42 , ’ Meaning o f L i f e ’ ) ;

Fonctions

Résultat de requête

SELECT resu l t s_eq (’SELECT ∗ FROM act ive_users ( ) ’ ,’SELECT ∗ FROM users WHERE a c t i v e ’ ,’ ac t i ve_users ( ) should r e t u r n a c t i v e users ’) ;

Fonctions

Test de schéma

SELECT has_table ( ’myschema ’ : : name, ’ sometable ’ : : name) ;

Fonctions

Test de schéma

SELECT has_tablespace ( ’ sometablespace ’ , ’ / data / dbs ’ ) ;

Tests de schéma

I has_table()I has_column()I has_relation()I has_type()I has_index()I has_composite()I has_trigger()I has_view()I has_fk()

Tests de schéma

I hasnt_table()I hasnt_column()I hasnt_relation()I hasnt_type()I hasnt_index()I hasnt_composite()I hasnt_trigger()I hasnt_view()I hasnt_fk()

Tests de schéma

I col_default_is()I col_is_fk()I col_is_null()I col_is_pk()I col_is_unique()I col_type_is()

Tests de schémas

I col_default_is()I col_is_fk()I col_is_null()I col_is_pk()I col_is_unique()I col_type_is()

873 fonctions

Paramètres

Paramètres des fonctions

SELECT has_funct ion ( schema , func t i on , args , d e s c r i p t i o n ) ;SELECT has_funct ion ( schema , func t i on , args ) ;SELECT has_funct ion ( schema , func t i on , d e s c r i p t i o n ) ;SELECT has_funct ion ( schema , f u n c t i o n ) ;SELECT has_funct ion ( func t i on , args , d e s c r i p t i o n ) ;SELECT has_funct ion ( func t i on , args ) ;SELECT has_funct ion ( func t i on , d e s c r i p t i o n ) ;SELECT has_funct ion ( f u n c t i o n ) ;

CAST everywhere

~# SELECT has_funct ion ( ’ has_funct ion ’ ) ;

has_function---------------------------------------------ok 8 - Function has_function() should exist

(1 row)

CAST everywhere

~# SELECT has_funct ion ( ’ p u b l i c ’ , ’ has_funct ion ’ ) ;

has_function---------------------------------not ok 9 - has_function +

# Failed test 9: ‘‘has_function’’(1 row)

CAST everywhere

~# SELECT has_funct ion ( ’ p u b l i c ’ : : name,’ has_funct ion ’ : : name) ;

has_function-----------------------------------------------------ok 10 - Function public.has_function() should exist

(1 row)

CAST everywhere

PREPARE coun t_s i te ASSELECT count ( ∗ )

FROM s i t eWHERE i d < 0;

SELECT resu l t s_eq (’ coun t_s i t e ’ ,

ARRAY[ 0 ] ,’ check s i t e name ’ ) ;

CAST everywhere

results_eq-------------------------------------------------------------------not ok 2 - check site name +# Failed test 2: ‘‘check site name’’ +# Number of columns or their types differ between the queries

(1 row)

CAST everywhere

SELECT resu l t s_eq (’ coun t_s i t e ’ ,

ARRAY[ 0 : : b i g i n t ] ,’ check s i t e name ’ ) ;

Organisation

BEGIN ;SELECT plan ( 7 ) ;

SELECT has_table ( ’ domains ’ ) ;SELECT has_table ( ’ s t u f f ’ ) ;SELECT has_table ( ’ sources ’ ) ;SELECT has_table ( ’ domain_stu f f ’ ) ;

SELECT has_column ( ’ domains ’ , ’ i d ’ ) ;SELECT co l_ is_pk ( ’ domains ’ , ’ i d ’ ) ;SELECT has_column ( ’ domains ’ , ’ domain ’ ) ;

SELECT ∗ FROM f i n i s h ( ) ;ROLLBACK;

Organisation

~∗# SELECT plan ( 7 ) ;p lan

−−−−−−1 . . 7

(1 row )

Organisation

~∗# SELECT ∗ FROM f i n i s h ( ) ;f i n i s h

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−# Looks l i k e you f a i l e d 7 t e s t s o f 7

(1 row )

Erreur classique

BEGIN ;

~∗# SELECT has_table ( ’ s i t e ’ ) ;ERROR: P0001 : You t r i e d to run a t e s t w i thou t a plan !

Gotta have aplanCONTEXT: SQL statement ‘ ‘SELECT _ g e t _ l a t e s t ( ’ todo ’ ) ’ ’PL / pgSQL f u n c t i o n _todo ( ) l i n e 9 at assignmentSQL statement ‘ ‘SELECT _todo ( ) ’ ’PL / pgSQL f u n c t i o n ok ( boolean , t e x t ) l i n e 9 at assignmentLOCATION: exec_stmt_raise , pl_exec . c :3068

ROLLBACk;

Pas de plan

~# SELECT no_plan ( ) ;no_plan−−−−−−−−−(0 rows )

~# SELECT has_table ( ’ s i t e ’ ) ;has_table−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−ok 1 − Table s i t e should e x i s t(1 row )

Cas classique

PREPARE c r e a t e _ s i t e ASINSERT INTO s i t e

( id , fqdn , sitename , uuid )VALUES

(−1, ’www. foo . bar ’ , ’ foobar ’ , uuid_generate_v4 ( ) ) ,(−2, ’www. b iz . com ’ , ’ Bizcom ’ , uuid_generate_v4 ( ) ) ;

PREPARE check_s i te ASSELECT sitename

FROM s i t eWHERE i d = −1;

SELECT l i ves_ok ( ’ c r e a t e _ s i t e ’ , ’ [ SetUp ] create s i t e s ’ ) ;

SELECT resu l t s_eq ( ’ check_s i te ’ ,ARRAY[ ’ foobar ’ : : t e x t ] ,’ check s i t e name ’ ) ;

Utilisation

rodo@roz-desktop:~/meetNantes-3(master)$ pg_prove -d rodo tests/ -vtests/tables.pg ..1..2ok 1 - Table items should existnot ok 2 - Table bar should exist# Failed test 2: "Table bar should exist"# Looks like you failed 1 test of 2Failed 1/2 subtests

Test Summary Report-------------------tests/tables.pg (Wstat: 0 Tests: 2 Failed: 1)

Failed test: 2Files=1, Tests=2, 0 wallclock secs ( 0.02 usr 0.00 sys + 0.03 cusr

0.00 csys = 0.05 CPU)Result: FAIL

Cas d’usage

Cas d’usage

Zero Downtime DeploymentDéploiement successifs sur toutes les plateformes

I qualifI staging EUI staging USI production FRI production USI production EU

Cas d’usage

$ l s −116.7−b−user−uuid−a−view . sq l16.7−b−user−uuid−b−drop−column . sq l16.7−c−account−view−manager . sq l16.7−d−account−view−employee . sq l16.7−e−document−extensions−drop−column . sq l. . .t e s t s /

Cas d’usage

r q u i e d e v i l l e db−ca i r o ~/16.7$ pg_prove −p 5491 −d rh2t e s t s /

t e s t s /16.7−a . pg . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . okt e s t s /16.7−b . pg . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . okt e s t s /16.7−c . pg . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . okt e s t s /16.7−d . pg . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . okt e s t s /16.7− f−user−uuid−a . pg . . . . . . . . . . . . . . . . . . . . okt e s t s /16.7− f−user−uuid−b . pg . . . . . . . . . . . . . . . . . . . . okt e s t s /16.7− f−user−uuid−d . pg . . . . . . . . . . . . . . . . . . . . okt e s t s /16.7− f−user−uuid−e . pg . . . . . . . . . . . . . . . . . . . . okt e s t s /16.7− i−view−denorm . pg . . . . . . . . . . . . . . . . . . . . okt e s t s /16.7− l−document−category−b . pg . . . . . . . . . . . . okt e s t s /16.7− r−document−extensions−b−t r i g g e r . pg . . okt e s t s /16.7−u−denorm . pg . . . . . . . . . . . . . . . . . . . . . . . . . okt e s t s /16.7−v . pg . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . okt e s t s /16.7−w. pg . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . okA l l t e s t s success fu l .F i l e s =14 , Tests =58 , 1 w a l l c l o c k secs ( 0.06 usr 0.02

sys + 0.47cusr 0.10 csys = 0.65 CPU)Resul t : PASS

Pourquoi tout écrire

rodo@roz-desktop:~/$ pg_tapgen -d rodorodo@roz-desktop:~/$ cat schema.sql

SELECT views_are(’public’, ARRAY[’pg_all_foreign_keys’,’tap_funky’]);

Indempotence

Index avant la 9.6

DO LANGUAGE p lpgsq l $$BEGIN

IF _have_index (’ p u b l i c ’ ,’ account_user ’ ,’ account_user_date_delete_ idx ’ )

THEN

DROP INDEX " account_user_date_delete_ idx " ;

END IF ;

END;$$ ;

Création d’utilisateur

DO LANGUAGE p lpgsq l $$BEGIN

IF NOT _has_role ( ’ dba ’ ) THEN

CREATE ROLE dba ;

END IF ;

END;$$ ;

Questions ?

Rodolphe Quiédeville

[email protected]

Document publié sous Licence Creative Commons BY-SA 2.0

tests unitaires pour postgresql avec pgtap

Technology