Memory shared mode does not work

[Вячеслав Колыбелкин]

I would like to use memory database in shared mode to work with it from separate connections(threads) but I cannot make the functionality work. Could you please take a look at my unit test and its output below and correct me?

    @Test
    public void severalConnectionsShouldBeSharedTest() throws SQLiteException {
        System.out.println("Library version - " + SQLite.getLibraryVersion());
        System.out.println("SQLite version - " + SQLite.getSQLiteVersion());
        System.out.println("SQLite compile options - " + SQLite.getSQLiteCompileOptions());
        SQLite.setSharedCache(true);
        SQLiteConnection connection1 = new SQLiteConnection();
        connection1.open();
        connection1.exec("create table test(id varchar primary key, value varchar)");
        connection1.exec("insert into test(id, value) values('id1', 'value')");
        SQLiteConnection connection2 = new SQLiteConnection();
        connection2.open();
        connection2.exec("select * from test"); <-- exception here
        connection1.dispose();
        connection2.dispose();
    }

Library version - 392

Oct 09, 2018 4:13:04 PM com.almworks.sqlite4java.Internal log
INFO: [sqlite] Internal: loaded sqlite4java-win32-x64-1.0.392 from build\libs\sqlite4java-win32-x64-1.0.392.dll
Oct 09, 2018 4:13:04 PM com.almworks.sqlite4java.Internal log
INFO: [sqlite] Internal: loaded sqlite 3.8.7, wrapper 1.3
SQLite version - 3.8.7
SQLite compile options - ENABLE_COLUMN_METADATA ENABLE_FTS3 ENABLE_FTS3_PARENTHESIS ENABLE_MEMORY_MANAGEMENT ENABLE_RTREE OMIT_DEPRECATED SYSTEM_MALLOC THREADSAFE=1
Oct 09, 2018 4:13:04 PM com.almworks.sqlite4java.Internal log
INFO: [sqlite] DB[1]: instantiated [null]
Oct 09, 2018 4:13:04 PM com.almworks.sqlite4java.Internal log
INFO: [sqlite] DB[1]: opened
Oct 09, 2018 4:13:04 PM com.almworks.sqlite4java.Internal log
INFO: [sqlite] DB[2]: instantiated [null]
Oct 09, 2018 4:13:04 PM com.almworks.sqlite4java.Internal log
INFO: [sqlite] DB[2]: opened

com.almworks.sqlite4java.SQLiteException: [1] DB[2] exec() no such table: test

    at com.almworks.sqlite4java.SQLiteConnection.throwResult(SQLiteConnection.java:1436)
    at com.almworks.sqlite4java.SQLiteConnection.exec(SQLiteConnection.java:496)
    at com.epam.fej.context.InMemoryStorageTest.severalConnectionsShouldBeSharedTest(InMemoryStorageTest.java:99)
...

[Вячеслав Колыбелкин]

The same case works if I use driver

    @Test
    public void severalConnectionsShouldBeSharedTestDriver() throws SQLException {
        Connection connection1 = DriverManager.getConnection("jdbc:sqlite:file::memory:?cache=shared");
        connection1.createStatement().execute("create table test(id varchar primary key, value varchar)");
        connection1.createStatement().execute("insert into test(id, value) values('id1', 'value')");
        Connection connection2 = DriverManager.getConnection("jdbc:sqlite:file::memory:?cache=shared");
        ResultSet resultSet = connection2.createStatement().executeQuery("select * from test");
        resultSet.next();
        System.out.println("id - " + resultSet.getString(1));
        System.out.println("value - " + resultSet.getString(2));
        connection1.close();
        connection2.close();
    }

[Вячеслав Колыбелкин]

The following trick works :)

    @Test
    public void severalConnectionsShouldBeSharedTest() throws SQLiteException {
        SQLiteConnection connection1 = new SQLiteConnection(new File("") {
            @Override
            public String getAbsolutePath() {
                return "file::memory:?cache=shared";
            }
        });
        connection1.openV2(SQLITE_OPEN_READWRITE | SQLITE_OPEN_URI);
        connection1.exec("create table test(id varchar primary key, value varchar)");
        connection1.exec("insert into test(id, value) values('id1', 'value')");
        SQLiteConnection connection2 = new SQLiteConnection(new File("") {
            @Override
            public String getAbsolutePath() {
                return "file::memory:?cache=shared";
            }
        });
        connection2.openV2(SQLITE_OPEN_READWRITE | SQLITE_OPEN_URI);
        SQLiteStatement statement = connection2.prepare("select * from test");
        statement.step();
        System.out.println("id - " + statement.columnString(0));
        System.out.println("value - " + statement.columnString(0));
        connection1.dispose();
        connection2.dispose();
    }

[Igor Sereda]

Hi Вячеслав,

Thanks for posting the problem and sharing the trick. This is consistent with the instructions at https://www.sqlite.org/inmemorydb.html

Although I'm a bit reserved about sharing cache, we can improve the library to allow this particular configuration or, in fact, any random database specification to be passed down to SQLite.

Kind regards, Igor