I have 3 MySQL tables in a parent-child-grandchild relationship. Each table has a unique auto-generated integer id. The child table includes a reference to the parent id and the grandchild includes a reference to the child id. The tables look like this:[code]Table_A Table_B Table_C
(parent) (child) (grandchild)
idA idB idC
issue idA idB
dataA dataB dataC[/code]For each entry in table A there can be multiple entries in table B. Similarly, for each entry in table B, there can be multiple entries in table C.
When a change is made to table B or C, the parent record in table A is copied and the issue number incremented (the old data must remain for historical purposes). All the child and grandchild records also have to be copied. Table A can be updated withINSERT INTO Table_A SET dataA={old dataA}, issue={old issue + 1} and the new idA noted.
Table B is relatively straightforward and can be updated withINSERT INTO Table_B (idA, dataB)
SELECT {new idA}, dataB FROM Table B WHERE idA={old idA}My question is (if anyone is still reading ), how can I update table C? I can find a list of idB values matching the original idA, and therefore find the associated idC values, but once I change the records in table B I cannot match the new idBs with the old idBs.
It would be nice to find a generic solution, as it looks like there may be a further 2 tables added onto the chain
When you are going through the rows in table B where idA = the old idA, just record the idB’s. Then you can insert into table B and after that insert into table c. ie:[code]#get the old data for table A
oldData = system.db.runQuery(“SELECT idA, issue, dataA FROM Table_A WHERE …”)
oldIdA = oldData[0][0]
oldIssue = oldData[0][1]
oldDataA = oldData[0][2] #add the new row to table A and save the new id
newIdA = system.db.runUpdateQueryGetKey(“INSERT INTO Table_A (dataA, issue) VALUES (’%s’, %d)” %(oldDataA, oldIssue))
#get the rows to update in table B
oldBRows = system.db.runQuery(“SELECT idB, dataB FROM Table_B WHERE idA=%d” %(oldIdA)) #add new rows to table B and store the new ids for table B
newIdBs = []
for brow in oldBRows:
oldDataB = brow[1]
newIdB = system.db.runUpdateQueryGetKey(“INSERT INTO Table_B (dataB, idA) VALUES (’%s’, %d)” %(oldDataB, newIdA))
#get the rows to update in table C
oldIdB = brow[0]
oldCRows = system.db.runQuery("SELECT dataC FROM Table_B WHERE idB=%d" %(oldIdB))
#add new rows to table C
for crow in oldCRows:
oldDataC = crow
system.db.runUpdateQuery("INSERT INTO Table_C (dataCB, idB) VALUES ('%s', %d)" %(oldDataC, newIdB))[/code]I haven't tested this code, but you should be able to get what you need out of it.
I’ve done something similar in the past. What we did then was create an audit table that had all the columns of the main table but no foreign keys. Every time the record changed, the old data was copied into the audit table, we added the time of change and who changed it. This was managed through triggers so the main application didn’t know about the audit tables.
Thanks for the replies. I could see how your idea would work Bobby, although you could potentially end up with a large number of consecutive INSERTs. I am trying to keep these to a minimum due to not being able to implement database transactions with Ignition.
I liked your idea too Robert, copying the existing data to an audit table before updating the ‘working’ table. If I’d thought of it in time, this would probably have been the simplest solution.
I ended up ditching the auto-generated IDs and, as Carl suggested, copying the unique data across all tables. It is not as elegant, but it has the advantage that while fault finding you can make sense of each table by itself without constantly having to refer to the others.
), how can I update table C? I can find a list of idB values matching the original idA, and therefore find the associated idC values, but once I change the records in table B I cannot match the new idBs with the old idBs.