/******************************************************** * ADO.NET 2.0 Data Provider for SQLite Version 3.X * Written by Robert Simpson (robert@blackcastlesoft.com) * * Released to the public domain, use at your own risk! ********************************************************/ namespace System.Data.SQLite { using System; using System.Collections; using System.Collections.Generic; using System.Runtime.InteropServices; using System.Globalization; /// /// The type of user-defined function to declare /// public enum FunctionType { /// /// Scalar functions are designed to be called and return a result immediately. Examples include ABS(), Upper(), Lower(), etc. /// Scalar = 0, /// /// Aggregate functions are designed to accumulate data until the end of a call and then return a result gleaned from the accumulated data. /// Examples include SUM(), COUNT(), AVG(), etc. /// Aggregate = 1, /// /// 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 /// sorted using a straight case-insensitive comparison function. Custom collating sequences can be used to alter the behavior of text sorting /// in a user-defined manner. /// Collation = 2, } /// /// An internal callback delegate declaration. /// /// Raw context pointer for the user function /// Count of arguments to the function /// A pointer to the array of argument pointers internal delegate void SQLiteCallback(int context, int nArgs, IntPtr argsptr); /// /// Internal callback delegate for implementing collation sequences /// /// Length of the string pv1 /// Pointer to the first string to compare /// Length of the string pv2 /// Pointer to the second string to compare /// Returns -1 if the first string is less than the second. 0 if they are equal, or 1 if the first string is greater /// than the second. internal delegate int SQLiteCollation(int len1, IntPtr pv1, int len2, IntPtr pv2); /// /// This abstract class is designed to handle user-defined functions easily. An instance of the derived class is made for each /// connection to the database. /// /// /// Although there is one instance of a class derived from SQLiteFunction per database connection, the derived class has no access /// to the underlying connection. This is necessary to deter implementers from thinking it would be a good idea to make database /// calls during processing. /// /// It is important to distinguish between a per-connection instance, and a per-SQL statement context. One instance of this class /// services all SQL statements being stepped through on that connection, and there can be many. One should never store per-statement /// information in member variables of user-defined function classes. /// /// For aggregate functions, always create and store your per-statement data in the contextData object on the 1st step. This data will /// be automatically freed for you (and Dispose() called if the item supports IDisposable) when the statement completes. /// public abstract class SQLiteFunction : IDisposable { /// /// The base connection this function is attached to /// private SQLiteBase _base; /// /// Used internally to keep track of memory allocated for aggregate functions /// private int _interopCookie; /// /// Internal array used to keep track of aggregate function context data /// private SortedList _contextDataList; /// /// Holds a reference to the callback function for user functions /// private SQLiteCallback _InvokeFunc; /// /// Holds a reference to the callbakc function for stepping in an aggregate function /// private SQLiteCallback _StepFunc; /// /// Holds a reference to the callback function for finalizing an aggregate function /// private SQLiteCallback _FinalFunc; /// /// Holds a reference to the callback function for collation sequences /// private SQLiteCollation _CompareFunc; /// /// This static list contains all the user-defined functions declared using the proper attributes. /// private static List _registeredFunctions = new List(); /// /// Internal constructor, initializes the function's internal variables. /// protected SQLiteFunction() { _contextDataList = new SortedList(); } /// /// Returns a reference to the underlying connection's SQLiteConvert class, which can be used to convert /// strings and DateTime's into the current connection's encoding schema. /// public SQLiteConvert SQLiteConvert { get { return _base; } } /// /// Scalar functions override this method to do their magic. /// /// /// Parameters passed to functions have only an affinity for a certain data type, there is no underlying schema available /// to force them into a certain type. Therefore the only types you will ever see as parameters are /// DBNull.Value, Int64, Double, String or byte[] array. /// /// The arguments for the command to process /// You may return most simple types as a return value, null or DBNull.Value to return null, DateTime, or /// you may return an Exception-derived class if you wish to return an error to SQLite. Do not actually throw the error, /// just return it! public virtual object Invoke(object[] args) { return null; } /// /// Aggregate functions override this method to do their magic. /// /// /// Typically you'll be updating whatever you've placed in the contextData field and returning as quickly as possible. /// /// The arguments for the command to process /// The 1-based step number. This is incrememted each time the step method is called. /// A placeholder for implementers to store contextual data pertaining to the current context. public virtual void Step(object[] args, int stepNumber, ref object contextData) { } /// /// Aggregate functions override this method to finish their aggregate processing. /// /// /// If you implemented your aggregate function properly, /// you've been recording and keeping track of your data in the contextData object provided, and now at this stage you should have /// all the information you need in there to figure out what to return. /// /// Your own assigned contextData, provided for you so you can return your final results. /// You may return most simple types as a return value, null or DBNull.Value to return null, DateTime, or /// you may return an Exception-derived class if you wish to return an error to SQLite. Do not actually throw the error, /// just return it! /// public virtual object Final(object contextData) { return null; } /// /// User-defined collation sequences override this method to provide a custom string sorting algorithm. /// /// The first string to compare /// The second strnig to compare /// 1 if param1 is greater than param2, 0 if they are equal, or -1 if param1 is less than param2 public virtual int Compare(string param1, string param2) { return 0; } /// /// Converts an IntPtr array of context arguments to an object array containing the resolved parameters the pointers point to. /// /// /// Parameters passed to functions have only an affinity for a certain data type, there is no underlying schema available /// to force them into a certain type. Therefore the only types you will ever see as parameters are /// DBNull.Value, Int64, Double, String or byte[] array. /// /// The number of arguments /// A pointer to the array of arguments /// An object array of the arguments once they've been converted to .NET values internal object[] ConvertParams(int nArgs, IntPtr argsptr) { object[] parms = new object[nArgs]; int[] argint = new int[nArgs]; //string s; //DateTime dt; Marshal.Copy(argsptr, argint, 0, nArgs); for (int n = 0; n < nArgs; n++) { switch (_base.GetParamValueType(argint[n])) { case TypeAffinity.Null: parms[n] = DBNull.Value; break; case TypeAffinity.Int64: parms[n] = _base.GetParamValueInt64(argint[n]); break; case TypeAffinity.Double: parms[n] = _base.GetParamValueDouble(argint[n]); break; case TypeAffinity.Text: parms[n] = _base.GetParamValueText(argint[n]); //s = _base.GetParamValueText(argint[n]); //if (_base.TryToDateTime(s, out dt) == true) parms[n] = dt; //else parms[n] = s; break; case TypeAffinity.Blob: { int x; byte[] blob; x = (int)_base.GetParamValueBytes(argint[n], 0, null, 0, 0); blob = new byte[x]; _base.GetParamValueBytes(argint[n], 0, blob, 0, x); parms[n] = blob; } break; case TypeAffinity.DateTime: // Never happens here but what the heck, maybe it will one day. parms[n] = _base.ToDateTime(_base.GetParamValueText(argint[n])); break; } } return parms; } /// /// Takes the return value from Invoke() and Final() and figures out how to return it to SQLite's context. /// /// The context the return value applies to /// The parameter to return to SQLite void SetReturnValue(int context, object returnValue) { if (returnValue == null || returnValue == DBNull.Value) { _base.ReturnNull(context); return; } Type t = returnValue.GetType(); if (t == typeof(DateTime)) { _base.ReturnText(context, _base.ToString((DateTime)returnValue)); return; } else { Exception r = returnValue as Exception; if (r != null) { _base.ReturnError(context, r.Message); return; } } switch (SQLiteConvert.TypeToAffinity(t)) { case TypeAffinity.Null: _base.ReturnNull(context); return; case TypeAffinity.Int64: _base.ReturnInt64(context, Convert.ToInt64(returnValue, CultureInfo.CurrentCulture)); return; case TypeAffinity.Double: _base.ReturnDouble(context, Convert.ToDouble(returnValue, CultureInfo.CurrentCulture)); return; case TypeAffinity.Text: _base.ReturnText(context, returnValue.ToString()); return; case TypeAffinity.Blob: _base.ReturnBlob(context, (byte[])returnValue); return; } } /// /// Internal scalar callback function, which wraps the raw context pointer and calls the virtual Invoke() method. /// /// A raw context pointer /// Number of arguments passed in /// A pointer to the array of arguments internal void ScalarCallback(int context, int nArgs, IntPtr argsptr) { SetReturnValue(context, Invoke(ConvertParams(nArgs, argsptr))); } /// /// Internal collation sequence function, which wraps up the raw string pointers and executes the Compare() virtual function. /// /// Length of the string pv1 /// Pointer to the first string to compare /// Length of the string pv2 /// Pointer to the second string to compare /// Returns -1 if the first string is less than the second. 0 if they are equal, or 1 if the first string is greater /// than the second. internal int CompareCallback(int len1, IntPtr ptr1, int len2, IntPtr ptr2) { return Compare(_base.ToString(ptr1, len1), _base.ToString(ptr2, len2)); } /// /// The internal aggregate Step function callback, which wraps the raw context pointer and calls the virtual Step() method. /// /// /// This function takes care of doing the lookups and getting the important information put together to call the Step() function. /// That includes pulling out the user's contextData and updating it after the call is made. We use a sorted list for this so /// binary searches can be done to find the data. /// /// A raw context pointer /// Number of arguments passed in /// A pointer to the array of arguments internal void StepCallback(int context, int nArgs, IntPtr argsptr) { int n = _base.AggregateCount(context); int nAux; object obj = null; nAux = _base.AggregateContext(context); if (n > 1) obj = _contextDataList[nAux]; Step(ConvertParams(nArgs, argsptr), n, ref obj); _contextDataList[nAux] = obj; } /// /// An internal aggregate Final function callback, which wraps the context pointer and calls the virtual Final() method. /// /// A raw context pointer /// Not used, always zero /// Not used, always zero internal void FinalCallback(int context, int nArgs, IntPtr argsptr) { int n = _base.AggregateContext(context); object obj = null; if (_contextDataList.ContainsKey(n)) { obj = _contextDataList[n]; _contextDataList.Remove(n); } SetReturnValue(context, Final(obj)); IDisposable disp = obj as IDisposable; if (disp != null) disp.Dispose(); } /// /// Placeholder for a user-defined disposal routine /// /// True if the object is being disposed explicitly protected virtual void Dispose(bool disposing) { } /// /// Disposes of any active contextData variables that were not automatically cleaned up. Sometimes this can happen if /// someone closes the connection while a DataReader is open. /// public void Dispose() { Dispose(true); IDisposable disp; foreach (KeyValuePair kv in _contextDataList) { disp = kv.Value as IDisposable; if (disp != null) disp.Dispose(); } _contextDataList.Clear(); _InvokeFunc = null; _StepFunc = null; _FinalFunc = null; _CompareFunc = null; _base = null; _contextDataList = null; GC.SuppressFinalize(this); } #if !PLATFORM_COMPACTFRAMEWORK /// /// Using reflection, enumerate all assemblies in the current appdomain looking for classes that /// have a SQLiteFunctionAttribute attribute, and registering them accordingly. /// static SQLiteFunction() { SQLiteFunctionAttribute at; System.Reflection.Assembly[] arAssemblies = System.AppDomain.CurrentDomain.GetAssemblies(); int w = arAssemblies.Length; for (int n = 0; n < w; n++) { Type[] arTypes; try { arTypes = arAssemblies[n].GetTypes(); } catch (Reflection.ReflectionTypeLoadException e) { arTypes = e.Types; } int v = arTypes.Length; for (int x = 0; x < v; x++) { if (arTypes[x] == null) continue; object[] arAtt = arTypes[x].GetCustomAttributes(typeof(SQLiteFunctionAttribute), false); int u = arAtt.Length; for (int y = 0; y < u; y++) { at = arAtt[y] as SQLiteFunctionAttribute; if (at != null) { at._instanceType = arTypes[x]; _registeredFunctions.Add(at); } } } } } #else /// /// Manual method of registering a function. The type must still have the SQLiteFunctionAttributes in order to work /// properly, but this is a workaround for the Compact Framework where enumerating assemblies is not currently supported. /// /// The type of the function to register public static void RegisterFunction(Type typ) { object[] arAtt = typ.GetCustomAttributes(typeof(SQLiteFunctionAttribute), false); int u = arAtt.Length; SQLiteFunctionAttribute at; for (int y = 0; y < u; y++) { at = arAtt[y] as SQLiteFunctionAttribute; if (at != null) { at._instanceType = typ; _registeredFunctions.Add(at); } } } #endif /// /// Called by SQLiteBase derived classes, this function binds all user-defined functions to a connection. /// It is done this way so that all user-defined functions will access the database using the same encoding scheme /// as the connection (UTF-8 or UTF-16). /// /// /// The wrapper functions that interop with SQLite will create a unique cooke value, which internally is a pointer to /// all the wrapped callback functions. The interop function uses it to map CDecl callbacks to StdCall callbacks. /// /// The base object on which the functions are to bind /// Returns an array of functions which the connection object should retain until the connection is closed. internal static SQLiteFunction[] BindFunctions(SQLiteBase sqlbase) { SQLiteFunction f; List lFunctions = new List(); foreach (SQLiteFunctionAttribute pr in _registeredFunctions) { f = (SQLiteFunction)Activator.CreateInstance(pr._instanceType); f._base = sqlbase; f._InvokeFunc = (pr.FuncType == FunctionType.Scalar) ? new SQLiteCallback(f.ScalarCallback) : null; f._StepFunc = (pr.FuncType == FunctionType.Aggregate) ? new SQLiteCallback(f.StepCallback) : null; f._FinalFunc = (pr.FuncType == FunctionType.Aggregate) ? new SQLiteCallback(f.FinalCallback) : null; f._CompareFunc = (pr.FuncType == FunctionType.Collation) ? new SQLiteCollation(f.CompareCallback) : null; if (pr.FuncType != FunctionType.Collation) f._interopCookie = sqlbase.CreateFunction(pr.Name, pr.Arguments, f._InvokeFunc, f._StepFunc, f._FinalFunc); else f._interopCookie = sqlbase.CreateCollation(pr.Name, f._CompareFunc); lFunctions.Add(f); } SQLiteFunction[] arFunctions = new SQLiteFunction[lFunctions.Count]; lFunctions.CopyTo(arFunctions, 0); return arFunctions; } /// /// Issued after the base connection is closed, this function cleans up all user-defined functions and disposes of them. /// /// /// Cleaning up here is done mainly because of the interop wrapper. It allocated memory to hold a reference to all the /// delegates, and now must free that memory. /// Freeing is done after the connection is closed to ensure no callbacks get hit after we've freed the cookie. /// /// The base SQLite connection object /// An array of user-defined functions for this object internal static void UnbindFunctions(SQLiteBase sqlbase, SQLiteFunction[] ar) { if (ar == null) return; int x = ar.Length; for (int n = 0; n < x; n++) { sqlbase.FreeFunction(ar[n]._interopCookie); ar[n].Dispose(); } } } }