Metadata-Version: 2.4
Name: optimadetosql
Version: 0.2.1
Summary: Optimade filter language SQL translator
Author-email: Gumar Arutynian <legendary.killtell@gmail.com>, Evgeny Blokhin <eb@tilde.pro>
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Science/Research
Classifier: Topic :: Scientific/Engineering :: Chemistry
Classifier: Topic :: Scientific/Engineering :: Physics
Classifier: Topic :: Scientific/Engineering :: Information Analysis
Classifier: Topic :: Software Development :: Libraries :: Python Modules
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: Programming Language :: Python :: 3.15
Requires-Python: >=3.11
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: optimade==1.4.1
Requires-Dist: mcp
Requires-Dist: httpx
Requires-Dist: python-dotenv
Provides-Extra: dev
Requires-Dist: pytest>=7.0; extra == "dev"
Requires-Dist: mypy>=1.0; extra == "dev"
Requires-Dist: ruff>=0.1.0; extra == "dev"
Dynamic: license-file

# optimadetosql

Translate [OPTIMADE](https://www.optimade.org) filter language queries into SQL with support for custom field mapping, behaviors, JOINs, and multiple SQL dialects.

## Installation

```bash
pip install optimadetosql
```

For development:
```bash
pip install -e ".[dev]"
```

Requires Python >= 3.11.

## Quick Start

```python
from optimadetosql import DynamicMapper, OptimadeToSQL

mapper = DynamicMapper(
    table_name="structures",
    field_mapping={
        "id": "id",
        "nelements": "n_elements",
        "chemical_formula": "formula",
        "elements": "elements",
    },
)

translator = OptimadeToSQL(mapper=mapper)
sql = translator.translate("nelements > 3")
print(sql)
# SELECT * FROM structures WHERE structures.n_elements > 3
```

## Core Concepts

### 1. DynamicMapper -- Map OPTIMADE fields to database columns

The `DynamicMapper` is the bridge between OPTIMADE field names and your actual database schema.

| Parameter | Type | Description |
|-----------|------|-------------|
| `table_name` | `str` | Your database table name |
| `field_mapping` | `dict[str, str]` | Maps OPTIMADE field names to database column names |
| `provider_prefix` | `str` | Prefix for provider-specific fields (default: `"exa"`) |
| `field_types` | `dict[str, str]` | Type hints for fields (`"string"`, `"integer"`, `"float"`, `"boolean"`, `"datetime"`) |
| `defaults` | `dict[str, Any]` | Default values for fields |
| `enum_types` | `dict[str, str]` | PostgreSQL enum type names for fields |
| `behaviors` | `dict[str, BehaviorInput]` | Value transformation behaviors for fields |
| `join_map` | `dict[str, tuple]` | Defines JOINs to related tables |

#### Field Mapping Examples

```python
# Simple column mapping
mapper = DynamicMapper(
    table_name="structures",
    field_mapping={
        "id": "id",
        "nelements": "n_elements",
        "chemical_formula": "formula",
    },
)

# JSON / JSONB extraction (PostgreSQL)
mapper = DynamicMapper(
    table_name="entries",
    field_mapping={
        "id": "id",
        "nelements": "value->>'nelements'",     # text extraction
        "elements": "value->'elements'",           # jsonb extraction
        "_mpds_bandgap": "attributes->>'bandgap'",
    },
)

# Literal / constant values (prefix the value with single quotes)
mapper = DynamicMapper(
    table_name="structures",
    field_mapping={
        "type": "'structures'",   # always returns the string 'structures'
    },
)
```

#### Field Types

Use `field_types` to tell the translator how to handle numeric comparisons on JSON fields:

```python
mapper = DynamicMapper(
    table_name="entries",
    field_mapping={
        "_mpds_bandgap": "attributes->>'bandgap'",
    },
    field_types={
        "id": "string",
        "nelements": "integer",
        "_mpds_bandgap": "float",
        "last_modified": "datetime",
    },
)
```

When a field is typed as `"float"` or `"integer"` and maps to a JSON path (contains `->>`), the translator automatically casts it in SQL:

```
# Input: _mpds_bandgap > 2.5
# Output: ... WHERE (attributes->>'bandgap')::float > 2.5
```

### 2. Behaviors -- Transform field values

Behaviors let you transform how field values are handled in queries. Each behavior implements two methods:

- **`get(input, dialect)`** -- transforms the *user-supplied value* (e.g., normalizes a chemical formula)
- **`format(input, dialect)`** -- wraps the *SQL column expression* (e.g., applies a SQL function)

#### Built-in Behaviors

##### ChemicalFormulaBehavior

Normalizes chemical formula input (e.g., `"sio2"` -> `"SiO2"`):

```python
from optimadetosql import DynamicMapper
from optimadetosql.behaviors.ready import ChemicalFormulaBehavior

mapper = DynamicMapper(
    table_name="structures",
    field_mapping={"chemical_formula": "formula"},
    behaviors={"chemical_formula": ChemicalFormulaBehavior()},
)

translator = OptimadeToSQL(mapper=mapper)
result = translator.translate('chemical_formula = "SiO2"')
```

##### PeriodicTableBehavior

Expands element group names into element lists for set queries:

```python
from optimadetosql.behaviors.ready import PeriodicTableBehavior

mapper = DynamicMapper(
    table_name="structures",
    field_mapping={"elements": "elements"},
    behaviors={"elements": PeriodicTableBehavior()},
)

# Now you can query:
# elements HAS "transition_metals"
# elements HAS "halogens"
# elements HAS "alkali_metals"
```

Available groups: `alkali_metals`, `alkaline_earth_metals`, `transition_metals`, `post_transition_metals`, `metalloids`, `nonmetals`, `halogens`, `noble_gases`, `lanthanides`, `actinides`, `rare_earths`, `refractory_metals`, `noble_metals`, `metals`. Also: `group_N` (1-18) and `period_N` (1-7).

##### CrystalSystemBehavior

Expands crystal system names into space group number ranges:

```python
from optimadetosql.behaviors.ready import CrystalSystemBehavior

mapper = DynamicMapper(
    table_name="phases",
    field_mapping={"spacegroup": "spg"},
    behaviors={"spacegroup": CrystalSystemBehavior()},
)

# Query: spacegroup IN "cubic" -> space group numbers 195-230
# Query: spacegroup IN "hexagonal" -> space group numbers 168-194
```

Available systems: `triclinic`, `monoclinic`, `orthorhombic`, `tetragonal`, `trigonal`, `hexagonal`, `cubic`.

##### PropertyRangeBehavior

Maps named property ranges to numeric thresholds:

```python
from optimadetosql.behaviors.ready import PropertyRangeBehavior

mapper = DynamicMapper(
    table_name="entries",
    field_mapping={"_mpds_bandgap": "value->>'bandgap'"},
    field_types={"_mpds_bandgap": "float"},
    behaviors={"_mpds_bandgap": PropertyRangeBehavior("band_gap")},
)

# _mpds_bandgap >= "semiconductor"  ->  (value->>'bandgap')::float >= 0.1
# _mpds_bandgap >= "insulator"      ->  (value->>'bandgap')::float >= 4.0
```

Available properties and ranges:

- **band_gap**: `insulator` (>=4.0), `semiconductor` (>=0.1), `semiconductor_wide` (>=2.0), `semiconductor_narrow` (>=0.1), `metal` (>=0.0), `semimetal` (0.0)
- **density**: `ultra_light` (0-1), `light` (1-5), `medium` (5-10), `heavy` (10-20), `ultra_heavy` (20+)
- **formation_energy**: `thermodynamically_stable` (<0), `metastable` (0-0.1), `unstable` (>0.1)
- **magnetic_moment**: `non_magnetic` (0), `paramagnetic` (0-0.1), `ferromagnetic` (>0.1)
- **hardness**: `soft` (0-3), `medium` (3-7), `hard` (7-10), `superhard` (10+)

##### TemperatureUnitBehavior

Converts temperatures to Kelvin:

```python
from optimadetosql.behaviors.ready import TemperatureUnitBehavior

mapper = DynamicMapper(
    table_name="phases",
    field_mapping={"temperature_min": "tmin", "temperature_max": "tmax"},
    field_types={"temperature_min": "float", "temperature_max": "float"},
    behaviors={
        "temperature_min": TemperatureUnitBehavior(),
        "temperature_max": TemperatureUnitBehavior(),
    },
)

# temperature_min > "300c"   ->  tmin > 573.15
# temperature_max < "500f"   ->  tmax < 533.15
```

##### EnergyUnitBehavior

Converts energy units to eV:

```python
from optimadetosql.behaviors.ready import EnergyUnitBehavior

mapper = DynamicMapper(
    table_name="entries",
    field_mapping={"_mpds_formation_energy": "value->>'formation_energy'"},
    field_types={"_mpds_formation_energy": "float"},
    behaviors={"_mpds_formation_energy": EnergyUnitBehavior()},
)

# _mpds_formation_energy < "100meV"  ->  (value->>'formation_energy')::float < 0.1
# _mpds_formation_energy < "0.5ry"   ->  (value->>'formation_energy')::float < 6.803
```

Supported units: `eV`, `meV`, `Ry`/`rydberg`, `Ha`/`hartree`, `kJ`/`kJ/mol`.

##### StoichiometryBehavior

Maps stoichiometry class names to element counts:

```python
from optimadetosql.behaviors.ready import StoichiometryBehavior

mapper = DynamicMapper(
    table_name="structures",
    field_mapping={"nelements": "n_elements"},
    field_types={"nelements": "integer"},
    behaviors={"nelements": StoichiometryBehavior()},
)

# nelements = "binary"    ->  n_elements = 2
# nelements = "ternary"   ->  n_elements = 3
# nelements >= "ternary"  ->  n_elements >= 3
```

Available names: `unary` (1), `binary` (2), `ternary` (3), `quaternary` (4), `quinary` (5), `senary` (6).

##### CompositionFilterBehavior

Maps composition class names to their anion element:

```python
from optimadetosql.behaviors.ready import CompositionFilterBehavior

mapper = DynamicMapper(
    table_name="structures",
    field_mapping={"elements": "elements"},
    behaviors={"elements": CompositionFilterBehavior()},
)

# elements HAS "oxide"    ->  elements HAS "O"
# elements HAS "nitride"   ->  elements HAS "N"
```

Available: `oxide`, `nitride`, `sulfide`, `hydride`, `carbide`, `phosphide`, `chloride`, `fluoride`, `bromide`, `iodide`, `selenide`, `telluride`, `arsenide`, `silicide`, `boride`.

#### Creating a custom behavior

```python
from optimadetosql import CustomBehavior
from optimadetosql.behaviors import register_behavior


class UpperBehavior(CustomBehavior):
    def get(self, input, dialect=None):
        return input.upper()

    def format(self, input, dialect=None):
        return f"UPPER({input})"


register_behavior("upper", UpperBehavior)

mapper = DynamicMapper(
    table_name="structures",
    field_mapping={"chemical_formula": "formula"},
    behaviors={"chemical_formula": "upper"},
)

translator = OptimadeToSQL(mapper=mapper)
result = translator.translate('chemical_formula = "sio2"')
# SELECT * FROM structures WHERE UPPER(formula) = 'SIO2'
```

#### Behavior input formats

For each field in the `behaviors` dict, you can pass:

```python
# 1. Instance (most common)
behaviors={"chemical_formula": ChemicalFormulaBehavior()}

# 2. Class reference (auto-instantiated)
behaviors={"chemical_formula": ChemicalFormulaBehavior}

# 3. String name (registered name or full module path)
behaviors={"chemical_formula": "upper"}
behaviors={"chemical_formula": "optimadetosql.behaviors.ready.chemical_formulae.ChemicalFormulaBehavior"}
```

### 3. JOINs -- Query across related tables

Use `join_map` to define relationships between tables. The join map value is a tuple `(left_key, right_key, related_mapper)`.

```python
from optimadetosql import DynamicMapper

phases_mapper = DynamicMapper(
    table_name="phases",
    field_mapping={
        "id": "phid",
        "chemical_formula": "formula_txt",
        "spacegroup": "spg",
    },
)

entries_mapper = DynamicMapper(
    table_name="entries",
    field_mapping={
        "id": "id",
        "phase_id": "phid",
        "nelements": "value->>'nelements'",
        "chemical_formula": "value->>'chemical_formula'",
    },
    join_map={"phases": ("phid", "phid", phases_mapper)},
)

translator = OptimadeToSQL(mapper=entries_mapper)
result = translator.translate("nelements > 3")
# SELECT * FROM entries
# LEFT JOIN phases ON entries.phid = phases.phid
# WHERE (entries.value->>'nelements')::int > 3
```

Fields from related tables (like `spacegroup`) are automatically routed through the JOIN, including nested JOINs:

```python
result = translator.translate('spacegroup = "225"')
# SELECT * FROM entries
# LEFT JOIN phases ON entries.phid = phases.phid
# WHERE phases.spg = '225'
```

### 4. Multiple Mappers -- Work with different resource types

#### Option A: Separate instances

```python
translator_phases = OptimadeToSQL(mapper=phases_mapper)
translator_entries = OptimadeToSQL(mapper=entries_mapper)
translator_structures = OptimadeToSQL(mapper=structures_mapper)
```

#### Option B: Switch mappers with `.using()`

```python
translator = OptimadeToSQL(mapper=phases_mapper)
translator.using("entries")  # switch to registered entries mapper
sql = translator.translate("nelements > 3")
translator.using("structures")
```

#### Option C: Mapper Registry

```python
from optimadetosql import get_registry

registry = get_registry()
registry.register("phases", phases_mapper, default=True)
registry.register("entries", entries_mapper)

translator = OptimadeToSQL.for_resource("entries")
# or infer from URL path:
translator = OptimadeToSQL.for_path("/structures")
translator = OptimadeToSQL.for_path("/v1/entries?filter=...")
```

### 5. Parameterized Queries (SQL Injection Prevention)

For production use, always prefer parameterized queries which separate values from SQL:

```python
translator = OptimadeToSQL(mapper=mapper)

# Returns (sql_with_placeholders, params)
sql, params = translator.translate_params('nelements > 3 AND elements HAS "Si"')
# sql = 'SELECT * FROM structures WHERE "structures"."n_elements" > $1 ...'
# params = [3, 'Si']

# Use with SQLAlchemy
from sqlalchemy import text
stmt = text(sql)
result = await session.execute(stmt, {f"param_{i+1}": v for i, v in enumerate(params)})
```

### 6. Error Handling

The library provides specific exception types:

```python
from optimadetosql import OptimadeFilterError, OptimadeTranslationError, MapperError, BehaviorError

try:
    sql = translator.translate("!!!invalid!!!")
except OptimadeFilterError as e:
    print(f"Invalid filter: {e}")
except OptimadeTranslationError as e:
    print(f"Translation failed: {e}")
except MapperError as e:
    print(f"Mapper configuration error: {e}")
except BehaviorError as e:
    print(f"Behavior transformation failed: {e}")
```

### 7. OPTIMADE Response Formatting

Wrap database results into OPTIMADE-compliant JSON responses:

```python
from optimadetosql import build_optimade_response

rows = await repository.execute(sql, limit=100, offset=0)
response = build_optimade_response(
    rows,
    query="nelements > 3",
    resource_type="structures",
    provider_name="My Database",
    provider_description="A materials database",
    provider_prefix="mydb",
    more_data_available=len(rows) >= 100,
)

# Returns OPTIMADE-compliant dict with 'data' and 'meta' keys
```

### 8. SQL Dialects

The library currently supports **PostgreSQL** with a dialect system ready for extension.

```python
# PostgreSQL (default)
translator = OptimadeToSQL(mapper=mapper, provider="postgresql")
```

The dialect handles:
- Identifier quoting (`"table"."column"`)
- Value formatting (strings, numbers, booleans)
- JSON/JSONB extraction (`->`, `->>`)
- LIKE escaping
- Array and JSONB operations (`@>`, `?`, `?&`, `?|`, `ANY()`)

### 9. Supported OPTIMADE Filter Features

| Feature | Example | SQL Output |
|---------|---------|------------|
| **Comparisons** | `nelements > 3` | `n_elements > 3` |
| **String equality** | `chemical_formula = "SiO2"` | `formula = 'SiO2'` |
| **Fuzzy strings** | `chemical_formula CONTAINS "Si"` | `formula LIKE '%Si%'` |
| **Logic** | `nelements > 3 AND nelements < 10` | `(... AND ...)` |
| **Null checks** | `chemical_formula IS KNOWN` | `formula IS NOT NULL` |
| **Set membership** | `elements HAS "Si"` | `'Si' = ANY(elements)` |
| **Array length** | `elements LENGTH > 3` | `array_length(elements, 1) > 3` |
| **JSON extraction** | `_mpds_bandgap > 2.0` | `(attributes->>'bandgap')::float > 2.0` |

## MCP Server

The project includes an MCP (Model Context Protocol) server with tools for LLM-based querying:

```bash
python mcp_server.py
```

Tools available:
- **`translate_filter`** -- Translate an OPTIMADE filter string to SQL (uses core library)
- **`query_structures`** -- Query structures from an OPTIMADE API
- **`get_structure_by_id`** -- Retrieve a single structure by ID
- **`get_info`** -- Get server info
- **`element_groups`** -- List available element group names
- **`crystal_systems`** -- List crystal system names and space group ranges
- **`property_ranges`** -- List available named property ranges

Configure the target OPTIMADE API via the `OPTIMADE_BASE_URL` environment variable.

## Development

```bash
# Install from source with dev dependencies
pip install -e ".[dev]"

# Run tests
pytest tests/ -v

# Run linting
ruff check optimadetosql/

# Run type checking
mypy optimadetosql/
```

## License

&copy; Gumar Arutynian and Evgeny Blokhin

MIT License
