UTF8 issues with _id in SQLite3

[netsensei]

Problem

I'm trying to import a CSV file with UTF8 data in a SQLite store using the DBI module. The _id field has values which contain non-ASCII characters (é, à, ë,...) because these are names. When I'm trying to convert the SQLite database back again to JSON, CSV,... the characters in the _id field are garbled:

So this Abbate, Nicolò dell' becomes this Abbate, Nicolò dell'

Steps to reproduce

Given this CSV file called "creator.csv"

_id,id,rkd_uri,viaf_uri,wikidata_uri,viaf_alternate
"Abbate, Nicolò dell'",20001109,https://rkd.nl/explore/artists/112,http://viaf.org/viaf/88804962,,"Nicolo Abate,Niccolo,Niccolò dell'Abate,Niccolò Dell'Abbate,Nicolo Dell'Abbate,Նիկոլո դել Աբատե,Niccolo Abbate,Niccolò Dell'Abate,Niccolò dell' Abbate,Abbate,Nicolo dell'Abate,Messer Niccolo,Niccolino dell'Abate,Nicolo dell'Abbate,Nicolò Abbati,Niccolò del Abbate,尼科洛·德尔·阿巴特,Nicolas Labbé,Nicolò ˜dellœ Abbate,Nicolò dell’Abbate,Niccolo ˜dell'œ Abate,Niccolò dell' Abate,Niccolò Abate,Niccolino Dell' Abbate,Niccolò dell' Abatti,Niccolò dell' Abate,Messer Niccolò,Nicolo dell' Abate,Nicolò Dell'Abate,Abati,Messer Niccolino,Nicolò dell' Abate,Nicolò dell'Abbate,Niccolo dell' Abbate,Nicolas Labbé,Niccolò Pisano de Brusis,Nicolas de Batty,Nicolas de Labaty,Нікколо дель Аббате,ニコロ・デッラバーテ,Abbate Nïkolo delʹ,Messer Niccolò,Nicolò dell' Abate,Никколо дель Аббате,Niccolò Pisano di Bartolomeo,Nicolò dell Abbate,Niccolo dell’Abbate,Nicolo Dell'Abate,Niccolò Dell' Abate,Nicolò Dell'Abate,Nicolò dell'Abate,Niccolò Dell'Abbate,Niccolò dell'Abate,أبيات نيقولا دل,Dell'Abate,Nicolò Abbati,Niccolò dell'Abbate,Niccolo dell'Abate,Nicolo del Abati,Nikolo del Abate,ابباتە نىيكولو دەل,Nicolo del Abbati,Niccolò dell' Abbate,Nicolo de Labbaty,Niccolo di Bartolomeo Pisano,Niccolo dell' Abate,Nicola,Nicolo ˜delœ Abbati,Nicolò dell Abbate,Niccolò,Niccolò Abbati,Nicolaio,Niccolo Dell'Abbate,Dell'Abbate,Niccolò Pisano,Nicolo dell Abate,Niccolo ˜dell'œ Abbate,Nicolas Abbati,Niccolò Abbati,Николо дел Абате,1470 Niccolò,Аббате Николо дель,Niccolino,Abate,Niccolò,Niccolo' dell' Abate,Niccolò Abatti,Nicolo ˜delœ Abati"

I'm import this into a SQLite database using DB like this:

catmandu import CSV to DBI --data_source dbi:SQLite:/tmp/creators.sqlite < creators.csv

And then I go back to JSON (or any other format) with this:

catmandu export DBI --data_source dbi:SQLite:/tmp/creators.sqlite to JSON --pretty 1

Which yields this output:

[{
   "wikidata_uri" : "",
   "_id" : "Abbate, Nicolò dell'",
   "viaf_alternate" : "Nicolo Abate,Niccolo,Niccolò dell'Abate,Niccolò Dell'Abbate,Nicolo Dell'Abbate,Նիկոլո դել Աբատե,Niccolo Abbate,Niccolò Dell'Abate,Niccolò dell' Abbate,Abbate,Nicolo dell'Abate,Messer Niccolo,Niccolino dell'Abate,Nicolo dell'Abbate,Nicolò Abbati,Niccolò del Abbate,尼科洛·德尔·阿巴特,Nicolas Labbé,Nicolò ˜dellœ Abbate,Nicolò dell’Abbate,Niccolo ˜dell'œ Abate,Niccolò dell' Abate,Niccolò Abate,Niccolino Dell' Abbate,Niccolò dell' Abatti,Niccolò dell' Abate,Messer Niccolò,Nicolo dell' Abate,Nicolò Dell'Abate,Abati,Messer Niccolino,Nicolò dell' Abate,Nicolò dell'Abbate,Niccolo dell' Abbate,Nicolas Labbé,Niccolò Pisano de Brusis,Nicolas de Batty,Nicolas de Labaty,Нікколо дель Аббате,ニコロ・デッラバーテ,Abbate Nïkolo delʹ,Messer Niccolò,Nicolò dell' Abate,Никколо дель Аббате,Niccolò Pisano di Bartolomeo,Nicolò dell Abbate,Niccolo dell’Abbate,Nicolo Dell'Abate,Niccolò Dell' Abate,Nicolò Dell'Abate,Nicolò dell'Abate,Niccolò Dell'Abbate,Niccolò dell'Abate,أبيات نيقولا دل,Dell'Abate,Nicolò Abbati,Niccolò dell'Abbate,Niccolo dell'Abate,Nicolo del Abati,Nikolo del Abate,ابباتە نىيكولو دەل,Nicolo del Abbati,Niccolò dell' Abbate,Nicolo de Labbaty,Niccolo di Bartolomeo Pisano,Niccolo dell' Abate,Nicola,Nicolo ˜delœ Abbati,Nicolò dell Abbate,Niccolò,Niccolò Abbati,Nicolaio,Niccolo Dell'Abbate,Dell'Abbate,Niccolò Pisano,Nicolo dell Abate,Niccolo ˜dell'œ Abbate,Nicolas Abbati,Niccolò Abbati,Николо дел Абате,1470 Niccolò,Аббате Николо дель,Niccolino,Abate,Niccolò,Niccolo' dell' Abate,Niccolò Abatti,Nicolo ˜delœ Abati",
   "viaf_uri" : "http://viaf.org/viaf/88804962",
   "id" : "20001109",
   "rkd_uri" : "https://rkd.nl/explore/artists/112"
}]

Notice how the data in the other fields are all okay, but the data in the _id field is not.

Probable causes

The problem is that the _id field is a TEXT type, while all other fields are stored as a binary blob in SQLite. So, that explains why it works perfectly for the other fields like "viaf_alternate". It fails for the _id field because somewhere, somehow the UTF-8 conversion is not done correctly.

On the CLI, inspecting the SQLite database with sqlite3 yields this:

$ sqlite3 /tmp/creators.sqlite
SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
sqlite> select * from data;
{"wikidata_uri":"","rkd_uri":"https://rkd.nl/explore/artists/112","id":"20001109","viaf_alternate":"Nicolo Abate,Niccolo,Niccolò dell'Abate,Niccolò Dell'Abbate,Nicolo Dell'Abbate,Նիկոլո դել Աբատե,Niccolo Abbate,Niccolò Dell'Abate,Niccolò dell' Abbate,Abbate,Nicolo dell'Abate,Messer Niccolo,Niccolino dell'Abate,Nicolo dell'Abbate,Nicolò Abbati,Niccolò del Abbate,尼科洛·德尔·阿巴特,Nicolas Labbé,Nicolò ˜dellœ Abbate,Nicolò dell’Abbate,Niccolo ˜dell'œ Abate,Niccolò dell' Abate,Niccolò Abate,Niccolino Dell' Abbate,Niccolò dell' Abatti,Niccolò dell' Abate,Messer Niccolò,Nicolo dell' Abate,Nicolò Dell'Abate,Abati,Messer Niccolino,Nicolò dell' Abate,Nicolò dell'Abbate,Niccolo dell' Abbate,Nicolas Labbé,Niccolò Pisano de Brusis,Nicolas de Batty,Nicolas de Labaty,Нікколо дель Аббате,ニコロ・デッラバーテ,Abbate Nïkolo delʹ,Messer Niccolò,Nicolò dell' Abate,Никколо дель Аббате,Niccolò Pisano di Bartolomeo,Nicolò dell Abbate,Niccolo dell’Abbate,Nicolo Dell'Abate,Niccolò Dell' Abate,Nicolò Dell'Abate,Nicolò dell'Abate,Niccolò Dell'Abbate,Niccolò dell'Abate,أبيات نيقولا دل,Dell'Abate,Nicolò Abbati,Niccolò dell'Abbate,Niccolo dell'Abate,Nicolo del Abati,Nikolo del Abate,ابباتە نىيكولو دەل,Nicolo del Abbati,Niccolò dell' Abbate,Nicolo de Labbaty,Niccolo di Bartolomeo Pisano,Niccolo dell' Abate,Nicola,Nicolo ˜delœ Abbati,Nicolò dell Abbate,Niccolò,Niccolò Abbati,Nicolaio,Niccolo Dell'Abbate,Dell'Abbate,Niccolò Pisano,Nicolo dell Abate,Niccolo ˜dell'œ Abbate,Nicolas Abbati,Niccolò Abbati,Николо дел Абате,1470 Niccolò,Аббате Николо дель,Niccolino,Abate,Niccolò,Niccolo' dell' Abate,Niccolò Abatti,Nicolo ˜delœ Abati","viaf_uri":"http://viaf.org/viaf/88804962"}|Abbate, Nicolò dell'
sqlite> .schema data
CREATE TABLE "data"("data" BLOB,"id" TEXT UNIQUE NOT NULL);

So, if you look at the output, you'll notice that the characters in the _id field are stored as not-encoded. It's just that when Catmandu::DBI retrieves them, somewhere it gets converted / garbled.

Any ideas what could go wrong here?

[netsensei]

For reference: using the latest version of Catmandu::DBI on OSX Sierra.

[phochste]

Same problem in MySQL and Postgres

[nicolasfranck]

@netsensei Look more closely at the field "viaf_alternate", where these characters appear: Nicolò �dell� Abbate

and others too

So it is not only a problem in the field _id

[nicolasfranck]

@netsensei oops, those characters were the same in the CSV as you posted here

[netsensei]

@nicolasfranck Yeah 😀 I pulled those from VIAF via Catmandu::VIAF. Other characters do work correctly, so those are an issue on their end, it seems to me.

[netsensei]

@phochste Tested this. This works for me 👍 Thanks!

[nicolasfranck]

@netsensei tested what? ;-)

[nics]

fixed in 0.0701