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

BUGS

231       Report any issues on the public bugtracker.
232

AUTHOR

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

COPYRIGHT AND LICENSE

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