diesel/sqlite/connection/
mod.rs

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

diesel/sqlite/connection/
mod.rs
