Datenbank Interface

Anleitungen & Tutorials

zur App im Store

Datenbank Interface SQL API

Mit der SQL-API der App Datenbank-Interface können Sie SQL-Befehle von extern absetzen um Daten aus tricoma auszulesen, einzutragen, aktualisieren und sogar zu löschen.

ACHTUNG: dieses Tutorial ist an Entwickler bzw. Techniker gerichtet, welche mindestens REST-API / SQL-Grundkenntnisse besitzen.
Um diese Funktion nutzen zu können, müssen Sie die zuerst in den "Allgemeinen Einstellungen" die Option "SQL-API aktivieren" aktivieren.

Ist der Haken gesetzt, können Sie optional kommagetrennt mehrere IP-Adressen angeben, welche auf die API zugreifen dürfen. So kann der Zugriff im Voraus bereits eingeschränkt werden.

Bei Timeframe geben Sie an, wie weit der übergebene Timestamp im Header ("X-tricoma-API-Timestamp") maximal von der Serverzeit +- abweichen darf. (Defaultwert = 120 Minuten)


Im nächsten Schritt müssen Sie ein Zugriffskonto anlegen. Hierzu wechseln Sie in den Reiter "Einstellungen » API-Zugriffskonten". Ist das Konto angelegt müssen Sie dort ein Passwort hinterlegen, welches später für das Erstellen eines Tokens benötigt wird (siehe hierzu Punkt 1 im Screenshot).

Je Konto können Sie separat einstellen, welche SQL-Statements über diesen Zugang abgesetzt werden dürfen. Die einzelnen Statements können Sie direkt in dieser Oberfläche aktivieren (siehe hierzu Punkt 2 im Screenshot).

Über "Details" können Sie einstellen, auf welche Tabellen einer App per API zugegriffen werden darf, so kann man z.B. pauschal den Zugriff auf alle Tabellen, welche der App Rechnungen zugeordnet wurden, verhindern.

Die API kann per cURL anprogrammiert werden. Ein Beispiel-Quellcode geschrieben in PHP können Sie hier herunterladen - vorausgesetzt, Sie haben die App bereits erworben.

Folgende Parameter müssen in der derzeitigen API v1 angegeben werden:
Bild zu: Einrichtung
Bild zu: Einrichtung
 Bild zu: Zugriffskonten
Bild zu: Zugriffskonten
Header:

Name: Wert:
Accept: application/vnd.tricoma.v1+json
Content-Type: application/json
X-tricoma-API-Version: z.B.: 1.0
X-tricoma-API-ID: ID des Zugriffskontos
X-tricoma-API-Timestamp: aktueller Unix-Timestamp/Zeitstempel
X-tricoma-API-Auth: der Token (siehe Beispiel-Quellcode)
Body:

Hier wird der Befehl als JSON-Repräsentation eines Arrays übergeben.

Das Array baut sich z.B: wie folgt auf:
  1. $data = array  
  2. (  
  3.     'statement' => 's',  
  4.     'select'    => array  
  5.     (  
  6.         '*'  
  7.     ),  
  8.     'from'      => array('rechnungen')  
  9. );  

Als erster Wert wird der Index "statement" angegeben. Folgende Werte sind derzeit möglich:

s SELECT
i INSERT
u UPDATE
d DELETE

Je nachdem ob das im Header angegebene Zugriffskonto Zugriff auf das ausgewählte Statement hat, wird die Anfrage verworfen oder weiter verarbeitet.

Verschiedene Statements ermöglichen / benötigen im Anschluss andere Parameter. Die Logik dahinter ist allerdings idR. immer die gleiche.

Bei Statement "s" gibt es folgende Indexe:

Name: Pflicht: Beschreibung:
select x Hier müssen die zu selektierenden Spalten angegeben werden.
Es ist sowohl die Wildcard * als auch einzelne Spalten (inkl. Alias) und Mathematischen Funkionen möglich.
from x Hier werden die Tabellen zu den zu selektierenden Spalten angegeben. 1
joins Hier können JOINS zusammen gebaut werden. 1
where Hier werden die WHERE Bedingungen angegeben.
group Hier kann die GROUP BY Bedingungen angegeben werden.
having Hier kann die HAVING Anweisungen angegeben werden.
order Hier kann die Sortierreihenfolge angegeben werden.
limit Hier können die Parameter für LIMIT angegeben werden.

Bei Statement "i" gibt es folgende Indexe:

Name: Pflicht: Beschreibung:
table x Hier wird die Tabelle angegeben, auf die der INSERT ausgeführt werden soll. 1
set x Hier müssen die Spalten inkl. Inhalte angegeben werden.

Bei Statement "u" gibt es folgende Indexe:

Name: Pflicht: Beschreibung:
table x Hier wird die Tabelle angegeben, auf die der UPDATE ausgeführt werden soll. 1
set x Hier müssen die Spalten inkl. Inhalte angegeben werden.
where Hier werden die WHERE Bedingungen angegeben.

Bei Statement "d" gibt es folgende Indexe:

Name: Pflicht: Beschreibung:
table x Hier wird die Tabelle angegeben, auf die der DELETE ausgeführt werden soll. 1
where x Hier müssen die WHERE Bedingungen angegeben werden.

1 ACHTUNG: befindet sich eine Tabelle einer App in diesem Wert, auf die das Zugriffskonto keine Freigabe hat, wird die Anfrage verworfen.
Index "select":

Im Array dieses Indexes wird im Key die Datenbankspalte und im Value der Alias angegeben. Wird kein Alias benötigt, kann die Value auch leer gelassen werden. Im Falle von JOINS sollte darauf geachtet werden, bei den zu selektierenden Felder die Tabelle als Präfix mit anzugeben.
Auch Aggregats-/Rechen-Funktionen sind teilweise möglich.

Beispiel:
  1. 'select'    => array  
  2. (  
  3.     '`bestellungen`.`ID`' => '',  
  4.     'bestellungen_positionen.produktid' => 'postenid',  
  5.     'ROUND(SUM(bestellungen_positionen.einzelpreis*bestellungen_positionen.anzahl),2)' => 'summe',  
  6.     'bestellungen_positionen.*'  
  7. )  
Ergibt folgenden SQL-Befehl:
  1. SELECT   
  2. `bestellungen`.`ID`,   
  3. `bestellungen_positionen`.`produktid` AS postenid,   
  4. ROUND(SUM(bestellungen_positionen.einzelpreis*bestellungen_positionen.anzahl),2) AS summe,   
  5. `bestellungen_positionen`.*  
Index "from":

Im Array dieses Indexes wird als Value der Tabellename angegeben

Beispiel:
  1. 'from'      => array('bestellungen', 'rechnungen'),  
Index "joins":

Im Array dieses Indexes wird im Key der JOIN-Typ und im Value ein weiteres Array mit den Anweisungen angegeben.

Folgende Typen sind derzeit möglich:

l LEFT JOIN
r RIGHT JOIN
i INNER JOIN

Werden mehrere JOINS mit dem selben Typ benötigt, kann ein numerischer Suffix an den Typ angehängt werden, da mehere identische Indexe im selben Array nicht immer möglich sind. z.B. i{1}, i{2}, i{3}

Im Array der Anweisungen wird im Key die Zieltabelle und in der Value die Verkettung, bestehend aus zwei Werten, angegeben. Wobei der zweite Wert deutlich detailierter angegeben werden kann, wenn nicht zwei Tabellenspalten mit einem = verkettet werden sollen, sondern SQL-Funktionen oder bzw. andere Vergleichsoperatoren benötigt werden.

Folgende Werte sind im zweiten Teil der Verkettung möglich:

Name: Beschreibung:
value der zu vergleichende Wert. Entweder eine Zabellenspalte z.B. "kunden.ID" oder ein String z.B. "97"
operator siehe Vergleichsoperatoren
function z.B: trim, isnull


Beispiel:
  1. 'joins'     => array  
  2. (  
  3.     'i' => array  
  4.     (  
  5.         'kunden_kat_zuordnung' => array  
  6.         (  
  7.             0 => array ('kunden_kat_zuordnung.kundennummer','kunden.ID')  
  8.         )   
  9.     ),  
  10.     'i{1}' => array  
  11.     (  
  12.         'kunden_felder_werte' => array  
  13.         (  
  14.             0   => array ('kunden_felder_werte.kundennummer','kunden.ID'),  
  15.             1   => array ('kunden_felder_werte.wert1', array('value' => 97, 'operator' => '_%','function' => 'trim'))  
  16.         )  
  17.     )  
  18. )  
Ergibt folgenden SQL-Befehl:
  1. INNER JOIN `kunden_kat_zuordnung` ON `kunden_kat_zuordnung`.`kundennummer`=`kunden`.`ID`  
  2. INNER JOIN `kunden_felder_werte` ON `kunden_felder_werte`.`kundennummer`=`kunden`.`ID` AND TRIM(`kunden_felder_werte`.`wert1`) LIKE '97%'  
Index "where":

Im Array dieses Indexes wird im Key / Value entweder die Spalte und der Vergleichswert oder im Value ein Weiteres Array, mit detailierter Angaben, angegeben.

Folgende detailierter Angaben sind im Array möglich:

Name: Beschreibung:
value der zu vergleichende Wert. Entweder eine Zabellenspalte z.B. "kunden.ID" oder ein String z.B. "97"
operator siehe Vergleichsoperatoren
function derzeit: trim, isnull, lower, upper, md5

Sollen mehrer Werte im WHERE per OR miteinander verknüpft werden, muss das Array (siehe erster Eintrag im Beispiel) nur weiter verschachtelt werden.

Beispiel:
  1. 'where'         => array  
  2. (   
  3.     0 => array('bestellungen_positionen.produktid'   => array('value' => 20000), 'bestellungen_positionen_sets.produktid'  => 20000),  
  4.     1 => 'bestellungen.status'                       => array('value' => 2, 'operator' => '>=','function' => 'trim'),  
  5.     'bestellungen.mandanten_ID'                     => 5  
  6. )  
Ergibt folgenden SQL-Befehl:
  1. WHERE   (`bestellungen_positionen`.`produktid`='20000' OR `bestellungen_positionen_sets`.`produktid`='20000')  
  2. AND     TRIM(`bestellungen`.`status`)>='2'  
  3. AND     `bestellungen`.`mandanten_ID`='5'  
Index "group":

Im Array dieses Indexes wird kurz und schmerzlos als Values die zu gruppierenden Tabellenspalten angegeben.

Beispiel:
  1. 'group'      => array('bestellungen.ID', 'rechnungen.ID'),  
Index "having":

Im Array dieses Indexes wird im Key die Spalte angegeben, welche benötigt wird und im Value ein Array mit detailierten Angaben.

Folgende Angaben sind im Value möglich:

Name: Beschreibung:
value der zu vergleichende Wert z.B. "42"
operator siehe Vergleichsoperatoren

Die Verkettung mehrerer HAVINGs erfolgt derzeit ausschließlich mit AND

Beispiel:
  1. 'having'        => array  
  2. (     
  3.     'summe'     => array('value' => 99.9, 'operator' => '<=' ),  
  4.     'summe{1}'  => array('value' => 1, 'operator' => '>=' )       
  5. )  
Ergibt folgenden SQL-Befehl:
  1. HAVING `summe`<='99.9' AND `summe`>='1'  
Index "order":

Im Array dieses Indexes wird im Key die Spalte angegeben und im Value die Sortierrichtung (ASC und DESC) .

Beispiel:
  1. 'order'         => array('kunden_felder_werte.wert1' => 'DESC','kunden.ID' => 'DESC')  
Ergibt folgenden SQL-Befehl:
  1. ORDER BY `kunden_felder_werte`.`wert1` DESC, `kunden`.`ID` DESC  
Index "limit":

Im Array dieses Indexes sind nur zwei Werte (Ganze, ntürliche Zahlen) erlaubt: Limit und Offset

Index "set":

Im Array dieses Indexes wird im Key die zu ändernde Spalte und als Values der Wert angegeben.

Beispiel:
  1. 'set'       => array  
  2. (  
  3.     'papierkorb'            => 0,  
  4.     'onlinefreigabe'        => 1  
  5. )  
Ergibt folgenden SQL-Befehl:
  1. SET `papierkorb` = 0, `onlinefreigabe` = 1  
Vergleichsoperatoren:

Folgende Angaben sind derzeit möglich:

Operator: Beschreibung:
%% Like-Suche mit beidseitigen Wildcards: Spalte LIKE '%Wert%'
%_ Like-Suche mit Wildcard am Anfang: Spalte LIKE '%Wert'
_% Like-Suche mit Wildcard am Ende: Spalte LIKE 'Wert%'
in IN ( ...) - Abfrage. Ist die dazugehörende Value ein Array ergit es Spalte IN ( 3.14, 42, 1337 ) ansonsten Spalte IN ( 3.14 )
!in NOT IN ( ...) - Abfrage. Funktionsweise analog zu "in"
= (defaultwert) Direkter Vergleich Spalte = Wert
Response:

Das Ergebnis wird als JSON-Repräsentation eines Arrays zurück geliefert.

Ein HTTP-Statuscode der Anfrage z.B.: 200, 404, etc. wird mitgesendet.

Das Ergebnis ist wie folgt aufgebaut:

Index: Beschreibung:
error Im Fehlerfall wird der Index "error" mit einer entsprechenden Fehlermeldung als Value zurück geliefert. z.B: 'error' => 'Unauthorized.'
success Läuft es erfolgreich durch, erhält man im Index "success" je nach Statement zwei Werte:

s "num_rows" mit der Anzahl an Ergebnissen und "result" mit dem eigentlichen Ergebnis, der vorherigen SQL-Abfrage.
i "insert_id" mit der soeben eingefügten Datensatz-ID.
u/d "affected_rows" mit der Anzahl an betroffenen Datensätzen.

Das JSON könnte dann wie folgt aussehen:
  1. "success":  
  2.    {  
  3.       "num_rows":"3",  
  4.       "result":  
  5.       {  
  6.          "0":  
  7.          {  
  8.             "ID":"40118482",  
  9.             "import_ID1":"",  
  10.             "kategorie":"42",  
  11.             "wert1":"97514"  
  12.          },  
  13.          "1":  
  14.          {  
  15.             "ID":"10210",  
  16.             "import_ID1":"",  
  17.             "kategorie":"23",  
  18.             "wert1":"97478"  
  19.          },  
  20.          "2":  
  21.          {  
  22.             "ID":"10060",  
  23.             "import_ID1":"",  
  24.             "kategorie":"23",  
  25.             "wert1":"97478"  
  26.          }  
  27.       }  
  28.    }  
Link für externe Aufrufe: https://tricoma.de/modul.php?modul=tricoma&modulkat=tutlink&ID=2152
 
Hersteller / Partner

tricoma AG

Zum Hersteller
tricoma AG
 

Über tricoma

Produkt

Lösungen

Branchen

Community

Kompatibel mit

Folge uns

  • Copyright © 2024 tricoma AG - Alle Rechte vorbehalten
  • Hinweis: * Alle Preise zzgl. Mehrwertsteuer
Einen Augenblick bitte...
Cookie und Tracking
Diese Webseite verwendet Cookies
Cookies werden zur Verbesserung der Benutzerführung verwendet und helfen dabei, diese Webseite besser zu machen.
Einstellungen