Creating a LibreOffice Music Database
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):
- Open LibreOffice Base and the Database Wizard appears
- Click Create a new database
- Click Next >
- Check the following options:
- Yes, register the database for me
- Open the database for editing
- Create tables using the table wizard
- Click Finish
- 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:
- 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 >.
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 >.
- 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 >.
- 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.
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:
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).
- Click on Sorting order: Sort in ascending order by Artist first, then Album, and then Song.
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.
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.)
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).
- 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.
« Previous 1 2 3 Next »
Buy this article as PDF
(incl. VAT)
Buy Linux Magazine
Subscribe to our Linux Newsletters
Find Linux and Open Source Jobs
Subscribe to our ADMIN Newsletters
Support Our Work
Linux Magazine content is made possible with support from readers like you. Please consider contributing when you’ve found an article to be beneficial.
![Learn More](https://www.linux-magazine.com/var/linux_magazin/storage/images/media/linux-magazine-eng-us/images/misc/learn-more/834592-1-eng-US/Learn-More_medium.png)
News
-
NVIDIA Released Driver for Upcoming NVIDIA 560 GPU for Linux
Not only has NVIDIA released the driver for its upcoming CPU series, it's the first release that defaults to using open-source GPU kernel modules.
-
OpenMandriva Lx 24.07 Released
If you’re into rolling release Linux distributions, OpenMandriva ROME has a new snapshot with a new kernel.
-
Kernel 6.10 Available for General Usage
Linus Torvalds has released the 6.10 kernel and it includes significant performance increases for Intel Core hybrid systems and more.
-
TUXEDO Computers Releases InfinityBook Pro 14 Gen9 Laptop
Sporting either AMD or Intel CPUs, the TUXEDO InfinityBook Pro 14 is an extremely compact, lightweight, sturdy powerhouse.
-
Google Extends Support for Linux Kernels Used for Android
Because the LTS Linux kernel releases are so important to Android, Google has decided to extend the support period beyond that offered by the kernel development team.
-
Linux Mint 22 Stable Delayed
If you're anxious about getting your hands on the stable release of Linux Mint 22, it looks as if you're going to have to wait a bit longer.
-
Nitrux 3.5.1 Available for Install
The latest version of the immutable, systemd-free distribution includes an updated kernel and NVIDIA driver.
-
Debian 12.6 Released with Plenty of Bug Fixes and Updates
The sixth update to Debian "Bookworm" is all about security mitigations and making adjustments for some "serious problems."
-
Canonical Offers 12-Year LTS for Open Source Docker Images
Canonical is expanding its LTS offering to reach beyond the DEB packages with a new distro-less Docker image.
-
Plasma Desktop 6.1 Released with Several Enhancements
If you're a fan of Plasma Desktop, you should be excited about this new point release.