Running the code below seems to prove that the rowid on a table that has a row deleted is not keeping the rowid but re-assigning it again to the next inserted row. This has big implications for my database and is not how sqlite rowid works. Am I doing something wrong?
This is the log output for the code below
Insert 3 new rows
Loop through to read rowid and then delete that row
11
12
13
Now insert another 3 new rows
Read the list of rowid and it shows new rowids have not been added but given the ids of the 3 deleted rows:
11
12
13
Log("Insert 3 new rows")
For i = 0 To 2
Main.sql.ExecNonQuery($"INSERT INTO plots (siteID, userID) VALUES (${i},55)"$)
Next
Log("Loop through to read rowid and then delete that row")
Dim RS As ResultSet = Main.sql.ExecQuery("SELECT rowid, * FROM plots WHERE userID = 55")
Do While RS.NextRow
Log(RS.GetInt("rowid"))
Main.sql.ExecNonQuery($"DELETE FROM plots WHERE rowid = ${RS.GetInt("rowid")}"$)
Loop
Log("Now insert another 3 new rows")
For i = 0 To 2
Main.sql.ExecNonQuery($"INSERT INTO plots (siteID, userID) VALUES (${i},55)"$)
Next
Log("Read the list of rowid and it shows new rowids have not been added but given the ids of the 3 deleted rows:")
Dim RS As ResultSet = Main.sql.ExecQuery("SELECT rowid, * FROM plots WHERE userID = 55")
Do While RS.NextRow
Log(RS.GetInt("rowid"))
Loop
Regards,
Simon
This is the log output for the code below
Insert 3 new rows
Loop through to read rowid and then delete that row
11
12
13
Now insert another 3 new rows
Read the list of rowid and it shows new rowids have not been added but given the ids of the 3 deleted rows:
11
12
13
Log("Insert 3 new rows")
For i = 0 To 2
Main.sql.ExecNonQuery($"INSERT INTO plots (siteID, userID) VALUES (${i},55)"$)
Next
Log("Loop through to read rowid and then delete that row")
Dim RS As ResultSet = Main.sql.ExecQuery("SELECT rowid, * FROM plots WHERE userID = 55")
Do While RS.NextRow
Log(RS.GetInt("rowid"))
Main.sql.ExecNonQuery($"DELETE FROM plots WHERE rowid = ${RS.GetInt("rowid")}"$)
Loop
Log("Now insert another 3 new rows")
For i = 0 To 2
Main.sql.ExecNonQuery($"INSERT INTO plots (siteID, userID) VALUES (${i},55)"$)
Next
Log("Read the list of rowid and it shows new rowids have not been added but given the ids of the 3 deleted rows:")
Dim RS As ResultSet = Main.sql.ExecQuery("SELECT rowid, * FROM plots WHERE userID = 55")
Do While RS.NextRow
Log(RS.GetInt("rowid"))
Loop
Regards,
Simon