# SQL Trainer API Documentation :rocket:
## Table of Contents
1. [Return Status](#return-status)
2. [Views](#views)
3. [Authentication API](#authentication-api)
3.1. [Login](#login)
3.2. [Logout](#logout)
4. [User API](#user-api)
5. [Role API](#role-api)
6. [SchemaDef API](#schemadef-api)
7. [TableDef API](#tabledef-api)
8. [ColumnDef API](#columndef-api)
9. [ForeignKeyDef API](#foreignkey-api)
10. [ForeignKeyRelationDef API](#foreignkeyrelation-api)
11. [Task API](#task-api)
## Return Status
### 200 - ok
Returned if everything ran as expected. The responses are described for the individual routes.
### 400-403
**Response:**
```json
{
}
```
### 404 - not found
**Response:**
```json
{
}
```
### 500-??? - internal Server Error
**Response:**
## Different views
Relations in delivered objects are represented differently dependent on the used front end (since the [Trainer App](https://github.com/RDB-Academy/Interface-Module-SQL) and the [Admin App](https://github.com/RDB-Academy/Interface-Module-SQL-Admin) are using different front end libraries, [Ember](https://emberjs.com/) and [React](https://facebook.github.io/react/)).
Example-wise, dependent on the front-end, two [TableDef](#tabledef-api) objects with id 2,3 respectively are represented as a relation-list within other objects differently.
In the Trainer-App only the ids are represented,
```json
[
2,
3
]
```
while the Admin App also delivers a list of basic views on the linked objects:
```json
[
{
"id":2,
"name":"table2Name",
"columnDefListSize":10,
"createdAt":"2017-05-15T17:30:24.468",
"modifiedAt":"2017-05-15T17:30:24.574"
},{
"id":3,
"name":"table3Name",
"columnDefListSize":4,
"createdAt":"2017-05-15T17:30:24.468",
"modifiedAt":"2017-05-15T17:30:24.574"
}
]
```
:::info
In the Admin App, a basic view on any object contains all but the relational attributes. Also a full view, containing relations, exists for each object.
Basic objects are always delivered, when requesting a list of objects. Full objects need to be requested individualy.
:::
## Authentication API
### Login
This method...
**Required Permission:** Admin, Hiwi, User, Anonymous
++**Request:**++
```
POST /api/login
```
*Body:*
```json
{
"email": "test@test.de",
"password": "test"
}
```
++**Response:**++
```json
{
}
```
### Logout
**Required Permission:** Admin, Hiwi, User
++**Request:**++
```
DELETE /api/session
```
++**Response:**++
*HTTP-Status:* ***OK***
### Validate Session
**Required Permission:*** Admin, Hiwi, User
**Required:**
```
GET /api/login
```
## User API
## Role API
0. [Overview](#overview)
1. [Create Role](#create-role)
2. [Get all Role](#get-all-role)
3. [Get a single Role](#get-a-single-role)
4. [Update Role](#update-role)
5. [Delete Role](#delete-role)
### Overview
4 Roles are present in descending rights:
- Admin - any permissions
- Hiwi - any permissions except Role permissions
- User - standard permissions
- Anonymous - almost no permissions
### Create Role
### Get all Roles
### Get a single Role
### Update Role
### Delete Role
## SchemaDef API
A schemaDef describes an existent Schema. Owns [Tables](#tabledef-api), [ForeignKeys](foreignkey-api) and [Tasks](#task-api).
1. [Create SchemaDef](#create-schemadef)
2. [Get all SchemaDefs](#get-all-schemadefs)
3. [Get a single SchemaDef](#get-a-single-schemadef)
4. [Update SchemaDef](#update-schemadef)
5. [Delete SchemaDef](#delete-schemadef)
### Create SchemaDef
**Required Permission:** Admin, Hiwi
**Request:**
```
POST /api/schema-defs
```
**Request Body:**
| Name | Type | Description |
|-----------|---------|-------------------------------|
| name | String | Schema Name |
| available | Boolean | Availability of the SchemaDef |
**Response:**
The created [SchemaDef](#get-a-single-schemadef)
### Get all SchemaDefs
**Required Permission:** Admin, Hiwi, User
**Request:**
```
GET /api/schema-defs
```
:::info
**Parameters:**
| Name | Type | Description |
|-----------|---------|-------------------------------|
| name | String | Schema Name |
| available | Boolean | Availability of the SchemaDef |
| creator | String | Creator of the SchemaDef |
:::
**Response:**
Matching [SchemaDef](#get-a-single-schemadef)s
```json
[
SchemaDef1,
SchemaDef2,
...
]
```
### Get a Single SchemaDef
**Required Permission:** Admin, Hiwi, User
**Request:**
```
GET /api/schema-defs/{:id}
```
**Response:**
```json
{
"id":1,
"name":"SchemaDefName",
"available":true,
"createdAt":"2017-05-08T15:59:02.93",
"modifiedAt":"2017-05-08T15:59:03.014",
"tableCount":2,
"foreignKeyCount":1,
"taskCount":2,
"relations":
{
"tableDefList": [
4,
5
],
"foreignKeyList": [
1
],
"taskList": [
1,
2
]
}
}
```
### Update SchemaDef
**Required Permission:** Admin, Hiwi
**Request:**
```
PATCH /api/schema-defs
```
**Request Body:**
```json
{
}
```
**Response:**
The updated [SchemaDef](#get-a-single-schemadef)
### Delete SchemaDef
**Required Permission:** Admin, Hiwi
**Request:**
```
DELETE /api/schema-defs/{:id}
```
**Request Body:**
```json
{
}
```
**Response:**
## TableDef API
A table existent in some schema. Owns [Columns](#columndef-api) and its owning [Schema](#schemadef-api).
1. [Create TableDef](#create-tabledef)
2. [Get all TableDefs](#get-all-tabledefs)
3. [Get a single TableDef](#get-a-single-tabledef)
4. [Update TableDef](#update-tabledef)
5. [Delete TableDef](#delete-tabledef)
### Create TableDef
**Required Permission:** Admin, Hiwi
**Request:**
```
POST /api/table-defs
```
**Request Body:**
```json
{
}
```
**Response:**
The created [TableDef](#get-a-single-tabledef)
### Get all TableDefs
**Required Permission:** Admin, Hiwi, User
**Request:**
```
GET /api/table-defs
```
:::info
**Parameters:**
| Name | Type | Description |
|------------|--------|--------------|
| Schema-Def | Number | SchemaDef Id |
:::
**Response:**
Matching [TableDef](#get-a-single-tabledef)s
```json
[
tableDef1,
tableDef2,
...
]
```
### Get a single TableDef
**Required Permission:** Admin, Hiwi, User
**Request:**
```
GET /api/table-defs/{:id}
```
**Response:**
```json
{
}
```
### Update TableDef
**Required Permission:** Admin, Hiwi
**Request:**
```
PATCH /api/table-defs/{:id}
```
**Response:**
The updated [TableDef](#get-a-single-tabledef)
### Delete TableDef
**Required Permission:** Admin, Hiwi
**Request:**
```
DELETE /api/table-defs/{:id}
```
**Response:**
```
```
## ColumnDef API
A column existent in some table. Consists its owning [Table](#tabledef-api) and its owning [ForeignKeyRelation](#foreignkeyrelation-api)s.
1. [Create ColumnDef](#create-columndef)
2. [Get all ColumnDefs](#get-all-columndefs)
3. [Get a single ColumnDef](#get-a-single-columndef)
4. [Update ColumnDef](#update-columndef)
5. [Delete ColumnDef](#delete-columndef)
### Create ColumnDef
**Required Permission:** Admin, Hiwi
**Request:**
```
POST /api/column-defs
```
**Request Body:**
```json
{
}
```
**Response:**
The created [ColumnDef](#get-a-single-columndef)
### Get all ColumnDefs
**Required Permission:** Admin, Hiwi, User
**Request:**
```
GET /api/column-defs
```
:::info
**Parameters:**
| Name | Type | Description |
|-----------|---------|-----------------|
| Table-Def | Integer | TableDef Id |
:::
**Response:**
Matching [ColumnDef](#get-a-single-columndef)s
```json
[
ColumnDef1,
ColumnDef2,
...
]
```
### Get a single ColumnDef
**Required Permission:** Admin, Hiwi, User
**Request:**
```
GET /api/table-defs/{:id}
```
**Response:**
```
```
### Update ColumnDef
**Required Permission:** Admin, Hiwi
**Request:**
```
PATCH /api/table-defs/{:id}
```
**Response:**
The updated [ColumnDef](#get-a-single-columndef)
### Delete ColumnDef
**Required Permission:** Admin, Hiwi
**Request:**
```
DELETE /api/column-defs/{:id}
```
**Response:**
```
```
## ForeignKey API
A foreign Key between existing tables in some schema. Consists of (possibly multiple) [ForeignKeyRelation](#foreignkeyrelation-api)s. Also contains the owning [Schema](#schemadef-api).
1. [Create ForeignKey](#create-foreignkey)
2. [Get all ForeignKeys](#get-all-foreignkeys)
3. [Get a single ForeignKey](#get-a-single-foreignkey)
4. [Update ForeignKey](#update-foreignkey)
5. [Delete ForeignKey](#delete-foreignkey)
### Create ForeignKey
**Required Permission:** Admin, Hiwi
**Request:**
```
POST /api/foreign-keys
```
**Response:**
The created [ForeinKey](#get-a-single-foreignkey)
### Get all ForeignKeys
**Required Permission:** Admin, Hiwi, User
**Request:**
```
GET /api/foreign-keys
```
:::info
**Parameter:**
| Name | Type | Description |
|-----------|---------|-----------------|
| Table-Def | Integer | TableDef Id |
:::
**Response:**
Matching [ForeinKey](#get-a-single-foreignkey)s
```json
[
ForeignKey1,
ForeignKey2,
...
]
```
### Get a single ForeignKey
**Required Permission:** Admin, Hiwi, User
**Request:**
```
GET /api/foreign-keys/{:id}
```
**Response:**
```
```
### Update ForeignKey
**Required Permission:** Admin, Hiwi
**Request:**
```
PATCH /api/foreign-keys/{:id}
```
**Response:**
The updated [ForeinKey](#get-a-single-foreignkey)
### Delete ForeignKey
**Required Permission:** Admin, Hiwi
**Request:**
```
DELETE /api/foreign-keys/{:id}
```
**Response:**
```
```
## ForeignKeyRelation API
Direct Mapping of a source [Column](#column-api) to a foreignKey [Column](#column-api). Also contains the owning [ForeignKey](#foreignkey-api).
1. [Create ForeignKeyRelation](#create-foreignkeyrelation)
2. [Get all ForeignKeyRelations](#get-all-foreignkeyrelations)
3. [Get a single ForeignKeyRelation](#get-a-single-foreignkeyrelation)
4. [Update ForeignKeyRelation](#update-foreignkeyrelation)
5. [Delete ForeignKeyRelation](#delete-foreignkeyrelation)
### Create ForeignkeyRelation
**Required Permission:** Admin, Hiwi
**Request:**
```
POST /api/foreign-key-relations
```
**Response:**
The created [ForeignKeyRelation](#get-a-single-foreignkeyrelation)
### Get all ForeignKeyRelations
**Required Permission:** Admin, Hiwi, User
**Request:**
```
GET /api/foreign-key-relations/{:id}
```
:::info
**Parameter:**
| Name | Type | Description |
|-----------|---------|-----------------|
| Table-Def | Integer | TableDef Id |
:::
**Response:**
Matching [ForeinKeyRelation](#get-a-single-foreignkeyrelation)s
```json
ForeignKeyRelation1,
ForeignKeyRelation2,
...
```
### Get a single ForeignKeyRelation
**Required Permission:** Admin, Hiwi, User
**Request:**
```
GET /api/forreign-key-relation/{:id}
```
**Response:**
```
```
### Update ForeignKeyRelation
**Required Permission:** Admin, Hiwi
**Request:**
```
PATCH /api/foreign-key-relations/{:id}
```
**Response:**
The updated [ForeinKeyRelation](#get-a-single-foreignkeyrelation)
### Delete ForeignKeyRelation
**Required Permission:** Admin, Hiwi
**Request:**
```
DELETE /api/foreign-key-relations/{:id}
```
**Response:**
```
```
## Task API
A task for the owning [Schema](#schemadef-api).
0. [Different Views](#views)
1. [Create Task](#create-task)
2. [Get all Tasks](#get-all-tasks)
3. [Get a single Task](#get-a-single-task)
4. [Update Task](#update-task)
5. [Delete Task](#delete-task)
### Views
Based on Role, Requests will deliver different Views on the data due to solutions of the tasks.
For a User, the following task object is delivered.
```json
{
}
```
Otherwise (Admin, Hiwi) the complete object is deliviered as described in the respective subtopic.
### Create Task
**Required Permission:** Admin, Hiwi
**Request:**
```
POST /api/tasks
```
**Response:**
The created [Task](#get-a-single-task)
### Get All Tasks
**Required Permission:** Admin, Hiwi, User
**Request:**
```
GET /api/tasks
```
:::info
**Parameters:**
| Name | Type | Description |
|------------|---------|---------------------|
| Task | Integer | TableDef Id |
| Difficulty | Integer | TableDef Difficulty |
:::
**Response:**
Matching [Task](#get-a-single-task)s
```json
[
Task1,
Task2,
...
]
```
### Get a single Task
**Required Permission:** Admin, Hiwi, User
**Request:**
```
GET /api/tasks/{:id}
```
**Response:**
```
```
### Update Task
**Required Permission:** Admin, Hiwi
**Request:**
```
PATCH /api/tasks/{:id}
```
**Response:**
The updated [Task](#get-a-single-task)
### Delete Task
**Required Permission:** Admin, Hiwi
**Request:**
```
DELETE /api/tasks/{:id}
```
**Response:**
```
```
Sign in via Facebook
Sign in via X(Twitter)
Sign in via GitHub
Sign in via Dropbox
Sign in with Wallet
Connect another wallet