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

BM-2cX62WCeFcUwzXWqxTBfaAzNy4j1y8yZVm
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
BM-2cWy7cvHoq3f1rYMerRJp8PT653jjSuEdY

Subject Last Count
BM bot sourcecode sample Jul 20 15:56 7
Network Size Jul 20 15:51 3
RE: BM bot sourcecode sample Jul 20 04:01 1
BitMessage need new improvements Jul 19 23:35 3
2 bugs while quitting Jul 19 19:03 1
Brute forcing BM addr's Jul 19 13:13 22
[Feature Request/Question] Blacklist counters Jul 19 12:01 1
raspberry pi Jul 18 17:13 3
Feature request (2) Jul 17 00:44 13
Bug report (UI shows deleted messages) Jul 17 00:20 3
ECC Curves: secp256k1 versus secp521r1 -> BitMessage Secure Station Jul 16 22:18 1
PyBitmessage message save folder Jul 16 21:00 5
HELP(!) - Impossible PoW recipient address msg Jul 16 20:53 6
ECC Curves: secp256k1 versus secp521r1 Jul 16 20:44 28
Precedence of work / Priority of work Jul 16 20:43 5
I need help Jul 16 16:42 1
Pre-commitment and "open source canary" Jul 16 16:41 2
Killing impossible PoW sent items (daemon) Jul 16 16:40 1
Feature request (1) Jul 13 13:45 6
Bitmessage statistics Jul 13 09:42 13
addr command problems and plans Jul 13 06:19 7
BM client 0.6.2 (OS X) does not show images Jul 12 04:16 24
bug in latest commits Jul 11 08:46 11
Bitmessage bug in latest v0.6 branch Jul 11 06:09 1
bmwrapper broadcasts Jul 10 14:27 5
Beamstat chanlist bug? Jul 10 06:10 3
FPGA Hardware backdoors risk and counter-measures, regarding « TOR/VPN fingerprinting family anonymity breach fix » with a custom FPGA based « Single Socket » Ethernet Controller. Jul 9 11:36 1
tor socks access issues continue (0.6dEV pYbm code @ 20170706) Jul 8 23:10 3
FPGA Hardware backdoors, regarding « TOR/VPN fingerprinting family anonymity breach fix » with a custom FPGA based « Single Socket » Ethernet Controller. Jul 8 14:10 3
BM-2NB prefix ? Jul 8 13:30 3
Jabit and Abit update Jul 8 07:45 1
Peer "Ratings" Jul 7 12:54 9
How to.. Jul 6 18:01 49
Is there a quick way to export a thread, or all threads of a chan in a readable manner. Jul 6 04:00 6
Dev-talk PML status / instructions pls Jul 5 21:04 5
BM db blob questions Jul 5 07:40 11
How to connect to more nodes Jul 5 07:34 13
P.P.S. Re: BM db blob questions Jul 5 03:21 2
bitmessage API TTL Jul 5 01:08 2
Re: Re: Re: Re: Religious garbage at this point Jul 4 22:28 7
Yes it's obvious my BM private keys have been stolen.... Jul 4 22:20 34
MiNode I2P support testing Jul 2 07:04 25
To the person running /wire:0.1.0/bmd:0.0.1/ on 158.69.119.35:8444 Jul 1 12:55 9
So you think God is a myth ... Jul 1 12:14 1
How to.. (Node Conns Encrypted) Jul 1 10:17 1
Any predictions? Jun 30 12:24 3
Hello ! Jun 30 10:03 18
If I had cash Jun 30 09:56 3
"addr" commands Jun 29 22:14 2
How to.. (DNS issues and asyncore/conventional Network Access behaviour) Jun 29 13:03 3
I2P peer discovery by publishing destinations as custom objects Jun 27 03:53 4
Confused Jun 25 21:29 6
Curious Jun 25 21:23 1
Question - BM mesage model alteration idea Jun 25 18:39 4
Privacy? I don't have anything to hide. Jun 25 18:30 4
Support (connections are "stuck" when using TOR+iptables) Jun 24 21:10 7
Wondering Jun 23 21:36 5