SQL written once, for every database and language.

#include <iostream>
#include <memory>
#include <string>
#include <tuple>
#include <vector>

#include "wherewith/init.hpp"

using temper::core::NullableParam;
using temper::core::Str;
using temper::core::TemperBubble;
using wherewith::Dialect;
using wherewith::Identifier;
using wherewith::identifierProblem;
using wherewith::PlaceholderStyle;
using wherewith::Query;
using wherewith::QueryBuilder;
using wherewith::SqlValue;

// Each database, with a placeholder style its drivers take.
std::vector<std::tuple<std::string, std::shared_ptr<Dialect>, std::shared_ptr<PlaceholderStyle>>> databases() {
  return {
      std::make_tuple("sqlite", Dialect::sqlite(), PlaceholderStyle::question()),
      std::make_tuple("postgres", Dialect::postgres(), PlaceholderStyle::dollarNumbered()),
      std::make_tuple("mysql", Dialect::mysql(), PlaceholderStyle::percentS()),
      std::make_tuple("duckdb", Dialect::duckdb(), PlaceholderStyle::dollarNumbered()),
  };
}

void forEachDatabase(std::shared_ptr<Query> const &q) {
  for (auto const &db : databases()) {
    std::cout << std::get<0>(db) << ": " << q->render(std::get<1>(db), std::get<2>(db))->sql << "\n";
  }
}

// Values as SQL would write them, joined.
std::string shown(std::vector<std::shared_ptr<SqlValue>> const &values) {
  std::string out;
  for (size_t i = 0; i < values.size(); i++) {
    out += (i ? ", " : "") + std::string(values[i]->toString());
  }
  return out;
}

// templateProblem and renderProblem are nullable: has_value says whether
// there's a problem, and value is its text.
std::string problem(NullableParam<Str> const &found) { return found.has_value ? std::string(found.value) : ""; }

// A common query is a QueryBuilder for Dialect::common(): SQL text with
// appendSafe, and values, names, and other queries in holes between. In
// Temper, sql"…" builds the same.
std::shared_ptr<QueryBuilder> common(std::string const &text) {
  auto b = QueryBuilder::make(Dialect::common());
  b->appendSafe(text);
  return b;
}

using System;
using System.Collections.Generic;
using System.Linq;
using Wherewith;
using static Wherewith.WherewithGlobal;

// Each database, with a placeholder style its drivers take.
var databases = new (string Name, Dialect Dialect, PlaceholderStyle Style)[]
{
    ("sqlite", Dialect.Sqlite, PlaceholderStyle.Question),
    ("postgres", Dialect.Postgres, PlaceholderStyle.DollarNumbered),
    ("mysql", Dialect.Mysql, PlaceholderStyle.PercentS),
    ("duckdb", Dialect.Duckdb, PlaceholderStyle.DollarNumbered),
};

void ForEachDatabase(Query q)
{
    foreach (var db in databases)
    {
        Console.WriteLine($"{db.Name}: {q.Render(db.Dialect, db.Style).Sql}");
    }
}

// Values as SQL would write them, joined.
string Shown(IEnumerable<ISqlValue> values) => string.Join(", ", values.Select(v => v.ToString()));

// A common query is a QueryBuilder for Dialect.Common: SQL text with
// AppendSafe, and values, names, and other queries in holes between. In
// Temper, sql"…" builds the same.
QueryBuilder Common(string text)
{
    var b = new QueryBuilder(Dialect.Common);
    b.AppendSafe(text);
    return b;
}

import static wherewith.WherewithGlobal.identifierProblem;

import java.util.List;
import java.util.stream.Collectors;
import wherewith.Dialect;
import wherewith.Identifier;
import wherewith.PlaceholderStyle;
import wherewith.Query;
import wherewith.QueryBuilder;
import wherewith.RenderedQuery;
import wherewith.SqlValue;

public class Tour {
    // Each database, with a placeholder style its drivers take.
    record Database(String name, Dialect dialect, PlaceholderStyle style) {}

    static final List<Database> DATABASES = List.of(
        new Database("sqlite", Dialect.sqlite, PlaceholderStyle.question),
        new Database("postgres", Dialect.postgres, PlaceholderStyle.dollarNumbered),
        new Database("mysql", Dialect.mysql, PlaceholderStyle.percentS),
        new Database("duckdb", Dialect.duckdb, PlaceholderStyle.dollarNumbered));

    static void forEachDatabase(Query q) {
        for (Database db : DATABASES) {
            System.out.println(db.name() + ": " + q.render(db.dialect(), db.style()).getSql());
        }
    }

    // Values as SQL would write them, joined.
    static String shown(List<SqlValue> values) {
        return values.stream().map(SqlValue::toString).collect(Collectors.joining(", "));
    }

    // A common query is a QueryBuilder for Dialect.common: SQL text with
    // appendSafe, and values, names, and other queries in holes between. In
    // Temper, sql"…" builds the same.
    static QueryBuilder common(String text) {
        QueryBuilder b = new QueryBuilder(Dialect.common);
        b.appendSafe(text);
        return b;
    }

import { Dialect, Identifier, PlaceholderStyle, QueryBuilder, identifierProblem } from "wherewith";

// Each database, with a placeholder style its drivers take.
const databases = [
  ["sqlite", Dialect.sqlite, PlaceholderStyle.question],
  ["postgres", Dialect.postgres, PlaceholderStyle.dollarNumbered],
  ["mysql", Dialect.mysql, PlaceholderStyle.percentS],
  ["duckdb", Dialect.duckdb, PlaceholderStyle.dollarNumbered],
];

function forEachDatabase(q) {
  for (const [name, dialect, style] of databases) {
    console.log(`${name}: ${q.render(dialect, style).sql}`);
  }
}

// A common query is a QueryBuilder for Dialect.common: SQL text with
// appendSafe, and values, names, and other queries in holes between. In
// Temper, sql"…" builds the same.
function common(text) {
  const b = new QueryBuilder(Dialect.common);
  b.appendSafe(text);
  return b;
}

local wherewith = require("wherewith")
local Dialect, Identifier = wherewith.Dialect, wherewith.Identifier
local PlaceholderStyle, QueryBuilder = wherewith.PlaceholderStyle, wherewith.QueryBuilder
local identifierProblem = wherewith.identifierProblem
-- Temper's runtime gives temper.null, not nil, for no value. It's a
-- table, so it's truthy; null_to_nil turns it into nil.
local null_to_nil = require("temper-core").null_to_nil

-- Each database, with a placeholder style its drivers take.
local databases = {
  { "sqlite", Dialect.sqlite, PlaceholderStyle.question },
  { "postgres", Dialect.postgres, PlaceholderStyle.dollarNumbered },
  { "mysql", Dialect.mysql, PlaceholderStyle.percentS },
  { "duckdb", Dialect.duckdb, PlaceholderStyle.dollarNumbered },
}

local function forEachDatabase(q)
  for _, db in ipairs(databases) do
    print(db[1] .. ": " .. q:render(db[2], db[3]).sql)
  end
end

-- Values as SQL would write them, joined.
local function shown(values)
  local out = {}
  for i, v in ipairs(values) do
    out[i] = v:toString()
  end
  return table.concat(out, ", ")
end

-- A common query is a QueryBuilder for Dialect.common: SQL text with
-- appendSafe, and values, names, and other queries in holes between. In
-- Temper, sql"…" builds the same.
local function common(text)
  local b = QueryBuilder(Dialect.common)
  b:appendSafe(text)
  return b
end

from wherewith import Dialect, Identifier, PlaceholderStyle, QueryBuilder, identifier_problem

# Each database, with a placeholder style its drivers take.
databases = [
    ("sqlite", Dialect.sqlite, PlaceholderStyle.question),
    ("postgres", Dialect.postgres, PlaceholderStyle.dollar_numbered),
    ("mysql", Dialect.mysql, PlaceholderStyle.percent_s),
    ("duckdb", Dialect.duckdb, PlaceholderStyle.dollar_numbered),
]


def for_each_database(q):
    for name, dialect, style in databases:
        print(f"{name}: {q.render(dialect, style).sql}")


# A common query is a QueryBuilder for Dialect.common: SQL text with
# append_safe, and values, names, and other queries in holes between. In
# Temper, sql"…" builds the same.
def common(text):
    b = QueryBuilder(Dialect.common)
    b.append_safe(text)
    return b

use wherewith::{identifier_problem, Dialect, Identifier, PlaceholderStyle, Query, QueryBuilder, SqlValue, SqlValueTrait};

// Each database, with a placeholder style its drivers take.
fn databases() -> Vec<(&'static str, Dialect, PlaceholderStyle)> {
    vec![
        ("sqlite", Dialect::sqlite(), PlaceholderStyle::question()),
        ("postgres", Dialect::postgres(), PlaceholderStyle::dollar_numbered()),
        ("mysql", Dialect::mysql(), PlaceholderStyle::percent_s()),
        ("duckdb", Dialect::duckdb(), PlaceholderStyle::dollar_numbered()),
    ]
}

fn for_each_database(q: &Query) -> temper_core::Result<()> {
    for (name, dialect, style) in databases() {
        println!("{name}: {}", q.render(dialect, style)?.sql());
    }
    Ok(())
}

// Values as SQL would write them, joined.
fn shown(values: &[SqlValue]) -> String {
    values.iter().map(|v| v.to_string().to_string()).collect::<Vec<_>>().join(", ")
}

// A common query is a QueryBuilder for Dialect::common(): SQL text with
// append_safe, and values, names, and other queries in holes between. In
// Temper, sql"…" builds the same.
fn common(text: &str) -> QueryBuilder {
    let b = QueryBuilder::new(Dialect::common());
    b.append_safe(text);
    b
}

// A problem, or nothing, as text.
fn problem(found: Option<std::sync::Arc<String>>) -> String {
    found.map(|s| s.to_string()).unwrap_or_default()
}

// Each database, with a placeholder style its drivers take.
class Database(public name: String, public dialect: Dialect, public style: PlaceholderStyle) {}

let tourDatabases: List<Database> = [
  new Database("sqlite", Dialect.sqlite, PlaceholderStyle.question),
  new Database("postgres", Dialect.postgres, PlaceholderStyle.dollarNumbered),
  new Database("mysql", Dialect.mysql, PlaceholderStyle.percentS),
  new Database("duckdb", Dialect.duckdb, PlaceholderStyle.dollarNumbered),
];

// A query's SQL for a database, with that style's placeholders.
let sqlFor(q: Query, d: Dialect, style: PlaceholderStyle): String {
  let r = q.render(d, style) orelse panic();
  r.sql
}

let forEachDatabase(out: ListBuilder<String>, q: Query): Void {
  for (let db of tourDatabases) {
    out.add("${db.name}: ${sqlFor(q, db.dialect, db.style)}");
  }
}

// Values as SQL would write them, joined.
let joinedValues(values: List<SqlValue>): String {
  values.join(", ", fn (v: SqlValue): String { v.toString() })
}

// A common query built in code, as the other languages build it, for
// what the sql tag would refuse, or would build in pieces.
let common(text: String): QueryBuilder {
  let b = new QueryBuilder(Dialect.common);
  b.appendSafe(text);
  b
}

auto b = QueryBuilder::make(Dialect::common());
b->appendSafe("SELECT name, coalesce(nick, name) AS shown FROM users WHERE id IN (");
b->appendInt(7);
b->appendSafe(", ");
b->appendInt(9);
b->appendSafe(") ORDER BY name NULLS LAST LIMIT ");
b->appendInt(10);
auto q = b->build();  // throws TemperBubble if the query is refused

// Rendered for Dialect::common(), it's the canonical form, for logs.
std::cout << "common: " << q->render(Dialect::common(), PlaceholderStyle::question())->sql << "\n";
// PostgreSQL already sorts nulls last going up, so it needs no NULLS LAST,
// and MySQL has no NULLS, so it sorts by name IS NULL first.
forEachDatabase(q);
// Values print as SQL would write them, for reading only.
std::cout << "params: " << shown(*q->params) << "\n";

var b = new QueryBuilder(Dialect.Common);
b.AppendSafe("SELECT name, coalesce(nick, name) AS shown FROM users WHERE id IN (");
b.AppendInt(7);
b.AppendSafe(", ");
b.AppendInt(9);
b.AppendSafe(") ORDER BY name NULLS LAST LIMIT ");
b.AppendInt(10);
var q = b.Build(); // throws an Exception if the query is refused

// Rendered for Dialect.Common, it's the canonical form, for logs.
Console.WriteLine("common: " + q.Render(Dialect.Common, PlaceholderStyle.Question).Sql);
// PostgreSQL already sorts nulls last going up, so it needs no NULLS LAST,
// and MySQL has no NULLS, so it sorts by name IS NULL first.
ForEachDatabase(q);
// Values print as SQL would write them, for reading only.
Console.WriteLine("params: " + Shown(q.Params));

QueryBuilder b = new QueryBuilder(Dialect.common);
b.appendSafe("SELECT name, coalesce(nick, name) AS shown FROM users WHERE id IN (");
b.appendInt(7);
b.appendSafe(", ");
b.appendInt(9);
b.appendSafe(") ORDER BY name NULLS LAST LIMIT ");
b.appendInt(10);
Query q = b.build(); // throws RuntimeException if the query is refused

// Rendered for Dialect.common, it's the canonical form, for logs.
System.out.println("common: " + q.render(Dialect.common, PlaceholderStyle.question).getSql());
// PostgreSQL already sorts nulls last going up, so it needs no NULLS
// LAST, and MySQL has no NULLS, so it sorts by name IS NULL first.
forEachDatabase(q);
// Values print as SQL would write them, for reading only.
System.out.println("params: " + shown(q.getParams()));

let b = new QueryBuilder(Dialect.common);
b.appendSafe("SELECT name, coalesce(nick, name) AS shown FROM users WHERE id IN (");
b.appendInt(7);
b.appendSafe(", ");
b.appendInt(9);
b.appendSafe(") ORDER BY name NULLS LAST LIMIT ");
b.appendInt(10);
let q = b.build(); // throws if the query is refused

// Rendered for Dialect.common, it's the canonical form, for logs.
console.log("common: " + q.render(Dialect.common, PlaceholderStyle.question).sql);
// PostgreSQL already sorts nulls last going up, so it needs no NULLS LAST,
// and MySQL has no NULLS, so it sorts by name IS NULL first.
forEachDatabase(q);
// Values print as SQL would write them, for reading only.
console.log("params: " + q.params.map((v) => v.toString()).join(", "));

local b = QueryBuilder(Dialect.common)
b:appendSafe("SELECT name, coalesce(nick, name) AS shown FROM users WHERE id IN (")
b:appendInt(7)
b:appendSafe(", ")
b:appendInt(9)
b:appendSafe(") ORDER BY name NULLS LAST LIMIT ")
b:appendInt(10)
local q = b:build() -- raises an error if the query is refused

-- Rendered for Dialect.common, it's the canonical form, for logs.
print("common: " .. q:render(Dialect.common, PlaceholderStyle.question).sql)
-- PostgreSQL already sorts nulls last going up, so it needs no NULLS LAST,
-- and MySQL has no NULLS, so it sorts by name IS NULL first.
forEachDatabase(q)
-- Values print as SQL would write them, for reading only.
print("params: " .. shown(q.params))

b = QueryBuilder(Dialect.common)
b.append_safe("SELECT name, coalesce(nick, name) AS shown FROM users WHERE id IN (")
b.append_int(7)
b.append_safe(", ")
b.append_int(9)
b.append_safe(") ORDER BY name NULLS LAST LIMIT ")
b.append_int(10)
q = b.build()  # raises RuntimeError if the query is refused

# Rendered for Dialect.common, it's the canonical form, for logs.
print("common: " + q.render(Dialect.common, PlaceholderStyle.question).sql)
# PostgreSQL already sorts nulls last going up, so it needs no NULLS LAST,
# and MySQL has no NULLS, so it sorts by name IS NULL first.
for_each_database(q)
# Values print as SQL would write them, for reading only.
print("params: " + ", ".join(str(v) for v in q.params))

let b = QueryBuilder::new(Dialect::common());
b.append_safe("SELECT name, coalesce(nick, name) AS shown FROM users WHERE id IN (");
b.append_int(7);
b.append_safe(", ");
b.append_int(9);
b.append_safe(") ORDER BY name NULLS LAST LIMIT ");
b.append_int(10);
let q = b.build()?; // an Err if the query is refused

// Rendered for Dialect::common(), it's the canonical form, for logs.
println!("common: {}", q.render(Dialect::common(), PlaceholderStyle::question())?.sql());
// PostgreSQL already sorts nulls last going up, so it needs no NULLS
// LAST, and MySQL has no NULLS, so it sorts by name IS NULL first.
for_each_database(&q)?;
// Values print as SQL would write them, for reading only.
println!("params: {}", shown(&q.params()));

// One query, as plain SQL with holes for values. The sql tag checks
// it for every database, and panics if it's refused.
let q = sql"""
  "SELECT name, coalesce(nick, name) AS shown FROM users
  "WHERE id IN (${7}, ${9}) ORDER BY name NULLS LAST LIMIT ${10}
;
// Rendered for Dialect.common, it's the canonical form, for logs.
say("common: ${sqlFor(q, Dialect.common, PlaceholderStyle.question)}");
// PostgreSQL already sorts nulls last going up, so it needs no NULLS
// LAST, and MySQL has no NULLS, so it sorts by name IS NULL first.
forEachDatabase(out, q);
// Values print as SQL would write them, for reading only.
say("params: ${joinedValues(q.params)}");

std::string name = "Robert'); DROP TABLE students; --";
// Building SQL by hand puts the value in the text, where it ends the
// string early and adds a statement of its own.
std::cout << "by hand: SELECT id FROM students WHERE name = '" << name << "'\n";
// A hole sends the value apart from the SQL, for the driver to bind.
b = common("SELECT id FROM students WHERE name = ");
b->appendString(name);
auto r = b->build()->render(Dialect::sqlite(), PlaceholderStyle::question());
std::cout << "wherewith: " << r->sql << "\n";
std::cout << "params: " << shown(*r->params) << "\n";

// A name from outside is an Identifier, quoted for each database, never
// a value or SQL.
b = common("SELECT * FROM ");
b->appendIdentifier(Identifier::named("students\"; DROP TABLE x; --"));
q = b->build();
std::cout << "a hostile name: " << q->render(Dialect::sqlite(), PlaceholderStyle::question())->sql << "\n";
std::cout << "in MySQL: " << q->render(Dialect::mysql(), PlaceholderStyle::percentS())->sql << "\n";

// A hole inside quotes is refused: it would be text, not a value.
b = common("SELECT id FROM students WHERE name = '");
b->appendString(name);
b->appendSafe("'");
std::cout << "a hole in quotes: " << problem(b->get_templateProblem()) << "\n";

var name = "Robert'); DROP TABLE students; --";
// Building SQL by hand puts the value in the text, where it ends the
// string early and adds a statement of its own.
Console.WriteLine("by hand: SELECT id FROM students WHERE name = '" + name + "'");
// A hole sends the value apart from the SQL, for the driver to bind.
b = Common("SELECT id FROM students WHERE name = ");
b.AppendString(name);
var r = b.Build().Render(Dialect.Sqlite, PlaceholderStyle.Question);
Console.WriteLine("wherewith: " + r.Sql);
Console.WriteLine("params: " + Shown(r.Params));

// A name from outside is an Identifier, quoted for each database, never
// a value or SQL.
b = Common("SELECT * FROM ");
b.AppendIdentifier(Identifier.Named(@"students""; DROP TABLE x; --"));
q = b.Build();
Console.WriteLine("a hostile name: " + q.Render(Dialect.Sqlite, PlaceholderStyle.Question).Sql);
Console.WriteLine("in MySQL: " + q.Render(Dialect.Mysql, PlaceholderStyle.PercentS).Sql);

// A hole inside quotes is refused: it would be text, not a value.
b = Common("SELECT id FROM students WHERE name = '");
b.AppendString(name);
b.AppendSafe("'");
Console.WriteLine("a hole in quotes: " + b.TemplateProblem);

String name = "Robert'); DROP TABLE students; --";
// Building SQL by hand puts the value in the text, where it ends the
// string early and adds a statement of its own.
System.out.println("by hand: SELECT id FROM students WHERE name = '" + name + "'");
// A hole sends the value apart from the SQL, for the driver to bind.
b = common("SELECT id FROM students WHERE name = ");
b.appendString(name);
RenderedQuery r = b.build().render(Dialect.sqlite, PlaceholderStyle.question);
System.out.println("wherewith: " + r.getSql());
System.out.println("params: " + shown(r.getParams()));

// A name from outside is an Identifier, quoted for each database,
// never a value or SQL.
b = common("SELECT * FROM ");
b.appendIdentifier(Identifier.named("students\"; DROP TABLE x; --"));
q = b.build();
System.out.println("a hostile name: " + q.render(Dialect.sqlite, PlaceholderStyle.question).getSql());
System.out.println("in MySQL: " + q.render(Dialect.mysql, PlaceholderStyle.percentS).getSql());

// A hole inside quotes is refused: it would be text, not a value.
b = common("SELECT id FROM students WHERE name = '");
b.appendString(name);
b.appendSafe("'");
System.out.println("a hole in quotes: " + b.getTemplateProblem());

const name = "Robert'); DROP TABLE students; --";
// Building SQL by hand puts the value in the text, where it ends the
// string early and adds a statement of its own.
console.log("by hand: SELECT id FROM students WHERE name = '" + name + "'");
// A hole sends the value apart from the SQL, for the driver to bind.
b = common("SELECT id FROM students WHERE name = ");
b.appendString(name);
const r = b.build().render(Dialect.sqlite, PlaceholderStyle.question);
console.log("wherewith: " + r.sql);
console.log("params: " + r.params.map((v) => v.toString()).join(", "));

// A name from outside is an Identifier, quoted for each database, never
// a value or SQL.
b = common("SELECT * FROM ");
b.appendIdentifier(Identifier.named('students"; DROP TABLE x; --'));
q = b.build();
console.log("a hostile name: " + q.render(Dialect.sqlite, PlaceholderStyle.question).sql);
console.log("in MySQL: " + q.render(Dialect.mysql, PlaceholderStyle.percentS).sql);

// A hole inside quotes is refused: it would be text, not a value.
b = common("SELECT id FROM students WHERE name = '");
b.appendString(name);
b.appendSafe("'");
console.log("a hole in quotes: " + b.templateProblem);

local name = "Robert'); DROP TABLE students; --"
-- Building SQL by hand puts the value in the text, where it ends the
-- string early and adds a statement of its own.
print("by hand: SELECT id FROM students WHERE name = '" .. name .. "'")
-- A hole sends the value apart from the SQL, for the driver to bind.
b = common("SELECT id FROM students WHERE name = ")
b:appendString(name)
local r = b:build():render(Dialect.sqlite, PlaceholderStyle.question)
print("wherewith: " .. r.sql)
print("params: " .. shown(r.params))

-- A name from outside is an Identifier, quoted for each database, never
-- a value or SQL.
b = common("SELECT * FROM ")
b:appendIdentifier(Identifier.named('students"; DROP TABLE x; --'))
q = b:build()
print("a hostile name: " .. q:render(Dialect.sqlite, PlaceholderStyle.question).sql)
print("in MySQL: " .. q:render(Dialect.mysql, PlaceholderStyle.percentS).sql)

-- A hole inside quotes is refused: it would be text, not a value.
b = common("SELECT id FROM students WHERE name = '")
b:appendString(name)
b:appendSafe("'")
print("a hole in quotes: " .. b.templateProblem)

name = "Robert'); DROP TABLE students; --"
# Building SQL by hand puts the value in the text, where it ends the
# string early and adds a statement of its own.
print("by hand: SELECT id FROM students WHERE name = '" + name + "'")
# A hole sends the value apart from the SQL, for the driver to bind.
b = common("SELECT id FROM students WHERE name = ")
b.append_string(name)
r = b.build().render(Dialect.sqlite, PlaceholderStyle.question)
print("wherewith: " + r.sql)
print("params: " + ", ".join(str(v) for v in r.params))

# A name from outside is an Identifier, quoted for each database, never
# a value or SQL.
b = common("SELECT * FROM ")
b.append_identifier(Identifier.named('students"; DROP TABLE x; --'))
q = b.build()
print("a hostile name: " + q.render(Dialect.sqlite, PlaceholderStyle.question).sql)
print("in MySQL: " + q.render(Dialect.mysql, PlaceholderStyle.percent_s).sql)

# A hole inside quotes is refused: it would be text, not a value.
b = common("SELECT id FROM students WHERE name = '")
b.append_string(name)
b.append_safe("'")
print("a hole in quotes: " + b.template_problem)

let name = "Robert'); DROP TABLE students; --";
// Building SQL by hand puts the value in the text, where it ends the
// string early and adds a statement of its own.
println!("by hand: SELECT id FROM students WHERE name = '{name}'");
// A hole sends the value apart from the SQL, for the driver to bind.
let b = common("SELECT id FROM students WHERE name = ");
b.append_string(Some(name));
let r = b.build()?.render(Dialect::sqlite(), PlaceholderStyle::question())?;
println!("wherewith: {}", r.sql());
println!("params: {}", shown(&r.params()));

// A name from outside is an Identifier, quoted for each database, never
// a value or SQL.
let b = common("SELECT * FROM ");
b.append_identifier(Identifier::named(r#"students"; DROP TABLE x; --"#)?);
let q = b.build()?;
println!("a hostile name: {}", q.render(Dialect::sqlite(), PlaceholderStyle::question())?.sql());
println!("in MySQL: {}", q.render(Dialect::mysql(), PlaceholderStyle::percent_s())?.sql());

// A hole inside quotes is refused: it would be text, not a value.
let b = common("SELECT id FROM students WHERE name = '");
b.append_string(Some(name));
b.append_safe("'");
println!("a hole in quotes: {}", problem(b.template_problem()));

let name = "Robert'); DROP TABLE students; --";
// Building SQL by hand puts the value in the text, where it ends the
// string early and adds a statement of its own.
say("by hand: SELECT id FROM students WHERE name = '${name}'");
// A hole sends the value apart from the SQL, for the driver to bind.
let r = sql"SELECT id FROM students WHERE name = ${name}".render(
  Dialect.sqlite, PlaceholderStyle.question) orelse panic();
say("wherewith: ${r.sql}");
say("params: ${joinedValues(r.params)}");

// A name from outside is an Identifier, quoted for each database,
// never a value or SQL.
let table = Identifier.named("students\"; DROP TABLE x; --") orelse panic();
let named = sql"SELECT * FROM ${table}";
say("a hostile name: ${sqlFor(named, Dialect.sqlite, PlaceholderStyle.question)}");
say("in MySQL: ${sqlFor(named, Dialect.mysql, PlaceholderStyle.percentS)}");

// A hole inside quotes is refused: it would be text, not a value.
// The tag would panic, so this builds it in code.
let quoted = common("SELECT id FROM students WHERE name = '");
quoted.appendString(name);
quoted.appendSafe("'");
say("a hole in quotes: ${quoted.templateProblem ?? ""}");

// Each of these runs on every database, and gives different results on
// some. get_templateProblem says why build would refuse it, and where.
void refused(std::string const &sql) {
  auto found = common(sql)->get_templateProblem();
  std::cout << sql << ": " << (found.has_value ? std::string(found.value) : "built") << "\n";
}

// 7 / 2 is 3 in SQLite and PostgreSQL, and 3.5 in MySQL and DuckDB.
refused("SELECT a / 2 FROM t");
// b = NULL matches no row: NULL isn't equal to anything, even NULL.
refused("SELECT a FROM t WHERE b = NULL");
// PostgreSQL reads Name as name; the others keep its case.
refused("SELECT Name FROM t");
// SQLite gives any row's a; the others refuse the query.
refused("SELECT a, count(*) AS n FROM t GROUP BY b");
// SQLite reads ('a' || 1) + 2, PostgreSQL 'a' || (1 + 2).
refused("SELECT 'a' || 1 + 2 AS x");
// Some databases turn 0 into text, and others refuse it.
refused("SELECT CASE WHEN a > 1 THEN 'x' ELSE 0 END AS c FROM t");
// Databases name a + 1 differently (a+1, ?column?, or (a + 1)), so
// an expression in a FROM subquery needs an alias.
refused("SELECT * FROM (SELECT a + 1 FROM t) AS s");
// A placeholder in the text would shift every value's place.
refused("SELECT a FROM t WHERE id = ?");
// What isn't in the common dialect yet is refused too.
refused("SELECT 1 AS a UNION SELECT 2");
// The fix for the first: a float operand divides the same everywhere.
refused("SELECT 7 / 2.0 AS x");
// A null value is refused where NULL would be.
b = common("SELECT a FROM t WHERE b = ");
b->appendString(nullptr);
std::cout << "SELECT a FROM t WHERE b = ${null}: " << problem(b->get_templateProblem()) << "\n";

// Each of these runs on every database, and gives different results on
// some. TemplateProblem says why Build would refuse it, and where.
void Refused(string sql) => Console.WriteLine($"{sql}: {Common(sql).TemplateProblem ?? "built"}");

// 7 / 2 is 3 in SQLite and PostgreSQL, and 3.5 in MySQL and DuckDB.
Refused("SELECT a / 2 FROM t");
// b = NULL matches no row: NULL isn't equal to anything, even NULL.
Refused("SELECT a FROM t WHERE b = NULL");
// PostgreSQL reads Name as name; the others keep its case.
Refused("SELECT Name FROM t");
// SQLite gives any row's a; the others refuse the query.
Refused("SELECT a, count(*) AS n FROM t GROUP BY b");
// SQLite reads ('a' || 1) + 2, PostgreSQL 'a' || (1 + 2).
Refused("SELECT 'a' || 1 + 2 AS x");
// Some databases turn 0 into text, and others refuse it.
Refused("SELECT CASE WHEN a > 1 THEN 'x' ELSE 0 END AS c FROM t");
// Databases name a + 1 differently (a+1, ?column?, or (a + 1)), so
// an expression in a FROM subquery needs an alias.
Refused("SELECT * FROM (SELECT a + 1 FROM t) AS s");
// A placeholder in the text would shift every value's place.
Refused("SELECT a FROM t WHERE id = ?");
// What isn't in the common dialect yet is refused too.
Refused("SELECT 1 AS a UNION SELECT 2");
// The fix for the first: a float operand divides the same everywhere.
Refused("SELECT 7 / 2.0 AS x");
// A null value is refused where NULL would be.
b = Common("SELECT a FROM t WHERE b = ");
b.AppendString(null);
Console.WriteLine("SELECT a FROM t WHERE b = ${null}: " + b.TemplateProblem);

// Each of these runs on every database, and gives different results on
// some. getTemplateProblem says why build would refuse it, and where.
static void refused(String sql) {
    String problem = common(sql).getTemplateProblem();
    System.out.println(sql + ": " + (problem != null ? problem : "built"));
}

// 7 / 2 is 3 in SQLite and PostgreSQL, and 3.5 in MySQL and DuckDB.
refused("SELECT a / 2 FROM t");
// b = NULL matches no row: NULL isn't equal to anything, even NULL.
refused("SELECT a FROM t WHERE b = NULL");
// PostgreSQL reads Name as name; the others keep its case.
refused("SELECT Name FROM t");
// SQLite gives any row's a; the others refuse the query.
refused("SELECT a, count(*) AS n FROM t GROUP BY b");
// SQLite reads ('a' || 1) + 2, PostgreSQL 'a' || (1 + 2).
refused("SELECT 'a' || 1 + 2 AS x");
// Some databases turn 0 into text, and others refuse it.
refused("SELECT CASE WHEN a > 1 THEN 'x' ELSE 0 END AS c FROM t");
// Databases name a + 1 differently (a+1, ?column?, or (a + 1)), so
// an expression in a FROM subquery needs an alias.
refused("SELECT * FROM (SELECT a + 1 FROM t) AS s");
// A placeholder in the text would shift every value's place.
refused("SELECT a FROM t WHERE id = ?");
// What isn't in the common dialect yet is refused too.
refused("SELECT 1 AS a UNION SELECT 2");
// The fix for the first: a float operand divides the same everywhere.
refused("SELECT 7 / 2.0 AS x");
// A null value is refused where NULL would be.
b = common("SELECT a FROM t WHERE b = ");
b.appendString(null);
System.out.println("SELECT a FROM t WHERE b = ${null}: " + b.getTemplateProblem());

// Each of these runs on every database, and gives different results on
// some. templateProblem says why build would refuse it, and where.
function refused(sql) {
  console.log(`${sql}: ${common(sql).templateProblem ?? "built"}`);
}

// 7 / 2 is 3 in SQLite and PostgreSQL, and 3.5 in MySQL and DuckDB.
refused("SELECT a / 2 FROM t");
// b = NULL matches no row: NULL isn't equal to anything, even NULL.
refused("SELECT a FROM t WHERE b = NULL");
// PostgreSQL reads Name as name; the others keep its case.
refused("SELECT Name FROM t");
// SQLite gives any row's a; the others refuse the query.
refused("SELECT a, count(*) AS n FROM t GROUP BY b");
// SQLite reads ('a' || 1) + 2, PostgreSQL 'a' || (1 + 2).
refused("SELECT 'a' || 1 + 2 AS x");
// Some databases turn 0 into text, and others refuse it.
refused("SELECT CASE WHEN a > 1 THEN 'x' ELSE 0 END AS c FROM t");
// Databases name a + 1 differently (a+1, ?column?, or (a + 1)), so
// an expression in a FROM subquery needs an alias.
refused("SELECT * FROM (SELECT a + 1 FROM t) AS s");
// A placeholder in the text would shift every value's place.
refused("SELECT a FROM t WHERE id = ?");
// What isn't in the common dialect yet is refused too.
refused("SELECT 1 AS a UNION SELECT 2");
// The fix for the first: a float operand divides the same everywhere.
refused("SELECT 7 / 2.0 AS x");
// A null value is refused where NULL would be.
b = common("SELECT a FROM t WHERE b = ");
b.appendString(null);
console.log("SELECT a FROM t WHERE b = ${null}: " + b.templateProblem);

-- Each of these runs on every database, and gives different results on
-- some. templateProblem says why build would refuse it, and where.
local function refused(sql)
  print(sql .. ": " .. (null_to_nil(common(sql).templateProblem) or "built"))
end

-- 7 / 2 is 3 in SQLite and PostgreSQL, and 3.5 in MySQL and DuckDB.
refused("SELECT a / 2 FROM t")
-- b = NULL matches no row: NULL isn't equal to anything, even NULL.
refused("SELECT a FROM t WHERE b = NULL")
-- PostgreSQL reads Name as name; the others keep its case.
refused("SELECT Name FROM t")
-- SQLite gives any row's a; the others refuse the query.
refused("SELECT a, count(*) AS n FROM t GROUP BY b")
-- SQLite reads ('a' || 1) + 2, PostgreSQL 'a' || (1 + 2).
refused("SELECT 'a' || 1 + 2 AS x")
-- Some databases turn 0 into text, and others refuse it.
refused("SELECT CASE WHEN a > 1 THEN 'x' ELSE 0 END AS c FROM t")
-- Databases name a + 1 differently (a+1, ?column?, or (a + 1)), so
-- an expression in a FROM subquery needs an alias.
refused("SELECT * FROM (SELECT a + 1 FROM t) AS s")
-- A placeholder in the text would shift every value's place.
refused("SELECT a FROM t WHERE id = ?")
-- What isn't in the common dialect yet is refused too.
refused("SELECT 1 AS a UNION SELECT 2")
-- The fix for the first: a float operand divides the same everywhere.
refused("SELECT 7 / 2.0 AS x")
-- A null value is refused where NULL would be.
b = common("SELECT a FROM t WHERE b = ")
b:appendString(nil)
print("SELECT a FROM t WHERE b = ${null}: " .. b.templateProblem)

# Each of these runs on every database, and gives different results on
# some. template_problem says why build would refuse it, and where.
def refused(sql):
    print(f"{sql}: {common(sql).template_problem or 'built'}")


# 7 / 2 is 3 in SQLite and PostgreSQL, and 3.5 in MySQL and DuckDB.
refused("SELECT a / 2 FROM t")
# b = NULL matches no row: NULL isn't equal to anything, even NULL.
refused("SELECT a FROM t WHERE b = NULL")
# PostgreSQL reads Name as name; the others keep its case.
refused("SELECT Name FROM t")
# SQLite gives any row's a; the others refuse the query.
refused("SELECT a, count(*) AS n FROM t GROUP BY b")
# SQLite reads ('a' || 1) + 2, PostgreSQL 'a' || (1 + 2).
refused("SELECT 'a' || 1 + 2 AS x")
# Some databases turn 0 into text, and others refuse it.
refused("SELECT CASE WHEN a > 1 THEN 'x' ELSE 0 END AS c FROM t")
# Databases name a + 1 differently (a+1, ?column?, or (a + 1)), so
# an expression in a FROM subquery needs an alias.
refused("SELECT * FROM (SELECT a + 1 FROM t) AS s")
# A placeholder in the text would shift every value's place.
refused("SELECT a FROM t WHERE id = ?")
# What isn't in the common dialect yet is refused too.
refused("SELECT 1 AS a UNION SELECT 2")
# The fix for the first: a float operand divides the same everywhere.
refused("SELECT 7 / 2.0 AS x")
# A null value is refused where NULL would be.
b = common("SELECT a FROM t WHERE b = ")
b.append_string(None)
print("SELECT a FROM t WHERE b = ${null}: " + b.template_problem)

// Each of these runs on every database, and gives different results on
// some. template_problem says why build would refuse it, and where.
let refused = |sql: &str| {
    let found = common(sql).template_problem();
    println!("{sql}: {}", found.map(|s| s.to_string()).unwrap_or("built".into()));
};

// 7 / 2 is 3 in SQLite and PostgreSQL, and 3.5 in MySQL and DuckDB.
refused("SELECT a / 2 FROM t");
// b = NULL matches no row: NULL isn't equal to anything, even NULL.
refused("SELECT a FROM t WHERE b = NULL");
// PostgreSQL reads Name as name; the others keep its case.
refused("SELECT Name FROM t");
// SQLite gives any row's a; the others refuse the query.
refused("SELECT a, count(*) AS n FROM t GROUP BY b");
// SQLite reads ('a' || 1) + 2, PostgreSQL 'a' || (1 + 2).
refused("SELECT 'a' || 1 + 2 AS x");
// Some databases turn 0 into text, and others refuse it.
refused("SELECT CASE WHEN a > 1 THEN 'x' ELSE 0 END AS c FROM t");
// Databases name a + 1 differently (a+1, ?column?, or (a + 1)), so
// an expression in a FROM subquery needs an alias.
refused("SELECT * FROM (SELECT a + 1 FROM t) AS s");
// A placeholder in the text would shift every value's place.
refused("SELECT a FROM t WHERE id = ?");
// What isn't in the common dialect yet is refused too.
refused("SELECT 1 AS a UNION SELECT 2");
// The fix for the first: a float operand divides the same everywhere.
refused("SELECT 7 / 2.0 AS x");
// A null value is refused where NULL would be.
let b = common("SELECT a FROM t WHERE b = ");
b.append_string(None::<&str>);
println!("SELECT a FROM t WHERE b = ${{null}}: {}", problem(b.template_problem()));

// Each of these runs on every database, and gives different results
// on some. templateProblem says why build would refuse it, and
// where; the sql tag would panic with the same.
let refused(text: String): Void {
  say("${text}: ${common(text).templateProblem ?? "built"}");
}

// 7 / 2 is 3 in SQLite and PostgreSQL, and 3.5 in MySQL and DuckDB.
refused("SELECT a / 2 FROM t");
// b = NULL matches no row: NULL isn't equal to anything, even NULL.
refused("SELECT a FROM t WHERE b = NULL");
// PostgreSQL reads Name as name; the others keep its case.
refused("SELECT Name FROM t");
// SQLite gives any row's a; the others refuse the query.
refused("SELECT a, count(*) AS n FROM t GROUP BY b");
// SQLite reads ('a' || 1) + 2, PostgreSQL 'a' || (1 + 2).
refused("SELECT 'a' || 1 + 2 AS x");
// Some databases turn 0 into text, and others refuse it.
refused("SELECT CASE WHEN a > 1 THEN 'x' ELSE 0 END AS c FROM t");
// Databases name a + 1 differently (a+1, ?column?, or (a + 1)), so
// an expression in a FROM subquery needs an alias.
refused("SELECT * FROM (SELECT a + 1 FROM t) AS s");
// A placeholder in the text would shift every value's place.
refused("SELECT a FROM t WHERE id = ?");
// What isn't in the common dialect yet is refused too.
refused("SELECT 1 AS a UNION SELECT 2");
// The fix for the first: a float operand divides the same everywhere.
refused("SELECT 7 / 2.0 AS x");
// A null value is refused where NULL would be.
let withNull = common("SELECT a FROM t WHERE b = ");
withNull.appendString(null);
say("SELECT a FROM t WHERE b = \${null}: ${withNull.templateProblem ?? ""}");

// MySQL's || means OR, so it's CONCAT there.
std::string text = "SELECT id || ':' || name AS tag FROM people";
std::cout << text << "\n";
forEachDatabase(common(text)->build());
// Each database names its types differently, and MySQL's LENGTH counts
// bytes, not characters.
text = "SELECT CAST(score AS INTEGER) AS s, CAST(id AS TEXT) AS t, LENGTH(name) AS n FROM people";
std::cout << text << "\n";
forEachDatabase(common(text)->build());
// Where nulls sort differs, and so does OFFSET without LIMIT.
b = common("SELECT name FROM people ORDER BY nick DESC OFFSET ");
b->appendInt(20);
std::cout << "SELECT name FROM people ORDER BY nick DESC OFFSET ${20}\n";
forEachDatabase(b->build());
// PostgreSQL can't guess a lone parameter's type, so it gets a cast.
b = common("SELECT ");
b->appendString("hi");
b->appendSafe(" AS greeting");
std::cout << "SELECT ${\"hi\"} AS greeting\n";
forEachDatabase(b->build());

// MySQL's || means OR, so it's CONCAT there.
var text = "SELECT id || ':' || name AS tag FROM people";
Console.WriteLine(text);
ForEachDatabase(Common(text).Build());
// Each database names its types differently, and MySQL's LENGTH counts
// bytes, not characters.
text = "SELECT CAST(score AS INTEGER) AS s, CAST(id AS TEXT) AS t, LENGTH(name) AS n FROM people";
Console.WriteLine(text);
ForEachDatabase(Common(text).Build());
// Where nulls sort differs, and so does OFFSET without LIMIT.
b = Common("SELECT name FROM people ORDER BY nick DESC OFFSET ");
b.AppendInt(20);
Console.WriteLine("SELECT name FROM people ORDER BY nick DESC OFFSET ${20}");
ForEachDatabase(b.Build());
// PostgreSQL can't guess a lone parameter's type, so it gets a cast.
b = Common("SELECT ");
b.AppendString("hi");
b.AppendSafe(" AS greeting");
Console.WriteLine(@"SELECT ${""hi""} AS greeting");
ForEachDatabase(b.Build());

// MySQL's || means OR, so it's CONCAT there.
String text = "SELECT id || ':' || name AS tag FROM people";
System.out.println(text);
forEachDatabase(common(text).build());
// Each database names its types differently, and MySQL's LENGTH
// counts bytes, not characters.
text = "SELECT CAST(score AS INTEGER) AS s, CAST(id AS TEXT) AS t, LENGTH(name) AS n FROM people";
System.out.println(text);
forEachDatabase(common(text).build());
// Where nulls sort differs, and so does OFFSET without LIMIT.
b = common("SELECT name FROM people ORDER BY nick DESC OFFSET ");
b.appendInt(20);
System.out.println("SELECT name FROM people ORDER BY nick DESC OFFSET ${20}");
forEachDatabase(b.build());
// PostgreSQL can't guess a lone parameter's type, so it gets a cast.
b = common("SELECT ");
b.appendString("hi");
b.appendSafe(" AS greeting");
System.out.println("SELECT ${\"hi\"} AS greeting");
forEachDatabase(b.build());

// MySQL's || means OR, so it's CONCAT there.
let text = "SELECT id || ':' || name AS tag FROM people";
console.log(text);
forEachDatabase(common(text).build());
// Each database names its types differently, and MySQL's LENGTH counts
// bytes, not characters.
text = "SELECT CAST(score AS INTEGER) AS s, CAST(id AS TEXT) AS t, LENGTH(name) AS n FROM people";
console.log(text);
forEachDatabase(common(text).build());
// Where nulls sort differs, and so does OFFSET without LIMIT.
b = common("SELECT name FROM people ORDER BY nick DESC OFFSET ");
b.appendInt(20);
console.log("SELECT name FROM people ORDER BY nick DESC OFFSET ${20}");
forEachDatabase(b.build());
// PostgreSQL can't guess a lone parameter's type, so it gets a cast.
b = common("SELECT ");
b.appendString("hi");
b.appendSafe(" AS greeting");
console.log('SELECT ${"hi"} AS greeting');
forEachDatabase(b.build());

-- MySQL's || means OR, so it's CONCAT there.
local text = "SELECT id || ':' || name AS tag FROM people"
print(text)
forEachDatabase(common(text):build())
-- Each database names its types differently, and MySQL's LENGTH counts
-- bytes, not characters.
text = "SELECT CAST(score AS INTEGER) AS s, CAST(id AS TEXT) AS t, LENGTH(name) AS n FROM people"
print(text)
forEachDatabase(common(text):build())
-- Where nulls sort differs, and so does OFFSET without LIMIT.
b = common("SELECT name FROM people ORDER BY nick DESC OFFSET ")
b:appendInt(20)
print("SELECT name FROM people ORDER BY nick DESC OFFSET ${20}")
forEachDatabase(b:build())
-- PostgreSQL can't guess a lone parameter's type, so it gets a cast.
b = common("SELECT ")
b:appendString("hi")
b:appendSafe(" AS greeting")
print('SELECT ${"hi"} AS greeting')
forEachDatabase(b:build())

# MySQL's || means OR, so it's CONCAT there.
text = "SELECT id || ':' || name AS tag FROM people"
print(text)
for_each_database(common(text).build())
# Each database names its types differently, and MySQL's LENGTH counts
# bytes, not characters.
text = "SELECT CAST(score AS INTEGER) AS s, CAST(id AS TEXT) AS t, LENGTH(name) AS n FROM people"
print(text)
for_each_database(common(text).build())
# Where nulls sort differs, and so does OFFSET without LIMIT.
b = common("SELECT name FROM people ORDER BY nick DESC OFFSET ")
b.append_int(20)
print("SELECT name FROM people ORDER BY nick DESC OFFSET ${20}")
for_each_database(b.build())
# PostgreSQL can't guess a lone parameter's type, so it gets a cast.
b = common("SELECT ")
b.append_string("hi")
b.append_safe(" AS greeting")
print('SELECT ${"hi"} AS greeting')
for_each_database(b.build())

// MySQL's || means OR, so it's CONCAT there.
let text = "SELECT id || ':' || name AS tag FROM people";
println!("{text}");
for_each_database(&common(text).build()?)?;
// Each database names its types differently, and MySQL's LENGTH counts
// bytes, not characters.
let text = "SELECT CAST(score AS INTEGER) AS s, CAST(id AS TEXT) AS t, LENGTH(name) AS n FROM people";
println!("{text}");
for_each_database(&common(text).build()?)?;
// Where nulls sort differs, and so does OFFSET without LIMIT.
let b = common("SELECT name FROM people ORDER BY nick DESC OFFSET ");
b.append_int(20);
println!("SELECT name FROM people ORDER BY nick DESC OFFSET ${{20}}");
for_each_database(&b.build()?)?;
// PostgreSQL can't guess a lone parameter's type, so it gets a cast.
let b = common("SELECT ");
b.append_string(Some("hi"));
b.append_safe(" AS greeting");
println!(r#"SELECT ${{"hi"}} AS greeting"#);
for_each_database(&b.build()?)?;

// MySQL's || means OR, so it's CONCAT there.
say("SELECT id || ':' || name AS tag FROM people");
forEachDatabase(out, sql"SELECT id || ':' || name AS tag FROM people");
// Each database names its types differently, and MySQL's LENGTH
// counts bytes, not characters.
say("SELECT CAST(score AS INTEGER) AS s, CAST(id AS TEXT) AS t, LENGTH(name) AS n FROM people");
forEachDatabase(out, sql"SELECT CAST(score AS INTEGER) AS s, CAST(id AS TEXT) AS t, LENGTH(name) AS n FROM people");
// Where nulls sort differs, and so does OFFSET without LIMIT.
say("SELECT name FROM people ORDER BY nick DESC OFFSET \${20}");
forEachDatabase(out, sql"SELECT name FROM people ORDER BY nick DESC OFFSET ${20}");
// PostgreSQL can't guess a lone parameter's type, so it gets a cast.
say("SELECT \${\"hi\"} AS greeting");
forEachDatabase(out, sql"SELECT ${"hi"} AS greeting");

auto big = common("SELECT user_id FROM orders WHERE total > ");
big->appendInt(100);
b = common("SELECT name FROM users WHERE id IN (");
b->appendQuery(big->build());
b->appendSafe(") AND active = ");
b->appendString("yes");
q = b->build();
// The inner query's parameters are numbered with the outer query's.
std::cout << "postgres: " << q->render(Dialect::postgres(), PlaceholderStyle::dollarNumbered())->sql << "\n";
std::cout << "mysql: " << q->render(Dialect::mysql(), PlaceholderStyle::percentS())->sql << "\n";
// And it's checked again as part of the whole, with problems at its hole.
auto inner = common("SELECT user_id FROM orders LIMIT ");
inner->appendInt(5);
b = common("SELECT name FROM people WHERE id IN (");
b->appendQuery(inner->build());
b->appendSafe(")");
std::cout << "with LIMIT inside: " << problem(b->get_templateProblem()) << "\n";

var big = Common("SELECT user_id FROM orders WHERE total > ");
big.AppendInt(100);
b = Common("SELECT name FROM users WHERE id IN (");
b.AppendQuery(big.Build());
b.AppendSafe(") AND active = ");
b.AppendString("yes");
q = b.Build();
// The inner query's parameters are numbered with the outer query's.
Console.WriteLine("postgres: " + q.Render(Dialect.Postgres, PlaceholderStyle.DollarNumbered).Sql);
Console.WriteLine("mysql: " + q.Render(Dialect.Mysql, PlaceholderStyle.PercentS).Sql);
// And it's checked again as part of the whole, with problems at its hole.
var inner = Common("SELECT user_id FROM orders LIMIT ");
inner.AppendInt(5);
b = Common("SELECT name FROM people WHERE id IN (");
b.AppendQuery(inner.Build());
b.AppendSafe(")");
Console.WriteLine("with LIMIT inside: " + b.TemplateProblem);

QueryBuilder big = common("SELECT user_id FROM orders WHERE total > ");
big.appendInt(100);
b = common("SELECT name FROM users WHERE id IN (");
b.appendQuery(big.build());
b.appendSafe(") AND active = ");
b.appendString("yes");
q = b.build();
// The inner query's parameters are numbered with the outer query's.
System.out.println("postgres: " + q.render(Dialect.postgres, PlaceholderStyle.dollarNumbered).getSql());
System.out.println("mysql: " + q.render(Dialect.mysql, PlaceholderStyle.percentS).getSql());
// And it's checked again as part of the whole, with problems at its
// hole.
QueryBuilder inner = common("SELECT user_id FROM orders LIMIT ");
inner.appendInt(5);
b = common("SELECT name FROM people WHERE id IN (");
b.appendQuery(inner.build());
b.appendSafe(")");
System.out.println("with LIMIT inside: " + b.getTemplateProblem());

const big = common("SELECT user_id FROM orders WHERE total > ");
big.appendInt(100);
b = common("SELECT name FROM users WHERE id IN (");
b.appendQuery(big.build());
b.appendSafe(") AND active = ");
b.appendString("yes");
q = b.build();
// The inner query's parameters are numbered with the outer query's.
console.log("postgres: " + q.render(Dialect.postgres, PlaceholderStyle.dollarNumbered).sql);
console.log("mysql: " + q.render(Dialect.mysql, PlaceholderStyle.percentS).sql);
// And it's checked again as part of the whole, with problems at its hole.
const inner = common("SELECT user_id FROM orders LIMIT ");
inner.appendInt(5);
b = common("SELECT name FROM people WHERE id IN (");
b.appendQuery(inner.build());
b.appendSafe(")");
console.log("with LIMIT inside: " + b.templateProblem);

local big = common("SELECT user_id FROM orders WHERE total > ")
big:appendInt(100)
b = common("SELECT name FROM users WHERE id IN (")
b:appendQuery(big:build())
b:appendSafe(") AND active = ")
b:appendString("yes")
q = b:build()
-- The inner query's parameters are numbered with the outer query's.
print("postgres: " .. q:render(Dialect.postgres, PlaceholderStyle.dollarNumbered).sql)
print("mysql: " .. q:render(Dialect.mysql, PlaceholderStyle.percentS).sql)
-- And it's checked again as part of the whole, with problems at its hole.
local inner = common("SELECT user_id FROM orders LIMIT ")
inner:appendInt(5)
b = common("SELECT name FROM people WHERE id IN (")
b:appendQuery(inner:build())
b:appendSafe(")")
print("with LIMIT inside: " .. b.templateProblem)

big = common("SELECT user_id FROM orders WHERE total > ")
big.append_int(100)
b = common("SELECT name FROM users WHERE id IN (")
b.append_query(big.build())
b.append_safe(") AND active = ")
b.append_string("yes")
q = b.build()
# The inner query's parameters are numbered with the outer query's.
print("postgres: " + q.render(Dialect.postgres, PlaceholderStyle.dollar_numbered).sql)
print("mysql: " + q.render(Dialect.mysql, PlaceholderStyle.percent_s).sql)
# And it's checked again as part of the whole, with problems at its hole.
inner = common("SELECT user_id FROM orders LIMIT ")
inner.append_int(5)
b = common("SELECT name FROM people WHERE id IN (")
b.append_query(inner.build())
b.append_safe(")")
print("with LIMIT inside: " + b.template_problem)

let big = common("SELECT user_id FROM orders WHERE total > ");
big.append_int(100);
let b = common("SELECT name FROM users WHERE id IN (");
b.append_query(big.build()?);
b.append_safe(") AND active = ");
b.append_string(Some("yes"));
let q = b.build()?;
// The inner query's parameters are numbered with the outer query's.
println!("postgres: {}", q.render(Dialect::postgres(), PlaceholderStyle::dollar_numbered())?.sql());
println!("mysql: {}", q.render(Dialect::mysql(), PlaceholderStyle::percent_s())?.sql());
// And it's checked again as part of the whole, with problems at its hole.
let inner = common("SELECT user_id FROM orders LIMIT ");
inner.append_int(5);
let b = common("SELECT name FROM people WHERE id IN (");
b.append_query(inner.build()?);
b.append_safe(")");
println!("with LIMIT inside: {}", problem(b.template_problem()));

let big = sql"SELECT user_id FROM orders WHERE total > ${100}";
let both = sql"SELECT name FROM users WHERE id IN (${big}) AND active = ${"yes"}";
// The inner query's parameters are numbered with the outer query's.
say("postgres: ${sqlFor(both, Dialect.postgres, PlaceholderStyle.dollarNumbered)}");
say("mysql: ${sqlFor(both, Dialect.mysql, PlaceholderStyle.percentS)}");
// And it's checked again as part of the whole, with problems at its
// hole.
let inner = sql"SELECT user_id FROM orders LIMIT ${5}";
let outer = common("SELECT name FROM people WHERE id IN (");
outer.appendQuery(inner);
outer.appendSafe(")");
say("with LIMIT inside: ${outer.templateProblem ?? ""}");

// Filters only when given (an empty word, or a negative total, means
// none), a sort column from the caller, and a page. Every variant is
// still checked when it's built.
std::string search(std::string const &word, int minTotal, std::shared_ptr<Identifier> const &sort, bool descending,
                   int page) {
  auto b = common("SELECT id, title FROM orders");
  std::string joiner = " WHERE ";
  if (!word.empty()) {
    b->appendSafe(joiner + "title LIKE ");
    b->appendString(word);
    b->appendSafe(" || '%' ESCAPE '!'");
    joiner = " AND ";
  }
  if (minTotal >= 0) {
    b->appendSafe(joiner + "total >= ");
    b->appendInt(minTotal);
  }
  b->appendSafe(" ORDER BY ");
  b->appendIdentifier(sort);
  b->appendSafe(descending ? " DESC LIMIT " : " LIMIT ");
  b->appendInt(20);
  b->appendSafe(" OFFSET ");
  b->appendInt(page * 20);
  return b->build()->render(Dialect::postgres(), PlaceholderStyle::dollarNumbered())->sql;
}

auto created = Identifier::named("created");
std::cout << search("", -1, created, false, 0) << "\n";
std::cout << search("Launch", -1, created, false, 0) << "\n";
std::cout << search("", 100, Identifier::named("total"), true, 2) << "\n";

// A query for fewer databases relaxes the checks for the others: MySQL
// reads \ in a string as an escape, so it's refused only while MySQL is
// a target. The query renders only for its targets.
b = QueryBuilder::make(Dialect::common());
b->set_targets(std::make_shared<std::vector<std::shared_ptr<Dialect>>>(
    std::vector<std::shared_ptr<Dialect>>{Dialect::postgres(), Dialect::sqlite()}));
b->appendSafe("SELECT 'C:\\temp' AS p");
q = b->build();
std::cout << "for postgres and sqlite: " << q->render(Dialect::postgres(), PlaceholderStyle::dollarNumbered())->sql
          << "\n";
std::cout << "for mysql: " << problem(q->renderProblem(Dialect::mysql(), PlaceholderStyle::percentS())) << "\n";

// Filters only when given, a sort column from the caller, and a page.
// Every variant is still checked when it's built.
string Search(string? word = null, int? minTotal = null, Identifier? sort = null, bool descending = false, int page = 0)
{
    var b = Common("SELECT id, title FROM orders");
    var joiner = " WHERE ";
    if (word != null)
    {
        b.AppendSafe(joiner + "title LIKE ");
        b.AppendString(word);
        b.AppendSafe(" || '%' ESCAPE '!'");
        joiner = " AND ";
    }
    if (minTotal != null)
    {
        b.AppendSafe(joiner + "total >= ");
        b.AppendInt(minTotal.Value);
    }
    b.AppendSafe(" ORDER BY ");
    b.AppendIdentifier(sort ?? Identifier.Named("created"));
    b.AppendSafe(descending ? " DESC LIMIT " : " LIMIT ");
    b.AppendInt(20);
    b.AppendSafe(" OFFSET ");
    b.AppendInt(page * 20);
    return b.Build().Render(Dialect.Postgres, PlaceholderStyle.DollarNumbered).Sql;
}

Console.WriteLine(Search());
Console.WriteLine(Search(word: "Launch"));
Console.WriteLine(Search(minTotal: 100, sort: Identifier.Named("total"), descending: true, page: 2));

// A query for fewer databases relaxes the checks for the others: MySQL
// reads \ in a string as an escape, so it's refused only while MySQL is
// a target. The query renders only for its targets.
b = new QueryBuilder(Dialect.Common);
b.Targets = new List<Dialect> { Dialect.Postgres, Dialect.Sqlite };
b.AppendSafe(@"SELECT 'C:\temp' AS p");
q = b.Build();
Console.WriteLine("for postgres and sqlite: " + q.Render(Dialect.Postgres, PlaceholderStyle.DollarNumbered).Sql);
Console.WriteLine("for mysql: " + q.RenderProblem(Dialect.Mysql, PlaceholderStyle.PercentS));

// Filters only when given, a sort column from the caller, and a page.
// Every variant is still checked when it's built.
static String search(String word, Integer minTotal, Identifier sort, boolean descending, int page) {
    QueryBuilder b = common("SELECT id, title FROM orders");
    String joiner = " WHERE ";
    if (word != null) {
        b.appendSafe(joiner + "title LIKE ");
        b.appendString(word);
        b.appendSafe(" || '%' ESCAPE '!'");
        joiner = " AND ";
    }
    if (minTotal != null) {
        b.appendSafe(joiner + "total >= ");
        b.appendInt(minTotal);
    }
    b.appendSafe(" ORDER BY ");
    b.appendIdentifier(sort);
    b.appendSafe(descending ? " DESC LIMIT " : " LIMIT ");
    b.appendInt(20);
    b.appendSafe(" OFFSET ");
    b.appendInt(page * 20);
    return b.build().render(Dialect.postgres, PlaceholderStyle.dollarNumbered).getSql();
}

Identifier created = Identifier.named("created");
System.out.println(search(null, null, created, false, 0));
System.out.println(search("Launch", null, created, false, 0));
System.out.println(search(null, 100, Identifier.named("total"), true, 2));

// A query for fewer databases relaxes the checks for the others:
// MySQL reads \ in a string as an escape, so it's refused only while
// MySQL is a target. The query renders only for its targets.
b = new QueryBuilder(Dialect.common);
b.setTargets(List.of(Dialect.postgres, Dialect.sqlite));
b.appendSafe("SELECT 'C:\\temp' AS p");
q = b.build();
System.out.println("for postgres and sqlite: " + q.render(Dialect.postgres, PlaceholderStyle.dollarNumbered).getSql());
System.out.println("for mysql: " + q.renderProblem(Dialect.mysql, PlaceholderStyle.percentS));

// Filters only when given, a sort column from the caller, and a page.
// Every variant is still checked when it's built.
function search({ word = null, minTotal = null, sort = Identifier.named("created"), descending = false, page = 0 } = {}) {
  const b = common("SELECT id, title FROM orders");
  let joiner = " WHERE ";
  if (word !== null) {
    b.appendSafe(joiner + "title LIKE ");
    b.appendString(word);
    b.appendSafe(" || '%' ESCAPE '!'");
    joiner = " AND ";
  }
  if (minTotal !== null) {
    b.appendSafe(joiner + "total >= ");
    b.appendInt(minTotal);
  }
  b.appendSafe(" ORDER BY ");
  b.appendIdentifier(sort);
  b.appendSafe(descending ? " DESC LIMIT " : " LIMIT ");
  b.appendInt(20);
  b.appendSafe(" OFFSET ");
  b.appendInt(page * 20);
  return b.build().render(Dialect.postgres, PlaceholderStyle.dollarNumbered).sql;
}

console.log(search());
console.log(search({ word: "Launch" }));
console.log(search({ minTotal: 100, sort: Identifier.named("total"), descending: true, page: 2 }));

// A query for fewer databases relaxes the checks for the others: MySQL
// reads \ in a string as an escape, so it's refused only while MySQL is
// a target. The query renders only for its targets.
b = new QueryBuilder(Dialect.common);
b.targets = [Dialect.postgres, Dialect.sqlite];
b.appendSafe("SELECT 'C:\\temp' AS p");
q = b.build();
console.log("for postgres and sqlite: " + q.render(Dialect.postgres, PlaceholderStyle.dollarNumbered).sql);
console.log("for mysql: " + q.renderProblem(Dialect.mysql, PlaceholderStyle.percentS));

-- Filters only when given, a sort column from the caller, and a page.
-- Every variant is still checked when it's built.
local function search(options)
  local b = common("SELECT id, title FROM orders")
  local joiner = " WHERE "
  if options.word ~= nil then
    b:appendSafe(joiner .. "title LIKE ")
    b:appendString(options.word)
    b:appendSafe(" || '%' ESCAPE '!'")
    joiner = " AND "
  end
  if options.minTotal ~= nil then
    b:appendSafe(joiner .. "total >= ")
    b:appendInt(options.minTotal)
  end
  b:appendSafe(" ORDER BY ")
  b:appendIdentifier(options.sort or Identifier.named("created"))
  b:appendSafe(options.descending and " DESC LIMIT " or " LIMIT ")
  b:appendInt(20)
  b:appendSafe(" OFFSET ")
  b:appendInt((options.page or 0) * 20)
  return b:build():render(Dialect.postgres, PlaceholderStyle.dollarNumbered).sql
end

print(search({}))
print(search({ word = "Launch" }))
print(search({ minTotal = 100, sort = Identifier.named("total"), descending = true, page = 2 }))

-- A query for fewer databases relaxes the checks for the others: MySQL
-- reads \ in a string as an escape, so it's refused only while MySQL is
-- a target. The query renders only for its targets.
b = QueryBuilder(Dialect.common)
b.targets = { Dialect.postgres, Dialect.sqlite }
b:appendSafe([[SELECT 'C:\temp' AS p]])
q = b:build()
print("for postgres and sqlite: " .. q:render(Dialect.postgres, PlaceholderStyle.dollarNumbered).sql)
print("for mysql: " .. q:renderProblem(Dialect.mysql, PlaceholderStyle.percentS))

# Filters only when given, a sort column from the caller, and a page.
# Every variant is still checked when it's built.
def search(word=None, min_total=None, sort=Identifier.named("created"), descending=False, page=0):
    b = common("SELECT id, title FROM orders")
    joiner = " WHERE "
    if word is not None:
        b.append_safe(joiner + "title LIKE ")
        b.append_string(word)
        b.append_safe(" || '%' ESCAPE '!'")
        joiner = " AND "
    if min_total is not None:
        b.append_safe(joiner + "total >= ")
        b.append_int(min_total)
    b.append_safe(" ORDER BY ")
    b.append_identifier(sort)
    b.append_safe(" DESC LIMIT " if descending else " LIMIT ")
    b.append_int(20)
    b.append_safe(" OFFSET ")
    b.append_int(page * 20)
    return b.build().render(Dialect.postgres, PlaceholderStyle.dollar_numbered).sql


print(search())
print(search(word="Launch"))
print(search(min_total=100, sort=Identifier.named("total"), descending=True, page=2))

# A query for fewer databases relaxes the checks for the others: MySQL
# reads \ in a string as an escape, so it's refused only while MySQL is
# a target. The query renders only for its targets.
b = QueryBuilder(Dialect.common)
b.targets = [Dialect.postgres, Dialect.sqlite]
b.append_safe("SELECT 'C:\\temp' AS p")
q = b.build()
print("for postgres and sqlite: " + q.render(Dialect.postgres, PlaceholderStyle.dollar_numbered).sql)
print("for mysql: " + q.render_problem(Dialect.mysql, PlaceholderStyle.percent_s))

// Filters only when given, a sort column from the caller, and a page.
// Every variant is still checked when it's built.
fn search(
    word: Option<&str>,
    min_total: Option<i32>,
    sort: Identifier,
    descending: bool,
    page: i32,
) -> temper_core::Result<String> {
    let b = common("SELECT id, title FROM orders");
    let mut joiner = " WHERE ";
    if let Some(word) = word {
        b.append_safe(format!("{joiner}title LIKE "));
        b.append_string(Some(word));
        b.append_safe(" || '%' ESCAPE '!'");
        joiner = " AND ";
    }
    if let Some(min_total) = min_total {
        b.append_safe(format!("{joiner}total >= "));
        b.append_int(min_total);
    }
    b.append_safe(" ORDER BY ");
    b.append_identifier(sort);
    b.append_safe(if descending { " DESC LIMIT " } else { " LIMIT " });
    b.append_int(20);
    b.append_safe(" OFFSET ");
    b.append_int(page * 20);
    Ok(b.build()?.render(Dialect::postgres(), PlaceholderStyle::dollar_numbered())?.sql().to_string())
}

let created = Identifier::named("created")?;
println!("{}", search(None, None, created.clone(), false, 0)?);
println!("{}", search(Some("Launch"), None, created, false, 0)?);
println!("{}", search(None, Some(100), Identifier::named("total")?, true, 2)?);

// A query for fewer databases relaxes the checks for the others: MySQL
// reads \ in a string as an escape, so it's refused only while MySQL is
// a target. The query renders only for its targets.
let b = QueryBuilder::new(Dialect::common());
b.set_targets(vec![Dialect::postgres(), Dialect::sqlite()]);
b.append_safe(r"SELECT 'C:\temp' AS p");
let q = b.build()?;
println!("for postgres and sqlite: {}", q.render(Dialect::postgres(), PlaceholderStyle::dollar_numbered())?.sql());
println!("for mysql: {}", problem(q.render_problem(Dialect::mysql(), PlaceholderStyle::percent_s())));

// Filters only when given, a sort column from the caller, and a page.
// Every variant is still checked when it's built.
let search(
  word: String?,
  minTotal: Int?,
  sort: Identifier,
  descending: Boolean,
  page: Int,
): String {
  let b = common("SELECT id, title FROM orders");
  var joiner = " WHERE ";
  if (word != null) {
    b.appendSafe("${joiner}title LIKE ");
    b.appendString(word);
    b.appendSafe(" || '%' ESCAPE '!'");
    joiner = " AND ";
  }
  if (minTotal != null) {
    b.appendSafe("${joiner}total >= ");
    b.appendInt(minTotal);
  }
  b.appendSafe(" ORDER BY ");
  b.appendIdentifier(sort);
  b.appendSafe(if (descending) { " DESC LIMIT " } else { " LIMIT " });
  b.appendInt(20);
  b.appendSafe(" OFFSET ");
  b.appendInt(page * 20);
  sqlFor(b.build() orelse panic(), Dialect.postgres, PlaceholderStyle.dollarNumbered)
}

let created = Identifier.named("created") orelse panic();
say(search(null, null, created, false, 0));
say(search("Launch", null, created, false, 0));
say(search(null, 100, Identifier.named("total") orelse panic(), true, 2));

// A query for fewer databases relaxes the checks for the others:
// MySQL reads \ in a string as an escape, so it's refused only while
// MySQL is a target. The query renders only for its targets.
let few = new QueryBuilder(Dialect.common);
few.targets = [Dialect.postgres, Dialect.sqlite];
few.appendSafe("SELECT 'C:\\temp' AS p");
let path = few.build() orelse panic();
say("for postgres and sqlite: ${sqlFor(path, Dialect.postgres, PlaceholderStyle.dollarNumbered)}");
say("for mysql: ${path.renderProblem(Dialect.mysql, PlaceholderStyle.percentS) ?? ""}");

// The placeholder style depends on the driver, not the database, so it's
// chosen when rendering. percentS doubles %, for drivers that fill in
// values with Python's % formatting.
b = common("SELECT name FROM users WHERE name LIKE 'a%' ESCAPE '!' AND id = ");
b->appendInt(1);
q = b->build();
std::vector<std::shared_ptr<PlaceholderStyle>> styles = {
    PlaceholderStyle::question(),
    PlaceholderStyle::dollarNumbered(),
    PlaceholderStyle::percentS(),
    PlaceholderStyle::atNamed(),
    PlaceholderStyle::colonNamed(),
};
for (auto const &style : styles) {
  std::cout << style->toString() << ": " << q->render(Dialect::sqlite(), style)->sql << "\n";
}
// A named style refuses text holding its marker, which a driver could
// misread as a name.
b = common("SELECT 'a:b' AS s FROM users WHERE id = ");
b->appendInt(1);
auto found = b->build()->renderProblem(Dialect::sqlite(), PlaceholderStyle::colonNamed());
std::cout << "colonNamed with 'a:b': " << problem(found) << "\n";

// The placeholder style depends on the driver, not the database, so it's
// chosen when rendering. PercentS doubles %, for drivers that fill in
// values with Python's % formatting.
b = Common("SELECT name FROM users WHERE name LIKE 'a%' ESCAPE '!' AND id = ");
b.AppendInt(1);
q = b.Build();
var styles = new[]
{
    PlaceholderStyle.Question,
    PlaceholderStyle.DollarNumbered,
    PlaceholderStyle.PercentS,
    PlaceholderStyle.AtNamed,
    PlaceholderStyle.ColonNamed,
};
foreach (var style in styles)
{
    Console.WriteLine($"{style}: {q.Render(Dialect.Sqlite, style).Sql}");
}
// A named style refuses text holding its marker, which a driver could
// misread as a name.
b = Common("SELECT 'a:b' AS s FROM users WHERE id = ");
b.AppendInt(1);
Console.WriteLine("colonNamed with 'a:b': " + b.Build().RenderProblem(Dialect.Sqlite, PlaceholderStyle.ColonNamed));

// The placeholder style depends on the driver, not the database, so
// it's chosen when rendering. percentS doubles %, for drivers that
// fill in values with Python's % formatting.
b = common("SELECT name FROM users WHERE name LIKE 'a%' ESCAPE '!' AND id = ");
b.appendInt(1);
q = b.build();
List<PlaceholderStyle> styles = List.of(
    PlaceholderStyle.question,
    PlaceholderStyle.dollarNumbered,
    PlaceholderStyle.percentS,
    PlaceholderStyle.atNamed,
    PlaceholderStyle.colonNamed);
for (PlaceholderStyle style : styles) {
    System.out.println(style + ": " + q.render(Dialect.sqlite, style).getSql());
}
// A named style refuses text holding its marker, which a driver
// could misread as a name.
b = common("SELECT 'a:b' AS s FROM users WHERE id = ");
b.appendInt(1);
System.out.println("colonNamed with 'a:b': " + b.build().renderProblem(Dialect.sqlite, PlaceholderStyle.colonNamed));

// The placeholder style depends on the driver, not the database, so it's
// chosen when rendering. percentS doubles %, for drivers that fill in
// values with Python's % formatting.
b = common("SELECT name FROM users WHERE name LIKE 'a%' ESCAPE '!' AND id = ");
b.appendInt(1);
q = b.build();
const styles = [
  PlaceholderStyle.question,
  PlaceholderStyle.dollarNumbered,
  PlaceholderStyle.percentS,
  PlaceholderStyle.atNamed,
  PlaceholderStyle.colonNamed,
];
for (const style of styles) {
  console.log(`${style}: ${q.render(Dialect.sqlite, style).sql}`);
}
// A named style refuses text holding its marker, which a driver could
// misread as a name.
b = common("SELECT 'a:b' AS s FROM users WHERE id = ");
b.appendInt(1);
console.log("colonNamed with 'a:b': " + b.build().renderProblem(Dialect.sqlite, PlaceholderStyle.colonNamed));

-- The placeholder style depends on the driver, not the database, so it's
-- chosen when rendering. percentS doubles %, for drivers that fill in
-- values with Python's % formatting.
b = common("SELECT name FROM users WHERE name LIKE 'a%' ESCAPE '!' AND id = ")
b:appendInt(1)
q = b:build()
local styles = {
  PlaceholderStyle.question,
  PlaceholderStyle.dollarNumbered,
  PlaceholderStyle.percentS,
  PlaceholderStyle.atNamed,
  PlaceholderStyle.colonNamed,
}
for _, style in ipairs(styles) do
  print(style:toString() .. ": " .. q:render(Dialect.sqlite, style).sql)
end
-- A named style refuses text holding its marker, which a driver could
-- misread as a name.
b = common("SELECT 'a:b' AS s FROM users WHERE id = ")
b:appendInt(1)
print("colonNamed with 'a:b': " .. b:build():renderProblem(Dialect.sqlite, PlaceholderStyle.colonNamed))

# The placeholder style depends on the driver, not the database, so it's
# chosen when rendering. percent_s doubles %, for drivers that fill in
# values with Python's % formatting.
b = common("SELECT name FROM users WHERE name LIKE 'a%' ESCAPE '!' AND id = ")
b.append_int(1)
q = b.build()
styles = [
    PlaceholderStyle.question,
    PlaceholderStyle.dollar_numbered,
    PlaceholderStyle.percent_s,
    PlaceholderStyle.at_named,
    PlaceholderStyle.colon_named,
]
for style in styles:
    print(f"{style}: {q.render(Dialect.sqlite, style).sql}")
# A named style refuses text holding its marker, which a driver could
# misread as a name.
b = common("SELECT 'a:b' AS s FROM users WHERE id = ")
b.append_int(1)
print("colonNamed with 'a:b': " + b.build().render_problem(Dialect.sqlite, PlaceholderStyle.colon_named))

// The placeholder style depends on the driver, not the database, so it's
// chosen when rendering. percent_s doubles %, for drivers that fill in
// values with Python's % formatting.
let b = common("SELECT name FROM users WHERE name LIKE 'a%' ESCAPE '!' AND id = ");
b.append_int(1);
let q = b.build()?;
let styles = [
    PlaceholderStyle::question(),
    PlaceholderStyle::dollar_numbered(),
    PlaceholderStyle::percent_s(),
    PlaceholderStyle::at_named(),
    PlaceholderStyle::colon_named(),
];
for style in styles {
    println!("{}: {}", style.to_string(), q.render(Dialect::sqlite(), style.clone())?.sql());
}
// A named style refuses text holding its marker, which a driver could
// misread as a name.
let b = common("SELECT 'a:b' AS s FROM users WHERE id = ");
b.append_int(1);
let found = b.build()?.render_problem(Dialect::sqlite(), PlaceholderStyle::colon_named());
println!("colonNamed with 'a:b': {}", problem(found));

// The placeholder style depends on the driver, not the database, so
// it's chosen when rendering. percentS doubles %, for drivers that
// fill in values with Python's % formatting.
let like = sql"SELECT name FROM users WHERE name LIKE 'a%' ESCAPE '!' AND id = ${1}";
let styles = [
  PlaceholderStyle.question,
  PlaceholderStyle.dollarNumbered,
  PlaceholderStyle.percentS,
  PlaceholderStyle.atNamed,
  PlaceholderStyle.colonNamed,
];
for (let style of styles) {
  say("${style.toString()}: ${sqlFor(like, Dialect.sqlite, style)}");
}
// A named style refuses text holding its marker, which a driver
// could misread as a name.
let colon = sql"SELECT 'a:b' AS s FROM users WHERE id = ${1}";
say("colonNamed with 'a:b': ${colon.renderProblem(Dialect.sqlite, PlaceholderStyle.colonNamed) ?? ""}");

// For SQL the common dialect doesn't cover yet, a database's own
// template, checked with its rules. In Temper, postgres"…" builds it.
b = QueryBuilder::make(Dialect::postgres());
b->appendSafe("INSERT INTO tags (name) VALUES (");
b->appendString("sql");
b->appendSafe(") ON CONFLICT (name) DO NOTHING RETURNING id");
q = b->build();
std::cout << "upsert: " << q->render(Dialect::postgres(), PlaceholderStyle::dollarNumbered())->sql << "\n";
// It renders only for its own database.
std::cout << "for mysql: " << problem(q->renderProblem(Dialect::mysql(), PlaceholderStyle::percentS())) << "\n";
// A common query nests in it, rendered for that database.
b = QueryBuilder::make(Dialect::postgres());
b->appendSafe("SELECT jsonb_agg(x) FROM (");
b->appendQuery(big->build());
b->appendSafe(") AS s(x)");
std::cout << "nested: " << b->build()->render(Dialect::postgres(), PlaceholderStyle::dollarNumbered())->sql << "\n";
// # starts a comment in MySQL only, so a value after it would vanish.
b = QueryBuilder::make(Dialect::mysql());
b->appendSafe("SELECT * FROM t WHERE a = 1 # ");
b->appendString("x");
std::cout << "mysql, after #: " << problem(b->get_templateProblem()) << "\n";

// For SQL the common dialect doesn't cover yet, a database's own
// template, checked with its rules. In Temper, postgres"…" builds it.
b = new QueryBuilder(Dialect.Postgres);
b.AppendSafe("INSERT INTO tags (name) VALUES (");
b.AppendString("sql");
b.AppendSafe(") ON CONFLICT (name) DO NOTHING RETURNING id");
q = b.Build();
Console.WriteLine("upsert: " + q.Render(Dialect.Postgres, PlaceholderStyle.DollarNumbered).Sql);
// It renders only for its own database.
Console.WriteLine("for mysql: " + q.RenderProblem(Dialect.Mysql, PlaceholderStyle.PercentS));
// A common query nests in it, rendered for that database.
b = new QueryBuilder(Dialect.Postgres);
b.AppendSafe("SELECT jsonb_agg(x) FROM (");
b.AppendQuery(big.Build());
b.AppendSafe(") AS s(x)");
Console.WriteLine("nested: " + b.Build().Render(Dialect.Postgres, PlaceholderStyle.DollarNumbered).Sql);
// # starts a comment in MySQL only, so a value after it would vanish.
b = new QueryBuilder(Dialect.Mysql);
b.AppendSafe("SELECT * FROM t WHERE a = 1 # ");
b.AppendString("x");
Console.WriteLine("mysql, after #: " + b.TemplateProblem);

// For SQL the common dialect doesn't cover yet, a database's own
// template, checked with its rules. In Temper, postgres"…" builds it.
b = new QueryBuilder(Dialect.postgres);
b.appendSafe("INSERT INTO tags (name) VALUES (");
b.appendString("sql");
b.appendSafe(") ON CONFLICT (name) DO NOTHING RETURNING id");
q = b.build();
System.out.println("upsert: " + q.render(Dialect.postgres, PlaceholderStyle.dollarNumbered).getSql());
// It renders only for its own database.
System.out.println("for mysql: " + q.renderProblem(Dialect.mysql, PlaceholderStyle.percentS));
// A common query nests in it, rendered for that database.
b = new QueryBuilder(Dialect.postgres);
b.appendSafe("SELECT jsonb_agg(x) FROM (");
b.appendQuery(big.build());
b.appendSafe(") AS s(x)");
System.out.println("nested: " + b.build().render(Dialect.postgres, PlaceholderStyle.dollarNumbered).getSql());
// # starts a comment in MySQL only, so a value after it would vanish.
b = new QueryBuilder(Dialect.mysql);
b.appendSafe("SELECT * FROM t WHERE a = 1 # ");
b.appendString("x");
System.out.println("mysql, after #: " + b.getTemplateProblem());

// For SQL the common dialect doesn't cover yet, a database's own
// template, checked with its rules. In Temper, postgres"…" builds it.
b = new QueryBuilder(Dialect.postgres);
b.appendSafe("INSERT INTO tags (name) VALUES (");
b.appendString("sql");
b.appendSafe(") ON CONFLICT (name) DO NOTHING RETURNING id");
q = b.build();
console.log("upsert: " + q.render(Dialect.postgres, PlaceholderStyle.dollarNumbered).sql);
// It renders only for its own database.
console.log("for mysql: " + q.renderProblem(Dialect.mysql, PlaceholderStyle.percentS));
// A common query nests in it, rendered for that database.
b = new QueryBuilder(Dialect.postgres);
b.appendSafe("SELECT jsonb_agg(x) FROM (");
b.appendQuery(big.build());
b.appendSafe(") AS s(x)");
console.log("nested: " + b.build().render(Dialect.postgres, PlaceholderStyle.dollarNumbered).sql);
// # starts a comment in MySQL only, so a value after it would vanish.
b = new QueryBuilder(Dialect.mysql);
b.appendSafe("SELECT * FROM t WHERE a = 1 # ");
b.appendString("x");
console.log("mysql, after #: " + b.templateProblem);

-- For SQL the common dialect doesn't cover yet, a database's own
-- template, checked with its rules. In Temper, postgres"…" builds it.
b = QueryBuilder(Dialect.postgres)
b:appendSafe("INSERT INTO tags (name) VALUES (")
b:appendString("sql")
b:appendSafe(") ON CONFLICT (name) DO NOTHING RETURNING id")
q = b:build()
print("upsert: " .. q:render(Dialect.postgres, PlaceholderStyle.dollarNumbered).sql)
-- It renders only for its own database.
print("for mysql: " .. q:renderProblem(Dialect.mysql, PlaceholderStyle.percentS))
-- A common query nests in it, rendered for that database.
b = QueryBuilder(Dialect.postgres)
b:appendSafe("SELECT jsonb_agg(x) FROM (")
b:appendQuery(big:build())
b:appendSafe(") AS s(x)")
print("nested: " .. b:build():render(Dialect.postgres, PlaceholderStyle.dollarNumbered).sql)
-- # starts a comment in MySQL only, so a value after it would vanish.
b = QueryBuilder(Dialect.mysql)
b:appendSafe("SELECT * FROM t WHERE a = 1 # ")
b:appendString("x")
print("mysql, after #: " .. b.templateProblem)

# For SQL the common dialect doesn't cover yet, a database's own
# template, checked with its rules. In Temper, postgres"…" builds it.
b = QueryBuilder(Dialect.postgres)
b.append_safe("INSERT INTO tags (name) VALUES (")
b.append_string("sql")
b.append_safe(") ON CONFLICT (name) DO NOTHING RETURNING id")
q = b.build()
print("upsert: " + q.render(Dialect.postgres, PlaceholderStyle.dollar_numbered).sql)
# It renders only for its own database.
print("for mysql: " + q.render_problem(Dialect.mysql, PlaceholderStyle.percent_s))
# A common query nests in it, rendered for that database.
b = QueryBuilder(Dialect.postgres)
b.append_safe("SELECT jsonb_agg(x) FROM (")
b.append_query(big.build())
b.append_safe(") AS s(x)")
print("nested: " + b.build().render(Dialect.postgres, PlaceholderStyle.dollar_numbered).sql)
# # starts a comment in MySQL only, so a value after it would vanish.
b = QueryBuilder(Dialect.mysql)
b.append_safe("SELECT * FROM t WHERE a = 1 # ")
b.append_string("x")
print("mysql, after #: " + b.template_problem)

// For SQL the common dialect doesn't cover yet, a database's own
// template, checked with its rules. In Temper, postgres"…" builds it.
let b = QueryBuilder::new(Dialect::postgres());
b.append_safe("INSERT INTO tags (name) VALUES (");
b.append_string(Some("sql"));
b.append_safe(") ON CONFLICT (name) DO NOTHING RETURNING id");
let q = b.build()?;
println!("upsert: {}", q.render(Dialect::postgres(), PlaceholderStyle::dollar_numbered())?.sql());
// It renders only for its own database.
println!("for mysql: {}", problem(q.render_problem(Dialect::mysql(), PlaceholderStyle::percent_s())));
// A common query nests in it, rendered for that database.
let b = QueryBuilder::new(Dialect::postgres());
b.append_safe("SELECT jsonb_agg(x) FROM (");
b.append_query(big.build()?);
b.append_safe(") AS s(x)");
println!("nested: {}", b.build()?.render(Dialect::postgres(), PlaceholderStyle::dollar_numbered())?.sql());
// # starts a comment in MySQL only, so a value after it would vanish.
let b = QueryBuilder::new(Dialect::mysql());
b.append_safe("SELECT * FROM t WHERE a = 1 # ");
b.append_string(Some("x"));
println!("mysql, after #: {}", problem(b.template_problem()));

// For SQL the common dialect doesn't cover yet, a database's own
// template, checked with its rules.
let upsert = postgres"INSERT INTO tags (name) VALUES (${"sql"}) ON CONFLICT (name) DO NOTHING RETURNING id";
say("upsert: ${sqlFor(upsert, Dialect.postgres, PlaceholderStyle.dollarNumbered)}");
// It renders only for its own database.
say("for mysql: ${upsert.renderProblem(Dialect.mysql, PlaceholderStyle.percentS) ?? ""}");
// A common query nests in it, rendered for that database.
let agg = postgres"SELECT jsonb_agg(x) FROM (${big}) AS s(x)";
say("nested: ${sqlFor(agg, Dialect.postgres, PlaceholderStyle.dollarNumbered)}");
// # starts a comment in MySQL only, so a value after it would vanish.
// The mysql tag would panic, so this builds it in code.
let hash = new QueryBuilder(Dialect.mysql);
hash.appendSafe("SELECT * FROM t WHERE a = 1 # ");
hash.appendString("x");
say("mysql, after #: ${hash.templateProblem ?? ""}");

// For tools that write SQL text themselves, such as DDL defaults. Each
// throws TemperBubble rather than change what it can't write, and a
// ...Problem method says why.
std::cout << "mysql name: " << Dialect::mysql()->quoteName("my `table`") << "\n";
std::cout << "mysql string: " << Dialect::mysql()->quoteString("it's \\ fine") << "\n";
std::cout << "postgres string: " << Dialect::postgres()->quoteString("it's \\ fine") << "\n";
std::string longName(64, 'n');
std::cout << "postgres, 64-byte name: " << problem(Dialect::postgres()->nameProblem(longName)) << "\n";
std::string withNul("a\0b", 3);
try {
  Dialect::postgres()->quoteString(withNul);
  std::cout << "postgres, NUL in a string: quoted\n";
} catch (TemperBubble const &) {
  std::cout << "postgres, NUL in a string: refused, " << problem(Dialect::postgres()->stringProblem(withNul)) << "\n";
}
try {
  Identifier::named(longName);
  std::cout << "Identifier, 64 bytes: named\n";
} catch (TemperBubble const &) {
  std::cout << "Identifier, 64 bytes: refused, " << problem(identifierProblem(longName)) << "\n";
}

// For tools that write SQL text themselves, such as DDL defaults. Each
// throws an Exception rather than change what it can't write, and a
// ...Problem method says why.
Console.WriteLine("mysql name: " + Dialect.Mysql.QuoteName("my `table`"));
Console.WriteLine("mysql string: " + Dialect.Mysql.QuoteString(@"it's \ fine"));
Console.WriteLine("postgres string: " + Dialect.Postgres.QuoteString(@"it's \ fine"));
var longName = new string('n', 64);
Console.WriteLine("postgres, 64-byte name: " + Dialect.Postgres.NameProblem(longName));
try
{
    Dialect.Postgres.QuoteString("a\0b");
    Console.WriteLine("postgres, NUL in a string: quoted");
}
catch (Exception)
{
    Console.WriteLine("postgres, NUL in a string: refused, " + Dialect.Postgres.StringProblem("a\0b"));
}
try
{
    Identifier.Named(longName);
    Console.WriteLine("Identifier, 64 bytes: named");
}
catch (Exception)
{
    Console.WriteLine("Identifier, 64 bytes: refused, " + IdentifierProblem(longName));
}

// For tools that write SQL text themselves, such as DDL defaults.
// Each throws RuntimeException rather than change what it can't
// write, and a ...Problem method says why.
System.out.println("mysql name: " + Dialect.mysql.quoteName("my `table`"));
System.out.println("mysql string: " + Dialect.mysql.quoteString("it's \\ fine"));
System.out.println("postgres string: " + Dialect.postgres.quoteString("it's \\ fine"));
String longName = "n".repeat(64);
System.out.println("postgres, 64-byte name: " + Dialect.postgres.nameProblem(longName));
try {
    Dialect.postgres.quoteString("a\0b");
    System.out.println("postgres, NUL in a string: quoted");
} catch (RuntimeException e) {
    System.out.println("postgres, NUL in a string: refused, " + Dialect.postgres.stringProblem("a\0b"));
}
try {
    Identifier.named(longName);
    System.out.println("Identifier, 64 bytes: named");
} catch (RuntimeException e) {
    System.out.println("Identifier, 64 bytes: refused, " + identifierProblem(longName));
}

// For tools that write SQL text themselves, such as DDL defaults. Each
// throws rather than change what it can't write, and a ...Problem method
// says why.
console.log("mysql name: " + Dialect.mysql.quoteName("my `table`"));
console.log("mysql string: " + Dialect.mysql.quoteString("it's \\ fine"));
console.log("postgres string: " + Dialect.postgres.quoteString("it's \\ fine"));
const longName = "n".repeat(64);
console.log("postgres, 64-byte name: " + Dialect.postgres.nameProblem(longName));
try {
  Dialect.postgres.quoteString("a\0b");
  console.log("postgres, NUL in a string: quoted");
} catch {
  console.log("postgres, NUL in a string: refused, " + Dialect.postgres.stringProblem("a\0b"));
}
try {
  Identifier.named(longName);
  console.log("Identifier, 64 bytes: named");
} catch {
  console.log("Identifier, 64 bytes: refused, " + identifierProblem(longName));
}

-- For tools that write SQL text themselves, such as DDL defaults. Each
-- raises an error rather than change what it can't write, which pcall
-- catches, and a ...Problem method says why.
print("mysql name: " .. Dialect.mysql:quoteName("my `table`"))
print("mysql string: " .. Dialect.mysql:quoteString([[it's \ fine]]))
print("postgres string: " .. Dialect.postgres:quoteString([[it's \ fine]]))
local longName = string.rep("n", 64)
print("postgres, 64-byte name: " .. Dialect.postgres:nameProblem(longName))
if pcall(Dialect.postgres.quoteString, Dialect.postgres, "a\0b") then
  print("postgres, NUL in a string: quoted")
else
  print("postgres, NUL in a string: refused, " .. Dialect.postgres:stringProblem("a\0b"))
end
if pcall(Identifier.named, longName) then
  print("Identifier, 64 bytes: named")
else
  print("Identifier, 64 bytes: refused, " .. identifierProblem(longName))
end

# For tools that write SQL text themselves, such as DDL defaults. Each
# raises RuntimeError rather than change what it can't write, and a
# _problem function says why.
print("mysql name: " + Dialect.mysql.quote_name("my `table`"))
print("mysql string: " + Dialect.mysql.quote_string("it's \\ fine"))
print("postgres string: " + Dialect.postgres.quote_string("it's \\ fine"))
long_name = "n" * 64
print("postgres, 64-byte name: " + Dialect.postgres.name_problem(long_name))
try:
    Dialect.postgres.quote_string("a\0b")
    print("postgres, NUL in a string: quoted")
except RuntimeError:
    print("postgres, NUL in a string: refused, " + Dialect.postgres.string_problem("a\0b"))
try:
    Identifier.named(long_name)
    print("Identifier, 64 bytes: named")
except RuntimeError:
    print("Identifier, 64 bytes: refused, " + identifier_problem(long_name))

// For tools that write SQL text themselves, such as DDL defaults. Each
// gives an Err rather than change what it can't write, and a _problem
// method says why.
println!("mysql name: {}", Dialect::mysql().quote_name("my `table`")?);
println!("mysql string: {}", Dialect::mysql().quote_string(r"it's \ fine")?);
println!("postgres string: {}", Dialect::postgres().quote_string(r"it's \ fine")?);
let long_name = "n".repeat(64);
println!("postgres, 64-byte name: {}", problem(Dialect::postgres().name_problem(long_name.as_str())));
match Dialect::postgres().quote_string("a\0b") {
    Ok(_) => println!("postgres, NUL in a string: quoted"),
    Err(_) => println!("postgres, NUL in a string: refused, {}", problem(Dialect::postgres().string_problem("a\0b"))),
}
match Identifier::named(long_name.as_str()) {
    Ok(_) => println!("Identifier, 64 bytes: named"),
    Err(_) => println!("Identifier, 64 bytes: refused, {}", problem(identifier_problem(long_name.as_str()))),
}

// For tools that write SQL text themselves, such as DDL defaults.
// Each bubbles rather than change what it can't write, and a
// ...Problem method says why.
say("mysql name: ${Dialect.mysql.quoteName("my `table`") orelse panic()}");
say("mysql string: ${Dialect.mysql.quoteString("it's \\ fine") orelse panic()}");
say("postgres string: ${Dialect.postgres.quoteString("it's \\ fine") orelse panic()}");
let longName = new StringBuilder();
for (var i = 0; i < 64; i += 1) { longName.append("n"); }
let long = longName.toString();
say("postgres, 64-byte name: ${Dialect.postgres.nameProblem(long) ?? ""}");
let withNul = "a${String.fromCodePoint(0) orelse panic()}b";
say("postgres, NUL in a string: ${
  do { Dialect.postgres.quoteString(withNul); "quoted" }
  orelse "refused, ${Dialect.postgres.stringProblem(withNul) ?? ""}"
}");
say("Identifier, 64 bytes: ${
  do { Identifier.named(long); "named" }
  orelse "refused, ${identifierProblem(long) ?? ""}"
}");