Crash when viewing UUIDs

[tchoutri]

Hi! I'm using SQLite and my table's primary key is an UUID. When I click on the Content tab, sqlite_web crashes with this exception:

OperationalError: Could not decode to UTF-8 column 'id' with text '��6.��N���؎b�w�'

Full exception

[2017-11-16 10:59:42,191] ERROR in app: Exception on /chans/content/ [GET] Traceback (most recent call last): File "/home/tchoutri/.local/lib/python2.7/site-packages/flask/app.py", line 1982, in wsgi_app response = self.full_dispatch_request() File "/home/tchoutri/.local/lib/python2.7/site-packages/flask/app.py", line 1614, in full_dispatch_request rv = self.handle_user_exception(e) File "/home/tchoutri/.local/lib/python2.7/site-packages/flask/app.py", line 1517, in handle_user_exception reraise(exc_type, exc_value, tb) File "/home/tchoutri/.local/lib/python2.7/site-packages/flask/app.py", line 1612, in full_dispatch_request rv = self.dispatch_request() File "/home/tchoutri/.local/lib/python2.7/site-packages/flask/app.py", line 1598, in dispatch_request return self.view_functions[rule.endpoint](**req.view_args) File "/home/tchoutri/.local/lib/python2.7/site-packages/sqlite_web/sqlite_web.py", line 185, in inner return fn(table, *args, **kwargs) File "/home/tchoutri/.local/lib/python2.7/site-packages/sqlite_web/sqlite_web.py", line 436, in table_content total_rows=total_rows) File "/home/tchoutri/.local/lib/python2.7/site-packages/flask/templating.py", line 134, in render_template context, ctx.app) File "/home/tchoutri/.local/lib/python2.7/site-packages/flask/templating.py", line 116, in _render rv = template.render(context) File "/usr/lib/python2.7/site-packages/jinja2/environment.py", line 1008, in render return self.environment.handle_exception(exc_info, True) File "/usr/lib/python2.7/site-packages/jinja2/environment.py", line 780, in handle_exception reraise(exc_type, exc_value, tb) File "/home/tchoutri/.local/lib/python2.7/site-packages/sqlite_web/templates/table_content.html", line 1, in top-level template code {% extends "base_table.html" %} File "/home/tchoutri/.local/lib/python2.7/site-packages/sqlite_web/templates/base_table.html", line 1, in top-level template code {% extends "base_tables.html" %} File "/home/tchoutri/.local/lib/python2.7/site-packages/sqlite_web/templates/base_tables.html", line 1, in top-level template code {% extends "base.html" %} File "/home/tchoutri/.local/lib/python2.7/site-packages/sqlite_web/templates/base.html", line 58, in top-level template code {% block content %}{% endblock %} File "/home/tchoutri/.local/lib/python2.7/site-packages/sqlite_web/templates/base_table.html", line 26, in block "content" {% block inner_content %} File "/home/tchoutri/.local/lib/python2.7/site-packages/sqlite_web/templates/table_content.html", line 38, in block "inner_content" {% for row in query %} File "/home/tchoutri/.local/lib/python2.7/site-packages/peewee.py", line 3240, in __iter__ return iter(self.execute()) File "/home/tchoutri/.local/lib/python2.7/site-packages/peewee.py", line 3233, in execute self._qr = ResultWrapper(model_class, self._execute(), query_meta) File "/home/tchoutri/.local/lib/python2.7/site-packages/peewee.py", line 2912, in _execute return self.database.execute_sql(sql, params, self.require_commit) File "/home/tchoutri/.local/lib/python2.7/site-packages/peewee.py", line 3775, in execute_sql self.commit() File "/home/tchoutri/.local/lib/python2.7/site-packages/peewee.py", line 3598, in __exit__ reraise(new_type, new_type(*exc_args), traceback) File "/home/tchoutri/.local/lib/python2.7/site-packages/peewee.py", line 3768, in execute_sql cursor.execute(sql, params or ()) OperationalError: Could not decode to UTF-8 column 'id' with text '��6.��N���؎b�w�'

Do you plan to add support for UUID inspection?

[coleifer]

SQLite does not natively support a UUID data-type. SQLite knows about BLOB, TEXT, INTEGER, REAL and NULL...that's it. Is the UUID stored in a BLOB column?

[tchoutri]

Hmm, since the column type isn't TEXT then I'll assume that yes, it's a BLOB…

SQLite version 3.20.1 2017-08-24 16:21:36
Enter ".help" for usage hints.
sqlite> .schema
[…]
CREATE TABLE IF NOT EXISTS "chans" ("id" UUID PRIMARY KEY, "name" TEXT NOT NULL, "inserted_at" NAIVE_DATETIME NOT NULL, "updated_at" NAIVE_DATETIME NOT NULL);

Moreover:

[coleifer]

With SQLite you can specify arbitrary/bogus types, but internally it has only the type "affinities" I listed earlier. UUID is not recognized, and is thus treated as TEXT (hence why the app is trying to decode it as UTF-8). Might try specifying id BLOB and checking if that fixes it?

[tchoutri]

I changed the type in my migration file, but it didn't help peewee :/

  File "/home/tchoutri/.local/lib/python2.7/site-packages/peewee.py", line 3830, in execute_sql
    cursor.execute(sql, params or ())
OperationalError: Could not decode to UTF-8 column 'id' with text '}�˅
�G��3i��@'

Instead of wanting to decode that, wouldn't it be easier to catch the exception and display a placeholder text like "binary data"? It's a rough guess, I never wrote python but I heard that the language uses exception-based programming a lot. :)

[coleifer]

Hmm... I'm sorry to have wasted your time, I thought that would fix it.