SQLite rowid

Databases
1

I’m working on a custom combobox, and an using an SQLite in memory database to hold the list. Everything works great so far except I’m running into a problem with removerow and insertrow. The problem is I’m using rowid for my list index. When i delete rows from the table it leaves a ‘hole’ in the rowid sequence. Adding a row is also a problem because is I want to insert at the correct position I first need to increase all the higher rowid’s.

I’ve been wracking my brain but can’t think of a solution. Any ideas? A totally different approach? I really need to use a database for the list because of the filtering capabilities needed.

2

use the listbow rowtag property to store your rowid
the database rowid are not following each other.

3

Ok this seems a little expensive but it works. I’m still open to suggestions.

'InsertRow(5000)
BEGIN TRANSACTION;
CREATE TABLE temp1 AS SELECT * FROM temp WHERE rowid < 5000;
INSERT INTO temp1 (rowid, f1) VALUES (5000, 'test insert');
INSERT INTO temp1 SELECT * FROM temp WHERE rowid > 4999;
DROP TABLE temp;
ALTER TABLE temp1 RENAME TO temp;
COMMIT ; 

'RemoveRow(5000)
BEGIN TRANSACTION;
CREATE TABLE temp1 AS SELECT * FROM temp WHERE rowid < 5000 OR rowid > 5000;
DROP TABLE temp;
ALTER TABLE temp1 RENAME TO temp;
COMMIT ;
4

RowID are unique… if you delete a row, it WILL leave a hole… you can fill the hole if you defined a AutoInc Integer PK… and insure you assign it a value that does not exist… AutoInc takes the HIGHEST value and inserts it if you supply NULL

But in any case for what you are trying… don’t use “rowid” directly… assign your own PK

also… if you have ROWID but NOT AutoInc PK… VACUUM will compress those holes… but if you have your own PK it will not

5

[quote=333156:@Jean-Yves Pochez]use the listbow rowtag property to store your rowid
the database rowid are not following each other.[/quote]

Normally that would work but not in this case.
The dropdown listbox shows dynamically changing data that is filtered and loaded as the user types in the combobox.
I store the rowid in the listbox so when a user clicks the item I can find it in the underlying db.
The underlying db contains all the data for the combobox and needs to contain an integer field to be used as the listindex.
The real problem is that when a row is remove all rows with a higher index are changed.

I could do that but it still doesn’t circumvent the need to change every single id when a record is deleted.

… and VACUUM is painfully slow.

6 
update mytable set myPK=myPK+1 where myPK>5000

but again… do not reference ROWID directly… add your own PK field

7

Not sure what exactly you’re doing, but you are doing it wrong. You should never have to manipulate the db like that.

8

OK in a combo box you have.

0 Apple
1 Peach
2 Orange

When you do Combobox1.RemoveRow(0)

You end up with:

0 Peach
1 Orange

not

1 Peach
2 Orange

In other words the list index of all the items change when an item is removed or inserted.

9

That creates a unique constraint failed, because the records are updated in whatever order the db sees fit.

10

Right. Never rely on the listindex. Use RowTag to store the value you need.

11

I don’t think you understand. I’m creating a custom combobox. I need to allow the user to specify whatever rowtag they please.

12

You are doing something way way wrong here…
if you have a table (forget combobox or listbox)… stay with the base database table
and it has 3 records

create table myTable (fruit text)

insert into mytable values('Apple')
insert into mytable values('Peach')
insert into mytable values('Orange')

and then select that data

SELECT rowid,fruit FROM mytable

you get
1 Apple
2 Peach
3 Orange

NOTE : Starts with ONE, not ZERO

DELETE FROM mytable where Fruit='Peach'

Select then returns
1 Apple
3 Orange

The ROWID does NOT change, it will NEVER change unless you do a VACUUM

If you created a AutoInc PK, the hole would still be there, but you could then fill the hole if you wished, and VACUUM would have no effect on those values

Now, in a combobox (listbox)… the LISTINDEX DOES change, but it has NOTHING to do with the PK or RowID of the database

13

If it is a custom list box, then you have full control over adding NEW properties to support additonal rowtags etc.

14

Exactly. Add an array property to hold the PK values, and override the AddRow and RemoveRow methods to maintain this array accordingly.

15

That is exactly my problem. I’m looking for a way to ‘sync’/map the listindex to the record id.

So if my IDs are all in order, and the user does: CustomListBox1.listindex = 999 then i could do:

SELECT * FROM temp WHERE ID = 999

However once the user starts deleting rows and my ID has a gap in the sequence things won’t work.

16

That would work but the reverse would be cumbersome. So if the user selects an item from the list (of queried results) I know what the PK number is but I would need to loop through the array to find the index.

17

Sorry IndexOf might do it.

18

and your combobox is so huge that this is an issue?
If so, I’d say you need to rethink this from the beginning…
a combobox should not have more than a few dozen entries at best

19

I don’t understand your problem. When the user picks an entry, you already know the ListIndex value. Why would you need to look it up from the PK?

20

I still maintain you can use the rowtag property to store the PK
don’t need to create another property, the rowtag is already here for that purpose.
if you delete a row of the listbox, the rowtag gets deleted too
if you delete something in the database, or narrow the search, then update the listbox with the recordset values,
and re-store the rowtag of the listbox with the newly searched rowids