Sqlcx.Server (sqlcx v1.2.0) View Source
Sqlcx.Server provides a GenServer to wrap an sqlcipher db. This makes it easy to share a SQLCipher database between multiple processes without worrying about concurrency issues. You can also register the process with a name so you can query by name later.
Unsupervised Example
iex> {:ok, pid} = Sqlcx.Server.start_link(":memory:", [name: :example])
iex> Sqlcx.Server.exec(pid, "CREATE TABLE t (a INTEGER, b INTEGER)")
:ok
iex> Sqlcx.Server.exec(pid, "INSERT INTO t (a, b) VALUES (1, 1), (2, 2), (3, 3)")
:ok
iex> Sqlcx.Server.query(pid, "SELECT * FROM t WHERE b = 2")
{:ok, [[a: 2, b: 2]]}
iex> Sqlcx.Server.query(:example, "SELECT * FROM t ORDER BY a LIMIT 1", into: %{})
{:ok, [%{a: 1, b: 1}]}
iex> Sqlcx.Server.query_rows(:example, "SELECT * FROM t ORDER BY a LIMIT 2")
{:ok, %{rows: [[1, 1], [2, 2]], columns: [:a, :b], types: [:INTEGER, :INTEGER]}}
iex> Sqlcx.Server.prepare(:example, "SELECT * FROM t")
{:ok, %{columns: [:a, :b], types: [:INTEGER, :INTEGER]}}
# Subsequent queries using this exact statement will now operate more efficiently
# because this statement has been cached.
iex> Sqlcx.Server.prepare(:example, "INVALID SQL")
{:error, {:sqlite_error, 'near "INVALID": syntax error'}}
iex> Sqlcx.Server.stop(:example)
:ok
iex> :timer.sleep(10) # wait for the process to exit asynchronously
iex> Process.alive?(pid)
false
Supervised Example
import Supervisor.Spec
children = [
worker(Sqlcx.Server, ["priv/my_db.db", [name: :my_db])
]
Supervisor.start_link(children, strategy: :one_for_one)Link to this section Summary
Functions
Returns a specification to start this module under a supervisor.
Same as Sqlcx.exec/3 but using the shared db connections saved in the GenServer state.
Callback implementation for GenServer.init/1.
Prepares a SQL statement for future use.
Same as Sqlcx.Query.query/3 but using the shared db connections saved in the GenServer state.
Same as Sqlcx.Query.query_rows/3 but using the shared db connections saved in the GenServer state.
Change the password used to encrypt the database.
Starts a SQLCipher Server (GenServer) instance.
Runs fun inside a transaction. If fun returns without raising an exception,
the transaction will be commited via commit. Otherwise, rollback will be called.
Link to this section Functions
Returns a specification to start this module under a supervisor.
See Supervisor.
Same as Sqlcx.exec/3 but using the shared db connections saved in the GenServer state.
Returns the results otherwise.
Callback implementation for GenServer.init/1.
Prepares a SQL statement for future use.
This causes a call to sqlite3_prepare_v2
to be executed in the Server process. To protect the reference to the corresponding
sqlite3_stmt struct from misuse in other
processes, that reference is not passed back. Instead, prepared statements are
cached in the Server process. If a subsequent call to query/3 or query_rows/3
is made with a matching SQL statement, the prepared statement is reused.
Prepared statements are purged from the cache when the cache exceeds a preset limit (20 statements by default).
Returns summary information about the prepared statement.
{:ok, %{columns: [:column1_name, :column2_name,... ], types: [:column1_type, ...]}}
on success or {:error, {:reason_code, 'SQLite message'}} if the statement
could not be prepared.
Same as Sqlcx.Query.query/3 but using the shared db connections saved in the GenServer state.
Returns the results otherwise.
Same as Sqlcx.Query.query_rows/3 but using the shared db connections saved in the GenServer state.
Returns the results otherwise.
Change the password used to encrypt the database.
Starts a SQLCipher Server (GenServer) instance.
In addition to the options that are typically provided to GenServer.start_link/3,
you can also specify:
stmt_cache_size: (positive_integer)to override the default limit (20) of statements that are cached when callingprepare/3.db_timeout: (positive_integer)to override:esqlcipher's default timeout of 5000 ms for interactions with the database. This can also be set inconfig.exsasconfig :sqlcx, db_timeout: 5_000.db_chunk_size: (positive_integer)to override:esqlcipher's default chunk_size of 5000 rows to read from native sqlite and send to erlang process in one bulk. This can also be set inconfig.exsasconfig :sqlcx, db_chunk_size: 5_000.
Runs fun inside a transaction. If fun returns without raising an exception,
the transaction will be commited via commit. Otherwise, rollback will be called.
Be careful if fun might take a long time to run. The function is executed in the
context of the server and therefore blocks other requests until it's finished.
## Examples
iex> {:ok, server} = Sqlcx.Server.start_link(":memory:")
iex> Sqlcx.Server.with_transaction(server, fn(db) ->
...> Sqlcx.Server.exec(db, "create table foo(id integer)")
...> Sqlcx.Server.exec(db, "insert into foo (id) values(42)")
...> end)
iex> Sqlcx.Server.query(server, "select * from foo")
{:ok, [[{:id, 42}]]}