I have created a rule in a PosgreSQL table to execute it when inserting a row.
It calls "NOTIFY myNotify" and works fine when I insert a row with an INSERT SQL command. Qt detects the notification well.
When I insert a row in a table view that depends on a table model, the rule works fine, but Qt doesn't detect the notification.
Here is a sample main function:
#include <QApplication>
#include <QSqlDriver>
#include "myClass.hpp"
int main(int argc, char *argv[]) {
// Open conection to database
bd.setHostName("localhost");
bd.setDatabaseName("myDB");
bd.open("postgres", "postgres");
// Load data in the table
myModel.setTable("myTable");
// Show table with the data from database
myModel.select();
myTV.setModel(&myModel);
myTV.show();
// Create the object with the slot to save changes to database
TmyClass myObject(0, &myModel);
// Subscribe and define the treatment for the notification thrown when updating a field
bd.driver()->subscribeToNotification("modified_row");
QObject::connect((QObject*)bd.
driver(),
SIGNAL(notification
(const QString
&)),
(QObject*)&myObject,
SLOT(myNotifyHandler
(const QString
&)));
return aplication.exec();
}
#include <QApplication>
#include <QSqlDriver>
#include "myClass.hpp"
int main(int argc, char *argv[]) {
QApplication aplication(argc, argv);
// Open conection to database
QSqlDatabase bd(QSqlDatabase::addDatabase("QPSQL"));
bd.setHostName("localhost");
bd.setDatabaseName("myDB");
bd.open("postgres", "postgres");
// Load data in the table
QTableView myTV;
QSqlRelationalTableModel myModel(&myTV, bd);
myModel.setEditStrategy(QSqlTableModel::OnManualSubmit);
myModel.setTable("myTable");
// Show table with the data from database
myModel.select();
myTV.setModel(&myModel);
myTV.show();
// Create the object with the slot to save changes to database
TmyClass myObject(0, &myModel);
// Subscribe and define the treatment for the notification thrown when updating a field
bd.driver()->subscribeToNotification("modified_row");
QObject::connect((QObject*)bd.driver(), SIGNAL(notification(const QString&)), (QObject*)&myObject, SLOT(myNotifyHandler(const QString&)));
return aplication.exec();
}
To copy to clipboard, switch view to plain text mode
The class to save changes manually:
#ifndef TMYCLASS
#define TMYCLASS
#include <QtCore/QDebug>
#include <QtGui/QTableView>
#include <QtSql/QSqlError>
#include <QtSql/QSqlRelationalDelegate>
{
Q_OBJECT
public:
public slots:
void myNotifyHandler
(const QString ¬ification
);
private:
};
#endif
{
// When something changes, call the slot mySave
connect(m_model, SIGNAL(dataChanged(const QModelIndex&, const QModelIndex&)), this, SLOT(mySave(const QModelIndex&, const QModelIndex&)));
}
{
if(!m_model->submitAll())
{
qDebug()<<"Error when submitting:"<<m_model->lastError().text();
}
}
void TmyClass
::myNotifyHandler(const QString ¬ification
) {
qDebug()<<"Detected notification from PostgreSQL: "<<notification;
}
#ifndef TMYCLASS
#define TMYCLASS
#include <QtCore/QDebug>
#include <QtGui/QTableView>
#include <QtSql/QSqlError>
#include <QtSql/QSqlRelationalDelegate>
class TmyClass : public QObject
{
Q_OBJECT
public:
TmyClass(QObject *parent, QSqlRelationalTableModel *oneModel);
public slots:
void mySave(const QModelIndex &a, const QModelIndex &b);
void myNotifyHandler(const QString ¬ification);
private:
QSqlRelationalTableModel *m_model;
};
#endif
TmyClass::TmyClass(QObject *parent, QSqlRelationalTableModel *oneModel): QObject(parent), m_model(oneModel)
{
// When something changes, call the slot mySave
connect(m_model, SIGNAL(dataChanged(const QModelIndex&, const QModelIndex&)), this, SLOT(mySave(const QModelIndex&, const QModelIndex&)));
}
void TmyClass::mySave(const QModelIndex &a, const QModelIndex &b)
{
if(!m_model->submitAll())
{
qDebug()<<"Error when submitting:"<<m_model->lastError().text();
}
}
void TmyClass::myNotifyHandler(const QString ¬ification)
{
qDebug()<<"Detected notification from PostgreSQL: "<<notification;
}
To copy to clipboard, switch view to plain text mode
PostgreSQL sample database dump (is a very simple table, the important detail is "CREATE RULE "myRule" AS ON UPDATE TO "myTable" DO NOTIFY modified_row;"):
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
CREATE TABLE "myTable" (
"ID" integer NOT NULL,
name text
);
ALTER TABLE public."myTable" OWNER TO postgres;
CREATE SEQUENCE "myTable_ID_seq"
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE public."myTable_ID_seq" OWNER TO postgres;
ALTER SEQUENCE "myTable_ID_seq" OWNED BY "myTable"."ID";
SELECT pg_catalog.setval('"myTable_ID_seq"', 3, true);
ALTER TABLE "myTable" ALTER COLUMN "ID" SET DEFAULT nextval('"myTable_ID_seq"'::regclass);
COPY "myTable" ("ID", name) FROM stdin;
1 One
2 Two
3 Three
\.
ALTER TABLE ONLY "myTable" ADD CONSTRAINT pk PRIMARY KEY ("ID");
CREATE RULE "myRule" AS ON UPDATE TO "myTable" DO NOTIFY modified_row;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
CREATE TABLE "myTable" (
"ID" integer NOT NULL,
name text
);
ALTER TABLE public."myTable" OWNER TO postgres;
CREATE SEQUENCE "myTable_ID_seq"
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE public."myTable_ID_seq" OWNER TO postgres;
ALTER SEQUENCE "myTable_ID_seq" OWNED BY "myTable"."ID";
SELECT pg_catalog.setval('"myTable_ID_seq"', 3, true);
ALTER TABLE "myTable" ALTER COLUMN "ID" SET DEFAULT nextval('"myTable_ID_seq"'::regclass);
COPY "myTable" ("ID", name) FROM stdin;
1 One
2 Two
3 Three
\.
ALTER TABLE ONLY "myTable" ADD CONSTRAINT pk PRIMARY KEY ("ID");
CREATE RULE "myRule" AS ON UPDATE TO "myTable" DO NOTIFY modified_row;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
To copy to clipboard, switch view to plain text mode
Some links I have read:
QSqlDriver notifications
Blog about asynchronous database event notifications
Asynchronous notifications in PostgreSQL
Bookmarks