CODEWITHSUNDEEP
triggerssqlitehexblob
maha
maha

Reputation: 607

sqlite3 trigger to convert hex text into binary blob equivalent

I'd like an after insert trigger to convert a hex text string into its binary blob equivalent.

I've tried something like this:

CREATE TABLE data
(
   t_hex   TEXT,
   b_hex   BLOB
);

CREATE TRIGGER data_insert_trigger AFTER INSERT ON data
BEGIN
  UPDATE data SET b_hex = "x''"||t_hex||"''" WHERE rowid = new.rowid;
END;

INSERT into data(t_hex) VALUES ('A5A5');

this results in:

sqlite> select * from data;
t_hex = A5A5
b_hex = x''A5A5''

also tried

CREATE TRIGGER data_insert_trigger AFTER INSERT ON data
BEGIN
  UPDATE data SET b_hex = x''||t_hex||'' WHERE rowid = new.rowid;
END;

this results in:

sqlite> select * from data;
t_hex = A5A5
b_hex = A5A5

anyone know of a way to utilize the x'value' syntax yet reference an existing column value or perhaps some other SQL-based mechanism?

** EDIT **

Taking into consideration the custom function, thanks CL and LS_dev. For the SQL only solution that LS_dev provided here's where I'm at. I tweaked data_t_update_trigger to

CREATE TRIGGER data_t_update_trigger 
AFTER UPDATE ON data 
WHEN NEW.t_hex IS NOT OLD.t_hex
BEGIN
   UPDATE data SET b_hex = x'' WHERE ROWID = NEW.ROWID;
END;

and my test set generated:

sqlite> insert into data(t_hex) values('A5A5');
sqlite> select t_hex, hex(b_hex) from data;
A5A5|A5A5
sqlite> update data set t_hex = 'FF'; 
sqlite> select t_hex, hex(b_hex) from data;
FF|FF
sqlite> update data set t_hex = 'FFFE';
sqlite> select t_hex, hex(b_hex) from data;
FFFE|FFFE3F
sqlite> update data set t_hex = '00';
Error: too many levels of trigger recursion

working through that I qualified a few lines in the data_h_update_trigger as:

CREATE TRIGGER data_b_update_trigger
AFTER UPDATE ON data 
WHEN LENGTH(NEW.t_hex)>LENGTH(NEW.b_hex)*2
BEGIN
   UPDATE data SET b_hex = NEW.b_hex||COALESCE((
      SELECT b FROM _hb WHERE h=SUBSTR(NEW.t_hex, (LENGTH(NEW.b_hex)*2)+1, 2)
   ), CAST('?' AS BLOB)) WHERE ROWID = NEW.ROWID;
END;

and now my test set yields:

sqlite> select t_hex, hex(b_hex) from data;
A5A5|A5A5
sqlite> update data set t_hex = 'FF'; 
sqlite> select t_hex, hex(b_hex) from data;
FF|FF
sqlite> update data set t_hex = 'FFFE';
sqlite> select t_hex, hex(b_hex) from data;
FFFE|FFFE
sqlite> update data set t_hex = '00';
Error: too many levels of trigger recursion

so still dealing with some unexplained recursion. FWIW, this also happens with a statement like this:

sqlite> update data set t_hex = 'DEADBEEF';
Error: too many levels of trigger recursion

running: SQLite version 3.7.9 2011-11-01 00:52:41

Upvotes: 4

Views: 5943

Answers (2)

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11181

Using a user defined function would be much easier and inexpensive, but I got a solution.

First, you need a look-up table as this:

CREATE TABLE _hb(h TEXT COLLATE NOCASE, b BLOB);
BEGIN;
INSERT INTO _hb VALUES('00', x'00');
INSERT INTO _hb VALUES('01', x'01');
(...)
INSERT INTO _hb VALUES('A4', x'A4');
INSERT INTO _hb VALUES('A5', x'A5');
INSERT INTO _hb VALUES('A6', x'A6');
(...)
INSERT INTO _hb VALUES('FE', x'FE');
INSERT INTO _hb VALUES('FF', x'FF');
COMMIT;

Then, enabling recursive triggers (SQLite>=3.6.18):

PRAGMA RECURSIVE_TRIGGERS=1;

you may create a trigger which will progressively append bytes to b_hex:

CREATE TRIGGER data_h_update_trigger
AFTER UPDATE ON data 
WHEN LENGTH(NEW.t_hex)>LENGTH(NEW.b_hex)*2
BEGIN
   UPDATE data SET b_hex = b_hex||COALESCE((
      SELECT b FROM _hb WHERE h=SUBSTR(NEW.t_hex, LENGTH(b_hex)*2+1, 2)
   ), CAST('?' AS BLOB)) WHERE ROWID = NEW.ROWID;
END;

Two more triggers to trigger data_h_update_trigger upon data insertion or t_hex update:

CREATE TRIGGER data_insert_trigger 
AFTER INSERT ON data
BEGIN
   UPDATE data SET b_hex = x'' WHERE ROWID = NEW.ROWID;
END;

CREATE TRIGGER data_t_update_trigger 
AFTER UPDATE OF t_hex ON data
BEGIN
   UPDATE data SET b_hex = x'' WHERE ROWID = NEW.ROWID;
END;

Limitations: single step blob calculation limited to SQLITE_MAX_TRIGGER_DEPTH (1000 by default) bytes.

Upvotes: 1

CL.
CL.

Reputation: 180182

Blob literals can be used only directly in the SQL statement; they cannot be constructed dynamically from SQL code.

To convert a hex string into a blob, you would have to install your own user-defined function.

Upvotes: 1

Related Questions