With Access, Microsoft created a program that hid SQL databases (see the "Relational Databases" box) behind a graphical user interface. With this approach, SQL queries and data entry forms are easily created using mouse clicks without users' having to know anything about programming.

LibreOffice, too, recognized the potential of such software and delivered the Access-like Base (Figure 1) [1] at the end of 2005. Users accustomed to graphical user interfaces could thereby "program" their book or video databases, and smaller companies could analyze their financial data.

Figure 1: With a query editor and wizards, Base can help complete typical programming tasks using a graphical interface.

Data Storage

The core of the image database is the Images table. It contains a column ("field") for each property of a photo you want to save. Figure 2 suggests seven such data fields.

Figure 2: The Images table stores descriptive image data on the hard drive and is the core of the sample database.

To create a table in Base, click Tables in the left pane and, under Tasks , click Create Table in Design View . The first field that appears, id , is not a data field per se; it doesn't appear in the input form and isn't visible to database users. You do need it, however, to later set up relationships between images and categories (see the "Relational Databases" box for more details). Entering data into forms will not work without this key in Base.

For the Field Type in the key column, select Integer [INTEGER] . Also, for the Field Properties set AutoValue to Yes , which assures you are assigning a unique value to new data records.

The little key icon in the border column identifies the field as a primary key. Right-clicking the field edge and selecting Primary Key from the context menu makes the selected field a primary key. Having this primary key signals to the database that it should create an index for fast access and prevent reuse of the same value.

Apart from the Integer field type, you also have Text and Date/Time types for the table. Text fields impose a fixed Length for the Field Properties that are allocated storage space according to the data record. For Title , Author , and Data Path the default setting of 100 will work fine, whereas Comments could use a setting between 500 and 1000 for the maximum length.

The Date/Time data type has an 8-byte storage space. Other types used frequently include Decimal (e.g., for currencies) and Float (e.g., floating-point for physical sizes). There are also field types for separate Date and Time .

Once you've completed all the Field Name and Field Type entries as in Figure 2 (or those of your own choice), save the table with the name Images .

Forms

Double-clicking Images in the Base main window opens the table in a familiar spreadsheet view; however, entry forms (Figure 3) are a bit easier to use, so I'll show how to make one. The Form Wizard that you get when you click Use Wizard to Create Form creates entry forms for the Images table in just a few mouse clicks.

Figure 3: The Base wizard helps create an entry form for the Images table.

Under Tables and Queries in Step 1 of the wizard, choose Table: Images and all Available fields except the automatically created id one (Figure 4). Skip over Step 2 through Step 4 (for creating subforms) and go directly to Step 5. In Step 5, choose In Blocks – Labels Above for Arrangement of the main form to get the results in Figure 5.

Figure 4: In the Form Wizard, you need only select the fields and formatting, and the wizard arranges them for you on the form.

Figure 5: This is how the entry fields show up in a raw form as created by the wizard.

The form edit view remains open as soon as you exit the wizard. To get to form entry mode, change to the Base main window, where you click Forms in the left pane. Under Forms , right-click Images and then click Edit in the context menu.

Figure 3 shows the entry forms enhanced by rescaling the input mask of a few fields. The Data Path field was converted, by right-clicking Replace with | Image Control Element , to a field that opens a file manager when double-clicked, where you can choose the image you want to place. You can change the size of the resulting preview window according to your own taste. However, the table field stores the relative path to the image file, so if you change the location of the database later, the form will not be able to find the picture.

Before scaling the File Path and Comments fields, ungroup them by right-clicking them and choosing Ungroup . After double-clicking the Data Path label, change the contents of Label field in the properties box to Images .

To fully use the expanded Comments field, double-click the entry field to open its properties. Change the Text type to Multi-line and choose Vertical for Scrollbars . Choose Dropdown for both fields to create a practical data selection.

To navigate through the data fields independently of any possibly hidden icon bars in the program window, add a Navigation Bar to the form. You can find it in the More Controls toolbar (shown framed in a red rectangle in Figure 3), which you can access from the toolbar on the left. Add the navigation bar button to the main icon bar and use the control elements tool to resize it as desired.

After saving the form, close it, then reopen it with a double-click in the main window, this time in entry mode instead of the previous design mode. As you can see, this largely automatically designed entry form in Images creates a simple, yet fully functioning image database.

Flexible Connections

As explained in the "Relational Databases" box, you'll need two new tables to associate any number of categories to a set of images. Categories includes the Category text field and – as already created in the Images table – an id column with an AutoValue of type Integer that you set as the primary key.

Save this table with the name Categories , close it, and reopen it in data entry mode. Enter the category names you want to use for your image collection – for example, "Landscape," "Portrait," "Still life," etc. In this case, for such a simple table, you will not need to create a form.

You link both tables with what is called a pivot table that you'll name ImageCategories . This connects the Images table with Categories through their id fields. To create this table, use the Create Table in Design View and enter two fields, ImageID and CategoryID , and make them Integer field types.

Be sure to click Yes when asked whether to automatically create a primary key for the ImageCategories table when you save the table. Remember also to set AutoValue to Yes for the new ID field the wizard creates.

It would be unwieldy to manually enter all the id fields for all the images in the ImageCategories table. Instead, I will show how you can integrate the Categories table into the form and display it like you see in Figure 6, surrounded by a red box.

Figure 6: The subform shown in red assigns as many categories as desired to an image.

The categories table embedded in a subform shows all the current categories related to the data set in ImageCategories – plus an empty row where you can add another category selecting it from a drop-down menu. You can modify existing entries the same way. I'll show how it's done.

Lower and Upper

LibreOffice Base connects each form to a table or query as data source. You can access the form's properties using the fourth button from the top, in Form in the left toolbar of the form editing view, on the Data tab (Figure 7). The wizard automatically creates the connection for the Images form.

Figure 7: Every form is bound to a data source (whether it's a query or a table). With embedded subforms, an entry mask still accesses multiple tables.

To include data from the Categories table in an entry form, you need to create a subform (i.e., a form within your main form) that connects with a data source independent from the main form. To create a subform, open the Form Navigator with the button in the bottom toolbar of the form editing view (Figure 8).

Figure 8: The Form Navigator shows an overview or the control elements in an entry mask. A right-click changes the settings or adds subforms.

In the Form Navigator, right-click MainForm , choose New | Form , and enter the name of your new subform. You can call your subform Categories . Right-clicking the Categories subform opens its properties, where you select Table as the Content type and the pivot table ImageCategories as the Content .

Now all the data fields in the subform access the ImageCategories table. At the moment, that's every single register in the table, but I'm interested only in the categories linked to a record opened in the main form. Open the subform's data properties and you should see the Link master fields and Link slave fields values.

Clicking the ellipses button (… ) after each of these properties opens the Link fields dialog, where you can choose the fields from the already defined ImageCategories and Images tables. Choose id for Link master fields and ImageId for Links slave fields . This selection restricts the subform to the rows in ImageCategories whose ImageId matches the id of the opened record in the main form.

Line by Line

Unlike the main form that displays only one record at a time, all the linked categories in the categories subform show up together. Instead of simple text fields, what you really need is a multi-line Table control element (Figure 9), like a drop-down menu. You can add this to the empty subform using the aforementioned More Controls tool. Be sure that the Categories subform is active in the Form Navigator, because only then will new control elements be created in the subform and have access to the data records.

Figure 9: A table control element shows all the records of a form or subform as table rows. Like ordinary forms, it recognizes different field types, such as the list box used in the example.

First, open up some space by minimizing the Comments field, then use the table control elements tool to open the Categories table, as in Figure 6. Cancel the wizard after it starts up. Then, right-click the table control element to set the Navigation bar and Record marker to No . The table control element shown in the figure appears but has no data fields in it.

Next, right-click the table header and choose Column… from the pop-up menu and create a List Box (that will show up as List Box 1 ) column. On the General tab, change the Title to Categories . On the Data tab, link the Data field to the column in ImageCategories that stores the linked categories, namely CategoryId .

You don't really want the CategoryId numerical key to appear in the list box, however. Instead of key integers such as 1 , 2 , or 3 , the category labels, such as "Portrait", "Landscape," and "Still Life" should be shown. Unfortunately, these category names don't appear in the ImageCategories table bound to the subform but in Categories instead.

That's why the List Box field type extracts the list items from an fixed data source, which is different from the Data Field that stores the list box fields, and which should have at least two fields per list entry (compare Figure 10, List Box Categories ). The first field of the data source is always made visible in the list. A different Linked Field provides the value that appears in the ImageCategories table after a selection. The Linked Field setting determines what the value is. The column count begins with zero.

Figure 10: The category mapping works as follows: A subform associates all the categories in the record via a link of id in Image with ImageId in Categories. A list box makes another internal link: It shows the names associated with the numerical CategoryId.

Not Asking Is Not Learning

If the order of fields in the Category table were Category then id , you could use the table directly for the contents of a list. However, the id is typically made the first field by convention.

To solve this problem, you need a simple query at this point to change the order of the fields. As a bonus, the query will sort the category names alphabetically.

Click Queries in the Base main window and choose Create Query in Design View . For the question of which table to use, choose Categories , click Add , and Close .

Next, all you need to do is drag the fields Category and id to two empty columns at the bottom of the Query Designer , so that Category is in the first columns and id is in the second. Then use Sorting with Category to sort ascending (ASC ) and save the query with the name CategoryList .

Go back to the form design (right-click and then Edit on the form). Right-click on the column header of the table in the subform and select Column… again to open the list box's properties.

Go to the Data tab and chose Query for Type of list of contents and the query CategoryList for List content . Type 1 in the Bound Field , that is, the second field CategoryList (i.e., the id column in the Categories table that we set up in the query above).

Think of CategoryList as a virtual table that reconstructs the contents of the Categories table. Queries are seldom as easy as that because most combine fields of many tables or filter specific records based on the Criterion setting in the query editor. One of these queries will be described in the second part of this workshop when programming a search function.

Off to New Shores

I'll recap the function of the category system in Figure 10. The main form has an open record in Images . Matching with its id , the subform selects records from the ImageCategories pivot table based on the link Image.id = ImageCategories.ImageId .

The table control element contains a list box row in the subform record that shows the category names matching the CategoryId from ImageCategories . You can also use it to add new CategoryId s.

Although the form wizard relieves you of some effort, the second part of this workshop will show you that Base, like MS Access, can't solve all problems without requiring some knowledge of the SQL database programming languages. Further details and practical examples are available as PDF and OpenDocument files in the official Base handbook [2], which goes much further than the online help for the product.