tests unitaires pour postgresql avec pgtap

Download Tests unitaires pour PostgreSQL avec pgTap

Post on 21-Jan-2017

313 views

Category:

Technology

0 download

Embed Size (px)

TRANSCRIPT

  • pgTap, tests unitaires pour PostgreSQL

    Rodolphe Quideville

    Meetup PostgreSQL Nantes

    22 juin 2016

  • #mylife

    I Dcouvert 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 lcriture 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 implmentationsaujourdhui 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

    Rsultat de fonction

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

  • Fonctions

    Rsultat de requte

    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 schma

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

  • Fonctions

    Test de schma

    SELECT has_tablespace ( sometablespace , / data / dbs ) ;

  • Tests de schma

    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 schma

    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 schma

    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 schmas

    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

  • Paramtres

    Paramtres 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

    List of functionsSchema | Name | Result data type | Argument data types | Type

    --------+--------------+------------------+--------------------------+--------public | has_function | text | name | normalpublic | has_function | text | name, name | normalpublic | has_function | text | name, name[] | normalpublic | has_function | text | name, name, name[] | normalpublic | has_function | text | name, name, name[], text | normalpublic | has_function | text | name, name, text | normalpublic | has_function | text | name, name[], text | normalpublic | has_function | text | name, text | normal

    (8 rows)

  • 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

    ~# \df count

    List of functionsSchema | Name | Result data type | Argument data types | Type

    ------------+-------+------------------+---------------------+------pg_catalog | count | bigint | | aggpg_catalog | count | bigint | "any" | agg

    (2 rows)

  • 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_tableok 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 dusage

  • Cas dusage

    Zero Downtime DeploymentDploiement successifs sur toutes les plateformes

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

  • Cas dusage

    $ l s 116.7buseruuidaview . sq l16.7buseruuidbdropcolumn . sq l16.7caccountviewmanager . sq l16.7daccountviewemployee . sq l16.7edocumentextensionsdropcolumn . sq l. . .t e s t s /

  • Cas dusage

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

    t e s t s /16.7a . pg . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . okt e s t s /16.7b . pg . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . okt e s t s /16.7c . pg . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . okt e s t s /16.7d . pg . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . okt e s t s /16.7 fuseruuida . pg . . . . . . . . . . . . . . . . . . . . okt e s t s /16.7 fuseruuidb . pg . . . . . . . . . . . . . . . . . . . . okt e s t s /16.7 fuseruuidd . pg . . . . . . . . . . . . . . . . . . . . okt e s t s /16.7 fuseruuide . pg . . . . . . . . . . . . . . . . . . . . okt e s t s /16.7 iviewdenorm . pg . . . . . . . . . . . . . . . . . . . . okt e s t s /16.7 ldocumentcategoryb . pg . . . . . . . . . . . . okt e s t s /16.7 rdocumentextensionsbt r i g g e r . pg . . okt e s t s /16.7udenorm . pg . . . . . . . . . . . . . . . . . . . . . . . . . okt e s t s /16.7v . pg . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . okt e s t s /16.7w. 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;$$ ;

  • Cration dutilisateur

    DO LANGUAGE p lpgsq l $$BEGIN

    IF NOT _has_role ( dba ) THEN

    CREATE ROLE dba ;

    END IF ;

    END;$$ ;

  • Questions ?

    Rodolphe Quideville

    rodolphe.quiedeville@people-doc.com

    Document publi sous Licence Creative Commons BY-SA 2.

Recommended

View more >