Test API lib

[a-givertzman]

Fix problems:

• [x] non-utf-8 message finalizes api-server app
• [x] api-server returns package with empty auth-token and zero id if invalid json has been sent to him
• [ ] request to executable with wrong params returns uninformative message
• [ ] there is an ability to perform executable requests with non-executable services (e.g. databases)

[Minyewoo]

Report:

I. Common

Valid requests

Request with multiple types of queries

request json:

{
    "auth_token": "your-auth-token",
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9",
    "executable": {
        "name": "database",
        "params": {
            "a": 1,
            "b": 2,
        },
    },
    "sql": {
        "database":"database",
        "sql":"SELECT prep_object, pad FROM do_data LIMIT 2;"
    },
    "python":{
        "script":"py-test",
        "params":{       
            "a":123,
            "b":345    
        },
    }
}

response json:

{
    "auth_token": "your-auth-token", 
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9", 
    "query": {
        "Sql": {
            "database": "executable-test", 
            "sql": "SELECT * FROM do_data LIMIT 2;"
        },
    }, 
    "data": [{"prep_object": "ДНС-2 Еты-Пуровского", "pad": "12"}, {"prep_object": "ДНС-2 Еты-Пуровского", "pad": "22А"}], 
    "errors": [],
}

I made multiple requests of that kind and came to a conclusion: backend processes json keys picking only one (others are ignored) with the following priorities: sql -> python -> executable. Maybe we should add a support for the batch of requests, because for multiple requests we have multiple delay over the network.

• [ ] How to compose responses from multiple requests?
• [ ] How to identify responses to requests coupling?

Invalid requests

Request with wrong key

request json:

{
    "auth_token": "your-auth-token",
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9",
    "sql_invalid": {
        "database":"database",
        "sql":"SELECT prep_object, pad FROM do_data LIMIT 2;"
    },
}

response json:

{
    "auth_token": "none", 
    "id": "0", 
    "query": "Error", 
    "data": [], 
    "errors": ["Error: Wrong query structure"],
}

• [x] Error message is correct, but for some reason auth_token and id is not.
• [x] "Query" field in response contains "Error", but more logically it should be "Incorrect query" or something similar to that.

Request without first and last bracket

request json:

"auth_kavo_token":"your-auth-token",
"id":"8a3568e0-395a-11ee-8f57-55d00ab011c9",
"sql_invalid":{
    "database":"database",
    "sql":"SELECT * FROM do_data LIMIT 2;"
}

response json:

{
    "auth_token": "unknown", 
    "id": "unknown", 
    "query": "Error", 
    "data": [], 
    "errors": ["trailing characters at line 1 column 13"],
}

• [x] Error message is correct, but for some reason auth_token and id is not.
• [x] "Query" field in response contains "Error", but more logically it should be "Incorrect query" or something similar to that.

Request with json package cut in half

request json:

{
    "auth_kavo_token":"your-auth-token",
    "id":"8a3568e0-395a-11ee-8f57-55d00ab011c9",
    "sql_invalid":{
        "database":"database",
        "sql":"SELECT * FROM

response json:

{
    "auth_token": "unknown", 
    "id": "unknown", 
    "query": "Error", 
    "data": [], 
    "errors": ["EOF while parsing a string at line 1 column 133"],
}

• [x] Error message is correct, but for some reason auth_token and id is not.
• [x] "Query" field in response contains "Error", but more logically it should be "Incorrect query" or something similar to that.

II. Python script

Valid requests

Request with valid params (int)

request json:

{
    "auth_token": "your-auth-token",
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9",
    "python": {
        "script":"py-test",
        "params": {
            "a": 123,
            "b": 345,
        },
    },
}

response json:

{
    "auth_token": "your-auth-token", 
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9", 
    "query": {
        "Python": {
            "name": "py-test", 
            "params": {"a": 123, "b": 345}
        },
    }, 
    "data": [{"bb": 690, "aa": 246}], 
    "errors": [],
}

Response is valid.

Request with valid params (double)

request json:

{
    "auth_token": "your-auth-token",
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9",
    "python": {
        "script":"py-test",
        "params": {
            "a": 123.456,
            "b": 345.678,
        },
    },
}

response json:

{
    "auth_token": "your-auth-token", 
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9", 
    "query": {
        "Python": {
            "name": "py-test", 
            "params": {"a": 123.456, "b": 345.678}
        },
    }, 
    "data": [{"bb": 691.356, "aa": 246.912}], 
    "errors": [],
}

Response is valid.

Request with valid params (string)

request json:

{
    "auth_token": "your-auth-token",
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9",
    "python": {
        "script":"py-test",
        "params": {
            "a": "123.456",
            "b": "345.678",
        },
    },
}

response json:

{
    "auth_token": "your-auth-token", 
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9", 
    "query": {
        "Python": {
            "name": "py-test",
            "params": {"a": "123.456", "b": "345.678"}
        },
    }, 
    "data": [{"bb": "345.678345.678", "aa": "123.456123.456"}], 
    "errors": [],
}

Response is valid.

Request with valid params (bool)

request json:

{
    "auth_token": "your-auth-token",
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9",
    "python": {
        "script":"py-test",
        "params": {
            "a": true,
            "b": false,
        },
    },
}

response json:

{
    "auth_token": "your-auth-token", 
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9", 
    "query": {
        "Python": {
            "name": "py-test",
            "params": {"a": true, "b": false}
        },
    }, 
    "data": [{"bb": 0, "aa": 2}], 
    "errors": [],
}

Response is valid.

Invalid requests

Request without needed params

request json:

{
    "auth_token": "your-auth-token",
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9",
    "python": {
        "script":"py-test",
        "params": {
            "aaa": "123.456",
            "bbb": "345.678",
        },
    },
}

response json:

{
    "auth_token": "your-auth-token", 
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9", 
    "query": {
        "Python": {
            "name": "py-test",
            "params": {"aaa": "123.456", "bbb": "345.678"}
        },
    }, 
    "data": [], 
    "errors": ["PythonQuery.execute | python script error: \"Traceback (most recent call last):\\n  File \"/home/minyewoo/Development/api-server/extensions/scripts/script_tamplate.py\", line 29, in <module>\\n    main()\\n  File \"/home/minyewoo/Development/api-server/extensions/scripts/script_tamplate.py\", line 18, in main\\n    \"aa\": parsed[\\'a\\'] * 2,\\nKeyError: \\'a\\'\\n\""],
}

Error is readable for python developer, but unusable for common API consumer. This error message also shows the actual line of code where the error occured - this could leak credentials or other secret information.

Python request with database service name

request json:

{
    "auth_token": "",
    "id": "",
    "python": {
        "script": "database",
        "params": {
            "a": 1,
            "b": 2,
        },
    },
}

response json:

{
    "auth_token": "", 
    "id": "", 
    "query": {
        "Python": {"script": "database", "params": {"a": 1, "b": 2}}
    }, 
    "data": [], 
    "errors": [
        "PythonQuery.execute | python script error: \"  File \"/Users/minyewoo/development/api-server/database.sqlite\", line 1\\n    SQLite format 3\\nSyntaxError: source code cannot contain null bytes\\n\""
    ]
}

The response error message is incorrect, ideally it should say that there is no such python script.

Python request with executable service name

request json:

{
    "auth_token": "",
    "id": "",
    "python": {
        "script": "executable-test",
        "params": {
            "a": 1,
            "b": 2,
        },
    },
}

response json:

{
    "auth_token": "", 
    "id": "", 
    "query": {
        "Python": {"script": "executable-test", "params": {"a": 1, "b": 2}}
    }, 
    "data": [], 
    "errors": [
        "PythonQuery.execute | python script error: \"  File \"/Users/minyewoo/development/api-server/extensions/bin/simple-executable\", line 1\\n    \\u{7f}ELF\\u{2}\\u{1}\\u{1}\\nSyntaxError: source code cannot contain null bytes\\n\""
    ]
}

The response error message is incorrect, ideally it should say that there is no such python script.

III. Database

Valid requests

Request with select sql query

request json:

{
    "auth_token": "your-auth-token",
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9",
    "sql": {
        "database":"database",
        "sql":"SELECT * FROM do_data LIMIT 2;"
    },
}

response json:

{
    "auth_token": "your-auth-token", 
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9", 
    "query": {
        "Sql": {
            "database": "database", 
            "sql": "SELECT * FROM do_data LIMIT 2;"
        },
    }, 
    "data": [{"prep_object": "ДНС-2 Еты-Пуровского", "pad": "12"}, {"prep_object": "ДНС-2 Еты-Пуровского", "pad": "22А"}], 
    "errors": [],
}

Response is valid.

Request with drop-table sql query

request json:

{
    "auth_token": "your-auth-token",
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9",
    "sql": {
        "database":"database",
        "sql":"DROP TABLE do_data;"
    },
}

response json:

{
    "auth_token": "your-auth-token", 
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9", 
    "query": {
        "Sql": {
            "database": "database", 
            "sql": "DROP TABLE do_data;"
        },
    }, 
    "data": [], 
    "errors": [],
}

Response is valid.

Invalid requests

Request with invalid sql query

request json:

{
    "auth_token": "your-auth-token",
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9",
    "sql": {
        "database":"database",
        "sql":"SELECT ALL DATA;"
    },
}

response json:

{
    "auth_token": "your-auth-token", 
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9", 
    "query": {
        "Sql": {
            "database": "database", 
            "sql": "SELECT ALL DATA;"
        },
    }, 
    "data": [], 
    "errors": ["no such column: DATA in SELECT ALL DATA; at offset 11"],
}

Error is correct.

Database request with executable service name

request json:

{
    "auth_token": "",
    "id": "",
    "sql": {
        "name": "executable-test",
        "sql": "SELECT * FROM do_data;",
    },
}

response json:

{
    "auth_token": "", 
    "id": "", 
    "query": {
        "Sql": {"database": "executable-test", "sql": "SELECT * FROM do_data;"}
    }, 
    "data": [], 
    "errors": [
        "ApiServer.build | Error: Database with the namne '' can't be found"
    ]
}

Typo and missing database name in error message.

Database request with python service name

request json:

{
    "auth_token": "",
    "id": "",
    "sql": {
        "name": "py-test",
        "sql": "SELECT * FROM do_data;",
    },
}

response json:

{
    "auth_token": "", 
    "id": "", 
    "query": {
        "Sql": {"database": "py-test", "sql": "SELECT * FROM do_data;"}
    }, 
    "data": [], 
    "errors": [
        "ApiServer.build | Error: Database with the namne '' can't be found"
    ]
}

Typo and missing database name in error message.

IV. Executable

Valid requests

Executable with correct name and correct parameters (variant 1)

request json:

{
    "auth_token": "your-auth-token",
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9",
    "executable": {
        "name": "executable-test",
        "params": {
            "a": 1,
            "b": 2,
        },
    },
}

response json:

{
    "auth_token": "your-auth-token", 
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9", 
    "query": {
        "Executable": {
            "name": "executable-test", 
            "params": {"a": 1, "b": 2}
        },
    }, 
    "data": [{"a": 1, "b": 2, "c": 0.5}], 
    "errors": [],
}

Response is valid.

Executable with correct name and correct parameters (variant 2)

request json:

{
    "auth_token": "your-auth-token",
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9",
    "executable": {
        "name": "executable-test",
        "params": {
            "a": 123,
            "b": 456,
        },
    },
}

response json:

{
    "auth_token": "your-auth-token", 
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9", 
    "query": {
        "Executable": {
            "name": "executable-test", 
            "params": {"a": 123, "b": 456}
        },
    }, 
    "data": [{"a": 123, "b": 456, "c": 0.26973684210526316}], 
    "errors": [],
}

Response is valid.

Invalid requests

Executable request with name of database service

request json:

{
    "auth_token": "your-auth-token",
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9",
    "executable": {
        "name": "database",
        "params": {
            "a": 1,
            "b": 2,
        },
    },
}

response json:

{
    "auth_token": "your-auth-token", 
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9", 
    "query": {
        "Executable": {
            "name": "database", 
            "params": {"a": 1, "b": 2},
        },
    }, 
    "data": [], 
    "errors": ["ExecutableQuery.execute | executable error: Os { code: 13, kind: PermissionDenied, message: \"Permission denied\" }"],
}

The response error is wrong, ideally it should say that there is no such executable.

Executable request with python service name

request json:

{
    "auth_token": "",
    "id": "",
    "executable": {
        "name": "py-test",
        "params": {
            "a": 1,
            "b": 2,
        },
    },
}

response json:

{
    "auth_token": "", 
    "id": "", 
    "query": {
        "Executable": {"name": "py-test", "params": {"a": 1, "b": 2}}
    }, 
    "data": [], 
    "errors": [
        "ExecutableQuery.execute | executable error: Os { code: 13, kind: PermissionDenied, message: \"Permission denied\" }"
    ]
}

The response error is wrong, ideally it should say that there is no such executable.

Executable request with wrong numeric type parameters

request json:

{
    "auth_token": "your-auth-token",
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9",
    "executable": {
        "name": "executable-test",
        "params": {
            "a": 1,
            "b": 2.0,
        },
    },
}

response json:

{
    "auth_token": "your-auth-token", 
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9", 
    "query": {
        "Executable": {
            "name": "executable-test", 
            "params": {"a": 1, "b": 2.0},
        },
    }, 
    "data": [], 
    "errors": ["ExecutableQuery.execute | executable result json parsing error: Error(\"expected value\", line: 1, column: 1)"],
}

The response error is not informative. Ideally it should mention the wrong parameter name.

Executable request with completely wrong type parameters (variant 1)

request json:

{
    "auth_token": "your-auth-token",
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9",
    "executable": {
        "name": "executable-test",
        "params": {
            "a": 1,
            "b": true,
        },
    },
}

response json:

{
    "auth_token": "your-auth-token", 
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9", 
    "query": {
        "Executable": {
            "name": "executable-test", 
            "params": {"a": 1, "b": true},
        },
    }, 
    "data": [], 
    "errors": ["ExecutableQuery.execute | executable result json parsing error: Error(\"expected value\", line: 1, column: 1)"],
}

The response error is not informative. Ideally it should mention the wrong parameter name.

Executable request with completely wrong type parameters (variant 2)

request json:

{
    "auth_token": "your-auth-token",
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9",
    "executable": {
        "name": "executable-test",
        "params": {
            "a": "1",
            "b": 2,
        },
    },
}

response json:

{
    "auth_token": "your-auth-token", 
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9", 
    "query": {
        "Executable": {
            "name": "executable-test", 
            "params": {"a": "1", "b": 2},
        },
    }, 
    "data": [], 
    "errors": ["ExecutableQuery.execute | executable result json parsing error: Error(\"expected value\", line: 1, column: 1)"],
}

The response error is not informative. Ideally it should mention the wrong parameter name.

Executable request with completely wrong type parameters (variant 3)

request json:

{
    "auth_token": "your-auth-token",
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9",
    "executable": {
        "name": "executable-test",
        "params": {
            "a": 1,
            "b": "2",
        },
    },
}

response json:

{
    "auth_token": "your-auth-token", 
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9", 
    "query": {
        "Executable": {
            "name": "executable-test", 
            "params": {"a": 1, "b": "2"},
        },
    }, 
    "data": [], 
    "errors": ["ExecutableQuery.execute | executable result json parsing error: Error(\"expected value\", line: 1, column: 1)"],
}

The response error is not informative. Ideally it should mention the wrong parameter name.

Executable request with completely wrong type parameters (variant 4)

request json:

{
    "auth_token": "your-auth-token",
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9",
    "executable": {
        "name": "executable-test",
        "params": {
            "a": " parameterAValue ",
            "b": ":;parameterBValue+-0()",
        },
    },
}

response json:

{
    "auth_token": "your-auth-token", 
    "id": "8a3568e0-395a-11ee-8f57-55d00ab011c9", 
    "query": {
        "Executable": {
            "name": "executable-test", 
            "params": {"a": " parameterAValue ", "b": ":;parameterBValue+-0()"},
        },
    }, 
    "data": [], 
    "errors": ["ExecutableQuery.execute | executable result json parsing error: Error(\"expected value\", line: 1, column: 1)"],
}

The response error is not informative. Ideally it should mention the wrong parameter name.

V. Broken connection

Request done on broken connection

test sequence: client connects, then server panics, but client doesn't know about it and sends to socket.

request:

ApiRequest(
    address: ApiAddress.localhost(), 
    sqlQuery: SqlQuery(
    authToken: '', 
    database: 'database', 
    sql: 'SELECT id FROM do_data;',
    ),
)
.fetch()

client lib message:

.fetch | socket error: Unsupported operation: Please set "hierarchicalLoggingEnabled" to true if you want to change the level on a non-root logger.

After enabling hierarchial logging:

 .fetch | socket error: FormatException: Unexpected end of input (at character 1)

Connection lost while request in progress

test sequence: client connects, sends data, then server panics.

request:

ApiRequest(
    address: ApiAddress.localhost(), 
    sqlQuery: SqlQuery(
    authToken: '', 
    database: 'database', 
    sql: 'SELECT id FROM do_data;',
    ),
)
.fetch()

client lib message:

.fetch | socket error: FormatException: Unexpected end of input (at character 1)

VI. Additional suggestions:

1. We can create an object of SqlQuery and immediately ask it for id, after which we get an error (LateInitializationError). Is it possible to write a class in such way so that the use of id is more secure?

2. It's a bit confusing that SqlQuery contains a requestToken. Maybe move it to the layer above? Can we create ApiCredentials class, which contains ApiAddress and that token? For example:

class ApiCredentials {
  final ApiAddress _address;
  final String _requestToken;
}

class Books {
  final ApiCredentials _credentials;
  final SqlQuery _sqlQuery;
}

3. I took a look at the server config file to understand which database I should send requests to and this file also caused a little confusion:

databases: 
    - database-sqlite: 
            name: 'database'   

I saw comments in this file but maybe it can be rethought a bit to avoid the human factor.

4. About client-server communications Is it necessary to communicate through sockets? For each request you open new socket, send a json package and then close it. I think it's a bit too much for our task. It is more reliable and not so costly in terms of resources to send a simple POST or GET http request because in fact we just query the existing data once and we don't need real-time updates. Additionally if you implement these changes, it will be easier to use/test.

5. About package format Сan we get rid of flutter dependency and make it based on dart sdk only? We don't use any UI-related features here.

6. About json format for sql queries Example of currently supported json payload for sql queries:

{
    ...
    "sql":{
        "database":"servicename",
        "sql":"SELECT * FROM tablename;"
    }
}

As you can see there are two nested "sql" keys. I suggest to rename the inner duplicate key to "query".