Mojo::SQLite::Database(3pm)

1Mojo::SQLite::Database(U3spemr)Contributed Perl DocumentMaotjioo:n:SQLite::Database(3pm)
2
3
4

NAME

6       Mojo::SQLite::Database - Database
7

SYNOPSIS

9         use Mojo::SQLite::Database;
10
11         my $db = Mojo::SQLite::Database->new(sqlite => $sql, dbh => $dbh);
12         $db->query('select * from foo')
13           ->hashes->map(sub { $_->{bar} })->join("\n")->say;
14

DESCRIPTION

16       Mojo::SQLite::Database is a container for DBD::SQLite database handles
17       used by Mojo::SQLite.
18

ATTRIBUTES

20       Mojo::SQLite::Database implements the following attributes.
21
22   dbh
23         my $dbh = $db->dbh;
24         $db     = $db->dbh($dbh);
25
26       DBD::SQLite database handle used for all queries.
27
28         # Use DBI utility methods
29         my $quoted = $db->dbh->quote_identifier('foo.bar');
30
31   results_class
32         my $class = $db->results_class;
33         $db       = $db->results_class('MyApp::Results');
34
35       Class to be used by "query", defaults to Mojo::SQLite::Results. Note
36       that this class needs to have already been loaded before "query" is
37       called.
38
39   sqlite
40         my $sql = $db->sqlite;
41         $db     = $db->sqlite(Mojo::SQLite->new);
42
43       Mojo::SQLite object this database belongs to.
44

METHODS

46       Mojo::SQLite::Database inherits all methods from Mojo::Base and
47       implements the following new ones.
48
49   begin
50         my $tx = $db->begin;
51         my $tx = $db->begin('exclusive');
52
53       Begin transaction and return Mojo::SQLite::Transaction object, which
54       will automatically roll back the transaction unless "commit" in
55       Mojo::SQLite::Transaction has been called before it is destroyed.
56
57         # Insert rows in a transaction
58         eval {
59           my $tx = $db->begin;
60           $db->insert('frameworks', {name => 'Catalyst'});
61           $db->insert('frameworks', {name => 'Mojolicious'});
62           $tx->commit;
63         };
64         say $@ if $@;
65
66       A transaction locking behavior of "deferred", "immediate", or
67       "exclusive" may optionally be passed; the default in DBD::SQLite is
68       currently "immediate". See "Transaction and Database Locking" in
69       DBD::SQLite and <https://sqlite.org/lang_transaction.html> for more
70       details.
71
72   delete
73         my $results = $db->delete($table, \%where);
74
75       Generate a "DELETE" statement with "abstract" in Mojo::SQLite (usually
76       an SQL::Abstract::Pg object) and execute it with "query". You can also
77       append a callback for API compatibility with Mojo::Pg; the query is
78       still executed in a blocking manner.
79
80         $db->delete(some_table => sub ($db, $err, $results) {
81           ...
82         });
83         Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
84
85       Use all the same argument variations you would pass to the "delete"
86       method of SQL::Abstract.
87
88         # "delete from some_table"
89         $db->delete('some_table');
90
91         # "delete from some_table where foo = 'bar'"
92         $db->delete('some_table', {foo => 'bar'});
93
94         # "delete from some_table where foo like '%test%'"
95         $db->delete('some_table', {foo => {-like => '%test%'}});
96
97   delete_p
98         my $promise = $db->delete_p($table, \%where, \%options);
99
100       Same as "delete" but returns a Mojo::Promise object instead of
101       accepting a callback. For API compatibility with Mojo::Pg; the query is
102       still executed in a blocking manner.
103
104         $db->delete_p('some_table')->then(sub ($results) {
105           ...
106         })->catch(sub ($err) {
107           ...
108         })->wait;
109
110   disconnect
111         $db->disconnect;
112
113       Disconnect "dbh" and prevent it from getting reused.
114
115   insert
116         my $results = $db->insert($table, \@values || \%fieldvals, \%options);
117
118       Generate an "INSERT" statement with "abstract" in Mojo::SQLite (usually
119       an SQL::Abstract::Pg object) and execute it with "query". You can also
120       append a callback for API compatibility with Mojo::Pg; the query is
121       still executed in a blocking manner.
122
123         $db->insert(some_table => {foo => 'bar'} => sub ($db, $err, $results) {
124           ...
125         });
126         Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
127
128       Use all the same argument variations you would pass to the "insert"
129       method of SQL::Abstract.
130
131         # "insert into some_table (foo, baz) values ('bar', 'yada')"
132         $db->insert('some_table', {foo => 'bar', baz => 'yada'});
133
134   insert_p
135         my $promise = $db->insert_p($table, \@values || \%fieldvals, \%options);
136
137       Same as "insert" but returns a Mojo::Promise object instead of
138       accepting a callback. For API compatibility with Mojo::Pg; the query is
139       still executed in a blocking manner.
140
141         $db->insert_p(some_table => {foo => 'bar'})->then(sub ($results) {
142           ...
143         })->catch(sub ($err) {
144           ...
145         })->wait;
146
147   ping
148         my $bool = $db->ping;
149
150       Check database connection.
151
152   query
153         my $results = $db->query('select * from foo');
154         my $results = $db->query('insert into foo values (?, ?, ?)', @values);
155         my $results = $db->query('select ? as img', {type => SQL_BLOB, value => slurp 'img.jpg'});
156         my $results = $db->query('select ? as foo', {json => {bar => 'baz'}});
157
158       Execute a blocking SQL
159       <http://www.postgresql.org/docs/current/static/sql.html> statement and
160       return a results object based on "results_class" (which is usually
161       Mojo::SQLite::Results) with the query results. The DBD::SQLite
162       statement handle will be automatically reused when it is not active
163       anymore, to increase the performance of future queries. You can also
164       append a callback for API compatibility with Mojo::Pg; the query is
165       still executed in a blocking manner.
166
167         $db->query('insert into foo values (?, ?, ?)' => @values => sub ($db, $err, $results) {
168           ...
169         });
170         Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
171
172       Hash reference arguments containing "type" and "value" elements will
173       use the specified bind type for the parameter, using types from "DBI
174       Constants" in DBI; see "Blobs" in DBD::SQLite and the subsequent
175       section for more information.
176
177       Hash reference arguments containing a value named "json" or "-json"
178       will be encoded to JSON text <http://sqlite.org/json1.html> with
179       "to_json" in Mojo::JSON. To accomplish the reverse, you can use the
180       method "expand" in Mojo::SQLite::Results to decode JSON text fields to
181       Perl values with "from_json" in Mojo::JSON.
182
183         # "I ♥ SQLite!"
184         $db->query('select ? as foo', {json => {bar => 'I ♥ SQLite!'}})
185           ->expand(json => 'foo')->hash->{foo}{bar};
186
187   query_p
188         my $promise = $db->query_p('SELECT * FROM foo');
189
190       Same as "query" but returns a Mojo::Promise object instead of accepting
191       a callback. For API compatibility with Mojo::Pg; the query is still
192       executed in a blocking manner.
193
194         $db->query_p('INSERT INTO foo VALUES (?, ?, ?)' => @values)->then(sub ($results) {
195           ...
196         })->catch(sub ($err) {
197           ...
198         })->wait;
199
200   select
201         my $results = $db->select($source, $fields, $where, $order);
202
203       Generate a "SELECT" statement with "abstract" in Mojo::SQLite (usually
204       an SQL::Abstract::Pg object) and execute it with "query". You can also
205       append a callback for API compatibility with Mojo::Pg; the query is
206       still executed in a blocking manner.
207
208         $db->select(some_table => ['foo'] => {bar => 'yada'} => sub ($db, $err, $results) {
209           ...
210         });
211         Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
212
213       Use all the same argument variations you would pass to the "select"
214       method of SQL::Abstract.
215
216         # "select * from some_table"
217         $db->select('some_table');
218
219         # "select id, foo from some_table"
220         $db->select('some_table', ['id', 'foo']);
221
222         # "select * from some_table where foo = 'bar'"
223         $db->select('some_table', undef, {foo => 'bar'});
224
225         # "select * from some_table where foo = 'bar' order by id desc"
226         $db->select('some_table', undef, {foo => 'bar'}, {-desc => 'id'});
227
228         # "select * from some_table where foo like '%test%'"
229         $db->select('some_table', undef, {foo => {-like => '%test%'}});
230
231   select_p
232         my $promise = $db->select_p($source, $fields, $where, \%options);
233
234       Same as "select" but returns a Mojo::Promise object instead of
235       accepting a callback. For API compatibility with Mojo::Pg; the query is
236       still executed in a blocking manner.
237
238         $db->select_p(some_table => ['foo'] => {bar => 'yada'})->then(sub ($results) {
239           ...
240         })->catch(sub ($err) {
241           ...
242         })->wait;
243
244   tables
245         my $tables = $db->tables;
246
247       Return table and view names for this database, that are visible to the
248       current user and not internal, as an array reference. Names will be
249       quoted and prefixed by a schema name of "main" for standard tables,
250       "temp" for temporary tables, and the appropriate schema name for
251       attached databases <http://sqlite.org/lang_attach.html>.
252
253         # Names of all tables
254         say for @{$db->tables};
255
256   update
257         my $results = $db->update($table, \%fieldvals, \%where);
258
259       Generate an "UPDATE" statement with "abstract" in Mojo::SQLite (usually
260       an SQL::Abstract::Pg object) and execute it with "query". You can also
261       append a callback for API compatibility with Mojo::Pg; the query is
262       still executed in a blocking manner.
263
264         $db->update(some_table => {foo => 'baz'} => {foo => 'bar'} => sub ($db, $err, $results) {
265           ...
266         });
267         Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
268
269       Use all the same argument variations you would pass to the "update"
270       method of SQL::Abstract.
271
272         # "update some_table set foo = 'bar' where id = 23"
273         $db->update('some_table', {foo => 'bar'}, {id => 23});
274
275         # "update some_table set foo = 'bar' where foo like '%test%'"
276         $db->update('some_table', {foo => 'bar'}, {foo => {-like => '%test%'}});
277
278   update_p
279         my $promise = $db->update_p($table, \%fieldvals, \%where, \%options);
280
281       Same as "update" but returns a Mojo::Promise object instead of
282       accepting a callback. For API compatibility with Mojo::Pg; the query is
283       still executed in a blocking manner.
284
285         $db->update_p(some_table => {foo => 'baz'} => {foo => 'bar'})->then(sub ($results) {
286           ...
287         })->catch(sub ($err) {
288           ...
289         })->wait;
290

BUGS

292       Report any issues on the public bugtracker.
293

AUTHOR

295       Dan Book, "dbook@cpan.org"
296

COPYRIGHT AND LICENSE

298       Copyright 2015, Dan Book.
299
300       This library is free software; you may redistribute it and/or modify it
301       under the terms of the Artistic License version 2.0.
302