[SOLVED] [N00b] Access and usage of PostgreSQL functions in a Qt GUI
-
Hello Qt Devs!
Background Info
I have a summer project to build a relational DB and a Qt GUI on top of it. However, I'm new to programming and the task before me seems humongous.Question Proper
Is there any way to access PostgreSQL functions from a Qt GUI? I've read some of the documentation on <QtSql>, but from a performance persepective, I'd rather not embed SQL queries in the C++ source code.Also, can I access views I created in Postgres in Qt?
Thanks for all your answers!
-
Oh, I thought that the query would have to be parsed every time I executed it. Thanks for the info!
However, it would make the DB more portable if all the views and functions I needed were directly in the DB, no?
Do you know of a way of accessing my functions and views, or I'd better forget about that?
-
[quote author="valandil211" date="1307708714"]Hello Qt Devs!
Is there any way to access PostgreSQL functions from a Qt GUI? I've read some of the documentation on <QtSql>, but from a performance persepective, I'd rather not embed SQL queries in the C++ source code.
[/quote]Yes. You have to build the PostgreSQL driver manually, shouldn't be too hard on a unix/linux like box.
As your'e a programming newbie, it's not time yet to care about speed. On what experience is your expectation based? And if you do not want to emebed them in C++, where else?
[quote author="valandil211" date="1307708714"]
Also, can I access views I created in Postgres in Qt?
Thanks for all your answers![/quote]Yes. They are just tables. You cannot insert or update in views though.
-
QtSql is a set of classes to access SQL databases. I guess that is what you will indeed need to use. Of course you can also use the plain postgres libraries (most likely C).
I am curious as to how you want to use postgres from Qt without putting SQL queries into the code.
-
bq. I am curious as to how you want to use postgres from Qt without putting SQL queries into the code.
If Qt can call PostgreSQL functions and parse their results, I would not have to use
@query = new QSqlQuery();
query->prepare("Insert SQL Query here");
query->exec();@but I have not seen any documentation on that yet. As far as I know, PostgreSQL functions can return a single row to whole columns. If Qt could parse the results of a function the same way it can parse the results of a query (like the one above), I could use functions stored in the DB.
[quote author="Volker" date="1307712324"]
Yes. They are just tables. You cannot insert or update in views though.[/quote]This is partly why I want to use views. There are some data that I do not want the user to be able to update or insert into.
-
As Volker and Tobias have already said, how else were you planning on executing queries? Surely not with QProcess and the postgres cli interface? That would be much slower as it would involve launching an entire process for each query.
A database view is essentially just a read-only table. Just query it in the same way.
If you need any specific help then please feel free to ask for help here. We are a friendly bunch ;-)
-
If you wish to restrict operations then get your application to connect as a user that has the minimum rights to do what is needed. This is good practise in general.
Yes you could use the postgres client library directly and bypass the QtSql stuff entirely but then all the data type conversions will need to be performed by you rather than Qt.
I would use QtSql unless you have some specific requirement that it cannot meet.
-
You will have to call SQL anyways. I think you focus on the wrong topics.
Make the SQL queries using Qt. It's enough work to learn the framework and C++. Don't wast time on hypothetical optimizations that you don't even know if they're necessary at all.
Qt SQL itself is platform independent. As long as you are using standard SQL queries (insert, update, select...), you should be database agnostic. Calling stored procedures from Qt breaks this, as it is not cross DB!
Some tips:
If you do insert or select the same query with different parameters frequently, prepare the statement (QSqlQuery::prepare()) and save that query. This speeds up the queries significantly.Set reasonable indexes on your tables, this speeds up the execution of queries in the database itself (independent from the used client).
-
[quote author="ZapB" date="1307713126"]As Volker and Tobias have already said, how else were you planning on executing queries? Surely not with QProcess and the postgres cli interface? That would be much slower as it would involve launching an entire process for each query.[/quote]
Well, my father-in-law uses SQL Server and Visual Studio and uses stored procedures to access the DB. These are stored directly in the DB and called from the GUI. I thought there was an equivalent for PostgreSQL and Qt.
[quote author="ZapB" date="1307713126"] If you need any specific help then please feel free to ask for help here. We are a friendly bunch ;-)[/quote]
Yes, I noticed that. Thanks for all the help!
-
Thanks Volker, I will keep what you said in mind. Thanks for your time.
-
Stored procedures are just like prepared queries except that they persist across sessions (I think that is true across DB's that support them). With prepared queries you just pay the cost of parsing them once per session - which is very minimal.
As a general rule - get it working first. Worry about optimisation later when you have somethign to profile and can find out if anything needs profiling and if so which bit is it.
-
I recently created a SQLITE database with a simple Qt interface, resorting to using raw SQL only for the CREATE TABLE statements when initializing an empty db. I used Qt classes for everything else, as this application is to take data from a giant POSTGRESQL db on the web and store some of it locally in SQLITE.
I .append() then .setValue() QSqlFields to QSqlRecords that I .insertRecord() into QSqlTableModels which I had bound with .setModel() to my QTableViews.
I had also done this whole project using .prepare() and .exec() of QSqlQueries. This way was a little less typing, and a little quicker to code. The simple SQL that I used could have been tailored for POSTGRESQL, which SQLITE would have converted internally (data types in particular), so it could be "cross db."
I did the all SQL to all QSql-model comparison strictly to judge performance. There was simply no discernable difference in performance of the db actions when the app was being developed with a half-million records and the SQLITE .filename() was set to ":memory:". Disk access was the bottleneck in the final creation of the complete db.
In the end- I prefered the QSql-classes all the way, especially for binding to my QModelViews and letting it negotiate the lazy population of views in my 16 million record db.