Server Functions in NDO verwenden

Viele Sql-Datenbanken unterstützen Server Functions. Die Funktionsaufrufe können an jeder Stelle in einer Abfrage verwendet werden, an der ein skalarer Wert verwendet werden kann:

SELECT * FROM Employee WHERE CONCAT(firstName, lastName) = 'Mirko Matytschak';

Im Sql Server gibt CONCAT einen NVarChar-Wert zurück, der dann wie ein String in der Query verwendet werden kann.

Die Anwendungsgebiete von Functions sind meist höchst Datenbank-spezifisch, aber es gibt auch Funktionen die auf mehreren Datenbanken mit dem gleichen Code funktionieren. Ein gutes Beispiel dafür sind die JSON-Funktionen. Die wichtigsten JSON-Funktionen wie JSON_QUERY und JSON_VALUE funktionieren auf dem Sql Server und auf MariaDb (=MySql) gleich.

Solche Functions können häufig durch clientseitigen Code nicht nachgebildet werden, daher haben wir uns entschlossen, sie ab der Version 4.0.6 in NDO zu unterstützen. In NDOql ist das ziemlich simpel:

var q = new NDOQuery<Employee>(pm, "CONCAT (firstName, lastName) = {0}";
q.Parameters.Add("Mirko Matytschak");
var result = q.Execute();

Der NDOql-Parser erkennt Funktionen an ihrer Syntax und reicht diese unverändert in den SQL-Code weiter. Die Parameter werden natürlich wie gewohnt von NDO als Identifier betrachtet und entsprechend in Column-Referenzen umgewandelt.

Server Functions mit Linq

Linq-Expressions werden vom Compiler überprüft. Das bedeutet, dass die Server Functions irgendwie als C#-Code deklariert werden müssen. Dies geschieht auf die einfachst mögliche Weise:

class ServerFunctions
{
    public static string CONCAT(string s1, string s2)
    {
        return null;
    }
}

Die Funktion CONCAT wird hier als statische Methode einer Klasse deklariert. Die Implementierung ist völlig egal, weil der Code ja nicht ausgeführt, sondern die Deklaration in SQL-Code übersetzt wird. Die Abfrage sieht in Linq dann so aus:

var result = pm.Objects<Employee>().Where(e => ServerFunctions.CONCAT(e.firstName, e.lastName) == "Mirko Matytschak").ResultTable;

oder entsprechend:

var result = from e in pm.Objects<Employee>() where ServerFunctions.CONCAT(e.firstName, e.lastName) == "Mirko Matytschak" select e;

Alias-Namen

SQL und NDOql sind typlos, sodass es völlig egal ist, welche Resultate von den Functions zurückgegeben werden. So sind die folgenden SQL-Expressions korrekt:

SELECT * FROM Employee WHERE JSON_VALUE(json, '$.intValue') = 42;
SELECT * FROM Employee WHERE JSON_VALUE(json, '$.stringValue') = 'Hi there!';

wenn in der json-Spalte folgender Ausdruck steht:

{ intValue: 42, stringValue: "Hi there!"}

Will man dies mit Linq in NDO realisieren, stellt sich das Problem, dass die CONCAT-Methode nur mit einem Rückgabetyp programmiert werden kann. Man kann nun zwei Klassen definieren, StringServerFunctions und IntServerFunctions, aber Sie sehen schon, dass das nicht sonderlich elegant ist. Die Lösung ist ein Attribut, das den Namen der Funktion angibt, wobei der Name der Methode beliebig sein kann:

class ServerFunctions
{
    [ServerFunction("JSON_VALUE")]
    public static string JsonValueAsString(string json, string path)
    {
        return null;
    }
    [ServerFunction("JSON_VALUE")]
    public static int JsonValueAsInt(string json, string path)
    {
        return 0;
    }
}

Nun lassen sich die Abfragen entsprechend formulieren:

var result = pm.Objects<Employee>().Where(e => ServerFunctions.JsonValueAsString(e.json, "$.intValue") == "Hi There!").ResultTable;
var result = pm.Objects<Employee>().Where(e => ServerFunctions.JsonValueAsInt(e.json, "$.intValue") == 42).ResultTable;

Datenabgleich Sql Server und IndexedDb

Alle modernen Browser haben eine clientseitige Datenbank, die IndexedDb. Damit lassen sich Daten vom Server holen und cachen, sodass zwischen den Sessions die Daten auf dem Client-System erhalten bleiben. Sie müssen dann nicht jedes Mal vom Server geladen werden, was die Server ziemlich entlastet. Wie kann man nun Datensätze vom Server laden, die sich in der Zwischenzeit geändert haben? Dafür gibt es im Sql Server den Column-Typ Rowversion. Das ist eigentlich ein long Integer, aber der Sql Server speichert ihn als Byte-Array mit 8 Bytes. Wenn Sie einen Long-Wert in ein Byte-Array umwandeln wollen, dann können Sie in C# so vorgehen:

ulong version = …
byte[] versionBytes = BitConverter.GetBytes( version ).Reverse().ToArray();

Die Bytereihenfolge muss umgekehrt werden, weil .NET mit little-endian-Integers arbeitet.

Solche Byte-Arrays können nun als Parameter an den Sql Server übergeben werden, wobei die Vergleichsoperatoren genauso funktionieren, wie bei skalaren Werten. Die Umwandlung von ulong in byte[] und umgekehrt ermöglicht es Ihnen, im Applikationskontext mit ulong-Werten zu arbeiten, die zum Beispiel von REST-Services als Hex-Strings weitergegeben und in JavaScript in BigInt-Objekte konvertiert werden können, wo sie dann wiederum Vergleichsoperationen ermöglichen.

In der IndexedDb speichern Sie nun die gecachten Datensätze und einen Extra-Datensatz der nur die Rowversion enthält. Für eine Abfrage der neuesten Datensätze muss aber noch etwas berücksichtigt werden: Sie möchten nämlich die Datensätze nur von bislang abgeschlossenen Transaktionen abfragen. Die maximale Rowversion, die Sie dafür abfragen können, wird mit der Funktion MIN_ACTIVE_ROWVERSION abgefragt. Sie können nun also folgende Methode für die ServerFunction deklarieren:

public class SqlServerFunctions
{
    [ServerFunction( "MIN_ACTIVE_ROWVERSION" )]
    public static byte[] MinActiveRowversion()
    {
        return null;
    }
}

Die Abfrage nach den neuesten Records könnte dann folgendermaßen aussehen:

public static IEnumerable<UserSettings> GetNewerThan( Guid userGuid, ulong version )
{
    byte[] versionBytes = BitConverter.GetBytes( version ).Reverse().ToArray();
    PersistenceManager pm = new PersistenceManager();
    return pm.Objects<UserSettings>().Where(
    u => u.UserGuid == userGuid
        && u.rowVersion.GreaterThan( versionBytes )
        && u.rowVersion.LowerThan( SqlServerFunctions.MinActiveRowversion() )
    ).ResultTable;
}

Sie erhalten mit dieser Abfrage nur die Objekte, deren Rowversion größer als die angegebene Version ist.

Sie sehen, dass in Linq hier die Methoden GreaterThan und LowerThan verwendet werden müssen, weil für Byte-Arrays in C# die Operatoren < und > nicht definiert sind. Für Strings gilt übrigens das gleiche. Diese Methoden sind im Namespace NDO.Linq definiert.

Ohne Server Functions hätten wir keine Chance, korrekte Ergebnisse zu erhalten, da die Abfrage nach oben keine Begrenzung hat und daher Rowversion-Werte berücksichtigt, die zu noch nicht abgeschlossenen Transaktionen gehören. Die Beschreibung, warum das im Sql Server so kompliziert ist, würde den Rahmen dieses Artikels sprengen. Aber dass man auf diese Art einen verlässlichen Abgleich von Datenbeständen vornehmen kann, zeichnet den Sql Server gegenüber anderen Datenbanken wiederum aus. In MariaDb habe ich eine solche Funktionalität vergebens gesucht.

Falls bei Ihnen jetzt die Frage aufkommt, warum man nicht einfach Integer-IDs verwendet, da die neuen Datensätze doch höhere IDs haben, als die alten Datensätze: Sie wollen ja auch mitbekommen, wenn sich ein Datensatz geändert hat. Geänderte Datensätze haben nach dem Speichern eine neue RowVersion. Die ID des Datensatzes bleibt gleich, die RowVersion wird höher.

Read-Only-Felder

Weil die Rowversion-Spalten nicht vom Client beschrieben werden können, müssen wir NDO noch sagen, dass diese Spalten nur vom Server geschrieben werden. Aus Sicht von NDO sind diese Spalten Read-Only. Das darf nicht mit dem readonly-Modifizierer von C# verwechselt werden. Die Variablen, die die RowVersion-Werte enthalten, müssen beschreibbar sein, da sonst die Werte vom Server nicht gelesen werden können. Für alle Felder, deren Werte vom Server erzeugt werden, müssen Sie daher das Attribut NDOReadOnly (seit NDO 4.0.7) setzen:

[NDOReadOnly]
byte[] rowVersion = null;  // Initialisieren, da sonst der Compiler meckert, dass niemand in die Variable schreibt

Beim Anlegen (Insert) und beim Update werden diese Felder nicht berücksichtigt.

Das ist aber noch nicht alles, worauf geachtet werden sollte. Standardmäßig wird der C#-Typ byte[] auf den Datenbanktyp Image gemappt. Dies können Sie mit dem Column-Attribut ändern:

[Column( DbType = "rowversion" )]
[NDOReadOnly]
byte[] rowVersion = null;

Damit wird der korrekte DDL-Code erzeugt, mit dem Sie die Tabellen in Sql Server anlegen können, sowie beim Übertragen der RowVersion-Werte bei Abfragen der richtige Parametertyp verwendet.

Die komplette Lösung

Die komplette Lösung einer Klasse, die Row-Versions benutzt, sieht dann so aus: