MS SQL Server je moje nejoblíbenější databáze a že jsem jich zažil už dost. Nepovažuji se za nějakého databázového specialistu, ale ostatní databáze mě vždycky něčím strašně štvaly. SQL Server mě dost často štve taky svými občas hloupými a nepochopitelnými omezeními, ale pokud něco neumí, většinou se to dá zaplácnout tím, že si danou funkcionalitu prostě dopíšu a dodělám sám. Není to úplně pohodlné, ale jde to a není to tak strašné.
Příkladně každá normální databáze má datové typy DATE a TIME, SQL Server má jen univerzální DATETIME. To by sám o sobě nebyl problém, pokud toho datový typ umí víc než potřebujete je rozhodně lepší, než když toho datový typ umí míň.
V aplikaci, kterou zrovna píšu, máme v jedné tabulce sloupec s časovou dotací. Je samozřejmě typu DATETIME a čas 0:00:00 je reprezentován hodnotou 1. 1. 1900 0:00:00. Krásně to funguje, když vynásobíte dvěma 1. 1. 1900 10:00:00, vyjde 1. 1. 1900 20:00:00, což je v pořádku.
A teď si představte jednoduchý dotaz:
SELECT [CustomerGuid], SUM([SolutionTime]) FROM [Items] GROUP BY [CustomerGuid]
Co nám SQL Server řekne? Pošle nás do háje s tím, že přes DATETIME teda jako SUM dělat nebude. No ale já ty časy prostě potřebuju sečíst a vím, že to vyjde správně, ať už si o tom SQL Server myslí, co chce.
Řešením je napsat si v C# nebo ve VB.NET vlastní agregační funkci, pojmenujme ji třeba SumTime. Ve Visual Studiu (musíte mít asi nějakou vyšší edici) si vytvoříte projekt typu SQL Server Project. Po odkliknutí průvodce jednoduše nastavíte databázi.
Aby mohla databáze využívat uživatelsky definované funkce psané v .NETu, je třeba ještě na straně databáze spustit tyto příkazy (ale Visual Studio vám o tom řekne samo).
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
No a kód uživatelsky definované funkce vypadá takto - musí být uvnitř struktury a je tam spousta omezení - neumí to členské proměnné typu DateTime (prý se špatně serializují), nelíbí se tomu statické proměnné, proto kód vypadá tak, jak vypadá.
V metodě Init připravíme samotnou agregaci. V metodě Accumulate přičteme parametr Value do naší agregované hodnoty, v metodě Merge je kód pro sloučení dvou agregovaných skupin a metoda Terminate vrátí výsledek.
[Serializable]
[SqlUserDefinedAggregate(Format.Native, Name = "SumTime")]
public struct SumTime
{
private long ticks;
private long minValueTicks;
public void Init()
{
ticks = 0;
minValueTicks = new DateTime(1900, 1, 1, 0, 0, 0).Ticks;
}
public void Accumulate(object Value)
{
if ((Value != DBNull.Value) && (Value is SqlDateTime))
{
DateTime add = ((SqlDateTime)Value).Value;
ticks += add.Ticks - minValueTicks;
}
}
public void Merge(SumTime Group)
{
ticks += Group.ticks;
}
public SqlDateTime Terminate()
{
return new SqlDateTime(new DateTime(1900, 1, 1, 0, 0, 0).AddTicks(ticks));
}
}
Zkompilujete, v menu Build vyberete Deploy a tím se funkce přidají do vámi zvolené databáze. Použití této funkce je úplně stejné jako použití SUM:
SELECT [CustomerGuid], [dbo].[SumTime]([SolutionTime]) FROM [Items] GROUP BY [CustomerGuid]