Just a note to let folks know what database solution I have chosen for
a couple projects of mine. I searched this group quite a bit looking
for database alternatives and did find the options below from this
search. I thank all the posters before me who pointed me to these
options. I'm posting this in the hope it can be of use to other
developers in a position similar to mine where I needed a low cost
alternative to Pocket Access.
Requirements:
I have a couple of apps I sell and use for personal use. One app
requires
synchronization between desktop and mobile device, the other requires
a push
of data from the desktop to mobile (read-only on the device).
The read-only app requires speedy lookup of data in a flat table
(OLAP-style) containing some 20,000 records. The sync-ed app
typically holds much less data (several hundred rows) and the mobile
device is considered the primary data input device.
While I do sell one of the apps discussed here, I'm not making enough
to quit my day job much less afford to buy a commercial database
solution so free or very low cost was a major factor in my search.
History:
Both of these apps used Pocket Access on the device with Peter Foot's
wrapper from www.inthehand.com and Jet on the desktop. Life was good.
Sync'ing between apps was easy using ActiveSync features and
performance was
acceptable even on the read-only OLAP app. However, problems arose
with the
release of Windows Mobile 2005. In short, Pocket Access was no longer
available and I started getting problem reports from frustrated users
in the
field. I had to find another database.
Research:
I had already started the search for a different database while
planning the
next version of my sync'ed app. The new problem reports re: WM5
expedited
the choice.
I expected to use Visual Studio 2005 and CompactFramework v2 to
develop the new version, so v1.1 compatibility was not an issue.
Performance of the OLAP app is not "critical" (in the sense of
life-or-death), but when a search is started, the sooner the data
comes back the better (obviously). Performance of the sync'ed app is
less critical since more time is spent entering data than searching
records.
Option 1 - SQL Mobile:
I was interested in the new version of Microsoft's mobile version of
SQL
Server, but this was soon discounted. I have a need for
synchronization/replication between the desktop and the device, but
the new
SQL Mobile still requires IIS or a web service or a hack to
synchronize
data. None of these options is easy to configure for an end user.
Another
negative aspect for my purpose is the size of the redistributable. My
largest app weighs in at under 1MB so it's difficult for me to justify
requiring my users to download an extra 20MB or so just to run my app.
SQL
Mobile may work very well for other applications, but I had to discard
it as
an option due to size and complexity of synchronization.
Option 2 - db4o:
My next consideration was an object database provided by www.db4o.com
.
This solution provides a lightweight redistributable with a common
file
format between platforms and a built-in sync option (after downloading
the
data file to the desktop from the device). Performance of this option
is
quite good (much better than either SQL Mobile or Pocket Access) and,
since
it's an object database, it's quite easy to store and extract
application
objects. The query mechanism is not intuitive for those with a strong
SQL
background, but v5 of their solution provides a new query mechanism
that is
easier to learn. Integrating this solution isn't difficult, but it
does
require a different way of thinking and converting an existing
codebase to
use an object model over a traditional relational model could be a
challenge - especially for larger projects. Some will find this
solution
more comfortable and others may find it "invasive" as their older
query
mechanism returns custom collections. Their new v5 release supports
.NET
2's generics, though, so it's less invasive than their previous
releases.
This is an open source solution released under one of two licenses.
If you
need a low cost solution (as I do), their GPL license is free - but it
has
very limiting restrictions. In a nutshell, they want you to give away
your
app under open source. Since one of my solutions is a commercial
exercise,
I don't want to give it away nor do I want to publish my source code.
They
do express a willingness to negotiate a commercial license, but the
example
they list on their site ("as low as $9/unit in lots of 10,000") was
not
encouraging. As intriguing as this solution is, I discarded it due to
its
licensing scheme and relative difficulty of querying (i.e.,
significant code
re-writes to implement).
Option 3 - SQLite:
The third option I considered was a .NET port of SQLite. SQLite is
primarily available at www.sqlite.org, but the option I chose to
evaluate
was the ADO/CF version by Robert Simpson found at
http://sqlite.phxsoftware.com . This version is purely a .NET v2
option,
but there is a v1.1 option available at
http://sourceforge.net/projects/adodotnetsqlite . SQLite has a primary
goal
of being small and light and they achieve that goal coming in with a
redistributable under 300KB. However, a limitation of this goal is
the
relative lack of data types supported and multi-user access probably
is not
a strong suit. The wrapper provided by Robert Simpson hides this
issue of
data types to a large degree, though, and converting an existing
relational
codebase (either SQL Mobile or Pocket Access) should not be difficult.
Performance of this solution is quite good and far exceeds Pocket
Access.
Full table scans on an indexed varchar(255) field of the OLAP database
above
take less than 2 seconds on SQLite as opposed to about 10 using Pocket
Access - on a data file significantly smaller than Access, too (always
a
concern on mobile devices). The data file is portable between both
platforms and while there is no out-of-the-box sync option,
synchronizing
data is a relatively trivial matter of iterating changes between the
two
data files. This does require a means of tracking changes on both
platforms, though, so it is a more complex task than using db4o's sync
feature. The price of this solution cannot be beaten, either. The
code for
the database is released in the public domain with no GPL, LGPL or
other
license required. The ADO wrapper I chose is also free of cost - even
for
commercial use. Considering the low margins I get on sales of my app,
this
was good news, indeed.
My Choice:
After looking at these three database solutions, I decided to
implement
SQLite. This free engine is quite speedy and easy to implement -
especially
into an existing relational codebase. If I were developing an open
source
project from the ground up, the balance may shift in favor of db4o,
but the
performance, ease of implementation and price of SQLite made it the
clear
winner for my needs.
