わいえむねっと

use DBI;

my $dbh = DBI->connect('dbi:SQLite:dbname=foo.db');
$dbh->{AutoCommit} = 0;
$dbh->do('update foo set bar=1');
$dbh->commit;
$dbh->disconnect;

use DBI;

my $dbh = DBI->connect('dbi:SQLite:dbname=foo.db');
$dbh->{AutoCommit} = 0;
$dbh->do('update foo set bar=1');
$dbh->commit;
#$dbh->disconnect;

use DBI;

my $dbh = DBI->connect('dbi:SQLite:dbname=foo.db');
$dbh->{AutoCommit} = 0;
$dbh->do('update foo set bar=1');
#$dbh->commit;
$dbh->disconnect;

use DBI;

my $dbh = DBI->connect('dbi:SQLite:dbname=foo.db');
$dbh->{AutoCommit} = 0;
$dbh->do('update foo set bar=1');
#$dbh->commit;
#$dbh->disconnect;

use DBI;

my $dbh = DBI->connect('dbi:SQLite:dbname=foo.db');
$dbh->{AutoCommit} = 0;
my $sth = $dbh->prepare('select * from foo');
$sth->execute;
undef $sth;
#$dbh->commit;
#$dbh->disconnect;

use DBI;

my $dbh = DBI->connect('dbi:SQLite:dbname=foo.db');
$dbh->{AutoCommit} = 0;
my $sth = $dbh->prepare('select * from foo');
$sth->execute;
undef $sth;
$dbh->commit;
#$dbh->disconnect;

use strict;
use warnings;
use DBI;

unlink 'foo.db';
my $dbh = DBI->connect('dbi:SQLite:dbname=foo.db');

$dbh->do('CREATE TABLE foo(foo, bar INTEGER, baz TEXT, qux NULL)');
$dbh->do('INSERT INTO foo VALUES(1,1,1,1)');
$dbh->do("INSERT INTO foo VALUES('2','2','2','2')");
$dbh->do("INSERT INTO foo VALUES('a','a','a','a')");

foreach my $col('foo','bar','baz','qux')
{
    foreach my $val("1","'1'","2","'2'","'a'")
    {
        my $sth = $dbh->prepare("SELECT * FROM foo WHERE $col=$val");
        $sth->execute;
        $sth->fetch or warn "$col=$val";

        $sth = $dbh->prepare("SELECT * FROM foo WHERE $col=?");
        $sth->execute(eval $val);
        $sth->fetch or warn "$col=? $val";
    }
}

> perl datatype.pl
foo=? 1 at datatype.pl line 23.
foo='1' at datatype.pl line 19.
foo=? '1' at datatype.pl line 23.
foo=2 at datatype.pl line 19.
qux=? 1 at datatype.pl line 23.
qux='1' at datatype.pl line 19.
qux=? '1' at datatype.pl line 23.
qux=2 at datatype.pl line 19.

use strict;
use warnings;
use DBI;
use Time::HiRes;

foreach my $begin_commit(
        ['', ''],
        ['$dbh->do("begin");', '$dbh->do("commit");'],
        ['$dbh->{AutoCommit} = 0;', '$dbh->commit;'])
{
    foreach my $do_execute([100,0], [50,50], [0,100])
    {
        my $sum = 0;
        my $min = 0;
        my $max = 0;
        foreach(1..10)
        {
            my $val = &transaction(@$begin_commit, @$do_execute);
            $sum += $val;
            $min = $val if $min > $val || $min == 0;
            $max = $val if $max < $val;
        }
        warn sprintf '%8.5f(%8.5f~%8.5f)', $sum / 10, $min, $max;
    }
}

sub transaction
{
    my($begin, $commit, $do, $execute) = @_;

    unlink 'foo.db';

    my $time = Time::HiRes::time();

    my $dbh = DBI->connect('dbi:SQLite:dbname=foo.db');
    eval $begin;
    $dbh->do('create table foo(foo)');

    foreach my $i(1..$do)
    {
        $dbh->do("insert into foo values($i)");
    }

    my $sth = $dbh->prepare('insert into foo values(?)');
    foreach my $i(1..$execute)
    {
        $sth->execute($i + $do);
    }
    undef $sth;

    eval $commit;
    $dbh->disconnect;

    return Time::HiRes::time() - $time;
}

> perl transaction.pl
10.00447( 9.81217~10.20283) at transaction.pl line 23.
10.13726(10.06212~10.31213) at transaction.pl line 23.
10.18888(10.14030~10.23409) at transaction.pl line 23.
 0.12704( 0.11595~ 0.14120) at transaction.pl line 23.
 0.12462( 0.10943~ 0.14125) at transaction.pl line 23.
 0.11114( 0.10413~ 0.11639) at transaction.pl line 23.
 0.12563( 0.11620~ 0.14054) at transaction.pl line 23.
 0.12695( 0.11620~ 0.14166) at transaction.pl line 23.
 0.11217( 0.10785~ 0.12078) at transaction.pl line 23.
>
> perl transaction.pl
11.79044(11.65592~11.89033) at transaction.pl line 23.
11.83569(11.79644~11.87464) at transaction.pl line 23.
11.83567(11.79658~11.89022) at transaction.pl line 23.
 0.14603( 0.13568~ 0.15803) at transaction.pl line 23.
 0.13457( 0.12304~ 0.14113) at transaction.pl line 23.
 0.12933( 0.10813~ 0.14678) at transaction.pl line 23.
 0.14184( 0.11892~ 0.15527) at transaction.pl line 23.
 0.13834( 0.13286~ 0.14959) at transaction.pl line 23.
 0.12602( 0.11557~ 0.13844) at transaction.pl line 23.