This page is for the discussion of a proposed implementation of an SQL backend for GRAMPS.

SQL Backend

Currently, GRAMPS uses a BSD database as its internal file format. While this is considerably better than, say, an XML format, the choice of the BSD-DB has a considerable number of drawbacks. This proposal explores the use of SQL, specifically SQLite as a replacement.

First, there are a number of facts related to this proposal:

BSDDB is being removed from the standard distribution of Python (as of Python 2.6)
SQLITE is being added to the standard distribution
BSDDB is not a relational database, but a hierarchical one
BSDDB requires quite a bit of the database structure logic to reside in code (rather than in the database)
SQLite tables of a database reside in a single file
SQLite can optimize queries (in low-level C) whereas BSDDB is done in Python

Next, are a number of issues that need to be tested, but best guesses suggest:

An SQLite version of a GRAMPS BSDDB would be at least 4 times smaller
An SQLite version of a GRAMPS BSDDB should be much faster
An SQLite version of GRAMPS would allow people to create much bigger trees

Further implications:

A fullscale MySQL backend would be a trivial step from SQLite
Easy to allow multiple users in a SQLite database (uses file-locking)

Discussions of BSDDB in Python

BSDDB has had a hard time in Python. Python Developers have been wrestling with trying to keep it stable. Guido finally decided to separate BSDDB from the standard Python Distribution. See discussions:

PEP 3108 marks BSDDB to be removed: http://www.python.org/dev/peps/pep-3108/

A sqlite shelve interface for Python: http://bugs.python.org/issue3783

From http://www.sqlite.org/cvstrac/wiki?p=SqliteCompetitors: SQLite versus Berkeley DB:

      Berkeley DB (BDB) is just the data storage layer - it does not
      support SQL or schemas. In spite of this, BDB is twice the size
      of SQLite. A comparison between BDB and SQLite is similar to a
      comparison between assembly language and a dynamic language like
      Python or Tcl. BDB is probably much faster if you code it
      carefully. But it is much more difficult to use and considerably
      less flexible.

      On the other hand BDB has very fine grained locking (although
      it's not very well documented), while SQLite currently has only
      database-level locking. -- fine grain locking is important for
      enterprise database engines, but much less so for embedded
      databases. In SQLite, a writer gets a lock, does an update, and
      releases the lock all in a few milliseconds. Other readers have
      to wait a few milliseconds to access the database, but is that
      really ever a serious problem?

See http://www.tribler.org/DatabaseMigration:

See http://www.oracle.com/database/docs/Berkeley-DB-v-Relational.pdf:

Berkeley DB Offers APIs, not Query Languages 

Berkeley DB was designed for software developers, by software
developers.  Relational database systems generally provide SQL access
to the data that they manage, and usually offer some SQL abstraction,
like ODBC or JDBC, for use in applications.

http://pybsddb.sourceforge.net/ref/intro/dbisnot.html

http://mlblog.osdir.com/genealogy.gramps.devel/2005-02/msg00092.shtml&ei=2MYxSanZNaCgesqz0KQB&usg=AFQjCNG1l3yKZ4YP_L7Yo0cQ8bqWmoJKTQ&sig2=H8x1qf4YrFYlsLFlJUsZ-w

From the GRAMPS archives:

> Now, sometimes we get a request for a major architectural change that we
> will accept. A good example is the new database backend for the upcoming
> GRAMPS 2.0. The request came in to support a real database backend so we
> could support larger databases. We analyzed the request, and felt that
> it matched the goals of the project and would provide a significant step
> forward in the usability of the program. The result was a major redesign
> effort that will soon be released.

I think I and few others are the ones that impacted this decision.  Having an 
850,000 person database tends to be deadly to the XML architecture that we 
were with.  I've been the main person to test the integrity of the system 
with my Gedcom file importing.  When I found that I couldn't import my file 
without extensive data loss, I came to Don and Alex and we all sought for 
solutions.  We found that the XML interface was taking huge amounts of 
memory, and we looked for database backends that would handle the load.  Don 
and Alex came through with the BSDDB backend, and ever since 1.1.3, I've been 
happy as a clam with the Gramps project, because I'm one step closer to 
killing Windows.

I personally want to do away with it, but I need it for other applications.  
I've also come to the realization that both Windows and Linux are good, but 
in their own realms.  I don't want this to become a huge flame war about 
Linux and Windows. so if you have other questions as to why I feel this way, 
email me.

> So, would we accept a mySQL database backend? There is a good chance we
> would (depending on the implementation), as long did not impact Aunt
> Martha. We have even architected the backend to support this, since we
> can see that higher end databases could provide additional functionality
> such as versioning and multiuser support.

We could accept mySQL because of this, but I agree with Don.  If it negatively 
impacts the end user, why would we want to proceed with it?  I have a friend 
that wondered about mySQL interaction, but he can see the impact that BSDDB 
has had on my database, and he has sided with me as well as the rest of the 
team.  Not to say that this is not a possibility, but we need to remain 
focused on the tasks at hand.

> So, in summary, the project is going in a direction that seems to meet
> the needs of our users. If we changed directions, we might or might not
> be able to reach a larger audience, but numbers are not our goal. We
> fully support others submitting patches and other contributions, but
> they will be weighed on how they match the goals of the project (and
> most of the patches we've received to date do match the goals). If
> someone wants us take the project in a different direction, we may or
> may not be receptive depending if the direction matches our goals.
> However, we will support your efforts if you decide to fork the project.
> Who knows, maybe a remerge will occur in the future, or a forked project
> will make us irrelevent.

I agree with Don on this, numbers don't matter as long as the users are happy.  
Getting things appropriately nailed down and ready for the end user's use is 
what is paramount.  After all, if there were no users, why would we even have 
a project with which to collaborate in the first place?

We are here for the users, especially Aunt Martha, because of the fact that 
many people are just moving over to Linux and having something familiar to 
them, like a genealogical program is what matters to them.  Making the 
transition to Linux is hard, don't get me wrong.  But we are making it one 
step easier by not complicating the user's experience in their move.

Like I said before, I'm just a bug finder.  I'm not really a Python 
programmer, or anything, but I like to find bugs.  Even if that's all I do on 
this project, I'm rather content.  Everyone else that wants to port over to 
other toolkits and whatnot is free to do so.

But also as an end user that's still a greenie to Linux in general, I can say 
that this program has helped my move over to Linux that much easier.  Even if 
I have only contributed a little in the way of feedback (mostly from the 
end-user perspective).

-Jason

From http://osdir.com/ml/genealogy.gramps.user/2004-06/msg00078.html:

Alex said:

    SQLite might be better or it might not, we haven't tried it. A great factor
    speaking for BSDDB is that it is supported by a standard Python module,
    bsddb. 


Don said:

This is an important factor here - ease of setup and use. GRAMPS is
difficult enough to get installed on some platforms (especially
KDE-centric systems). Requiring someone to get an SQL database up and
running to try out the program is probably too much effort. What I've
discovered is that GRAMPS is one of the first programs that a lot of
new users want to get running - usually before they have a lot of
Linux experience. So we can't make the barriers to entry too high.

GEPS 010: Relational Backend

SQL Backend

Discussions of BSDDB in Python

Navigation menu

Personal tools

Namespaces

Variants

Views

More

Search

Navigation

Contributor help pages

wiki

Tools