PInvoke-Signaturen für die SQLite Backup-API | .NET-Komponenten und C#-Snippets

Beschreibung:
Ich stand gerade vor dem Problem eine in-memory SQLite Datenbank persistieren zu müssen und bin dabei über die

SQLite Backup API gestolpert die genau das ermöglicht.

Im

offiziellen ADO.Net Provider scheint diese Funktionalität bereits implementiert zu sein. Da ich allerdings mit Xamarin für Android arbeite, muss ich

Mono.Data.Sqlite verwenden wo diese Funktionalität noch nicht enthalten ist.
Deshalb hierfür ein Snippet welches ich euch nicht vorenthalten möchte:

C#-Code:

using Mono.Data.Sqlite;

using System;

using System.IO;

using System.Reflection;

using System.Runtime.InteropServices;

using System.Text;

C#-Code:

    //

    // Alles aus Mono.Data.Sqlite übernommen + unten die Ergänzung um die Backup-API

    //

    [SuppressUnmanagedCodeSecurity]

    public static class UnsafeNativeMethods

    {

        public enum TypeAffinity

        {

            Uninitialized,

            Int64,

            Double,

            Text,

            Blob,

            Null,

            DateTime = 10,

            None

        }



        public enum SQLiteConfig

        {

            SingleThread = 1,

            MultiThread,

            Serialized

        }



        [UnmanagedFunctionPointer(System.Runtime.InteropServices.CallingConvention.Cdecl)]

        internal delegate void SQLiteCallback(IntPtr context, int nArgs, IntPtr argsptr);



        [UnmanagedFunctionPointer(System.Runtime.InteropServices.CallingConvention.Cdecl)]

        internal delegate void SQLiteFinalCallback(IntPtr context);



        [UnmanagedFunctionPointer(System.Runtime.InteropServices.CallingConvention.Cdecl)]

        internal delegate void SQLiteUpdateCallback(IntPtr puser, int type, IntPtr database, IntPtr table, long rowid);



        [UnmanagedFunctionPointer(System.Runtime.InteropServices.CallingConvention.Cdecl)]

        internal delegate int SQLiteCommitCallback(IntPtr puser);



        [UnmanagedFunctionPointer(System.Runtime.InteropServices.CallingConvention.Cdecl)]

        internal delegate void SQLiteRollbackCallback(IntPtr puser);



        [UnmanagedFunctionPointer(System.Runtime.InteropServices.CallingConvention.Cdecl)]

        internal delegate int SQLiteCollation(IntPtr puser, int len1, IntPtr pv1, int len2, IntPtr pv2);



        private const string SQLITE_DLL = "sqlite3";

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_close(IntPtr db);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_create_function(IntPtr db, byte[] strName, int nArgs, int nType, IntPtr pvUser, SQLiteCallback func, SQLiteCallback fstep, SQLiteFinalCallback ffinal);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_create_function_v2(IntPtr db, byte[] strName, int nArgs, int nType, IntPtr pvUser, SQLiteCallback func, SQLiteCallback fstep, SQLiteFinalCallback ffinal, SQLiteFinalCallback fdestroy);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_finalize(IntPtr stmt);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_open_v2(byte[] utf8Filename, out IntPtr db, int flags, IntPtr vfs);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_open(byte[] utf8Filename, out IntPtr db);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl, CharSet = CharSet.Unicode)]

        internal static extern int sqlite3_open16(string fileName, out IntPtr db);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_reset(IntPtr stmt);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_bind_parameter_name(IntPtr stmt, int index);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_column_database_name(IntPtr stmt, int index);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_column_database_name16(IntPtr stmt, int index);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_column_decltype(IntPtr stmt, int index);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_column_decltype16(IntPtr stmt, int index);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_column_name(IntPtr stmt, int index);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_column_name16(IntPtr stmt, int index);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_column_origin_name(IntPtr stmt, int index);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_column_origin_name16(IntPtr stmt, int index);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_column_table_name(IntPtr stmt, int index);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_column_table_name16(IntPtr stmt, int index);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_column_text(IntPtr stmt, int index);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_column_text16(IntPtr stmt, int index);



        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_errmsg(IntPtr db);



        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_prepare(IntPtr db, IntPtr pSql, int nBytes, out IntPtr stmt, out IntPtr ptrRemain);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_table_column_metadata(IntPtr db, byte[] dbName, byte[] tblName, byte[] colName, out IntPtr ptrDataType, out IntPtr ptrCollSeq, out int notNull, out int primaryKey, out int autoInc);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_value_text(IntPtr p);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_value_text16(IntPtr p);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_libversion();

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern void sqlite3_interrupt(IntPtr db);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_changes(IntPtr db);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_busy_timeout(IntPtr db, int ms);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_bind_blob(IntPtr stmt, int index, byte[] value, int nSize, IntPtr nTransient);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_bind_double(IntPtr stmt, int index, double value);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_bind_int(IntPtr stmt, int index, int value);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_bind_int64(IntPtr stmt, int index, long value);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_bind_null(IntPtr stmt, int index);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_bind_text(IntPtr stmt, int index, byte[] value, int nlen, IntPtr pvReserved);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_bind_parameter_count(IntPtr stmt);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_bind_parameter_index(IntPtr stmt, byte[] strName);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_column_count(IntPtr stmt);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_step(IntPtr stmt);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern double sqlite3_column_double(IntPtr stmt, int index);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_column_int(IntPtr stmt, int index);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern long sqlite3_column_int64(IntPtr stmt, int index);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_column_blob(IntPtr stmt, int index);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_column_bytes(IntPtr stmt, int index);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern TypeAffinity sqlite3_column_type(IntPtr stmt, int index);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_create_collation(IntPtr db, byte[] strName, int nType, IntPtr pvUser, SQLiteCollation func);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_aggregate_count(IntPtr context);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_value_blob(IntPtr p);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_value_bytes(IntPtr p);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern double sqlite3_value_double(IntPtr p);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_value_int(IntPtr p);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern long sqlite3_value_int64(IntPtr p);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern TypeAffinity sqlite3_value_type(IntPtr p);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern void sqlite3_result_blob(IntPtr context, byte[] value, int nSize, IntPtr pvReserved);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern void sqlite3_result_double(IntPtr context, double value);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern void sqlite3_result_error(IntPtr context, byte[] strErr, int nLen);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern void sqlite3_result_int(IntPtr context, int value);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern void sqlite3_result_int64(IntPtr context, long value);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern void sqlite3_result_null(IntPtr context);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern void sqlite3_result_text(IntPtr context, byte[] value, int nLen, IntPtr pvReserved);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_aggregate_context(IntPtr context, int nBytes);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl, CharSet = CharSet.Unicode)]

        internal static extern int sqlite3_bind_text16(IntPtr stmt, int index, string value, int nlen, IntPtr pvReserved);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl, CharSet = CharSet.Unicode)]

        internal static extern void sqlite3_result_error16(IntPtr context, string strName, int nLen);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl, CharSet = CharSet.Unicode)]

        internal static extern void sqlite3_result_text16(IntPtr context, string strName, int nLen, IntPtr pvReserved);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_key(IntPtr db, byte[] key, int keylen);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_rekey(IntPtr db, byte[] key, int keylen);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_update_hook(IntPtr db, SQLiteUpdateCallback func, IntPtr pvUser);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_commit_hook(IntPtr db, SQLiteCommitCallback func, IntPtr pvUser);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_rollback_hook(IntPtr db, SQLiteRollbackCallback func, IntPtr pvUser);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_db_handle(IntPtr stmt);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_next_stmt(IntPtr db, IntPtr stmt);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_exec(IntPtr db, byte[] strSql, IntPtr pvCallback, IntPtr pvParam, out IntPtr errMsg);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_config(SQLiteConfig config);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_user_data(IntPtr context);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_free(IntPtr ptr);



        //

        // Hier sind die neuen Signaturen:

        //

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern IntPtr sqlite3_backup_init(

          IntPtr pDest,                        /* Destination database handle */

          string zDestName,                 /* Destination database name */

          IntPtr pSource,                      /* Source database handle */

          string zSourceName                /* Source database name */

        );

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_backup_step(IntPtr p, int nPage);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_backup_finish(IntPtr p);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_backup_remaining(IntPtr p);

        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]

        internal static extern int sqlite3_backup_pagecount(IntPtr p);

    }





    public static class SqlliteBackup

    {

        public static IntPtr GetDbHandleFromConnection(SqliteConnection connection)

        {

            Type sqliteConnectionType = typeof(SqliteConnection);

            var _sqlField = sqliteConnectionType.GetField("_sql", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);



            var _sqlFieldValue = _sqlField.GetValue(connection);

            var _sqlFiedlType = _sqlFieldValue.GetType();



            var inner_sqlField = _sqlFiedlType.GetField("_sql", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);

            var inner_sqlFieldValue = inner_sqlField.GetValue(_sqlFieldValue);



            var handleField = inner_sqlFieldValue.GetType().GetField("handle", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);

            return (IntPtr)handleField.GetValue(inner_sqlFieldValue);

        }



        public static string Backup(IntPtr dbHandle)

        {

            if (dbHandle == IntPtr.Zero)

                throw new ArgumentException("The database which should be backed up is not specified (dbHandle == IntPtr.Zero).", "dbHandle");



            string emptyDb = "empty.db";  // Für Android hier Pfad z.B. auf sdcard anpassen

            IntPtr dstDb = IntPtr.Zero;



            string dstDbFileName = Guid.NewGuid().ToString() + ".db"; // Für Android hier Pfad z.B. auf sdcard anpassen

            File.Copy(emptyDb, dstDbFileName);



            if (UnsafeNativeMethods.sqlite3_open(Encoding.UTF8.GetBytes(dstDbFileName), out dstDb) != 0)

                ThrowErrMsg(dstDb);



            var pBackup = UnsafeNativeMethods.sqlite3_backup_init(dstDb, "main", dbHandle, "main");

            if (pBackup == IntPtr.Zero)

                ThrowErrMsg(dstDb);



            // Copy all DB-Pages at once (-1)

            if (UnsafeNativeMethods.sqlite3_backup_step(pBackup, -1) != 101)

                throw new Exception("SQLite should backup all pages at once but didn't finish.");



            if (UnsafeNativeMethods.sqlite3_backup_finish(pBackup) != 0)

                ThrowErrMsg(dstDb);



            UnsafeNativeMethods.sqlite3_close(dstDb);



            return dstDbFileName;

        }



        public static void ThrowErrMsg(IntPtr dbHandle)

        {

            var msgPtr = UnsafeNativeMethods.sqlite3_errmsg(dbHandle);

            throw new Exception(string.Format("Error while openig the destination database: {0}", Marshal.PtrToStringAnsi(msgPtr)));

        }

    }

So verwendet man das ganze:

C#-Code:

            _connection = new SqliteConnection("URI=file::memory:,version=3");

            _connection.Open();



            // Do something with the in-memory db



            string pathToPersistedDb = SqlliteBackup.Backup(SqlliteBackup.GetDbHandleFromConnection(_connection));



            _connection.Close();

Ich habe es mir hier ein bisschen einfach gemacht und verwende eine leere Datenbank, die vor jedem Backup einmal kopiert wird und dann das Ziel darstellt.
Damit das ganze also funktioniert, muss eine leere Datenbank mit dem Namen "empty.db" vorhanden sein (Funktioniert so nicht 1:1 auf Android, dort muss die leere Datenbank in den Assets mitgeliefert oder zur Laufzeit erzeugt werden und dann alles weitere an einer Stelle mit Schreibzugriff stattfinden (z.B. sdcard)).
Es werden auch alle Pages auf einmal kopiert was bei größeren DBs problematisch werden könnte.

Damit man die SqliteConnection weiterverwnden kann und nicht alles über diese PInvoke-Signaturen machen muss, habe ich noch einen kleinen dreckigen Helper gebaut (GetDbHandleFromConnection) der das DB-Handle per Reflection aus einer SqliteConnection-Instanz holt. Hier muss man bei Updates höllisch aufpassen! (am besten einen Build-Task bauen der fehlschlägt, wenn die Version des Mono.Data.Sqlite-Assemblies eine andere Version hat als (bei mir) 2.0.5.0).

Schlagwörter: Mono.Data.Sqlite; Backup; PInvoke