Base questions

users
#1

All,

Was reading the FAQ at https://wiki.openoffice.org/wiki/FAQ_(Base)
and came upon some insight that worries me. Under the heading "So why
should I avoid 'embedded databases'?" it goes on to say the normal Base
file is not reliable, so use a split file, the points to Microsoft docs.

My questions are:
1. How robust are embedded Base files created by the GUI?
2. If they are not that reliable, what is the LO Base procedure to
create a split database?
3. Anything else I need to know?

I'm going ahead with using Base for a project for now. Once the data
has stabilized (fields, field type, number of tables), I'll move to
either Mysql or Mariadb.

Thanks,
Dave

#2

If the normal Base file is not reliable, why do we have it in the first place?

Either we (the community) must make it reliable, or we should abandon it and come with a reliable alternative.
Not reliable is in fact not very acceptable. The documentation should describe when to use, and when not to use base. Simply generalizing in the FACs is not the right way to go, I think.

Rob.

#3

Hi Dave,

All,

Was reading the FAQ at https://wiki.openoffice.org/wiki/FAQ_(Base)
and came upon some insight that worries me. Under the heading "So why
should I avoid 'embedded databases'?" it goes on to say the normal Base
file is not reliable, so use a split file, the points to Microsoft docs.

My questions are:
1. How robust are embedded Base files created by the GUI?

Not much, because when Base is running, the whole DB is in memory. Any
crash (from any origin) may corrupt the DB and make it completely
unreadable. A daily backup is necessary.

2. If they are not that reliable, what is the LO Base procedure to
create a split database?
3. Anything else I need to know?

Can you read French? I've written a document about just that. Title "How
to get rid of the Base HSQLDB embedded mode". My sources were various
wikis, forums and mailing lists messages (EN and FR) that I reviewed,
rephrased and assembled.

If anyone is interested in translating from FR, it's ok by me (there are
39 pages in all and there remains one or two draft paragraphs). The
current license is CC-BY-SA. Feel free to contact me in PM.

HTH,

#4

Base is a very useful and versatile tool which deserves much attention.
So, abandoning it would be a very bad idea, if you ask me. Dunno about
the current developments (wrt FireBird embedding) but I do like Base for
many uses:
-- HSQL embedded mode: demo-ing, modelling, personal DBs, front-end to
Calc spreadsheets or even text files
-- HSQL file mode: everything else (professional single-user or
multi-user DBs). In this situation, Base is a good front-end that could
become excellent, if the devs where to focus on that (I've got no
information here).

The main question is "how to switch from embedded mode to file mode?"
which I had a hard time figuring out. Fortunately, the web is you friend :wink:

#5

Hi :slight_smile:
All the database experts and many others of us keep on recommending using
an external back-end such as Postgresql, MySql/MariaDb or anything else.

The devs are working on removing the current warped and ancient version of
HsqlDb and replacing it with Firebird.

Using the proper version of HsqlDb straight from their website as an
external back-end apparently works really well in complete contrast to the
embedded version.
Regards from
Tom :slight_smile:

#6

Hi :slight_smile:
Sorry! I hadn't realised this was addressed to me only as a personal,
off-list message. I was wondering why no-one responded!

This mailing list is set-up so that you have to use "Reply to all" or
"Reply to List" or similar option. The familiar "Reply to" doesn't work as
it tyakes the message off-list. It's something we keep on having to
explain to pretty much every new person who starts posting here, or seems
like it!!

Luckily there is nothing personal, inflammatory, embarrassing or anything
so it was easy to see it could be forwarded back to the mailing list. The
question is a good one imo and it's one i still haven't got a good answer
to - despite it being an obvious question and probably handled quite a few
times already.

I think that if your current database IS working just fine then now is a
good time to create a back-up of it. By that i simply mean right-click on
the file and do "copy" and then in an empty space in amongst the files,
beside the existing one just do "paste". Any other copy&paste of the file
would do the same trick. There are all sorts of fancy back-up options but
at core a back-up is just a copy.

At work i tend to put a sub-folder in all of the main folders i use and
call it "OldVersions-BackUps" and use it/them to dump all such backups and
works-in-progress other than the most recent copy of a file. That way the
main folder is, hopefully, less cluttered&confusing. I do tend to rename
back-up files to put the date in reverse order at the end and maybe a v1 at
the end so i can quickly tell which are newer and which are too ancient.

As for how to migrate your existing tables to an external back-end i think
that you can do this inside Base. I'm not sure if you have to set-up an
external program first and a blank file for it and then in Base (maybe in
the tables area?) do something like "link to external source" and create a
blank table to copy your existing table into? Hmm, that sounds a bit wrong
somewhere. Can you right-click on a table and do "Save As .." to a
Postgresql or MySql format?? I'm really hoping there is something easy
like that!

Of course having moved the table you might need to go into Queries and edit
them to pick-up on the new table. Hopefully Forms and Reports are based on
Queries so that they just keep on reading the same Queries (or new Queries
once they have been renamed to the same as the old ones).

Hopefully the migration should be fairly smooth or help you do a few tweaks
to make the database more flexible ready for when you decide to change
between different back-end in the future.
Good luck!! Regards from
Tom :slight_smile:

#7

1. How robust are embedded Base files created by the GUI?

With an "embedded" database, any bug in the application (doesn't
even have to crash) can corrupt your data.

With a client-server database, it's essentially impossible for the
client to corrupt the data.

2. If they are not that reliable, what is the LO Base procedure to
create a split database?

"Split"?

3. Anything else I need to know?

Use a client-server RDBMS.

PostgreSQL is legendary for its reliability and the SDBC driver comes
with LO.

Personally I wouldn't trust a database (MySQL and its descendents) whose
original developers didn't even consider it necessary to enforce
foreign key constraints.

Sincerely,

Wolfgang

#8

All the database experts and many others of us keep on recommending
using an external back-end such as Postgresql, MySql/MariaDb or
anything else.

The devs are working on removing the current warped and ancient
version of HsqlDb and replacing it with Firebird.

Using the "embedded" version of Firebird won't solve the problem.

I never understood why anyone halfway sane in their minds would use an
"embedded" database anyway or why the developers of StarOffice/LO/OO
even considered it.

Sincerely,

Wolfgang

#9

[snip]

I never understood why anyone halfway sane in their minds would use an
"embedded" database anyway or why the developers of StarOffice/LO/OO
even considered it.

Convenience, with a dash of necessity.

Average end-user is challenged enough just installing an O/S and
productivity suite, and learning how to use them, much less a real
RDBMS. Plus: MS Office (Pro) has one, so LibréOffice and relatives
have to have one.

Yes: Such tools should never, ever be used for anything very
important. People will, anyway. Then complain when their db gets
corrupted. Best thing people can do is regularly back up known good
versions of their db. That's what I tell users of MS Access to do.

(I am curious why anybody'd design a database "tool" like that to run
entirely out of memory. Seems like asking for trouble.)

Regards,
Jim

#10

Hi :slight_smile:
MS Access does it that way and therefore everyone else 'must' too.

We can't sell Base by pointing out that it has huge advantages over
Access. (One of those being that it's so much easier to connect it to an
external back-end and is really kinda the default way of using Base). So
we can only 'sell' it by saying that it's not quite as good as Access = but
only at the stupidly dangerous things that are probably best to avoid.
Base is more solid and flexible but we can't say that because people wont
understand it.

Errr, Jim is right except that it's extremely rare to find anyone who is
able to install programs let alone an entire OS. His bar for the average
computer user's skill level is far higher than my experience. HooRay for
"apps" so that people have learned a bit about installing programs.
Actually even using Calc puts people quite a lot higher up the skill scale
than "the average user" as spreadsheets baffle most people.

I disagree about using databases though. Often it would hugely simplify
things that are horribly clunky and heavy at the moment. The problem is
that Access puts normal users waaay outside their comfort-zone, gives them
a very unfamiliar and somewhat scary interface and put them dangerously
close to tools that make huge changes (such as being able to delete the
tables). It's even 'difficult' to hide the design-tools so users can
easily accidentally change the layout of a Form in a really bad way. Sadly
they seem unable to figure out how to move things back again!

Anyone actually using a database can put themselves in the top 20% of
computer users imo, probably a lot higher but that depends on whether they
designed it or maintain it or how simple it is (simplifying it takes it a
step further).
Regards from
Tom :slight_smile:

#11

Hello,
I know that its not LO base specific but can,t find a way how to or example of something like this.
What i want using cars as example is to make a search for color,year,doors,model.kilometers driven,cabrio,engine,fuel and so many more options.
Who can help me on my way how to set somthing like this up ?

TIA John

#12

Hi :slight_smile:
Pretty much any database should be able to sort on any of the
columns/fields. Even spreadsheets allow it. Most even allow sorting on
multiple columns/fields at the same time.

Of course if you have a proper database you can use Sql statements or use
the Query gui to set-up specific searches and then modify them. Another
way might be to set-up a form with drop-downs for each field so that weeu's
(wide eyed end users) can select their own search parameters.

Errr, if you are doing this for a website then the form might be in html or
something like that for the front-end and then MySql or something for the
back-end. Errr, i specify MySql for that rather than giving alternatives
because most hosting companies still seem to have only MySql as an option
even though a LOT of people would prefer to have choices such as Postgresql
or MariaDb or lighter faster ones for smaller databases. This is also
where Base really scores because it can connect to a MySql/MariaDb,
Postgresql or whatever so that your local users can update the back-end.
So people visiting the website wouldn't be using Base and wouldn't have
access to the updating capability. In other words different people using
different tools to access the same data in different ways for different
purposes. The choice is largely yours!

Regards from
Tom :slight_smile:

#13

As I recall, at the time of the release of OpenOffice2, it had to be
like Access - but of course, as Sun was mainly running the show, that
meant that it had to be multi-OS, thus Java based and a fairly "simple",
"drop-in" piece of code with an appropriate licence. One would do well
to remember that at the time, the internal Sun Base development team
only comprised about 3 members of staff working full-time on the
project, thus resources were painfully limited. It turned out to be not
so simple after all, but that can be said about many things in software
development.

The issue of performance was raised even back then, and remains with us
today - embedded Java bridging (via UNO JNI) of an in-memory database -
loads of things to go potentially wrong at any given instance in the
lifecycle of the application, notwithstanding that upgrading to newer
versions of the db has always been fraught with complications, so a
maintenance nightmare for OOo/AOO/LO developers. It can be done though,
Fred Toussi (one of the lead developers on the hsqldb project) has, I
believe, provided patches for the integration of an update to the hsqldb
version code, but these have not been integrated due to the decision to
move to Firebird.

Database noobs wanted Access-like functionality and portability and
multi-OS operation - the fact that they don't really get that today with
LibreOffice Base is due more to lack of functional
implementation/integration witn the other parts of LibreOffice than to
the type of underlying db. Even MS Access has moved to a separated
db/frontend paradigm, as far as I understand.

Just to put things in perspective, there are, to my knowledge, currently
no full/part time paid-to-work developers within the LibreOffice project
that work on Base - everything done is voluntary, spare/free time
involvement, so it is hardly surprising that things with Base move a
little more slowly than modules such as Writer and Calc, in fact, it is
my undestanding that the main voluntary developer spends most of his
time in the project undoing the bugs/regressions caused by ongoing code
development elsewhere within the LibreOffice project.

Alex

#14

How does switching to Firebird solve the issue of an embedded, all in
memory database? I would be happy if LO offered an option (when
creating a db) to use embedded model or the split model.

Dave,

#15

Any embedded DB engine (HSQL or Firebird or any other) won't solve the
Base [R]DBMS embedding problems. Only external DB engines can be reliable.
Using an external DBMS is up to you. Base/LibO can be setup accordingly.

#16

That is my point. That is why Base needs to make setting up an external
db easy - like when the db is being created. Check one box for the
embedded model, another for the split/external model. Appropriate Help
infornation will be needed. While they are at it, how about an option
to do mariadb or mysql external db instead of just HSQL or Firebird?

Dave,

#17

BIG +1 (or rather +100)

I think this is a MUST!! It took me a couple of years to puzzle out what was wrong, (Embedded vs External), and then a few weeks to get it all to work - very little info available - all I could do was step-by-step with the Forum.

But once you get it all working the difference is amazing. One year down the line and not one crash or one piece of data lost!!!

IanW
Pretoria RSA

#18

Hi Dave,

How does switching to Firebird solve the issue of an embedded, all in
memory database? I would be happy if LO offered an option (when
creating a db) to use embedded model or the split model.

It doesn't, but Firebird wasn't chosen for that reason, it was chosen to
reduce the dependency on Java code within the LibreOffice project.

Alex

#19

Average end-user is challenged enough just installing an O/S and
productivity suite, and learning how to use them, much less a real
RDBMS.

PostgreSQL is pretty foolproof to install and use. I know what I am
talking about since I am the reference fool.

It may be something different if you need to maintain it for running a
huge database that gets a serious load of queries and transactions and
needs to be fault-tolerant, accessible from the "public" Internet etc.
But for "home" or "small business" use cases it's really simple.

Plus: MS Office (Pro) has one, so LibréOffice and relatives
have to have one.

Access is a dangerous heap of junk.

I know of Access users who get their database corrupted roughly every
four weeks on average.

Yes: Such tools should never, ever be used for anything very
important.

Base looks and feels like a perfectly credible "CRUD" and
reporting frontend for a database. It's just the choice of an embedded
database for storage that's a mistery for me.

At best, it's useless to include HSQL. At worst, it might discredit LO
as a whole.

Sincerely,

Wolfgang

#20

The database creation wizard (File > New > Database) already provides
for a user to setup a connection a mysql database, either via an ODBC or
JDBC driver or, when it is available, via a native C/C++ mysql connector
extension, under the "Connect to an existing database" option.

Alex