Android Question Reordering rows in SQLite [SOLVED]

[Didier9]

I have a project based on the SQLiteLight4 example.
I have stripped it to the minimum to show the problem.
I have added functionality to move rows up or down. I do it by creating a temp copy of the table in a different order, and then I copy the temp table over the old table.
It seems that the table is resorted back to the old order when the program closes. More specifically, the rowID of the records change between the time the program is closed and reopened, or so it seems.

Here is the code that displays the table:

Query = "SELECT Name, SchedC FROM Businesses ORDER BY rowID "
wbvTable.LoadHtml( Utils.ExecuteHtml( Starter.SQL1, Query, Null, 0, True ))
ReadDataBaseIDs ' this is the code from SQLiteLight4

Here is the code that moves the selected row one up:

' move the selected row one up
Sub btnUp_Click
    Dim Query, s As String
    Dim Cursor1 As Cursor

    If Starter.SelectedBusinessID = 1 Then Return

    ' start a transaction so we do everything or nothing
    Starter.SQL1.BeginTransaction
  
    ' create tempBusinesses table
    Starter.SQL1.ExecNonQuery( "DROP TABLE IF EXISTS tempBusinesses" )
    Query = "CREATE TABLE tempBusinesses( Name TEXT, SchedC TEXT )"
    Starter.SQL1.ExecNonQuery( Query )

    ' get all the Businesses from the database
    Query = "SELECT * FROM Businesses "
    Cursor1 = Starter.SQL1.ExecQuery( Query )
    Log( "SelectedBusinessID: " & Starter.SelectedBusinessID ) ' SelectedBusinessID starts at 1, row starts at 0
    For Row = 0 To Cursor1.RowCount - 1
        If Row = Starter.SelectedBusinessID-2 Then
            ' here we are at the row BEFORE the selected one
            ' so we copy the data from the next row into the new table
            Cursor1.Position = Row + 1
        Else If Row = Starter.SelectedBusinessID-1 Then
            ' here we are at the row that is selected
            ' so we copy the data from the previous row into the new table
            Cursor1.Position = Row - 1
        Else
            Cursor1.Position = Row
        End If
        s = Cursor1.GetString2( 1 )
        If s = Null Then s = ""
        Query = "INSERT INTO tempBusinesses( rowID, Name, SchedC ) VALUES( " & (Row+1) & ", '" & Cursor1.GetString2( 0 ) & "', '" & s & "' )"
        Log( Query )
        Starter.SQL1.ExecNonQuery( Query )
    Next
  
    ' copy the temp table back over the Business table
    Starter.SQL1.BeginTransaction ' <=== this statement was causing the problem!
    Try
        Starter.SQL1.ExecNonQuery( "DROP TABLE IF EXISTS Businesses" )
        Starter.SQL1.ExecNonQuery( "CREATE TABLE Businesses( Name TEXT, SchedC TEXT )" )
        Starter.SQL1.ExecNonQuery( "INSERT INTO Businesses( rowID, Name, SchedC ) SELECT rowID, Name, SchedC FROM tempBusinesses" )
        Starter.SQL1.TransactionSuccessful
    Catch
        Log( "Transaction failed" )
    End Try
    Starter.SQL1.EndTransaction
  
    ' update the display
    ShowTableBusinesses
  
    ' can we continue or not?
    If Starter.SelectedBusinessID > 1 Then
        Starter.SelectedBusinessID = Starter.SelectedBusinessID - 1
    Else
        lblSelectedItem.Text = ""
        btnUp.Visible = False
        btnDown.Visible = False
    End If
End Sub ' btnUp_Click

About the example project:
There is a table with 5 entries. They are displayed sorted by rowID.
I have added a pair of buttons to allow moving rows up and down to let the user place the entries in the order that he or she wants. It does not have to be alphabetical.
Only the Up button actually rearranges the table. It does work as you can see it reorganizes the rows when you select a row first, then press the Up button.
For now, the down button only prints the table in the Log for debugging.
However, once you exit the program and restart, the table comes back in the old order.

I would rather not have to add a separate "index" column because I have the app on several machines and some have a large existing database that I would prefer not to have to recreate. In any case, I am not sure what a separate column would do that the rowID would not do.

Thanks in advance for any suggestion.

 

[Didier9]

Here is a copy of the Log. I have added a call to btnDown_Click in Activity_Resume to show the order of the table when the app starts, I select the row "Personal", I press the Up button 3 times, and I make another call to btnDown_Click to show the new table order before exiting.

*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
** Activity (main) Pause, UserClosed = false **
** Activity (businesses) Create, isFirst = true **
** Activity (businesses) Resume **
1, Art Shop, *
2, Rental 1, *
3, Rental 2, *
4, Personal,
URL: http://0.3.com/
SelectedBusinessID: 4
INSERT INTO tempBusinesses( rowID, Name, SchedC ) VALUES( 1, 'Art Shop', '*' )
INSERT INTO tempBusinesses( rowID, Name, SchedC ) VALUES( 2, 'Rental 1', '*' )
INSERT INTO tempBusinesses( rowID, Name, SchedC ) VALUES( 3, 'Personal', '' )
INSERT INTO tempBusinesses( rowID, Name, SchedC ) VALUES( 4, 'Rental 2', '*' )
SelectedBusinessID: 3
INSERT INTO tempBusinesses( rowID, Name, SchedC ) VALUES( 1, 'Art Shop', '*' )
INSERT INTO tempBusinesses( rowID, Name, SchedC ) VALUES( 2, 'Personal', '' )
INSERT INTO tempBusinesses( rowID, Name, SchedC ) VALUES( 3, 'Rental 1', '*' )
INSERT INTO tempBusinesses( rowID, Name, SchedC ) VALUES( 4, 'Rental 2', '*' )
SelectedBusinessID: 2
INSERT INTO tempBusinesses( rowID, Name, SchedC ) VALUES( 1, 'Personal', '' )
INSERT INTO tempBusinesses( rowID, Name, SchedC ) VALUES( 2, 'Art Shop', '*' )
INSERT INTO tempBusinesses( rowID, Name, SchedC ) VALUES( 3, 'Rental 1', '*' )
INSERT INTO tempBusinesses( rowID, Name, SchedC ) VALUES( 4, 'Rental 2', '*' )
1, Personal,
2, Art Shop, *
3, Rental 1, *
4, Rental 2, *

It clearly shows the table order being changed, but when I restart the app, the order is back to the original order. I can do it as many times as I want...

 

[Didier9]

Found the problem...

I had an extra call to
Starter.SQL1.BeginTransaction
before the Try statement.

 

[keirS]

What I see is the expected behavior from SQLite. RowID's in SQLite can change at any time as they are not guaranteed to be persistent. See here for details.

 

[KMatle]

I don't get it. I have several db's with tables using a position column. This has nothing to do with "RowID". Do not use reserved parms of SQlite as you don't want to change the row's id. What you want is to sort your data using a column. That's all.

 

[Didier9]

What I see is the expected behavior from SQLite. RowID's in SQLite can change at any time as they are not guaranteed to be persistent. See here for details.

The rowID does not change willy nilly "at any time", but it may change when you make other, unrelated changes to the table, like adding or deleting records. It is actually documented http://www.sqlabs.com/blog/2010/12/sqlite-and-unique-rowid-something-you-really-need-to-know/

I have been using this app for over a year and over a thousand transactions on another table in the same database, the rows have not changed in that table unless I made a change to the table of course. They do not change on their own.

The problem was, as often, a stupid mistake on my part: an unclosed SLQ1.BeginTransaction statement which prevented my changes to become permanent.
I have found that many times by the time I write a post on the forum explaining my problem, the solution becomes obvious. With the modification highlighted in the code posted in the first post, it is now working fine.

In my case. the rowID is unimportant but the order of the records is, and as I pointed out in the original post, I did not want to add a column to the table in order to be able to change the row order. I realize this creates a new problem but it is now resolved. My app does not depend on any particular value in the rowID, I simply use it for that reordering function.

 

[Didier9]

I don't get it. I have several db's with tables using a position column. This has nothing to do with "RowID". Do not use reserved parms of SQlite as you don't want to change the row's id. What you want is to sort your data using a column. That's all.

Agreed, but for the reasons explained above, I did not want to add a column to that table.

I should point out that the row order in that table is not actually critical, it is for convenience but even if it were to change on its own, it would not break the app. I may cause the user some consternation at the most If it were critical, there would be a column to order it by.

 

[keirS]

The rowID does not change willy nilly "at any time", but it may change when you make other, unrelated changes to the table, like adding or deleting records. It is actually documented http://www.sqlabs.com/blog/2010/12/sqlite-and-unique-rowid-something-you-really-need-to-know/

You obviously didn't look at the link in my post. It links to the documentation at SQlite.org which is the official site of SQLite so I am well aware it is documented. Dropping a table and recreating it just to change the order of the records is a very bad solution to sorting records. What happens if the app crashes halfway through the process?

 

[Didier9]

You obviously didn't look at the link in my post. It links to the documentation at SQlite.org which is the official site of SQLite so I am well aware it is documented. Dropping a table and recreating it just to change the order of the records is a very bad solution to sorting records. What happens if the app crashes halfway through the process?

I did look at the link. It raises a lot of warnings but is short on specifics as to why. I rarely satisfy myself with statements like "don't do this" when the reasons are not obvious without explanation.

That's why I use the BeginTransaction and EndTransaction tags. They effectively prevent corruption if something happens during execution of the critical statements. I agree that sorting the tables that way is not the best way, and if I had to start from scratch I would do differently. However, I have seen it done in many examples and in this case, it seems to be an acceptable trade off because I did not want to add a column to the existing tables as it would have broken a fair amount of code. The 4 tables that are handled that way are short (a dozen records typically) and not updated frequently. The main table in this app is sorted by date.

What I find interesting is that in response to this post, I have gotten several comments, none of them applicable or even helpful. We are having this discussion that is 100% unrelated to the actual problem itself. It seems that people are more interested in latching on a pet peeve and drag on it rather than actually help.

I took the time to strip my project down to the absolute minimum to demonstrate the problem down to a 15k download, post it with pictures, yet nobody bothered to install it and look at the problem. Instead all the responses were totally unrelated. I would call them "shoot from the hip" responses based on a quick read of the initial post. I find myself having to defend the original construct of this app which, since it is based on the SQLiteLight4 example I did not write.

But as I pointed out earlier, the process was helpful to me since it allowed me to solve the problem myself, the forum just being the necessary pretext. I learned something too.