elixir-sqlite / sqlitex Goto Github PK
View Code? Open in Web Editor NEWAn Elixir wrapper around esqlite. Allows access to sqlite3 databases.
Home Page: https://hex.pm/packages/sqlitex
An Elixir wrapper around esqlite. Allows access to sqlite3 databases.
Home Page: https://hex.pm/packages/sqlitex
We currently use the pipe
library to do pattern matching and early exit in the query module.
As of elixir 1.2.0
this will have language-level support using the new with
syntax. What timeline should we be thinking about for dropping the pipe
dependency and using the new with
syntax? Should we just keep pipe
until we hit the 1.0
version and then switch to using with
and change our project definition to require elixir 1.2.0+
?
As far as I see, using begin
and end
together with Sqlitex.Server
has a race condition when two processes are sending messages:
Serialized:
A -> Server: "begin"
A -> Server: "Insert into foo..."
A -> Server: "rollback"
B -> Server: "select * from foo"
Parallel:
A -> Server: "begin"
A -> Server: "Insert into ..."
B -> Server: "select * from foo"
A -> Server: "commit"
The correct result for the select * from foo
of B
would be []
, but with this race condition, B
will see the data A
inserted in a transaction which gets canceled.
To me this looks like an inherent issue with the current implementation of Sqlitex.Server
. However, we could work around this by moving it behind something more resembling a connection pool which will spawn a new instance for each process connecting to it, monitoring that process, and closing the connections when the process exits (or after some timeout).
Do you think this solution would work, or do you see any issues? If everything looks good, I might try to contribute a pool like this in the next few days.
When I am working with Sqlitex and opening connections, I always get a tri-tuple like the following:
{
:connection,
#Reference<0.1196713639.350486530.23370>,
#Reference<0.1196713639.350617602.23368>
}
IMO the type should not be:
@type connection :: {:connection, reference, binary()}
but instead it should be:
@type connection :: {:connection, reference(), reference()}
Am I missing something obvious?
I just added dogma to the project and set it up to run at the end of each Travis build. As you can see we have a lot of violations right now. Most of them are for long lines (perhaps we should consider how long we want our lines to be?), but they also include a lot undocumented modules.
I don't want to add dogma as a requirement for Travis to pass, but a lot of these violations should probably be cleaned up.
For
sqlite> select * from users;
id|username|email|password|status|inserted_at|updated_at
1|admin|root@localhost|******|2|2015-09-28 16:53:52.000000|2015-09-28 16:53:52.000000
datetime is parsed fine.
For
sqlite> select * from users;
id|username|email|password|status|inserted_at|updated_at
1|admin|root@localhost|******|2|2015-09-28 16:53:52|2015-09-28 16:53:52
I have error:
** (MatchError) no match of right hand side value: "16:53:52"
(sqlitex) lib/sqlitex/row.ex:60: Sqlitex.Row.to_time/1
(sqlitex) lib/sqlitex/row.ex:32: Sqlitex.Row.translate_value/1
(elixir) lib/enum.ex:1043: anonymous fn/3 in Enum.map/2
(elixir) lib/enum.ex:1385: Enum."-reduce/3-lists^foldl/2-0-"/3
Good to parse
2015-09-28 16:53:52 without milisecond
With the introduction of Elixir 1.8, Mix.target()
was added. This had the side effect of breaking packages that build C code that is not built by an updated version of elixir_make
including this project's dependency on esqlite
. esqlite
is built by pc
(port compiler, a rebar3 plugin). I believe it is unreasonable to ask Rebar to support the Elixir Mix.target()
, but any thing that needs to be cross compiled is now broken.
I've drafted up one solution that i am incredibly unhappy with.
{:elixir_make, "~> 0.5", runtime: false}
to the depsMakefile
that basically recompiles the same assets that pc
has already emitted:# Set Erlang-specific compile and linker flags
ERL_CFLAGS ?= -I$(ERL_EI_INCLUDE_DIR)
ERL_LDFLAGS ?= -L$(ERL_EI_LIBDIR) -lei
CFLAGS += -fPIC --std=c11
LDFLAGS += -fPIC -shared
ifeq ($(ERL_EI_INCLUDE_DIR),)
$(warning ERL_EI_INCLUDE_DIR not set. Invoke via mix)
endif
ESQLITE_SRC = $(MIX_DEPS_PATH)/esqlite/c_src
ESQLITE_BUILD = $(MIX_BUILD_PATH)/lib/esqlite/obj
ESQLITE_PREFIX = $(MIX_BUILD_PATH)/lib/esqlite/priv
.PHONY: all clean
SQLITE_CFLAGS := -DSQLITE_THREADSAFE=1 \
-DSQLITE_USE_URI \
-DSQLITE_ENABLE_FTS3 \
-DSQLITE_ENABLE_FTS3_PARENTHESIS
all: $(ESQLITE_BUILD) \
$(ESQLITE_PREFIX) \
$(ESQLITE_PREFIX)/esqlite3_nif.so
clean:
$(RM) $(ESQLITE_PREFIX)/*.so
## ESQLITE NIF HACK
$(ESQLITE_PREFIX)/esqlite3_nif.so: $(ESQLITE_BUILD)/sqlite3.o $(ESQLITE_BUILD)/queue.o $(ESQLITE_BUILD)/esqlite3_nif.o
$(CC) -o $@ $(ERL_LDFLAGS) $(LDFLAGS) $^
$(ESQLITE_BUILD)/esqlite3_nif.o: $(ESQLITE_SRC)/esqlite3_nif.c
$(CC) -c $(ERL_CFLAGS) $(CFLAGS) $(SQLITE_CFLAGS) -o $@ $<
$(ESQLITE_BUILD)/queue.o: $(ESQLITE_SRC)/queue.c
$(CC) -c $(ERL_CFLAGS) $(CFLAGS) $(SQLITE_CFLAGS) -o $@ $<
$(ESQLITE_BUILD)/sqlite3.o: $(ESQLITE_SRC)/sqlite3.c
$(CC) -c $(CFLAGS) $(SQLITE_CFLAGS) -o $@ $<
## DIRECTORIES
$(ESQLITE_BUILD):
mkdir -p $(ESQLITE_BUILD)
$(ESQLITE_PREFIX):
mkdir -p $(ESQLITE_PREFIX)
This has 1 positive side effect of allowing us to control the compiler options to sqlite independently of esqlite
. This may or may not be a good thing.
There is another potential solution in #53 on esqlite
I have binary UUIDs that I'm inserting into Sqlite from a JSON string.
Currently I'm binding them after decoding them like so:
Base.decode16!("0000000225313C3467355473F0FF8E2D", case: :mixed)
In another (C-based) application sqlite3_column_type
returns SQLITE_TEXT
instead of SQLITE_BLOB
.
Is there a way to make sure it's inserted as a blob? Note that my column is already defined as a blob type.
Could we get a 1.6.1 release on Hex?
I'd like to pick up the new changes you've merged, but would prefer to reference the hex package rather than the Github repo.
Thanks : )
I've never set up hex documentation for a project before. I'm thinking it would be nice to try to set that up for sqlitex since it is relatively small so documenting it shouldn't be too bad.
When compiling sqlite
with elixir 1.5.1 there are warnings due to deprecations.
✔ ~/Projects/sqlitex [old L|✔]
12:46 $ mix clean
✔ ~/Projects/sqlitex [old L|✔]
12:46 $ mix compile
Compiling 8 files (.ex)
warning: String.to_char_list/1 is deprecated, use String.to_charlist/1
lib/sqlitex.ex:32
warning: String.rstrip/2 is deprecated, use String.trim_trailing/2 with a binary as second argument
lib/sqlitex/row.ex:56
warning: the char_list() type is deprecated, use charlist()
lib/sqlitex/query.ex:82
warning: the char_list() type is deprecated, use charlist()
lib/sqlitex/query.ex:81
warning: the char_list() type is deprecated, use charlist()
lib/sqlitex/query.ex:68
warning: the char_list() type is deprecated, use charlist()
lib/sqlitex/query.ex:67
warning: the char_list() type is deprecated, use charlist()
lib/sqlitex/query.ex:39
warning: the char_list() type is deprecated, use charlist()
lib/sqlitex/query.ex:38
warning: the char_list() type is deprecated, use charlist()
lib/sqlitex/query.ex:25
warning: the char_list() type is deprecated, use charlist()
lib/sqlitex/query.ex:24
warning: the char_list() type is deprecated, use charlist()
lib/sqlitex.ex:4
warning: the char_list() type is deprecated, use charlist()
lib/sqlitex.ex:3
warning: the char_list() type is deprecated, use charlist()
lib/sqlitex.ex:31
warning: the char_list() type is deprecated, use charlist()
lib/sqlitex.ex:31
Generated sqlitex app
The esqlite error that incorrectly returned :no_columns
for queries has been fixed. (See: mmzeeman/esqlite#16) We currently have some work-around code in sqlitex to handle the error condition. We can now:
The tests are failing locally, here the error:
MIX_ENV=dev mix test
A new Hex version is available (0.13.0), please update with `mix local.hex`
.................................
Finished in 0.2 seconds
33 tests, 0 failures
Randomized with seed 988060
➜ sqlitex git:(fix-datetime-empty) ✗ MIX_ENV=dev mix test
A new Hex version is available (0.13.0), please update with `mix local.hex`
1) test moduledoc at Sqlitex.Statement (1) (StatementTest)
test/statement_test.exs:3
Doctest failed
code: {:ok, db} = Sqlitex.open(":memory:")
Sqlitex.query(db, "CREATE TABLE data (id, name);") === []
lhs: {:ok, []}
stacktrace:
lib/sqlitex/statement.ex:15: Sqlitex.Statement (module)
..................................
Finished in 0.2 seconds
35 tests, 1 failure
Randomized with seed 233801
How valuable would it be to add typespecs to our library? It should help people who run dialyzer and it would provide some extra details for the documentation.
Is it worth it?
Hi,
I do not know if you know the depo elixir package, but it has an interesting feature.
This feature may be implemented in the Sqlitex.Server
module.
Basically, you create a statement with a name. So instead of calling Sqlitex.Server.prepare(:example, "SELECT * FROM t")
you would call Sqlitex.Server.register_statement(:example, :all_from_t, "SELECT * FROM t")
.
And now, you could call the statement with Sqlitex.Server.query(:example, :all_from_t)
The point is to liberate the user from the statements bookeeping as users do not need to keep the statement variable around, neither the SQL query binary. Plus, as the statement is only visible in the db server, it cannot be messed with by another process.
What do you think ?
Once this gets merged mmzeeman/esqlite#60 it would be nice to bump that dependency to consume those changes.
Currently my test is expecting to get back query results as keylists like:
[id: 123, name: "Me"]
But I'm wondering if it would be better to return maps like this:
%{id: 123, name: "Me"}
I'm having an issue if I try to fetch a certain number of record, it works well with LIMIT 1
up to LIMIT 3
but if I set the query with LIMIT 4
or higher I get the following error
iex(8)> Sqlitex.with_db('./data/mydb.db', fn(db) ->
...(8)> Sqlitex.query(db, "SELECT * FROM cdr LIMIT 3")
...(8)> end)
{:ok,
[[field1: "...", ...],
[field1: "...", ...]}
iex(9)>
nil
iex(10)> Sqlitex.with_db('./data/mydb.db', fn(db) ->
...(10)> Sqlitex.query(db, "SELECT * FROM cdr LIMIT 4")
...(10)> end)
** (MatchError) no match of right hand side value: []
(sqlitex) lib/sqlitex/row.ex:31: Sqlitex.Row.translate_value/1
(elixir) lib/enum.ex:1184: Enum."-map/2-lists^map/1-0-"/2
(elixir) lib/enum.ex:1184: Enum."-map/2-lists^map/1-0-"/2
(sqlitex) lib/sqlitex/row.ex:12: Sqlitex.Row.build_row/4
(sqlitex) lib/sqlitex/row.ex:4: anonymous fn/5 in Sqlitex.Row.from/4
(elixir) lib/enum.ex:1623: Enum."-reduce/3-lists^foldl/2-0-"/3
(sqlitex) lib/sqlitex/row.ex:3: Sqlitex.Row.from/4
(sqlitex) lib/sqlitex/statement.ex:129: Sqlitex.Statement.fetch_all/2
Right now, opening a database is a special case in that we pass a char list in for the path argument. All other functions take Strings. Would it make sense to either (1) have open/1
exclusively accept Strings for path args, or (2) have open/1
accept both char lists and Strings? Thoughts?
This line from my test is giving me heartburn:
assert row == [id: 1, name: "Mikey", created_at: "2012-10-14 05:46:28.318107", updated_at: "2013-09-06 22:29:36.610911", type: :undefined]
Is there a standard way to represent these values in Elixir/Erlang?
I'm pretty sure that passing around times as strings is a bad idea since it would be impossible to do math or formatting without turning into some sort of numeric representation.
I cannot find information on this project's license. Could you maybe add a LICENSE
file? I guess the license would be the same as for esqlite
(Apache 2.0)?
The Postgrex and Mariaex database drivers both support Decimal types, an arbitrary precision decimal module. One Ecto integration test even relies on adapters handling Decimals. I don't currently know anything about how important or widely used this library is, but I wanted to go ahead and start the discussion about whether or not to include support for Decimal in Sqlitex. This is not a high priority.
I've tried to handle Decimals in Sqlite.Ecto, but it is rather complicated since Sqlite.Ecto has so far assumed Sqlitex will handle all the type logic/conversions.
Sqlite.Ecto issue for reference: jazzyb/sqlite_ecto#26
Having a long running Sqlitex.Server
process is awesome. What if I have N number of sqlite files in my project, and I want to start a process per file. Since I will have N processes running, I will want any process that has not been queried for a certain time, say 10 minutes, to be automatically shutdown.
Something like:
def init({state, timeout}) do
Process.send_after(self(), :shutdown, timeout)
{:ok, []}
end
def handle_info(:shutdown, _state) do
exit(:normal)
{:noreply, []}
end
Thanks
Is there any way to batch insert with sqlitex?
Howdy 👋
I'm trying to contribute to the ecto 3 upgrade that @DiodonHystrix has started here. Are there any instructions on how to get the environment setup for development?
I've cloned the repo and when I run mix test
it asks me to create the database. However there are no repositories defined in config/config.exs
. Do I need to define one?
I also had a look at how the tests are running in CI and it looks like it uses mix coveralls.circle
to run the tests, but again I don't see where the database is created?
Any help would be appreciated 🍻
If the file permissions do not allow access to the db file, instead of returning an error tuple the library throws a MatchError:
pry(1)> Sqlitex.with_db('file.db', fn db -> Sqlitex
.query(db, sql_search) end)
** (MatchError) no match of right hand side value: {:error, {:cantopen, 'unable to open database
file'}}
The same occurs if an empty sql string is passed:
pry(1)> Sqlitex.query(db, "")
** (MatchError) no match of right hand side value: {:error, :no_prepared_statement}
Thanks for providing this library! It's super useful
Just wanted to get your opinion on this: I sometimes run into issues testing sqlitex on my machines because I often use NFS, and SQLite will not work with files over NFS (because NFS doesn't provide file locking that SQLite uses). Hence, using an in-memory database is easier for me for testing.
I noticed that the fixture database isn't very large at all. What are your thoughts on converting test/fixtures/golfscores.sqlite3
(or a subset of that) to an in-memory database? I would be willing to do the conversion myself, by the way.
I think it would be great to be able to specify the parameter by their position in the list.
Sqlitex.Server.query(@db, "insert into table (name, description, category_id) values($2, $1, $1)", bind: [name, category_id])
If tables have been created like the following:
CREATE TABLE t (key INTEGER, inserted_at DATETIME)
then the Sqlitex.Row
module will not correctly translate the value in inserted_at
to an Erlang datetime value because the type atom will be :DATETIME
instead of :datetime
. Any combination of lower and upper case letters could be valid, e.g. :DateTime
or :Datetime
.
I don't immediately know the best solution, but I'm opening the issue to remember to come back to it when I have the time. Of course, anyone else is welcome to fix it in the meantime ;)
Doing an INSERT statement with bound values, Sqlitex.exec("INSERT ... VALUES(?1,...)", bind:[...])
fails to bind anything. Sqlitex.query() with the same statement works as expected.
With sqlitex 1.7.0.
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.