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

diesel/sqlite/connection/
mod.rs
