crystal-lang / crystal-sqlite3 Goto Github PK
View Code? Open in Web Editor NEWSQLite3 bindings for Crystal
License: MIT License
SQLite3 bindings for Crystal
License: MIT License
A column with a BLOB
type, reads as whatever the type of the value is, as opposed to Bytes
, which would make more sense.
DB.open "sqlite3://./data.db" do |db|
db.exec "create table contacts (name text, data BLOG)"
db.exec "insert into contacts values (?, ?)", "John Doe", 30
db.query "select name, data from contacts" do |rs|
rs.each do
puts "#{rs.read(String)} (#{rs.read(Bytes)})"
end
end
end
SQLite3::ResultSet#read returned a Int64. A Slice(UInt8) was expected. (Exception)
Changing the 30
to "foo"
SQLite3::ResultSet#read returned a String. A Slice(UInt8) was expected. (Exception)
Hi,
This is the error being returned:
Exception occurred: UNIQUE constraint failed: my_join_table.left_id, my_join_table.right_id
Query : INSERT INTO my_join_table(left_id,right_id) VALUES(?,?)
ON CONFLICT(left_id) DO UPDATE SET right_id = excluded.right_id;
args : [239, 1]
That's run using the DB::QueryMethods#exec
method.
If I plug in the values into that statement and run it using the sqlite client (v3.36.0) it works fine. The syntax is from this Sqlite doc.
If there's any more info you need from me, just ask. I'll use one of the older workarounds I've found until (if) this is fixed, so no pressure (but it would be nice if it's looked at 😄 )
Edit: forgive me, I forgot the library info
sqlite3:
git: https://github.com/crystal-lang/crystal-sqlite3.git
version: 0.18.0
db:
git: https://github.com/crystal-lang/crystal-db.git
version: 0.10.1
$ crystal --version
Crystal 1.1.0 [af095d72d] (2021-07-14)
LLVM: 10.0.0
Default target: x86_64-apple-macosx
Regards,
iain
Do you plan to allow statement with a SQL injection protection ?
Could you please add support for sqlite3_serialize
and sqlite3_deserialize
?
As a downstream app, that would allow us to manipulate database in memory only, removing the need to write data on disk!
Hi,
The current Exception.code
field return the primary result code. It would be nice to add a new field extented_code
, using sqlite3_extended_errcode()
.
Hi,
I am trying to understand how exceptions are handled in Crystal-sqlite3.
Given the following code :
require "sqlite3"
DBCONN = DB.connect "sqlite3://%3Amemory%3A"
begin
DBCONN.exec "create table test ( id integer primary key, code text not null unique)"
DBCONN.exec "insert into test(code) values('A')"
DBCONN.exec "insert into test(code) values('A')"
rescue e
if e.message =~ /UNIQUE constraint failed/i
puts ("Already in database")
else
puts (e.message)
end
ensure
DBCONN.close
end
output is :
Already in database
Unhandled exception: UNIQUE constraint failed: test.code (SQLite3::Exception)
from lib/sqlite3/src/sqlite3/statement.cr:81:5 in 'check'
from lib/sqlite3/src/sqlite3/statement.cr:37:5 in 'do_close'
from lib/db/src/db/disposable.cr:11:7 in 'close'
from lib/db/src/db/connection.cr:60:38 in 'do_close'
from lib/sqlite3/src/sqlite3/connection.cr:34:5 in 'do_close'
from lib/db/src/db/disposable.cr:11:7 in 'close'
from test1a.cr:14:3 in '__crystal_main'
from /usr/lib/crystal/crystal/main.cr:97:5 in 'main_user_code'
from /usr/lib/crystal/crystal/main.cr:86:7 in 'main'
from /usr/lib/crystal/crystal/main.cr:106:3 in 'main'
from __libc_start_main
from _start
from ???
Why does Crystal tell me the exception is unhandled when the displayed message in the rescue clause "proves" it was rescued ?
I noticed that if I comment out the DBCONN.close statement, I only get the "Already in database" message, no more unhandled exception.
If however, I run the following program :
require "sqlite3"
DBCONN = DB.connect "sqlite3://%3Amemory%3A"
begin
DBCONN.exec "create table test ( id integer primary key, code text not null unique)"
DBCONN.exec "insert into test(code) values('A')"
sql = "select 1 from test where code = 'Z'"
code = DBCONN.query_one sql, as: {String}
rescue e
puts (e.message)
ensure
DBCONN.close
end
the output is a bare
no rows
So, no more unhandled exception and I'm somewhat puzzled : can you enlighten me on this?
Are there different "levels" of exceptions ?
I also noticed that putting the DBCONN.close in an at_exit statement produce the same "unhandled exception" for the first code snippet.
Thanks
I just try the 0.8.2 version with a Crystal 0.23.1 [e2a1389e8] LLVM 3.8.
There is a bug that doesn't allow sqlite to run in a spawned Fiber (which is annoying if you try to run Kemal e.g.)
The bug is easy to reproduce:
require "sqlite3"
spawn do
DB.open "sqlite3://%3Amemory%3A" do |db|
db.exec "CREATE TABLE IF NOT EXISTS contacts (name string, age integer)"
puts "=== count is #{db.scalar "select count(*) from contacts"}"
end
end
sleep 1.seconds
The execution gives:
---inside #step : #SQLite3::Statement:0x412d20 sql select count(*) from contacts
Unhandled exception in spawn:
database disk image is malformed (SQLite3::Exception)
0x72eb4: *CallStack::unwind:Array(Pointer(Void)) at /home/pi/Projects/crystal/src/float/printer/cached_powers.cr 1:1
0xd7cf0: move_next at /home/pi/Projects/CrystalThinking/lib/sqlite3/src/sqlite3/result_set.cr 20:7
0xce484: scalar at /home/pi/Projects/CrystalThinking/lib/db/src/db/statement.cr 39:13
0xcec04: scalar at /home/pi/Projects/CrystalThinking/lib/db/src/db/pool_statement.cr 44:30
0xc5268: scalar at /home/pi/Projects/CrystalThinking/lib/db/src/db/query_methods.cr 223:7
0x6e584: ~procProc(Nil) at /home/pi/Projects/crystal/src/time/format/pattern.cr 1:1
0x83260: run at /home/pi/Projects/crystal/src/fiber.cr 255:3
0x6d834: ~proc2Proc(Fiber, (IO::FileDescriptor | Nil)) at /home/pi/Projects/crystal/src/concurrent.cr 61:3
When I define a time column to my table, I got an error like
unexpected char: '.' (19) (Time::Format::Error)
When I read it as String, I got a format
2016-12-25 14:25:53
Is the DATE_FORMAT right?
I think it should be "%F %T"
.
My SQLite version is here.
3.8.10.2 2015-05-20 18:17:19 2ef4f3a5b1d1d0c4338f8243d40a2452cc1f7fe4
Setting the journal mode isn't standardized.
Suggested url format:
DB.open "sqlite3://./data.db?journal_mode=wal"
Putting the journal mode in the connection params allows all ORM's to transparently pass through the journal mode.
Here's a minimal example to reproduce
require "sqlite3"
puts "creating DB"
DB.open "sqlite3://./test.db" do |db|
db.exec "create table if not exists test (number integer)"
end
puts "DB created. Querying"
DB.open "sqlite3://./test.db" do |db|
begin
db.query_one "select * from test where number = 1" do |rs|
pp rs
end
rescue e
puts e
end
end
Console output when running on an armhf device (tested on a Raspberry Pi 3):
creating DB
DB created. Querying
Failed to raise an exception: FAILURE
[0x0] ???
The same code works as expected on amd64.
Crystal 1.0.0 [dd40a2442] (2021-03-22)
LLVM: 10.0.0
Default target: x86_64-unknown-linux-gnu
sqlite3: version: 0.18.0
Running this code with query inside iteration, fails with Invalid memory access
error:
require "sqlite3"
DB.open "sqlite3://%3amemory%3a" do |db|
db.exec "create table t1(id integer primary key autoincrement, t text)"
db.exec "insert into t1 (t) values ('v1')"
db.query "select id, t from t1" do |rs|
rs.each do
v = db.scalar "select id from t1 limit 1"
end
end
end
and error:
Invalid memory access (signal 11) at address 0x0
[0x55e447408396] *Exception::CallStack::print_backtrace:(Int32 | Nil) +118
[0x55e4473f783c] __crystal_sigfault_handler +316
[0x7fb5022963c0] ???
[0x7fb50228cfc4] pthread_mutex_lock +4
[0x7fb5024ea28f] sqlite3_reset +31
[0x55e4474da30b] *SQLite3::ResultSet#do_close:Int32 +27
[0x55e4474da2bf] *SQLite3::ResultSet +63
[0x55e4473e605a] __crystal_main +1658
[0x55e4474db386] *Crystal::main_user_code<Int32, Pointer(Pointer(UInt8))>:Nil +6
[0x55e4474db1fc] *Crystal::main<Int32, Pointer(Pointer(UInt8))>:Int32 +44
[0x55e4473f15e6] main +6
[0x7fb50203d0b3] __libc_start_main +243
[0x55e4473e591e] _start +46
[0x0] ???
Hi @bcardiff
v0.8.2 was released on Mar 21, at least 5 commit and fixes has been published on master.
Would be good to have a new release
Thanks you for maintain this shard! <3
I looked what's missing to run this on Travis. The build fails with a missing reference to sqlite3_close_v2
. That got added in SQLite 3.7.14 but Travis runs on Ubuntu precise, thus SQLite 3.7.9. There's a PPA with 3.7.15, but it's not in the whitelist, although a request is pending, also a general request for 3.8.
So we can either wait for either of those to happen, Travis migrating to a newer Ubuntu version or look into using the legacy infrastructure which doesn't require the whitelist and the PPA.
The sqlite3 REGEXP operator relies on a user-defined regexp
function which is not defined by default, so regex support requires specifying a function. Does/will this library have an API to connect Crystal's stdlib regex functionality to it?
DB.open "sqlite3:./mydb.sqlite3" do |db|
db.exec "DROP TABLE IF EXISTS tablename"
end
triggers "Exception: no such table: tablename (SQLite3::Exception)"
when:
sqlite3 mydb.sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .tables
tablename
drop_table_if_exists_syntax do |table_name|
"drop table if exists #{table_name}"
end
crystal version 1.7.3
What am I doing wrong?
Hello,
I wrote a kind of generator, and, as I generated sqlite3 bindings to test it, I though you may be interested :
The generated file is here : https://github.com/TechMagister/bindgencr/blob/master/samples/sqlite3/lib_sqlite3.cr
The project : https://github.com/TechMagister/bindgencr/
Hi.
I'm running Crystal natively on Windows 10 and I was wondering about how to use this shard.
Right now I have a small project with successfully installed crystal-sqlite3 shard. I also have installed sqlite3 library on my system as a dll file in a folder that is added to my path. Unfortunately it seems that my setup doesn't work out of the box, because I'm getting the following error when I'm trying to run any SQLite3 related code:
Error: Cannot locate the .lib files for the following libraries: sqlite3
I don't have any .lib files in my SQLite3 installation folder. I only have a single .dll file. Is there any way to make it work or do I need to install SQLite3 differently to also have those .lib files?
The create_function()
support exists, and was added in order to implement the REGEX() callback function. It would be very useful to have a documented Crystal interface to sqlite3_create_function_v2()
that could be used similar to this:
@db.create_function returning: Int32.class do |param1, param2|
# ... do something, returning an Int32
end
Syntax to support returning an array, handling aggregate functions, and handling window functions would be (hopefully) similar.
In addition, for my uses, it doesn't need to be exposed through DB; this is obviously for use cases that are very specific to SQLite and not portable, so having a different interface than Database::DB
would be perfectly acceptable, though obviously a lot of the functionality would have to be duplicated.
By default sqlite3 doesn't check foreign keys. For application portability and consistency with other db implmentations it should be enabled.
After reviewing a variety of projects using crystal-sqlite3
Not changing the default can also lead to broken behavior. Applications that depend on CASCADE
can break when moving between other db engines and sqlite.
require "sqlite3"
DBFILE = "test.db"
db = DB.open("sqlite3://./#{DBFILE}")
while true
begin
db.scalar("select padding from foo where id=#{Random.rand(100000000)}") # size matters!
rescue DB::NoResultsError
end
end
grows beyond 32GB of RAM on my machine in a couple of minutes.
For the setup you need:
https://www.sqlite.org/src/file/ext/misc/series.c
https://www.sqlite.org/loadext.html: gcc -g -fPIC -shared series.c -o series.so
save the lines below in setup.sql, then
cat setup.sql | sqlite3 test.db
.load ./series
.header on
.timer on
drop table if exists foo;
create table foo (id int, padding text);
insert into foo (id, padding) select value, random() from generate_series(1, 100000000) order by random();
create index foo_id_ix on foo (id);
I use crystal 1.1.1 on Ubuntu 18.04
Decided to separate this from #87, as it relates to performance and not necessarily the exception experienced in the original report.
Using luislavena/test-crystal-sqlite3-db repository/app, run a few benchmarks single-thread vs multi-thread vs other pooling mechanisms and found that it appears that the usage of double mutexes (connection pool and prepared statement pool) cause a bottleneck when running in mutli-concurrency scenarios.
Based on #87 (fix-mt
branch) build, all this is run and tested on 4C/8T AMD Ryzen CPU, static (musl) and release mode builds, natively (no containers or VMs) using plow
locally (localhost), no network latency involved. Tests were performed multiple times after warm up processes and pick the best of 3 runs.
fix-mt
build:
Single thread build:
CRYSTAL_WORKERS=4
, multi-thread:
CRYSTAL_WORKERS=8
, multi-thread:
Same as above, but replaced DB.open
with DB.connect
and usage of DB's pool with Syn::Pool
wrap around DB::Connection
, (see exp-syn-pool
branch)
Single-thread build:
CRYSTAL_WORKERS=4
, multi-thread:
CRYSTAL_WORKERS=8
, multi-thread:
For reference, as baseline, a no-op HTTP server app (Eg. Hello world) in similar scenarios:
Single-thread build, same concurrency:
CRYSTAL_WORKERS=4
, multi-thread:
CRYSTAL_WORKERS=8
, multi-thread:
Some notes:
On 10 concurrent connections:
Single-thread, noop: ~73K RPS
Single-thread, fix-mt: ~43.9K RPS
Single-thread, Syn::Pool: ~45.7K RPS
Conclusion: not much overhead.
Multi-thread (4), noop: ~79K RPS
Multi-thread (4), fix-mt: ~10K RPS
Multi-thread (4), Syn::Pool: ~49.8K RPS
Conclusion: hit bottleneck
I will perform a deeper performance analysis on this and come back with more details.
last_insert_rowid()
can be used to get the autoincrement-generated ID of a row: https://stackoverflow.com/a/30715960/12211329
But this has to be atomic with the insert statement to be useful, and I'm seeing that if I execute a combined statement like db.scalar("INSERT INTO table (col1, col2) VALUES ('blah', 'blah'); SELECT last_insert_rowid()")
, I get a DB::NoResultsError
. It works if I execute the insert with db.exec
and then the select with db.scalar
, but I don't believe that's atomic.
Hello folks,
Been using crystal-sqlite3 a lot recently and found a bit cumbersome to use DB#setup_connection
on every codebase. Things outside of DB pool size or retry timeout cannot be controlled or passed to the lower level driver.
When looked at crystal-sqlite3 source noticed a TODO for enabling URI support for additional options:
https://github.com/crystal-lang/crystal-sqlite3/blob/master/src/sqlite3/connection.cr#L5-L6
But the recognized query parameters provided by that are quite limited.
Comparing this with other implementations of sqlite3 in other languages like Go (example mattn/go-sqlite3), saw out-of-the-box support for foreign keys, timeout and journal mode (see feature list).
Made an attempt to explore adding this, but encountered that couldn't use #exec
directly from within the connection initialize
, as this is being instantiated by the Pool and exec
tries to access the pool to pick a connection from it, and then 💣
Saw comments in #48 about considering broader support for things like this.
The SQLite3 example of setting PRAGMAs is one, but other examples like TLS support for MySQL comes to my mind. Perhaps something for this could be added to the abstraction that allows doing these adjustments on a per-driver basis?
(perhaps should be posting this on crystal-db instead, but better ask somewhere).
Thank you in advance for your responses! 😊
❤️ ❤️ ❤️
While attempting to execute a particular PRAGMA
statement, my application crashed with an "unknown error".
require "db"
require "sqlite3"
DB.open "sqlite3://./tester.db" do |db|
db.exec("PRAGMA journal_mode = memory")
end
$ crystal run example.cr
unknown error (SQLite3::Exception)
0x55c42a4f6f4d: perform_exec at /tmp/pragma-crystal-sqlite3/lib/sqlite3/src/sqlite3/statement.cr 25:40
0x55c42a4f766d: perform_exec_and_release at /tmp/pragma-crystal-sqlite3/lib/db/src/db/statement.cr 97:14
0x55c42a4f75f9: exec at /tmp/pragma-crystal-sqlite3/lib/db/src/db/statement.cr 67:7
0x55c42a4f7ac9: exec at /tmp/pragma-crystal-sqlite3/lib/db/src/db/pool_statement.cr 14:30
0x55c42a4eeb55: exec at /tmp/pragma-crystal-sqlite3/lib/db/src/db/query_methods.cr 217:7
0x55c42a488952: __crystal_main at /tmp/pragma-crystal-sqlite3/example.cr 5:3
0x55c42a498159: main at /usr/lib/crystal/main.cr 12:15
0x7f2efbf24f6a: __libc_start_main at ??
0x55c42a4880fa: _start at ??
0x0: ??? at ??
The PRAGMA statement given in the example code returns a LibSQLite3::Code::ROW
after being stepped once. It appears that this statement needs more than one step before it is considered done.
Hello, I'm new to crystal, I see some reference to this problem, but I don't see what I might need to do to work around the problem. From what I see, the version of sqlite3 and the driver are not compatible. What is a working combination? I simply pasted the sample from this git into a text file and attempted to run.
SQLite35858Connection.o: In function *SQLite3::Connection#do_close:Int32': SQLite3::Connection:(.text+0x403): undefined reference to
sqlite3_close_v2'
collect2: error: ld returned 1 exit status
Error: execution of command failed with code: 1: cc -o "/home/eugene/workspace/harvest/harvest" "${@}" -rdynamic -lsqlite3 -lpcre -lgc -lpthread /opt/crystal/src/ext/libcrystal.a -levent -lrt -ldl -L/usr/lib -L/usr/local/lib
require "db"
require "sqlite3"
module Harvest
# TODO Put your code here
puts "Hello"
db = DB.open "sqlite3://data.db"
db.exec "create table contacts (name string, 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
# 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)
db.close
I've been skimming through the source and looking through the docs, and I can't find any indications that crystal-sqlite3
supports the sqlite3_update_hook
callback to get "notified" when potentially interesting data is created, updated or deleted. It is similar to the PostgreSQL NOTIFY
feature.
I'm not sure if the underlying DB wrapper has support for this kind of functionality yet though, but I figured I'd open an issue here nonetheless.
Currently (0.8.1) when reading/writing Time values the format SQLite3::DATE_FORMAT
is used. This is one of the supported formats by sqlite. It is actually the one with more precision: YYYY-MM-DD HH:MM:SS.SSS
This is enough for reading/writing values using Time
crystal's type. But it would be proper to allow reading time values using a fallback for all other DateTime formats described in https://www.sqlite.org/lang_datefunc.html so the following queries will work:
select date('now');
select datetime('now');
SELECT current_timestamp;
This should be handled in
crystal-sqlite3/src/sqlite3/result_set.cr
Line 59 in 9784573
def write_entry
args = [] of DB::Any
args << @id
args << @datum
args << @ht
args << @nt
args << @tarif_id
MY_DB.exec "INSERT INTO dayly VALUES(?, ?, ?, ?, ?)", args
end
When writing to the database I get the following exception:
Unhandled exception: SQLite3::Statement does not support Array(Bool | Float32 | Float64 | Int32 | Int64 | Slice(UInt8) | String | Time | Nil) params (Exception)
It worked fine with previous releases
Umm, I have an interesting problem with compiling an app that uses this shard. I don't really know how to describe it, sorry 😕
I created a minimal project and just copied the readme example. On running shards build --error-trace --no-color
(--no-color
doesn't seem to be respected for the ^~~~
interestingly enough^^) I receive the following output (hidden via details 'cause it's a lot). I get this output no matter what I try to do with the db. All that actually does compile is DB.open [...]
without any further actions.
I: Dependencies are satisfied
I: Building: sqlitetest
E: Error target sqlitetest failed to compile:
Error in src/sqlitetest.cr:5: instantiating 'DB:Module#open(String)'
DB.open "sqlite3://./data.db" do |db|
�[32;1m^~~~�[0m
in src/sqlitetest.cr:5: instantiating 'DB:Module#open(String)'
DB.open "sqlite3://./data.db" do |db|
�[32;1m^~~~�[0m
in src/sqlitetest.cr:6: instantiating 'DB::Database#exec(String)'
db.exec "create table contacts (name string, age integer)"
�[32;1m^~~~�[0m
in lib/db/src/db/query_methods.cr:217: instantiating 'build(String)'
build(query).exec(*args)
�[32;1m^~~~~�[0m
in lib/db/src/db/session_methods.cr:23: instantiating 'fetch_or_build_prepared_statement(String)'
fetch_or_build_prepared_statement(query)
�[32;1m^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~�[0m
in lib/db/src/db/database.cr:84: instantiating 'DB::StringKeyCache(DB::PoolPreparedStatement)#fetch(String)'
@statements_cache.fetch(query) { build_prepared_statement(query) }
�[32;1m^~~~~�[0m
in lib/db/src/db/database.cr:84: instantiating 'DB::StringKeyCache(DB::PoolPreparedStatement)#fetch(String)'
@statements_cache.fetch(query) { build_prepared_statement(query) }
�[32;1m^~~~~�[0m
in lib/db/src/db/database.cr:84: instantiating 'build_prepared_statement(String)'
@statements_cache.fetch(query) { build_prepared_statement(query) }
�[32;1m^~~~~~~~~~~~~~~~~~~~~~~~�[0m
in lib/db/src/db/database.cr:89: instantiating 'DB::PoolPreparedStatement:Class#new(DB::Database, String)'
PoolPreparedStatement.new(self, query)
�[32;1m^~~�[0m
in lib/db/src/db/pool_prepared_statement.cr:17: instantiating 'statement_with_retry()'
statement_with_retry &.release_connection
�[32;1m^~~~~~~~~~~~~~~~~~~~�[0m
in lib/db/src/db/pool_statement.cr:52: instantiating 'DB::Database#retry()'
@db.retry do
�[32;1m^~~~~�[0m
in lib/db/src/db/database.cr:135: instantiating 'DB::Pool(DB::Connection)#retry()'
@pool.retry do
�[32;1m^~~~~�[0m
in lib/db/src/db/database.cr:135: instantiating 'DB::Pool(DB::Connection)#retry()'
@pool.retry do
�[32;1m^~~~~�[0m
in lib/db/src/db/pool_statement.cr:52: instantiating 'DB::Database#retry()'
@db.retry do
�[32;1m^~~~~�[0m
in lib/db/src/db/pool_statement.cr:53: instantiating 'build_statement()'
return yield build_statement
�[32;1m^~~~~~~~~~~~~~~�[0m
in lib/db/src/db/pool_prepared_statement.cr:34: instantiating 'clean_connections()'
clean_connections
�[32;1m^~~~~~~~~~~~~~~~~�[0m
in lib/db/src/db/pool_prepared_statement.cr:42: instantiating 'Set(WeakRef(DB::Connection))#each()'
@connections.each do |ref|
�[32;1m^~~~�[0m
in /usr/local/Cellar/crystal-lang/0.21.1_1/src/set.cr:149: instantiating 'Hash(WeakRef(DB::Connection), Nil)#each_key()'
@hash.each_key do |key|
�[32;1m^~~~~~~~�[0m
in /usr/local/Cellar/crystal-lang/0.21.1_1/src/hash.cr:336: instantiating 'each()'
each do |key, value|
�[32;1m^~~~�[0m
in /usr/local/Cellar/crystal-lang/0.21.1_1/src/hash.cr:336: instantiating 'each()'
each do |key, value|
�[32;1m^~~~�[0m
in /usr/local/Cellar/crystal-lang/0.21.1_1/src/set.cr:149: instantiating 'Hash(WeakRef(DB::Connection), Nil)#each_key()'
@hash.each_key do |key|
�[32;1m^~~~~~~~�[0m
in lib/db/src/db/pool_prepared_statement.cr:42: instantiating 'Set(WeakRef(DB::Connection))#each()'
@connections.each do |ref|
�[32;1m^~~~�[0m
in lib/db/src/db/pool_prepared_statement.cr:43: undefined method 'value' for WeakRef(DB::Connection)
conn = ref.value
�[32;1m^~~~~�[0m
================================================================================
WeakRef(DB::Connection) trace:
lib/db/src/db/pool_prepared_statement.cr:42
@connections.each do |ref|
�[32;1m^~~�[0m
I'm running macOS 10.12 if that helps. I also have sqlite3 installed via homebrew, not the outdated system default. I don't have it installed with any of the provided options though, should any of those be necessary.
--with-dbstat
Enable the 'dbstat' virtual table
--with-docs
Install HTML documentation
--with-fts
Enable the FTS3 module
--with-fts5
Enable the FTS5 module (experimental)
--with-functions
Enable more math and string functions for SQL queries
--with-icu4c
Enable the ICU module
--with-json1
Enable the JSON1 extension
--with-secure-delete
Defaults secure_delete to on
--with-session
Enable the session extension
--with-unlock-notify
Enable the unlock notification feature
--without-readline
Build without readline support
--without-rtree
Disable the R*Tree index module
Thanks for any help! 😊
In my project I am monkey-patching some native ActiveRecord methods like:
find_by
find_by_and_initialize
find_or_create_by
Model.first
instead of Model.all.first
Is the long-term vision for Jennifer to be as close as possible to AR's API or do you want to be more selective? Just want to clarify before opening any PRs. 😄
Hello!
While working on drift project, I had an internal prototype that work with different DB drivers by inspecting #uri
of the given connection.
You can see the commits I had from previous version of crystal-db here, specifically .klass_for
However, since 0.12.0 refactor, is not possible to determine the driver without having to load the different shards and compare the connection:
db.using_connection do |conn|
p! conn.is_a?(SQLite3::Connection) # => true
end
This will force Drift to depend on all the other DB drivers, causing unneeded code to be parsed, compiled, etc.
I tried to look at connection_options
and others, but appears that the scheme
from URI is no longer kept.
Any suggestion on how to approach this? I received some interest in allowing Drift work with adapters other than SQLite3.
Thank you.
❤️ ❤️ ❤️
require "sqlite3"
mem_db = DB.open "sqlite3::memory:"
file_db = DB.open "sqlite3:test.sqlite"
mem_db.using_connection do |mem_conn|
file_db.using_connection do |file_conn|
file_conn.dump(mem_conn)
end
end
The above fails with not an error (SQLite3::Exception)
when the size of test.sqlite
exceeds 1024K. Works fine if I delete enough rows beforehand and issue VACUUM
to get the size below 1024K. The issue is the file size, because if I don't do a VACUUM
, it still fails.
Specifically, on line 77 in src/sqlite3/connection.cr
, code
is OKAY
when the expected value is DONE
.
Hi @bcardiff. Same question just correct repo. :-)
Today Jennifer doesn't support Model.new
or Model.build
without any attributes nor with a sub-set of required attributes. Is this by choice or something that slipped through?
Example:
class User < Jennifer::Model::Base
with_timestamps
mapping(
id: Primary32,
name: String,
created_at: Time?,
updated_at: Time?,
)
end
User.new({:name => "Jennifer"})
Unhandled exception: Column User.age can't be casted from Nil to it's type - Int32 (Jennifer::DataTypeCasting)
from src/models/user.cr:4:3 in '_extract_attributes'
from src/models/user.cr:4:3 in 'initialize'
from src/models/user.cr:4:3 in 'initialize'
from src/models/user.cr:4:3 in 'new'
from .icr_cRpmabygCPTixNpJwNqrZA.cr:13:3 in '__icr_exec__'
from .icr_cRpmabygCPTixNpJwNqrZA.cr:16:26 in '__crystal_main'
from /opt/homebrew/Cellar/crystal/1.3.2/src/crystal/main.cr:115:5 in 'main_user_code'
from /opt/homebrew/Cellar/crystal/1.3.2/src/crystal/main.cr:101:7 in 'main'
from /opt/homebrew/Cellar/crystal/1.3.2/src/crystal/main.cr:127:3 in 'main'
User.new
^--
Error: wrong number of arguments for 'User.new' (given 0, expected 1..2)
Overloads are:
- User.new(values : Hash | NamedTuple, new_record : Bool)
- User.new(values : Hash | NamedTuple, new_record)
- User.new(pull : DB::ResultSet)
- User.new(values : Hash(Symbol, AttrType) | NamedTuple)
- User.new(values : Hash(String, AttrType))
Inspired from the Ruby sqlite gem, here is an improvement I would like to see in the Crystal shard syntax :
Being able to create a new database object, allowing usage everywhere in the code, as opposed to only in the 'do' block. Also would allow for usage of several databases at once.
db = SQLite3::Database.new 'database.db'
You would then be able to run db.exec, db.query on this object.
Minimal Example:
require "db"
require "sqlite3"
DB.open("sqlite3://test.db") do |db|
db.exec("create table if not exists foo (bar string);")
db.exec("insert into foo (bar) values (?);", "42")
pp db.query_all("select * from foo;", as: String)
end
Output:
[nix-shell:~/tmp/sqlite-crash-reduction]$ shards build && bin/sqlite-crash-reduction
Dependencies are satisfied
Building: sqlite-crash-reduction
Unhandled exception: SQLite3::ResultSet#read returned a Int64. A String was expected. (Exception)
from lib/db/src/db/result_set.cr:0:9 in 'read'
from lib/db/src/db/query_methods.cr:249:9 in 'query_all:as'
from src/sqlite-crash-reduction.cr:7:6 in '__crystal_main'
from /nix/store/1j9vv8lxl38ylhx970fbcfnplari4gl1-crystal-0.30.1/lib/crystal/crystal/main.cr:97:5 in 'main_user_code'
from /nix/store/1j9vv8lxl38ylhx970fbcfnplari4gl1-crystal-0.30.1/lib/crystal/crystal/main.cr:86:7 in 'main'
from /nix/store/1j9vv8lxl38ylhx970fbcfnplari4gl1-crystal-0.30.1/lib/crystal/crystal/main.cr:106:3 in 'main'
from __libc_start_main
from ../sysdeps/x86_64/start.S:122:0 in '_start'
from ???
Versions:
crystal 0.30.1
sqlite3 3.28.0
crystal-db 0.7.0
crystal-sqlite3 0.14.0
A workaround is reading strings as DB::Any
initially and later running to_s
on them.
Database:
sqlite> .schema
CREATE TABLE IF NOT EXISTS "things"(id bigserial primary key, created_at text);
sqlite> select * from things;
1|2020-11-14 21:56:52
Code:
require "sqlite3"
DB.open("sqlite3:./test.db") do |db|
db.query "select * from things" do |rs|
rs.each do
puts "#{rs.read(Int64)}, #{rs.read(Time)}"
end
end
end
Output:
Unhandled exception: Unexpected char: '\u{0}' at 19: "2020-11-14 21:56:52>>" (Time::Format::Error)
from ../../../../usr/lib/crystal/time/format/parser.cr:558:11 in 'raise'
from ../../../../usr/lib/crystal/time/format/parser.cr:554:5 in 'raise'
from ../../../../usr/lib/crystal/time/format/parser.cr:474:9 in 'char'
from ../../../../usr/lib/crystal/time/format/pattern.cr:165:9 in 'check_char'
from ../../../../usr/lib/crystal/time/format/pattern.cr:8:18 in 'visit'
from ../../../../usr/lib/crystal/time/format.cr:83:5 in 'parse'
from ../../../../usr/lib/crystal/time/format.cr:81:3 in 'parse'
from ../../../../usr/lib/crystal/time.cr:1181:5 in 'parse'
from lib/sqlite3/src/sqlite3/result_set.cr:67:5 in 'read'
from test.cr:6:34 in '__crystal_main'
from ../../../../usr/lib/crystal/crystal/main.cr:105:5 in 'main_user_code'
from ../../../../usr/lib/crystal/crystal/main.cr:91:7 in 'main'
from ../../../../usr/lib/crystal/crystal/main.cr:114:3 in 'main'
from __libc_start_main
from _start
from ???
However, this is the timestamp format used by SQLite's current_timestamp
, so not being able to parse it is a problem. I'm not sure if the appropriate solution is a change here or somewhere else, but I'm posting it here as a starting point.
Occurs on master branch.
Hello!
Been testing out preview_mt
mode with SQLite and encountered some issues. It has been hard to reproduce this, but I was able to get a small test program:
require "sqlite3"
COUNT = 10
puts "preview_mt: ", {{ flag?(:preview_mt) }}
db = DB.open("sqlite3:data.db?journal_mode=wal&busy_timeout=5000")
COUNT.times do |i|
spawn do
rand_id = rand(1..500)
puts db.scalar("SELECT age FROM contacts WHERE id = ? LIMIT 1;", rand_id).as(Int64)
end
end
Fiber.yield
db.close
data.db
contains a simple schema with 500 records in it:
require "sqlite3"
CREATE_SQL = <<-SQL
CREATE TABLE IF NOT EXISTS contacts (
id INTEGER PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
age INTEGER NOT NULL
);
SQL
INSERT_SQL = <<-SQL
INSERT INTO contacts (id, name, age) VALUES (?, ?, ?);
SQL
DB.open("sqlite3:data.db?journal_mode=wal&busy_timeout=5000") do |db|
db.exec CREATE_SQL
db.transaction do
500.times do |t|
id = t + 1
db.exec INSERT_SQL, id, "Name #{id}", id + 20
end
end
end
When compiled and executed with -Dpreview_mt
, I randomly receive something like the following:
preview_mt:
true
202
500
393
337
Invalid memory access (signal 11) at address 0x0
Unhandled exception in spawn: bad parameter or other API misuse (SQLite3::Exception)
[0x557449e450f6] *Exception::CallStack::print_backtrace:Nil +118 in /home/luis/repos/github.com/luislavena/test-sqlite3-mt/bin/test
Invalid memory access (signal 11) at address 0x0
426
[0x557449e30646] ~procProc(Int32, Pointer(LibC::SiginfoT), Pointer(Void), Nil) +310 in /home/luis/repos/github.com/luislavena/test-sqlite3-mt/bin/test
[0x557449e450f6] *Exception::CallStack::print_backtrace:Nil +118 in /home/luis/repos/github.com/luislavena/test-sqlite3-mt/bin/test
[0x7f20a5a42520] ?? +139778194679072 in /lib/x86_64-linux-gnu/libc.so.6
[0x557449e30646] ~procProc(Int32, Pointer(LibC::SiginfoT), Pointer(Void), Nil) +310 in /home/luis/repos/github.com/luislavena/test-sqlite3-mt/bin/test
[0x7f20a5ffbc76] sqlite3_reset +22 in /lib/x86_64-linux-gnu/libsqlite3.so.0
[0x7f20a5a42520] ?? +139778194679072 in /lib/x86_64-linux-gnu/libc.so.6
[0x557449f2c42b] *SQLite3::ResultSet#do_close:Int32 +27 in /home/luis/repos/github.com/luislavena/test-sqlite3-mt/bin/test
[0x7f20a5ffbc76] sqlite3_reset +22 in /lib/x86_64-linux-gnu/libsqlite3.so.0
[0x557449f2c3dc] *SQLite3::ResultSet +60 in /home/luis/repos/github.com/luislavena/test-sqlite3-mt/bin/test
[0x557449f2c42b] *SQLite3::ResultSet#do_close:Int32 +27 in /home/luis/repos/github.com/luislavena/test-sqlite3-mt/bin/test
[0x557449f29f63] *DB::Statement+ +291 in /home/luis/repos/github.com/luislavena/test-sqlite3-mt/bin/test
[0x557449f2c3dc] *SQLite3::ResultSet +60 in /home/luis/repos/github.com/luislavena/test-sqlite3-mt/bin/test
[0x557449f2a7b9] *DB::PoolStatement+ +633 in /home/luis/repos/github.com/luislavena/test-sqlite3-mt/bin/test
[0x557449f29f63] *DB::Statement+ +291 in /home/luis/repos/github.com/luislavena/test-sqlite3-mt/bin/test
[0x557449f1195b] *DB::Database +59 in /home/luis/repos/github.com/luislavena/test-sqlite3-mt/bin/test
[0x557449f2a7b9] *DB::PoolStatement+ +633 in /home/luis/repos/github.com/luislavena/test-sqlite3-mt/bin/test
[0x557449e31b9b] ~procProc(Nil) +155 in /home/luis/repos/github.com/luislavena/test-sqlite3-mt/bin/test
[0x557449f1195b] *DB::Database +59 in /home/luis/repos/github.com/luislavena/test-sqlite3-mt/bin/test
[0x557449e8bc52] *Fiber#run:(IO::FileDescriptor | Nil) +114 in /home/luis/repos/github.com/luislavena/test-sqlite3-mt/bin/test
[0x557449e31b9b] ~procProc(Nil) +155 in /home/luis/repos/github.com/luislavena/test-sqlite3-mt/bin/test
[0x557449e30166] ~proc2Proc(Fiber, (IO::FileDescriptor | Nil)) +6 in /home/luis/repos/github.com/luislavena/test-sqlite3-mt/bin/test
[0x557449e8bc52] *Fiber#run:(IO::FileDescriptor | Nil) +114 in /home/luis/repos/github.com/luislavena/test-sqlite3-mt/bin/test
[0x0] ???
Sometimes receiving bad parameter or other API misuse
, others unable to close due to unfinalized statements or unfinished backups
I was able to workaround this by using using_connection
block instead:
COUNT.times do |i|
spawn do
rand_id = rand(1..500)
db.using_connection do |conn|
puts conn.scalar("SELECT age FROM contacts WHERE id = ? LIMIT 1;", rand_id).as(Int64)
end
end
end
Which points there might be an issue on how #exec
, #scalar
and possible other calls are handled by DB::Database
and passed to one of the connections in the pool.
But even so, I will still randomly get errors (small adaptation to my example on my site).
Since I'm blowing things up, decided to put more dynamite into the issue and tried ysbaddaden/pool shard:
db = ConnectionPool(DB::Connection).new(capacity: 20) do
DB.connect(DATABASE_URL)
end
And then:
db.connection do |conn|
conn.scalar("SELECT name FROM contacts WHERE id = ? LIMIT 1;", id).as(String)
end
To my surprise, these random exceptions are gone and performance is even higher:
$ plow -c 20 -d 10s http://localhost:8080
Benchmarking http://localhost:8080 for 10s using 20 connection(s).
@ Real-time charts is listening on http://[::]:18888
Summary:
Elapsed 10s
Count 746816
2xx 746816
RPS 74681.412
Reads 7.392MB/s
Writes 4.131MB/s
Statistics Min Mean StdDev Max
Latency 29µs 266µs 804µs 35.593ms
RPS 71105.09 74675.39 1390.64 75844.23
Latency Percentile:
P50 P75 P90 P95 P99 P99.9 P99.99
146µs 169µs 201µs 257µs 6.482ms 6.658ms 6.901ms
Latency Histogram:
149µs 632328 84.67%
268µs 99222 13.29%
3.697ms 7679 1.03%
6.503ms 5546 0.74%
6.592ms 1752 0.23%
6.656ms 190 0.03%
6.786ms 87 0.01%
7.004ms 12 0.00%
Tried running this project own specs with -Dpreview_mt
and fails with the same issue, so not sure where to start looking at.
Apologies for the rambling, but wanted to make sure I included all the information I think is connected and relevant, I hope is not too much.
Any ideas where I should start looking at first?
Thank you in advance.
❤️ ❤️ ❤️
If I install a newer version of SQLite (e.g. in /usr/local/
) how do I get crystal to use the newer binaries?
For easy reproduction, just run this and wait a bit:
require "sqlite3"
puts "PID is #{Process.pid}"
DB.open("sqlite3://./test-db-feel-free-to-delete-me.sqlite") do |db|
db.exec("CREATE TABLE IF NOT EXISTS foo (bar)")
loop do
db.transaction do
db.exec("INSERT INTO foo VALUES (1)")
end
end
end
The PID output is so that you can browse to /proc/$PID/fd
and see the number of open file descriptors accumulate.
Invalid memory access (signal 11) at address 0x0
[0x55eb4f123e66] *Exception::CallStack::print_backtrace:Nil +118 in bin/translater
[0x55eb4f1048e6] ~procProc(Int32, Pointer(LibC::SiginfoT), Pointer(Void), Nil) +310 in bin/translater
[0x7f828811c770] ?? +140198605342576 in /usr/lib/libc.so.6
[0x0] ???
I can reproduce this on my recent version translater.
it is an simple app with infrequent access to SQL.
bin/translater -e baidu,bing 'hello world'
As you can see, it raise Invalid memory access this time, but it work on next time.
the broken code is here, when insert into two table in same db file in a times loop
.
Anyway, i thiought whatever happen don't should cause a Invalid memory access
.
Add a sleep 0.1
after this line, seem like fix this issue.
BTW: same issue happen both on use DB.connect or DB.open.
Test on Crystal 1.12.0
Thanks
Debian Testing
Crystal 25.1
SQLITE3 installed
shards install seemed to work ok
require "kemal"
require "db"
require "sqlite3"
DB.open "sqlite3://ourwebsite.db" do |db|
/usr/bin/ld: cannot find -lsqlite3 (this usually means you need to install the development package for libsqlite3)
/usr/bin/ld: cannot find -lz (this usually means you need to install the development package for libz)
collect2: error: ld returned 1 exit status
Error: execution of command failed with code: 1: cc "${@}" -o '/home/work/.cache/crystal/crystal-run-website.tmp' -rdynamic -lsqlite3 -lz
command -v pkg-config > /dev/null && pkg-config --libs --silence-errors libssl || printf %s '-lssl -lcrypto'
command -v pkg-config > /dev/null && pkg-config --libs --silence-errors libcrypto || printf %s '-lcrypto' -lpcre -lm -lgc -lpthread /usr/share/crystal/src/ext/libcrystal.a -levent -lrt -ldl -L/usr/lib -L/usr/local/lib
we have tried to look for a zlib-dev but cannot find. We have this working on a Fedora 28 machine so something is missing with packages
appreciate any help
Support for virtual tables in crystal-sqlite3
would be very useful. (See https://sqlite.org/vtab.html#implementation for docs)
Specifically, it would be great to be able to define a virtual table in a Crystal class, which enforces the required methods and allows overriding the ones with defaults.
For example, a Crystal implementation of the generate_series virtual table (https://sqlite.org/src/file/ext/misc/series.c) might look a little like this (I'm totally making this up, and assume a real implementation will be wildly different, the point is that it's idiomatic Crystal):
class SeriesVirtualTable < SQLite3::VirtualTable
class_property max_row_id : Int64 = 10_i64
module_name "generate_series"
@row_id : Int64 = 0_i64
declare_vtab "CREATE TABLE x(a,b)" { a: Int64, b: Int64 } # raises SQLite3::Exception::SomethingAppropriate if sqlite3_declare_vtab() fails
def next
@row_id += 1
end
def row_id
@row_id
end
def eof
@row_id >= @@max_row_id
end
def filter
@row_id=0
end
def best_index
{estimatedCost: 10.0, estimatedRows: @@max_row_id}
end
def column(id)
case id
when 0
1000 + @row_id
when 1
2000 + @row_id
else
raise SQLite3::Exception.new("#{id}: row id doesn't exist")
end
end
Hopefully the intention is clear; the details are pretty flexible. Creating the instance provides the necessary parameters as class attributes. Then it is attached to the database connection and the virtual table can be created. A separate cursor object might be needed, of course, though maybe the instance can serve as both the virtual table definition and the cursor?
Maybe the other methods (like xOpen
, xClose
, xBegin
, xCommit
, etc) can be exposed as additional methods in the class.
Eponymous, eponymous-only, and shadowed virtual tables might be different base classes.
And maybe there are no base classes, but instead modules to include into a class…
Ideas and suggestions are always welcome!
Well, it's not an issue really, but it'd be nice if a sample db would be included sample program. It'll save some time to start testing/experimenting ...
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.