Silectis Scala Assessment
This repository contains a Scala assessment for Silectis Platform Engineer applicants
Assessment Instructions
The purpose of this application is to accept a SQL query via an HTTP request, parse the query, and audit which table and columns are being accessed by the query.
So far, the SQL parser has been implemented as well as the HTTP server and request handler. Your task is to implement the query auditor.
SQL Parser
For this simple example, only a small subset of the SQL specification is supported. The parser only
recognizes simple SELECT
statements with an optional FROM
clause. If specified, the FROM
clause can only reference a single table (no joins). However, subqueries are supported.
Simple column references, literal values, and functions are supported as columns in the select statement.
Aliases are also supported for both tables and columns.
For example, the following queries are supported:
select 1 as one, 'two' as two
select col1, col2 from table1
select a, b, x, y from (
select
col1 as a,
2 as b,
x,
upper('test') as y
from table2
) as s
You can find more examples of supported queries in com.silectis.sql.parse.SqlParserSpec
.
Query Auditor
com.silectis.sql.audit.QueryAuditor
contains a stub of the audit method to implement. This method
should return whether the query references a table, and, if it does, which table is referenced and
which columns from that table are referenced.
For example, the following sql query
select col1, col2 from table1
should return
{
"table": "table1",
"columns": ["col1", "col2"]
}
The following query
select a, b, x, y from (
select
col1 as a,
2 as b,
x,
upper('test') as y
from table2
) as s
should return
{
"table": "table2",
"columns": ["col1", "x"]
}
And the following query
select 1 as one, 'two'
should return
{
"columns": []
}
since it does not reference any named table.
Submission
Clone this repository and work on your solution, committing your changes to your local repository.
When you are finished, zip the entire project folder, including your local git repository,
and email it to recruiting@silect.is
.
Required Software
SBT is used as the build tool.
Building and Testing
Start an sbt shell. We will use this to compile, test, and run the application.
sbt
Clean the build:
clean
Compile and execute tests:
test
Run the tests with code coverage and generate a coverage report:
; clean; coverage; test; coverageReport
Run the application as server on the local machine (CTRL+C to stop):
run
The server binds to port 8080. While it is running, you can use curl
to submit a query for auditing:
curl --data 'select col1, col2 from test1' localhost:8080