1Mojo::SQLite::Database(U3s)er Contributed Perl DocumentatMioojno::SQLite::Database(3)
2
6 Mojo::SQLite::Database - Database
7
9 use Mojo::SQLite::Database;
10 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
16 Mojo::SQLite::Database is a container for DBD::SQLite database handles
17 used by Mojo::SQLite.
18
20 Mojo::SQLite::Database implements the following attributes.
21 dbh
23 my $dbh = $db->dbh;
24 $db = $db->dbh($dbh);
25 DBD::SQLite database handle used for all queries.
27 # Use DBI utility methods
29 my $quoted = $db->dbh->quote_identifier('foo.bar');
30 results_class
32 my $class = $db->results_class;
33 $db = $db->results_class('MyApp::Results');
34 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 sqlite
40 my $sql = $db->sqlite;
41 $db = $db->sqlite(Mojo::SQLite->new);
42 Mojo::SQLite object this database belongs to.
44
46 Mojo::SQLite::Database inherits all methods from Mojo::Base and
47 implements the following new ones.
48 begin
50 my $tx = $db->begin;
51 my $tx = $db->begin('exclusive');
52 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 # 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 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 delete
72 my $results = $db->delete($table, \%where);
73 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 $db->delete(some_table => sub {
80 my ($db, $err, $results) = @_;
81 ...
82 });
83 Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
84 Use all the same argument variations you would pass to the "delete"
86 method of SQL::Abstract.
87 # "delete from some_table"
89 $db->delete('some_table');
90 # "delete from some_table where foo = 'bar'"
92 $db->delete('some_table', {foo => 'bar'});
93 # "delete from some_table where foo like '%test%'"
95 $db->delete('some_table', {foo => {-like => '%test%'}});
96 disconnect
98 $db->disconnect;
99 Disconnect "dbh" and prevent it from getting reused.
101 insert
103 my $results = $db->insert($table, \@values || \%fieldvals, \%options);
104 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 $db->insert(some_table => {foo => 'bar'} => sub {
111 my ($db, $err, $results) = @_;
112 ...
113 });
114 Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
115 Use all the same argument variations you would pass to the "insert"
117 method of SQL::Abstract.
118 # "insert into some_table (foo, baz) values ('bar', 'yada')"
120 $db->insert('some_table', {foo => 'bar', baz => 'yada'});
121 ping
123 my $bool = $db->ping;
124 Check database connection.
126 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 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 $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 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 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 # "I X SQLite!"
160 $db->query('select ? as foo', {json => {bar => 'I X SQLite!'}})
161 ->expand(json => 'foo')->hash->{foo}{bar};
162 select
164 my $results = $db->select($source, $fields, $where, $order);
165 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 $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 Use all the same argument variations you would pass to the "select"
178 method of SQL::Abstract.
179 # "select * from some_table"
181 $db->select('some_table');
182 # "select id, foo from some_table"
184 $db->select('some_table', ['id', 'foo']);
185 # "select * from some_table where foo = 'bar'"
187 $db->select('some_table', undef, {foo => 'bar'});
188 # "select * from some_table where foo = 'bar' order by id desc"
190 $db->select('some_table', undef, {foo => 'bar'}, {-desc => 'id'});
191 # "select * from some_table where foo like '%test%'"
193 $db->select('some_table', undef, {foo => {-like => '%test%'}});
194 tables
196 my $tables = $db->tables;
197 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 # Names of all tables
205 say for @{$db->tables};
206 update
208 my $results = $db->update($table, \%fieldvals, \%where);
209 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 $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 Use all the same argument variations you would pass to the "update"
222 method of SQL::Abstract.
223 # "update some_table set foo = 'bar' where id = 23"
225 $db->update('some_table', {foo => 'bar'}, {id => 23});
226 # "update some_table set foo = 'bar' where foo like '%test%'"
228 $db->update('some_table', {foo => 'bar'}, {foo => {-like => '%test%'}});
229
231 Report any issues on the public bugtracker.
232
234 Dan Book, "dbook@cpan.org"
235
237 Copyright 2015, Dan Book.
238 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
243 Mojo::SQLite
244perl v5.30.0 2019-07-26 Mojo::SQLite::Database(3)