Page tree
Skip to end of metadata
Go to start of metadata

Damit Inhalte im Portal angezeigt werden können, muss definiert werden, welche Daten von wo aus der Datenbank abgerufen werden sollen bzw. dürfen. Dies geschieht über die Verwendung von Queries. Im Kontext von YUNA werden Dashboard Inhalte, also auch Queries, über XML definiert. Da die Datenbank jedoch SQL-Statements benötigt, muss die XML-Definition übersetzt werden. Hierfür wird die Meta-Sprache Query Builder genutzt. 

Query Builder

QueryBuilder ist eine XML-Meta-Sprache für MSSQL.

Verwendet wird der QueryBuilder im CMP zum Erstellen von

  • Dashboard Inhalten / Filter
  • Abfragen
  • Security-Aspekten wie der Verhinderung von DropTable o.ä.

 Allgemeine Struktur einer SELECT-Query

<xml>
    <data name="template_qy_NameOfDataID" roles="System_Admin, AdHoc_Full_Issue">
        <!-- Setting the dataID -->
        <friendlyName>template_qy_NameOfDataID</friendlyName>
        <!-- StoredProcedure/QueryBuilder -->
        <type>QueryBuilder</type>
        <!-- Use filter on this query -->
        <filter>false</filter>
        <!-- Optional Path: Database Scheme Table -->
        <path/>
        <!-- Data-Query build with the QueryBilder (also see "QueryBuilder" in the Content Developer Guide) -->
        <query>
            <![CDATA[
                <QueryBuilder>
                    <select>
                        <table>CM-DataDB</table>
                        <table>data</table>
                        <table>DataTable</table>
                        <fields>
                            <asteriskfield />
                        </fields>
                        <where/>
                        <orderby/>
                        <limit>0</limit>
                        <limitoffset>0</limitoffset>
                    </select>
                    <dictionary/>
                </QueryBuilder>
            ]]>
        </query>
    </data>
</xml>


Mögliche SELECT-Fields

Name

Beispiel

SQL

Erklärung

field
<field>
        <name>Spaltenname</name>
        <prefix>Tabellenalias</prefix>
        <as>Spaltenalias</as>
</field>
SELECT SpaltennameWählt bestimmte Spalten aus. Optional ist der <prefix>. <prefix> wird beim Auftreten mehrerer Tabellen nacheinander als A, B, C, ... automatisch definiert.
asteriskfield
<asteriskfield/>
SELECT *Wählt alle Spalten aus.
distinctfield
<distinctfield>
    <field>
        <name>Spaltenname</name>
        <prefix>A</prefix>
        <as>Spaltenalias</as>
    </field>
</distinctfield>
SELECT DISTINCT SpaltennameGibt eine Liste unterschiedlicher Werte zurück, in der keine Duplikate der selektierten Spalte mehr vorhanden sind. Mehrere <field>-Angaben sind möglich.
countfield
<countfield>
    <name>Spaltenname</name>
    <prefix>A</prefix>
    <as>Anzahl</as>
</countfield>
SELECT COUNT(Spaltenname)Anzahl der Zeilen ohne NULL-Werte in der Spalte. Die Angabe eines Namens für <as> ist obligatorisch.
countdistinctfield
<countdistinctfield>
    <name>Spaltenname</name>
    <prefix>A</prefix>
    <as>EquiCount</as>
</countdistinctfield>
SELECT COUNT DISTINCT (Spaltenname)Gibt die Anzahl unterschiedlicher Werte einer selektierten Spalte zurück.
maxfield
<maxfield>
    <name>Spaltenname</name>
    <prefix>A</prefix>
    <as>Maximum</as>
</maxfield>
SELECT MAX(Spaltenname)Maximum der Spalte. Die Angabe eines Namens <as> ist obligatorisch.
minfield
<minfield>
    <name>Spaltename</name>
    <prefix>A</prefix>
    <as>Minimum</as>
</minfield>
SELECT MIN(Spaltenname)Minimum der Spalte. Die Angabe eines Namens <as> ist obligatorisch.
staticfield
<staticfield>
    <as>stringexample</as>
    <staticValue class="java.lang.String">abcdefg</staticValue>
</staticfield>
<staticfield>
    <as>numberexample</as>
    <staticValue class="java.lang.Integer">123456</staticValue>
</staticfield>

SELECT "abcdefg" AS stringexample




SELECT 123456 AS numberexample

Statische Werte für eine Tabellenspalte
concatfield
<concatfield>
   <as>Message</as>
   <innerfield>
      <name>Spaltenname1</name>
      <prefix>A</prefix>
   </innerfield>
   <innerstaticfield>
      <staticValue class="java.lang.String"> </staticValue>
   </innerstaticfield>
   <innerfield>
      <name>Spaltenname2</name>
      <prefix>A</prefix>
   </innerfield>
</concatfield>
SELECT CONCAT(Spaltenname1, ' ', Spaltenname2)Bildet die SQL-CONCAT-Funktion ab. Als Felder der Funktion sind innerfield (analog zu field) und innerstaticfield (analog zu staticfield) möglich. Voraussichtlich ab Version 0.14.

Mögliche SELECT-Fields

TABLE

Die eindeutige Angabe von Tabellen erfolgt über die Zusammensetzung aus Datenbankname, Tabellenpräfix und Tabellennamen.

Name

Beispiel

SQL

Erklärung

table
<table>Datenbank</table>
<table>Präfix</table>
<table>Name</table>
FROM [Datenbank].[Präfix].[Name]Gibt die Tabelle an, auf die sich die Abfrage bezieht.

JOIN

Im XML steht derzeit der INNER JOIN zur Verfügung.

 Name

Beispiel

SQL

Erklärung

innerjoin
<innerjoin>
        <a>
                <name>SpalteTabelleA</name>
               <prefix>A</prefix>
        </a>
       <b>
               <name>SpalteTabelleB</name>
               <prefix>B</prefix>
        </b>
        <table>DatenbankTabelleB</table>
        <table>PräfixTabelleB</table>
        <table>NameTabelleB</table>
        <as>B</as>
</innerjoin>
INNER JOIN [TabelleB] AS B ON A.SpalteTabelleA = B.SpalteTabelleB<innerjoin> lassen sich aneinander hängen. <a> gibt die erste Spalte nach dem 'ON' an, <b> die zweite.
leftjoins.o.LEFT JOIN [TabelleB] AS B ON A.SpalteTabelleA = B.SpalteTabelleB(ab Version 0.12)
rightjoins.o.RIGHT JOIN [TabelleB] AS B ON A.SpalteTabelleA = B.SpalteTabelleB(ab Version 0.14)

WHERE

Liste der Vergleichsoperatoren

Innerhalb von <where></where> können Bedingungen angegeben werden.

 Name

Beispiel

SQL

Erklärung

equal
<equal>
    <field>
        <name>Spaltenname</name>
        <prefix>A</prefix>
    </field>
    <value class="java.lang.String">Wert</value>
</equal>
Spaltenname = Wert

Gibt die Tabellenzeilen zurück, die die Bedingung erfüllen.

Werte für das Attribut class in <value> "java.lang.String", "java.lang.Integer", "java.lang.Boolean" [TRUE | FALSE]

notequals.o.Spaltenname != Werts.o.
lesss.o.Spaltenname < Werts.o.
lessequals.o.Spaltenname <= Werts.o.
greaters.o.Spaltenname > Werts.o.
greaterequals.o.Spaltenname >= Werts.o.
nulls.o.Spaltenname IS NULLTabellenzeilen, in denen der Spaltenwert NULL ist.
notnulls.o.Spaltenname IS NOT NULLTabellenzeilen, in denen der Spaltenwert nicht NULL ist.
like
<like>
    <field>
        <name>Spaltenname</name>
        <prefix>A</prefix>
    </field>
    <value class="java.lang.String">%search%</value>
</like>
Spaltenname LIKE "%seach%"Wildcards sind vor dem Suchbegriff, danach oder beides möglich.
in
<in>
    <field>
        <name>Spaltenname</name>
        <prefix>A</prefix>
    </field>
    <value>
        <object class="java.lang.String">Wert1</object>
        <object class="java.lang.String">Wert2</object>
        <object class="java.lang.String">Wert3</object>      
    </value>
</in>
Spaltenname IN (Wert1, Wert2, Wert3)Beachten: Attribut class gehört zu <object>, nicht zu <value> wie an anderer Stelle.

Weitere Operatoren

Zusätzlich besteht mit ID die Möglichkeit, triggerParams aus der URL in die Abfrage einzubeziehen, die <value> ersetzen. Weiterhin können die Vergleichoperatoren logisch verknüpft werden.

 Name

Beispiel

SQL

Erklärung

ID
<less>
    <field>
        <ID>triggerParam</ID>
        <name>Spaltenname</name>
        <prefix>A</prefix>
    </field>
</less>
Spaltenname < triggerParamDie Abfrage übernimmt einen der im Widget definierten triggerParams aus der URL. Somit können die <value> bei den Vergleichsoperatoren durch variable Werte ersetzt werden.
and
<equal>...</equal>
<and/>
<less>...</less>
Spaltename1 = Wert1 AND Spaltenname2 < Wert2 Logisches UND von WHERE-Bedingungen
or
<equal>...</equal>
<or/>
<less>...</less>
Spaltename1 = Wert1 OR Spaltenname2 < Wert2 Logisches ODER von WHERE-Bedingungen
not
<not/>
<like>...</like>
<and/>
<not/>
<equal>...</equal>
NOT Spaltenname1 LIKE "%search%" AND NOT Spaltenname2 = Wert2Logisches NICHT von WHERE-Bedingungen (ab Version 0.12)

Reservierte Key Words für Backend generierte Values



 

Beispiel

SQL

Erklärung

UserID
<where>
    <equal>
        <field>
            <ID>UserID</ID>
            <name>ID</name>
            <prefix>A</prefix>
        </field>
        <value class="java.lang.Integer">0</value
    </equal>
</where>
-Das Backend übergibt die UserID (des aktuell eingeloggten Users).
RoleID
<where>
         <equal>
            <field>
               <ID>RoleID</ID>
               <name>ID</name>
               <prefix>A</prefix>
            </field>
            <value class="java.lang.Integer">0</value>
         </equal>
</where>
-Das Backend übergibt die RoleID (des aktuell eingeloggten Users).

GROUP BY

 Name

Beispiel

SQL

Erklärung

groupby
<groupby>
    <field>
        <name>Spaltenname1</name>
        <prefix>A</prefix>
    </field>
    <field>
        <name>Spaltenname2</name>
        <prefix>A</prefix>
    </field>  
</groupby>
GROUP BY Spaltenname1, Spaltenname2Gruppiert nach Spalten. Sinnvoll für Gruppierungsfunktionen countfield, maxfield, minfield.

ORDER BY

 Name

Beispiel

SQL

Erklärung

orderby
<orderby>
    <field order="ASCENDING">
        <name>Spaltenname1</name>
        <prefix>A</prefix>
    </field>
    <field order="DESCENDING">
        <name>Spaltenname2</name>
        <prefix>A</prefix>
    </field>
</orderby>
ORDER BY Spaltenname1 ASC, Spaltenname2 DESCSortiert nach Werten der Spaltennamen.

LIMIT

 Name

Name

Beispiel

SQL

Erklärung

limit
<limit>n</limit>

SELECT TOP n <MSSQL>

LIMIT n <MYSQL>

Liefert die ersten n Werte einer Abfrage
limitoffset
<limitoffset>m</limitoffset>
LIMIT n, m <MYSQL>Liefert n Werte einer Abfrage, beginnend mit dem m-ten Wert

(NOLOCK)

MSSQL-spezifisch. Sperrung anderer Abfragen während der Ausführung.

Abbrechen von Datenbankabfragen

Über den Query-Builder definierte Abfragen werden üblicherweise abgebrochen, wenn ein Benutzer während der Durchführung der Abfrage das aufrufende Dashboard schließt, wird diese abgebrochen. Soll dies nicht nicht passieren, kann dies entweder global über die Service-Konfiguration oder für eine einzelne Query konfiguriert werden:

Name

Beispiel

Erklärung

cancellable
<cancelable>false</cancelable>
Verhindert das Abbrechen der definierten Datenbankabfrage
Beispiel für das Setzen des <cancelable>-Tags
<xml>
    <data name="qy_query_with_disabled_cancellation"
        roles="System_Admin, AdHoc_Full_Issue">
        <friendlyName>qy_DataQueryIntegrationTest_ExecWithDelay_CancelableFalse</friendlyName>
        <type>StoredProcedure</type>
        <filter>true</filter>
        <path>DataDB data</path>
        <params>
            <cancelable>false</cancelable>
        </params>
        <query><![CDATA[
        <QueryBuilder>
              <exec>
                <procedure>DSE-DataDB</procedure>
                <procedure>data</procedure>
                <procedure>sp_nameOfStoredProcedure</procedure>
            </exec>
        </QueryBuilder>
        ]]></query>
    </data>
</xml>

EXEC-Query. Aufruf einer Stored Procedure

 Name

Beispiel

SQL

Erklärung

exec
<QueryBuilder>
  <exec>
    <procedure>CM-PortalDB</procedure>
    <procedure>portal</procedure>   <procedure>sp_IssueTable_4SingleLaser</procedure>
    <parameters>
      <parameter>
        <id>equi</id>
        <parameter>EquipmentNo</parameter>
        <type>VARCHAR</type>
      </parameter>
    </parameters>
  </exec>
</QueryBuilder>

Aufruf einer Stored Procedure mit Parametern.


Nutzung von Filtern und aus dem Backend gesetzten Parametern

 Name

Beispiel

SQL

Erklärung

exec
<QueryBuilder>
    <exec>
        <procedure>CM-PortalDB</procedure>
        <procedure>dev</procedure>     <procedure>sp_IssueHotList_EquiList</procedure>
            <parameters>
                <parameter>
                    <id>filter2</id>                  <parameter>InstBasis</parameter>
                    <type>VARCHAR</type>
                </parameter>
                <parameter>
                    <id>UserID</id>
<parameter>UserID</parameter>  
                    <type>INT</type>
                </parameter>
            </parameters>
    </exec>
</QueryBuilder>

Beispielaufruf einer Stored Procedure mit Filter und User ID

DECLARE @InstBasis VARCHAR(MAX)
DECLARE @UserID INT
EXEC [CM-PortalDB].[dev].[sp_IssueHotList_EquiList]
@InstBasis = N'(([InstBasis].[CommissioningDate] >= '2016-09-14' AND [InstBasis].[CommissioningDate] <= '2016-10-15') AND ([InstBasis].[ProductGroup] IN (N'TruDisk')))', @UserID = 31
Aufruf einer Stored Procedure mit Filtern und User ID.


Falls die Spalten, auf die der Filter wirkt, nicht vorhanden sind, wird die Basistabelle des Filters (z.B. die Installierte Basis) über die definierten Felder mit der Tabelle, auf der gefiltert werden soll, gejoined:

  1. Eintrag in [CM-PortalDB].[sp].[Query] mit
    Filter = 1
    QueryTablePath = Tabelle auf die der angehangene Filter wirken soll


  2. Eintrag in [CM-PortalDB].[dev].[FilterAssocTMP]
    Query_ID = ID der Query, an die ein Filter angehangen werden soll
    Filter_ID = ID des Filters, der an die Query angehangen werden soll
    FilterField = Feld des Filters für den JOIN
    QueryResultField = Feld des Querys für den JOIN


Im Query-XML wird definiert:

  • über <filter>true</filter>, dass ein Filter angehangen wird;
  • über <path> tabelle </path>, auf welcher Tabelle gefiltert wird
  • und über z.B. den folgenden XML-Code, über welche Felder der JOIN für den Filter erfolgt, sofern dieser auf einer anderen Tabelle definiert ist als das Query:   
<data>
…
   <FilterAssociation_2>
      <Filter_ID>1</Filter_ID>
         <FilterField>EquipmentNo</FilterField>
         <QueryResultField>EquipmentNo</QueryResultField>
   </FilterAssociation_2>
</data>


Filter werden automatisch vom Backend als B,C, ... (in der Reihenfolge, wie sie angehängt werden = FilterAssociation) gejoint - d.h. wenn in der Query noch ein weiterer Join vorhanden ist, muss diese Tabelle mit einem anderen Prefix versehen werden.


EXEC-Query: Reservierte Key Words für Parameter

 Name

Beispiel

SQL

Erklärung

filterX
<parameter>
  
<id>filter2</id>
  
<parameter>InstBasis</parameter>
  
<type>VARCHAR</type>
  
</parameter>

Beispielaufruf einer Stored Procedure mit Filter und User ID


DECLARE @InstBasis VARCHAR(MAX)
...
@InstBasis = N'(([InstBasis].[CommissioningDate] >= '2016-09-14' AND [InstBasis].[CommissioningDate] <= '2016-10-15') AND ([InstBasis].[ProductGroup] IN (N'TruDisk')))'...

Ein Filter kann aus dem Frontend übergeben werden.

Dafür wird der Hash (der Value) im backend aufgelöst und der zu MSSQL konvertiert String wird dann an die Stored Procedure übergeben

UserID
<parameter>
  
<id>UserID</id>
  
<parameter>UserID</parameter>
  
<type>INT</type>
  
</parameter>

Beispielaufruf einer Stored Procedure mit Filter und User ID


DECLARE @UserID INT
...
@UserID = 31
UserID stellt eine gesonderte Funktion bereit. Im Backend wird diesem Parameter die UserID übergeben.
currentLanguage
<parameter>
  
<id>currentLanguage</id>
  
<parameter>languageParameter</parameter>
        
<type>VARCHAR</type>
  
</parameter>

Beispielaufruf einer Stored Procedure


...
DECLARE @language VARCHAR(20) = @languageParameter
...

Die aktuell ausgewählte Sprache eines Benutzers wird bei Abfragen als Parameter 'currentLanguage' an die DataId übergeben. Dieser kann genutzt werden, um abhängig von dem Parameter verschiedene Spalten mit Übersetzungen anzuzeigen.

  • No labels