Unable to change page size in Xamarin PCL

[BillFulton]

Hey, wonderful product!!

I encountered this issue:

PRAGMA page_size appears to be ignored with Xamarin Forms PCL.

In my Xamarin PCL I have:

public interface ISQLite
{
    SQLiteAsyncConnection GetAsyncConnection ();
}

In my iOS project I have:

private static string DatabaseFilename = "MyDatabase.db3";

private static string DatabaseFilePath
{
    get
    {
        return (NSFileManager.DefaultManager.GetUrls
                    (NSSearchPathDirectory.LibraryDirectory,
                        NSSearchPathDomain.User) [0]).ToString();
    }
}

private static readonly SQLiteConnectionString connectionString = 
    new SQLiteConnectionString(Path.Combine(DatabaseFilePath, DatabaseFilename), false);
private static readonly SQLiteAsyncConnection asyncConnection = 
    new SQLiteAsyncConnection(() => new SQLiteConnectionWithLock(new SQLitePlatformIOS(), connectionString));

public SQLiteAsyncConnection GetAsyncConnection() 
{
    var syncTask = new Task (() =>
    {
        asyncConnection.ExecuteAsync ("PRAGMA page_size=1024;vacuum");  // Has no effect on page size
    });
    syncTask.RunSynchronously();
    return asyncConnection;
}

This executes without error but does not change the page size. (The journal_mode is DELETE and no database table has yet been created.)

[BillFulton]

I didn't see a way to create a database without also creating a table -- maybe there has to a table before setting the page_size?

[oysteinkrog]

Hmm, I don't know anything about page_size and I'm not sure sqlite.net has support for it at all?

[BillFulton]

Page size seems pretty important. For my app, the default of 4096 bytes per row is at least 8 times larger than needed, meaning that database size blows out very fast. Is there somewhere else I should post this issue?

Thanks

Bill.

[oysteinkrog]

Ah I see, I guess we would have to add explicit support for pragmas that execute before any tables are created. After reading about page size I do agree that this would be nice to have.

[BillFulton]

That would have benefits apart from page_size. Thanks for looking into this issue.

[BillFulton]

I read that for page_size pragma to work, you have to do a vacuum immediately after. Can you vacuum a database that doesn't exist yet? Maybe another solution would be to provide a way to create a database without creating a table?

[oysteinkrog]

I'm sorry if I gave the wrong impression, I don't plan to look at this myself in the immediate future. I simply don't have very much free time to spend on this project, I'm sorry.

[BillFulton]

Thanks for the heads-up @oysteinkrog

[BillFulton]

Here's a possible workaround: http://stackoverflow.com/questions/28779572/version-of-sqlite-net-allowing-page-size-to-be-set-on-xamarin/30904308#30904308