The Lack of a Small Unified Database

Why a single-file SQL standard format is necessary and how SQLite can get us there.
Status in KOffice

The database interface in KOffice is called Kexi. Kexi is an integrated environment for creating database schemas and for inserting, querying and processing data. It can run without KDE, on UNIX, MS Windows and Mac OS X. Kexi already creates self-contained database files (.kexi). Queries and other metadata are stored inside the database itself, in special hidden tables named kexi__*. Such metadata is of both visual (column widths, detailed cell formatting) and functional natures (constraints, error messages). This is quite different from what happens today in OO.o, where metadata is stored in an XML package while the data itself is stored somewhere else.

Proposed Solution

Obviously, using the Microsoft Access format, .mdb, is not an option. Why remain dependent on yet another proprietary format that could change overnight? As far as I know, the best solution is SQLite, a lean and mean database engine already available in KDE and GNOME--it's embedded in Kexi and gnome-db, respectively--and built from less than 30K lines of C code. Using SQLite, databases can be designed and used with a standalone, public domain browser on Linux, Windows and Mac OS X. Wrappers in all popular languages already exist; even PHP 5.0.0 already embeds SQLite, version 2.8.14.

For the record, it currently is possible to use SQLite databases as external data sources in OO.o using an ODBC driver. Detailed instructions are available both in English and French.

Another reason to go for this engine might be the SQLite license, making it suitable for inclusion in proprietary products. The main drawback of SQLite today is the lack of some functions, such as ALTER TABLE, check constraints and referential integrity. However, the addition of these function, starting with ALTER TABLE, is planned to happen over the next months. Last but not least, the small amount of code involved makes it easy to become familiar with SQLite and add new features. D. Richard Hipp, the author of SQLite, is very interested in seeing this project succeed.

What Needs to Be Done

Both developers and end users can contribute to make this portable database dream come true. The first thing to do is help embed SQLite in OO.o. A possible approach to this goal is discussed here. Other things you should know are the "Basic UNO" and "Database Access" sections of the Developer's Guide, as well as the relevant APIs of SQLite. The list to join is dev@dba.openoffice.org. The developers eagerly are awaiting your support on this particular sub-project.

The hardest part, of course, is to make a complete standard of all this. Once OO.o embeds SQLite, what still would be missing from making it possible to exchange databases and forms directly among the users of OO.o, KOffice and, later, everybody else? Above all, do the KOffice and OO.o teams have the will to converge fully on achieving this standard?

After hearing my arguments, OO.o developers agreed that SQLite indeed might be a better bet than HSQLDB, even if it will require additional time to implement. Sun already has a strong interest in standards, so I hope it will support this proposal of mine. As far as KOffice is concerned, all its core developers have the goal that "all KOffice applications either follow the OASIS format or help to define new OASIS definitions for formats that are currently not standardized". Looking good, no? Of course, SQLite is only the database engine part. The greatest work is to define and use in the same way all the other associated information--how reports and forms are represented and recorded, how the query is stored, and so on. Theoretically, it should be possible to converge on some XML user interface description--perhaps UIML, another OASIS standard.

Another issue that needs to be dealt with is the current difference in file format approaches. In Kexi, everything is added to the actual database in separate tables. In OO.o, a ZIP archive with administrative XML streams is used. As of current plans, OO.o 2.0 is supposed to embed the actual data into that archive. Also, OO.o forms and reports are real OO.o Writer documents generated by a wizard that contain a macro that, when the document is opened, fills it with the data. Consequently, either Kexi or OO.o should change the top-level file format itself (ZIP archive or SQLite DB) and then agree on a common formats for queries and forms. Initially, the choice seemed to me to be already made, even if not all the interested parties had realized it yet. If the OO.o format has become an open OASIS standard, and KOffice is adopting it as its own native format and Kexi is part of KOffice, then what's left? The standard forms will be OASIS text documents, as they already are in OO.o. But, this is my initial opinion. What matters is that one common format does comes out and that it is given the rank of official standard. To me, SQLite seems to be the best way to do it.

______________________

Articles about Digital Rights and more at http://stop.zona-m.net CV, talks and bio at http://mfioretti.com

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

I think it should be mentione

Antoine's picture

I think it should be mentioned (a long time after the release of this article) that koffice is now standardising on the same format as OO. I presume that this will mean they will naturally move to the same native database format. So the standardisation is happening. Maybe this article helped?
Cheers
Antoine

Re: The Lack of a Small Unified Database

Anonymous's picture

You forgot to mention that there's already a native OO.o 1.1.x
SQLite driver available (alpha-version, only tested on Linux).

More information here.

Re: The Lack of a Small Unified Database

Anonymous's picture

I submitted this article some time ago. As far as I remember (should check
my notes at home to be sure), that page had not been mentioned back then by any
of the developers I spoke with, but I might be wrong.
Thanks for pointing it out, of course it helps to reach the unified database
goal

Marco F.

unified database

eric drake's picture

In the DOS world I used to use a simple integrated package called Alpha Works. It had spreadsheet, database, word processor, communications, shell access all in one piece of software. The database format was dBase III+. When I moved to Windows, I began using Microsoft Works. The database service (or should I call it the "table" service) provided filtering, sorting, some formulas that were similar to what was available in the spreadsheet, table reporting and a form view. I have been using it for years and it has become my favorite application. The spreadsheet is more than adequate (most users aren't plotting space craft trajectories or performing complex matrix algebra). Excel has more than most users need and so does Word. Oddly enough, Works was free with the computer but I have yet to find anybody around me who uses it. They all went out and bought Excel and Word and the fact is they do far less on their systems than me. I am a sales rep handling multiple companies and multiple customers who buy some lines but not others. The database files aren't large 2 - 3000 records. The MSWorks program works totally in memory so sorting and filters are instantaneous. I can cut and paste records easily into the spreadsheet and into my Windows version of Open Office which I used to set up the order forms that my companies require (each requires a different order form). I use Open Office for this because the Works spreadsheet doesn't allow the embedding of graphics my orders often require graphic representations of what I need. I export the forms to pdf and email them to the various companies. So what is Linux missing ? (I'm writing this on my Linux system now) Something like Microsoft Works. A full integrated set of applications that isn't overloaded with features most people never use, allows easy cutting and pasting as well as database field embedding in form letters, has a small footprint, runs in memory and can export and import all the basic file formats (as Microsoft Works can). Then I would ditch Windows for good. Not that everything would be great with Linux. There's still the problem of driver support especially wireless USB and printers. My laptop's Linksys USB 1.0 802.11.b wireless network device is not recognised and my workhorse cheap Konica Minolta laser printer has no drivers for Linux. I know there is a ton of software available for Linux but the average user doesn't need a ton. A Swiss army knife with a couple of good blades, a scissors and a screwdriver covers alot of needs. Keep the corkscrew and the toothpick.

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState