SQLite for Genealogy Software - Sat, 23 Apr 2016

An article by Keith Riggle yesterday: “Where Are the Free Family Tree Maker Updates” caught my attention. Keith indicated that the Family Tree Maker database was based on the SQLite database software. That surprised me.

SQLite is a multi-platform, speedy, single file database with a small footprint that’s embeddable within the executable of a program. It uses standard relational constructs and the universally used SQL (Structured Query Language) to access and update its data. It is open source and has a very large support community. It is not likely to become a database that will become unsupported anytime soon.

I had known for a long while that RootsMagic uses SQLite. They benefit from a small techno user community who are developing addons, and look at the great stuff they’re doing. RootsMagic has let the group do what it is doing, but does not appear to have been helping them. If they were, they would have supplied the database structure definitions to them. Instead, at least to me, it appears that the SQLite Tools for RM group has mostly reverse engineered what the codes in the database seem to mean.

My Heritage recently rewrote Family Tree Builder using SQLite, which is used in their new version 8.0. Tamura Jones recently reported on the new FTB technology. And I understand that Gramps is considering SQLite for their Version 5.0 release.

I commented to Keith asking if he’s ever tried viewing the FTM database with an SQLite browser.  (I use the free tool SQLiteSpy from Delphi Inspiration). Keith replied back that the FTM SQLite database is encrypted, which basically means it is protected from being read except by authorized programs that know the encryption key.

Jack’s reason was for “the security of user data”. Keith wondered about that in his comment and noted that GEDCOM has the same data and is just as insecure.

I think Jack may have been referring to the security of the database itself. If the database is encrypted, then nobody can use an SQLite tool to add something to it, delete something from it, or corrupt the database in some way.

But I personally think that is a mistake on the part of the developer. RootsMagic to me does it right and leaves their SQLite database open. The SQLiteSpy tool and many others can view and read all the data in the database. You can write and update to the database yourself with an SQL tool if you know what you’re doing. You get a community of people who can feed off your database and write utility programs and enhancements. And I’ve not yet seen any complaints from anyone that the RootsMagic SQLite database is open.

Family Tree Builder 8.0, like RootsMagic, also did not encrypt their new SQLite database. They have an open API for their MyHeritage site as well and encourage developers to support their systems. And I’m sure that Gramps won’t encrypt their database. They’ll want their programming community to interact with it.

This is what a database looks like with a program such as SQLiteSpy:

When access is given to a program’s data, tremendous things can be done by the user community. Desktop programs can provide plugins. Family Historian does this and allows users with programming ability to write Family Historian plugins which they make freely available.

Online databases typically do this through Application Programming Interfaces (APIs) through which programmers can access and even modify the data where allowed. FamilySearch has scores of different programs that access their Family Tree database, including full featured programs like RootsMagic, Ancestral Quest, Family Tree Maker and Legacy. MyHeritage, Geni and others also have an API which it makes freely available.

These companies see this as a win-win situation. More developers can develop addons for their system. And more people will access their system.

Ancestry does not. They too have an API, but it is private. Currently only Family Tree Maker and RootsMagic have been given access to it.

So I wonder why the hesitation in giving access to the database itself by Family Tree Maker and RootsMagic? Why the need for encryption?

I see one last really good reason for a company to open up its database structure.  If they’ve got a really good structure, then maybe others will copy it. If others copy it, then maybe it will become the standard. If it becomes the standard then they are the leaders. Just as FamilySearch was with GEDCOM.

Or even if it doesn’t become the standard, if the database is open, developers can write programs to directly transfer from one database to another without the data loss usually incurred through GEDCOM. This seamless sharing of data with other programs and online trees is something all genealogists want to see.

Followup:  Arb pointed out to me on Twitter that MacFamilyTree also uses SQLite and does not encrypt it. Here’s an example of a wonderful way the database was accessed for a Geographical mapping project.

MobileFamilyTree employs exactly the same SQLite database structure as MacFamilyTree, meaning people can use either program with the same database. Now isn’t that a wonderful concept?

Also in discussion with Arb, I stated that developers should not be afraid of opening up their databases. They may think it will make it easier for their users to move away from their product. But that’s wrong thinking. What it really will do is reassure their users that their data is not trapped within the product and that they won’t lose their data should the product become unsupported and stop working. So it will give them more reason to stay with the product.