Assume we have some system with entities that might relate to other entities, and we map that to SQL. We’d like to provide users with a simplified dot-expression query language.
The idea below seems obvious, but I haven’t seen it before – so probably I’m missing something, and ask for your thoughts.
Basically, we write paths through our object graph, and optionally can label any entity we come across. These labels can be used in constraints, and in more specific queries.
Account<acc>
.owner.address<addr>
.transactions<trx>
where addr.country = 'NL'
where trx.amount > 15
richStreets = addr.street
where acc.amount > 1000
shoppingAddress = Address['FR', 'Champs-Élysées'] // id: 23
mediumTransactionAtShoppingAccounts = acc
where trx.amount < 50
where addr = shoppingAddress
(Please disregard parsing, the syntax is not set in stone)
I hope for the following advantages:
- Easier to understand for users
- Users don’t have to think about our internal mapping to SQL
- Complex queries can easily be reused, as we can reach “in the middle” and just get the entity we care for
- Optimization potential, e.g:
- we might skip some joins if the result doesn’t need access to
- add constraints at the most efficient place in the query
- create temporary views for the base query
My main concern is whether the query language can always be translated to SQL unambiguously.
Above’s example would translate to SQL like:
-- richStreets =
select addr.street
from
Account acc,
Person p,
Address addr,
Transaction trx
where
acc.amount > 1000
and acc.owner = p.id
and p.address = addr.id
and trx.account = acc.id
and addr.country = 'NL'
and trx.amount > 15
-- mediumTransactionAtShoppingAccounts =
select
acc.id,
acc.balance,
acc.owner
from
Account acc,
Person p,
Address addr,
Transaction trx
where
acc.owner = 23
and trx.amount < 50
and acc.owner = p.id
and p.address = addr.id
and trx.account = acc.id
and addr.country = 'NL'
and trx.amount > 15
Example entities:
entity Account {
balance: decimal
owner: 1 Person
transactions: * Transaction
}
entity Person {
name: string
address: 1 Address
}
entity Address {
country: string
street: string
}
entity Transaction {
amount: decimal
}
Example tables (skipping constraints):
create table Account (
id int,
balance decimal,
owner int references Person.id
);
create table Person (
id int,
name varchar,
address int references Address.id
);
create table Address (
id int,
country: char(2),
street: varchar
);
create table Transaction (
id int,
amount decimal,
account int references Account.id
);