# `EctoLibSql.Pragma`
[🔗](https://github.com/ocean/ecto_libsql/blob/v0.9.1/lib/ecto_libsql/pragma.ex#L1)

Helper functions for executing SQLite PRAGMA statements.

PRAGMA statements are SQLite's configuration mechanism. This module provides
convenient wrapper functions for common PRAGMA operations.

## Common Use Cases

- Enable foreign key constraints
- Set journal mode (WAL for better concurrency)
- Configure synchronisation level
- Query database configuration

## Examples

    # Enable foreign keys
    {:ok, state} = EctoLibSql.connect(database: "app.db")
    :ok = EctoLibSql.Pragma.enable_foreign_keys(state)

    # Set WAL mode
    {:ok, _result} = EctoLibSql.Pragma.set_journal_mode(state, :wal)

    # Check current foreign keys setting
    {:ok, result} = EctoLibSql.Pragma.foreign_keys(state)
    # result.rows => [[1]] if enabled, [[0]] if disabled

## Integration with Ecto

PRAGMA statements are often executed during repository initialisation:

    # In your Repo module
    def init(_type, config) do
      {:ok, Keyword.put(config, :after_connect, &set_pragmas/1)}
    end

    defp set_pragmas(conn) do
      with {:ok, state} <- DBConnection.get_connection_state(conn),
           :ok <- EctoLibSql.Pragma.enable_foreign_keys(state),
           {:ok, _} <- EctoLibSql.Pragma.set_journal_mode(state, :wal) do
        :ok
      end
    end

# `disable_foreign_keys`

Disable foreign key constraints.

## Parameters

  - state: Connection state

## Returns

  - `:ok` on success
  - `{:error, reason}` on failure

## Examples

    :ok = EctoLibSql.Pragma.disable_foreign_keys(state)

# `enable_foreign_keys`

```elixir
@spec enable_foreign_keys(EctoLibSql.State.t()) :: :ok | {:error, term()}
```

Enable foreign key constraints.

By default, SQLite does not enforce foreign key constraints. This function
enables them for the current connection.

## Parameters

  - state: Connection state

## Returns

  - `:ok` on success
  - `{:error, reason}` on failure

## Examples

    :ok = EctoLibSql.Pragma.enable_foreign_keys(state)

## Notes

This setting is per-connection and must be set each time you connect.
Consider setting it in your Repo's `after_connect` callback.

# `foreign_keys`

Query the current foreign keys setting.

## Parameters

  - state: Connection state

## Returns

  - `{:ok, result}` where result.rows is [[1]] if enabled, [[0]] if disabled
  - `{:error, reason}` on failure

## Examples

    {:ok, result} = EctoLibSql.Pragma.foreign_keys(state)
    enabled? = result.rows == [[1]]

# `journal_mode`

Query the current journal mode.

## Parameters

  - state: Connection state

## Returns

  - `{:ok, result}` where result.rows contains the current mode
  - `{:error, reason}` on failure

## Examples

    {:ok, result} = EctoLibSql.Pragma.journal_mode(state)
    # result.rows => [["wal"]] or [["delete"]], etc.

# `query`

```elixir
@spec query(EctoLibSql.State.t(), String.t()) ::
  {:ok, EctoLibSql.Result.t()} | {:error, term()}
```

Execute a raw PRAGMA statement.

This is the low-level function that all other PRAGMA helpers use.

## Parameters

  - state: Connection state
  - pragma_stmt: The complete PRAGMA statement (e.g., "PRAGMA foreign_keys = ON")

## Returns

  - `{:ok, result}` with query result
  - `{:error, reason}` on failure

## Examples

    {:ok, result} = EctoLibSql.Pragma.query(state, "PRAGMA foreign_keys = ON")
    {:ok, result} = EctoLibSql.Pragma.query(state, "PRAGMA table_info(users)")

# `set_journal_mode`

Set the journal mode.

SQLite supports several journal modes:
- `:delete` - Default mode, deletes journal file after transaction
- `:wal` - Write-Ahead Logging, better for concurrent access
- `:memory` - Keep journal in memory
- `:persist` - Keep journal file but zero it out
- `:truncate` - Truncate journal file instead of deleting
- `:off` - No journal (dangerous, not recommended)

## Parameters

  - state: Connection state
  - mode: One of `:delete`, `:wal`, `:memory`, `:persist`, `:truncate`, `:off`

## Returns

  - `{:ok, result}` with the new journal mode
  - `{:error, reason}` on failure

## Examples

    {:ok, result} = EctoLibSql.Pragma.set_journal_mode(state, :wal)
    # result.rows => [["wal"]]

## Recommendations

For applications with concurrent reads/writes, use `:wal` mode:

    EctoLibSql.Pragma.set_journal_mode(state, :wal)

# `set_synchronous`

Set the synchronous mode.

Controls how often SQLite syncs data to disk:
- `:off` (0) - No syncing (fastest, risk of corruption)
- `:normal` (1) - Sync at critical moments (good balance)
- `:full` (2) - Sync after every write (safest, slowest)
- `:extra` (3) - Even more syncing than FULL

## Parameters

  - state: Connection state
  - level: One of `:off`, `:normal`, `:full`, `:extra`, or integer 0-3

## Returns

  - `{:ok, result}` on success
  - `{:error, reason}` on failure

## Examples

    {:ok, _} = EctoLibSql.Pragma.set_synchronous(state, :normal)

## Recommendations

- Production: `:normal` or `:full` (with WAL mode, `:normal` is usually sufficient)
- Development: `:normal`
- Never use `:off` in production

# `set_user_version`

Set the user version number.

## Parameters

  - state: Connection state
  - version: Integer version number

## Returns

  - `{:ok, result}` on success
  - `{:error, reason}` on failure

## Examples

    {:ok, _} = EctoLibSql.Pragma.set_user_version(state, 42)

# `synchronous`

Query the current synchronous setting.

## Parameters

  - state: Connection state

## Returns

  - `{:ok, result}` where result.rows contains the current level (0-3)
  - `{:error, reason}` on failure

## Examples

    {:ok, result} = EctoLibSql.Pragma.synchronous(state)
    # result.rows => [[2]] for FULL, [[1]] for NORMAL, etc.

# `table_info`

Get information about a table's columns.

This is useful for introspection and debugging.

## Parameters

  - state: Connection state
  - table_name: Name of the table (string or atom)

## Returns

  - `{:ok, result}` with column information
  - `{:error, reason}` on failure

## Examples

    {:ok, result} = EctoLibSql.Pragma.table_info(state, :users)
    # result.rows => [
    #   [0, "id", "INTEGER", 0, nil, 1],
    #   [1, "name", "TEXT", 1, nil, 0],
    #   ...
    # ]

Each row contains: [cid, name, type, notnull, dflt_value, pk]

# `table_list`

List all tables in the database.

## Parameters

  - state: Connection state

## Returns

  - `{:ok, result}` with table names
  - `{:error, reason}` on failure

## Examples

    {:ok, result} = EctoLibSql.Pragma.table_list(state)
    # result.rows => [["users"], ["posts"], ...]

# `user_version`

Get the user version number.

SQLite databases can store a user version number (typically used for schema versioning).

## Parameters

  - state: Connection state

## Returns

  - `{:ok, result}` where result.rows contains the version number
  - `{:error, reason}` on failure

## Examples

    {:ok, result} = EctoLibSql.Pragma.user_version(state)
    # result.rows => [[42]]

---

*Consult [api-reference.md](api-reference.md) for complete listing*