Why choose a different backend DB?

Discuss the database features
Post Reply
User avatar
memilanuk
Posts: 22
Joined: Thu Nov 13, 2008 1:03 am

Why choose a different backend DB?

Post by memilanuk »

Hello all,

I've been going through the forums off and on over the last while, and fairly frequently see people advising to use an 'adult' or 'mature' or 'real' database backend such MySQL, SQLite, PostgreSQL, etc. for serious projects as opposed to the included HSQLDB. Most of what I've read hints at it being incomplete and/or unreliable. I guess what I'm asking is... how bad is it, really? Is it suitable for a single-user environment, where concurrent access and table access permissions are not necessary? Where does it go from being an acceptable choice to a bad choice?

Next... I noticed some people mentioned SQLite. To my limited understanding, isn't it also a light-weight, file based database as well? What would it have going for it that hsqldb does not?

Kind of an open-ended question, but hopefully not a taboo one.

TIA,

Monte
OOo 3.2.X on Ms Windows Vista & XP, Mac OS X Snow Leopard
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Why choose a different backend DB?

Post by r4zoli »

OOo is office application, Base a part of it and was designed for single user with single file with HSQLDB engine, without any network capability. You can also use connection to any file based data source (which has a driver for it.).
If you want to use in networked environment you needs real database with user rights, Base can connect to it (driver same question as above), can handle user and password, and use Base UI features Form, Report, and Queries (with UI or in direct mode without UI).

Selection between SQLite, and HSQLDB was discussed in time when OOo 2.0 was designed, and selected because HSQLDB is java based, and we hurt some way with this decision from that time. (Not so much developers with C++ knowledge which is needed for SQLite programming)

Now as OOo 3.1 nearly finished OOo Base project announced a features pool, from which any body can vote for features what needs more, and it will direct Base further development. All this discussed on Base mailing lists, not here.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Why choose a different backend DB?

Post by Villeroy »

A database is a storgae system having more in common with a file system on your drive X:\ rather than a document on that drive.
Like file systems serve small portions at the same time to program, database servers use to serve small portions of their databases to requesting programs.
Like a file system, a database may serve data to many different software components able to communicate through a driver.
Like a file system, a database may contain Gigabytes of data.
Like a file system, you may have full administrator privileges to change anything, read-only access, write-access to some data or no access at all, depending how you logged-in on the system.
Both, databases and file systems, are developed with extremely high safety standards because they can store more than some hours of work of a single user.

Code: Select all

|--- Server machine---------||-----one or more client machines with logged-in users -----|
[Raw Data] <-->  [Server] <-||-> [Driver] <--> [Frontend with tools to actually work with the stuff] 
[  Files  ] <--> [MySQL]  <-||-> [ODBC] <--> [Base queries, forms, reports] --> other office documents
The server machine and client machine may be identical. There may be single-user access only. Usually, the driver is a small piece of software from the same vendor as the server. It "knows how to communicate" with the server.
Server programs usually juggle more than one connection with simultaniously logged-in users.

Embedded HSQL wraps the raw data together with configuration and Writer documents (forms, reports) into one zip-archive (just unzip your *.odb to see what I mean).
When you "open the odb-document", the whole raw data have to be unzipped into a temporary directory before the HSQL server connects to the unzipped database, logs you in as the only, allmighty user "SA" and connects the Base backend with the installed database.
When you "close the odb-document" the whole thing has to be "uninstalled" again. The whole database is wrapped again into the zip-archive.
This is like continuously installing/uninstalling an a software package. (Un-)compressing a whole database moves every single byte of content through your hardware.

- It becomes slow. A properly set up databases should read and write portions of data within seconds out of Gigabytes.
- The thing becomes unsafe. Many reports about data-loss confirm this. Data loss is inacceptable for any database unless it is due to hardware failure.
- The thing becomes limited to a single, allmighty user on the same machine although you have a more powerful machine under the hood.
- The thing becomes proprietary. You can not connect your web-shop, your accounting software, your statistic package nor MS Access to this database although HSQL supports such connections through JDBC drivers.

On the backend side ...
... the embedded forms and reports are Writer documents connected only to the database they belong to. You can not embed Calc documents. Outside the Base container you can create input forms as well as output in Writer and Calc. You can even attach forms to presentation slides.
Outside the Base container you can create forms and subforms connected to different sources (show persons on a spreadsheet list having the same name as in the MySQL main form).

Back in the days of version 1, you organized the connection, the queries, the forms and the reports as separate items and you could collect the items under one datasource name in the beamer. They could have saved so much developers time if they would have developed database add-ons to bundle portable databases by means of installable packages rather than "documents" that get installed every time you "open" the document.

By the way: MS Acces does the same thing with it's mdb-documents. The important difference is: The huge mdb file sits on the disk and can be served immediately without being installed.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
memilanuk
Posts: 22
Joined: Thu Nov 13, 2008 1:03 am

Re: Why choose a different backend DB?

Post by memilanuk »

villeroy, r4zoli,

Thanks for the detailed responses. Lots of good information, as usual!

The project I have in mind will be, for the foreseeable future, a single-user, single-PC solution. So that part doesn't really impact what I want to do (yet). I'm hoping to keep things fairly simple - we have been doing them with a very crude setup in Works, and had been looking at replacing them with a combination of Excel + Word, so I hope we won't be running into many 'walls' with ooBase.

I have to say, even as the rank beginner that I am... the more I read about HSQLDB and particularly the implementation in ooBase, the less I'm impressed with either. I know, I know... don't complain too much unless you're ready to roll up your sleeves and pitch in ;)

Hopefully things will continue to progress forwards... both on my local scale, and on the native MySQL implementation in ooBase that I've been reading about. Doesn't look like it'll make 3.1, but maybe 3.2? One can always hope...

Monte
OOo 3.2.X on Ms Windows Vista & XP, Mac OS X Snow Leopard
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Why choose a different backend DB?

Post by Villeroy »

memilanuk wrote:villeroy, r4zoli,

Thanks for the detailed responses. Lots of good information, as usual!

The project I have in mind will be, for the foreseeable future, a single-user, single-PC solution. So that part doesn't really impact what I want to do (yet). I'm hoping to keep things fairly simple - we have been doing them with a very crude setup in Works, and had been looking at replacing them with a combination of Excel + Word, so I hope we won't be running into many 'walls' with ooBase.

I have to say, even as the rank beginner that I am... the more I read about HSQLDB and particularly the implementation in ooBase, the less I'm impressed with either. I know, I know... don't complain too much unless you're ready to roll up your sleeves and pitch in ;)
HSQLDB is just a normal database engine. The original intent was a slim database completely controllable by Java programmers, so you can easily write your own customized database solution. It's slim, it's fast and simply works as specified when you install it independently from Base.
Hopefully things will continue to progress forwards... both on my local scale, and on the native MySQL implementation in ooBase that I've been reading about. Doesn't look like it'll make 3.1, but maybe 3.2? One can always hope...

Monte
What is this "native" MySQL in Base?. You save a few clicks when configuring the connection and then you can use the Base's crappy table designer and relation designer. Hey, anything is better than that. The world uses MySQL since a decade at least. Design your database with the graphical tool shipped with MySQL, set up a connection through an appropriate driver, connect the database with Base. Just do not use the table designer and the relations designer. It does not offer all the features of MySQL anyway (and I do not believe that Base will fully support MySQL at any time in the future).
Base imports tabular data into office documents. The additional extra-database is a nice try to make certain spreadsheets obsolete, keep track of your private DVD collection and learn Basics about databases in general. But I would not commit any business data to zipped HSQL.
The greedy crowd cried for something like Access for no money. This is what a small group of developers nailed down within a few months based on a 3rd party database engine and the drivers that used to be in version 1 already. It's all about time and money.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
memilanuk
Posts: 22
Joined: Thu Nov 13, 2008 1:03 am

Re: Why choose a different backend DB?

Post by memilanuk »

You save a few clicks when configuring the connection and then you can use the Base's crappy table designer and relation designer. Hey, anything is better than that. The world uses MySQL since a decade at least. Design your database with the graphical tool shipped with MySQL, set up a connection through an appropriate driver, connect the database with Base. Just do not use the table designer and the relations designer. It does not offer all the features of MySQL anyway (and I do not believe that Base will fully support MySQL at any time in the future).
Using MySQL from the start would have its benefits... the book I have for learning SQL commands is based off the MySQL implementation (something I already ran into putzing around with SQLite); it would be a big help to not have to stop and figure out the translation (which commands work as printed, and which don't). Sounds like there would be any number of other benefits also.

The big negative, at least from where I'm sitting, is that I would have to install, set up, and maintain a MySQL server installation on a PC that I don't have any regular access to (i.e. have to drive over to someone else's house) and trust the local user to *not* break it in some fashion.
OOo 3.2.X on Ms Windows Vista & XP, Mac OS X Snow Leopard
Post Reply