How to migrate from UTF-16 to UTF-8

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

How to migrate from UTF-16 to UTF-8

Carsten Müncheberg
Hi,

I have a database which is UTF-16 encoded and 80% of its data is
strings. To save disk space and ideally also increase performance, I
would like to migrate it to UTF-8.

As far as I know there is no straight forward way to do this, since you
cannot ATTACH a database which has a different encoding, which would
allow you to convert the data in a safe way.

My goal is to write as few code as possible. How would you do this?

Carsten
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to migrate from UTF-16 to UTF-8

Warren Young
On Aug 28, 2017, at 9:15 AM, Carsten Müncheberg <[hidden email]> wrote:
>
> My goal is to write as few code as possible. How would you do this?

Something like this should work:

    $ sqlite3 my.db .dump | iconv -f utf-16le -t utf-8 | sqlite3 new.db
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to migrate from UTF-16 to UTF-8

Simon Slavin-3
In reply to this post by Carsten Müncheberg


On 28 Aug 2017, at 4:15pm, Carsten Müncheberg <[hidden email]> wrote:

> I have a database which is UTF-16 encoded and 80% of its data is strings. To save disk space and ideally also increase performance, I would like to migrate it to UTF-8.
>
> As far as I know there is no straight forward way to do this, since you cannot ATTACH a database which has a different encoding, which would allow you to convert the data in a safe way.
>
> My goal is to write as few code as possible. How would you do this?

1) Use the SQLite shell tool to ".dump" the existing database into a text file of SQL commands.

2) Modify the beginning of the text file by adding/replacing so it it sets

PRAGMA encoding = "UTF-8"

before any commands to create content.  It must appear before the first CREATE command.

3) Go back into the shell tool, this time supplying a name for a new database file, and use the ".read" command to read your edited text file.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users