Metadata-Version: 2.4
Name: minidb-sql
Version: 1.3.1
Summary: A miniature in-memory database with SQL-like query support
Author: MiniDB Contributors
License-Expression: MIT
Project-URL: Homepage, https://github.com/sebtardif/MiniDB
Project-URL: Repository, https://github.com/sebtardif/MiniDB
Project-URL: Issues, https://github.com/sebtardif/MiniDB/issues
Project-URL: Changelog, https://github.com/sebtardif/MiniDB/blob/main/CHANGELOG.md
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Topic :: Database
Classifier: Typing :: Typed
Requires-Python: >=3.11
Description-Content-Type: text/markdown
License-File: LICENSE
Dynamic: license-file

# MiniDB

[![CI](https://github.com/SebTardif/MiniDB/actions/workflows/ci.yml/badge.svg?event=pull_request)](https://github.com/SebTardif/MiniDB/actions/workflows/ci.yml?query=event%3Apull_request)
[![Security](https://github.com/SebTardif/MiniDB/actions/workflows/security.yml/badge.svg?event=push)](https://github.com/SebTardif/MiniDB/actions/workflows/security.yml)
[![Release](https://img.shields.io/github/v/release/SebTardif/MiniDB?logo=github&sort=semver)](https://github.com/SebTardif/MiniDB/releases/latest)
[![Python](https://img.shields.io/badge/python-3.11%2B-blue?logo=python&logoColor=white)](https://www.python.org/downloads/)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](./LICENSE)

[![PyPI](https://img.shields.io/pypi/v/minidb-sql)](https://pypi.org/project/minidb-sql/)
[![Tests](https://img.shields.io/badge/tests-200%2B%20passing-brightgreen)](https://github.com/SebTardif/MiniDB/tree/main/tests)
[![Scorecard](https://github.com/SebTardif/MiniDB/actions/workflows/scorecard.yml/badge.svg?event=push)](https://github.com/SebTardif/MiniDB/actions/workflows/scorecard.yml)

A miniature in-memory database with SQL-like query support, built from scratch using only Python 3.11+ standard library.

## Features

- **Typed Columns**: INTEGER, STRING, FLOAT, BOOLEAN
- **CRUD Operations**: INSERT, SELECT, UPDATE, DELETE
- **Parameters**: `?` placeholders bound after parse (`execute(sql, params)`)
- **Transactions**: in-memory `BEGIN` / `COMMIT` / `ROLLBACK` snapshots
- **SQL-like Query Language**:
  - WHERE with AND/OR, NOT, parentheses, comparisons (=, >, <, >=, <=, !=, LIKE, IN), and IS NULL / IS NOT NULL
  - SELECT column aliases (`col AS alias`)
  - SELECT DISTINCT
  - ORDER BY (ASC/DESC)
  - GROUP BY with aggregations
  - HAVING after GROUP BY
  - LIMIT clause
  - Searched `CASE` in the select list (`CASE WHEN ... THEN ... ELSE ... END`)
- **Aggregations**: COUNT, SUM, AVG, MIN, MAX
- **JOINs**: INNER JOIN and LEFT JOIN between tables
- **Indexing**: Automatic hash-based indexing on primary keys
- **Query Planner**: Chooses between index scans and table scans (`db.explain(sql)`)
- **Persistence**: Save/load database to JSON files with versioning

## Installation

```bash
pip install minidb-sql
```

The library uses only the Python standard library. `pip install minidb` is a different project.

```python
from minidb_sql import MiniDB
```

## Quick Start

`execute()` runs any statement and returns a row id, an affected-row count, or None.
`query()` is SELECT-only and returns rows.
Both accept an optional `params` sequence for `?` placeholders. Values are bound
after parse, not interpolated into the SQL string.

```python
from minidb_sql import MiniDB, Column, ColumnType

# Create database
db = MiniDB()

# Create table using SQL
db.execute("""
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name STRING NOT NULL,
        age INTEGER,
        salary FLOAT,
        active BOOLEAN
    )
""")

# Or create table programmatically
db.create_table(
    'orders',
    [
        Column('id', ColumnType.INTEGER, primary_key=True),
        Column('user_id', ColumnType.INTEGER),
        Column('product', ColumnType.STRING),
        Column('total', ColumnType.FLOAT),
    ],
)

# Insert data
db.execute("INSERT INTO users (id, name, age, salary, active) VALUES (1, 'Alice', 30, 75000.0, true)")
db.execute(
    'INSERT INTO users (id, name, age, salary, active) VALUES (?, ?, ?, ?, ?)',
    [2, 'Bob', 25, 55000.0, False],
)

# Query data
results = db.query('SELECT * FROM users WHERE age > ?', [28])

# Complex queries
results = db.query("""
    SELECT name, salary FROM users
    WHERE active = true AND salary > 60000
    ORDER BY salary DESC
""")

# Inspect the SELECT plan (index_scan vs table_scan)
print(db.explain('SELECT * FROM users WHERE id = 1'))

# Aggregations
results = db.query('SELECT COUNT(*), AVG(salary) FROM users')

# GROUP BY with HAVING
results = db.query("""
    SELECT active, COUNT(*), AVG(salary)
    FROM users
    GROUP BY active
    HAVING COUNT(*) > 1
""")

# JOINs
results = db.query("""
    SELECT users.name, orders.product, orders.total
    FROM users
    JOIN orders ON users.id = orders.user_id
    WHERE orders.total > 50
""")

# UPDATE
affected = db.execute('UPDATE users SET salary = 80000.0 WHERE id = 1')

# DELETE
affected = db.execute('DELETE FROM users WHERE active = false')

# Snapshot transaction (in-memory only)
db.execute('BEGIN')
db.execute("INSERT INTO users (id, name, age, salary, active) VALUES (3, 'Cara', 40, 90000.0, true)")
db.execute('ROLLBACK')

# DROP TABLE (SQL or Python API)
db.execute('CREATE TABLE scratch (id INTEGER PRIMARY KEY)')
db.execute('DROP TABLE scratch')
db.create_table('temp', [Column('id', ColumnType.INTEGER, primary_key=True)])
db.drop_table('temp')

# Persistence
db.save('my_database.json')
db = MiniDB.load('my_database.json')
```

## SQL Syntax Reference

### CREATE TABLE

```sql
CREATE TABLE table_name (
    column1 INTEGER PRIMARY KEY,
    column2 STRING NOT NULL,
    column3 FLOAT,
    column4 BOOLEAN
)
```

### INSERT

```sql
INSERT INTO table_name (col1, col2, col3) VALUES (1, 'value', 3.14)
INSERT INTO table_name (col1, col2, col3) VALUES (?, ?, ?)
```

Use `db.execute(sql, [1, 'value', 3.14])` or `db.query(sql, params)` to bind `?`
left-to-right. Too few or too many params raise `InvalidQueryError`.

### SELECT

```sql
SELECT * FROM table_name
SELECT col1, col2 FROM table_name
SELECT col1 AS alias FROM table_name
SELECT DISTINCT col1 FROM table_name
SELECT DISTINCT col1, col2 FROM table_name
SELECT col1, COUNT(*), AVG(col2) FROM table_name GROUP BY col1
SELECT col1, COUNT(*) FROM table_name GROUP BY col1 HAVING COUNT(*) > 1
SELECT col1, COUNT(*) AS n FROM table_name GROUP BY col1 HAVING n > 1
```

### CASE

```sql
SELECT CASE WHEN age >= 18 THEN 'adult' ELSE 'minor' END FROM users
SELECT CASE WHEN age >= 18 THEN 'adult' WHEN age IS NULL THEN 'unknown' ELSE 'minor' END AS kind
```

`CASE` is searched form in the select list. The first true `WHEN` wins. A comparison that is unknown, including `= NULL`, is not true, so a later `WHEN` or `ELSE` runs. No `ELSE` returns NULL. `WHEN` uses the same comparison rules as `WHERE`. The result column is `CASE` unless you give it an alias. `CASE` next to `GROUP BY` may only read grouped columns.

### HAVING

```sql
HAVING COUNT(*) > 1
HAVING SUM(quantity) > 30
HAVING n > 2
```

HAVING filters grouped rows after GROUP BY (or the single row from an ungrouped aggregate). It is rejected when the SELECT has neither GROUP BY nor aggregate functions.

### WHERE Clause

```sql
WHERE col = value
WHERE col > value
WHERE col >= value
WHERE col < value
WHERE col <= value
WHERE col != value
WHERE col LIKE 'pattern%'     -- % matches any sequence
WHERE col IN (1, 2, 3)
WHERE col = ?
WHERE col IN (?, ?)
WHERE col IS NULL
WHERE col IS NOT NULL
WHERE cond1 AND cond2
WHERE cond1 OR cond2
WHERE NOT col = value
WHERE (cond1 OR cond2) AND cond3
```

### ORDER BY

```sql
ORDER BY col ASC
ORDER BY col DESC
ORDER BY col1 ASC, col2 DESC
```

### LIMIT

```sql
LIMIT 10
```

### JOIN

```sql
SELECT t1.col, t2.col
FROM t1
JOIN t2 ON t1.id = t2.t1_id

-- LEFT JOIN (includes unmatched left rows with NULLs)
SELECT t1.col, t2.col
FROM t1
LEFT JOIN t2 ON t1.id = t2.t1_id
```

### UPDATE

```sql
UPDATE table_name SET col1 = value1, col2 = value2 WHERE condition
```

### DELETE

```sql
DELETE FROM table_name WHERE condition
```

### BEGIN / COMMIT / ROLLBACK

```sql
BEGIN
COMMIT
ROLLBACK
```

These copy the in-memory tables at `BEGIN` and restore that copy on `ROLLBACK`.
They are not durable and not concurrent. MiniDB is single-threaded.

### DROP TABLE

```sql
DROP TABLE table_name
```

```python
db.execute('DROP TABLE table_name')
db.drop_table('table_name')
```

## Architecture

```
minidb_sql/
├── __init__.py      # Public API exports
├── database.py      # MiniDB main class
├── table.py         # Table storage
├── column.py        # Column and Schema definitions
├── index.py         # Hash-based indexing
├── parser.py        # SQL lexer and parser
├── query.py         # Query execution engine
├── planner.py       # Query planner
├── persistence.py   # JSON serialization
├── types.py         # Type definitions
└── errors.py        # Custom exceptions
```

## Test Suite

MiniDB includes a comprehensive test suite with 95+ test cases:

```bash
# Run all tests
python -m pytest tests/ -v

# Run specific test file
python -m pytest tests/test_queries.py -v

# Run with coverage
python -m pytest tests/ -v --cov=minidb_sql
```

### Test Categories

- **test_database.py**: Database lifecycle, table management, `?` parameters, and snapshot transactions
- **test_crud.py**: INSERT, SELECT, UPDATE, DELETE operations
- **test_queries.py**: WHERE, ORDER BY, LIMIT, parser errors, type validation
- **test_aggregations.py**: COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING
- **test_joins.py**: JOIN operations
- **test_index.py**: Indexing and query planning
- **test_persistence.py**: Save/load functionality
- **test_performance.py**: Large dataset tests (10,000+ rows)

## Performance

MiniDB is designed for small to medium datasets:

- **10,000 rows**: All operations complete in under 30 seconds
- **Indexed lookups**: O(1) for primary key equality
- **Range queries**: O(n) with index optimization
- **Table scans**: O(n) for non-indexed columns

## Limitations

- In-memory only (no disk-based storage during operation)
- Single-threaded
- Transactions are in-memory snapshots only (not durable, not concurrent)
- No foreign key constraints
- Limited JOIN support (INNER and LEFT JOIN only, no RIGHT JOIN execution)

## License

MIT License - Use freely for any purpose.

## Contributing

See [CONTRIBUTING.md](CONTRIBUTING.md) for development setup and guidelines.

Areas for improvement:

- B-tree indexes for range queries
- RIGHT JOIN
- Subqueries
- More aggregate functions
- Query optimization
- Concurrent access
