=pod
=head1 NAME
Mojo::SQLite - A tiny Mojolicious wrapper for SQLite
=head1 SYNOPSIS
use Mojo::SQLite;
my $sql = Mojo::SQLite->new('sqlite:test.db'); say $sql->db->query('select sqlite_version() as version')->hash->{version};
$sql->migrations->name('my_names_app')->from_string(<<EOF)->migrate; -- 1 up create table names (id integer primary key autoincrement, name text); -- 1 down drop table names; EOF
$sql->migrations->migrate(0)->migrate;
my $db = $sql->db;
$db->insert('names', {name => 'Isabel'}); my $id = $db->select('names', ['id'], {name => 'Isabel'})->hash->{id}; $db->update('names', {name => 'Bel'}, {id => $id}); $db->delete('names', {name => 'Bel'});
eval { my $tx = $db->begin; $db->query('insert into names (name) values (?)', 'Sara'); $db->query('insert into names (name) values (?)', 'Stefan'); $tx->commit; }; say $@ if $@;
say $db->insert('names', {name => 'Daniel'})->last_insert_id;
say $db->query('select ? as foo', {json => {bar => 'baz'}}) ->expand(json => 'foo')->hash->{foo}{bar};
my $results = $db->query('select * from names'); while (my $next = $results->hash) { say $next->{name}; }
say $_->{name} for $db->select('names')->hashes->each;
=head1 DESCRIPTION
L
=head1 BASICS
Database and statement handles are cached automatically, so they can be reused transparently to increase performance. And you can handle connection timeouts gracefully by holding on to them only for short amounts of time.
use Mojolicious::Lite; use Mojo::SQLite;
helper sqlite => sub { state $sql = Mojo::SQLite->new('sqlite:test.db') };
get '/' => sub ($c) { my $db = $c->sqlite->db; $c->render(json => $db->query(q{select datetime('now','localtime') as now})->hash); };
app->start;
In this example application, we create a C
All I/O and queries are performed synchronously, and SQLite's default journal
mode only supports concurrent reads from multiple processes while the database
is not being written. The "Write-Ahead Log" journal mode allows multiple
processes to read and write concurrently to the same database file (but only
one can write at a time). WAL mode is enabled by the C
my $pid = fork || die $!; say $sql->db->query(q{select datetime('now','localtime') as time})->hash->{time}; exit unless $pid;
The C
The L<double-quoted string literal misfeature |https://sqlite.org/quirks.html#double_quoted_string_literals_are_accepted> is disabled for all connections since Mojo::SQLite 3.003; use single quotes for string literals and double quotes for identifiers, as is normally recommended.
All cached database handles will be reset automatically if a new process has
been forked, this allows multiple processes to share the same L
Any database errors will throw an exception as C
While passing a file path of C<:memory:> (or a custom L</"dsn"> with
C
use File::Spec::Functions 'catfile'; use File::Temp; use Mojo::SQLite; my $tempdir = File::Temp->newdir; # Deleted when object goes out of scope my $tempfile = catfile $tempdir, 'test.db'; my $sql = Mojo::SQLite->new->from_filename($tempfile);
=head1 EXAMPLES
This distribution also contains a well-structured example L<blog application|https://github.com/Grinnz/Mojo-SQLite/tree/master/examples/blog> you can use for inspiration. This application shows how to apply the MVC design pattern in practice.
=head1 EVENTS
L
=head2 connection
$sql->on(connection => sub ($sql, $dbh) { $dbh->do('pragma journal_size_limit=1000000'); });
Emitted when a new database connection has been established.
=head1 ATTRIBUTES
L
=head2 abstract
my $abstract = $sql->abstract; $sql = $sql->abstract(SQL::Abstract->new);
L
my($stmt, @bind) = $sql->abstract->where({foo => 'bar', baz => 'yada'});
L
$sql->db->select(['some_table', ['other_table', foo_id => 'id']], ['foo', [bar => 'baz'], \q{datetime('now') as dt}], {foo => 'value'}, {order_by => 'foo', limit => 10, offset => 5, group_by => ['foo'], having => {baz => 'value'}});
$sql->db->insert('some_table', {name => $name, value => $value}, {on_conflict => [name => {value => \'"excluded"."value"'}]});
=head2 auto_migrate
my $bool = $sql->auto_migrate; $sql = $sql->auto_migrate($bool);
Automatically migrate to the latest database schema with L</"migrations">, as soon as L</"db"> has been called for the first time.
=head2 database_class
my $class = $sql->database_class; $sql = $sql->database_class('MyApp::Database');
Class to be used by L</"db">, defaults to L
=head2 dsn
my $dsn = $sql->dsn; $sql = $sql->dsn('dbi:SQLite:uri=file:foo.db');
Data source name, defaults to C<dbi:SQLite:dbname=> followed by a path to a temporary file.
=head2 max_connections
my $max = $sql->max_connections; $sql = $sql->max_connections(3);
Maximum number of idle database handles to cache for future use, defaults to C<1>.
=head2 migrations
my $migrations = $sql->migrations; $sql = $sql->migrations(Mojo::SQLite::Migrations->new);
L
$sql->migrations->from_file('/home/dbook/migrations.sql')->migrate;
=head2 options
my $options = $sql->options; $sql = $sql->options({AutoCommit => 1, RaiseError => 1});
Options for database handles, defaults to setting C
=head2 parent
my $parent = $sql->parent; $sql = $sql->parent(Mojo::SQLite->new);
Another L
=head1 METHODS
L
=head2 new
my $sql = Mojo::SQLite->new; my $sql = Mojo::SQLite->new('file:test.db); my $sql = Mojo::SQLite->new('sqlite:test.db'); my $sql = Mojo::SQLite->new(Mojo::SQLite->new);
Construct a new L
my $sql = Mojo::SQLite->new->dsn('dbi:SQLite:dbname=test.db'); my $sql = Mojo::SQLite->new->dsn('dbi:SQLite:uri=file:test.db?mode=memory');
my $sql = Mojo::SQLite->new->from_filename($filename);
=head2 db
my $db = $sql->db;
Get a database object based on L</"database_class"> (which is usually
L
say $sql->db->select('accounts') ->hashes->reduce(sub { $a->{money} + $b->{money} });
=head2 from_filename
$sql = $sql->from_filename('C:\Documents and Settings\foo & bar.db', $options);
Parse database filename directly. Unlike L</"from_string">, the filename is parsed as a local filename and not a URL. A hashref of L</"options"> may be passed as the second argument.
$sql->from_filename('/home/fred/data.db');
$sql->from_filename('data.db');
$sql->from_filename(':temp:');
my $db = $sql->from_filename(':memory:')->db;
$sql->from_filename($filename, { PrintError => 1 });
$sql->from_filename($filename, { ReadOnly => 1, no_wal => 1 });
use DBD::SQLite::Constants ':dbd_sqlite_string_mode'; $sql->from_filename($filename, { sqlite_string_mode => DBD_SQLITE_STRING_MODE_UNICODE_STRICT, wal_mode => 1 });
=head2 from_string
$sql = $sql->from_string('test.db'); $sql = $sql->from_string('file:test.db'); $sql = $sql->from_string('file:///C:/foo/bar.db'); $sql = $sql->from_string('sqlite:C:%5Cfoo%5Cbar.db'); $sql = $sql->from_string(Mojo::SQLite->new);
Parse configuration from connection string or use another L
$sql->from_string('sqlite:////home/fred/data.db'); $sql->from_string('sqlite://localhost//home/fred/data.db'); $sql->from_string('sqlite:/home/fred/data.db'); $sql->from_string('file:///home/fred/data.db'); $sql->from_string('file://localhost/home/fred/data.db'); $sql->from_string('file:/home/fred/data.db'); $sql->from_string('///home/fred/data.db'); $sql->from_string('//localhost/home/fred/data.db'); $sql->from_string('/home/fred/data.db');
$sql->from_string('sqlite:data.db'); $sql->from_string('file:data.db'); $sql->from_string('data.db');
$sql->from_string(Mojo::URL->new->scheme('sqlite')->path($filename)); $sql->from_string(URI::db->new->Mojo::Base::tap(engine => 'sqlite')->Mojo::Base::tap(dbname => $filename)); $sql->from_string(URI::file->new($filename));
$sql->from_string(':temp:');
my $db = $sql->from_string(':memory:')->db;
$sql->from_string('data.db?PrintError=1&sqlite_allow_multiple_statements=1'); $sql->from_string(Mojo::URL->new->scheme('sqlite')->path($filename)->query(sqlite_see_if_its_a_number => 1)); $sql->from_string(URI::file->new($filename)->Mojo::Base::tap(query_form => {PrintError => 1}));
$sql->from_string('data.db?ReadOnly=1&no_wal=1');
use DBD::SQLite::Constants ':dbd_sqlite_string_mode'; $sql->from_string(Mojo::URL->new->scheme('sqlite')->path('data.db') ->query(sqlite_string_mode => DBD_SQLITE_STRING_MODE_UNICODE_STRICT, wal_mode => 1));
=head1 DEBUGGING
You can set the C
DBI_TRACE=1 DBI_TRACE=15 DBI_TRACE=SQL
=head1 REFERENCE
This is the class hierarchy of the L
=over 2
=item * L
=item * L
=item * L
=item * L
=item * L
=back
=head1 BUGS
Report any issues on the public bugtracker.
=head1 AUTHOR
Dan Book, Cdbook@cpan.org
=head1 CREDITS
Sebastian Riedel, author of L
=head1 COPYRIGHT AND LICENSE
Copyright 2015, Dan Book.
This library is free software; you may redistribute it and/or modify it under the terms of the Artistic License version 2.0.
=head1 SEE ALSO
L
=cut