Object persistentie eenvoudig gemaakt

Inleiding

Bij het toepassen van objectoriëntatie in een ontwikkeltraject waarbij de domeinobjecten als klasse geïmplementeerd worden, speelt het probleem van object-relationele mappings. Vooral het ophalen en wegschrijven van gegevens is lastig, omdat er steeds een vertaalslag gemaakt moet worden van de representatie in de database naar de representatie in de applicatie. Het wordt nog lastiger op het moment dat men overerving gaat toepassen in de klassestructuur. Een voorbeeld van het probleem wordt gegeven in onderstaande afbeelding.

Fig. 1: Overerving in de klassestructuur

Het probleem dat hier ontstaat, is dat men bij het vertalen naar een tabel in een relationele database 6 kolommen nodig heeft terwijl er altijd slechts 4 ingevuld worden. Onderstaande tabel geeft een strokendiagram dat dit weergeeft.

Bij het opslaan van de gegevens in de database zullen kolommen gedefinieerd moeten worden zonder dat zij ooit gevuld worden. Bijkomend probleem is dat bij een kleine wijziging in het objectmodel, dit doorgevoerd moet worden in het relationele model in de database. Een soortgelijk probleem doet zich voor bij de implementatie van condities in werkprocessen. Het implementeren van vertakkingen kent een soortgelijk patroon.

Sinds SQL Server 2008 is het mogelijk om zgn. ‘sparse columns’ te definiëren. In dit artikel ga ik in op de mogelijkheden van deze functionaliteit voor object-relational mappings. Stapsgewijs geef ik uitbreidingen aan op de werkwijze in de vorm van SQL statements, te beginnen bij inserts en updates en eindigend bij stored procedures die in één enkele stap een lijst van objectdefinities implementeren. Waar nodig wordt de resultaatset getoond bij een opdracht.

Sparse columns

Sparse columns worden gedefinieerd voor gegevens in de database die niet altijd ingevuld worden (b.v. voor de vertakkingen zoals hierboven beschreven). In onderstaand commando ziet u hoe een tabel met sparse columns gedefinieerd wordt en een tweetal statements:

CREATE TABLE [dbo].[Person](
       [Id] [int] IDENTITY(1,1) NOT NULL,
       [status] [varchar](50) NOT NULL,
       [Name] [varchar](100) SPARSE  NULL,
       [Birthdate] [date] SPARSE  NULL,
       [Address] [varchar](50) SPARSE  NULL,
       [Place] [varchar](20) SPARSE  NULL,
) ON [PRIMARY]
GO

UPDATE Person
SET Birthdate = GETDATE()-(45*365)
WHERE Id = 1;

SELECT *
FROM Person;

In het Create table statement worden de sparse columns gewoon gedefinieerd als de andere kolommen met een toevoeging van het trefwoord sparse. Daarnaast moet de kolom gedefinieerd worden als NULL wat logisch is omdat de gegevens gebaseerd zijn op een vertakking.

Sparse columns worden opgeslagen als kolom van het type XML

Vervolgens kunnen de gegevens in de sparse columns gewoon gewijzigd en opgevraagd worden met de standaard SQL commando’s. Dat laatste is bijzonder, want fysiek worden de gegevens van de sparse columns helemaal niet in gewone kolommen opgeslagen maar in een kolom van het type XML. Dit maken we zichtbaar met de volgende twee commando’s.

CREATE TABLE [dbo].[Person](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [status] [varchar](50) NOT NULL,
  [Name] [varchar](100) SPARSE  NULL,
  [Birthdate] [date] SPARSE  NULL,
  [Address] [varchar](50) SPARSE  NULL,
  [Place] [varchar](20) SPARSE  NULL,
  [AllXML] [xml] COLUMN_SET FOR ALL_SPARSE_COLUMNS NULL
) ON [PRIMARY];
GO
SELECT *
FROM Person
GO

Verrassend is nu dat de resultaatset er als volgt uitziet:

Het blijkt dat nu bij een * wildcard niet meer de sparse columns getoond worden maar alleen de fysieke kolommen. Verder is te zien hoe de sparse columns in een XML-kolom worden opgeslagen als een XML-string. Wel is het mogelijk om de sparse columns te muteren en of op te vragen door ze op te nemen in de specificatie, zoals hieronder:

UPDATE Person
SET allxml = '<Name>Bert Dingemans</Name>
<Birthdate>1962-09-21</Birthdate>
<Address>Wildforster 37</Address>
<Place>Ede</Place>'
WHERE Id = 1
GO
SELECT Name, Address, Place, Birthdate
FROM Person
WHERE year(birthdate) < 1965

Het voorbeeld toont dat het mogelijk is om met een update statement de XML-kolom te muteren en vervolgens met de sparse columns de gegevens op te vragen en omgekeerd. Dit biedt hele interessante mogelijkheden voor object persistentie en het werken met stored procedures zoals je zult zien in de volgende paragraaf.

Stored procedures

Stored procedures in SQL Server hebben o.a. als voordeel dat zij meerdere opdrachten tegelijkertijd op de database kunnen uitvoeren. Met name in onze situatie is dat wenselijk. In het voorbeeldscript is een aantal voorbeeld stored procedures opgenomen. In dit artikel lichten we er twee uit die laten zien dat SQL Server 2008 extra functionaliteiten biedt.

In de eerste stored procedure wordt als parameter een XML-string gebruikt om de nieuwe waarden van een rij te bewerken.

CREATE PROCEDURE [dbo].[Person_bewerken_XML](
  @p_xml xml, @Id Int
)
AS
BEGIN
--exec Person_bewerken @xml = '#allxml', @Id=#id#
UPDATE [Person] SET [allxml] = @p_xml
,  STATUS = 'Person_bewerken_xml' 
WHERE id = @id

END
GO

De opzet is eenvoudig en bestaat uit een enkel statement dat de update uitvoert op basis van de primaire sleutel in de tabel; de status is een extra kolom die aangeeft welke methode als laatste is uitgevoerd op het object. Dit gegeven is relevant in onze situatie van objectpersistentie. Voordeel van het werken met een XML-string in plaats van met de afzonderlijke sparse columns is dat we ongestraft in onze applicatie elementen aan ons objectmodel kunnen toevoegen of muteren zonder dat dit gevolgen heeft voor de werking van onze stored procedure. Ook als we de gegevens willen opvragen in de toepassing voor het vullen van ons objectmodel, kunnen we dat opnieuw doen op basis van de XML-kolom. Alleen bij rapportages en het bevragen van ons model voor specifieke objecten maken we gebruik van de sparse columns in de SELECT- of WHERE-component van een statement.

SQL Server 2008 kent parameter-tables

Nadeel van deze opzet is dat je nog steeds per object naar de database moet om een bewerking uit te voeren op de tabellen in de database. Sinds SQL Server 2008 is dat niet meer nodig, het is nu nl. mogelijk om met parameter-tables te werken. Dat is een soort parameter-collection die je als read-only waarde kunt meegeven aan de stored procedure. Hiertoe dient een type gedefinieerd te worden en deze moet voor de aanroep van de stored procedure gevuld worden met de gewenste waarden. In het codevoorbeeld zie je de definitie en vervolgens 1 stored procedure aanroep die 3 rijen wijzigt in de tabel.

CREATE TYPE dla_parametertable
AS TABLE
(id int
, p_xml xml
, p_status varchar(50)
);
GO

CREATE PROCEDURE [dbo].[Person_aanmaken_valuepara](
@p_xml dla_parametertable readonly
 )
AS
BEGIN

DELETE FROM [Person];

INSERT INTO [Person] ([allxml], status )
SELECT p_xml, p_status FROM @p_xml

END
GO

declare @table dla_parametertable;
insert into @table values (1, '<Name>Bert Dingemans</Name>
<Birthdate>1962-09-21</Birthdate>
<Address>Rhodosdreef 154</Address>
<Place>Utrecht</Place>', 'Person_aanmaken_para')
, (2, '<Name>Anneke Hubert</Name>
<Birthdate>1965-10-15</Birthdate>
<Address>Rhodosdreef 154</Address>
<Place>Utrecht</Place>', 'Person_aanmaken_para')
, (3, '<Name>Jeroen Dingemans</Name>
<Birthdate>1995-08-31</Birthdate>
<Address>Rhodosdreef 154</Address>
<Place>Utrecht</Place>', 'Person_aanmaken_para');
exec dbo.person_aanmaken_valuepara @table;

Het voordeel van deze werkwijze zal duidelijk zijn: aan de client-side van de toepassing wordt dit statement opgebouwd en vervolgens is er slechts 1 aanroep naar de database die vervolgens een refresh uitvoert op de objecten die meegegeven worden aan de stored procedure aanroep.

Uitbreidingen van de werkwijze zijn natuurlijk denkbaar zoals het daadwerkelijk bijwerken van rijen in plaats van een delete en insert. Voorbeelden hiervan zijn te vinden op mijn website, waar ik werk aan een Object Relational Mapper in Vulcan.Net.

Samenvatting

Met de komst van SQL Server 2008 wordt een aantal nieuwe concepten geïntroduceerd die het mogelijk maken om op eenvoudige wijze Object Relational Mappers te introduceren. In het bijzonder sparse columns, XML columns en parameter tables worden in dit artikel uitgewerkt en toegelicht. Bij dit artikel is een voorbeeldscript opgenomen waarin extra voorbeelden zijn opgenomen en de source code als compleet script is uitgewerkt.

De sources die bij dit artikel horen kun je downloaden via Dingemans_SparseSparseKolomSQLServer2008_SRC.zip.