Sql questions

I have some QSql related questions that I can't seem to find answered anywhere. Perhaps someone here can help out.

1) My database gets updated externally at a very fast rate. What is the proper/best way to update any QSql*Models when their table is changed externally? Since SQLite is embedded I was kinda hoping it would happen automatically but that doesn't seem to be the case. I guess I could just have a timer and manually loop over the sql table and update the model rows/colums? Or is there a more elegant way using some database triggers or something? Calling select() repeatedly will update the data but any model selections & editing gets wiped out. Does MySql have any features that might help in this area?

2) The QSqlRelation* classes say they support foreign keys. If a database table has a column specified as a foreign key will QSqlRelationalTableModel "know" it automatically? Or will I still have to specifically use it's setRelation() method on all the columns manually? (I know SQLite doesn't enforce them but you can still specify them.)

3) Did I do something wrong or does SQLite's ":memory:" database not support multiple threads? When I switched it to using a file, updates and selects from multiple threads began working without problems. (Yes, I made unique db connections for each thread)

Thanks in advance,

RobF

Quote:

---

Originally Posted by Nb2Qt

1) My database gets updated externally at a very fast rate. What is the proper/best way to update any QSql*Models when their table is changed externally? Since SQLite is embedded I was kinda hoping it would happen automatically but that doesn't seem to be the case. I guess I could just have a timer and manually loop over the sql table and update the model rows/colums? Or is there a more elegant way using some database triggers or something? Calling select() repeatedly will update the data but any model selections & editing gets wiped out. Does MySql have any features that might help in this area?

---

With these models you are limited to the sql language/protocol and it doesn't provide any means of checking for changes since a defined moment in the past, therefore the only certain way of getting all the changes is to refetch all data. Unfortunately this is connected to losing all data related to selections, etc. You can only store the selection before you reinitialise the model (by calling "select") and then restore the selection afterwards.

Quote:

---

2) The QSqlRelation* classes say they support foreign keys. If a database table has a column specified as a foreign key will QSqlRelationalTableModel "know" it automatically? Or will I still have to specifically use it's setRelation() method on all the columns manually? (I know SQLite doesn't enforce them but you can still specify them.)

---

You can only substitute a column which is a foreign key with some other column from the table which the key belongs to and you have to explicitely name the columns (there is an example how to use it in QSqlRelationalTableModel.

Quote:

---

3) Did I do something wrong or does SQLite's ":memory:" database not support multiple threads? When I switched it to using a file, updates and selects from multiple threads began working without problems. (Yes, I made unique db connections for each thread)

---

If you use ":memory:" then the database is created only for that single connection, therefore if you create another connection (i.e. in a different thread) again passing ":memory:" as the database name, a new database will be created for the other connection - they won't share data.

Regarding your first question: At work (even though it's a plain vanilla win32api-app) we're using a timer to refresh our models and a locking mechanism to prevent two users from editing the same row. Other dbms (like mysql or pgsql) won't help you much there except for providing locking mechanisms you can use directly instead of having to invent your own.

How about writing a little service / daemon or even just another thread that monitors connections on the db port (Does SQLite use a port). Whenever a connection is established, then signal your main program to do an sql refresh.
You might need to consider waiting a bit, or repolling until you know the connection is finished before signalling.

Just a thought.

The other problem I'm having is that calling select() via a timer is not updating the contents of the table. It appears only to update if the #row or #columns has changed in the database. Is there a way of forcing QSqlQueryModel (and derivatives) to update the table *contents* without subclassing?

Thanks again,

RobF