sqlite_unicode フラグについて


DBD::SQLite

>これを ON にすると、 UTF-8 フラグを付けてくれる。

そんだけ？

## 結論

これを ON にすると、

- 登録時には、問答無用で、`encode 'utf8'` し
- 呼び出し時に、UTF-8 フラグを付けて

くれる。

単純に UTF-8 フラグを削除して文字列を登録している訳ではない。


### code

```pl:test.pl
use strict ;
use warnings ;
use DBI;
use Devel::Peek ;

sub exec_insert_sql {
    my $flag = shift ;
    my $dbh = DBI->connect("dbi:SQLite:dbname=test.db");
    $dbh->{sqlite_unicode} = 1 if $flag ;
    $dbh->do(q{CREATE TABLE IF NOT EXISTS cc (id INTEGER PRIMARY KEY, item TEXT);});
    my $str = 'バグ' ;
    printf "# sqlite_unicode: %s\n", $flag ;
    $dbh->do(q{INSERT INTO cc ( item ) VALUES ( '} . ${str} . q{' );} );
    Dump $dbh->selectall_arrayref(q{SELECT item FROM cc;})->[0][-1] ;
    
    print `sqlite3 test.db 'select item from cc order by id desc limit 1;' ` ;
    print `sqlite3 test.db 'select item from cc order by id desc limit 1;' | hexdump -b` ;
    unlink 'test.db' ;
    $dbh->disconnect;
}

for ( 0, 1 ){
	exec_insert_sql $_ ;
}

```

### no utf8;

```
$ perl test.pl
# sqlite_unicode: 0
SV = PV(0x7fe4d0908480) at 0x7fe4d100e0a8
  REFCNT = 1
  FLAGS = (POK,pPOK)
  PV = 0x7fe4d07000f0 "\343\203\220\343\202\260"\0
  CUR = 6
  LEN = 16
バグ
0000000 343 203 220 343 202 260 012                                    
0000007
# sqlite_unicode: 1
SV = PV(0x7fe4d0908480) at 0x7fe4d100e108
  REFCNT = 1
  FLAGS = (POK,pPOK,UTF8)
  PV = 0x7fe4d07000f0 "\303\243\302\203\302\220\303\243\302\202\302\260"\0 [UTF8 "\x{e3}\x{83}\x{90}\x{e3}\x{82}\x{b0}"]
  CUR = 12
  LEN = 16

ãã°
0000000 303 243 302 203 302 220 303 243 302 202 302 260 012            
000000d

```



### use utf8;

```
$ perl test.pl -Mutf8
# sqlite_unicode: 0
SV = PV(0x7f971c10bcf0) at 0x7f971c8ad2c8
  REFCNT = 1
  FLAGS = (POK,pPOK)
  PV = 0x7f971bc26600 "\343\203\220\343\202\260"\0
  CUR = 6
  LEN = 16
バグ
0000000 343 203 220 343 202 260 012                                    
0000007
# sqlite_unicode: 1
SV = PV(0x7f971c10bcf0) at 0x7f971c8ad328
  REFCNT = 1
  FLAGS = (POK,pPOK,UTF8)
  PV = 0x7f971bd2fd00 "\343\203\220\343\202\260"\0 [UTF8 "\x{30d0}\x{30b0}"]
  CUR = 6
  LEN = 16
バグ
0000000 343 203 220 343 202 260 012                                    
0000007
```


### encode と decode

#### code

```pl:test2.pl
use Encode ;
use Devel::Peek ;

sub _enc { encode 'utf8', shift }
sub _dec { decode 'utf8', shift }

my $str = 'バグ' ;

print "### org\n" ;
Dump $str ;
print "\n" ;

my $str2 = _enc $str ;
print "### encode org\n" ;
Dump $str2 ;
print "\n" ;

my $str3 = _dec $str2 ;
print "### decode encode org # A.\n" ;
Dump $str3 ;
print "\n" ;

my $str4 = _enc $str3 ;
print "### encode A.\n" ;
Dump $str4 ;
print "\n" ;


```

#### no utf8

```
$ perl test2.pl
### org
SV = PV(0x7f9841805270) at 0x7f984182a040
  REFCNT = 1
  FLAGS = (PADMY,POK,pPOK)
  PV = 0x7f98414106c0 "\343\203\220\343\202\260"\0
  CUR = 6
  LEN = 16

### encode org
SV = PV(0x7f9841805f60) at 0x7f98418f3768
  REFCNT = 1
  FLAGS = (PADMY,POK,pPOK)
  PV = 0x7f9841443090 "\303\243\302\203\302\220\303\243\302\202\302\260"\0
  CUR = 12
  LEN = 16

### decode encode org # A.
SV = PV(0x7f9841805f20) at 0x7f9841903d78
  REFCNT = 1
  FLAGS = (PADMY,POK,pPOK,UTF8)
  PV = 0x7f984140f2a0 "\303\243\302\203\302\220\303\243\302\202\302\260"\0 [UTF8 "\x{e3}\x{83}\x{90}\x{e3}\x{82}\x{b0}"]
  CUR = 12
  LEN = 16

### encode A.
SV = PV(0x7f9841805ef0) at 0x7f9841903e08
  REFCNT = 1
  FLAGS = (PADMY,POK,pPOK)
  PV = 0x7f98414235f0 "\303\243\302\203\302\220\303\243\302\202\302\260"\0
  CUR = 12
  LEN = 16


```

#### use utf8

```
$ perl test2.pl -Mutf8
### org
SV = PV(0x7f9150805270) at 0x7f91508045a8
  REFCNT = 1
  FLAGS = (PADMY,POK,pPOK,UTF8)
  PV = 0x7f9150410910 "\343\203\220\343\202\260"\0 [UTF8 "\x{30d0}\x{30b0}"]
  CUR = 6
  LEN = 16

### encode org
SV = PV(0x7f9150805fd0) at 0x7f91508fd9c8
  REFCNT = 1
  FLAGS = (PADMY,POK,pPOK)
  PV = 0x7f915040b9c0 "\343\203\220\343\202\260"\0
  CUR = 6
  LEN = 16

### decode encode org # A.
SV = PV(0x7f9150805f90) at 0x7f91508fa1e0
  REFCNT = 1
  FLAGS = (PADMY,POK,pPOK,UTF8)
  PV = 0x7f9150438040 "\343\203\220\343\202\260"\0 [UTF8 "\x{30d0}\x{30b0}"]
  CUR = 6
  LEN = 16

### encode A.
SV = PV(0x7f9150805f60) at 0x7f91508fa240
  REFCNT = 1
  FLAGS = (PADMY,POK,pPOK)
  PV = 0x7f915040b9b0 "\343\203\220\343\202\260"\0
  CUR = 6
  LEN = 16


```