Getting started with LibreOffice Base

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.