Other [B4X] KeyValueStore2 -order is not preserved

LucaMs

Expert
Licensed User
Longtime User
I had a problem using KeyValueStore2 and, "investigating", saw it uses "INSERT OR REPLACE".
This doesn't work well in SQLite (almost certainly due to the rowid field).

The problem I had was that I couldn't preserve the order of the data by using Put (actually PutEncrypted but it doesn't matter) to change a value of an existing key.

Testing "INSERT OR REPLACE" with a tool:

CREATE TABLE main(key TEXT PRIMARY KEY, value NONE);
INSERT OR REPLACE INTO main VALUES('Site 0', 'AAA');
INSERT OR REPLACE INTO main VALUES('Site 1', 'BBB');
INSERT OR REPLACE INTO main VALUES('Site 2', 'CCC');

1618508652877.png


... then...

INSERT OR REPLACE INTO main VALUES('Site 1', '111');

1618508731756.png


Next, reading all the data using:
B4X:
For Each Key As String In mKVS2.ListKeys
i got the data in that order, instead of the original order (note: the key value may not be done that way, not sortable like that).


At that point I modified KVS2 to be able to keep the order of the data as if it were a Map. Added also:
- Replace (so that you can change a key)
- ReplaceEncrypted
- Changed PutMapAsync (not tested) because of the same reason. Note that a "PutEncryptedMap" and "PutEncryptedMapAsync" would needed.

I believe my version is fully compatible with the original. I am attaching only the bas file, instead of the B4Xlib. I would like Erel to examine it, test it and use it in his KeyValueStore2, without me being forced to unnecessarily create my own lmKeyValueStore(2).

I kept the original routines, commenting on them and written my routines with the comment: ' LucaMs 04/15/2021
 

Attachments

  • KeyValueStore.bas
    7.2 KB · Views: 273
Last edited:

LucaMs

Expert
Licensed User
Longtime User
If you require a special order, you handle it yourself.
I had decided to use encrypted KVS2 and only then did I notice this problem. I could just give a different name to my personal version of KVS2, no problem, but since I'm not selfish I thought it might be useful for others too and so I wanted to suggest and provide those changes.

From my point of view this would be an improvement on KVS2 and I don't think I cursed.

Also, I believe not many people know about the SQLite "INSERT OR REPLACE" flaw.

Ok, I'll have my own OrderedKVS library.

End.
 
Upvote 0

Sandman

Expert
Licensed User
Longtime User
So I can choose between creating my own version or directly using a db (or both).
Or do the trivial solution Erel described.

In any case I will have to avoid using KVS2, because I will forget this problem.
You'll still have the same situation if you decide to use a database. A database is never guaranteed to return values in same order as you stored them in. If order is important, you handle it yourself.

It's a little bit surprising that you expect the same order, that seems like a rookie mistake, and I don't consider you a rookie. šŸ˜•
 
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
Or do the trivial solution Erel described.

Well:

1 - I don't think that adding this feature would be a "constraint"
2 - I don't see where would be "things more complicated"
3 - creating a list and save it "might affect performance", since if you need to change a single value you have to save all.

You'll still have the same situation if you decide to use a database. A database is never guaranteed to return values in same order as you stored them in. If order is important, you handle it yourself.

It's a little bit surprising that you expect the same order, that seems like a rookie mistake, and I don't consider you a rookie.
Don't be surprised, I don't have a great memory. Furthermore, I've probably never had the need to read records in the same order they were entered. Tested right now (quickly) and the order seems to be preserved. BTW, in SQLite you just need to sort by the rowid special field.

You may not have noticed that the original intention would be to insert or UPDATE a key, just as with a Map you write a Put ("MyKey", 7) and after Put ("MyKey", 10) you will not have created a new element, by moving it to the bottom, you will have made an update of a value associated with an existing key, instead "INSERT OR REPLACE" does not perform an UPDATE, due to the rowid field.

Anyway, I did as I wrote: I simply used a DB and I will always do this way.


Thank you for your invaluable comment, @Sandman
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
Upvote 0
Top