J
John T
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.
--
John T
http://tknowlogy.com/TknoFlyer
http://www.pocketgear.com/products_search.asp?developerid=4415
Reduce spam. Use Sender Policy Framework: http://spf.pobox.com
____________________
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.
--
John T
http://tknowlogy.com/TknoFlyer
http://www.pocketgear.com/products_search.asp?developerid=4415
Reduce spam. Use Sender Policy Framework: http://spf.pobox.com
____________________
