Oha wrote:
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.
Ah, you have touched on a matter that I care greatly about and have been working
on for awhile, albeit more comprehensively with my Muldis database projects.
First off, see Anton Berezin's DBIx::Perlish Perl 5 module on CPAN, for some
functional prior art of what it looks like you're trying to do.
Next, design-wise, you should be able to have much tighter and more natural
syntactic integration than your example shows, such that you make the database
access code look just like normal Perl code, wherein the database is just a Perl
variable, as if you had something like this (I apologize if this isn't correct
Perl 6, and I'm not sure how to specify a hash slice that returns pairs rather
than just values):
my %db is Database = FooDBMS.new(...);
my ($state, $dept) = <active storage>;
my @fetched_users := (%db<users> where {
$_{'state'} eq $state and $_{'dept'} eq $dept
})<username age salary>;
for @fetched_users -> $user {
my ($username, $age, $salary) = $user<username age salary>;
say "fetched '$username'";
}
... or since your filter is just anded equality tests, you could alternately say
for the main query (the "matching" takes a Hash second argument, matches by
common key/field names):
my @fetched_users := (%db<users>
matching {state => $state, dept => $dept}
)<username age salary>;
A relational database is structurally equivalent to a Hash of Set of Hash (or if
you have multiple schemas, a Hash of Hash of Set of Hash) and you should be able
to syntactically treat it like one in Perl. The various things you can do in a
SQL SELECT can easily have Perl-defined routine/operators doing the same. A
WHERE is just a "grep", picking a subset of fields is just a hash slice keeping
pairs or "map", ORDER BY is just "sort". Joins and groups are a bit more
complicated.
See also my functional Set::Relation Perl 5 module on CPAN, which demonstrates
this.
As for your "(select a.foo, b.bar)" question, don't do that; instead, rename
(with "AS" in SQL") any same-named fields to have distinct names, and then the
field names in your rowset can be a properly flat namespace.
On a side note, you shouldn't name things all uppercase since those are reserved
for use by Perl itself; just say "select".
-- Darren Duncan
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
