SQLite 3.8.x with WAL support

Since Plastic 5.4.16.625 (Nov 21th 2014) we support the new 3.8.x SQLite series adding WAL (Write-Ahead-Logging).

In short: it means if you’re using SQLite and Plastic, it will now work much better than before since it now supports several reads simultaneously and less chances for write operations to get blocked waiting for readers to finish and vice-versa. SQLite is a good option to store your repos on your laptop and this improvements means Plastic will work smoother than before. You can be refreshing your Branch Explorer while the SyncView is still recalculating, something that was not possible before this version.

Why does it matter?

You may be wondering: why should I care about SQLite?

By default Windows severs install a SQLServer Compact Edition (so there’s no need for an external database server) and once you move into production, your team server will be probably running SQL Server or MySQL for performance reasons.

While I wouldn’t recommend SQLite as the backend for team servers, I really think it is a very good option for distributed servers on laptops and workstations.

I run a small footprint Plastic server on my laptop to host a replica of the Plastic code. It is the typical distributed scenario. I always checkin to my localhost server and then I push/pull to the central team server.

Well, I’ve been running SQLite on my Windows laptop (actually the last 2 laptops) for years and I really love it.

Overall I have 64 repositories and 19GB. The largest repo is 5GB so far and it works great (some colleagues in the gaming industry use SQLite repos bigger than 20GB on their laptops).

SQLite works great for individual use and since you’ll be the only user on your local server, it is really a good choice. By great I mean it gives you extremely good performance for both checkins and updates, quite close to what we get with big servers running SQL Server or MySQL on big dedicated hardware.

Now with the 3.8.x support everything goes even better since, as I said above, concurrent reads and writes are supported to a larger extent. You’ll clearly notice if you run a big syncview and while it is calculating you refresh your Branch Explorer. It simply waited for the sync to finish before 3.8 but it runs on parallel now.

How to configure WAL

It is very easy: just edit your db.conf and configure your “ConnectionString” as follows:

Data Source={0};Synchronous=FULL;Journal Mode=WAL;Pooling=true

Where the important parts are the “synchronous” to FULL and the “Journal Mode” to WAL.

A typical db.conf file looks like this:

>type db.conf
<DbConfig>
  <ProviderName>sqlite</ProviderName>
  <ConnectionString>Data Source={0};Synchronous=FULL;Journal Mode=WALL;Pooling=true</ConnectionString>
  <DatabasePath></DatabasePath>
</DbConfig>