Skip to main content

rusqlite/
functions.rs

1//! Create or redefine SQL functions.
2//!
3//! # Example
4//!
5//! Adding a `regexp` function to a connection in which compiled regular
6//! expressions are cached in a `HashMap`. For an alternative implementation
7//! that uses SQLite's [Function Auxiliary Data](https://www.sqlite.org/c3ref/get_auxdata.html) interface
8//! to avoid recompiling regular expressions, see the unit tests for this
9//! module.
10//!
11//! ```rust
12//! use regex::Regex;
13//! use rusqlite::functions::FunctionFlags;
14//! use rusqlite::{Connection, Error, Result};
15//! use std::sync::Arc;
16//! type BoxError = Box<dyn std::error::Error + Send + Sync + 'static>;
17//!
18//! fn add_regexp_function(db: &Connection) -> Result<()> {
19//!     db.create_scalar_function(
20//!         "regexp",
21//!         2,
22//!         FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
23//!         move |ctx| {
24//!             assert_eq!(ctx.len(), 2, "called with unexpected number of arguments");
25//!             let regexp: Arc<Regex> = ctx.get_or_create_aux(0, |vr| -> Result<_, BoxError> {
26//!                 Ok(Regex::new(vr.as_str()?)?)
27//!             })?;
28//!             let is_match = {
29//!                 let text = ctx
30//!                     .get_raw(1)
31//!                     .as_str()
32//!                     .map_err(|e| Error::UserFunctionError(e.into()))?;
33//!
34//!                 regexp.is_match(text)
35//!             };
36//!
37//!             Ok(is_match)
38//!         },
39//!     )
40//! }
41//!
42//! fn main() -> Result<()> {
43//!     let db = Connection::open_in_memory()?;
44//!     add_regexp_function(&db)?;
45//!
46//!     let is_match: bool =
47//!         db.query_row("SELECT regexp('[aeiou]*', 'aaaaeeeiii')", [], |row| {
48//!             row.get(0)
49//!         })?;
50//!
51//!     assert!(is_match);
52//!     Ok(())
53//! }
54//! ```
55use std::any::Any;
56use std::ffi::{c_int, c_uint, c_void};
57use std::marker::PhantomData;
58use std::ops::Deref;
59use std::panic::{RefUnwindSafe, UnwindSafe, catch_unwind};
60use std::ptr;
61use std::slice;
62use std::sync::Arc;
63
64use crate::ffi::{self, sqlite3_context, sqlite3_value};
65
66use crate::context::set_result;
67use crate::types::{FromSql, FromSqlError, ToSql, ToSqlOutput, ValueRef};
68use crate::util::free_boxed_value;
69use crate::{Connection, Error, InnerConnection, Name, Result, str_to_cstring};
70
71unsafe fn report_error(ctx: *mut sqlite3_context, err: &Error) {
72    unsafe {
73        if let Error::SqliteFailure(ref err, ref s) = *err {
74            ffi::sqlite3_result_error_code(ctx, err.extended_code);
75            if let Some(Ok(cstr)) = s.as_ref().map(|s| str_to_cstring(s)) {
76                ffi::sqlite3_result_error(ctx, cstr.as_ptr(), -1);
77            }
78        } else {
79            ffi::sqlite3_result_error_code(ctx, ffi::SQLITE_CONSTRAINT_FUNCTION);
80            if let Ok(cstr) = str_to_cstring(&err.to_string()) {
81                ffi::sqlite3_result_error(ctx, cstr.as_ptr(), -1);
82            }
83        }
84    }
85}
86
87/// Context is a wrapper for the SQLite function
88/// evaluation context.
89pub struct Context<'a> {
90    ctx: *mut sqlite3_context,
91    args: &'a [*mut sqlite3_value],
92}
93
94impl Context<'_> {
95    /// Returns the number of arguments to the function.
96    #[inline]
97    #[must_use]
98    pub fn len(&self) -> usize {
99        self.args.len()
100    }
101
102    /// Returns `true` when there is no argument.
103    #[inline]
104    #[must_use]
105    pub fn is_empty(&self) -> bool {
106        self.args.is_empty()
107    }
108
109    /// Returns the `idx`th argument as a `T`.
110    ///
111    /// # Failure
112    ///
113    /// Will panic if `idx` is greater than or equal to
114    /// [`self.len()`](Context::len).
115    ///
116    /// Will return Err if the underlying SQLite type cannot be converted to a
117    /// `T`.
118    pub fn get<T: FromSql>(&self, idx: usize) -> Result<T> {
119        let arg = self.args[idx];
120        let value = unsafe { ValueRef::from_value(arg) };
121        FromSql::column_result(value).map_err(|err| match err {
122            FromSqlError::InvalidType => {
123                Error::InvalidFunctionParameterType(idx, value.data_type())
124            }
125            FromSqlError::OutOfRange(i) => Error::IntegralValueOutOfRange(idx, i),
126            FromSqlError::Utf8Error(err) => Error::Utf8Error(idx, err),
127            FromSqlError::Other(err) => {
128                Error::FromSqlConversionFailure(idx, value.data_type(), err)
129            }
130            FromSqlError::InvalidBlobSize { .. } => {
131                Error::FromSqlConversionFailure(idx, value.data_type(), Box::new(err))
132            }
133        })
134    }
135
136    /// Return raw pointer at `idx`
137    /// # Safety
138    /// This function is unsafe because it uses raw pointer and cast
139    #[cfg(feature = "pointer")]
140    #[must_use]
141    pub unsafe fn get_pointer<T: 'static>(
142        &self,
143        idx: usize,
144        ptr_type: &'static std::ffi::CStr,
145    ) -> Option<&T> {
146        let arg = self.args[idx];
147        debug_assert_eq!(unsafe { ffi::sqlite3_value_type(arg) }, ffi::SQLITE_NULL);
148        unsafe {
149            ffi::sqlite3_value_pointer(arg, ptr_type.as_ptr())
150                .cast::<T>()
151                .as_ref()
152        }
153    }
154
155    /// Returns the `idx`th argument as a `ValueRef`.
156    ///
157    /// # Failure
158    ///
159    /// Will panic if `idx` is greater than or equal to
160    /// [`self.len()`](Context::len).
161    #[inline]
162    #[must_use]
163    pub fn get_raw(&self, idx: usize) -> ValueRef<'_> {
164        let arg = self.args[idx];
165        unsafe { ValueRef::from_value(arg) }
166    }
167
168    /// Returns the `idx`th argument as a `SqlFnArg`.
169    /// To be used when the SQL function result is one of its arguments.
170    #[inline]
171    #[must_use]
172    pub fn get_arg(&self, idx: usize) -> SqlFnArg {
173        assert!(idx < self.len());
174        SqlFnArg { idx }
175    }
176
177    /// Returns the subtype of `idx`th argument.
178    ///
179    /// # Failure
180    ///
181    /// Will panic if `idx` is greater than or equal to
182    /// [`self.len()`](Context::len).
183    #[must_use]
184    pub fn get_subtype(&self, idx: usize) -> c_uint {
185        let arg = self.args[idx];
186        unsafe { ffi::sqlite3_value_subtype(arg) }
187    }
188
189    /// Fetch or insert the auxiliary data associated with a particular
190    /// parameter. This is intended to be an easier-to-use way of fetching it
191    /// compared to calling [`get_aux`](Context::get_aux) and
192    /// [`set_aux`](Context::set_aux) separately.
193    ///
194    /// See `https://www.sqlite.org/c3ref/get_auxdata.html` for a discussion of
195    /// this feature, or the unit tests of this module for an example.
196    ///
197    /// # Failure
198    ///
199    /// Will panic if `arg` is greater than or equal to
200    /// [`self.len()`](Context::len).
201    pub fn get_or_create_aux<T, E, F>(&self, arg: c_int, func: F) -> Result<Arc<T>>
202    where
203        T: Send + Sync + 'static,
204        E: Into<Box<dyn std::error::Error + Send + Sync + 'static>>,
205        F: FnOnce(ValueRef<'_>) -> Result<T, E>,
206    {
207        if let Some(v) = self.get_aux(arg)? {
208            Ok(v)
209        } else {
210            let vr = self.get_raw(arg as usize);
211            self.set_aux(
212                arg,
213                func(vr).map_err(|e| Error::UserFunctionError(e.into()))?,
214            )
215        }
216    }
217
218    /// Sets the auxiliary data associated with a particular parameter. See
219    /// `https://www.sqlite.org/c3ref/get_auxdata.html` for a discussion of
220    /// this feature, or the unit tests of this module for an example.
221    ///
222    /// # Failure
223    ///
224    /// Will panic if `arg` is greater than or equal to
225    /// [`self.len()`](Context::len).
226    pub fn set_aux<T: Send + Sync + 'static>(&self, arg: c_int, value: T) -> Result<Arc<T>> {
227        assert!(arg < self.len() as i32);
228        let orig: Arc<T> = Arc::new(value);
229        let inner: AuxInner = orig.clone();
230        let outer = Box::new(inner);
231        let raw: *mut AuxInner = Box::into_raw(outer);
232        unsafe {
233            ffi::sqlite3_set_auxdata(
234                self.ctx,
235                arg,
236                raw.cast(),
237                Some(free_boxed_value::<AuxInner>),
238            );
239        };
240        Ok(orig)
241    }
242
243    /// Gets the auxiliary data that was associated with a given parameter via
244    /// [`set_aux`](Context::set_aux). Returns `Ok(None)` if no data has been
245    /// associated, and Ok(Some(v)) if it has. Returns an error if the
246    /// requested type does not match.
247    ///
248    /// # Failure
249    ///
250    /// Will panic if `arg` is greater than or equal to
251    /// [`self.len()`](Context::len).
252    pub fn get_aux<T: Send + Sync + 'static>(&self, arg: c_int) -> Result<Option<Arc<T>>> {
253        assert!(arg < self.len() as i32);
254        let p = unsafe { ffi::sqlite3_get_auxdata(self.ctx, arg) as *const AuxInner };
255        if p.is_null() {
256            Ok(None)
257        } else {
258            let v: AuxInner = AuxInner::clone(unsafe { &*p });
259            v.downcast::<T>()
260                .map(Some)
261                .map_err(|_| Error::GetAuxWrongType)
262        }
263    }
264
265    /// Get the db connection handle via [sqlite3_context_db_handle](https://www.sqlite.org/c3ref/context_db_handle.html)
266    ///
267    /// # Safety
268    ///
269    /// This function is marked unsafe because there is a potential for other
270    /// references to the connection to be sent across threads, [see this comment](https://github.com/rusqlite/rusqlite/issues/643#issuecomment-640181213).
271    pub unsafe fn get_connection(&self) -> Result<ConnectionRef<'_>> {
272        unsafe {
273            let handle = ffi::sqlite3_context_db_handle(self.ctx);
274            Ok(ConnectionRef {
275                conn: Connection::from_handle(handle)?,
276                phantom: PhantomData,
277            })
278        }
279    }
280}
281
282/// A reference to a connection handle with a lifetime bound to something.
283pub struct ConnectionRef<'ctx> {
284    // comes from Connection::from_handle(sqlite3_context_db_handle(...))
285    // and is non-owning
286    conn: Connection,
287    phantom: PhantomData<&'ctx Context<'ctx>>,
288}
289
290impl Deref for ConnectionRef<'_> {
291    type Target = Connection;
292
293    #[inline]
294    fn deref(&self) -> &Connection {
295        &self.conn
296    }
297}
298
299type AuxInner = Arc<dyn Any + Send + Sync + 'static>;
300
301/// Subtype of an SQL function
302pub type SubType = Option<c_uint>;
303
304/// Result of an SQL function
305pub trait SqlFnOutput {
306    /// Converts Rust value to SQLite value with an optional subtype
307    fn to_sql(&self) -> Result<(ToSqlOutput<'_>, SubType)>;
308}
309
310impl<T: ToSql> SqlFnOutput for T {
311    #[inline]
312    fn to_sql(&self) -> Result<(ToSqlOutput<'_>, SubType)> {
313        ToSql::to_sql(self).map(|o| (o, None))
314    }
315}
316
317impl<T: ToSql> SqlFnOutput for (T, SubType) {
318    fn to_sql(&self) -> Result<(ToSqlOutput<'_>, SubType)> {
319        ToSql::to_sql(&self.0).map(|o| (o, self.1))
320    }
321}
322
323/// n-th arg of an SQL scalar function
324pub struct SqlFnArg {
325    idx: usize,
326}
327impl ToSql for SqlFnArg {
328    fn to_sql(&self) -> Result<ToSqlOutput<'_>> {
329        Ok(ToSqlOutput::Arg(self.idx))
330    }
331}
332
333unsafe fn sql_result<T: SqlFnOutput>(
334    ctx: *mut sqlite3_context,
335    args: &[*mut sqlite3_value],
336    r: Result<T>,
337) {
338    unsafe {
339        if let Err(err) = _sql_result(ctx, args, r) {
340            report_error(ctx, &err);
341        }
342    }
343}
344
345unsafe fn _sql_result<T: SqlFnOutput>(
346    ctx: *mut sqlite3_context,
347    args: &[*mut sqlite3_value],
348    r: Result<T>,
349) -> Result<()> {
350    let r = r?;
351    let (value, sub_type) = r.to_sql()?;
352    unsafe {
353        set_result(ctx, args, value)?;
354        if let Some(sub_type) = sub_type {
355            ffi::sqlite3_result_subtype(ctx, sub_type);
356        }
357    }
358    Ok(())
359}
360
361/// Aggregate is the callback interface for user-defined
362/// aggregate function.
363///
364/// `A` is the type of the aggregation context and `T` is the type of the final
365/// result. Implementations should be stateless.
366pub trait Aggregate<A, T>
367where
368    A: RefUnwindSafe + UnwindSafe,
369    T: SqlFnOutput,
370{
371    /// Initializes the aggregation context. Will be called prior to the first
372    /// call to [`step()`](Aggregate::step) to set up the context for an
373    /// invocation of the function. (Note: `init()` will not be called if
374    /// there are no rows.)
375    fn init(&self, ctx: &mut Context<'_>) -> Result<A>;
376
377    /// "step" function called once for each row in an aggregate group. May be
378    /// called 0 times if there are no rows.
379    fn step(&self, ctx: &mut Context<'_>, acc: &mut A) -> Result<()>;
380
381    /// Computes and returns the final result. Will be called exactly once for
382    /// each invocation of the function. If [`step()`](Aggregate::step) was
383    /// called at least once, will be given `Some(A)` (the same `A` as was
384    /// created by [`init`](Aggregate::init) and given to
385    /// [`step`](Aggregate::step)); if [`step()`](Aggregate::step) was not
386    /// called (because the function is running against 0 rows), will be
387    /// given `None`.
388    ///
389    /// The passed context will have no arguments.
390    fn finalize(&self, ctx: &mut Context<'_>, acc: Option<A>) -> Result<T>;
391}
392
393/// `WindowAggregate` is the callback interface for
394/// user-defined aggregate window function.
395#[cfg(feature = "window")]
396pub trait WindowAggregate<A, T>: Aggregate<A, T>
397where
398    A: RefUnwindSafe + UnwindSafe,
399    T: SqlFnOutput,
400{
401    /// Returns the current value of the aggregate. Unlike xFinal, the
402    /// implementation should not delete any context.
403    fn value(&self, acc: Option<&mut A>) -> Result<T>;
404
405    /// Removes a row from the current window.
406    fn inverse(&self, ctx: &mut Context<'_>, acc: &mut A) -> Result<()>;
407}
408
409bitflags::bitflags! {
410    /// Function Flags.
411    /// See [sqlite3_create_function](https://sqlite.org/c3ref/create_function.html)
412    /// and [Function Flags](https://sqlite.org/c3ref/c_deterministic.html) for details.
413    #[derive(Clone, Copy, Debug)]
414    #[repr(C)]
415    pub struct FunctionFlags: c_int {
416        /// Specifies UTF-8 as the text encoding this SQL function prefers for its parameters.
417        const SQLITE_UTF8     = ffi::SQLITE_UTF8;
418        /// Specifies UTF-16 using little-endian byte order as the text encoding this SQL function prefers for its parameters.
419        const SQLITE_UTF16LE  = ffi::SQLITE_UTF16LE;
420        /// Specifies UTF-16 using big-endian byte order as the text encoding this SQL function prefers for its parameters.
421        const SQLITE_UTF16BE  = ffi::SQLITE_UTF16BE;
422        /// Specifies UTF-16 using native byte order as the text encoding this SQL function prefers for its parameters.
423        const SQLITE_UTF16    = ffi::SQLITE_UTF16;
424        /// Means that the function always gives the same output when the input parameters are the same.
425        const SQLITE_DETERMINISTIC = ffi::SQLITE_DETERMINISTIC; // 3.8.3
426        /// Means that the function may only be invoked from top-level SQL.
427        const SQLITE_DIRECTONLY    = 0x0000_0008_0000; // 3.30.0
428        /// Indicates to SQLite that a function may call `sqlite3_value_subtype()` to inspect the subtypes of its arguments.
429        const SQLITE_SUBTYPE       = 0x0000_0010_0000; // 3.30.0
430        /// Means that the function is unlikely to cause problems even if misused.
431        const SQLITE_INNOCUOUS     = 0x0000_0020_0000; // 3.31.0
432        /// Indicates to SQLite that a function might call `sqlite3_result_subtype()` to cause a subtype to be associated with its result.
433        const SQLITE_RESULT_SUBTYPE     = 0x0000_0100_0000; // 3.45.0
434        /// Indicates that the function is an aggregate that internally orders the values provided to the first argument.
435        const SQLITE_SELFORDER1 = 0x0000_0200_0000; // 3.47.0
436    }
437}
438
439impl Default for FunctionFlags {
440    #[inline]
441    fn default() -> Self {
442        Self::SQLITE_UTF8
443    }
444}
445
446impl Connection {
447    /// Attach a user-defined scalar function to
448    /// this database connection.
449    ///
450    /// `fn_name` is the name the function will be accessible from SQL.
451    /// `n_arg` is the number of arguments to the function. Use `-1` for a
452    /// variable number. If the function always returns the same value
453    /// given the same input, `deterministic` should be `true`.
454    ///
455    /// The function will remain available until the connection is closed or
456    /// until it is explicitly removed via
457    /// [`remove_function`](Connection::remove_function).
458    ///
459    /// # Example
460    ///
461    /// ```rust
462    /// # use rusqlite::{Connection, Result};
463    /// # use rusqlite::functions::FunctionFlags;
464    /// fn scalar_function_example(db: Connection) -> Result<()> {
465    ///     db.create_scalar_function(
466    ///         "halve",
467    ///         1,
468    ///         FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
469    ///         |ctx| {
470    ///             let value = ctx.get::<f64>(0)?;
471    ///             Ok(value / 2f64)
472    ///         },
473    ///     )?;
474    ///
475    ///     let six_halved: f64 = db.query_row("SELECT halve(6)", [], |r| r.get(0))?;
476    ///     assert_eq!(six_halved, 3f64);
477    ///     Ok(())
478    /// }
479    /// ```
480    ///
481    /// # Failure
482    ///
483    /// Will return Err if the function could not be attached to the connection.
484    #[inline]
485    pub fn create_scalar_function<F, N: Name, T>(
486        &self,
487        fn_name: N,
488        n_arg: c_int,
489        flags: FunctionFlags,
490        x_func: F,
491    ) -> Result<()>
492    where
493        F: Fn(&Context<'_>) -> Result<T> + Send + 'static,
494        T: SqlFnOutput,
495    {
496        self.db
497            .borrow_mut()
498            .create_scalar_function(fn_name, n_arg, flags, x_func)
499    }
500
501    /// Attach a user-defined aggregate function to this
502    /// database connection.
503    ///
504    /// # Failure
505    ///
506    /// Will return Err if the function could not be attached to the connection.
507    #[inline]
508    pub fn create_aggregate_function<A, D, N: Name, T>(
509        &self,
510        fn_name: N,
511        n_arg: c_int,
512        flags: FunctionFlags,
513        aggr: D,
514    ) -> Result<()>
515    where
516        A: RefUnwindSafe + UnwindSafe + Send,
517        D: Aggregate<A, T> + Send + 'static,
518        T: SqlFnOutput,
519    {
520        self.db
521            .borrow_mut()
522            .create_aggregate_function(fn_name, n_arg, flags, aggr)
523    }
524
525    /// Attach a user-defined aggregate window function to
526    /// this database connection.
527    ///
528    /// See `https://sqlite.org/windowfunctions.html#udfwinfunc` for more
529    /// information.
530    #[cfg(feature = "window")]
531    #[inline]
532    pub fn create_window_function<A, N: Name, W, T>(
533        &self,
534        fn_name: N,
535        n_arg: c_int,
536        flags: FunctionFlags,
537        aggr: W,
538    ) -> Result<()>
539    where
540        A: RefUnwindSafe + UnwindSafe + Send,
541        W: WindowAggregate<A, T> + Send + 'static,
542        T: SqlFnOutput,
543    {
544        self.db
545            .borrow_mut()
546            .create_window_function(fn_name, n_arg, flags, aggr)
547    }
548
549    /// Removes a user-defined function from this
550    /// database connection.
551    ///
552    /// `fn_name` and `n_arg` should match the name and number of arguments
553    /// given to [`create_scalar_function`](Connection::create_scalar_function)
554    /// or [`create_aggregate_function`](Connection::create_aggregate_function).
555    ///
556    /// # Failure
557    ///
558    /// Will return Err if the function could not be removed.
559    #[inline]
560    pub fn remove_function<N: Name>(&self, fn_name: N, n_arg: c_int) -> Result<()> {
561        self.db.borrow_mut().remove_function(fn_name, n_arg)
562    }
563}
564
565impl InnerConnection {
566    /// ```compile_fail
567    /// use rusqlite::{functions::FunctionFlags, Connection, Result};
568    /// fn main() -> Result<()> {
569    ///     let db = Connection::open_in_memory()?;
570    ///     {
571    ///         let mut called = std::sync::atomic::AtomicBool::new(false);
572    ///         db.create_scalar_function(
573    ///             "test",
574    ///             0,
575    ///             FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
576    ///             |_| {
577    ///                 called.store(true, std::sync::atomic::Ordering::Relaxed);
578    ///                 Ok(true)
579    ///             },
580    ///         );
581    ///     }
582    ///     let result: Result<bool> = db.query_row("SELECT test()", [], |r| r.get(0));
583    ///     assert!(result?);
584    ///     Ok(())
585    /// }
586    /// ```
587    fn create_scalar_function<F, N: Name, T>(
588        &mut self,
589        fn_name: N,
590        n_arg: c_int,
591        flags: FunctionFlags,
592        x_func: F,
593    ) -> Result<()>
594    where
595        F: Fn(&Context<'_>) -> Result<T> + Send + 'static,
596        T: SqlFnOutput,
597    {
598        unsafe extern "C" fn call_boxed_closure<F, T>(
599            ctx: *mut sqlite3_context,
600            argc: c_int,
601            argv: *mut *mut sqlite3_value,
602        ) where
603            F: Fn(&Context<'_>) -> Result<T>,
604            T: SqlFnOutput,
605        {
606            unsafe {
607                let args = slice::from_raw_parts(argv, argc as usize);
608                let r = catch_unwind(|| {
609                    let boxed_f: *const F = ffi::sqlite3_user_data(ctx).cast::<F>();
610                    assert!(!boxed_f.is_null(), "Internal error - null function pointer");
611                    let ctx = Context { ctx, args };
612                    (*boxed_f)(&ctx)
613                });
614                let Ok(r) = r else {
615                    report_error(ctx, &Error::UnwindingPanic);
616                    return;
617                };
618                sql_result(ctx, args, r);
619            }
620        }
621
622        let boxed_f: *mut F = Box::into_raw(Box::new(x_func));
623        let c_name = fn_name.as_cstr()?;
624        let r = unsafe {
625            ffi::sqlite3_create_function_v2(
626                self.db(),
627                c_name.as_ptr(),
628                n_arg,
629                flags.bits(),
630                boxed_f.cast::<c_void>(),
631                Some(call_boxed_closure::<F, T>),
632                None,
633                None,
634                Some(free_boxed_value::<F>),
635            )
636        };
637        self.decode_result(r)
638    }
639
640    fn create_aggregate_function<A, D, N: Name, T>(
641        &mut self,
642        fn_name: N,
643        n_arg: c_int,
644        flags: FunctionFlags,
645        aggr: D,
646    ) -> Result<()>
647    where
648        A: RefUnwindSafe + UnwindSafe + Send,
649        D: Aggregate<A, T> + Send + 'static,
650        T: SqlFnOutput,
651    {
652        let boxed_aggr: *mut D = Box::into_raw(Box::new(aggr));
653        let c_name = fn_name.as_cstr()?;
654        let r = unsafe {
655            ffi::sqlite3_create_function_v2(
656                self.db(),
657                c_name.as_ptr(),
658                n_arg,
659                flags.bits(),
660                boxed_aggr.cast::<c_void>(),
661                None,
662                Some(call_boxed_step::<A, D, T>),
663                Some(call_boxed_final::<A, D, T>),
664                Some(free_boxed_value::<D>),
665            )
666        };
667        self.decode_result(r)
668    }
669
670    #[cfg(feature = "window")]
671    fn create_window_function<A, N: Name, W, T>(
672        &mut self,
673        fn_name: N,
674        n_arg: c_int,
675        flags: FunctionFlags,
676        aggr: W,
677    ) -> Result<()>
678    where
679        A: RefUnwindSafe + UnwindSafe + Send,
680        W: WindowAggregate<A, T> + Send + 'static,
681        T: SqlFnOutput,
682    {
683        let boxed_aggr: *mut W = Box::into_raw(Box::new(aggr));
684        let c_name = fn_name.as_cstr()?;
685        let r = unsafe {
686            ffi::sqlite3_create_window_function(
687                self.db(),
688                c_name.as_ptr(),
689                n_arg,
690                flags.bits(),
691                boxed_aggr.cast::<c_void>(),
692                Some(call_boxed_step::<A, W, T>),
693                Some(call_boxed_final::<A, W, T>),
694                Some(call_boxed_value::<A, W, T>),
695                Some(call_boxed_inverse::<A, W, T>),
696                Some(free_boxed_value::<W>),
697            )
698        };
699        self.decode_result(r)
700    }
701
702    fn remove_function<N: Name>(&mut self, fn_name: N, n_arg: c_int) -> Result<()> {
703        let c_name = fn_name.as_cstr()?;
704        let r = unsafe {
705            ffi::sqlite3_create_function_v2(
706                self.db(),
707                c_name.as_ptr(),
708                n_arg,
709                ffi::SQLITE_UTF8,
710                ptr::null_mut(),
711                None,
712                None,
713                None,
714                None,
715            )
716        };
717        self.decode_result(r)
718    }
719}
720
721unsafe fn aggregate_context<A>(ctx: *mut sqlite3_context, bytes: usize) -> Option<*mut *mut A> {
722    let pac = unsafe { ffi::sqlite3_aggregate_context(ctx, bytes as c_int).cast::<*mut A>() };
723    if pac.is_null() {
724        return None;
725    }
726    Some(pac)
727}
728
729unsafe extern "C" fn call_boxed_step<A, D, T>(
730    ctx: *mut sqlite3_context,
731    argc: c_int,
732    argv: *mut *mut sqlite3_value,
733) where
734    A: RefUnwindSafe + UnwindSafe,
735    D: Aggregate<A, T>,
736    T: SqlFnOutput,
737{
738    unsafe {
739        let Some(pac) = aggregate_context::<A>(ctx, size_of::<*mut A>()) else {
740            ffi::sqlite3_result_error_nomem(ctx);
741            return;
742        };
743
744        let r = catch_unwind(|| {
745            let boxed_aggr: *mut D = ffi::sqlite3_user_data(ctx).cast::<D>();
746            assert!(
747                !boxed_aggr.is_null(),
748                "Internal error - null aggregate pointer"
749            );
750            let mut ctx = Context {
751                ctx,
752                args: slice::from_raw_parts(argv, argc as usize),
753            };
754
755            if (*pac).is_null() {
756                *pac = Box::into_raw(Box::new((*boxed_aggr).init(&mut ctx)?));
757            }
758
759            (*boxed_aggr).step(&mut ctx, &mut **pac)
760        });
761        let Ok(r) = r else {
762            report_error(ctx, &Error::UnwindingPanic);
763            return;
764        };
765        match r {
766            Ok(()) => {}
767            Err(err) => report_error(ctx, &err),
768        }
769    }
770}
771
772#[cfg(feature = "window")]
773unsafe extern "C" fn call_boxed_inverse<A, W, T>(
774    ctx: *mut sqlite3_context,
775    argc: c_int,
776    argv: *mut *mut sqlite3_value,
777) where
778    A: RefUnwindSafe + UnwindSafe,
779    W: WindowAggregate<A, T>,
780    T: SqlFnOutput,
781{
782    unsafe {
783        let Some(pac) = aggregate_context::<A>(ctx, size_of::<*mut A>()) else {
784            ffi::sqlite3_result_error_nomem(ctx);
785            return;
786        };
787
788        let r = catch_unwind(|| {
789            let boxed_aggr: *mut W = ffi::sqlite3_user_data(ctx).cast::<W>();
790            assert!(
791                !boxed_aggr.is_null(),
792                "Internal error - null aggregate pointer"
793            );
794            let mut ctx = Context {
795                ctx,
796                args: slice::from_raw_parts(argv, argc as usize),
797            };
798            (*boxed_aggr).inverse(&mut ctx, &mut **pac)
799        });
800        let Ok(r) = r else {
801            report_error(ctx, &Error::UnwindingPanic);
802            return;
803        };
804        match r {
805            Ok(()) => {}
806            Err(err) => report_error(ctx, &err),
807        }
808    }
809}
810
811unsafe extern "C" fn call_boxed_final<A, D, T>(ctx: *mut sqlite3_context)
812where
813    A: RefUnwindSafe + UnwindSafe,
814    D: Aggregate<A, T>,
815    T: SqlFnOutput,
816{
817    unsafe {
818        // Within the xFinal callback, it is customary to set N=0 in calls to
819        // sqlite3_aggregate_context(C,N) so that no pointless memory allocations occur.
820        let a: Option<A> = match aggregate_context::<A>(ctx, 0) {
821            Some(pac) => {
822                if (*pac).is_null() {
823                    None
824                } else {
825                    let a = Box::from_raw(*pac);
826                    Some(*a)
827                }
828            }
829            None => None,
830        };
831
832        let r = catch_unwind(|| {
833            let boxed_aggr: *mut D = ffi::sqlite3_user_data(ctx).cast::<D>();
834            assert!(
835                !boxed_aggr.is_null(),
836                "Internal error - null aggregate pointer"
837            );
838            let mut ctx = Context { ctx, args: &mut [] };
839            (*boxed_aggr).finalize(&mut ctx, a)
840        });
841        let Ok(r) = r else {
842            report_error(ctx, &Error::UnwindingPanic);
843            return;
844        };
845        sql_result(ctx, &[], r);
846    }
847}
848
849#[cfg(feature = "window")]
850unsafe extern "C" fn call_boxed_value<A, W, T>(ctx: *mut sqlite3_context)
851where
852    A: RefUnwindSafe + UnwindSafe,
853    W: WindowAggregate<A, T>,
854    T: SqlFnOutput,
855{
856    unsafe {
857        // Within the xValue callback, it is customary to set N=0 in calls to
858        // sqlite3_aggregate_context(C,N) so that no pointless memory allocations occur.
859        let pac = aggregate_context::<A>(ctx, 0).filter(|&pac| !(*pac).is_null());
860
861        let r = catch_unwind(|| {
862            let boxed_aggr: *mut W = ffi::sqlite3_user_data(ctx).cast::<W>();
863            assert!(
864                !boxed_aggr.is_null(),
865                "Internal error - null aggregate pointer"
866            );
867            (*boxed_aggr).value(pac.map(|pac| &mut **pac))
868        });
869        let Ok(r) = r else {
870            report_error(ctx, &Error::UnwindingPanic);
871            return;
872        };
873        sql_result(ctx, &[], r);
874    }
875}
876
877#[cfg(all(test, not(miri)))]
878mod test {
879    #[cfg(all(target_family = "wasm", target_os = "unknown"))]
880    use wasm_bindgen_test::wasm_bindgen_test as test;
881
882    #[cfg(feature = "window")]
883    use crate::functions::WindowAggregate;
884    use crate::functions::{Aggregate, Context, FunctionFlags, SqlFnArg, SubType};
885    use crate::{Connection, Error, Result};
886    use regex::Regex;
887    use std::ffi::c_double;
888
889    fn half(ctx: &Context<'_>) -> Result<c_double> {
890        assert!(!ctx.is_empty());
891        assert_eq!(ctx.len(), 1, "called with unexpected number of arguments");
892        assert!(unsafe {
893            ctx.get_connection()
894                .as_ref()
895                .map(std::ops::Deref::deref)
896                .is_ok()
897        });
898        let value = ctx.get::<c_double>(0)?;
899        Ok(value / 2f64)
900    }
901
902    #[test]
903    fn test_function_half() -> Result<()> {
904        let db = Connection::open_in_memory()?;
905        db.create_scalar_function(
906            c"half",
907            1,
908            FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
909            half,
910        )?;
911        let result: f64 = db.one_column("SELECT half(6)", [])?;
912
913        assert!((3f64 - result).abs() < f64::EPSILON);
914        Ok(())
915    }
916
917    #[test]
918    fn test_remove_function() -> Result<()> {
919        let db = Connection::open_in_memory()?;
920        db.create_scalar_function(
921            c"half",
922            1,
923            FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
924            half,
925        )?;
926        assert!((3f64 - db.one_column::<f64, _>("SELECT half(6)", [])?).abs() < f64::EPSILON);
927
928        db.remove_function(c"half", 1)?;
929        db.one_column::<f64, _>("SELECT half(6)", []).unwrap_err();
930        Ok(())
931    }
932
933    // This implementation of a regexp scalar function uses SQLite's auxiliary data
934    // (https://www.sqlite.org/c3ref/get_auxdata.html) to avoid recompiling the regular
935    // expression multiple times within one query.
936    fn regexp_with_auxiliary(ctx: &Context<'_>) -> Result<bool> {
937        assert_eq!(ctx.len(), 2, "called with unexpected number of arguments");
938        type BoxError = Box<dyn std::error::Error + Send + Sync + 'static>;
939        let regexp: std::sync::Arc<Regex> = ctx
940            .get_or_create_aux(0, |vr| -> Result<_, BoxError> {
941                Ok(Regex::new(vr.as_str()?)?)
942            })?;
943
944        let is_match = {
945            let text = ctx
946                .get_raw(1)
947                .as_str()
948                .map_err(|e| Error::UserFunctionError(e.into()))?;
949
950            regexp.is_match(text)
951        };
952
953        Ok(is_match)
954    }
955
956    #[test]
957    fn test_function_regexp_with_auxiliary() -> Result<()> {
958        let db = Connection::open_in_memory()?;
959        db.execute_batch(
960            "BEGIN;
961             CREATE TABLE foo (x string);
962             INSERT INTO foo VALUES ('lisa');
963             INSERT INTO foo VALUES ('lXsi');
964             INSERT INTO foo VALUES ('lisX');
965             END;",
966        )?;
967        db.create_scalar_function(
968            c"regexp",
969            2,
970            FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
971            regexp_with_auxiliary,
972        )?;
973
974        assert!(db.one_column::<bool, _>("SELECT regexp('l.s[aeiouy]', 'lisa')", [])?);
975
976        assert_eq!(
977            2,
978            db.one_column::<i64, _>(
979                "SELECT COUNT(*) FROM foo WHERE regexp('l.s[aeiouy]', x) == 1",
980                [],
981            )?
982        );
983        Ok(())
984    }
985
986    #[test]
987    fn test_varargs_function() -> Result<()> {
988        let db = Connection::open_in_memory()?;
989        db.create_scalar_function(
990            c"my_concat",
991            -1,
992            FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
993            |ctx| {
994                let mut ret = String::new();
995
996                for idx in 0..ctx.len() {
997                    let s = ctx.get::<String>(idx)?;
998                    ret.push_str(&s);
999                }
1000
1001                Ok(ret)
1002            },
1003        )?;
1004
1005        for &(expected, query) in &[
1006            ("", "SELECT my_concat()"),
1007            ("onetwo", "SELECT my_concat('one', 'two')"),
1008            ("abc", "SELECT my_concat('a', 'b', 'c')"),
1009        ] {
1010            assert_eq!(expected, db.one_column::<String, _>(query, [])?);
1011        }
1012        Ok(())
1013    }
1014
1015    #[test]
1016    fn test_get_aux_type_checking() -> Result<()> {
1017        let db = Connection::open_in_memory()?;
1018        db.create_scalar_function(c"example", 2, FunctionFlags::default(), |ctx| {
1019            if !ctx.get::<bool>(1)? {
1020                ctx.set_aux::<i64>(0, 100)?;
1021            } else {
1022                assert_eq!(ctx.get_aux::<String>(0), Err(Error::GetAuxWrongType));
1023                assert_eq!(*ctx.get_aux::<i64>(0)?.unwrap(), 100);
1024            }
1025            Ok(true)
1026        })?;
1027
1028        let res: bool = db.query_row(
1029            "SELECT example(0, i) FROM (SELECT 0 as i UNION SELECT 1)",
1030            [],
1031            |r| r.get(0),
1032        )?;
1033        // Doesn't actually matter, we'll assert in the function if there's a problem.
1034        assert!(res);
1035        Ok(())
1036    }
1037
1038    struct Sum;
1039    struct Count;
1040
1041    impl Aggregate<i64, Option<i64>> for Sum {
1042        fn init(&self, _: &mut Context<'_>) -> Result<i64> {
1043            Ok(0)
1044        }
1045
1046        fn step(&self, ctx: &mut Context<'_>, sum: &mut i64) -> Result<()> {
1047            *sum += ctx.get::<i64>(0)?;
1048            Ok(())
1049        }
1050
1051        fn finalize(&self, _: &mut Context<'_>, sum: Option<i64>) -> Result<Option<i64>> {
1052            Ok(sum)
1053        }
1054    }
1055
1056    impl Aggregate<i64, i64> for Count {
1057        fn init(&self, _: &mut Context<'_>) -> Result<i64> {
1058            Ok(0)
1059        }
1060
1061        fn step(&self, _ctx: &mut Context<'_>, sum: &mut i64) -> Result<()> {
1062            *sum += 1;
1063            Ok(())
1064        }
1065
1066        fn finalize(&self, _: &mut Context<'_>, sum: Option<i64>) -> Result<i64> {
1067            Ok(sum.unwrap_or(0))
1068        }
1069    }
1070
1071    #[test]
1072    fn test_sum() -> Result<()> {
1073        let db = Connection::open_in_memory()?;
1074        db.create_aggregate_function(
1075            c"my_sum",
1076            1,
1077            FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
1078            Sum,
1079        )?;
1080
1081        // sum should return NULL when given no columns (contrast with count below)
1082        let no_result = "SELECT my_sum(i) FROM (SELECT 2 AS i WHERE 1 <> 1)";
1083        assert!(db.one_column::<Option<i64>, _>(no_result, [])?.is_none());
1084
1085        let single_sum = "SELECT my_sum(i) FROM (SELECT 2 AS i UNION ALL SELECT 2)";
1086        assert_eq!(4, db.one_column::<i64, _>(single_sum, [])?);
1087
1088        let dual_sum = "SELECT my_sum(i), my_sum(j) FROM (SELECT 2 AS i, 1 AS j UNION ALL SELECT \
1089                        2, 1)";
1090        let result: (i64, i64) = db.query_row(dual_sum, [], |r| Ok((r.get(0)?, r.get(1)?)))?;
1091        assert_eq!((4, 2), result);
1092        Ok(())
1093    }
1094
1095    #[test]
1096    fn test_count() -> Result<()> {
1097        let db = Connection::open_in_memory()?;
1098        db.create_aggregate_function(
1099            c"my_count",
1100            -1,
1101            FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
1102            Count,
1103        )?;
1104
1105        // count should return 0 when given no columns (contrast with sum above)
1106        let no_result = "SELECT my_count(i) FROM (SELECT 2 AS i WHERE 1 <> 1)";
1107        assert_eq!(db.one_column::<i64, _>(no_result, [])?, 0);
1108
1109        let single_sum = "SELECT my_count(i) FROM (SELECT 2 AS i UNION ALL SELECT 2)";
1110        assert_eq!(2, db.one_column::<i64, _>(single_sum, [])?);
1111        Ok(())
1112    }
1113
1114    #[cfg(feature = "window")]
1115    impl WindowAggregate<i64, Option<i64>> for Sum {
1116        fn inverse(&self, ctx: &mut Context<'_>, sum: &mut i64) -> Result<()> {
1117            *sum -= ctx.get::<i64>(0)?;
1118            Ok(())
1119        }
1120
1121        fn value(&self, sum: Option<&mut i64>) -> Result<Option<i64>> {
1122            Ok(sum.copied())
1123        }
1124    }
1125
1126    #[test]
1127    #[cfg(feature = "window")]
1128    fn test_window() -> Result<()> {
1129        use fallible_iterator::FallibleIterator as _;
1130
1131        let db = Connection::open_in_memory()?;
1132        db.create_window_function(
1133            c"sumint",
1134            1,
1135            FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
1136            Sum,
1137        )?;
1138        db.execute_batch(
1139            "CREATE TABLE t3(x, y);
1140             INSERT INTO t3 VALUES('a', 4),
1141                     ('b', 5),
1142                     ('c', 3),
1143                     ('d', 8),
1144                     ('e', 1);",
1145        )?;
1146
1147        let mut stmt = db.prepare(
1148            "SELECT x, sumint(y) OVER (
1149                   ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
1150                 ) AS sum_y
1151                 FROM t3 ORDER BY x;",
1152        )?;
1153
1154        let results: Vec<(String, i64)> = stmt
1155            .query([])?
1156            .map(|row| Ok((row.get("x")?, row.get("sum_y")?)))
1157            .collect()?;
1158        let expected = vec![
1159            ("a".to_owned(), 9),
1160            ("b".to_owned(), 12),
1161            ("c".to_owned(), 16),
1162            ("d".to_owned(), 12),
1163            ("e".to_owned(), 9),
1164        ];
1165        assert_eq!(expected, results);
1166        Ok(())
1167    }
1168
1169    #[test]
1170    fn test_sub_type() -> Result<()> {
1171        fn test_getsubtype(ctx: &Context<'_>) -> Result<i32> {
1172            Ok(ctx.get_subtype(0) as i32)
1173        }
1174        fn test_setsubtype(ctx: &Context<'_>) -> Result<(SqlFnArg, SubType)> {
1175            use std::ffi::c_uint;
1176            let value = ctx.get_arg(0);
1177            let sub_type = ctx.get::<c_uint>(1)?;
1178            Ok((value, Some(sub_type)))
1179        }
1180        let db = Connection::open_in_memory()?;
1181        db.create_scalar_function(
1182            c"test_getsubtype",
1183            1,
1184            FunctionFlags::SQLITE_UTF8,
1185            test_getsubtype,
1186        )?;
1187        db.create_scalar_function(
1188            c"test_setsubtype",
1189            2,
1190            FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_RESULT_SUBTYPE,
1191            test_setsubtype,
1192        )?;
1193        let result: i32 = db.one_column("SELECT test_getsubtype('hello');", [])?;
1194        assert_eq!(0, result);
1195
1196        let result: i32 =
1197            db.one_column("SELECT test_getsubtype(test_setsubtype('hello',123));", [])?;
1198        assert_eq!(123, result);
1199
1200        Ok(())
1201    }
1202
1203    #[test]
1204    fn test_blob() -> Result<()> {
1205        fn test_len(ctx: &Context<'_>) -> Result<u32> {
1206            let blob = ctx.get_raw(0);
1207            Ok(blob
1208                .as_bytes_or_null()?
1209                .map_or(0, |b| b.len().try_into().unwrap()))
1210        }
1211        let db = Connection::open_in_memory()?;
1212        db.create_scalar_function("test_len", 1, FunctionFlags::SQLITE_DETERMINISTIC, test_len)?;
1213        assert_eq!(
1214            6,
1215            db.one_column::<u32, _>("SELECT test_len(X'53514C697465');", [])?
1216        );
1217        assert_eq!(0, db.one_column::<u32, _>("SELECT test_len(X'');", [])?);
1218        assert_eq!(0, db.one_column::<u32, _>("SELECT test_len(NULL);", [])?);
1219        Ok(())
1220    }
1221
1222    #[test]
1223    #[cfg(feature = "pointer")]
1224    fn test_rc_pointer() -> Result<()> {
1225        use crate::types::ToSqlOutput;
1226        use std::ops::Deref as _;
1227        use std::rc::Rc;
1228
1229        const PTR_TYPE: &std::ffi::CStr = c"my_rust_ptr";
1230        let rc = Rc::new(1);
1231        {
1232            let ptr = ToSqlOutput::from_rc(rc.clone(), PTR_TYPE);
1233            assert_eq!(2, Rc::strong_count(&rc));
1234            fn myfunc(ctx: &Context<'_>) -> Result<ToSqlOutput<'static>> {
1235                let x = unsafe { ctx.get_pointer(0, PTR_TYPE) };
1236                assert_eq!(x, Some(&1));
1237                Ok(ToSqlOutput::from_rc(Rc::new(*x.unwrap()), PTR_TYPE))
1238            }
1239            let db = Connection::open_in_memory()?;
1240            db.create_scalar_function("myfunc", 1, FunctionFlags::SQLITE_DETERMINISTIC, myfunc)?;
1241            let mut stmt = db.prepare("SELECT myfunc(?)")?;
1242            let result = stmt.query_one([ptr], |r| {
1243                unsafe { r.get_pointer::<_, i32>(0, PTR_TYPE) }.map(|opt| opt.cloned())
1244            })?;
1245            assert_eq!(result.unwrap(), *rc.deref());
1246        }
1247        assert_eq!(1, Rc::strong_count(&rc));
1248        Ok(())
1249    }
1250
1251    #[test]
1252    #[cfg(feature = "pointer")]
1253    fn test_box_pointer() -> Result<()> {
1254        use crate::types::ToSqlOutput;
1255
1256        const PTR_TYPE: &std::ffi::CStr = c"my_rust_ptr";
1257        let value = 1;
1258        let ptr = ToSqlOutput::new_boxed(value, PTR_TYPE);
1259        fn myfunc(ctx: &Context<'_>) -> Result<ToSqlOutput<'static>> {
1260            let x = unsafe { ctx.get_pointer(0, PTR_TYPE) };
1261            assert_eq!(x, Some(&1));
1262            Ok(ToSqlOutput::new_boxed(*x.unwrap(), PTR_TYPE))
1263        }
1264        let db = Connection::open_in_memory()?;
1265        db.create_scalar_function("myfunc", 1, FunctionFlags::SQLITE_DETERMINISTIC, myfunc)?;
1266        let mut stmt = db.prepare("SELECT myfunc(?)")?;
1267        let result = stmt.query_one([ptr], |r| {
1268            unsafe { r.get_pointer::<_, i32>(0, PTR_TYPE) }.map(|opt| opt.cloned())
1269        })?;
1270        assert_eq!(result.unwrap(), value);
1271        Ok(())
1272    }
1273}