Read sets from database table

Question

I have two sets A and B, set B depends on set A;

set A;
set B{A};

In my database I have two tables - A_Table with primary key a_id and table B_Table with composite key b_id and a_id (foreign key). I tried to read data from B_Table table to set B using this script:

table B_Table IN "ODBC" (ConnectionStr) "B_Table":
   [b ~ b_id, a ~ a_id], B[a] ~ b;

But this script doesn't work. It writes

syntax error
context:  [b ~ b_id, a ~ a_id],  >>> B[ <<< a] ~ b;

How can I read data from a table that has 2 key to a one-dimentional set?

Answer 1

I don't think you can read the data directly into an indexed set using a single table declaration, but you can use a helper two-dimensional set:

set AB dimen 2;
table B_Table IN "ODBC" (ConnectionStr) "B_Table":
   AB <- [a_id, b_id];
read table B_Table;
let{a in A} B[a] := setof{(a, b) in AB} b;

Another option is to make the table declaration indexed over A and use an SQL statement to select data for a in A:

table B_Table{a in A} IN "ODBC" (ConnectionStr)
  ("SQL=SELECT b_id FROM B_Table WHERE a_id = " & a & ";"):
   B[a] <- [b_id];

or something like that.