mirror of
https://gitea.invidious.io/iv-org/shard-crystal-sqlite3.git
synced 2024-08-15 00:53:26 +00:00
84857724c1
* Automatically set PRAGMAs using connection query params Introduce the flexibility to adjust certain PRAGMAs of the SQLite3 connection without having to hardcode those in your codebase (and wait for compilation). This allows applications to use `DATABASE_URL` to dynamically fine tune their SQLite3 configuration. The change complements `#setup_connection` that offers, via code, the option to perform queries on setup of each connection. Only a few PRAGMAs necessary to allow more performant concurrent reads and reduce write locking. These pragmas are detected and combined in a single SQL string to reduce to 1 the number of calls to `sqlite3_exec` C function. There is no validation of supplied values as SQLite3 automatically ignores incorrect values for these pragmas. Closes #84 References: - https://www.sqlite.org/pragma.html * Simplify PRAGMA mapping and detection No longer prefix PRAGMAS with `_`, so the mapping between the real SQLite3 pragmas and the usage in the URI is more direct. Use macros instead of case to detect pragmas from URI params and return those as NamedTuple. * Update README.md --------- Co-authored-by: Brian J. Cardiff <bcardiff@gmail.com>
87 lines
2.8 KiB
Markdown
87 lines
2.8 KiB
Markdown
# crystal-sqlite3 [](https://travis-ci.org/crystal-lang/crystal-sqlite3)
|
|
|
|
SQLite3 bindings for [Crystal](http://crystal-lang.org/).
|
|
|
|
Check [crystal-db](https://github.com/crystal-lang/crystal-db) for general db driver documentation. crystal-sqlite3 driver is registered under `sqlite3://` uri.
|
|
|
|
## Installation
|
|
|
|
Add this to your application's `shard.yml`:
|
|
|
|
```yml
|
|
dependencies:
|
|
sqlite3:
|
|
github: crystal-lang/crystal-sqlite3
|
|
```
|
|
|
|
### Usage
|
|
|
|
```crystal
|
|
require "sqlite3"
|
|
|
|
DB.open "sqlite3://./data.db" do |db|
|
|
db.exec "create table contacts (name text, age integer)"
|
|
db.exec "insert into contacts values (?, ?)", "John Doe", 30
|
|
|
|
args = [] of DB::Any
|
|
args << "Sarah"
|
|
args << 33
|
|
db.exec "insert into contacts values (?, ?)", args: args
|
|
|
|
puts "max age:"
|
|
puts db.scalar "select max(age) from contacts" # => 33
|
|
|
|
puts "contacts:"
|
|
db.query "select name, age from contacts order by age desc" do |rs|
|
|
puts "#{rs.column_name(0)} (#{rs.column_name(1)})"
|
|
# => name (age)
|
|
rs.each do
|
|
puts "#{rs.read(String)} (#{rs.read(Int32)})"
|
|
# => Sarah (33)
|
|
# => John Doe (30)
|
|
end
|
|
end
|
|
end
|
|
```
|
|
|
|
### DB::Any
|
|
|
|
* `Time` is implemented as `TEXT` column using `SQLite3::DATE_FORMAT_SUBSECOND` format (or `SQLite3::DATE_FORMAT_SECOND` if the text does not contain a dot).
|
|
* `Bool` is implemented as `INT` column mapping `0`/`1` values.
|
|
|
|
### Setting PRAGMAs
|
|
|
|
You can adjust certain [SQLite3 PRAGMAs](https://www.sqlite.org/pragma.html)
|
|
automatically when the connection is created by using the query parameters:
|
|
|
|
```crystal
|
|
require "sqlite3"
|
|
|
|
DB.open "sqlite3://./data.db?journal_mode=wal&synchronous=normal" do |db|
|
|
# this database now uses WAL journal and normal synchronous mode
|
|
# (defaults were `delete` and `full`, respectively)
|
|
end
|
|
```
|
|
|
|
The following is the list of supported options:
|
|
|
|
| Name | Connection key |
|
|
|---------------------------|-----------------|
|
|
| [Busy Timeout][pragma-to] | `busy_timeout` |
|
|
| [Cache Size][pragma-cs] | `cache_size` |
|
|
| [Foreign Keys][pragma-fk] | `foreign_keys` |
|
|
| [Journal Mode][pragma-jm] | `journal_mode` |
|
|
| [Synchronous][pragma-sync] | `synchronous` |
|
|
| [WAL autocheckoint][pragma-walck] | `wal_autocheckpoint` |
|
|
|
|
Please note there values passed using these connection keys are passed
|
|
directly to SQLite3 without check or evaluation. Using incorrect values result
|
|
in no error by the library.
|
|
|
|
[pragma-to]: https://www.sqlite.org/pragma.html#pragma_busy_timeout
|
|
[pragma-cs]: https://www.sqlite.org/pragma.html#pragma_cache_size
|
|
[pragma-fk]: https://www.sqlite.org/pragma.html#pragma_foreign_keys
|
|
[pragma-jm]: https://www.sqlite.org/pragma.html#pragma_journal_mode
|
|
[pragma-sync]: https://www.sqlite.org/pragma.html#pragma_synchronous
|
|
[pragma-walck]: https://www.sqlite.org/pragma.html#pragma_wal_autocheckpoint
|