Other [B4X] KeyValueStore2 -order is not preserved

[LucaMs]

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');

... then...

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

Next, reading all the data using:

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

 

[Erel]

A key value store isn't expected to preserve the order between the items. If the order is important for you then put all the relevant items in a List and save it at a single item. You can use a custom type for the list items.

 

[LucaMs]

A key value store isn't expected to preserve the order between the items.

What kind of answer is this?!?!
Would it be bad for its health?
It's like saying that a tool might have an extra feature that can be useful (and in my case it is), that it's easy to add but I don't do it because... for no good reason.

 

[Sandman]

What kind of answer is this?!?!

It's the standard answer for a database. If you require a special order, you handle it yourself.

 

[LucaMs]

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.

 

[LucaMs]

End.

Not really ...

A key value store isn't expected to preserve the order between the items.

Maybe you could have written the same thing about Maps; but then you have specially created your own collection: B4XOrderedMap.

In this case you just need to check my changes and replace the bas!

 

[LucaMs]

Wonderfull!!! (sarcasm!)

I get an ordered "list" with B4J, not ordered with B4A (same code!).

 

[Erel]

Would it be bad for its health?

Yes. Adding such constraint, which is rarely needed, will make things more complicated and might affect performance.

The workaround is trivial:
Create a list with the ordered items and store the list as a single item.

 

[LucaMs]

So I can choose between creating my own version or directly using a db (or both).
In any case I will have to avoid using KVS2, because I will forget this problem.

 

[Sandman]

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.

 

[LucaMs]

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

 

[OliverA]

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

Please note it is only a flaw if it does not work as documented. It does work as documented, see https://sqlite.org/lang_insert.html and https://sqlite.org/lang_conflict.html . Your statement of course may just be an ambiguity of language translation

 

[LucaMs]

Please note it is only a flaw if it does not work as documented. It does work as documented, see https://sqlite.org/lang_insert.html and https://sqlite.org/lang_conflict.html . Your statement of course may just be an ambiguity of language translation

From that site-documentation:

I undestand that as: "You can write REPLACE only, if you wish, however an INSERT will be executed".
[In case of conflict... that "action"]