Copying within QSqlRelationalDataModel

[scott_hollen]

Hi all --

I have a small master-detail app that's worked exactly as it's supposed to - very vanilla. Click on a previously saved master/parent record (QSqlTableModel) and the detail/child records display correctly filtered (QSqlRelationalTableModel). Again, works perfectly. But now I've been asked to create a COPY mechanism wherein I select a previously created master record, and copy it's detail/child records to a newly created master record -- the only thing that will be different between the two sets of detail records is their primary key/ID will point to their master records. Make sense? I correctly have the ID of the original master, the ID of the new master, and am correctly pointing to the filtered model holding the original's detail records.

I may be approaching this all wrong, but what I'm doing is basically doubling the detail records for the original master and changing the ID in the new records to be the new master ID. What's happening, though, is nothing is appearing in my model and nothing is being saved back out to the DB and no error is being generated anywhere -- I thought if I refreshed my view thru the filter that everything would be fine but my copied data seems to have just vanished.

Is there a better approach? The reason I'm needing to copy is any data saved to the database can't be edited but a copy of a existing set of detail records can be (until they're saved to the DB). I've spent about 10 hours trying to work this out.

Thanks for any non-sarcastic replies! :)


scott

My copying code:

void MainWindow::on_FlowpathcopyPB_clicked()
{
//************************************************** ****************
//* This functions takes the currently selected flowpath, *
//* prompts for a new name, and makes a copy of the data sets... *
//************************************************** ****************
QModelIndex old_index = ui->FlowpathTV->currentIndex ();
QSqlRecord old_record = flowpathmodel->record (old_index.row ());
int old_fp_id = old_record.value ("flowpath_id").toInt();

if (old_index.isValid ())
{
on_FlowpathnewPB_clicked ();

//************************************************** ***
//* Here we have the ID from the copied flowpath *
//* and the new ID of the new flowpath...Use *
//* these to cycle thru the associated data sets... *
//************************************************** ***
QModelIndex new_index = ui->FlowpathTV->currentIndex ();
QSqlRecord new_record = flowpathmodel->record (new_index.row ());
int new_fp_id = new_record.value ("flowpath_id").toInt ();

//************************************************** *****
//* Reset the index to the original flowdata model... *
//************************************************** *****
ui->FlowdatanewPB->setEnabled (true);
ui->FlowdatarevertPB->setEnabled (true);
ui->FlowdatasavePB->setEnabled (true);

flowdatamodel->setFilter(
QString("flowdata_flowpath_id = %1").arg(old_fp_id));

int num_rows_old = flowdatamodel->rowCount ();
int row = num_rows_old;

for (int i = 0; i < num_rows_old; i++)
{
//**************************************************
//* Get the records from the set to copy from... *
//**************************************************
old_index = flowdatamodel->index (i, 0);
old_record = flowdatamodel->record (old_index.row ());

flowdatamodel->insertRow(row);

flowdatamodel->setData (flowdatamodel->index (row, 0),
old_record.value("flowdata_od").toReal ());
flowdatamodel->setData (flowdatamodel->index (row, 1),
old_record.value("flowdata_id").toReal ());
flowdatamodel->setData (flowdatamodel->index (row, 2),
old_record.value("flowdate_yield").toReal ());
flowdatamodel->setData (flowdatamodel->index (row, 3),
old_record.value("flowdate_emod").toReal ());
flowdatamodel->setData (flowdatamodel->index (row, 4),
old_record.value("flowdate_val_gap").toReal ());
flowdatamodel->setData (flowdatamodel->index (row, 5),
old_record.value("flowdata_val_offset").toReal ());
flowdatamodel->setData (flowdatamodel->index (row, 6),
old_record.value("flowdate_string").toString ());
flowdatamodel->setData (flowdatamodel->index (row, 7),
old_record.value("flowdata_seq_no").toInt ());
flowdatamodel->setData (flowdatamodel->index (row, 8), user);
flowdatamodel->setData (flowdatamodel->index (row, 9),
QDateTime::currentDateTime ());
flowdatamodel->setData (flowdatamodel-> index (row, 10), new_fp_id);

new_index = flowdatamodel->index (row, 0);
ui->FlowdataTV->setCurrentIndex (new_index);
ui->FlowdataTV->edit (new_index);
ui->FlowdataTV->resizeColumnsToContents ();
ui->FlowdataTV->setColumnHidden (7, true);
ui->FlowdataTV->setColumnHidden (10, true);
ui->FlowdataTV->horizontalHeader ()->setVisible (true);

++row;
}

new_index = ui->FlowpathTV->currentIndex ();

refresh_FlowdataView ();
}
}



My code to refresh/filter the view...


void MainWindow::refresh_FlowdataView()
{
//************************************************** ********
//* This function is called to display the flowdata data *
//* set that are related to a particular flowpath... *
//************************************************** ********
QModelIndex index = ui->FlowpathTV->currentIndex ();

if (index.isValid ())
{
QSqlRecord record = flowpathmodel->record (index.row ());
int fp_id = record.value ("flowpath_id").toInt();

//************************************************** ***
//* Now we want to find out if this is new data *
//* or not as we can't allow editing of old data... *
//************************************************** ***
if (sqldb.numflowdatasetsDB (fp_id) > 0)
{
ui->FlowdatanewPB->setEnabled (false);
ui->FlowdatarevertPB->setEnabled (false);
ui->FlowdatasavePB->setEnabled (false);
}
else
{
ui->FlowdatanewPB->setEnabled (true);
ui->FlowdatarevertPB->setEnabled (true);
ui->FlowdatasavePB->setEnabled (true);
}

flowdatamodel->setFilter(QString("flowdata_flowpath_id = %1").arg(fp_id));
}
else
{
flowdatamodel->setFilter ("flowdata_flowpath_id = -1");
}

flowdatamodel->select ();
ui->FlowdataTV->horizontalHeader ()->setVisible (
flowdatamodel->rowCount () > 0);
}

[wysota]

Aren't you violating the uniqueness constraint on the primary key? Does insertRow() return true? What about submitAll()?

[scott_hollen]

Well, I tried the submitALL() and nothing happened but let me come back to that. Yep, insertRow() returns true -- just for fun, I changed line 66 to set the data to "old_fp_id" and I was able to effectively double the data saved with the dataset I'm copying. Row 1 was copied to a row 3, and row 2 copied to row 4 and off I went on my merry way.

As for the primary key, what's happening is I select on a row in my master view (call it SNOW), say COPY, and that prompts me for the name of a new master row (call it RAIN -- this is the primary key), so that's covered. I then want to copy the detail rows linked to SNOW and link those new ones to RAIN which is what you see on line #66 of the first block of code.

You gave me an idea about the primary key, though. My creation of *new* master and detail records (vs. copy) works great EXCEPT for something I just discovered. I have to submitALL() after the creation of the master record - if I don't and I try to create detail records, a submitALL() at this point results in no detail records being saved. Even though I'm not getting a DB error on my submitALL(), since I don't know the order that the records are being written out, there could be an issue where the child records can't get created in the DB correctly because the parent/primary key record doesn't exist yet.

When I leave my normal job I'm going to try a submitALL after line 14 of the first block of code (which is where the new master record is being created) and see if that helps...That should write out my new master/parent record...

Thanks for the questions -- they pulled me up out of the weeds! (BTW -- my day job is as an Oracle DB programmer so you'd think this would be a cakewalk)


scott