#Slang::SQL
Quick little SQL SLang for perl6 to embed SQL into the language. Design goals were:
- accept parameters
- not have to quote your sql
- treat SQL statements that return something as loops
- have optional parameters/callback
- nested SQLs
- loop control
last,next..
What it doesn't do [yet]
- interpolate variables in the SQL
- verify your SQL syntax is correct
- handle your DB connection (currently depends on DBIish)
- auto parameters based on SQL (PerlJam's suggestion, it's a cool idea)
#Use
use Slang::SQL;
use DBIish;
my $*DB = DBIish.connect('SQLite', :database<sqlite.sqlite3>);
sql drop table if exists stuff; #runs 'drop table if exists stuff';
sql create table if not exists stuff (
id integer,
sid varchar(32)
);
for ^5 {
sql insert into stuff (id, sid)
values (?, ?); with ($_, ('A'..'Z').pick(16).join);
}
sql select * from stuff order by id asc; do -> $row {
FIRST "{$*STATEMENT}id\tsid".say;
"{$row<id>}\t{$row<sid>}".say;
};Output:
select * from stuff order by id asc
id sid
0 WSNPLYBHJRMVXKFQ
1 UYNZMXFSABRCOLKP
2 MIQVEDTNXBWGHZFL
3 KFNJWXLSRQEUGBZA
4 VDOMIUYCWQZHGRPF
##Equivalent Code Slang vs. Only DBIish
###Example Above
####Slang::SQL
use Slang::SQL;
use DBIish;
my $*DB = DBIish.connect('SQLite', :database<sqlite.sqlite3>);
sql drop table if exists stuff; #runs 'drop table if exists stuff';
sql create table if not exists stuff (
id integer,
sid varchar(32)
);
for ^5 {
sql insert into stuff (id, sid)
values (?, ?); with ($_, ('A'..'Z').pick(16).join);
}
sql select * from stuff order by id asc; do -> $row {
FIRST "{$*STATEMENT}id\tsid".say;
"{$row<id>}\t{$row<sid>}".say;
};####DBIish
use DBIish;
my $db = DBIish.connect('SQLite', :database<sqlite.sqlite3>);
$db.do('drop table if exists stuff;');
$db.do('create table if not exists stuff (
id integer,
sid varchar(32)
)');
for ^5 {
$db.do('insert into stuff (id, sid)
values(?,?);', ($_, ('A'..'Z').pick(16).join));
}
my $sql = 'select * from stuff order by id asc';
my $stmt = $db.prepare($sql);
$stmt.execute();
while (my $row = $stmt.fetchrow_hashref) {
FIRST "{$sql}id\tsid".say;
"{$row<id>}\t{$row<sid>}".say;
}
$stmt.finish;###Nested SQL
####Slang::SQL
use Slang::SQL;
use DBIish;
my $*DB = DBIish.connect('SQLite', :database<sqlite.sqlite3>);
sql select * from stuff order by id asc; do -> $row1 {
sql select * from stuff where id > ?; with ($row1<id>) do -> $row2 {
#do something with $row1 or $row2!
};
};####DBIish
use DBIish;
my $db = DBIish.connect('SQLite', :database<sqlite.sqlite3>);
my $sql1 = 'select * from stuff order by id asc';
my $sql2 = 'select * from stuff where id > ?';
my $stm1 = $db.prepare($sql1);
my $stm2 = $db.prepare($sql2);
$stm1.execute();
while (my $row1 = $stm1.fetchrow_hashref) {
$stm2.execute($row1<id>);
while (my $row2 = $stm2.fetchrow_hashref) {
#do something here
}
}
##Mo Better Examples
Check out t/01_basic.t
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent