Support long pathnames (more than 255 characters) on Windows

[buko]

Attempt to open a database with a pathname of more than 255 characters results in the following error:

Caused by: java.sql.SQLException: The database has been closed
    at org.sqlite.core.NativeDB.throwex(NativeDB.java:471)
    at org.sqlite.core.NativeDB.errmsg_utf8(Native Method)
    at org.sqlite.core.NativeDB.errmsg(NativeDB.java:137)
    at org.sqlite.core.DB.newSQLException(DB.java:921)
    at org.sqlite.core.DB.throwex(DB.java:886)
    at org.sqlite.core.NativeDB._open_utf8(Native Method)
    at org.sqlite.core.NativeDB._open(NativeDB.java:71)
    at org.sqlite.core.DB.open(DB.java:174)
    at org.sqlite.core.CoreConnection.open(CoreConnection.java:220)
    at org.sqlite.core.CoreConnection.<init>(CoreConnection.java:76)
    at org.sqlite.jdbc3.JDBC3Connection.<init>(JDBC3Connection.java:25)
    at org.sqlite.jdbc4.JDBC4Connection.<init>(JDBC4Connection.java:24)
    at org.sqlite.SQLiteConnection.<init>(SQLiteConnection.java:45)
    at org.sqlite.JDBC.createConnection(JDBC.java:114)
    at org.sqlite.SQLiteDataSource.getConnection(SQLiteDataSource.java:410)
    at org.sqlite.SQLiteDataSource.getConnection(SQLiteDataSource.java:398)

The underlying issue is that Windows 10, until very recently, doesn't support pathnames longer than 255 characters unless you do some hackery.

This isn't really an issue in xerial or sqlite but I wonder if the code could be modified to present a more informative error. Xerial does not relay the underlying sqlite error code (ERROR 14 CANT_OPEN) and the message "The database has been closed" is pretty misleading. It took me a while to track down this problem and it might save others some time if we could get a more specific error message indicating the database file pathname is too long.

[buko]

It seems like perhaps this is an issue with xerial.

sqlite does support windows longpath names via its vfs mechanism [https://www.sqlite.org/vfs.html#standard_windows_vfses].

For some reason Xerial does not seem to allow specifying the vfs to be 'win32-longpath'.

final SQLiteConfig config = new SQLiteConfig();
            config.setOpenMode(SQLiteOpenMode.OPEN_URI);
            final SQLiteDataSource dataSource = new SQLiteDataSource(config);
            dataSource.setUrl("jdbc:sqlite:" + sqliteDbFile.toAbsolutePath().toUri().toString() + "?vfs=win32-longpath");

Doesn't seem to work though it should according to the docs.

[buko]

There is a workaround for this it seems. A special file prefix exists that will enable longpathnames on Windows: '"\?\".

Therefore the following code seems to work:

final Path sqliteDbFile = createDirectoryAndAncestors(databaseDirectory).resolve(SQLITE_DB_FILENAME);

            // XXX: xerial does not support win32 longpaths. See https://github.com/xerial/sqlite-jdbc/issues/354. While 
            final SQLiteConfig config = new SQLiteConfig();
            //config.setOpenMode(SQLiteOpenMode.OPEN_URI);
            final SQLiteDataSource dataSource = new SQLiteDataSource(config);
            // dataSource.setUrl("jdbc:sqlite:" + sqliteDbFile.toAbsolutePath().toUri().toString() + "?vfs=win32-longpath");
            dataSource.setUrl("jdbc:sqlite:\\\\?\\" + sqliteDbFile.toAbsolutePath().toFile().toString());

The database is opened successfully and all tests pass on Windows.

Obviously this code is no longer portable and will not work on other operating systems like Linux.

Perhaps the native Windows library for Xerial could add this prefix (assuming the pathname is not a 'file:'//') automatically? Though this might break some systems if the library silently manipulates the database pathname.

For systems that use URIs database names Xerial probably should support specifying a vfs using the '?vfs=win32-longpath' parameter. Applications could then detect the OS and add this parameter to be safe.

[gotson]

Is this still happening on the latest version?

[gotson]

Doesn't seem to work though it should according to the docs.

It is not implemented, this would need to define a new pragma and use it when opening the database.

[gotson]

I actually checked the code, and if you set ?vfs=win32-longpath it will be passed to the sqlite3_open_v2() call, so it should work fine.

[gotson]

You need to use the file: URI: https://www.sqlite.org/uri.html for it to work:

ds.setUrl("jdbc:sqlite:file:yourfile.db?vfs=win32-longpath");