The Lack of a Small Unified Database

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

Many desktop users do not have the skills or, frankly, any real need to install and manage a multiuser database server. They still need, however, to use and exchange SQL databases. Unfortunately, many developers fail to see this situation as a problem and dismiss it, at least initially, with any combination of these responses:

  • It now is so easy to install PostgreSQL, MySQL or any other RDBMS server, desktop users should install one of them.

  • Program X also is available for Windows/Mac/whatever, so just install it.

  • If anything, we only need to improve the interface and/or the documentation to do the above.

Nothing technically is wrong with these arguments, but they simply don't apply to the niche sector of soho and corporate users. Nobody in this sector is whining because installing a server requires two or one mouse click instead of zero. But there is a huge difference between a perfect, completely documented install wizard for any SQL server and what those users currently are getting.

Everybody says how great file sharing is. Okay, so answer this one: how can a Linux/KOffice geek share his book or recipe database with his aunt, who is running Windows/OO.o? How can an employee send a product database from his MAC/OO.o desktop to a potential corporate customer running Solaris/KOffice? What if the receiver has no root password or permission to install extra software, the standard situation in most offices? In general, in the real world, the "just install and configure this" attitude doesn't make sense and doesn't help information to flow freely. This attitude can be just as impractical, if not impossible, as having to install a new font or print server simply to open a text file.

Currently, free software users are missing a single-file SQL standard format, which may be a tar or ZIP archive, that contains everything needed by a generic frontend to let people work: schemas, data, indexes, forms structures and so on. Such databases could be copied immediately, uploaded to a Web server or sent by e-mail, the same as any other file. Users would have the certainty that the receiver immediately could access all the data, queries and forms, even if they might look different. Above all, it would be great if such a file format became an OASIS standard, because it would make it much easier to accept in corporate or government scenarios.

In the text/spreadsheet/presentation space, the Right Thing already is happening. The two most popular free office software suites, OO.o and KOffice, are converging on the same default file format, which is an OASIS standard. This means being able to write, read and share such documents today between OO.o KOffice and tomorrow with any other OASIS-compliant application--transparently. This level of standardization also gives much more credibility and strength to Free Software.

Wouldn't it be really great and isn't it time to do the same thing for simple SQL databases? Without, of course, preventing anybody who wants a full blown RDBMS daemon from using it? Today, such databases are not covered or influenced by OASIS. I say that they should be. The rest of this article proposes a way to achieve this goal.

Status in OO.o

The first two applications that should converge on this database standard are OpenOffice.org (OO.o) and KOffice. OO.o has data sources, meaning it can connect to external RDBMS servers and can use single-file databases in dBase format, whose features simply are too limited. When I started to investigate this matter, I learned that OO.o developers already have begun working on improving support for a server-less database engine. Standardization, however, simply isn't among their objectives right now, though. What they anticipate today to include in OO.o 2.0 (alpha snapshot available here) is a database file format that is XML-based and that contains everything except the actual data (forms, reports, queries and administrative information).

When I asked, I was told that the most probable file format choice is HSQLDB, mainly because it supports more features than its competitors do. Personally, I am against this choice for four reasons. The first is performance (read more here and here), especially considering that OO.o doesn't need to remain as heavy as it is today. The second reason is HSQLDB requires Java, and I don't like the idea of depending on third-party elements, as it makes it more likely that these single-file DBs don't work in practice when moved from PC to PC. The third reason is many other application and languages in the free software arena have partially converged on something else (more on this in a moment), so I think OO.o should be a good community citizen and follow suit. Hence, my fourth reason: by not proposing a portable standard in OO.o, OO.o users would be in the position of saying to everybody else "yes, we are using this so called "free" software, but if you want to share small databases transparently, please force yourself to freely install OO.o, HSQLDB/Java or any combination of the above".

______________________

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.

Yes, we do need this

Martin Pilka's picture

Yes, the need for *.mdb alternative for Linux is high. Thanks for pointing me to sqlite.

If anybody knows how to access *.mdb from Linux app (both read and write access), let me know.

Thanks,
Martin

My 2 cents.

Anonymous's picture

I fully understand (I think) the author's main points.

"Access" is the single most problematic point when migrating from Office to OpenOffice.org.

SQLite, which I never tested, could help a lot in big business migrations.

I hope it works, even for those who can't see how important this is.

Anyway, Marco, thanks for this _very_ important article.

From the author: My 2 cents.

Anonymous's picture

Thanks!

Please spread the word if you agree that the issue is very important,
and above all vote and ask other users to vote for both issues mentioned in the article.

Ciao,

Marco F.

ooo db

Paul's picture

We are a small business (5 users) as as we all know MS products are expensive. Most users only need access to some accounting functions, and use of Excel.
I would like to migrate all our business functionality to Linux, but we have an in house accounting system in MS Access. If it were not for this sticking point, I would change now. We really need an SQl server because Access just is not designed for multiple users acessing same data at same time. It works but prohibitively slow to updates on same table even if different rows.
And we have so many problems with transaction processing I've given up trying and just hope that 99.9% of the time there are no conflicts.

Re: The Lack of a Small Unified Database

Anonymous's picture

Currently, free software users are missing a standard single-file SQL format...

I wouldn't say I've been missing it, Bob.

Re: The Lack of a Small Unified Database

Anonymous's picture

I'm struggling to understand exactly what the article is driving at.

Maybe the title of this article should really be "The Lack of a Small Unified Database Engine+GUI installer Which Can Somehow Magically Exchange Data with All Other Major SQL-based DBMSs (and forget about business logic)". I mean, such a thing is only remotely conceivable if you dumb down your usage of each DBMS to simple base tables without constraints, procedures, etc...

Even in the Windows world, there is no such thing as a "transparent" way to just trade databases around, on different platforms, with different database engines, and expect anything like reasonable results. You send someone an Access .MDB file: that's fine IF they happen to have Access installed, and if it is not an earlier version of Access. If they happen to have SQL Server, they still need to install Access, even if just to extract the data via ODBC. And *then* the problems really start: if there are any procedures written in the Access database, those will not automatically translate to SQL Server stored prcedures. Plus, Access is a database back-end and a front end. If you want the front end to run, you still need Access. And this is between two closely-integrated Microsoft products. Now expand this to include Oracle, Sybase, Borland/Paradox,not to mention all the other database engines out there.

Now, the introductory sentence is interesting: "Why a standard single-file SQL format is necessary and how SQLite can get us there." Are we talking about a data format or about an interchange format? Sure, I am all for a common data interchange format. But that is not the same thing as a piece of software. It seems that the rest of the article is talking about some sort of specific lowest-common-denominator database engine. I suppose SQLlite can do that, again as long as you forget about business logic in the database. So then, what's the real problem here? Make a simple GUI installer for SQLite, one that even allows for install in the user's personal bin/ directory, and the argument is over.

But this still belies the true nature of the database interactivity problem. The simple fact is that no two developers, much less any two DBMS vendors/developers agree on just what features a DBMS should support, and they do not completely agree on how those features should be *represented* in SQL, much less as an actual file format. Theoretically, SQL is a standard, thus the DDL+Data can be (theoretically) dumped, zipped, and sent to any other SQL engine. In practice no one supports all of the SQL standard (which grows bigger and more complex every year) and it looks like no one ever will.

Developers choose database engines based on the features they need for the project they are working on. This is the same in the close-source and open-source world. If we make everything have a "common" format, then the only way to support that is for all DBMS's to have exactly the same features. What's the point of having different DBMS's at all then? Performance alone? Not for me.

So fine, if SQLite can be a simple baseline database system, the statement begs the question. It seems like that is exactly what SQLite is doing. But as far as creating basic export format that will just automatically work for all database engines, simple logic prevents that.

I think you missed the point

Axel Hallez's picture

I think you missed the point here. It's not about introducing a database file format that all RDBMS's share (which would be ridiculous indeed). It's all about a self-containing file format for small (relational) databases that all users can access, no matter what operating system and/or office suite they are using.
It's for all those situations where a client/server database setup is major overkill. I don't want to setup MySQL (for instance) to maintain a small customer list in a one-man shop (even if I know how to do that), nor do I want to send a ten page installation manual along with my home-made-cocktails-recipe-repository to my not so computer savy friends.
MS Access makes all this very easy. I can create a complicated relational structure, I can even create nice form's an reports to access/update the data and everything is contained in one single file that I can send to other people to use.

I'm currently maintaining an application that uses MS Access files to store data. This gives me several advantages:
- My users don't have to mess with DBMS installation.
- My users can access the data through MS Access if they want to.
- The user opens the data as a document (i.e. the user opens a file instead of connecting to a database).
- I have the flexibility for structuring and querying a relational database offers.
- My users don't have to mess with import/export procedures to exchange data.

There is also a major disadvantage: allthough written in Java, my application is not cross-platform.

You say that developers choose a DBMS based on the feature set they need, well there's a combination of features that, as of today, cannot be satisfied:
- cross-platform
- single file database
- no messing around with server setup
- easily accessible from office software

It existed 30 years ago, it exists today !

Anonymous's picture

it's called "the file system" and unix has had the tools to query data stored in it for over 30 years, even in a sql like way :

man join
man sort
man grep
man awk

etc. etc. etc. etc. etc. etc.

NEVER LOSE SIGHT OF THIS

it's called rdb

Anonymous's picture

and it works very well. Commercial support is available.

Re: It existed 30 years ago, it exists today !

Anonymous's picture

Yeah, right. Do these come inside OO.o for Windows or Mac? So that you can just write shell scripts queries which will be just working everywhere, including all the different brands of *nix? Or are you saying that everybody should be forced to one OS, just to make this work?

Shell scripting and Unix text munging utilities are great, but this is a totally different problem

Would 'unity' to be cancidate?

Anonymous's picture

Would perhaps "unity" from
http://www.bell-labs.com/project/wwexptools/unity/
be a candidate to do what you want?

I have used it a lot and it is very easy to setup and work, for
very small tasks. A database can therefor exist of 1 or more
files. Each file has a seperate file that describes the table.
That's all there is.

Re: The Lack of a Small Unified Database

Anonymous's picture

Your points against HSQLDB are all bogus.

>>The first is performance (read more here and here), especially considering that OO.o doesn't need to remain as heavy as it is today.

You are referring to performance figures with OO 1.1 where no integration whatsoever has been made between OOo and HSQLDB. The current project will integrate OOo 2.00 and HSQLDB 1.7.3 and will perform as fast or better than with any other database engine.

>>The second reason is HSQLDB requires Java, and I don't like the idea of depending on third-party elements, as it makes it more likely that these single-file DBs don't work in practice when moved from PC to PC.

Requires Java or Kaffe. Kaffe is as third-party to Linux as languages such as Tcl or PhP. In addition, you can even compile HSQLDB with GCJ.

>>The third reason is many other application and languages in the free software arena have partially converged on something else (more on this in a moment), so I think OO.o should be a good community citizen and follow suit.

As many Java applications in free software arena have converged on HSQLDB.

>>Hence, my fourth reason: by not proposing a portable standard in OO.o, OO.o users would be in the position of saying to everybody else "yes, we are using this so called "free" software, but if you want to share small databases transparently, please force yourself to freely install OO.o, HSQLDB/Java or any combination of the above".

HSQLDB will be distributed with OOo as its license is now the Modified BSD and fully compatible with GLP and other free software licenses. Java will not be needed as it can run with Kaffe.

----

As to your various other points, with great respect to Hipp and all fellow open source developers, SQLite is lightweight exactly because it does not support the more advanced SQL features such as those supported by HSQLDB (or PostgreSQL, or Firebird). It probably will at some point in the future but by then it will have to stand to comparison against the future versions of the other databases.

If you want to be constructive, please help the Kaffe project to bring the great wealth of Java Open Source software to a wider Linux audience.

Fred Toussi
Maintainer, HSQLDB Project

How?

Anonymous's picture

>> you can even compile HSQLDB with GCJ.

How do you do that? I have been trying to to this for some time. I am sure it is possible, but it looks that if you want to do this you must have your own build.xml.

Ant, I wonder what reading

Anonymous's picture

Ant, I wonder what reading documentation is good for.

From the author: on HSQLDB problems being bogus

Anonymous's picture

>Your points against HSQLDB are all bogus.
Let's check
>>The first is performance (read more here and here), especially considering that OO.o doesn't need to remain as heavy as it is today...You are referring to performance figures with OO 1.1 where no integration > whatsoever has been made between OOo and HSQLDB.
I delivered the article some months ago. When I asked back then, I was pointed to the issues mentioned in the article. I'm happy to know that this
is not the case anymore, but to the best of my knowledge when I delivered they weren't bogus then.

>>The second reason is HSQLDB requires Java, and I don't like the idea of depending on third-party elements, as it makes it more likely that these single-file DBs don't work in practice when moved from PC to PC.

>Requires Java or Kaffe. Kaffe is as third-party to Linux as languages such as Tcl or PhP.

Even Kaffe was not mentioned by the OO.o developers I spoke with, only Java. In any case, I have nothing against Java. I said "I don't like the idea of third party elements". In this sense, there is no difference between Java, Kaffe, Tcl, Php, etc...

>>The third reason is many other application and languages in the free software arena have partially converged on something else (more on this in a moment),...
>As many Java applications in free software arena have converged on >HSQLDB.

The article is centered on office suites. Bridging KOffice and OO.o
first seemed (and still seems) easier to me than with HSQLDB.

>HSQLDB will be distributed with OOo...

OK. But it will still need a third party thing, no matter which. And, will it easily/immediately usable to exchange databases with KOffice, without them installing anything else?

Generally speaking, please keep in mind a couple of things:
1) I have nothing against Java and/or HSQLDB in principle.
I care about the end functionality (meant, see the article, as easy
moving data AND forms AND queries in one single bundle)

2) As said in the article, the actual data format is just part of the problem.
It is forms that must be standardized. Since both KOffice and OO.o
can (kinda) do SQLite today, and OO.o already has forms in the same
format to which KOffice will converge, it still seems to me the path of
least resistance to solve the initial problem.

>As to your various other points, with great respect to Hipp and all fellow >open source developers, SQLite is lightweight exactly because it does >not support the more advanced SQL features...

I knew that. Fine, we are talking of simple applications. Please remember, again, that I am not here to destroy HSQLDB. Long live and prosper. I haven't even said that OO.o or KOffice should support ONLY SQLite, it would be ridiculous. I am saying that for *this* particular need SQLite looks like the easiest solution to achieve.

Thanks for your attention,

Marco Fioretti

Re: From the author: on HSQLDB problems being bogus

Anonymous's picture

1. Those performance tests were published after it was made public that a new integration project was underway. Did anyone from dba.OOo or HSQLDB projects tell you that performance would be similar to those tests after the integration? Did you even ask?

2. The difference between Java and Kaffe in this context is that some Linux distributions do not include Java because it is proprietary, and Java will not be distributed with OOo for the same reason. However, Kaffe is GPL and can be distributed as part of an OOo distribution. With both Kaffe and HSQLDB included in the distribution and the user having the choice between using (bundled) Kaffe and Java, there won't be any problem.

I understand your argument that KOffice and OOo could converge in the file format, but then there are major differences of functionality between SQLite and HSQLDB. The issues cannot be simplified by saying that we are "talking of simple applications". OpenOffice will be used by public and private institutions that use "not so simple applications" which need the kind of SQL functionality currently provided by the likes of Access and can be fulfilled by HSQLDB (or Firebird, or PostgreSQL). See:

http://article.gmane.org/gmane.comp.java.hsqldb.user/1736

As HSQLDB can be compiled with gcj and linked with any C/C++ app, it will be just a matter of time when it is distributed with KOffice too.

Finally, I never thought that you had anything against HSQLDB and accept that some developmets (such as the updated HSQLDB License) took place after you wrote the article. I have just written to counter your arguments and demonstrate that the choice made by OOo developers is not a bad one.

Fred Toussi

Re: From the author: on HSQLDB problems being bogus

Anonymous's picture

..Did anyone from dba.OOo or HSQLDB projects tell you that performance would be similar to those tests after the integration? Did you even ask?
To recall every question I actually asked, I should check my email archives. Said this, what happened is more or less this:
I saw the problem in the office suites
I asked to _those_ developers (oo.o/KOffice) how it could be solved
At that time they answered that HSQLDB required Java and was more featured but slower (from oo.o) and that Kexi (=KOffice) already supported SQLite.
Since the minimum common SQL denominator seemed to be SQLite, I just moved on to investigate the rest of the problem (sharing forms and queries all in one (zip?) file). Since I was told that this is the biggest problem, I thought "OK, let's see if I can get them to agree and provide pointers on this bigger issue"
...With both Kaffe and HSQLDB included in the distribution and the user having the choice between using (bundled) Kaffe and Java, there won't be any problem.

Correct as far as KOffice and OO.o are concerned
...there are major differences of functionality between SQLite and HSQLDB. The issues cannot be simplified by saying that we are "talking of simple applications".
We have to start somewhere, don't we? The actual problem is to agree on the fact that data should be independent by the SW application, and as easily movable as possible. And (speaking for us end users unable to code) to have something asap that can be moved around. I still believe that SQLite is the best compromise between speed, ubiquity, and "having it soon everywhere". However, if HSQLDB gets there first, and with more features, I won't be upset to have been proven wrong, frankly. I'll just sincerely thank all you coders and happily use it.

Finally, I never thought that you had anything against HSQLDB and accept that some developmets (such as the updated HSQLDB License) took place after you wrote the article. I have just written to counter your arguments and demonstrate that the choice made by OOo developers is not a bad one.

I don't consider it bad, I consider it, as far as I know today, not the best
one for the problem space I addressed. Again, no problem to be proven wrong, I'm more interested in the end result than in the particular tool. And I'd be equally happy to see OO.o, KOffice whatever, doing any database around.

Friendly,

Marco Fioretti

Re: From the author: on HSQLDB problems being bogus

Anonymous's picture

Thanks for the positive comments.

Regardless of HSQLDB, I can see we are coming from different perspectives. You are interested in a common solution that has a basic level of functionality, while I'm interested in a solution for OOo that can compete head-on with the alternative, commercial office suites that are used in public institutions and are (indirectly) costing us all a lot of money.

I'm confident the joint effort between the dba.OOo and HSQLDB developers will result in a capable application that will be well recieved by the whole Open Source community.

Regards

Fred Toussi

Re: From the author: on HSQLDB problems being bogus

Anonymous's picture

I just listened at Italian Linux World Expo to several sysadmins of public administrations who migrated their users to Linux. One common theme was the fact that the commercial office suites are overpowered even for public institutions. Especially on the database side (their words), because there you either need a front end to an RDBMS server, or something much simpler.

Said this, our approaches _are_ different (developer versus end user), but the perspective is the same.

My point is just that to "compete head-on with the alternative, commercial office suites that are used in public institutions and are (indirectly) costing us all a lot of money" all free software products, not just OO.o, must become completely interoperable, going so quickly to common, ubiquitous formats that proprietary products cannot claim anymore that "they are the de-facto standard". Or that somebody is just trying to substitute their monopoly with another (OO.o in this case).

Regards,
Marco

Re: From the author: on HSQLDB problems being bogus

Anonymous's picture

"the fact that the commercial office suites are overpowered even for public institutions", is not a fact, it is the opinion of some sysadmins. I have seen how it works in reality and that is: if even less than 10 percent of users in institutions need a certain app (e.g. business presentation) or capability (a sophisticated desktop database), economies of scale dictate that you provide the functionality in all installations.

To compete with commercial suites, free software products need not be interoperable with each other, they must be equal to or better than the commercial suites. I would be very happy for OO.o to become the standard office application and "monopolise" the market. It is open source and can be modified and distributed without restrictions. Other open source or commercial software can easily become interoperable with OO.o as there is no secret hidden in closed source binaries.

> "the fact that the commerci

Anonymous's picture

> "the fact that the commercial office suites are overpowered even for public institutions", is not a fact, it is the opinion of some sysadmins.

We already have powerful database engines. What we need is an end-user database that doesn't require a degree in computer engineering to operate.

> if even less than 10 percent of users in institutions need a certain app (e.g. business presentation) or capability (a sophisticated desktop database), economies of scale dictate that you provide the functionality in all installations.

Go use MySQL/PostgreSQL with PHP and Apache.

> To compete with commercial suites, free software products need not be interoperable with each other, they must be equal to or better than the commercial suites.

You obviously have not worked in a commercial environment. Database compatibility would be a critical key to getting end-users and corporate environments to switch over from a competing product.

Re: The Lack of a Small Unified Database

Anonymous's picture

OASIS has already published an own, modified OO.o format, and SUN thinks about bringing the original OO.o format to ISO. Both is, logically, in XML which does the job well, if not better, than a binary file made by an SQL database. The only missing piece would be processing the file like being a database.

Re: The Lack of a Small Unified Database

Anonymous's picture

My own understanding is that:

1) this would lose portability with respect to anything else, outside OO.o and KOffice, which can already use SQLite

2) It could be a sever performance hit, especially on more limited systems.

But I could be wrong on both.

Marco Fioretti

Re: The Lack of a Small Unified Database

Anonymous's picture

I have to say, having tried HSQLDB, that I think it's great, and I would strongly recommend others to at least give it a try.

For the record, my criteria for using HSQLDB were that I was looking for a lightweight and free (as in free beer) platform independent database for a web application.

I haven't had any problems with performance, and to argue against HSQLDB because it uses Java seems a little bit silly (how many other truly cross platform languages do you know?).

Re: The Lack of a Small Unified Database

ClayDowling's picture

Java is not especially cross platform. It's a nuisance to install the virtual machine on many platforms. I've had better luck using C++ for cross platform development, because I didn't have the problematic step of first installing the java virtual machine. If you're already a java developer this issue doesn't come up because you've already done it.

For OpenBSD or FreeBSD users Java is a major pain, and requiring java is an automatic killer for me. To get OpenOffice or KOffice on the desktop of the typical corporate user, you absolutely can't make them jump through this extra painful step. If one of a project's dependencies makes programmers run away, you can bet that corporate desktop users and over-burdened system administrators are going to refuse. That kills your project dead in the water.

I haven't used HSQLDB, but if its merits are so great, possibly it could be ported to C++ so that an executable can be delivered to the end user. That would make it far more acceptable to most people.

Re: The Lack of a Small Unified Database

Anonymous's picture

C and C++ are cross-platform and do not require the user to install a VM. That is good reason for SQLite over HSQLDB.

Re: The Lack of a Small Unified Database

Anonymous's picture

I agree. But I would go further...

I have never understood why we don't agree on a single database format, and then let all the applications that store data - email readers, address books, calendars, etc. - store the data in that format. Imagine it - it would be interoperability heaven!

And I can't see how it would be difficult to do, all the pieces of the jigsaw are already there. We would leapfrog MS applications in terms of interoperability, and make the linux desktop much more compelling.

Can anyone tell me why this isn't happening?

Re: The Lack of a Small Unified Database

Anonymous's picture

Well put. I asked once on the Thunderbird list why they don't consider using sqlite to store the data. I would love to make backups of huge email folders and carry it with me. Something like a .pst file in Outlook. Nobody replied so I guess nobody else need it.

Re: The Lack of a Small Unified Database

Anonymous's picture

Looks to me like a very worthwhile effort. I would be glad to see this gain some momentum.

-- Jeff Smith (an mdbtools developer)

Re: The Lack of a Small Unified Database

Anonymous's picture

Thanks!

Please spread the word then, inviting users to vote the Koffice/OO.o requests,
and developers to contribute.

Ciao,

Marco Fioretti

Firebird a better choice?

Anonymous's picture

Firbird also uses a single file format, it can be embedded. It is also a lot more SQL compliant and less limited than SQL lite. It runs on multiple platforms such as windows, Linux, MacOS X and other BSDs.

Re: Firebird a better choice?

Anonymous's picture

Firebird. last I looked, cannot be embedded on all its platforms.

Re: Firebird a better choice?

Anonymous's picture

True... I think only on Windows at this time.

Re: Firebird a better choice?

Anonymous's picture

Nope, it is single files on Linux as well.

Re: Firebird a better choice?

Anonymous's picture

Is Firebird a server-less database? That is, can I move database files from PC
to PC and use them through the local OO.o, KOffice or whatever, WITHOUT installing anything else? Just like I do from Windows to Windows PC with Access .mdb files?

If not, as good as it may be, Firebird is totally useless in this context, isn't it?

Just curious

Re: Firebird a better choice?

Anonymous's picture

Yes, you can. You need to install firbird. But that is no more different than you have to install MS-Access to handle .mbd files.

Re: Firebird a better choice?

Anonymous's picture

Good point... I think you would need to install more than just the database file.

From the author: Firebird a better choice?

Anonymous's picture

then we are back at square one, aren't we?
If this is the case, for this kind of problems we do need either SQLite (which is already usable in KOffice, OpenOffice and lots of other programs, not to mention php, perl, etc..) or something equally "single-file", without any dependency

Ciao,
Marco F.

Why not SQL scripts

tonygreene's picture

SQL scripts (the format dumped by PostgreSQL, MySQL, and others) contain the data, but not the interface elements and scripting, that allow other databases to work with the data. The only problem with them is that some SQL features are not supported in some software and some features are implemented slightly differently.

What would be good is if database developers enabled export and import modes that conform to SQL92, for all the feature supported by the database.

That would leave interface and logic. I don't see a good way to address those issues across software packages. Perhaps OOo forms could be a solution.

--Tony

Why not SQL scripts? Because users also need forms with queries

Anonymous's picture

Tony Greene said: That would leave interface and logic. I don't see a good way to address those issues across software packages. Perhaps OOo forms could be a solution.

SQL scripts have exactly this limit: they are not a valid solution for small businesses/non technical users which:

  • might simply not have a running RDBMS server, or permission to install it.
  • need to immediately share the "interface and logic", ie forms and queries, as least as much as the actual data

This is why I suggest in the article the same solution you mention: to make OO.o forms usable also in KOffice, and, possibly, other programs too:
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.

Thank for your time,
Marco Fioretti

Re: The Lack of a Small Unified Database

Anonymous's picture

An interesting article that cuts right into a major small business weakness in the open source desktop.

The need for an easy to use database tool that can be both stand-alone and extended to connect to a database server (while preserving the original front-end) is a significant problem for the open source desktop. I spend my time supporting users in this space, and having a standard here that could be shared with Windows desktops (Access driver?) would be as important as to desktops as an open source replacement for the Exchange server has been to the open source server segment.

Re: The Lack of a Small Unified Database

Anonymous's picture

Moving data in SQL format would lock you in with ditto databases. There are much more powerful ways to store and retrieve data than SQL. Your app may even be better off with BerkeleyDB than an SQL engine, depending on what you are doing. No, data transport needs to be as independent as possible.

Re: The Lack of a Small Unified Database

Anonymous's picture

You are totally missing the point.

Re: The Lack of a Small Unified Database

Anonymous's picture

"You are totally missing the point. "

What do you mean? Which point?

Re: The Lack of a Small Unified Database

Anonymous's picture

Sorry, I misplaced my comment

Re: The Lack of a Small Unified Database

Anonymous's picture

SQL is a query language and not something that can be serialized. Almost, anyway. The closest you get is the backup format that mysql and others have, but these are not software independent. Maybe you are really asking for an XML dialect?

Re: The Lack of a Small Unified Database

Anonymous's picture

There's one other issue you failed to mention: that SQLite currently does not support data types -- everything is a string. While this makes SQLite very popular among PHP and Perl developers, it causes a serious "compatibility gap" between SQLite and other SQL databases. Is this being addressed in the SQLite development plan?

A second issue to address would be to make sure that all SQL92-compliant features are preserved in the schema whether or not they are supported in the current target database. In addition to constraints and FKs, SQLite also lacks views, schema, domains, and many other SQL features -- with good reason -- but it would be problematic to see these features "stripped out" of database transport files which are coming from PostgreSQL or Firebird.

Or perhaps I'm not understanding your goal here?

Josh Berkus
PostgreSQL Project
OpenOffice.org DBA Project

Re: The Lack of a Small Unified Database

Anonymous's picture

A few technical points: SQLite does support datatypes.
See
http://www.sqlite.org/datatype3.html
.
SQLite is very permissive in its type system in order to maximize
compatibility with other SQL database engines.
SQLite does support views and triggers. Finally,
SQLite preserves its schema exactly as entered without
"stripping out" any information. Those features of the
SQL language (such as foreign keys and CHECK constraints) that
SQLite choses not to enforce are retained in the schema for
compatibility with other database engines.

D. Richard Hipp
Author of SQLite

Re: The Lack of a Small Unified Database

Anonymous's picture

I have asked on the SQLite list if some developer can answer to your
comment.

As far as I (the author, and an end user) am concerned, I do know that
PostgreSQL and other RDBMS products have much more features than
SQLite.
I am not saying that SQLite can replace those products. I am saying that
SQLite is needed, and more than enough, but not available yet, for the cases I mention at the beginning.
Users without time, skills or simply the possibility to install this or that,
no matter how simple is or looks to experts. If you (generic OO.o user)
want to share a PostgreSQL database with me (other generic OO.o user)
I have to already have, or install, PostgreSQL. This is far from being
always granted. Or from always making sense, for that matter. I cannot ask a customer (who might not have root password!) to install a server only to take a look to my special offer catalog.
So, we might say that my goal is not to replace PostgreSQL or similar
products with SQLite, or to make people believe this is possible. My goal
is to have a decent, enough featured, easily portable SQL solution (=not flat DB files) whenever the heavyweight stuff is not possible or necessary.

Thanks for your attention,

Marco Fioretti

Please also vote for support in KOffice...

Anonymous's picture

even if you prefer OO.o, because it is important to exchange
databases as easily as possible. The feature request to vote for is http://bugs.kde.org/show_bug.cgi?id=90392
Of course, the same applies if you use KOffice, that is vote for support in OO.o
Thanks,
Marco Fioretti

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