Repository: njord-rs/njord (language: rust)

Issue:
Add support for subqueries
SELECT, INSERT, UPDATE, and DELETE statements.


Discussion / hints:

Fix patch:
diff --git a/njord/src/column.rs b/njord/src/column.rs
new file mode 100644
index 00000000..426eab26
--- /dev/null
+++ b/njord/src/column.rs
@@ -0,0 +1,53 @@
+use crate::{sqlite::select::SelectQueryBuilder, table::Table};
+
+/// Define the enum to represent a column as either a String or SelectQueryBuilder
+#[derive(Clone)]
+pub enum Column<'a, T: Table + Default> {
+    Text(String),
+    SubQuery(SelectQueryBuilder<'a, T>),
+}
+
+// Implement the build method to convert the enum to a string
+impl<'a, T: Table + Default> Column<'a, T> {
+    /// Helper function to convert the columns to a string
+    pub fn build(&self) -> String {
+        match self {
+            Column::Text(text) => text.clone(),
+            Column::SubQuery(sub_query) => "(".to_string() + &sub_query.build_query() + ")",
+        }
+    }
+}
+
+// Implement fmt::Display for Column
+impl<'a, T: Table + Default> std::fmt::Display for Column<'a, T> {
+    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
+        write!(f, "{}", self.build())
+    }
+}
+
+// Implement PartialEq for Column
+impl<'a, T: Table + Default> PartialEq for Column<'a, T> {
+    fn eq(&self, other: &Self) -> bool {
+        self.build() == other.build()
+    }
+}
+
+// Implement PartialEq<String> for Column
+impl<'a, T: Table + Default> PartialEq<String> for Column<'a, T> {
+    fn eq(&self, other: &String) -> bool {
+        match self {
+            Column::Text(text) => text == other,
+            Column::SubQuery(sub_query) => sub_query.build_query() == *other,
+        }
+    }
+}
+
+// You can also implement PartialEq<&str> for convenience
+impl<'a, T: Table + Default> PartialEq<&str> for Column<'a, T> {
+    fn eq(&self, other: &&str) -> bool {
+        match self {
+            Column::Text(text) => text == other,
+            Column::SubQuery(sub_query) => sub_query.build_query() == *other,
+        }
+    }
+}
diff --git a/njord/src/keys.rs b/njord/src/keys.rs
index 1ae42cd5..0e0c5c2a 100644
--- a/njord/src/keys.rs
+++ b/njord/src/keys.rs
@@ -38,7 +38,7 @@ use serde::{Deserialize, Deserializer};
 #[derive(Debug)]
 pub struct PrimaryKey<T>(T);
 
-#[derive(Debug)]
+#[derive(Debug, Clone)]
 pub struct AutoIncrementPrimaryKey<T>(Option<T>);
 
 impl<T> PrimaryKey<T> {
diff --git a/njord/src/lib.rs b/njord/src/lib.rs
index 445925a2..3f21140f 100644
--- a/njord/src/lib.rs
+++ b/njord/src/lib.rs
@@ -27,9 +27,10 @@
 //! OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
 //! OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 
+pub mod column;
 pub mod condition;
+pub mod keys;
 #[cfg(feature = "sqlite")]
 pub mod sqlite;
 pub mod table;
 pub mod util;
-pub mod keys;
\ No newline at end of file
diff --git a/njord/src/sqlite/select.rs b/njord/src/sqlite/select.rs
index 5c747cd8..f5ac8e4e 100644
--- a/njord/src/sqlite/select.rs
+++ b/njord/src/sqlite/select.rs
@@ -28,6 +28,7 @@
 //! OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 
 use crate::{
+    column::Column,
     condition::Condition,
     sqlite::util::{
         generate_group_by_str, generate_having_str, generate_limit_str, generate_offset_str,
@@ -43,24 +44,6 @@ use rusqlite::types::Value;
 
 use crate::table::Table;
 
-/// Define the enum to represent a column as either a String or SelectQueryBuilder
-/// TODO: Implement clone
-pub enum Column<'a, T: Table + Default> {
-    Text(String),
-    SubQuery(SelectQueryBuilder<'a, T>),
-}
-
-// Implement the build method to convert the enum to a string
-impl<'a, T: Table + Default> Column<'a, T> {
-    /// Helper function to convert the columns to a string
-    pub fn build(&self) -> String {
-        match self {
-            Column::Text(text) => text.clone(),
-            Column::SubQuery(sub_query) => "(".to_string() + &sub_query.build_query() + ")",
-        }
-    }
-}
-
 /// Constructs a new SELECT query builder.
 ///
 /// # Arguments
@@ -79,6 +62,7 @@ pub fn select<'a, T: Table + Default>(
 }
 
 /// A builder for constructing SELECT queries.
+#[derive(Clone)]
 pub struct SelectQueryBuilder<'a, T: Table + Default> {
     conn: &'a Connection,
     table: Option<T>,
@@ -251,7 +235,7 @@ impl<'a, T: Table + Default> SelectQueryBuilder<'a, T> {
     }
 
     /// Builds the query string, this function should be used internally.
-    fn build_query(&self) -> String {
+    pub fn build_query(&self) -> String {
         let columns_str = self
             .columns
             .iter()
diff --git a/njord/src/sqlite/update.rs b/njord/src/sqlite/update.rs
index 6a425fd2..851d9983 100644
--- a/njord/src/sqlite/update.rs
+++ b/njord/src/sqlite/update.rs
@@ -43,6 +43,8 @@ use log::info;
 
 use crate::table::Table;
 
+use super::select::SelectQueryBuilder;
+
 /// Constructs a new UPDATE query builder.
 ///
 /// # Arguments
@@ -53,33 +55,35 @@ use crate::table::Table;
 /// # Returns
 ///
 /// An `UpdateQueryBuilder` instance.
-pub fn update<T: Table + Default>(conn: Connection, table: T) -> UpdateQueryBuilder<T> {
+pub fn update<'a, T: Table + Default>(conn: &'a Connection, table: T) -> UpdateQueryBuilder<'a, T> {
     UpdateQueryBuilder::new(conn, table)
 }
 
 /// A builder for constructing UPDATE queries.
-pub struct UpdateQueryBuilder<T: Table + Default> {
-    conn: Connection,
+pub struct UpdateQueryBuilder<'a, T: Table + Default> {
+    conn: &'a Connection,
     table: Option<T>,
     columns: Vec<String>,
+    sub_queries: HashMap<String, SelectQueryBuilder<'a, T>>,
     where_condition: Option<Condition>,
     order_by: Option<HashMap<Vec<String>, String>>,
     limit: Option<usize>,
     offset: Option<usize>,
 }
 
-impl<T: Table + Default> UpdateQueryBuilder<T> {
+impl<'a, T: Table + Default> UpdateQueryBuilder<'a, T> {
     /// Creates a new `UpdateQueryBuilder` instance.
     ///
     /// # Arguments
     ///
     /// * `conn` - A `rusqlite::Connection` to the SQLite database.
     /// * `table` - An instance of the table to be updated.
-    pub fn new(conn: Connection, table: T) -> Self {
+    pub fn new(conn: &'a Connection, table: T) -> Self {
         UpdateQueryBuilder {
             conn,
             table: Some(table),
             columns: Vec::new(),
+            sub_queries: HashMap::new(),
             where_condition: None,
             order_by: None,
             limit: None,
@@ -97,6 +101,16 @@ impl<T: Table + Default> UpdateQueryBuilder<T> {
         self
     }
 
+    /// Sets the columns and values (as subquery) to be updated.
+    ///
+    /// # Arguments
+    ///     
+    /// * `columns` - A hashmap representing the columns and their subqueries.
+    pub fn set_subqueries(mut self, columns: HashMap<String, SelectQueryBuilder<'a, T>>) -> Self {
+        self.sub_queries = columns;
+        self
+    }
+
     /// Sets the WHERE clause condition.
     ///
     /// # Arguments
@@ -160,7 +174,7 @@ impl<T: Table + Default> UpdateQueryBuilder<T> {
 
             for column in &self.columns {
                 // Check if column exists in the table's fields
-                if let Some(index) = fields.iter().position(|c| c == column) {
+                if let Some(index) = fields.iter().position(|c| column == c) {
                     let value = values.get(index).cloned().unwrap_or_default();
                     let formatted_value = if value.is_empty() {
                         "NULL".to_string()
@@ -176,6 +190,12 @@ impl<T: Table + Default> UpdateQueryBuilder<T> {
                 }
             }
 
+            // Generate subqueries
+            for (column_name, sub_query) in &self.sub_queries {
+                let formatted_value = format!("({})", sub_query.build_query());
+                set_fields.push(format!("{} = {}", column_name, formatted_value));
+            }
+
             set_fields.join(", ")
         } else {
             String::new()


Test patch:
diff --git a/njord/tests/sqlite_test.rs b/njord/tests/sqlite_test.rs
index 0d42b3f3..29375cc1 100644
--- a/njord/tests/sqlite_test.rs
+++ b/njord/tests/sqlite_test.rs
@@ -1,15 +1,16 @@
 // integrations tests for sqlite
 
+use njord::column::Column;
 use njord::condition::Condition;
 use njord::keys::{AutoIncrementPrimaryKey, PrimaryKey};
-use njord::sqlite::select::Column;
+use njord::sqlite::select::SelectQueryBuilder;
 use njord::sqlite::{self};
 use njord::table::Table;
 use njord_derive::Table;
 use std::collections::HashMap;
 use std::path::Path;
 
-#[derive(Table)]
+#[derive(Table, Clone)]
 #[table_name = "users"]
 pub struct User {
     id: AutoIncrementPrimaryKey<usize>,
@@ -95,7 +96,7 @@ fn update() {
     let db_path = Path::new(&db_relative_path);
     let conn = sqlite::open(db_path);
 
-    let columns: Vec<String> = vec!["address".to_string()];
+    let columns = vec!["username".to_string()];
 
     let condition = Condition::Eq("username".to_string(), "mjovanc".to_string());
 
@@ -111,7 +112,7 @@ fn update() {
 
     match conn {
         Ok(c) => {
-            let result = sqlite::update(c, table_row)
+            let result = sqlite::update(&c, table_row)
                 .set(columns)
                 .where_clause(condition)
                 .order_by(order)
@@ -379,20 +380,6 @@ fn select_except() {
         Column::Text("address".to_string()),
     ];
 
-    let columns2 = vec![
-        Column::Text("id".to_string()),
-        Column::Text("username".to_string()),
-        Column::Text("email".to_string()),
-        Column::Text("address".to_string()),
-    ];
-
-    let columns3 = vec![
-        Column::Text("id".to_string()),
-        Column::Text("username".to_string()),
-        Column::Text("email".to_string()),
-        Column::Text("address".to_string()),
-    ];
-
     let condition1 = Condition::Eq("username".to_string(), "mjovanc".to_string());
     let condition2 = Condition::Eq("username".to_string(), "otheruser".to_string());
     let condition3 = Condition::Eq("username".to_string(), "anotheruser".to_string());
@@ -400,15 +387,15 @@ fn select_except() {
     match conn {
         Ok(c) => {
             // Create a new connection for each query builder
-            let query1 = sqlite::select(&c, columns)
+            let query1 = sqlite::select(&c, columns.clone())
                 .from(User::default())
                 .where_clause(condition1);
 
-            let query2 = sqlite::select(&c, columns2)
+            let query2 = sqlite::select(&c, columns.clone())
                 .from(User::default())
                 .where_clause(condition2);
 
-            let query3 = sqlite::select(&c, columns3)
+            let query3 = sqlite::select(&c, columns.clone())
                 .from(User::default())
                 .where_clause(condition3);
 
@@ -438,12 +425,6 @@ fn select_union() {
         Column::Text("email".to_string()),
         Column::Text("address".to_string()),
     ];
-    let columns2 = vec![
-        Column::Text("id".to_string()),
-        Column::Text("username".to_string()),
-        Column::Text("email".to_string()),
-        Column::Text("address".to_string()),
-    ];
 
     let condition1 = Condition::Eq("id".to_string(), 42.to_string());
     let condition2 = Condition::Eq("id".to_string(), 43.to_string());
@@ -451,11 +432,11 @@ fn select_union() {
     match conn {
         Ok(c) => {
             // Create a new connection for each query builder
-            let query1 = sqlite::select(&c, columns)
+            let query1 = sqlite::select(&c, columns.clone())
                 .from(User::default())
                 .where_clause(condition1);
 
-            let query2 = sqlite::select(&c, columns2)
+            let query2 = sqlite::select(&c, columns.clone())
                 .from(User::default())
                 .where_clause(condition2);
 
@@ -518,3 +499,43 @@ fn select_sub_queries() {
         Err(e) => panic!("Failed to SELECT: {:?}", e),
     };
 }
+
+#[test]
+fn update_with_sub_queries() {
+    let db_relative_path = "./db/update.db";
+    let db_path = Path::new(&db_relative_path);
+    let conn = sqlite::open(db_path);
+
+    let table_row: User = User {
+        id: AutoIncrementPrimaryKey::<usize>::new(Some(0)),
+        username: "mjovanc".to_string(),
+        email: "mjovanc@icloud.com".to_string(),
+        address: "Some Random Address 1".to_string(),
+    };
+
+    let columns = vec!["username".to_string()];
+
+    match conn {
+        Ok(c) => {
+            let sub_query = SelectQueryBuilder::new(&c, vec![Column::Text("email".to_string())])
+                .from(User::default())
+                .where_clause(Condition::Eq(
+                    "email".to_string(),
+                    "mjovanc@icloud.com".to_string(),
+                ))
+                .limit(1);
+
+            let set_subqueries = HashMap::from([("email".to_string(), sub_query)]);
+
+            let result = sqlite::update(&c, table_row)
+                .set(columns)
+                .set_subqueries(set_subqueries)
+                .where_clause(Condition::Eq("username".to_owned(), "otheruser".to_owned()))
+                .build();
+
+            println!("{:?}", result);
+            assert!(result.is_ok());
+        }
+        Err(e) => panic!("Failed to UPDATE: {:?}", e),
+    };
+}