Repository navigation
Format BLOBs as UniqueIdentifier #2342
Description
Describe the new feature
When you create table column with "UNIQUEIDENTIFIER" type, SQLite always stores its values as BLOB. It would be great to add human-readable formatting for such columns.
Additional info
Now SQLiteBrowser doesn't display BLOB data by default and I use the following custom display format:
substr(hex("column"), 7, 2) || substr(hex("column"), 5, 2) || substr(hex("column"), 3, 2) || substr(hex("column"), 1, 2) || '-' || substr(hex("column"), 11, 2) || substr(hex("column"), 9, 2) || '-' || substr(hex("column"), 15, 2) || substr(hex("column"), 13, 2) || '-' || substr(hex("column"), 17, 4) || '-' || substr(hex("column"), 21, 12)
(this solution came from here: https://neosmart.net/blog/2018/converting-a-binary-blob-guid-to-text-in-sql)
But it's very uncomfortable to rewrite this code for each column name.
I believe I am not the only person who need this feature.
E.g. it can be easily added to ColumnDisplayFormatDialog.cpp with the following lines (I am not a C++ developer):
ui->comboDisplayFormat->addItem(tr("UniqueIdentifier blob"), "uuidblob");
...
formatFunctions["uuidblob"] = "substr(hex(" + sqlb::escapeIdentifiercolumn_name + "), 7, 2) || substr(hex(" + sqlb::escapeIdentifiercolumn_name + "), 5, 2) || substr(hex(" + sqlb::escapeIdentifiercolumn_name + "), 3, 2) || substr(hex(" + sqlb::escapeIdentifiercolumn_name + "), 1, 2) || '-' || substr(hex(" + sqlb::escapeIdentifiercolumn_name + "), 11, 2) || substr(hex(" + sqlb::escapeIdentifiercolumn_name + "), 9, 2) || '-' || substr(hex(" + sqlb::escapeIdentifiercolumn_name + "), 15, 2) || substr(hex(" + sqlb::escapeIdentifiercolumn_name + "), 13, 2) || '-' || substr(hex(" + sqlb::escapeIdentifiercolumn_name + "), 17, 4) || '-' || substr(hex(" + sqlb::escapeIdentifiercolumn_name + "), 21, 12)";
Default displaying of UniqueIdentifier column:
Formatted displaying of UniqueIdentifier column:
Activity
justinclift commentedon Jul 23, 2020
Just to make sure I'm understanding, you're wanting us to add a
uuidblobdisplay format to the built-in list, using the code snippet above (or similar)?If so, no worries. That's probably do-able. 😄
maseal commentedon Jul 23, 2020
Yes, exactly
Add GUID display format
MKleusberg commentedon Jul 23, 2020
Thanks for the suggestion and excellent preparation, @maseal! I have just added a new display format like that. Can you check tomorrow's nightly build and see if it's working for you? 😄
justinclift commentedon Jul 30, 2020
@maseal Have you had a chance to see if the new format option works ok?
maseal commentedon Jul 30, 2020
Yes I've already tested it. It works as expected
justinclift commentedon Jul 30, 2020
Awesome! Thanks for making the suggestion @maseal. 😄
@MKleusberg Good stuff. I've added this to the 3.12.1 milestone, as the code change looks pretty simple. 😄
straypointer commentedon Dec 22, 2020
This is a great feature, but I am not seeing it in my display format dropdown list... does 'uniqueidentifier' need to be capitalized for it to work?
chrisjlocke commentedon Dec 22, 2020
What version of DB4S are you using? It'll be in the 3.12.1 version or the nightlies...
straypointer commentedon Dec 22, 2020
chrisjlocke commentedon Dec 22, 2020
Interesting - this got missed from the 3.12.1 release. Just checked the 3.12.1 and yes, the list stops at 'upper case', while it's there in the nightlies.
I'd suggest download the nightly and trying that.
https://nightlies.sqlitebrowser.org/latest/
If you download the .zip flavour (https://nightlies.sqlitebrowser.org/latest/DB.Browser.for.SQLite-win64.zip), extract it, you can run it without 'installing' and alongside your existing version.
straypointer commentedon Dec 22, 2020
thigg commentedon Nov 15, 2022
is this released already?
chrisjlocke commentedon Nov 15, 2022
Don't think its in the 3.12.2 release .. it's in the nightlies though.
https://nightlies.sqlitebrowser.org/latest/
thigg commentedon Nov 16, 2022
Any indication when this will be released? Will this be in the next release?
justinclift commentedon Feb 17, 2023
Yep. We're still figuring out when to do the next release though. 😉
alexey-leonovich commentedon Aug 7, 2024
@chrisjlocke @justinclift is this feature included in recent 3.13.0 release?
justinclift commentedon Aug 11, 2024
Should be. Everything that's been in the nightlies before we branched the 3.13.x series should be in 3.13.0.
jogibear9988 commentedon May 7, 2025
I don't see this....
jogibear9988 commentedon May 7, 2025
Sorry, list is scrollable, itt works