创建表结构和插入记录等极其常见（基本上是不可避免的）任务可以通过在 Connection 对象上的特殊帮助/扩展方法来实现，通过 ConnectionExt 特性。这些特性反过来使用 Table 特性来准备和调用相应的 SQL 语句，并且可以方便地调用。

示例

绝对的基础 - 创建一个表，插入一些记录到其中，然后检索它们

use std::fmt::{self, Formatter};
use nanosql::{
    Result, Connection, ConnectionExt, Query,
    ToSql, FromSql, AsSqlTy, Param, ResultRecord, Table
};


/// This type is going to represent our table.
///
/// We derive the `Param` trait for it so that it can be used for
/// binding parameters to the statement when inserting new records,
/// and the `ResultRecord` trait so that we can use it to retrieve
/// results, too.
///
/// We also derive the `Table` trait so that basic operations such as
/// creating the table in the schema and bulk insertion can be performed
/// using the appropriate convenience methods in [`ConnectionExt`].
///
/// The parameter prefix is '$' by default (if not specified via the
/// param_prefix attribute); it may also be one of ':', '@', or '?',
/// the last one being allowed only for tuples and scalar parameters.
///
/// `#[nanosql(rename)]` on a struct renames the table itself, while
/// `#[nanosql(rename_all)]` applies a casing convention to all columns.
#[derive(Clone, PartialEq, Eq, Hash, Debug, Param, ResultRecord, Table)]
#[nanosql(param_prefix = '$')] // optional
#[nanosql(rename = "MyLittlePet", rename_all = "lowerCamelCase")]
struct Pet {
    /// If you don't like the default `AsSqlTy` impl for your column's
    /// type, you can specify a different one. Here we add a non-zero
    /// constraint, but the `id` remains a plain `i64` for convenience.
    ///
    /// You can also add additional `CHECK` constraints, if necessary.
    #[nanosql(sql_ty = core::num::NonZeroI64, check = "id <= 999999")]
    id: i64,
    /// You can apply a `UNIQUE` constraint to any field.
    #[nanosql(unique)]
    nick_name: String,
    /// You can also rename fields/columns one by one
    #[nanosql(rename = "type")]
    kind: PetKind,
}

/// Collective and field-level casing/renaming also works with `enum`s
#[derive(Clone, Copy, PartialEq, Eq, Hash, Debug, ToSql, FromSql, AsSqlTy)]
#[nanosql(rename_all = "UPPER_SNAKE_CASE")]
enum PetKind {
    Dog,
    #[nanosql(rename = "KITTEN")]
    Cat,
    Fish,
}

/// Our first custom query retrieves a pet by its unique ID.
///
/// If you don't want to spell out the impl by hand, you can
/// use the `define_query!{}` macro for a shorter incantation.
struct PetById;

impl Query for PetById {
    /// The type of the parameter(s). This can be a single scalar, a tuple,
    /// a tuple struct of scalars, or a struct with named fields of scalar types.
    type Input<'p> = i64;

    /// The return type of a query can be either a scalar, a single record (struct or
    /// tuple), or an optional of a scalar/record (when it returns either 0 or 1 rows),
    /// or a collection of arbitrarily many scalars/records. Here we choose an `Option`,
    /// because a given ID corresponds to at most one `Pet`.
    type Output = Option<Pet>;

    /// Finally, we create the actual SQL query.
    fn format_sql(&self, formatter: &mut Formatter<'_>) -> fmt::Result {
        formatter.write_str("SELECT id, nickName, type FROM MyLittlePet WHERE id = ?")
    }
}

fn main() -> Result<()> {
    // First, we open a database connection.
    let mut conn = Connection::connect_in_memory()?;
    
    // Then, we ensure that the table exists in the schema.
    conn.create_table::<Pet>()?;

    // Next, we insert a couple of records so we have test data to work on.
    conn.insert_batch([
        Pet {
            id: 1,
            nick_name: "Fluffy".into(),
            kind: PetKind::Dog,
        },
        Pet {
            id: 2,
            nick_name: "Hello Kitty".into(),
            kind: PetKind::Cat,
        },
        Pet {
            id: 3,
            nick_name: "Nemo".into(),
            kind: PetKind::Fish,
        },
    ])?;

    // We then compile the query into a prepared statement.
    let mut stmt = conn.compile(PetById)?;

    // Finally, we execute the compiled statement, passing parameters, and retrieve the results.
    let result = stmt.invoke(3)?;
    assert_eq!(result, Some(Pet { 
        id: 3,
        nick_name: "Nemo".into(),
        kind: PetKind::Fish,
    }));

    // We can re-use the statement and execute it multiple times
    let result = stmt.invoke(99)?;
    assert_eq!(result, None);

    drop(stmt);

    // Inserting a pet with id = 0 should fail due to the `#[nanosql(sql_ty = ...)]` attribute.
    let insert_id_0_result = conn.insert_batch([
        Pet {
            id: 0,
            nick_name: "Error".into(),
            kind: PetKind::Cat,
        }
    ]);
    assert!(insert_id_0_result.is_err(), "id = 0 violates NonZeroI64's CHECK constraint");

    // Inserting a pet with a high ID is expected to fail due to the CHECK constraint.
    let insert_id_high_result = conn.insert_batch([
        Pet {
            id: 1000000,
            nick_name: "this is unique".into(),
            kind: PetKind::Dog,
        }
    ]);
    assert!(insert_id_high_result.is_err(), "id = 1000000 violates extra CHECK constraint");

    // Inserting a pet with a duplicate name is not allowed due to `#[nanosql(unique)]`.
    let insert_dup_name_result = conn.insert_batch([
        Pet {
            id: 137731,
            nick_name: "Hello Kitty".into(),
            kind: PetKind::Dog,
        }
    ]);
    assert!(insert_dup_name_result.is_err(), "duplicate name violates uniqueness constraint");

    Ok(())
}

查看 nanosql/examples 目录（特别是 realistic.rs）以获取更高级和有趣的示例。

关于批量插入和事务的说明

[ConnectionExt::insert_batch()] 方法将插入语句包装在事务中以提高性能。事务的排他性在 rusqlite 中通过在事务期间可变地（唯一地）借用 Connection 对象在类型级别上建模。这意味着 insert_batch() 也需要可变借用。然而，准备和调用查询需要一个不可变借用，而预定义的语句在其存在期间借用 Connection。因此，在混合批量插入和其他查询时，您可能会遇到“不能可变借用 connection 因为它也被不可变借用”的错误。解决这个问题的有两个基本方法

1. 在调用 [ConnectionExt::insert_batch()] 之前删除挂起的预定义语句；
2. 或者如果您不能这样做，那么使用 [Connection::unchecked_transaction()] 获取一个在编译时不会被检查排他性的事务对象，然后在该 事务对象 上调用不可变借用的 [TransactionExt::insert_batch()] 方法。

Cargo 功能

• derive：激活过程宏 - 主要用于常用特性的自定义 #[derive]。默认启用。
• expr-check：使用 sqlparser crate 在编译时检查 derive 宏属性中原始 SQL 表达式的语法错误。这确保生成的 SQL 将是有效的，并且在用户提供的 SQL 代码中存在的语法错误将被清楚地指出，而不是在运行时导致神秘的语句准备错误。默认启用。
• not-nan：为 ordered_float::NotNan 实现 Param 和 ResultRecord。这允许在查询中具有更安全的类型接口：由于 SQLite 将 NaN 视为 SQL 的 NULL 值，当绑定或检索 f32::NAN 或 f64::NAN 以及相应的参数需要是 NOT NULL 时，或者源列 可能 是 NULL，你可能会遇到意外的错误。
• pretty-eqp：使用 ptree crate 来美化打印 EXPLAIN QUERY PLAN 的结果。这将实现 Display 对于 QueryPlan，这是 [ConnectionExt::explain_query_plan()] 的返回类型，它使用 ASCII 艺术以美观、可读的格式呈现树。

cargo clean
cargo test --workspace --all-features