THECODEPROGRAM

Now let's get started.

First we have to add sql library in our projects .pro file.

QT       += sql 

Now we can start to use QSqLite in our QT C++ program. To use QSQlite you can write your codes directly under your slots or you can ceate another C++ class and make callbacks from everywhere of your program. I suggest second way to decrease confisuon. Also write less code.

#include <QSqlDatabase>
#include <QSqlQueryModel>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlRecord>
#include <QSqlQueryModel>

private:
    QSqlDatabase m_db;

After including header files we will create a constructor method of the class, a destroyer method and an open database method. In constructor method we will set the database type and database file. When we calledback this class we will send the database file name in it. When we close the program to prevent some issues we will close the connection between database and our program.

I am also using the table and column names from structures. Maybe you will use these tabels and columns in many code blocks. Sometimes you may change the name of them. If you write them into everywhere maybe some confissuons take place. To prevent this I will write these names in a structure and I will fetch them from these structures.

    QString tableNamesMusicList = "tblMusicList";
    QString tableNamesSettings = "tblSettings";

    typedef struct {
         QString id = "id";
         QString current_index = "current_index";
         QString selected_index = "selected_index";
         QString repeatSingle = "repeatSingle";
         QString repeatAll = "repeatAll";
         QString shuffle = "shuffle";
         QString volume = "volume";
     } table_settings ;

    typedef struct{
        QString id = "id";
        QString name = "name";
        QString path = "path";
        QString lenghtAsSecond = "lenghtAsSecond";
        QString lenghtToShow = "lenghtToShow";
    } table_music;


    /**
     * @brief List of table and column names which creatted above
     */
    struct tablesAndColumns{

        table_settings _tableSettings;
        table_music _tableMusic;

    };

DbManager::DbManager(const QString &path)
{
    m_db = QSqlDatabase::addDatabase("QSQLITE");
    m_db.setDatabaseName(path);
    allTables = new tablesAndColumns();

    if (!m_db.open())
        qDebug() << "Error: connection with database fail";
    else
        qDebug() << "Database: connection ok";
}

DbManager::~DbManager()
{
    if (m_db.isOpen())
        m_db.close();
}

bool DbManager::isOpen() const
{
    return m_db.isOpen();
}

These are required function to start and stop our QSqlite and now we need functions which accomodate main purpose of sql. So we need functions to create table, insert record, select record, delete and update record. Now we will write these methods one by one.

As I mentioned above I will use the stuctures to use table and column names. I Will not write the names directly into query. Below code block will create a table which named as variable tableName and related column names.

bool DbManager::createMusicListTable(const QString &tableName)
{
    bool success = true;
    QSqlQuery query;
    query.prepare("CREATE TABLE " + tableName + "("
                  + allTables->_tableMusic.id       + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                  + allTables->_tableMusic.name     + " TEXT, "
                  + allTables->_tableMusic.path     + " TEXT,"
                  + allTables->_tableMusic.lenghtAsSecond   + " INTEGER "
                  + allTables->_tableMusic.lenghtToShow + " TEXT );");
    if (!query.exec())
    {
        qDebug() << "Music list table already created ";
        success = false;
    }
    return success;
}

bool DbManager::insertMusic(const QString tableName,const QString &name, const QString &path, const int &lenghtAsSecond, QString lenghtToShow)
{
    bool success = false;
    if (!name.isEmpty())
    {
        QSqlQuery queryAdd;
        queryAdd.prepare("INSERT INTO " + tableName + " (name,path,lenghtAsSecond,lenghtToShow) VALUES (:name,:path,:lenghtAsSecond,:lenghtToShow)");
        queryAdd.bindValue(":name", name);
        queryAdd.bindValue(":path", path);
        queryAdd.bindValue(":lenghtAsSecond", lenghtAsSecond);
        queryAdd.bindValue(":lenghtToShow", lenghtToShow);

        if(queryAdd.exec())
            success = true;
        else
            qDebug() << "record could not add: " << queryAdd.lastError();
    }
    else
        qDebug() << "Data is required to insert.";

    return success;
}

bool DbManager::removeMusic(const int &musicId)
{
    bool success = false;

    if (musicId > -1)
    {
        QSqlQuery queryDelete;
        queryDelete.prepare("DELETE FROM " + tableNamesMusicList + " WHERE id=" + QString::number(musicId) );
        success = queryDelete.exec();

        if(!success)
            qDebug() << "Record has been deleted: " << queryDelete.lastError();
    }
    else
        qDebug() << "Record has not been deleted";
    return success;
}

bool DbManager::updateMusicDuration(const int &musicId , const int &lenghtAsSecond , QString lenghtToShow )
{
    bool success = false;

    if (musicId > -1)
    {
        QString query_str = "UPDATE " + tableNamesMusicList +
                " SET "
                + "lenghtAsSecond= " + QString::number(lenghtAsSecond) + " , "
                + "lenghtToShow='"+ lenghtToShow + "' "
                + "where id=" + QString::number(musicId);

        QSqlQuery queryAdd;
        queryAdd.prepare(query_str);

        if(queryAdd.exec())
            success = true;
        else
            qDebug() << "Music could not be added: " << queryAdd.lastError();
    }
    else
        qDebug() << "Music ID is required to update...";

    return success;
}

Now our codes are ready in our database manger class. Now we will make callbacks from our main window class. Now let's write some codes.

#include <dbmanager.h>

static const QString path = "TheMusicDataBase.db";
DbManager db(path);

    if (db.isOpen())
    {
        //if not exist create the database tables.
        db.createMusicListTable(db.tableNamesMusicList);
        db.createSettingsTable(db.tableNamesSettings);
    }
    else
    {
        qDebug() << "Database could not be opened!";
    }

db.updateMusicDuration( music->id, position /1000, sarki_sure_getir( position/1000 )  );

void MainWindow::keyReleaseEvent(QKeyEvent* ke)
{
    switch (ke->key()) {
    case(Qt::Key_Delete):
        if(_activeSettings.selected_index > -1){
            db.removeMusic(_activeSettings.selected_id);
        }
        break;
    }
}

db.insertMusic(db.tableNamesMusicList,fileInfo.fileName(),_activeSettings.dosya, 0, "00:00");

That is all in this article.

You can also reach the example program with this link.

Have a nice coding.

Burak Hamdi TUFAN.