diesel

$ brew install mysql-client sqlite postgresql

# 开启自动启动 postgresql
$ brew services start postgresql@14

# 确认启动成功
$ brew services list
Name          Status  User  File
bind          none
dbus          none
emacs-plus@30 none
postgresql@14 started alizj ~/Library/LaunchAgents/[email protected]
unbound       none

$ lsof -i tcp:5432
COMMAND    PID  USER   FD   TYPE             DEVICE SIZE/OFF NODE NAME
postgres 64220 alizj    7u  IPv6 0x5297ba1d8b4e0e13      0t0  TCP localhost:postgresql (LISTEN)
postgres 64220 alizj    8u  IPv4 0x5297ba271f55c663      0t0  TCP localhost:postgresql (LISTEN)

# 连接 postgresql，设置账号密码
$ psql -d postgres
psql (14.12 (Homebrew))
Type "help" for help.
postgres=# CREATE ROLE zj WITH LOGIN PASSWORD 'xxx';
CREATE ROLE
postgres=# ALTER ROLE zj CREATEDB;
ALTER ROLE

$ psql -l
List of databases
Name    | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
postgres  | alizj | UTF8     | C       | C     |
    template0 | alizj | UTF8     | C       | C     | =c/alizj         +
			       |       |          |         |       | alizj=CTc/alizj
			       template1 | alizj | UTF8     | C       | C     | =c/alizj         +
			       |       |          |         |       | alizj=CTc/alizj
			       (3 rows)

# 编译安装 diesel_cli 前需要指定 MYSQLCLIENT_LIB_DIR 和 MYSQLCLIENT_VERSION 环境变量，否则编译失败。
$ ls -l /opt/homebrew/opt/mysql-client/lib
total 14M
-rw-r--r-- 1 alizj 6.6M  8  1 13:44 libmysqlclient.23.dylib
-r--r--r-- 1 alizj 7.1M 12 14  2023 libmysqlclient.a
lrwxr-xr-x 1 alizj   23 12 14  2023 libmysqlclient.dylib -> libmysqlclient.23.dylib
drwxr-xr-x 3 alizj   96  8  1 13:44 pkgconfig/
$ export MYSQLCLIENT_LIB_DIR=/opt/homebrew/opt/mysql-client/lib MYSQLCLIENT_VERSION=23

$ cargo install diesel_cli

# 创建环境变量文件
zj@a:~/work/code/learn-by-doing/rust/diesel_demo$ echo DATABASE_URL=postgres://zj:1234@localhost/diesel_demo > .env

# 创建 migrations 目录
zj@a:~/work/code/learn-by-doing/rust/diesel_demo$ diesel setup
Creating migrations directory at: /Users/alizj/work/code/learn-by-doing/rust/diesel_demo/migrations
Creating database: diesel_demo

zj@a:~/work/code/learn-by-doing/rust/diesel_demo$ cat diesel.toml
[print_schema]
file = "src/schema.rs"
custom_type_derives = ["diesel::query_builder::QueryId", "Clone"]

[migrations_directory]
dir = "/Users/alizj/work/code/learn-by-doing/rust/diesel_demo/migrations"


zj@a:~/work/code/learn-by-doing/rust/diesel_demo$ ls -l migrations/00000000000000_diesel_initial_setup/
total 8.0K
-rw-r--r-- 1 alizj  328  8  1 14:05 down.sql
-rw-r--r-- 1 alizj 1.2K  8  1 14:05 up.sql


zj@a:~/work/code/learn-by-doing/rust/diesel_demo$ psql -l
List of databases
Name     | Owner | Encoding | Collate | Ctype | Access privileges
-------------+-------+----------+---------+-------+-------------------
diesel_demo | zj    | UTF8     | C       | C     |
    postgres    | alizj | UTF8     | C       | C     |
    template0   | alizj | UTF8     | C       | C     | =c/alizj         +
				 |       |          |         |       | alizj=CTc/alizj
				 template1   | alizj | UTF8     | C       | C     | =c/alizj         +
				 |       |          |         |       | alizj=CTc/alizj
				 (4 rows)


				 zj@a:~/work/code/learn-by-doing/rust/diesel_demo$ diesel migration generate create_posts
				 Creating migrations/2024-08-01-060838_create_posts/up.sql
				 Creating migrations/2024-08-01-060838_create_posts/down.sql

				 zj@a:~/work/code/learn-by-doing/rust/diesel_demo$ cat migrations/2024-08-01-060838_create_posts/up.sql
				 -- Your SQL goes here
				 CREATE TABLE posts (
				     id SERIAL PRIMARY KEY,
				     title VARCHAR NOT NULL,
				     body TEXT NOT NULL,
				     published BOOLEAN NOT NULL DEFAULT FALSE
				 )

				 zj@a:~/work/code/learn-by-doing/rust/diesel_demo$ cat migrations/2024-08-01-060838_create_posts/down.sql
				 -- This file should undo anything in `up.sql`
				 DROP TABLE posts

				 zj@a:~/work/code/learn-by-doing/rust/diesel_demo$ diesel migration run
				 Running migration 2024-08-01-060838_create_posts

				 zj@a:~/work/code/learn-by-doing/rust/diesel_demo$ cat src/schema.rs # 或者 diesel print-schema
				 // @generated automatically by Diesel CLI.

				 diesel::table! {
				     posts (id) {
					 id -> Int4,
					 title -> Varchar,
					 body -> Text,
					 published -> Bool,
				     }
				 }

// Struct diesel::sql_types::Integer
pub struct Integer;

// 每种  DB Backend 都定义了自己的 ToSql 和 FromSql 实现，用于 sql_type 和 Rust 类型类型的转换
impl ToSql<Integer, Mysql> for i32
impl ToSql<Integer, Pg> for i32
impl ToSql<Integer, Sqlite> for i32

impl FromSql<Integer, Mysql> for i32
impl FromSql<Integer, Pg> for i32
impl FromSql<Integer, Sqlite> for i32

// cargo.toml 中需要为 diesel 启用 chron feature

// [dependencies]
// diesel = { version = "2.1.0", path = "../../../diesel", features = ["mysql", "chrono"] }
// serde = { version = "1.0.130", features = ["derive"] }
// serde_json = "1.0.68"
// chrono = { version = "0.4.20", default-features = false, features = ["clock", "std"] }


// SQL：
// CREATE TABLE users (
//   id INTEGER PRIMARY KEY AUTO_INCREMENT,
//   name TEXT NOT NULL,
//   hair_color TEXT,
//   created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
//   updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
// );

use chrono::NaiveDateTime;
#[cfg(test)]
use diesel::debug_query;
use diesel::insert_into;
#[cfg(test)]
use diesel::mysql::Mysql;
use diesel::prelude::*;
use serde::Deserialize;
use std::error::Error;

mod schema {
    diesel::table! {
        users {
            id -> Integer,                // diesel sql_type 类型.
            name -> Text,                 // 默认都是 NOT NULL（对应 table 字段加了 NOT NULL）
            hair_color -> Nullable<Text>, // 可插入 NULL 字段（因为对应 table 字段没有加 NOT NULL）
            created_at -> Timestamp,
            updated_at -> Timestamp,
        }
    }
}
use schema::users;
#[derive(Queryable, PartialEq, Debug)] // Queryable 要求 struct 字段必须和 table!() 生成的完全一致.
struct User {
    id: i32,
    name: String,
    hair_color: Option<String>, // 可选字段
    created_at: NaiveDateTime, // Rust 类型：chrono::NaiveDateTime
    updated_at: NaiveDateTime,
}

#[derive(Deserialize, Insertable)] // Insertable 只需定义 table!() 中部分字段.
#[diesel(table_name = users)]
pub struct UserForm<'a> {
    name: &'a str,
    hair_color: Option<&'a str>, // 对于可插入 NULL 字段，需要使用 Option<T>, 传入 None 时插入 NULL
}

// CREATE TABLE posts (
//   id INTEGER AUTO_INCREMENT PRIMARY KEY,
//   title VARCHAR(255) NOT NULL,
//   body TEXT NOT NULL,
//   published BOOLEAN NOT NULL DEFAULT FALSE
// );

diesel::table! {
    posts (id) {
        id -> Integer,
        title -> Varchar, // SQL VARCHAR(N) 类型对应 diesel 的 Varchar 类型
        body -> Text,     // SQL TEXT 类型对应 diesel 的 Text 类型
        published -> Bool,
    }
}

#[derive(Queryable, Selectable)]
#[diesel(table_name = posts)]
#[diesel(check_for_backend(diesel::mysql::Mysql))]
pub struct Post {
    pub id: i32,       // Integer 可以使用 i32 类型
    pub title: String, // Varchar/Text 都可以使用 Rust String 或 &str 类型
    pub body: String,
    pub published: bool,
}

#[derive(Insertable)]
#[diesel(table_name = posts)]
pub struct NewPost<'a> {
    pub title: &'a str,
    pub body: &'a str,
}

/// A marker trait for SQL types
///
/// # Deriving
///
/// This trait is automatically implemented by [`#[derive(SqlType)]`](derive@SqlType)
/// which sets `IsNull` to [`is_nullable::NotNull`]
///
pub trait SqlType: 'static {
    /// Is this type nullable?
    ///
    /// This type should always be one of the structs in the ['is_nullable`]
    /// module. See the documentation of those structs for more details.
    ///
    /// ['is_nullable`]: is_nullable
    type IsNull: OneIsNullable<is_nullable::IsNullable> + OneIsNullable<is_nullable::NotNull>;
}

/// The integer SQL type.
///
/// ### [`ToSql`](crate::serialize::ToSql) impls
///
/// - [`i32`][i32]
///
/// ### [`FromSql`](crate::deserialize::FromSql) impls
///
/// - [`i32`][i32]
///
/// [i32]: https://doc.rust-lang.org/nightly/std/primitive.i32.html
#[derive(Debug, Clone, Copy, Default, QueryId, SqlType)] // 自动实现 SqlType
#[diesel(postgres_type(oid = 23, array_oid = 1007))] // 各种 DB Backend 的字段类型
#[diesel(sqlite_type(name = "Integer"))]
#[diesel(mysql_type(name = "Long"))]
pub struct Integer;

pub type Int4 = Integer;

// https://github.com/diesel-rs/diesel/tree/2.2.x/examples/postgres/custom_types

// up.sql
// CREATE TYPE Language AS ENUM (
//     'en', 'ru', 'de'
// );

// CREATE TABLE translations (
//     word_id INTEGER NOT NULL,
//     translation_id INTEGER NOT NULL,
//     language Language NOT NULL,

//     PRIMARY KEY (word_id, translation_id)
// )


// @generated automatically by Diesel CLI.

// 在 sql_types module 中自动生成一个名为 Language 的自定义 diesel sql_type 类型.
// 1. 使用 #[derive(diesel::sql_types::SqlType)] 来为 Language 实现 SqlType trait;
pub mod sql_types {
    #[derive(diesel::sql_types::SqlType)] // 使用 derive macro 实现 SqlType
    #[diesel(postgres_type(name = "language"))] // 关联的 PG 字段类型名称
    pub struct Language;
}

diesel::table! {
    use diesel::sql_types::*;
    use super::sql_types::Language; // 导入自定义 diesel sql_type 类型

    translations (word_id, translation_id) {
        word_id -> Int4,
        translation_id -> Int4,
        language -> Language, // 使用该类型
    }
}


use diesel::deserialize::{self, FromSql, FromSqlRow};
use diesel::expression::AsExpression;
use diesel::pg::{Pg, PgValue};
use diesel::serialize::{self, IsNull, Output, ToSql};
use std::io::Write;

// 再为 sql_types 类型绑定一个 Rust 类型， 需要实现  AsExpression 和 FromSqlRow
// 1. 实现 ToSql 的同时需要实现 AsExpression
// 2. 实现 FromSql 的同时需要实现 FromSqlRow
#[derive(Debug, AsExpression, FromSqlRow)]
#[diesel(sql_type = crate::schema::sql_types::Language)] // 关联 diesel sql_type 类型
pub enum Language {
    En,
    Ru,
    De,
}

impl ToSql<crate::schema::sql_types::Language, Pg> for Language {
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
        match *self {
            Language::En => out.write_all(b"en")?,
            Language::Ru => out.write_all(b"ru")?,
            Language::De => out.write_all(b"de")?,
        }
        Ok(IsNull::No)
    }
}

impl FromSql<crate::schema::sql_types::Language, Pg> for Language {
    fn from_sql(bytes: PgValue) -> deserialize::Result<Self> {
        match bytes.as_bytes() {
            b"en" => Ok(Language::En),
            b"ru" => Ok(Language::Ru),
            b"de" => Ok(Language::De),
            _ => Err("Unrecognized enum variant".into()),
        }
    }
}

// main.rs
use self::schema::translations;
use diesel::prelude::*;

mod model;
mod schema;

#[derive(Debug, Queryable, Insertable)]
#[diesel(table_name = translations)]
pub struct Translation {
    word_id: i32,
    translation_id: i32,
    language: model::Language, // 使用自定义类型
}

fn main() {
    let database_url = std::env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    let conn = &mut PgConnection::establish(&database_url)
        .unwrap_or_else(|e| panic!("Error connecting to {}: {}", database_url, e));

    let _ = diesel::insert_into(translations::table)
        .values(&Translation {
            word_id: 1,
            translation_id: 1,
            language: model::Language::En,
        })
        .execute(conn);

    let t = translations::table
        .select((
            translations::word_id,
            translations::translation_id,
            translations::language,
        ))
        .get_results::<Translation>(conn)
        .expect("select");
    println!("{t:?}");
}

use diesel::deserialize::{self, FromSql};
use diesel::pg::Pg;

#[derive(FromSqlRow, AsExpression)]
#[diesel(sql_type = diesel::sql_types::Text)] // 已有的 sql_type 类型
struct Email(String);

impl FromSql<diesel::sql_types::Text, Pg> for Email {
    fn from_sql(bytes: Option<&[u8]>) -> deserialize::Result<Self> {
        let s = <String as FromSql<diesel::sql_types::Text, Pg>>::from_sql(bytes)?;
        if is_valid_email(&s) {
            Ok(Email(s))
        } else {
            Err("Invalid email format".into())
        }
    }
}

/// Represents a typed fragment of SQL.
///
/// Apps should not need to implement this type directly, but it may be common
/// to use this in where clauses. Libraries should consider using
/// [`infix_operator!`](crate::infix_operator!) or
/// [`postfix_operator!`](crate::postfix_operator!) instead of
/// implementing this directly.
pub trait Expression {
    /// The type that this expression represents in SQL
    type SqlType: TypedExpressionType;
}

/// Marker trait for possible types of [`Expression::SqlType`]
///
pub trait TypedExpressionType {}

impl<ST> TypedExpressionType for ST where ST: SingleValue {}

/// A marker trait indicating that a SQL type represents a single value, as
/// opposed to a list of values.
///
/// This trait should generally be implemented for all SQL types with the
/// exception of Rust tuples. If a column could have this as its type, this
/// trait should be implemented.
///
/// # Deriving
///
/// This trait is automatically implemented by [`#[derive(SqlType)]`](derive@SqlType)
///
pub trait SingleValue: SqlType {}

diesel::table! {
    posts (id) {
        id -> Int4,
        title -> Varchar,
        body -> Text,
        published -> Bool,
    }
}

impl diesel::expression::Expression for id {
    type SqlType = Int4;
}

/// Converts a type to its representation for use in Diesel's query builder.
///
/// This trait is used directly. Apps should typically use [`IntoSql`] instead.
///
/// Implementations of this trait will generally do one of 3 things:
///
/// - Return `self` for types which are already parts of Diesel's query builder
/// - Perform some implicit coercion (for example, allowing [`now`] to be used as
///   both [`Timestamp`] and [`Timestamptz`].
/// - Indicate that the type has data which will be sent separately from the
///   query. This is generally referred as a "bind parameter". Types which
///   implement [`ToSql`] will generally implement `AsExpression` this way.
///
///   [`IntoSql`]: crate::IntoSql
///   [`now`]: crate::dsl::now
///   [`Timestamp`]: crate::sql_types::Timestamp
///   [`Timestamptz`]: ../pg/types/sql_types/struct.Timestamptz.html
///   [`ToSql`]: crate::serialize::ToSql
///
///  This trait could be [derived](derive@AsExpression)
pub trait AsExpression<T> where T: SqlType + TypedExpressionType,
{
    /// The expression being returned
    type Expression: Expression<SqlType = T>;

    /// Perform the conversion
    #[allow(clippy::wrong_self_convention)]
    // That's public API we cannot change it to appease clippy
    fn as_expression(self) -> Self::Expression;
}

use crate::sql_types::{
    self, BigInt, Binary, Bool, Double, Float, Integer, SingleValue, SmallInt, Text,
};

#[allow(dead_code)]
mod foreign_impls {
    use super::*;
    use crate::deserialize::FromSqlRow;

    #[derive(AsExpression, FromSqlRow)] // 自动实现 AsExpression, FromSqlRow
    #[diesel(foreign_derive)]
    #[diesel(sql_type = Bool)] // 对应 diesel sql_type 类型是 Bool
    struct BoolProxy(bool);

    #[derive(FromSqlRow)]
    #[cfg_attr(feature = "mysql_backend", derive(AsExpression))]
    #[diesel(foreign_derive)]
    #[cfg_attr(feature = "mysql_backend", diesel(sql_type = crate::sql_types::TinyInt))]
    struct I8Proxy(i8);

    #[derive(AsExpression, FromSqlRow)]
    #[diesel(foreign_derive)]
    #[diesel(sql_type = SmallInt)]
    struct I16Proxy(i16);

    #[derive(AsExpression, FromSqlRow)]
    #[diesel(foreign_derive)]
    #[diesel(sql_type = Integer)]
    struct I32Proxy(i32);

    #[derive(AsExpression, FromSqlRow)]
    #[diesel(foreign_derive)]
    #[diesel(sql_type = BigInt)]
    struct I64Proxy(i64);

    #[derive(FromSqlRow)]
    #[cfg_attr(
        any(feature = "mysql_backend", feature = "postgres_backend"),
        derive(AsExpression)
    )]
    #[diesel(foreign_derive)]
    #[cfg_attr(
        feature = "mysql_backend",
        diesel(sql_type = crate::sql_types::Unsigned<crate::sql_types::TinyInt>)
    )]
    #[cfg_attr(feature = "postgres_backend", diesel(foreign_derive, sql_type = crate::sql_types::CChar))]
    struct U8Proxy(u8);

    #[derive(FromSqlRow)]
    #[cfg_attr(feature = "mysql_backend", derive(AsExpression))]
    #[diesel(foreign_derive)]
    #[cfg_attr(feature = "mysql_backend", diesel(sql_type = crate::sql_types::Unsigned<SmallInt>))]
    struct U16Proxy(u16);

    #[derive(FromSqlRow)]
    #[cfg_attr(
        any(feature = "mysql_backend", feature = "postgres_backend"),
        derive(AsExpression)
    )]
    #[diesel(foreign_derive)]
    #[cfg_attr(feature = "mysql_backend", diesel(sql_type = crate::sql_types::Unsigned<Integer>))]
    #[cfg_attr(feature = "postgres_backend", diesel(sql_type = crate::sql_types::Oid))]
    struct U32Proxy(u32);

    #[derive(FromSqlRow)]
    #[cfg_attr(feature = "mysql_backend", derive(AsExpression))]
    #[diesel(foreign_derive)]
    #[cfg_attr(feature = "mysql_backend", diesel(sql_type = crate::sql_types::Unsigned<BigInt>))]
    struct U64Proxy(u64);

    #[derive(AsExpression, FromSqlRow)]
    #[diesel(foreign_derive)]
    #[diesel(sql_type = Float)]
    struct F32Proxy(f32);

    #[derive(AsExpression, FromSqlRow)]
    #[diesel(foreign_derive)]
    #[diesel(sql_type = Double)]
    struct F64Proxy(f64);

    #[derive(AsExpression, FromSqlRow)]
    #[diesel(foreign_derive)]
    #[diesel(sql_type = Text)]
    #[cfg_attr(feature = "sqlite", diesel(sql_type = crate::sql_types::Date))]
    #[cfg_attr(feature = "sqlite", diesel(sql_type = crate::sql_types::Time))]
    #[cfg_attr(feature = "sqlite", diesel(sql_type = crate::sql_types::Timestamp))]
    #[cfg_attr(feature = "postgres_backend", diesel(sql_type = crate::sql_types::Citext))]
    struct StringProxy(String);

    #[derive(AsExpression)]
    #[diesel(foreign_derive, not_sized)]
    #[diesel(sql_type = Text)]
    #[cfg_attr(feature = "sqlite", diesel(sql_type = crate::sql_types::Date))]
    #[cfg_attr(feature = "sqlite", diesel(sql_type = crate::sql_types::Time))]
    #[cfg_attr(feature = "sqlite", diesel(sql_type = crate::sql_types::Timestamp))]
    #[cfg_attr(feature = "postgres_backend", diesel(sql_type = crate::sql_types::Citext))]
    struct StrProxy(str);

    #[derive(FromSqlRow)]
    #[diesel(foreign_derive)]
    struct VecProxy<T>(Vec<T>);

    #[derive(AsExpression)]
    #[diesel(foreign_derive)]
    #[diesel(sql_type = Binary)]
    struct BinaryVecProxy(Vec<u8>);

    #[derive(AsExpression)]
    #[diesel(foreign_derive, not_sized)]
    #[diesel(sql_type = Binary)]
    struct BinarySliceProxy([u8]);

    #[derive(AsExpression)]
    #[diesel(foreign_derive)]
    #[diesel(sql_type = Binary)]
    struct BinaryArrayProxy<const N: usize>([u8; N]);
}

// Trait diesel::expression_methods::ExpressionMethods
pub trait ExpressionMethods: Expression + Sized {
    // 指定的值
    fn eq<T>(self, other: T) -> Eq<Self, T>
       where Self::SqlType: SqlType, T: AsExpression<Self::SqlType>
    fn ne<T>(self, other: T) -> NotEq<Self, T>
       where Self::SqlType: SqlType, T: AsExpression<Self::SqlType>
    fn eq_any<T>(self, values: T) -> EqAny<Self, T>
       where Self::SqlType: SqlType, T: AsInExpression<Self::SqlType>
    fn ne_all<T>(self, values: T) -> NeAny<Self, T>
       where Self::SqlType: SqlType, T: AsInExpression<Self::SqlType>
   //...
}

let names = users::table.select("The Amazing ".as_expression::<Text>().concat(users::name)).load(conn);

let names = users::table.select("The Amazing ".into_sql::<Text>().concat(users::name)).load(conn);

/// Converts a type to its representation for use in Diesel's query builder.
///
/// This trait only exists to make usage of `AsExpression` more ergonomic when
/// the `SqlType` cannot be inferred. It is generally used when you need to use
/// a Rust value as the left hand side of an expression, or when you want to
/// select a constant value.
///
/// # Example
///
/// ```rust
/// # include!("../doctest_setup.rs");
/// # use schema::users;
/// #
/// # fn main() {
/// use diesel::sql_types::Text;
/// #   let conn = &mut establish_connection();
/// let names = users::table
///     .select("The Amazing ".into_sql::<Text>().concat(users::name))
///     .load(conn);
/// let expected_names = vec![
///     "The Amazing Sean".to_string(),
///     "The Amazing Tess".to_string(),
/// ];
/// assert_eq!(Ok(expected_names), names);
/// # }
/// ```
pub trait IntoSql {
    /// Convert `self` to an expression for Diesel's query builder.
    ///
    /// There is no difference in behavior between `x.into_sql::<Y>()` and
    /// `AsExpression::<Y>::as_expression(x)`.
    fn into_sql<T>(self) -> AsExprOf<Self, T>
    where
        Self: AsExpression<T> + Sized,
        T: SqlType + TypedExpressionType,
    {
        self.as_expression()
    }

    /// Convert `&self` to an expression for Diesel's query builder.
    ///
    /// There is no difference in behavior between `x.as_sql::<Y>()` and
    /// `AsExpression::<Y>::as_expression(&x)`.
    fn as_sql<'a, T>(&'a self) -> AsExprOf<&'a Self, T>
    where
        &'a Self: AsExpression<T>,
        T: SqlType + TypedExpressionType,
    {
        <&'a Self as AsExpression<T>>::as_expression(self)
    }
}

// 为任意类型 T 实现 IntoSql
impl<T> IntoSql for T {}

// table!() 宏使用的 sql type
use diesel::sql_types::*;


diesel::table! {
    posts (id) { // id 为默认 PK
        id -> Int4,
        title -> Varchar, // 字段默认是 NOT NULL
        body -> Text,
        published -> Bool,
    }
}

diesel::table! {
    users {
        id -> Integer,
        name -> VarChar,
        favorite_color -> Nullable<VarChar>, // 使用 Nullable 来设置可以为 NULL 的字段
    }
}


diesel::table! {
    users (non_standard_primary_key) { // 如果 PK 不是 id，则可以手动指定
        non_standard_primary_key -> Integer,
        name -> VarChar,
        favorite_color -> Nullable<VarChar>,
    }
}

diesel::table! {
    followings (user_id, post_id) { // 组合 PK
        user_id -> Integer,
        post_id -> Integer,
        favorited -> Bool,
    }
}

// 手动为自动生成的 table 添加注释（注意是 cargo doc 注释类型，后续会保留）
diesel::table! {
    /// The table containing all blog posts
    posts {
        /// The post's unique id
        id -> Integer,
        /// The post's title
        title -> Text,
    }
}

diesel::table! {
    posts {
        id -> Integer,
        /// This column is named `mytype` but references the table `type` column.
        #[sql_name = "type"] // 避免 Rust 关键字冲突
        mytype -> Text,
    }
}

diesel::table! {
    api_tokens (id) {
        /// The `id` column of the `api_tokens` table.
        ///
        /// Its SQL type is `Int4`.
        ///
        /// (Automatically generated by Diesel.)
        id -> Int4,
        /// The `token` column of the `api_tokens` table.
        ///
        /// Its SQL type is `Bytea`.
        ///
        /// (Automatically generated by Diesel.)
        token -> Bytea,
        /// The `name` column of the `api_tokens` table.
        ///
        /// Its SQL type is `Varchar`.
        ///
        /// (Automatically generated by Diesel.)
        name -> Varchar,
        /// The `created_at` column of the `api_tokens` table.
        ///
        /// Its SQL type is `Timestamp`.
        ///
        /// (Automatically generated by Diesel.)
        created_at -> Timestamp,
        /// The `last_used_at` column of the `api_tokens` table.
        ///
        /// Its SQL type is `Nullable<Timestamp>`.
        ///
        /// (Automatically generated by Diesel.)
        last_used_at -> Nullable<Timestamp>,
        /// The `revoked` column of the `api_tokens` table.
        ///
        /// Its SQL type is `Bool`.
        ///
        /// (Automatically generated by Diesel.)
        revoked -> Bool,
        /// NULL or an array of crate scope patterns (see RFC #2947)
        crate_scopes -> Nullable<Array<Text>>,
        /// An array of endpoint scopes or NULL for the `legacy` endpoint scope (see RFC #2947)
        endpoint_scopes -> Nullable<Array<Text>>,
        /// The `expired_at` column of the `api_tokens` table.
        ///
        /// Its SQL type is `Nullable<Timestamp>`.
        ///
        /// (Automatically generated by Diesel.)
        expired_at -> Nullable<Timestamp>,
        /// timestamp of when the user was informed about their token's impending expiration
        expiry_notification_at -> Nullable<Timestamp>,
    }
}

diesel::table! {
    /// Representation of the `background_jobs` table.
    ///
    /// (Automatically generated by Diesel.)
    background_jobs (id) {
        /// The `id` column of the `background_jobs` table.
        ///
        /// Its SQL type is `Int8`.
        ///
        /// (Automatically generated by Diesel.)
        id -> Int8,
        /// The `job_type` column of the `background_jobs` table.
        ///
        /// Its SQL type is `Text`.
        ///
        /// (Automatically generated by Diesel.)
        job_type -> Text,
        /// The `data` column of the `background_jobs` table.
        ///
        /// Its SQL type is `Jsonb`.
        ///
        /// (Automatically generated by Diesel.)
        data -> Jsonb,
        /// The `retries` column of the `background_jobs` table.
        ///
        /// Its SQL type is `Int4`.
        ///
        /// (Automatically generated by Diesel.)
        retries -> Int4,
        /// The `last_retry` column of the `background_jobs` table.
        ///
        /// Its SQL type is `Timestamp`.
        ///
        /// (Automatically generated by Diesel.)
        last_retry -> Timestamp,
        /// The `created_at` column of the `background_jobs` table.
        ///
        /// Its SQL type is `Timestamp`.
        ///
        /// (Automatically generated by Diesel.)
        created_at -> Timestamp,
        /// The `priority` column of the `background_jobs` table.
        ///
        /// Its SQL type is `Int2`.
        ///
        /// (Automatically generated by Diesel.)
        priority -> Int2,
    }
}

#[allow(unused_imports,dead_code,unreachable_pub)]
pub mod posts {
    pub use self::columns:: * ; // 导出各 field 字段对应的类型,如 id, title, body 等

    pub mod dsl {
        pub use super::columns::id;
        pub use super::columns::title;
        pub use super::columns::body;
        pub use super::columns::published;
        pub use super::table as posts; // table 别名为 posts
    }

    pub const all_columns:(id,title,body,published,) = (id,title,body,published,);

    // 固定类型 table
    pub struct table;
    impl table {
        pub fn star(&self) -> star {
            star // star 是在 self::columns 中定义的 pub struct star;
        }
    }

    // all_columns 对应的字段类型
    pub type SqlType = (Int4,Varchar,Text,Bool,);

    pub type BoxedQuery<'a,DB,ST = SqlType>

    // table 实现了各种 trait
    impl diesel::QuerySource for table
    impl <DB>diesel::query_builder::QueryFragment<DB>for table
    impl diesel::query_builder::AsQuery for table
    impl diesel::Table for table
    impl diesel::associations::HasTable for table
    impl diesel::query_builder::IntoUpdateTarget for table
    impl diesel::query_source::AppearsInFromClause<table>for table
    impl <T>diesel::insertable::Insertable<T>for table

    #[doc = r" Contains all of the columns of this table"]
    pub mod columns {
        use super::table;

        // 代表 users.* 查询
        pub struct star;
        // star 实现了各种 trait
        impl <__GB>diesel::expression::ValidGrouping<__GB>for star
        impl diesel::Expression for star {
        impl <DB:diesel::backend::Backend>diesel::query_builder::QueryFragment<DB>for star
        impl diesel::SelectableExpression<table>for star
        impl diesel::AppearsOnTable<table>for star

        // 每一个 struct 成员对对应一个类型
        #[allow(non_camel_case_types,dead_code)]
        #[derive(Debug,Clone,Copy,diesel::query_builder::QueryId,Default)]
        pub struct id;

        // 每个成员类型都实现了 Expression, 在 select 中使用.
        impl diesel::expression::Expression for id {
            type SqlType = Int4; // 决定了 ExpressionMethods 可以使用的方法
        }

        impl <DB>diesel::query_builder::QueryFragment<DB>for id
        impl diesel::SelectableExpression<super::table>for id

        // delete/update 等的 filter() 等使用
        impl <QS>diesel::AppearsOnTable<QS>for id

        impl <Left,Right>diesel::SelectableExpression<diesel::internal::table_macro::Join<Left,Right,diesel::internal::table_macro::LeftOuter> , >for id

        impl diesel::query_source::Column for id

        // 根据 id 的类型, 自动为它实现了各种 trait
        impl <T>diesel::EqAll<T>for id
        impl <Rhs> ::std::ops::Add<Rhs>for id
        impl <Rhs> ::std::ops::Sub<Rhs>for id
        impl <Rhs> ::std::ops::Div<Rhs>for id
        impl <Rhs> ::std::ops::Mul<Rhs>for id

// 不建议:
users::table
    .filter(users::name.eq("Sean"))
    .filter(users::hair_color.eq("black"))

    // 建议: 常用的形式:
    users.filter(name.eq("Sean")).filter(hair_color.eq("black"))

// 如果要使用 star, 则可以调用 users 的 star() 方法

// 示例:
fn main() {
    use self::schema::posts::dsl::posts; // 导入 posts table
    // ...
    let post = posts
        .find(post_id)
        .select(Post::as_select())
        .first(connection)
        .optional(); // This allows for returning an Option<Post>, otherwise it will throw an error
    // ...
}

diesel::insert_into(animals)
    .values(&vec![
        (species.eq("ferret"), legs.eq(4), name.eq("Freddy")),
        (species.eq("ferret"), legs.eq(4), name.eq("Jack")),
    ])
    .execute(connection)?;

let data = animals.select((species, name))
    .filter(species.eq("ferret").and(name.eq("Jack")))
    .load(connection)?;
let expected = vec![
    (String::from("ferret"), Some(String::from("Jack"))),
];
assert_eq!(expected, data);

use diesel::prelude::*;

#[derive(Queryable, Selectable)]
// table_name 是 Selectable 所需要的，默认为 struct 全小写名称加 s。
// check_for_backend 用于编译时静态检查，即检查 struct 成员类型和数据库表定义是否完全一致。
#[diesel(table_name = crate::schema::posts)]
#[diesel(check_for_backend(diesel::pg::Pg))]
pub struct Post {
    pub id: i32, // 默认表的 PK 是 id 字段。
    pub title: String,
    pub body: String,
    pub published: bool,
}

// 用于 diesel::insert_into(table).values() 的参数，需要实现 Insertable
// 如果使用 &str, 则需要为 struct 指定 lifetime
#[derive(Insertable)]
#[diesel(table_name = posts)]
pub struct NewPost<'a> {
    pub title: &'a str,
    pub body: &'a str,
}
let new_post = NewPost { title, body };
diesel::insert_into(posts::table)
    .values(&new_post)
    .returning(Post::as_returning()) // Selectable 可以作为 returning() 的参数
    .get_result(conn)
    .expect("Error saving new post");
let post = posts
    .find(post_id)
    .select(Post::as_select()) // Selectable 可以作为 select() 的参数
    .first(connection)
    .optional(); // This allows for returning an Option<Post>, otherwise it will throw an error


// 对于有缺省值的列，对应的数据库 schema 定义类似于 ~NOT NULL DEFAULT 'Green'~.
#[derive(Insertable)]
#[diesel(table_name = brands)]
struct NewBrand {
    color: Option<String>,  // Option<T> 表示该字段是可选的，在传入 None 时使用缺省值。
}
let new_brand = NewBrand { color: Some("Red".into()) };
diesel::insert_into(brands)
    .values(&new_brand)
    .execute(connection)
    .unwrap();

// Insert the default color
let new_brand = NewBrand { color: None }; // color 插入缺省值
diesel::insert_into(brands)
    .values(&new_brand)
    .execute(connection)
    .unwrap();


// 对于 Option<T> 类型的字段，如果在 insert 时值为 None，则 diesel 插入该字段的缺省值。
#[derive(Insertable)]
#[diesel(table_name = brands)]
struct NewBrand {
    color: Option<String>,
}

use schema::{users, posts};
#[derive(Debug, PartialEq, Queryable, Selectable)]
struct User {
    id: i32,
    name: String,
}
#[derive(Debug, PartialEq, Queryable, Selectable)]
struct Post {
    id: i32,
    user_id: i32,
    title: String,
}
let (first_user, first_post) = users::table
    .inner_join(posts::table)
    .select(<(User, Post)>::as_select()) // tuple 也实现了 Selectable
    .first(connection)?;


#[derive(Debug, PartialEq, Queryable, Selectable)]
struct User {
    id: i32,
    name: String,
}
#[derive(Debug, PartialEq, Queryable, Selectable)]
#[diesel(table_name = posts)]
struct PostTitle {
    title: String,
}
#[derive(Debug, PartialEq, Queryable, Selectable)]
struct UserPost {
    #[diesel(embed)]
    user: User, // 支持嵌入字段
    #[diesel(embed)]
    post_title: PostTitle,
}
let first_user_post = users::table
    .inner_join(posts::table)
    .select(UserPost::as_select())
    .first(connection)?;

let expected_user_post = UserPost {
    user: User {
        id: 1,
        name: "Sean".into(),
    },
    post_title: PostTitle {
        title: "My first post".into(),
    },
};
assert_eq!(expected_user_post, first_user_post);

// 示例: Identifiable 可以作为 update() 的参数, AsChangeset 可以作为 set() 的参数.  Identifiable
// 需要有作为 PK 的 id 字段.
#[derive(Queryable, Identifiable, AsChangeset)]
#[diesel(table_name = posts)]
pub struct Post {
    pub id: i64,
    pub title: String,
    pub body: String,
    pub draft: bool,
    pub publish_at: SystemTime,
    pub visit_count: i32,
}
diesel::update(post) // post 是上面的实现了 Identifiable 的 Post 类型值, 必须要设置 id 字段.
    .set(posts::draft.eq(false))
    .execute(conn)

update(post)
// 等效于
update(posts.find(post.id))
// 或
update(posts.filter(id.eq(post.id))).

    // Associations 关联表
    #[derive(Queryable, Identifiable, Selectable, Debug, PartialEq)]
#[diesel(table_name = books)]
pub struct Book {
    pub id: i32,
    pub title: String,
}
#[derive(Queryable, Selectable, Identifiable, Associations, Debug, PartialEq)]
#[diesel(belongs_to(Book, foreign_key = book_id))] // foreign_key 默认为 {table}_id 字段
#[diesel(table_name = pages)]
pub struct Page {
    pub id: i32,
    pub page_number: i32,
    pub content: String,
    pub book_id: i32,
}