Query Examples
Let’s see some examples to better clarify the previous concepts.
All examples start from a redis DB in which there are some records so composed:
- Records are called "kura:WR_data:N", where N is an integer from 1 to N
- Each record is a hash
- Each record contains 6 "key-value" pairs, respectively of type LONG, FLOAT, DOUBLE, BOOLEAN, INTEGER, STRING
- Keys are respectively
longTest,floatTest,doubleTest,booleanTest,intTest,stringTest - Values are respectively
17,20.55,10.55,true,9,HELLO
Simple redis call
When you want to interact with a redis DB, you can use a CLI to navigate the data: through this, you can use commands to interact with the information contained in the DB.
For example, some commands are:
KEYS *: to request all records within the DBHGETALL KEY: to ask for all content within the record called KEYHGET KEY FIELD: to request the content of the key FIELD within the record called KEY
Each value/values returned from the command are interpreted by the CLI, shown in the terminal casting each byte[] in a string format.
Another fundamental command is EVAL, which allows you to evaluate a LUA script. As said before, this script is allowed to call a specific function, the redis.call(): this method takes as parameters a redis-cli command between '' and the latter parameter, always enclose between ''.
If we wanted to replicate the behavior depicted in the second screenshot above:
The zero at the end represents the number of parameters passed to the script from the CLI: for further information, please refer to the official redis documentation.
In this case, the script returns the value in bytes[] obtained by making a call redis HGET kura:WR_data:2 longTest. But there is a problem: the value returned from the HGET KEY FIELD command, does not represent a hash value, but only the byte[] associated with the value contained in the hash. For this reason, a query with this command will fail within the component.
Simple Query
To build a valid query, the redis.call() function must return a hash record. This can be achieved by taking advantage of the call HGETALL KEY, which in this case will be HGETALL kura:WR_data:2:
The strange strings are due to the terminal's escape characters. Now the result of the call is a List<byte[]>, in which the key of the field and its value alternate. You can understand it by the ordered number preceding each field of the list, while in the previous example, the numbers were not present.
If we wanted to replicate this behavior with our component, we would have to build a query by joining the different parts:
- the LUA script:
"return redis.call('HGETALL', 'kura:WR_data:2')" - the placeholders "KEY::DATATYPE":
"longTest::LONG","floatTest::FLOAT", and so on - separate each part by the double pipe combination
||
So the final result should be:
"return redis.call('HGETALL','kura:WR_data:2')"||"longTest::LONG"||"floatTest::FLOAT"||"doubleTest::DOUBLE"||"intTest::INTEGER"||"booleanTest::BOOLEAN"||"stringTest::STRING"
For example, if we created a wire graph consisting of: Timer --> WireRecorQuery --> Logger (Verbose)
In the logger we'd see something like:
Multi record query
It is possible to create LUA scripts that return multiple records in one call. This type of script is allowed in the Wire Record Query, but under certain conditions:
- the records must be made up of the same data structure so that all hashes containing the same fields and expected data types
- the return value of the script must necessarily be a
List<byte[]>, so we need to find a way to group the data not as a record array, but as a key/values array, like the one-record call - placeholders "KEY::DATATYPE" must be declared only once, to define the record scheme
As an example, below is a possible LUA script, which allows you to extract 3 hashes from the DB and build a byte array in which all the records follow each other, alternating keys and values:
local keys = {'kura:WR_data:1', 'kura:WR_data:2', 'kura:WR_data:3'};
local results = {}; local z = {}; local n = 0;
for i = 1,3,1 do
results[i] = redis.call('hgetall', keys[i])
for _,v in ipairs(results[i]) do
n=n+1; z[n]=v
end
end;
return z;
This script is taking 3 records 'kura:WR_data:1', 'kura:WR_data:2', 'kura:WR_data:3' and with a loop, it appends in a unique List<byte[]> z all the keys and values of each record, returning it at the end. If we put this script in the same query as before, we get:
"local keys = {'kura:WR_data:1', 'kura:WR_data:2', 'kura:WR_data:3'}; local results = {}; local z = {}; local n = 0; for i = 1,3,1 do results[i] = redis.call('hgetall', keys[i]) for _,v in ipairs(results[i]) do n=n+1; z[n]=v end end; return z;"||"longTest::LONG"||"floatTest::FLOAT"||"doubleTest::DOUBLE"||"intTest::INTEGER"||"booleanTest::BOOLEAN"||"stringTest::STRING"
The number of records in the list will be calculated by the number of parameters entered after the script. In this specific case, the number of bytes[] in the list will be:
record number*(2*number of fields) = 3(26) = 36 bytes[]
The product 2*number of fields is because for each field there will be one byte[] for the key and one for the value. The record number will then be:
(number of bytes/2)/number of placeholders = (36/2)/6 = 3
The ratio (number of bytes/2) is because the list separates keys and values, but we gather them to build the WireRecord.
Using the same wire graph as before, but with the new LUA script, we would get in the logger:
It is possible to implement whatever LUA script the user wants, but it has to follow the indications provided in this document to obtain a valid result.
Get the last data from the database
As shown above, the data are stored in the database using a key in the format kura:<database-name>:index where the index is an integer number. To retrieve the last inserted data, the query needs the last index to build the key name. This is stored in the wire_record_id_counter key. So, the following script gets the last index, create the key and perform the query in the case of a stringTest value: