Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

SQLite Databases & Corruption: Why You Can't Please Everybody

DZone's Guide to

SQLite Databases & Corruption: Why You Can't Please Everybody

· Java Zone ·
Free Resource

Verify, standardize, and correct the Big 4 + more– name, email, phone and global addresses – try our Data Quality APIs now at Melissa Developer Portal!

According to Eric Sink, the reason your SQLite database is dragging may not be your code or any of your design choices, but just because of Google or Apple. And Sink is confident in that argument:

Seriously?

No. Yes. Kinda. Not really.

Ultimately, Sink points to the fact that SQLite databases are fairly easily corruptible simply by connecting more than one to an application corruptible by linking multiple copies of the SQLite library - a "global list (mutex protected) of open SQLite database files," as sqlite.org defines it - to the same application. Once database connections are made using multiple SQLite libraries, work-arounds designed to avoid Posix Advisory Locking bugs will no longer work. [1]

C# developers, for example, run the risk of such library duplication because they access SQLite through a wrapper, which puts some distance between the developer and their database. Sometimes, mistakes are made.

The really interesting thing, though, is how Google and Apple push you into making this mistake. According to Sink, both major platforms ship with SQLite versions from 2012 - potentially even older for Android, given that so many users are still using old versions of the OS - which leads many developers to package apps and tools with their own versions of SQLite. When a library bundles its own SQLite, though, it may cause a problem when your app attempts to use the built-in OS SQLite.

Sink illustrates the severity of the problem with a list of requirements one might have for a SQLite database:

  • Up-to-date version
  • Encrypted data
  • Same version on all mobile platforms
  • No risk of data corruption
  • Compatibility with ORMs and sync tools
  • Not have to think about it

These are all fairly reasonable requirements, and according to Sink, the current state of SQLite adoption makes it so they cannot all be met.

For a complete look at Sink's argument and his conclusions, check out the full article.

[1] Thanks to D. Richard Hipp for corrections and clarifications!

Developers! Quickly and easily gain access to the tools and information you need! Explore, test and combine our data quality APIs at Melissa Developer Portal – home to tools that save time and boost revenue. Our APIs verify, standardize, and correct the Big 4 + more – name, email, phone and global addresses – to ensure accurate delivery, prevent blacklisting and identify risks in real-time.

Topics:

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}