Feature request: sqlite CLI allow `.mode line` and `.mode quote` together

(1.1) By bokwoon on 2021-09-30 03:36:43 edited from 1.0 [link] [source]

I use sqlite CLI's .mode line output format as my default, however it does not show blobs quoted as hexadecimals like .mode quote does. But if I use .mode quote I lose the ability to display each column is a separate line. Is it possible to enable both line and quote behaviours together? i.e. each column appears in its own line, but the value is quoted accordingly.

My use case is UUID values, where I have opted to use BLOB over TEXT because of the space efficiency. UUID values are not displayable in .mode line output format at all.

sqlite> .schema
CREATE TABLE sites (
    site_id BLOB,
    domain TEXT,
    subdomain TEXT
);
sqlite> .mode line
sqlite> SELECT * FROM sites;
  site_id =
   domain = 𝗡𝗨𝗟𝗟
subdomain = 𝗡𝗨𝗟𝗟
sqlite> .mode quote
sqlite> SELECT * FROM sites;
'site_id','domain','subdomain'
X'747fd11e07fa4cc18279091f257c6292',NULL,NULL

What I would prefer:

sqlite> .mode line quote
sqlite> SELECT * FROM sites;
  site_id = X'747fd11e07fa4cc18279091f257c6292'
   domain = NULL
subdomain = NULL

(2) By Donald Griggs (dfgriggs) on 2021-09-30 13:45:21 in reply to 1.1 [source]

Hi, Bokwoon,

You may well be aware of this, but for immediate relief, you can use hex():

   SELECT hex(site_id), domain, subdomain FROM sites;

To format the UUID, you can of course insert hyphens as in

https://stackoverflow.com/questions/47550860/is-it-possible-to-convert-a-sqlite-blob-column-to-a-hex-string-guid

If you're using standard formats, you might have to consider endianness and variants.

https://neosmart.net/blog/2018/converting-a-binary-blob-guid-to-text-in-sql/