msgid is stored as text type

Bitmessage / PyBitmessage

Reference client for Bitmessage: a P2P encrypted decentralised communication protocol:

https://bitmessage.org/wiki/Main_Page

Other

2.83k stars 576 forks source link

msgid is stored as text type #2247

Open kashikoibumi opened 5 months ago

kashikoibumi commented 5 months ago

msgid is stored as text type. It should be binary blob.

$ sqlite3 messages.dat
SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
sqlite> SELECT TYPEOF(msgid) FROM inbox LIMIT 1;
text

hash in inventory is OK.

sqlite> SELECT TYPEOF(hash) FROM inventory LIMIT 1;
blob

PeterSurda commented 5 months ago

This is true, but there are potential problems with upgrades. That's why the sql thread should be refactored first and added tests. These are the PRs that tried that but they are too big and need cleaning up: #1794 #1999 #2150

kashikoibumi commented 5 months ago

The type of values whose declared type is blob:

inbox.msgid : text
inbox.sighash : text
inventory.hash : blob
inventory.payload : blob
inventory.tag : blob
objectprocessorqueue.data : (empty)
pubkeys.transmitdata : text
sent.msgid : text
sent.toripe : text
sent.ackdata : text
settings.key : text
settings.value : text, integer

PeterSurda commented 5 months ago

It actually isn't consistent across versions, there may be older databases which are different. That's why we need tests for this, even more for the data type change.

kashikoibumi commented 5 months ago

How about this:

Change all code at once to store all blob data as blob by using sqlite3.Binary(). Then,
Search any blob data:
- First, try SELECT by blob keys using sqlite3.Binary(key).
- When there is no match found, as a fallback, try SELECT by text key using CAST(? AS TEXT) in SQLite sintax.

I think this should be compatible to any past versions and future updates, including migrations to Python3.

kashikoibumi commented 5 months ago

I have implemented a quick workaround: #2248