qians_xql

An SQL query builder for sqlx. Work in progress

Table of Contents

1. Basic Query Building
   1. Insert statement
   2. Select statement
   3. Update statement
   4. Delete statement
2. Blanket
   1. Blanket on Expression
   2. Blanket on Table Expression
   3. Blanket on SELECT and VALUES statement
3. Derive
4. Execution
5. Notes on str and String

Basic Query Building

Suppose you have a table like this:

sql CREATE TABLE book( id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT, lang TEXT, year SMALLINT );

The CRUD (or ISUD with sql acronym) will be look like this:

INSERT statement.

```rust let book1 = "The Fellowship of the Rings".tostring(); let auth1 = "J. R. R. Tolkien".tostring(); let book2 = "Dune".tostring(); let auth2 = "Frank Herbret".tostring(); let english = "English".to_string();

let values = [ (1i32, &book1, &auth1, &english, 1954i16), (2i32, &book2, &auth2, &english, 1965i16), ]; let insert = qians_xql::insert("book", ["id", "title", "author", "lang", "year"]) .values(values) .returning(["id"]);

asserteq!( insert.tostring(), "INSERT INTO book(id, title, author, lang, year) VALUES \ (1, 'The Fellowship of the Rings', 'J. R. R. Tolkien', 'English', 1954), \ (2, 'Dune', 'Frank Herbret', 'English', 1965) \ RETURNING id", ); ```

SELECT statement.

```rust let select = qiansxql::select(["id", "title"]) .from("book") .filter(qiansxql::or(qiansxql::eq("id", 1), qiansxql::eq("id", 2))) .orderby(qiansxql::desc("year"));

asserteq!( select.tostring(), "SELECT id, title FROM book WHERE id = 1 OR id = 2 ORDER BY year DESC" ); ```

UPDATE statement.

```rust let author = &"Frank Herbert".tostring(); let update = qiansxql::update("book") .set("author", author) .filter(qians_xql::eq("id", 2)) .returning(["id"]);

asserteq!( update.tostring(), "UPDATE book SET author = 'Frank Herbert' WHERE id = 2 RETURNING id", ); ```

DELETE statement.

```rust let delete = qiansxql::delete("book") .filter(qiansxql::eq("id", 1)) .returning(["id", "title"]);

asserteq!( delete.tostring(), "DELETE FROM book WHERE id = 1 RETURNING id, title", ); ```

Blanket

There are some blanket implementation for traits that defined in qians_xql::blanket to assist query building.

Blanket on Expression

Most of expr's function defined in qians_xql::ops have method of blanket implementation of qians_xql::blanket::ExprExt.

```rust use qians_xql::blanket::ExprExt;

let cond = "year".greaterthan(1900).and("year".lessequal(2000)); asserteq!(cond.tostring(), "year > 1900 AND year <= 2000");

let query = qiansxql::select(["id"]).from("book").filter(cond); asserteq!(query.to_string(), "SELECT id FROM book WHERE year > 1900 AND year <= 2000"); ```

Well, that looks verbose. It can't be helped, because using gt or le will clash with PartialOrd (which can't be disabled even with no_implicit_prelude). This one below will not compile.

```rust,compilefail use qiansxql::blanket::ExprExt;

let cond = "year".gt(1900).and("year".le(2000)); ```

A work around is to turn the left hand side into Expr first or using a table qualified column reference.

```rust use qiansxql::expr::Expr; use qiansxql::blanket::ExprExt;

let year = Expr::from("year"); let qualified = ("book", "year");

let cond = year.gt(1900).and(qualified.le(2000)); asserteq!(cond.tostring(), "year > 1900 AND book.year <= 2000"); ```

Blanket on Table Expression

join family functions have some blanket implementations.

```rust use qiansxql::blanket::ExprExt; use qiansxql::blanket::TableExprExt;

let table = "book".join("category", ("book", "categoryid").eq(("category", "id"))); asserteq!(table.tostring(), "book JOIN category ON book.categoryid = category.id"); ```

Blanket on SELECT and VALUES statement

SELECT and VALUES are the only statements that can use UNION family functions.

```rust use qians_xql::blanket::ResultExt;

let query = qiansxql::select([1, 2]).union(qiansxql::values([(3, 4)]));

asserteq!(query.tostring(), "SELECT 1, 2 UNION VALUES (3, 4)"); ```

In case you're wondering, ResultExt's name came from qians_xql::stmt::result::Result which is an enum of only Select and Values. Why Result? Well, because naming is hard and it looks good in Stmt enum definition:

rust enum Stmt { Insert, Select, Update, Delete, Values, Binary, Result, // See!? Exactly 6 characters! Perfectly balanced as all things should be! }

Execution

To execute those queries, enable sqlx feature and one of postgres, mysql or sqlite feature.

```rust

[derive(sqlx::FromRow)]

struct Output { id: i32, title: String, }

[cfg(feature = "postgres")]

async fn execute(pool: sqlx::Pool::) -> Result<(), sqlx::Error> {

// sqlx::query(..).fetch_all
let query = qians_xql::select(["id", "title"]).from("book");
let rows = qians_xql::exec::fetch_all(query, &pool).await?;

// sqlx::query_as(..).fetch_all
let query = qians_xql::select(["id", "title"]).from("book");
let rows: Vec<Output> = qians_xql::exec::fetch_all_as(query, &pool).await?;

// sqlx::query_scalar(..).fetch_all
let query = qians_xql::select(["id"]).from("book");
let rows: Vec<i32> = qians_xql::exec::fetch_all_scalar(query, &pool).await?;

// or in blanket form
use qians_xql::blanket::StmtExt;

let rows = qians_xql::select(["id", "title"])
    .from("book")
    .fetch_all(&pool).await?;

let rows: Vec<Output> = qians_xql::select(["id", "title"])
    .from("book")
    .fetch_all_as(&pool)
    .await?;


let rows: Vec<i32> = qians_xql::select(["id"])
    .from("book")
    .fetch_all_scalar(&pool).await?;

Ok(())

} ```

Available variants are: fetch_one, fetch_all, fetch_optional with _as, _scalar or no suffix respectively.

Notes on str and String

You may notice serveral use of &"text".to_string() in the examples above. That's because &str will turn into an identifier while &String will turn into a literal text.