Of all four OpenOffice.org applications, Base is probably the most complicated one. It is a powerful tool that allows you to build complex databases, but this power comes at a price: if you’ve never worked with database applications, you might find Base a bit overwhelming. This article provides an introduction to the most important aspects of Base that will help you to create databases with consummate ease.

Like any Relational Database Management System (RDBMS) worth its salt, Base consists of four components: tables, queries, forms and reports.

Data within a database is stored in tables. Each table consists of rows and columns, and data contained in one row is known as a record. Each record contains one or more fields, and each field in the database has its type. For example, the INTEGER (or INT) field type is used to store numeric values, while the VARCHAR field type is suited for managing text strings. Unsurprisingly, the DATE field type stores dates, and the BINARY type is designed for storing binary data such as images.

Queries are used to extract, view, and manipulate data. Queries are the tools you use to sort, filter, tweak and analyze the data in the database. In Base (as in most database management systems), queries are scripts written in SQL. While Base does allow you to work with “raw” SQL commands and scripts, it also features graphical tools that help you to create advanced queries without getting your hands dirty with SQL programming.

Forms allow you to view and edit the data in a table. Think of forms as a GUI for the database: while tables are used to store data, forms are used to display and manipulate data in the tables. In fact, forms are the tool that allows you to turn a plain database into an application where users interact with data through a friendly interface instead of fiddling with arcane commands. To help you create forms, Base features a Form Designer that you can use to create simple and more complex graphical front-ends for your databases.

Reports are used to produce printed output from the table. For example, if you have a database table containing address info, you can create a report that prints addresses as a nicely formatted contact sheet. More advanced reports can pull data not only directly from tables, but also from queries. Again, Base’s Report Builder allows you to design reports using visual tools.

Figure 1: Database layers

All in all, you can say that a database consists of three layers. At the bottom layer are the tables where the data is stored, above those are the queries which extract and manipulate the data in the tables, and then there are the forms which are based on the queries or directly on the tables.

Base comes with its own Java-based database engine called HSQL. Its major advantage is convenience: as a user, you can just start building databases without getting your hands dirty with the technical stuff. The built-in engine does have a few serious drawbacks, though. First of all, it’s designed for single-user databases, so there is no easy way to share your databases with other users. It’s also relatively slow and the data is not separated from the database structure, which makes upgrading your database solution quite tricky. In other words, the built-in engine is more suited for small single-user databases that reside on your own machine. For more ambitious database solutions, Base offers support for external ODBC-compliant database engines such as MySQL, which is a perfect system for creating networkable and fast multi-user databases. However, setting up an external database engine requires some technical knowledge and driver software that acts as a connector between Base and the database engine. Besides ODBC-compliant engines, Base can use other types of external data sources, including Microsoft Access, Thunderbird Address Book, spreadsheets and even plain text files.

As mentioned above, one of the good things about OpenOffice.org is its ability to use different database engines. Just give it the right driver, and OpenOffice.org can connect to virtually any database system, including MySQL. However, deciding what database driver to use and configuring a connection between MySQL and OpenOffice.org can be a bit tricky.

First of all, you have to choose what driver (also called a connector) to use. MySQL offers two connectors that allow you to move data between OpenOffice.org and MySQL: ODBC Driver for MySQL (Connector/ODBC) and JDBC Driver for MySQL (Connector/J). The latter is easier to install and configure, and can be used on Linux, Windows, and Mac OS X (it even works with NeoOffice) in the exactly same way. However, as the name suggests, Connector/J requires that Java be installed on your machine. While the question of Java’s openness is no longer an issue, you still have to make sure that the Java Runtime Environment is installed and added to OpenOffice.org. Another, more serious, problem with Connector/J is its somewhat limited functionality when used with the OpenOffice.org Base database. For example, using Base’s GUI, you can create a primary key, but you can’t assign the auto_increment property to it. To work around this limitation, you can build the database by executing the appropriate SQL commands using the built-in SQL editor (Tools → SQL). For example: `ID` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY. Alternatively, you can use third-party tools like phpMyAdmin.

Connector/ODBC does not have these limitations, but installing and configuring it is a more complicated process. Also, on Linux, the connector works best when the MySQL database is installed on the same machine. This is because the driver expects to find the mysql.sock file in a specific directory, and if it doesn’t find it there (which happens with a remote database) it fails to establish a connection. You can, of course, make the driver connect to a remote database, but since this requires some wizardry, it’s often not worth it – especially if you plan to deploy the driver on multiple machines.

As a rule of thumb, try Connector/J first if you are running Linux or Mac OS X and you are comfortable using SQL or can edit the database using a third-party tool. If you are using a Windows or Linux machine with MySQL installed on it, then you can opt for Connector/ODBC.

To get started with the JDBC Driver for MySQL (Connector/J), download it from MySQL's website, unpack the downloaded file, and move the resulting mysql-connector-java-x.x.x-bin.jar file into the desired location (e.g. your home directory). In OpenOffice.org, choose Tools → Options → Java. Make sure that the appropriate Java Runtime Environment is selected and click the Class Path button. Next, click the Add Archive button and select mysql-connector-java-x.x.x-bin.jar. Click OK to save the settings and close the window, then restart OpenOffice.org.

Figure 2: Creating an JDBC connection in OpenOffice.org

Now you are ready to connect OpenOffice.org to the MySQL database. In OpenOffice.org, choose New → Database. In the Database Wizard, select the Connect to an existing database option, select MySQL from the drop-down list, and click Next. Select the Connect using JDBC (Java Database Connectivity) option and click Next. Specify the name of the database and the server address using the appropriate fields. To make sure that the MySQL JDBC driver works properly, click the Test Class button. Once the driver has been loaded successfully, click Next. Enter the database user name into the User name field. If the entered user name has a password, tick the Password required check box. To see whether everything works as it’s supposed to, click the Test Connection button. Click the Next button, select the Yes, register the database for me option, and click Finish. Give the database a name and save it.

If Connector/J works for you, you’re in business. If not, try Connector/ODBC. The process for getting it working is slightly different depending on whether you’re running Linux or Windows. We’ll try Linux first – specifically, Ubuntu.

First of all, install the required packages. Launch Synaptic and mark the following packages for installation: unixodbc, libmyodbc, and unixodbc-bin. The latter package contains the GUI tools for setting up an ODBC connection to a MySQL database, which you can use instead of fiddling with configuration files.

Figure 3: Setting up the MySQL ODBCdriver

Once the packages have been installed, launch the ODBC configuration tool by executing the sudo ODBCConfig command in a terminal window. Click the System DNS tab and click the Add button. Click Add again to create a new ODBC driver. This opens the Driver Properties window. Give the new driver a name and description using the appropriate fields. Assuming you’re running the 32-bit version of Ubuntu, specify the path to the libmyodbc.so file in the Driver field (/usr/lib/odbc/libmyodbc.so) and enter the path to the libodbcmyS.so file in the Setup field (/usr/lib/odbc/libodbcmyS.so). The final result should look like the figure. Click the Save and Exit button to save the settings, then click OK to open the Data Source Properties windows. Give the new data source a name, enter its description, then specify the MySQL server address, the database name, and port. Click OK, and you are ready to go.

Connecting OpenOffice.org to the MySQL database using the created connection is a matter of choosing the correct options in the Database Wizard. In OpenOffice.org, choose New → Database. In the Database Wizard, select the Connect to an existing database option, select MySQL from the drop-down list, and click Next. Select the Connect using ODBC (Open Database Connectivity) option and click Next. Point the Wizard to the created ODBC connection using the Browse button. Click Next and enter the database user name into the User name field. If the entered user name has a password, tick the Password required check box. To see whether everything works as it’s supposed to, click the Test Connection button. Click the Next button, select Yes, register the database for me option, and click Finish. Give the database a name and save it.

Figure 4: Configuring an ODBC connection in Windows

If you’re running OOo on Windows, download the ODBC Driver for MySQL (Connector/ODBC), unpack the downloaded file, and run the setup program. Navigate to Control Panel > Administrative Tools and double-click on Data Sources (ODBC). Click on the User DSN tab and click the Add button. From the list of available drivers, select the MySQL ODBC Driver, and click OK. This opens the Connector/ODBC configuration window. In the Login section, enter the required information into the appropriate fields. Click Test to see whether the created connection works properly, then click OK to save the settings and close the window. In OpenOffice.org, create a new database as described previously.

The best way to learn the ropes is, of course, to create a real-world database. The following description guides you through the process of creating a simple database that has all the basic features such as tables, relations, queries, and forms.

The very first step in designing a database is to create a database model. Let’s say that you read a lot of books and magazines in foreign languages, and you want to keep track of all the new words and expressions as well as their definitions and explanations.

For that purpose you create a database – let’s call it BookWorm – that consists of three tables: BOOKWORM, BOOKS and REFERENCES.

• The BOOKWORM table is used to store words and expressions (the Entry field), their definitions (the Definition field), the surrounding context (the Context field) and miscellaneous notes (the Notes field).
• The BOOKS table stores information about each related book and article including its title (the Title field), author (the Author field), and other relevant data.
• The REFERENCES table is used to keep track of sources used to find words’ definitions, and it includes the Reference and Notes fields.

Figure 5: BookWorm database model

Besides the fields that contain relevant information, you also need fields that are used as primary and foreign keys. A primary key is a value that uniquely identifies a record in the table, while a foreign key is a field that refers to the respective primary key from another table. Both keys are used to define relations between tables. In the BookWorm database, you must connect the BOOKWORM and BOOKS tables via the Title fields, and the BOOKWORM and REFERENCES tables via the Reference fields (Figure 5).

All relations in the database are so-called one-to-many relations. This means that the BOOKWORM table can contain multiple records with the same title and reference (marked as n in figure 5), while values in the BOOKS.Title and REFERENCES.Reference fields must be unique (marked as 1 in figure 1). For example, you can have many words and expressions from a book, but each book must have a unique title.

With the database model in place, you can create the actual database. Launch OpenOffice.org Base and choose File → New → Database to open the Database Wizard dialog. Use the Database Wizard to create and save a database file. When you click the Finish button, Base opens the main window. This is where you create your database.

Start by creating the BOOKWORM table. Choose Tables in the Database pane and click on the Create Table in Design View item. This will create an empty table. Create an ID field by typing its name in the Field Name column, and set its type to Integer [INTEGER].

Figure 6: Defining a primary key

To set the ID field as the primary key, right-click on the record pointer (the green triangle) and select Primary Key from the context menu. The primary key has two requirements: it must not be empty, and it must have a unique value. To ensure that the system inserts a unique value in the ID field every time you create a new record, select Yes from the AutoValue list in the Field properties pane.

Figure 7: Defining the primary key’s properties

Add the rest of the fields and set their types. Save the table (File → Save or Ctrl+S) and give it the name “BOOKWORM”. Now that you know how to create tables and fields, you can easily add the BOOKS and REFERENCES tables.

Using the Relation Design tools, you can create relations between tables by drawing connections between the primary and foreign keys. To define relations between the three tables, choose Tools → Relationships, and use the Add Table dialogue window to add the tables.

To establish a relation between the BOOKWORM and BOOKS tables select the BOOKS.Title field, click and hold down the left mouse button, then drag a relation onto the BOOKWORM.Title field, then release the button. In a similar manner, add a relation between the BOOKWORM and REFERENCES tables via the Reference fields.

Figure 8: Defining relations

The next step is to define the relations’ properties. Let’s say that you added a few records in the BOOKWORM table and then discovered a mistake in the book’s title. If you correct the mistake in the BOOKS.Title field, the relationship will be broken. To fix it, you have to go through all the related records in the BOOKWORM table and correct the book’s title in each of them.

Figure 9: Relation’s properties

To avoid these problems, set the relationship to Cascade update, which means that all changes in the primary key value are automatically propagated to the foreign key. Double-click on the relation between BOOKWORM and BOOKS, and in the Update options section, select the Update cascade option.

Now, if you delete a book from the BOOKS table, this will leave all of the related records in the BOOKWORM table orphaned. To prevent this from happening, select the Delete cascade option from the Delete options section. This ensures that when you delete a book, all related entries are also deleted.

When the relations have been defined, save them (File → Save or Ctrl+S) and close the Relation Design dialog window (File → Close).

After you fill tables with data, you can create queries to view and manipulate the data. Base allows you to create queries using either the Query Wizard or the Design View. Users familiar with SQL can also use the built-in SQL editor. Most people, however, are likely to use the Design View, which allows you to construct queries using a graphical user interface.

Figure 10: Creating a query

Let’s say that you want to create a query that allows you to present a detailed view of the entries and their related books. Switch to the Queries section by pressing the Queries button, and click on the Create Query in Design View item in the Tasks section. This opens the Query design window and the Add Tables dialog window. Add the BOOKWORM and BOOKS tables to the query using the Add button, then click Close. All you have to do now is to add fields to the query. You can do this either by choosing the fields from a drop-down list in the Field cells or by dragging fields from the tables to the Field cells.

By default, the query uses the field names as their labels, but you can change that to make your query more readable using the Alias row. For example, you can set the Entry field’s alias to “Word or Expression”.

To save the finished query, choose File → Save (Ctrl+S) and give the query a name. The new query appears in the Query section, and you can run the query by double-clicking on it.

In a similar manner, create two additional queries: the Title query that includes the BOOKS.Title field and Reference that includes the REFERENCE.Reference field. You will need them later to use with forms.

To make your database application complete, you need to build a form-based interface. But before you do that, you should get familiar with tools available in the Form Designer.

Figure 11: Form Design toolbars

• The Form Controls toolbar contains form controls such as text field, list, combo box and push button. To add a new control, click on the respective button on the Database Controls toolbar and draw the control on the form.
• The Form Design toolbar allows you to switch the Form Navigator and Position and Size windows on and off, show/hide the grid and guides, and change the fields’ activation order.
• The Form Navigator displays a list of all form controls in the form. It also allows you to create subforms and move controls.
• The Properties Inspector is used to specify and change the properties of the currently selected control.

Let’s create a form to display, edit, and add records to the BOOKWORM table. Switch to the Forms section by pressing on the Forms button, then click on Create Form in Design View. This opens a blank window.

Click on the Form Navigator button on the Form Design toolbar. In the displayed Form Navigator, right-click on the Forms folder and choose New → Form. Give the form a descriptive name, then right-click on the new form and select Properties. Under the Data tab, select Table from the Content Type list, and BOOKWORM from the Content list.

To add the Entry field to the form, click on the Text box button on the Form Controls toolbar, draw a text box, then double-click on it. Under the Data tab, select Entry from the Data field list. In a similar manner, add other fields to the form.

Using the Form Designer, you can also add more advanced controls. For example, you can create a combo box containing a list of all book titles from the BOOKS table. Click on the Combo Box button on the Form Controls toolbar and draw a combo box.

Figure 12: Control Properties Data tab

Double-click on the combo box and in the Properties: Combo Box window under the Data tab, select:

• Title from the Data field list
• Query from the Type of list contents
• Title from the List content

The created form allows you to add, view, and edit data in the BOOKWORM table, but you can also include data from other tables. For example, you may want to view the book details next to the words and their definitions. This can be done by using a subform. As the name suggests, a subform is just another form inserted in the main form. In the Form Navigator window, click on the BookWorm form and select New → Form. Give the subform a descriptive name, then right-click on it and select Properties. Under the Data tab, select Table from the Content Type list, and BOOKS from the Content list. You can then add form controls to the created subform.

When adding fields from the related table to the subform, you must specify a link between the primary and secondary keys. In the Data section, select the primary key field from the Link master fields list and the secondary key field from the Link slave fields.

Figure 13: Specifying a link between the master and slave fields

When you are finished, save the form and close the Form Designer. Double-click on the created form to open and use it.

Using the forms, you can easily add new records to the tables. But what if you already have some data, for example, in a spreadsheet? In many situations you’d want to import the existing data into your database. Let’s say you have a Calc file containing a list of books, and you want to import it into the BOOKS table.

Since properly formatted source data can greatly simplify the import procedure, it’s worth spending time on some preparatory work. First of all, you need to tweak the spreadsheet’s structure, so that it becomes identical to the structure of the BOOKS table. Make sure that the first row contains the field names identical to those in the database table. Ideally, they should also be in the same order. Ensure that all the data bits is in the proper columns.

Figure 14: Importing data

Now select the data in the sheet and copy it into the Clipboard (Edit → Copy or Ctrl+C). Open the database, click on the Tables button, and select the BOOKS table. Right-click on it and choose Paste. In the Copy table dialog window, type the exact name of the target table (BOOKS) into the Table name field and select the Attach data option.

In the Assign Columns dialog window, check whether all the fields in the Source table window have been selected and aligned with the respective fields in the Target table window. To align the fields, use the blue Up and Down arrows. Finally, click Create to import the data.

To get the most out of the OpenOffice.org Base application, you will need time and patience. But once you’ve become familiar with Base’s features, you can create rather impressive database applications.

Related articles: