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    // We potentially need to store a serialized
175    // database in here to make sure the database bytes
176    // live as long as the connection
177    // This is used by SqliteConnection::deserialize_readonly_database_from_buffer
178    // only
179    // This field needs to come after the RawConnection
180    // as we need to make sure the data are still there until the
181    // connection is dropped
182    serialized_data: Vec<u8>,
183}
184
185// This relies on the invariant that RawConnection or Statement are never
186// leaked. If a reference to one of those was held on a different thread, this
187// would not be thread safe.
188#[allow(unsafe_code)]
189unsafe impl Send for SqliteConnection {}
190
191impl SimpleConnection for SqliteConnection {
192    fn batch_execute(&mut self, query: &str) -> QueryResult<()> {
193        self.instrumentation
194            .on_connection_event(InstrumentationEvent::StartQuery {
195                query: &StrQueryHelper::new(query),
196            });
197        let resp = self.raw_connection.exec(query);
198        self.instrumentation
199            .on_connection_event(InstrumentationEvent::FinishQuery {
200                query: &StrQueryHelper::new(query),
201                error: resp.as_ref().err(),
202            });
203        resp
204    }
205}
206
207impl ConnectionSealed for SqliteConnection {}
208
209impl Connection for SqliteConnection {
210    type Backend = Sqlite;
211    type TransactionManager = AnsiTransactionManager;
212
213    /// Establish a connection to the database specified by `database_url`.
214    ///
215    /// See [SqliteConnection] for supported `database_url`.
216    ///
217    /// If the database does not exist, this method will try to
218    /// create a new database and then establish a connection to it.
219    ///
220    /// ## WASM support
221    ///
222    /// If you plan to use this connection type on the `wasm32-unknown-unknown` target please
223    /// make sure to read the following notes:
224    ///
225    /// * The database is stored in memory by default.
226    /// * Persistent VFS (Virtual File Systems) is optional,
227    ///   see <https://github.com/Spxg/sqlite-wasm-rs> for details
228    fn establish(database_url: &str) -> ConnectionResult<Self> {
229        let mut instrumentation = DynInstrumentation::default_instrumentation();
230        instrumentation.on_connection_event(InstrumentationEvent::StartEstablishConnection {
231            url: database_url,
232        });
233
234        let establish_result = Self::establish_inner(database_url);
235        instrumentation.on_connection_event(InstrumentationEvent::FinishEstablishConnection {
236            url: database_url,
237            error: establish_result.as_ref().err(),
238        });
239        let mut conn = establish_result?;
240        conn.instrumentation = instrumentation;
241        Ok(conn)
242    }
243
244    fn execute_returning_count<T>(&mut self, source: &T) -> QueryResult<usize>
245    where
246        T: QueryFragment<Self::Backend> + QueryId,
247    {
248        let statement_use = self.prepared_query(source)?;
249        statement_use.run().and_then(|_| {
250            self.raw_connection
251                .rows_affected_by_last_query()
252                .map_err(Error::DeserializationError)
253        })
254    }
255
256    fn transaction_state(&mut self) -> &mut AnsiTransactionManager
257    where
258        Self: Sized,
259    {
260        &mut self.transaction_state
261    }
262
263    fn instrumentation(&mut self) -> &mut dyn Instrumentation {
264        &mut *self.instrumentation
265    }
266
267    fn set_instrumentation(&mut self, instrumentation: impl Instrumentation) {
268        self.instrumentation = instrumentation.into();
269    }
270
271    fn set_prepared_statement_cache_size(&mut self, size: CacheSize) {
272        self.statement_cache.set_cache_size(size);
273    }
274}
275
276impl LoadConnection<DefaultLoadingMode> for SqliteConnection {
277    type Cursor<'conn, 'query> = StatementIterator<'conn, 'query>;
278    type Row<'conn, 'query> = self::row::SqliteRow<'conn, 'query>;
279
280    fn load<'conn, 'query, T>(
281        &'conn mut self,
282        source: T,
283    ) -> QueryResult<Self::Cursor<'conn, 'query>>
284    where
285        T: Query + QueryFragment<Self::Backend> + QueryId + 'query,
286        Self::Backend: QueryMetadata<T::SqlType>,
287    {
288        let statement = self.prepared_query(source)?;
289
290        Ok(StatementIterator::new(statement))
291    }
292}
293
294impl WithMetadataLookup for SqliteConnection {
295    fn metadata_lookup(&mut self) -> &mut <Sqlite as TypeMetadata>::MetadataLookup {
296        &mut self.metadata_lookup
297    }
298}
299
300#[cfg(feature = "r2d2")]
301impl crate::r2d2::R2D2Connection for crate::sqlite::SqliteConnection {
302    fn ping(&mut self) -> QueryResult<()> {
303        use crate::RunQueryDsl;
304
305        crate::r2d2::CheckConnectionQuery.execute(self).map(|_| ())
306    }
307
308    fn is_broken(&mut self) -> bool {
309        AnsiTransactionManager::is_broken_transaction_manager(self)
310    }
311}
312
313impl MultiConnectionHelper for SqliteConnection {
314    fn to_any<'a>(
315        lookup: &mut <Self::Backend as crate::sql_types::TypeMetadata>::MetadataLookup,
316    ) -> &mut (dyn std::any::Any + 'a) {
317        lookup
318    }
319
320    fn from_any(
321        lookup: &mut dyn std::any::Any,
322    ) -> Option<&mut <Self::Backend as crate::sql_types::TypeMetadata>::MetadataLookup> {
323        lookup.downcast_mut()
324    }
325}
326
327impl SqliteConnection {
328    /// Run a transaction with `BEGIN IMMEDIATE`
329    ///
330    /// This method will return an error if a transaction is already open.
331    ///
332    /// # Example
333    ///
334    /// ```rust
335    /// # include!("../../doctest_setup.rs");
336    /// #
337    /// # fn main() {
338    /// #     run_test().unwrap();
339    /// # }
340    /// #
341    /// # fn run_test() -> QueryResult<()> {
342    /// #     let mut conn = SqliteConnection::establish(":memory:").unwrap();
343    /// conn.immediate_transaction(|conn| {
344    ///     // Do stuff in a transaction
345    ///     Ok(())
346    /// })
347    /// # }
348    /// ```
349    pub fn immediate_transaction<T, E, F>(&mut self, f: F) -> Result<T, E>
350    where
351        F: FnOnce(&mut Self) -> Result<T, E>,
352        E: From<Error>,
353    {
354        self.transaction_sql(f, "BEGIN IMMEDIATE")
355    }
356
357    /// Run a transaction with `BEGIN EXCLUSIVE`
358    ///
359    /// This method will return an error if a transaction is already open.
360    ///
361    /// # Example
362    ///
363    /// ```rust
364    /// # include!("../../doctest_setup.rs");
365    /// #
366    /// # fn main() {
367    /// #     run_test().unwrap();
368    /// # }
369    /// #
370    /// # fn run_test() -> QueryResult<()> {
371    /// #     let mut conn = SqliteConnection::establish(":memory:").unwrap();
372    /// conn.exclusive_transaction(|conn| {
373    ///     // Do stuff in a transaction
374    ///     Ok(())
375    /// })
376    /// # }
377    /// ```
378    pub fn exclusive_transaction<T, E, F>(&mut self, f: F) -> Result<T, E>
379    where
380        F: FnOnce(&mut Self) -> Result<T, E>,
381        E: From<Error>,
382    {
383        self.transaction_sql(f, "BEGIN EXCLUSIVE")
384    }
385
386    fn transaction_sql<T, E, F>(&mut self, f: F, sql: &str) -> Result<T, E>
387    where
388        F: FnOnce(&mut Self) -> Result<T, E>,
389        E: From<Error>,
390    {
391        AnsiTransactionManager::begin_transaction_sql(&mut *self, sql)?;
392        match f(&mut *self) {
393            Ok(value) => {
394                AnsiTransactionManager::commit_transaction(&mut *self)?;
395                Ok(value)
396            }
397            Err(e) => {
398                AnsiTransactionManager::rollback_transaction(&mut *self)?;
399                Err(e)
400            }
401        }
402    }
403
404    fn prepared_query<'conn, 'query, T>(
405        &'conn mut self,
406        source: T,
407    ) -> QueryResult<StatementUse<'conn, 'query>>
408    where
409        T: QueryFragment<Sqlite> + QueryId + 'query,
410    {
411        self.instrumentation
412            .on_connection_event(InstrumentationEvent::StartQuery {
413                query: &crate::debug_query(&source),
414            });
415        let raw_connection = &self.raw_connection;
416        let cache = &mut self.statement_cache;
417        let statement = match cache.cached_statement(
418            &source,
419            &Sqlite,
420            &[],
421            raw_connection,
422            Statement::prepare,
423            &mut *self.instrumentation,
424        ) {
425            Ok(statement) => statement,
426            Err(e) => {
427                self.instrumentation
428                    .on_connection_event(InstrumentationEvent::FinishQuery {
429                        query: &crate::debug_query(&source),
430                        error: Some(&e),
431                    });
432
433                return Err(e);
434            }
435        };
436
437        StatementUse::bind(statement, source, &mut *self.instrumentation)
438    }
439
440    #[doc(hidden)]
441    pub fn register_sql_function<ArgsSqlType, RetSqlType, Args, Ret, F>(
442        &mut self,
443        fn_name: &str,
444        deterministic: bool,
445        mut f: F,
446    ) -> QueryResult<()>
447    where
448        F: FnMut(Args) -> Ret + std::panic::UnwindSafe + Send + 'static,
449        Args: FromSqlRow<ArgsSqlType, Sqlite> + StaticallySizedRow<ArgsSqlType, Sqlite>,
450        Ret: ToSql<RetSqlType, Sqlite>,
451        Sqlite: HasSqlType<RetSqlType>,
452    {
453        functions::register(
454            &self.raw_connection,
455            fn_name,
456            deterministic,
457            move |_, args| f(args),
458        )
459    }
460
461    #[doc(hidden)]
462    pub fn register_noarg_sql_function<RetSqlType, Ret, F>(
463        &self,
464        fn_name: &str,
465        deterministic: bool,
466        f: F,
467    ) -> QueryResult<()>
468    where
469        F: FnMut() -> Ret + std::panic::UnwindSafe + Send + 'static,
470        Ret: ToSql<RetSqlType, Sqlite>,
471        Sqlite: HasSqlType<RetSqlType>,
472    {
473        functions::register_noargs(&self.raw_connection, fn_name, deterministic, f)
474    }
475
476    #[doc(hidden)]
477    pub fn register_aggregate_function<ArgsSqlType, RetSqlType, Args, Ret, A>(
478        &mut self,
479        fn_name: &str,
480    ) -> QueryResult<()>
481    where
482        A: SqliteAggregateFunction<Args, Output = Ret> + 'static + Send + std::panic::UnwindSafe,
483        Args: FromSqlRow<ArgsSqlType, Sqlite> + StaticallySizedRow<ArgsSqlType, Sqlite>,
484        Ret: ToSql<RetSqlType, Sqlite>,
485        Sqlite: HasSqlType<RetSqlType>,
486    {
487        functions::register_aggregate::<_, _, _, _, A>(&self.raw_connection, fn_name)
488    }
489
490    /// Register a collation function.
491    ///
492    /// `collation` must always return the same answer given the same inputs.
493    /// If `collation` panics and unwinds the stack, the process is aborted, since it is used
494    /// across a C FFI boundary, which cannot be unwound across and there is no way to
495    /// signal failures via the SQLite interface in this case..
496    ///
497    /// If the name is already registered it will be overwritten.
498    ///
499    /// This method will return an error if registering the function fails, either due to an
500    /// out-of-memory situation or because a collation with that name already exists and is
501    /// currently being used in parallel by a query.
502    ///
503    /// The collation needs to be specified when creating a table:
504    /// `CREATE TABLE my_table ( str TEXT COLLATE MY_COLLATION )`,
505    /// where `MY_COLLATION` corresponds to name passed as `collation_name`.
506    ///
507    /// # Example
508    ///
509    /// ```rust
510    /// # include!("../../doctest_setup.rs");
511    /// #
512    /// # fn main() {
513    /// #     run_test().unwrap();
514    /// # }
515    /// #
516    /// # fn run_test() -> QueryResult<()> {
517    /// #     let mut conn = SqliteConnection::establish(":memory:").unwrap();
518    /// // sqlite NOCASE only works for ASCII characters,
519    /// // this collation allows handling UTF-8 (barring locale differences)
520    /// conn.register_collation("RUSTNOCASE", |rhs, lhs| {
521    ///     rhs.to_lowercase().cmp(&lhs.to_lowercase())
522    /// })
523    /// # }
524    /// ```
525    pub fn register_collation<F>(&mut self, collation_name: &str, collation: F) -> QueryResult<()>
526    where
527        F: Fn(&str, &str) -> std::cmp::Ordering + Send + 'static + std::panic::UnwindSafe,
528    {
529        self.raw_connection
530            .register_collation_function(collation_name, collation)
531    }
532
533    /// Serialize the current SQLite database into a byte buffer.
534    ///
535    /// The serialized data is identical to the data that would be written to disk if the database
536    /// was saved in a file.
537    ///
538    /// # Returns
539    ///
540    /// This function returns a byte slice representing the serialized database.
541    pub fn serialize_database_to_buffer(&mut self) -> SerializedDatabase {
542        self.raw_connection.serialize()
543    }
544
545    /// Deserialize an SQLite database from a byte buffer.
546    ///
547    /// This function takes a byte slice and attempts to deserialize it into a SQLite database.
548    /// If successful, the database is loaded into the connection. If the deserialization fails,
549    /// an error is returned.
550    ///
551    /// The database is opened in READONLY mode.
552    ///
553    /// # Example
554    ///
555    /// ```no_run
556    /// # use diesel::sqlite::SerializedDatabase;
557    /// # use diesel::sqlite::SqliteConnection;
558    /// # use diesel::result::QueryResult;
559    /// # use diesel::sql_query;
560    /// # use diesel::Connection;
561    /// # use diesel::RunQueryDsl;
562    /// # fn main() {
563    /// let connection = &mut SqliteConnection::establish(":memory:").unwrap();
564    ///
565    /// sql_query("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)")
566    ///     .execute(connection).unwrap();
567    /// sql_query("INSERT INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com'), ('Jane Doe', 'jane.doe@example.com')")
568    ///     .execute(connection).unwrap();
569    ///
570    /// // Serialize the database to a byte vector
571    /// let serialized_db: SerializedDatabase = connection.serialize_database_to_buffer();
572    ///
573    /// // Create a new in-memory SQLite database
574    /// let connection = &mut SqliteConnection::establish(":memory:").unwrap();
575    ///
576    /// // Deserialize the byte vector into the new database
577    /// connection.deserialize_readonly_database_from_buffer(serialized_db.as_slice()).unwrap();
578    /// #
579    /// # }
580    /// ```
581    pub fn deserialize_readonly_database_from_buffer(&mut self, data: &[u8]) -> QueryResult<()> {
582        // we copy the buffer here
583        // to make sure the underlying buffer lives as long as the connection
584        self.serialized_data = data.to_vec();
585        self.raw_connection.deserialize(&self.serialized_data)
586    }
587
588    fn register_diesel_sql_functions(&self) -> QueryResult<()> {
589        use crate::sql_types::{Integer, Text};
590
591        functions::register::<Text, Integer, _, _, _>(
592            &self.raw_connection,
593            "diesel_manage_updated_at",
594            false,
595            |conn, table_name: String| {
596                conn.exec(&::alloc::__export::must_use({
        ::alloc::fmt::format(format_args!("CREATE TRIGGER __diesel_manage_updated_at_{0}\nAFTER UPDATE ON {0}\nFOR EACH ROW WHEN\n  old.updated_at IS NULL AND\n  new.updated_at IS NULL OR\n  old.updated_at == new.updated_at\nBEGIN\n  UPDATE {0}\n  SET updated_at = CURRENT_TIMESTAMP\n  WHERE ROWID = new.ROWID;\nEND\n",
                table_name))
    })format!(
597                    include_str!("diesel_manage_updated_at.sql"),
598                    table_name = table_name
599                ))
600                .expect("Failed to create trigger");
601                0 // have to return *something*
602            },
603        )
604    }
605
606    fn establish_inner(database_url: &str) -> Result<SqliteConnection, ConnectionError> {
607        use crate::result::ConnectionError::CouldntSetupConfiguration;
608        let raw_connection = RawConnection::establish(database_url)?;
609        let conn = Self {
610            statement_cache: StatementCache::new(),
611            raw_connection,
612            transaction_state: AnsiTransactionManager::default(),
613            metadata_lookup: (),
614            instrumentation: DynInstrumentation::none(),
615            serialized_data: Vec::new(),
616        };
617        conn.register_diesel_sql_functions()
618            .map_err(CouldntSetupConfiguration)?;
619        Ok(conn)
620    }
621}
622
623fn error_message(err_code: libc::c_int) -> &'static str {
624    ffi::code_to_str(err_code)
625}
626
627#[cfg(test)]
628mod tests {
629    use super::*;
630    use crate::dsl::sql;
631    use crate::prelude::*;
632    use crate::sql_types::{Integer, Text};
633
634    fn connection() -> SqliteConnection {
635        SqliteConnection::establish(":memory:").unwrap()
636    }
637
638    #[declare_sql_function]
639    extern "SQL" {
640        fn fun_case(x: Text) -> Text;
641        fn my_add(x: Integer, y: Integer) -> Integer;
642        fn answer() -> Integer;
643        fn add_counter(x: Integer) -> Integer;
644
645        #[aggregate]
646        fn my_sum(expr: Integer) -> Integer;
647        #[aggregate]
648        fn range_max(expr1: Integer, expr2: Integer, expr3: Integer) -> Nullable<Integer>;
649    }
650
651    #[diesel_test_helper::test]
652    fn database_serializes_and_deserializes_successfully() {
653        let expected_users = vec![
654            (
655                1,
656                "John Doe".to_string(),
657                "john.doe@example.com".to_string(),
658            ),
659            (
660                2,
661                "Jane Doe".to_string(),
662                "jane.doe@example.com".to_string(),
663            ),
664        ];
665
666        let conn1 = &mut connection();
667        let _ =
668            crate::sql_query("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)")
669                .execute(conn1);
670        let _ = crate::sql_query("INSERT INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com'), ('Jane Doe', 'jane.doe@example.com')")
671            .execute(conn1);
672
673        let serialized_database = conn1.serialize_database_to_buffer();
674
675        let conn2 = &mut connection();
676        conn2
677            .deserialize_readonly_database_from_buffer(serialized_database.as_slice())
678            .unwrap();
679
680        let query = sql::<(Integer, Text, Text)>("SELECT id, name, email FROM users ORDER BY id");
681        let actual_users = query.load::<(i32, String, String)>(conn2).unwrap();
682
683        assert_eq!(expected_users, actual_users);
684        // drop the database here
685        // and requery the database to make sure the database owns
686        // required data
687        std::mem::drop(serialized_database);
688        let query = sql::<(Integer, Text, Text)>("SELECT id, name, email FROM users ORDER BY id");
689        let actual_users = query.load::<(i32, String, String)>(conn2).unwrap();
690
691        assert_eq!(expected_users, actual_users);
692    }
693
694    #[diesel_test_helper::test]
695    fn register_custom_function() {
696        let connection = &mut connection();
697        fun_case_utils::register_impl(connection, |x: String| {
698            x.chars()
699                .enumerate()
700                .map(|(i, c)| {
701                    if i % 2 == 0 {
702                        c.to_lowercase().to_string()
703                    } else {
704                        c.to_uppercase().to_string()
705                    }
706                })
707                .collect::<String>()
708        })
709        .unwrap();
710
711        let mapped_string = crate::select(fun_case("foobar"))
712            .get_result::<String>(connection)
713            .unwrap();
714        assert_eq!("fOoBaR", mapped_string);
715    }
716
717    #[diesel_test_helper::test]
718    fn register_multiarg_function() {
719        let connection = &mut connection();
720        my_add_utils::register_impl(connection, |x: i32, y: i32| x + y).unwrap();
721
722        let added = crate::select(my_add(1, 2)).get_result::<i32>(connection);
723        assert_eq!(Ok(3), added);
724    }
725
726    #[diesel_test_helper::test]
727    fn register_noarg_function() {
728        let connection = &mut connection();
729        answer_utils::register_impl(connection, || 42).unwrap();
730
731        let answer = crate::select(answer()).get_result::<i32>(connection);
732        assert_eq!(Ok(42), answer);
733    }
734
735    #[diesel_test_helper::test]
736    fn register_nondeterministic_noarg_function() {
737        let connection = &mut connection();
738        answer_utils::register_nondeterministic_impl(connection, || 42).unwrap();
739
740        let answer = crate::select(answer()).get_result::<i32>(connection);
741        assert_eq!(Ok(42), answer);
742    }
743
744    #[diesel_test_helper::test]
745    fn register_nondeterministic_function() {
746        let connection = &mut connection();
747        let mut y = 0;
748        add_counter_utils::register_nondeterministic_impl(connection, move |x: i32| {
749            y += 1;
750            x + y
751        })
752        .unwrap();
753
754        let added = crate::select((add_counter(1), add_counter(1), add_counter(1)))
755            .get_result::<(i32, i32, i32)>(connection);
756        assert_eq!(Ok((2, 3, 4)), added);
757    }
758
759    #[derive(Default)]
760    struct MySum {
761        sum: i32,
762    }
763
764    impl SqliteAggregateFunction<i32> for MySum {
765        type Output = i32;
766
767        fn step(&mut self, expr: i32) {
768            self.sum += expr;
769        }
770
771        fn finalize(aggregator: Option<Self>) -> Self::Output {
772            aggregator.map(|a| a.sum).unwrap_or_default()
773        }
774    }
775
776    table! {
777        my_sum_example {
778            id -> Integer,
779            value -> Integer,
780        }
781    }
782
783    #[diesel_test_helper::test]
784    fn register_aggregate_function() {
785        use self::my_sum_example::dsl::*;
786
787        let connection = &mut connection();
788        crate::sql_query(
789            "CREATE TABLE my_sum_example (id integer primary key autoincrement, value integer)",
790        )
791        .execute(connection)
792        .unwrap();
793        crate::sql_query("INSERT INTO my_sum_example (value) VALUES (1), (2), (3)")
794            .execute(connection)
795            .unwrap();
796
797        my_sum_utils::register_impl::<MySum, _>(connection).unwrap();
798
799        let result = my_sum_example
800            .select(my_sum(value))
801            .get_result::<i32>(connection);
802        assert_eq!(Ok(6), result);
803    }
804
805    #[diesel_test_helper::test]
806    fn register_aggregate_function_returns_finalize_default_on_empty_set() {
807        use self::my_sum_example::dsl::*;
808
809        let connection = &mut connection();
810        crate::sql_query(
811            "CREATE TABLE my_sum_example (id integer primary key autoincrement, value integer)",
812        )
813        .execute(connection)
814        .unwrap();
815
816        my_sum_utils::register_impl::<MySum, _>(connection).unwrap();
817
818        let result = my_sum_example
819            .select(my_sum(value))
820            .get_result::<i32>(connection);
821        assert_eq!(Ok(0), result);
822    }
823
824    #[derive(Default)]
825    struct RangeMax<T> {
826        max_value: Option<T>,
827    }
828
829    impl<T: Default + Ord + Copy + Clone> SqliteAggregateFunction<(T, T, T)> for RangeMax<T> {
830        type Output = Option<T>;
831
832        fn step(&mut self, (x0, x1, x2): (T, T, T)) {
833            let max = if x0 >= x1 && x0 >= x2 {
834                x0
835            } else if x1 >= x0 && x1 >= x2 {
836                x1
837            } else {
838                x2
839            };
840
841            self.max_value = match self.max_value {
842                Some(current_max_value) if max > current_max_value => Some(max),
843                None => Some(max),
844                _ => self.max_value,
845            };
846        }
847
848        fn finalize(aggregator: Option<Self>) -> Self::Output {
849            aggregator?.max_value
850        }
851    }
852
853    table! {
854        range_max_example {
855            id -> Integer,
856            value1 -> Integer,
857            value2 -> Integer,
858            value3 -> Integer,
859        }
860    }
861
862    #[diesel_test_helper::test]
863    fn register_aggregate_multiarg_function() {
864        use self::range_max_example::dsl::*;
865
866        let connection = &mut connection();
867        crate::sql_query(
868            r#"CREATE TABLE range_max_example (
869                id integer primary key autoincrement,
870                value1 integer,
871                value2 integer,
872                value3 integer
873            )"#,
874        )
875        .execute(connection)
876        .unwrap();
877        crate::sql_query(
878            "INSERT INTO range_max_example (value1, value2, value3) VALUES (3, 2, 1), (2, 2, 2)",
879        )
880        .execute(connection)
881        .unwrap();
882
883        range_max_utils::register_impl::<RangeMax<i32>, _, _, _>(connection).unwrap();
884        let result = range_max_example
885            .select(range_max(value1, value2, value3))
886            .get_result::<Option<i32>>(connection)
887            .unwrap();
888        assert_eq!(Some(3), result);
889    }
890
891    table! {
892        my_collation_example {
893            id -> Integer,
894            value -> Text,
895        }
896    }
897
898    #[diesel_test_helper::test]
899    fn register_collation_function() {
900        use self::my_collation_example::dsl::*;
901
902        let connection = &mut connection();
903
904        connection
905            .register_collation("RUSTNOCASE", |rhs, lhs| {
906                rhs.to_lowercase().cmp(&lhs.to_lowercase())
907            })
908            .unwrap();
909
910        crate::sql_query(
911                "CREATE TABLE my_collation_example (id integer primary key autoincrement, value text collate RUSTNOCASE)",
912            ).execute(connection)
913            .unwrap();
914        crate::sql_query(
915            "INSERT INTO my_collation_example (value) VALUES ('foo'), ('FOo'), ('f00')",
916        )
917        .execute(connection)
918        .unwrap();
919
920        let result = my_collation_example
921            .filter(value.eq("foo"))
922            .select(value)
923            .load::<String>(connection);
924        assert_eq!(
925            Ok(&["foo".to_owned(), "FOo".to_owned()][..]),
926            result.as_ref().map(|vec| vec.as_ref())
927        );
928
929        let result = my_collation_example
930            .filter(value.eq("FOO"))
931            .select(value)
932            .load::<String>(connection);
933        assert_eq!(
934            Ok(&["foo".to_owned(), "FOo".to_owned()][..]),
935            result.as_ref().map(|vec| vec.as_ref())
936        );
937
938        let result = my_collation_example
939            .filter(value.eq("f00"))
940            .select(value)
941            .load::<String>(connection);
942        assert_eq!(
943            Ok(&["f00".to_owned()][..]),
944            result.as_ref().map(|vec| vec.as_ref())
945        );
946
947        let result = my_collation_example
948            .filter(value.eq("F00"))
949            .select(value)
950            .load::<String>(connection);
951        assert_eq!(
952            Ok(&["f00".to_owned()][..]),
953            result.as_ref().map(|vec| vec.as_ref())
954        );
955
956        let result = my_collation_example
957            .filter(value.eq("oof"))
958            .select(value)
959            .load::<String>(connection);
960        assert_eq!(Ok(&[][..]), result.as_ref().map(|vec| vec.as_ref()));
961    }
962
963    // regression test for https://github.com/diesel-rs/diesel/issues/3425
964    #[diesel_test_helper::test]
965    fn test_correct_serialization_of_owned_strings() {
966        use crate::prelude::*;
967
968        #[derive(Debug, crate::expression::AsExpression)]
969        #[diesel(sql_type = diesel::sql_types::Text)]
970        struct CustomWrapper(String);
971
972        impl crate::serialize::ToSql<Text, Sqlite> for CustomWrapper {
973            fn to_sql<'b>(
974                &'b self,
975                out: &mut crate::serialize::Output<'b, '_, Sqlite>,
976            ) -> crate::serialize::Result {
977                out.set_value(self.0.to_string());
978                Ok(crate::serialize::IsNull::No)
979            }
980        }
981
982        let connection = &mut connection();
983
984        let res = crate::select(
985            CustomWrapper("".into())
986                .into_sql::<crate::sql_types::Text>()
987                .nullable(),
988        )
989        .get_result::<Option<String>>(connection)
990        .unwrap();
991        assert_eq!(res, Some(String::new()));
992    }
993
994    #[diesel_test_helper::test]
995    fn test_correct_serialization_of_owned_bytes() {
996        use crate::prelude::*;
997
998        #[derive(Debug, crate::expression::AsExpression)]
999        #[diesel(sql_type = diesel::sql_types::Binary)]
1000        struct CustomWrapper(Vec<u8>);
1001
1002        impl crate::serialize::ToSql<crate::sql_types::Binary, Sqlite> for CustomWrapper {
1003            fn to_sql<'b>(
1004                &'b self,
1005                out: &mut crate::serialize::Output<'b, '_, Sqlite>,
1006            ) -> crate::serialize::Result {
1007                out.set_value(self.0.clone());
1008                Ok(crate::serialize::IsNull::No)
1009            }
1010        }
1011
1012        let connection = &mut connection();
1013
1014        let res = crate::select(
1015            CustomWrapper(Vec::new())
1016                .into_sql::<crate::sql_types::Binary>()
1017                .nullable(),
1018        )
1019        .get_result::<Option<Vec<u8>>>(connection)
1020        .unwrap();
1021        assert_eq!(res, Some(Vec::new()));
1022    }
1023
1024    #[diesel_test_helper::test]
1025    fn correctly_handle_empty_query() {
1026        let check_empty_query_error = |r: crate::QueryResult<usize>| {
1027            assert!(r.is_err());
1028            let err = r.unwrap_err();
1029            assert!(
1030                matches!(err, crate::result::Error::QueryBuilderError(ref b) if b.is::<crate::result::EmptyQuery>()),
1031                "Expected a query builder error, but got {err}"
1032            );
1033        };
1034        let connection = &mut SqliteConnection::establish(":memory:").unwrap();
1035        check_empty_query_error(crate::sql_query("").execute(connection));
1036        check_empty_query_error(crate::sql_query("   ").execute(connection));
1037        check_empty_query_error(crate::sql_query("\n\t").execute(connection));
1038        check_empty_query_error(crate::sql_query("-- SELECT 1;").execute(connection));
1039    }
1040
1041    #[diesel_test_helper::test]
1042    fn aggregate_function_works_with_aligned_data() {
1043        #[derive(Debug, Default)]
1044        #[repr(align(64))]
1045        struct OverAligned;
1046
1047        impl SqliteAggregateFunction<i32> for OverAligned {
1048            type Output = i64;
1049
1050            fn step(&mut self, _value: i32) {
1051                let need = core::mem::align_of::<Self>();
1052                let got = core::mem::align_of_val(self);
1053                assert_eq!(need, got);
1054            }
1055
1056            fn finalize(_agg: Option<Self>) -> i64 {
1057                0
1058            }
1059        }
1060        #[declare_sql_function]
1061        extern "SQL" {
1062            #[aggregate]
1063            fn over_aligned_sum(x: Integer) -> diesel::sql_types::BigInt;
1064        }
1065
1066        let mut conn = SqliteConnection::establish(":memory:").unwrap();
1067        over_aligned_sum_utils::register_impl::<OverAligned, _>(&mut conn).unwrap();
1068
1069        diesel::select(over_aligned_sum(1))
1070            .execute(&mut conn)
1071            .unwrap();
1072    }
1073
1074    #[diesel_test_helper::test]
1075    fn sum_twice() {
1076        #[derive(Default)]
1077        struct Sum(i32);
1078
1079        impl SqliteAggregateFunction<i32> for Sum {
1080            type Output = i32;
1081
1082            fn step(&mut self, value: i32) {
1083                self.0 += value;
1084            }
1085
1086            fn finalize(agg: Option<Self>) -> i32 {
1087                agg.map(|s| s.0).unwrap_or_default()
1088            }
1089        }
1090
1091        #[declare_sql_function]
1092        extern "SQL" {
1093            #[aggregate]
1094            fn my_sum(x: Integer) -> Integer;
1095        }
1096
1097        let mut conn = SqliteConnection::establish(":memory:").unwrap();
1098        my_sum_utils::register_impl::<Sum, _>(&mut conn).unwrap();
1099
1100        conn.batch_execute(
1101            "
1102            CREATE TABLE test(key1 INTEGER, key2 INTEGER);
1103            INSERT INTO test(key1, key2) VALUES (1, 2), (2, 4), (3, 6);
1104",
1105        )
1106        .unwrap();
1107
1108        table! {
1109            test (key1, key2) {
1110                key1 -> Integer,
1111                key2 -> Integer,
1112            }
1113        }
1114
1115        let (first_res, second_res) = test::table
1116            .select((my_sum(test::key1), my_sum(test::key2)))
1117            .get_result::<(i32, i32)>(&mut conn)
1118            .unwrap();
1119
1120        assert_eq!(first_res, 6);
1121        assert_eq!(second_res, 12);
1122
1123        conn.batch_execute("DELETE FROM test").unwrap();
1124        let (first_res, second_res) = test::table
1125            .select((my_sum(test::key1), my_sum(test::key2)))
1126            .get_result::<(i32, i32)>(&mut conn)
1127            .unwrap();
1128
1129        assert_eq!(first_res, 0);
1130        assert_eq!(second_res, 0);
1131    }
1132
1133    #[diesel_test_helper::test]
1134    fn test_injection() {
1135        diesel::table! {
1136            #[sql_name = "quote'table"]
1137            quote_table (id) {
1138                id -> Nullable<Integer>,
1139                name -> Nullable<Text>,
1140            }
1141        }
1142
1143        let mut conn = SqliteConnection::establish(":memory:").unwrap();
1144
1145        conn.batch_execute("CREATE TABLE \"quote'table\" (id INTEGER PRIMARY KEY, name TEXT);")
1146            .unwrap();
1147
1148        diesel::insert_into(quote_table::table)
1149            .values((quote_table::id.eq(1), quote_table::name.eq("Jane")))
1150            .execute(&mut conn)
1151            .unwrap();
1152
1153        let data = quote_table::table
1154            .load::<(Option<i32>, Option<String>)>(&mut conn)
1155            .unwrap();
1156        assert_eq!(data, [(Some(1), Some("Jane".to_owned()))]);
1157    }
1158}
diesel/sqlite/connection/mod.rs

diesel/sqlite/connection/
mod.rs
