kreyszig
26th April 2011, 11:06
Hi all
I'm using a QSQlRelationalTableModel with QSqlRelation as per the 4.7 documentation.
However if just one of the rows contains data in a column referred to by a QSqlRelation that doesn't map to a valid foreign key (e.g NULL), the model stops working - at least the Tableview displaying it shows no data.
For example:
Tables:
CREATE TABLE MaterialProperty (
ID text UNIQUE PRIMARY KEY NOT NULL,
ParameterID text,
ScalarValue real,
CurveID text,
StringValue text,
UnitID text,
MaterialID text,
FieldID text,
VersionNumber integer,
VersionDate datetime,
/* Foreign keys */
FOREIGN KEY (CurveID)
REFERENCES SeriesIndex(ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY (UnitID)
REFERENCES Unit(ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY (MaterialID)
REFERENCES MaterialIndex(ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY (ParameterID)
REFERENCES Parameter(ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY (FieldID)
REFERENCES FieldIndex(ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
CREATE TABLE SeriesIndex (
ID text UNIQUE PRIMARY KEY NOT NULL,
Name varchar(50),
Title varchar(50),
Dims integer default 2,
Xlabel varchar(50) default "X",
Ylabel varchar(50) default "Y",
Zlabel varchar(50) default "Z",
VersionNumber integer,
VersionDate datetime
);
QSqlRelationalTableModel *model = db->get_db_model();
model->setTable("materialproperty");
QSqlRelation r("seriesindex", "id", "name");
if(r.isValid()) // this passes
model->setRelation(3, r);
If a row in materialproperty has no curveID, then no data is presented in the view at all. If I don't set up the relation, all appears as expected. The same if any other foreign key value is NULL.
I have a number of optional parameters such as curveID that can end up null.
Is there a way I can stop this behaviour? I don't see why a failure to map a NULL value to a foreign key should break the whole view/model (i'm not sure which is broken exactly, I presume it's the model). FWIW I am using an Sqlite database.
I would prefer not to have to fill these columns with data (i.e some foreign key to represent null) simply to get Qt to work with them.
Your help most appreciated - I've asked this over at stack overflow but haven't had a single response.
before you ask I am using UUID's as keys to allow data migration between databases, hence the text primary key values.
I'm using a QSQlRelationalTableModel with QSqlRelation as per the 4.7 documentation.
However if just one of the rows contains data in a column referred to by a QSqlRelation that doesn't map to a valid foreign key (e.g NULL), the model stops working - at least the Tableview displaying it shows no data.
For example:
Tables:
CREATE TABLE MaterialProperty (
ID text UNIQUE PRIMARY KEY NOT NULL,
ParameterID text,
ScalarValue real,
CurveID text,
StringValue text,
UnitID text,
MaterialID text,
FieldID text,
VersionNumber integer,
VersionDate datetime,
/* Foreign keys */
FOREIGN KEY (CurveID)
REFERENCES SeriesIndex(ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY (UnitID)
REFERENCES Unit(ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY (MaterialID)
REFERENCES MaterialIndex(ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY (ParameterID)
REFERENCES Parameter(ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY (FieldID)
REFERENCES FieldIndex(ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
CREATE TABLE SeriesIndex (
ID text UNIQUE PRIMARY KEY NOT NULL,
Name varchar(50),
Title varchar(50),
Dims integer default 2,
Xlabel varchar(50) default "X",
Ylabel varchar(50) default "Y",
Zlabel varchar(50) default "Z",
VersionNumber integer,
VersionDate datetime
);
QSqlRelationalTableModel *model = db->get_db_model();
model->setTable("materialproperty");
QSqlRelation r("seriesindex", "id", "name");
if(r.isValid()) // this passes
model->setRelation(3, r);
If a row in materialproperty has no curveID, then no data is presented in the view at all. If I don't set up the relation, all appears as expected. The same if any other foreign key value is NULL.
I have a number of optional parameters such as curveID that can end up null.
Is there a way I can stop this behaviour? I don't see why a failure to map a NULL value to a foreign key should break the whole view/model (i'm not sure which is broken exactly, I presume it's the model). FWIW I am using an Sqlite database.
I would prefer not to have to fill these columns with data (i.e some foreign key to represent null) simply to get Qt to work with them.
Your help most appreciated - I've asked this over at stack overflow but haven't had a single response.
before you ask I am using UUID's as keys to allow data migration between databases, hence the text primary key values.