elixir-sqlite / sqlitex Goto Github PK

Is there any way to batch insert with sqlitex?

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.

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 ;)

Once this gets merged mmzeeman/esqlite#60 it would be nice to bump that dependency to consume those changes.

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+?

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 🍻

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 : )

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'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

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)?

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?

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

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

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.

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.

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?

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"}

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

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.

1. add {:elixir_make, "~> 0.5", runtime: false} to the deps
2. add a Makefile 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

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:

• Update the esqlite hex package to pull in the fix
• Remove the work-around code in sqlitex

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

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 ?

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.

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.

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

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])

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

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?