Database functions

Tcc can connect to an internal or external database. By default it uses H2 which is bundled with Tcc. TCL scripts can query and update the database. This is done using DbQuery objects within TCL. a simple example might be:

DbQueries: db
SmartCabs: cab1
Actions:
WHEN ... DO db.query ("SELECT speed FROM trains WHERE trNo=1"), cab1=db.get (1,1)

If our database has a table called "trains" which contains:

Train number trNo	Class of train class	Full speed (cab setting 0..100) speed	Length of train (in cm) length
1	Goods	60	74
2	Passenger	45	35
3	Express	80	24

The select statement looks in table "trains" and finds all the rows having a '1' in column "trNo". In this instance this selects only the first row. The data returned includes just the "speed" column and so we get an array containing just a single element: 60 which is the speed for train 1. Note that the call to query only fetches data from the database into an array inside the DbQuery object. Next we actually get data one element at a time using get (column, row) where column and row and positions in the returned array. As we only expect a single element we simply ask for the first element. The returned value (60) is given directly to the SmartCab driving the train in the above example.

SQL is an interpreted database query language and as such the query needs to be analysed each time and as such can be quite slow if we use it too much.

Rather than actually query the database frequently in the script we can make the DbQuery objects do the work more efficiently for us. Let us suppose we have a SmartCab attached to each track section and we want to set the power to that track section when a train approaches.

DbQueries: speeds
SmartCabs: cab1, cab2        { the SmartCab powering each section }
Variables: train1, train2    { the train on or approaching each section }
           initialised,row
Actions:
WHEN initialised=0 DO speeds.query ("SELECT trNo,speed FROM trains")
WHEN train1<>0 DO row=speeds.rowWith (1,trains1), cab1=speeds.get (2,row)

We only read the database once, at startup. We might need to read again if a new train is added later using a different query (see below). We are fetching two columns: trNo and speed from table trains. This will fetch the speed setting for ALL trains is the database, and will make an array stored inside the DbQuery object called speeds:

column number row number	1	2
1	1	60
2	2	45
3	3	80

In this instance the row number and the train number are equal. You cannot assume this will be the case as the database can store rows in any order it chooses, and as data gets removed, added and changed your database rows will get re-ordered. The columns will be in the order you requested then in the select statement, so "SELECT trNo,speed ..." will always put train number in column 1 and speed in column 2 regardless of the ordering of columns in the database.

To find the speed of a given train we need to find which row that train is in, and then fetch its speed. This is what speeds.rowWith (1,train1) is doing. It says find we the row in which column 1 contains the value that is in variable train1. So if train1 happens to be 2, then the row will (in this example) be 2. Then speeds.get (2, row) extracts the value from the array fetched by the query: column 2 (speed), and the discovered row, for for train 2 will return 45 (the speed setting stored for train 2).

You can have several DbQuery objects, perhaps 1 for each type of query you need. Each DbQuery can hold as much data as is needed, perhaps even the whole database.

You might well need your query to vary slightly from one call to another, in which case you could use a string variable to assemble your query, or perhaps simply some variable substitution in a regular string.

Each DbQuery supports the following methods:

Method	Description
db.query ("SQL statement") db.query (name, params, ...)	The string parameter is passed to the database for processing. It could CREATE a new table, UPDATE a table, or SELECT data from a table. If a SELECT then the data is stored. A pre-compiled query can be executed with a list of parameters – these do not have to be in a string.
db.rowWith (column, value, startRow, default)	Searches the specified column of the previously stored results (from a SELECT query) for an item of the specified value and returns the first row where that value is found. If no data stored or value not found returns -1. startRow (default value 1) can be used to find a subsequent row. default (default value undefined) is the value to return if no match is found.
db.get (column, row) db.get (column, withCol, withValue, withStart)	Returns the value previously stored (from a SELECT query) in the specified row and column. Same as db.get (column, db.rowWith (withCol, withValue, withStart)). withStart is optional.
db.heading (column)	Returns a string value being the name of the column previously SELECTed.
db.prepareQuery (name, query)	Pre-compile a query and save the result in table prepQuery

In addition to the above method there are also these instance variables:

Variable	Description
db.rowCount	The number of rows in the stored data from a SELECT query.
db.colCount	The number of columns stored by the previous SELECT query.

Each query variable remembers the most recent SELECT query and if another query such as UPDATE or INSERT is executed then the SELECT query is immediately re-executed so the cached data is kept up to date. This means that your script doesn't have to keep SELECTing data. The only drawback here is if you have more than one query object that access the same table - only the query object that performs the update is re-updated.

Some ideas and examples are here.

An explanation of how the database is used in auto-generated TCL is here.

Last updated 4/12/16