Struct Insert

Source

pub struct Insert<T> { /* private fields */ }

Expand description

An INSERT statement for adding rows to a table.

See the SQLite docs for details.

#[derive(Clone, PartialEq, Eq, Hash, Debug, Table, Param, ResultRecord)]
struct Food {
    #[nanosql(unique)]
    name: String,
    sugar: u16,
    energy: u32,
}

let inserted_foods = [
    Food { name: "beef".into(), sugar: 0, energy: 250 },
    Food { name: "fish".into(), sugar: 0, energy: 200 },
    Food { name: "chocolate".into(), sugar: 61, energy: 545 },
];
let mut conn = Connection::connect_in_memory()?;
conn.create_table::<Food>()?;

let returned_foods = conn.insert_or_ignore_batch(inserted_foods.clone())?;
assert!(returned_foods.iter().flatten().eq(&inserted_foods));

// convenience query for retrieving the properties of the "fish" record
define_query! {
    GetFish<'lt>: () => Single<Food> {
        r#"
        SELECT name AS name, sugar AS sugar, energy AS energy
        FROM food
        WHERE name = 'fish'
        "#
    }
}
// Try to insert a duplicate using various conflict resolution algorithms,
// applicable when PRIMARY KEY or UNIQUE constraints are violated.

// The default is ABORT: duplicates cause an error, the DB is not modified.
let result = conn.compile_invoke(
    Insert::<Food>::new(),
    Food { name: "fish".into(), sugar: 13, energy: 37 },
);
assert!(matches!(
    result,
    Err(Error::Sqlite(SqliteError::SqliteFailure(
        FfiError {
            code: ErrorCode::ConstraintViolation,
            ..
        },
        _
    )))
));
assert_eq!(
    conn.compile_invoke(GetFish, ())?,
    Single(Food { name: "fish".into(), sugar: 0, energy: 200 }),
);

// IGNORE means no error, but the database is still not modified.
let entity = conn.compile_invoke(
    Insert::<Food>::or_ignore(),
    Food { name: "fish".into(), sugar: 14, energy: 38 },
)?;
assert_eq!(entity, None);
assert_eq!(
    conn.compile_invoke(GetFish, ())?,
    Single(Food { name: "fish".into(), sugar: 0, energy: 200 }),
);

// REPLACE means update the other fields of the conflicting row.
let entity = conn.compile_invoke(
    Insert::<Food>::or_replace(),
    Food { name: "fish".into(), sugar: 15, energy: 39 },
)?;
assert_eq!(
    entity,
    Some(Food { name: "fish".into(), sugar: 15, energy: 39 }),
);
assert_eq!(
    conn.compile_invoke(GetFish, ())?,
    Single(Food { name: "fish".into(), sugar: 15, energy: 39 }),
);

// INSERT OR IGNORE also works via a convenience extension method on `Connection`
let entity = conn.insert_or_ignore_one(Food {
    name: "fish".into(),
    sugar: 16,
    energy: 49,
})?;
assert_eq!(entity, None);
assert_eq!(
    conn.compile_invoke(GetFish, ())?,
    Single(Food { name: "fish".into(), sugar: 15, energy: 39 }),
);

let entity = conn.insert_or_ignore_one(Food {
    name: "plum".into(),
    sugar: 43,
    energy: 129,
})?;
assert_eq!(
    entity,
    Some(Food { name: "plum".into(), sugar: 43, energy: 129 })
);

// replacing also works using a convenience method on connections
let replaced = conn.insert_or_replace_one(Food {
    name: "fish".into(),
    sugar: 17,
    energy: 64,
})?;
assert_eq!(
    replaced,
    Food { name: "fish".into(), sugar: 17, energy: 64 },
);
assert_eq!(
    conn.compile_invoke(GetFish, ())?,
    Single(replaced)
);