@spec bind(
  statement(),
  [bind_value()] | %{optional(String.t()) => bind_value()} | nil
) :: :ok

Resets a prepared statement and binds values to it.

iex> {:ok, conn} = Sqlite3.open(":memory:", [:readonly])
iex> {:ok, stmt} = Sqlite3.prepare(conn, "SELECT ?, ?, ?, ?, ?")
iex> Sqlite3.bind(stmt, [42, 3.14, "Alice", {:blob, <<0, 0, 0>>}, nil])
iex> Sqlite3.step(conn, stmt)
{:row, [42, 3.14, "Alice", <<0, 0, 0>>, nil]}

iex> {:ok, conn} = Sqlite3.open(":memory:", [:readonly])
iex> {:ok, stmt} = Sqlite3.prepare(conn, "SELECT :42, @pi, $name, @blob, :null")
iex> Sqlite3.bind(stmt, %{":42" => 42, "@pi" => 3.14, "$name" => "Alice", :"@blob" => {:blob, <<0, 0, 0>>}, ~c":null" => nil})
iex> Sqlite3.step(conn, stmt)
{:row, [42, 3.14, "Alice", <<0, 0, 0>>, nil]}

iex> {:ok, conn} = Sqlite3.open(":memory:", [:readonly])
iex> {:ok, stmt} = Sqlite3.prepare(conn, "SELECT ?")
iex> Sqlite3.bind(stmt, [42, 3.14, "Alice"])
** (ArgumentError) expected 1 arguments, got 3

iex> {:ok, conn} = Sqlite3.open(":memory:", [:readonly])
iex> {:ok, stmt} = Sqlite3.prepare(conn, "SELECT ?, ?")
iex> Sqlite3.bind(stmt, [42])
** (ArgumentError) expected 2 arguments, got 1

iex> {:ok, conn} = Sqlite3.open(":memory:", [:readonly])
iex> {:ok, stmt} = Sqlite3.prepare(conn, "SELECT ?")
iex> Sqlite3.bind(stmt, [:erlang.list_to_pid(~c"<0.0.0>")])
** (ArgumentError) unsupported type: #PID<0.0.0>

@spec bind_blob(statement(), non_neg_integer(), binary()) :: :ok

Binds a blob value to a prepared statement.

iex> {:ok, conn} = Sqlite3.open(":memory:", [:readonly])
iex> {:ok, stmt} = Sqlite3.prepare(conn, "SELECT ?")
iex> Sqlite3.bind_blob(stmt, 1, <<0, 0, 0>>)
:ok

@spec bind_float(statement(), non_neg_integer(), float()) :: :ok

Binds a float value to a prepared statement.

iex> {:ok, conn} = Sqlite3.open(":memory:", [:readonly])
iex> {:ok, stmt} = Sqlite3.prepare(conn, "SELECT ?")
iex> Sqlite3.bind_float(stmt, 1, 3.14)
:ok

@spec bind_integer(statement(), non_neg_integer(), integer()) :: :ok

Binds an integer value to a prepared statement.

iex> {:ok, conn} = Sqlite3.open(":memory:", [:readonly])
iex> {:ok, stmt} = Sqlite3.prepare(conn, "SELECT ?")
iex> Sqlite3.bind_integer(stmt, 1, 42)
:ok

@spec bind_null(statement(), non_neg_integer()) :: :ok

Binds a null value to a prepared statement.

iex> {:ok, conn} = Sqlite3.open(":memory:", [:readonly])
iex> {:ok, stmt} = Sqlite3.prepare(conn, "SELECT ?")
iex> Sqlite3.bind_null(stmt, 1)
:ok

@spec bind_parameter_count(statement()) :: non_neg_integer() | {:error, reason()}

Returns number of SQL parameters in a prepared statement.

iex> {:ok, conn} = Sqlite3.open(":memory:", [:readonly])
iex> {:ok, stmt} = Sqlite3.prepare(conn, "SELECT ?, ?")
iex> Sqlite3.bind_parameter_count(stmt)
2

@spec bind_text(statement(), non_neg_integer(), String.t()) :: :ok

Binds a text value to a prepared statement.

iex> {:ok, conn} = Sqlite3.open(":memory:", [:readonly])
iex> {:ok, stmt} = Sqlite3.prepare(conn, "SELECT ?")
iex> Sqlite3.bind_text(stmt, 1, "Alice")
:ok

@spec cancel(db() | nil) :: :ok | {:error, reason()}

Cancel a running query: wake any busy handler sleep and interrupt VDBE execution.

This is a superset of interrupt/1 — it sets a cancel flag that the busy and progress handlers observe, and also calls sqlite3_interrupt(). After a cancel, the connection can be reused normally.

Use this when a query might be blocked either inside SQLite bytecode execution or inside the busy handler waiting for a lock.

@spec changes(db()) :: {:ok, integer()} | {:error, reason()}

Get the number of changes recently.

Note: If triggers are used, the count may be larger than expected.

See: https://sqlite.org/c3ref/changes.html

@spec close(db() | nil) :: :ok | {:error, reason()}

Closes the database and releases any underlying resources.

@spec deserialize(db(), String.t(), binary()) :: :ok | {:error, reason()}

Disconnect from database and then reopen as an in-memory database based on the serialized binary.

@spec enable_load_extension(db(), boolean()) :: :ok | {:error, reason()}

Allow loading native extensions.

@spec execute(db(), String.t()) :: :ok | {:error, reason()}

Executes an sql script. Multiple stanzas can be passed at once.

@spec interrupt(db() | nil) :: :ok | {:error, reason()}

Interrupt a long-running query.

This calls sqlite3_interrupt() and is effective while SQLite is actively executing a statement. It does not wake the custom busy handler while the connection is sleeping and waiting on a lock. Use cancel/1 when you need to abort both statement execution and busy waits.

@spec open(String.t(), [open_opt()]) :: {:ok, db()} | {:error, reason()}

Opens a new sqlite database at the Path provided.

path can be ":memory" to keep the sqlite database in memory.

Options

• :mode - controls the flags for sqlite3_open_v2 (see https://www.sqlite.org/c3ref/c_open_autoproxy.html). Defaults to [:readwrite, :create] (opens for reading and writing and creates the file if it does not exist).

  Single modes are permitted:

  • :readwrite - read/write to the database. Does not create the database if it is not present. Use in combination with :create to create the database if it does not exist.
  • :readonly - read-only (file must exist).
  • :create - creates the database if it does not exist.

  Combinations are permitted:

  • [:readwrite, :create] - read/write + create if needed. This is the default if not specified.
  • [:readonly, :nomutex]

  Note: [:readwrite, :nomutex] is not recommended.

@spec release(db(), statement()) :: :ok | {:error, reason()}

Once finished with the prepared statement, call this to release the underlying resources.

This should be called whenever you are done operating with the prepared statement. If the system has a high load the garbage collector may not clean up the prepared statements in a timely manner and causing higher than normal levels of memory pressure.

If you are operating on limited memory capacity systems, definitely call this.

@spec reset(statement()) :: :ok | {:error, atom()}

Resets a prepared statement.

See: https://sqlite.org/c3ref/reset.html

@spec serialize(db(), String.t()) :: {:ok, binary()} | {:error, reason()}

Serialize the contents of the database to a binary.

@spec set_authorizer(db(), [atom()]) :: :ok | {:error, reason()}

Set an authorizer that denies specific SQL operations.

Accepts a list of action atoms to deny. Any SQL statement that triggers a denied action will fail with a "not authorized" error during preparation.

Pass an empty list to clear the authorizer.

Action atoms

:attach, :detach, :pragma, :insert, :update, :delete, :create_table, :drop_table, :create_index, :drop_index, :create_trigger, :drop_trigger, :create_view, :drop_view, :alter_table, :reindex, :analyze, :function, :savepoint, :transaction, :read, :select, :recursive, :create_temp_table, :create_temp_index, :create_temp_trigger, :create_temp_view, :drop_temp_table, :drop_temp_index, :drop_temp_trigger, :drop_temp_view, :create_vtable, :drop_vtable

Examples

# Block ATTACH and DETACH (prevent cross-database reads)
:ok = Sqlite3.set_authorizer(conn, [:attach, :detach])

# Clear the authorizer
:ok = Sqlite3.set_authorizer(conn, [])

@spec set_busy_timeout(db(), integer()) :: :ok | {:error, reason()}

Set the busy timeout in milliseconds without destroying the custom busy handler.

Unlike PRAGMA busy_timeout (which internally calls sqlite3_busy_timeout() and replaces any custom handler), this function only updates the timeout value that the custom busy handler reads. This preserves the ability to cancel busy waits via cancel/1.

A timeout of 0 makes lock contention fail immediately with SQLITE_BUSY. Larger values let SQLite keep retrying until the timeout expires or the wait is cancelled.

This is the low-level API behind the :busy_timeout connection option.

@spec set_commit_hook(db(), pid() | nil) :: :ok | {:error, reason()}

Send commit notifications to a process.

Each time a transaction is committed on the given connection, the atom :committed is sent to the pid provided as the second argument.

Passing nil as the second argument unregisters the hook.

Restrictions

• This is a notification-only hook. SQLite allows a commit hook to veto a commit (turning it into a rollback), but that requires a synchronous answer that a NIF callback cannot request from the BEAM, so commits are always allowed through. Use Exqlite.Sqlite3.transaction_status/1 or handle the commit's return value when you need the outcome.
• Only one pid can receive the notifications for a given connection at a time. If this function is called multiple times for the same connection, only the last pid will receive the notifications.
• Hooks are unregistered when the connection is closed, so closing a connection with an open transaction does not emit notifications for the rollback performed by close/1.

See sqlite3_commit_hook for more details.

@spec set_log_hook(pid()) :: :ok | {:error, reason()}

Send log messages to a process.

Each time a message is logged in SQLite a message will be sent to the pid provided as the argument.

The message is of the form: {:log, rc, message}, where:

• rc is an integer result code or an extended result code
• message is a string representing the log message

See SQLITE_CONFIG_LOG and "The Error And Warning Log" for more details.

Restrictions

• Only one pid can listen to the log messages at a time. If this function is called multiple times, only the last pid will receive the notifications

@spec set_preupdate_hook(db(), pid() | nil) :: :ok | {:error, reason()}

Send preupdate notifications with full row data to a process.

Before each insert, update, or delete is applied on the given connection, a message is sent to the pid provided as the second argument carrying both the old and the new row, which the update hook cannot provide.

The message is of the form: {action, db_name, table, row_id, old_row, new_row}, where:

• action is one of :insert, :update or :delete
• db_name is a string representing the database name where the change took place ("main", "temp", or the name of an attached database)
• table is a string representing the table name where the change took place
• row_id is an integer representing the row id assigned by SQLite. It is always 0 for WITHOUT ROWID tables and cannot identify a row there
• old_row is the complete row before the change, or nil for inserts
• new_row is the complete row after the change, or nil for deletes

Passing nil as the second argument unregisters the hook.

Restrictions

• The hook only fires for changes to real tables. Changes to virtual tables and to system tables such as sqlite_sequence or sqlite_stat1 are not reported.
• Changes performed by triggers and foreign key actions fire additional preupdate events for the modified tables.
• Only one pid can receive the notifications for a given connection at a time. If this function is called multiple times for the same connection, only the last pid will receive the notifications.
• The preupdate hook requires SQLite compiled with SQLITE_ENABLE_PREUPDATE_HOOK, which the vendored build enables. When Exqlite is built against a system SQLite without it (EXQLITE_USE_SYSTEM=1, unless the flag is passed via EXQLITE_SYSTEM_CFLAGS), this function returns {:error, :preupdate_hook_not_supported}.

See sqlite3_preupdate_hook for more details.

@spec set_progress_handler_steps(db(), integer()) :: :ok | {:error, reason()}

Configure how often SQLite invokes the progress handler during statement execution.

The default is 1000 virtual machine steps.

Values less than 1 disable the progress handler. Larger values reduce the overhead of cancellation checks at the cost of slower response to cancel/1 and interrupt/1 while a query is running.

This is the low-level API behind the :progress_handler_steps connection option.

@spec set_rollback_hook(db(), pid() | nil) :: :ok | {:error, reason()}

Send rollback notifications to a process.

Each time a transaction is rolled back on the given connection, the atom :rolled_back is sent to the pid provided as the second argument.

Passing nil as the second argument unregisters the hook.

Restrictions

• The rollback performed by close/1 (or by connection teardown) on an open transaction does not emit a notification, because hooks are unregistered before teardown begins.
• Only one pid can receive the notifications for a given connection at a time. If this function is called multiple times for the same connection, only the last pid will receive the notifications.

See sqlite3_rollback_hook for more details.

@spec set_update_hook(db(), pid()) :: :ok | {:error, reason()}

Send data change notifications to a process.

Each time an insert, update, or delete is performed on the connection provided as the first argument, a message will be sent to the pid provided as the second argument.

The message is of the form: {action, db_name, table, row_id}, where:

• action is one of :insert, :update or :delete
• db_name is a string representing the database name where the change took place
• table is a string representing the table name where the change took place
• row_id is an integer representing the unique row id assigned by SQLite

Restrictions

• There are some conditions where the update hook will not be invoked by SQLite. See the documentation for more details
• Only one pid can listen to the changes on a given database connection at a time. If this function is called multiple times for the same connection, only the last pid will receive the notifications
• Updates only happen for the connection that is opened. For example, there are two connections A and B. When an update happens on connection B, the hook set for connection A will not receive the update, but the hook for connection B will receive the update.

@spec shrink_memory(db()) :: :ok | {:error, reason()}

Causes the database connection to free as much memory as it can. This is useful if you are on a memory restricted system.