Sample Script

This script, drawn from the “crime-solver” sample application, illustrates some Trio commands and queries.

---------------------------------------------------------------------
-- Creating tables and indexes, inserting data
---------------------------------------------------------------------

create table CRIME(type varchar(32), sector int);
create index CS_INDEX on CRIME(sector);

insert into CRIME values ('Kidnapping', 1); 
insert into CRIME values ('Car theft', 2); 
insert into CRIME values ('Robbery', 2); 

create table WITNESS(name varchar(32), sector int);

insert into WITNESS values ('Mary', 2);
insert into WITNESS values ('Susan', 1);
insert into WITNESS values ('Jane', 1);

create table PERSON(name varchar(32), hair varchar(32), clothes varchar(32));

insert into PERSON values ('Jim', 'black', 'red');
insert into PERSON values ('Hank', 'brown', 'green');
insert into PERSON values ('Bob', 'gray', 'blue');

create trio table SAWPERSON(witness varchar(32), hair varchar(32), clothes varchar(32), uncertain(hair, clothes)); 
create index SHC_INDEX on SAWPERSON(hair,clothes);

insert into SAWPERSON values ('Mary', 'black', ['red' | 'green']);
insert into SAWPERSON values ('Jane', 'gray', 'blue');
insert into SAWPERSON values ('Jane', 'brown', 'green') ?;
insert into SAWPERSON values [ ('Susan', 'gray', 'red') 
                             | ('Susan', 'brown', 'red')
                             | ('Susan', 'brown', 'green') ];

create trio table DRIVES(person varchar(32), car varchar(32), uncertain(person,car)) with confidences;
create index DC_INDEX on DRIVES(car);

insert into DRIVES values [('Jim', 'Honda'):1];
insert into DRIVES values (['Hank':0.6 | 'Jim':0.3], 'Toyota':1);
insert into DRIVES values ('Bob':1, ['Toyota':0.5 | 'Mazda':0.3]);

create trio table SAWCAR(witness varchar(32), car varchar(32), uncertain(car)) with confidences;

insert into SAWCAR values [('Susan', 'Honda') :1];

insert into SAWCAR values ('Mary':1, 'Toyota':0.7);

insert into SAWCAR values [ ('Jane', 'Toyota' ) :0.5 
                          | ('Jane', 'Honda') :0.4];

---------------------------------------------------------------------
-- Running queries
---------------------------------------------------------------------

create table SUSPECT_S as
  select PERSON.name as suspect, CRIME.type as crime
  from  SAWPERSON, WITNESS, CRIME, PERSON
  where SAWPERSON.witness = WITNESS.name
    and CRIME.sector = WITNESS.sector 
    and PERSON.hair = SAWPERSON.hair
    and PERSON.clothes = SAWPERSON.clothes;

select DRIVES.person as suspect, CRIME.type as crime
from  SAWCAR, WITNESS, CRIME, DRIVES
where SAWCAR.witness = WITNESS.name
  and WITNESS.sector = CRIME.sector
  and DRIVES.car = SAWCAR.car;

---------------------------------------------------------------------
-- Dropping indexes and tables
---------------------------------------------------------------------

drop index CS_INDEX;
drop table SUSPECT_S;
drop table CRIME;
drop table WITNESS;
drop table DRIVES;
drop table PERSON;
drop table SAWCAR;
drop table SAWPERSON;