Simple Library Database Manager (SQLite + QSqlTableModel + QSqlQueryModel)

[c918]

Simple Library Database Manager (SQLite + QSqlTableModel + QSqlQueryModel)
Hey everyone,
I'd like to share a small project I built as a learning exercise -- a library (knihovna) database manager using Qt's SQL module with SQLite. It demonstrates switching between QSqlTableModel for direct CRUD operations and QSqlQueryModel for custom SELECT queries, all inside a single QMainWindow.
What it does
The app manages a simple two-table schema: authors and books with a foreign key relationship. The user can:

Switch between the autor and kniha tables via radio buttons, with each view backed by a QSqlTableModel using OnFieldChange edit strategy for immediate in-place editing.
Write arbitrary SQL queries in a dockable QTextEdit panel and execute them through a QSqlQueryModel.
Filter any view in real time -- typing in a QLineEdit builds a CAST(column AS TEXT) LIKE '%...%' filter. For the table model this uses setFilter(), for the query model it wraps the original query as a subselect to preserve existing JOINs and WHERE clauses.
Add and delete rows directly.
Optionally bulk-import authors from a CSV-like text file.

A few things I found interesting while building this
Filtering on QSqlQueryModel vs QSqlTableModel. QSqlTableModel::setFilter() handles the simple case nicely, but QSqlQueryModel has no built-in filter mechanism. My approach was to store the original SELECT query and wrap it: SELECT * FROM (originalQuery) WHERE filterCondition. This keeps the user's original query intact regardless of its complexity.
CAST for universal LIKE filtering. Using CAST(columnName AS TEXT) means the same filter logic works on both text and numeric columns without having to check types.
Combo box populated from model columns. When the user switches to a custom SELECT view, the filter combo box is rebuilt dynamically using QSqlQueryModel::headerData(), so it always matches whatever columns the query returns.
Tech stack

Qt 5/6 (Widgets + SQL modules)
C++17
SQLite via QSQLITE driver
Qt Designer for the UI layout

Full source
Databaze.pro
iniQT += core gui sql

greaterThan(QT_MAJOR_VERSION, 4): QT += widgets

CONFIG += c++17

SOURCES +=
main.cpp
mainwindow.cpp

HEADERS +=
mainwindow.h

FORMS +=
mainwindow.ui

qnx: target.path = /tmp/$${TARGET}/bin
else: unix:!android: target.path = /opt/$${TARGET}/bin
!isEmpty(target.path): INSTALLS += target
main.cpp
cpp#include "mainwindow.h"

#include <QApplication>

int main(int argc, char *argv[])
{
QApplication a(argc, argv);
MainWindow w;
w.show();
return a.exec();
}
mainwindow.h
cpp#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>

class QSqlTableModel;
class QSqlQueryModel;

QT_BEGIN_NAMESPACE
namespace Ui {
class MainWindow;
}
QT_END_NAMESPACE

class MainWindow : public QMainWindow
{
Q_OBJECT

public:
MainWindow(QWidget *parent = nullptr);
~MainWindow();
void fillDbFromFile();

public slots:
void onPridejRadek();
void onSmazRadek();

void onRadioAutor();
void onRadioKniha();
void onRadioSelect();

void onLineEditFiltr();

private:
Ui::MainWindow *ui;
QSqlTableModel *mTableModel;
QSqlQueryModel *mQueryModel;
QString mSelectDotaz;

void openDatabase();
void addLineToDb(QString& line);

void setTabulka(QString name);

};
#endif // MAINWINDOW_H
mainwindow.cpp
cpp#include "mainwindow.h"
#include "ui_mainwindow.h"

#include <QFile>
#include <QSqlDatabase>
#include <QSqlTableModel>
#include <QSqlQuery>
#include <QSqlError>

MainWindow::MainWindow(QWidget *parent)
: QMainWindow(parent)
, ui(new Ui::MainWindow)
{
ui->setupUi(this);
openDatabase();

connect(ui->buttonPridejRadek, &QPushButton::clicked,
        this, &MainWindow::onPridejRadek);
connect(ui->buttonSmazRadek, &QPushButton::clicked,
        this, &MainWindow::onSmazRadek);

connect(ui->radioAutor, &QRadioButton::toggled,
        this, &MainWindow::onRadioAutor);
connect(ui->radioKniha, &QRadioButton::clicked,
        this, &MainWindow::onRadioKniha);
connect(ui->radioSelect, &QRadioButton::clicked,
        this, &MainWindow::onRadioSelect);

connect(ui->lineEditFiltr, &QLineEdit::textChanged,
        this, &MainWindow::onLineEditFiltr);

}

MainWindow::~MainWindow()
{
delete mTableModel;
delete mQueryModel;
delete ui;
}

void MainWindow::openDatabase()
{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("knihovna3.db");
if (db.open()) {
qDebug() << "uspech";
} else {
qDebug() << "nepodarilo se otevrit databazi";
}

QSqlQuery dotaz;
dotaz.exec(" CREATE TABLE IF NOT EXISTS autor(\
               id INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT, \
               jmeno  TEXT\
               )");

dotaz.exec("CREATE TABLE IF NOT EXISTS kniha(\
    id INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT, \
    jmeno   TEXT, \
    datumVydani TEXT,\
    casVydani TEXT,\
    autorId INTEGER REFERENCES autor(id)\
    )");

mQueryModel = new QSqlQueryModel;
mTableModel = new QSqlTableModel;
setTabulka("autor");

//  fillDbFromFile();

ui->radioAutor->setChecked(true);

}

void MainWindow::fillDbFromFile()
{
QFile file("autor.txt");
if (!file.open(QIODevice::ReadOnly | QIODevice::Text)) {
qDebug() << "nepodarilo se otevrit soubor";
return;
}

while (!file.atEnd()) {
    QString line = file.readLine();
    addLineToDb(line);
}

}

void MainWindow::addLineToDb(QString &line)
{
QList<QString> radek = line.split(",");
int indexRow = mTableModel->rowCount();
mTableModel->insertRow(indexRow);
mTableModel->setData(mTableModel->index(indexRow, 0), radek[0]);
mTableModel->setData(mTableModel->index(indexRow, 1), radek[1]);
mTableModel->submitAll();
mTableModel->select();
}

void MainWindow::onPridejRadek()
{
mTableModel->insertRow(mTableModel->rowCount());
mTableModel->submitAll();
}

void MainWindow::onSmazRadek()
{
mTableModel->removeRow(ui->tableView->currentIndex().row());
mTableModel->submitAll();
}

void MainWindow::onRadioAutor()
{
setTabulka("autor");
ui->comboFiltr->clear();
ui->comboFiltr->addItem("jmeno");
}

void MainWindow::onRadioKniha()
{
setTabulka("kniha");
ui->comboFiltr->clear();
ui->comboFiltr->addItem("jmeno");
ui->comboFiltr->addItem("datumVydani");
ui->comboFiltr->addItem("casVydani");
ui->comboFiltr->addItem("autorId");
}

void MainWindow::onRadioSelect()
{
QString dotaz = ui->textEdit->toPlainText();
if (dotaz.isEmpty()) {
dotaz = "SELECT "
"kniha.id AS kniha_id, "
"kniha.jmeno AS kniha_nazev, "
"kniha.datumVydani, "
"kniha.casVydani, "
"autor.id AS autor_id, "
"autor.jmeno AS autor_meno "
"FROM kniha, autor";
}
mSelectDotaz = dotaz;

mQueryModel->setQuery(dotaz);
ui->tableView->setModel(mQueryModel);
ui->tableView->showColumn(0);

if (mQueryModel->lastError().isValid()) {
    qDebug() << "Chyba SQL:" << mQueryModel->lastError().text();
}

ui->comboFiltr->clear();
for (int i = 0; i < mQueryModel->columnCount(); ++i) {
    QString columnName = mQueryModel->headerData(i, Qt::Horizontal).toString();
     ui->comboFiltr->addItem(columnName);
}

}

void MainWindow::onLineEditFiltr()
{
QString filtr = "CAST(" + ui->comboFiltr->currentText() + " AS TEXT)"
" LIKE '%" +
ui->lineEditFiltr->text() + "%'";
qDebug() << filtr;

if(ui->radioSelect->isChecked()) {
    if (!mSelectDotaz.isEmpty()) {
        QString selectDotazSFiltrom = QString("SELECT * FROM (%1) WHERE %2")
                                .arg(mSelectDotaz)
                                .arg(filtr);

        mQueryModel->setQuery(selectDotazSFiltrom);

        if (mQueryModel->lastError().isValid()) {
            qDebug() << "Chyba SQL:" << mQueryModel->lastError().text();
        }
    }
}
else{
    mTableModel->setFilter(filtr);
    mTableModel->select();
}

}

void MainWindow::setTabulka(QString name)
{
mTableModel->setTable(name);
mTableModel->setEditStrategy(QSqlTableModel::OnFieldChange);
mTableModel->select();

ui->tableView->setModel(mTableModel);
ui->tableView->hideColumn(0);

ui->tableView->update();

}
mainwindow.ui
xml<?xml version="1.0" encoding="UTF-8"?>
<ui version="4.0">
<class>MainWindow</class>
<widget class="QMainWindow" name="MainWindow">
<property name="geometry">
<rect>
<x>0</x>
<y>0</y>
<width>800</width>
<height>600</height>
</rect>
</property>
<property name="windowTitle">
<string>MainWindow</string>
</property>
<widget class="QWidget" name="centralwidget">
<layout class="QGridLayout" name="gridLayout_3">
<item row="0" column="1">
<widget class="QRadioButton" name="radioKniha">
<property name="text">
<string>Kniha</string>
</property>
</widget>
</item>
<item row="3" column="1">
<widget class="QPushButton" name="buttonSmazRadek">
<property name="text">
<string>Smaz radek</string>
</property>
</widget>
</item>
<item row="2" column="0" colspan="3">
<widget class="QTableView" name="tableView"/>
</item>
<item row="0" column="2">
<widget class="QRadioButton" name="radioSelect">
<property name="text">
<string>Select</string>
</property>
</widget>
</item>
<item row="0" column="0">
<widget class="QRadioButton" name="radioAutor">
<property name="text">
<string>Autor</string>
</property>
</widget>
</item>
<item row="3" column="0">
<widget class="QPushButton" name="buttonPridejRadek">
<property name="text">
<string>Pridej radek</string>
</property>
</widget>
</item>
<item row="1" column="0">
<widget class="QLabel" name="label">
<property name="text">
<string>Filtr</string>
</property>
</widget>
</item>
<item row="1" column="2">
<widget class="QComboBox" name="comboFiltr"/>
</item>
<item row="1" column="1">
<widget class="QLineEdit" name="lineEditFiltr"/>
</item>
</layout>
</widget>
<widget class="QMenuBar" name="menubar">
<property name="geometry">
<rect>
<x>0</x>
<y>0</y>
<width>800</width>
<height>25</height>
</rect>
</property>
</widget>
<widget class="QStatusBar" name="statusbar"/>
<widget class="QDockWidget" name="dockWidget">
<attribute name="dockWidgetArea">
<number>1</number>
</attribute>
<widget class="QWidget" name="dockWidgetContents">
<layout class="QGridLayout" name="gridLayout_2">
<item row="0" column="0">
<widget class="QTextEdit" name="textEdit"/>
</item>
<item row="1" column="0">
<spacer name="verticalSpacer">
<property name="orientation">
<enum>Qt::Orientation::Vertical</enum>
</property>
<property name="sizeHint" stdset="0">
<size>
<width>20</width>
<height>40</height>
</size>
</property>
</spacer>
</item>
</layout>
</widget>
</widget>
</widget>
<resources/>
<connections/>
</ui>
What I'd improve next

Add input validation and proper error dialogs instead of qDebug() messages.
Parameterize SQL queries to avoid injection in the filter (currently using string concatenation with LIKE).
Add a proper relational view using QSqlRelationalTableModel to show author names instead of raw IDs in the book table.
Persist column widths and dock widget state with QSettings.

Feedback welcome -- especially on better patterns for combining QSqlTableModel and QSqlQueryModel in the same view.

[c918]

Update -- v2 changes:
Small iteration on the code above. Three things changed:

fillDbFromFile() is now called automatically on database open instead of being commented out. The input file was renamed from autor.txt to in.txt.
addLineToDb() is currently a stub -- it only prints the line to debug output (qDebug() << line;) instead of inserting into the model. This makes it easier to verify your input file format before wiring up the actual insert logic.
The default SELECT query now has a proper JOIN condition active: WHERE kniha.autorId = autor.id. In v1 this was commented out, which produced a cartesian product of both tables.

Updated openDatabase():
cppif (db.open()) {
qDebug() << "uspech";
fillDbFromFile(); // now called on startup
}
Updated addLineToDb():
cppvoid MainWindow::addLineToDb(QString &line)
{
qDebug() << line; // stub -- plug in your insert logic here
}
Updated default query in onRadioSelect():
cppdotaz = "SELECT "
"kniha.id AS kniha_id, "
"kniha.jmeno AS kniha_nazev, "
"kniha.datumVydani, "
"kniha.casVydani, "
"autor.id AS autor_id, "
"autor.jmeno AS autor_meno "
"FROM kniha, autor "
"WHERE kniha.autorId = autor.id"; // JOIN is now active
Everything else (header, UI, .pro) stays the same.

[JonB]

@c918
You have made various posts full of large blobs of code. If you expect them to be readable/useful to others you really need to put code blocks inside the forum's Code tags --- the </> button or a line of ``` (3 backticks) above and below a code block.