1 /********************************************************
\r
2 * ADO.NET 2.0 Data Provider for SQLite Version 3.X
\r
3 * Written by Robert Simpson (robert@blackcastlesoft.com)
\r
5 * Released to the public domain, use at your own risk!
\r
6 ********************************************************/
\r
8 namespace Mono.Data.Sqlite
\r
11 using System.Collections;
\r
12 using System.Collections.Generic;
\r
13 using System.Runtime.InteropServices;
\r
14 using System.Globalization;
\r
17 /// This abstract class is designed to handle user-defined functions easily. An instance of the derived class is made for each
\r
18 /// connection to the database.
\r
21 /// Although there is one instance of a class derived from SqliteFunction per database connection, the derived class has no access
\r
22 /// to the underlying connection. This is necessary to deter implementers from thinking it would be a good idea to make database
\r
23 /// calls during processing.
\r
25 /// It is important to distinguish between a per-connection instance, and a per-SQL statement context. One instance of this class
\r
26 /// services all SQL statements being stepped through on that connection, and there can be many. One should never store per-statement
\r
27 /// information in member variables of user-defined function classes.
\r
29 /// For aggregate functions, always create and store your per-statement data in the contextData object on the 1st step. This data will
\r
30 /// be automatically freed for you (and Dispose() called if the item supports IDisposable) when the statement completes.
\r
32 public abstract class SqliteFunction : IDisposable
\r
34 private class AggregateData
\r
36 internal int _count = 1;
\r
37 internal object _data = null;
\r
41 /// The base connection this function is attached to
\r
43 internal SQLiteBase _base;
\r
46 /// Internal array used to keep track of aggregate function context data
\r
48 private Dictionary<long, AggregateData> _contextDataList;
\r
51 /// Holds a reference to the callback function for user functions
\r
53 private SQLiteCallback _InvokeFunc;
\r
55 /// Holds a reference to the callbakc function for stepping in an aggregate function
\r
57 private SQLiteCallback _StepFunc;
\r
59 /// Holds a reference to the callback function for finalizing an aggregate function
\r
61 private SQLiteFinalCallback _FinalFunc;
\r
63 /// Holds a reference to the callback function for collation sequences
\r
65 private SQLiteCollation _CompareFunc;
\r
67 private SQLiteCollation _CompareFunc16;
\r
70 /// Current context of the current callback. Only valid during a callback
\r
72 internal IntPtr _context;
\r
75 /// This static list contains all the user-defined functions declared using the proper attributes.
\r
77 private static List<SqliteFunctionAttribute> _registeredFunctions = new List<SqliteFunctionAttribute>();
\r
80 /// Internal constructor, initializes the function's internal variables.
\r
82 protected SqliteFunction()
\r
84 _contextDataList = new Dictionary<long, AggregateData>();
\r
88 /// Returns a reference to the underlying connection's SqliteConvert class, which can be used to convert
\r
89 /// strings and DateTime's into the current connection's encoding schema.
\r
91 public SqliteConvert SqliteConvert
\r
100 /// Scalar functions override this method to do their magic.
\r
103 /// Parameters passed to functions have only an affinity for a certain data type, there is no underlying schema available
\r
104 /// to force them into a certain type. Therefore the only types you will ever see as parameters are
\r
105 /// DBNull.Value, Int64, Double, String or byte[] array.
\r
107 /// <param name="args">The arguments for the command to process</param>
\r
108 /// <returns>You may return most simple types as a return value, null or DBNull.Value to return null, DateTime, or
\r
109 /// you may return an Exception-derived class if you wish to return an error to SQLite. Do not actually throw the error,
\r
110 /// just return it!</returns>
\r
111 public virtual object Invoke(object[] args)
\r
117 /// Aggregate functions override this method to do their magic.
\r
120 /// Typically you'll be updating whatever you've placed in the contextData field and returning as quickly as possible.
\r
122 /// <param name="args">The arguments for the command to process</param>
\r
123 /// <param name="stepNumber">The 1-based step number. This is incrememted each time the step method is called.</param>
\r
124 /// <param name="contextData">A placeholder for implementers to store contextual data pertaining to the current context.</param>
\r
125 public virtual void Step(object[] args, int stepNumber, ref object contextData)
\r
130 /// Aggregate functions override this method to finish their aggregate processing.
\r
133 /// If you implemented your aggregate function properly,
\r
134 /// you've been recording and keeping track of your data in the contextData object provided, and now at this stage you should have
\r
135 /// all the information you need in there to figure out what to return.
\r
136 /// NOTE: It is possible to arrive here without receiving a previous call to Step(), in which case the contextData will
\r
137 /// be null. This can happen when no rows were returned. You can either return null, or 0 or some other custom return value
\r
138 /// if that is the case.
\r
140 /// <param name="contextData">Your own assigned contextData, provided for you so you can return your final results.</param>
\r
141 /// <returns>You may return most simple types as a return value, null or DBNull.Value to return null, DateTime, or
\r
142 /// you may return an Exception-derived class if you wish to return an error to SQLite. Do not actually throw the error,
\r
143 /// just return it!
\r
145 public virtual object Final(object contextData)
\r
151 /// User-defined collation sequences override this method to provide a custom string sorting algorithm.
\r
153 /// <param name="param1">The first string to compare</param>
\r
154 /// <param name="param2">The second strnig to compare</param>
\r
155 /// <returns>1 if param1 is greater than param2, 0 if they are equal, or -1 if param1 is less than param2</returns>
\r
156 public virtual int Compare(string param1, string param2)
\r
162 /// Converts an IntPtr array of context arguments to an object array containing the resolved parameters the pointers point to.
\r
165 /// Parameters passed to functions have only an affinity for a certain data type, there is no underlying schema available
\r
166 /// to force them into a certain type. Therefore the only types you will ever see as parameters are
\r
167 /// DBNull.Value, Int64, Double, String or byte[] array.
\r
169 /// <param name="nArgs">The number of arguments</param>
\r
170 /// <param name="argsptr">A pointer to the array of arguments</param>
\r
171 /// <returns>An object array of the arguments once they've been converted to .NET values</returns>
\r
172 internal object[] ConvertParams(int nArgs, IntPtr argsptr)
\r
174 object[] parms = new object[nArgs];
\r
175 #if !PLATFORM_COMPACTFRAMEWORK
\r
176 IntPtr[] argint = new IntPtr[nArgs];
\r
178 int[] argint = new int[nArgs];
\r
180 Marshal.Copy(argsptr, argint, 0, nArgs);
\r
182 for (int n = 0; n < nArgs; n++)
\r
184 switch (_base.GetParamValueType((IntPtr)argint[n]))
\r
186 case TypeAffinity.Null:
\r
187 parms[n] = DBNull.Value;
\r
189 case TypeAffinity.Int64:
\r
190 parms[n] = _base.GetParamValueInt64((IntPtr)argint[n]);
\r
192 case TypeAffinity.Double:
\r
193 parms[n] = _base.GetParamValueDouble((IntPtr)argint[n]);
\r
195 case TypeAffinity.Text:
\r
196 parms[n] = _base.GetParamValueText((IntPtr)argint[n]);
\r
198 case TypeAffinity.Blob:
\r
203 x = (int)_base.GetParamValueBytes((IntPtr)argint[n], 0, null, 0, 0);
\r
204 blob = new byte[x];
\r
205 _base.GetParamValueBytes((IntPtr)argint[n], 0, blob, 0, x);
\r
209 case TypeAffinity.DateTime: // Never happens here but what the heck, maybe it will one day.
\r
210 parms[n] = _base.ToDateTime(_base.GetParamValueText((IntPtr)argint[n]));
\r
218 /// Takes the return value from Invoke() and Final() and figures out how to return it to SQLite's context.
\r
220 /// <param name="context">The context the return value applies to</param>
\r
221 /// <param name="returnValue">The parameter to return to SQLite</param>
\r
222 void SetReturnValue(IntPtr context, object returnValue)
\r
224 if (returnValue == null || returnValue == DBNull.Value)
\r
226 _base.ReturnNull(context);
\r
230 Type t = returnValue.GetType();
\r
231 if (t == typeof(DateTime))
\r
233 _base.ReturnText(context, _base.ToString((DateTime)returnValue));
\r
238 Exception r = returnValue as Exception;
\r
242 _base.ReturnError(context, r.Message);
\r
247 switch (SqliteConvert.TypeToAffinity(t))
\r
249 case TypeAffinity.Null:
\r
250 _base.ReturnNull(context);
\r
252 case TypeAffinity.Int64:
\r
253 _base.ReturnInt64(context, Convert.ToInt64(returnValue, CultureInfo.CurrentCulture));
\r
255 case TypeAffinity.Double:
\r
256 _base.ReturnDouble(context, Convert.ToDouble(returnValue, CultureInfo.CurrentCulture));
\r
258 case TypeAffinity.Text:
\r
259 _base.ReturnText(context, returnValue.ToString());
\r
261 case TypeAffinity.Blob:
\r
262 _base.ReturnBlob(context, (byte[])returnValue);
\r
268 /// Internal scalar callback function, which wraps the raw context pointer and calls the virtual Invoke() method.
\r
270 /// <param name="context">A raw context pointer</param>
\r
271 /// <param name="nArgs">Number of arguments passed in</param>
\r
272 /// <param name="argsptr">A pointer to the array of arguments</param>
\r
273 internal void ScalarCallback(IntPtr context, int nArgs, IntPtr argsptr)
\r
275 _context = context;
\r
276 SetReturnValue(context, Invoke(ConvertParams(nArgs, argsptr)));
\r
280 /// Internal collation sequence function, which wraps up the raw string pointers and executes the Compare() virtual function.
\r
282 /// <param name="ptr">Not used</param>
\r
283 /// <param name="len1">Length of the string pv1</param>
\r
284 /// <param name="ptr1">Pointer to the first string to compare</param>
\r
285 /// <param name="len2">Length of the string pv2</param>
\r
286 /// <param name="ptr2">Pointer to the second string to compare</param>
\r
287 /// <returns>Returns -1 if the first string is less than the second. 0 if they are equal, or 1 if the first string is greater
\r
288 /// than the second.</returns>
\r
289 internal int CompareCallback(IntPtr ptr, int len1, IntPtr ptr1, int len2, IntPtr ptr2)
\r
291 return Compare(SqliteConvert.UTF8ToString(ptr1, len1), SqliteConvert.UTF8ToString(ptr2, len2));
\r
294 internal int CompareCallback16(IntPtr ptr, int len1, IntPtr ptr1, int len2, IntPtr ptr2)
\r
296 return Compare(SQLite3_UTF16.UTF16ToString(ptr1, len1), SQLite3_UTF16.UTF16ToString(ptr2, len2));
\r
300 /// The internal aggregate Step function callback, which wraps the raw context pointer and calls the virtual Step() method.
\r
303 /// This function takes care of doing the lookups and getting the important information put together to call the Step() function.
\r
304 /// That includes pulling out the user's contextData and updating it after the call is made. We use a sorted list for this so
\r
305 /// binary searches can be done to find the data.
\r
307 /// <param name="context">A raw context pointer</param>
\r
308 /// <param name="nArgs">Number of arguments passed in</param>
\r
309 /// <param name="argsptr">A pointer to the array of arguments</param>
\r
310 internal void StepCallback(IntPtr context, int nArgs, IntPtr argsptr)
\r
313 AggregateData data;
\r
315 nAux = (long)_base.AggregateContext(context);
\r
316 if (_contextDataList.TryGetValue(nAux, out data) == false)
\r
318 data = new AggregateData();
\r
319 _contextDataList[nAux] = data;
\r
324 _context = context;
\r
325 Step(ConvertParams(nArgs, argsptr), data._count, ref data._data);
\r
334 /// An internal aggregate Final function callback, which wraps the context pointer and calls the virtual Final() method.
\r
336 /// <param name="context">A raw context pointer</param>
\r
337 internal void FinalCallback(IntPtr context)
\r
339 long n = (long)_base.AggregateContext(context);
\r
342 if (_contextDataList.ContainsKey(n))
\r
344 obj = _contextDataList[n]._data;
\r
345 _contextDataList.Remove(n);
\r
348 _context = context;
\r
349 SetReturnValue(context, Final(obj));
\r
351 IDisposable disp = obj as IDisposable;
\r
352 if (disp != null) disp.Dispose();
\r
356 /// Placeholder for a user-defined disposal routine
\r
358 /// <param name="disposing">True if the object is being disposed explicitly</param>
\r
359 protected virtual void Dispose(bool disposing)
\r
365 foreach (KeyValuePair<long, AggregateData> kv in _contextDataList)
\r
367 disp = kv.Value._data as IDisposable;
\r
371 _contextDataList.Clear();
\r
373 _InvokeFunc = null;
\r
376 _CompareFunc = null;
\r
378 _contextDataList = null;
\r
383 /// Disposes of any active contextData variables that were not automatically cleaned up. Sometimes this can happen if
\r
384 /// someone closes the connection while a DataReader is open.
\r
386 public void Dispose()
\r
392 /// Using reflection, enumerate all assemblies in the current appdomain looking for classes that
\r
393 /// have a SqliteFunctionAttribute attribute, and registering them accordingly.
\r
395 #if !PLATFORM_COMPACTFRAMEWORK
\r
396 [global::System.Security.Permissions.FileIOPermission(global::System.Security.Permissions.SecurityAction.Assert, AllFiles = global::System.Security.Permissions.FileIOPermissionAccess.PathDiscovery)]
\r
398 static SqliteFunction()
\r
402 #if !PLATFORM_COMPACTFRAMEWORK
\r
403 SqliteFunctionAttribute at;
\r
404 System.Reflection.Assembly[] arAssemblies = System.AppDomain.CurrentDomain.GetAssemblies();
\r
405 int w = arAssemblies.Length;
\r
406 System.Reflection.AssemblyName sqlite = System.Reflection.Assembly.GetCallingAssembly().GetName();
\r
408 for (int n = 0; n < w; n++)
\r
411 bool found = false;
\r
412 System.Reflection.AssemblyName[] references;
\r
415 // Inspect only assemblies that reference SQLite
\r
416 references = arAssemblies[n].GetReferencedAssemblies();
\r
417 int t = references.Length;
\r
418 for (int z = 0; z < t; z++)
\r
420 if (references[z].Name == sqlite.Name)
\r
427 if (found == false)
\r
430 arTypes = arAssemblies[n].GetTypes();
\r
432 catch (global::System.Reflection.ReflectionTypeLoadException e)
\r
437 int v = arTypes.Length;
\r
438 for (int x = 0; x < v; x++)
\r
440 if (arTypes[x] == null) continue;
\r
442 object[] arAtt = arTypes[x].GetCustomAttributes(typeof(SqliteFunctionAttribute), false);
\r
443 int u = arAtt.Length;
\r
444 for (int y = 0; y < u; y++)
\r
446 at = arAtt[y] as SqliteFunctionAttribute;
\r
449 at._instanceType = arTypes[x];
\r
450 _registeredFunctions.Add(at);
\r
457 catch // SQLite provider can continue without being able to find built-in functions
\r
462 /// Manual method of registering a function. The type must still have the SqliteFunctionAttributes in order to work
\r
463 /// properly, but this is a workaround for the Compact Framework where enumerating assemblies is not currently supported.
\r
465 /// <param name="typ">The type of the function to register</param>
\r
466 public static void RegisterFunction(Type typ)
\r
468 object[] arAtt = typ.GetCustomAttributes(typeof(SqliteFunctionAttribute), false);
\r
469 int u = arAtt.Length;
\r
470 SqliteFunctionAttribute at;
\r
472 for (int y = 0; y < u; y++)
\r
474 at = arAtt[y] as SqliteFunctionAttribute;
\r
477 at._instanceType = typ;
\r
478 _registeredFunctions.Add(at);
\r
484 /// Called by SQLiteBase derived classes, this function binds all user-defined functions to a connection.
\r
485 /// It is done this way so that all user-defined functions will access the database using the same encoding scheme
\r
486 /// as the connection (UTF-8 or UTF-16).
\r
489 /// The wrapper functions that interop with SQLite will create a unique cookie value, which internally is a pointer to
\r
490 /// all the wrapped callback functions. The interop function uses it to map CDecl callbacks to StdCall callbacks.
\r
492 /// <param name="sqlbase">The base object on which the functions are to bind</param>
\r
493 /// <returns>Returns an array of functions which the connection object should retain until the connection is closed.</returns>
\r
494 internal static SqliteFunction[] BindFunctions(SQLiteBase sqlbase)
\r
497 List<SqliteFunction> lFunctions = new List<SqliteFunction>();
\r
499 foreach (SqliteFunctionAttribute pr in _registeredFunctions)
\r
501 f = (SqliteFunction)Activator.CreateInstance(pr._instanceType);
\r
503 f._InvokeFunc = (pr.FuncType == FunctionType.Scalar) ? new SQLiteCallback(f.ScalarCallback) : null;
\r
504 f._StepFunc = (pr.FuncType == FunctionType.Aggregate) ? new SQLiteCallback(f.StepCallback) : null;
\r
505 f._FinalFunc = (pr.FuncType == FunctionType.Aggregate) ? new SQLiteFinalCallback(f.FinalCallback) : null;
\r
506 f._CompareFunc = (pr.FuncType == FunctionType.Collation) ? new SQLiteCollation(f.CompareCallback) : null;
\r
507 f._CompareFunc16 = (pr.FuncType == FunctionType.Collation) ? new SQLiteCollation(f.CompareCallback16) : null;
\r
509 if (pr.FuncType != FunctionType.Collation)
\r
510 sqlbase.CreateFunction(pr.Name, pr.Arguments, (f is SqliteFunctionEx), f._InvokeFunc, f._StepFunc, f._FinalFunc);
\r
513 GCHandle handle = GCHandle.Alloc (f);
\r
514 sqlbase.CreateCollation(pr.Name, collation_callback, collation_callback16, GCHandle.ToIntPtr (handle));
\r
516 sqlbase.CreateCollation(pr.Name, f._CompareFunc, f._CompareFunc16, IntPtr.Zero);
\r
524 SqliteFunction[] arFunctions = new SqliteFunction[lFunctions.Count];
\r
525 lFunctions.CopyTo(arFunctions, 0);
\r
527 return arFunctions;
\r
531 [MonoTouch.MonoPInvokeCallback (typeof (SQLiteCollation))]
\r
532 internal static int collation_callback (IntPtr puser, int len1, IntPtr pv1, int len2, IntPtr pv2)
\r
534 var handle = GCHandle.FromIntPtr (puser);
\r
535 var func = (SqliteFunction) handle.Target;
\r
536 return func._CompareFunc (IntPtr.Zero, len1, pv1, len2, pv2);
\r
539 [MonoTouch.MonoPInvokeCallback (typeof (SQLiteCollation))]
\r
540 internal static int collation_callback16 (IntPtr puser, int len1, IntPtr pv1, int len2, IntPtr pv2)
\r
542 var handle = GCHandle.FromIntPtr (puser);
\r
543 var func = (SqliteFunction) handle.Target;
\r
544 return func._CompareFunc16 (IntPtr.Zero, len1, pv1, len2, pv2);
\r
550 /// Extends SqliteFunction and allows an inherited class to obtain the collating sequence associated with a function call.
\r
553 /// User-defined functions can call the GetCollationSequence() method in this class and use it to compare strings and char arrays.
\r
555 public class SqliteFunctionEx : SqliteFunction
\r
558 /// Obtains the collating sequence in effect for the given function.
\r
560 /// <returns></returns>
\r
561 protected CollationSequence GetCollationSequence()
\r
563 return _base.GetCollationSequence(this, _context);
\r
568 /// The type of user-defined function to declare
\r
570 public enum FunctionType
\r
573 /// Scalar functions are designed to be called and return a result immediately. Examples include ABS(), Upper(), Lower(), etc.
\r
577 /// Aggregate functions are designed to accumulate data until the end of a call and then return a result gleaned from the accumulated data.
\r
578 /// Examples include SUM(), COUNT(), AVG(), etc.
\r
582 /// Collation sequences are used to sort textual data in a custom manner, and appear in an ORDER BY clause. Typically text in an ORDER BY is
\r
583 /// sorted using a straight case-insensitive comparison function. Custom collating sequences can be used to alter the behavior of text sorting
\r
584 /// in a user-defined manner.
\r
590 /// An internal callback delegate declaration.
\r
592 /// <param name="context">Raw context pointer for the user function</param>
\r
593 /// <param name="nArgs">Count of arguments to the function</param>
\r
594 /// <param name="argsptr">A pointer to the array of argument pointers</param>
\r
595 #if !PLATFORM_COMPACTFRAMEWORK
\r
596 [UnmanagedFunctionPointer(CallingConvention.Cdecl)]
\r
598 internal delegate void SQLiteCallback(IntPtr context, int nArgs, IntPtr argsptr);
\r
600 /// An internal final callback delegate declaration.
\r
602 /// <param name="context">Raw context pointer for the user function</param>
\r
603 #if !PLATFORM_COMPACTFRAMEWORK
\r
604 [UnmanagedFunctionPointer(CallingConvention.Cdecl)]
\r
606 internal delegate void SQLiteFinalCallback(IntPtr context);
\r
608 /// Internal callback delegate for implementing collation sequences
\r
610 /// <param name="puser">Not used</param>
\r
611 /// <param name="len1">Length of the string pv1</param>
\r
612 /// <param name="pv1">Pointer to the first string to compare</param>
\r
613 /// <param name="len2">Length of the string pv2</param>
\r
614 /// <param name="pv2">Pointer to the second string to compare</param>
\r
615 /// <returns>Returns -1 if the first string is less than the second. 0 if they are equal, or 1 if the first string is greater
\r
616 /// than the second.</returns>
\r
617 #if !PLATFORM_COMPACTFRAMEWORK
\r
618 [UnmanagedFunctionPointer(CallingConvention.Cdecl)]
\r
620 internal delegate int SQLiteCollation(IntPtr puser, int len1, IntPtr pv1, int len2, IntPtr pv2);
\r
623 /// The type of collating sequence
\r
625 public enum CollationTypeEnum
\r
628 /// The built-in BINARY collating sequence
\r
632 /// The built-in NOCASE collating sequence
\r
636 /// The built-in REVERSE collating sequence
\r
640 /// A custom user-defined collating sequence
\r
646 /// The encoding type the collation sequence uses
\r
648 public enum CollationEncodingEnum
\r
651 /// The collation sequence is UTF8
\r
655 /// The collation sequence is UTF16 little-endian
\r
659 /// The collation sequence is UTF16 big-endian
\r
665 /// A struct describing the collating sequence a function is executing in
\r
667 public struct CollationSequence
\r
670 /// The name of the collating sequence
\r
672 public string Name;
\r
674 /// The type of collating sequence
\r
676 public CollationTypeEnum Type;
\r
679 /// The text encoding of the collation sequence
\r
681 public CollationEncodingEnum Encoding;
\r
684 /// Context of the function that requested the collating sequence
\r
686 internal SqliteFunction _func;
\r
689 /// Calls the base collating sequence to compare two strings
\r
691 /// <param name="s1">The first string to compare</param>
\r
692 /// <param name="s2">The second string to compare</param>
\r
693 /// <returns>-1 if s1 is less than s2, 0 if s1 is equal to s2, and 1 if s1 is greater than s2</returns>
\r
694 public int Compare(string s1, string s2)
\r
696 return _func._base.ContextCollateCompare(Encoding, _func._context, s1, s2);
\r
700 /// Calls the base collating sequence to compare two character arrays
\r
702 /// <param name="c1">The first array to compare</param>
\r
703 /// <param name="c2">The second array to compare</param>
\r
704 /// <returns>-1 if c1 is less than c2, 0 if c1 is equal to c2, and 1 if c1 is greater than c2</returns>
\r
705 public int Compare(char[] c1, char[] c2)
\r
707 return _func._base.ContextCollateCompare(Encoding, _func._context, c1, c2);
\r