diff options
Diffstat (limited to 'query')
| -rw-r--r-- | query/README.md | 51 | ||||
| -rw-r--r-- | query/batch.md | 63 | ||||
| -rw-r--r-- | query/count.md | 48 | ||||
| -rw-r--r-- | query/delete.md | 46 | ||||
| -rw-r--r-- | query/filters.md | 151 | ||||
| -rw-r--r-- | query/insert.md | 50 | ||||
| -rw-r--r-- | query/select.md | 67 | ||||
| -rw-r--r-- | query/update.md | 54 |
8 files changed, 530 insertions, 0 deletions
diff --git a/query/README.md b/query/README.md new file mode 100644 index 0000000..54e99af --- /dev/null +++ b/query/README.md @@ -0,0 +1,51 @@ +# Query + +[back to index](../README.md) + +The `/query` endpoint is where all database operations happen. One endpoint, one JSON DERULO format, multiple actions. Pretty much the heart of jsonderulo <3 and whats the minimum requirement for JSON DERULO v1 compliance (in addition to authentication). + +## Pages + +### Actions +- [Select](select.md) read rows from tables +- [Insert](insert.md) add new rows +- [Update](update.md) modify existing rows +- [Delete](delete.md) remove rows +- [Count](count.md) count matching rows + +### Features +- [Filters](filters.md) where clauses, structured filters, joins, ordering +- [Batch](batch.md) run multiple queries of the same type in one request + +## How it works + +`POST /query` with a JSONDERULO body. The `action` field tells the server what you want to do. The `table` field tells it which table. + +```json +{ + "action": "select", + "table": "items" +} +``` + +Thats the simplest possible query. Selects all columns from the items table with the default limit applied. + +Every query should run inside a transaction. The server should set `@current_user_id` and `@request_id` as SQL session variables before running your query so triggers and stuff can reference them if needed in your schema. + +## Auth + +Bearer token required. The server should ALWAYS check your permissions for the target table (and any joined tables) before doing anything. See [permissions](../permissions/README.md) for how that works. + +## Common Response Fields + +Every response has `success` (boolean). Beyond that: + +| Action | Extra fields | +|--------|----------------------------------------------| +| select | `data` (array of rows), optionally `warning` | +| insert | `data` (last insert id), `rows_affected` | +| update | `rows_affected`, if applicable `warning` | +| delete | `rows_affected`, if applicable `warning` | +| count | `data` (the count as a number) | + +The `warning` field should appear when json derulo striped for you, example : your limit being capped to a maximum or blocked columns being stripped away by json derulo stripping. its not an error just a hey bbgurl please comply to what you're allowed to do and to json derulos orders! diff --git a/query/batch.md b/query/batch.md new file mode 100644 index 0000000..471adf5 --- /dev/null +++ b/query/batch.md @@ -0,0 +1,63 @@ +# Batch Queries + +[back to query](README.md) /// [home](../README.md) + +Run multiple queries of the same type in one request. All should share the same `action` and `table`. + +## Request + +```json +{ + "action": "insert", + "table": "items", + "queries": [ + { "data": { "name": "Widget A", "price": 10 } }, + { "data": { "name": "Widget B", "price": 20 } }, + { "data": { "name": "Widget C", "price": 30 } } + ], + "rollback_on_error": true +} +``` + +## Fields + +| Field | Type | Required | Notes | +|---------------------|---------|----------|-------------------------------------------------------------| +| `action` | string | yes | shared across all sub queries | +| `table` | string | yes | shared across all sub queries | +| `queries` | array | yes | array of individual query bodies (without action and table) | +| `rollback_on_error` | boolean | no | default false. if true, any failure rolls back everything | + +Each item in `queries` can have the same fields as a normal single query (columns, data, where, filter, limit, offset, order_by) just without repeating action and table. + +## Permission + +Batch operations should require the `allow_batch_operations` permission for your power level (on `jde_groups`). Without it you get a 403. + +Someday better granularity of this permission might be introduced but for now this should suffice. + +## How it should work + +- everything runs in a single transaction +- for INSERT batches the server should ideally be smart about it and combine them into one multi row INSERT statement instead of doing N individual inserts (way faster) +- for SELECT and COUNT each sub query runs individually within the transaction +- if `rollback_on_error` is true and any sub query fails the entire batch should be rolled back and nothing committed +- if `rollback_on_error` is false (default) failures are reported but successful queries still commit + +## Response + +Success looks the same as the individual action responses but with a `results` array for partial success/failure scenarios. + +When `rollback_on_error` is true and something fails: + +```json +{ + "success": false, + "error": "Batch operation failed, all changes rolled back [request_id: abc123]", + "results": [ + { "success": true, "rows_affected": 1 }, + { "success": false, "error": "duplicate key" }, + { "success": true, "rows_affected": 1 } + ] +} +``` diff --git a/query/count.md b/query/count.md new file mode 100644 index 0000000..bb4e14d --- /dev/null +++ b/query/count.md @@ -0,0 +1,48 @@ +# Count + +[back to query](README.md) /// [home](../README.md) + +Count rows matching a filter. Like select but just gives you the number in data field. + +## Request + +```json +{ + "action": "count", + "table": "items", + "filter": { + "column": "status", + "op": "=", + "value": "active" + } +} +``` + +## Fields + +| Field | Type | Required | Notes | +|----------|--------|----------|-----------------------------| +| `action` | string | yes | must be `"count"` | +| `table` | string | yes | target table | +| `where` | object | no | simple key value filter | +| `filter` | object | no | structured filter | +| `joins` | array | no | join other tables if needed | + +No limit or offset for count, it always counts everything that matches. + +## Response + +```json +{ + "success": true, + "data": 42 +} +``` + +`data` is just the count as a number. + +## Behaviors + +- should support joins so you can count rows with conditions on related tables +- ownership scoping should apply: if you have `rwo` or `rwg` the count should only include rows you have access to +- you need at least read permission on the table diff --git a/query/delete.md b/query/delete.md new file mode 100644 index 0000000..db55292 --- /dev/null +++ b/query/delete.md @@ -0,0 +1,46 @@ +# Delete + +[back to query](README.md) /// [home](../README.md) + +Remove rows from a table. + +## Request + +```json +{ + "action": "delete", + "table": "items", + "filter": { + "column": "id", + "op": "=", + "value": 42 + } +} +``` + +## Fields + +| Field | Type | Required | Notes | +|----------|--------|----------|-------------------------| +| `action` | string | yes | must be `"delete"` | +| `table` | string | yes | target table | +| `where` | object | yes* | simple key value filter | +| `filter` | object | yes* | structured filter | + +*at least one of `where` or `filter` is required. no unfiltered deletes lol. + +## Response + +```json +{ + "success": true, + "rows_affected": 1 +} +``` + +## Behaviors + +- WHERE clause is **mandatory**. same as update, no blanket deletes allowed. jsonderulo doesnt want you nuking entire tables on accident +- ownership scoping should be applied based on your permission level +- limit should be applied and capped to your max +- you need write permission on the table (`rw`, `rwa`, `rwo`, `rwg`) diff --git a/query/filters.md b/query/filters.md new file mode 100644 index 0000000..13a929b --- /dev/null +++ b/query/filters.md @@ -0,0 +1,151 @@ +# Filters + +[back to query](README.md) /// [home](../README.md) + +How to filter, join and sort your query results. + +## Simple Where + +The `where` field is the lazy cunt way. Just key value pairs, all treated as `= value` with AND between them. + +```json +{ + "action": "select", + "table": "items", + "where": { + "status": "active", + "category_id": 3 + } +} +``` + +This becomes `WHERE status = 'active' AND category_id = 3`. + +## Structured Filter + +The `filter` field gives you full crazy schizo control. Supports operators, nesting, AND/OR/NOT. + +### Single condition + +```json +{ "column": "price", "op": ">", "value": 10 } +``` + +### AND (all must match) + +```json +{ + "and": [ + { "column": "status", "op": "=", "value": "active" }, + { "column": "price", "op": ">=", "value": 5 } + ] +} +``` + +### OR (any can match) + +```json +{ + "or": [ + { "column": "status", "op": "=", "value": "draft" }, + { "column": "status", "op": "=", "value": "active" } + ] +} +``` + +### NOT + +```json +{ + "not": { "column": "status", "op": "=", "value": "deleted" } +} +``` + +### Nesting + +You can nest these however you want: + +```json +{ + "and": [ + { "column": "price", "op": ">", "value": 0 }, + { + "or": [ + { "column": "status", "op": "=", "value": "active" }, + { "column": "is_featured", "op": "=", "value": true } + ] + } + ] +} +``` + +### Supported Operators + +| Operator | What it does | +|---------------|-------------------------------------------------| +| `=` | equals | +| `!=` | not equals | +| `>` | greater than | +| `>=` | greater than or equal | +| `<` | less than | +| `<=` | less than or equal | +| `like` | SQL LIKE pattern matching | +| `not_like` | inverse of LIKE | +| `in` | value is in a list (pass an array as value) | +| `not_in` | value is not in a list | +| `is_null` | column is NULL (no value needed) | +| `is_not_null` | column is not NULL (no value needed) | +| `between` | between two values (pass `[min, max]` as value) | + +### Maximum Conditions + +Theres a per power level limit on how many conditions you can have in one query (`max_where_conditions` on the group). If you exceed it the server should give you a 400. this is to prevent people from going insane with query complexity. + +## Joins + +Add `joins` to your query to join other tables. Works with select and count. + +```json +{ + "action": "select", + "table": "items", + "columns": ["items.name", "categories.name"], + "joins": [ + { + "table": "categories", + "on": "items.category_id = categories.id", + "type": "LEFT" + } + ] +} +``` + +| Field | Type | Required | Default | Notes | +|---------|--------|----------|-----------|------------------------------------| +| `table` | string | yes | | table to join | +| `on` | string | yes | | join condition | +| `type` | string | no | `"INNER"` | `INNER`, `LEFT`, `RIGHT` | + +type may also be full but keep in mind that this is not support on MySQL/MariaDB backed servers so I advice against using it. + +You need read permission on every table you join. If you dont have access to a joined table the whole query should get rejected by jsonderulo and he will spank you. + +## Order By + +Sort your results: + +```json +{ + "order_by": [ + { "column": "created_at", "direction": "DESC" }, + { "column": "name", "direction": "ASC" } + ] +} +``` + +| Field | Type | Required | Default | +|-------------|--------|----------|---------| +| `column` | string | yes | | +| `direction` | string | no | `"ASC"` | + +You can sort by multiple columns. They apply in order. diff --git a/query/insert.md b/query/insert.md new file mode 100644 index 0000000..899f804 --- /dev/null +++ b/query/insert.md @@ -0,0 +1,50 @@ +# Insert + +[back to query](README.md) /// [home](../README.md) + +Add a new row to a table. + +## Request + +```json +{ + "action": "insert", + "table": "items", + "data": { + "name": "New Widget", + "category_id": 3, + "price": 24.99, + "status": "draft" + } +} +``` + +## Fields + +| Field | Type | Required | Notes | +|----------|--------|----------|------------------------------| +| `action` | string | yes | must be `"insert"` | +| `table` | string | yes | target table | +| `data` | object | yes | column value pairs to insert | + +## Response + +```json +{ + "success": true, + "data": 42, + "rows_affected": 1 +} +``` + +`data` is the auto increment ID of the newly inserted row (`last_insert_id`). + +## Behaviors + +- columns you dont have write access to should cause jsonderulo to start stripping for you and remove the naughty stuff from `data`. if theres nothing left after stripping you get an error (cant insert nothing lol) +- system columns should be auto populated: + - `created_by` set to your user ID + - `pinned_to` set to your user ID (unless you have `rwa` permission and explicitly set it to someone else) + - `created_at` handled by SQL defaults +- if the table has auto generation rules configured (like auto generated codes or UUIDs) those should get applied automatically +- you need write permission on the table. read only permission codes like `r`, `rg` and `ro` will give you a nice 403 diff --git a/query/select.md b/query/select.md new file mode 100644 index 0000000..0b84dc8 --- /dev/null +++ b/query/select.md @@ -0,0 +1,67 @@ +# Select + +[back to query](README.md) /// [home](../README.md) + +Read rows from a table. + +## Request + +```json +{ + "action": "select", + "table": "items", + "columns": ["id", "name", "price"], + "filter": { + "and": [ + { "column": "status", "op": "=", "value": "active" }, + { "column": "price", "op": ">", "value": 10 } + ] + }, + "joins": [ + { + "table": "categories", + "on": "items.category_id = categories.id", + "type": "LEFT" + } + ], + "order_by": [ + { "column": "name", "direction": "ASC" } + ], + "limit": 50, + "offset": 0 +} +``` + +## Fields + +| Field | Type | Required | Default | Notes | +|------------|------------------|----------|----------------|--------------------------------------------------------------| +| `action` | string | yes | | must be `"select"` | +| `table` | string | yes | | target table | +| `columns` | array of strings | no | all columns | which columns to return | +| `where` | object | no | | simple key value filter (see [filters](filters.md)) | +| `filter` | object | no | | structured filter with operators (see [filters](filters.md)) | +| `joins` | array | no | | join other tables (see [filters](filters.md)) | +| `order_by` | array | no | | sort results (see [filters](filters.md)) | +| `limit` | integer | no | server default | max rows to return | +| `offset` | integer | no | 0 | skip this many rows | + +## Response + +```json +{ + "success": true, + "data": [ + { "id": 1, "name": "Widget", "price": 19.99 }, + { "id": 2, "name": "Gadget", "price": 29.99 } + ] +} +``` + +## Behaviors + +- if you dont specify `columns` you get all columns you have read access to +- if you request columns youre not allowed to see they should get silently stripped and a `warning` should be included so you know json derulo censored you +- `limit` should be auto applied if you dont set one (from config defaults). if you set one higher than your allowed max it gets capped and a `warning` tells you +- ownership scoping should be automatically applied based on your permission level (see [ownership](../permissions/ownership.md)) +- you need read permission on every joined table in regards to what you want to join obv. otherwise your whole query gets spanked by jsonderulo! diff --git a/query/update.md b/query/update.md new file mode 100644 index 0000000..2c5b753 --- /dev/null +++ b/query/update.md @@ -0,0 +1,54 @@ +# Update + +[back to query](README.md) /// [home](../README.md) + +Modify existing rows in a table. + +## Request + +```json +{ + "action": "update", + "table": "items", + "data": { + "price": 29.99, + "status": "active" + }, + "filter": { + "column": "id", + "op": "=", + "value": 42 + } +} +``` + +## Fields + +| Field | Type | Required | Notes | +|----------|--------|----------|------------------------------| +| `action` | string | yes | must be `"update"` | +| `table` | string | yes | target table | +| `data` | object | yes | column value pairs to update | +| `where` | object | yes* | simple key value filter | +| `filter` | object | yes* | structured filter | + +*at least one of `where` or `filter` is required. you cant do an unfiltered update. + +## Response + +```json +{ + "success": true, + "rows_affected": 1 +} +``` + +## Behaviors + +- a WHERE clause is **mandatory**. the server refuses to run an update without one. this is a safety thingy yk +- columns you dont have write access to are silently stripped. `warning` is included if json derulo stripped for you +- `last_modified_by` auto set to your user ID +- `last_modified_at` is handled by sqls ON UPDATE trigger +- ownership scoping applies: if you have `rwo` you can only update rows where `pinned_to` equals your user ID uness you have rwa. same deal with `rwg` for group scoping +- limit is applied if set and capped to your max +- auto generation rules apply on update too if configured for the update action |