Arbeiten mit SQL Abfragen
Cloud Abo
On-Premises
SQL Abfragen empfehlen sich immer dann, wenn Daten gefiltert (vorselektiert) ins Vertec geladen werden sollen. Während via OCL alle Objekte ins Memory geladen und erst dann gefiltert werden, wird bei SQL bereits auf der Datenbank gefiltert und nur die nur die resultierenden Objekte ins Memory geladen.
Siehe dazu den Artikel Performanceoptimierter Zugriff auf Vertec Objekte.
SQL Abfragen können in Vertec an verschiedenen Orten abgesetzt werden:
In Vertec wird bei SQL Abfragen nur angegeben, welche Objekte man in eine Liste bringen will, sowie das Filterkriterium. Den Rest erledigt das Objektsystem. Es werden also niemals Statements der Art "SELECT * FROM Project WHERE" abgesetzt, sondern nur eine Klasse (für das SELECT Statement) und die Filterkriterien (für die WHERE Clause).
Im unteren Teil des Artikels finden sich noch Informationen zu
In SQL-Ordnern definiert man das SQL Statement mittels Angabe einer Klasse sowie einem SQL Kriterium:
Invoicecharged=1 AND paid=0Daraus kreiert Vertec dann im Hintergrund die eigentliche SQL-Expression und setzt sie auf der Datenbank ab.
Das bedeutet: Möchte man zum Beispiel eine SQL Abfrage SELECT * FROM Invoice WHERE charged=1 AND paid=0 absetzen, kreiert man einen SQL-Ordner mit der Klasse Invoice und dem SQL charged=1 AND paid=0.
Eine allfällige Sortierung wird dann in den Listeneinstellungen vorgenommen.
Es gibt diverse Vertec Python Funktionen, um Daten via SQL abzufragen. Die wichtigste ist vtcapp.getwithsql(). Die Syntax hier lautet:
vtcapp.getwithsql(klasse: string, where: string, order: string, [fieldname: string, objectlist: list])
Auch hier sieht man schon, dass die eigentlich SQL Abfrage aus den verschiedenen Parametern zusammengesetzt wird. Es wird also nicht einfach ein SQL Statement übergeben, sondern die einzelnen Kriterien wie folgt zusammengesetzt:
Ein vollständiger Aufruf sieht zum Beispiel wie folgt aus:
rechnungen = vtcapp.getwithsql('Invoice', 'charged=1 AND paid=0', 'entrydate')
Daraus kreiert Vertec dann die eigentliche SQL Abfrage und setzt sie auf der Datenbank ab.
Das bedeutet: Möchte man zum Beispiel eine SQL Abfrage SELECT * FROM Invoice WHERE charged=1 AND paid=0 ORDER BY entrydate absetzen, gibt man in Vertec Python vtcapp.getwithsql('Invoice', 'charged=1 AND paid=0', 'entrydate') ein.
Oder, wenn man z.B. nur die bezahlten Rechnungen von eigenen Projekten laden will, mit optionalem Filterkriterium:
eigeneProjekte = argobject.evalocl("ownprojects") rechnungen = vtcapp.getwithsql('Invoice', 'charged=1 AND paid=0', 'entrydate', 'project', eigeneProjekte)
Das bedeutet: Für die SQL Abfrage SELECT * FROM Invoice WHERE charged=1 AND paid=0 AND project IN (SELECT BOLD_ID FROM Project WHERE projectmanager=12345) ORDER BY entrydate gibt man in Vertec Python vtcapp.getwithsql('Invoice', 'charged=1 AND paid=0', 'entrydate', 'project', argobject.evalocl('ownprojects')) ein.
Auch bei Anfragen an das Vertec REST API kann SQL abgesetzt werden.
Dabei werden die Queries direkt auf der Klasse abgesetzt. Mit dem Query-Parameter sql wird dann das Filterkriterium übergeben:
<ServerURL>/api/webservice/REST/objects/Invoice?sql=charged=1 AND paid=0
Das bedeutet: Möchte man zum Beispiel eine SQL Abfrage SELECT * FROM Invoice WHERE charged=1 AND paid=0 absetzen, gibt man bei der Anfrage ans REST API /objects/Invoice?sql=charged=1 AND paid=0 ein.
Eine allfällige Sortierung kann direkt in die WHERE Clause eingegeben werden: /objects/Invoice?sql=charged=1 AND paid=0 order by entrydate.
Auch die Vertec XML Schnittstelle lässt SQL Abfragen zu.
Dabei werden die Kriterien in der <Selection> mit folgenden Unterelementen übergeben:
<ocl>: Name der Klasse der Objekte, die man laden möchte. <sqlwhere>: Hier wird das Filterkriterium angegeben, welches auf die unter <ocl> genannten Klasse angewandt wird.<sqlorder>: Soll die Liste sortiert ausgegeben werden, kann optional noch das Attribut angegeben werden, nach welchem sortiert werden soll.Das bedeutet: Möchte man zum Beispiel eine SQL Abfrage SELECT * FROM Invoice WHERE charged=1 AND paid=0 ORDER BY entrydate absetzen, gibt man bei der Anfrage an die XML Schnittstelle
<Selection>
<ocl>Invoice</ocl>
<sqlwhere>charged=1 AND paid=0</sqlwhere>
<sqlorder>entrydate</sqlorder>
</Selection>
ein.
Seit Vertec 6.8 ist die Datenbank auf Englisch angelegt, die aktuelle Modellsprache in Vertec kann Deutsch oder Englisch sein. Was bedeutet das für SQL Abfragen?
Da die Abfragen immer von Vertec zusammengestellt und nie direkte SQL Statements auf der Datenbank abgesetzt werden, ist die gute Nachricht, dass es grundsätzlich nicht draufankommt, ob die Klassen- und Membernamen in Deutsch oder Englisch angegeben werden. Vertec hat ein internes Übersetzungssystem und die Angaben können sogar gemischt werden:
vtcapp.getwithsql("Rechnung", "charged=1 AND bezahlt=0", "")
Auch um das Datenmodell muss man sich dabei nicht kümmern. Man kann als Klasse Leistung angeben, obwohl es keine Tabelle dieses Namens gibt, sondern nur die Tabellen OffeneLeistung und VerrechneteLeistung. Vertec macht das automatisch richtig und gibt im Resultat sowohl offene als auch verrechnete Leistungen aus.
Natürlich muss man richtigen Members für das Filterkriterium kennen. Diese findet man im Vertec Model Browser oder auch im OCL Expression Editor. Wichtig ist hierbei zu wissen, dass man keine berechneten Datenfelder (Derived Attributes) abfragen kann, da diese zu Laufzeit berechnet werden und deshalb eben nicht in der Datenbank gespeichert sind.
Eine Rolle spielt das Datenmodell hingegen in Subqueries, also in Unterabfragen innerhalb der WHERE Clause.
Hier als Beispiel für eine Unterabfrage alle Projekte mit provisorischen Rechnungen:
vtcapp.getwithsql("Project", "bold_id in (select project from Rechnung where charged=0)", "")
Bei der Selektion auf eine weitere Klasse (Tabelle), im Beispiel from Rechnung, können nur Tabellen abgefragt werden, die es wirklich in der Datenbank gibt. An dieser Stelle würde beispielsweise eine Abfrage auf Leistung bzw. Service schiefgehen, da es keine solche Tabelle gibt.
Die Information, ob es eine Tabelle für diese Klasse gibt, findet man ausser direkt auf der Datenbank im Vertec Model Browser. Dort sieht man das Mapping auf jeder Klasse:
OWN bedeutet, dass es eine Tabelle für diese Klasse gibt.
Beispiel: Es gibt eine Tabelle Rechnung bzw. Invoice, die der Subquery abgefragt werden kann:
vtcapp.getwithsql("Project", "bold_id in (select project from Rechnung where charged=0)", "")
CHILDREN bedeutet, dass es keine Tabelle für diese Klasse gibt, sondern dass diese aufgeteilt ist in Tabellen der Subklassen.
Beispiel: Es gibt keine Tabelle Leistung, die in der Subquery abgefragt werden kann. Stattdessen müssen die Tabellen OffeneLeistung resp. OpenService und/oder VerrechneteLeistung resp. ChargedService abgefragt werden.
vtcapp.getwithsql("User", "bold_id in (select user from OffeneLeistung)", "")
Möchte man beide Tabellen abfragen, kann man mehrere Selections mit UNION zusammenhängen:
vtcapp.getwithsql("User", "bold_id in (select user from OffeneLeistung union select user from VerrechneteLeistung)", "")
PARENT bedeutet, dass es keine Tabelle für diese Klasse gibt, sondern diese in einer übergeordneten Tabelle subsummiert ist. Man kann dann direkt auf die Superclass navigieren, bis man auf das Mapping OWN stösst.
Beispiel: Es gibt keine Tabelle Firma, die in der Subquery abgefragt werden kann. Stattdessen muss die Tabelle Adresseintrag bzw. Address abgefragt werden.
vtcapp.getwithsql("User", "bold_id in (select betreuer from Adresseintrag)", "")
Möchte man hier trotzdem nur die Unterklasse berücksichtigen, kann man nach bold_type filtern. Dieser entspricht der Id im Model Browser. Im abgebildeten Beispiel ist das für die Firma die Id 84.
vtcapp.getwithsql("User", "bold_id in (select betreuer from Adresseintrag where bold_type=84)", "")
Die Namen der Tabellen entsprechen immer den Klassennamen im Modell, mit einer Ausnahme:
Es gibt keine Tabelle, die User heisst, da dies ein in SQL reserviertes Wort ist und nicht als Tabellennamen verwendet werden kann. Die Tabelle für die Klasse User lautet deshalb VtcUser. Auch bei den Members, die user heissen, lauten die Spalten in der Datenbank vtcuser.
Für die SQL Abfragen ink. Unterabfragen aus Vertec spielt das keine Rolle, auch nicht, ob die Klassen- und Membernamen in Deutsch oder Englisch angegeben werden. Vertec übersetzt das automatisch richtig.
vtcapp.getwithsql("Project", "projektleiter IN (select bold_id from User where name like '%keller')", "")
In Vertec Versionen bis 6.8.0.17 musste jedoch darauf geachtet werden. Griff man in früheren Versionen in einer Unterabfrage auf Englisch auf die User zu, musste der Tabellenname mit VtcUser angegeben werden (projektleiter IN (select bold_id from VtcUser where...). Projektbearbeiter funktionierte hingegen auch damals in beiden Sprachen.