Rand Stats

SQL::Abstract

zef:leont

Actions Status

Name

SQL::Abstract - Generate SQL from Raku data structures

Synopsis

use SQL::Abstract;

my $abstract = SQL::Abstract.new(:renderer<sqlite>);
my $query = $abstract.select('table', <foo bar>, { :id(3) });
my $result = $dbh.query($result.sql, $result.arguments);

my $join = { :left<books>, :right<authors>, :using<author_id> };
my $result = $abstract.select($join, ['books.name', 'authors.name'], { :cost('<' => 10) });

Description

SQL::Abstract abstracts the generation of SQL queries. Fundamentally its functionality has three components

It should be able to represent any SELECT, UPDATE, INSERT, or DELETE query that is valid in both Postgresql and SQLite. This subset should be generally portable to other databases as well.

Class SQL::Abstract

This is the main class of the module.

new(:$renderer!, Bool :$quoting, *%renderer-args)

This creates a new SQL::Abstract object.

It supports at least two named arguments:

select

method select(Source(Any) $from, Column::List(Any) $columns = *, Conditions(Any) $where?, Common(Any) :$common,
Distinction(Any) :$distinct, GroupBy(Any) :$group-by, Conditions(Any) :$having, Window::Clauses(Any) :$windows,
Compound(Pair) :$compound, OrderBy(Any) :$order-by, Limit :$limit, Offset :$offset, Locking(Any) :$locking)

This will generate a SELECT query. It will select $columns from $from, filtering by $conditions.

my $join = { :left<books>, :right<authors>, :using<author_id> };
my $result = $abstract.select($join, ['books.name', 'authors.name'], { :cost{ '<' => 10 } });
# SELECT books.name, authors.name FROM books INNER JOIN authors USING (author_id) WHERE cost < 10

my @columns = [ 'name', :sum{ :function<count>, :arguments(*) } ];
my $counts = $$abstract.select('artists', @columns, { :name(like => 'A%') }, :group-by<name>, :order-by(:sum<desc>));
# SELECT name, COUNT(*) as sum FROM artists WHERE name LIKE 'A%' GROUP BY name ORDER BY sum DESC

update

method update(Table(Any) $target, Assigns(Any) $assigns, Conditions(Any) $where?,
Common(Any) :$common, Source(Any) :$from, Column::List(Any) :$returning)

This will update $target by assigning the columns and values from $set if they match $where, returning $returning.

$abtract.update('artists', { :name('The Artist (Formerly Known as Prince)') }, { :name<Prince> });
# UPDATE artists SET name = 'The Artist (Formerly Known as Prince)' WHERE name = 'Prince'

insert

Map insertion

method insert(Table(Any) $target, Assigns(Any) $values, Common(Any) :$common,
Overriding(Str) :$overriding, Conflicts(Any) :$conflicts, Column::List(Any) :$returning)

Inserts the values in $values into the table $target, returning the columns in $returning

$abstract.insert('artists', { :name<Metallica> }, :returning(*));
# INSERT INTO artists (name) VALUES ('Metallica') RETURNING *

List insertions

method insert(Table(Any) $target, Identifiers(Any) $columns, Rows(List) $rows, Common(Any) :$common,
Overriding(Str) :$overriding, Conflicts(Any) :$conflicts, Column::List(Any) :$returning)

Insert into $target, assigning each of the values in Rows to a new row in the table. This way one can insert a multitude of rows into a table.

$abstract.insert('artists', ['name'], [ ['Metallica'], ['Motörhead'] ], :returning(*));
# INSERT INTO artists (name) VALUES ('Metallica'), ('Motörhead') RETURNING *

$abstract.insert('artists', List, [ [ 'Metallica'], ], :returning<id>);
# INSERT INTO artists VALUES ('Metallica') RETURNING id

Select insertion

method insert(Table(Any) $target, Identifiers(Any) $columns, Select(Map) $select, Common(Any) :$common,
Overriding(Str) :$overriding, Conflicts(Any) :$conflicts, Column::List(Any) :$returning)

This selects from a (usually different) table, and inserts the values into the table.

$abstract.insert('artists', 'name', { :from<new_artists>, :columns<name> }, :returning(*));
# INSERT INTO artists (name) SELECT name FROM new_artists RETURNING *

upsert

method upsert(Table(Any) $target, Assigns(Any) $values, Conflict::Targets $targets, Common(Any) :$common,
Overriding(Str) :$overriding, Column::List(Any) :$returning)

This upserts data into the table: it tries to insert data but if it fails because of a unique index constraint on one of $targets it will update the other columns instead.

$abstract.upsert('phone_numbers', { :$name, :$number}, ['name']);
# INSERT INTO phone_numbers (name, number) VALUES (?, ?) ON CONFLICT (name) UPDATE number = ?

=end

=head2 delete

=begin code :lang<raku>

method delete(Table(Any) $target, Conditions(Any) $where, Common(Any) :$common,
Source(Any) :$using, Column::List(Any) :$returning)

This deletes rows from the database, optionally returning their values.

$abstract.delete('artists', { :name<Madonna> });
# DELETE FROM artists WHERE name = 'Madonna'

Builder Types

Builder objects generate SQL statements using the builder pattern

my $builder = SQL::Abstract::Builder(:renderer<sqlite>);
my $query = $builder.on('table').where({ :1a, :b<bar> }).update({ :$b }).build;
# "UPDATE table SET b = ? WHERE a = 1 AND b = 'bar'", [ $b ]

Builder

on

method on(Source(Any) $name --> Builder::Source)

This will return a source builder for the indicated table or join

with

method with(Common(Any) $cte --> Builder)

This returns a new Builder object with common table expressions injected into it.

Builder::Source

select

multi method select(Column::List(Any) $columns --> Builder::Select)
multi method select(*@columns --> Builder::Select)

This returns a select builder with the given columns.

insert

multi method insert(Assigns(Any) $values)
multi method insert(Identifiers(Any) $main-columns, Rows(List) $main-rows)
multi method insert(Identifiers(Any) $columns, Value::Default $)

This returns a select builder with the given values.

$builder.insert({ :1a, :b<foo>, :c(Nil) }).build

update

method update(Assigns(Any) $assigns --> Builder::Update)

This returns a select builder with the given assignments.

$builder.on('table').where({ :1a }).update({ :2a })
$builder.on('table').update({ :2a }).where({ :1a })

delete

method delete(--> Builder::Delete)

This return a new delete builder for the given table

upsert

method upsert(Assigns $assigns, Conflict::Targets $targets --> Builder::Insert)

This returns a new insert builder with an upsert clause.

join

method join(Source(Any) $source, Join::Conditions() :$on)
method join(Source(Any) $source, Identifiers() :$using)
method join(Source(Any) $source, Bool :$natural)
method join(Source(Any) $source, Bool :$cross)

Join the target to another table.

$on.join('Bar', :using<id>);
$on.join('Bar', :on{ 'Foo.id' => 'Bar.foo_id' });

where

method where(Conditions(Any) $where --> Builder::On)

This adds a condition to the subsequent operation, or if it's an insert operation adds those values if possible.

Builder::Select

Builder::Insert

Builder::Update

Builder::Delete

Helper types

SQL::Abstract uses various helper types that will generally coerce from basic datastructures:

SQL::Abstract::Identifier

This represents an identifier (typically a table name or column name, or an alias for such). It can be coerced from a string (e.g. "foo" or "foo.bar").

SQL::Abstract::Identifiers

This takes either a list of Identifier(), or a single Identifier().

my SQL::Abstract::Identifiers() $identifiers = <name email website>;

SQL::Abstract::Source

A source is source of data, usually a table or a join. If not passed as a Source object it will upconvert from the following types:

SQL::Abstract::Table does SQL::Abstract::Source

This role takes the same conversions as Source, but only the ones that represent a table. Unlike other sources, this can be used for mutating operations (update/insert/delete).

SQL::Abstract::Column::List

my Column::List() $columns = ('name', :number(:count(*)));
# name, COUNT(*) AS number;

This is a list of items representing a column. Each item can either be a: much like Identifiers, however it will accept not just identifiers but any expression (e.g. comparisons, function calls, etc…). If given a pair it will rename the value to the key (value AS key). A whatever-star will represent all columns.

SQL::Abstract::Conditions

my Conditions() $conditions = { :name(:like<%leon%>), :age(25..45), :country('nl'|'be'|lu') };
# name LIKE '%leon%' AND AGE BETWEEN 25 AND 45 AND country IN('nl', 'be', 'lu')

This is a pair, a list of pairs, a hash or an Expression. In the former three cases, the key (called left in the rest of this section) shall be an Identifier() designating a column name, or an Expression. The right hand side can be one of several types:

Expression

This will be used as-is

Any:U

This will check if the left expression is NULL; :left(Any) equals left IS NULL.

Pair

This will use the key as operator to compare left against another value or expression. E.g. :left('<' => 42) renders like left < 42 . The following keys are known:

A few operators are not binary operators as such.

Range

This will check if a value is in a certain range. E.g. :left(1..42) will render like left BETWEEN 1 AND 42.

Junction

This will check against the values in the function. E.g. :left(1|2|4) will render like left IN (1, 2, 4).

Capture

This will be expanded as a capture expression (. E.g. :left(\'NOW()') will render like left = NOW(). If it's an :op expression, the column will be inserted as first/left operand: :left(\(:op('<' => 42))) renders like left < 42 .

Any

If none of the above options match, the value will be compared to as is (as a placeholder). :left(2) will render equivalent to left = 2.

SQL::Abstract::Assigns

This takes a list of pairs, or a hash. The keys shall be a value or an expression. E.g. :name<author>, :id(SQL::Abstract::Values::Default), :timestamp(\'NOW()')

SQL::Abstract::OrderBy

This takes a list of things to sort by. Much like Column::List this accepts identifiers and expressions, but * isn't allowed and pair values are interpreted as order modifier (e.g. :column<desc>). A hash element will be expanded as well (e.g. { :column<column_name>, :order<desc>, :nulls<last> } )

SQL::Abstract::Common

This represents a common table expression. It converts from a pair or a list of pairs, with the keys being the name and the values being either a table name, a select hash or an SQL::Abstract::Query object.

my Common() $cte = recent => { :from<users>, :columns('name', :count(:count(*)), :group-by(name) };
# WITH recent AS (SELECT name, COUNT(*) AS count FROM users GROUP BY name);
)

SQL::Abstract::Locking

This takes one or more locking clauses. A locking clause is usually taken ... strings: 'update', C'<no key update'>, 'share', 'key share', but it can also take a pair of stregth

SQL::Abstract::GroupBy

This takes a list of grouping elements. Usually these are just columns, but they may also be arbitrary expressions (inclusing lists of columns). A pair is taken as a function call with the key as function name and the value as arguments.

SQL::Abstract::Conflicts

This represents one or more upsert clause. It can either be the string 'nothing', or a pair with the columns as keys and an Assigns(Map).

my SQL::Abstract::Conflicts = <name organization> => { :$email };
# ON CONFLICT (name, organization) DO UPDATE email = ?

SQL::Abstract::Distinction

This takes Truefor a distinct row, or a Column::List for specific rows that have to be distinct.

Window::Definition

Window definiton converts from a map taking the following keys, all optional:

It defaults to 'preceding'.

my Window::Definition $d = { :partition-by<foo bar>, :from<current> :to(:following(5)), :exclude<ties> }
# PARTITION BY foo, bar RANGE BETWEEN CURRENT ROW AND 5 FOLLOWING EXCLUDE TIES

Window::Clauses

This takes one or more pairs, with the names being windows names and the values taking window definition maps.

my Windows::Clauses $clauses =
    over5 => { :frame{ :preceding(5) } },
    foo => { :partition-by<foo bar>, :mode<range>, :from<current> :to(:following(5)), :exclude<ties> };
# WINDOW
#   over5 AS (ROWS 5 PRECEDING),
#   foo as (PARTITION BY foo, bar RANGE BETWEEN CURRENT ROW AND 5 FOLLOWING EXCLUDE TIES)

SQL::Abstract::Limit / SQL::Abstract::Offset

These both take either an Int or an Expression.

Capture expressions

Captures can be used in most places where Expressions can be used. They allow for SQL expressions that can't be encoded using simpler values.

There are two kinds of capture expressions. The first kind has one or more named arguments; the first will be used as literal SQL, the rest will be arguments for the literal. E.g. \'NOW()'.

The second kind takes a single named argument that may or may not contain a value. Currently supported are:

Author

Leon Timmermans fawaka@gmail.com

Copyright and License

Copyright 2022 Leon Timmermans

This library is free software; you can redistribute it and/or modify it under the Artistic License 2.0.