QSql performance consideration/data flow design.

artwaw

Good afternoon,
I'd like someone experienced to advise on the design of the internal relations in model/view program.

I have Qt6 widgets application, using sqlite. Database is of design:

TABLE A(a1 int, a2 text, ... ,an int) - main table, lots of columns, I don't foresee it having more than a couple of hundred rows.
TABLE B(b1 int, b2 text, .... bn int) - subtable, possibly with 1-3k rows.
Internally, table A is QSqlRelationalTableModel (as it has two foreign keys further down the line, nothing big, works fine) and table B is simple QSqlTableModel. Both are linked to the QTableView, switched on demand by user.

One of the fields in table A consist of many entries out the column from table B - normally I'd use Postgress and write me a function computing that on demand but I am limited to SQLite. So helper table is being considered: TABLE C(c1 int, c2 int, c3 int, c4 int) where c2 is a foreign key to table A, c3 is a foreign key to table B. As one would guess there would be lots of entries in that, with query having where c3= filter so that I can combine them into 1 entry into table A column.

I need many to many relation and it isn't out of the box supported, as I am aware. As well absent from the set of models is generating a column out of sql subquery.

So the naive thing I came out with, is to subclass QSqlRelationalTableModel, add QSqlQueryModel/QSqlQuery inside it linked to TABLE C and doing internal query every time data from that one column from table A is needed. I am not happy with the idea but I don't think that, for the amount of rows designed, performance penalty would be THAT noticeable...
Is there less dirty way of solving the issue?

Many thanks in advance

SGaist

Hi,

Do I understand correctly that you are talking about stored procedures ?
If so, it seems that SQLite has an equivalent. See this article.

artwaw

@SGaist I missed that while browsing sqlite.org!

Thank you, will investigate if that is sufficient - sqlite.org says that implementation is not complete/doesn't cover all cases but I will do my research and get back!

artwaw

@SGaist I did some digging, this seems to be available as the C/C++ API element. Database itself says:

Execution finished with errors.
Result: near "function": syntax error

when asked to create function. So indeed I didn't missed anything while reading ;)
Native description of the method is here: https://sqlite.org/capi3ref.html#sqlite3_create_function

But more seriously: I am not sure if I should attempt to use it together with QSql? How would that affect QSqlDatabase? That bit of the API doesn't seem to be present in the driver or I can't find it.

Assuming I should keep relying on QSql only - how bad is my initial idea of encapsulating a query within a table model?

Christian Ehrlicher

A sqlite function is not a stored procedure.

JonB

@artwaw
We didn't see exactly what you wrote, and we don't know much about the sqlite3 interface.

You might try a QSqlQuery() with actual text of CREATE FUNCTION ..., that might work in SQLite, I don't know.

Further one could just as well use CREATE VIEW as their CREATE FUNCTION. You might have more luck with that.

In principle writing your own FUNCTIONs or VIEWs and calling them should not upset QSql....

@Christian-Ehrlicher
The SQLite implementation of "stored procedures" @SGaist pointed us to states:

How to Use Stored Procedure in SQLite

In SQLite, stored procedures can be implemented using User-defined Functions (UDFs) or Triggers. UDFs allow defining custom functions, while Triggers execute actions in response to database events, both enabling stored procedure functionality.

They are actually using CREATE FUNCTION/"UDFs" as what they choose to call/implement as "stored procedures". Even though this is way more limited, e.g. nothing but a single SELECT statement for CREATE FUNCTION and hence for their "stored procedures".

Christian Ehrlicher

Ok, I would never consider those functions as replacement for stored procedure. Only to implement custom function like e.g. regex. upper() or lower() (as we do inside the sqlite plugin).

artwaw

@JonB said in QSql performance consideration/data flow design.:

You might try a QSqlQuery() with actual text of CREATE FUNCTION ..., that might work in SQLite, I don't know.

That't what gave me an error. Sorry for not being clear enough - I try to run all the server side queries (select, create, etc.) through, well, a query.

I'll try experimenting with a views and triggers until I understand the limitations.

Thank you, Gentlemen.

artwaw

Just to add - my understanding of Sqlite is also limited, as we can see.

That's why the core of my question is/was - how bad idea it is to try to do to QSqlTableModel what I thought of doing :)

JonB

@Christian-Ehrlicher said in QSql performance consideration/data flow design.:

Ok, I would never consider those functions as replacement for stored procedure.

I agree, but it is their documentation, I guess "Lite" means "lite" :)