Nimble Database

Nimble DBMS and RDBMS tools

In the old days there were tools like FoxPro that made it easy for programmers to create and manage their own RDBMS tables, reducing reliance on DBA labor. This approach is perhaps good for tables that may change often but are not particularly large or heavily written to by users (such as ControlTables). If the table(s) later grows larger or more formal, then ideally the APIs are roughly the same between the "nimble" database and the BigIronDatabase like Oracle, so that the calls to the DB do not have to be rewritten from scratch. (Minor modifications may be in order.) Note that I am not talking about using the FoxPro language, but simply the table creation and management tools.

MicrosoftAccess is probably the closest thing to this today, but I really don't like it in terms of quality and design. It appears that Microsoft does not want to improve it because it would cut into the sales of MS-SQL-Server, a more expensive (profitable) product. Often whenever somebody complains about MS-Access's limits or lack of expected features, MS technicians just say, "Perhaps you should upgrade to SQL-Server". It needs some competition. Access's flaws seem to have spoiled the whole concept of NimbleDatabases in people's minds.

Use MSDE (aka SqlServerDesktopEngine), then, with something like DBaMgr (http://www.asql.biz/DbaMgr.shtm), SQLExecMS (http://www.laplas-soft.com/) or even WebMatrix (www.asp.net) for doing the table management etc. Download it from MS, and if it's being used in a "standalone" fashion, redistribute it for nothing along with your app. When you need more, go to "full" SQL Server. It's very compatible with SQL Server. It basically *is* SQL server, with limited performance

Note that a nimble database is not necessarily a DesktopDatabase. It is something that makes it easy for developers, but does not define implementation (such as file-based). I suppose we could loosely define 3 "levels" of database system targets: 1. end-user, 2. developer, and 3. formal DBA. DesktopDatabases tend to target mostly #1, at least the more recent ones.

If you're looking for something in the same neighborhood as MicrosoftAccess, I'd look at FileMaker ... Unlike Microsoft, FileMaker doesn't have any reason not to continue developing its product since doing so doesn't cannibalize its own sales. FileMaker is a bit of a niche product, but in fact a number of arts organizations are using it for non-trivial archiving/data-sharing projects. -- francis

IIRC, FileMaker is not relational. Plus, the MS Access engine (without GUI) generally comes free with most Windows systems.

The makers of FileMaker claim it's relational, but it's not! They've just got a bunch of clever work-arounds for the fact that it's not.

Would MySql (along with a good frontend) count as a NimbleDatabase? I've been using MySQL-Front on a WAMP development machine to map structure and test SQL for a live FoxPro database. I think its a step higher that what you're talking about here, though, so I'm curious.

MySql runs in its own process. I generally consider a NimbleDatabase to be one that executes directly upon API call rather than cross-process.

I'd be a bit more user-centric: If I can't ship a self-contained application, then its not a nimble. Ideally, I should be able to ship a single, statically-linked, executable. Users should just see a "save-as" dialog. If my users know/care whether its a file or a database -- then its not nimble. If it meets the "user doesn't know/care" requirement, then I (as the developer) don't care if the API implementation actually forks a new process.

I find a single file a rather stringent criteria. What about a single compressed file that unpacks to multiple files, some of which may be databases and other things?

I don't think that I said that it has to actually be a single file: as long as the interface to "it" is a single "filename". If I write a diagram-tool, then I want to be able to save my diagram in such a way that it can be accessed via a named entity within my directory tree; and preferably be able to be backed up by copying that entity using standard OS facilities. If the API implements incredible magic to provide that illusion, then I'll be happy.

With the current state of the art in open-source DBs, I don't think that I'd consider writing a diagram-tool that uses a database to store the drawings. Expecting a user to install/configure MySQL, or whatever, is it too great a barrier.

Obviously you guys are unfamiliar with MySQL's libMySQLd embedded server and tools, these enable a developer to bundle MySQL within their C/C++/C# application! http://dev.mysql.com/doc/mysql/en/libmysqld.html * MattErbst

It sounds like you want something like a Microsoft MDB (MS-Access) file. I don't know if anyone sells or makes API's to access those files so that you don't need to install MS-Access. I do know there are C libraries to read DBF files of the kind used in dBASE and FoxPro. But, they store one table per file instead of all tables of a database. Maybe there are other formats that might fit what you need. BTW, I don't consider MySql a NimbleDatabase system.

Another barrier is that databases don't typically play well with version control systems: I can't use a tool as part of my development flow if I can't manage its data in the same source control repository as the rest of my data. Binary files are bad, but databases are worse.

I am not quite sure what you mean. Is it because you can't find one that can put databases in single files?

That would certainly be a helpful feature. A table-per-file is often acceptable also. However, it is hard to transfer things such as indexes that way. You may transfer the table but forget to transfer the updated index file(s) for it.

Xbase was quite nimble because it did not use SQL and thus did not have to parse SQL and translate it into navigational primitives. However, it has a bit of NavigationalDatabase flavor to it, and thus is not pure relational. It uses the table structure, but cursors instead of sets for the most part. But, this is perhaps what made it nimble from a hardware perspective. There are open-source C libraries for accessing Xbase formats. This allows one to use a tool like FoxPro or dBASE IV to create, inspect, and edit the tables during development, yet ship with a small database engine.

Is Berkeley DB a NimbleDatabase? It is an embedded database and is used by the popular blogging software, Moveable Type. Seems pretty nimble to me...

Is Firebird a NimbleDatabase? it can also be embedded and there are no licensing fees.

I've not actually used it yet, but it's attracted my interest: http://www.sqlite.org

Embeddable SQL database that has no separate process. Whole DB is in one file, it's free, you can have the source and it runs on lots of platforms. --KatieLucas

SqLite is an excellent embedded database. Right now (June 2003) I think it is clearly the best open source embedded database around. It's a pretty clean drop-in for a full RDBMS too. -- IanBicking

One thing that seems to have made NimbleDatabases less common now than in the past is that SQL syntax is too complicated to write small and cheap interpreters for. Past products didn't use SQL for the most part (although many of the earlier languages were far from pure relational). Alternatives to SQL are listed in RelationalLanguage.

Perhaps hsqldb counts as nimble? It uses SQL syntax but runs entirely inprocess, and stores data in memory or in simple files. http://hsqldb.sf.net/

Features of Existing NimbleDatabases

File-based (generally a requirement for "nimble" status)
- EditHint: this doesn't make much sense, even MySQL is file based. All databases use files and memory. I think what you mean is that it uses a single file (or single per table) rather than multiple complex file layouts. And I think you mean that the file can easily exist without complicated installation of the database (i.e. SqLite is easy to deploy, just a DLL usually, whereas mysql is not so easy to deploy)
- Generally a "file-based" database means that there is no "central service" that serves up data to the clients. Rather, libraries embedded or installed at the client talk to the file system directly. They are generally not as reliable and scalable as the "service server" model. For example, if a client locks a segment of a file and then hangs, the segment may stay locked indeterminately, while a central service would police lock duration limits. The file approach is sometimes also called a "LAN database". Somewhere there's a topic about this on wiki. --top
Concurrency ability (yes, no)
Tables and indexes in single file or multiple
ODBC/JDBC drivers
Query Language (SQL or others)
Indexing (assume all have unless stated otherwise)

MicrosoftAccess: concur: yes, single-file, ODBC, Language: SQL

concur: yes?! Limited, perhaps. I've spent a lot of time "correcting" databases that access managed to screw up via concurrent writes

VisualFoxPro: concur: yes, multi-file, ODBC, Language: SQL and ExBase

HSQLDB: ?

AxionDatabase: concur: yes, multi-file (also supports transient "in memory" mode), JDBC, Language: SQL plus extensions

FileMaker: ?

SqLite: concur: limited (one writer at a time, many readers), single-file, ODBC: experimental, Language: SQL

Paradox (owned now by Corel?)

SmallSql: concur: yes, multi-file, JDBC, Language: SQL

Sorry for barging in, followed link from SqlServerDesktopEngine to here.

Coming from a BigIron background, I would call a database nimble if it has most of the usage characteristics of a DBMS without the ManagementSystem?. Would this suffice to define / describe a NimbleDatabase? --dl

Nobody seems to agree on the characteristics that deliniate these kinds of things. But feature-lists can be found in MinimalTable and DatabaseDefinition.

A new one: http://www.dekorte.com/projects/opensource/twindb/ -- JasonGrossman

See DbasGoneBad, DesktopDatabase, DynamicRelational, MinimalTable, SimplifyingRdbms, DoesSqlHinderNimbleDatabases,