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);
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:
> cdThe 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:
/RelationalBlackboard > java -cp $RBBJAR:$H2JAR org.h2.tools.Server
JDBC URL: jdbc:h2:tcp://localhost/mem:ExampleDB User Name: sa Password: xThe 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.
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.
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.
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).
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);
-- 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);
-- 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');
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=yThe 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.)