BM db blob questions

[chan] bitmessage
Jul 5 01:31

In the messages.dat file we have this structure: CREATE TABLE inbox (msgid blob, toaddress text, fromaddress text, subject text, received text, message text, folder text, encodingtype int, read bool, sighash blob, UNIQUE(msgid) ON CONFLICT REPLACE); I want to get msgid, sighash, and UNIQUE in human-readable format. Are these blobs in binary format? Do they represent numbers or text? How would I properly convert them to ASCII or UTF-8?

[chan] bitmessage
Jul 5 01:39

P.S. The reason I ask is because I want to use sqlite to 'select' based on msgid and/or sighash.

[chan] bitmessage
Jul 5 02:21

CREATE VIEW "inbox_view" AS SELECT HEX("msgid") AS "msgid", "toaddress", "fromaddress", "subject", CAST("received" AS INT), "message", "folder", "encodingtype", "read", HEX("sighash") FROM "inbox";

[chan] bitmessage
Jul 5 03:48

thank you hacker. that worked. i'm googling "create view" to see what it did to the blob but what is the short explanation of this? I don't know what format the blobs are. sqlite> select msgid from inbox_view where subject is "1234"; F063E5049EC4B2333AC5AA60D97AB5664297B427BA9678E015C2F52499AFDDFF sqlite> select subject from inbox_view where msgid is "F063E5049EC4B2333AC5AA60D97AB5664297B427BA9678E015C2F52499AFDDFF"; 1234

[chan] bitmessage
Jul 5 04:00

this works, too: $ sql messages.dat 'select hex("msgid") from inbox where subject is "1234";'

[chan] bitmessage
Jul 5 04:18

The CREATE VIEW command assigns a name to a pre-packaged SELECT statement. Once the view is created, it can be used in the FROM clause of another SELECT in place of a table name. The blobs are blobs incorrectly stored as text: sqlite> SELECT TYPEOF("msgid") FROM "inbox"; text text text text sqlite> CREATE TEMP TABLE "blob_test" ("text_col" TEXT, "blob_col" BLOB); sqlite> INSERT INTO "blob_test" VALUES ('gggg', X'00000000'); sqlite> INSERT INTO "blob_test" VALUES (X'11111111', 'ffff'); sqlite> INSERT INTO "blob_test" VALUES (X'22222222', X'33333333'); sqlite> INSERT INTO "blob_test" VALUES ('hhhh', 'iiii'); sqlite> SELECT * FROM "blob_test"; gggg| |ffff """"|3333 hhhh|iiii sqlite> SELECT HEX("text_col"), HEX("blob_col") FROM "blob_test"; 67676767|00000000 11111111|66666666 22222222|33333333 68686868|69696969 sqlite> SELECT TYPEOF("text_col"), TYPEOF("blob_col") FROM "blob_test"; text|blob blob|text blob|blob text|text

[chan] bitmessage
Jul 5 05:02

So the blobs are just text marked as blob?

[chan] bitmessage
Jul 5 06:53

No, its the other way around - the blobs are just blob marked as text

[chan] bitmessage
Jul 5 07:13

hay caramba

Jul 5 07:32

It's caused by the strange way that python converts data when inserting/updating sqlite databases. I have tested code to fix pybitmessage in this respect, but I need to do backward/forward compatibility testing. Peter Surda Bitmessage core developer

[chan] bitmessage
Jul 5 07:40

Thanks - A most useful thread - I maintain a bunch of message.dat sql templates No Blobs to mangle provided PyBitmessage is deliberately kept offline when run Which Begs the feature request :: Start GUI with an instruction to Stay Offline. And also a facility to leave the GUI open for reading etc, OFFLINE when other stuff is using an active network connection.

[chan] bitmessage

Subject Last Count
BitMessage crash Nov 18 14:28 2
Tor replacement Nov 18 13:10 4
Alternative Bitmessage port for official assignment with IANA? Nov 18 02:20 1
codewordtest2 Nov 17 21:52 1
bitmessage history Nov 15 08:43 28
stream and pool diagram Nov 12 12:05 21
( ͠° ͟ ʖ ͡°) Nov 10 09:51 2
I'm back. Nov 9 17:55 1
streams and pools Nov 7 01:37 1
How to examine bitmessage objects Nov 7 00:45 5
Tor curve vs bm curve Nov 7 00:45 4
keys.dat values Nov 6 23:16 2
Bitmessage history Nov 6 08:08 9
Pseudo-mailinglist vs chans? Nov 5 21:47 2
bitmessage node rating? Nov 5 21:32 2
can I connect to both onions and standard? Nov 5 19:33 11
Bitmessage won't exit cleanly Nov 4 18:06 2
keys.dat must be encrypted Nov 4 12:09 12
Question Nov 3 20:09 5
It's actually not that hard to de-anonymize someone on bitmessage. Nov 3 19:49 8
It's actually not that hard to de-anonymize someone on Nov 2 14:18 1
Bitmessage snapshots Nov 2 13:14 3
Why chan address? Nov 1 06:26 2
HASH Q Oct 31 21:16 1
bitpetite scam Oct 31 08:34 2
GitHub Supports Islamic Clitoris Removal Oct 31 01:02 14
What exactly is the address of [chan] general? Oct 30 05:29 7
MiNode addr bug Oct 27 11:53 1
Hi, users ! Oct 27 07:18 5
No incoming connections now Oct 26 09:40 33
RE: bitmessage implementation in any other programming language Oct 23 17:01 1
disabled address still working Oct 23 12:47 8
apinotifypath Oct 22 01:11 1 error Oct 21 22:04 3