LibreOffice Base Database

LibreOffice makes use of wizards in most of its applications. I find that wizards are very helpful in LibreOffice Base, especially for users that are not expert in the SQL language. The first step is to create a new database. While LibreOffice Base is capable of importing data directly from a spreadsheet, I prefer to copy and paste the table data from Calc into a predefined template in Base using a wizard.

Create New Database

Next I create and register a new database as follows (Figure 4):

Figure 4: Options for saving a database.

1. Open LibreOffice Base and the Database Wizard appears
2. Click Create a new database
3. Click Next >
4. Check the following options:

• Yes, register the database for me
• Open the database for editing
• Create tables using the table wizard

1. Click Finish
2. Click Save As MusicLibrary.odb

Note: Registering the database allows the database to be used by other LibreOffice components such as Writer. It is not required. You can use it at your own discretion.

Next, the Table Wizard (Figure 5) opens, prompting you to do the following steps:

Figure 5: The Table Wizard shows the steps for setting up a table.

1. Click on Select fields under Steps. Select Personal under Category. Then select CD-Collection from Sample tables drop-down list. Next, select and move the following fields from Available fields to Selected fields: Producer, Artist, AlbumTitle, Format, NumberofTracks, ReleaseYear. (Note: Some of these field names will be modified.) Finally, click Next >.

2. Click on Set types and formats. First, change the Field name but keep the corresponding Field type. For the Song, Artist, and Album fields, set Length to 150 (Figure 6). Next change the Field name of the following fields: Producer to Song, AlbumTitle to Album, Format to Genre, NumberofTracks to Track, and ReleaseYear to Year. Then, click Next >.

   

   Figure 10: Assigning aliases.
3. Click on Set the primary key. Check the Create a primary key box if not marked already. Select options: Create a primary key, Automatically add a primary key, and Auto value. Click Next >.
4. Click on Create a table. First change the name to MyTable, and then select the option Insert data immediately. Click Finish.

(The Table Wizard now closes and Table Data View opens. Confirm column labels and close the window. The LibreOffice Base window appears with Database, Tasks, and Tables sections visible.)

Once the table is created in Base, open the Music.txt spreadsheet in LibreOffice Calc. Select and copy all data cells from A1 through the last cell (that includes column label cells).

Now, switch back to Base to paste the copied table data into MyTable in Base.

In the Tables section, select MyTable. Right-click and choose paste from the drop-down menu (the Copy table dialog opens, see Figure 7). Select Append data, check Use first line as column names, and click Next > (which will take you to the Assign columns dialog). Review to confirm that the Source table data aligns with the destination table labels, and then click Create.

Figure 7: Copying a table from Calc into Base.

Double-click MyTable to open the Table Data View dialog and confirm that the data copied correctly. Set the width of the columns (the width of the query result field is determined by the width of the table fields.) Uncheck Automatic.

Set Up and Run Query

I now have the database ready to run SQL queries to find songs, artists, and album information from my music library. The next step is to set up and run queries on the database.

LibreOffice Base provides three methods to create SQL queries: Create Query in Design View, Use Wizard to Create Query, or Create Query in SQL View. I use the Use Wizard to Create Query method to set up and run a query. As an example, I first set up a query search for all songs by a particular artist. Then, I use the Standard Filter on the query results to refine my searches.

To begin, click on the Queries icon in the Database section. Then click on Use Wizard to Create Query, and complete the following steps in the Query Wizard:

1. Click on Field selection in the Query Wizard: Select Song, Artist, and Album in the Available fields window, and then move them to the Fields in the Query window. Use the up and down arrows to change the order if necessary (Figure 8).

   

   Figure 10: Assigning aliases.
2. Click on Sorting order: Sort in ascending order by Artist first, then Album, and then Song.

3. Click on Search conditions: Select MyTable.Artist in the Fields drop-down list (Figure 9). Select like from the Condition drop-down list and enter %Herbie H% in the Value field.

   

   Figure 10: Assigning aliases.

Note: In SQL, the % sign is used as a wildcard character. The like condition matches string patterns. The SQL view shows the SQL statement as:

SELECT "Artist" AS "Artist", "Song" FROM "MyTable" WHERE "Artist"LIKE '%Herbie H%'

(Skip the Grouping and Grouping conditions steps in the Query Wizard because grouping will not be used for this project.)

1. Click on Aliases (listed at Step 7 in the Query Wizard). The alias for MyTable.Artist defaults to Artist, and the alias for MyTable.Song defaults to Song. I use the defaults (as shown in Figure 10).

   

   Figure 10: Assigning aliases.
2. Click on Overview. In the Name of the query field, I change the default name Query_MyTable to Query_ArtistsAndSongs. Next review the contents in the Overview window. Use the default Display Query option.