Haal meer uit Stored Procedures

Het genereren van een database access laag op Stored Procedures

Inleiding

Na mijn bezoek aan de CttP in december 2003 ging ik huiswaarts met een behoorlijk goed gevoel. Zowel de sessie van Alex Roelvink over Software Renovatie als die van Jos Warmer over Model Driven Architecture waren voor mij enorm inspirerend. Beide sessies behandelden de nieuwste methodieken in softwareontwikkeling. Het ontwikkelproces wordt meer en meer volwassen. Een van de kernpunten was het vastleggen van applicatie-eigenschappen in een metadata model om er vervolgens code mee te kunnen genereren.

Het ontwikkelen van tools voor de ontwikkeling van software is voor een programmeur een krent in de pap, zo ook voor mij. Opgeladen door de CttP heb ik de donkere dagen voor kerst doorgebracht door mij te storten op een poging om een Database Access Code Laag te genereren in C# op basis van de informatie die ik uit mijn Stored Procedures kan halen. Dit artikel gaat niet in op het genereren van code, maar is een verslag van mijn zoektocht naar de juiste metadata om de code te kunnen genereren.

Beweegredenen

Hoewel dit onderwerp interessant kan zijn voor ontwikkelaars met diverse achtergronden, moet ik zeggen dat ik zelf ontwikkel in C# met behulp van Microsoft Visual Studio .NET 2003 en dat ik als database server gebruik maak van Microsoft SQL Server 2000. Voordat ik in detail treed over het genereren van code op basis van Stored Procedures, wil ik een kader schetsen dat moet voorkomen dat ik mijn verhaal telkens moet onderbreken om bepaalde keuzes te beargumenteren.

Wie er niet van overtuigd is, dat het werken met Stored Procedures vele voordelen kan bieden ten opzichte van het werken met Queries in code, zal mogelijk afhaken op het moment dat de eerste haken en ogen aan de orde komen. Ik ben er van overtuigd dat het werken met Stored Procedures voordelen biedt op het gebied van:

• Productiviteit;
• Onderhoudbaarheid;
• Snelheid;
• Beveiliging;
• Schaalbaarheid;
• Herbruikbaarheid.

Productiviteit

Je collega kan je met raad en daad bijstaan als het gaat om de ontwikkeling of het testen van Queries, terwijl hij of zij geen ervaring hoeft te hebben met de ontwikkelomgeving of de architectuur van de applicatie. Een andere vorm van productiviteit die ik heb ervaren, is het feit dat SQL Server uitgebreide mogelijkheden biedt voor het testen en debuggen van Queries. Ook het feit dat je even een Stored Procedure kunt vervangen zonder dat je de applicatie opnieuw hoeft te compileren, te deployen en op te starten vormt naar mijn mening een enorme productiviteitswinst.

Onderhoudbaarheid

Bij object georiënteerd ontwikkelen is het al lange tijd een goede gewoonte om de interne aangelegenheden van een object af te schermen voor de buitenwereld. We maken een duidelijk onderscheid tussen Private en Public. De belangrijkste reden hiervoor is de onderhoudbaarheid. Het geeft je de mogelijkheid om op een later tijdstip verbeteringen aan te brengen aan de implementatie en de onderliggende architectuur.

Ik herinner mij een project waarbij ik verantwoordelijk was voor het database model. Ik bleef verbeteringen aanbrengen aan het model. Hierdoor had ik continu ruzie met de ontwikkelaars van één van de applicaties die gebruik maakte van de database. Telkens als ik een verandering aanbracht, moesten zij hun datamodel in code aanpassen. Vandaag de dag zouden zij dat datamodel wellicht genereren, maar dan zouden de implicaties nog steeds dezelfde zijn. Het probleem was namelijk dat de code gebruik maakte van de letterlijke tabellen in de database.

Na deze ervaring hanteer ik, wat betreft de omgang met een database, de gewoonte dat Tabellen, Functies, Types en Regels een interne aangelegenheid betreffen. Enkel Stored Procedures en Views zijn voor de buitenwereld toegankelijk. Hiermee is de onderhoudbaarheid enorm verbeterd. De keuze voor bepaalde tabelstructuren is een implementatie geworden en daar heeft de gebruiker van de database niets mee te maken.

Snelheid

Ik heb een test geschreven op de Northwind database en uitgebreid gemeten - want meten is weten -, dat Stored Procedures overwegend sneller zijn dan Queries vanuit code. SQL Server analyseert een Stored Procedure, cachet een Execution Plan en telkens als de Stored Procedure wordt uitgevoerd, worden alleen de parameters vervangen. Naarmate de complexiteit van de Stored Procedure toeneemt zal de performance winst meestal ook toenemen.

Beveiliging

Het eerste en meest voor de hand liggende veiligheidsaspect is dat SQL Server voor elke Stored Procedure het executie recht kan toekennen aan gebruikers. Hiermee kan je twee verschillende toepassingen van veiligheid bereiken. Ten eerste kun je regelen dat een specifieke applicatie, wel of geen recht heeft om bepaalde Stored Procedures uit te voeren. Een andere werkwijze is dat je, met behulp van Impersonation, van elke gebruiker van de applicatie kan bepalen welke Stored Procedure mag worden uitgevoerd. De bovengenoemde oplossingen zijn aardig als je gebruik maakt van het bestaande gebruikers beheer, zoals Active Directory.

In mijn werksituatie heb ik veel te maken met de ontwikkeling van Content Management Systemen. Samenwerking  aan documenten speelt een belangrijke rol. Als het om de security gaat, wil je dus niet alleen bepalen wie iets mag, je wilt ook bepalen wat iemand mag en bovendien wil je bepalen met betrekking tot welke content iemand iets mag. De database beheert de content en zou naar mijn mening dan ook verantwoordelijk kunnen zijn voor het handhaven van de rechten. In de praktijk zitten de voorwaarden om die rechten te bepalen meestal ook in de database. In dat geval zou je Content Security kunnen oplossen in een Stored Procedure.

Schaalbaarheid

De architectuur van de meeste software-oplossingen is een Two-Tier architectuur. De oplossing als geheel draait op twee machines. De oplossing bestaat uit een client en een database server. Het meest voor de hand liggende ‘opschaal’-scenario in zo’n situatie is het uitbreiden van de database server door te gaan clusteren. Dit is vanuit het beheeroogpunt eenvoudig te realiseren.

Eerder al met behulp van COM en nu met de opkomst van Web Services neemt de architectuur, van veel toepassingen, steeds vaker de vorm aan van een Three-Tier architectuur. Er wordt een aparte machine ingericht voor een business service. Deze service zou bijvoorbeeld meerdere bronnen kunnen raadplegen en de content samengevoegd kunnen aanbieden. De noodzaak voor een dergelijke Middle-Tier zou kunnen zijn, dat je security wilt toepassen op de gecombineerde content, of dat je bepaalde afhankelijkheden moet controleren. In die situatie wordt opschalen ineens ook mogelijk op het niveau van de business service. Het opschaal scenario wordt vanuit het beheeroogpunt dus een stuk complexer.

Voor alle architectuurscenario’s geldt naar mijn idee, dat als je zoveel mogelijk in de database server oplost middels Stored Procedures, je het langst op een eenvoudige en beheersbare wijze kunt opschalen met behulp van de standaard faciliteiten die de database server biedt. Als het gaat om aggregatie van gegevens uit verschillende databases bieden SQL Server en de Stored Procedures natuurlijk ook mogelijkheden.

Herbruikbaarheid

De situatie op mijn werk is de afgelopen paar jaar nogal drastisch veranderd. Van oudsher was DiVa, het bedrijf waar ik werkzaam ben, een Macromedia ColdFusion specialist en bovendien een Internet specialist. Alle collega’s konden elkaar met raad en daad bijstaan als het ging om ColdFusion kennis. De afgelopen paar jaar zijn daar steeds meer ontwikkelomgevingen bijgekomen. Bovendien zijn inmiddels niet alle ontwikkelingen alleen maar web gebaseerd. Momenteel zijn de twee belangrijkste ontwikkelomgevingen, ColdFusion en .NET.

De situatie met betrekking tot kennis over bepaalde oplossingen is dus veranderd. Waar iedere collega een ander momenteel nog wel mee van dienst kan zijn, is zijn kennis over SQL Server. In ons geval gaat de database langer mee dan de ontwikkelomgeving. Hierdoor zijn voor ons de database server en bijbehorende tools meer naar de voorgrond gekomen. Door meer functionaliteit naar Stored Procedures te verhuizen kunnen we meer gebruik maken van elkaars ervaring en inzet.

Ik verwacht dat de komende jaren voor ons in het kader zullen staan van migratie naar .NET. De meeste van onze applicaties hebben één belangrijke overeenkomst, ze gebruiken Microsoft SQL Server. Doordat wij voor al onze ontwikkelingen SQL Server gebruiken, is het idee geboren om vanuit Stored Procedures een code laag te genereren. Het maakt, met de krachtige mogelijk heden van XML en XSLT, niet uit voor welk platform we dat doen. Dus in ons geval kunnen we nu ColdFusion code genereren en in een later stadium, als de klant rijp is, voor .NET een zelfde codelaag in C#.

Analyse

Ik heb voldoende beweegredenen en inmiddels vaak genoeg dezelfde soort regels code geschreven om nu de code te willen genereren. Ik ga dus bepalen wat ik wil genereren.

Ten eerste wil ik van SQL naar XML naar C#.

Ik zal dus moeten bepalen wat ik in C# wil bewerkstelligen om te kunnen bepalen wat ik aan metadata nodig heb.

Aan de kant van C#

Er is een aantal verschillende manieren waarop ik een Stored Procedure wil uitvoeren:

·          Via de ExecuteNonQuery method van het SqlCommand object. Deze methode retourneert enkel het aantal bewerkte rijen. ExecuteNonQuery gebruik ik dus voor het aansturen van de Stored Procedures die geen resultaat retourneren.

·          Via de ExecuteScalar method van het SqlCommand object. Deze retourneert enkel de eerste kolom uit de eerste rij. Deze methode gebruik je om bijvoorbeeld een enkele waarde uit een Stored Procedure te ontvangen.

·          Via de Fill method van het het SqlDataAdapter object. Deze methode gebruik ik om een DataTable te vullen met het resultaat van een Stored Procedure die een resultaat set retourneert.

Voor alle manieren waarop ik de Stored Procedures ga aansturen geldt dat ik parameters moet kunnen meegeven. Voor het gebruik van de ExecuteScalar methode en de Fill methode geldt dat ik wil weten wat het formaat is van het resultaat. Ik wil immers alle gegevens in typevaste datacollecties verder transporteren.

Aan de kant van de database

Aan de kant van de database valt nog op te merken, dat ik ook wel Stored Procedures gebruik die ik niet in mijn code laag nodig heb. Dit zijn vaak hulpprocedures en deze werken met output parameters.

De conclusie is dat ik de volgende metadata nodig heb:

De parameters

Ik wil van alle Stored Procedures gegevens over de parameters:

• Naam;
• Type;
• Lengte;
• Default;
• IsOutput.

Het gedrag

Ik wil van alle Stored Procedure bepalen wat het gedrag is. Zo kan ik in mijn code laag de juiste implementatie hanteren voor de aansturing ervan.

Ik maak onderscheid tussen vier soorten:

• Een Procedure die niks retourneert;
• Een Procedure die één enkele waarde retourneert;
• Een Procedure die een set van waardes retourneert;
• Een Procedure die zijn resultaat in output parameters zet.

Het resultaat

Ten derde wil ik van alle Views en alle Stored Procedures die iets retourneren, weten wat het formaat is van het resultaat, zodat ik het resultaat kan beschouwen als een tabel. Hiervoor heb ik nodig:

• Kolomnaam;
• Type.

Synthese

Nadat ik heb bepaald wat ik aan metadata gegevens moet verzamelen, zal ik moeten bepalen op welke wijze ik dat kan gaan realiseren.

De standaard Stored Procedures

Ik heb eerst de mogelijkheden bekeken van allerlei standaard Stored Procedures die aanwezig zijn in de Master database van SQL Server. Zo kun je bijvoorbeeld met de Stored Procedure “sp_procedure_params_rowset” alle gegevens boven water krijgen over het formaat van de parameters van een Stored Procedure. Maar als het gaat om het gedrag van een Stored Procedure of het formaat van het resultaat, dan ligt het wel wat gecompliceerder. Er zijn erg veel standaard Stored Procedures beschikbaar, maar ik heb er geen één kunnen vinden die mij deze metadata kan verschaffen. Als iemand weet wat ik over het hoofd zie, dan hoor ik dat heel graag.

Het parsen van een SQL script

Voorlopig ben ik tot de conclusie gekomen dat het via de standaard oplossingen niet mogelijk is om alle benodigde metadata te verzamelen. Ik heb besloten het te gaan proberen door een SQL dump te maken van de database.

Ik klik met rechts op de database in de SQL Server Enterprise manager, kies ‘All Tasks’ en vervolgens ‘Generate SQL Script’. In de eerste tab, ‘General’, maak ik een keuze voor alle objecten die ik nodig heb. In de tweede tab, ‘Formatting’ kies ik alleen de eerste optie, ‘Create’. In de derde tab, ‘Options’, kies ik ‘Script database’, ‘Script indexes’, ‘Script Primary keys…’ en klik op OK.

Nu beschik ik over die fameuze bak met knikkers zoals Alex Roelvink dat zo schitterend wist te omschrijven. Ik heb een tijdje in die bak zitten turen en zag allerlei verschillende kleuren knikkers, waarvan het naar mijn idee best mogelijk was om ze te sorteren.

De vraag die rijst als je aan zoiets begint, is natuurlijk of het überhaupt wel mogelijk is om alle metadata uit het SQL script boven water te halen. Volgens mij is het antwoord NEE, omdat SQL Server toestaat dat een Stored Procedure meerdere mogelijke resultaat sets kan retourneren.

Welke truc ik ook bedenk, er zal dus altijd een potentiële mismatch kunnen ontstaan tussen de te verwachten en de werkelijke uitkomst. Deze mismatch zal altijd worden veroorzaakt door de ontwikkelaar van een Stored Procedure. Dit is een risico en voor sommige onder ons wellicht onacceptabel.

Maar ik ga door, want ik zie te veel potentieel in het bereiken van mijn doel. Ik ben dus bereid om mezelf regels op te leggen over het schrijven van een Stored Procedure. Voor het schrijven van code hanteer ik immers ook Guidelines. De regel wordt dus dat een Stored Procedure altijd een zelfde resultaat set moet retourneren. Dan zou je dus het formaat van de uitkomst moeten kunnen vaststellen.

Geïnspireerd door de CttP sessie van Alex Roelvink waarin hij het heeft gehad over het Graphen van code, ben ik aan de slag gegaan om de SQL van een Stored  Procedure om te zetten in een boomstructuur. Ik kan nu achterhalen wat het diepst liggende SQL Statement is, in de buitenste nesting. Zo kan ik bepalen wat het gedrag is van een Stored Procedure.

Als het een resultaat retourneert is dat altijd het gevolg van een Select Statement, met uitzondering van de Stored Procedure die met output parameters werkt. Door de desbetreffende Select Clause en bij behorende From Clause te parsen moet het mogelijk zijn om te achterhalen wat het formaat van het resultaat is.

Door uit de From Clause de betrokken tabellen en views te verzamelen en de eventuele bijbehorende aliassen, kun je de referenties in de Select Clause herleiden tot de originele kolommen in de tabellen en de views. Zodoende kun je het type achterhalen.

Door alle declaraties te verzamelen uit de Stored Procedure kun je de referenties naar variabelen in de Select Clause terug leiden tot hun type. Wat hier mis kan gaan is dat er in het resultaat een kolom kan ontstaan zonder naam.

Bij de toekenning van waarden in een Select Clause zijn er meerdere mogelijkheden.

De eerste is de toekenning van een variabele of parameter. In deze situatie is het type dus goed te achterhalen.

De tweede situatie is de toekenning van een expressie. En dan wordt het toch echt lastig.

Als je kijkt naar het mogelijke type dat uit een SUM kan komen dan zijn dat er al 4. Kortom, om het uiteindelijke type te bepalen, zul je vanuit de diepste nesting omhoog moeten werken en alle typeconversies volgen. Hier zou je kunnen besluiten dat je een dergelijke expressie eerst in één van de eerder gedeclareerde variabelen zet. En je zou als regel kunnen hanteren dat er voor een toekenning in een Select Clause alleen variabelen zijn toegestaan. Dan is tenminste het type altijd bekend.

Extended properties

Het lijkt me wel tijd om me even af te vragen waar ik nu precies mee bezig ben. Er zitten inmiddels zoveel haken en ogen aan mijn verhaal, dat het wellicht tijd wordt voor een pragmatische oplossing.

Ik had al een reserve plan op de plank liggen. Je kunt natuurlijk ook in elke Stored Procedure die een resultaat retourneert, een regeltje commentaar opnemen waar de metadata wordt beschreven die het formaat van de output beschrijft.

Maar zo’n proprietary oplossing wil je natuurlijk zo lang mogelijk uitstellen.

Behoorlijk wanhopig ga ik toch maar weer met de SQL Query Analyzer aan de slag. Ik bekijk voor de zoveelste keer de standaard Stored Procedure “sp_sproc_columns” in de Master database. Je zou qua naamgeving toch verwachten dat een dergelijke Stored Procedure de kolommen laat zien. Maar het enige resultaat, wat ik er uit kan halen, zijn de parameters. Ik denk nog eens goed na, het is toch niet voor niets een Query Analyzer. De enige manier om die gegevens boven water te halen is door de Query te analyseren.

Ik begin maar eens naar allerlei andere mogelijkheden van de Query Analyzer te kijken. En dan opeens zit ik te spelen met een Stored Procedure en ontdek dat je Extended Properties kan toekennen aan een Stored Procedure.

Klik met rechts op een Stored Procedure, in de SQL Query Analyzer en kies ‘Extended properties…’. Je krijgt dan een heel timide venstertje waar je eigen properties kan toevoegen c.q. verwijderen.

Hiermee kan ik hetzelfde bereiken als met mijn regeltje commentaar, alleen iets minder proprietary, want deze gegevens worden in de database opgeslagen. Ook als je een SQL script genereert worden deze netjes mee geleverd. Moet je wel een optie voor aanzetten.

En ik kan natuurlijk een tooltje ontwikkelen om de Extended Properties te zetten. Dan kan ik in ieder geval voorkomen dat er typefouten worden gemaakt met betrekking tot de datatypes en lengtes. Met behulp van de standaard Stored Procedure “sp_datatype_info” kun je alle datatypes van SQL Server opvragen om ze in een dropdownlist te gooien.

Eigenlijk niet eens zo gek, als je de vergelijking trekt met het object georiënteerd programmeren. Ik schrijf als het ware een interface die het formaat van de resultaat set beschrijft. Wat zijn de risico’s van deze werkwijze? Er zitten dezelfde risico’s aan als met interfaces. Een mismatch vormt pas een probleem als de applicatie draait. Wat is het risico voor de content in de database? Die zijn beperkt want het gaat alleen om selecties en niet om bewerkingen.

Conclusie

Het is mij niet gelukt alle benodigde metadata uit de database te halen. Ik denk dat het probleem veroorzaakt wordt, doordat een Stored Procedure meerdere resultaatsets kan retourneren.

Om een codelaag te genereren wil je de garantie dat een Stored Procedure altijd een zelfde resultaatset retourneert. Dit kun je alleen bereiken door je zelf een regel op te leggen die daar voor zorgt.

Om uit de database te halen wat het formaat is van de resultaatset van een Stored Procedure zul je metadata moeten toevoegen. Dit kan middels Extended Properties.

Om in je code verschillende implementaties te kunnen hanteren voor Stored Procedures met een verschillend gedrag moet je dit gedrag kunnen onderscheiden in de metadata. Het is mij niet gelukt om deze metadata uit de database te halen.

Als je alle metadata verzamelt door het SQL script te parsen kun je zeer nauwkeurig bepalen wat het gedrag is van een Stored Procedure.

Als je alle metadata verzamelt door te werken met de standaard Stored Procedures, zul je middels Extended Properties moeten toevoegen wat het gedrag is van een Stored Procedure om zo te bepalen welke implementatie moet worden gekozen bij het genereren van code.

Tips

• De standaard Stored Procedure [sp_tables] geeft je alle systeemtabellen, views en custom tabellen.
• De standaard Stored Procedure [sp_columns 'TabelNaam'] geeft je het formaat van de resultaatsets van tabellen en views.
• De standaard Stored Procedure [sp_indexes_rowset 'TabelNaam'] geeft je een overzicht van de indexen.
• De standaard Stored Procedure [sp_primary_keys_rowset 'TabelNaam'] geeft je een overzicht van de keys.
• De standaard Stored Procedure [sp_stored_procedures] geeft je alle Stored Procedures en functies.
• De standaard Stored Procedure [sp_procedure_params_rowset ‘ProcedureNaam’] geeft je het formaat van alle parameters.
• Met Extended Properties kun je metadata toevoegen aan elk object in de database.
• Om Extended Properties te kunnen toevoegen, updaten, en verwijderen zijn de volgende Stored Procedures beschikbaar:
• [sp_addextendedproperty]
• [sp_updateextendedproperty]
• [sp_dropextendedproperty]
• Om Extended Properties te kunnen tonen is er de functie [fn_listextendedproperty].