Embedded Databases Evaluation

Alternatives of Embedded Databases

• BSDDB in Transactional mode
• SQLite
• Embedded Firebird

I check the embedded MySQL, but I cannot find anywhere to download it for Windows platform. From the official site, it seems I have to contact some of their salesman to buy the product. Not much documentation of it can be found.

Pros/Cons

Platforms

DB	Windows	Linux	Mac OS X	WinCE
BSDDB	Yes	Yes	Yes	Yes
SQLite	Yes	Yes	Yes	Yes
Firebird	Yes	Yes	Yes	No

Building Berkeley DB for Windoes CE

SQLite for Windows CE

Speed

BSDDB	SQLite	Firebird
Very Fast	Fast	Good

Features

BSDDB: does not support SQL

SQLite: implements most of SQL92

Firebird: Implements all of SQL92

Relational Database Comparison

Locking

BSDDB: fine grained locking, two-phase locking in transaction.

SQLite: database-level(file) locking, not good for highly concurrent environment

Firebird: particular Lock Management, which can lock an object or concept.

Update Schema

BSDDB: Need programming efforts to update the schema.

SQLite: Can alter the table, but cannot delete a column from a table.

Firebird: supported by SQL statement Alter Table.

Python Supporting

All of them have Python binding. BSDDB was included by the Python standard library since 2.3, SQLite was also included since 2.5, and Firebird has not been included.

Technique Supporting

Each of them has a stable development team.

Bugs

Both SQLite and Firebird have the problem to access the database file whose path contains non ASCII characters, while BSDDB doesn't have this problem. It is a serious problem because by default we save the database under C:\C:\Documents and Settings\USER_NAME\Application Data\.Tribler\bsddb, where USER_NAME may contain special characters, like Chinese. This bug is possible to be fixed.

Others

SQLAlchemy: SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL. It uses SQLite as the database.

Fastest Python Database Interface

Berkeley DB report in USENIX

reviews

• VistaDB or Sqlite I think SQLite is okay for very simple database needs where small size is important. Firebird is better if you need a solid database

• SQLite - ALMOST a great embedded database solution for .NET applications

• 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?