Login to participate
  
Register   Lost ID/password?
Louis Kessler's Behold Blog » Blog Entry           prev Prev   Next next

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.

image

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:
image

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 by Family Tree Builder? 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.

8 Comments           comments Leave a Comment

1. Keith Riggle (geneatech)
United States flag
Joined: Sun, 7 Apr 2013
12 blog comments, 0 forum posts
Posted: Sun, 24 Apr 2016  Permalink

Louis, you make a good case for making the databases open. However, clearly the issue is debatable, since there are apps in two camps, one that is open and one that is encrypted. I don’t think the argument for encryption is any less compelling than the argument for openness. As a genealogist who loves to play with technology, I like the idea of an open database that I can manipulate myself; maybe I could export a good GEDCOM file! But as someone concerned about privacy, I’m careful about protecting the personally identifying information on my devices. There’s more than enough information in my genealogy database for the identities of not just me, but also many family members, to be stolen. That’s why I encrypt my devices, and sensitive files are encrypted, as well. But when my devices are unlocked, which is most of the the time since they rarely leave the house, my unencrypted files are vulnerable. The risk is low and can be mitigated, but it’s also unpredictable.

I don’t think anyone has complained about it because no one has thought much about it. I hadn’t until this discussion started, and I think it’s a discussion worth having.

The great thing is that users have a choice: those that want to use SQLite tools on their database have RootsMagic and MacFamilyTree; those that want encryption have Family Tree Maker and Family Tree Builder. I don’t think all apps have to take the same approach—as with most things in life, one size doesn’t necessarily fit all.

BTW, Family Tree Maker for Windows allows plugins, so there is an API. I’ve seen only one plugin written for it, but the possibility is there. I’ll have to ask Jack Minsky if they plan to continue the API and open it up to the Mac version.

2. Louis Kessler (lkessler)
Canada flag
Joined: Sun, 9 Mar 2003
213 blog comments, 224 forum posts
Posted: Sun, 24 Apr 2016  Permalink

Thanks for your comments Keith.

Then I suppose you’ll never use a tool like RootsMagic, because its database is open and someone might get the .rmgc file from your computer and read it. And you’ll never make a GEDCOM of all your data and never send that data to anyone (at least not with your living people in it). And you’ll never put your data into an online database (at least not with your living people in it). That’s fine. The choice is yours.

I didn’t know that FTM allows plugins. If they do, it would likely give another way of accessing the database. If MacKiev’s policy is security as you say, then they won’t want to allow plugins.

Louis

3. Louis Kessler (lkessler)
Canada flag
Joined: Sun, 9 Mar 2003
213 blog comments, 224 forum posts
Posted: Mon, 25 Apr 2016  Permalink

Keith:

Thinking about this further, I’ve come to realize that thinking that the encryption of the SQLite database makes your data secure is quite inaccurate. In fact, anyone can read a Family Tree Maker database file by simply copying it to their own machine and opening it up in their own version of Family Tree Maker. The file may be encrypted by access at the raw database level, but it is entirely open at the application level.

The only way to provide the type of security you are talking about is if your genealogy software gives you the option to password protect your data file. Then you’d need to provide the password to open it on a different machine or by a different user on the same machine. Offhand, I don’t know of any genealogy software that allows you to password protect your dataset.

Louis

4. Keith Riggle (geneatech)
United States flag
Joined: Sun, 7 Apr 2013
12 blog comments, 0 forum posts
Posted: Mon, 25 Apr 2016  Permalink

Louis, as I said, there are two sides to the debate, but I didn’t say I favored one side over the other. I even specifically said “I like the idea of an open database that I can manipulate myself…”

Your point that encrypted databases like FTM’s can still be opened by anyone with the app is a good one and is the one that render’s Jack’s argument moot. Of course, the identity thieves would have to possess the correct version of FTM to open the databases, which they would have if they stole my Macbook and it were not encrypted at the volume level (which it is).

I asked Jack about the FTM API, and he pointed out that he had already talked to Tamura Jones about it at http://www.tamurajones.net/NewFamilyTreeMakerFuture.xhtml. But this is what he told me:

“As for plugins, there is only one plugin that’s ever been made for FTM — Stefan Harms’ Family Book creator. We worked with Stefan to make sure it would continue working on our new FTM 2014.1 edition (it does), but he has no plans to make a Mac version.

“So why aren’t there more plugins? Ancestry never made the API public. I’m not sure of all the reasons that they decided to let Stefan have access, but that was an historical one-off. And that, along with his lack of interest in make a Mac edition, is the reason there are no Mac plugins.

“As for the concept of plugins in general, Tamura asked me about that in the interview…”

While encrypting databases may be moot, that doesn’t mean I don’t think we should still think about securing the information of living people, but the risk mitigation measures should be taken along with everything else we do to protect our own PIIs. It’s just something that should be added to our checklist.

Keith

5. Louis Kessler (lkessler)
Canada flag
Joined: Sun, 9 Mar 2003
213 blog comments, 224 forum posts
Posted: Mon, 25 Apr 2016  Permalink

That’s funny. I notice I have Family Book Creator in GenSoftReviews, and even wrote in its description that it is a plugin for FTM. And I did read Tamura’s article, but obviously I didn’t register that FTM had a plugin capability. So much of the article was about Ancestry that I must have been thinking Ancestry API and overlooked plugin.

But this brings up plenty of possibilities for the future with API and Plugin announcements possible from many sources.

And I agree with you, Keith, about security with regards to giving your data to others. But when its your own data, I’d like to use different tools to access it different ways, and encryption takes away one of those ways.

6. Enno Borgsteede (ennoborg)
Netherlands flag
Joined: Wed, 9 May 2012
15 blog comments, 0 forum posts
Posted: Sat, 7 May 2016  Permalink

I ran a quick test today, and didn’t see any encryption in My Heritage Family Tree Builder 8, build 8260. When I found that I couldn’t open the database with SQLiteman for Windows, I copied it to the Linux host that my Windows 10 session is running on (in VirtualBox), and found that the Linux versions of sqlite3 and SQLiteman have no trouble opening the database, reading the database schema, etc.

I did notice that some columns have binary data, but I have no problem reading personal names, dates, etc.

7. Enno Borgsteede (ennoborg)
Netherlands flag
Joined: Wed, 9 May 2012
15 blog comments, 0 forum posts
Posted: Sat, 7 May 2016  Permalink

Note that SQLite files in Gramps 5.0 will have binary data too. That is because many primary objects are not normalized. The person object for instance has all name parts and alternative names embedded as lists, which are saved in blobs corresponding with the relevant Python objects.

See also:

http://gramps.1791082.n4.nabble.com/DB-API-and-Gramps-5-0-td4675458.html

8. Louis Kessler (lkessler)
Canada flag
Joined: Sun, 9 Mar 2003
213 blog comments, 224 forum posts
Posted: Sat, 7 May 2016  Permalink

Enno:

Thanks for pointing that out. You are 100% correct. The Family Tree Builder databases are in fact not encrypted. I must have tried opening a FTB 7 database by mistake, and of course, SQLiteSpy can’t open a database that’s not SQLite. But when I tried it on a FTB 8 database, it opened fine. I’ll update my post.

Louis

Leave a Comment

You must login to comment.

Login to participate
  
Register   Lost ID/password?