myrow.remove(&pool).await?;
myrow.some_field = 1; // compile_error

宏：介绍

Sqlo 支持 select!、insert 和 update! 宏。我们尽量保持 API 一致，以便更容易记忆和使用。在本章中，我们将解释使用这些宏的核心原则，下一章将解释每个宏。

• 宏仅作为 sqlx 宏 sqlx::query! 和 sqlx:query_as! 的语法糖。

• 宏返回一个闭包，该闭包以 sqlx Executor 作为唯一参数。返回类型取决于您使用的内容，与 fetch_one, fetch_all, fetch, execute, ... 相同。

• 这是 Rust 语法而不是 SQL：这就是我们为什么使用 == 而不是 = 的原因。

• Sqlo 宏内容转换为 sqlx 内容

select![. House where room >23](&pool)
// is replaced with
sqlx::query_as!(House, "select * from house h where h.room > ?", 23).fetch_one(&pool)

这意味着，经过 sqlo 的检查后，sqlx 的检查将按常规进行。

• 每个字面量、变量参数等都被作为参数传递给 sqlx 宏。

• sqlx 方法调用选择使用与常规查询开头相同的标点符号。它遵循众所周知的正则表达式语法

  • nothing -> execute（返回无）
  • . -> fetch_one（一个）
  • * -> fetch_all（零个或多个）
  • ? -> fetch_optional（一个或零个）
  • + -> fetch（一个或多个。）

有关更多信息，请参阅 sqlx 文档。

update! 宏

它支持以下格式

update![TableStruct[instance_id] field1=value1, field2=value2](&pool).await?
// with square bracket instance id is a u32, string, &str, Uuid, ....

update![TableStruct(instance) field1=value1, field2=value2](&pool).await?
// use an instance of TableStruct, primary_key is deduced.
// this format takes ownership of instance sor you can't use instance after.

// To reuse instance you have to specify a return (fetch_one, fetch_all, fetch)
let instance = update![. TableStruct(instance) field1=value1, field2=value2](&pool).await?
//  not the dot `.` meaning fetch_one
// only with sqlite and postgres

#[derive[Sqlo, Debug, PartialEq]]
struct House {
    id: i64,
    name: String,
    width: i64,
    height: i64
}
//...
let house = House::get(&pool, 2);
let house = update![. House(house) name= "bla", width=34](&pool).await?;
let big_height = 345;
update_House!(House[2] height=big_height)(&pool).await?;
//or
update_House!(House[2] height=::big_height)(&pool).await?;

insert! 宏

它支持以下格式

#[derive[Sqlo, Debug, PartialEq]]
struct House {
    #[sqlo(insert_fn="some::func::to_create_ids")]
    id: i64,
    name: String,
    width: i64,
    height: Some(i64)
}
// with all fiekds
insert![House, id=1, name="bla", width=23, height=34](&pool).await?
// with all fields, None explicit
insert![House, id=1, name="bla", width=23, height=None](&pool).await?
// with all fields, None implicit
insert![House, id=1, name="bla", width=23](&pool).await?
// using the `insert_fn` for primary key
insert![House,  name="bla", width=23, height=None](&pool).await?
// returning instance
let house  = insert![. House,  name="bla", width=23, height=None](&pool).await?
// with variable
let a  = 1;
insert![House,id=::a  name="bla", width=23, height=None](&pool).await?
//or
insert![House,id=a  name="bla", width=23, height=None](&pool).await?

如果数据库管理系统支持，主键也可以省略。

返回实例使用 .，在 SQL 中使用 insert.... returning。实际上与 MariaDB 不完全兼容

《select!》宏

使用《select!》宏执行选择查询。

// query returning a derived sqlo struct
let res: Vec<MyStruct> select![* MyStruct where myfield > 1](&pool).await.unwrap();
// select * from mystruct_table where mystruct_table.myfield >1

// query some specific values/column
let res = select![. MyStruct max(some_field) as bla where something == 23](&pool).await.unwrap();
assert_eq!(res.bla, 99)

让我们使用这些结构体来编写本章。

#[derive[Sqlo, Debug, PartialEq]]
struct House {
    id: i64,
    name: String,
    width: i64,
    height: i64,
    zipcode: i64
}


struct Room {
    id: i64,
    #[sqlo(fk = "House", related = "therooms")]
    house_id: i64
    bed: bool
}

简介

基本上，对于平面结构体查询，它底层使用 sqlx::query_as!，只翻译查询或 sqlx::query! 用于字段/列查询。

select![* House where bed == true].await
//roughly is translated into
query_as![House, "SELECT DISTINCT id, name, width, height FROM house where bed=?", true].fetch_all(&p.pool).await;

select![. House  max(width) as width_max where height > 1](&pool).await;
//roughly is translated into
query!["SELECT DISTINCT max(width) AS width_max FROM house where height > ?", 1].fetch_one(&pool).await

请注意，它假设一个 主 sqlo 结构体（这里为 House），从中推断出字段/列、关系/相关字段。

一些通用规则

• Sqlo 尝试自动通过添加 DISTINCT 来避免重复，因为重复的需求非常罕见。因此，请注意，每个《select!》查询都不会有重复的结果。

查询列

默认情况下，《select!》查询主结构体的所有字段。但如果您想查询某些列，也可以这样做

select![. House  max(width) as my_max where height > 1](&pool).await;

• 它将使用 sqlx::query! 而不是 sqlx::query_as!。

• 但 sqlx::query_as! 也可以用于指向另一个结构体，在结构体名称前添加逗号分隔

struct Total {
    all: i32
}
let total = select![. Total, House count(id) as all](&pool).await.unwrap();
assert_eq!(total.all, 5);

• 我们支持以下 "列" 格式
  • 标识符（id，width，...）：一个字段。
  • 字段访问（therooms.bed）：访问相关字段。这将添加一个 INNER JOIN
  • 字段访问（therooms=.bed）：访问相关字段。这将添加一个 LEFT JOIN
  • 带有结构体名称的字段访问：House.width
  • SQL 函数（sum(id)，replace(adresse, "1", "345")）
  • 二元运算（id + 3）
  • 一元：-id，-1，...
  • 情况：使用 Rust match case when then

let myvar = "bla".to_string();
let myarray = ["bli", "ble", "blo"];
select![* House replace(name, ::myvar, ::myarray[1]) as new_name](&pool).await.unwrap();
//sqlx::query!["SELECT REPLACE(name, ?, ?) as new_name FROM house", myvar, myarray[1]].fetch_all(&pool)

select![* House replace(name, ::myvar, ::myarray[1]) as "new_name!:String"](&pool).await.unwrap();

sqlx::query![r#"SELECT id, count(width) as "total!:i32" group by "total!:i32" "#]
//instead with sqlo, just repeat the alias name without type indication
select![. House id, count(width) as "total!:i32" group_by total]

select![. House id as id!, count(width) as total?]
//or
select![. House id!, count(width) as total?]

select![.House count(*)]

// Variables
let width = 34;
select![* House where height == ::width] // Right hand part of the expression will refere to the variable width not the field `width` of struct House
select![* House where width == ::width]

select![.House where id == ::width] // variable width is used
// sql : select * from house where id=? (? will be 34 as parameter)
select![.House where id == width] // variable width is ignored, column name wil be used in sql
// sql : select * from house where id=width

// Indexing
let array = [1 , 2, 3]
select![. House where width == ::array[0]]

// struct field
struct A {b:i32}
let a = A{b:2}
select![. House where width == ::a.b]

select[.House id, match width 33=>"small", 100=>"big", _=>"don't know" as "how_big:String"]
//sqlx::query![r#"SELECT id, CASE width WHEN ? THEN ? WHEN ? THEN ? ELSE ? END as "how_big:String""#,33,"small",100, "big", "dont know"]
select[.House id, match width<33=>"small", width<100=>"big", _=>"very big" as "how_big:String"]
//sqlx::query![r#"SELECT id, CASE WHEN house.width<? THEN ? WHEN house.width<? THEN ? ELSE ? END as "how_big:String""#,33,"small",100, "big", "very big"]

// select all related rooms of house where there is a bed
let a = 1;
let romms: Vec<Room> = select![* House[a].therooms where bed == true](&pool).await.unwrap();
//sqlx::query_as![Room, r#"SELECT * FROM room where id=? AND bed=?"#, a, true].fetch_all...

select![* House where therooms.bed == true]
// sqlx::query_as![House, "SELECT * FROM house INNER JOIN room ON house.id=room.maison_id WHERE room.bed == ?", true].fetch_all
select![ * House where width>3 && therooms=.bed == true]
// sqlx::query_as![House, "SELECT * FROM house LEFT JOIN room ON house.id=room.maison_id WHERE house.width> ? AND room.bed == ?", 3, true].fetch_all
select![. House id, count(therooms.id) as total]
// sqlx::query_as![House, "SELECT maison.id, count(room.id) as total FROM house JOIN room ON house.id=room.maison_id"].fetch_one

select![* House id, therooms.id where therooms=.bed == true] // BAD you use to different joins INNER and LEFT (sqlx will fail)
select![* House id, therooms=.id where therooms=.bed == true] // GOOD : the join is expressed in the same way

select![* House id, therooms=.id as "rooms_id?"]

select![.House width, count(id) as "total!:i32" group_by width order_by total]
select![.House name, count(therooms.house_id) as total group_by name] // follows foreign keys

select![.House id, sum(width) as total having total > 350]

// with foreign keys
select![.House id, count(therooms.id) as total having total > 4]

select![*House order_by -width, height]
select![*House order_by[-width, height]]
select![*House id, width as "bla:i32" order_by bla]

select![*House limit 5] // SELECT * FROM house LIMIT 5
select![*House limit 5,8] // SELECT * FROM house LIMIT 5 OFFSET 8
select![*House limit[5,8]] // SELECT * FROM house LIMIT 5 OFFSET 8

select![*House, House id as "id!", width as "width!", height as "height!", name as "name!" order_by name limit 4]
// when using fields `select!` uses query_as! behind the back so reinforce using query_as! with House

let limit = select![*House limit 2,4].fetch_all(&p.pool).await.unwrap();
let page = select![*House page 3,2].fetch_all(&p.pool).await.unwrap(); //means page 3 with page size of 2.
// will both select 5th et 6th entries.
assert_eq!(limit, page);

select![*House where zipcode in {ZipCodeTable zip where zip > 260}](&pool)...
// transltates to
// sqlx::query_as!(House, "select * from house where zipcode in (select distinct zip from zip_table where zip > ?)", 260 ).fetch_all...

select![*House id, {HouseKind count(*) where width == House.width} as kind_total ]
// a few notes here :
// - it needs an alias since it's returned
// - use the struct name to leverage ambigous fields (here width)
// - no `as` is required in the subquery since it's not returned

select![*House where zipcode where exists {ZipCodeTable zip where zip > 260}].fetch_all...

select![dbg! * House where width >30]...