LibreOffice Base: Question about backend database

[AlgonquinPeak]

Been a few weeks since my last posts. Am out-of-state taking care of business.

While on my trip, I have decided to try LibreOffice - since it is more modern with apparently better support.

I have been watching videos from TheFrugalComputerGuy.

So far, LO Base looks almost identical to MS Access, which I suppose is a good thing based on my needs right now.

Questions:

Q1.) According to this gentleman, LO Base is not aptly named in that it is just a front-end application designed to interact with a database on the back-end.

Is this true?


Q2.) Is it true that if I choose the Firebird database that I won't need the JRE running on my Mac?


Q3.) How does HSQLDB compare to Firebird?


Q4.) Whether I choose HSQLDB or Firebird, where does the actual database, and database files, exist? (When I used MS Access long ago, your entire database was in one or maybe two files. By contrast, I believe large databases like Oracle have hundreds or thousands of files which make up the database.)

Okay, that's a good start I guess...

[AOOisdead]

1) Well, all front end tools connect to the underlying database software; Kexi, HeidiSQL, Base, etc. all connect to and underlying database.

2) Yes. But you might have to still disable JRE.I'm not sure how to do that.

3) HSQLDB is less efficient than Firebird. There are also differences in their SQL "dialect", and Firebird has some commands that HSQLDB doesn't have.

4) Firebird or HSQLDB will have one key table and one database table as far as I know. I'm pretty sure you tell Base where you want to save it.

You might want to check out Kexi though. It's FOSS and more modern than Base.

[AlgonquinPeak]

What about using MySQL?

Is there any benefit to using HSQLDB or Firebird?

On my Mac, I run MAMP. If I chose to use MySQL for my database when using Base, where would the actual database objects (e.g. tables, queries, views, etc) get stored?

I assume in the same place that they get stored with MAMP?

Would there be a way to store the MySQL files elsewhere? And is that a function of MAMP/MySQL or Base?

[Villeroy]

Use whatever database engine fits your needs. If you don't know what fits your needs, we don't neither. For any stack like LAMP, MAMP or similar, you have mature tool sets. Base has nothing to do with that. Base is just a bridge between some database and an office suite. The one and only type of database you can create with Base is HSQL version 1.8 embedded in the Base file [*]. Any other product that may be connected to a Base document may be edited on data level but not on the design level. This depends on the database driver (the actual software which handles the data) and Base's capability to handle the capabilities of the driver.

If you have some database finished, up and running and you want to access the contained data with your favourite office suite, then you may connect a Base document to that database and work with the contained data (not with the db itself) in the context of this office suite.
If you have some database finished, up and running and you want to access the contained data with a browser on some network, then you certainly need a completely different set of tools. LibreOffice Base has nothing to offer in this context.
If you want to learn something about relational databases in general you may start with a Base document and embedded HSQL. If that database turns out to be productive by any means, you can convert it easily to a stand-alone HSQL database by extracting the embedded database out of the containing Base document. In a third step you may even set up a HSQL server to serve many instances of the office suite or any other type of frontend, including web based frontends. In many cases Base can help transfering data from one type of database to some other type of database.

[*] leaving aside the option to create an embedded Firebird database with LibreOffice 6. In my honest opinion this leads to nowhere until somebody shows me a working, non-trivial example database with one-to-one, one-to-many, many-to-many relations and all data types fully supported with queries, forms and reports.
P.S.: Right now on topic: viewtopic.php?f=13&t=98378 (function DATEDIFF requires direct SQL mode with embedded Firebird)

[AOOisdead]

What villeroy said; MAMP is a full stack, you don't need base for that, you probably want HeidiSQL to play with your data.

I'm not 100% sure what your needs are; you're looking to manipulate individual datapoints in your database, and want to use Base/something like Access to do so? Or are you building something from scratch?

To be candid, Base is the bastard child of Libreoffice, they haven't updated that feature of the office suite in years. AOO they completely ignore it. There was discussion of spinning it off, but some people still use it so the drag it along. Kexi http://www.kexi-project.org/ is the best FOSS alternative to MS Access. And there will be more support for what you want on their forum: https://forum.kde.org/viewforum.php?f=219

You might want to look at Navicat too, but it costs money.

[AlgonquinPeak]

Use whatever database engine fits your needs. If you don't know what fits your needs, we don't neither. For any stack like LAMP, MAMP or similar, you have mature tool sets. Base has nothing to do with that. Base is just a bridge between some database and an office suite. The one and only type of database you can create with Base is HSQL version 1.8 embedded in the Base file [*]. Any other product that may be connected to a Base document may be edited on data level but not on the design level. This depends on the database driver (the actual software which handles the data) and Base's capability to handle the capabilities of the driver.

Are you saying that if I use MySQL as the backend, then I will not be able to edit the MySQL data via Base's Design tool?

I am tying to build a database where I can enter in all of my expenses from receipts. So I would have a textbook ORDER -||---|<- ORDER_DETAILS ->0-----||- PRODUCT set up. Each receipt/order/invoice would also have a link to a scanned image of the original receipt.

Ideally I would have a normalized database where I could store things like MERCHANT, MERCHANT_LOCATION, PRODUCT, CATEGORY etc as entities, so as my database grows, I could pull up common Merchants, Products, etc and have a cleaner database.

Since this is just a local database for me, I guess I do NOT need fancy things like user access control, stored procedures, triggers, etc at this point.

I would like a solution where I can use *standard* SQL for my queries and not have to learn some hokey variant of SQL.

I've got over 10 years of data that I want to enter into this new database which means it has the potential to be rather large.

Originally I wanted to build a web application to do all of this, but since I do not know Javascript, I felt it was easier to look into something like LibreOffice Base.

Any suggestions?

Would I be okay just using the embedded HSQLDB that comes with Base, and then down the road if I need more port it over to something like MySQL?

Or would I be better to create the backend in MAMP/MySQL and then use Base as the GUI?

It sounds like if you choose a non HSQLDB for the backend, then it sorta defeats the purpose of using base at all, since I think you are saying you lose A LOT of Base's functionality??

[AlgonquinPeak]

What villeroy said; MAMP is a full stack, you don't need base for that, you probably want HeidiSQL to play with your data.

Well, that's not true.

MAMP doesn't provide a GUI to interact with MySQL - rather it provides an environment on which to build a front-end to interact with MySQL.

About 5 years ago I was rather proficient with HTML, CSS, PHP, and MySQL. But I am very rusty on web development now. Furthermore, I do NOT know how to build dynamic/interactive front-ends using Javascript. (I plan on learning this, but don't have the time right now.)

I'm not 100% sure what your needs are; you're looking to manipulate individual datapoints in your database, and want to use Base/something like Access to do so? Or are you building something from scratch?

As mentioned above responding to Villeroy, I want to build a database that I can use to enter in all of my invoices/receipts/bulls/etc. The "solution" would provide a form where I could enter in the Merchant, Order, Order Details and upload a scanned JPEG (or PDF) of the actual paper invoice/receipt.

I would have a way to also key in Merchants, Merchant Locations, Products, and so on. As the "solution" grew, that would allow me to quickly select the same Merchants, Merchant locations, and products which I regularly buy.

The #1 goal is a way to do data-entry. later on, I can worry about reporting.

My one strength is that I am very good at data-modeling!!

My weakness if that I am not proficient enough to quickly build an interactive web application, and so I am hoping that LO Base can help me get something up and running in a week or two.

I have started to draw out a fairly complex ERD ("Entity Relationship Diagram"), but where I need help is figuring out how to use Base and if it is the right solution.

I spent this weekend watching like 40 videos from TheFrugalComputerGuy regarding LibreOffice Base. In those videos, Base seems very close to what I recall when I was an MS Access developer in the late 1990s. I suppose that is good, however I never liked some things in MS Access like the bastardized version of SQL it used.

I'm not sure if I should use LO Base/HSQLDB as-is out-of-the-box, or if I should try and use LO Base and MyQL?!

To be candid, Base is the bastard child of Libreoffice, they haven't updated that feature of the office suite in years. AOO they completely ignore it. There was discussion of spinning it off, but some people still use it so the drag it along. Kexi http://www.kexi-project.org/ is the best FOSS alternative to MS Access. And there will be more support for what you want on their forum: https://forum.kde.org/viewforum.php?f=219

You might want to look at Navicat too, but it costs money.

Before I respond to this suggestion, what are your thoughts after my detailed response above?

I am *hoping* that LO Base will help me to get a basic expense database set up in a week or two, and I can get to work on converting CASES OF PAPER RECEIPTS into digital records!

Then maybe next year I can build a robust web application solution when I have time to learn things like Javascript the proper way.

Look forward to hearing back from you and Villeroy!

[Villeroy]

You want to enter invoices locally.

This is how my little local databases evolved:
1. Database design on paper sheet (tables, indices, relations).
2. Implementation in Base with embedded HSQL and some working forms and reports, no macros or very little macros.
3. When the backend is complete and the most important forms can populate the related tables, extract HSQLDB from the Base document.
3a) For multi-client access: put the extracted database on a server and run HSQL in server mode. Not necessary for one database on one machine.
4. Include your database in your daily backup strategy.
5. Start working and add less important things later (macros, helper forms, spreadsheets, reports).
Since 9 years my local databases do run reliably with no crashes nor problems.

[AlgonquinPeak]

You want to enter invoices locally.

This is how my little local databases evolved:
1. Database design on paper sheet (tables, indices, relations).
2. Implementation in Base with embedded HSQL and some working forms and reports, no macros or very little macros.
3. When the backend is complete and the most important forms can populate the related tables, extract HSQLDB from the Base document.
3a) For multi-client access: put the extracted database on a server and run HSQL in server mode. Not necessary for one database on one machine.
4. Include your database in your daily backup strategy.
5. Start working and add less important things later (macros, helper forms, spreadsheets, reports).
Since 9 years my local databases do run reliably with no crashes nor problems.

I have a pretty good database design on paper. (Haven't had a chance to draw out my GUI yet, but will try today and tomorrow.)

Did I understand you correctly in that if I use MySQL as the backend, then the Base design wizards will no longer work in Base?

How hard would it be to migrate an HSQL database into something more mature like MySQL down the road?

[Villeroy]

Apart from the report wizard, you can forget all the "wizards". They are incomplete and misleading. If you are familiar with MySQL, then do the backend with MySQL. Forms, reports and links to other office documents do not bother about the type of backend. The report wizard works fine with any type of database, even spreadsheet backends. The form wizard is almost useless.

[AOOisdead]

Go for it; check this out: https://www.techrepublic.com/article/ho ... -database/

[AlgonquinPeak]

Go for it; check this out: https://www.techrepublic.com/article/ho ... -database/

What are your thoughts on my responses to your questions above?

[Villeroy]

+1
Go for it. There is nothing wrong with HSQL. From my experience I can say that HSQL is very mature and reliable once it is outside the Base document.

[AlgonquinPeak]

+1
Go for it. There is nothing wrong with HSQL. From my experience I can say that HSQL is very mature and reliable once it is outside the Base document.

You're losing me...

Did you mean that I should try and use MySQL instead of using the embedded HSQL database?

[Villeroy]

Do whatever you want, but do it.

[AOOisdead]

My weakness if that I am not proficient enough to quickly build an interactive web application, and so I am hoping that LO Base can help me get something up and running in a week or two.

I have started to draw out a fairly complex ERD ("Entity Relationship Diagram"), but where I need help is figuring out how to use Base and if it is the right solution.

I spent this weekend watching like 40 videos from TheFrugalComputerGuy regarding LibreOffice Base. In those videos, Base seems very close to what I recall when I was an MS Access developer in the late 1990s. I suppose that is good, however I never liked some things in MS Access like the bastardized version of SQL it used.

I'm not sure if I should use LO Base/HSQLDB as-is out-of-the-box, or if I should try and use LO Base and MyQL?!

Yes, I think you should use MariaDB (which is the same thing as MySQL) and connect it to Base. Like you mentioned, you don't like the default DB. And there might not be as much support for issues with HQSQL as there is for MariaDB/MySQL. So use the database you are comfortable with. Reasons:

1) you can build your database quickly because you're comfortable with MariaDB/MySQL, and if you run into problems with interfacing it with Libreoffice Base, you can try Kexi, and if you run into problems there, you can try something else (there are applications that cost money that can do what you want)

2) if you build it with HQSQLDB or Firebird, and find out you are having problems, then you have to go through the effort to convert it back to your preferred SQL DB, which is extra work.

3) there are a few guides, like the one I linked, that say that you can easily use MariaDB/MySQL as a connection to LibreOffice Base. So don't feel that you are doing something improperly by not using the default databases. As I mentioned, Base is a bastard child of the LibreOffice suite. So don't think there are some master engineers that have a plan for seamless integration with the default databases. Firebird was chosen by preference of some guy who didn't like HQSQL, and they do like, one push a year on furthering it.

4) your use case isn't that complex so don't over think it. No offense but you seem to do what I do which is overanalyze for the perfect conditions. You're going to make mistakes regardless so it's better to get started now and figure out things empirically instead of the rationalism-route you're pursuing. Think like an engineer, not a scientist.

[AlgonquinPeak]

4) your use case isn't that complex so don't over think it. No offense but you seem to do what I do which is overanalyze for the perfect conditions. You're going to make mistakes regardless so it's better to get started now and figure out things empirically instead of the rationalism-route you're pursuing. Think like an engineer, not a scientist.

Guilty as charged!

I will be traveling over the next couple of days. But when I get back home, I'll take a look at your link and see if I can get MySQL to play nice with LO Base.

In the mean time, my biggest need/concern is being able to quickly set up a form/sub-form(s) combinations and get my receipt data entered into my database.

Hopefully hooking up MySQL to Base won't break how Sub-forms work, because that is the key piece I need help with, and as mentioned above, it is my Achilles heal when trying to build a web application solution.

[AOOisdead]

Let us know if it works!

[AlgonquinPeak]

Let us know if it works!

Am on another out-of-state trip, but have found some time to tinker with trying to point LibreOffice Base to MySQL the last few days.

Unfortunately, I was unable to get this to work using any of the 3 available methods: 1.) Direct Connect, 2.) ODBC or 3.) JDBC.

I had heard that JDBC was the best way to go, but LibreOffice was acting very bizarre (e.g. not seeing tables, relationship, etc.)

Will have to think about things on my way home this weekend, but I am basically at the point where I am ready to kick LibreOffice Base to the curb. And that is a shame, because I really needed the ability to drag-and-drop a form/sub-form and start entering in my receipts data.

Looks like I will have to learn Javascript and how to make a dynamic subform that will expand as I enter in more line items.

Very disappointing considering I love how LibreOffice Calc and Writer work.

Oh well.

[Villeroy]

You have to connect with the right user account. WIthout appropriate privileges you do not even see any database objects.

First you need to have a database with tables, relations and indices and users. This requires that you are familiar with the chosen database in a way that allows you to built up something that is usable by other people.
Then you may use this frontend or some other tool to make your database accessible to others.
How to set up a MySQL server with users and groups is off topic.

[Villeroy]

I installed a MySQL server on my Linux system and face the same problems even on the command line unless I work as root (the system admin account). This indicates clearly that I do not understand MySQL. It does not tell me anything about LibreOffice.

[robleyd]

Of course it is known that root (*nix system superuser) is different from the default root user for mysql.

[Villeroy]

Yes, but why do I get another database server when I run MySQL (or MariaDB for that purpose) as system root. As system root I can connect, create a user, grant privileges, create a table and use it as the normal user. But as a normal system user I can not even log-in.

[AlgonquinPeak]

You have to connect with the right user account. WIthout appropriate privileges you do not even see any database objects.

First you need to have a database with tables, relations and indices and users. This requires that you are familiar with the chosen database in a way that allows you to built up something that is usable by other people.
Then you may use this frontend or some other tool to make your database accessible to others.
How to set up a MySQL server with users and groups is off topic.

I don't follow what you are saying...

I took these steps...

- Created a new database in phpMyAdmin
- Opened up LibreOffice Base and set up JDBC with the database
- Went into Base and created two test tables.
- Verified that these existed in phpMyAdmin/MySQL on my Mac.
- Created a one-to-many relationship between the "Employee" table and the "Tele_No" table.
- Saved the relationship in LO Base.
- Closed LO.
- Went back in and the relationship doesn't show in the relationship window even though Foreign Key Constraints were built in MySQL (as shown in phpMyAdmin).
- Went to deleted the "Tele_No" child table and LO Base wouldn't allow this.
- Deleted "Tele_No" table in MySQL yet it still remained in the LO Base relationship window.
- And many other strange behaviors...

I was logged in as "root", so that should address all user access issues...

[AlgonquinPeak]

I installed a MySQL server on my Linux system and face the same problems even on the command line unless I work as root (the system admin account). This indicates clearly that I do not understand MySQL. It does not tell me anything about LibreOffice.

Not following what you are implying...

What is it that you do not understand?

I am on a Macintosh, so I work from MAMP which and application and stands for: Macintosh, Apache, MySQL and PHP.

I use phpMyAdmin as the GUI to manage my MySQL databases. This is part of MAMP.

Because this is a local database, I don't need to be overly concerned about security, so I run as "root" for now.

I *do* understand MySQL and relational databases - although I haven't actively used MAMP or MySQL or phpMyAdmin is 4-5 years admittedly.

Taking back something I (think) I said above...

I could NOT get the JDBC connection working properly because I got this error message...

The server time zone value 'EDT' is unrecognized or epresents more than one time zone. You must configure either the server of JDBC driver (via the serverTimeZone configuration property) to use a more specific time zone values if you want to utilize time zone support.

I spent hours researching this and there are no clear solutions online, PLUS it is over my head.

Then I used the "Direct Connect" option (or whatever it is called) and while it appears to work, I get the issues mentioned above.

Maybe there is a way to get the JDBC driver to work - which seems the best way to connect to MySQL - but I couldn't figure it out. And because LibreOffice Base seems so "buggy" using the Direct Connect option, I decided to bail.

[Villeroy]

I only tried to reproduce the connection problems by quickly installing a MariaDB stack and failed because I made too many mistakes on first try. I got one database up and running for admin access only, possibly because I included a hardening script "mysql_secure_installation".
If you think, it is LibreOffice, you could try OpenOffice or any other database frontend for your system. I could not even connect a command line interface to my first test database.
If you think, it is a driver problem, try ODBC or LibreOffice's native SDBC driver.
Possibly you are only person on this forum struggling with the combination of MacOS with MySQL via JDBC and LibreOffice. It is hard to tell anything without direct access to your system. I would expect that a MySQL server can log failed log-in attempts and failed data access due to missing privileges.

[Villeroy]

OK, I found some more time to play with MariaDB which is a variant of MySQL not owned by Oracle Corp.

Code: Select all

sudo mariadb -u root
CREATE USER 'villeroy' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'villeroy'@'localhost' ;
CREATE DATABASE TESTDB;
exit

Now I have an allmighty database user 'villeroy' for all databases on localhost (IP 127.0.0.1) plus an empty database named TESTDB.
----------------------------------------
LO-menu:Tools>Options>Advanced... [Class Path...] [Add Archive...] I added /usr/share/java/mariadb-java-client.jar which is the JDBC driver I have installed with package libmariadb-java from my Linux software repository. In case of MySQL it would be something like /usr/share/java/mysql-java-client.jar. I don't know anything about the installation paths on MacOS.
Restart LibreOffice. Now this driver can be found when needed.
-----------------------------------------
LO-menu:File>New>Database...
Connect to existing database of type: MySQL
Next step asks for ODBC, JDBC or "Directly". I choose JDBC
Next step asks for database name (TESTDB in my case), server is either localhost or 127.0.0.1, port number is 3306 and the driver class is com.mysql.jdbc.Driver if you run MySQL. I changed it to org.mariadb.jdbc.Driver. The driver class is some capability that is provided by the above specified database driver mariadb-xxx.jar or mysql-xxx.jar respectively.
I enter the user name villeroy and check the password option because villeroy requires to be prompted for a password (as set up in the database).
I don't register this test database. If I would do so, I could use this database to fill out stand-alone office documents with database data by using a simple data source name.
---------------------------------------
When I go to the tables section of my new database I am prompted for the password of user "villeroy". Being logged-in as allmighty user villeroy I am able to create a new table with auto-ID in the Base GUI. The resulting database window looks like this. Notice the status bar.

Base document connected via JDBC to a MySQL database (or MariaDB) named TESTDB on 127.0.0.1 (localhost), logged in as user villeroy. The new Table1 has been created in the Base GUI.

Edit: I use LibreOffice6.0 and 6.2 on Ubuntu Linux. Both office versions can do the trick whereas OpenOffice does not recognize the modified driver class org.mariadb.jdbc.Driver.

[robleyd]

sudo mariadb -u root

You shouldn't need the sudo there; -u tells mariadb to use the mariadb username that follows as authorisation. Why Monty Widenius chose the same default usename as the *nix superuser I don't understand.

[Villeroy]

Indeed, "root" can be confusing. But the first thing I stumbled upon was this:

Code: Select all

andreas@andreas-ThinkPad-W510:~$ mariadb -u root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
andreas@andreas-ThinkPad-W510:~$ sudo mariadb -u root
[sudo] Passwort für andreas: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 31
Server version: 10.1.40-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

[robleyd]

At a glance, mariadb may not be properly configured in terms of privileges. Might be related to the package; anyway probably outside the scope of this topic.