diesel/sqlite/connection/
mod.rs

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

diesel/sqlite/connection/
mod.rs
