Comparació de bases de dades en SQL Server (I)

Sovint, en el món de la informàtica i en d’altres camps, formem equips de treball (més o menys coordinats) per avançar en un projecte de forma paral·lela. Moltes vegades, sobretot quan programem, disposem d’elements comuns, com puguin ser fitxers de codi, binaris, bases de dades… als quals ha de poder accedir i manipular tothom alhora, sense que això suposi trepitjar la feina d’un altre equip.

Una primera solució a aquest problema seria incloure tot el material del que disposem en un fons comú, de manera que qui volgués editar quelcom s’ho descarregués temporalment i quan hagués acabat ho tornés a publicar de manera que tothom veiés els canvis. Els inconvenients apareixen abans de començar:

• Control de concurrència molt pobre (què passarà si dos usuaris estan modificant el mateix fitxer?)

• Es fa impossible saber qui ha efectuat cada canvi

• És un procés lent, ja que implica descarregar-se els fitxers un per un, quan és molt probable sigui necessari editar més d’un fitxer alhora

Existeixen altres mètodes per compartir la informació creant un arbre de versions, és a dir, cada vegada que algú modifica algun dels recursos es desa una còpia de l’element abans de modificar-se en un fons i la versió editada passa a ser considerada la base per a posteriors modificacions. D’aquesta manera es resolen els inconvenients presentats a la metodologia anterior.
Aquest apropament té noves mancances:

• Requereix que tots els equips de treball actualitzin periòdicament els documents a la versió més recent; sinó, algú podria donar com a vàlid un recurs modificat sobre una base que no conté tots els canvis

• Aquest sistema normalment inclou funcions de “mescla” de continguts, o sigui que si dos usuaris modifiquen el mateix fitxer (en segments diferents), el conjunt és prou “intel·ligent” per incloure ambdós canvis; els problemes vénen quan les modificacions dels usuaris afecten al mateix segment (quina és la bona?), sobretot comptant que són sistemes tan sensibles que un simple espai o tabulació poden generar el que es coneix com a conflicte, que ha de resoldre manualment l’usuari que se’l troba

• El mètode de control de canvis funciona correctament, sempre que es tracti de fitxers de text; quan estem parlant d’altres tipus de dades (imatges, binaris compilats, bases de dades…), el sistema pot reportar modificacions (de les que potser no som ni conscients) i crear conflictes

En aquest punt cal buscar alguna metodologia que ens permeti solucionar el màxim de problemes. Necessitem trobar una manera d’extreure “l’estructura” de les dades (i posteriorment el contingut en si) per tal que la comparació es pugui efectuar de forma automàtica sense gaire marge d’error.

En la sèrie d’articles que avui començo, faré èmfasi en la comparació de bases de dades, en especial aquelles aixecades sobre el sistema gestor SQL Server, i en aquest post en concret publicaré una petita solució que ens ajudarà a comparar els procediments emmagatzemats i funcions que resideixin en la pròpia base de dades. Tot i així, intentaré que les idees plantejades siguin el màxim de portables a altres sistemes, de manera que amb petits retocs es puguin adaptar fàcilment a altres entorns.

La meva primera aproximació ha estat aquesta:

T-SQL

DECLARE @def VARCHAR(MAX)
DECLARE rout CURSOR FOR
    SELECT ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES
    ORDER BY ROUTINE_NAME
OPEN rout
FETCH NEXT FROM rout INTO @def
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @def
    FETCH NEXT FROM rout INTO @def
END
CLOSE rout
DEALLOCATE rout

L’explicació del codi anterior és la següent:

1. En primer lloc declarem una variable que anirà recollint les definicions de cada procediment o funció. He escollit el tipus VARCHAR(MAX) ja que és el que m’ha semblat més adient per qüestions d’espai i flexibilitat; és possible que s’hagi d’adequar al SGBD o versió de SQL Server que s’utilitzi.

2. A continuació declarem una variable de tipus cursor. Un cursor és una mena de punter que utilitzarem per anar recorrent el resultat d’una consulta, que és la clàusula que segueix a la paraula clau FOR. En aquest cas, la consulta s’efectua sobre la vista de sistema INFORMATION_SCHEMA.ROUTINES, que és la que mostra tota la informació relativa als procediments i funcions que es troben dins la base de dades actual (si cal, podem incloure una directiva USE <base_de_dades> al principi de tot el codi per especificar el nom de la base de dades sobre la que actuarem) i extreu la ROUTINE_DEFINITION, és a dir, el text que representa el codi de cada funció o procediment. És important notar que la consulta ordena el resultat a partir del nom de la rutina. Si no especifiquem aquesta opció, el resultat pot variar d’una base de dades a una altra, i recordem que volem automatitzar, i per tant normalitzar, tot el procés de comparació. Més tard en veurem millor la necessitat.

3. Un cop declarat completament el cursor, procedim a obrir-lo (deixar-lo preparat per accedir-hi) mitjançant la operació de OPEN.

4. Cada vegada que volguem obtenir una línia de resultats, emprarem la operació FETCH NEXT FROM, especificant el nom del cursor del que volem extreure les dades i, a continuació, la llista de variables sobre les que volcarem la informació, precedida de la paraula INTO. En el nostre cas, com que únicament hem extret la ROUTINE_DEFINITION, només ens cal especificar una variable, però si en la consulta retornéssim múltiples camps, la llista podria augmentar.

5. En el següent bloc de codi realitzarem un bucle. Tal i com s’observa, la condició de permanència de la sentència iterativa és @@FETCH_STATUS = 0. La variable d’entorn @@FETCH_STATUS conté un valor que indica si l’última instrucció de FETCH que s’ha realitzat sobre un cursor (qualsevol) ha retornat algun resultat; així doncs, valdrà 0 sempre que s’hagi efectuat amb èxit o un número negatiu si s’ha produït un error o s’ha assolit el final dels registres del resultat. Es podria consultar l’estat d’un cursor específic mitjançant la funció dinàmica de sistema sys.dm_exec_cursors, però és una característica que va més enllà del propòsit d’aquest article.

6. A cada iteració, mostrem per consola el valor de la consulta que s’havia desat a la volta anterior; després, movem el cursor al següent registre del resultat amb un altre FETCH. L’absència d’aquesta instrucció provocaria un bucle infinit.

7. Per acabar, quan hem acabat totes les iteracions necessàries, tanquem el cursor mitjançant la operació CLOSE i alliberem la variable que el contenia, amb la instrucció DEALLOCATE, que permetrà reutilitzar la variable per a futures accions. Si s’omet aquest pas, ens podem trobar amb que no podem executar més d’un cop el procés perquè la variable encara es troba registrada:

   Msg 16915, Level 16, State 1, Line 3
A cursor with the name 'rout' already exists.

Ja per últim, només ens queda desar els resultats que hem anat mostrant per consola en un fitxer de text. Realitzant la mateixa operació en una altra base de dades obtindrem la “signatura” de tots els processos que tenen ambdues i podrem comparar els fitxers mitjançant alguna utilitat, com per exemple ExamDiff, que a més ens ajuda a realitzar el merge de forma més automatitzada. És en aquest punt on es veu clarament la necessitat d’ordenar els resultats de la consulta, ja que si no ho féssim així, els sistemes de comparació reportarien diferències relacionades amb l’ordre, més que no pas amb el contingut dels resultats.

Conclusions
Com tot, la solució que he proposat, té diversos punts fluixos, com ara el fet que es tracta d’un problema de base de dades, i com a tal, no és recomanable un apropament iteratiu. En comptes d’això, es podria haver optat per una solució aparentment més òptima de l’estil de

T-SQL

DECLARE @def VARCHAR(MAX)
SELECT @def = ''
SELECT @def = @def + ROUTINE_DEFINITION
   FROM INFORMATION_SCHEMA.ROUTINES
PRINT @def

Tot i així, un anàlisi més acurat ens revela que no estem ordenant de cap manera els resultats, ja que a causa de la naturalesa d’aquesta solució només se’ns retorna l’últim dels registres de la consulta en cas d’especificar la clàusula ORDER BY. A més, com que estem acumulant tot el text en una mateixa variable, l’espai necessari augmenta, i per tant l’execució s’alenteix progressivament; també relacionat amb el tema de l’espai, i depenent del gestor utilitzat, ens podem trobar amb què els resultats queden truncats abans de mostrar-se a la consola, i per tant s’impossibilita la comparació final.

Qualsevol comentari, correcció o optimització seran benvinguts.