diesel/sqlite/connection/
mod.rs

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

diesel/sqlite/connection/
mod.rs
