Metadata-Version: 2.4
Name: yesdb
Version: 0.6.1
Summary: A lightweight, relational database built from scratch in Python
Author-email: Azhar <azhar.takoy@strathmore.edu>
License: MIT
Project-URL: Homepage, https://github.com/AzharAhmed-bot/yes_db
Project-URL: Documentation, https://github.com/AzharAhmed-bot/yes_db#readme
Project-URL: Repository, https://github.com/AzharAhmed-bot/yes_db
Project-URL: Issues, https://github.com/AzharAhmed-bot/yes_db/issues
Keywords: database,sql,btree,embedded-database,baas,cloud
Classifier: License :: OSI Approved :: MIT License
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.8
Classifier: Programming Language :: Python :: 3.9
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Libraries :: Python Modules
Requires-Python: >=3.8
Description-Content-Type: text/markdown
License-File: LICENSE
Provides-Extra: cloud
Requires-Dist: requests>=2.28; extra == "cloud"
Provides-Extra: dev
Requires-Dist: pytest>=7.0; extra == "dev"
Requires-Dist: pytest-cov>=4.0; extra == "dev"
Requires-Dist: black>=23.0; extra == "dev"
Requires-Dist: mypy>=1.0; extra == "dev"
Requires-Dist: flake8>=6.0; extra == "dev"
Requires-Dist: requests>=2.28; extra == "dev"
Dynamic: license-file

# YesDB

**Beta** — A lightweight relational database built from scratch in Python, with SQL support, B-tree storage, and a cloud Backend-as-a-Service. Ready to use for real projects; still under active development, so expect the occasional rough edge.

## Installation

```bash
# Local only
pip install yesdb

# With cloud support
pip install yesdb[cloud]
```

## Quick Start

### Local Mode

```python
from yesdb import connect

db = connect("myapp.db")
db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
db.execute("INSERT INTO users VALUES (NULL, 'Alice', 30)")
results = db.execute("SELECT * FROM users")
for row in results:
    print(row)
db.close()
```

### Cloud Mode

YesDB Cloud lets you host your database on a remote server. Perfect for student projects that need a real backend.

#### 1. Sign up and create a database

```bash
yesdb signup
# Email: student@uni.edu
# Password: ********
# -> Account created! Logged in.

yesdb init myproject
# -> Created yesdb/ folder with schema.py
# -> Database "myproject" created on cloud.
```

#### 2. Define your schema

After running `yesdb init`, you'll have a `yesdb/schema.py` file in your project. Edit it:

```python
# yesdb/schema.py
from yesdb import Table, Column, Integer, Text, Real

users = Table("users", [
    Column("id", Integer, primary_key=True),
    Column("name", Text),
    Column("email", Text),
])

products = Table("products", [
    Column("id", Integer, primary_key=True),
    Column("name", Text),
    Column("price", Real),
])
```

#### 3. Push your schema to the cloud

```bash
yesdb push
# -> Connecting to myproject database...
# -> Table "users" created
# -> Table "products" created
# -> Schema synced. 2 tables pushed.
```

#### 4. Use in your code

```python
from yesdb import connect

db = connect("myproject")  # uses saved credentials automatically

db.execute("INSERT INTO users VALUES (NULL, 'Alice', 'alice@uni.edu')")
db.execute("INSERT INTO users VALUES (NULL, 'Bob', 'bob@uni.edu')")

rows = db.execute("SELECT * FROM users")
for row in rows:
    print(row)
```

Every response includes the database engine's internal logs (B-tree operations, SQL parsing, page allocations), so you can see exactly what's happening under the hood.

#### 5. Use with FastAPI or Flask

```python
# main.py
from fastapi import FastAPI
from yesdb import connect

app = FastAPI()
db = connect("myproject")

@app.get("/users")
def get_users():
    rows = db.execute("SELECT * FROM users")
    return {"users": [list(row) for row in rows]}

@app.post("/users")
def create_user(name: str, email: str):
    db.execute(f"INSERT INTO users VALUES (NULL, '{name}', '{email}')")
    return {"status": "created"}
```

### CLI Commands

```bash
yesdb signup              # Create an account
yesdb login               # Login to existing account
yesdb init <db_name>      # Initialize a project with a cloud database
yesdb push                # Push schema.py to the cloud
yesdb databases           # List your databases
yesdb shell <db_name>     # Interactive SQL shell against a cloud database
```

### Local CLI Shell

```bash
yesdb-local mydatabase.db
```

```sql
YesDB> CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);
YesDB> INSERT INTO users VALUES (NULL, 'Alice', 30);
YesDB> SELECT * FROM users;
```

```
.help          Show help
.tables        List all tables
.schema        Show table schemas
.exit          Exit shell
```

## Features

- **SQL Support**: CREATE/DROP TABLE, CREATE/DROP INDEX, SELECT, INSERT, UPDATE, DELETE, ALTER TABLE
- **Data Types**: INTEGER, TEXT, REAL, BLOB
- **Query Features**: WHERE, ORDER BY, LIMIT, OFFSET, DISTINCT, INNER/LEFT JOIN
- **B-Tree Storage**: Efficient indexing and data retrieval
- **Secondary Indexes**: `CREATE INDEX` accelerates equality lookups on non-primary-key columns
- **Transactions**: `BEGIN` / `COMMIT` / `ROLLBACK` for INSERT/UPDATE/DELETE
- **Foreign Keys**: `REFERENCES` constraints, enforced on INSERT/UPDATE, with RESTRICT on DELETE
- **No Dependencies**: Pure Python implementation (local mode)
- **Cloud BaaS**: Host your database remotely with a single command
- **Schema DSL**: Define tables in Python, push to cloud
- **Engine Logs**: See B-tree splits, page allocations, and SQL parsing in every response
- **Interactive Shell**: Built-in SQL shell (local and cloud)
- **Auto-increment**: PRIMARY KEY auto-increment support

## SQL Examples

```sql
-- Create table
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT, age INTEGER)

-- Foreign keys (enforced on INSERT/UPDATE; RESTRICT on DELETE of a referenced row)
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  total REAL
)

-- Insert data
INSERT INTO users VALUES (NULL, 'Alice', 'alice@example.com', 30)
INSERT INTO users VALUES (NULL, 'Bob', 'bob@example.com', 25)

-- Query with conditions
SELECT * FROM users WHERE age > 25
SELECT name, email FROM users WHERE name = 'Alice'

-- Order and limit
SELECT * FROM users ORDER BY age DESC
SELECT * FROM users LIMIT 10 OFFSET 5

-- Joins (INNER by default, or LEFT [OUTER])
SELECT users.name, orders.total FROM orders
  JOIN users ON orders.user_id = users.id
SELECT users.name, orders.total FROM users
  LEFT JOIN orders ON users.id = orders.user_id

-- Update and delete
UPDATE users SET age = 31 WHERE name = 'Alice'
DELETE FROM users WHERE age < 18

-- Alter table
ALTER TABLE users ADD COLUMN country TEXT

-- Secondary indexes (speeds up equality WHERE lookups)
CREATE INDEX idx_users_email ON users (email)
DROP INDEX idx_users_email

-- Drop table
DROP TABLE users

-- Transactions (INSERT/UPDATE/DELETE only — DDL isn't allowed mid-transaction)
BEGIN
INSERT INTO users VALUES (NULL, 'Dana', 'dana@example.com', 28)
ROLLBACK  -- or COMMIT
```

## How Cloud Mode Works

```
Your machine                   YesDB Cloud Server
────────────────               ──────────────────
yesdb CLI (signup/login/push)   ┌─────────────────┐
   <-> HTTPS                    │ Tailscale Funnel │
yesdb SDK (connect/execute)     │  └─ FastAPI      │
                                │     ├─ auth      │
                                │     └─ data/     │
                                │       ├─ user1/  │
                                │       │  └─ *.db │
                                │       └─ user2/  │
                                │          └─ *.db │
                                └─────────────────┘
```

The server can run on any machine reachable over Tailscale — a cloud VM, a
home desktop, whatever's available. Tailscale Funnel handles public HTTPS
without opening router ports or managing a domain/certificate. See
`deploy/setup.sh`.

- Each user gets their own isolated account with multiple databases
- All traffic is encrypted over HTTPS
- Authentication via API key (generated at signup, saved locally)
- Database engine logs are returned with every query for full transparency

## Security

### Local Mode
- Path validation (blocks system file access)
- Resource limits (SQL length, record size)
- Input validation (table/column names)

### Cloud Mode
- HTTPS encryption (TLS via Let's Encrypt)
- API key authentication (SHA-256 hashed, never stored in plaintext)
- Password hashing (bcrypt)
- Per-user data isolation
- Request size limits

## Development

### Install from Source

```bash
git clone https://github.com/AzharAhmed-bot/yes_db.git
cd yes_db
pip install -e ".[cloud]"
```

### Run Tests

```bash
pip install pytest
pytest
```

## Requirements

- Python 3.8+
- No external dependencies (local mode)
- `requests` (cloud mode, installed with `pip install yesdb[cloud]`)

## License

MIT License - see [LICENSE](LICENSE) file

## Links

- **PyPI**: https://pypi.org/project/yesdb/
- **GitHub**: https://github.com/AzharAhmed-bot/yes_db
- **Issues**: https://github.com/AzharAhmed-bot/yes_db/issues

## Version

Current version: **0.6.1** (Beta)

### Changelog

#### v0.6.1
- **Fix (critical)**: `WHERE ... AND/OR ...` silently matched every row instead of applying the condition — affected SELECT, UPDATE, and DELETE alike (e.g. `DELETE FROM t WHERE a = 1 AND b = 2` deleted the entire table). `_evaluate_where` now recursively evaluates AND/OR instead of only handling a single comparison.
- **Fix (critical)**: Once a table's B-tree grew past one page, INSERT/UPDATE/DELETE could misroute a key that exactly matched an internal split point — silently failing UPDATE/DELETE (reported success, changed nothing) and allowing duplicate PRIMARY KEY rows on INSERT. Internal-node routing in `chidb/btree.py` now matches `search()`'s (already-correct) equality-aware logic.
- **Fix**: `INSERT` of a `REAL` value was silently truncated to an integer (no `FLOAT` opcode existed in the bytecode VM). Added one; `10.5` no longer becomes `10`.
- **Fix**: Negative number literals (`-5`, `-3.25`) couldn't be parsed at all, in `INSERT`, `UPDATE ... SET`, or `WHERE`.
- **Fix**: `ORDER BY` on a column not included in the `SELECT` list sorted by the wrong values entirely (it resolved the column's position against the full schema but applied it to the already-projected row). `SELECT name FROM t ORDER BY age` now actually orders by `age`.
- **Improved**: The DBM bytecode VM's `DELETE` opcode was a no-op stub (didn't call `btree.delete()`) — fixed, and documented that it and several other codegen paths (`generate_select`/`generate_update`/`generate_delete`/`generate_create_table`) aren't wired into real query execution today (every statement except INSERT is handled directly in `api.py`), so nobody mistakes them for working code.

#### v0.6.0
- **New**: Foreign key constraints — `column TYPE REFERENCES table(column)` in `CREATE TABLE` (and `ALTER TABLE ADD COLUMN`). Enforced on `INSERT` and `UPDATE` (a non-NULL value must match an existing row in the referenced table/column); `DELETE` from a referenced row is rejected (RESTRICT) while any other table still references it. `CREATE TABLE` validates the referenced table/column exist up front, rather than failing only on first insert.

#### v0.5.0
- **New**: `BEGIN` / `COMMIT` / `ROLLBACK` transactions for INSERT/UPDATE/DELETE. Implemented as an in-memory snapshot-and-restore of the pager's page cache (where writes already live until `flush()`), so ROLLBACK is a pure in-memory operation — no partial writes ever reach disk. DDL (CREATE/DROP TABLE, ALTER TABLE, CREATE/DROP INDEX) is not allowed inside an active transaction, since it flushes to disk immediately and an in-memory rollback couldn't undo that. Closing a database with an active transaction rolls it back automatically.

#### v0.4.0
- **New**: `CREATE INDEX name ON table (column)` / `DROP INDEX name` — secondary indexes that accelerate `column = value` WHERE lookups from a full table scan down to a direct lookup. Implemented as an in-memory index (the hand-rolled B-tree only supports integer keys), automatically rebuilt when a database is reopened and kept in sync across INSERT/UPDATE/DELETE. `DROP TABLE` cascades to drop any indexes defined on it.

#### v0.3.0
- **New**: `JOIN` support — `INNER JOIN` (default) and `LEFT [OUTER] JOIN`, chainable across multiple tables, with `table.column` qualified references in `SELECT`, `ON`, `WHERE`, and `ORDER BY`. Unqualified column names resolve automatically when unambiguous across the joined tables, and raise a clear error when they're not. Not yet supported together with `GROUP BY`/aggregate functions.

#### v0.2.5
- **New**: A real first-run welcome — bare `yesdb` and `yesdb signup` now show a block-letter YESDB logo alongside the version/Beta tagline, plus a "New here? Get started" quickstart (signup → init → push, with a pointer to local mode) instead of just a bare help dump.

#### v0.2.4
- **Changed**: Dropped "educational" framing everywhere (PyPI classifiers/keywords, package docstring, README, SECURITY.md) in favor of a clear Beta positioning — YesDB is usable for real projects now, not just a teaching toy.
- **New**: Proper intro banners — `yesdb signup`, `yesdb-local <db>`, and `yesdb shell <db>` now show the product name, version, and Beta status on first touch. `signup` also points you to the next step (`yesdb init`).
- **Fix**: The Cloud server's `/docs` page showed a hardcoded, never-updated `0.1.0` API version — now reads the real package version.
- Removed a stale, unreferenced duplicate README (`readME.md`) left over from a very early pre-rename version of the project.

#### v0.2.3
- **Fix**: `SELECT ... WHERE ...` (without ORDER BY/LIMIT/DISTINCT/GROUP BY) silently returned wrong data — the WHERE clause never actually resolved column values, and column projection (`SELECT col FROM t`) returned full rows instead of the requested columns. Both now go through the same correct execution path as every other SELECT variant.
- **Fix**: Error messages were being needlessly hidden in production mode — `sanitize_error_message()` collapsed *every* error down to generic strings like "Invalid input" or "An error occurred," including safe, expected ones (table already exists, unknown table, SQL syntax errors). Added a `QueryError` class for errors that only ever echo back names/tokens the caller already supplied — these now show their real message instead of being sanitized away.
- **Improved**: `yesdb push` output redesigned — one line per table with a real status (created / already exists, skipped / failed: reason) and an accurate summary, instead of a misleading "Table 'X' created" claim for every table regardless of outcome. Raw engine logs moved behind a new `--verbose`/`-v` flag.
- **Fix**: logs captured before a statement failed during `push` were silently dropped instead of reaching `--verbose` output.

#### v0.2.2
- **Fix**: `yesdb push` claimed every table in your local `schema.py` was "created" even when the server rejected some of them (e.g. re-pushing after a table already exists). The real per-table result was already shown via engine logs; the misleading summary line is now removed.

#### v0.2.1
- **Fix**: `yesdb signup`/`yesdb login` default server URL pointed at a dead Azure endpoint (no longer hosted). Now points at the live, self-hosted Cloud instance.
- **Fix**: Package metadata (`pyproject.toml`, `setup.cfg`) and README referenced the wrong GitHub repo name (`yesdb` instead of `yes_db`), producing broken links.

#### v0.2.0 (Beta)
- **New**: `GROUP BY` with aggregate functions — `COUNT`, `COUNT(*)`, `SUM`, `AVG`, `MIN`, `MAX`, combinable with `WHERE`, `ORDER BY`, and `LIMIT`/`OFFSET`.
- **Fix**: Cloud server connection pool had a race condition — concurrent requests could open duplicate connections to the same database, or run unsynchronized queries against it, risking corruption. Access per database is now serialized.
- **New**: Query execution timeout on the cloud server (default 10s, configurable via `YESDB_QUERY_TIMEOUT_SECONDS`) so a slow query can't block every other user of that database indefinitely.
- **Status**: Moved from Alpha to Beta. Local Mode is beta-ready; Cloud Mode is deployed and live (self-hosted, see Cloud Mode section).

#### v0.1.5 (bug fixes)
- **Fix**: `from yesdb import connect` now works correctly. A `yesdb` compatibility package is included so the import matches the PyPI package name.
- **Fix**: `Table.to_sql()` now emits the `PRIMARY KEY` constraint in the generated `CREATE TABLE` SQL, so auto-increment works as expected when inserting `NULL` into a primary key column.

---

**Note**: YesDB is in active beta. It's built from scratch in Python and usable today for real projects and prototypes — you'll also see exactly how a database works internally along the way. Report issues on GitHub; for workloads with strict production/security requirements, an established database (PostgreSQL, MySQL, SQLite) is still the safer choice for now.
