A query layer for Pharo collections and relational backends, with in-memory and SQL execution
# Rudesheim Table Query for Pharo
[](https://github.com/devid-rudesheim/Table-Query-Rudesheim-Pharo/releases/latest)
[](https://github.com/devid-rudesheim/Table-Query-Rudesheim-Pharo/actions/workflows/tests.yml)
[](https://pharo.org)
Rudesheim Table Query is a query layer for Pharo collections and relational backends.
It lets a query be written once as a Smalltalk expression tree and prepared against an in-memory backend or an SQL backend.
The in-memory backend has an optimizer: for supported equality conditions it uses an `EqualityIntersection` plan, avoiding a fully multiplied nested iteration over all source collections.
## Installation
Load the default project group with Metacello:
```smalltalk
Metacello new
baseline: 'RudesheimTableQuery';
repository: 'github://devid-rudesheim/Table-Query-Rudesheim-Pharo:main';
load
```
This also loads the required `RudesheimKernel` and `RudesheimUtility` dependencies from GitHub. `DMirror` is only loaded by the optional `dmirror` group (see Groups below).
## Requirements
- Pharo with Metacello.
- Windows is supported: the in-memory backend picks its optimizer table per OS (`Smalltalk os rudesheim tableQuery`, the same dispatch style `Rudesheim-OpenCL` uses). On Windows the `DMirror`-backed plans are dropped automatically, since `DMirror` declares its own `OSSubprocess` dependency, which is POSIX-only.
- Plain in-memory use needs only the default group. The default group does not load DMirror.
- The optional `dmirror` group adds the `DMirror`-backed plans (large-input parallel execution) for POSIX-like systems. It is not part of `default` and is unavailable/unused on Windows.
- SQLite3 use needs the `sqlite3` group. The group loads the `Core` part of Pharo-SQLite3 through the baseline.
## Dependencies
The baseline loads these repositories:
- `RudesheimKernel`: `github://devid-rudesheim/Kernel-Rudesheim-Pharo:main`
- `RudesheimUtility`: `github://devid-rudesheim/Utility-Rudesheim-Pharo:main`
- `DMirror`: `github://ObjectProfile/DMirror/src`
The `sqlite3` group also loads `SQLite3Core` from Pharo-SQLite3:
- `SQLite3`: `github://pharo-rdbms/Pharo-SQLite3/src`, loaded with `#( 'Core' )`
DMirror declares its own OSSubprocess dependency in its baseline. Because the `dmirror` group's plans use DMirror, load that group only on a POSIX-like operating system.
## Load Options
Default runtime load:
```smalltalk
Metacello new
baseline: 'RudesheimTableQuery';
repository: 'github://devid-rudesheim/Table-Query-Rudesheim-Pharo:main';
load
```
SQLite3 adapter:
```smalltalk
Metacello new
baseline: 'RudesheimTableQuery';
repository: 'github://devid-rudesheim/Table-Query-Rudesheim-Pharo:main';
load: #(sqlite3)
```
## Groups
- `core`: table query, in-memory backend, and SQL backend packages.
- `sqlite3`: SQLite3 SQL backend adapter.
- `dmirror`: adds the `DMirror`-backed in-memory plans (large-input parallel execution). Loads `DMirror`, which is POSIX-only; not part of `default` and not usable on Windows.
- `default`: aliases `core`.
## Basic Use
```smalltalk
sourceCollections :=
{
1 to: 200.
50 to: 60.
1 to: 200
}
collect:
[ :eachRange |
eachRange collect: [ :eachInteger | eachInteger -> eachInteger ].
].
preparedQuery :=
sourceCollections asRHTableQuery asInMemory
prepareTableQuery:
[ :statement :rows :parameters |
statement
select:
[
(rows first key = rows second key) & (rows first key = rows last key) & (rows first key = parameters first).
]
collect:
[
{
rows first.
rows last
}.
].
].
preparedQuery value: 50.
```
The selected plan is `Rudesheim TableQuery Backend InMemory Plan EqualityIntersection`.
The result is `#( #( 50 50 ) )`.
For this equality shape, the in-memory backend intersects candidate values instead of evaluating every combination from the three input collections.
When a prepared query does not need runtime parameters, omit the third block argument:
```smalltalk
preparedQuery :=
{
1 to: 1000.
500 to: 550.
1 to: 1000
} asRHTableQuery asInMemory
prepareTableQuery:
[ :statement :rows |
statement
select:
[
(rows first = rows second) & (rows second = rows last).
]
collect:
[
{
rows first.
rows last
}.
].
].
preparedQuery valueWithArguments: #().
```
Use `inquireTableQuery:` when the query has no runtime parameters and should be evaluated immediately:
```smalltalk
matchedRows :=
{
1 to: 1000.
500 to: 550.
1 to: 1000
} asRHTableQuery asInMemory
inquireTableQuery:
[ :statement :rows |
statement
select:
[
(rows first = rows second) & (rows second = rows last).
]
collect:
[
{
rows first.
rows last
}.
].
].
matchedRows size.
matchedRows first asArray.
```
The result size is `51`, and the first row is `#( 500 500 )`.
The same expression-capturing interface can target SQL:
```smalltalk
connection :=
SQLite3Connection memory open
execute: 'CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);';
execute: 'CREATE TABLE orders (customerId INTEGER, total INTEGER);';
execute: 'INSERT INTO customers (id, name) VALUES (?, ?);'
value: 1
value: 'Alice';
execute: 'INSERT INTO customers (id, name) VALUES (?, ?);'
value: 2
value: 'Bob';
execute: 'INSERT INTO orders (customerId, total) VALUES (?, ?);'
value: 1
value: 150;
execute: 'INSERT INTO orders (customerId, total) VALUES (?, ?);'
value: 1
value: 50;
execute: 'INSERT INTO orders (customerId, total) VALUES (?, ?);'
value: 2
value: 300;
yourself.
backend := Rudesheim TableQuery Backend SQL.
table := backend Table.
query :=
{
table name: #customers.
table name: #orders
} asRHTableQuery
asInSQLUsing:
(
backend Driver SQLite3
connection: connection
).
preparedQuery :=
query
prepareTableQuery:
[ :statement :rows :parameters |
statement
select:
[
(rows first id = rows second customerId) & (rows second total > parameters first).
]
collect:
[
{
rows first name.
rows second total.
}
].
].
preparedQuery sqlString.
preparedQuery value: 100.
```
`#sqlString` returns:
```text
SELECT t1.name, t2.total FROM customers t1, orders t2 WHERE ((t1.id = t2.customerId) AND (t2.total > ?))
```
`#value:` returns:
```text
#(#('Alice' 150) #('Bob' 300))
```
## Usage Constraints
- Query capture blocks accept `:statement :rows :parameters`. If the query does not use runtime parameters, omit the third argument and write `:statement :rows`.
- `inquireTableQuery:` is a one-shot convenience API. It calls `prepareTableQuery:` internally and immediately evaluates the prepared query with no runtime arguments.
- `prepareTableQuery:` captures the expression tree before execution. For a single execution it is not inherently faster than `inquireTableQuery:`, but repeated executions can reuse the captured query and avoid rebuilding the expression tree each time.
- A prepared query is tied to the source collections or tables supplied when it is prepared. In-memory optimized plans can derive candidate or index-like data from those sources during execution. If the source tables are replaced, or if changed contents must be reflected in a fresh optimization basis, run `prepareTableQuery:` again.
- `statement select:collect:` records one predicate expression and one projection expression. Calling it more than once in the same capture block overwrites the previously captured expressions.
- `statement collect:` is the no-filter form. It records a constant true predicate and one projection expression.
- The blocks are evaluated once against symbolic row and parameter objects. They should build expressions from `rows` and `parameters`; arbitrary side effects are not part of the query model.
- SQL output is limited to expressions the SQL renderer can translate. Unsupported Smalltalk messages are not automatically executable by a real SQL backend.
- In-memory execution always works through a plan. If no optimizer accepts the captured expression, it falls back to sequential iteration.
- The `EqualityIntersection` optimizer applies to connected equality predicates over all source collections, optionally including equality with a runtime parameter. The equality predicates must be expressed with `=` and connected with `&`.
- Equality keys can be whole rows or supported accessor chains such as `rows first key` or nested value access. Conditions outside this equality shape do not use the intersection plan.
- Result ordering follows the selected plan and source collections; do not rely on SQL-style ordering unless an explicit backend adds ordering support.