Jasmine : tests unitaires en JavaScript - Human Talks Grenoble 14.05.2013
Tests unitaires pour PostgreSQL avec pgTap
-
Upload
rodolphe-quiedeville -
Category
Technology
-
view
324 -
download
0
Transcript of 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
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_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
Document publié sous Licence Creative Commons BY-SA 2.0