1use 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
87pub struct Context<'a> {
90 ctx: *mut sqlite3_context,
91 args: &'a [*mut sqlite3_value],
92}
93
94impl Context<'_> {
95 #[inline]
97 #[must_use]
98 pub fn len(&self) -> usize {
99 self.args.len()
100 }
101
102 #[inline]
104 #[must_use]
105 pub fn is_empty(&self) -> bool {
106 self.args.is_empty()
107 }
108
109 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 #[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 #[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 #[inline]
171 #[must_use]
172 pub fn get_arg(&self, idx: usize) -> SqlFnArg {
173 assert!(idx < self.len());
174 SqlFnArg { idx }
175 }
176
177 #[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 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 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 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 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
282pub struct ConnectionRef<'ctx> {
284 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
301pub type SubType = Option<c_uint>;
303
304pub trait SqlFnOutput {
306 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
323pub 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
361pub trait Aggregate<A, T>
367where
368 A: RefUnwindSafe + UnwindSafe,
369 T: SqlFnOutput,
370{
371 fn init(&self, ctx: &mut Context<'_>) -> Result<A>;
376
377 fn step(&self, ctx: &mut Context<'_>, acc: &mut A) -> Result<()>;
380
381 fn finalize(&self, ctx: &mut Context<'_>, acc: Option<A>) -> Result<T>;
391}
392
393#[cfg(feature = "window")]
396pub trait WindowAggregate<A, T>: Aggregate<A, T>
397where
398 A: RefUnwindSafe + UnwindSafe,
399 T: SqlFnOutput,
400{
401 fn value(&self, acc: Option<&mut A>) -> Result<T>;
404
405 fn inverse(&self, ctx: &mut Context<'_>, acc: &mut A) -> Result<()>;
407}
408
409bitflags::bitflags! {
410 #[derive(Clone, Copy, Debug)]
414 #[repr(C)]
415 pub struct FunctionFlags: c_int {
416 const SQLITE_UTF8 = ffi::SQLITE_UTF8;
418 const SQLITE_UTF16LE = ffi::SQLITE_UTF16LE;
420 const SQLITE_UTF16BE = ffi::SQLITE_UTF16BE;
422 const SQLITE_UTF16 = ffi::SQLITE_UTF16;
424 const SQLITE_DETERMINISTIC = ffi::SQLITE_DETERMINISTIC; const SQLITE_DIRECTONLY = 0x0000_0008_0000; const SQLITE_SUBTYPE = 0x0000_0010_0000; const SQLITE_INNOCUOUS = 0x0000_0020_0000; const SQLITE_RESULT_SUBTYPE = 0x0000_0100_0000; const SQLITE_SELFORDER1 = 0x0000_0200_0000; }
437}
438
439impl Default for FunctionFlags {
440 #[inline]
441 fn default() -> Self {
442 Self::SQLITE_UTF8
443 }
444}
445
446impl Connection {
447 #[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 #[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 #[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 #[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 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 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 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 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 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 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 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}