parsql_sqlite/
crud_ops.rs

1use rusqlite::{types::FromSql, Error, Row, ToSql};
2
3use crate::traits::{CrudOps, FromRow, SqlCommand, SqlParams, SqlQuery, UpdateParams};
4
5// CrudOps trait implementasyonu rusqlite::Connection için
6impl CrudOps for rusqlite::Connection {
7    fn insert<T: SqlCommand + SqlParams, P: for<'a> FromSql + Send + Sync>(
8        &self,
9        entity: T,
10    ) -> Result<P, Error> {
11        insert(self, entity)
12    }
13
14    fn update<T: SqlCommand + UpdateParams>(&self, entity: T) -> Result<usize, Error> {
15        update(self, entity)
16    }
17
18    fn delete<T: SqlCommand + SqlParams>(&self, entity: T) -> Result<usize, Error> {
19        delete(self, entity)
20    }
21
22    fn fetch<P, R>(&self, params: &P) -> Result<R, Error>
23    where
24        P: SqlQuery<R> + SqlParams,
25        R: FromRow,
26    {
27        fetch(self, params)
28    }
29
30    fn fetch_all<P, R>(&self, params: &P) -> Result<Vec<R>, Error>
31    where
32        P: SqlQuery<R> + SqlParams,
33        R: FromRow,
34    {
35        fetch_all(self, params)
36    }
37
38    fn select<T: SqlQuery<T> + SqlParams, F, R>(&self, entity: &T, to_model: F) -> Result<R, Error>
39    where
40        F: Fn(&Row) -> Result<R, Error>,
41    {
42        let sql = T::query();
43        if std::env::var("PARSQL_TRACE").unwrap_or_default() == "1" {
44            println!("[PARSQL-SQLITE] Execute SQL: {}", sql);
45        }
46
47        let params = entity.params();
48        let param_refs: Vec<&dyn ToSql> = params.iter().map(|p| *p as &dyn ToSql).collect();
49        self.query_row(&sql, param_refs.as_slice(), to_model)
50    }
51
52    fn select_all<T: SqlQuery<T> + SqlParams, F, R>(
53        &self,
54        entity: &T,
55        to_model: F,
56    ) -> Result<Vec<R>, Error>
57    where
58        F: Fn(&Row) -> Result<R, Error>,
59    {
60        let sql = T::query();
61        if std::env::var("PARSQL_TRACE").unwrap_or_default() == "1" {
62            println!("[PARSQL-SQLITE] Execute SQL: {}", sql);
63        }
64
65        let params = entity.params();
66        let param_refs: Vec<&dyn ToSql> = params.iter().map(|p| *p as &dyn ToSql).collect();
67        let mut stmt = self.prepare(&sql)?;
68        let rows = stmt.query_map(param_refs.as_slice(), to_model)?;
69
70        let mut results = Vec::new();
71        for row in rows {
72            results.push(row?);
73        }
74
75        Ok(results)
76    }
77}
78
79/// # insert
80///
81/// Inserts a new record into the SQLite database.
82///
83/// ## Parameters
84/// - `conn`: SQLite database connection
85/// - `entity`: Data object to be inserted (must implement SqlCommand and SqlParams traits)
86///
87/// ## Return Value
88/// - `Result<usize, rusqlite::Error>`: On success, returns the number of inserted records; on failure, returns Error
89///
90/// ## Struct Definition
91/// Structs used with this function should be annotated with the following derive macros:
92///
93/// ```rust,no_run
94/// #[derive(Insertable, SqlParams)]  // Required macros
95/// #[table("table_name")]            // Table name to insert into
96/// pub struct MyEntity {
97///     pub field1: String,
98///     pub field2: i32,
99///     // ...
100/// }
101/// ```
102///
103/// - `Insertable`: Automatically generates SQL INSERT statements
104/// - `SqlParams`: Automatically generates SQL parameters
105/// - `#[table("table_name")]`: Specifies the table name for the insertion
106///
107/// ## Example Usage
108///
109/// ```rust,no_run
110/// use rusqlite::{Connection, Result};
111/// use parsql_macros::{Insertable, SqlParams};
112/// use parsql_sqlite::insert;
113///
114/// fn main() -> Result<()> {
115///     // Create a database connection
116///     let conn = Connection::open("test.db")?;
117///
118///     // Create the table
119///     conn.execute("CREATE TABLE users (name TEXT, email TEXT, state INTEGER)", [])?;
120///
121///     // Define your entity with appropriate macros
122///     #[derive(Insertable, SqlParams)]
123///     #[table("users")]
124///     pub struct InsertUser {
125///         pub name: String,
126///         pub email: String,
127///         pub state: i16,
128///     }
129///
130///     // Create a new instance of your entity
131///     let insert_user = InsertUser {
132///         name: "John".to_string(),
133///         email: "john@example.com".to_string(),
134///         state: 1,
135///     };
136///
137///     // Insert into database
138///     let insert_result = insert(&conn, insert_user)?;
139///     println!("Insert result: {:?}", insert_result);
140///     Ok(())
141/// }
142/// ```
143pub fn insert<T: SqlCommand + SqlParams, P: for<'a> FromSql + Send + Sync>(
144    conn: &rusqlite::Connection,
145    entity: T,
146) -> Result<P, rusqlite::Error> {
147    let sql = T::query();
148    if std::env::var("PARSQL_TRACE").unwrap_or_default() == "1" {
149        println!("[PARSQL-SQLITE] Execute SQL: {}", sql);
150    }
151
152    let params = entity.params();
153    let param_refs: Vec<&dyn ToSql> = params.iter().map(|p| *p as &dyn ToSql).collect();
154
155    // Check if the SQL contains RETURNING clause
156    if sql.to_uppercase().contains("RETURNING") {
157        // Use query_row for RETURNING statements
158        conn.query_row(&sql, param_refs.as_slice(), |row| {
159            P::column_result(row.get_ref(0)?).map_err(|e| {
160                rusqlite::Error::FromSqlConversionFailure(
161                    0,
162                    rusqlite::types::Type::Integer,
163                    Box::new(e),
164                )
165            })
166        })
167    } else {
168        // Use execute for regular INSERT statements
169        conn.execute(&sql, param_refs.as_slice())?;
170
171        // Get the last inserted ID and use FromSql to convert it
172        let last_id = conn.last_insert_rowid();
173        P::column_result(rusqlite::types::ValueRef::Integer(last_id)).map_err(|e| {
174            rusqlite::Error::FromSqlConversionFailure(
175                0,
176                rusqlite::types::Type::Integer,
177                Box::new(e),
178            )
179        })
180    }
181}
182
183/// # update
184///
185/// Updates a record in the database.
186///
187/// ## Parameters
188/// - `conn`: SQLite database connection
189/// - `entity`: The entity to update (must implement SqlCommand and UpdateParams traits)
190///
191/// ## Return Value
192/// - `Result<usize, Error>`: On success, returns the number of rows affected; on failure, returns Error
193///
194/// ## Struct Definition
195/// Structs used with this function should be annotated with the following derive macros:
196///
197/// ```rust,no_run
198/// use parsql_macros::{Updateable, UpdateParams};
199///
200/// #[derive(Updateable, UpdateParams)]  // Required macros
201/// #[table("table_name")]              // Table name to update
202/// #[update("field1, field2")]         // Fields to update
203/// #[where_clause("id = ?")]           // Update condition
204/// pub struct MyEntity {
205///     pub id: i64,                    // Field used in the where clause
206///     pub field1: String,             // Fields to update
207///     pub field2: i32,
208/// }
209/// ```
210///
211/// ## Example Usage
212///
213/// ```rust,no_run
214/// use rusqlite::{Connection, Result};
215/// use parsql_macros::{Updateable, UpdateParams};
216/// use parsql_sqlite::update;
217///
218/// fn main() -> Result<()> {
219///     // Create database connection
220///     let conn = Connection::open("test.db")?;
221///     conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT, state INTEGER)", [])?;
222///     conn.execute("INSERT INTO users (id, name, email, state) VALUES (1, 'Old Name', 'old@example.com', 0)", [])?;
223///
224///     // Define an entity for updating
225///     #[derive(Updateable, UpdateParams)]
226///     #[table("users")]
227///     #[update("name, email")]
228///     #[where_clause("id = ?")]
229///     pub struct UpdateUser {
230///         pub id: i64,
231///         pub name: String,
232///         pub email: String,
233///         pub state: i16,  // Not included in the update
234///     }
235///
236///     // Create the update parameters (update user with id 1)
237///     let update_query = UpdateUser {
238///         id: 1,
239///         name: String::from("John Updated"),
240///         email: String::from("john.updated@example.com"),
241///         state: 1,
242///     };
243///
244///     // Execute update
245///     let update_result = update(&conn, update_query)?;
246///     println!("Update result: {:?}", update_result);
247///     Ok(())
248/// }
249/// ```
250pub fn update<T: SqlCommand + UpdateParams>(
251    conn: &rusqlite::Connection,
252    entity: T,
253) -> Result<usize, Error> {
254    let sql = T::query();
255    if std::env::var("PARSQL_TRACE").unwrap_or_default() == "1" {
256        println!("[PARSQL-SQLITE] Execute SQL: {}", sql);
257    }
258
259    let params = entity.params();
260    let param_refs: Vec<&dyn ToSql> = params.iter().map(|p| *p as &dyn ToSql).collect();
261    let affected_rows = conn.execute(&sql, param_refs.as_slice())?;
262    Ok(affected_rows)
263}
264
265/// # delete
266///
267/// Deletes a record from the database.
268///
269/// ## Parameters
270/// - `conn`: SQLite database connection
271/// - `entity`: Data object containing deletion parameters (must implement SqlCommand and SqlParams traits)
272///
273/// ## Return Value
274/// - `Result<usize, Error>`: On success, returns the number of deleted records; on failure, returns Error
275///
276/// ## Struct Definition
277/// Structs used with this function should be annotated with the following derive macros:
278///
279/// ```rust,no_run
280/// #[derive(Deletable, SqlParams)]   // Required macros
281/// #[table("table_name")]            // Table name to delete from
282/// #[where_clause("id = ?")]         // Delete condition
283/// pub struct MyEntity {
284///     pub id: i64,                  // Field used in the condition
285/// }
286/// ```
287///
288/// - `Deletable`: Automatically generates SQL DELETE statements
289/// - `SqlParams`: Automatically generates SQL parameters
290/// - `#[table("table_name")]`: Specifies the table name for the deletion
291/// - `#[where_clause("id = ?")]`: Specifies the delete condition (`?` will be replaced with parameter value)
292///
293/// ## Example Usage
294///
295/// ```rust,no_run
296/// use rusqlite::{Connection, Result};
297/// use parsql_macros::{Deletable, SqlParams};
298/// use parsql_sqlite::delete;
299///
300/// fn main() -> Result<()> {
301///     // Create database connection
302///     let conn = Connection::open("test.db")?;
303///     conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)", [])?;
304///     conn.execute("INSERT INTO users (id, name, email) VALUES (1, 'John', 'john@example.com')", [])?;
305///
306///     // Define a delete query
307///     #[derive(Deletable, SqlParams)]
308///     #[table("users")]
309///     #[where_clause("id = ?")]
310///     pub struct DeleteUser {
311///         pub id: i64,
312///     }
313///
314///     // Create delete parameters (delete user with ID 1)
315///     let delete_query = DeleteUser { id: 1 };
316///
317///     // Execute delete
318///     let delete_result = delete(&conn, delete_query)?;
319///     println!("Delete result: {:?}", delete_result);
320///     Ok(())
321/// }
322/// ```
323pub fn delete<T: SqlCommand + SqlParams>(
324    conn: &rusqlite::Connection,
325    entity: T,
326) -> Result<usize, Error> {
327    let sql = T::query();
328    if std::env::var("PARSQL_TRACE").unwrap_or_default() == "1" {
329        println!("[PARSQL-SQLITE] Execute SQL: {}", sql);
330    }
331
332    let params = entity.params();
333    let param_refs: Vec<&dyn ToSql> = params.iter().map(|p| *p as &dyn ToSql).collect();
334    let affected_rows = conn.execute(&sql, param_refs.as_slice())?;
335    Ok(affected_rows)
336}
337
338/// # fetch
339///
340/// Retrieves a single record from the database based on a specific condition.
341///
342/// ## Parameters
343/// - `conn`: SQLite database connection
344/// - `entity`: Query parameter object (must implement SqlQuery, FromRow, and SqlParams traits)
345///
346/// ## Return Value
347/// - `Result<T, Error>`: On success, returns the queried record; on failure, returns Error
348///
349/// ## Struct Definition
350/// Structs used with this function should be annotated with the following derive macros:
351///
352/// ```rust,no_run
353/// #[derive(Queryable, FromRow, SqlParams)]  // Required macros
354/// #[table("table_name")]                    // Table name to query
355/// #[where_clause("id = ?")]                 // Query condition
356/// pub struct MyEntity {
357///     pub id: i64,                          // Field used in the condition
358///     pub field1: String,                   // Fields to retrieve
359///     pub field2: i32,
360/// }
361/// ```
362///
363/// ## Example Usage
364///
365/// ```rust,no_run
366/// use rusqlite::{Connection, Result};
367/// use parsql_macros::{Queryable, FromRow, SqlParams};
368/// use parsql_sqlite::fetch;
369///
370/// fn main() -> Result<()> {
371///     // Create database connection
372///     let conn = Connection::open("test.db")?;
373///     conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)", [])?;
374///     conn.execute("INSERT INTO users (id, name, email) VALUES (1, 'John', 'john@example.com')", [])?;
375///
376///     // Define a query
377///     #[derive(Queryable, FromRow, SqlParams)]
378///     #[table("users")]
379///     #[where_clause("id = ?")]
380///     pub struct GetUser {
381///         pub id: i64,
382///         pub name: String,
383///         pub email: String,
384///     }
385///
386///     // Create query parameters (get user with ID 1)
387///     let get_query = GetUser {
388///         id: 1,
389///         name: String::new(),
390///         email: String::new(),
391///     };
392///
393///     // Execute query
394///     let user = fetch(&conn, &get_query)?;
395///     println!("User: {:?}", user);
396///     Ok(())
397/// }
398/// ```
399pub fn fetch<P, R>(conn: &rusqlite::Connection, params: &P) -> Result<R, Error>
400where
401    P: SqlQuery<R> + SqlParams,
402    R: FromRow,
403{
404    let sql = P::query();
405    if std::env::var("PARSQL_TRACE").unwrap_or_default() == "1" {
406        println!("[PARSQL-SQLITE] Execute SQL: {}", sql);
407    }
408
409    let query_params = params.params();
410    let param_refs: Vec<&dyn ToSql> = query_params.iter().map(|p| *p as &dyn ToSql).collect();
411    conn.query_row(&sql, param_refs.as_slice(), |row| R::from_row(row))
412}
413
414/// # fetch_all
415///
416/// Retrieves multiple records from the database based on a specific condition.
417///
418/// ## Parameters
419/// - `conn`: SQLite database connection
420/// - `entity`: Query parameter object (must implement SqlQuery, FromRow, and SqlParams traits)
421///
422/// ## Return Value
423/// - `Result<Vec<T>, Error>`: On success, returns a vector of records; on failure, returns Error
424///
425/// ## Example Usage
426///
427/// ```rust,no_run
428/// use rusqlite::{Connection, Result};
429/// use parsql_macros::{Queryable, FromRow, SqlParams};
430/// use parsql_sqlite::fetch_all;
431///
432/// fn main() -> Result<()> {
433///     // Create database connection
434///     let conn = Connection::open("test.db")?;
435///     conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT, active INTEGER)", [])?;
436///     conn.execute("INSERT INTO users (id, name, email, active) VALUES (1, 'John', 'john@example.com', 1)", [])?;
437///     conn.execute("INSERT INTO users (id, name, email, active) VALUES (2, 'Jane', 'jane@example.com', 1)", [])?;
438///
439///     // Define a query
440///     #[derive(Queryable, FromRow, SqlParams)]
441///     #[table("users")]
442///     #[where_clause("active = ?")]
443///     pub struct GetActiveUsers {
444///         pub id: i64,
445///         pub name: String,
446///         pub email: String,
447///         pub active: i32,
448///     }
449///
450///     // Create query parameters (get all active users)
451///     let query = GetActiveUsers {
452///         id: 0,
453///         name: String::new(),
454///         email: String::new(),
455///         active: 1,
456///     };
457///
458///     // Execute query
459///     let users = fetch_all(&conn, &query)?;
460///     println!("Active users: {:?}", users);
461///     Ok(())
462/// }
463/// ```
464pub fn fetch_all<P, R>(conn: &rusqlite::Connection, params: &P) -> Result<Vec<R>, Error>
465where
466    P: SqlQuery<R> + SqlParams,
467    R: FromRow,
468{
469    let sql = P::query();
470    if std::env::var("PARSQL_TRACE").unwrap_or_default() == "1" {
471        println!("[PARSQL-SQLITE] Execute SQL: {}", sql);
472    }
473
474    let query_params = params.params();
475    let param_refs: Vec<&dyn ToSql> = query_params.iter().map(|p| *p as &dyn ToSql).collect();
476    let mut stmt = conn.prepare(&sql)?;
477    let rows = stmt.query_map(param_refs.as_slice(), |row| R::from_row(row))?;
478
479    let mut results = Vec::new();
480    for row in rows {
481        results.push(row?);
482    }
483
484    Ok(results)
485}
486
487/// # get
488///
489/// Retrieves a single record from the database based on a specific condition.
490///
491/// # Deprecated
492/// This function has been renamed to `fetch`. Please use `fetch` instead.
493///
494/// ## Parameters
495/// - `conn`: SQLite database connection
496/// - `entity`: Query parameter object (must implement SqlQuery, FromRow, and SqlParams traits)
497///
498/// ## Return Value
499/// - `Result<T, Error>`: On success, returns the queried record; on failure, returns Error
500#[deprecated(
501    since = "0.3.7",
502    note = "Renamed to `fetch`. Please use `fetch` function instead."
503)]
504pub fn get<T: SqlQuery<T> + SqlParams, R>(
505    conn: &rusqlite::Connection,
506    entity: &T,
507) -> Result<R, Error>
508where
509    T: SqlQuery<R> + SqlParams,
510    R: FromRow,
511{
512    fetch(conn, entity)
513}
514
515/// # get_all
516///
517/// Retrieves multiple records from the database based on a specific condition.
518///
519/// # Deprecated
520/// This function has been renamed to `fetch_all`. Please use `fetch_all` instead.
521///
522/// ## Parameters
523/// - `conn`: SQLite database connection
524/// - `entity`: Query parameter object (must implement SqlQuery, FromRow, and SqlParams traits)
525///
526/// ## Return Value
527/// - `Result<Vec<T>, Error>`: On success, returns a vector of records; on failure, returns Error
528#[deprecated(
529    since = "0.3.7",
530    note = "Renamed to `fetch_all`. Please use `fetch_all` function instead."
531)]
532pub fn get_all<T: SqlQuery<T> + SqlParams, R>(
533    conn: &rusqlite::Connection,
534    entity: &T,
535) -> Result<Vec<R>, Error>
536where
537    T: SqlQuery<R> + SqlParams,
538    R: FromRow,
539{
540    fetch_all(conn, entity)
541}
542
543/// # select
544///
545/// Executes a custom SELECT query and maps the result to a model using a provided mapping function.
546///
547/// ## Parameters
548/// - `conn`: SQLite database connection
549/// - `entity`: Query parameter object (must implement SqlQuery and SqlParams traits)
550/// - `to_model`: Function to map a database row to your model type
551///
552/// ## Return Value
553/// - `Result<T, Error>`: On success, returns the mapped model; on failure, returns Error
554///
555/// ## Example Usage
556///
557/// ```rust,no_run
558/// use rusqlite::{Connection, Result, Row};
559/// use parsql_macros::{Queryable, SqlParams};
560/// use parsql_sqlite::select;
561///
562/// fn main() -> Result<()> {
563///     // Create database connection
564///     let conn = Connection::open("test.db")?;
565///     conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)", [])?;
566///     conn.execute("INSERT INTO users (id, name, email) VALUES (1, 'John', 'john@example.com')", [])?;
567///
568///     // Define your model
569///     #[derive(Debug)]
570///     pub struct User {
571///         pub id: i64,
572///         pub name: String,
573///         pub email: String,
574///     }
575///
576///     // Define a query
577///     #[derive(Queryable, SqlParams)]
578///     #[table("users")]
579///     #[where_clause("id = ?")]
580///     pub struct GetUser {
581///         pub id: i64,
582///     }
583///
584///     // Create query parameters
585///     let get_query = GetUser { id: 1 };
586///
587///     // Define row mapping function
588///     let to_user = |row: &Row| -> Result<User> {
589///         Ok(User {
590///             id: row.get(0)?,
591///             name: row.get(1)?,
592///             email: row.get(2)?,
593///         })
594///     };
595///
596///     // Execute query with custom mapping
597///     let user = select(&conn, &get_query, to_user)?;
598///     println!("User: {:?}", user);
599///     Ok(())
600/// }
601/// ```
602pub fn select<T: SqlQuery<T> + SqlParams, F, R>(
603    conn: &rusqlite::Connection,
604    entity: &T,
605    to_model: F,
606) -> Result<R, Error>
607where
608    F: Fn(&Row) -> Result<R, Error>,
609{
610    conn.select(entity, to_model)
611}
612
613/// # select_all
614///
615/// Executes a custom SELECT query and maps multiple results to models using a provided mapping function.
616///
617/// ## Parameters
618/// - `conn`: SQLite database connection
619/// - `entity`: Query parameter object (must implement SqlQuery and SqlParams traits)
620/// - `to_model`: Function to map a database row to your model type
621///
622/// ## Return Value
623/// - `Result<Vec<T>, Error>`: On success, returns a vector of mapped models; on failure, returns Error
624///
625/// ## Example Usage
626///
627/// ```rust,no_run
628/// use rusqlite::{Connection, Result, Row};
629/// use parsql_macros::{Queryable, SqlParams};
630/// use parsql_sqlite::select_all;
631///
632/// fn main() -> Result<()> {
633///     // Create database connection
634///     let conn = Connection::open("test.db")?;
635///     conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT, state INTEGER)", [])?;
636///     conn.execute("INSERT INTO users (name, email, state) VALUES ('John', 'john@example.com', 1)", [])?;
637///     conn.execute("INSERT INTO users (name, email, state) VALUES ('Jane', 'jane@example.com', 1)", [])?;
638///
639///     // Define your model
640///     #[derive(Debug)]
641///     pub struct User {
642///         pub id: i64,
643///         pub name: String,
644///         pub email: String,
645///         pub state: i16,
646///     }
647///
648///     // Define a query
649///     #[derive(Queryable, SqlParams)]
650///     #[table("users")]
651///     #[where_clause("state = ?")]
652///     pub struct GetActiveUsers {
653///         pub state: i16,
654///     }
655///
656///     // Create query parameters
657///     let get_query = GetActiveUsers { state: 1 };
658///
659///     // Define row mapping function
660///     let to_user = |row: &Row| -> Result<User> {
661///         Ok(User {
662///             id: row.get(0)?,
663///             name: row.get(1)?,
664///             email: row.get(2)?,
665///             state: row.get(3)?,
666///         })
667///     };
668///
669///     // Execute query with custom mapping
670///     let users = select_all(&conn, &get_query, to_user)?;
671///     println!("Active users: {:?}", users);
672///     Ok(())
673/// }
674/// ```
675pub fn select_all<T: SqlQuery<T> + SqlParams, F, R>(
676    conn: &rusqlite::Connection,
677    entity: &T,
678    to_model: F,
679) -> Result<Vec<R>, Error>
680where
681    F: Fn(&Row) -> Result<R, Error>,
682{
683    conn.select_all(entity, to_model)
684}
parsql_sqlite/crud_ops.rs

parsql_sqlite/
crud_ops.rs
