Ho già descritto in pas­sato le gioie del lavo­rare con MySQL¹. Qualche giorno mi sono imbat­tuto in que­sta perla²:

Attempting to “ALTER TABLE … DROP PRIMARY KEY” on a table when an AUTO_INCREMENT column exists in the key gene­ra­tes an error:

ERROR 1075 (42000): Incorrect table defi­ni­tion; there can be only one auto column and it must be defi­ned as a key.

To make this work without erro­ring, drop and re-add the new pri­mary key in a sin­gle sta­te­ment, e.g.:

ALTER TABLE myta­ble DROP PRIMARY KEY, ADD PRIMARY KEY(col1,col2);

Uno dirà che in fondo non è un gran pro­blema però è dif­fi­cile non avere l’impressione di scarsa orto­go­na­lità.³

Mi viene anche da fare que­sto paral­lelo. La lar­ghis­sima dif­fu­sione di WIndows com­bi­nata con i suoi difetti è stato un cata­liz­za­tore per la cre­scita di Linux; allo stesso modo, la dif­fu­sione di MySQL com­bi­nata con i suoi difetti sta acce­le­rando l’uso delle solu­zioni NoSQL. Sono fuori strada?

Vedo che anche altri sono più o meno del mio parere¹.

Mi sem­bra inte­res­sante anche SQLAlchemy, credo varrà la pena di dar­gli un’occhiata.

Premessa dove­rosa: non ho mai usato Core Data anche se ho usato in lungo e in largo l’EOF, fino a lasciarlo per­dere. I post¹ di Brent Simmons in cui dice di aver rinun­ciato a usare Core Data sull’iPhone per pro­blemi di pre­sta­zioni, quindi, non poteva sfug­girmi, tanto più che ha gene­rato diverse rea­zioni inte­res­santi².

Quello che mi inte­ressa discu­tere non è la scelta in sé, che è solo buon senso appli­cato alla pro­gram­ma­zione, ovvero, lavora sem­pre al più alto livello di astra­zione pos­si­bile, scendi sotto solo se sei obbligato.

Quello che con­ti­nuo a non capire è come si possa soste­nere che una cosa come update newsItems set read = 1 where... stia a un livello di astra­zione più basso di un loop in Objective-C o qual­che altro linguaggio.

Non mi sfug­gono gli aspetti pro­ble­ma­tici del fare inte­ra­gire un pro­gramma con un data­base né che secondo alcuni Core Data non è un data­base o un ORM³, ma è pro­prio que­sto arguire intorno al cosa sia Core Data mi rende sospet­toso per­ché que­ste discus­sioni sem­brano spo­stare il pro­blema nell’àmbito delle aspet­ta­tive sbagliate.

Secondo la docu­men­ta­zione ufficiale:

The tech­ni­cally cor­rect way to describe Core Data is as an object-graph mana­ge­ment and per­si­stence fra­mework. In down-to-earth terms, this means that Core Data orga­ni­zes the application’s model layer into a set of defi­ned in-memory data objects. Core Data tracks chan­ges to these objects and can reverse those chan­ges on demand, such as when a user per­forms an undo com­mand. Then, when it is time to save chan­ges to your application’s data, Core Data takes care of archi­ving the objects to a per­si­stent store. (…)

Core Data builds on some of the con­cepts of enterprise-class data­base appli­ca­tion fra­meworks, such as the Enterprise Objects Framework in WebObjects. However, make no mistake, Core Data is not an object-relational data­base access fra­mework. Instead, it uses con­cepts from the data­base world to take appli­ca­tion data mana­ge­ment to a new level.

Ora, se diamo per buona que­sta descri­zione — e non vedo per­ché non dovremmo — biso­gna chie­dersi: le aspet­ta­tive di Brent Simmons erano ragio­ne­voli? Credo di sì, per­ché se “usi dei con­cetti dal mondo dei data­base” non è strano aspet­tarsi un modo effi­ciente di fare un update. Giusto?

Tempo fa, quando spie­gavo le novità di PostgreSQL 8.4 nelle fun­zioni, avver­tivo anche che la com­bi­na­zione dei para­me­tri di default con l’overloading degli argo­menti delle fun­zioni poteva con­durre ad ambiguità.

Mi ero però limi­tato alla parte nega­tiva, men­tre è pro­prio gra­zie ai default che diventa pos­si­bile sba­raz­zarsi di fun­zioni dallo scopo iden­tico, che variano solo per il numero di argomenti.

In que­sto post si spie­gano i van­taggi (piut­to­sto ovvi, in verità) di que­sto approc­cio e si accen­nano alle novità in arrivo per la ver­sione 8.5.

Pensierini della sera

Nei momenti di sciat­te­ria mi vien da pen­sare che in fondo, per la mag­gior parte degli usi, MySQL va più che bene. Sì, d’accordo, PostgreSQL è un’altra cosa ma sono dif­fe­renze che spesso non si vedono.

Poi mi capi­tano cose come quella che segue e capi­sco che è una sciocchezza.

Il fatto

C’è que­sta appli­ca­zione PHP+MySQL da met­tere in sicu­rezza, prima della riscrit­tura da zero. Provo alcune modi­fi­che in locale e, essendo tutto a posto, riporto gli aggior­na­menti sul ser­ver di deployment.

Verifico che fun­zioni tutto e noto alcune stra­nezze per cui ini­zio a fare tutte le pos­si­bili veri­fi­che ma sem­bra tutto a posto, fin quando provo l’SQL a mano e vedo che una query dà errore dicendo che la tabella è inesistente (????).

A que­sto punto guardo la docu­men­ta­zione di MySQL e sco­pro che:

In MySQL, data­ba­ses cor­re­spond to direc­to­ries within the data direc­tory. Each table within a data­base cor­re­sponds to at least one file within the data­base direc­tory (and pos­si­bly more, depen­ding on the sto­rage engine). Consequently, the case sen­si­ti­vity of the under­ly­ing ope­ra­ting system plays a part in the case sen­si­ti­vity of data­base and table names. This means data­base and table names are not case sen­si­tive in Windows, and case sen­si­tive in most varie­ties of Unix. One nota­ble excep­tion is Mac OS X, which is Unix-based but uses a default file system type (HFS+) that is not case sensitive.

A quel punto capi­sco: sto svi­lup­pando su Mac OS X e il ser­ver è Linux.

La morale della storia

Che dire? tutto que­sto è ridi­colo. Non so cosa dica lo stan­dard SQL: se il rife­ri­mento ai nomi debba esser case sen­si­tive o meno ma che una query smetta di fun­zio­nare a causa del sistema ope­ra­tivo sot­to­stante è vera­mente un deli­rio. Non vedo l’ora di man­dare in pen­sione que­sta applicazione!

Tanto tempo fa… Un prologo

Qualche anno fa mi ero messo a scri­vere un fra­mework di auten­ti­ca­zione e auto­riz­za­zione per WebObjects. L’obiettivo era quello di creare un sistema inte­grato in cui i prin­ci­pali aspetti della sicu­rezza fos­sero affron­tati e messi in col­le­ga­mento con il DirectToWeb. Fu un mezzo fallimento.

Funzionava, sì, ma era troppo mac­chi­noso da impo­stare e uti­liz­zare. Uno dei motivi fu quello di voler usare un data­base per memo­riz­zare le con­fi­gu­ra­zioni, obbli­gan­domi anche a pre­ve­dere un’interfaccia di gestione per aspetti che è quasi sem­pre pre­fe­ri­bile affron­tare con un buon edi­tor di testo.

Flash for­ward

L’altro ieri mi tro­vavo a met­tere le mani su un’applicazione fatta in Joomla (oh, the hor­ror… Risparmio le impre­ca­zioni per un codice che grida vendetta).

Anche Joomla usa un data­base die­tro le quinte per archi­viare le sue con­fi­gu­ra­zioni e con­sente un approc­cio sem­plice ad alcune delle fun­zioni di CMS ma rende le ope­ra­zioni di sta­ging molto, troppo com­pli­cate: non posso pro­vare una con­fi­gu­ra­zione in locale, fare il com­mit nella repo­si­tory e poi fare il deploy­ment ma devo repli­care le modi­fi­che sul data­base di pro­du­zione; inol­tre non ho nes­sun con­trollo di ver­sione se non facendo il dump delle tabelle.

Anche in WordPress, come segna­lavo qual­che giorno fa, alcune ope­ra­zioni con­cet­tual­mente sem­plici e stan­dard sono più com­pli­cate del previsto.

Un anti-pattern?

A que­sto punto, comin­cio a sospet­tare seria­mente che que­sta solu­zione sia in realtà un anti-pattern bello e buono; forse dovremmo essere un po’ più par­si­mo­niosi nell’uso dei database.

Nelle appli­ca­zioni che sfrut­tano una con­nes­sione per­si­stente verso il data­base e imple­men­tano al loro interno la logica di auten­ti­ca­zione e auto­riz­za­zione, può pre­sen­tarsi la neces­sità di pas­sare al data­base delle infor­ma­zioni rela­tive all’utente dell’applicazione, che sarà diverso da quello usato per con­net­tersi al database.

In molti casi, se si tratta solo di inse­rire le infor­ma­zioni di audi­ting su poche tabelle, è suf­fi­ciente che sia l’applicazione a pas­sare espli­ci­ta­mente que­ste infor­ma­zioni, durante il sal­va­tag­gio dei record. Se l’auditing va fatto su tutte le tabelle si può pro­get­tare il codice in modo da ren­dere l’operazione più o meno trasparente.

Non sem­pre que­sto è pos­si­bile — le modi­fi­che al codice sareb­bero troppo one­rose o impos­si­bili — né ade­guato. Ad esem­pio, quando vogliamo trac­ciare even­tuali atti­vità svolte al di fuori del con­trollo dell’applicazione. In que­sti casi la solu­zione dev’essere tro­vata al livello del database.

Il pro­blema diventa quindi come pas­sare in modo tra­spa­rente i dati sull’utente dell’applicazione. Una pos­si­bi­lità con­si­ste nell’usare le cosid­dette opzioni custom in com­bi­na­zione con le trigger.

Al login sull’applicazione verrà chia­mata una fun­zione che impo­sta il valore dell’opzione custom:

CREATE OR REPLACE FUNCTION begin_sess(staffid text)
RETURNS void AS $$ BEGIN
    PERFORM set_config(
        'mysess.curr_user',
        coalesce(staffid,''),
        false
    );
END; $$ LANGUAGE 'plpgsql' VOLATILE

Con que­sta infor­ma­zione, basta aggan­ciare una trig­ger ad ogni tabella che ci interessa:

...
DECLARE
   curr_user	staff.staff_id%TYPE;
BEGIN
   SELECT current_setting('mysess.curr_user') INTO curr_user;
...

Se il valore di mysess.curr_user fosse vuoto, ad es. per­ché l’operazione viene fatta diret­ta­mente sul data­base, basterà ripie­gare sull’utente a livello del database.

Le infor­ma­zioni ripor­tate qui sono state rica­vate più o meno pedis­se­qua­mente da que­sto thread: Audit Trigger puzz­ler.

Anche se non amo i NULL, que­sto “trucco” che ho letto sulla mai­ling list di PostgreSQL è troppo carino per non citarlo.

Qual è il problema?

Come dice il titolo, vogliamo imporre un vin­colo di uni­cità su più colonne, una delle quali può essere NULL. In PostgreSQL, se abbiamo tre colonne A, B e C — con C che può essere NULL — e creiamo uno UNIQUE INDEX con tutte e tre, posso inse­rire due record con valori dupli­cati per A e B.

Salvati dagli indici parziali

La solu­zione, una volta tanto, è sem­plice: basta creare un indice par­ziale:
CREATE INDEX idx ON table_name(A,B) WHERE (C IS NULL);

Qualche giorno fa par­lavo di pgtap e accen­navo alla pos­si­bi­lità di usarlo per usare il TDD diret­ta­mente con il data­base, sfrut­tando il fatto che PostgreSQL con­sente l’uso di tran­sa­zioni anche per
i comandi di defi­ni­zione delete database.

Questo è un grosso van­tag­gio per­ché posso defi­nire nuovi oggetti dello schema senza che even­tuali errori si pro­pa­ghino al di fuori della ses­sione corrente.

NB: L’esempio che segue è pro­lisso e pro­ba­bil­mente simile a innu­me­re­voli altri esempi sulle virtù del “test infec­ted” ma mi sem­bra comun­que degno di nota che si possa usare que­sto approc­cio anche all’interno di un data­base. Bene, cominciamo:

\set ECHO
\set QUIET 1
\pset format unaligned
\pset tuples_only true
\pset pager
\set ON_ERROR_ROLLBACK 1
\set ON_ERROR_STOP true
\set QUIET 1

Dopo aver impo­stato alcuni para­me­tri per psql, pro­viamo a defi­nire un nuovo domain che verrà poi uti­liz­zato da una nuova tabella.

Ho instal­lato pgtap in uno schema a sé stante, in modo che le sue fun­zioni non si mischino con quelle dello schema “vero”.

Visto che vogliamo svi­lup­pare in moda­lità TDD, creiamo subito il test per veri­fi­care la pre­senza del domain, che ovvia­mente fallirà.

BEGIN;
SET search_path TO tap, public;
SELECT plan(1);
SELECT has_domain( 'new_domain', 'verifichiamo la presenza del domain');
SELECT * FROM finish();

ROLLBACK;

1..1
not ok 1 - verifichiamo la presenza del domain
# Failed test 1: "verifichiamo la presenza del domain"
# Looks like you failed 1 test of 1

Aggiungiamo il domain:

BEGIN;

CREATE DOMAIN new_domain int CHECK (VALUE > 0 AND VALUE < 5);

SET search_path TO tap, public;
SELECT plan(1);
SELECT has_domain( 'new_domain', 'verifichiamo la presenza del domain');
SELECT * FROM finish();

ROLLBACK;

1..1
ok 1 - verifichiamo la presenza del domain

Funziona! Adesso tocca alla tabella:

BEGIN;

CREATE DOMAIN new_domain int CHECK (VALUE > 0 AND VALUE < 5);

SET search_path TO tap, public;
SELECT plan(2);
SELECT has_domain( 'new_domain', 'verifichiamo la presenza del domain');
SELECT has_table( 'new_table', 'verifichiamo la presenza di new_table');
SELECT * FROM finish();

ROLLBACK;

1..2
ok 1 - verifichiamo la presenza del domain
not ok 2 - verifichiamo la presenza di new_table
# Failed test 2: "verifichiamo la presenza di new_table"
# Looks like you failed 1 test of 2

Solita sto­ria: fac­ciamo in modo di pas­sare anche que­sto secondo test.

BEGIN;

CREATE DOMAIN new_domain int CHECK (VALUE > 0 AND VALUE < 5);
CREATE TABLE new_table (col new_domain);

SET search_path TO tap, public;
SELECT plan(2);
SELECT has_domain( 'new_domain', 'verifichiamo la presenza del domain');
SELECT has_table( 'new_table', 'verifichiamo la presenza di new_table');
SELECT * FROM finish();

ROLLBACK;

Aggiungiamo qual­che test “perimetrale”:

BEGIN;

CREATE DOMAIN new_domain int CHECK (VALUE > 0 AND VALUE < 5);
CREATE TABLE new_table (col new_domain);

SET search_path TO tap, public;
SELECT plan(4);
SELECT has_domain( 'new_domain', 'verifichiamo la presenza del domain');
SELECT has_table( 'new_table', 'verifichiamo la presenza di new_table');
SELECT col_type_is('new_table', 'col', 'new_domain');
SELECT col_hasnt_default('new_table', 'col');
SELECT * FROM finish();

ROLLBACK;

Uno degli aspetti inte­res­santi è che pos­siamo testare l’efficacia della vali­da­zione del domain sepa­ra­ta­mente dal suo uso effet­tivo in qual­che tabella. Nell’esempio che segue creiamo una tabella che ha il solo scopo di testare gli insert:

BEGIN;

CREATE DOMAIN new_domain int CHECK (VALUE > 0 AND VALUE < 5);
CREATE TEMP TABLE dummy (col new_domain);

SET search_path TO tap, public;
SELECT plan(3);
SELECT throws_ok(
    'INSERT INTO dummy (col) values(5)',
    '23514',
    'value for domain new_domain violates check constraint "new_domain_check"',
    'Non sono consentiti valori superiori a 4'
);
SELECT throws_ok(
    'INSERT INTO dummy (col) values(0)',
    '23514',
    'value for domain new_domain violates check constraint "new_domain_check"',
    'Non sono consentiti valori inferiori a 0'
);
SELECT lives_ok(
    'INSERT INTO dummy (col) values(1)',
    '1 è un valore consentito'
);
SELECT * FROM finish();

ROLLBACK;

1..3
ok 1 - Non sono consentiti valori superiori a 4
ok 2 - Non sono consentiti valori inferiori a 0
ok 3 - 1 è un valore consentito

Una volta che siamo sod­di­sfatti delle modi­fi­che basterà estrarre gli sta­te­ment DML e sot­to­met­terli, lasciando alla nostra suite di test.

Fino ad ora ho sem­pre testato il data­base attra­verso un qual­che stru­mento esterno, come i vari JUnit, TestNG o tcl­test.

Sono libre­rie che fun­zio­nano bene e che sono fami­liari a chiun­que pra­ti­chi un minimo di TDD ma non par­ti­co­lar­mente tagliate per testare i data­base (con l’eccezione forse di TestNG, che non uso da anni): vuoi per­ché biso­gna pre­ve­dere delle fix­ture ela­bo­rate che vanno poi eli­mi­nate; vuoi per­ché, a ben guar­dare, biso­gna attra­ver­sare tutta una serie di strati soft­ware anche solo per veri­fi­care un constraint.

Così ho fatto una ricerca e ho tro­vato due uti­lità per PostgreSQL — pgTAP e PGUnit — che per­met­tono di creare delle suite di test ese­guite diret­ta­mente nel database.

I van­taggi sono diversi: oltre all’accesso più diretto e pre­sta­zioni pre­su­mi­bil­mente migliori, la gestione delle fix­ture risulta sem­pli­fi­cata per­ché basta fare il roll­back alla fine dei test; inol­tre, dato che in PostgreSQL i comandi DDL sono tran­sa­zio­nali, diventa pos­si­bile testare le modi­fi­che al data­base istan­ta­nea­mente, senza doverlo fare in modo permanente.

Per ora ho scelto pgTAP. Mette a dispo­si­zione una serie di fun­zioni che pos­sono venire molto comode per scri­vere i test.

Le prime impres­sioni sono buone e penso che il miglio­ra­mento sarà anche più mar­cato quando potrò dise­gnare diret­ta­mente da zero uno schema in moda­lità TDD.