FreshCode test assignment (clojure)

Description

This task should introduce you a design pattern from one of our projects and give you a chance to show off your design skills.

Project is provided with a simple Star Wars data set for a PostgreSQL database. There is an SQL script that will initialize the database the first time it is started.

Working with the project

Prerequisites

To get started with this project you will need to first install the following software (follow the links to get installation instruction for your operating system):

• Clojure itself

• Docker

• Docker Compose

• Babashka

• clj-kondo

• cljstyle

Start Up/Shut Down

To get started with the development run this commands in your terminal:

bb up # starts the database
bb repl # starts project repl

To shut down the database:

bb down

Debugging

For ease of debugging, the project contains hashp dependency, that is required in the dev.user namespace and is available in all namespaces after repl startup.

Running tests

There are commands for runnig tests:

bb test # run both unit and integration tests
bb test:unit # run unit tests only
bb test:integration # run integration tests only

Linting

There are commands to find and fix lint errors:

bb lint # find problems
bb lint:fix # fix them

Your assignment

Table Manager

You need to implement a table manager functionality. Assume that your table manager has to deal with a PostgreSQL database only.

Your table manager should be capable of doing the following:

1. List all tables.

2. Get table structure. (including column names, column data types, if column is nullable)

3. Get table data.

4. Check if table with specified name is defined.

5. Check if table is empty.

6. Run query (if you are not using ITable protocol).

List all tables

There should be a function that, given a table manager entity, lists all available tables or tables satisfying certain requirements. A filter may be supplied. Filter is a predicate that takes a table and returns true if it satisfies a condition. A table may be represented as a name string or as a data structure implementing ITable protocol.

(list-tables table-manager)
;; => ["table-1" "table-2" ...]
;; or
;; => [^ITable table-1 ^ITable table-2 ...]

(list-tables table-manager has-specific-column?)
;; => ["table-2" "table-4"]
;; or
;; => [^ITable table-2 ^ITable table-4]

Get table structure

There should be a function that, given a table manager entity and a table name, gets the table strucrure (including column names, column structure).

(table-structure table-manager "table-name")
;; => {:column-names   ["col-1" "col-2" ...]
;;     :column-structs {"col-1" {:sql-type  "varchar"
;;                               :nullable? false}
;;                      ...}

Get table data

There should be a function that, given a table manager entity and a table name, gets table. A table may be represented as a collection of hash maps or as a data structure implementing SQL script`ITable` protocol. Function returns nil if there is no table with given name.

(table-data table-manager table-id)
;; => [{"col-1" value-1 "col-2" value-2 ...} ...]
;;
;; => or ^ITable table

If table is defined

There should be a function that, given a table manager entity and a table name, returns true if the table is defined.

(has-table? table-manager "table-name")
;; => true

If table is empty

There should be a function that, given a table manager entity and a table name, returns true if table is empty.

(empty-table? table-manager "table-name")
;; => true (if empty)

Run query

If you are not using ITable protocol, there should be a function that, given a table manager and a query structure, should run a query against a database and return a result.

(run-query table-manager ["select name from planet limit 10 order by name"])
;; => ["Alderaan" "Aleen Minor" "Bespin" "Bestine IV" "Cato Neimoidia"]

ITable protocol

You cam implement the ITable protocol for your tables to make it responsible for building, running and caching queries.

Protocol should have methods capable of 1. getting table name. 2. getting table structure. 3. running and caching queries

Testing Your Assignment

You should write test for all table manager methods and for the following queries against the Star Wars sample database:

1. Get planets list.

2. Get males list.

3. Match all pairs from the same planet.

4. Get people’s mass by planet.