Derby v SQLite

[David Leader-2]

In another thread I recently wrote "I'm happy with the way that Derby  
has behaved, but I'd like to ask a provocative question nevertheless..."

Back in the summer, when I told my son (who is a computer  
professional) that I had a Masters student porting my MySQL db to  
Derby, he told me I should be using SQLite, which is what Google is  
using in its Gears thing. I persisted with Derby, mainly because it  
is Java and my app was in Java, but I'd be interested to know what  
people regard as the relative strengths of weaknesses of the two  
embedded DBs.

David

[Derby-3]

There are a couple of issues raised by your post/email...

First the architecture decision on whether to use a java db or to use a c
language based db.  You don't really talk about your application and any
design constraints. In some respects, the database could be separate from
your app. (A centralized DB server for example.)

The second issue is Derby vs whatever....

What you're concerned with is finding a tool that has gained enough critical
mass to be viable long term. I asked a question on linkedIn regarded what
criteria is there in determining if a software language/architecture has
obtained "critical mass". (COBOL, C/C++, Java, Python, ... all have or had
reached critical mass are viable long term)

If we looked at MySQL, it has but it hasn't. Sure its free to develop in,
but wait until your app goes in to production.... I think that MySQL has hit
a ceiling and I don't expect to see very much more growth.

If you look at Derby, it exists as Cloudscape, Derby, and JavaDB. (Although
IBM is pulling its support....)  As JavaDB, I believe that Derby is close to
achieving "critical mass".  

With respect to SQLite, I don't think that it has achieved "critical mass"
nor will it. There are a lot of other products out there that just doesn't
have the mind share to survive.

Its nice that Google "supports" it, but is Google like Microsoft going to
support and promote its own development platform like .net and C#?

Does this make sense?
As an "early" adopter, you're taking a risk that your platform/technology
will be viable over the next 5+ years or is portable enough to be easily
migrated....


HTH

-G

> -----Original Message-----
> From: David Leader [mailto:d.leader@...]
> Sent: Tuesday, October 30, 2007 2:54 PM
> To: Derby Discussion
> Subject: Derby v SQLite
>
> In another thread I recently wrote "I'm happy with the way that Derby
> has behaved, but I'd like to ask a provocative question nevertheless..."
>
> Back in the summer, when I told my son (who is a computer
> professional) that I had a Masters student porting my MySQL db to
> Derby, he told me I should be using SQLite, which is what Google is
> using in its Gears thing. I persisted with Derby, mainly because it
> is Java and my app was in Java, but I'd be interested to know what
> people regard as the relative strengths of weaknesses of the two
> embedded DBs.
>
> David
>

[francois.orsini]

David,

Gears is a client side framework for web applications. If you have a Java application (be it client standalone, web or server) and you want to embed a database system and be able to port it across platforms, Derby makes a lot of sense (being 100% Java); It has a strong and active development community and it is integrated in lots of other products ( http://wiki.apache.org/db-derby/UsesOfDerby). One can also purchase support outside of the community with Java DB (http://developers.sun.com/javadb ), which is Sun's supported version of Apache Derby and which comes bundled in Sun JDK 6 as well as GlassFish Java application server.

Last, Derby has been developed with multi-user support and has been around for around 10 years, in various incarnations. If multi-user support is another important point for your application, Derby has a multi-threaded core engine with row-level locking and support for standard isolation levels, as well as online back-up / restore capabilities, data/network encryption and you can enable an embedded instance of Derby to be remotely accessible, if required.

Hope this helps,

--francois

On 10/30/07, derby@... <derby@...> wrote:

There are a couple of issues raised by your post/email...

First the architecture decision on whether to use a java db or to use a c
language based db.  You don't really talk about your application and any
design constraints. In some respects, the database could be separate from
your app. (A centralized DB server for example.)

The second issue is Derby vs whatever....

What you're concerned with is finding a tool that has gained enough critical
mass to be viable long term. I asked a question on linkedIn regarded what
criteria is there in determining if a software language/architecture has
obtained "critical mass". (COBOL, C/C++, Java, Python, ... all have or had
reached critical mass are viable long term)

If we looked at MySQL, it has but it hasn't. Sure its free to develop in,
but wait until your app goes in to production.... I think that MySQL has hit
a ceiling and I don't expect to see very much more growth.

If you look at Derby, it exists as Cloudscape, Derby, and JavaDB. (Although
IBM is pulling its support....)  As JavaDB, I believe that Derby is close to
achieving "critical mass".

With respect to SQLite, I don't think that it has achieved "critical mass"
nor will it. There are a lot of other products out there that just doesn't
have the mind share to survive.

Its nice that Google "supports" it, but is Google like Microsoft going to
support and promote its own development platform like .net and C#?

Does this make sense?
As an "early" adopter, you're taking a risk that your platform/technology
will be viable over the next 5+ years or is portable enough to be easily
migrated....


HTH

-G

> -----Original Message-----
> From: David Leader [mailto:d.leader@...]
> Sent: Tuesday, October 30, 2007 2:54 PM
> To: Derby Discussion
> Subject: Derby v SQLite
>
> In another thread I recently wrote "I'm happy with the way that Derby

> has behaved, but I'd like to ask a provocative question nevertheless..."
>
> Back in the summer, when I told my son (who is a computer
> professional) that I had a Masters student porting my MySQL db to
> Derby, he told me I should be using SQLite, which is what Google is
> using in its Gears thing. I persisted with Derby, mainly because it
> is Java and my app was in Java, but I'd be interested to know what
> people regard as the relative strengths of weaknesses of the two
> embedded DBs.
>
> David
>

[Ryan P Bobko]

Personally, I think there's no comparison between Derby and SQLite.
Derby's an actual database with "all" the bells and whistles, but still
a really compact size. SQLite is an extremely fast database-like system
with a much smaller subset of features and SQL compliance. Plus, if
you're writing in Java, go with Derby. If you're writing in C or C++,
give SQLite a run-through.

Off the top of my head, SQLite doesn't support foreign key constraints,
or use column types (everything is a string, unless it's an int, which
is actually a string). In the quirks department, I've noticed join order
can have a dramatic effect on performance. What's really nice is that
the whole database is a single file, which makes using it as a save file
 in your application really nice. Also, startup times are zero. I think
Derby takes a second or two to startup. Both systems support
transactions. Derby can be used in a multi-user mode, while SQLite is
strictly mono-user.

I know both databases claim to be zero-administration, but I'd say
SQLite more serious about it. I don't even know how to configure SQLite.
Derby certainly works great without administration, but there are a
whole lot of options you can muck with if you like.

My two cents,
ry

> In another thread I recently wrote "I'm happy with the way that Derby  
> has behaved, but I'd like to ask a provocative question nevertheless..."
>
> Back in the summer, when I told my son (who is a computer  
> professional) that I had a Masters student porting my MySQL db to  
> Derby, he told me I should be using SQLite, which is what Google is  
> using in its Gears thing. I persisted with Derby, mainly because it  
> is Java and my app was in Java, but I'd be interested to know what  
> people regard as the relative strengths of weaknesses of the two  
> embedded DBs.
>
> David
>
>
>
>

[David Leader-2]

I found the replies to my query interesting. I guess I need to ask my  
son why he favours SQLite. I much prefer to stick with a Java-based  
solution as writing academic software on the Mac it has to be cross-
platform, and I was unaware - and found it heartening - that Sun's  
JavaDB was an implementation of Derby. (OK, for server stuff it  
doesn't matter. Hell, I have Perl scripts called by my Servlet-based  
web app.) I guess that the one reason that Google preferred SQL is  
that it could not rely on Java being installed on user's machines. In  
a way Microsoft made the decision for them.

David

PS If anyone is interested in my final executable app with the  
embedded Derby db, they can download it from <http://motif.gla.ac.uk/ 
dihedralizer.html>. Even if you don't understand it, you can get some  
pretty pictures :-)

[David Van Couvering-2]

Very nice!  Is Java DB embedded in the applet, or used solely on the
server side?

David

On Oct 31, 2007 2:41 PM, David Leader <d.leader@...> wrote:

> I found the replies to my query interesting. I guess I need to ask my
> son why he favours SQLite. I much prefer to stick with a Java-based
> solution as writing academic software on the Mac it has to be cross-
> platform, and I was unaware - and found it heartening - that Sun's
> JavaDB was an implementation of Derby. (OK, for server stuff it
> doesn't matter. Hell, I have Perl scripts called by my Servlet-based
> web app.) I guess that the one reason that Google preferred SQL is
> that it could not rely on Java being installed on user's machines. In
> a way Microsoft made the decision for them.
>
> David
>
> PS If anyone is interested in my final executable app with the
> embedded Derby db, they can download it from <http://motif.gla.ac.uk/
> dihedralizer.html>. Even if you don't understand it, you can get some
> pretty pictures :-)
>
>
>