The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Sqlite DB as application document format?

Hi all,

I'm currently using zipped xml file as the application's document format, and now are considering switching to Sqlite db for the following reasons:

1. With the current approach read/write operation usually involves extracting/compressing zip archive and parsing/writing XML documents, low performance.

2. I' going to implement sophisticated search functions including full-text-search functions, I'll get inborn support on implementing these new functions if the application documents are stored in SQLITE DBs.

3. I'm now facing in-memory data structure changes due to massive features enhancements, even keeping the current document format I'll have to take some rework on the persistence module.

However, on the other hand, One consideration in my mind is that the document format will be published for the purchase of data exchange in the future, obviously zipped XML file has the advantage of easy to read/understand. But is this worth not switching to SQLITE DB?

Any comments are appreciated.
Wednesday, March 26, 2008
Another advantage of using SQLITE DB is, my application is desktop application current, I'll might need to implement collaborating features, for instance, change to a C/S architecture, SQITE DBs will give me far more support than zipped xml files can do.
Wednesday, March 26, 2008
I've used SQLite this way before. Its a great package. Firefox uses it now. Its open source and the file format is documented, so no worries about people not being able to take a peek at their data. ( ).

Also, no reason you can't retain the XML as an export format.

Subversion kinda has the same thing going: plain text, multi version supported dump files for export/backup/import, and then Berkeley DB based binary files for the repository.

You might want to look at Berkeley DB, although frankly, I find SQL easier for a lot of things than "raw" B-tree's.

On second thought, go for SQLite, and do the XML as export if you really think it would be useful/valuable to people.
Matt Send private email
Wednesday, March 26, 2008
Apple uses SQLite in serveral of its applications. Safari and Mail use it. Apple's CoreData can be configured to use it as well when you build your own applications.
Jeff Hawkins
Wednesday, March 26, 2008
Jeff Hawkins reads DoS??  :)
Wednesday, March 26, 2008
+1 for sqlite
you can't go wrong. the disk files are rock solid, also using an in-memory database (open with filename of :memory: ) is very useful. i use an in-memory database to cache data calls to sql servers on other machines and sometimes to hold internal data structures. its a very flexible tool.

Don Dickinson Send private email
Wednesday, March 26, 2008
The beauty of XML is it is super easy to add new elements and attributes, and modify or remove same, without usually breaking anything. ie. The eXtensible bit.

SQL is a different beast altogether. You are dealing with structured data, schema's, tables etc. Migrating schema changes can be difficult. Further SQLite doesn't help much with such things. And it doesn't let you drop columns.

There is a lot to be said for XML in certain use cases.
Neville Franks Send private email
Wednesday, March 26, 2008
FWIW, You can drop columns, not the "easiest" compared to what it should be:

Anyway, there is a lot to be said for XML and adding fields willy nilly. Personally, I consider having to upgrade the schema with a script on explicit version points to be good anyway.

As to using it as a "file format" for an application, there are some clear arguments pro database. The chief one is time to completely rewrite the XML file on each file save, vs. the transaction random access nature of writing to a DB.

Of course, adding fields in an "application" file format means that you will have to version the schema(you ought to version the XML too, you realize?) and the whole schema will have to be updated as well.

Anyway, I would likely to opt to keep an XML format, but only for export/import. Honestly, depending on the nature of the data, I would also go for some other formats like a CSV file(again, it would depend on the nature of the data and expected use cases, particularly ones involving spreadsheets).
Matt Send private email
Wednesday, March 26, 2008
Why not getting the best of two worlds? Try an xml based data base which has the extensibility of Xml and the searchability of SQL.

There are a couple of open source projects that offers such functionality. I've not tried any of them personally. Apache has one, caled Xindice ( Been from Apache, I would try it the first. It comes from an old project called XmlDB, so i would expect it to be mature.

This other looks pretty neat:
Pablo Send private email
Thursday, March 27, 2008
These are both Java applications. Not something small and light weight that you'd want to throw into an existing app, especially if it wasn;t Java which I assume the OP's app isn't. I am not aware of any light weight XML DB's, period.
Neville Franks Send private email
Thursday, March 27, 2008
The TWiT Floss Weekly podcast just did an episode on SQLite:

If your application's data looks like a set of documents you may want to consider storing XML data in the database and having enough structure in the database to support sensible SQL querying.

Also, if you need full text searching I see there is a FTS plugin for SQLite but you may also want to look at one of the ports of Lucene to C or C++.
Thursday, March 27, 2008
Thank you guys for all your input, In fact I develope applications using DELPHI, so not to mention the performance issue with XML DBs, lacking of Delphi support is the first barrier to me. My app documents are similar to Visio drawings, so storing XML data into DB is not a good way in terms of searchability and performance.

I'm primary considering SQLITE is not only because its high performance but also because AFAIK it's the only embedded DB that supports full text search, I think I'll go with DiSqlite which is a native Delphi port of the original SQLITE, anyone has experience with its stability and performance?

Thank you!
Thursday, March 27, 2008
Neville Franks

>I am not aware of any light weight XML DB's, period.

Well, here is one:
Pablo Send private email
Friday, March 28, 2008
Pablo: I can't believe you used "light weight" and a link to Oracle in the same post.
Ken White Send private email
Friday, March 28, 2008
FWIW, I'm in the process of moving FeedDemon's backend from XML to SQLite, and so far the change has been a good one.  As you can imagine, the performance is much better since parsing and updating separate XML files is painfully slow.

I'm using DISQLite3 and have found it to be very well done, but I haven't used the FTS features yet.
Nick Bradbury Send private email
Monday, March 31, 2008
Thank you all for your comments, it seems now there is no reasons not to go for SQLITE :)
Tuesday, April 01, 2008

I can't believe you made this comment just because it is named Oracle. Read the product's page, google a little, then make your comments. An advance:

"Berkeley DB, initially written by Michael Olson, Keith Bostic, and Margo Seltzer,originated in 1991 at the University of California at Berkeley with the intent of replacing AT&T's dbm library in BSD 4.4. Several years later, Netscape had embedded Berkeley DB in their Netscape Directory Server and required several enhancements regarding recovery, transaction capabilities, and multi-user support. In order to further the development of Berkeley DB, the initial developers founded a company called Sleepycat.

Years later, in 2006, after Berkeley DB had grown into being the most successful open source embeddable database engine, Oracle purchased Sleepycat. Since that time, Oracle has furthered the development of Berkeley DB by adding multi-version concurrency control, on the fly upgrade capabilities, and more."

Pablo Send private email
Wednesday, April 02, 2008
Ken, are you competing with Brice now?
Object Hater
Sunday, April 06, 2008
I could also point you to ScimoreDB SQL.

Scimore can run both as embedded, standalone and distributed over many machine. That makes it a better choice, if you want later to switch into client/server application.

Its nice and stable, have more than 1 mio installations world wide (the embedded version).

And it has fully integrated Lucene engine.

And it supports t-sql...

And you can interface using COM, c++ and .net.
Scimore Send private email
Tuesday, April 08, 2008

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz