requiredStatus on Qt 4.3 and PostgreSQL 8.3.3

[Auryn]

Some fields in one PostgreSQL table are configured with "NOT NULL".
Inside my Qt 4.3 application I try to detect this constraint, but I am trying with

if(my_model.requiredStatus == QSqlField::Required)
  // Warn user that this field must be filled

if(my_model.requiredStatus == QSqlField::Required)
  // Warn user that this field must be filled

To copy to clipboard, switch view to plain text mode 

But it allways returns -1 (QSqlField::Unknown).
If this is not the method to follow, how can PostgreSQL tell to Qt when a field has some restriction (check, not null...)?

[jacek]

How do you create that my_model object?

[Auryn]

Here is a sample code showing how I am creating the model:

QSqlRelationalTableModel my_model;
 
my_model.setEditStrategy(QSqlTableModel::OnManualSubmit);
my_model.setTable("my_postgresql_table");
 
my_model.setHeaderData(0, Qt::Horizontal, t("my_field_0"));
 
my_model.setHeaderData(1, Qt::Horizontal, t("my_field_1"));
my_model.setRelation(1, QSqlRelation("foreign_table", "id", "caption");
 
my_model.select();
 
QSqlRelationalTableModel my_table;
my_table.setModel(my_model);
my_table.setItemDelegate(new QSqlRelationalDelegate(my_table));

QSqlRelationalTableModel my_model;

my_model.setEditStrategy(QSqlTableModel::OnManualSubmit);
my_model.setTable("my_postgresql_table");

my_model.setHeaderData(0, Qt::Horizontal, t("my_field_0"));

my_model.setHeaderData(1, Qt::Horizontal, t("my_field_1"));
my_model.setRelation(1, QSqlRelation("foreign_table", "id", "caption");

my_model.select();

QSqlRelationalTableModel my_table;
my_table.setModel(my_model);
my_table.setItemDelegate(new QSqlRelationalDelegate(my_table));

To copy to clipboard, switch view to plain text mode 

The signal dataChanged is connected to one slot that calls:

my_model->submitAll();

my_model->submitAll();

To copy to clipboard, switch view to plain text mode 

This works, but if one constraint (as NOT NULL) doesn't match, the line doesn't get inserted, and doesn't warn the user.

Here is the code where I connect to my PostgreSQL database (before creating the model):

my_conection = new QSqlDatabase(QSqlDatabase::addDatabase("QPSQL"));
my_conection->setHostName("my_host");
my_conection->setDatabaseName("my_postgresql_database");
my_conection->setUserName("my_username");
my_conection->setPassword("my_password");

my_conection = new QSqlDatabase(QSqlDatabase::addDatabase("QPSQL"));
my_conection->setHostName("my_host");
my_conection->setDatabaseName("my_postgresql_database");
my_conection->setUserName("my_username");
my_conection->setPassword("my_password");

To copy to clipboard, switch view to plain text mode 

Thank you

[jacek]

But requiredStatus() is a method of QSqlField class, not QSqlRelationalTableModel. You can get one through QSqlRecord class.

[Auryn]

Sorry, what I wanted to say at the beginning was this:

if(my_model.record().field(0).requiredStatus() == QSqlField::Required)
  // Warn user that this field must be filled

if(my_model.record().field(0).requiredStatus() == QSqlField::Required)
  // Warn user that this field must be filled

To copy to clipboard, switch view to plain text mode 

It allways returns -1, so the field never is considered as required (unless it is required for PostgreSQL: ALTER TABLE my_table ALTER COLUMN first_field SET NOT NULL).

In this way, I think my_model.record().field(0) is a QSqlField.

[jacek]

What does my_model.record().count() return?

[Auryn]

The sentence:

my_model.record().count()

my_model.record().count()

To copy to clipboard, switch view to plain text mode 

returns four (the correct number of columns in the table).

[jacek]

So it seems that everything is OK on your side and the problem is with the driver. Since you have table and field names (or at least you can get them from Qt), you can write your own method that will check that. You have to query the information_schema.columns.

Something like this should do (if you don't use schemas):

SELECT ( is_nullable = 'NO' AND column_default IS NULL ) AS required
FROM information_schema.COLUMNS
WHERE table_name = :table_name AND column_name = :column_name;

SELECT ( is_nullable = 'NO' AND column_default is NULL ) AS required
FROM information_schema.columns
WHERE table_name = :table_name AND column_name = :column_name;

To copy to clipboard, switch view to plain text mode 

[Auryn]

Thank you, this method works to check "NOT NULL" constraint:

bool My_class::Is_field_required(const QString &my_table, const QString &my_field) const
{
	QSqlQuery my_query("SELECT(is_nullable='NO' AND column_default IS NULL) AS required FROM information_schema.COLUMNS WHERE table_name='" 
		+ my_table + "' AND column_name='" + my_field + "';");
	if (!my_query.exec())
	{
		return(false);
	}
	else
	{
		consulta.first();
		return(my_query.value(0).toBool());
	}
}

bool My_class::Is_field_required(const QString &my_table, const QString &my_field) const
{
	QSqlQuery my_query("SELECT(is_nullable='NO' AND column_default IS NULL) AS required FROM information_schema.COLUMNS WHERE table_name='" 
		+ my_table + "' AND column_name='" + my_field + "';");
	if (!my_query.exec())
	{
		return(false);
	}
	else
	{
		consulta.first();
		return(my_query.value(0).toBool());
	}
}

To copy to clipboard, switch view to plain text mode 

But, what about other constraints (CHECK)?

I supose that I must search inside information_schema too.

SQL table example:

CREATE TABLE my_products (
    my_product integer,
    my_product_name text,
    my_price numeric CHECK (my_price > 0)
);

CREATE TABLE my_products (
    my_product integer,
    my_product_name text,
    my_price numeric CHECK (my_price > 0)
);

To copy to clipboard, switch view to plain text mode 

[jacek]

QSqlQuery my_query("SELECT(is_nullable='NO' AND column_default IS NULL) AS required FROM information_schema.COLUMNS WHERE table_name='"
+ my_table + "' AND column_name='" + my_field + "';");

Better use QSqlQuery::prepare() and QSqlQuery::bindValue().

I supose that I must search inside information_schema too.

Yes, see information_schema.check_constraints.