Transaction fails due to invalid temporary directory on UWP

[AlekseyMartynov]

(Not sure whether this library is meant to be usable in UAP, but based on aspnet/dnx#2302 I suppose that yes. Currently it works fine when referenced directly).

In UAP you need to adjust SQLite temp path (see similar issue oysteinkrog/SQLite.Net-PCL#157). Without it, certain scenarios involving DDL inside a transaction fail with "SQL logic error".

Related test case:

public MainPage() {
    this.InitializeComponent();

    var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, Guid.NewGuid().ToString());

    // >>>>> this line is a fix
    //SetDirectory(2, Windows.Storage.ApplicationData.Current.TemporaryFolder.Path);

    using(var conn = new SqliteConnection("data source=" + dbPath)) {
        conn.Open();
        using(var cmd = conn.CreateCommand()) {
            cmd.CommandText = @"
                create table foo(id integer primary key, value integer);
                insert into foo(value) values ('a');

                begin; -- >>>>> this causes 'sql logic error'

                create table foo_copy(id integer primary key, value text);
                insert into foo_copy select * from foo;
            ";
            cmd.ExecuteNonQuery();
        }
    }
}

[DllImport("sqlite3", EntryPoint = "sqlite3_win32_set_directory", CallingConvention = CallingConvention.Cdecl, CharSet = CharSet.Unicode)]
static extern int SetDirectory(uint directoryType, string directoryPath);

[bricelam]

Hmm, I thought the native library had code that did this automatically...

[bricelam]

You could always just do this too:

var command = connection.CreateCommand();
command.CommandText =
    $"PRAGMA data_store_directory = '{ApplicationData.Current.TemporaryFolder.Path}';";

connection.Open();
connection.ExecuteNonQuery();

[bricelam]

I think the "pit of failure" here relates to #55. It sucks that you have to do two extra things on UWP just to get it working.

[natemcmaster]

Interesting...why is this bug occurring? (and not just in our library). Isn't SQLite supposed to put temp files in the same directory as the database file?

[AlekseyMartynov]

Both data_store_directory and temp_store_directory are deprecated. https://www.sqlite.org/pragma.html#pragma_data_store_directory

PRAGMA temp_store=MEMORY helps.

[divega]

According to the documentation PRAGMA data_store_directory is still supported for "WinRT" (read UWP) apps.

I am not sure we need to do anything with temp_store. In case it helps, I found a thread that claims SQLite will use whatever the TEMP environment variable says.

[natemcmaster]

We could add automatically run this when a new connection opens. (e.g. https://github.com/natemcmaster/Microsoft.Data.Sqlite/commit/91e2be3ba5acd020aecf08bbfc2123f3992ee3fa). @bricelam how would we setup x-compile? This sample doesn't compile because there is no DNX-friendly way to add a reference Windows SDK APIs.

[bricelam]

Blocked on aspnet/dnx#2302?

[natemcmaster]

For true cross-compile, yes. We can probably get around this using DLR.