Login to participate
Register   Lost ID/password?

Louis Kessler's Behold Blog

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.

Family Tree Builder 8.0 also has encrypted their new SQLite database, possibly for the same reason.

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.

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.

Not so Simple Mail Transfer Protocol - Mon, 28 Mar 2016

@Sparkpost to the rescue. As I recently mentioned, it’s been over 4 years since my last Behold Newsletter. This was something that I really had intended to get going again.

In 2010, I was having trouble with the mailing service that I had been using, and I started looking for a new one. Back then I selected LuxSci. They worked well for a while, but then when the Netfirms site moved, they had some glitches and they couldn’t handle bounces any more, so I had to pay for and set up an email account at LuxSci simply to accept the bounce emails so that I could process them and remove the bad addresses from my list.

For another year or so, I continued to use that SMTP service. I was paying a monthly fee whether I used it or not, plus the monthly fee for the bounce email address. But that I could have lived with.

The real problem was the sending of the newsletters. About 300 or so would go out fine in batches of 60 and then it would stall. I’d have to manually restart it, and maybe another 400 would go. Then again, and again. I remember it took me all night once to get all the newsletters out, and I had to babysit it. That was no good.

I spent a couple of weeks going through the phpmailer code. Ugh. Did I ever tell you I really really really hate the PHP language. Well, I do. And I spent days on it and tested it 100 times and couldn’t get it to work any better. I couldn’t spend any more time on this, so I let it lay dormant for a while.

Over the next couple of years, I looked for another solution, and thought I had found one. It was called Mandrill. It was recommended to me by someone technical I know who was using it. It was more than just an SMTP service, but had a programmers interface built into it. The sweet thing was the pricing. “The first 12,000 emails per month are always free.” After that, it was 20 cents per thousand. How could I go wrong? I was ready to go with it.

But I never quite got to that point. I spent the last couple of years developing Versions 1.1. and 1.2 of Behold without the opportunity to breathe and set up the new system for the newsletter. When I started working on version 1.2.1 about 4 months ago, I said to myself that I’m definitely going to start the newsletter up again after I release this version. And then I’d implement Mandrill.

About 2 months ago Mandrill suddenly changed its pricing structure. They went to $9.95 a month for 25,000 emails. That was still bearable. But shortly after they changed again and started selling in blocks of 25,000 emails for $20 a block. And with that, they decided they’d use Mandrill to promote their parent service MailChimp and now required a subscription to the MailChimp service at $20 a month to use Mandrill. Okay, now I’m completely done over with this company before I even got started. Others were upset as well: ”In hostile move, Mandrill gives all developers 60 days to switch to paid Mailchimp service”.


So it was time to look for something else. There were a few companies trying to take advantage of Mandrill’s faux pas. The one I noticed first was Pepipost. Note the bar at the top of the screenshot of their page which says: “Coming from Mandrill? Meet the Free Alternative”:


That sounded cool. 25,000 free emails a month. So I tweeted:image

Shortly after I tweeted that, I saw this tweet: (**Note: Before reading too much into this tweet, see the comment by Sachin of Pepipost as well as my response)


So I took a look at Mailgun and Sparkpost. Sparkpost had an unbelievable offer:


I went down there, found Sparkpost is the SMTP service for programmers using the same fantastic platform that their parent company Message Systems uses. And they’re serious about helping developers as well.

Okay. Did it work? Well, it took me a couple of hours to set everything up. What I didn’t know was whether the 300 to 400 email jam-up 4 years ago was something on my end (Netfirms or phpmailer) or if it was the fault of LuxSci. So then the big test. I sent them out and help my breath. And wow! They were going out, uninterrupted at a rate of 3,500 per hour! No more staying up all night and babysitting. It was simple and it worked.

This was using SMTP to send out each email individually exactly as I had been doing it previously with LuxSci, but without the glitches. This should do me well until I get close to the 100,000 free limit. When I do, I’ll likely spend some time to use Sparkpost’s Application Programming Interface (API) tools and send the newsletter to them just once along with a list of the recipients which should speed things up even more. I’m not yet sending the bounces back to my website to automatically handle them, and I will set that up for a future mailing. For now I just manually downloaded them to a file.

So 4 years went by and I had about 10% bounces (i.e. people on my list who changed email addresses) that I’ve now deleted from my Newsletter list. This agrees with the natural decay rate of about 2.5% per year that I’ve observed in the past.

Hopefully Sparkpost will serve me well up to 100,000 mailings a month and beyond.

Ancestral Birthplace Chart - Sat, 26 Mar 2016

This seems to be the current meme going around. Put together an ancestral birthplace chart of the states and countries your ancestors are from.

Many people have very interesting and diverse backgrounds.

Not me. Mine’s not too exciting:


And my wife’s is even less so: