Data interface (REST)

Data Structures

Constraint Definition

constraint_definition (Dictonary):
 
Specifies a definition of a constraint.
  • action Action of constraint (e.g. ADD, DROP)
  • constraint_type Type of constraint (e.g. UNIQUE, PRIMARY KEY, FOREIGN KEY)
  • constraint_name Name of constraint.
  • constraint_parameter Parameter of constraint.
  • reference_table Name of reference table, can be None.
  • reference_column Name of reference column, can be None.

Column Definition

column_definition (Dictonary):
 
Specifies a definition of a column.
  • name Name of column.
  • new_name New name of column, can be None.
  • data_type New datatype of column, can be None.
  • is_nullable New null value of column, can be None.
  • character_maximum_length New data length of column, can be None.

Response Definition

response_dictonary (Dictonary):
 
Describes the result of an api action.

Table (RESTful)

URL: /schema/{schema}/table/{table}

GET

Reference needed.

PUT

Creates a new table in database. JSON should contain a constraint definition array and a column definition array.

Example:

{
  "constraints": [
    {
      "constraint_type": "FOREIGN KEY",
      "constraint_name": "fkey_schema_table_database_id",
      "constraint_parameter": "database_id",
      "reference_table": "example.table",
      "reference_column": "database_id_ref"
    },
    {
      "constraint_type": "PRIMARY KEY",
      "constraint_name": "pkey_schema_table_id",
      "constraint_parameter": "id",
      "reference_table": null,
      "reference_column": null
    }
  ],
  "columns": [
    {
      "name": "id",
      "data_type": "int",
      "is_nullable": "YES",
      "character_maximum_length": null
    },
    {
      "name": "name",
      "data_type": "character varying",
      "is_nullable": "NO",
      "character_maximum_length": 50
    }
  ]
}

POST

JSON should contain a column or constraint definition. Additionally action and type should be mentioned.

  • type can be constraint or column.
  • action can be ADD and DROP.
  • constraint_type can be every constraint type supported by Postgres.
  • reference_table and reference_column can be null, if not necessary.

Example:

{
  "type" : "constraint",
  "action": "ADD",
  "constraint_type": "FOREIGN KEY",
  "constraint_name": "fkey_label",
  "constraint_parameter": "changed_name",
  "reference_table" : "reference.group_types",
  "reference_column" : "label"
}

{
  "type" : "column",
  "name" : "test_name",
  "newname" : "changed_name",
  "data_type": "character varying",
  "is_nullable": "NO",
  "character_maximum_length": 50
}

Rows (RESTful)

GET

URL: /schema/<schema>/tables/<table>/rows/

You can use this part to get information from the database.

You can specify the following parameters in the url:
  • columns (List) List of selected columns, e.g. id,name
  • where (List) List of where clauses, e.g. id+OPERATOR+1+CONNECTOR+name+OPERATOR+georg
    • OPERATORS could be EQUAL, GREATER, LOWER, NOTEQUAL, NOTGREATER, NOTLOWER
    • CONNECTORS could be AND, OR
  • orderby (List) List of order columns, e.g. name,code
  • limit (Number) Number of displayed items, e.g. 100
  • offset (Number) Number of offset from start, e.g. 10

Deprecated Stuff

Create a table

[1]The OEP is currently only supporting a non-compound integer primary key labeled ‘id’. Violation of this constraint might render the OEP unable to display the data stored in this table.

Dictionary structure

schema (String):
 Specifies the schema name the table should be created in. If this schema does not exist it will be created.
table (String):Specifies the name of the table to be created.
fields (List):List specifying the columns of the new table (see Field specification).
constraints (List):
 List of additional constraints (see Constraint specification).

Field specification

name (String):Name of the field
type (String):Name of a valid Postgresql type
pk (Bool):Specifies whether this column is a primary key. Be aware of [1]

Constraint specification

Args:
name (String):

Type of constraint. Possible values:
constraint (Dictionary):
 

Dictionary as specified by the foreign key.

Foreign key specification

schema (String):
 

Name of the schema the referenced table is stored in
table (String):

Name of the referenced table
field (String):

Name of the referenced column
on_delete (String):
 

Specifies the behaviour if this field is deleted. Possible values:

  • cascade
  • no action
  • restrict
  • set null
  • set default

Insert data

schema (String):
 Specifies the schema name the table should be created in. If this schema does not exist it will be created.
table (String):Specifies the name of the table to be created.
fields (List):List specifying the column names the date should be inserted in.
values (List):Each element is a list of values that should be inserted. The number of elements must match the number of fields.
returning (Bool):
 An expression that is evaluated and returned as result. If this entry is present the result of this expression is returned as in Select Data.

Select data

all (Bool):Specifies whether all rows should be returned (default)
distinct (Bool):
 Specifies whether only unique rows should be returned
fields (List):The list of columns that should be returned (see select_field_spec_)
where (List):The list of condition that should be considered (see select_condition_spec_)
limit (Integer or ‘all’):
 Specifies how many results should be returned. If ‘all’ is set all matching rows will be returned (default).
offset (Integer):
 Specifies how many entries should be skipped before returning data

Binding the API to python