140 lines
4.2 KiB
Markdown
140 lines
4.2 KiB
Markdown
# MiniSQL
|
|
|
|
## Project Structure
|
|
- `client` - REPL client for connecting to the server
|
|
- `server` - MiniSQL server
|
|
- `minisql` - SQL AST interpreter
|
|
- `parser` - SQL parser
|
|
- `proto` - Implementation of subset of PostgreSQL protocol
|
|
|
|
## Requirements
|
|
- Rust + Cargo with version **>= 1.74.0** (using `impl` in return statements)
|
|
- Optional: `psql` cli for testing the server
|
|
|
|
# Usage
|
|
|
|
## Running the server
|
|
```bash
|
|
cargo run -p server -- [OPTIONS] --folder <FOLDER>
|
|
```
|
|
```
|
|
Options:
|
|
-a, --address <ADDRESS> IP address for the server to listen on [default: 127.0.0.1]
|
|
-p, --port <PORT> Port for the server to listen on [default: 5432]
|
|
-f, --folder <FOLDER> Path to the folder for database data
|
|
-t, --throttle <DELAY> Delay between rows in milliseconds
|
|
-h, --help Print help
|
|
```
|
|
|
|
This will start the server listening on `<ADDRESS>:<PORT>` and load the state from `<FOLDER>`.
|
|
If the `<FOLDER>` does not exist, it will be created.
|
|
|
|
### Demo Database
|
|
Commands that set up a database with demo data are available in `demo-1.sql`, `demo-2.sql`, and `demo-3.sql`.
|
|
|
|
These files showcase the following things:
|
|
- `demo-1.sql`: small tables that handle Unicode and make use of the Optional type
|
|
- `demo-2.sql`: a bigger (1000 rows) table with realistic data
|
|
- `demo-3.sql`: a big (10,000 rows) table with simple data (was easier to generate)
|
|
|
|
See comments in these files for details.
|
|
|
|
## Running the client
|
|
```bash
|
|
cargo run -p client -- [OPTIONS]
|
|
```
|
|
```
|
|
Options:
|
|
-p, --port <PORT> Port number of the server [default: 5432]
|
|
--host <HOST> Host name or IP address of the server [default: 127.0.0.1]
|
|
--username <USERNAME> User name [default: "minisql user"]
|
|
-h, --help Print help
|
|
```
|
|
|
|
This will start a REPL client that connects to the server at `<HOST>:<PORT>` and authorizes with `<USERNAME>`.
|
|
SQL queries can be entered line by line. The client will print the result of each query.
|
|
|
|
To exit the REPL client, enter `exit` or `quit`.
|
|
|
|
# Features
|
|
- SQL must be on single line, in **UPPERCASE**, as it should be. The `;` at EOL is optional.
|
|
- Supported operations: `CREATE TABLE`, `CREATE INDEX`, `SELECT`, `INSERT`, `DELETE`
|
|
- Supported data types: `UUID`, `STRING`, `INT`, `NUMBER`
|
|
- Supported subset of PostgreSQL protocol, without authentication and simple query flow
|
|
|
|
## Operations
|
|
|
|
### `CREATE TABLE`
|
|
- Every table requires one column with type `UUID` as `PRIMARY KEY`
|
|
|
|
```sql
|
|
CREATE TABLE <TableName> (<ColumnName> <DataType>, ...);
|
|
```
|
|
|
|
With the demo database, this can be used to create a new table:
|
|
```sql
|
|
CREATE TABLE games (id UUID PRIMARY KEY, name STRING, year INT, price NUMBER);
|
|
```
|
|
|
|
### `CREATE INDEX`
|
|
- Indexing is not supported for the `NUMBER` data type
|
|
|
|
```sql
|
|
CREATE INDEX <IndexName> ON <TableName> (<ColumnName>);
|
|
```
|
|
|
|
With the demo database, this can be used to create a new index:
|
|
```sql
|
|
CREATE INDEX CarsYear ON cars (year);
|
|
```
|
|
|
|
### `SELECT`
|
|
- `WHERE` clause is optional and only equality is supported
|
|
- All columns can be selected with `*`
|
|
|
|
```sql
|
|
SELECT <ColumnName>, ... FROM <TableName> [WHERE <ColumnName> = <Value>];
|
|
```
|
|
|
|
With the demo database, this can be used to select all cars with year 2001:
|
|
```sql
|
|
SELECT * FROM cars WHERE year = 2001;
|
|
```
|
|
|
|
### `INSERT`
|
|
|
|
```sql
|
|
INSERT INTO <TableName> (<ColumnName>, ...) VALUES (<Value>, ...);
|
|
```
|
|
|
|
With the demo database, this can be used to insert a new user:
|
|
```sql
|
|
INSERT INTO users (id, name, surname, email) VALUES (u12345, "John", "Doe", "johhny.trucker@seznam.cz");
|
|
```
|
|
|
|
|
|
### `DELETE`
|
|
- `WHERE` clause is optional and only equality is supported
|
|
|
|
```sql
|
|
DELETE FROM <TableName> [WHERE <ColumnName> = <Value>];
|
|
```
|
|
|
|
With the demo database, this can be used to delete all users with name Christina:
|
|
```sql
|
|
DELETE FROM users WHERE name = "Christina";
|
|
```
|
|
|
|
## Data Types
|
|
- `UUID` - special integer prefixed with `u`, e.g. `u12345`
|
|
- `STRING` - string enclosed in double quotes, e.g. `"Hello World"`
|
|
- `INT` - integer, e.g. `12345`
|
|
- `NUMBER` - floating point number, e.g. `123.45`
|
|
- `Option(Something)` - optional type, e.g. `Some("james.stuart@gmail.com")` or `None`
|
|
|
|
## Testing with `psql`
|
|
Thanks to the subset of PostgreSQL protocol implemented in `proto`, the server can be tested with `psql`,
|
|
which offers REPL client similar to the `client` crate:
|
|
```bash
|
|
psql -h <HOST> -p <PORT>
|
|
```
|