Kadio: Adding permanence
As of the last post, we have a working web radio player, where we can add stations by URL, but without persistence. In this post, we’re going to add the ability to load stations from, and store station in a database, using Qt’s SQL capability.
This isn’t that difficult so this’ll be a short one.
Database Layer
As with JSON, Qt has a suite of classes to handle database connections and queries.
The way to do this is by initializing a database connection with the appropriate driver, using QSqlDatabase::addDatabase(), and credentials, using setDatabaseName(), setHostName(), setUserName(), setPassword(), and setPort(), depending on the driver.
This database connection is stateful, but opaque to the program — you can only access it via QSqlDatabase::database() (so you don’t store any handle to it in your program). If you have more than one connection, you can differentiate between them using the connectionName you’ve passed to addDatabase() as an argument.
Since we only need one connection, we don’t need to specify the name.
Before we start, we need to update cmake.
CMakeLists.txt:
1# Find the QtWidgets library
2find_package(Qt5 CONFIG REQUIRED COMPONENTS
3 [...]
4 Sql
5)
6[...]
7# Use the Widgets module from Qt 5.
8target_link_libraries(kadio
9 [...]
10 Qt5::Sql
11 [...]
12)
Initializing db and schema
Although I don’t expect Kadio to grow to be a giant program, it’s still a good idea to separate the data layer from the rest of the program. This means we should put all interactions with the database in a separate class, and we’ll use this class when we need to query the database from elsewhere in the program.
Since we want to be able to access the database from everywhere, and always the same database instance, we’ll implement it as a singleton.
database.h:
1class KadioDatabase
2{
3
4public:
5 [[nodiscard]] static KadioDatabase& instance()
6 {
7 static KadioDatabase instance;
8 return instance;
9 }
10
11private:
12 KadioDatabase();
13 ~KadioDatabase();
14 KadioDatabase(const KadioDatabase&) = delete;
15 KadioDatabase(KadioDatabase&&) = delete;
16};
Although just having the constructors be private is enough to control when we create KadioDatabase objects, we should go the extra step and delete the unused copy and move constructors.
As mentioned before, we need to add a database. We’ll use SQLite driver since that’s perfect for our use case.
When using SQLite, we don’t need to specify any database options except the filename to the local sqlite3 file; we can set this with setDatabaseName(). We want to store our database in a standard location, which, for Linux, is $HOME/.local/share/kadio.
To get a standard directory, we use QStandardPaths::writableLocation() and pass in the directory’s type (cache, temp, config, etc.); in our case this is QStandardPaths::AppLocalDataLocation.
database.cpp:
1KadioDatabase::KadioDatabase()
2{
3 QSqlDatabase db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"));
4 QString file_name;
5 QTextStream(&file_name) << QStandardPaths::writableLocation(QStandardPaths::AppDataLocation)
6 << QStringLiteral("/kadio.db");
7 db.setDatabaseName(file_name);
8 if (! db.open()) {
9 auto err = db.lastError();
10 qDebug << err.text();
11 exit(1);
12 }
13 createStations();
14}
For the destructor, we simply have to close the database.
1KadioDatabase::~KadioDatabase()
2{
3 QSqlDatabase::database().close();
4}
We should create the main table when creating the database for the first time. Since we may need to set up the main table more than once in the program (e.g., if the user imports a new playlist), we factor this to a new function, createStations().
The table, Stations, should have the station’s title and URL, but we can add a few more columns: we can add an image, a description for the station, the station’s country, and the date it was added to Kadio. Radio-browser’s stations have more metadata, and we’ll add more to the schema once we connect directly to radio-browser, but this selection is a good start.
database.cpp:
1bool KadioDatabase::createStations()
2{
3 QSqlQuery query;
4 query.prepare(QStringLiteral(
5 "CREATE TABLE IF NOT EXISTS Stations"
6 "(id INTEGER PRIMARY KEY"
7 ",title TEXT NOT NULL"
8 ",url TEXT NOT NULL"
9 ",image_url TEXT"
10 ",description TEXT"
11 ",date_added TEXT DEFAULT (datetime('now'))"
12 ",country TEXT) STRICT"
13 ));
14 return execute(query);
15}
Sqlite doesn’t respect table types by default, which nullifies a big advantage of using a static language like SQL, so we’ll make the table be STRICT.
Running queries
Whenever we want to send a new query to the database, we should implement it as a method in this class — we don’t want any SQL anywhere else in the program.
Although we can run queries directly on the database (QSqlDatabase::exec(), although Qt has deprecated this), let’s standardize the way we call the database: we’re always going to create a QSqlQuery object (empty; if you give it a QString it runs the query automatically), call prepare() with the query, and then execute it.
When constructing queries, we can pass in the database under which to execute it. If we don’t pass anything, the default database is used (i.e., the connection we created in addDatabase by not passing any name to it; if we had passed a name then we would have to specify it in the query’s constructor also).
Queries are called with exec() but, since they can fail, let’s define the function execute() in KadioDatabase that executes a query
and handles errors if they appear, returning true if the query was successful. For now, we just output the error to qDebug.
database.cpp:
1bool KadioDatabase::execute(QSqlQuery& query)
2{
3 if (! query.exec()) {
4 auto err = query.lastError();
5 qDebug() << err.text();
6 return false;
7 }
8 return true;
9}
Add station
The query to add new stations is pretty simple:
database.cpp:
1bool KadioDatabase::addStation(const QString& title, const QString& url)
2{
3 QSqlQuery query;
4 query.prepare(QStringLiteral("INSERT INTO Stations(title, url) VALUES (:title, :url)"));
5 query.bindValue(QStringLiteral(":title"), title);
6 query.bindValue(QStringLiteral(":url"), url);
7 return execute(query);
8}
We use this in two occasions: when we add a new station:
kadio.cpp:
1void kadio::addNewStation()
2{
3 bool ok = false;
4 QString url_string = QInputDialog::getText(this, "Add station", "Web radio URL:", QLineEdit::Normal, "https://", &ok);
5 QUrl url(url_string);
6 if (ok && url.isValid() && (url.scheme() == "http" || url.scheme() == "https")) {
7 auto list_item = new StationListItem(url_string, url);
8 left_pane->layout()->addWidget(list_item);
9 connect(list_item, &StationListItem::labelClicked, this, &kadio::changeTrack);
10 KadioDatabase::instance().addStation(url_string, url_string);
11 changeTrack(list_item);
12 }
13}And when we import stations from a file:
kadio.cpp:
1void kadio::importStations()
2{
3 // [... read export file ...]
4 for (auto entry_value : items) {
5 QJsonObject entry = entry_value.toObject();
6 QString title = entry["title"].toString();
7 QString url = entry["url"].toString();
8
9 auto list_item = new StationListItem(title, url);
10 left_pane->layout()->addWidget(list_item);
11 connect(list_item, &StationListItem::labelClicked, this, &kadio::changeTrack);
12 KadioDatabase::instance().addStation(title, url);
13 }
14 this->statusBar()->showMessage(QStringLiteral("Successfully imported %1 stations").arg(items.size()), 3000);
15}Clearing stations
To clear the stations, we just have to drop the Stations table and then create it again.
database.cpp:
1bool KadioDatabase::clearStations()
2{
3 QSqlQuery query;
4 query.prepare(QStringLiteral("DROP TABLE Stations"));
5 if (! execute(query)) {
6 return false;
7 }
8 return createStations();
9}
We use this when we import stations from a file, to reset the database. Of course, the import procedure could merge the imported stations with the existing stations but, for now, this is a simple way of implementing it. We’ll add this feature later, asking the user which behaviour is wanted.
kadio.cpp:
1void kadio::importStations()
2{
3 // [... read export file ...]
4 auto labels = left_pane->findChildren<StationListItem*>();
5 for (auto label_pointer : labels) {
6 delete label_pointer;
7 }
8 KadioDatabase::instance().clearStations();
9 // [... create stations ...]
10}Initializing station list
This is the one case where our database layer abstraction breaks down slightly. To initialize the station list, we have to query the database and access the results; we shouldn’t access them in the database layer (since that’s mixing business and data logic), so we have to access them in kadio.cpp. But this means we have to return the SQL query object to a part of the program that shouldn’t have to care about the underlying method to query the database.
database.cpp:
1QSqlQuery KadioDatabase::selectStationTitleUrl()
2{
3 QSqlQuery query;
4 query.prepare(QStringLiteral("SELECT title, url FROM Stations"));
5 execute(query);
6 return query;
7}
We can just return the query object: if we look at QSqlQuery’s source code, we see that the class is written with a pImpl pattern, so copying it is just copying a pointer.
Of course, reading Qt’s documentation would have told us the same thing.
Let’s remove the old station load code:
kadio.cpp:
1kadio::kadio(const QVector<QString>& words, QWidget *parent) :
2 KXmlGuiWindow(parent)
3{
4 // [...]
5 // for (const QString& filename : words) {
6 // auto line = new StationListItem(filename, QUrl::fromLocalFile(filename));
7 // connect(line, &StationListItem::labelClicked, this, &kadio::changeTrack);
8 // left_pane_layout->addWidget(line);
9 // }
10 // [...]
11}
And also the passed-in words vector in the constructor:
kadio.cpp:
1// kadio::kadio(const QVector<QString>& words, QWidget *parent) :
2kadio::kadio(QWidget *parent) :
3 KXmlGuiWindow(parent)
4{
5 // [... constructor ...]
6}
We’ll delete the line setting an initial media for mediaplayer. Since we don’t have the words vector anymore, and we’re not sure to have any station in the database, it makes no sense to have mediaplayer pre-loaded. If anything, we’d pre-load the mediaplayer object with the station that the user was last listening to, but that involves a bit more work, so we’ll leave it for later. For the same reason, we’ll start the status bar with an empty QLabel.
kadio.cpp:
1kadio::kadio(QWidget *parent) :
2 KXmlGuiWindow(parent)
3{
4 // [... rest of setup ...]
5 mediaplayer = new QMediaPlayer(this);
6 // mediaplayer->setMedia(QUrl::fromLocalFile(words.first()));
7
8 QStatusBar* status_bar = new QStatusBar(window);
9 // status_bar->addPermanentWidget(new QLabel(mediaplayer->media().request().url().url()));
10 status_bar->addPermanentWidget(new QLabel);
11 this->setStatusBar(status_bar);
12
13 this->setupGUI(Default, "kadioui.rc");
14}
We’ll also remove the file reading code in main.cpp:
main.cpp:
1int main(int argc, char *argv[])
2{
3 // [... about data setup ...]
4 // QFile words_file("test-file");
5 // if (! words_file.open(QIODevice::ReadOnly | QIODevice::Text)) {
6 // return 0;
7 // }
8
9 // QTextStream words_file_stream(&words_file);
10 // QVector<QString> words;
11 // while (! words_file_stream.atEnd()) {
12 // words.append(words_file_stream.readLine());
13 // }
14
15 // kadio* w = new kadio(words);
16 kadio* w = new kadio;
17 w->show();
18 return app.exec();
19}
After clearing all the old code, the initial loading of stations is simple:
kadio.cpp:
1kadio::kadio(QWidget *parent) :
2 KXmlGuiWindow(parent)
3{
4 // [ ... widget setup ...]
5 QSqlQuery q = KadioDatabase::instance().selectStationTitleUrl();
6 while (q.next()) {
7 auto line = new StationListItem(q.value("title").toString(), q.value("url").toUrl());
8 connect(line, &StationListItem::labelClicked, this, &kadio::changeTrack);
9 left_pane_layout->addWidget(line);
10 }
11 // [... rest of setup ...]
12}Conclusion
So we now have a web radio player with persistence. We still can’t remove stations though, and have little to no error checking, and we can’t change stations’ titles, and and and…
We have a lot of work accumulated from this and other posts (too many “we’ll add it later”), so for the next post we should start paying some of this “technical debt” before we start working on the radio-browser integration.
As of now, our git repository looks like this.