sqawk 0.8.2

# Sqawk

[![Crates.io](https://img.shields.io/crates/v/sqawk.svg)](https://crates.io/crates/sqawk)
[![Docs.rs](https://docs.rs/sqawk/badge.svg)](https://docs.rs/sqawk)
[![MIT licensed](https://img.shields.io/crates/l/sqawk.svg)](./LICENSE)

Sqawk is an SQL-based command-line tool for processing delimiter-separated files (CSV, TSV, etc.), inspired by the classic `awk` command. It loads data into in-memory tables, executes SQL queries, and optionally writes results back to files.

## Features

- **SQL Query Engine** - SELECT, INSERT, UPDATE, DELETE with WHERE, ORDER BY, GROUP BY, HAVING, LIMIT/OFFSET
- **Joins** - INNER, LEFT, RIGHT, FULL OUTER, and CROSS joins with ON conditions
- **Aggregates** - COUNT, SUM, AVG, MIN, MAX with GROUP BY support
- **Functions** - String (UPPER, LOWER, SUBSTR, REPLACE, etc.), math (ABS, ROUND, etc.), date/time
- **Subqueries** - Scalar, `IN (SELECT ...)`, and `EXISTS`, including correlated
- **Set Operations** - UNION, UNION ALL, INTERSECT, EXCEPT
- **Window Functions** - ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and aggregates with `OVER (PARTITION BY ... ORDER BY ...)`
- **DDL** - CREATE TABLE, CREATE TABLE AS SELECT, DROP, ALTER TABLE ADD COLUMN, TRUNCATE
- **Expressions** - CASE, CAST, COALESCE, NULLIF, BETWEEN, IN, LIKE/ILIKE, `||`, arithmetic
- **File Formats** - CSV, TSV, and custom delimiters; headerless files via `--tabledef`
- **Pipelines** - Reads standard input with a `-` file operand
- **Safe by Default** - Files unchanged unless `--write` flag is specified
- **Interactive REPL** - Explore data interactively with `-i` flag

## Installation

```sh
cargo install sqawk
```

This installs two binaries: `sqawk` and [`tsq`](#tsq---test-data-generator).
Requires Rust 1.88 or newer.

## Quick Examples

```sh
# Query a CSV file
sqawk -s "SELECT name, salary FROM employees WHERE department = 'Engineering'" employees.csv

# Join two files
sqawk -s "SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id" users.csv orders.csv

# Aggregate data
sqawk -s "SELECT department, AVG(salary) FROM employees GROUP BY department" employees.csv

# Modify and save
sqawk -s "UPDATE data SET status = 'archived' WHERE year < 2020" data.csv --write

# Read standard input as the table "stdin"
cat employees.csv | sqawk -s "SELECT name FROM stdin WHERE salary > 70000" -
```

## `tsq` - test data generator

`cargo install sqawk` also installs `tsq`, which generates deterministic
multi-table CSV data plus a corpus of SQL queries for exercising sqawk.

```sh
tsq --seed 42 --rows 1000 --output-dir /tmp/sqawk-test
sqawk -s "SELECT * FROM customers LIMIT 10" /tmp/sqawk-test/data/customers.csv
```

It writes `data/` (customers, products, orders, order_items, reviews, with
realistic foreign-key relationships), `queries/` (numbered `.sql` files
covering selects, joins, aggregates, subqueries and window functions),
`verify/run_verification.sh`, and a `metadata.json` recording the seed and row
counts. The same seed always produces the same data.

| Option | Meaning |
| --- | --- |
| `-s`, `--seed` | Seed for reproducible generation; a random one is printed if omitted |
| `-r`, `--rows` | Base customer row count; other tables scale proportionally (default 100000) |
| `-o`, `--output-dir` | Where to write the generated tree (required) |
| `-v`, `--verbose` | Show generation progress |

## Documentation

- [User Guide](doc/user_guide.md) - Installation, CLI options, and examples
- [SQL Reference](doc/sql_reference.md) - Complete SQL syntax and functions
- [Database Architecture](doc/database.md) - Technical internals (for contributors)

## License

MIT License - see [LICENSE](LICENSE)

## Contributing

Contributions welcome. Any contribution submitted for inclusion shall be licensed as MIT.