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