HTTP: OData v4 (Referenz)

Die OData-v4-Oberfläche unter /odata/v4/ – für Access über den ODBC-Treiber, Excel und Power BI über Power Query und jeden anderen OData-Client. Sie liest Tabellen und Sichten, schreibt einzelne Sätze, fasst über $apply zusammen und beschreibt sich selbst im Schemadokument.

Eine zweite Oberfläche über denselben Katalog, für Clients, die OData v4 sprechen statt des eigenen Vertrags: Power Query in Excel und Power BI, und ein ODBC-Treiber für OData, über den Access Tabellen verknüpft. Ein solcher Client lernt alles aus dem Schemadokument und folgt den Anmerkungen des Protokolls. Die Abmachung window oder den Schlüssel im Abfrageteil des eigenen Vertrags kennt er nicht. Anmeldung, Freigaben, Ratenbegrenzung, Audit und Fehlerform sind dieselben wie auf dem eigenen Vertrag (siehe HTTP: Fehlerform und HTTP: Anmeldung).

PUT beantwortet der Dienst mit 405, wie auf dem eigenen Vertrag auch.

Welche Datenbanken und Schemata ein Aufrufer nutzen kann, sagen die beiden Listen über den Dienstwurzeln – für Clients, die eine Auswahl anbieten, etwa den Verbindungsdialog des ODBC-Treibers wie die Datenbankliste im Dialog von SQL Server. Es ist ein gewöhnliches Dienstdokument des Protokolls, dessen Einträge die Art ServiceDocument haben – die Art, die OData für den Verweis auf einen weiteren Dienst vorsieht (OData JSON Format 4.01, Abschnitt „Service Document“). Ein generischer Client kann ihnen folgen, ohne diesen Dienst zu kennen:

{"@odata.context":"https://daten.example.com/odata/v4/$metadata",
 "value":[{"name":"fachdaten","kind":"ServiceDocument","url":"fachdaten/"}]}

Geschrieben wird in Tabellen und Sichten, und immer genau ein Satz. Die Anweisung entsteht aus den Spalten des Katalogs, wie beim eigenen Vertrag; dieselben Regeln gelten unverändert. Der Aufrufer bestimmt den Satz (über den Schlüssel in der Adresse) und die Werte (über den Rumpf), nie eine Bedingung. Fehlt der Schlüssel in der Adresse, wird die Anfrage abgewiesen, statt die ganze Menge zu treffen. Eine Sicht nimmt eine Änderung an, wenn die Regeln des Aufrufers Sichten mit diesem Verb erlauben und SQL Server die Sicht für änderbar hält – wie beim ODBC-Treiber von SQL Server, über den Access verknüpfte Sichten bearbeitet; lehnt SQL Server ab, antwortet der Dienst 422. Spalten einer Sicht, die SQL Server nicht aktualisieren kann – eine skalare Unterabfrage, ein Ausdruck, eine Zusammenfassung –, meldet der Dienst als schreibgeschützt (Core.Computed); eine Änderung, die eine von ihnen nennt, weist er mit 400 und dem Namen der Spalte ab. Ausgenommen sind Sichten mit einem INSTEAD OF-Trigger: Dort entscheidet der Trigger, was eine solche Spalte bedeutet. Access übernimmt diese Angabe nicht: Eine verknüpfte Sicht bietet eine solche Spalte weiter zum Bearbeiten an, mit diesem Treiber wie mit dem ODBC Driver 18 von SQL Server, und wer sie ändert, verliert mit der Ablehnung auch die übrigen Änderungen des Satzes. Wo Benutzer in Access bearbeiten, verknüpfen Sie deshalb die Basistabelle zum Schreiben und die Sicht nur zum Lesen. Der eigene Vertrag (cf) schreibt nur in Tabellen.

Jeder Schreibvorgang nennt einen Satz über seinen vollständigen Schlüssel. Die einzige Ausnahme sind freigegebene Tabellen ohne Schlüssel: Dort nennt der Aufrufer den Satz über seine alten Werte, und auch dann wird genau ein Satz geschrieben.

Die Antwort ist der gespeicherte Satz mit seinem neuen ETag; beim Anlegen zusätzlich die Adresse des neuen Satzes im Kopf Location. So kennt der Aufrufer Identitätsschlüssel, Vorgabewerte und berechnete Spalten, ohne den Satz noch einmal lesen zu müssen.

Ein Schlüssel, den die Datenbank selbst vergibt – als Identität oder per Vorgabe wie NEWSEQUENTIALID() –, darf beim Anlegen fehlen; die Antwort und Location nennen den vergebenen Wert.

Ein Zeitpunkt mit Zone wird für eine Spalte ohne Zone in koordinierte Weltzeit umgerechnet und als Wanduhrzeit gespeichert: …T17:00:00Z als 17:00, …T17:00:00+02:00 als 15:00. Abgewiesen wird er hier nicht (anders als auf der eigenen Route); siehe Zeitpunkte, Zahlen und Namen.

Felder mit @ im Namen – @odata.etag, @odata.type – werden übergangen statt abgewiesen. Ein Client, der einen gelesenen Satz zurückschickt, führt sie mit; sie sind keine Spalten.

Ein Dienststamm je Alias und Schema. Eine Entitätsmenge heißt in OData wie ein einfacher Bezeichner, und die Objekte einer Datenbank sind nur je Schema eindeutig. Mit dem Schema im Stamm heißt die Menge genau wie das Objekt (APPARAT), ohne ein Trennzeichen, das auch in einem echten Namen vorkommen könnte.

Das Schemadokument gilt je Aufrufer. Es nennt Tabellen, wenn der Aufrufer Tabellen des Alias lesen darf, und Sichten, wenn er Sichten lesen darf – nichts, was ihm verweigert würde. Funktionen und Prozeduren erscheinen nicht. Ein Schema ohne lesbares Objekt, auch ein vertipptes, antwortet mit 404 und nicht mit einem leeren Dienst.

let
    Quelle = OData.Feed("https://gateway.example.com/odata/v4/fachdaten/dbo/", null,
        [Implementation = "2.0", Headers = [#"X-Api-Key" = "…"]]),
    Apparate = Quelle{[Name = "APPARAT"]}[Data],
    Keller = Table.SelectRows(Apparate, each Text.Contains([Benennung], "Keller"))
in
    Keller

Der Schlüssel steht in diesem Beispiel im Klartext in der Abfrage und damit in jeder Kopie der Mappe. Das ist eine Grenze der Anmeldung per Kopfzeile, nicht dieser Oberfläche.

Mit der Windows-Anmeldung entfällt die Kopfzeile: OData.Feed("https://gateway.example.com/odata/v4/fachdaten/dbo/", null, [Implementation = "2.0"]), dazu im Anmeldedialog von Power Query die Anmeldeart „Windows“.

Schlüssel. Schlüssel einer Menge ist der Primärschlüssel, ersatzweise der erste eindeutige Index – aber nur, wenn keine seiner Spalten leer sein darf und alle einen Typ haben, den das Protokoll als Schlüssel zulässt. Eine Sicht ohne eigenen Index bekommt den Schlüssel, den SQL Server für ihr Ergebnis nennt (sp_describe_first_result_set im Browse-Modus, is_part_of_unique_key) – dieselbe Angabe, aus der der ODBC-Treiber von SQL Server den Schlüssel einer Sicht ableitet. Übernommen wird er nur, wenn die Sicht alle diese Spalten zeigt: Muss SQL Server eine Schlüsselspalte einer Basistabelle ergänzen, die die Sicht nicht auswählt (ein Verbund), identifizieren die sichtbaren allein keinen Satz. Eine Sicht ohne solchen Schlüssel wird ohne Schlüssel angeboten: lesbar und filterbar, aber kein einzelner Satz adressierbar. Das Protokoll erwartet eigentlich einen; ohne diese Ausnahme wären gerade die Sichten unerreichbar, über die ein Client lesen soll.

Beschrieben wird eine Sicht nach ihrem Ergebnis, nicht nach ihrer gespeicherten Spaltenliste. Eine Sicht über SELECT *, deren Tabellen sich seither geändert haben, behält die alte Liste, bis jemand sp_refreshview ausführt, und SQL Server liefert die neuen Spalten unter den alten Namen; Werte kommen dann unter falschen Spaltennamen und mit falschem Typ an. Der Dienst beschreibt deshalb jede Sicht über sys.dm_exec_describe_first_result_set, wie der ODBC-Treiber ein Ergebnis beschreibt, und schreibt einmal je Sicht eine Warnung ins Protokoll.

Was der Dienst über die Datenbank sagt. Was OData nicht kennt, ein Client aber braucht, um sich gegenüber einer Anwendung wie der Treiber des Produkts zu verhalten, steht als Annotation eines eigenen Vokabulars in $metadata. Das Vokabular (Namensraum Wkl.Store.V1, Alias Store) liegt ohne Anmeldung unter /odata/v4/$vocabulary/Wkl.Store.V1.xml; ein Client, der es nicht kennt, übergeht die Annotationen und verliert nichts.

Power Query in Excel für Microsoft 365 liest das erweiterte Schemadokument unverändert.

Zeilen und Seiten sind Planungshilfen, keine Zählung. Access liest sie beim Verknüpfen über SQLStatistics und plant danach seine Verbünde. Ohne Seitenzahl schlägt Access bei einem Verbund die Zeilen einer Tabelle unter Umständen einzeln über den Schlüssel nach, je eine Anfrage für jede Zeile der anderen Seite; mit Seitenzahl schickt es den Verbund in einer Anweisung. Die Zeilenzahl allein ändert den Plan nicht. Beide Zahlen stammen aus Katalogsichten, die SQL Server laufend führt; ihr Lesen kostet unabhängig von der Tabellengröße gleich wenig und braucht kein zusätzliches Recht.

Die Schlüsselangabe Menge(4711) oder Menge(Auftrag=7,Zeile='a') wird mit der Filtergrammatik gelesen und danach auf ihre Form geprüft: genau ein Gleichheitsvergleich je Schlüsselspalte, sonst nichts. APPARAT(1 or nummer ne 0) ist als Ausdruck gültig und träfe jede Zeile; es wird mit 400 abgewiesen.

Typen und kodierte Namen: Zeitpunkte, Zahlen und Namen.

Was Power Query zeigt (Excel, OData.Feed mit Implementation="2.0"):

Zeilenversion. Führt eine Tabelle eine, trägt jeder Satz sie als @odata.etag (W/"<Base64>"), ein Einzelsatz zusätzlich im Kopf ETag. Das Schemadokument kündigt die Spalte mit Core.OptimisticConcurrency an. Der Wert ist derselbe, den If-Match beim Schreiben annimmt. Für bearbeitbare Tabellen ist die Spalte keine Kür: Ein Client, der einen Satz ändern will, hat sonst nichts, woran er eine Vorbedingung knüpfen kann. Der OData-ODBC-Treiber dieses Produkts weist deshalb Ändern und Löschen ohne Zeilenversion ab, statt eine fremde Änderung stillschweigend zu überschreiben. Für eine Tabelle ohne Zeilenversion tritt der Zeilenstempel an ihre Stelle, wenn er für sie freigeschaltet ist.

Folgeseiten bietet der Dienst nach denselben Regeln wie nextLink im eigenen Vertrag an, hier als @odata.nextLink und als absolute Adresse, wie das Protokoll sie vorsieht. Hinter einem vorgeschalteten Proxy muss dieser deshalb Schema und Host weiterreichen (X-Forwarded-* mit der zugehörigen Middleware); sonst zeigen Kontext- und Folgeadressen auf den Host, den dieser Prozess sieht.

Ist die Menge nach ihrem Schlüssel geordnet – kein $orderby des Aufrufers, ein Schlüssel aus ganzen Zahlen, Text oder uniqueidentifier, alle Schlüsselspalten ausgewählt –, setzt die Folgeseite nach dem Schlüssel der letzten Zeile fort ($skiptoken, etwa ?$skiptoken=3691 oder ?$skiptoken=Auftrag=7,Zeile='a') statt nach einer Zeilenzahl ($skip). $skip wird zu OFFSET … ROWS, und SQL Server zählt für jede Seite alle vorigen Zeilen erneut ab; jede Seite dauert dann länger als die vorige. Mit $skiptoken bleibt die Zeit je Seite gleich. Das Token ist für den Client undurchsichtig; der Dienst liest es wie die Schlüsselangabe einer Adresse und weist es für eine anders geordnete Leseanfrage mit 400 ab. In allen anderen Fällen bleibt es bei $skip. Ein Dezimalschlüssel ist bewusst ausgenommen: Über die Filtergrammatik gelesen, könnte sein Wert gerundet werden.

Folgeseiten zählen nicht gegen die Anfragengrenze (Gateway:Security:RateLimit) – hier wie im eigenen Vertrag. Jede Folgeadresse trägt dazu die Option wkl-page, einen Prüfwert über Aufrufer, Pfad, alle übrigen Optionen und einen Ablaufzeitpunkt (15 Minuten). Der Schlüssel dafür entsteht zufällig beim Start des Prozesses und verlässt ihn nie. Grund: Ein Client liest eine große Menge Seite für Seite, jede Seite ist eine Anfrage, und ein Abbruch mitten im Lesen mit 429 ließe Access eine Tabelle unvollständig mit „ODBC-Aufruf fehlgeschlagen“ zurück. Gezählt wird deshalb nur der Beginn eines Lesevorgangs. Die Regeln:

Abgewiesen wird wegen wkl-page nie: Ein ungültiger Prüfwert hebt nur die Befreiung auf; Anmeldung und Rechte prüft der Dienst bei jeder Seite wie sonst. Die Option erscheint nur in Adressen, die der Dienst selbst bildet; ein Client reicht sie ungelesen weiter (sie beginnt nicht mit $).

Eine Abweichung vom Protokoll und eine Ergänzung gehen auf das Verhalten von Power Query zurück:

Erweiterung dieses Dienstes, nicht Teil von OData. OData sieht für mehrere Änderungen in einem Schritt den Stapel $batch mit Änderungsgruppen vor; eine Transaktion über mehrere einzelne Anfragen, wie sie ODBC-Anwendungen erwarten, kennt es nicht. Ein generischer OData-Client nutzt diese Erweiterung nicht; sie ist für den ODBC-Treiber und Programme gedacht, die sie ausdrücklich ansprechen.

Ein Client klammert mehrere Anfragen in einer Transaktion, wie eine Anwendung über den Treiber von SQL Server mit BeginTrans/CommitTrans/Rollback. Der Dienst kündigt das im Schemadokument an (Store.Transactions am Entitätscontainer, die Adresse unten); der ODBC-Treiber meldet Transaktionen nur dann.

{ "Name": "fachdaten", "Provider": "sqlserver", "ConnectionString": "…",
  "KeylessWriteTables": [ "dbo.Protokoll" ] }

Die Formen der Einträge sind dieselben wie bei RowStampTables (schema.tabelle, schema.*, *), ebenso die Prüfung beim Start. Eine gelistete Tabelle, die doch einen Schlüssel hat, wird weiter über ihn geschrieben und im Protokoll genannt.

Über das Protokoll geschieht das mit zwei gebundenen Aktionen, die das Schemadokument nur für freigegebene Tabellen deklariert. Ein Client erkennt daran, ob die Tabelle beschreibbar ist:

POST /odata/v4/fachdaten/dbo/Protokoll/UpdateByOriginal
{ "original": { "Nummer": 2, "Text": "zwei", "Betrag": null, "Aktiv": false },
  "changes":  { "Text": "neu" } }
 
POST /odata/v4/fachdaten/dbo/Protokoll/DeleteByOriginal
{ "original": { … } }

Der Aktionsname darf mit dem Namensraum des Schemas stehen (fachdaten.dbo.UpdateByOriginal), wie das Protokoll ihn schreibt. Geprüft wird als das, was die Aktion tut: Ändern braucht das Recht für PATCH, Löschen das für DELETE. Ein Recht nur zum Anlegen öffnet diese Tür nicht.

Antwort bei Erfolg: 200 mit {"value": 1}.

Eine Menge lässt sich gruppiert und zusammengefasst lesen, nach OData Data Aggregation 4.0. Grund ist Access: Das Datenbankmodul schickt SUM, COUNT(Spalte), MIN, MAX, GROUP BY, HAVING, DISTINCT und für eine Standardabweichung SUM(x * x) über eine verknüpfte Tabelle an den ODBC-Treiber, gleich was dieser über sich meldet, und rechnet nicht selbst, wenn der Treiber ablehnt – die Abfrage scheitert dann mit „ODBC-Aufruf fehlgeschlagen“. Ohne Durchreiche, also für jeden Schlüssel ohne das Recht sql, führen $apply und – wo der Alias sie annimmt – geprüfte Anweisungen zu Summen und Gruppen. Er ist ein Lesen: Es gelten die Leserechte der Menge, und eine Summe über eine Million Zeilen reist als eine Zahl.

GET …/Auftrag?$apply=groupby((Werk),aggregate($count as Anzahl,Menge with sum as Summe))
GET …/Auftrag?$apply=filter(Werk eq 3)/aggregate(Menge with average as Mittel)
GET …/Rolle?$apply=groupby((Abteilung),aggregate($count as N))&$filter=N gt 1&$orderby=N desc&$top=10
GET …/Rolle?$apply=compute(Id mul Id as Q)/aggregate(Q with sum as Quadratsumme)
GET …/Rolle?$apply=groupby((Abteilung))                          verschiedene Werte (DISTINCT)

{"@odata.context":"…/$metadata#Auftrag(Werk,Anzahl,Summe)",
 "value":[{"Werk":1,"Anzahl":2140,"Summe":18350.5}, …]}

Was $apply ausführt – in dieser Reihenfolge, jede Transformation höchstens einmal:

Nach $apply beziehen sich die übrigen Optionen auf die Gruppen, wie das Protokoll es vorsieht: $filter ist SQL HAVING und darf einen Alias nennen ($filter=N gt 1), $orderby ordnet die Gruppen, $top, $skip und $count=true fenstern und zählen sie. Sie nennen nur, was das Ergebnis hat – eine Gruppierungseigenschaft oder einen Alias; eine andere Spalte der Menge weist der Dienst mit 400 ab. Ohne $orderby sind die Gruppen nach ihren Gruppierungseigenschaften geordnet, sodass aufeinander folgende Fenster sich nicht überschneiden.

Das Schemadokument kündigt an, was ausgeführt wird: Aggregation.ApplySupported mit den vier Transformationen und ohne Rollup, dazu die eigene Zählmethode. Der ODBC-Treiber gruppiert nur über einen Dienst, der das ankündigt. Auf der eigenen Route (/api/v1/cf/…) bleibt $apply abgewiesen.

Die Anweisung ist eine abgeleitete Tabelle, aus der die Gruppen wie Zeilen gelesen werden:

SELECT TOP (100) * FROM (
    SELECT [Kunde], COUNT_BIG(*) AS [Anzahl] FROM [dbo].[Umsatz] WHERE [Nr] > @p0 GROUP BY [Kunde]
) AS [gw_g] WHERE [Anzahl] > @p1 ORDER BY [Anzahl] DESC

Nur so kann die Bedingung auf die Gruppen einen Alias nennen und gilt für das Fenster dieselbe Logik wie beim gewöhnlichen Lesen. Werte gehen wie überall als Parameter hinein; Zahlen aus compute schreibt der Dienst aus dem gelesenen Wert, nie aus dem Text des Aufrufers.

• HTTP: eigener Vertrag (Referenz) · Anmeldung
• Daten in Excel mit Power Query laden
• Zeitpunkte, Zahlen und Namen