Saving a file to an OS X WebDAV server using Apache VFS and Sardine

I’ve recently been looking into the options for storing a relatively large number of user-uploaded files in a non-hierarchical storage “bucket” with a sufficient degree of abstraction to allow the files to reside either locally or on a server (e.g. WebDAV, SFTP) without any (or many) changes to the code. A virtual file system (VFS) is pretty well suited to this kind of task and, as it happens, there are a number of VFS implementations available for Java including Apache Commons VFS and TrueVFS.

For now, I’ve opted to use Apache Commons VFS with a WebDAV file provider. Overall it’s relatively straightforward to get the various moving parts up and running, but there were a few pitfalls in setting up the WebDAV server, ensuring that Maven is pulling in the right dependencies and copying file content to VFS.

Rationale for using Sardine

From the Commons VFS homepage: “Apache Commons VFS provides a single API for accessing various different file systems. It presents a uniform view of the files from various different sources, such as the files on local disk, on an HTTP server, or inside a Zip archive.” WebDAV is one of the sources supported by Commons VFS, but the default implementation is the same as that used by Apache JackRabbit. As of writing, the JackRabbit implementation relies on the deprecated Commons HttpClient for its HTTP support, which has long been superseded by Apache HttpComponents. Including a deprecated and unsupported HTTP client library isn’t very appealing from a security standpoint, neither is it desirable in terms of longer-term project maintenance.

Thankfully, Sardine comes to the rescue here. In brief, Sardine is a relatively modern, lightweight WebDAV client focussed on the most common use cases for WebDAV. In combination with Nicolas Delsaux’s excellent commons-vfs-webdav-sardine project, it’s relatively straightforward to use Sardine as a Commons VFS WebDAV provider.

Configuring a WebDAV server compatible with Sardine

One of most attractive aspects of WebDAV is that it’s extremely easy to configure, especially on machines that are already running Apache. However, even given the relative ease of configuration, there are usually a couple of file permission and directory index issues that needed to be resolved.

A basic WebDAV server can be set up by including (or preferably Including) the following in httpd.conf:

The inclusion of the “Options Indexes FollowSymLinks” directive is important as it prevents 403 errors being returned in response to HEAD requests to test for the existence of files on the WebDAV share.

The directory containing the DavLockDB file and the WebDAV directory both need to be readable and writable by the current Apache user and group. The following commands should ensure that this is the case (given the above configuration in httpd.conf):

With AuthType set to Basic, the only remaining task is to create the “admin” user referenced in the LimitExcept directive and restart Apache:

On OS X, the server configuration can then be tested using “Connect to Server…” in the Finder (Cmd-K):

WebDAV connection

Authenticating with “admin” and the password set in the htpasswd stage above should mount a readable and writable WebDAV share in the Finder.

Using Sardine with Apache VFS

With a WebDAV server running locally, VFS and Sardine can be used to take a “local” file and upload it to the WebDAV server as follows:

The above saveFileToDefaultFileSystem() method:

  • Takes a Java File and a baseURL as a String
  • Sets up the VFS DefaultFileSystemManager adding providers for WebDAV and the local filesystem
  • Generates a random filename (UUID) for the bucket store
  • Resolves and creates the (heretofore non-existent) file in the WebDAV file system
  • Converts the passed-in File to a VFS FileObject
  • Uses a try-with-resources block to open an InputStream and an OutputStream from the source and destination FileObjects, respectively
  • Uses the Apache Commons IOUtils to copy the InputStream to the OutputStream and closes everything down
  • Returns the UUID as a String

The only other aspect not covered in the code is the form of the URL passed into the method, which must contain the full URL for the WebDAV server, including credentials as follows:

webdav://username:password@127.0.0.1:80/uploads/

VFS does support use of a UserAuthenticator rather than including credentials in the URL, but that’s probably overkill for getting something up and running quickly. There’s an example of how to use UserAuthenticator on the Commons VFS website, but as a middle ground, the password can be encrypted:

java -cp commons-vfs-2.0.jar org.apache.commons.vfs2.util.EncryptUtil encrypt mypassword

and the output enclosed in curly braces in the URL as follows:

webdav://username:{D7B82198B272F5C93790FEB38A73C7B8}@127.0.0.1:80/uploads/

Maven

Sardine, Apache Commons VFS2 and the Commons VFS Sardine WebDAV file provider will all be added as dependencies when the commons-vfs-webdav-sardine dependency is added to Maven.

Sardine has a dependency on the Apache Commons Codec library. Notably, it makes use of the public Base64(int lineLength) constructor for the Base64 class, which was only introduced in version 1.4. The current project also had a dependency on docx4j, which in turn depends on version 1.3 of the Apache Commons Codec library, resulting in NoSuchMethodExceptions being thrown when the constructor is used. The solution is to either ensure that the Sardine dependency is declared first in the POM (in Maven versions 2.0.8 and later) or that an Apache Commons Codec version >=1.4 dependency is added explicitly to the POM. From the Maven Introduction to the Dependency Mechanism:

You can always guarantee a version by declaring it explicitly in your project’s POM.

Conclusion

There’s obviously much, much more to using Commons VFS with Sardine and WebDAV, but the above should get the basic toolchain up and running, providing a good base on which more sophisticated file operations can be built.

Non-volatile summation of contiguous dynamic ranges in Excel

This is just a very quick post, more as a reminder to my future self than anything else, but when summing a contiguous, dynamically-sized range in Excel, it can be orders of magnitude faster to use INDEX in place OFFSET, the latter of which is volatile and is therefore recalculated in response to changes to any other value on the sheet, regardless of its dependencies. This has already been explored by others, but just for future reference, always try to replace:

with:

That’s all for now!

Generating a hyperlinked table of contents for an Excel workbook using VBA

If you’ve ever needed to generate a hyperlinked table of contents (TOC) for an Excel workbook with more than a few worksheets, you’ll appreciate that it can be quite a laborious task. The following snippet of VBA will iterate over the sheets in a workbook, generating a list of hyperlinks to each sheet in a new sheet at the start of the workbook called “TOC”. If a “TOC” worksheet already exists, the script will ask for a new worksheet name in which to place the table of contents. Typing the same name again will overwrite the contents of the sheet with the TOC. Simple as that.

The code’s fairly run-of-the-mill with one possible exception: the implementation of the SheetExists function. SheetExists takes a string and returns true or false depending on whether a sheet with the specified name already exists in the active workbook. The function uses VBA’s On Error Resume Next statement to achieve this. As per the MSDN documentation, this “specifies that when a run-time error occurs, control goes to the statement immediately following the statement where the error occurred where execution continues.”

As such, in the SheetExists, the call to Sheets(SheetName) would ordinarily throw a “Run-time error 9: Subscript out of range” if the sheet didn’t exist. But with On Error Resume Next the code continues uninterrupted and can check to establish whether the assignment to TestWorksheet was successful or not. If it was, the function return value is set to True, the TestWorksheet variable is set to Nothing (so it can be garbage collected) and On Error GoTo 0 is called to disable the On Error Resume Next. Technically this last step isn’t required as custom error handling is disabled automatically when exiting the Function, but it’s a good practice to get into to avoid leaving run-time errors going unchecked over vast swathes of code.

There’s a gist available here for clones, forks or comments.