Official website for Linux User & Developer
FOLLOW US ON:
Nov
2

Make a small business database with LibreOffice

by Michael Reed

Create a database that combines an easy-to-use, form-based front-end using LibreOffice with a portable, networked MySQL back-end

We’re going to show you how to put together a typical database for small business use: a database of customer details. It will be possible to both export and import contact data from in standard formats by making use of Calc, LibreOffice’s spreadsheet module. We’ll use Gmail contacts as our source, but you can use any software that can export CSV files – and pretty much everything can.

We’ve added a few twists to keep things interesting. This project uses the Base module of LibreOffice as the front-end, and this provides a GUI for setting up the database, creating the forms for data entry and the actual business of entering data. For the back-end, we will be using the industry-standard MySQL. This allows us to locate the back-end on a central server. This, in turn, allows multiple users to access the database.

For initial creation of the MySQL database, we’ll use phpMyAdmin thanks to its friendly web interface, although the actual database design will be carried out from within Base. By the end of the project, you will have a GUI system for browsing and editing the database with a portable, networked back-end.

Create the perfect database with LibreOffice
Create the perfect database with LibreOffice

Resources

LibreOffice
At least one Ubuntu Linux PC

Step by step

Step 01

Install LibreOffice

At time of writing, the major Linux distributions haven’t moved over to LibreOffice 4 and are still offering 3.x. This means that you may have to install LibreOffice 4 manually. Visit the LibreOffice website (www.libreoffice.org) and follow the instructions. On Ubuntu, this consists of unpacking the archive and running sudo dpkg -i *.deb on the contents.

Step 02

Install the Apache web server

We’ll install Apache early on and with its own command because some of the other packages need to be able to configure a working Apache installation. Carry out the installation withsudo apt-get install apache2.Test it by navigating a web browser to http://localhost.

Step 03

Install Java and additional classes Connectivity between Base and MySQL makes use of a Java class. Type sudo apt-get install default-jdk to install the Java runtime. Type sudo apt-get install libmysql-java to install the needed additional Java classes.

Step 04

Install the MySQL Server

Type sudo apt-get install mysql-server to begin installation. Before long, you should be prompted to set a root MySQL password. Note that this isn’t the same as the administrator account of your system, which is also called ‘root’. Choose a password and make a note of it.

Step 05

Install and test phpMyAdmin

Typesudo apt-get install phpmyadmin to begin installation. When prompted to choose a web server, choose Apache2, select it with the space bar and press Return. When requested, give it the MySQL root password and then choose a password for phpMyAdmin and make a note of it. Navigating to http://localhost/phpmyadmin/ should take you to a functioning login page. Log in using the MySQL root username and password. We’ll use MySQL to set up and maintain the actual database, although we’ll create the fields from within LibreOffice later on.

Step 06

Create database

Within the phpMyAdmin web interface, select the Databases tab. Now create a new database by entering the name ‘customer’ into the text box and clicking on Create. This database will contain our customer data.

Step 07

Add JDBC in LibreOffice

We now need to tell LibreOffice where to find the JDBC class file. Start LibreOffice and go to LibreOffice > Options > Advanced. In the Java Options section, select Class Path and then Add Archive. The file you need is located at: /usr/share/java/mysql-connector-java.jar. Select it and restart LibreOffice.

Step 08

Connect the database

We now need to connect our front-end (LibreOffice) to the back-end (MySQL) of our database. Start LibreOffice and launch the Base module. In the dialog that pops up, select ‘Connect to an existing database’. From the drop- down menu below this, select MySQL as the database type.

Step 09

Configure Base

On the next page, select ‘Connect using JDBC’. On the next page, click on ‘Test class’ to ensure that the Java RT is working. Now enter the name of the database that we created, customer, and enter localhost into the Server field. On the next page, add Root as the username and tick ‘Password required’. Now click on the ‘Test connection’ button and enter the root MySQL password, when asked for it, to test the connection between LibreOffice and the local MySQL server. Presuming that this completes without errors, click on Next.

Step 10

Save the database

You can accept the defaults on the next page, so click on Finish. When prompted, give the database a name and save it. Remember that this file contains the connection information for access to our MySQL database – it doesn’t contain the actual records.

Step 11

Create fields from a template

You may want to create a custom set of fields for your customer records, but to save time we’re going to use the one of the templates that is built into Base. Select Tables from the side menu and then ‘Use Wizard to Create Table’. Using the Sample tables pull-down menu, select Customers. Use the >> button to copy all of them across. On the next page, you can tweak the fields that you have included and add new ones. Select the defaults on the next two pages and then click on Finish.

Step 12

Create form from template

Select Forms from the sidebar. Click on ‘Use Wizard to Create Form’ in the Tasks window. In the table wizard, click on the >> symbol to copy across all of the fields in the database.

Step 13

Finalise form

Accept the defaults in sections 2, 3 and 4, but select the first arrangement icon in section 5, ‘Arrange controls’. You should now see a preview of our entry form in the main window. Select defaults on the other sections and then click on Finish.

Step 14

Test data entry

To enter data into the database, use the form that we created. Select Forms in the sidebar and then double-click on the name of the form in the main window. This brings up the GUI record- editing interface. The form can still be tweaked and edited by right-clicking on its name in the main window.

Step 15

Export your contacts from Gmail

Switch from the Gmail contacts view using the pull-down menu in the top-left corner, underneath the Gmail logo. From here, click on the More icon pull-down menu and select Export… Click on Export.

Step 16

Clean up the data and create a key Start a new spreadsheet and open the CSV file that you exported from Gmail. Use Ctrl-mousewheel zooming to get an overview. Typically, a lot of the fields will be completely blank, so select these columns (click on the column letter at the top of the window) and remove them (Edit>Delete cells). We have to create a key for each record. Label a column ID. Select the first cell in the column and then select the final cell by Shift-clicking on it. Use the fill feature (Edit>Fill>Series).

Step 17

Import the data into Base

When you’ve cleaned up the spreadsheet, select the data (including the column headers) by clicking on the top-left cell and then Shift- clicking on the bottom-right cell. Right-click and select Copy. Select Tables from the side menu of the Base module. From here, right-click on the customers table and click on Paste. This should bring up the import wizard. Select ‘Append data’ and ‘Use first line as column names’ options, and click on Next.

Step 18

Align the fields

The field names from our imported data don’t quite match those of the database and so we need to use the second page of the wizard to line them up. To do so, click on a field name and use the up and down icons in the other list to create the correct attachments. Then click on Create.

Step 19

Create a new database user

To access the database from more than one machine, you must create additional users. Log back into phpMyAdmin, click on the Users tab and select ‘Add user’. From here, create a new user with the name and password of your choosing and make a note of it. Click on ‘Check all’ in the ‘Global privileges’ section.

Step 20

Redistribute the database

In the Base module, re-save the database under a new name. In this new version of the file, we have to alter a few details. Select Edit > Database > Properties and enter the name of the new database user. Click on the Additional Settings tab and enter the IP address of the machine with the MySQL database.

Tags:
  • Tell a Friend
  • Follow our Twitter to find out about all the latest Linux news, reviews, previews, interviews, features and a whole more.
    • Geoff Halsey

      It maybe three steps to heaven, but twenty is not that bad for a database with a frontend. I’ll give it a go.

    • Rony Gabriel

      I just wanted to say that it worked perfectly fine, and that it was very easy to accomplish. Thanks for sharing with us this step-by-step. Cheers!