Storing Hex Emoji as Blob Instead of Text in SQLite via TCL

Issue Overview: Hexadecimal Emoji Representation Stored as Blob Despite TEXT Column Definition

The core issue revolves around storing hexadecimal representations of emojis (e.g., f09f9299 for 💙) in SQLite database columns defined as TEXT, only to have them persistently stored as BLOB values. This creates a mismatch between the intended storage type (text) and the actual storage type (blob), leading to failed query comparisons (e.g., SELECT ... WHERE column = 'f09f9299' returning no results). The problem manifests in environments where TCL scripts interact with SQLite databases, particularly when handling Unicode characters converted to hexadecimal strings. The critical symptoms include:

1. Unexpected BLOB Storage: Hexadecimal strings derived from emoji Unicode code points are stored as BLOB despite column definitions explicitly declaring TEXT types.
2. Query Mismatches: Direct equality checks (WHERE column = 'hex_string') fail because the stored BLOB does not match the text literal in queries.
3. Encoding Workflow Complexity: The use of TCL procedures (binary encode hex, encoding convertto utf-8) inadvertently converts values into bytearrays, which SQLite’s TCL binding interprets as BLOBs.

This issue disrupts workflows where hexadecimal representations are used as human-readable identifiers for emojis or other Unicode characters. The mismatch between TCL’s internal data representation (bytearrays) and SQLite’s type affinity system creates a disconnect that requires careful handling of data types at insertion and retrieval stages.

Possible Causes: TCL Bytearray Handling and SQLite Type Affinity Conflicts

1. TCL Bytearray vs. String Representation

TCL distinguishes between strings and bytearrays. When binary encode hex is applied to a value, it returns a bytearray object. SQLite’s TCL binding maps TCL bytearrays directly to SQLite BLOBs, bypassing the column’s declared TEXT affinity. This occurs because SQLite’s type system prioritizes the actual data type of the inserted value over the column’s declared affinity. For example:

set valueb [encoding convertto utf-8 $value]  ;# Converts to binary data (bytearray)
set valuec [binary encode hex $valueb]       ;# Returns a bytearray, not a string

Here, valuec becomes a bytearray, which SQLite stores as a BLOB.

2. SQLite’s Type Affinity Limitations

SQLite’s TEXT affinity does not coerce BLOB values into text. When a BLOB is inserted into a TEXT column, it remains a BLOB. Queries comparing text literals to BLOBs will fail because SQLite performs byte-for-byte comparisons between BLOBs and text. For instance:

SELECT * FROM t1 WHERE c1 = 'f09f9299';  -- Fails if 'f09f9299' is stored as BLOB

3. Implicit Encoding Conversions

The use of encoding convertto utf-8 and binary encode hex introduces implicit type conversions that are not intuitive:

• encoding convertto utf-8 converts a TCL string to a UTF-8 byte sequence (bytearray).
• binary encode hex converts that bytearray into a hexadecimal representation, but retains the bytearray type.
• When passed to SQLite, the bytearray is stored as a BLOB, not as a text string.

4. TCL’s "Everything is a String" Philosophy Clash

TCL treats all values as strings internally, but certain operations (like binary encode) create non-string internal representations. SQLite’s TCL binding respects these internal types, leading to unintended BLOB storage. This clashes with the expectation that hexadecimal strings (e.g., f09f9299) should behave like ordinary text.

Troubleshooting Steps, Solutions & Fixes: Ensuring Hexadecimal Values Stored as TEXT

1. Force String Representation in TCL Before Insertion

To ensure SQLite stores values as TEXT, convert bytearrays to strings in TCL before insertion. This overrides SQLite’s default behavior of mapping bytearrays to BLOBs.

Method 1: Use string map to Convert Bytearray to String

Apply a no-op string transformation to force a string representation:

set value %F0%9F%92%99  ;# Example input (URL-encoded emoji)
set valueb [encoding convertto utf-8 $value]
set valuec [binary encode hex $valueb]
set valued [string map {} $valuec]  ;# Forces string representation
db eval { INSERT INTO t1 VALUES ($valued) }

Mechanism: string map operates on strings, so applying it to a bytearray converts the bytearray to a string. The empty map {} ensures no actual changes to the content.

Method 2: Use string trimright for Conversion

Trim zero-length content to force string conversion:

set valuec [string trimright [binary encode hex $valueb] ""]

Advantage: This method explicitly converts the bytearray to a string without altering its content.

Method 3: Append and Remove a Dummy Character

Modify the value temporarily to force string type:

set valuec "[binary encode hex $valueb]x"
set valuec [string range $valuec 0 end-1]

Use Case: Useful in scenarios where other methods interfere with data integrity.

2. Explicit Casting in SQLite Queries

Cast the column to TEXT during queries to enable comparisons with text literals:

SELECT * FROM t1 WHERE CAST(c1 AS TEXT) = 'f09f9299';

Limitation: Requires modifying existing queries and may impact performance if used extensively.

3. Modify the Insertion Query with CAST

Use SQLite’s CAST during insertion to coerce the value to TEXT:

db eval {
 INSERT INTO t1 VALUES (CAST($valuec AS TEXT))
}

Effect: Forces SQLite to treat the bytearray as a text string, overriding the default BLOB mapping.

4. Avoid Intermediate Bytearray Creation

Revise the encoding workflow to avoid creating bytearrays entirely:

proc 2Hex { input } {
 set utf8 [encoding convertto utf-8 $input]
 set hex ""
 foreach byte [split $utf8 ""] {
  append hex [format "%02x" [scan $byte %c]]
 }
 return $hex
}

Explanation: This custom procedure converts the input to UTF-8 and manually constructs a hexadecimal string without using binary encode hex, ensuring the result is a string, not a bytearray.

5. Validate Data Types at Insertion and Retrieval

Add debug steps to inspect TCL variable types and SQLite storage types:

puts [::tcl::unsupported::representation $valuec]  ;# Check if bytearray/string
db eval { SELECT typeof(c1), c1 FROM t1 }          ;# Verify storage type

Outcome: Identifies whether values are being stored as BLOB or TEXT, aiding in debugging.

6. Schema Adjustments with CHECK Constraints

Enforce text storage at the schema level using CHECK constraints:

CREATE TABLE t1 (
 c1 TEXT CHECK (typeof(c1) = 'text')
);

Limitation: SQLite does not enforce strict data types, but this raises an error if non-text data is inserted.

7. Use TCL’s format Command for Hex Conversion

Manually convert binary data to hex strings without bytearrays:

set valueb [encoding convertto utf-8 $value]
set hex ""
for {set i 0} {$i < [string length $valueb]} {incr i} {
 append hex [format "%02x" [scan [string index $valueb $i] %c]]
}

Advantage: Generates a hexadecimal string without using binary encode hex, avoiding bytearrays.

8. Update SQLite and TCL Bindings

Ensure you’re using the latest versions of SQLite and the TCL binding:

# Update TCL
sudo apt-get install tcl8.7

# Update TCL-SQLite binding
teacup install sqlite3

Rationale: Newer versions may handle type conversions more predictably.

9. Leverage SQLite’s HEX() Function for Queries

Use SQLite’s built-in HEX() function to compare BLOB contents with text:

SELECT * FROM t1 WHERE HEX(c1) = 'f09f9299';

Caveat: Requires the column to store the exact byte sequence of the hex string (e.g., f09f9299 as bytes, not as a text string).

10. Normalize Data Storage Strategy

Adopt a consistent strategy for handling hex strings:

• Option A: Store all hex strings as BLOBs and use HEX() in queries.
• Option B: Ensure all hex strings are stored as TEXT by converting bytearrays early.

Recommendation: Option B aligns with the goal of treating hex strings as human-readable identifiers.

Final Recommendation

The most robust solution is Method 1 (string map conversion) combined with Method 4 (custom hex encoding). This ensures hexadecimal values are stored as TEXT without relying on SQLite casting or query modifications. Example implementation:

proc SafeHex { input } {
 set utf8 [encoding convertto utf-8 $input]
 set hex [binary encode hex $utf8]
 return [string map {} $hex]  ;# Force string representation
}

db eval { INSERT INTO t1 VALUES ($hex) }

This approach guarantees TEXT storage while maintaining the integrity of the hexadecimal representation.