test.sql

/*
create tables
*/

create table sources (
  id integer primary key autoincrement,
  name string not null
);

create table bags (
  id integer primary key autoincrement,
  source_id integer not null references sources(id),
  start int,
  end int,
  location string
);

create virtual table range_overlaps_index using rtree(bag_id, start, end);

/*
load the data
*/

-- 1000 robots
insert into sources (name) select random() from generate_series(1, 1000);

-- 100k bags/robot
insert into bags(source_id, start, end, location)
select sources.id, value, value+20, random()||random()||random()||random()
from sources
cross join generate_series(1, 100000);

-- index for range overlaps search with rtree
insert into range_overlaps_index(bag_id, start, end)
select id, start, end from bags;

-- give me the bags for source '1047631123709421117' covering messages between
-- 23 and 35
select * from bags
inner join sources on bags.source_id = sources.id
inner join range_overlaps_index on bags.id = range_overlaps_index.bag_id
where range_overlaps_index.end >= 23
and range_overlaps_index.start <= 35
and sources.name = '1047631123709421117';

-- now go run time/topic queries on them locations...