Apache Derby is available at
http://db.apache.org/derby/. It is also included standard in Java 6, under the name "Java DB".
Overall Both SQLite and Derby operate directly from disk. Only parts of the database file(s) that are needed in order to carry out the requested operations are read.
Zero-Administration Both SQLite and Derby offer zero-administration, embeddable SQL database engines. SQLite stores all the data in a single cross-platform disk file. Derby spreads its data across multiple disk files.
Host Language Support SQLite is written in ANSI-C. It supports bindings to dozens of languages, including Java. You cannot use Derby with languages that do not use the JAVA VM.
Derby is written in Java and is thus usable only by Java and scripting languages that run on the Java VM (Jython, JRuby, Jacl, etc.) and is currently only exposed via JDBC driver. (There is an ODBC driver for Derby, but it is no longer maintained). However it is a 100% Java JDBC driver, and hence (with occasional glitches) runs cross platform on any JAVA VM with a single binary distribution. (SQLite is very portable as well, but you would have to maintain multiple binaries if shipping a cross-platform product).
SQL Language Support Derby supports all of SQL92 and most of SQL99. SQLite only supports a subset of SQL92, though the supported subset is large and covers the most commonly used parts of SQL92. Some differences are pointed out below. One specific difference: Derby supports RIGHT JOINS and FULL OUTER JOINS, SQLite does not.
Memory Utilization The code footprint of SQLite is less than 250KB. The code footprint for Derby is about 2000KB compressed and is thus more than 8 times larger. However a large amount of this difference is due to Derby's extensive localization and collation support for multiple languages built in. In general the memory utilization of Derby is considerably higher than SQLite, occupying several megabytes of memory.
Concurrency SQLite allows multiple simultaneous readers and a single writer. Multiple processes can have the database open simultaneously. Derby only allows a single process to have the database open at a time in its embedded mode. However, Derby also offers a full client/server mode. In client/server mode, Derby supports giving multiple processes access to the database with row-level locking. Client/server mode of course requires that there be a thread or process available to act as the server, and is less performant than embedded mode.
Roles, Security, Schemas Derby supports full encryption (see below). In addition it supports multiple databases, and full SQL role granting. Derby supports the SQL SCHEMAS, for separation of data in a single database, and full user authorization. SQLite is largely a single database at a time engine. The ATTACH DATABASE command can be used to partially ameliorate this. Because of this design, neither SQL roles are not implemented nor are Schemas. Typically, access to the SQLite disk file grants full access to the caller. This is not a defect, but by design.
Callable Procedures Derby has support for this built in. SQLite allows you to "fake" these, but has no comparable feature.
Typing/Keys SQLite supports only basic types - it is a mostly type-less system. Which can be very nice in some cases, and annoying in others. Derby supports a wide variety of data types, including XML. The foreign key and referential integrity support is also complete.
Built in utilities Derby has built in online backup/restore and database consistency check utilities. SQLite has a basic database consistency check utility, but no corresponding online backup/restore - you must close connections to the file to get a consistent backup. This is not usually a problem for SQLite, since concurrent usage by multiple PROGRAMS is not usually a design goal.
Encryption/Compression Derby has built in support for encryption and compression. SQLite has some optional add ins, but they are not part of the standard library.
Collation Support Both support custom collation functions. Derby comes with many multilingual collations and localizations built in - these have to be manually added to the core SQLite package by the programmer.
Case Sensitive LIKE Derby has case sensitive LIKE operator, SQLite does not. Derby supports custom collation and indices, like SQLite, but doesn't ship with a built in case-insensitive option.
Pagination Derby now fully supports pagination, albeit not with the non standard but common LIMIT, OFFSET paradigms. SQLite fully supports the non-standard-but-extremely useful LIMIT and OFFSET commands that Postgres and MySQL have adopted.
Replication/Failover Derby offers a basic master-slave replication system. SQLite does not have any such mechanism (again, this is rarely part of the design spec for usage of SQLite). There are JDBC clustering drivers available for Derby that allow failover to another Derby server.
Crash-Resistance Both Derby and SQLite are ACID compliant in their default configurations, so their databases will survive a program crash or even a power failure.
Database File Size No data is currently available on the relative sizes of the database files for SQLite and Derby. Both SQLite and Derby support compression of database files; however SQLITE's VACUUM command makes the database inaccessible during its run, whilst Derby's analogous procedure can be run online.
Full Text/Virtual Table Derby has no similar functions to compare to SQLite.
Encryption The ability to encrypt databases is built into Derby. For SQLite, stubs are left for the implementation, but the implementation itself is an extra cost feature.
Speed No data is currently available on the relative speed of SQLite and Derby database engines. Their query operation is similar in function, relative speed of different queries depend on cache-utilization, query plan optimization and implementation. However you should be prepared to unpredictable speed penalty when using Derby under different VMs even on same hardware/OS.
Sphere: Related Content
+18.20.50.png)
