Perform Data Table Operations - campsych/concerto-platform GitHub Wiki

Data tables are used to store information; anything from test items to demographic info or feedback text. Each data table has its corresponding table in the database server, which means you can perform all kinds of queries on your data from within Concverto.

Every Concerto data table must have a column named id with the type ‘integer’ and the values of each row in this column need to be unique for all records. This column is created automatically for all Concerto data tables. You are free to design the rest of your table structure as you wish.

Common data queries:

• SELECT – reading data from table
• INSERT – adding new data records to table
• UPDATE – changing fields of existing data records
• DELETE – removing data records

We’re going to create a new table in Concerto and use it in our test logic. We’re going to perform all four queries above for our table by using both the dataManipulation node and by writing queries directly in code.

Lets start with creating a data table. Switch to Data Tables tab.

Clik Add new.

For name, enter demo and press Save.

Each table consists of Data table structure and Table data. Structure defines what field each data record contains. These are your table’s columns. Table data are the actual records of your dataset. In other words, these are the rows in your table rows.

Check your Data table structure. By default you should have two columns defined:

• id – Cannot be edited or removed. Each Concerto table requires numeric id column with unique values.
• temp – Temporary text column. You probably want to replace this with something else once your table is created.

Let’s change temp column name to something more relevant. Click Edit next to the temp column in the list.

Set its name to name and click Save.

Lets add one more column. Click Add new.

Enter score as its name and set its type to smallint. Click Save when done.

This is how your structure should look now:

Below Data table structure, you’ll find the Table data section. You can come back here later to see the effect of our queries on our data.

Lets create a test and add a dataManipulation node, by right-clicking flow and selecting dataManipulation.

Node wizard should pop up. Select INSERT as the query type and select our recently added table demo as a table.
Leave SET clause empty (unchanged). We’re going to use dynamic inputs alone to set the values of our record.

Add two dynamic input ports to our dataManipulation node. Name them the same as the columns of the table:

• name
• score

Set newly added port default values to: Mark and 1 respectively, by clicking on each dynamic input port name. Then connect your execution flow.

Now run the test. Each time you run it a new record will be inserted with the values specified. You can check your data in Table data section of your data table.

Lets add a new dataManipulation node. This time we want it to update our existing data. Set its Query type to UPDATE. Select demo table and change Set clause to:
score = score + {{scoreIncrement}}

The above query means that for each record, we want to take its score value and add to it the value of the dynamic input scoreIncrement.
Leave the Where clause empty. For UPDATE queries, leaving the Where clause empty means that we don’t want to filter out any rows and that we want to update all records.

When done click Save. Your properties should look like this:

Add the dynamic input we used inside the Set clause to your dataManipulation node. It needs to be named scoreIncrement. Set its default value to 1 and connect the newly added node to your execution flow. Your flow should look like this now:

One last touch. Lets rename our dataManipulation nodes to something more descriptive (see below).

Run your test a couple of times. If you have followed the guide successfully then you should see that new rows are inserted and the score values of the older rows are incremented each time you run the test.

Add a new dataManipulation node. Set the Query type to DELETE, the table to demo and the Where clause to:
score > {{maxScore}}
This query means that we want to delete all rows where the score value is higher than the value provided in the maxScore dynamic input.

This is how your node properties should look now:.

Add a dynamic input port called maxScore to your newly added dataManipulation node and set its default value to 10. Rename the node to DELETE too high scores and connect your execution flow. Your flow should now look like this:

Run your test multiple times and check that the queries are operating on your data as expected.

Add a new dataManipulation node. Set Query type to SELECT, table to demo, and leave the Where clause as it is. We’re going to override this value by using a data connection in our flow.

Rename the newly added node to SELECT inserted score, expose the native whereClause input port, and expose the native result return port. Finally, expose the native insertId return port on our INSERT score node.

Make execution connections and connect data from insertId return port to whereClause input port. Your flow should look like this now:

When the whereClause port is set to a numeric value, it will treat it as an id and construct a proper where clause for it. The insertId return port is only set to INSERT queries and will always have the numeric value of the recently inserted record id value.

Lets add one final node to log the selected record. Add a log node. Add a dynamic input to it called selectedScore, connect the execution flow and connect the data flow. Remember to save your work.

Run your test in Debug mode this time. Open your browser console and you should see the newly inserted score every time you run the test. When using the SELECT query type, the native result return port of the node will be set to the value of the data frame consisting of the rows that have been selected.

Lets now do the same thing but in code.

#INSERT score
concerto.table.query("INSERT INTO demo SET name='{{name}}', score={{score}}", params=list(
  name="Mark",
  score=1
))
insertId = concerto.table.lastInsertId()

#UPDATE scores
concerto.table.query("UPDATE demo SET score = score + {{scoreIncrement}}", params=list(
  scoreIncrement=1
))

#DELETE too high scores
concerto.table.query("DELETE FROM demo WHERE score > {{maxScore}}", params=list(
  maxScore=10
))

#SELECT inserted score
result = concerto.table.query("SELECT * FROM demo WHERE id={{insertId}}", params=list(
  insertId=insertId
))

#log
concerto.log(result, "selectedScore")