diesel/sqlite/connection/
mod.rs

Help
1#[cfg(not(all(target_family = "wasm", target_os = "unknown")))]
2extern crate libsqlite3_sys as ffi;
3
4#[cfg(all(target_family = "wasm", target_os = "unknown"))]
5use sqlite_wasm_rs::export as ffi;
6
7mod bind_collector;
8mod functions;
9mod owned_row;
10mod raw;
11mod row;
12mod serialized_database;
13mod sqlite_value;
14mod statement_iterator;
15mod stmt;
16
17pub(in crate::sqlite) use self::bind_collector::SqliteBindCollector;
18pub use self::bind_collector::SqliteBindValue;
19pub use self::serialized_database::SerializedDatabase;
20pub use self::sqlite_value::SqliteValue;
21
22use std::os::raw as libc;
23
24use self::raw::RawConnection;
25use self::statement_iterator::*;
26use self::stmt::{Statement, StatementUse};
27use super::SqliteAggregateFunction;
28use crate::connection::instrumentation::{DynInstrumentation, StrQueryHelper};
29use crate::connection::statement_cache::StatementCache;
30use crate::connection::*;
31use crate::deserialize::{FromSqlRow, StaticallySizedRow};
32use crate::expression::QueryMetadata;
33use crate::query_builder::*;
34use crate::result::*;
35use crate::serialize::ToSql;
36use crate::sql_types::{HasSqlType, TypeMetadata};
37use crate::sqlite::Sqlite;
38
39/// Connections for the SQLite backend. Unlike other backends, SQLite supported
40/// connection URLs are:
41///
42/// - File paths (`test.db`)
43/// - [URIs](https://sqlite.org/uri.html) (`file://test.db`)
44/// - Special identifiers (`:memory:`)
45///
46/// # Supported loading model implementations
47///
48/// * [`DefaultLoadingMode`]
49///
50/// As `SqliteConnection` only supports a single loading mode implementation
51/// it is **not required** to explicitly specify a loading mode
52/// when calling [`RunQueryDsl::load_iter()`] or [`LoadConnection::load`]
53///
54/// [`RunQueryDsl::load_iter()`]: crate::query_dsl::RunQueryDsl::load_iter
55///
56/// ## DefaultLoadingMode
57///
58/// `SqliteConnection` only supports a single loading mode, which loads
59/// values row by row from the result set.
60///
61/// ```rust
62/// # include!("../../doctest_setup.rs");
63/// #
64/// # fn main() {
65/// #     run_test().unwrap();
66/// # }
67/// #
68/// # fn run_test() -> QueryResult<()> {
69/// #     use schema::users;
70/// #     let connection = &mut establish_connection();
71/// use diesel::connection::DefaultLoadingMode;
72/// { // scope to restrict the lifetime of the iterator
73///     let iter1 = users::table.load_iter::<(i32, String), DefaultLoadingMode>(connection)?;
74///
75///     for r in iter1 {
76///         let (id, name) = r?;
77///         println!("Id: {} Name: {}", id, name);
78///     }
79/// }
80///
81/// // works without specifying the loading mode
82/// let iter2 = users::table.load_iter::<(i32, String), _>(connection)?;
83///
84/// for r in iter2 {
85///     let (id, name) = r?;
86///     println!("Id: {} Name: {}", id, name);
87/// }
88/// #   Ok(())
89/// # }
90/// ```
91///
92/// This mode does **not support** creating
93/// multiple iterators using the same connection.
94///
95/// ```compile_fail
96/// # include!("../../doctest_setup.rs");
97/// #
98/// # fn main() {
99/// #     run_test().unwrap();
100/// # }
101/// #
102/// # fn run_test() -> QueryResult<()> {
103/// #     use schema::users;
104/// #     let connection = &mut establish_connection();
105/// use diesel::connection::DefaultLoadingMode;
106///
107/// let iter1 = users::table.load_iter::<(i32, String), DefaultLoadingMode>(connection)?;
108/// let iter2 = users::table.load_iter::<(i32, String), DefaultLoadingMode>(connection)?;
109///
110/// for r in iter1 {
111///     let (id, name) = r?;
112///     println!("Id: {} Name: {}", id, name);
113/// }
114///
115/// for r in iter2 {
116///     let (id, name) = r?;
117///     println!("Id: {} Name: {}", id, name);
118/// }
119/// #   Ok(())
120/// # }
121/// ```
122#[allow(missing_debug_implementations)]
123#[cfg(feature = "sqlite")]
124pub struct SqliteConnection {
125    // statement_cache needs to be before raw_connection
126    // otherwise we will get errors about open statements before closing the
127    // connection itself
128    statement_cache: StatementCache<Sqlite, Statement>,
129    raw_connection: RawConnection,
130    transaction_state: AnsiTransactionManager,
131    // this exists for the sole purpose of implementing `WithMetadataLookup` trait
132    // and avoiding static mut which will be deprecated in 2024 edition
133    metadata_lookup: (),
134    instrumentation: DynInstrumentation,
135}
136
137// This relies on the invariant that RawConnection or Statement are never
138// leaked. If a reference to one of those was held on a different thread, this
139// would not be thread safe.
140#[allow(unsafe_code)]
141unsafe impl Send for SqliteConnection {}
142
143impl SimpleConnection for SqliteConnection {
144    fn batch_execute(&mut self, query: &str) -> QueryResult<()> {
145        self.instrumentation
146            .on_connection_event(InstrumentationEvent::StartQuery {
147                query: &StrQueryHelper::new(query),
148            });
149        let resp = self.raw_connection.exec(query);
150        self.instrumentation
151            .on_connection_event(InstrumentationEvent::FinishQuery {
152                query: &StrQueryHelper::new(query),
153                error: resp.as_ref().err(),
154            });
155        resp
156    }
157}
158
159impl ConnectionSealed for SqliteConnection {}
160
161impl Connection for SqliteConnection {
162    type Backend = Sqlite;
163    type TransactionManager = AnsiTransactionManager;
164
165    /// Establish a connection to the database specified by `database_url`.
166    ///
167    /// See [SqliteConnection] for supported `database_url`.
168    ///
169    /// If the database does not exist, this method will try to
170    /// create a new database and then establish a connection to it.
171    ///
172    /// ## WASM support
173    ///
174    /// If you plan to use this connection type on the `wasm32-unknown-unknown` target please
175    /// make sure to read the following notes:
176    ///
177    /// * The database is stored in memory by default.
178    /// * Persistent VFS (Virtual File Systems) is optional,
179    ///   see <https://github.com/Spxg/sqlite-wasm-rs/blob/master/VFS.md> for details
180    fn establish(database_url: &str) -> ConnectionResult<Self> {
181        let mut instrumentation = DynInstrumentation::default_instrumentation();
182        instrumentation.on_connection_event(InstrumentationEvent::StartEstablishConnection {
183            url: database_url,
184        });
185
186        let establish_result = Self::establish_inner(database_url);
187        instrumentation.on_connection_event(InstrumentationEvent::FinishEstablishConnection {
188            url: database_url,
189            error: establish_result.as_ref().err(),
190        });
191        let mut conn = establish_result?;
192        conn.instrumentation = instrumentation;
193        Ok(conn)
194    }
195
196    fn execute_returning_count<T>(&mut self, source: &T) -> QueryResult<usize>
197    where
198        T: QueryFragment<Self::Backend> + QueryId,
199    {
200        let statement_use = self.prepared_query(source)?;
201        statement_use.run().and_then(|_| {
202            self.raw_connection
203                .rows_affected_by_last_query()
204                .map_err(Error::DeserializationError)
205        })
206    }
207
208    fn transaction_state(&mut self) -> &mut AnsiTransactionManager
209    where
210        Self: Sized,
211    {
212        &mut self.transaction_state
213    }
214
215    fn instrumentation(&mut self) -> &mut dyn Instrumentation {
216        &mut *self.instrumentation
217    }
218
219    fn set_instrumentation(&mut self, instrumentation: impl Instrumentation) {
220        self.instrumentation = instrumentation.into();
221    }
222
223    fn set_prepared_statement_cache_size(&mut self, size: CacheSize) {
224        self.statement_cache.set_cache_size(size);
225    }
226}
227
228impl LoadConnection<DefaultLoadingMode> for SqliteConnection {
229    type Cursor<'conn, 'query> = StatementIterator<'conn, 'query>;
230    type Row<'conn, 'query> = self::row::SqliteRow<'conn, 'query>;
231
232    fn load<'conn, 'query, T>(
233        &'conn mut self,
234        source: T,
235    ) -> QueryResult<Self::Cursor<'conn, 'query>>
236    where
237        T: Query + QueryFragment<Self::Backend> + QueryId + 'query,
238        Self::Backend: QueryMetadata<T::SqlType>,
239    {
240        let statement = self.prepared_query(source)?;
241
242        Ok(StatementIterator::new(statement))
243    }
244}
245
246impl WithMetadataLookup for SqliteConnection {
247    fn metadata_lookup(&mut self) -> &mut <Sqlite as TypeMetadata>::MetadataLookup {
248        &mut self.metadata_lookup
249    }
250}
251
252#[cfg(feature = "r2d2")]
253impl crate::r2d2::R2D2Connection for crate::sqlite::SqliteConnection {
254    fn ping(&mut self) -> QueryResult<()> {
255        use crate::RunQueryDsl;
256
257        crate::r2d2::CheckConnectionQuery.execute(self).map(|_| ())
258    }
259
260    fn is_broken(&mut self) -> bool {
261        AnsiTransactionManager::is_broken_transaction_manager(self)
262    }
263}
264
265impl MultiConnectionHelper for SqliteConnection {
266    fn to_any<'a>(
267        lookup: &mut <Self::Backend as crate::sql_types::TypeMetadata>::MetadataLookup,
268    ) -> &mut (dyn std::any::Any + 'a) {
269        lookup
270    }
271
272    fn from_any(
273        lookup: &mut dyn std::any::Any,
274    ) -> Option<&mut <Self::Backend as crate::sql_types::TypeMetadata>::MetadataLookup> {
275        lookup.downcast_mut()
276    }
277}
278
279impl SqliteConnection {
280    /// Run a transaction with `BEGIN IMMEDIATE`
281    ///
282    /// This method will return an error if a transaction is already open.
283    ///
284    /// # Example
285    ///
286    /// ```rust
287    /// # include!("../../doctest_setup.rs");
288    /// #
289    /// # fn main() {
290    /// #     run_test().unwrap();
291    /// # }
292    /// #
293    /// # fn run_test() -> QueryResult<()> {
294    /// #     let mut conn = SqliteConnection::establish(":memory:").unwrap();
295    /// conn.immediate_transaction(|conn| {
296    ///     // Do stuff in a transaction
297    ///     Ok(())
298    /// })
299    /// # }
300    /// ```
301    pub fn immediate_transaction<T, E, F>(&mut self, f: F) -> Result<T, E>
302    where
303        F: FnOnce(&mut Self) -> Result<T, E>,
304        E: From<Error>,
305    {
306        self.transaction_sql(f, "BEGIN IMMEDIATE")
307    }
308
309    /// Run a transaction with `BEGIN EXCLUSIVE`
310    ///
311    /// This method will return an error if a transaction is already open.
312    ///
313    /// # Example
314    ///
315    /// ```rust
316    /// # include!("../../doctest_setup.rs");
317    /// #
318    /// # fn main() {
319    /// #     run_test().unwrap();
320    /// # }
321    /// #
322    /// # fn run_test() -> QueryResult<()> {
323    /// #     let mut conn = SqliteConnection::establish(":memory:").unwrap();
324    /// conn.exclusive_transaction(|conn| {
325    ///     // Do stuff in a transaction
326    ///     Ok(())
327    /// })
328    /// # }
329    /// ```
330    pub fn exclusive_transaction<T, E, F>(&mut self, f: F) -> Result<T, E>
331    where
332        F: FnOnce(&mut Self) -> Result<T, E>,
333        E: From<Error>,
334    {
335        self.transaction_sql(f, "BEGIN EXCLUSIVE")
336    }
337
338    fn transaction_sql<T, E, F>(&mut self, f: F, sql: &str) -> Result<T, E>
339    where
340        F: FnOnce(&mut Self) -> Result<T, E>,
341        E: From<Error>,
342    {
343        AnsiTransactionManager::begin_transaction_sql(&mut *self, sql)?;
344        match f(&mut *self) {
345            Ok(value) => {
346                AnsiTransactionManager::commit_transaction(&mut *self)?;
347                Ok(value)
348            }
349            Err(e) => {
350                AnsiTransactionManager::rollback_transaction(&mut *self)?;
351                Err(e)
352            }
353        }
354    }
355
356    fn prepared_query<'conn, 'query, T>(
357        &'conn mut self,
358        source: T,
359    ) -> QueryResult<StatementUse<'conn, 'query>>
360    where
361        T: QueryFragment<Sqlite> + QueryId + 'query,
362    {
363        self.instrumentation
364            .on_connection_event(InstrumentationEvent::StartQuery {
365                query: &crate::debug_query(&source),
366            });
367        let raw_connection = &self.raw_connection;
368        let cache = &mut self.statement_cache;
369        let statement = match cache.cached_statement(
370            &source,
371            &Sqlite,
372            &[],
373            raw_connection,
374            Statement::prepare,
375            &mut *self.instrumentation,
376        ) {
377            Ok(statement) => statement,
378            Err(e) => {
379                self.instrumentation
380                    .on_connection_event(InstrumentationEvent::FinishQuery {
381                        query: &crate::debug_query(&source),
382                        error: Some(&e),
383                    });
384
385                return Err(e);
386            }
387        };
388
389        StatementUse::bind(statement, source, &mut *self.instrumentation)
390    }
391
392    #[doc(hidden)]
393    pub fn register_sql_function<ArgsSqlType, RetSqlType, Args, Ret, F>(
394        &mut self,
395        fn_name: &str,
396        deterministic: bool,
397        mut f: F,
398    ) -> QueryResult<()>
399    where
400        F: FnMut(Args) -> Ret + std::panic::UnwindSafe + Send + 'static,
401        Args: FromSqlRow<ArgsSqlType, Sqlite> + StaticallySizedRow<ArgsSqlType, Sqlite>,
402        Ret: ToSql<RetSqlType, Sqlite>,
403        Sqlite: HasSqlType<RetSqlType>,
404    {
405        functions::register(
406            &self.raw_connection,
407            fn_name,
408            deterministic,
409            move |_, args| f(args),
410        )
411    }
412
413    #[doc(hidden)]
414    pub fn register_noarg_sql_function<RetSqlType, Ret, F>(
415        &self,
416        fn_name: &str,
417        deterministic: bool,
418        f: F,
419    ) -> QueryResult<()>
420    where
421        F: FnMut() -> Ret + std::panic::UnwindSafe + Send + 'static,
422        Ret: ToSql<RetSqlType, Sqlite>,
423        Sqlite: HasSqlType<RetSqlType>,
424    {
425        functions::register_noargs(&self.raw_connection, fn_name, deterministic, f)
426    }
427
428    #[doc(hidden)]
429    pub fn register_aggregate_function<ArgsSqlType, RetSqlType, Args, Ret, A>(
430        &mut self,
431        fn_name: &str,
432    ) -> QueryResult<()>
433    where
434        A: SqliteAggregateFunction<Args, Output = Ret> + 'static + Send + std::panic::UnwindSafe,
435        Args: FromSqlRow<ArgsSqlType, Sqlite> + StaticallySizedRow<ArgsSqlType, Sqlite>,
436        Ret: ToSql<RetSqlType, Sqlite>,
437        Sqlite: HasSqlType<RetSqlType>,
438    {
439        functions::register_aggregate::<_, _, _, _, A>(&self.raw_connection, fn_name)
440    }
441
442    /// Register a collation function.
443    ///
444    /// `collation` must always return the same answer given the same inputs.
445    /// If `collation` panics and unwinds the stack, the process is aborted, since it is used
446    /// across a C FFI boundary, which cannot be unwound across and there is no way to
447    /// signal failures via the SQLite interface in this case..
448    ///
449    /// If the name is already registered it will be overwritten.
450    ///
451    /// This method will return an error if registering the function fails, either due to an
452    /// out-of-memory situation or because a collation with that name already exists and is
453    /// currently being used in parallel by a query.
454    ///
455    /// The collation needs to be specified when creating a table:
456    /// `CREATE TABLE my_table ( str TEXT COLLATE MY_COLLATION )`,
457    /// where `MY_COLLATION` corresponds to name passed as `collation_name`.
458    ///
459    /// # Example
460    ///
461    /// ```rust
462    /// # include!("../../doctest_setup.rs");
463    /// #
464    /// # fn main() {
465    /// #     run_test().unwrap();
466    /// # }
467    /// #
468    /// # fn run_test() -> QueryResult<()> {
469    /// #     let mut conn = SqliteConnection::establish(":memory:").unwrap();
470    /// // sqlite NOCASE only works for ASCII characters,
471    /// // this collation allows handling UTF-8 (barring locale differences)
472    /// conn.register_collation("RUSTNOCASE", |rhs, lhs| {
473    ///     rhs.to_lowercase().cmp(&lhs.to_lowercase())
474    /// })
475    /// # }
476    /// ```
477    pub fn register_collation<F>(&mut self, collation_name: &str, collation: F) -> QueryResult<()>
478    where
479        F: Fn(&str, &str) -> std::cmp::Ordering + Send + 'static + std::panic::UnwindSafe,
480    {
481        self.raw_connection
482            .register_collation_function(collation_name, collation)
483    }
484
485    /// Serialize the current SQLite database into a byte buffer.
486    ///
487    /// The serialized data is identical to the data that would be written to disk if the database
488    /// was saved in a file.
489    ///
490    /// # Returns
491    ///
492    /// This function returns a byte slice representing the serialized database.
493    pub fn serialize_database_to_buffer(&mut self) -> SerializedDatabase {
494        self.raw_connection.serialize()
495    }
496
497    /// Deserialize an SQLite database from a byte buffer.
498    ///
499    /// This function takes a byte slice and attempts to deserialize it into a SQLite database.
500    /// If successful, the database is loaded into the connection. If the deserialization fails,
501    /// an error is returned.
502    ///
503    /// The database is opened in READONLY mode.
504    ///
505    /// # Example
506    ///
507    /// ```no_run
508    /// # use diesel::sqlite::SerializedDatabase;
509    /// # use diesel::sqlite::SqliteConnection;
510    /// # use diesel::result::QueryResult;
511    /// # use diesel::sql_query;
512    /// # use diesel::Connection;
513    /// # use diesel::RunQueryDsl;
514    /// # fn main() {
515    /// let connection = &mut SqliteConnection::establish(":memory:").unwrap();
516    ///
517    /// sql_query("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)")
518    ///     .execute(connection).unwrap();
519    /// sql_query("INSERT INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com'), ('Jane Doe', 'jane.doe@example.com')")
520    ///     .execute(connection).unwrap();
521    ///
522    /// // Serialize the database to a byte vector
523    /// let serialized_db: SerializedDatabase = connection.serialize_database_to_buffer();
524    ///
525    /// // Create a new in-memory SQLite database
526    /// let connection = &mut SqliteConnection::establish(":memory:").unwrap();
527    ///
528    /// // Deserialize the byte vector into the new database
529    /// connection.deserialize_readonly_database_from_buffer(serialized_db.as_slice()).unwrap();
530    /// #
531    /// # }
532    /// ```
533    pub fn deserialize_readonly_database_from_buffer(&mut self, data: &[u8]) -> QueryResult<()> {
534        self.raw_connection.deserialize(data)
535    }
536
537    fn register_diesel_sql_functions(&self) -> QueryResult<()> {
538        use crate::sql_types::{Integer, Text};
539
540        functions::register::<Text, Integer, _, _, _>(
541            &self.raw_connection,
542            "diesel_manage_updated_at",
543            false,
544            |conn, table_name: String| {
545                conn.exec(&format!(
546                    include_str!("diesel_manage_updated_at.sql"),
547                    table_name = table_name
548                ))
549                .expect("Failed to create trigger");
550                0 // have to return *something*
551            },
552        )
553    }
554
555    fn establish_inner(database_url: &str) -> Result<SqliteConnection, ConnectionError> {
556        use crate::result::ConnectionError::CouldntSetupConfiguration;
557        let raw_connection = RawConnection::establish(database_url)?;
558        let conn = Self {
559            statement_cache: StatementCache::new(),
560            raw_connection,
561            transaction_state: AnsiTransactionManager::default(),
562            metadata_lookup: (),
563            instrumentation: DynInstrumentation::none(),
564        };
565        conn.register_diesel_sql_functions()
566            .map_err(CouldntSetupConfiguration)?;
567        Ok(conn)
568    }
569}
570
571fn error_message(err_code: libc::c_int) -> &'static str {
572    ffi::code_to_str(err_code)
573}
574
575#[cfg(test)]
576mod tests {
577    use super::*;
578    use crate::dsl::sql;
579    use crate::prelude::*;
580    use crate::sql_types::{Integer, Text};
581
582    fn connection() -> SqliteConnection {
583        SqliteConnection::establish(":memory:").unwrap()
584    }
585
586    #[declare_sql_function]
587    extern "SQL" {
588        fn fun_case(x: Text) -> Text;
589        fn my_add(x: Integer, y: Integer) -> Integer;
590        fn answer() -> Integer;
591        fn add_counter(x: Integer) -> Integer;
592
593        #[aggregate]
594        fn my_sum(expr: Integer) -> Integer;
595        #[aggregate]
596        fn range_max(expr1: Integer, expr2: Integer, expr3: Integer) -> Nullable<Integer>;
597    }
598
599    #[diesel_test_helper::test]
600    fn database_serializes_and_deserializes_successfully() {
601        let expected_users = vec![
602            (
603                1,
604                "John Doe".to_string(),
605                "john.doe@example.com".to_string(),
606            ),
607            (
608                2,
609                "Jane Doe".to_string(),
610                "jane.doe@example.com".to_string(),
611            ),
612        ];
613
614        let conn1 = &mut connection();
615        let _ =
616            crate::sql_query("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)")
617                .execute(conn1);
618        let _ = crate::sql_query("INSERT INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com'), ('Jane Doe', 'jane.doe@example.com')")
619            .execute(conn1);
620
621        let serialized_database = conn1.serialize_database_to_buffer();
622
623        let conn2 = &mut connection();
624        conn2
625            .deserialize_readonly_database_from_buffer(serialized_database.as_slice())
626            .unwrap();
627
628        let query = sql::<(Integer, Text, Text)>("SELECT id, name, email FROM users ORDER BY id");
629        let actual_users = query.load::<(i32, String, String)>(conn2).unwrap();
630
631        assert_eq!(expected_users, actual_users);
632    }
633
634    #[diesel_test_helper::test]
635    fn register_custom_function() {
636        let connection = &mut connection();
637        fun_case_utils::register_impl(connection, |x: String| {
638            x.chars()
639                .enumerate()
640                .map(|(i, c)| {
641                    if i % 2 == 0 {
642                        c.to_lowercase().to_string()
643                    } else {
644                        c.to_uppercase().to_string()
645                    }
646                })
647                .collect::<String>()
648        })
649        .unwrap();
650
651        let mapped_string = crate::select(fun_case("foobar"))
652            .get_result::<String>(connection)
653            .unwrap();
654        assert_eq!("fOoBaR", mapped_string);
655    }
656
657    #[diesel_test_helper::test]
658    fn register_multiarg_function() {
659        let connection = &mut connection();
660        my_add_utils::register_impl(connection, |x: i32, y: i32| x + y).unwrap();
661
662        let added = crate::select(my_add(1, 2)).get_result::<i32>(connection);
663        assert_eq!(Ok(3), added);
664    }
665
666    #[diesel_test_helper::test]
667    fn register_noarg_function() {
668        let connection = &mut connection();
669        answer_utils::register_impl(connection, || 42).unwrap();
670
671        let answer = crate::select(answer()).get_result::<i32>(connection);
672        assert_eq!(Ok(42), answer);
673    }
674
675    #[diesel_test_helper::test]
676    fn register_nondeterministic_noarg_function() {
677        let connection = &mut connection();
678        answer_utils::register_nondeterministic_impl(connection, || 42).unwrap();
679
680        let answer = crate::select(answer()).get_result::<i32>(connection);
681        assert_eq!(Ok(42), answer);
682    }
683
684    #[diesel_test_helper::test]
685    fn register_nondeterministic_function() {
686        let connection = &mut connection();
687        let mut y = 0;
688        add_counter_utils::register_nondeterministic_impl(connection, move |x: i32| {
689            y += 1;
690            x + y
691        })
692        .unwrap();
693
694        let added = crate::select((add_counter(1), add_counter(1), add_counter(1)))
695            .get_result::<(i32, i32, i32)>(connection);
696        assert_eq!(Ok((2, 3, 4)), added);
697    }
698
699    #[derive(Default)]
700    struct MySum {
701        sum: i32,
702    }
703
704    impl SqliteAggregateFunction<i32> for MySum {
705        type Output = i32;
706
707        fn step(&mut self, expr: i32) {
708            self.sum += expr;
709        }
710
711        fn finalize(aggregator: Option<Self>) -> Self::Output {
712            aggregator.map(|a| a.sum).unwrap_or_default()
713        }
714    }
715
716    table! {
717        my_sum_example {
718            id -> Integer,
719            value -> Integer,
720        }
721    }
722
723    #[diesel_test_helper::test]
724    fn register_aggregate_function() {
725        use self::my_sum_example::dsl::*;
726
727        let connection = &mut connection();
728        crate::sql_query(
729            "CREATE TABLE my_sum_example (id integer primary key autoincrement, value integer)",
730        )
731        .execute(connection)
732        .unwrap();
733        crate::sql_query("INSERT INTO my_sum_example (value) VALUES (1), (2), (3)")
734            .execute(connection)
735            .unwrap();
736
737        my_sum_utils::register_impl::<MySum, _>(connection).unwrap();
738
739        let result = my_sum_example
740            .select(my_sum(value))
741            .get_result::<i32>(connection);
742        assert_eq!(Ok(6), result);
743    }
744
745    #[diesel_test_helper::test]
746    fn register_aggregate_function_returns_finalize_default_on_empty_set() {
747        use self::my_sum_example::dsl::*;
748
749        let connection = &mut connection();
750        crate::sql_query(
751            "CREATE TABLE my_sum_example (id integer primary key autoincrement, value integer)",
752        )
753        .execute(connection)
754        .unwrap();
755
756        my_sum_utils::register_impl::<MySum, _>(connection).unwrap();
757
758        let result = my_sum_example
759            .select(my_sum(value))
760            .get_result::<i32>(connection);
761        assert_eq!(Ok(0), result);
762    }
763
764    #[derive(Default)]
765    struct RangeMax<T> {
766        max_value: Option<T>,
767    }
768
769    impl<T: Default + Ord + Copy + Clone> SqliteAggregateFunction<(T, T, T)> for RangeMax<T> {
770        type Output = Option<T>;
771
772        fn step(&mut self, (x0, x1, x2): (T, T, T)) {
773            let max = if x0 >= x1 && x0 >= x2 {
774                x0
775            } else if x1 >= x0 && x1 >= x2 {
776                x1
777            } else {
778                x2
779            };
780
781            self.max_value = match self.max_value {
782                Some(current_max_value) if max > current_max_value => Some(max),
783                None => Some(max),
784                _ => self.max_value,
785            };
786        }
787
788        fn finalize(aggregator: Option<Self>) -> Self::Output {
789            aggregator?.max_value
790        }
791    }
792
793    table! {
794        range_max_example {
795            id -> Integer,
796            value1 -> Integer,
797            value2 -> Integer,
798            value3 -> Integer,
799        }
800    }
801
802    #[diesel_test_helper::test]
803    fn register_aggregate_multiarg_function() {
804        use self::range_max_example::dsl::*;
805
806        let connection = &mut connection();
807        crate::sql_query(
808            r#"CREATE TABLE range_max_example (
809                id integer primary key autoincrement,
810                value1 integer,
811                value2 integer,
812                value3 integer
813            )"#,
814        )
815        .execute(connection)
816        .unwrap();
817        crate::sql_query(
818            "INSERT INTO range_max_example (value1, value2, value3) VALUES (3, 2, 1), (2, 2, 2)",
819        )
820        .execute(connection)
821        .unwrap();
822
823        range_max_utils::register_impl::<RangeMax<i32>, _, _, _>(connection).unwrap();
824        let result = range_max_example
825            .select(range_max(value1, value2, value3))
826            .get_result::<Option<i32>>(connection)
827            .unwrap();
828        assert_eq!(Some(3), result);
829    }
830
831    table! {
832        my_collation_example {
833            id -> Integer,
834            value -> Text,
835        }
836    }
837
838    #[diesel_test_helper::test]
839    fn register_collation_function() {
840        use self::my_collation_example::dsl::*;
841
842        let connection = &mut connection();
843
844        connection
845            .register_collation("RUSTNOCASE", |rhs, lhs| {
846                rhs.to_lowercase().cmp(&lhs.to_lowercase())
847            })
848            .unwrap();
849
850        crate::sql_query(
851                "CREATE TABLE my_collation_example (id integer primary key autoincrement, value text collate RUSTNOCASE)",
852            ).execute(connection)
853            .unwrap();
854        crate::sql_query(
855            "INSERT INTO my_collation_example (value) VALUES ('foo'), ('FOo'), ('f00')",
856        )
857        .execute(connection)
858        .unwrap();
859
860        let result = my_collation_example
861            .filter(value.eq("foo"))
862            .select(value)
863            .load::<String>(connection);
864        assert_eq!(
865            Ok(&["foo".to_owned(), "FOo".to_owned()][..]),
866            result.as_ref().map(|vec| vec.as_ref())
867        );
868
869        let result = my_collation_example
870            .filter(value.eq("FOO"))
871            .select(value)
872            .load::<String>(connection);
873        assert_eq!(
874            Ok(&["foo".to_owned(), "FOo".to_owned()][..]),
875            result.as_ref().map(|vec| vec.as_ref())
876        );
877
878        let result = my_collation_example
879            .filter(value.eq("f00"))
880            .select(value)
881            .load::<String>(connection);
882        assert_eq!(
883            Ok(&["f00".to_owned()][..]),
884            result.as_ref().map(|vec| vec.as_ref())
885        );
886
887        let result = my_collation_example
888            .filter(value.eq("F00"))
889            .select(value)
890            .load::<String>(connection);
891        assert_eq!(
892            Ok(&["f00".to_owned()][..]),
893            result.as_ref().map(|vec| vec.as_ref())
894        );
895
896        let result = my_collation_example
897            .filter(value.eq("oof"))
898            .select(value)
899            .load::<String>(connection);
900        assert_eq!(Ok(&[][..]), result.as_ref().map(|vec| vec.as_ref()));
901    }
902
903    // regression test for https://github.com/diesel-rs/diesel/issues/3425
904    #[diesel_test_helper::test]
905    fn test_correct_seralization_of_owned_strings() {
906        use crate::prelude::*;
907
908        #[derive(Debug, crate::expression::AsExpression)]
909        #[diesel(sql_type = diesel::sql_types::Text)]
910        struct CustomWrapper(String);
911
912        impl crate::serialize::ToSql<Text, Sqlite> for CustomWrapper {
913            fn to_sql<'b>(
914                &'b self,
915                out: &mut crate::serialize::Output<'b, '_, Sqlite>,
916            ) -> crate::serialize::Result {
917                out.set_value(self.0.to_string());
918                Ok(crate::serialize::IsNull::No)
919            }
920        }
921
922        let connection = &mut connection();
923
924        let res = crate::select(
925            CustomWrapper("".into())
926                .into_sql::<crate::sql_types::Text>()
927                .nullable(),
928        )
929        .get_result::<Option<String>>(connection)
930        .unwrap();
931        assert_eq!(res, Some(String::new()));
932    }
933
934    #[diesel_test_helper::test]
935    fn test_correct_seralization_of_owned_bytes() {
936        use crate::prelude::*;
937
938        #[derive(Debug, crate::expression::AsExpression)]
939        #[diesel(sql_type = diesel::sql_types::Binary)]
940        struct CustomWrapper(Vec<u8>);
941
942        impl crate::serialize::ToSql<crate::sql_types::Binary, Sqlite> for CustomWrapper {
943            fn to_sql<'b>(
944                &'b self,
945                out: &mut crate::serialize::Output<'b, '_, Sqlite>,
946            ) -> crate::serialize::Result {
947                out.set_value(self.0.clone());
948                Ok(crate::serialize::IsNull::No)
949            }
950        }
951
952        let connection = &mut connection();
953
954        let res = crate::select(
955            CustomWrapper(Vec::new())
956                .into_sql::<crate::sql_types::Binary>()
957                .nullable(),
958        )
959        .get_result::<Option<Vec<u8>>>(connection)
960        .unwrap();
961        assert_eq!(res, Some(Vec::new()));
962    }
963
964    #[diesel_test_helper::test]
965    fn correctly_handle_empty_query() {
966        let check_empty_query_error = |r: crate::QueryResult<usize>| {
967            assert!(r.is_err());
968            let err = r.unwrap_err();
969            assert!(
970                matches!(err, crate::result::Error::QueryBuilderError(ref b) if b.is::<crate::result::EmptyQuery>()),
971                "Expected a query builder error, but got {err}"
972            );
973        };
974        let connection = &mut SqliteConnection::establish(":memory:").unwrap();
975        check_empty_query_error(crate::sql_query("").execute(connection));
976        check_empty_query_error(crate::sql_query("   ").execute(connection));
977        check_empty_query_error(crate::sql_query("\n\t").execute(connection));
978        check_empty_query_error(crate::sql_query("-- SELECT 1;").execute(connection));
979    }
980
981    #[cfg(all(target_family = "wasm", target_os = "unknown"))]
982    #[wasm_bindgen_test::wasm_bindgen_test]
983    fn test_sqlite_wasm_vfs_default() {
984        SqliteConnection::establish("test_sqlite_wasm_vfs_default.db").unwrap();
985    }
986
987    #[cfg(all(target_family = "wasm", target_os = "unknown"))]
988    #[wasm_bindgen_test::wasm_bindgen_test]
989    async fn test_sqlite_wasm_vfs_opfs_sahpool() {
990        let util = sqlite_wasm_rs::export::install_opfs_sahpool(None, false)
991            .await
992            .unwrap();
993        SqliteConnection::establish("file:test_sqlite_wasm_vfs_opfs_sahpool.db?vfs=opfs-sahpool")
994            .unwrap();
995        assert!(util.get_file_count() > 0);
996    }
997}
diesel/sqlite/connection/mod.rs

diesel/sqlite/connection/
mod.rs
