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
Building instructions for Windows Sep 24 17:53 13
beamstat.com is being cached by google Sep 24 16:51 1
How to.. Sep 24 16:43 2
BitText XHKhFPCDzj: ultimate bitmessage forum Sep 24 16:36 1
BitText Error Sep 24 16:32 1
BitText LIST Sep 24 16:16 2
HELP Sep 24 15:49 2
BitText ADD confirmation Sep 24 15:34 1
TTL Tweaks Sep 24 15:27 12
turn MiNode into a full client - simple task ! Sep 24 15:12 1
use MiNode py3 app to route a BM via "stream 7" Sep 24 14:07 1
BM GUI via API using monkey studio Sep 23 20:35 26
BM GUI via API using KDevelop Sep 23 18:21 1
Email client integration Sep 23 16:34 10
got green light Sep 23 10:59 3
pyBM eats too many CPU cycles Sep 23 10:36 22
Git pull error Sep 23 08:49 17
What's a status of DevTalk pseudo-mailing list? Sep 23 07:44 7
Kdevelop + qt-designer for python Sep 22 21:47 1
NATO member Turkey boast that Russian S-400 SAMs can take out American B-52s, F-22s and Tomahawks Sep 22 12:24 1
bug? pyBM eats 17% of my 6core CPU cycles SOLVED :-) Sep 22 11:41 1
bug? pyBM eats 17% of my 6core CPU cycles Sep 22 11:37 21
monkey studio Sep 22 11:06 1
MX.forum.cool Sep 22 10:56 2
so stream 7 cannot work ? Then what did I do ? Sep 22 10:44 1
How make new stream? Sep 22 10:29 22
(Qt4 security staus?) Sep 22 10:27 6
Question Sep 22 05:49 8
GUI choice is Good. Ncurses anyone? Sep 22 05:35 6
attitude Sep 22 03:21 2
(Qt4 security status?) Sep 22 02:57 2
Searching for BM address for g1itch/Dmitri Bogomolov Sep 21 15:41 4
QT designer and Monkey GUI builder for pyBM Sep 21 15:39 1
bug report: Connected hosts: 0 -- SOLVED :-) retracted Sep 21 13:35 1
bug report: Connected hosts: 0 -- SOLVED :-) Sep 21 13:32 2
Gabon has teamed up with militant conservation group Sea Shepherd Sep 21 13:17 1
twister micro blogging Sep 21 12:40 6
rating in pybitmessage snapshot build Sep 21 12:38 3
#Assange Sep 21 11:45 4
bug report: Connected hosts: 0 Sep 21 11:09 7
How would you describe the technology behind Bitmessage Sep 21 09:43 4
does anyone get BM over tor to work ? SOLVED :-) Sep 20 22:03 1
does anyone get BM over tor to work ? Sep 20 22:00 12
what needs to be in a real BM GUI via API - web Sep 20 21:55 20
what needs to be in a real BM GUI via API Sep 20 21:52 14
BM GUI via API Sep 20 21:47 1
bot bug: bot keeps sending me a list - ACK is missing Sep 20 21:38 2
get BM over tor to work ( see UBF ) Sep 20 21:32 1
GUI Sep 20 20:38 1
BM future & features Sep 20 16:41 7
Feature Mashup Sep 20 16:40 1
BitMessage Secure Station (Developer Version) open-core open-hardware project PCB routing advancement : 75% Sep 20 16:01 9
py typo-squatting Sep 20 15:41 1
does anyone get BM over tor to work ?? --> poss. solution now? Sep 20 12:09 2
does anyone get BM over tor to work ?? Sep 20 11:17 5
what needs to be in a real BM GUI Sep 20 11:14 27
Is anyone there? Sep 20 07:47 5
Rewarded help needed for scanning old amazon (Or any other website) shipment barcodes (Reward $10 in Bitcoin per scan). Sep 19 22:25 1
Bitmessage noise script Sep 19 00:48 15
BitMessage Secure Station's architecture security review : White Papers & Publications about Designing Secure Hardware and fighting Hardware Backdoors. Sep 19 00:48 4
How to compliment a female cryptographer? (joke) Sep 19 00:47 3
Great article about fighting Hardware Backdoors and how to design secure open-hardware open-core systems (BitMessage Secure Station) Sep 17 11:58 1
Question about curves Sep 16 21:27 10
Streams Sep 16 10:39 4
BitMessage Secure Station open-core open-hardware project news. Sep 15 22:07 8
Tor Browser Sandbox Sep 13 21:39 1
BitMessage Secure Station "version developer" security update Sep 13 20:56 1
ISS Space Station - Augmented Virtual Reality Sep 13 12:41 2
TTL of pubkey requests Sep 13 05:52 2
encryption in BM Sep 12 16:55 20
BM connection security Sep 12 08:26 8
First pictures of the in routing PCB of the BitMessage Secure Station, version developper (Implemented with a PIC 24 Microcontroller, and not a Spartan 6 FPGA) Sep 11 13:37 1
Another Question about curves Sep 11 09:49 3
onion issues/questions? Sep 11 06:53 32
CAN SOMEONE REVERSEENGINEER? Sep 9 15:22 42
bitmessage has forward secrecy? Sep 9 08:35 17
mixmaster / mixminion routing in bitmessage Sep 9 08:23 6
Passphrase encryptsion for keys.dat? Sep 8 20:33 14
Bitmessage Addresses Question Sep 8 09:46 2
FEATURE REQUEST: PGP SIGNING Sep 7 00:55 6
Anatomy of encrypted payload Sep 6 08:24 8
BM / singleWorker.py Sep 6 07:07 6
Bitmessage Mix Routing Sep 6 03:32 1
Unencrypted image Sep 5 20:22 5
Do PoW but don't send the object Sep 5 09:05 2
Question on BM source code Sep 5 08:42 3
ENCRYPTION IN BITMESSAGE Sep 5 08:26 15
What does this mean? Sep 2 05:03 3
Anybody willing to share their xmpp IDs? Sep 1 15:05 1
Short Addresses Sep 1 01:40 35
Bitmessage and Namecoin Aug 31 06:45 3