Drops.SQL.Sqlite (drops_relation v0.1.0)

View Source

SQLite database adapter for introspecting table metadata.

This module implements the Drops.SQL.Database behavior to provide SQLite-specific database introspection capabilities. It uses SQLite's PRAGMA statements to extract comprehensive table metadata including columns, constraints, indices, and foreign keys.

SQLite-Specific Features

SQLite has several unique characteristics that this adapter handles:

  • Dynamic typing - SQLite uses type affinity rather than strict types
  • PRAGMA statements - Used for introspection instead of information schema
  • Foreign key constraints - Must be explicitly enabled and queried
  • Check constraints - Extracted from table creation SQL
  • Autoincrement - Special handling for INTEGER PRIMARY KEY columns

PRAGMA Statements Used

The adapter uses several SQLite PRAGMA statements for introspection:

  • PRAGMA table_info(table_name) - Column information
  • PRAGMA index_list(table_name) - Index information
  • PRAGMA index_info(index_name) - Index column details
  • PRAGMA foreign_key_list(table_name) - Foreign key constraints
  • sqlite_master table queries - Check constraints and table SQL

Type Mapping

SQLite types are mapped to Ecto types through the Drops.SQL.Compilers.Sqlite compiler. Common mappings include:

  • INTEGER → :integer
  • TEXT → :string
  • REAL → :float
  • BLOB → :binary
  • NUMERIC → :decimal

Usage

# Direct usage (typically not needed)
{:ok, table} = Drops.SQL.Sqlite.table("users", MyApp.Repo)

# Preferred usage through main interface
{:ok, table} = Drops.SQL.Database.table("users", MyApp.Repo)

Implementation Notes

  • Foreign key information is cross-referenced with column data
  • Index information is merged with column metadata
  • Check constraints are parsed from table creation SQL
  • Primary key detection handles both explicit and implicit cases
  • Supports composite primary keys and foreign keys

Error Handling

The adapter handles various SQLite-specific error conditions:

  • Table not found
  • Invalid PRAGMA statements
  • Foreign key constraint parsing errors
  • SQL parsing errors for check constraints

Summary

Functions

adapter()

Returns the database adapter identifier.

introspect_table(table_name, repo)

Introspects a SQLite table and returns its complete metadata as an AST.

list_tables(repo)

Lists all tables in the SQLite database.

opts()

Returns the complete adapter configuration options.

table(name, repo)

Introspects and compiles a database table into a structured representation.

Functions

adapter()

@spec adapter() :: atom()

Returns the database adapter identifier.

This function provides quick access to the adapter identifier atom that was specified in the use Drops.SQL.Database configuration.

Returns

The adapter identifier atom.

Examples

MyAdapter.adapter()
# => :postgres

introspect_table(table_name, repo)

@spec introspect_table(String.t(), module()) ::
  {:ok, Drops.SQL.Database.table()} | {:error, term()}

Introspects a SQLite table and returns its complete metadata as an AST.

This function implements the Drops.SQL.Database behavior for SQLite databases. It uses SQLite's PRAGMA statements to extract comprehensive table information including columns, foreign keys, and indices.

Process

  1. Introspects foreign keys using PRAGMA foreign_key_list
  2. Introspects indices using PRAGMA index_list and PRAGMA index_info
  3. Introspects columns using PRAGMA table_info with cross-referenced FK/index data
  4. Combines all metadata into a table AST structure

Parameters

  • table_name - The name of the SQLite table to introspect
  • repo - The Ecto repository configured for SQLite

Returns

  • {:ok, Database.table()} - Successfully introspected table AST
  • {:error, term()} - Error during introspection (table not found, etc.)

AST Structure

Returns a table AST in the format: {:table, {{:identifier, table_name}, columns, foreign_keys, indices}}

SQLite-Specific Behavior

  • Handles SQLite's dynamic typing system
  • Processes composite primary keys correctly
  • Cross-references foreign key and index information with columns
  • Extracts check constraints from table creation SQL
  • Handles autoincrement detection for INTEGER PRIMARY KEY columns

list_tables(repo)

@spec list_tables(module()) :: {:ok, [String.t()]} | {:error, term()}

Lists all tables in the SQLite database.

This function implements the list_tables/1 callback for SQLite databases. It queries the sqlite_master table to retrieve all user-defined tables.

Parameters

  • repo - The Ecto repository configured for SQLite

Returns

  • {:ok, [String.t()]} - Successfully retrieved list of table names
  • {:error, term()} - Error during query execution

Examples

{:ok, tables} = Drops.SQL.Sqlite.list_tables(MyApp.Repo)
# => {:ok, ["users", "posts", "comments"]}

Implementation Notes

  • Excludes SQLite system tables (those starting with 'sqlite_')
  • Excludes migration tables (those ending with '_migrations')
  • Results are ordered alphabetically by table name
  • Only returns actual tables, not views or other database objects

opts()

@spec opts() :: keyword()

Returns the complete adapter configuration options.

This function provides access to all configuration options passed to the use Drops.SQL.Database macro, including the adapter identifier and compiler module.

Returns

A keyword list containing all adapter configuration options.

Examples

MyAdapter.opts()
# => [adapter: :postgres, compiler: Drops.SQL.Compilers.Postgres]

table(name, repo)

@spec table(String.t(), module()) ::
  {:ok, Drops.SQL.Database.Table.t()} | {:error, term()}

Introspects and compiles a database table into a structured representation.

This function provides the main interface for the adapter, combining both introspection and compilation steps into a single operation. It calls the adapter's introspect_table/2 implementation and then processes the resulting AST through the configured compiler.

Parameters

  • name - The name of the database table to introspect (as a string)
  • repo - The Ecto repository module configured for database access

Returns

  • {:ok, Table.t()} - Successfully compiled table with complete metadata
  • {:error, term()} - Error during introspection or compilation

Examples

# Introspect a users table
{:ok, table} = MyAdapter.table("users", MyApp.Repo)

# Access the compiled table data
table.name          # :users
table.columns       # [%Column{...}, ...]
table.primary_key   # %PrimaryKey{...}

Error Handling

case MyAdapter.table("users", MyApp.Repo) do
  {:ok, table} ->
    process_table(table)
  {:error, reason} ->
    Logger.error("Failed to introspect table: #{inspect(reason)}")
end