Hi,
I was thinking how to abuse the p6 grammar to simplify the interface to
SQL DBs.
- First i wanted to have a SELECT which works like a map.
- Then i wanted to use a pointy block to bind fields to variables.
- And i wanted also to make it lazy.
I come up to the following code:
my $db = FakeDBI.new;
my ($state, $dept) = active storage;
my @out := $db.SELECT:
- $username, $age, $salary {
say fetched '$username';
$username = $salary, $age;
}, 'from users where state = ? and dept = ?',
($state, $dept);
say 2nd: , @out[1];
say 1st: , @out[0];
say 4th: , @out[3];
the fake module which make the above code to work (but do not really
connect to a db):
class FakeDBI {
method SELECT(block, $stm, *...@binds) {
my $sig = block.signature;
my @fields = map {
$_.name ~~ /\$(\w+)/ or die invalid
signature params: ~$_.perl;
$/[0];
}, $sig.params;
my @stm = (SELECT);
if @fields ~~ ('_') { push @stm, '*'; }
else { push @stm, join ', ', @fields; }
push @stm, $stm;
say 'DBG prepare ', join ' ', @stm, '';
gather {
say 'DBG fake execute, fetching rows...';
for john paul sara - $row {
my @data = map { $row ~ _$_ },
@fields;
take block.(|@data);
}
say 'DBG no more rows...';
}
}
}
and the output:
DBG prepare SELECT username, age, salary from users where state =
? and dept = ?
DBG fake execute, fetching rows...
fetched 'john_username'
fetched 'paul_username'
2nd: paul_username = paul_salary, paul_age
1st: john_username = john_salary, john_age
fetched 'sara_username'
DBG no more rows...
4th: Any()
I found the usage very nice, and the lazyness may be removed by
assigning instead of binding,
so i thought i should share this little useless example, HTH!
TODO: i would like to find a way to use the Signature for table names
(select a.foo, b.bar),
but i don't want to make it too complex, any suggestion?
Oha
