[PyQt] QSqlTableModel: how to check the UPDATE statement inside the model?

[Sibylle Koczian]

Am Dienstag, 11. März 2008 23:11:31 schrieb Hans-Peter Jansen:
> Am Dienstag, 11. März 2008 schrieb Sibylle Koczian:
> > Hello,
> >
> > somewhere in the QSqlTableModel code there must be SQL statements for
> > UPDATE, INSERT and DELETE, to make the model editable. Right? I can't
> > find methods to view these statements - is it necessary to dig into the
> > C++ code to find them?
> >
> > Background: I'm still trying to edit data in a view (SQL view, not Qt
> > view) joining two tables. I wrote a trigger to make this possible at all,
> > and in the command line client the update statements I construct myself
> > are executed correctly. Same thing in PyQt, if I use QSqlQuery instances
> > directly.
> >
> > The database is Firebird 2.0.3, Qt version 4.3.4, PyQt 4.3.3, Python
> > 2.5.1, OS Gentoo Linux.
> >
> > If I use a QSqlTableModel, things get complicated: with the view in
> > the "Employee" example database ("phone_list") all is still well, the
> > database tables are updated using the view. With my own database I don't
> > get any error messages, but no updates either. INSERT works as expected.
> >
> > If I could execute the UPDATE statement used by the model in the command
> > line client, I might find out what happens  - a bug in my trigger is
> > quite probable. But for this I have to know this statement.
> >
> > Can anybody help me?
>
> Well, QSqlQuery/QSqlResult of Qt3 fame had a method lastQuery(). Otherwise,
> if you won't look into Qt itself (which was very enlightening, each time I
> did that), you're always open to redirect the db communication over the
> network, and trace that. tcpdump and wireshark are nice tools in this
> respect.
>

I did dig around a little in the Qt sources, changed my triggers, and found a 
way to update the SQL view. But perhaps there is a better way, so I'll put it 
here. The problem seems to be that a SQL view doesn't have a primary key. But 
my view is such that the primary key of one of the participating tables can 
very well be used as surrogate primary key of the view. So I've subclassed 
QSqlTableModel like this, because setPrimaryKey is protected: 

class SqlViewModel(QtSql.QSqlTableModel):

    def setTable(self, viewname, primname):
        QtSql.QSqlTableModel.setTable(self, viewname)
        db = self.database()
        prim = db.primaryIndex(primname)
        self.setPrimaryKey(prim)

Of course I can post the whole (still small) application - but the database is 
Firebird and adapting the triggers to SQLite would probably produce lots of 
other bugs. But if anyone is interested I'll try.

Regards,
Sibylle

-- 
Dr. Sibylle Koczian