An example of using a database with Tcc

Data item

What it could be used for

List of locos

Deducing throttle settings

Full speed (MPH, KPH, or throttle setting)

Indicate to driver, or set automatically speed on clear straight track.

Min speed

Indicate to driver the throttle setting below which train tends to stall

List of rolling stock

Deduce other data about the train

Length (of the whole train)

Know whether a train will fit in a siding or passing loop.

Class (goods, passenger etc)

Helps restrict train routing to 'sensible' in absence of formal route being defined.

Route

Indicate to driver the route this train should follow. Perhaps set the route automatically.

Current section (where the train is presently)

Restore all settings at power-up. If current is detected in a section that the database says has a train then assume that its still the same train.

Data item

What it could be used for

Name

Human-friendly means to identify which item of stock the data refers to

Stock number

Database-friendly item identifier

Image

Filename of picture of the item. Can be displayed on a CTC to help an operator identify the item, or locate the item, or perhaps a whole train of images could be shown.

Length

To allow the length of a train to be calculated if its list of stock is known.

Purchase info

Date, price, shop name etc to keep track of where the best stock comes from.

Maintenance info

When serviced, how well it rolls etc, to keep track of condition

Running history

How many miles (or hours) run, to gauge quality of manufacture, or perhaps time to next wheel cleaning.

RFID tag code (if fitted)

To identify train makeup, or to check no stock has become uncoupled.

Train info

Which train this item is presently in. Perhaps where in that train.

Data item

What it could be used for

Min speed setting

The throttle setting below which the loco tends to stall. Can be copied into train data when the loco is added to a train.

Reference speed setting

Throttle setting at which train runs at 100 MPH (or KPH etc). Can be used to figure a throttle setting for any required speed. Most useful with feedback throttles as warm-up can affect speed considerable.

Dead-reckoning calibration data

Allows position of the loco (and hence whole train) to be deduced with reasonable accuracy.

Default class

Can be used to guess the class of a train in the absence of other information.

Create trains table

CREATE TABLE trains (trNo int, loc char, cal char, head int, tail int, minSpeed int, maxSpeed int, class int)

Create stock table

CREATE TABLE stock (code char, stockid int(4), stocktype int(4), name char(24), image char(34) ,tagtype int(4) ,trno int(4),carno int(4))

Add a train

INSERT INTO trains (trno) values (@trno) { using variable substitution in a string }

Record which train some stock is in.

UPDATE stock SET trNo=@trno WHERE stockNo=@x

Change the class of a train

UPDATE trains SET class=@class WHERE trNo=@trno

The lookup that a train object does on the trains table

SELECT trno,loc,cal,head,tail,minspeed,maxspeed FROM trains

The update done automatically by a train object

UPDATE trains SET loc=&@loc ,cal=@cal ,head=@head ,tail=@tail ,minspeed=@mst ,maxspeed=@fst WHERE trNo=@myTrain

Or, if a new train is discovered

INSERT INTO trains (trno,loc,cal,head,tail,minspeed,maxspeed) VALUES (@myTrain, &@loc, @cal, @head, @tail, @mst, @fst)

Or, if a train is removed

DELETE FROM trains WHERE trNo=@myTrain

The query done to search for RFid tags

SELECT code,stockType,stockId FROM stock

To populate the unused loco menu on Train settings CTC

SELECT name,image,stockNo FROM stock WHERE trno=0,stockType=0

Name and image are used by the popup itself and stockNo is retrieved once the selection has been made.

Trains: tp [8]

Create a train object for each simultaneously running train supported on this layout.

CtcWindows: spare, ctcSettings

Create a CtcWindow object for CTC2 named ctcCettings. CTC2 is used to assign train settings in auto-generated scripts.

DbQueries: dbStock, dpPopLoco

dbStock is used to hold information about the stock on our layout. dbPopLoco is used as temporary workspace to populate a loco selecting popup menu.

WHEN $Left_Mouse=(1,2-9,2)

When the left mouse button is clicked in the first column of CTC2 (the Train settings window). The row range 2-9 will vary according to the number of trains supported.

DO dbPopLoco.query ("SELECT name,image,stockId FROM stock WHERE trno=0 AND stockType=0")

Query the 'Stock' table in the database. Extract the columns name, image and stockId. Only return stock items which are locos (stockType=0) that are not presently in a train (trno=0)

ctcSettings.popup (1,System.mouseY,"Loco&dbPopLoco")

Popup a menu in the ctcSettings window (CTC2, headed 'Train settings'). The menu will appear in column 1, row System.mouseY (the same cell that the left mouse click occurred in). It will contain a single item called 'Loco' which contains a submenu populated from the database query. (more items are to be added to the menu in future developments). Two columns of the query are used: The first column contains the names to display in each item in the submenu and the second column (if supplied) contains the filename of an image to use as an icon in the menu. Thus we have a popup of loco names with images attached.

WHEN ctcSettings.popIndex<>0

When a selection has been made from the popup, the popIndex element is set to the index selected (indexes start from 1 and popIndex is set to zero when the popup is opened).

DO d=System.mouseY-1

Set variable d to the train number. System.mouseY is the row number of the cell that was clicked in to open the popup and row 2 is the first train (row 1 contains headings only) so train number is one less than row number.

dbStock.query ("UPDATE stock SET trNo=0 WHERE trNo=@d AND stockType=0")

Update the stock database to remove previous locos from our train. All locos (stocktype=0) assigned to our train (trNo=@d) will get trNo set to zero, meaning that such locos are not presently in any train. This simple assignment does not presently support multi-headed trains. Note the use of dbStock and not dbPopLoco. This is because dbStock is used to hold interesting data about stock and this update will cause dbStock to be updated as well as the database. dbPopLoco is only used as workspace for the popup so there is no need to keep it updated.

c=dbPopLoco.get (2,ctcSettings.popIndex), c.trim ()

Fetch column 2, row ctcSettings.popIndex from the dbPopLoco query and store the result in variable c. Column 2 is called 'image' (see the SELECT used when opening the popup, above) and contains the name of a file containing an image of the selected loco. As strings are stored as fixed length in some databases the returned string is trimmed which means that leading and training spaces are removed. Variable c (a normal numeric variable) contains a reference to a temporary string variable containing the trimmed filename.

$Draw Image(1,System.mouseY,2)="@{c}" SIZE 2*1

Draw the image of the selected loco (filename in variable c) in the same square that was used to call the popup, but use two columns as loco images tend to be wider than they are high. We now have the currently selected loco's image displayed in the train settings window against the colour and class of that train.

e=dbPopLoco.get (3,ctcSettings.popIndex)

Fetch column 3 from our selection in the popup menu query. Column 3 contains stockId. So variable e is set to the stockId (which is also the locoId in the locos table) of our chosen loco.

dbStock.query ("UPDATE stock SET trNo=@d WHERE stockId=@e")

Once again update the stock table (using the dbStock query) so that our selected loco (stockId=@e) is marked as being in our train (trNo=@d)

dbPopLoco.query ("SELECT cal,slow,speed100,pickuplen,defclass FROM locos WHERE locoid=@e")

Using our workspace query fetch from the locos table some data about our chosen loco (locoid=@e). The result of this query will have a single row as we only support single-headed trains presently.

c=dbPopLoco.get (5,1), IF tp[d].class<>c, THEN tp[d].class=c, tp[d].mph=csp[c], ENDIF

Extract column 5 (defClass). If our train (variable d) has no class assiged or a different class then update its class to that normally associated with this loco and update its nominal running speed to suit.

tp[d].cal=dbPopLoco.get (1,1), tp[d].mst=dbPopLoco.get (2,1)

Set our trains dead-reckoning calibration coefficient and minimum running speed setting from the columns 'cal' and 'slow'.

c=dbPopLoco.get (3,1)*csp[d], tp[d].fst=c/100

Fetch column 3 'speed100' which is the speed setting for the loco to run at 100MPH or whatever reference speed you choose. This setting is scaled to the full speed appropriate to the class of train.