QSqlRelationalTableModel insert record error
-
I have a sqlite db with two tables: project and image:
-- Table: image DROP TABLE IF EXISTS image; CREATE TABLE IF NOT EXISTS image ( id INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL, parent_id INTEGER DEFAULT NULL, project_id INTEGER REFERENCES project (id) NOT NULL, file_name TEXT NOT NULL, scan_datetime DATETIME DEFAULT NULL, identifier TEXT DEFAULT NULL, laterality INTEGER NOT NULL DEFAULT ( -1), position_type INTEGER DEFAULT ( -1) NOT NULL, transposed INTEGER NOT NULL DEFAULT 0, x_size INTEGER NOT NULL DEFAULT (0), y_size INTEGER NOT NULL DEFAULT (0), x_origin REAL NOT NULL DEFAULT (0), y_origin REAL NOT NULL DEFAULT (0), x_spacing REAL NOT NULL DEFAULT (1), y_spacing REAL NOT NULL DEFAULT (1), created_at TEXT DEFAULT (CURRENT_TIMESTAMP) NOT NULL, updated_at TEXT DEFAULT CURRENT_TIMESTAMP NOT NULL ); -- Table: project DROP TABLE IF EXISTS project; CREATE TABLE IF NOT EXISTS project ( id INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL, name TEXT UNIQUE NOT NULL, root_path TEXT NOT NULL, created_at TEXT DEFAULT (CURRENT_TIMESTAMP) NOT NULL, updated_at TEXT DEFAULT (CURRENT_TIMESTAMP) NOT NULL, UNIQUE ( name, root_path ) ON CONFLICT ROLLBACK );My main window class has QSqlRelationalTableModels class members for each table:
void jswqDBWindow::createModels() { m_project = new QSqlRelationalTableModel(this); m_project->setEditStrategy(QSqlTableModel::OnManualSubmit); m_project->setTable("project"); // set column aliases m_project->setHeaderData(0, Qt::Horizontal, tr("ID")); m_project->setHeaderData(1, Qt::Horizontal, tr("Name")); m_project->setHeaderData(2, Qt::Horizontal, tr("Path")); m_image = new QSqlRelationalTableModel(this); m_image->setEditStrategy(QSqlTableModel::OnManualSubmit); m_image->setTable("image"); // set column aliases m_image->setHeaderData(0, Qt::Horizontal, tr("ID")); m_image->setHeaderData(1, Qt::Horizontal, tr("Parent")); m_image->setHeaderData(2, Qt::Horizontal, tr("Project")); m_image->setHeaderData(3, Qt::Horizontal, tr("File Name")); m_image->setHeaderData(4, Qt::Horizontal, tr("Scan Datetime")); m_image->setHeaderData(5, Qt::Horizontal, tr("Identifier")); m_image->setHeaderData(6, Qt::Horizontal, tr("Laterality")); m_image->setHeaderData(7, Qt::Horizontal, tr("Position")); m_image->setHeaderData(8, Qt::Horizontal, tr("Transposed")); m_image->setHeaderData(9, Qt::Horizontal, tr("x size")); m_image->setHeaderData(10, Qt::Horizontal, tr("y size")); m_image->setHeaderData(11, Qt::Horizontal, tr("x origin")); m_image->setHeaderData(12, Qt::Horizontal, tr("y origin")); m_image->setHeaderData(13, Qt::Horizontal, tr("x spacing")); m_image->setHeaderData(14, Qt::Horizontal, tr("y spacing")); m_image->setRelation(2, QSqlRelation("project", "id", "name"));I create a project entry
int jswqDBWindow::createProject(QString projectName, QString projectPath) { // get and fill empty record QSqlRecord rec = m_project->record(); rec.setGenerated("created_at", false); rec.setGenerated("updated_at", false); rec.setValue("name", QVariant(projectName)); rec.setValue("root_path", QVariant(projectPath)); m_project->insertRecord(-1, rec); bool ok = m_project->submitAll(); if (!ok) { QSqlError err = m_project->lastError(); qDebug() << err.text(); return -1; } QVariant lastId = m_project->query().lastInsertId(); if (!lastId.isValid()) { qDebug() << "last inserted id invalid"; } else qDebug() << "last inserted (project) id " << lastId.toString(); int result = lastId.isValid() ? lastId.toInt() : -1; return result; }and then use an image reading class to populate the image table rows:
void jswqDBWindow::appendImages(int projectId, QString path) { vtkSmartPointer<vtkDICOMDirectory> dicomDir = vtkSmartPointer<vtkDICOMDirectory>::New(); dicomDir->RequirePixelDataOff(); dicomDir->IgnoreDicomdirOn(); dicomDir->SetFilePattern("*.dcm"); dicomDir->SetScanDepth(10); dicomDir->SetDirectoryName(path.toStdString().c_str()); dicomDir->Update(); vtkSmartPointer<vtkDICOMParser> parser = vtkSmartPointer<vtkDICOMParser>::New(); vtkSmartPointer<vtkDICOMMetaData> data = vtkSmartPointer<vtkDICOMMetaData>::New(); parser->SetMetaData(data); for (int i = 0; i < dicomDir->GetNumberOfStudies(); i++) { // Iterate through all of the series in this study. int j = dicomDir->GetFirstSeriesForStudy(i); for (; j <= dicomDir->GetLastSeriesForStudy(i); j++) { vtkStringArray* sortedFiles = dicomDir->GetFileNamesForSeries(j); const vtkIdType l = sortedFiles->GetNumberOfValues(); data->Clear(); data->SetNumberOfInstances(static_cast<int> (l)); for (vtkIdType k = 0; k < l; k++) { parser->SetIndex(k); parser->SetFileName(sortedFiles->GetValue(k)); parser->Update(); QString fileName = QString::fromStdString(parser->GetFileName()).trimmed(); int imageSize[2] = { 0, 0 }; if (data->Get(0, DC::Rows).IsValid()) { imageSize[1] = data->Get(0, DC::Rows).AsInt(); } if (data->Get(0, DC::Columns).IsValid()) { imageSize[0] = data->Get(0, DC::Columns).AsInt(); } QString patientName = QString::fromStdString(data->Get(0, DC::PatientName).AsString()).trimmed(); QString patientId = QString::fromStdString(data->Get(0, DC::PatientID).AsString()).trimmed(); QString identifier = patientId.isEmpty() ? (patientName.isEmpty() ? "unknown" : patientName) : patientId; QString acqDateTime = QString::fromStdString(data->Get(0, DC::AcquisitionDate).AsString()).trimmed(); if (acqDateTime.isEmpty()) acqDateTime = QString::fromStdString(data->Get(0, DC::StudyDate).AsString()).trimmed(); vtkDICOMValue pixelSpacingValue = data->Get(0, DC::PixelSpacing); double pixelSize[2] = { 1, 1 }; if (pixelSpacingValue.GetNumberOfValues() == 2) { pixelSpacingValue.GetValues(pixelSize, 2); } QSqlRecord rec = m_image->record(); rec.replace(rec.indexOf("name"), QSqlField("project_id")); rec.setGenerated("parent_id", false); rec.setGenerated("laterality", false); rec.setGenerated("position_type", false); rec.setGenerated("transposed", false); rec.setGenerated("x_origin", false); rec.setGenerated("y_origin", false); rec.setGenerated("created_at", false); rec.setGenerated("updated_at", false); rec.setValue("file_name", QVariant(fileName)); rec.setValue("project_id", QVariant(projectId)); rec.setValue("identifier", QVariant(identifier)); rec.setValue("scan_datetime", QVariant(acqDateTime)); rec.setValue("x_size", QVariant(imageSize[0])); rec.setValue("y_size", QVariant(imageSize[1])); rec.setValue("x_spacing", QVariant(pixelSize[0])); rec.setValue("y_spacing", QVariant(pixelSize[1])); } } } bool ok = m_image->submitAll(); if (!ok) { QSqlError err = m_image->lastError(); qDebug() << err.text(); return; } QVariant lastId = m_image->query().lastInsertId(); if (!lastId.isValid()) { qDebug() << "last inserted id invalid"; } else qDebug() << "last inserted (image) id " << lastId.toString(); }the
rec.replace(rec.indexOf("name"), QSqlField("project_id"));is there because without it, the project_id foreign key is aliased as "name" by the QSqlRelation and the record cannot be created. I can create one project, populate the image table referencing the project by its foreign key
int p1 = window.createProject("test1", path1); window.appendImages(p1, img1); int p2 = window.createProject("test2", path2); window.appendImages(p2, img2);but then if I create another project (different name "test2' and path "path2") followed by creating new image records (different path "img2" containing different images) the following error occurs:
"NOT NULL constraint failed: image.project_id Unable to fetch row"I have tried replacing the aliased project_id field with an explicitly defined record which also fails
QSqlRecord rec = m_image->record(); QSqlField f = QSqlField("project_id"); f.setTableName("project"); f.setGenerated(true); f.setType(QVariant::Int); f.setRequired(true); f.setValue(QVariant(projectId)); rec.replace(rec.indexOf("name"), f);In VS debugger, a watch in the insert SQL before binding has all the correct fields named:
INSERT INTO "image" ("id", "project_id", "file_name", "scan_datetime", "identifier", "x_size", "y_size", "x_spacing", "y_spacing") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)but on the second time around adding the 2nd project and submitting new image rows:
INSERT INTO "image" ("id", "file_name", "scan_datetime", "identifier", "x_size", "y_size", "x_spacing", "y_spacing") VALUES (?, ?, ?, ?, ?, ?, ?, ?)Any help would be greatly appreciated.
-
Please provide a minimal, compilable example of your problem, maybe even in a bug report. But before opening a new report, please take a look into https://bugreports.qt.io/browse/QTBUG-128434 - it sounds very similar to your problem here and will be fixed soon.
-
I have a project locked in at branch 5.15.2,
will your bug fix be back ported to 5.15 LTS and if so when ?@inglis-dl said in QSqlRelationalTableModel insert record error:
will your bug fix be back ported to 5.15 LTS and if so when ?
No
But you try to apply to patch by yourself. -
I back ported and applied the patch but keep getting the same error when adding anything after the first project record and its image records. I can add project records but submitAll fails with the NOT NULL constraint error. This is with a simplified image table schema with only id, project_id and file_name fields. My work around is to append images to the image table with a locally instantiated QSqlTableModel and then call select on the QSqlRelationalTable member ivar.
-
Then create a minimal, reproducable example and post it in a bug report.
-
thanks, I do appreciate your attention to my post but I may not get to creating a bug report for awhile. I came across a solution that seems to be working link text
In my case, just before looping and appending images I added
m_image->relationModel(2)->select();
