Table of Contents

1. Getting Started
   • Prerequisites
   • Installation
2. Build And Run
   • Build With Maven
   • Run With Maven
   • Run Using Jar
3. Usage
• Embeded Database
• DDL
• Local Machine Hosting
• Software for testing
• Webservice
• Upload Api
• Fetch API
• Get API
• Create API
• Update API
• Delete API
4. Assumption

Getting Started

Prerequisites

Required JDK version

• jdk 1.8

  jdk1.8

• java home point to jdk

SET JAVA_HOME

Required maven version,

• maven 3

  maven 3

or use maven wrapper included

{projectDir} refers to source code root path

• maven wrapper windows

{projectDir}\mvnw.cmd

• maven wrapper linux

{projectDir}\mvnw

Installation

1. Clone the repo

 git clone https://github.com/pureiboi/nphcswe.git

2. Download dependency with maven under Build and Run

Build And Run

Build With Maven

Build using maven wrapper, under working directory

• mvn wrapper (Windows) - command to build

  mvnw.cmd clean package

• mvn wrapper (linux) - command to build

  mvnw clean package

Maven build tool installed on local machine

• maven - command to build

  mvn clean package

Run With Maven

• command to start application

mvn spring-boot:run

Run using Jar

Project is required to be built to generate jar file @ Build With Maven

{projectDir} refers to source code root path

• command to start application

java -jar {projectDir}\nphcswe-0.0.1-SNAPSHOT.jar

Usage

Embeded Database

http://localhost:8080/h2-console

user	password
sa

DDL

DDL is stored and auto executed uppon application stats up

{projectDir}\src\main\resources\schema.sql

TB_USER

Information stored for user, with audit information

Column	Data Type	Length	Remark
ID	string	100	- Primary Key - Unique
LOGIN	string	100	- Unique - Not Null
NAME	string	255	- Not Null
SALARY	number	2 decimal place	- Not Null
START_DATE	date		- Not Null - supported format: yyyy-MM-dd, dd-MMM-yy
VERSION	number
CREATED_BY	string	255
CREATED_DATE	timestamp
UPDATED_BY	string	255
UPDATED_DATE	timestamp

TB_USER_REV

Record versioning for User

Column	Data Type	Length	Remark
REVISION_ID	integer		- Primary Key - Not Null
REVISION_TYPE	integer		- 0 = insert - 1 = update - 2 = delete - Not Null
ID	string	100	- Primary Key - Not Null
LOGIN	string	100	Not Null
NAME	string	255	Not Null
SALARY	number	2 decimal	Not Null
START_DATE	date	Not Null
CREATED_BY	string	255
CREATED_DATE	timestamp
UPDATED_BY	string	255
UPDATED_DATE	timestamp

REV_INFO

Revision changes with time stamp info

Column	Data Type	Length	Remark
REVISION_ID	integer		- Primary Key
REV_TIMESTAMP	integer		- time stamp in number for revision info updated

Local Machine Hosting

http://localhost:8080

Software for testing

Postman https://www.postman.com/

Webservice

Available end points

Name	URL	Request method	Response Type
Upload API	/users/upload	POST	application/json
Fetch API	/users	GET	application/json
Get API	/users/{$id}	GET	application/json
Create API	/users/	POST	application/json
Update API	/users/	PUT/PATCH	application/json
Delete API	/users/	DELEE	application/json

Upload API

POST http://localhost:8080/users/upload

Parameter Name	Data Type	Type	Remark
file	multipart/form-data	Request Parameter	required max 10mb comma delimited first row is header supported column: id,login,name,salary,startDate all columns are mandatory id and login to be unique startDate - supported format: yyyy-MM-dd, dd-MMM-yy

Fetch API

GET http://localhost:8080/users

Parameter Name	Data Type	Type	Remark
minSalary	decimal	request parameter	optional default value when not provided: 0 value inclusive parameter is case sensitive, not applied when wrong case
maxSalary	decimal	request parameter	optional default value when not provided: 4000 value not inclusive parameter is case sensitive, not applied when wrong case
offset	integer	request parameter	optional default value when not provided: 0 parameter is case sensitive, not applied when wrong case
limit	integer	request parameter	optional default value when not provided: no limit parameter is case sensitive, not applied when wrong case max records 2147483647, integer max value
startDateFrom	string	request parameter	optional value inclusive accepted format: yyyy-mm-dd, dd-MMM-yy parameter is case sensitive, not applied when wrong case
startDateTo	string	request parameter	optional value inclusive accepted format: yyyy-mm-dd, dd-MMM-yy parameter is case sensitive, not applied when wrong case
id	string	request parameter	optional filter value is case insensitive wild card match by default custom wild card value "#" i.e #search , search# parameter is case sensitive, not applied when wrong case
login	string	request parameter	optional filter value is case insensitive wild card match by default custom wild card value "#" i.e #search , search# parameter is case sensitive, not applied when wrong case
name	string	request parameter	optional filter value is case insensitive wild card match by default custom wild card value "#" i.e #search , search# parameter is case sensitive, not applied when wrong case
sort	string	request parameter	optional comma separated support single column sort support coloumn: id, login, name, salary, startDate column name case insensitive format: column,sort order i.e id,asc sort order: asc or desc default when sort order not provided: asc ignore when column is not valid parameter is case sensitive, not applied when wrong case

Get API

GET http://localhost:8080/users/{$id}

Parameter Name	Data Type	Type	Remark
{$id}	string	Path Variable	Required

Response

Content	Data Type	Type	Remark
{ "id": "e0001", "name": "Harry Potter", "login": "hpotter", "salary": 1234.00, "startDate": "2001-11-16" }	application/json	Response Body	http status : 200
{"message": "No such employee"}	application/json	Response Body	http status : 400

Create API

POST http://localhost:8080/users/

Parameter Name	Data Type	Type	Remark
{ "id": "e0001", "name": "Harry Potter", "login": "pharry", "salary": 399, "startDate": "2020-01-10" }	string	Request Body	Required

Response

Content	Data Type	Type	Remark
{"message": "Successfully created"}	application/json	Response Body	http status : 201
{"message": "Employee ID already exists"} {"message": "Employee login not unique"} {"message": "Invalid salary"} {"message": "Invalid date"}	application/json	Response Body	http status : 400

Update API

PUT/PATCH http://localhost:8080/users/${id}

Request

Parameter Name	Data Type	Type	Remark
{$id}	string	Path Variable	Required
{ "id": "e0001", "name": "Harry Potter", "login": "pharry", "salary": 399, "startDate": "2020-01-10" }	string	Request Body	Required

Response

Content	Data Type	Type	Remark
{"message": "Successfully updated"}	application/json	Response Body	http status : 200
{"message": "No such employee"} {"message": "Employee login not unique"} {"message": "Invalid salary"} {"message": "Invalid date"}	application/json	Response Body	http status : 400

Delete API

DELETE http://localhost:8080/users/${id}

Request

Parameter Name	Data Type	Type	Remark
{$id}	string	Path Variable	Required

Response

Content	Data Type	Type	Remark
{"message": "Successfully deleted"}	application/json	Response Body	http status : 200
{"message": "No such employee"}	application/json	Response Body	http status : 400

Assumption

• Only to serve web service
• All API return responses are in JSON format
• Allow different error message structure on top specification
• Preset data not required
• Request parameter name is case sensitive
• All request parameter, request body is provided
• Request body/data is correctly formed
• Local timezone refer to Singapore timezone
• User Story 1
  • Validation required against database to check if id or login is unique
  • CSV provided complies to specification of 5 columns id,login,name,salary,startDate
• User Story 2
  • Request parameter will be default value when not provided
  • Additional parameter is not used when not provided
  • Allow only single column sorting
  • "no limit" refer to 2147483647 rows, max value of Integer