etoy-xgame/etoy/Components/Database/Database.cs

using System;
using System.Runtime.InteropServices;
using System.Text;

namespace etoy
{
    class Database : IDisposable
    {
        const string SQLITE = "sqlite";
        const int SQLITE_OK = 0;

        IntPtr _db;
        bool _connected;

        public Database(string file)
        {
            Connect(file);
        }

        void Connect(string file)
        {
            if (_connected)
                throw new Exception("There is already an open connection");

            Encoding.UTF8.GetBytes(file);

            SQLiteOpenFlags flag = SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create;

            if (sqlite3_open_v2(GetNullTerminatedUtf8(file), out _db, (int)flag, null) != SQLITE_OK)
                throw new Exception("Could not open database file: " + file);

            _connected = true;
        }

        public unsafe void Exec(string sql)
        {
            int r = sqlite3_exec(_db, GetNullTerminatedUtf8(sql), OnSqliteCallback, IntPtr.Zero, out IntPtr errmsg);
            if (r != SQLITE_OK)
                throw new Exception($"Exec code({r}), {Marshal.PtrToStringAnsi(errmsg)}, sql: {sql}");
        }


        public void Close()
        {
            if (!_connected) return;
            _connected = false;
            if (_db != IntPtr.Zero)
                sqlite3_close(_db);
        }

        public void Dispose()
        {
            Close();
        }


        static byte[] GetNullTerminatedUtf8(string s)
        {
            var utf8Length = Encoding.UTF8.GetByteCount(s);
            var bytes = new byte[utf8Length + 1];
            Encoding.UTF8.GetBytes(s, 0, s.Length, bytes, 0);
            return bytes;
        }



        enum SQLiteOpenFlags
        {
            ReadOnly = 1, 
            ReadWrite = 2, 
            Create = 4
        }


        unsafe int OnSqliteCallback(IntPtr state, int columnCount, void** columnValues, void** columnNames)
        {
            for (int i = 0; i < columnCount; i++)
            {
                var name = new IntPtr(*(columnNames + i));
                var value = new IntPtr(*(columnValues + i));
                Console.WriteLine($"{Marshal.PtrToStringAnsi(name)} = {Marshal.PtrToStringAnsi(value)}");
            }
            Console.WriteLine();
            return 0;
        }



        [DllImport(SQLITE, EntryPoint = "sqlite3_open_v2")]
        static extern int sqlite3_open_v2(byte[] filename, out IntPtr db, int flags, [MarshalAs(UnmanagedType.LPStr)] string zvfs);

        [DllImport(SQLITE, EntryPoint = "sqlite3_close")]
        static extern int sqlite3_close(IntPtr db);

        [DllImport(SQLITE, EntryPoint = "sqlite3_exec", CallingConvention = CallingConvention.StdCall)]
        static extern int sqlite3_exec(IntPtr db, byte[] sql, SqliteExecCallback callback, IntPtr state, out IntPtr errmsg);

        unsafe delegate int SqliteExecCallback(IntPtr state, int columnCount, void** columnValues, void** columnNames);
    }
}