From bc0afe2353f925124c67bf5785813432de44af57 Mon Sep 17 00:00:00 2001
From: Teddy Wing
Date: Sat, 2 Apr 2022 20:35:28 +0200
Subject: Add a SQL update function to update a record from YAML
Add a new `db_update()` function to update an existing database record
from a YAML hash.
Had a little trouble building up the params for the prepared SQL
statement but finally managed to set up the types correctly and include
the record ID to update. Thanks to this Stack Overflow answer by
Shepmaster (https://stackoverflow.com/users/155423/shepmaster) for
pointing me in the right direction:
https://stackoverflow.com/questions/46624591/cannot-call-rusqlites-query-because-it-expects-the-type-rusqlitetypestos/46625932#46625932
---
src/lib.rs | 2 +
src/update.rs | 143 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
src/yaml.rs | 72 +++++++++++++++++++++++++++++
3 files changed, 217 insertions(+)
create mode 100644 src/update.rs
(limited to 'src')
diff --git a/src/lib.rs b/src/lib.rs
index 10240fa..910aa2c 100644
--- a/src/lib.rs
+++ b/src/lib.rs
@@ -18,11 +18,13 @@
pub mod insert;
pub mod select;
pub mod sqlite;
+pub mod update;
pub mod yaml;
pub use insert::*;
pub use select::*;
+pub use update::*;
/// Yaqlite errors.
diff --git a/src/update.rs b/src/update.rs
new file mode 100644
index 0000000..ebed217
--- /dev/null
+++ b/src/update.rs
@@ -0,0 +1,143 @@
+// Copyright (c) 2022 Teddy Wing
+//
+// This file is part of Yaqlite.
+//
+// Yaqlite is free software: you can redistribute it and/or modify it
+// under the terms of the GNU General Public License as published by
+// the Free Software Foundation, either version 3 of the License, or
+// (at your option) any later version.
+//
+// Yaqlite is distributed in the hope that it will be useful, but
+// WITHOUT ANY WARRANTY; without even the implied warranty of
+// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+// General Public License for more details.
+//
+// You should have received a copy of the GNU General Public License
+// along with Yaqlite. If not, see .
+
+/// Update a YAML record in the given database.
+pub fn update(
+ dbconn: &mut rusqlite::Connection,
+ table_name: &str,
+ record_id: &str,
+ data: &mut yaml_rust::Yaml,
+) -> Result<(), crate::Error> {
+ let table_columns = crate::sqlite::get_column_names(&dbconn, table_name)?;
+
+ let tx = dbconn.transaction()?;
+
+ crate::yaml::db_update(
+ data,
+ &tx,
+ &table_name,
+ &table_columns,
+ // TODO: dynamic or user-supplied
+ "id",
+ record_id,
+ )?;
+
+ tx.commit()?;
+
+ Ok(())
+}
+
+
+#[cfg(test)]
+mod tests {
+ use super::*;
+
+ #[test]
+ fn updates_database_record_from_yaml() {
+ #[derive(Debug, PartialEq)]
+ struct TestRecord {
+ id: i8,
+ count: i16,
+ weight: f32,
+ description: String,
+ }
+
+ let mut conn = rusqlite::Connection::open_in_memory().unwrap();
+
+ conn.execute(
+ r#"
+ CREATE TABLE "test" (
+ id INTEGER PRIMARY KEY,
+ count INTEGER,
+ weight REAL,
+ description TEXT
+ );
+ "#,
+ [],
+ ).unwrap();
+
+ {
+ let mut stmt = conn.prepare(r#"
+ INSERT INTO "test"
+ (count, weight, description)
+ VALUES
+ (?, ?, ?);
+ "#).unwrap();
+
+ stmt.insert(
+ rusqlite::params![
+ 55_i16,
+ 0.8_f32,
+ "Ounces or grams?",
+ ],
+ ).unwrap();
+ }
+
+ let expected = TestRecord {
+ id: 1,
+ count: 28,
+ weight: 1.2,
+ description: r#"This is a multiline
+
+description."#.to_owned(),
+ };
+
+ let mut yaml_data = yaml_rust::YamlLoader::load_from_str(
+ &format!(
+r#"count: {}
+weight: {}
+description: |-
+ This is a multiline
+
+ description.
+"#,
+ expected.count,
+ expected.weight,
+ ),
+ ).unwrap();
+ let mut yaml_record = yaml_data.get_mut(0).unwrap();
+
+ update(&mut conn, "test", "1", &mut yaml_record).unwrap();
+
+ {
+ let mut stmt = conn.prepare(r#"
+ SELECT
+ "id", "count", "weight", "description"
+ FROM "test"
+ WHERE "id" = 1;
+ "#).unwrap();
+
+ let got = stmt.query_row(
+ [],
+ |row| {
+ Ok(
+ TestRecord {
+ id: row.get(0).unwrap(),
+ count: row.get(1).unwrap(),
+ weight: row.get(2).unwrap(),
+ description: row.get(3).unwrap(),
+ }
+ )
+ }
+ ).unwrap();
+
+ assert_eq!(expected, got);
+ }
+
+ conn.close().unwrap();
+ }
+}
diff --git a/src/yaml.rs b/src/yaml.rs
index acf1a9e..d1108f1 100644
--- a/src/yaml.rs
+++ b/src/yaml.rs
@@ -74,6 +74,78 @@ pub fn db_insert(
)
}
+/// TODO
+pub fn db_update(
+ doc: &mut yaml::Yaml,
+ tx: &rusqlite::Transaction,
+ table_name: &str,
+ table_columns: &HashSet,
+ primary_key_column: &str,
+ record_id: &str,
+) -> Result<(), crate::Error> {
+ with_hash(
+ doc,
+ &mut |hash| {
+ use std::borrow::Cow;
+
+ hash_filter_table_columns(hash, &table_columns);
+
+ let mut stmt = tx.prepare(
+ &format!(
+ r#"
+ UPDATE "{}"
+ SET
+ {}
+ WHERE {} = ?;
+ "#,
+ table_name,
+
+ // List of:
+ // "column_name" = ?,
+ // "column_name" = ?
+ hash.keys()
+ .map(|k| k.as_str())
+ .filter(|k| k.is_some())
+
+ // Always `Some`.
+ .map(|k| format!(r#""{}" = ?"#, k.unwrap()))
+ .collect::>()
+ .join(", "),
+
+ primary_key_column,
+ ),
+ )?;
+
+ // TODO: convert to &[&dyn ToSql] ?
+ // let values = hash.values().map(|v| Yaml(Cow::Borrowed(v)));
+ // values.push(primary_key);
+ // stmt.execute(rusqlite::params_from_iter(values))?;
+
+ // let values: Vec<&dyn rusqlite::ToSql>;
+ //
+ // for v in hash.values() {
+ // values.push(&Yaml(Cow::Borrowed(v)));
+ // }
+ //
+ // stmt.execute(values)?;
+
+ // let values: dyn Iterator- = hash.values();
+ // values = values.map(|v| Yaml(Cow::Borrowed(v)));
+ // values.chain(&[&primary_key]);
+ // stmt.execute(rusqlite::params_from_iter(values))?;
+
+ let mut values: Vec<_> = hash.values()
+ .map(|v| Yaml(Cow::Borrowed(v)))
+ .map(|v| Box::new(v) as Box)
+ .collect();
+ values.push(Box::new(record_id));
+ stmt.execute(rusqlite::params_from_iter(values))?;
+
+ Ok(())
+ }
+ )
+}
+
/// Parse a YAML document and run a function for all hashes in the document.
fn with_hash(
doc: &mut yaml::Yaml,
--
cgit v1.2.3