#!/usr/bin/env perl
#
# Source: http://savage.net.au/Perl/sqlite.utf8.pl
# Notes:
# 'use utf8;' because the source contains a Unicode string: 'Château'.
# 'use open qw(:std :utf8);' because we print to STDOUT.

use strict;
use utf8;
use warnings;
use warnings  qw(FATAL utf8); # Fatalize encoding glitches.
use open      qw(:std :utf8); # Undeclared streams in UTF-8.

use DBD::SQLite::Constants ':dbd_sqlite_string_mode';

use DBI;

use Encode ':fallback_all';

# ------------------------------------------------

sub process
{
	my($db, $dsn) = @_;
	my($dbh)      = DBI -> connect(@$dsn);

	print "Database: $db. \nDSN: $$dsn[0]. \nVersion: ";

	if ($db =~ 'mysql')
	{
		print ${$dbh -> selectall_arrayref('show global variables like "version"')}[0][1], ". \n";
	}
	elsif ($db eq 'pg')
	{
		print ${$dbh -> selectall_arrayref('select version()')}[0][0], ". \n";
	}

	print 'Version: ', $dbh -> get_info(18), ". \n";

	$dbh -> do('drop table if exists t');
	$dbh -> do('create table t (' .
		'id integer primary key, ' .
		't1 varchar(255), ' .
		't2 varchar(255), ' .
		'timestamp timestamp default current_timestamp ' .
	')');

	my($chateau)   = 'Château'; # é, ®.
	my($encoded)	= Encode::encode('UTF-8', $chateau, DIE_ON_ERR | LEAVE_SRC);
	my($file_name) = "/tmp/$db.log";

	$dbh -> do("insert into t (id, t1, t2) values (1, '$chateau', '$encoded')");

	open(my $fh, '>:encoding(utf-8)', $file_name);

	for my $result ($dbh -> selectall_arrayref('select * from t') )
	{
		print $fh "Raw: $$result[0][1]", '. Decoded: ', Encode::decode('UTF-8', $$result[0][2], DIE_ON_ERR | LEAVE_SRC), ". \n";
		print "Raw: $$result[0][1]. Decoded: ", Encode::decode('UTF-8', $$result[0][2], DIE_ON_ERR | LEAVE_SRC), ". \n";
	}

	close $fh;

	print "Wrote $file_name. \n";
	print '-' x 50, "\n";

} # End of process.

# ------------------------------------------------
# Create database with:
# MySQL:
# drop db:   mysqladmin -uroot -p -f drop testdb
# create db: mysqladmin -uroot -p create testdb --default-character-set=utf8
# Pg:
# drop db:   dropdb testdb
# create db: createdb -E utf8 -O testuser testdb
# SQLite:

my(%mode)	= (sqlite_string_mode => DBD_SQLITE_STRING_MODE_BYTES);
my(%dsn)	=
(
#	mysql  => ['dbi:mysql:testdb',       'testuser', 'testpass',	{mysql_enable_utf8	=> 1}],
#	pg     => ['dbi:Pg:database=testdb', 'testuser', 'testpass',	{pg_enable_utf8		=> 1}],
	sqlite => ['dbi:SQLite:database=/tmp/testdb.sqlite', '', '',	{sqlite_string_mode	=> $mode{sqlite_string_mode} }],
);

for my $db (sort keys %dsn)
{
	process($db, $dsn{$db});
}