Metadata-Version: 2.4
Name: swan-aisql
Version: 0.1.1
Summary: SWAN AI-SQL: semantic SQL (ai_filter, ai_classify, ai_score, ai_complete, ai_agg, ai_embed) as a DuckDB extension with the two-currency optimizer
Author: Fuheng Zhao
License-Expression: MIT
Project-URL: Homepage, https://github.com/ZhaoFuheng/SWAN-AISQL
Project-URL: Repository, https://github.com/ZhaoFuheng/SWAN-AISQL
Project-URL: Benchmark, https://github.com/ZhaoFuheng/SWANBench
Keywords: duckdb,llm,semantic-sql,ai-sql,query-optimization
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: Intended Audience :: Science/Research
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: SQL
Classifier: Topic :: Database
Requires-Python: >=3.11
Description-Content-Type: text/markdown
Requires-Dist: duckdb==2.0.0.dev2610011535
Provides-Extra: embed
Requires-Dist: sentence-transformers<4,>=3.0; extra == "embed"
Requires-Dist: torch; extra == "embed"
Requires-Dist: pillow>=10; extra == "embed"
Requires-Dist: transformers<4.50,>=4.40; (sys_platform == "darwin" and platform_machine == "x86_64") and extra == "embed"
Requires-Dist: numpy<2; (sys_platform == "darwin" and platform_machine == "x86_64") and extra == "embed"

# swan-aisql

SWAN AI-SQL as a pip package: DuckDB with semantic SQL functions (`ai_filter`, `ai_classify`, `ai_score`,
`ai_complete`, `ai_agg`, `ai_embed`, `ai_image`) and the SWAN optimizer, which folds duplicate prompts,
orders predicates by learned selectivity, pushes `LIMIT` into the model calls and keeps a fixed number of
requests in flight. Engine, design and benchmarks: <https://github.com/ZhaoFuheng/SWAN-AISQL>.

```sh
pip install "swan-aisql[embed]"      # the extension, the pinned duckdb, and the local embeddings server
pip install swan-aisql               # without the embeddings server (sentence-transformers + torch)
```

```python
import os, swan_aisql

con = swan_aisql.connect()        # OPENAI_API_KEY from the environment -> OpenAI directly, model gpt-5.6-luna
con.sql("CREATE TABLE reviews AS SELECT * FROM 'reviews.parquet'")
con.sql("""
    SELECT critic, review
    FROM reviews
    WHERE ai_filter('Is this movie review clearly positive? Review: ' || review)
    LIMIT 10
""").show()
con.sql("SELECT llm_calls, cache_hits, cost_usd FROM ai_usage()").show()
```

[examples/swan2_formula_1.ipynb](examples/swan2_formula_1.ipynb) walks through this on a SWAN 2.0 benchmark
database, with the model calls each question costs. `connect()` returns a plain `duckdb.DuckDBPyConnection`; `swan_aisql.load(con)` adds the extension to a
connection you opened yourself (with `allow_unsigned_extensions`).

**Endpoint.** Any OpenAI-compatible chat endpoint: `connect(endpoint="https://api.openai.com")`, a litellm
proxy, or the repository's caching proxy (`endpoint="http://localhost:4001"`). With no `endpoint`, the
extension uses `AI_PROXY_URL` from the environment and, failing that, OpenAI when `OPENAI_API_KEY` is set.
`https://` endpoints need the TLS build of the extension, which the published wheels are. Requests carry
`temperature: 0`; a model that rejects the parameter (the gpt-5 reasoning models answer HTTP 400 to it)
gets the request again without it, and the process leaves it out from then on.

**Settings.** `model`, `api_key`, `reasoning_effort` and `price_input` / `price_output` / `price_cached` (USD per 1M
tokens, so `ai_usage().cost_usd` prices a direct endpoint's calls) are keywords; every other `SET ai_*` option passes
through, e.g. `connect(ai_concurrency=50, ai_limit=False)`. The full list is `SELECT * FROM
duckdb_settings() WHERE name LIKE 'ai_%'` and the repository README.

**Embeddings.** The learned predicate ordering embeds prompts and inputs through a local server: MiniLM for
text and CLIP for `ai_image` predicates, the setup every published number used. With the `embed` extra
installed, `connect()` starts it when nothing answers on the embeddings endpoint (localhost:4002 unless
`ai_embed_endpoint` or `AI_EMBED_URL` says otherwise) and waits for it to listen; the first start downloads
the two models (about 1.2 GB into `~/.cache/huggingface`), later starts take a few seconds. The server
outlives the Python process so the next one finds it running; `swan_aisql.stop_embed_server()` ends it,
`swan-aisql-embed [port]` runs it by hand, and `connect(embed_server=False)` never starts one. Any
OpenAI-compatible embeddings endpoint works instead, text only:
`connect(ai_embed_endpoint="https://api.openai.com", ai_embed_model="text-embedding-3-small")`. With no
server at all the engine notices on the first request, orders predicates by prompt cost alone from then on,
and every query still runs.

**Version coupling.** A DuckDB extension loads only into the exact DuckDB build it was compiled for, so each
swan-aisql release pins one `duckdb` version (this release: the version in the package metadata, a 2.0
pre-release). `connect()` reports a mismatch with the `pip install 'duckdb==...'` line that fixes it.

**Building a wheel yourself** (from the repository checkout, after `make release`):

```sh
pip install build wheel --pre "duckdb==$(sed -n 's/^dependencies = \["duckdb==\([^"]*\)"\]/\1/p' python/pyproject.toml)"
python/build_wheel.sh                        # packs build/release/extension/aisql/aisql.duckdb_extension
pip install python/dist/swan_aisql-*.whl
```

The binary must come from the DuckDB commit the pinned `duckdb` wheel was built from (the `duckdb/` submodule
pin); the script checks the binary's footer against the installed engine and refuses any other build.

**Releasing** (maintainers). `.github/workflows/PythonWheels.yml` builds the extension for every platform
through the DuckDB distribution pipeline, packs one wheel per platform, smoke-tests each against the pinned
`duckdb` wheel on its own runner, and on a `v*` tag publishes them: a tag ending in `-test` goes to TestPyPI,
any other to PyPI. Publishing uses PyPI trusted publishing (no stored token): register the project
`swan-aisql` with owner `ZhaoFuheng`, repository `SWAN-AISQL`, workflow `PythonWheels.yml` and environment
`pypi` on pypi.org (`testpypi` on test.pypi.org), and create those two environments in the repository
settings. Bumping DuckDB: pin the `duckdb/` submodule to the commit the target `duckdb` wheel reports as
`source_id`, set the same commit in both workflows and the matching `duckdb==` version in `pyproject.toml`.
