### ----------------------------------------------------
### -- SQL::Builder
### -- Licenses: Artistic-2.0
### -- Authors: Adrian Kreher
### -- File: lib/SQL/Builder.rakumod
### ----------------------------------------------------
### -- Chunk 2 of 2
>, <foo bar>, :c<d>);
# SELECT "foo", "bar", "b" AS "a", "d" AS "c" FROM table

# Instead, pass all values as positional elements, in any of the following ways:
$sql.from('table').select([:a<b>, "foo", "bar", :c<d>]);
$sql.from('table').select([:a<b>, <foo bar>.flat, :c<d>]);
$sql.from('table').select((:a<b>), "foo", "bar", (:c<d>));
# SELECT "b" AS "a", "foo", "bar", "d" AS "c" FROM table
=end code

=head2 where(@where)

Provide a C<WHERE> clause with one or more values. These clauses are combined with C<AND> logic by
default. The values are used as a C<ConditionClause>, see the documentation below for the details.

=begin code :lang<raku>
$sql.from('users').select('email').where(:username<ak>);
# sql: SELECT "email" FROM "users" WHERE "username" = ?
# bind: ["ak",]

$sql.from('users').select('email').where(["username", "=", "ak"]);
# sql: SELECT "email" FROM "users" WHERE "username" = ?
# bind: ["ak",]

$sql.from('users').select('email').where([["email", "LIKE", "%gmail.com"], ["email", "LIKE", "ak%"]]);
# sql: SELECT "email" FROM "users" WHERE "email" LIKE ? AND "email" LIKE ?
# bind: ["%gmail.com", "ak%"]

# Use a Capture to make a sub-group to switch to OR logic:
$sql.from('users').select('email').where(\(:or[["email", "LIKE", "%gmail.com"], ["email", "LIKE", "%googlemail.com"]]));
# sql: SELECT "email" FROM "users" WHERE ("email" LIKE ? OR "email" LIKE ?)
# bind: ["%gmail.com", "%googlemail.com"]
=end code

=head2 join($table, :@on, :$using, Str :$as, :$inner/:$left/:$right/:$full)

Provides a C<JOIN> clause to this query. C<$table> should be either the name of a table (treated as
an Identifier), or a C<SelectBuilder> to use a sub-query.

Exactly one of C<:$using> or C<:@on> must be specified, which determines how to join the table.
C<:$using> is for when the columns match exactly. C<:@on> is passed to C<ConditionClause> and lets
you fully control the join logic. See the C<ConditionClause> documentation below. These correspond
to the C<USING(column)> and C<ON expr> portions of the SQL join expression, respectively.

The C<:$as> parameter is optional which controls the join expression's alias. This is needed if you
use a sub-query or the same table is joined multiple times. This is treated as an Identifier.

Finally, one of C<:$inner/:$left/:$right/:$full> may be specified to control the type of JOIN. This
is optional.

Multiple JOINs are supported and are processed in the order they are added.

There is currently no way to clear the list of JOINs from a query.

Examples:

=begin code :lang<raku>
$sql.from('t1').join('t2', :using<id>).select(<t1.foo t2.bar>);
# sql: SELECT "t1"."foo", "t2"."bar" FROM "t1" JOIN "t2" USING("id")

my $inner = $sql.from('t1').select(:bar<foo>, 'id');
$sql.from('t2').
    join(:left, $inner, :as<inner>, :on['inner.id', '=', Identifier.new('t2.id')]).
    select(<t1.foo t2.bar>);
# sql: SELECT "t1"."foo", "t2"."bar" FROM "t2" LEFT JOIN (SELECT "id", "foo" AS "bar" FROM "t1") AS "inner" ON "inner"."id" = "t2"."id"

$sql.from('t1').
    join('t2', :on(["t1.id", "=", Identifier.new("t2.id")], ["t1.foo", "<", "t2.foo"])).
    select(<t1.foo t2.bar>);
# sql: SELECT "t1"."foo", "t2"."bar" FROM "t1" JOIN "t2" ON "t1.id" = "t2.id" AND "t1"."foo" < "t2"."foo"
=end code

=head2 limit(Int $n)

Provides a C<LIMIT> clause (with the specified value as a placeholder):

=begin code :lang<raku>
$sql.from('table').select(<foo bar>).limit(1);
# sql: SELECT "foo", "bar" FROM "table" LIMIT ?
# bind: 1
=end code

=head2 offset(Int $n)

Provides a C<OFFSET> clause (with the specified value as a placeholder):

=begin code :lang<raku>
$sql.from('table').select(<foo bar>).limit(1).offset(2);
# sql: SELECT "foo", "bar" FROM "table" LIMIT ? OFFSET ?
# bind: [1, 2]
=end code

=head2 group-by(*@columns)

Provides a C<GROUP BY> clause on the specified columns:

=begin code :lang<raku>
$sql.from('songs').select(Fn.new('SUM', 'length'), 'artist', 'year').group-by('artist', 'year');
# SELECT SUM("length"), "artist", "year" FROM songs GROUP BY "artist", "year"
=end code

=head2 having(@having)

Provides a C<HAVING> clause. This is handled identical to a C<WHERE> clause, see the documentation above.

=head2 order-by(*@columns)

Provides an C<ORDER BY> clause on the specified columns. Each value is interpreted as an C<Identifier>, though you may specify another subclass of C<SQLSyntax> to use an expression instead:

=begin code :lang<raku>
# pick 10 shortest shortest songs
$sql.from('songs').select('title').order-by('length').limit(10);
# sql: SELECT "title" FROM "songs" ORDER BY "length" limit ?
# bind: 10

# pick 10 random items
$sql.from('songs').select('title').order-by(Fn.new('RANDOM')).limit(10);
# sql: SELECT "title" FROM "songs" ORDER BY RANDOM() limit ?
# bind: 10
=end code

A C<Capture> may be specified to use descending order, by providing the column name (or expression) and C<:desc>. C<:asc> is also supported if you wish to be explicit about ascending order.

=begin code :lang<raku>
# pick 10 longest songs
$sql.from('songs').select('title').order-by(\("length", :desc)).limit(10);
# sql: SELECT "title" FROM "songs" ORDER BY "length" DESC limit ?
# bind: 10
=end code

=head2 build(:$pretty=False)

Converts this C<SelectBuilder> into an C<SQLFragment> that has C<.sql> and C<.bind> methods. This
method may be called more than once, but it is not recommended. The SQL and bind values may not
appear in the same order across invocations.

If C<$pretty> is true, then the resulting SQL will be (minimally) pretty-printed.

=head2 clone()

Returns a new C<SelectBuilder> in the same state. Useful if you want to have a common set of
options, and then use many times. (Note that if you do not clone, then the original builder would be
modified, which may not be what you want.)

=begin code :lang<raku>
sub getuser {
    state $q = $sql.from('users').select(<username email address>);
    $q.clone;
}

# multiple times later:
my $username = "whoever";
my $st2 = getuser().where(:$username).build;
# $db.query($st2.sql, |$st2.bind);
=end code

=head1 INSERT QUERIES

Insert queries are created with the C<insert-into> method on the C<SQL::Builder> object. All other
options can be passed in any order. All options overwrite the current value. Each option returns the
InsertBuilder instance, allowing for a chain style:

=begin code :lang<raku>
$sql.insert-into("table").values([:a(1), :b(2)])
# INSERT INTO "table" ("a", "b") VALUES (?, ?)
=end code

The Insert query requires some values to insert. You may specify this with the C<values> clause to
provide values verbatim, or the combination of C<columns> and C<query> to get values from a subquery
(typically a sub-select).

=head2 new(Str $table)

Creates a C<InsertBuilder> for the given table.

=head2 values(@values)

Set the data to be inserted. The data is a List of Pairs, where the key is the column name (as an
C<Identifier>), and the value is the value (interpreted as a C<Placeholder>). This may be passed in
a variety of styles, all equivalent:

=begin code :lang<raku>
$sql.insert-into("table").values([:a(1), :b(2)])
$sql.insert-into("table").values(:a(1), :b(2))
$sql.insert-into("table").values([:a(1)], :b(2))
# sql: INSERT INTO "table" ("a", "b") VALUES (?, ?)
# bind: [1, 2]
=end code

This only supports inserting a single row.

=head2 columns(@columns)

Sets the columns to insert, as a List of columns (interpreted as C<Identifier>s). Used in
combination with C<query>, see below for an example.

=head2 query(SQLStatement $query)

Provides the values to insert from the result of a query. This query is typically a Select statement
but may be other queries depending on database support.

=begin code :lang<raku>
my $inner = $sql.from("t1").select("a", "b").order-by("a").limit(1);
$sql.insert-into("t2").columns("c", "d").query($inner);
# INSERT INTO "t2" ("c", "d") SELECT "a", "b" FROM "t1" ORDER BY "a" LIMIT 1
=end code

=head2 returning(@columns)

Provides a C<RETURNING> clause, with list of columns (or other expressions) to return. This works
identically to the C<select> clause of a Select query, see that documentation above.

=begin code :lang<raku>
$sql.insert-into("table").values(:a(1), :b(2), :c(3)).returning("b", Fn.new("LOWER", "c"))
# INSERT INTO "table" WHERE "a" = ? RETURNING "b", LOWER("c")
=end code

=head2 build(:$pretty=False)

Converts this C<InsertBuilder> into an C<SQLFragment> that has C<.sql> and C<.bind> methods. This
method may be called more than once, but it is not recommended. The SQL and bind values may not
appear in the same order across invocations.

If C<$pretty> is true, then the resulting SQL will be (minimally) pretty-printed.


=head1 UPDATE QUERIES

Update queries are created with the C<update> method on the C<SQL::Builder> object. All other
options can be passed in any order. All options overwrite the current value. Each option returns the
UpdateBuilder instance, allowing for a chain style:

=begin code :lang<raku>
$sql.update("table").set(:a(1)).where(["b", "=", 2])
# UPDATE "table" SET a = ? WHERE "a" = ?
=end code

=head2 new(Str $table)

Creates a C<UpdateBuilder> for the given table.

=head2 set(@values)

Set the data to be updated. The data is a List of Pairs, where the key is the column name (as an
C<Identifier>), and the value is the value (interpreted as a C<Placeholder>). This may be passed in
a variety of styles, all equivalent:

=begin code :lang<raku>
$sql.update("table").set([:a(1), :b(2)]).where(:c(3))
$sql.update("table").set(:a(1), :b(2)).where(:c(3))
$sql.update("table").set([:a(1)], :b(2)).where(:c(3))
# sql: UPDATE "table" SET "a" = ?, "b" = ? WHERE "c" = ?
# bind: [1, 2, 3]
=end code

If you want to provide an expression for a column, use a C<Fn> or C<Raw> value:

=begin code :lang<raku>
my $fn = Fn.new("MAX", "a", "d");
my $expr = Raw.fmt('{} + {}', Identifier.new("c"), Placeholder.new(1234));
$sql.update("table").set([:a($fn), :b($expr)]).where(:c(3))
# sql: UPDATE "table" SET "a" = MAX("a", "d"), "b" = "c" + ? WHERE "c" = ?
# bind: [1234, 3]
=end code

=head2 where($where) / where(@where)

Provides a C<WHERE> clause with one or more values. This works identically to C<where> for Select
queries, and is used to make a ConditionClause. See documentation for C<where> above, and on
C<ConditionClause> below.

Unlike Select queries, this is required, even if you want to update all rows in a table.

=head2 returning(@columns)

Provides a C<RETURNING> clause, with list of columns (or other expressions) to return. This works
identically to the C<select> clause of a Select query, see that documentation above.

=head2 build(:$pretty=False)

Converts this C<UpdateBuilder> into an C<SQLFragment> that has C<.sql> and C<.bind> methods. This
method may be called more than once, but it is not recommended. The SQL and bind values may not
appear in the same order across invocations.

If C<$pretty> is true, then the resulting SQL will be (minimally) pretty-printed.

=head1 DELETE QUERIES

Delete queries are created with the C<delete-from> method on the C<SQL::Builder> object. All other
options can be passed in any order. All options overwrite the current value. Each option returns the
DeleteBuilder instance, allowing for a chain style:

=begin code :lang<raku>
$sql.delete-from("table").where(["a", "=", 1])
# DELETE FROM "table" WHERE "a" = ?
=end code

=head2 new(Str $table)

Creates a C<DeleteBuilder> for the given table.

=head2 where($where) / where(@where)

Provides a C<WHERE> clause with one or more values. This works identically to C<where> for Select
queries, and is used to make a ConditionClause. See documentation for C<where> above, and on
C<ConditionClause> below.

Unlike Select queries, this is required, even if you want to delete all rows in a table.

=head2 returning(@columns)

Provides a C<RETURNING> clause, with list of columns (or other expressions) to return. This works
identically to the C<select> clause of a Select query, see that documentation above.

=begin code :lang<raku>
$sql.delete-from("table").where(["a", "=", 1]).returning("b", Fn.new("LOWER", "c"))
# DELETE FROM "table" WHERE "a" = ? RETURNING "b", LOWER("c")
=end code

=head2 build(:$pretty=False)

Converts this C<DeleteBuilder> into an C<SQLFragment> that has C<.sql> and C<.bind> methods. This
method may be called more than once, but it is not recommended. The SQL and bind values may not
appear in the same order across invocations.

If C<$pretty> is true, then the resulting SQL will be (minimally) pretty-printed.

=head1 ConditionClause

The C<ConditionClause> syntax is how C<SQL::Builder> encodes the options of the
C<WHERE> clause, the C<HAVING> clause, and the C<JOIN ... ON> clause.

Multiple items are combined with C<AND> logic. To use C<OR> logic, use a
sub-group (Capture), documented below.

At its core, this syntax is a list of items. Each item can be one of three values:

=head2 3-Valued List

The main item is a list of exactly three items, typically a column (C<Identifier>), an operator (C<Raw>), and a C<Placeholder> value.

=begin code :lang<raku>
$q.where(["a", "=", 1]);
# sql: ... WHERE a = ?
# bind: [1,]
=end code

Any of these three can be replaced by an explicit type from the "Type System" section above:

=begin code :lang<raku>
$q.where([Fn.new("lower", "a"), "=", Fn.new("lower", "b")]);
# sql: ... WHERE lower("a") = lower("b")

$q.where(["b", ">=", Raw.fmt('{} / 2.0', Placeholder.new(3))]);
# sql: ... WHERE "b" >= ? / 2.0
# bind: [3,]
=end code

=head2 Pair

As a convenience, a Pair is used for equality.

=begin code :lang<raku>
$q.where([:a(1)]);
# sql: ... WHERE "a" = ?
# bind: [1,]

my $a = 1;
$q.where([:$a]);
# sql: ... WHERE "a" = ?
# bind: [$a,]
=end code

If the value is exactly C<Nil>, then C<IS NULL> is used instead. C<Nil> is the only undefined value
with this special treatment:

=begin code :lang<raku>
$q.where([:a(Nil)]);
# sql: ... WHERE "a" IS NULL

# other values are not special:
$q.where([:a(Any)]);
# sql: ... WHERE "a" = ?
# bind: [Any,]
=end code

=head2 Sub-group (Capture)

To represent a parenthesized sub-group, use a C<Capture> with the C<\(...)> syntax. This Capture
must contain a single Pair, with the key of C<and> or C<or>, depending on which you want, and the
value is another list that creates another C<ConditionClause>.

=begin code :lang<raku>
$q.where([:a<b>, \(:or[
    :c<d>, :e<f>
])]);
# sql: ... WHERE "a" = ? AND (c = ? OR e = ?)
# bind: ["b", "d", "f"]
=end code

This syntax is chosen to avoid difficulties with flattening of lists in Raku. It also avoids some
confusion between Pairs and single-item Hashes.

Subgroups also provide a way to switch from C<AND> to C<OR> logic:

=begin code :lang<raku>
$q.where(\(:or[
    :c<d>, :e<f>
]));
# sql: ... WHERE (c = ? OR e = ?)
# bind: ["d", "f"]
=end code

=head1 COMPATIBILITY

The author primarily uses this module with PostgeSQL, however it should work fine for most database
engines. Some syntax may not be readily accessible outside of the C<Raw.fmt> syntax.

=head1 AUTHOR

Adrian Kreher <avuserow@gmail.com>

=head1 COPYRIGHT AND LICENSE

Copyright 2022 - 2024 Adrian Kreher

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

=end pod


