Relational database comparison

From Gramps
Jump to: navigation, search

This page is for a comparison of different relational database engines, and is specific for how they might be used for Gramps. (Gramps has used the BSDDB database engine from the 2.0 to 5.0 versions.) This comparison was started to help with GEPS 010: SQL Backend.

SQLite

Advantages

  • far easier to setup. just start writing to the file! no connection or user accounts.
  • smaller install (code) size.
  • easier for users to manage / and share sepperate db's
  • single file
  • good support.

Transportable Trees

From http://www.sqlite.org/onefile.html:

Single-file Cross-platform Database

A database in SQLite is a single disk file. Furthermore, the file format is cross-platform. A database that is created on one machine can be copied and used on a different machine with a different architecture. SQLite databases are portable across 32-bit and 64-bit machines and between big-endian and little-endian architectures.

The SQLite database file format is also stable. All releases of of SQLite version 3 can read and write database files created by the very first SQLite 3 release (version 3.0.0) going back to 2004-06-18. This is "backwards compatibility". The developers promise to maintain backwards compatibility of the database file format for all future releases of SQLite 3. "Forwards compatiblity" means that older releases of SQLite can also read and write databases created by newer releases. SQLite is usually, but not completely forwards compatible.

The stability of the SQLite database file format and the fact that the file format is cross-platform combine to make SQLite database files an excellent choice as an Application File Format.

The Single disk file of sqlite db would be a major selling point for sqlite
for genealogy software since users share and compare db's all the time.
--Aarons

Disadvantages

  • while great for what it is it's not an enterprise level database
  • many "traditional" relational db things are lacking.
  • while tools exist they aren't as fleshed out and solid as the mysql ones.
  • there may be some limits on size mostly due to ram and rom limts. Limits In SQLite Since the whole db doesn't need to be loaded on reads and rights these limits should be far larger than for bsddb.

MySQL

Advantages

  • far better tools for management and reporting
  • a true enterprise level database capable of handling serious loads
  • far more is built into the db. ie auto incrementing fields, stored procedures and on and on. (sqlite may not even have triggers but I can't remember)
  • far more extensive user base and support.

Disadvantages

  • install size (bloat)
  • an actual server to setup run and maintain.
    • there are tools that can do this automatically though and make things almost none existent for an end user. also the embeddable mysql might be an option.
  • may be difficult to manage / share multiple databases. more difficult but very do able. maybe not even that difficult. it should just take some planning.

MySQL Embeded

Needs to be looked at, may be more powerful than sqlite but easier for end users than full mysql

Comparing BSDDB to SQLite

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?


A company that justifies a switch from BSDDB to SQLite; see http://www.tribler.org/DatabaseMigration:

Oracle's description of BSDDB; see http://www.oracle.com/database/docs/Berkeley-DB-v-Relational.pdf. Excerpt:

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.

What BSDDB is not:

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