msgid is stored as text type #2247

Open
opened 2024-05-29 20:02:59 +02:00 by kashikoibumi · 5 comments
kashikoibumi commented 2024-05-29 20:02:59 +02:00 (Migrated from github.com)

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
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 2024-05-30 01:05:48 +02:00 (Migrated from github.com)

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

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 2024-05-30 02:58:11 +02:00 (Migrated from github.com)

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
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 2024-05-30 04:31:49 +02:00 (Migrated from github.com)

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.

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 2024-05-30 05:08:10 +02:00 (Migrated from github.com)

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.

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 2024-05-30 11:24:42 +02:00 (Migrated from github.com)

I have implemented a quick workaround: #2248

I have implemented a quick workaround: #2248
This repo is archived. You cannot comment on issues.
No Milestone
No project
No Assignees
1 Participants
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: Bitmessage/PyBitmessage-2024-12-08#2247
No description provided.