Index: System.Data.SQLite/SQLite3.cs ================================================================== --- System.Data.SQLite/SQLite3.cs +++ System.Data.SQLite/SQLite3.cs @@ -1733,11 +1733,12 @@ } #if INTEROP_VIRTUAL_TABLE /// /// Calls the native SQLite core library in order to declare a virtual table - /// in response to a call into the xCreate or xConnect virtual table methods. + /// in response to a call into the + /// or virtual table methods. /// /// /// The virtual table module that is to be responsible for the virtual table /// being declared. /// @@ -1787,10 +1788,68 @@ SQLiteMemory.Free(pSql); pSql = IntPtr.Zero; } } } + + /// + /// Calls the native SQLite core library in order to declare a virtual table + /// function in response to a call into the + /// or virtual table methods. + /// + /// + /// The virtual table module that is to be responsible for the virtual table + /// function being declared. + /// + /// + /// The number of arguments to the function being declared. + /// + /// + /// The name of the function being declared. + /// + /// + /// Upon success, the contents of this parameter are undefined. Upon failure, + /// it should contain an appropriate error message. + /// + /// + /// A standard SQLite return code. + /// + internal override SQLiteErrorCode DeclareVirtualFunction( + SQLiteModule module, + int argumentCount, + string name, + ref string error + ) + { + if (_sql == null) + { + error = "connection has an invalid handle"; + return SQLiteErrorCode.Error; + } + + IntPtr pName = IntPtr.Zero; + + try + { + pName = SQLiteString.Utf8IntPtrFromString(name); + + SQLiteErrorCode n = UnsafeNativeMethods.sqlite3_overload_function( + _sql, pName, argumentCount); + + if (n != SQLiteErrorCode.Ok) error = GetLastError(); + + return n; + } + finally + { + if (pName != IntPtr.Zero) + { + SQLiteMemory.Free(pName); + pName = IntPtr.Zero; + } + } + } #endif /// /// Enables or disabled extension loading by SQLite. /// Index: System.Data.SQLite/SQLiteBase.cs ================================================================== --- System.Data.SQLite/SQLiteBase.cs +++ System.Data.SQLite/SQLiteBase.cs @@ -235,11 +235,12 @@ /// internal abstract void DisposeModule(SQLiteModule module); /// /// Calls the native SQLite core library in order to declare a virtual table - /// in response to a call into the xCreate or xConnect virtual table methods. + /// in response to a call into the + /// or virtual table methods. /// /// /// The virtual table module that is to be responsible for the virtual table /// being declared. /// @@ -253,10 +254,34 @@ /// /// /// A standard SQLite return code. /// internal abstract SQLiteErrorCode DeclareVirtualTable(SQLiteModule module, string strSql, ref string error); + + /// + /// Calls the native SQLite core library in order to declare a virtual table + /// function in response to a call into the + /// or virtual table methods. + /// + /// + /// The virtual table module that is to be responsible for the virtual table + /// function being declared. + /// + /// + /// The number of arguments to the function being declared. + /// + /// + /// The name of the function being declared. + /// + /// + /// Upon success, the contents of this parameter are undefined. Upon failure, + /// it should contain an appropriate error message. + /// + /// + /// A standard SQLite return code. + /// + internal abstract SQLiteErrorCode DeclareVirtualFunction(SQLiteModule module, int argumentCount, string name, ref string error); #endif /// /// Enables or disabled extension loading by SQLite. /// Index: System.Data.SQLite/SQLiteFunction.cs ================================================================== --- System.Data.SQLite/SQLiteFunction.cs +++ System.Data.SQLite/SQLiteFunction.cs @@ -88,10 +88,44 @@ /// protected SQLiteFunction() { _contextDataList = new Dictionary(); } + + /// + /// Constructs an instance of this class using the specified data-type + /// conversion parameters. + /// + /// + /// The DateTime format to be used when converting string values to a + /// DateTime and binding DateTime parameters. + /// + /// + /// The to be used when creating DateTime + /// values. + /// + /// + /// The format string to be used when parsing and formatting DateTime + /// values. + /// + /// + /// Non-zero to create a UTF-16 data-type conversion context; otherwise, + /// a UTF-8 data-type conversion context will be created. + /// + protected SQLiteFunction( + SQLiteDateFormats format, + DateTimeKind kind, + string formatString, + bool utf16 + ) + : this() + { + if (utf16) + _base = new SQLite3_UTF16(format, kind, formatString, IntPtr.Zero, null, false); + else + _base = new SQLite3(format, kind, formatString, IntPtr.Zero, null, false); + } /////////////////////////////////////////////////////////////////////////////////////////////// #region IDisposable Members /// Index: System.Data.SQLite/SQLiteModule.cs ================================================================== --- System.Data.SQLite/SQLiteModule.cs +++ System.Data.SQLite/SQLiteModule.cs @@ -5176,10 +5176,20 @@ /// associated with this module. The native pointer to the /// sqlite3_vtab_cursor derived structure is used to key into this /// collection. /// private Dictionary cursors; + + /////////////////////////////////////////////////////////////////////// + + /// + /// This field is used to store the virtual table function instances + /// associated with this module. The case-insensitive function name + /// and the number of arguments (with -1 meaning "any") are used to + /// construct the string that is used to key into this collection. + /// + private Dictionary functions; #endregion /////////////////////////////////////////////////////////////////////// #region Public Constructors @@ -5195,10 +5205,11 @@ throw new ArgumentNullException("name"); this.name = name; this.tables = new Dictionary(); this.cursors = new Dictionary(); + this.functions = new Dictionary(); } #endregion /////////////////////////////////////////////////////////////////////// @@ -5787,10 +5798,43 @@ } #endregion /////////////////////////////////////////////////////////////////////// + #region Function Lookup Methods + /// + /// Deterimines the key that should be used to identify and store the + /// function instance for the virtual table (i.e. to be returned via + /// the method). + /// + /// + /// The number of arguments to the virtual table function. + /// + /// + /// The name of the virtual table function. + /// + /// + /// The object instance associated with + /// this virtual table function. + /// + /// + /// The string that should be used to identify and store the virtual + /// table function instance. This method cannot return null. If null + /// is returned from this method, the behavior is undefined. + /// + protected virtual string GetFunctionKey( + int argumentCount, + string name, + SQLiteFunction function + ) + { + return String.Format("{0}:{1}", argumentCount, name); + } + #endregion + + /////////////////////////////////////////////////////////////////////// + #region Table Declaration Helper Methods /// /// Attempts to declare the schema for the virtual table using the /// specified database connection. /// @@ -5830,10 +5874,39 @@ } return sqliteBase.DeclareVirtualTable(this, sql, ref error); } #endregion + + /////////////////////////////////////////////////////////////////////// + + #region Function Declaration Helper Methods + protected virtual SQLiteErrorCode DeclareFunction( + SQLiteConnection connection, + int argumentCount, + string name, + ref string error + ) + { + if (connection == null) + { + error = "invalid connection"; + return SQLiteErrorCode.Error; + } + + SQLiteBase sqliteBase = connection._sql; + + if (sqliteBase == null) + { + error = "connection has invalid handle"; + return SQLiteErrorCode.Error; + } + + return sqliteBase.DeclareVirtualFunction( + this, argumentCount, name, ref error); + } + #endregion /////////////////////////////////////////////////////////////////////// #region Error Handling Helper Methods /// @@ -7038,20 +7111,23 @@ { SQLiteVirtualTable table = TableFromIntPtr(pVtab); if (table != null) { + string name = SQLiteString.StringFromUtf8IntPtr(zName); SQLiteFunction function = null; if (FindFunction( - table, nArg, - SQLiteString.StringFromUtf8IntPtr(zName), - ref function, ref pClientData)) + table, nArg, name, ref function, ref pClientData)) { if (function != null) { + string key = GetFunctionKey(nArg, name, function); + + functions[key] = function; callback = function.ScalarCallback; + return 1; } else { SetTableError(pVtab, "no function was created"); @@ -7792,16 +7868,19 @@ /// protected virtual void Dispose(bool disposing) { if (!disposed) { - //if (disposing) - //{ - // //////////////////////////////////// - // // dispose managed resources here... - // //////////////////////////////////// - //} + if (disposing) + { + //////////////////////////////////// + // dispose managed resources here... + //////////////////////////////////// + + if (functions != null) + functions.Clear(); + } ////////////////////////////////////// // release unmanaged resources here... ////////////////////////////////////// Index: System.Data.SQLite/UnsafeNativeMethods.cs ================================================================== --- System.Data.SQLite/UnsafeNativeMethods.cs +++ System.Data.SQLite/UnsafeNativeMethods.cs @@ -988,10 +988,17 @@ [DllImport(SQLITE_DLL)] #endif internal static extern SQLiteErrorCode sqlite3_load_extension( IntPtr db, byte[] fileName, byte[] procName, ref IntPtr pError); +#if !PLATFORM_COMPACTFRAMEWORK + [DllImport(SQLITE_DLL, CallingConvention = CallingConvention.Cdecl)] +#else + [DllImport(SQLITE_DLL)] +#endif + internal static extern SQLiteErrorCode sqlite3_overload_function(IntPtr db, IntPtr zName, int nArgs); + #if !PLATFORM_COMPACTFRAMEWORK [DllImport(SQLITE_DLL, CallingConvention = CallingConvention.Cdecl, CharSet = CharSet.Unicode)] #else [DllImport(SQLITE_DLL, CharSet = CharSet.Unicode)] #endif Index: Tests/vtab.eagle ================================================================== --- Tests/vtab.eagle +++ Tests/vtab.eagle @@ -18,11 +18,11 @@ package require System.Data.SQLite.Test runSQLiteTestPrologue ############################################################################### -runTest {test vtab-1.1 {virtual table support} -setup { +runTest {test vtab-1.1 {basic virtual table support} -setup { setupDb [set fileName vtab-1.1.db] } -body { set id [object invoke Interpreter.GetActive NextId] set dataSource [file join [getDatabaseDirectory] $fileName] @@ -318,10 +318,263 @@ {eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite\ defineConstant.System.Data.SQLite.INTEROP_VIRTUAL_TABLE} -match regexp -result \ [string map [list \n \r\n] {^Ok System#CodeDom#Compiler#CompilerResults#\d+\ \{\} 0 \{1 2 3 4 5 Error \{SQL logic error or missing database virtual table "t\d+" is read-only\}\}$}]} + +############################################################################### + +runTest {test vtab-1.4 {virtual table function support} -setup { + setupDb [set fileName vtab-1.4.db] +} -body { + set id [object invoke Interpreter.GetActive NextId] + set dataSource [file join [getDatabaseDirectory] $fileName] + + set sql(1) { \ + CREATE VIRTUAL TABLE t${id} USING mod${id}; \ + } + + set sql(2) { \ + SELECT Base64(x, CAST('one' AS BLOB)) FROM t${id}; \ + } + + set sql(3) { \ + SELECT Base64(x, CAST('one' AS BLOB), 'two') FROM t${id}; \ + } + + set sql(4) { \ + SELECT Base65(x, CAST('one' AS BLOB)) FROM t${id}; \ + } + + unset -nocomplain results errors + + set code [compileCSharpWith [subst { + using System; + using System.Data.SQLite; + using Eagle._Containers.Public; + + namespace _Dynamic${id} + { + public class SQLiteFunction${id} : SQLiteFunction + { + public SQLiteFunction${id}() + : base(SQLiteDateFormats.Default, DateTimeKind.Unspecified, + null, false) + { + // do nothing. + } + + /////////////////////////////////////////////////////////////////////// + + public override object Invoke( + object\[\] args + ) + { + if (args == null) + return null; + + if (args.Length != 2) + return new ArgumentException(String.Format( + "need exactly two arguments, got {0}", args.Length)); + + object arg = args\[1\]; + + if (arg == null) + return String.Empty; + + Type type = arg.GetType(); + + if (type == typeof(DBNull)) + return String.Empty; + + if (type != typeof(byte\[\])) + return new ArgumentException(String.Format( + "argument must be byte array, got {0}", type)); + + return Convert.ToBase64String((byte\[\]) arg); + } + } + + ///////////////////////////////////////////////////////////////////////// + + public sealed class SQLiteModuleTest${id} : SQLiteModuleNoop + { + public SQLiteModuleTest${id}(string name) + : base(name) + { + // do nothing. + } + + /////////////////////////////////////////////////////////////////////// + + public override SQLiteErrorCode Create( + SQLiteConnection connection, + IntPtr pClientData, + string\[\] arguments, + ref SQLiteVirtualTable table, + ref string error + ) + { + SQLiteErrorCode rc = DeclareTable( + connection, "CREATE TABLE ignored(x);", ref error); + + if (rc != SQLiteErrorCode.Ok) + return rc; + + rc = DeclareFunction(connection, -1, "Base64", ref error); + + if (rc != SQLiteErrorCode.Ok) + return rc; + + table = new SQLiteVirtualTable(arguments); + return SQLiteErrorCode.Ok; + } + + /////////////////////////////////////////////////////////////////////// + + public override SQLiteErrorCode Open( + SQLiteVirtualTable table, + ref SQLiteVirtualTableCursor cursor + ) + { + cursor = new SQLiteVirtualTableCursor(table); + return SQLiteErrorCode.Ok; + } + + /////////////////////////////////////////////////////////////////////// + + public override bool FindFunction( + SQLiteVirtualTable table, + int argumentCount, + string name, + ref SQLiteFunction function, + ref IntPtr pClientData + ) + { + if (argumentCount != 2) + { + SetTableError(table, String.Format( + "no \\"{0}\\" functions accept {1} argument(s)", + base.Name, argumentCount)); + + return false; + } + + if (!String.Equals(name, "Base64", + StringComparison.OrdinalIgnoreCase)) + { + SetTableError(table, String.Format( + "no \\"{0}\\" functions are named \\"{1}\\"", + base.Name, name)); + + return false; + } + + function = new SQLiteFunction${id}(); + return true; + } + } + + ///////////////////////////////////////////////////////////////////////// + + public static class Test${id} + { + public static StringList GetList() + { + StringList result = new StringList(); + + using (SQLiteConnection connection = new SQLiteConnection( + "Data Source=${dataSource};")) + { + connection.Open(); + connection.CreateModule(new SQLiteModuleTest${id}("mod${id}")); + + try + { + using (SQLiteCommand command = connection.CreateCommand()) + { + command.CommandText = "[subst ${sql(1)}]"; + result.Add(String.Format("{0}", command.ExecuteScalar())); + } + } + catch (Exception e) + { + result.Add(e.Message); + } + + try + { + using (SQLiteCommand command = connection.CreateCommand()) + { + command.CommandText = "[subst ${sql(2)}]"; + result.Add(String.Format("{0}", command.ExecuteScalar())); + } + } + catch (Exception e) + { + result.Add(e.Message); + } + + try + { + using (SQLiteCommand command = connection.CreateCommand()) + { + command.CommandText = "[subst ${sql(3)}]"; + result.Add(String.Format("{0}", command.ExecuteScalar())); + } + } + catch (Exception e) + { + result.Add(e.Message); + } + + try + { + using (SQLiteCommand command = connection.CreateCommand()) + { + command.CommandText = "[subst ${sql(4)}]"; + result.Add(String.Format("{0}", command.ExecuteScalar())); + } + } + catch (Exception e) + { + result.Add(e.Message); + } + + connection.Close(); + } + + return result; + } + + /////////////////////////////////////////////////////////////////////// + + public static void Main() + { + // do nothing. + } + } + } + }] true true true results errors [list System.Data.SQLite.dll Eagle.dll]] + + list $code $results \ + [expr {[info exists errors] ? $errors : ""}] \ + [expr {$code eq "Ok" ? [catch { + object invoke _Dynamic${id}.Test${id} GetList + } result] : [set result ""]}] $result +} -cleanup { + cleanupDb $fileName + + unset -nocomplain result code results errors sql dataSource id db fileName +} -constraints \ +{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite\ +defineConstant.System.Data.SQLite.INTEROP_VIRTUAL_TABLE} -match regexp -result \ +[string map [list \n \r\n] {^Ok System#CodeDom#Compiler#CompilerResults#\d+\ +\{\} 0 \{\{\} b25l \{SQL logic error or missing database +unable to use function Base64 in the requested context\} \{SQL logic error or\ +missing database +no such function: Base65\}\}$}]} ############################################################################### runSQLiteTestEpilogue runTestEpilogue