| <div class="docbook"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">6.3. REST API</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="Java-Broker-Management-Channel-Web-Console.html">Prev</a> </td><th width="60%" align="center">Chapter 6. Management Channels</th><td width="20%" align="right"> <a accesskey="n" href="Java-Broker-Management-Metrics.html">Next</a></td></tr></table><hr /></div><div class="section"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a id="Java-Broker-Management-Channel-REST-API"></a>6.3. REST API</h2></div></div></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-API-Introduction"></a>6.3.1. Introduction</h3></div></div></div><p>This section describes the REST API provided by the Apache Qpid Broker-J. The REST API is intended |
| for use by developers who wish to automate the management or monitoring of the Broker. It |
| is also very useful for adhoc monitoring on the command line using tools such as |
| <code class="literal">curl</code>.</p><p>The REST API provides access to all of the Broker's entities using hierarchical paths |
| expressed by the URI. Responses are returned in JSON format.</p><p>The <code class="literal">GET</code> method request retrieves information about an object, the |
| <code class="literal">DELETE</code> method requests the removal of one, and the <code class="literal">PUT</code> |
| or <code class="literal">POST</code> methods perform updates or create new objects. The |
| <code class="literal">POST</code> method is also used to invoke operations.</p><p>The REST API is versioned with the version number embedded within the URI. The general form |
| of the URI is <code class="literal">/api/<version></code> where <version> is a dot separated |
| major and minor model version prefixed with "v", for example, "v6.1" (without the quotation marks). |
| For convenience the alias <code class="literal">latest</code> (<code class="literal">/api/latest</code>) signifies the |
| latest supported version.</p><p>There are also some ancillary services under URI <code class="literal">/service</code> used for |
| authentication and logout.</p></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-API-APIDocs"></a>6.3.2. REST API documentation</h3></div></div></div><p>REST API documentation is available on-line from any Broker at location |
| <code class="literal">/apidocs</code>. It is also linked from the menu of the Web Management Console. |
| </p></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-API-Authentication"></a>6.3.3. Authentication</h3></div></div></div><p>Before you can use the REST API, you must authenticate. Authentication decisions are made |
| by the <a class="link" href="Java-Broker-Concepts-Authentication-Providers.html" title="4.9. Authentication Providers">authentication |
| provider</a> associated with HTTP <a class="link" href="Java-Broker-Concepts-Ports.html" title="4.8. Ports">port</a> |
| on which you connect.</p><p>You may authenticate using <a class="link" href="https://www.ietf.org/rfc/rfc4422.txt" target="_top">SASL</a> |
| (<code class="literal">/service/sasl</code>) or <a class="link" href="https://tools.ietf.org/html/rfc2617" target="_top">HTTP |
| Basic Authentication</a>. The latter is convienent when using tools such as |
| <code class="literal">curl</code> on the command line. This is illustrated in the examples |
| below.</p><p>For SASL authentication use a <code class="literal">GET</code> request to |
| <code class="literal">/service/sasl</code> to get a list of supported SASL mechanisms, and use |
| <code class="literal">PUT</code> to the same URL to perform the SASL negotiation.</p><p>It is possible to end an authenticated session using |
| <code class="literal">/service/logout</code>.</p></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-API-Create"></a>6.3.4. Configured Object creation</h3></div></div></div><p>Methods PUT or POST can be used to create ConfiguredObject.</p><p> ConfiguredObject can be created by submitting PUT request against ConfiguredObject full |
| URI (the one ending with configured object name) or by submitting PUT/POST request against |
| parent URI. The request encoding should be json (application/json) and request body should |
| contain attributes values in json format. On successful completion of operation a response |
| should be returned having response status code set to 201 and response header "Location" set |
| to ConfiguredObject full URI. If object with a such name/id already exist and POST/PUT |
| requests is made against parent URI, an error response should be returned having response code |
| 409 (conflict) and body containing the json with the reason of operation failure. If object |
| with a such name/id already exist and and PUT request is made against ConfiguredObject full |
| URI, then ConfiguredObject update should be performed and http status code 200 should be |
| returned. If ConfiguredObject cannot be created because of validation failure(s) the response |
| should have http status code set 422 (Unprocessible Entity) and body should contain json with |
| the reason of operation failure. On any other failure to create ConfiguredObject the response |
| should have status code set to 400 (Bad Request) and payload should contain a json with error |
| explaining the exact reason of failure. </p><div class="example"><a id="d0e2147"></a><p class="title"><strong>Example 6.1. Examples of REST calls for Queue creation</strong></p><div class="example-contents"><p> To create Queue with name "my-queue" on a virtual host with name "vh" (which is |
| contained within virtual host node with name "vhn") either of the following requests should |
| be made: </p><pre class="screen">PUT /api/latest/queue/vhn/vh HTTP/1.1</pre><pre class="screen">POST /api/latest/queue/vhn/vh HTTP/1.1</pre><pre class="screen">PUT /api/latest/queue/vhn/vh/my-queue HTTP/1.1</pre><p> Response code 201 should be returned on successful queue creation. Response header |
| "Location" should be set to "/api/latest/queue/test/my-queue". If queue with name "my-queue" |
| already exists and either of 2 first requests above were used, an error response with |
| response code 409 (conflict) and body containing json with message that queue exists should |
| be returned. If queue with name "my-queue" exists and last request is used, then Queue |
| update should occur. </p></div></div><br class="example-break" /></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-API-Update"></a>6.3.5. Configured Object update</h3></div></div></div><p>Methods PUT or POST can be used to update ConfiguredObject.</p><p> ConfiguredObject can be updated by submitting PUT or POST request against |
| ConfiguredObject full URI (the one ending with configured object name). The request encoding |
| should be json (application/json) and request body should contain a ConfiguredObject json |
| (with all or only modified attributes). On successful completion of operation a response code |
| 200 should be returned. If ConfiguredObject does not exists and PUT method is used, such |
| object should be created (201 response will be returned in this case). If ConfiguredObject |
| does not exists and POST method is used, an error response should be returned having response |
| status code 404 and payload with json explaining the problem. If any error occur on update, a |
| response with response code 400 or 422 or 404 should be sent back to the client containing |
| json body with error details. </p><div class="example"><a id="d0e2167"></a><p class="title"><strong>Example 6.2. Examples of REST calls for Queue update</strong></p><div class="example-contents"><p>To update Queue with name "my-queue" on a virtual host with name "vh" (contained in |
| virtual host node with name "vhn") either of the following requests can be made:</p><pre class="screen">POST /api/latest/queue/vhn/vh/my-queue HTTP/1.1</pre><pre class="screen">POST /api/latest/queue/vhn/vh/my-queue HTTP/1.1</pre></div></div><br class="example-break" /></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-API-Delete"></a>6.3.6. Configured Object deletion</h3></div></div></div><p>Method DELETE can be used to delete ConfiguredObject. Alternatively, ConfiguredObject can |
| be deleted with update request having desiredState attribute set to value "DELETED". POST or |
| PUT methods can be used in this case.</p><p>On successful completion of operation a response code 200 should be returned.</p><p>With DELETE method object ConfiguredObject in following ways:</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>by submitting DELETE request using ConfiguredObject full URI (the one ending with |
| configured object name)</p></li><li class="listitem"><p>by submitting DELETE request using parent URI and providing parameters having the same |
| names as children attributes, for example, id, name, etc. Multiple children can be deleted |
| in a such way. Many "id" parameters can be specified in such requests. Only children with |
| matching attribute values will be deleted.</p></li></ul></div><div class="example"><a id="d0e2192"></a><p class="title"><strong>Example 6.3. Examples of REST calls for Queue deletion</strong></p><div class="example-contents"><p>To delete Queue with name "my-queue" on a virtual host with name "vh" (contained in |
| virtual host node with name "vhn") either of the following requests can be made:</p><pre class="screen">DELETE /api/latest/queue/vhn/vh/my-queue HTTP/1.1</pre><pre class="screen">DELETE /api/latest/queue/vhn/vh?name=my-queue HTTP/1.1</pre><pre class="screen">DELETE /api/latest/queue/vhn/vh?id=real-queue-id HTTP/1.1</pre></div></div><br class="example-break" /></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-API-Get"></a>6.3.7. Retrieving Configured Object details</h3></div></div></div><p>Method GET is used to retrieve an object's attributes values and statistics.</p><p>To retrieve a single object, use its full URI. For instance, to retrieve a single queue:</p><pre class="screen">GET /api/latest/queue/vhn/vh/my-queue</pre><p>To retrieve all objects beneath a parent, pass the parent's URI. For instance, to retrieve |
| all queues beneath the virtualhost called <code class="literal">vh</code>. A collection will be returned. |
| </p><pre class="screen">GET /api/latest/queue/vhn/vh</pre><p>Request parameters (with the same name as an attribute) are used to filter the returned collection. |
| For instance, to filter those queues of type <code class="literal">standard</code>:</p><pre class="screen">GET /api/latest/queue/vhn/vh?type=standard</pre><p>Additional parameters supported in GET requests:</p><div class="variablelist"><dl class="variablelist"><dt><span class="term">depth</span></dt><dd><p>To restrict the depth of hierarchy of configured objects to return in |
| response</p></dd><dt><span class="term">actuals</span></dt><dd><p>If set to "true" attribute actual values are returned instead of effective</p></dd><dt><span class="term">excludeInheritedContext</span></dt><dd><p>If set to "false" the inherited context is included from the object's ancestors. Default is true.</p></dd><dt><span class="term">oversize</span></dt><dd><p>Sets the maximum length for values of over-sized attributes to trim</p></dd><dt><span class="term">extractInitialConfig</span></dt><dd><p>If set to "true", the returned json can be used as initial configuration.</p></dd></dl></div></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-API-Operations"></a>6.3.8. Configured Object operations</h3></div></div></div><p>Method POST is used to invoke Configured Objects operations. Some operations support |
| parameters. Pass parameters using a JSON request body containing a map with a map entry for |
| each parameter. </p><div class="example"><a id="d0e2264"></a><p class="title"><strong>Example 6.4. Example REST call invoking the operation clear queue</strong></p><div class="example-contents"><p>To clear the queue with name "my-queue" on a virtual host with name "vh".</p><pre class="screen">POST api/latest/queue/vhn/vh/my-queue/clearQueue HTTP/1.1</pre></div></div><br class="example-break" /></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-API-Status-Codes"></a>6.3.9. HTTP status codes returned by REST interfaces</h3></div></div></div><div class="table"><a id="d0e2274"></a><p class="title"><strong>Table 6.1. HTTP status codes returned by REST interfaces</strong></p><div class="table-contents"><table summary="HTTP status codes returned by REST interfaces" border="1"><colgroup><col class="statuscode" /><col class="description" /></colgroup><thead><tr><th>Status code</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>200</p> |
| </td><td> |
| <p>REST request is successfully completed. This status code can be returned by |
| update, delete and get requests.</p> |
| </td></tr><tr><td> |
| <p>201</p> |
| </td><td> |
| <p>New configured object is created. It is returned by REST PUT and POST requests |
| for creation of configured objects.</p> |
| </td></tr><tr><td> |
| <p>400</p> |
| </td><td> |
| <p>REST request cannot be performed due to errors in request. It can be returned |
| from create, update and delete requests. The details of a problem are provided in |
| the response payload in json format.</p> |
| </td></tr><tr><td> |
| <p>401</p> |
| </td><td> |
| <p>The request requires user authentication</p> |
| </td></tr><tr><td> |
| <p>403</p> |
| </td><td> |
| <p>Execution of request is not allowed due to failure to authorize user |
| operation.</p> |
| </td></tr><tr><td> |
| <p>404</p> |
| </td><td> |
| <p> The requested configured object cannot be found. This status code can be |
| returned from POST update requests if configured object does not exist. The reason |
| for the status code is provided in the response payload in json format. </p> |
| </td></tr><tr><td> |
| <p>409</p> |
| </td><td> |
| <p>The request can not be performed because its execution can create conflicts in |
| the broker. This status code can be returned from POST/PUT create requests against |
| parent URI if configured object with requested name or id already exists. The status |
| code 409 can also be returned if removal or update of configured object can violate |
| system integrity. The reason for the status code is provided in the response payload |
| in json format. </p> |
| </td></tr><tr><td> |
| <p>422</p> |
| </td><td> |
| <p>The request can not be performed because provided information either incomplete |
| or invalid. This status code can be returned from create or update requests. The |
| reason for the status code is provided in the response payload in json |
| format.</p> |
| </td></tr></tbody></table></div></div><br class="table-break" /></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-API-Examples"></a>6.3.10. Examples of REST requests with curl</h3></div></div></div><div class="example"><a id="d0e2378"></a><p class="title"><strong>Example 6.5. Examples of queue creation using curl (authenticating as user admin):</strong></p><div class="example-contents"><pre class="programlisting"> |
| #create a durable queue |
| curl --user admin -X PUT -d '{"durable":true}' http://localhost:8080/api/latest/queue/<vhostnode name>/<vhostname>/<queuename> |
| #create a durable priority queue |
| curl --user admin -X PUT -d '{"durable":true,"type":"priority"}' http://localhost:8080/api/latest/queue/<vhostnode name>/<vhostname>/<queuename> |
| </pre></div></div><br class="example-break" /><p> NOTE: These curl examples utilise an unsecured HTTP transport. To use the examples it is |
| first necessary enable Basic authentication for HTTP within the HTTP Management Configuration |
| (it is off by default). For details see <a class="xref" href="Java-Broker-Management-Managing-Plugin-HTTP.html" title="7.17. HTTP Plugin">Section 7.17, “HTTP Plugin”</a> |
| </p></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-Query-API"></a>6.3.11. Query API</h3></div></div></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-API-Introduction"></a>6.3.11.1. Introduction</h4></div></div></div><p>The <span class="emphasis"><em>Qpid Broker-J</em></span> provides a powerful feature called |
| the <span class="emphasis"><em>Query API</em></span>. This allows the retrieval of the existing configured objects attributes |
| satisfying user-provided queries.</p><p>Developers and operators can use this feature to monitor the Broker. |
| For example, using <span class="emphasis"><em>Query API</em></span> one can find all queues with queue depth |
| exceeding some limit or existing connections made from a particular location(s).</p></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-API-Overview"></a>6.3.11.2. Query API Overview</h4></div></div></div><p> |
| When using the <span class="emphasis"><em>Query API</em></span> one specifies the category of the object |
| to query, a list of attributes to return in the result set, an optional where clause, |
| expressed as a predicate, that determines the filtering criteria, ordering, and |
| limit/offset. The features should be readily recognisable to anyone who has has familiarity |
| with SQL. |
| </p><p>Queries associate with either the <span class="emphasis"><em>broker</em></span> as a whole, or an |
| individual <span class="emphasis"><em>virtualhost</em></span>. Queries associated with the Broker |
| can query any object within the Broker. Queries associated with a virtualhost are limited |
| to the objects of the virtualhost itself. For instance a queue query associated |
| with a virtualhost queries only the queues belonging to that virtualhost. On the other |
| hand, a queue query associated with the Broker sees all the queues belonging on the entire |
| Broker. |
| </p><p> |
| </p><div class="table"><a id="d0e2425"></a><p class="title"><strong>Table 6.2. Query API URLs</strong></p><div class="table-contents"><table summary="Query API URLs" border="1"><colgroup><col class="queryurl" /><col class="description" /></colgroup><thead><tr><th>Query API URL</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>/api/latest/querybroker/<configured object category name></p> |
| <p>/api/<version>/querybroker/<configured object category name></p> |
| </td><td> |
| <p>Query API URL fragment to query the specified object type across the entire broker</p> |
| </td></tr><tr><td> |
| <p>/api/latest/queryvhost/<virtual host node name>/<virtual host name>/<configured object category name></p> |
| <p>/api/<version>/queryvhost/<virtual host node name>/<virtual host name>/<configured object category name></p> |
| </td><td> |
| <p>Query API URL fragment to query the specified object type for a specific virtualhost</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p><p> |
| The QueryAPI accepts <code class="literal">select</code>, <code class="literal">where</code>, <code class="literal">orderBy</code>, |
| <code class="literal">limit</code> and <code class="literal">offset</code> request parameters. |
| </p><div class="table"><a id="d0e2484"></a><p class="title"><strong>Table 6.3. Query API request parameters</strong></p><div class="table-contents"><table summary="Query API request parameters" border="1"><colgroup><col class="paramname" /><col class="description" /></colgroup><thead><tr><th>Parameter Name</th><th>Parameter Description</th></tr></thead><tbody><tr><td> |
| <p><code class="literal">select</code></p> |
| </td><td> |
| <p>The <code class="literal">select</code> defines the columns of the result set. It is a |
| comma-separated list of expressions. At its most simple, an expression can be |
| the name of the attribute (e.g. <code class="literal">queueDepthBytes</code>), but more complex |
| <a class="link" href="Java-Broker-Management-Channel-REST-API.html#Java-Broker-Management-Channel-REST-Query-API-Expressions" title="Query API expressions">expressions</a> are also supported.</p> |
| <p>Columns within the result set are named. For expressions that are simple attribute |
| names, the column names will follow the attributes themselves. By default, other |
| expressions will have a no name.</p> |
| <p>Column names can be overridden with an <code class="literal">AS</code> |
| clause e.g. <code class="literal">now() AS currentDate</code> |
| </p> |
| </td></tr><tr><td> |
| <p><code class="literal">where</code></p> |
| </td><td> |
| <p>The <code class="literal">where</code> provides a boolean expression defining the result set filtering.</p> |
| <p>The syntax of the <a class="link" href="Java-Broker-Management-Channel-REST-API.html#Java-Broker-Management-Channel-REST-Query-API-Expressions" title="Query API expressions">expression</a> |
| is based on a subset of the SQL92 conditional expression syntax and is similar to selector expressions in JMS e.g. |
| <code class="literal">queueDepthBytes > 16384 AND name like '%flow_queue'</code>. |
| </p> |
| </td></tr><tr><td> |
| <p><code class="literal">orderBy</code></p> |
| </td><td> |
| <p>Ordering conditions; the syntax of the |
| <a class="link" href="Java-Broker-Management-Channel-REST-API.html#Java-Broker-Management-Channel-REST-Query-API-Expressions" title="Query API expressions"> |
| expression |
| </a> |
| is based on a subset of |
| the SQL92 ordering expression syntax. Similar to ordering expressions in SQL, |
| one can specify in ordering expression attributes names, sub-expressions |
| or indexes (starting from 1) of attributes or expressions specified in select. |
| </p> |
| </td></tr><tr><td> |
| <p><code class="literal">limit</code></p> |
| </td><td> |
| <p>The maximum number of results to provide starting from given offset.</p> |
| </td></tr><tr><td> |
| <p><code class="literal">offset</code></p> |
| </td><td> |
| <p>An offset in results (default is 0) to provide results from.</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p><div class="example"><a id="d0e2594"></a><p class="title"><strong>Example 6.6. Example of a Query API request to retrieve queue names and depths.</strong></p><div class="example-contents"><pre class="screen">GET api/latest/querybroker/queue?select=name,queueDepthBytes,queueDepthMessages&where=queueDepthBytes>0&orderBy=1 desc,2 desc&offset=0&limit=100 HTTP/1.1</pre></div></div><br class="example-break" /></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-API-Results"></a>6.3.11.3. Query API Results</h4></div></div></div><p>The <span class="emphasis"><em>Query API</em></span> returns a JSON response. The response contains the following: |
| </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">headers</code></span></dt><dd><p>ordered list of result set column names derived from the <code class="literal">select</code> |
| clause. Note that anonymous expressions (that is, those expressed without an |
| <code class="literal">AS</code>) will have empty column name.</p></dd><dt><span class="term"><code class="literal">results</code></span></dt><dd><p>two dimensional array containing the result-set</p></dd><dt><span class="term"><code class="literal">total</code></span></dt><dd><p>The <span class="emphasis"><em>total</em></span> number of results matching the where criteria.</p></dd></dl></div><p> |
| </p><div class="example"><a id="d0e2639"></a><p class="title"><strong>Example 6.7. Example of Query API call for queue names and depths.</strong></p><div class="example-contents"><pre class="screen">GET api/latest/querybroker/queue?select=name,queueDepthBytes,queueDepthMessages&where=queueDepthBytes>0&orderBy=1 desc,2 desc&offset=0&limit=100 HTTP/1.1</pre><pre class="programlisting"> |
| { |
| "headers" : [ "name", "queueDepthBytes", "queueDepthMessages" ], |
| "results" : [ [ "foo", 312, 26], [ "bar", 300, 24 ] ], |
| "total" : 2 |
| } |
| </pre></div></div><br class="example-break" /><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-API-Expressions"></a>Query API expressions</h5></div></div></div><p>Expressions within the <code class="literal">select</code>, <code class="literal">where</code> and <code class="literal">orderBy</code> |
| clauses can be comprised in the following manner. Expressions can be nested to arbitary depth. Parentheses |
| allow for precedence to be explicitly denoted. |
| </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>variable name which can be an attribute name e.g <code class="literal">queueDepthBytes</code> or |
| a reference to a parent attribute <code class="literal">$parent.name</code></p></li><li class="listitem"><p>literal e.g. <code class="literal">3</code> or <code class="literal">'foo'</code></p></li><li class="listitem"><p>functions - see below e.g. <code class="literal">now()</code> or <code class="literal">to_string(createdDate, '%tm/%td/%ty', 'EST')</code></p></li><li class="listitem"><p>arithmetic operations e.g. <code class="literal">3 * 4</code> or <code class="literal">to_string(now()) + name</code></p></li></ul></div><p>The following functions are supported: |
| </p><div class="table"><a id="d0e2695"></a><p class="title"><strong>Table 6.4. Query API functions</strong></p><div class="table-contents"><table summary="Query API functions" border="1"><colgroup><col class="functionname" /><col class="description" /></colgroup><thead><tr><th>Function Name</th><th>Function Description</th></tr></thead><tbody><tr><td> |
| <p><code class="literal">concat(obj[,obj..])</code></p> |
| </td><td> |
| <p>concatenates the given objects into a string</p> |
| </td></tr><tr><td> |
| <p><code class="literal">now()</code></p> |
| </td><td> |
| <p>returns current date and time</p> |
| </td></tr><tr><td> |
| <p><code class="literal">to_date(object)</code></p> |
| </td><td> |
| <p>converts the first parameter, which must be a string. into a date. The |
| string must be in ISO-8601 format e.g. <code class="literal">1970-01-01T10:00:00Z</code>.</p> |
| </td></tr><tr><td> |
| <p><code class="literal">date_add(object, duration)</code></p> |
| </td><td> |
| <p>adds the given ISO-8601 duration <code class="literal">duration</code> e.g. |
| <code class="literal">P1D</code> or <code class="literal">-PT10H</code> to the date provided by the |
| first parameter.</p> |
| </td></tr><tr><td> |
| <p><code class="literal">to_string(object[, format[, timezone]])</code></p> |
| </td><td> |
| <p>Converts given object into a string.</p> |
| <p>If the format argument is present, it must be a Java |
| <a class="link" href="http://docs.oracle.com/javase/7/docs/api/java/util/Formatter.html" target="_top">Formatter</a> |
| compliant string e.g. <code class="literal">%f</code> or <code class="literal">%tY-%tm-%td</code>. |
| </p> |
| <p>The timezone argument is significant if the object is a Date. If the timezone |
| argument is specified it must be a valid Java timezone name. The date is converted |
| to the specified timezone before being formatted by the<code class="literal">format</code>. |
| If the timezone is omitted <code class="literal">UTC</code> is assumed. |
| </p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div></div></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine"></a>6.3.12. Query Engine</h3></div></div></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Introduction"></a>6.3.12.1. Introduction</h4></div></div></div><p> |
| Broker query engine extends existing functionality of broker query API and allows executing complex SQL-like |
| queries against the broker. It allows using predicates combining AND/OR/NOT logical operations, supports |
| aggregation and grouping as well as numerous numeric, datetime and string functions. |
| Currently, querying from multiple object types (domains) in a single query as well as all types of joins |
| are not supported. |
| </p></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Broker-Configuration"></a>6.3.12.2. Broker Configuration</h4></div></div></div><p> |
| Some properties influencing the query output can be specified directly in the request, but there are also global |
| properties, affecting the way query engine works. |
| </p><p> |
| </p><div class="table"><a id="d0e2818"></a><p class="title"><strong>Table 6.5. Query Engine Configuration</strong></p><div class="table-contents"><table summary="Query Engine Configuration" border="1"><colgroup><col class="context-property-name" /><col class="description" /></colgroup><thead><tr><th>Context Property Name</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>qpid.port.http.query.engine.cacheSize</p> |
| </td><td> |
| <p>Query cache size</p> |
| </td></tr><tr><td> |
| <p>qpid.port.http.query.engine.maxQueryDepth</p> |
| </td><td> |
| <p>Maximal query depth</p> |
| </td></tr><tr><td> |
| <p>qpid.port.http.query.engine.zoneId</p> |
| </td><td> |
| <p>Timezone ID</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Broker-Configuration-Query-Cache-Size"></a>Query cache size</h5></div></div></div><p> |
| After query is parsed from the SQL string, it is stored into a cache. When the same query will be fired against |
| the query engine, parsing will be omitted and the query structure will be retrieved from cache. By default, query cache |
| size is 1000. This means, that when 1000 different queries will be fired against the query engine, the next one will |
| override the oldest cache entry. When set to 0 or to negative value, query cache will not be used and each query |
| will be parsed. |
| </p></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Broker-Configuration-Max-Query-Depth"></a>Maximal query depth</h5></div></div></div><p> |
| The longer is the query and the more conditions it contains, the bigger becomes the query depth. To limit query |
| complexity, maximal query depth parameter can be used. By default, maximal query depth is 4096. This should suffice |
| for most queries even complicated ones. If query depth exceeds this limit, following error will |
| be returned: |
| </p><pre class="programlisting"> |
| { |
| "errorMessage": "Max query depth reached: 4096" |
| } |
| </pre></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Broker-Configuration-Zone-ID"></a>Zone ID</h5></div></div></div><p> |
| Zone ID value should follow the rules described in <a class="link" href="https://docs.oracle.com/javase/8/docs/api/java/time/ZoneId.html#of-java.lang.String-" target="_top">javadoc</a>. |
| The default value for zone id is "UTC". |
| </p></div></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Request-Format"></a>6.3.12.3. Request Format</h4></div></div></div><p> |
| An authorized request should be sent to the following endpoint: |
| POST http://<hostname>:<port>/api/latest/querybroker/broker |
| SQL query should be supplied in the "sql" field of the JSON body: |
| </p><pre class="programlisting"> |
| { |
| "sql": "select * from broker" |
| } |
| </pre><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-SQL-Query-Format"></a>SQL Query Format</h5></div></div></div><p> |
| SQL keywords, operators and functions are case-insensitive, so are domain names (object types) specified in |
| the <code class="literal">FROM</code> clause. Field names specified in the <code class="literal">SELECT</code> clause are case-sensitive. |
| Following queries are similar: |
| </p><pre class="programlisting"> |
| { |
| "sql": "SELECT name FROM BROKER" |
| } |
| </pre><pre class="programlisting"> |
| { |
| "sql": "SELECT name FROM broker" |
| } |
| </pre><pre class="programlisting"> |
| { |
| "sql": "select name from broker" |
| } |
| </pre><p> |
| They will return the same output. |
| When an entity field name is written in wrong case or misspelled, an error will be returned. |
| For example, following query |
| </p><pre class="programlisting"> |
| { |
| "sql": "SELECT NAME FROM BROKER" |
| } |
| </pre><p> |
| has field <code class="literal">NAME</code> written in upper case, which will result in an error: |
| </p><pre class="programlisting"> |
| { |
| "errorMessage": "Domain 'BROKER' does not contain field 'NAME'" |
| } |
| </pre><p> |
| In this document many SQL queries are split into several lines for better readability, but JSON format does not support |
| multiline string fields. Therefore, even the long SQL queries should be placed in <code class="literal">sql</code> field of the JSON body as a |
| single line. |
| Aside from SQL query several configuration parameters can be provided to influence output format: |
| </p><p> |
| </p><div class="table"><a id="d0e2927"></a><p class="title"><strong>Table 6.6. Additional Request Parameters</strong></p><div class="table-contents"><table summary="Additional Request Parameters" border="1"><colgroup><col class="field-name" /><col class="description" /></colgroup><thead><tr><th>Field Name</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>dateTimeFormat</p> |
| </td><td> |
| <p>Format of the datetime fields, possible values: LONG, STRING</p> |
| </td></tr><tr><td> |
| <p>dateTimePattern</p> |
| </td><td> |
| <p>Pattern for datetime fields formatting, e.g. yyyy-MM-dd HH:mm:ss</p> |
| </td></tr><tr><td> |
| <p>decimalDigits</p> |
| </td><td> |
| <p>Amount of decimal digits</p> |
| </td></tr><tr><td> |
| <p>roundingMode</p> |
| </td><td> |
| <p>Rounding mode for arithmetic operations, possible values UP, DOWN, CEILING, FLOOR, |
| HALF_UP, HALF_DOWN, HALF_EVEN, UNNECESSARY |
| </p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Datetime-Format"></a>Datetime Format</h5></div></div></div><p> |
| When datetime format is specified as <code class="literal">LONG</code>, datetime fields will be returned as |
| milliseconds from UNIX epoch. So, following query |
| </p><pre class="programlisting"> |
| { |
| "sql": "select id, name, createdTime from broker", |
| "dateTimeFormat": "LONG" |
| } |
| </pre><p> |
| returns following result: |
| </p><pre class="programlisting"> |
| { |
| "results": [ |
| { |
| "id": "ce8bbaf0-3efa-4176-889a-7987ac1988cc", |
| "name": "broker", |
| "createdTime": 1645195849272 |
| } |
| ], |
| "total": 1 |
| } |
| </pre><p> |
| In opposite the query |
| </p><pre class="programlisting"> |
| { |
| "sql": "select id, name, createdTime from broker", |
| "dateTimeFormat": "STRING" |
| } |
| </pre><p> |
| returns following result: |
| </p><pre class="programlisting"> |
| { |
| "results": [ |
| { |
| "id": "ce8bbaf0-3efa-4176-889a-7987ac1988cc", |
| "name": "broker", |
| "createdTime": "2022-02-18 15:50:49.272" |
| } |
| ], |
| "total": 1 |
| } |
| </pre></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Datetime-Pattern"></a>Datetime Pattern</h5></div></div></div><p> |
| The default format of the string datetime representation is "yyyy-MM-DD HH:mm:ss.SSS". |
| It can be changed using the parameter <code class="literal">dateTimePattern</code>. |
| The query |
| </p><pre class="programlisting"> |
| { |
| "sql": "select id, name, createdTime from broker", |
| "dateTimeFormat": "STRING", |
| "dateTimePattern": "yyyy/MM/dd HH:mm:ss.SSS" |
| } |
| </pre><p> |
| returns following result |
| </p><pre class="programlisting"> |
| { |
| "results": [ |
| { |
| "id": "ce8bbaf0-3efa-4176-889a-7987ac1988cc", |
| "name": "broker", |
| "createdTime": "2022/02/18 15:50:49.272" |
| } |
| ], |
| "total": 1 |
| } |
| </pre></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Decimal-Digits"></a>Decimal Digits</h5></div></div></div><p> |
| By default, decimal digits value is 6, meaning there will be 6 digits after decimal point. |
| For example, following query |
| </p><pre class="programlisting"> |
| { |
| "sql": "select avg(queueDepthMessages) from queue" |
| } |
| </pre><p> |
| returns following result: |
| </p><pre class="programlisting"> |
| { |
| "results": [ |
| { |
| "avg(queueDepthMessages)": 0.437227 |
| } |
| ], |
| "total": 1 |
| } |
| </pre><p> |
| This behavior can be changed for each value separately using ROUND or TRUNC functions, but can also be changed for |
| the whole query result by supplying <code class="literal">decimalDigits</code> parameter. Following query |
| </p><pre class="programlisting"> |
| { |
| "sql": "select avg(queueDepthMessages) from queue", |
| "decimalDigits": 2 |
| } |
| </pre><p> |
| returns following result: |
| </p><pre class="programlisting"> |
| { |
| "results": [ |
| { |
| "avg(queueDepthMessages)": 0.43 |
| } |
| ], |
| "total": 1 |
| } |
| </pre></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Rounding-Mode"></a>Rounding Mode</h5></div></div></div><p> |
| Rounding mode affects how results of the arithmetic operations will be rounded. The rules of applying different rounding |
| modes can be found in appropriate <a class="link" href="https://docs.oracle.com/javase/8/docs/api/java/math/RoundingMode.html" target="_top">javadoc</a>. |
| Default rounding mode is HALF_UP. Changing rounding mode will affect division operations, but will not affect results of |
| ROUND() and TRUNC() functions (which always use rounding mode HALF_UP and HALF_DOWN appropriately). |
| Following query |
| </p><pre class="programlisting"> |
| { |
| "sql": "select 2/3", |
| "decimalDigits": 2, |
| "roundingMode": "DOWN" |
| } |
| </pre><p> |
| uses rounding mode <code class="literal">DOWN</code> and returns following result: |
| </p><pre class="programlisting"> |
| { |
| "results": [ |
| { |
| "2/3": 0.66 |
| } |
| ], |
| "total": 1 |
| } |
| </pre><p> |
| When rounding mode will be changed to <code class="literal">UP</code> |
| </p><pre class="programlisting"> |
| { |
| "sql": "select 2/3", |
| "decimalDigits": 2, |
| "roundingMode": "UP" |
| } |
| </pre><p> |
| result will be changed as well: |
| </p><pre class="programlisting"> |
| { |
| "results": [ |
| { |
| "2/3": 0.67 |
| } |
| ], |
| "total": 1 |
| } |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Object-Types"></a>6.3.12.4. Object Types (Domains)</h4></div></div></div><p> |
| Object types or domains to query from are specified in the "FROM" clause. The broker object hierarchy can be retrieved |
| using an endpoint http://<hostname>:<port>/service/metadata |
| |
| Alternatively following SQL query can be fired |
| </p><pre class="programlisting"> |
| { |
| "sql": "select * from domain" |
| } |
| </pre><p> |
| returning similar result: |
| </p><pre class="programlisting"> |
| { |
| "results": [ |
| { |
| "name": "AccessControlProvider" |
| }, |
| { |
| "name": "AclRule" |
| }, |
| { |
| "name": "AuthenticationProvider" |
| }, |
| { |
| "name": "Binding" |
| }, |
| { |
| "name": "BrokerConnectionLimitProvider" |
| }, |
| { |
| "name": "BrokerLogInclusionRule" |
| }, |
| { |
| "name": "BrokerLogger" |
| }, |
| { |
| "name": "Certificate" |
| }, |
| { |
| "name": "Connection" |
| }, |
| { |
| "name": "ConnectionLimitRule" |
| }, |
| { |
| "name": "Consumer" |
| }, |
| { |
| "name": "Domain" |
| }, |
| { |
| "name": "Exchange" |
| }, |
| { |
| "name": "Group" |
| }, |
| { |
| "name": "GroupMember" |
| }, |
| { |
| "name": "GroupProvider" |
| }, |
| { |
| "name": "KeyStore" |
| }, |
| { |
| "name": "Plugin" |
| }, |
| { |
| "name": "Port" |
| }, |
| { |
| "name": "Queue" |
| }, |
| { |
| "name": "RemoteReplicationNode" |
| }, |
| { |
| "name": "Session" |
| }, |
| { |
| "name": "TrustStore" |
| }, |
| { |
| "name": "User" |
| }, |
| { |
| "name": "VirtualHost" |
| }, |
| { |
| "name": "VirtualHostAccessControlProvider" |
| }, |
| { |
| "name": "VirtualHostAlias" |
| }, |
| { |
| "name": "VirtualHostConnectionLimitProvider" |
| }, |
| { |
| "name": "VirtualHostLogInclusionRule" |
| }, |
| { |
| "name": "VirtualHostLogger" |
| }, |
| { |
| "name": "VirtualHostNode" |
| } |
| ], |
| "total": 31 |
| } |
| </pre><p> |
| In addition to the object types supported by broker query REST API, following object types (domains) |
| can be used as well: |
| </p><p> |
| </p><div class="table"><a id="d0e3086"></a><p class="title"><strong>Table 6.7. Query Engine Domains</strong></p><div class="table-contents"><table summary="Query Engine Domains" border="1"><colgroup><col class="domain" /></colgroup><thead><tr><th>Domain</th></tr></thead><tbody><tr><td> |
| <p>AclRule</p> |
| </td></tr><tr><td> |
| <p>Binding</p> |
| </td></tr><tr><td> |
| <p>Certificate</p> |
| </td></tr><tr><td> |
| <p>ConnectionLimitRule</p> |
| </td></tr><tr><td> |
| <p>Domain</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p><p> |
| Those objects do not belong to the broker object hierarchy (as they don't descend from ConfiguredObject), they were |
| added to make queries against listed domains more simple. |
| For example, following query |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM AclRule |
| WHERE identity = 'amqp_user1' |
| </pre><p> |
| returns following result: |
| </p><pre class="programlisting"> |
| { |
| "results": [ |
| { |
| "identity": "amqp_user1", |
| "attributes": {}, |
| "action": { |
| "objectType": "VIRTUALHOST", |
| "properties": { |
| "name": null, |
| "empty": true |
| }, |
| "operation": "ACCESS" |
| }, |
| "objectType": null, |
| "operation": null, |
| "outcome": "ALLOW_LOG" |
| }, |
| { |
| "identity": "amqp_user1", |
| "attributes": { |
| "NAME": "request.amqp_user1", |
| "ROUTING_KEY": "*" |
| }, |
| "action": { |
| "objectType": "EXCHANGE", |
| "properties": { |
| "name": "request.amqp_user1", |
| "empty": false |
| }, |
| "operation": "PUBLISH" |
| }, |
| "objectType": null, |
| "operation": null, |
| "outcome": "ALLOW" |
| }, |
| { |
| "identity": "amqp_user1", |
| "attributes": { |
| "NAME": "broadcast.amqp_user1.*" |
| }, |
| "action": { |
| "objectType": "QUEUE", |
| "properties": { |
| "name": "broadcast.amqp_user1.*", |
| "empty": false |
| }, |
| "operation": "CONSUME" |
| }, |
| "objectType": null, |
| "operation": null, |
| "outcome": "ALLOW_LOG" |
| }, |
| { |
| "identity": "amqp_user1", |
| "attributes": { |
| "NAME": "response.amqp_user1" |
| }, |
| "action": { |
| "objectType": "QUEUE", |
| "properties": { |
| "name": "response.amqp_user1", |
| "empty": false |
| }, |
| "operation": "CONSUME" |
| }, |
| "objectType": null, |
| "operation": null, |
| "outcome": "ALLOW_LOG" |
| } |
| ], |
| "total": 4 |
| } |
| </pre><p> |
| Please note, that keyword <code class="literal">FROM</code> isn't mandatory, it is possible to execute queries without it, when the result shouldn't |
| retrieve any data from broker. Few examples of such queries would be: |
| </p><pre class="programlisting"> |
| SELECT CURRENT_TIMESTAMP() |
| </pre><pre class="programlisting"> |
| SELECT DATE(CURRENT_TIMESTAMP()) |
| </pre><pre class="programlisting"> |
| SELECT (2 + 10) / 3 |
| </pre><pre class="programlisting"> |
| SELECT 2 * 5 > 12 |
| </pre></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Filtering-Results"></a>6.3.12.5. Filtering Results</h4></div></div></div><p> |
| Filtering is achieved by using different operators groups in a <code class="literal">WHERE</code> clause. Operators |
| can be divided into comparison operators, conditional operators and logical operators. |
| </p><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Broker-Data-Types"></a>Broker Data Types</h5></div></div></div><p> |
| Broker entities have fields belonging to different java types: primitives (boolean, int, long, double), strings, |
| datetime <a class="link" href="https://docs.oracle.com/javase/8/docs/api/java/util/Date.html" target="_top">Date</a>, |
| <a class="link" href="https://docs.oracle.com/javase/8/docs/api/java/time/LocalDate.html" target="_top">LocalDate</a>, |
| <a class="link" href="https://docs.oracle.com/javase/8/docs/api/java/time/LocalDateTime.html" target="_top">LocalDateTime</a>, |
| <a class="link" href="https://docs.oracle.com/javase/8/docs/api/java/time/Instant.html" target="_top">Instant</a>. |
| Object IDs are usually of <a class="link" href="https://docs.oracle.com/javase/8/docs/api/java/util/UUID.html" target="_top">UUID</a> type. |
| Many values are enums. |
| |
| When comparing field values, they follow some implicit casting rules: enums and UUIDs are cast to strings, |
| datetime values are cast to <a class="link" href="https://docs.oracle.com/javase/8/docs/api/java/time/Instant.html" target="_top">Instant</a>, |
| numeric values are cast to <a class="link" href="https://docs.oracle.com/javase/8/docs/api/java/math/BigDecimal.html" target="_top">BigDecimal</a>. |
| |
| When casting string value to date, by default is used pattern "uuuu-MM-dd". |
| |
| That allows to run following queries: |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM certificate |
| WHERE DATE(validUntil) = '2020-12-31' |
| </pre><p> |
| Here string value is implicitly cast to Instant and both value are compared as Instant instances. |
| When casting string to datetime, by default is used pattern "uuuu-MM-dd HH:mm:ss" with optional 0-6 second fractions. |
| That allows to run following queries: |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM certificate |
| WHERE DATE(validUntil) > '2020-12-31 23:59:59.999' |
| </pre><p> |
| Here string value is implicitly cast to Instant as well and both value are compared as Instant instances. |
| It is important to compare values of the same type, otherwise an error may be returned or query may be evaluated |
| erroneously. For example, following query |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM queue |
| WHERE durable = 'true' |
| </pre><p> |
| will return an empty result, because field <code class="literal">durable</code> is of boolean type and comparing boolean value with a string 'true' |
| will always return false. The correct query should be |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM queue |
| WHERE durable = true |
| </pre></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Broker-Keyword-DISTINCT"></a>Keyword DISTINCT</h5></div></div></div><p> |
| To remove duplicates from the results keyword "DISTINCT" can be used. For example, query |
| </p><pre class="programlisting"> |
| SELECT overflowPolicy |
| FROM queue |
| </pre><p> |
| will return results for all queues, but query |
| </p><pre class="programlisting"> |
| SELECT DISTINCT overflowPolicy |
| FROM queue |
| </pre><p> |
| will return only several values. |
| </p></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Comparison-Operators"></a>Comparison Operators</h5></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-BETWEEN"></a>BETWEEN</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-BETWEEN-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN |
| operator is inclusive: begin and end values are included. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-BETWEEN-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| BETWEEN(expression1 AND expression2) |
| BETWEEN(expression1, expression2) |
| BETWEEN expression1 AND expression2 |
| BETWEEN expression1, expression2 |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-BETWEEN-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e3233"></a><p class="title"><strong>Table 6.8. BETWEEN Parameters</strong></p><div class="table-contents"><table summary="BETWEEN Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>expression1</p> |
| </td><td> |
| <p>Lower threshold</p> |
| </td></tr><tr><td> |
| <p>expression2</p> |
| </td><td> |
| <p>Higher threshold</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-BETWEEN-Examples"></a>Examples</h6></div></div></div><p> |
| Find names of the queues having depth in messages between 1000 and 2000 |
| </p><pre class="programlisting"> |
| SELECT |
| name |
| FROM queue |
| WHERE queueDepthMessages BETWEEN (1000, 2000) |
| </pre><p> |
| Find certificates expiring between 2024-12-01 and 2024-12-31 |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM certificate |
| WHERE DATE(validUntil) BETWEEN ('2024-12-01' AND '2024-12-31') |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-EQUAL"></a>EQUAL</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-EQUAL-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| Equal operator is designated using "=" character. It allows comparison of boolean, datetime, |
| numeric and string values. Both compared values must have same type. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-EQUAL-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| expression1 = expression2 |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-EQUAL-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e3298"></a><p class="title"><strong>Table 6.9. EQUAL Parameters</strong></p><div class="table-contents"><table summary="EQUAL Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>expression1</p> |
| </td><td> |
| <p>Expression to compare to</p> |
| </td></tr><tr><td> |
| <p>expression2</p> |
| </td><td> |
| <p>Expression to compare with</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-EQUAL-Examples"></a>Examples</h6></div></div></div><p> |
| Find queue by name |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM queue |
| WHERE name = 'broadcast.amqp_user1.Public' |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-GREATER-THAN"></a>GREATER THAN</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-GREATER-THAN-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| Greater than operator is designated using ">" character. It allows comparison of datetime, |
| numeric and string values. Both compared values must have same type. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-GREATER-THAN-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| expression1 > expression2 |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-GREATER-THAN-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e3359"></a><p class="title"><strong>Table 6.10. GREATER THAN Parameters</strong></p><div class="table-contents"><table summary="GREATER THAN Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>expression1</p> |
| </td><td> |
| <p>Expression to compare to</p> |
| </td></tr><tr><td> |
| <p>expression2</p> |
| </td><td> |
| <p>Expression to compare with</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-GREATER-THAN-Examples"></a>Examples</h6></div></div></div><p> |
| Find queues having message depth greater than 1000 |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM queue |
| WHERE queueDepthMessages > 1000 |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-GREATER-THAN-OR-EQUAL"></a>GREATER THAN OR EQUAL</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-GREATER-THAN-OR-EQUAL-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| Greater than or equal operator is designated using ">=" characters. It allows comparison of |
| datetime, numeric and string values. Both compared values must have same type. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-GREATER-THAN-OR-EQUAL-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| expression1 >= expression2 |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-GREATER-THAN-OR-EQUAL-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e3420"></a><p class="title"><strong>Table 6.11. GREATER THAN OR EQUAL Parameters</strong></p><div class="table-contents"><table summary="GREATER THAN OR EQUAL Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>expression1</p> |
| </td><td> |
| <p>Expression to compare to</p> |
| </td></tr><tr><td> |
| <p>expression2</p> |
| </td><td> |
| <p>Expression to compare with</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-GREATER-THAN-OR-EQUAL-Examples"></a>Examples</h6></div></div></div><p> |
| Find queues having message depth greater than or equal to 1000 |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM queue |
| WHERE queueDepthMessages >= 1000 |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-IN"></a>IN</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-IN-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The IN operator allows specifying multiple values in a WHERE clause. The IN operator is a |
| shorthand for multiple OR conditions. |
| Alternatively IN operator can be used with a subquery. When a subquery is used, it should |
| return only one value, otherwise an error will be returned. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-IN-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| expression IN (value_1, value_2, ..., value_n) |
| expression IN (SELECT value FROM domain) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-IN-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e3481"></a><p class="title"><strong>Table 6.12. IN Parameters</strong></p><div class="table-contents"><table summary="IN Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>expression</p> |
| </td><td> |
| <p>Expression to compare to</p> |
| </td></tr><tr><td> |
| <p>value_1 - value_n</p> |
| </td><td> |
| <p>Values to compare with</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-IN-Examples"></a>Examples</h6></div></div></div><p> |
| Find bindings having destination queue belonging to the list |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM binding |
| WHERE destination IN ('broadcast.amqp_user1.Service1', 'broadcast.amqp_user1.Service2', 'broadcast.amqp_user1.Service3') |
| </pre><p> |
| Find bindings having destination queue with message depth between 1000 and 2000 |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM binding |
| WHERE destination IN (SELECT name FROM queue WHERE queueDepthMessages BETWEEN (1000, 2000)) |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-IS-NULL"></a>IS NULL</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-IS-NULL-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The IS NULL operator is used to compare ordinary values with NULL values. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-IS-NULL-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| expression IS NULL |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-IS-NULL-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e3546"></a><p class="title"><strong>Table 6.13. IS NULL Parameters</strong></p><div class="table-contents"><table summary="IS NULL Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>expression</p> |
| </td><td> |
| <p>Expression to compare to NULL</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-IS-NULL-Examples"></a>Examples</h6></div></div></div><p> |
| Find queues having NULL description |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM queue |
| WHERE description IS NULL |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LESS-THAN"></a>LESS THAN</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LESS-THAN-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| Less than operator is designated using "<" character. It allows comparison of datetime, numeric and string values. |
| Both compared values must have same type. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LESS-THAN-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| expression1 < expression2 |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LESS-THAN-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e3596"></a><p class="title"><strong>Table 6.14. LESS THAN Parameters</strong></p><div class="table-contents"><table summary="LESS THAN Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>expression1</p> |
| </td><td> |
| <p>Expression to compare to</p> |
| </td></tr><tr><td> |
| <p>expression2</p> |
| </td><td> |
| <p>Expression to compare with</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LESS-THAN-Examples"></a>Examples</h6></div></div></div><p> |
| Find queues having message depth less than 1000 |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM queue |
| WHERE queueDepthMessages < 1000 |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LESS-THAN-OR-EQUAL"></a>LESS THAN OR EQUAL</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LESS-THAN-OR-EQUAL-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| Less than or equal operator is designated using "<=" characters. It allows comparison of |
| datetime, numeric and string values. Both compared values must have same type. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LESS-THAN-OR-EQUAL-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| expression1 <= expression2 |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LESS-THAN-OR-EQUAL-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e3657"></a><p class="title"><strong>Table 6.15. LESS THAN OR EQUAL Parameters</strong></p><div class="table-contents"><table summary="LESS THAN OR EQUAL Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>expression1</p> |
| </td><td> |
| <p>Expression to compare to</p> |
| </td></tr><tr><td> |
| <p>expression2</p> |
| </td><td> |
| <p>Expression to compare with</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LESS-THAN-OR-EQUAL-Examples"></a>Examples</h6></div></div></div><p> |
| Find queues having message depth less than or equal to 1000 |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM queue |
| WHERE queueDepthMessages <= 1000 |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LIKE"></a>LIKE</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LIKE-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The LIKE operator is used to search for a specified pattern in a string. There are two |
| wildcards often used in conjunction with the LIKE operator: the percent sign "%" represents |
| zero, one, or multiple characters; the question mark "?" represents one, single character. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LIKE-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| expression LIKE pattern |
| expression LIKE pattern ESCAPE escapeCharacter |
| expression LIKE (pattern) |
| expression LIKE (pattern ESCAPE escapeCharacter) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LIKE-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e3718"></a><p class="title"><strong>Table 6.16. LIKE Parameters</strong></p><div class="table-contents"><table summary="LIKE Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>expression</p> |
| </td><td> |
| <p>Expression to compare to</p> |
| </td></tr><tr><td> |
| <p>pattern</p> |
| </td><td> |
| <p>Pattern to compare against</p> |
| </td></tr><tr><td> |
| <p>escapeCharacter</p> |
| </td><td> |
| <p>Character used to escape percent sign or question mark</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LIKE-Examples"></a>Examples</h6></div></div></div><p> |
| Find queues having name starting with a string "broadcast" |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM queue |
| WHERE name LIKE 'broadcast%' |
| </pre><p> |
| Find queues with name containing string "amqp_user1" |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM queue |
| WHERE name LIKE '%amqp_user1%' |
| </pre></div></div></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Conditional-Operators"></a>Conditional Operators</h5></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CASE"></a>CASE</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CASE-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The CASE statement goes through conditions and returns a value when the first condition is met |
| (like an if-then-else statement). So, once a condition is true, it will stop reading and return |
| the result. If no conditions are true, it returns the value in the ELSE clause. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CASE-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| CASE |
| WHEN condition1 THEN result1 |
| WHEN condition2 THEN result2 |
| WHEN conditionN THEN resultN |
| ELSE result |
| END |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CASE-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e3797"></a><p class="title"><strong>Table 6.17. CASE Parameters</strong></p><div class="table-contents"><table summary="CASE Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>condition1 - conditionN</p> |
| </td><td> |
| <p>Conditions to estimate</p> |
| </td></tr><tr><td> |
| <p>result1 - resultN</p> |
| </td><td> |
| <p>Results to return</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CASE-Examples"></a>Examples</h6></div></div></div><p> |
| Group queues into good (< 60% of max depth), bad (60% - 90% of max depth) and critical |
| (> 90% of max depth), count number of queues in each group. Consider queues with unlimited |
| depth being good. |
| </p><pre class="programlisting"> |
| SELECT |
| COUNT(*), |
| CASE |
| WHEN maximumQueueDepthMessages != -1 AND maximumQueueDepthBytes != -1 |
| AND (queueDepthMessages > maximumQueueDepthMessages * 0.9 OR queueDepthBytes > maximumQueueDepthBytes * 0.9) |
| THEN 'critical' |
| WHEN maximumQueueDepthMessages != -1 AND maximumQueueDepthBytes != -1 |
| AND queueDepthMessages BETWEEN (maximumQueueDepthMessages * 0.6 AND maximumQueueDepthMessages * 0.9) |
| OR queueDepthBytes BETWEEN (maximumQueueDepthBytes * 0.6 AND maximumQueueDepthBytes * 0.9) |
| THEN 'bad' |
| ELSE 'good' |
| END AS queueState |
| FROM queue |
| GROUP BY queueState |
| </pre></div></div></div><div class="section"><div class="titlepage" /><p> |
| The "AND" and "OR" operators are used to filter records based on more than one condition: the "AND" |
| operator displays a record if all the conditions separated by "AND" are TRUE. The "OR" operator |
| displays a record if any of the conditions separated by "OR" is TRUE. The "NOT" operator displays a |
| record if the condition(s) is NOT TRUE. |
| </p></div></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Sorting-Results"></a>6.3.12.6. Sorting Results</h4></div></div></div><p> |
| Default sorting order is ascending, default sorting field is `name` for domains having this field. Results |
| of the following query will be sorted ascending by name: |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM queue |
| </pre><p>Few exceptions are following:</p><p> |
| </p><div class="table"><a id="d0e3854"></a><p class="title"><strong>Table 6.18. Default sorting fields</strong></p><div class="table-contents"><table summary="Default sorting fields" border="1"><colgroup><col class="domain" /><col class="field-name" /></colgroup><thead><tr><th>Domain</th><th>Default sorting field</th></tr></thead><tbody><tr><td> |
| <p>AclRule</p> |
| </td><td> |
| <p>identity</p> |
| </td></tr><tr><td> |
| <p>Certificate</p> |
| </td><td> |
| <p>alias</p> |
| </td></tr><tr><td> |
| <p>ConnectionLimitRule</p> |
| </td><td> |
| <p>identity</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p><p>Results of the following query will be sorted ascending by alias:</p><pre class="programlisting"> |
| SELECT * |
| FROM certificate |
| </pre><p> |
| To apply another sorting rules clause `ORDER BY` should be used. It may contain one of the fields specified |
| in the `SELECT` clause: |
| </p><pre class="programlisting"> |
| SELECT |
| id, name, state |
| FROM queue |
| ORDER BY name |
| </pre><p> |
| Alternatively it may contain fields not specified in `SELECT` clause: |
| </p><pre class="programlisting"> |
| SELECT |
| id, name, state |
| FROM queue |
| ORDER BY overflowPolicy |
| </pre><p> |
| Instead of using field names or aliases items in the `ORDER BY` clause can also be referenced by ordinal - |
| the numeric value of their order of appearance in the `SELECT` clause. For example, following query |
| </p><pre class="programlisting"> |
| SELECT |
| name, overflowPolicy |
| FROM queue |
| ORDER BY 2 DESC, 1 ASC |
| </pre><p> |
| will return results sorted in descending order by overflow policy and inside the groups with the same overflow policy |
| name results will be sorted by queue name in ascending order. |
| </p></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Aggregation"></a>6.3.12.7. Aggregation</h4></div></div></div><p> |
| Aggregation is achieved using functions AVG(), COUNT(), MAX(), MIN() and SUM(). |
| It's important to remember, that aggregation functions don't consider NULL values. For example, following query |
| </p><pre class="programlisting"> |
| SELECT COUNT(description) |
| FROM queue |
| </pre><p> |
| will return count of queues having non-null value of a field `description`. |
| To consider NULL values, they should be handled using COALESCE() function or CASE |
| operator: |
| </p><pre class="programlisting"> |
| SELECT COUNT(COALESCE(description, '')) |
| FROM queue |
| </pre><p>Alternatively</p><pre class="programlisting"> |
| SELECT COUNT(CASE WHEN description IS NULL THEN '' ELSE description END) |
| FROM queue |
| </pre><p>Several aggregation functions can be used together in the same query:</p><pre class="programlisting"> |
| SELECT |
| COUNT(*), |
| AVG(queueDepthMessages), |
| SUM(queueDepthMessages), |
| SUM(queueDepthBytes), |
| MIN(queueDepthMessages), |
| MAX(queueDepthMessages), |
| MIN(queueDepthBytes), |
| MAX(queueDepthBytes) |
| FROM queue |
| </pre></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Grouping"></a>6.3.12.8. Grouping</h4></div></div></div><p> |
| Grouping of the aggregated results can be achieved using the `GROUP BY` clause. |
| For example, following query finds count of ACL rules for each user and output them in descending order: |
| </p><pre class="programlisting"> |
| SELECT |
| COUNT(*) AS cnt, identity |
| FROM aclrule |
| GROUP BY identity |
| ORDER BY 1 DESC |
| </pre><p>The result of the query:</p><pre class="programlisting"> |
| { |
| "results": [ |
| { |
| "cnt": { |
| "amqp_user1": 6, |
| "amqp_user2": 4, |
| "amqp_user3": 4, |
| ... some results ommited ... |
| "amqp_user97": 2, |
| "amqp_user98": 1, |
| "amqp_user99": 1 |
| } |
| } |
| ], |
| "total": 1 |
| } |
| </pre><p> |
| To filter the grouped result `HAVING` clause can be used: |
| </p><pre class="programlisting"> |
| SELECT |
| overflowPolicy, COUNT(*) |
| FROM queue |
| GROUP BY overflowPolicy |
| HAVING SUM(queueDepthMessages) > 1000 |
| </pre></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Functions"></a>6.3.12.9. Functions</h4></div></div></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Aggregation-Functions"></a>Aggregation Functions</h5></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-AVG"></a>AVG</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-AVG-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The AVG() function returns the average value of a collection. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-AVG-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| AVG(expression) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-AVG-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e3977"></a><p class="title"><strong>Table 6.19. AVG Parameters</strong></p><div class="table-contents"><table summary="AVG Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>expression</p> |
| </td><td> |
| <p>Expression result average value of which should be found</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-AVG-Examples"></a>Examples</h6></div></div></div><p> |
| Find average amount of bytes used by queues with names starting with "broadcast" |
| </p><pre class="programlisting"> |
| SELECT |
| AVG(queueDepthBytes) |
| FROM queue |
| WHERE name LIKE 'broadcast%' |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-COUNT"></a>COUNT</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-COUNT-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The COUNT() function returns the number of items that matches a specified criterion. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-COUNT-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| COUNT(expression) |
| COUNT(DISTINCT expression) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-COUNT-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e4027"></a><p class="title"><strong>Table 6.20. COUNT Parameters</strong></p><div class="table-contents"><table summary="COUNT Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>expression</p> |
| </td><td> |
| <p>Expression result of which should be counted</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-COUNT-Examples"></a>Examples</h6></div></div></div><p> |
| Find amount of queues with names starting with "broadcast" |
| </p><pre class="programlisting"> |
| SELECT |
| COUNT (*) |
| FROM queue |
| WHERE name LIKE 'broadcast%' |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-MAX"></a>MAX</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-MAX-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The MAX() function returns the maximum value of a collection. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-MAX-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| MAX(expression) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-MAX-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e4077"></a><p class="title"><strong>Table 6.21. MAX Parameters</strong></p><div class="table-contents"><table summary="MAX Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>expression</p> |
| </td><td> |
| <p>Expression result maximal value of which should be found</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-MAX-Examples"></a>Examples</h6></div></div></div><p> |
| Find maximal amount of bytes used by queues with names starting with "broadcast" |
| </p><pre class="programlisting"> |
| SELECT |
| MAX(queueDepthBytes) |
| FROM queue |
| WHERE name LIKE 'broadcast%' |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-MIN"></a>MIN</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-MIN-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The MIN() function returns the minimum value of a collection. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-MIN-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| MIN(expression) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-MIN-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e4127"></a><p class="title"><strong>Table 6.22. MIN Parameters</strong></p><div class="table-contents"><table summary="MIN Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>expression</p> |
| </td><td> |
| <p>Expression result minimal value of which should be found</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-MIN-Examples"></a>Examples</h6></div></div></div><p> |
| Find minimal amount of bytes used by queues with names starting with "broadcast" |
| </p><pre class="programlisting"> |
| SELECT |
| MIN(queueDepthBytes) |
| FROM queue |
| WHERE name LIKE 'broadcast%' |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-SUM"></a>SUM</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-SUM-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The SUM() function returns the total sum of a numeric collection. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-SUM-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| SUM(expression) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-SUM-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e4177"></a><p class="title"><strong>Table 6.23. SUM Parameters</strong></p><div class="table-contents"><table summary="SUM Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>expression</p> |
| </td><td> |
| <p>Expression result of which should be summed</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-SUM-Examples"></a>Examples</h6></div></div></div><p> |
| Find amount of bytes used by queues having names starting with "broadcast" |
| </p><pre class="programlisting"> |
| SELECT |
| SUM(queueDepthBytes) |
| FROM queue |
| WHERE name LIKE 'broadcast%' |
| </pre></div></div></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Datetime-Functions"></a>Datetime Functions</h5></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CURRENT_TIMESTAMP"></a>CURRENT_TIMESTAMP</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CURRENT_TIMESTAMP-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The CURRENT_TIMESTAMP() function returns current date and time. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CURRENT_TIMESTAMP-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| CURRENT_TIMESTAMP() |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CURRENT_TIMESTAMP-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>Function has no parameters</p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CURRENT_TIMESTAMP-Examples"></a>Examples</h6></div></div></div><p> |
| Find current date and time |
| </p><pre class="programlisting"> |
| SELECT CURRENT_TIMESTAMP() |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATE"></a>DATE</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATE-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The DATE() function extracts the date part from a datetime expression. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATE-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| DATE(expression) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATE-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e4255"></a><p class="title"><strong>Table 6.24. DATE Parameters</strong></p><div class="table-contents"><table summary="DATE Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>expression</p> |
| </td><td> |
| <p>A valid date/datetime value</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATE-Examples"></a>Examples</h6></div></div></div><p> |
| Find certificates having validFrom equal to 01. January 2020 |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM certificate |
| WHERE DATE(validFrom) = '2020-01-01' |
| </pre><p> |
| Find certificates expiring between 01. January 2020 and 10. January 2020 |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM certificate |
| WHERE DATE(validUntil) BETWEEN ('2020-01-01', '2020-01-10') |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATEADD"></a>DATEADD</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATEADD-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The DATEADD() function adds a time/date interval to a date and returns the date. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATEADD-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| DATEADD(TIMEUNIT, VALUE, DATE) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATEADD-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e4309"></a><p class="title"><strong>Table 6.25. DATEADD Parameters</strong></p><div class="table-contents"><table summary="DATEADD Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>TIMEUNIT</p> |
| </td><td> |
| <p> |
| The type of time unit to add. |
| Can be one of the following values: |
| YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND |
| </p> |
| </td></tr><tr><td> |
| <p>VALUE</p> |
| </td><td> |
| <p> |
| The value of the time/date interval to add. |
| Both positive and negative values are allowed |
| </p> |
| </td></tr><tr><td> |
| <p>DATE</p> |
| </td><td> |
| <p>The date to be modified</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATEADD-Examples"></a>Examples</h6></div></div></div><p> |
| Find certificates expiring in less than 30 days |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM certificate |
| WHERE DATEADD(DAY, -30, validUntil) < CURRENT_TIMESTAMP() |
| LIMIT 10 OFFSET 0 |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATEDIFF"></a>DATEDIFF</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATEDIFF-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The DATEDIFF() function returns the number of time units between two date values. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATEDIFF-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| DATEDIFF(TIMEUNIT, DATE1, DATE2) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATEDIFF-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e4381"></a><p class="title"><strong>Table 6.26. DATEDIFF Parameters</strong></p><div class="table-contents"><table summary="DATEDIFF Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>TIMEUNIT</p> |
| </td><td> |
| <p> |
| Time unit to calculate difference. |
| Can be one of the following values: |
| YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND |
| </p> |
| </td></tr><tr><td> |
| <p>DATE1</p> |
| </td><td> |
| <p> |
| Start date |
| </p> |
| </td></tr><tr><td> |
| <p>DATE2</p> |
| </td><td> |
| <p> |
| End date |
| </p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATEDIFF-Examples"></a>Examples</h6></div></div></div><p> |
| Find certificate aliases and days until expiry |
| </p><pre class="programlisting"> |
| SELECT |
| alias, |
| DATEDIFF(DAY, CURRENT_TIMESTAMP(), validUntil) AS days_until_expiry |
| FROM certificate |
| LIMIT 10 OFFSET 0 |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-EXTRACT"></a>EXTRACT</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-EXTRACT-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The EXTRACT() function extracts a part from a given date. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-EXTRACT-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| EXTRACT(TIMEUNIT FROM DATE) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-EXTRACT-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e4453"></a><p class="title"><strong>Table 6.27. EXTRACT Parameters</strong></p><div class="table-contents"><table summary="EXTRACT Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>TIMEUNIT</p> |
| </td><td> |
| <p> |
| Time unit to extract. |
| Can be one of the following values: |
| YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND |
| </p> |
| </td></tr><tr><td> |
| <p>DATE</p> |
| </td><td> |
| <p>The date to extract a part from</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-EXTRACT-Examples"></a>Examples</h6></div></div></div><p> |
| Find certificates issued in January 2020 |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM certificate |
| WHERE EXTRACT(YEAR FROM validFrom) = 2020 |
| AND EXTRACT(MONTH FROM validFrom) = 1 |
| LIMIT 10 OFFSET 0 |
| </pre></div></div></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Null-Functions"></a>NULL Functions</h5></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-COALESCE"></a>COALESCE</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-COALESCE-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The COALESCE() function returns the first non-null value in a list. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-COALESCE-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| COALESCE(value_1, value_2, ...., value_n) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-COALESCE-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e4517"></a><p class="title"><strong>Table 6.28. COALESCE Parameters</strong></p><div class="table-contents"><table summary="COALESCE Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>value_1 - value_n</p> |
| </td><td> |
| <p>The values to test</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-COALESCE-Examples"></a>Examples</h6></div></div></div><p> |
| Find count of queues having NULL description |
| </p><pre class="programlisting"> |
| SELECT |
| COUNT(COALESCE(description, 'empty')) AS RESULT |
| FROM queue |
| HAVING COALESCE(description, 'empty') = 'empty' |
| </pre></div></div></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Numeric-Functions"></a>Numeric Functions</h5></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-ABS"></a>ABS</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-ABS-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The ABS() function returns the absolute value of a number. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-ABS-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| ABS(number) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-ABS-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e4570"></a><p class="title"><strong>Table 6.29. ABS Parameters</strong></p><div class="table-contents"><table summary="ABS Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>number</p> |
| </td><td> |
| <p>A numeric value</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-ABS-Examples"></a>Examples</h6></div></div></div><p> |
| Find absolute amount of days after the validFrom date of the certificates |
| </p><pre class="programlisting"> |
| SELECT |
| ABS(DATEDIFF(DAY, CURRENT_TIMESTAMP(), validFrom)) |
| FROM certificate |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-ROUND"></a>ROUND</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-ROUND-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The ROUND() function takes a numeric parameter and rounds it to the specified number |
| of decimal places. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-ROUND-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| ROUND(number, decimals) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-ROUND-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e4620"></a><p class="title"><strong>Table 6.30. ROUND Parameters</strong></p><div class="table-contents"><table summary="ROUND Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>number</p> |
| </td><td> |
| <p>The number to be rounded</p> |
| </td></tr><tr><td> |
| <p>decimals</p> |
| </td><td> |
| <p>The number of decimal places to round to</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-ROUND-Examples"></a>Examples</h6></div></div></div><p> |
| Find average queue depth in messages and round result to 2 decimal places |
| </p><pre class="programlisting"> |
| SELECT |
| ROUND(AVG(queueDepthMessages)) as result |
| FROM queue |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-TRUNC"></a>TRUNC</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-TRUNC-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The TRUNC() function takes a numeric parameter and truncates it to the specified number |
| of decimal places. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-TRUNC-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| TRUNC(number, decimals) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-TRUNC-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e4681"></a><p class="title"><strong>Table 6.31. TRUNC Parameters</strong></p><div class="table-contents"><table summary="TRUNC Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>number</p> |
| </td><td> |
| <p>The number to be truncated</p> |
| </td></tr><tr><td> |
| <p>decimals</p> |
| </td><td> |
| <p>The number of decimal places to truncate to</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-TRUNC-Examples"></a>Examples</h6></div></div></div><p> |
| Find average queue depth in messages and truncate result to 2 decimal places |
| </p><pre class="programlisting"> |
| SELECT |
| TRUNC(AVG(queueDepthMessages)) as result |
| FROM queue |
| </pre></div></div></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-String-Functions"></a>String Functions</h5></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CONCAT"></a>CONCAT</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CONCAT-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The CONCAT() function takes a variable number of arguments and concatenates them into a single string. |
| It requires a minimum of one input value, otherwise CONCAT will raise an error. CONCAT implicitly |
| converts all arguments to string types before concatenation. The implicit conversion to strings follows |
| the existing rules for data type conversions. If any argument is NULL, CONCAT returns NULL. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CONCAT-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| CONCAT(expression_1, expression_2, expression_3, ..., expression_n) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CONCAT-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e4745"></a><p class="title"><strong>Table 6.32. LENGTH Parameters</strong></p><div class="table-contents"><table summary="LENGTH Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>expression_1 - expression_n</p> |
| </td><td> |
| <p>The expressions to add together</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CONCAT-Examples"></a>Examples</h6></div></div></div><p> |
| Output certificate alias and validity dates using format "alias: validFrom - validUntil" |
| </p><pre class="programlisting"> |
| SELECT |
| CONCAT(alias, ': ', DATE(validFrom), ' - ', DATE(validUntil)) as validity |
| FROM certificate |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LENGTH"></a>LEN / LENGTH</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LENGTH-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The LEN() / LENGTH() function takes a string parameter and returns its length. |
| The implicit conversion to strings follows the existing rules for data |
| type conversions. If any argument is NULL, LEN / LENGTH returns 0. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LENGTH-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| LEN(string) |
| LENGTH(string) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LENGTH-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e4795"></a><p class="title"><strong>Table 6.33. LENGTH Parameters</strong></p><div class="table-contents"><table summary="LENGTH Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>string</p> |
| </td><td> |
| <p>The string to count the length for</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LENGTH-Examples"></a>Examples</h6></div></div></div><p> |
| Find certificate aliases having alias length greater than 10 |
| </p><pre class="programlisting"> |
| SELECT |
| alias |
| FROM certificate |
| WHERE LENGTH(alias) > 10 |
| LIMIT 10 OFFSET 0 |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LOWER"></a>LOWER</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LOWER-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The LOWER() function takes a string parameter and converts it to lower case. The implicit |
| conversion to strings follows the existing rules for data type conversions. |
| If argument is NULL, LOWER returns NULL. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LOWER-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| LOWER(string) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LOWER-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e4845"></a><p class="title"><strong>Table 6.34. LOWER Parameters</strong></p><div class="table-contents"><table summary="LOWER Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>string</p> |
| </td><td> |
| <p>The string to convert</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LOWER-Examples"></a>Examples</h6></div></div></div><p> |
| Filter connections by principal name (case-insensitive) |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM connection |
| WHERE LOWER(principal) = 'amqp_user1' |
| LIMIT 10 OFFSET 0 |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LTRIM"></a>LTRIM</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LTRIM-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The LTRIM() function removes leading spaces from a string. |
| If argument is NULL, RTRIM returns NULL. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LTRIM-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| LTRIM(string) |
| LTRIM(string, chars) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LTRIM-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e4895"></a><p class="title"><strong>Table 6.35. LTRIM Parameters</strong></p><div class="table-contents"><table summary="LTRIM Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>string</p> |
| </td><td> |
| <p>The string to remove leading and trailing spaces from</p> |
| </td></tr><tr><td> |
| <p>chars</p> |
| </td><td> |
| <p>Specific characters to remove</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LTRIM-Examples"></a>Examples</h6></div></div></div><p> |
| Find connection remote addresses |
| </p><pre class="programlisting"> |
| SELECT |
| LTRIM(remoteAddress, '/') AS remoteAddress |
| FROM connection |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-POSITION"></a>POSITION</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-POSITION-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The POSITION() function takes a search pattern and a source string as parameters and returns |
| the position of the first occurrence of a pattern in a source string. If the pattern is not |
| found within the source string, this function returns 0. |
| Optionally takes third integer parameter, defining from which position search should be started. |
| Third parameter should be an integer greater than 0. If source string is NULL, returns zero. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-POSITION-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| POSITION(pattern IN source) |
| POSITION(pattern IN source, startIndex) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-POSITION-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e4956"></a><p class="title"><strong>Table 6.36. POSITION Parameters</strong></p><div class="table-contents"><table summary="POSITION Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>pattern</p> |
| </td><td> |
| <p>The pattern to search for in source</p> |
| </td></tr><tr><td> |
| <p>source</p> |
| </td><td> |
| <p>The original string that will be searched</p> |
| </td></tr><tr><td> |
| <p>startIndex</p> |
| </td><td> |
| <p>The index from which search will be started</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-POSITION-Examples"></a>Examples</h6></div></div></div><p> |
| Find queues having string "broadcast" in their names |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM queue |
| WHERE POSITION('broadcast', name) > 0 |
| LIMIT 10 OFFSET 0 |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-REPLACE"></a>REPLACE</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-REPLACE-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The REPLACE() function replaces all occurrences of a substring within |
| a string, with a new substring. If source string is NULL, returns NULL. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-REPLACE-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| REPLACE(source, pattern, replacement) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-REPLACE-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e5028"></a><p class="title"><strong>Table 6.37. REPLACE Parameters</strong></p><div class="table-contents"><table summary="REPLACE Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>source</p> |
| </td><td> |
| <p>The original string</p> |
| </td></tr><tr><td> |
| <p>pattern</p> |
| </td><td> |
| <p>The substring to be replaced</p> |
| </td></tr><tr><td> |
| <p>replacement</p> |
| </td><td> |
| <p>The new replacement substring</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-REPLACE-Examples"></a>Examples</h6></div></div></div><p> |
| Output certificate issuer names without leading "CN=" |
| </p><pre class="programlisting"> |
| SELECT |
| REPLACE(issuerName, 'CN=', '') AS issuer |
| FROM certificate |
| LIMIT 10 OFFSET 0 |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-RTRIM"></a>RTRIM</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-RTRIM-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The RTRIM() function removes trailing spaces from a string. |
| If argument is NULL, RTRIM returns NULL. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-RTRIM-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| RTRIM(string) |
| RTRIM(string, chars) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-RTRIM-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e5100"></a><p class="title"><strong>Table 6.38. RTRIM Parameters</strong></p><div class="table-contents"><table summary="RTRIM Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>string</p> |
| </td><td> |
| <p>The string to remove leading and trailing spaces from</p> |
| </td></tr><tr><td> |
| <p>chars</p> |
| </td><td> |
| <p>Specific characters to remove</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-RTRIM-Examples"></a>Examples</h6></div></div></div><p> |
| Find connection remote addresses |
| </p><pre class="programlisting"> |
| SELECT |
| RTRIM(remoteAddress) |
| FROM connection |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-SUBSTRING"></a>SUBSTR / SUBSTRING</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-SUBSTRING-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The SUBSTRING() function takes a source parameter, a start index parameter and optional |
| length parameter. Returns substring of a source string from the start index to the end or |
| using the length parameter. If source string is NULL, return NULL. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-SUBSTRING-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| SUBSTRING(source, startIndex, length) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-SUBSTRING-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e5161"></a><p class="title"><strong>Table 6.39. SUBSTRING Parameters</strong></p><div class="table-contents"><table summary="SUBSTRING Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>source</p> |
| </td><td> |
| <p>The string to extract from</p> |
| </td></tr><tr><td> |
| <p>startIndex</p> |
| </td><td> |
| <p> |
| The start position. Can be both a positive or negative number. If it is |
| a positive number, this function extracts from the beginning of the string. |
| If it is a negative number, function extracts from the end of the string |
| </p> |
| </td></tr><tr><td> |
| <p>length</p> |
| </td><td> |
| <p> |
| The number of characters to extract. If omitted, the whole string will |
| be returned (from the start position). If zero or negative, an empty |
| string is returned |
| </p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-SUBSTRING-Examples"></a>Examples</h6></div></div></div><p> |
| Find queue names removing from name part before the `.` character |
| </p><pre class="programlisting"> |
| SELECT |
| SUBSTRING(name, POSITION('.', name) + 1, LEN(name) - POSITION('.', name)) |
| FROM queue |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-TRIM"></a>TRIM</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-TRIM-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The TRIM() function removes both leading and trailing spaces from a string. |
| If argument is NULL, TRIM returns NULL. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-TRIM-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| TRIM(string) |
| TRIM(string, chars) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-TRIM-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e5233"></a><p class="title"><strong>Table 6.40. TRIM Parameters</strong></p><div class="table-contents"><table summary="TRIM Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>string</p> |
| </td><td> |
| <p>The string to remove leading and trailing spaces from</p> |
| </td></tr><tr><td> |
| <p>chars</p> |
| </td><td> |
| <p>Specific characters to remove</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-TRIM-Examples"></a>Examples</h6></div></div></div><p> |
| Find connections remote addresses removing `/` characters from both sides |
| </p><pre class="programlisting"> |
| SELECT |
| TRIM(remoteAddress, '/') |
| FROM connection |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-UPPER"></a>UPPER</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-UPPER-Definition"></a>Definition and Usage</h6></div></div></div><p> |
| The UPPER() function takes a string parameter and converts it to upper case. The implicit |
| conversion to strings follows the existing rules for data type conversions. If argument is NULL, |
| UPPER returns NULL. |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-UPPER-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting"> |
| UPPER(string) |
| </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-UPPER-Parameter-Values"></a>Parameter Values</h6></div></div></div><p> |
| </p><div class="table"><a id="d0e5294"></a><p class="title"><strong>Table 6.41. UPPER Parameters</strong></p><div class="table-contents"><table summary="UPPER Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td> |
| <p>string</p> |
| </td><td> |
| <p>The string to convert</p> |
| </td></tr></tbody></table></div></div><p><br class="table-break" /> |
| </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-UPPER-Examples"></a>Examples</h6></div></div></div><p> |
| Filter connections by principal name (case-insensitive) |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM connection |
| WHERE UPPER(principal) = 'AMQP_USER1' |
| LIMIT 10 OFFSET 0 |
| </pre></div></div></div></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Set-Operations"></a>6.3.12.10. Set Operations</h4></div></div></div><p> |
| UNION, MINUS and INTERSECT set operations are supported. |
| |
| The UNION operator is used to combine the result-set of two or more SELECT statements. Every SELECT statement within |
| UNION must have the same number of columns. The UNION operator selects distinct values by default. |
| To keep duplicates, UNION ALL should be used. |
| |
| For example, following query return certificate aliases along with the user names: |
| </p><pre class="programlisting"> |
| SELECT UPPER(alias) |
| FROM certificate |
| UNION |
| SELECT UPPER(name) |
| FROM user |
| </pre><p> |
| The MINUS operator is used to remove the results of right SELECT statement from the results of left SELECT statement. |
| Every SELECT statement within MINUS must have the same number of columns. The MINUS operator selects distinct values by default. |
| To eliminate duplicates, MINUS ALL should be used. |
| |
| For example, following query finds queue names, not specified as binding destinations: |
| </p><pre class="programlisting"> |
| SELECT name |
| FROM queue |
| MINUS |
| SELECT destination |
| FROM binding |
| </pre><p> |
| The INTERSECT operation is used to retain the results of right SELECT statement present in the results of left SELECT statement. |
| Every SELECT statement within INTERSECT must have the same number of columns. The INTERSECT operator selects distinct values by default. |
| to eliminate duplicates, INTERSECT ALL should be used. |
| |
| For example, following query finds certificate aliases similar with the user names: |
| </p><pre class="programlisting"> |
| SELECT UPPER(alias) |
| FROM certificate |
| INTERSECT |
| SELECT UPPER(name) |
| FROM user |
| </pre></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Subqueries"></a>6.3.12.11. Subqueries</h4></div></div></div><p> |
| When executing subquery parent query domain mat be passed into the subquery using alias. |
| E.g. this query |
| </p><pre class="programlisting"> |
| SELECT |
| id, |
| name, |
| (SELECT name FROM connection WHERE SUBSTRING(name, 1, POSITION(']' IN name)) = '[' + SUBSTRING(c.name, 1, POSITION('|' IN c.name) - 1) + ']') as connection, |
| (SELECT id FROM connection WHERE SUBSTRING(name, 1, POSITION(']' IN name)) = '[' + SUBSTRING(c.name, 1, POSITION('|' IN c.name) - 1) + ']') as connectionId, |
| (SELECT name FROM session WHERE id = c.session.id) as session |
| FROM consumer c |
| </pre><p> |
| returns following result: |
| </p><pre class="programlisting"> |
| { |
| "results": [ |
| { |
| "id": "7a4d7a86-652b-4112-b535-61272b936b57", |
| "name": "1|1|qpid-jms:receiver:ID:6bd18833-3c96-4936-b9ee-9dec5f408b5c:1:1:1:broadcast.amqp_user1.public", |
| "connection": "[1] 127.0.0.1:39134", |
| "connectionId": "afbd0480-43b1-4b39-bc00-260c077095f3", |
| "session": "1" |
| } |
| ], |
| "total": 1 |
| } |
| </pre><p> |
| Query |
| </p><pre class="programlisting"> |
| SELECT |
| name, |
| destination, |
| (SELECT id FROM queue WHERE name = b.destination) AS destinationId, |
| exchange, |
| (SELECT id FROM exchange WHERE name = b.exchange) AS exchangeId |
| FROM binding b |
| WHERE name = 'broadcast.amqp_user1.xxx.#' |
| </pre><p> |
| returns following result: |
| </p><pre class="programlisting"> |
| { |
| "results": [ |
| { |
| "name": "broadcast.amqp_user1.xxx.#", |
| "destination": "broadcast.amqp_user1.xxx", |
| "destinationId": "d5ce9e78-8558-40db-8690-15abf69ab255", |
| "exchange": "broadcast", |
| "exchangeId": "470273aa-7243-4cb7-80ec-13e698c36158" |
| }, |
| { |
| "name": "broadcast.amqp_user1.xxx.#", |
| "destination": "broadcast.amqp_user2.xxx", |
| "destinationId": "88357d15-a590-4ccf-aee8-2d5cda77752e", |
| "exchange": "broadcast", |
| "exchangeId": "470273aa-7243-4cb7-80ec-13e698c36158" |
| }, |
| { |
| "name": "broadcast.amqp_user1.xxx.#", |
| "destination": "broadcast.amqp_user3.xxx", |
| "destinationId": "c8200f89-2587-4b0c-a8f6-120cda975d03", |
| "exchange": "broadcast", |
| "exchangeId": "470273aa-7243-4cb7-80ec-13e698c36158" |
| } |
| ], |
| "total": 3 |
| } |
| </pre><p> |
| Query |
| </p><pre class="programlisting"> |
| SELECT |
| alias, |
| (SELECT COUNT(id) FROM queue WHERE POSITION(UPPER(c.alias) IN name) > 0) AS queueCount |
| FROM certificate c |
| </pre><p> |
| returns following result: |
| </p><pre class="programlisting"> |
| { |
| "results": [ |
| { |
| "alias": "xxx", |
| "queueCount": 5 |
| }, |
| { |
| "alias": "xxy", |
| "queueCount": 5 |
| }, |
| { |
| "alias": "xxz", |
| "queueCount": 7 |
| } |
| ], |
| "total": 3 |
| } |
| </pre></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Performance-Tips"></a>6.3.12.12. Performance Tips</h4></div></div></div><p> |
| Try to select entity fields by names instead of using an asterix. For example, this query |
| </p><pre class="programlisting"> |
| SELECT |
| id, name, state, overflowPolicy, expiryPolicy |
| FROM queue |
| </pre><p> |
| will be executed faster than this one: |
| </p><pre class="programlisting"> |
| SELECT * |
| FROM queue |
| </pre><p>Try to use `LIMIT` and `OFFSET` clauses where applicable to reduce the response JSON size:</p><pre class="programlisting"> |
| SELECT |
| id, name, state, overflowPolicy, expiryPolicy |
| FROM queue |
| LIMIT 10 OFFSET 0 |
| </pre></div></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-API-CORS"></a>6.3.13. Cross Origin Resource Sharing (CORS)</h3></div></div></div><p> The Broker supports Cross Origin Resource Sharing (CORS) |
| to allow web management consoles other than the one embedded in the |
| broker to use the REST API. This feature must be enabled by configuring |
| the CORS Allow Origins and related attributes on the |
| <a class="xref" href="Java-Broker-Management-Managing-Plugin-HTTP.html" title="7.17. HTTP Plugin">Section 7.17, “HTTP Plugin”</a> |
| </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="Java-Broker-Management-Channel-Web-Console.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="Java-Broker-Management-Channel.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="Java-Broker-Management-Metrics.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">6.2. Web Management Console </td><td width="20%" align="center"><a accesskey="h" href="Apache-Qpid-Broker-J-Book.html">Home</a></td><td width="40%" align="right" valign="top"> 6.4. Prometheus Metrics</td></tr></table></div></div> |
