Make a small business database with LibreOffice
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.
At least one Ubuntu Linux PC
Step by step
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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).
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.
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.
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.
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.