Skip to content
No description, website, or topics provided.
Other
  1. Other 100.0%
Branch: master
Clone or download

Latest commit

Fetching latest commit…
Cannot retrieve the latest commit at this time.

Files

Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
lib/Slang
t
.gitignore
.travis.yml
META6.json
README.md

README.md

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

You can’t perform that action at this time.