File Coverage

blib/lib/Mojo/SQLite/Database.pm
Criterion Covered Total %
statement 85 86 98.8
branch 29 36 80.5
condition 9 17 52.9
subroutine 27 27 100.0
pod 6 6 100.0
total 156 172 90.7


line stmt bran cond sub pod time code
1             package Mojo::SQLite::Database;
2 6     6   52 use Mojo::Base -base;
  6         13  
  6         58  
3              
4 6     6   1343 use Carp qw(croak shortmess);
  6         13  
  6         307  
5 6     6   36 use DBI 'SQL_VARCHAR';
  6         12  
  6         259  
6 6     6   3007 use Mojo::JSON 'to_json';
  6         144672  
  6         480  
7 6     6   3515 use Mojo::Promise;
  6         828261  
  6         43  
8 6     6   3375 use Mojo::SQLite::Results;
  6         18  
  6         51  
9 6     6   2735 use Mojo::SQLite::Transaction;
  6         16  
  6         49  
10 6     6   238 use Mojo::Util 'monkey_patch';
  6         14  
  6         9538  
11              
12             our $VERSION = '3.008';
13              
14             our @CARP_NOT = qw(Mojo::SQLite::Migrations);
15              
16             has [qw(dbh sqlite)];
17             has results_class => 'Mojo::SQLite::Results';
18              
19             for my $name (qw(delete insert select update)) {
20             monkey_patch __PACKAGE__, $name, sub {
21 19 50   19   11486 my ($self, @cb) = (shift, ref $_[-1] eq 'CODE' ? pop : ());
        19      
        19      
        19      
22 19         73 return $self->query($self->sqlite->abstract->$name(@_), @cb);
23             };
24             monkey_patch __PACKAGE__, "${name}_p", sub {
25 2     2   2340 my $self = shift;
        2      
        2      
        2      
26 2         8 return $self->query_p($self->sqlite->abstract->$name(@_));
27             };
28             }
29              
30             sub DESTROY {
31 118     118   23164 my $self = shift;
32              
33             # Supported on Perl 5.14+
34 118 50 33     751 return() if defined ${^GLOBAL_PHASE} && ${^GLOBAL_PHASE} eq 'DESTRUCT';
35              
36 118 50 33     387 return() unless (my $sql = $self->sqlite) && (my $dbh = $self->dbh);
37 118         1432 $sql->_enqueue($dbh);
38             }
39              
40             sub begin {
41 23     23 1 826 my ($self, $behavior) = @_;
42 23         92 return Mojo::SQLite::Transaction->new(db => $self, behavior => $behavior);
43             }
44              
45             sub disconnect {
46 2     2 1 60 my $self = shift;
47 2         7 $self->dbh->disconnect;
48             }
49              
50 1     1 1 16 sub ping { shift->dbh->ping }
51              
52             sub query {
53 206     206 1 33740 my ($self, $query) = (shift, shift);
54 206 100       563 my $cb = ref $_[-1] eq 'CODE' ? pop : undef;
55              
56 206         579 my $dbh = $self->dbh;
57              
58 206         1644 my $prev_h = $dbh->{HandleError};
59             # Better context for error messages
60 206 50 66 17   1856 local $dbh->{HandleError} = sub { $_[0] = shortmess $_[0]; ($prev_h and $prev_h->(@_)) ? 1 : 0 };
  17         4159  
  17         806  
61              
62 206         2540 my ($sth, $errored, $error);
63             {
64 206         352 local $@;
  206         314  
65 206 100       462 unless (eval {
66             # If RaiseError has been disabled, we might not get a handle
67 206 100       877 if (defined($sth = $dbh->prepare_cached($query, undef, 3))) {
68 198         11421 _bind_params($sth, @_);
69 198         27965 $sth->execute;
70             }
71 200         1004 1;
72 6         16 }) { $errored = 1; $error = $@ }
  6         14  
73             }
74              
75 206 100 100     669 die $error if $errored and !$cb; # bail out for errored "blocking" queries
76              
77             # We won't have a statement handle if prepare failed in a "non-blocking"
78             # query or with RaiseError disabled
79 201         328 my $results;
80 201 100       416 if (defined $sth) {
81 198         734 $results = $self->results_class->new(db => $self, sth => $sth);
82 198         1112 $results->{last_insert_id} = $dbh->{private_mojo_last_insert_id};
83             }
84              
85 201 100       2333 return $results unless $cb; # blocking
86              
87             # Still blocking, but call the callback on the next tick
88 13 50 0     151 $error = $dbh->err ? $dbh->errstr : $errored ? ($error || 'Error running SQLite query') : undef;
    100          
89 13         96 require Mojo::IOLoop;
90 13     13   125 Mojo::IOLoop->next_tick(sub { $self->$cb($error, $results) });
  13         3771  
91 13         844 return $self;
92             }
93              
94             sub query_p {
95 11     11 1 3400 my $self = shift;
96 11         104 my $promise = Mojo::Promise->new;
97 11 50   11   598 $self->query(@_ => sub { $_[1] ? $promise->reject($_[1]) : $promise->resolve($_[2]) });
  11         51  
98 11         91 return $promise;
99             }
100              
101             sub tables {
102 7     7 1 2093 my @tables = shift->dbh->tables(undef, undef, undef, 'TABLE,VIEW,LOCAL TEMPORARY');
103 7         10651 my %names; # Deduplicate returned temporary table indexes
104 7         22 return [grep { !$names{$_}++ } @tables];
  13         74  
105             }
106              
107             sub _bind_params {
108 198     198   332 my $sth = shift;
109 198 100       501 return $sth unless @_;
110 121         367 foreach my $i (0..$#_) {
111 149         460 my $param = $_[$i];
112 149 100       334 if (ref $param eq 'HASH') {
113 23 100 66     129 if (exists $param->{type} && exists $param->{value}) {
    100          
    50          
114 5         43 $sth->bind_param($i+1, $param->{value}, $param->{type});
115             } elsif (exists $param->{json}) {
116 17         63 $sth->bind_param($i+1, to_json($param->{json}), SQL_VARCHAR);
117             } elsif (exists $param->{-json}) {
118 1         6 $sth->bind_param($i+1, to_json($param->{-json}), SQL_VARCHAR);
119             } else {
120 0         0 croak qq{Unknown parameter hashref (no "type"/"value", "json" or "-json")};
121             }
122             } else {
123 126         680 $sth->bind_param($i+1, $param);
124             }
125             }
126 121         865 return $sth;
127             }
128              
129             1;
130              
131             =encoding utf8
132              
133             =head1 NAME
134              
135             Mojo::SQLite::Database - Database
136              
137             =head1 SYNOPSIS
138              
139             use Mojo::SQLite::Database;
140              
141             my $db = Mojo::SQLite::Database->new(sqlite => $sql, dbh => $dbh);
142             $db->query('select * from foo')
143             ->hashes->map(sub { $_->{bar} })->join("\n")->say;
144              
145             =head1 DESCRIPTION
146              
147             L is a container for L database handles
148             used by L.
149              
150             =head1 ATTRIBUTES
151              
152             L implements the following attributes.
153              
154             =head2 dbh
155              
156             my $dbh = $db->dbh;
157             $db = $db->dbh($dbh);
158              
159             L database handle used for all queries.
160              
161             # Use DBI utility methods
162             my $quoted = $db->dbh->quote_identifier('foo.bar');
163              
164             =head2 results_class
165              
166             my $class = $db->results_class;
167             $db = $db->results_class('MyApp::Results');
168              
169             Class to be used by L, defaults to L. Note
170             that this class needs to have already been loaded before L is called.
171              
172             =head2 sqlite
173              
174             my $sql = $db->sqlite;
175             $db = $db->sqlite(Mojo::SQLite->new);
176              
177             L object this database belongs to.
178              
179             =head1 METHODS
180              
181             L inherits all methods from L and
182             implements the following new ones.
183              
184             =head2 begin
185              
186             my $tx = $db->begin;
187             my $tx = $db->begin('exclusive');
188              
189             Begin transaction and return L object, which will
190             automatically roll back the transaction unless
191             L has been called before it is destroyed.
192              
193             # Insert rows in a transaction
194             eval {
195             my $tx = $db->begin;
196             $db->insert('frameworks', {name => 'Catalyst'});
197             $db->insert('frameworks', {name => 'Mojolicious'});
198             $tx->commit;
199             };
200             say $@ if $@;
201              
202             A transaction locking behavior of C, C, or C
203             may optionally be passed; the default in L is currently
204             C. See L and
205             L for more details.
206              
207             =head2 delete
208              
209             my $results = $db->delete($table, \%where);
210              
211             Generate a C statement with L (usually an
212             L object) and execute it with L. You can also
213             append a callback for API compatibility with L; the query is still
214             executed in a blocking manner.
215              
216             $db->delete(some_table => sub ($db, $err, $results) {
217             ...
218             });
219             Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
220              
221             Use all the same argument variations you would pass to the C method of
222             L.
223              
224             # "delete from some_table"
225             $db->delete('some_table');
226              
227             # "delete from some_table where foo = 'bar'"
228             $db->delete('some_table', {foo => 'bar'});
229              
230             # "delete from some_table where foo like '%test%'"
231             $db->delete('some_table', {foo => {-like => '%test%'}});
232              
233             =head2 delete_p
234              
235             my $promise = $db->delete_p($table, \%where, \%options);
236              
237             Same as L but returns a L object instead of accepting
238             a callback. For API compatibility with L; the query is still executed
239             in a blocking manner.
240              
241             $db->delete_p('some_table')->then(sub ($results) {
242             ...
243             })->catch(sub ($err) {
244             ...
245             })->wait;
246              
247             =head2 disconnect
248              
249             $db->disconnect;
250              
251             Disconnect L and prevent it from getting reused.
252              
253             =head2 insert
254              
255             my $results = $db->insert($table, \@values || \%fieldvals, \%options);
256              
257             Generate an C statement with L (usually an
258             L object) and execute it with L. You can also
259             append a callback for API compatibility with L; the query is still
260             executed in a blocking manner.
261              
262             $db->insert(some_table => {foo => 'bar'} => sub ($db, $err, $results) {
263             ...
264             });
265             Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
266              
267             Use all the same argument variations you would pass to the C method of
268             L.
269              
270             # "insert into some_table (foo, baz) values ('bar', 'yada')"
271             $db->insert('some_table', {foo => 'bar', baz => 'yada'});
272              
273             =head2 insert_p
274              
275             my $promise = $db->insert_p($table, \@values || \%fieldvals, \%options);
276              
277             Same as L but returns a L object instead of accepting
278             a callback. For API compatibility with L; the query is still executed
279             in a blocking manner.
280              
281             $db->insert_p(some_table => {foo => 'bar'})->then(sub ($results) {
282             ...
283             })->catch(sub ($err) {
284             ...
285             })->wait;
286              
287             =head2 ping
288              
289             my $bool = $db->ping;
290              
291             Check database connection.
292              
293             =head2 query
294              
295             my $results = $db->query('select * from foo');
296             my $results = $db->query('insert into foo values (?, ?, ?)', @values);
297             my $results = $db->query('select ? as img', {type => SQL_BLOB, value => slurp 'img.jpg'});
298             my $results = $db->query('select ? as foo', {json => {bar => 'baz'}});
299              
300             Execute a blocking L
301             statement and return a results object based on L (which is
302             usually L) with the query results. The L
303             statement handle will be automatically reused when it is not active anymore, to
304             increase the performance of future queries. You can also append a callback for
305             API compatibility with L; the query is still executed in a blocking
306             manner.
307              
308             $db->query('insert into foo values (?, ?, ?)' => @values => sub ($db, $err, $results) {
309             ...
310             });
311             Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
312              
313             Hash reference arguments containing C and C elements will use the
314             specified bind type for the parameter, using types from L;
315             see L and the subsequent section for more information.
316              
317             Hash reference arguments containing a value named C or C<-json> will be
318             encoded to L with
319             L. To accomplish the reverse, you can use the method
320             L to decode JSON text fields to Perl values
321             with L.
322              
323             # "I ♥ SQLite!"
324             $db->query('select ? as foo', {json => {bar => 'I ♥ SQLite!'}})
325             ->expand(json => 'foo')->hash->{foo}{bar};
326              
327             =head2 query_p
328              
329             my $promise = $db->query_p('SELECT * FROM foo');
330              
331             Same as L but returns a L object instead of accepting
332             a callback. For API compatibility with L; the query is still executed
333             in a blocking manner.
334              
335             $db->query_p('INSERT INTO foo VALUES (?, ?, ?)' => @values)->then(sub ($results) {
336             ...
337             })->catch(sub ($err) {
338             ...
339             })->wait;
340              
341             =head2 select
342              
343             my $results = $db->select($source, $fields, $where, $order);
344              
345             Generate a C
346             L object) and execute it with L. You can also
347             append a callback for API compatibility with L; the query is still
348             executed in a blocking manner.
349              
350             $db->select(some_table => ['foo'] => {bar => 'yada'} => sub ($db, $err, $results) {
351             ...
352             });
353             Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
354              
355             Use all the same argument variations you would pass to the C
356             L.
357              
358             # "select * from some_table"
359             $db->select('some_table');
360              
361             # "select id, foo from some_table"
362             $db->select('some_table', ['id', 'foo']);
363              
364             # "select * from some_table where foo = 'bar'"
365             $db->select('some_table', undef, {foo => 'bar'});
366              
367             # "select * from some_table where foo = 'bar' order by id desc"
368             $db->select('some_table', undef, {foo => 'bar'}, {-desc => 'id'});
369              
370             # "select * from some_table where foo like '%test%'"
371             $db->select('some_table', undef, {foo => {-like => '%test%'}});
372              
373             =head2 select_p
374              
375             my $promise = $db->select_p($source, $fields, $where, \%options);
376              
377             Same as L but returns a L object instead of accepting
378             a callback. For API compatibility with L; the query is still executed
379             in a blocking manner.
380              
381             $db->select_p(some_table => ['foo'] => {bar => 'yada'})->then(sub ($results) {
382             ...
383             })->catch(sub ($err) {
384             ...
385             })->wait;
386              
387             =head2 tables
388              
389             my $tables = $db->tables;
390              
391             Return table and view names for this database, that are visible to the current
392             user and not internal, as an array reference. Names will be quoted and prefixed
393             by a schema name of C<"main"> for standard tables, C<"temp"> for temporary
394             tables, and the appropriate schema name for
395             L.
396              
397             # Names of all tables
398             say for @{$db->tables};
399              
400             =head2 update
401              
402             my $results = $db->update($table, \%fieldvals, \%where);
403              
404             Generate an C statement with L (usually an
405             L object) and execute it with L. You can also
406             append a callback for API compatibility with L; the query is still
407             executed in a blocking manner.
408              
409             $db->update(some_table => {foo => 'baz'} => {foo => 'bar'} => sub ($db, $err, $results) {
410             ...
411             });
412             Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
413              
414             Use all the same argument variations you would pass to the C method of
415             L.
416              
417             # "update some_table set foo = 'bar' where id = 23"
418             $db->update('some_table', {foo => 'bar'}, {id => 23});
419              
420             # "update some_table set foo = 'bar' where foo like '%test%'"
421             $db->update('some_table', {foo => 'bar'}, {foo => {-like => '%test%'}});
422              
423             =head2 update_p
424              
425             my $promise = $db->update_p($table, \%fieldvals, \%where, \%options);
426              
427             Same as L but returns a L object instead of accepting
428             a callback. For API compatibility with L; the query is still executed
429             in a blocking manner.
430              
431             $db->update_p(some_table => {foo => 'baz'} => {foo => 'bar'})->then(sub ($results) {
432             ...
433             })->catch(sub ($err) {
434             ...
435             })->wait;
436              
437             =head1 BUGS
438              
439             Report any issues on the public bugtracker.
440              
441             =head1 AUTHOR
442              
443             Dan Book, C
444              
445             =head1 COPYRIGHT AND LICENSE
446              
447             Copyright 2015, Dan Book.
448              
449             This library is free software; you may redistribute it and/or modify it under
450             the terms of the Artistic License version 2.0.
451              
452             =head1 SEE ALSO
453              
454             L