Mojo::SQLite::Database(3pm)

1Mojo::SQLite::Database(U3s)er Contributed Perl DocumentatMioojno::SQLite::Database(3)
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 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 {
81           my ($db, $err, $results) = @_;
82           ...
83         });
84         Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
85
86       Use all the same argument variations you would pass to the "delete"
87       method of SQL::Abstract.
88
89         # "delete from some_table"
90         $db->delete('some_table');
91
92         # "delete from some_table where foo = 'bar'"
93         $db->delete('some_table', {foo => 'bar'});
94
95         # "delete from some_table where foo like '%test%'"
96         $db->delete('some_table', {foo => {-like => '%test%'}});
97
98   disconnect
99         $db->disconnect;
100
101       Disconnect "dbh" and prevent it from getting reused.
102
103   insert
104         my $results = $db->insert($table, \@values || \%fieldvals, \%options);
105
106       Generate an "INSERT" statement with "abstract" in Mojo::SQLite (usually
107       an SQL::Abstract object) and execute it with "query". You can also
108       append a callback for API compatibility with Mojo::Pg; the query is
109       still executed in a blocking manner.
110
111         $db->insert(some_table => {foo => 'bar'} => sub {
112           my ($db, $err, $results) = @_;
113           ...
114         });
115         Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
116
117       Use all the same argument variations you would pass to the "insert"
118       method of SQL::Abstract.
119
120         # "insert into some_table (foo, baz) values ('bar', 'yada')"
121         $db->insert('some_table', {foo => 'bar', baz => 'yada'});
122
123   ping
124         my $bool = $db->ping;
125
126       Check database connection.
127
128   query
129         my $results = $db->query('select * from foo');
130         my $results = $db->query('insert into foo values (?, ?, ?)', @values);
131         my $results = $db->query('select ? as img', {type => SQL_BLOB, value => slurp 'img.jpg'});
132         my $results = $db->query('select ? as foo', {json => {bar => 'baz'}});
133
134       Execute a blocking SQL
135       <http://www.postgresql.org/docs/current/static/sql.html> statement and
136       return a results object based on "results_class" (which is usually
137       Mojo::SQLite::Results) with the query results. The DBD::SQLite
138       statement handle will be automatically reused when it is not active
139       anymore, to increase the performance of future queries. You can also
140       append a callback for API compatibility with Mojo::Pg; the query is
141       still executed in a blocking manner.
142
143         $db->query('insert into foo values (?, ?, ?)' => @values => sub {
144           my ($db, $err, $results) = @_;
145           ...
146         });
147         Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
148
149       Hash reference arguments containing "type" and "value" elements will
150       use the specified bind type for the parameter, using types from "DBI
151       Constants" in DBI; see "Blobs" in DBD::SQLite and the subsequent
152       section for more information.
153
154       Hash reference arguments containing a value named "json" or "-json"
155       will be encoded to JSON text <http://sqlite.org/json1.html> with
156       "to_json" in Mojo::JSON. To accomplish the reverse, you can use the
157       method "expand" in Mojo::SQLite::Results to decode JSON text fields to
158       Perl values with "from_json" in Mojo::JSON.
159
160         # "I X SQLite!"
161         $db->query('select ? as foo', {json => {bar => 'I X SQLite!'}})
162           ->expand(json => 'foo')->hash->{foo}{bar};
163
164   select
165         my $results = $db->select($source, $fields, $where, $order);
166
167       Generate a "SELECT" statement with "abstract" in Mojo::SQLite (usually
168       an SQL::Abstract object) and execute it with "query". You can also
169       append a callback for API compatibility with Mojo::Pg; the query is
170       still executed in a blocking manner.
171
172         $db->select(some_table => ['foo'] => {bar => 'yada'} => sub {
173           my ($db, $err, $results) = @_;
174           ...
175         });
176         Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
177
178       Use all the same argument variations you would pass to the "select"
179       method of SQL::Abstract.
180
181         # "select * from some_table"
182         $db->select('some_table');
183
184         # "select id, foo from some_table"
185         $db->select('some_table', ['id', 'foo']);
186
187         # "select * from some_table where foo = 'bar'"
188         $db->select('some_table', undef, {foo => 'bar'});
189
190         # "select * from some_table where foo = 'bar' order by id desc"
191         $db->select('some_table', undef, {foo => 'bar'}, {-desc => 'id'});
192
193         # "select * from some_table where foo like '%test%'"
194         $db->select('some_table', undef, {foo => {-like => '%test%'}});
195
196   tables
197         my $tables = $db->tables;
198
199       Return table and view names for this database, that are visible to the
200       current user and not internal, as an array reference. Names will be
201       quoted and prefixed by a schema name of "main" for standard tables,
202       "temp" for temporary tables, and the appropriate schema name for
203       attached databases <http://sqlite.org/lang_attach.html>.
204
205         # Names of all tables
206         say for @{$db->tables};
207
208   update
209         my $results = $db->update($table, \%fieldvals, \%where);
210
211       Generate an "UPDATE" statement with "abstract" in Mojo::SQLite (usually
212       an SQL::Abstract object) and execute it with "query". You can also
213       append a callback for API compatibility with Mojo::Pg; the query is
214       still executed in a blocking manner.
215
216         $db->update(some_table => {foo => 'baz'} => {foo => 'bar'} => sub {
217           my ($db, $err, $results) = @_;
218           ...
219         });
220         Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
221
222       Use all the same argument variations you would pass to the "update"
223       method of SQL::Abstract.
224
225         # "update some_table set foo = 'bar' where id = 23"
226         $db->update('some_table', {foo => 'bar'}, {id => 23});
227
228         # "update some_table set foo = 'bar' where foo like '%test%'"
229         $db->update('some_table', {foo => 'bar'}, {foo => {-like => '%test%'}});
230

BUGS

232       Report any issues on the public bugtracker.
233

AUTHOR

235       Dan Book, "dbook@cpan.org"
236

COPYRIGHT AND LICENSE

238       Copyright 2015, Dan Book.
239
240       This library is free software; you may redistribute it and/or modify it
241       under the terms of the Artistic License version 2.0.
242