Colored rows

[marcpley]

For the data-only-mode, ability to color rows according to the row's data

Priority to set cell color
1 selected cell -> selected cell color
2 same row of selected cell -> selected row color (#5078)
3 according to row's data and plugin parameters -> plugin parametred color
4 otherwise -> color depending of the column data type (#5078)

The row's colors parameter could be : if there is a field named XXX (ex: "Color") in the table/view, use the color code stored in (ex: #BF4040) to set the color of the row and hide the XXX column.

[pawelsalawa]

I just curious - wouldn't it be easier for you just to create a very simple Qt application for this "data only" operations? I mean Qt has very simple framework for dealing with databases (i.e. https://doc.qt.io/qt-6/qsqltablemodel.html and it's "friends"). Writing simple grid for table data editing would be very easy and you are free to manipulate your grid as you like. In SQLiteStudio you will be limited in several ways, because - as already discussed - SQLiteStudio must maintain its primary target use case.

You are basically trying to take a Jet Plane, remove its jet engines, remove wings, make it shorter, change its wheels, add bigger windows, add diesel engine and to start using it as a urban car. With huge effort it's possible, but... why?

I can already tell it will be much harder for you to achieve all your goals by trying to alter behavior of SQLiteStudio, instead of creating simple Qt app for that (btw. I can guide you with that too - as I wrote in my bio I received a lot of help from Open Source community back in the days and I can help others, who show constructive attitude).

To be clear - I really appreciate all the input and contribution you've done to the project so far and I will still guide you if you continue going this path. I'm just giving you an advise, because I can see where this is going and how many problems you will face - instead of going the easier path.

[marcpley]

Thanks very much for this advice. Developing a specific app was and still is an option but I can't evaluate like you what is the good choice. I will look this framework :-)

[pawelsalawa]

If you want to give it a try, start with QtCreator "new project" wizard, create some basic UI project with QMainWindow. Then use UI Designer for this main window to add QTableView and then add QSqlTableModel, like in example at https://doc.qt.io/qt-6/qsqltablemodel.html see how that works for you and if you have further questions.

[marcpley]

I create the project and add QTableView but I don't find QSqlTableModel in the list:

Something is messing in my Qt ? Or do I have to declare manually and where ?

[pawelsalawa]

I will reply ina day or two. I cought flu and it's peeking now... czw., 12 gru 2024, 22:11 użytkownik marcpley ***@***.***> napisał:

…

I create the project and add QTableView but I don't find QSqlTableModel in the list: Capture.d.ecran.du.2024-12-12.22-06-09.png (view on web) <https://github.com/user-attachments/assets/8410cf6e-66af-4bed-aee6-92d9c3232f20> Something is messing in my Qt ? Or do I have to declare manually and where ? — Reply to this email directly, view it on GitHub <#5163 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AADVUNVSOTNB5T7YSHOUM732FH3WVAVCNFSM6AAAAABTMDFBTKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDKNBQGAZDAMRYGU> . You are receiving this because you commented.Message ID: ***@***.***>

[pawelsalawa]

The model will not be in the palette. Palette is all about what you can see, while Q*Model is what data and how to pull form database and provide it to the view. If you go to https://doc.qt.io/qt-6/qsqltablemodel.html and scroll to like 15-20% of the page you will see example.

[pawelsalawa]

I will add here few more hints:

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("example.db");

This sets global "default" connection, which you can later get anywhere in the app by calling:

QSqlDatabase db = QSqlDatabase::database();

Generally, once you addDatabase() you can use QSqlQueryModel or QSqlTableModel, because they will use this default connection.

[marcpley]

Thanks for that. I got the error : QSqlDatabase: No such file or directory
In main.cpp, this dont work:
#include <QSqlDatabase>
Where to set that I use sql ? In build params ?

[pawelsalawa]

Oh, you need to open the *.pro file and there add:

CONFIG += sql

Or if there is already a "CONFIG" line, then just append sql after a space.

[marcpley]

Thanks for that. I now have an app that connect to the SQLite file.
I set a versioning of the DB an the app have to update the DB if the shema is too old.

How to use the DDL of SQLiteStudio in my Qt app ?

I create a SQL file and add it to my projet. It is in other file :

Il would like to set the QSqlQueryModel with the content of this file. I will have several such SQL file.

[pawelsalawa]

Do you need to execute this DDL from your application, or you just need a database with this view already created?

It's actually the essential role of apps like SQLiteStudio to prepare database tables/views, which then are used by the end-user applications - like yours and that's why I'm asking, whether you should maybe create this view in SQLiteStudio and in your app you will just operate on existing view?

Or maybe I misunderstand your intentions?

[marcpley]

I create all DB in SQLiteStudio and I create my app for the end-user.
With the v1.0 of my app, users will create their DB.
I will develop my app and modify the schema with SQLiteStudio. I want the v2.0 to detect the DB schema is v1.0 and update it to v2.0.
I think there is 2 options :

• store somewhere the DDL of SQLiteStudio when developing from v1.0 to v2.0 and using it in my v2.0 app to update it the user v1.0 DB.
• my app create a new empty v2.0 schema and import the v1.0 DB data in.
  I actually try the 1rst option.

[pawelsalawa]

Okay, so I guess your app needs to create database using SQL/DDL statements embedded in your app.
Basically your app can keep the SQL statements in static QString and execute them as needed.

Basically to execute arbitrary SQL you can use:

QSqlDatabase::database()->exec(sql);

Now, to detect what version is user's database you can leverage the user_version PRAGMA: https://sqlite.org/pragma.html#pragma_user_version (by default it's 0 and you can detect and increment it as you upgrade your database version).

[marcpley]

Ok, but I have DDL statement in a file. How to store it in a QString easily ?
I tried unsuccessfully that:

the sql file:

[pawelsalawa]

At top of your file, just below #include directives you can do:

...
#include <QString>
...

static QString VERSION_1_DDL = QStringLiteral(R"(
   CREATE TABLE abc (...);
   CREATE INDEX idx ON abc ....;
   etc, etc....
)");

(note, that R"( .... )" is extension of C++11, so you need to make sure to have CONFIG += c++11 or even better CONFIG += c++17)

You can put this static QString in a separate *.h file and use #include just like you indented. Just put the #include at the top level of the cpp file (not inside a function).

This "VERSION_1_DDL" becomes a global QString variable.
This way your DDL is embedded inside of your app.

If you want to keep the DDL in external *.sql file and load it dynamically, you can do:

    QFile file(filePath);

    if (!file.open(QIODevice::ReadOnly | QIODevice::Text)) {
        qWarning() << "Failed to open file:" << file.errorString();
        return -1; // Exit with an error code
    }

    QTextStream in(&file);
    QString fileContents = in.readAll();

    file.close();

[marcpley]

Thanks, I will try that :-)

I access data with QTableView and QSqlTableModel.
The foreign key is not respected ! I can modify data and break the link. Is there a special setting for that?

[pawelsalawa]

For foreign keys there is a QSqlRelationalTableModel - https://doc.qt.io/qt-6.5/qsqlrelationaltablemodel.html

[marcpley]

Each of your indications save me time :-)
I will test that later because at this time I'm blocked by another issue and I don't find solution in forums and documentation, certainly because I don't know what to search... So, another question, so c++ :

I create class QPotaWidget derived from QWidget

class QPotaWidget: public QWidget {
public:
    QSqlTableModel *model = new QSqlTableModel(this);
    QTableView *tv = new QTableView(this);
    };

I dynamically create QPotaWidget tabs in TabWidget:

        QPotaWidget *w = new QPotaWidget;
        w->setObjectName("wDO"+sNames);
        w->model->setTable(sTableName);
        w->model->setEditStrategy(QSqlTableModel::OnManualSubmit);
        w->model->select();
       ui->tabWidget->addTab(w,sTitre);

My problem is here:
ui->tabWidget->currentWidget()->tv->... //Error

currentWidget give a QWidget and don't know tv and model.
But currentWidget is QPotaWidget.
How to cast (or something else) currentWidget to QPotaWidget for accessing tv ?

[pawelsalawa]

dynamic_cast<QPotaWidget*>(ui->tabWidget->currentWidget())->tv->...

The dynamic_cast<>() will result in null pointer if given object is not castable to desired type.

Btw. Maybe you want to setup your own GitHub repo and continue discussion there? I can tell you how to do it and we can transfer this issue (entire discussion) to your repo.

[marcpley]

I'm annoyed squatting your repo.
I'm now probably engaged in a long and complex thing despite you said: "wouldn't it be easier for you just to create a very simple Qt application for this "data only" operations?" :-)
Lets do that.

[pawelsalawa]

It won't be longer nor more complex than trying to adapt SQLiteStudio to do things you need.

Regarding git repo:

1. Have a git (on Linux/MacOS) or "git-bash" (on Windows) installed.
2. Open command line and enter your project folder
3. git init -b main
4. git add .
5. git commit -m "First commit"
6. Create GitHub repo https://docs.github.com/en/repositories/creating-and-managing-repositories/creating-a-new-repository
7. Copy repo url:
   
8. In your command line do: git remote add origin REMOTE-URL replacing the REMOTE-URL accordingly
9. git push origin main

This is procedure for initial creation. Later you will do only 3 steps:

1. git add .
2. git commit -m "my comments on changes made in this commit"
3. git push

Optionally you can always use git status to see what files have pending changes.

[marcpley]

done
https://github.com/marcpley/potaleger