RBB Programming in SQL

RBB is built on the H2 SQL database, so SQL is the most fundamental RBB interface. All the queries and commands in this section are in sql, so they may be executed from any language or environment with sql support. The other interfaces supplied by the RBB package (Java and command-line) build on the SQL interface. RBB extensions to the H2 database are stored procedures in the package rbb.impl.h2.statics.

Note the syntax for calling the H2 stored procedures in a query

call rbb_concurrent_events(('set=a','set=b', 'set=c'), 2.5, 100, null);
not
select rbb_concurrent_events(('set=a','set=b', 'set=c'), 2.5, 100, null);

Outline

  1. Creating an RBB
  2. Creating a Timeseries
  3. Adding an Observation to a Timeseries
  4. Ending a Timeseries
  5. Finding Timeseries
  6. Retrieving data
  7. Deleting timeseries
  8. Event-driven processing

Creating an RBB

Note: in most situations it is more convenient to create an RBB is through the command-line utility create, rather than through SQL directly.

The H2 database is created implicitly by connecting to a database that did not already exist. The H2 web console is a convenient way to do this. First, run the H2 server:

> cd /RelationalBlackboard
> java -cp $RBBJAR:$H2JAR org.h2.tools.Server
The RBB jarfile gov-sandia-cognition-rbb-core.jar must be in the classpath; otherwise the stored procedures implementing RBB will not be available. Next create a database by connecting to the server. Example settings for the web console are:
JDBC URL: jdbc:h2:tcp://localhost/mem:ExampleDB
User Name: sa
Password: x
The User Name sa and Password x are conventions in RBB. The JDBC URL specifies many attributes of the database; whether it is memory or disk-based, where it is stored on disk, etc. JDBC URL details are found in the online H2 documentation. To create an RBB in the new database, execute the SQL command: runscript from 'Projects/Core/Build/classes/gov/sandia/cognition/rbb/impl/h2/resources/create_rbb.sql'; This assumes the server was run from the RelationalBlackboard directory (the root of the source code directory hierarchy), as shown above; otherwise the sql script will not be found. The script creates a number of tables, e.g. RBB_DESCRIPTOR and RBB_STRINGS.

Creating a Timeseries

rbb_start_timeseries(dimension, start_time, taglist)
Here, Dimension is the number of data values in each observation, e.g. 2 for a timeseries of (x,y) coordinates:
set @id = rbb_start_timeseries(2, 1.0, 'tag1=value1,tag2=value2');
This example sets the SQL variable id to the returned value.

Adding an Observation to a Timeseries

rbb_add_to_timeseries(SequenceID, time, (dim1, dim2,…))
Example:
call rbb_add_to_timeseries(@ID, 1.2, (10.1, 10.2));
Rows may also be inserted into a timeseries without using the rbb function. A final null argument is required for the insert.
insert into RBB_SEQUENCES.S1 values(1.3, 10.1, 10.2, null);
Note: The extra column value is required for insert because the sequence table has an extra column whose value is computed automatically to be the negative of time. Indexing this column accelerates some common queries. Any value provided for this column is ignored, however not specifying a value causes an error. H2 does not allow specifying a default value for a computed column.

Ending a Timeseries

rbb_end_timeseries(SequenceID, end_time);
Example:
call rbb_end_timeseries(@ID, 123.4);
By default, timeseries are open; they don’t have an end time. (More precisely, the default end time is MAX_DOUBLE).

Finding Timeseries

The RBB_SEQUENCE_INFO table can be queried directly to find timeseries IDs on the basis of start and end times:
select ID from rbb_timeseries_info where start_time > 0 and end_time < 20;
The RBB stored procedures support finding timeseries using tags in combination with time:
-- this is similar to select * from rbb_timeseries_info, but converts the taglists to readable strings:
call rbb_find_timeseries(null, null, null, null);
-- find only timeseries with at least the specified tags:
call rbb_find_timeseries('name1=value1,name2=value2', null, null, null);
-- find all timeseries that exist some time during the time interval [1,10]
call rbb_find_timeseries('name1=value1, 1, 10, null);
rbb_concurrent_events(filter_tags[], start_time, end_time, time_coordinate)
-- as used by the utility program that generated data for Figure 2
call rbb_concurrent_timeseries(('color=red','color=green','color=blue'), null, null, null);

Retrieving Data

-- resample a timeseries at regular intervals
--  rbb_resample_timeseries_linear(SequenceID, start, timestep, end, time_coordinate)
call rbb_resample_timeseries_linear(40, 0, 1, 15, null);
-- get all observations within a time window
select * from rbb_timeseries.s40 where time >= 1 and time <= 15;
-- get the final observation at or before a specified time – not indexed; slow for big timeseries!
select * from rbb_timeseries.s72 where time  <= 500000 order by time desc limit 1;
-- same thing but indexed (fast) - this is what the minustime column is for
select * from rbb_timeseries.s72 where minustime  >= -500000 order by minustime limit 1;
-- same, but use rbb stored procedure
-- (can specify a time coordinate with optional last parameter)
call rbb_timeseries_value_prev(72, 500000, null);
-- same, except interpolate between neighboring values if no observation at exact time.
call rbb_timeseries_value_linear(72, 500000, null);

Deleting timeseries

It is not recommended to delete timeseries using SQL keywords directly (DROP TABLE... and DELETE FROM...) because deleting a timeseries affects several tables. Instead, use the stored procedures:
-- Delete a single timeseries by ID
call rbb_delete_timeseries_by_id(61);

-- Delete ALL timeseries matching the tagset (use with caution!)
call rbb_delete_timeseries('color=red');

Event-driven processing

There is no mechanism for event-driven (or "data push") applications in SQL, so programs receive notifications through a TCP socket. However a SQL call is used to start the TCP server in the RBB and retrieve its port number:
call RBB_START_EVENT_TCP_SERVER();
This returns the port number on which RBB is listening for TCP connections. This is commonly 1974, but will vary if some other program was already using that port, or multiple RBBs are listening for connections simultaneously.

To determine the ip address of the server:

call RBB_GET_SERVER_ADDRESS();
This call is not necessary if the H2 server is running on the same host as the client program (simply connect using 127.0.0.1 or localhost). But if the client may run on a different host than the server, this allows the client to find the host without using any configuration information other than the SQL connection URL.

After establishing a tcp connection to this address and port, the client must send one line of text containing zero or more tagsets separated by semicolons and ending with a newline \n (not a \r\n pair). For example:

test=x;test=y
The RBB will now send one line of text reporting any change to any event (or timeseries) matching any of the specified tagsets. The fields in the updates are separated by tabs. The updates are:
eventCreated <id> <start> <end> <tagset>
eventDataAdded<id> <start> <end> <tagset> <schemaName.tableName> <newRowCol1> <newRowCol2>...
eventModified<id> <start> <end> <tagset>
eventDeleted<id> <start> <end> <tagset>
In each case, <start> and <end> are the start/end times of the event (or timeseries). As new samples are added to a timeseries, eventDataAdded will be emitted with the following extra columns:
RBB_TIMESERIES.S<id> <sampleTime> <sampleDim1> <sampleDim2>... <-sampleTime>
(The final column may be ignored - it is the time of the sample negated, which is an H2 implementation detail.)