> ## Documentation Index
> Fetch the complete documentation index at: https://docs.myspellchecker.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Schema Management

> Centralized database schema definitions and management for SQLite table creation, indexing, and migrations.

All dictionary tables (syllables, words, bigrams, trigrams, and processed-file tracking) are defined in one place and applied through the `SchemaManager`. This keeps schema evolution consistent across pipeline stages and manual database operations.

## Overview

```python theme={null}
from myspellchecker.data_pipeline.schema_manager import SchemaManager
import sqlite3

# SchemaManager requires connection and cursor
with sqlite3.connect("mydict.db") as conn:
    cursor = conn.cursor()
    manager = SchemaManager(conn, cursor)

    # Create all tables and indexes
    manager.create_schema()
```

## SchemaManager Class

Manages database schema with predefined tables and indexes:

```python theme={null}
class SchemaManager:
    """Manages database schema for spell checker dictionaries.

    Provides centralized definitions for all tables and indexes,
    ensuring consistency across dictionary builds.

    Args:
        conn: SQLite database connection
        cursor: SQLite database cursor
        console: Optional PipelineConsole for output
    """

    # Table definitions
    TABLES = {
        "syllables": '''
            CREATE TABLE IF NOT EXISTS syllables (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                syllable TEXT UNIQUE NOT NULL,
                frequency INTEGER DEFAULT 0
            )
        ''',

        "words": '''
            CREATE TABLE IF NOT EXISTS words (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                word TEXT UNIQUE NOT NULL,
                syllable_count INTEGER,
                frequency INTEGER DEFAULT 0,
                pos_tag TEXT,
                is_curated INTEGER DEFAULT 0,
                inferred_pos TEXT,
                inferred_confidence REAL,
                inferred_source TEXT
            )
        ''',

        "bigrams": '''
            CREATE TABLE IF NOT EXISTS bigrams (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                word1_id INTEGER,
                word2_id INTEGER,
                probability REAL DEFAULT 0.0,
                count INTEGER DEFAULT 0,
                FOREIGN KEY(word1_id) REFERENCES words(id),
                FOREIGN KEY(word2_id) REFERENCES words(id),
                UNIQUE(word1_id, word2_id)
            )
        ''',

        "trigrams": '''
            CREATE TABLE IF NOT EXISTS trigrams (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                word1_id INTEGER,
                word2_id INTEGER,
                word3_id INTEGER,
                probability REAL DEFAULT 0.0,
                count INTEGER DEFAULT 0,
                FOREIGN KEY(word1_id) REFERENCES words(id),
                FOREIGN KEY(word2_id) REFERENCES words(id),
                FOREIGN KEY(word3_id) REFERENCES words(id),
                UNIQUE(word1_id, word2_id, word3_id)
            )
        ''',

        "pos_unigrams": '''
            CREATE TABLE IF NOT EXISTS pos_unigrams (
                pos TEXT UNIQUE NOT NULL,
                probability REAL DEFAULT 0.0
            )
        ''',

        "pos_bigrams": '''
            CREATE TABLE IF NOT EXISTS pos_bigrams (
                pos1 TEXT NOT NULL,
                pos2 TEXT NOT NULL,
                probability REAL DEFAULT 0.0,
                UNIQUE(pos1, pos2)
            )
        ''',

        "pos_trigrams": '''
            CREATE TABLE IF NOT EXISTS pos_trigrams (
                pos1 TEXT NOT NULL,
                pos2 TEXT NOT NULL,
                pos3 TEXT NOT NULL,
                probability REAL DEFAULT 0.0,
                UNIQUE(pos1, pos2, pos3)
            )
        ''',

        "processed_files": '''
            CREATE TABLE IF NOT EXISTS processed_files (
                path TEXT PRIMARY KEY,
                mtime REAL,
                size INTEGER
            )
        ''',

        "fourgrams": '''
            CREATE TABLE IF NOT EXISTS fourgrams (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                word1_id INTEGER,
                word2_id INTEGER,
                word3_id INTEGER,
                word4_id INTEGER,
                probability REAL DEFAULT 0.0,
                count INTEGER DEFAULT 0,
                FOREIGN KEY(word1_id) REFERENCES words(id),
                FOREIGN KEY(word2_id) REFERENCES words(id),
                FOREIGN KEY(word3_id) REFERENCES words(id),
                FOREIGN KEY(word4_id) REFERENCES words(id),
                UNIQUE(word1_id, word2_id, word3_id, word4_id)
            )
        ''',

        "fivegrams": '''
            CREATE TABLE IF NOT EXISTS fivegrams (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                word1_id INTEGER,
                word2_id INTEGER,
                word3_id INTEGER,
                word4_id INTEGER,
                word5_id INTEGER,
                probability REAL DEFAULT 0.0,
                count INTEGER DEFAULT 0,
                FOREIGN KEY(word1_id) REFERENCES words(id),
                FOREIGN KEY(word2_id) REFERENCES words(id),
                FOREIGN KEY(word3_id) REFERENCES words(id),
                FOREIGN KEY(word4_id) REFERENCES words(id),
                FOREIGN KEY(word5_id) REFERENCES words(id),
                UNIQUE(word1_id, word2_id, word3_id, word4_id, word5_id)
            )
        ''',

        "metadata": '''
            CREATE TABLE IF NOT EXISTS metadata (
                key TEXT PRIMARY KEY,
                value TEXT NOT NULL
            )
        ''',

        "confusable_pairs": '''
            CREATE TABLE IF NOT EXISTS confusable_pairs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                word1 TEXT NOT NULL,
                word2 TEXT NOT NULL,
                confusion_type TEXT NOT NULL,
                context_overlap REAL DEFAULT 0.0,
                freq_ratio REAL,
                suppress INTEGER DEFAULT 0,
                source TEXT DEFAULT 'mined',
                UNIQUE(word1, word2, confusion_type)
            )
        ''',

        "compound_confusions": '''
            CREATE TABLE IF NOT EXISTS compound_confusions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                compound TEXT NOT NULL,
                part1 TEXT NOT NULL,
                part2 TEXT NOT NULL,
                compound_freq INTEGER DEFAULT 0,
                split_freq INTEGER DEFAULT 0,
                pmi REAL DEFAULT 0.0,
                UNIQUE(compound, part1, part2)
            )
        ''',

        "collocations": '''
            CREATE TABLE IF NOT EXISTS collocations (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                word1 TEXT NOT NULL,
                word2 TEXT NOT NULL,
                pmi REAL NOT NULL,
                npmi REAL,
                count INTEGER NOT NULL,
                UNIQUE(word1, word2)
            )
        ''',

        "register_tags": '''
            CREATE TABLE IF NOT EXISTS register_tags (
                word TEXT PRIMARY KEY,
                register TEXT NOT NULL,
                confidence REAL DEFAULT 0.0,
                formal_count INTEGER DEFAULT 0,
                informal_count INTEGER DEFAULT 0
            )
        ''',
    }

    # Index definitions for query optimization
    INDEXES = {
        "idx_syllables_text": "CREATE INDEX IF NOT EXISTS idx_syllables_text ON syllables(syllable)",
        "idx_words_text": "CREATE INDEX IF NOT EXISTS idx_words_text ON words(word)",
        "idx_bigrams_w1_w2": "CREATE INDEX IF NOT EXISTS idx_bigrams_w1_w2 ON bigrams(word1_id, word2_id)",
        "idx_trigrams_w1_w2_w3": "CREATE INDEX IF NOT EXISTS idx_trigrams_w1_w2_w3 ON trigrams(word1_id, word2_id, word3_id)",
        "idx_fourgrams_w1_w2_w3_w4": "CREATE INDEX IF NOT EXISTS idx_fourgrams_w1_w2_w3_w4 ON fourgrams(word1_id, word2_id, word3_id, word4_id)",
        "idx_fivegrams_w1_w2_w3_w4_w5": "CREATE INDEX IF NOT EXISTS idx_fivegrams_w1_w2_w3_w4_w5 ON fivegrams(word1_id, word2_id, word3_id, word4_id, word5_id)",
        "idx_words_frequency": "CREATE INDEX IF NOT EXISTS idx_words_frequency ON words(frequency)",
        "idx_confusable_word1": "CREATE INDEX IF NOT EXISTS idx_confusable_word1 ON confusable_pairs(word1)",
        "idx_confusable_word2": "CREATE INDEX IF NOT EXISTS idx_confusable_word2 ON confusable_pairs(word2)",
        "idx_compound_word": "CREATE INDEX IF NOT EXISTS idx_compound_word ON compound_confusions(compound)",
        "idx_compound_parts": "CREATE INDEX IF NOT EXISTS idx_compound_parts ON compound_confusions(part1, part2)",
        "idx_colloc_word1": "CREATE INDEX IF NOT EXISTS idx_colloc_word1 ON collocations(word1)",
        "idx_colloc_word2": "CREATE INDEX IF NOT EXISTS idx_colloc_word2 ON collocations(word2)",
    }
```

## Table Schemas

### syllables Table

Stores validated Myanmar syllables:

| Column      | Type                | Description            |
| ----------- | ------------------- | ---------------------- |
| `id`        | INTEGER PRIMARY KEY | Auto-incrementing ID   |
| `syllable`  | TEXT UNIQUE         | The syllable text      |
| `frequency` | INTEGER             | Corpus frequency count |

```sql theme={null}
-- Example queries
SELECT * FROM syllables WHERE frequency > 100;
SELECT syllable FROM syllables ORDER BY frequency DESC LIMIT 10;
```

### words Table

Stores dictionary words with POS information:

| Column                | Type                | Description                    |
| --------------------- | ------------------- | ------------------------------ |
| `id`                  | INTEGER PRIMARY KEY | Auto-incrementing ID           |
| `word`                | TEXT UNIQUE         | The word text                  |
| `syllable_count`      | INTEGER             | Number of syllables            |
| `frequency`           | INTEGER             | Corpus frequency count         |
| `pos_tag`             | TEXT                | POS tag from corpus            |
| `is_curated`          | INTEGER             | Curated word flag (0/1)        |
| `inferred_pos`        | TEXT                | POS tag from inference         |
| `inferred_confidence` | REAL                | Inference confidence (0.0-1.0) |
| `inferred_source`     | TEXT                | Source of inference            |

```sql theme={null}
-- Example queries
SELECT * FROM words WHERE pos_tag = 'N' ORDER BY frequency DESC;
SELECT word, inferred_pos FROM words WHERE inferred_confidence > 0.8;
```

### bigrams Table

Stores word bigram probabilities using word IDs:

| Column        | Type                | Description                |
| ------------- | ------------------- | -------------------------- |
| `id`          | INTEGER PRIMARY KEY | Auto-incrementing ID       |
| `word1_id`    | INTEGER             | Foreign key to first word  |
| `word2_id`    | INTEGER             | Foreign key to second word |
| `probability` | REAL                | P(word2 \| word1)          |
| `count`       | INTEGER             | Raw co-occurrence count    |

```sql theme={null}
-- Example: Find common word pairs (requires JOIN)
SELECT w1.word, w2.word, b.probability
FROM bigrams b
JOIN words w1 ON b.word1_id = w1.id
JOIN words w2 ON b.word2_id = w2.id
WHERE w1.word = 'မြန်မာ'
ORDER BY b.probability DESC;
```

### trigrams Table

Stores word trigram probabilities using word IDs:

| Column        | Type                | Description                |
| ------------- | ------------------- | -------------------------- |
| `id`          | INTEGER PRIMARY KEY | Auto-incrementing ID       |
| `word1_id`    | INTEGER             | Foreign key to first word  |
| `word2_id`    | INTEGER             | Foreign key to second word |
| `word3_id`    | INTEGER             | Foreign key to third word  |
| `probability` | REAL                | P(word3 \| word1, word2)   |
| `count`       | INTEGER             | Raw co-occurrence count    |

### pos\_\* Tables

POS tag probability tables for statistical tagging:

```sql theme={null}
-- POS unigrams: Tag probabilities
SELECT pos, probability FROM pos_unigrams ORDER BY probability DESC;

-- POS bigrams: Tag transition probabilities
SELECT pos1, pos2, probability FROM pos_bigrams WHERE pos1 = 'N';

-- POS trigrams: Tag sequence probabilities
SELECT * FROM pos_trigrams WHERE pos1 = 'N' AND pos2 = 'P';
```

### processed\_files Table

Tracks processed files for incremental builds:

| Column  | Type             | Description            |
| ------- | ---------------- | ---------------------- |
| `path`  | TEXT PRIMARY KEY | File path              |
| `mtime` | REAL             | Modification timestamp |
| `size`  | INTEGER          | File size in bytes     |

## Methods

### create\_schema

Creates all tables:

```python theme={null}
def create_schema(
    self,
    in_transaction: bool = False,
    defer_indexes: bool = False,
) -> List[str]:
    """Create all tables in the database.

    Args:
        in_transaction: If True, don't start/commit own transaction
        defer_indexes: If True, skip index creation (call create_indexes later)

    Returns:
        List of verified table names
    """
```

### create\_indexes

Creates performance indexes:

```python theme={null}
def create_indexes(self) -> None:
    """Create all indexes for query optimization.

    Should be called AFTER bulk data insertion for best performance.
    """
```

### ensure\_inferred\_pos\_columns

Adds columns for inferred POS data:

```python theme={null}
def ensure_inferred_pos_columns(self) -> None:
    """Ensure the inferred_pos columns exist in the words table.

    Adds the columns if they don't exist (for database migration):
    - inferred_pos: POS tag from statistical inference
    - inferred_confidence: Confidence score (0.0-1.0)
    - inferred_source: Source of the inference
    """
```

## Usage in Pipeline

### Creating a New Database

```python theme={null}
from myspellchecker.data_pipeline.schema_manager import SchemaManager
import sqlite3

def create_dictionary(output_path: str):
    """Create a new dictionary database."""
    with sqlite3.connect(output_path) as conn:
        cursor = conn.cursor()
        manager = SchemaManager(conn, cursor)

        # Create schema (tables and indexes)
        manager.create_schema()

        # Insert data...
```

### Using with DatabasePackager

The recommended approach is to use `DatabasePackager` which handles schema management automatically:

```python theme={null}
from myspellchecker.data_pipeline import DatabasePackager

packager = DatabasePackager(
    input_dir="frequencies/",
    database_path="dict.db",
)

packager.connect()
packager.create_schema()  # Uses SchemaManager internally
packager.load_syllables()
packager.load_words()
packager.close()
```

## See Also

* [Database Schema](/data-pipeline/database-schema) - Full schema reference
* [Building Guide](/data-pipeline/building) - Build process
* [Data Pipeline Index](/data-pipeline/index) - Pipeline overview