What I learnt during the internship...
Setting up Knex in a controller
Creating a controller that will recieve the file (in JSON format) and send it to the query builder that will return the results of the query in the server response.
import { Router, Request, Response, NextFunction } from 'express';
[...]
import { QueryBuilder } from '../models/query-builder';
// Form validation with Joi + express-joi-validator
const expressJoi = require('express-joi-validator');
import { querySchema } from '../models/query-validator';
// Express Router
const router: Router = Router({ mergeParams: true });
/**
* Route used to send search filters to the DatabaseFilterService
* @Response : JSON file containing search results
*/
router.post('/query/:table', expressJoi(querySchema), async (req: Request, res: Response, next: NextFunction) => {
const request = <DtlRequest>req.body;
// Grabbing the table ID from the url:
request.table = req.params.table;
// Initiating queryBuilder with the JSON file passed from the user interface.
const qb = new QueryBuilder(request);
// Catch any errors or the result in order to return it in the response.
let result: any;
try {
result = await qb.query;
} catch (err) {
console.log(qb.query.toSQL());
next(err);
}
// Respond with a json containing the response from the service
res.status(200).json({
tableId: req.params.table,
query: qb.query.toSQL(),
body: result
});
});
export const RootController: Router = router;
This controller sends the JSON file to the DatabaseFilterService where it is to be translated into a knex request.
Executing a database query with knex
Creating a request with knex is pretty straightforward once the knex object has been initialised (here as "db") you can then add conditions to the query via many different methods belonging to knex such as .select(), .offset(), .limit() and many others that are very well documented.
import { db } from '../utils/knex-db';
[...]
return db.select().table("table").limit(integer);
Chaining multiple conditions to a query:
import { db } from '../utils/knex-db';
[...]
this.query = db(this.request.table);
[...] // chaining methods to the query
this.query = this.query.offset(this.request.offset);
this.query = this.query.select(field);
Recursive knex query and JSON parsing:
The concept
Here is a JSON file generated upon submission of the form and that I will need to parse in order to translate the request with knex. The different where clauses are embedded into each other and the amount of possible clauses can be unlimited.
My project manager let me dwell on how to solve this problem for a while, since this cannot be solved by using a for-each statement maps or filters.
For this problem I would need to use a recursive function, or a function that calls upon itself until it hits an end point.
This is used a lot when parsing through embedded comment chains in forums and websites.
In my form Users have the capacity of adding conditions in a recursive manor so as to be able to create complexe requests to the database.
The user interface
User interface allowing for users to create new groups in a recursive manor, the user has the ability to nest clauses inside of each other. The real challenge was to make it easy to verify the recursive properties of the file.
The service that parses the JSON
This is the finished function that parses the data in the file and returns a query.
private parseWhere(query: any, whereGroup: DtlWhereGroup) {
if (whereGroup) {
const link = whereGroup.link;
// Go through all DtlWhereGroup objects in conditions:
const conditions: (DtlWhereGroup | DtlWhereCondition)[] = [];
if (!isNull(link)) {
whereGroup.conditions.forEach(newWhereGroup => {
conditions.push(newWhereGroup);
});
// Parse each new DtlWhereGroup:
conditions.forEach(newWhereGroup => {
// Seperate the clauses (field, operation, value) from the newWhereGroup (link, objects)
query = this.parseNewWhereGroup(newWhereGroup, link, query);
});
}
}
return query;
}
// Recursive transform whereObject into query
public parseNewWhereGroup(newWhereGroup: any, operator: string, query: any) {
// Check if DtlWhereGroup is another DtlWhereGroup
if (this.identifyWhereGroup(newWhereGroup) == "unknown") {
if (operator == "or") {
// Recursive, pass new DtlWhereGroup objects through main parseWhere();
query.orWhere((query: any) => { this.parseWhere(query, newWhereGroup); });
} else {
query.where((query: any) => { this.parseWhere(query, newWhereGroup); });
}
// Check if DtlWhereGroup is a clause: (field, operation, value)
} else if (this.identifyWhereGroup(newWhereGroup) == "condition") {
const clause = this.formatClause(newWhereGroup.field, newWhereGroup.operation, newWhereGroup.value);
(operator == "or") ? query.orWhere(clause.field, clause.operation, clause.value) : query.where(clause.field, clause.operation, clause.value);
}
return query;
}
/**
* Checks for operation,
* uses RegEx to determine the operator
* @param whereObject : DtlWhereGroup
*/
public getOperation(whereObject: DtlWhereGroup) {
if (whereObject != null) {
return whereObject.link;
}
return null;
}
/**
* Check if object is (condition or whereObject)
* @param newWhereGroup
*/
public identifyWhereGroup(newWhereGroup: DtlWhereGroup) {
if (newWhereGroup != null) {
const keys = Object.keys(newWhereGroup);
return (keys[0] == "field") ? "condition" : "unknown";
}
}
Git Conflict resolution with git kraken is easy
I really enjoyed working with gitKraken software as it makes using git flow and resolving conflict resolutions a lot easier
Recursive Schema validation with Joi
Using Joi to verify the information sent from the form to the server is important to ensure the quality of the information sent to the server and prevents errors due to incorrect form entries and malicious injections
In order to check the information sent to the server I started off by creating a template that will be a comparison in order to check the incomming data from the request.
The difficulty here was to create a schema that could support the recursive inputs of the json in the "where clause". I solved this by making a "clause" schema that would call upon itself in a lazy manor:
const clause = Joi.alternatives().try([
{ link: Joi.string(), conditions: Joi.array().items(Joi.lazy(() => clause)) },
condition
]);
This would enable me to validate the JSON where the clause below as well as checking if fields are null.
JSON
{
"select": [
{
"field": "mnemo",
"as": "MNEMO",
"aggregate": null
},
{
"field": "longueur",
"as": "longueur_moy",
"aggregate": "avg"
},
{
"field": "longueur",
"as": "longueur_max",
"aggregate": "max"
},
{
"field": "longueur",
"as": "longueur_min",
"aggregate": "min"
}
],
"where": {
"link": "or",
"conditions": [
{
"link": "and",
"conditions": [
{
"field": "index",
"operation": ">",
"value": "90"
},
{
"field": "index",
"operation": "<",
"value": "100"
}
]
},
{
"link": "and",
"conditions": [
{
"link": "and",
"conditions": [
{
"field": "index",
"operation": ">",
"value": "590"
},
{
"field": "index",
"operation": "<",
"value": "600"
}
]
},
{
"link": "or",
"conditions": [
{
"link": "and",
"conditions": [
{
"field": "longueur",
"operation": "<",
"value": "5"
},
{
"field": "libelleobstacle",
"operation": "substring",
"value": "chemin"
}
]
},
{
"link": "and",
"conditions": [
{
"field": "longueur",
"operation": ">",
"value": "5"
},
{
"field": "libelleobstacle",
"operation": "substring",
"value": "rural"
}
]
}
]
}
]
}
]
},
"orderBy": [],
"limit": 4,
"offset": 4
}
Joi Schema
const Joi = require('@hapi/joi');
const condition = Joi.object({
field: Joi.string(),
operation: Joi.string(),
value: Joi.alternatives().try([Joi.string(), Joi.number()])
});
const clause = Joi.alternatives().try([
{ link: Joi.string(), conditions: Joi.array().items(Joi.lazy(() => clause)) },
condition
]);
const selectItem = Joi.object({
field: Joi.string(),
as: Joi.string(),
aggregate: Joi.alternatives().try([Joi.string(), null])
});
export const querySchema = {
body: {
select: Joi.array().items(selectItem),
where: clause,
orderBy: Joi.array().items([{ sort: Joi.string(), column: Joi.string() }]),
limit: Joi.number(),
offset: Joi.number()
}
};
Here is an example of the joi validator rejecting a null input and returning an error in the response:
