lib/sqlHandler.js
'use strict'
// http://docs.sequelizejs.com/en/latest/
var Sequelize = require('sequelize')
var DataTypes = Sequelize.DataTypes
var async = require('async')
var crypto = require('crypto')
var debug = require('debug')('jsonApi:store:relationaldb')
var Joi = require('joi')
var semver = require('semver')
var _ = require('lodash')
var util = require('util')
var modelGenerators = {
Postgres: require('./modelGenerators/postgres'),
Default: require('./modelGenerators/default')
}
var MIN_SERVER_VERSION = '5.6.5'
var Op = Sequelize.Op
class SqlStoreError extends Error {
constructor (properties) {
super()
Object.assign(this, properties)
}
}
var SqlStore = module.exports = function SqlStore (config) {
SqlStore._checkMinServerVersion()
this.config = config
this.handlesSort = true // Sequelize can handle sorting
this.handlesFilter = true // Sequelize can handle filtering
}
SqlStore._sequelizeInstances = Object.create(null)
/**
Handlers readiness status. This should be set to `true` once all handlers are ready to process requests.
*/
SqlStore.prototype.ready = false
SqlStore._checkMinServerVersion = function () {
var serverVersion = require('@jagql/framework')._version
if (!serverVersion) return
if (semver.lt(serverVersion, MIN_SERVER_VERSION)) {
throw new Error('This version of store-sequelize requires @jagql/framework >=' + MIN_SERVER_VERSION + '.')
}
}
/**
initialise gets invoked once for each resource that uses this hander.
In this instance, we're instantiating a Sequelize instance and building models.
*/
SqlStore.prototype.initialise = function (resourceConfig) {
var self = this
self.resourceConfig = resourceConfig
if (self.config.sequelize) {
self.sequelize = self.config.sequelize
} else {
var database = self.config.database || resourceConfig.resource
var sequelizeArgs = [database, self.config.username, self.config.password, {
operatorsAliases: false,
dialect: self.config.dialect,
dialectOptions: self.config.dialectOptions,
host: self.config.host,
port: self.config.port,
storage: self.config.storage || ':memory:',
logging: self.config.logging || require('debug')('jsonApi:store:relationaldb:sequelize'),
define: {
// Set freezeTableName on all table definitions to prevent sequelize from pluralizing
// all table names by itself.
freezeTableName: true
}
}]
// To prevent too many open connections, we will store all Sequelize instances in a hash map.
// Index the hash map by a hash of the entire config object. If the same config is passed again,
// reuse the existing Sequelize connection resource instead of opening a new one.
var md5sum = crypto.createHash('md5')
var instanceId = md5sum.update(JSON.stringify(sequelizeArgs)).digest('hex')
var instances = SqlStore._sequelizeInstances
if (!instances[instanceId]) {
var sequelize = new (Function.prototype.bind.apply(Sequelize, [null].concat(sequelizeArgs)))()
instances[instanceId] = sequelize
}
self.sequelize = instances[instanceId]
}
self._buildModels()
self.ready = true
}
SqlStore.prototype.populate = function (options, callback) {
if (typeof options === 'function') {
callback = options
options = {}
}
var self = this
var tasks = [
function (cb) {
self.baseModel.sync(options).asCallback(cb)
},
function (cb) {
async.eachSeries(Object.keys(self.relations).map((relationName) => self.relations[relationName]), function (model, ecb) {
model.sync(options).asCallback(ecb)
}, cb)
},
function (cb) {
async.eachSeries(self.resourceConfig.examples, function (exampleJson, ecb) {
var validation = Joi.validate(exampleJson, self.resourceConfig.attributes)
if (validation.error) return ecb(validation.error)
self.create({ request: { type: self.resourceConfig.resource } }, validation.value, ecb)
}, cb)
}
]
async.series(tasks, callback)
}
SqlStore.prototype._buildModels = function () {
var self = this
var localAttributes = Object.keys(self.resourceConfig.attributes).filter(function (attributeName) {
var settings = self.resourceConfig.attributes[attributeName]._settings
if (!settings) return true
return !(settings.__one || settings.__many)
})
localAttributes = _.pick(self.resourceConfig.attributes, localAttributes)
var relations = Object.keys(self.resourceConfig.attributes).filter(function (attributeName) {
var settings = self.resourceConfig.attributes[attributeName]._settings
if (!settings) return false
return (settings.__one || settings.__many) && !settings.__as
})
relations = _.pick(self.resourceConfig.attributes, relations)
var modelAttributes = { }
switch (self.config.dialect) {
case 'postgres':
modelAttributes = new modelGenerators.Postgres(self.resourceConfig, localAttributes)
break
default:
modelAttributes = new modelGenerators.Default(self.resourceConfig, localAttributes)
break
}
self.baseModel = self.sequelize.define(self.resourceConfig.resource, modelAttributes, { timestamps: false })
self.relations = { }
Object.keys(relations).forEach(function (relationName) {
var relation = relations[relationName]
var otherModel = self._defineRelationModel(relation, relationName, relation._settings.__many)
self.relations[relationName] = otherModel
})
}
SqlStore.prototype._defineRelationModel = function (relation, relationName, many) {
var self = this
var modelName = self.resourceConfig.resource + '-' + relationName
var modelProperties = {
uid: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true
},
type: {
type: new DataTypes.STRING(38),
allowNull: false
},
meta: {
type: DataTypes.STRING,
get: function () {
var data = this.getDataValue('meta')
if (!data) return undefined
return JSON.parse(data)
},
set: function (val) {
return this.setDataValue('meta', JSON.stringify(val))
}
}
}
switch (relation._settings._uidType) {
case 'uuid':
modelProperties.id = {
type: DataTypes.UUID,
allowNull: false
}
break
case 'autoincrement':
modelProperties.id = {
type: DataTypes.INTEGER,
allowNull: false,
get: function () {
return this.getDataValue('id').toString()
}
}
break
case 'string': default:
modelProperties.id = {
type: DataTypes.STRING,
allowNull: false
}
break
}
var relatedModel = self.sequelize.define(modelName, modelProperties, {
timestamps: false,
indexes: [ { fields: [ 'id' ] } ],
freezeTableName: true
})
if (many) {
self.baseModel.hasMany(relatedModel, { onDelete: 'CASCADE', foreignKey: self.resourceConfig.resource + 'Id' })
} else {
self.baseModel.hasOne(relatedModel, { onDelete: 'CASCADE', foreignKey: self.resourceConfig.resource + 'Id' })
}
return relatedModel
}
SqlStore.prototype._fixObject = function (json) {
var self = this
var resourceId = self.resourceConfig.resource + 'Id'
Object.keys(json).forEach(function (attribute) {
if (attribute.indexOf(self.resourceConfig.resource + '-') !== 0) return
var fixedName = attribute.split(self.resourceConfig.resource + '-').pop()
json[fixedName] = json[attribute]
var val = json[attribute]
delete json[attribute]
if (!val) return
if (!(val instanceof Array)) val = [ val ]
val.forEach(function (j) {
if (j.uid) delete j.uid
if (j[resourceId]) delete j[resourceId]
})
})
return json
}
SqlStore.prototype._errorHandler = function (e, callback) {
// console.log(e, e.stack);
if (e.message.match(/^ER_LOCK_DEADLOCK/)) {
return callback(new SqlStoreError({
status: '500',
code: 'EMODIFIED',
title: 'Resource Just Changed',
detail: 'The resource you tried to mutate was modified by another request. Your request has been aborted.'
}))
}
return callback(new SqlStoreError({
status: '500',
code: 'EUNKNOWN',
title: 'An unknown error has occured',
detail: 'Something broke when connecting to the database - ' + e.message
}))
}
SqlStore.prototype._generateSearchIncludes = function (relationships) {
var self = this
if (!relationships) {
return {
count: [],
findAll: Object.keys(self.relations).map(function (key) {
return self.relations[key]
})
}
}
var searchIncludes = Object.keys(self.relations).reduce(function (partialSearchIncludes, relationName) {
var model = self.relations[relationName]
partialSearchIncludes.findAll.push(model)
var matchingValue = relationships[relationName]
if (!matchingValue) return partialSearchIncludes
if (typeof matchingValue === 'string') {
matchingValue = matchingValue.split(',')
}
if (matchingValue instanceof Array) {
matchingValue = matchingValue.filter(function (i) {
return !(i instanceof Object)
})
if (!matchingValue.length) return partialSearchIncludes
} else if (matchingValue instanceof Object) {
return partialSearchIncludes
}
var includeClause = {
model: model,
where: { id: matchingValue }
}
partialSearchIncludes.count.push(includeClause)
// replace simple model with clause
partialSearchIncludes.findAll.pop()
partialSearchIncludes.findAll.push(includeClause)
return partialSearchIncludes
}, {
count: [],
findAll: []
})
return searchIncludes
}
SqlStore.prototype._generateSearchBlock = function (request) {
var self = this
var attributesToFilter = _.omit(request.processedFilter, Object.keys(self.relations))
var searchBlock = self._getSearchBlock(attributesToFilter)
return searchBlock
}
SqlStore.prototype._scalarFilterElementToWhereObj = function (element) {
var self = this
var value = element.value
var op = element.operator
if (!op) return value
if (op === '>') return { [Op.gt]: value }
if (op === '<') return { [Op.lt]: value }
var iLikeOperator = Op.like
if (self.sequelize.getDialect() === 'postgres') iLikeOperator = Op.iLike
if (op === '~') {
var caseInsensitiveEqualExpression = { }
caseInsensitiveEqualExpression[iLikeOperator] = value
return caseInsensitiveEqualExpression
}
if (op === ':') {
var caseInsensitiveContainsExpression = { }
caseInsensitiveContainsExpression[iLikeOperator] = '%' + value + '%'
return caseInsensitiveContainsExpression
}
}
SqlStore.prototype._filterElementToSearchBlock = function (filterElement) {
var self = this
if (!filterElement) return { }
var whereObjs = filterElement.map(function (scalarFilterElement) {
return self._scalarFilterElementToWhereObj(scalarFilterElement)
})
if (!whereObjs.length) return { }
if (filterElement.length === 1) {
return whereObjs[0]
}
return { [Op.or]: whereObjs }
}
SqlStore.prototype._getSearchBlock = function (filter) {
var self = this
if (!filter) return { }
var searchBlock = { }
Object.keys(filter).forEach(function (attributeName) {
var filterElement = filter[attributeName]
searchBlock[attributeName] = self._filterElementToSearchBlock(filterElement)
})
return searchBlock
}
SqlStore.prototype._dealWithTransaction = function (done, callback) {
var self = this
var transactionOptions = {
isolationLevel: Sequelize.Transaction.ISOLATION_LEVELS.READ_COMMITTED,
autocommit: false
}
self.sequelize.transaction(transactionOptions).asCallback(function (err1, transaction) {
if (err1) return done(err1)
var t = { transaction: transaction }
var commit = function () {
var args = arguments
transaction.commit().asCallback(function (err2) {
if (err2) return done(err2)
return done.apply(null, Array.prototype.slice.call(args))
})
}
var rollback = function (e) {
debug('Err', transaction.name, e)
var a = function () {
if (e instanceof Error) return self._errorHandler(e, done)
return done(e)
}
transaction.rollback().then(a, a)
}
var finishTransaction = function (err) {
if (err) return rollback(err)
return commit.apply(null, Array.prototype.slice.call(arguments))
}
return callback(t, finishTransaction)
})
}
SqlStore.prototype._clearAndSetMany = function (relationModel, prop, theResource, keyName, ucKeyName, t, taskCallback) {
var whereClause = { }
whereClause[theResource.type + 'Id'] = theResource.id
relationModel.destroy({
where: whereClause,
transaction: t.transaction
}).asCallback(function (deleteError) {
if (deleteError) return taskCallback(deleteError)
async.map(prop, function (item, acallback) {
relationModel.create(item, t).asCallback(function (err, newRelationModel) {
if (err) return acallback(err)
theResource['add' + ucKeyName](newRelationModel, t).asCallback(acallback)
})
}, taskCallback)
})
}
SqlStore.prototype._clearAndSetOne = function (relationModel, prop, theResource, keyName, ucKeyName, t, taskCallback) {
var whereClause = { }
whereClause[theResource.type + 'Id'] = theResource.id
relationModel.destroy({
where: whereClause,
transaction: t.transaction
}).asCallback(function (deleteError) {
if (deleteError) return taskCallback(deleteError)
if (!prop) {
theResource['set' + ucKeyName](null, t).asCallback(taskCallback)
} else {
relationModel.create(prop, t).asCallback(function (err, newRelationModel) {
if (err) return taskCallback(err)
theResource['set' + ucKeyName](newRelationModel, t).asCallback(taskCallback)
})
}
})
}
SqlStore.prototype._clearAndSetRelationTables = function (theResource, partialResource, t, callback) {
var self = this
var tasks = { }
Object.keys(self.relations).forEach(function (relationName) {
var prop = partialResource[relationName]
if (!partialResource.hasOwnProperty(relationName)) return
var relationModel = self.relations[relationName]
var keyName = self.resourceConfig.resource + '-' + relationName
var ucKeyName = keyName[0].toUpperCase() + keyName.slice(1, keyName.length)
tasks[relationName] = function (taskCallback) {
if (prop instanceof Array) {
self._clearAndSetMany(relationModel, prop, theResource, keyName, ucKeyName, t, taskCallback)
} else {
self._clearAndSetOne(relationModel, prop, theResource, keyName, ucKeyName, t, taskCallback)
}
}
})
async.parallel(tasks, callback)
}
SqlStore.prototype._generateSearchOrdering = function (request) {
if (!request.params.sort) return undefined
var attribute = request.params.sort
var order = 'ASC'
attribute = String(attribute)
if (attribute[0] === '-') {
order = 'DESC'
attribute = attribute.substring(1, attribute.length)
}
return [ [ attribute, order ] ]
}
SqlStore.prototype._generateSearchPagination = function (request) {
var page = request.params.page
if (!page) return undefined
return {
limit: page.limit,
offset: page.offset
}
}
/**
Search for a list of resources, given a resource type.
*/
SqlStore.prototype.search = function (request, callback) {
var self = this
var options = { }
var where = self._generateSearchBlock(request)
if (where) {
options.where = where
}
var includeBlocks = self._generateSearchIncludes(request.params.filter)
debug('includeBlocks', util.inspect(includeBlocks, { depth: null }))
if (includeBlocks.count.length) {
options.include = includeBlocks.count
}
self.baseModel.count(options).asCallback(function (ignoredErr, count) {
debug('Count', count)
if (includeBlocks.findAll.length) {
options.include = includeBlocks.findAll
}
var order = self._generateSearchOrdering(request)
if (order) {
options.order = order
}
var pagination = self._generateSearchPagination(request)
if (pagination) {
if (pagination.offset > 0 || pagination.limit <= count) {
_.assign(options, pagination)
}
}
self.baseModel.findAll(options).asCallback(function (err, result) {
debug(options, err, JSON.stringify(result))
if (err) return self._errorHandler(err, callback)
var records = result.map(function (i) { return self._fixObject(i.toJSON()) })
debug('Produced', JSON.stringify(records))
return callback(null, records, count)
})
})
}
/**
Find a specific resource, given a resource type and an id.
*/
SqlStore.prototype.find = function (request, callback) {
var self = this
var whereClause = { id: request.params.id }
var whereExtras = self._generateSearchBlock(request)
whereClause = _.merge({ id: request.params.id }, whereExtras)
self.baseModel.findOne({
where: whereClause,
include: Object.keys(self.relations).map((relationName) => self.relations[relationName])
}).asCallback(function (err, theResource) {
if (err) {
// We get a 22P02 error if the UUID format is wrong
if (err.original.code === '22P02') {
return callback(new SqlStoreError({
status: '404',
code: 'ENOTFOUND',
title: 'Requested resource does not exist',
detail: 'The' + request.params.id + ' for ' + request.params.type + ' is of wrong format. (Make sure it is UUID)'
}))
}
return self._errorHandler(err, callback)
}
// If the resource doesn't exist, error
if (!theResource) {
return callback(new SqlStoreError({
status: '404',
code: 'ENOTFOUND',
title: 'Requested resource does not exist',
detail: 'There is no ' + request.params.type + ' with id ' + request.params.id
}))
}
theResource = self._fixObject(theResource.toJSON())
debug('Produced', JSON.stringify(theResource))
return callback(null, theResource)
})
}
/**
Create (store) a new resource give a resource type and an object.
*/
SqlStore.prototype.create = function (request, newResource, finishedCallback) {
var self = this
self._dealWithTransaction(finishedCallback, function (t, finishTransaction) {
if (newResource.id === 0) {
delete newResource.id
} else if (self.config.dialect === 'postgres') {
if (!isNaN(_.toNumber(newResource.id))) {
/*
If _.toNumber(newResource.id) is NaN, it means
it is 'DEFAULT', which is fine.
If it **is** a number, then we need to
update the sequence
*/
self.sequelize.query(`ALTER SEQUENCE "${newResource.type}_id_seq" RESTART WITH ${parseInt(newResource.id) + 1};`)
.asCallback(function (err5, result) {})
}
}
self.baseModel.create(newResource, t).asCallback(function (err2, theResource) {
if (err2) return finishTransaction(err2)
self._clearAndSetRelationTables(theResource, newResource, t, function (err) {
if (err) return finishTransaction(err)
return finishTransaction(null, theResource)
})
})
})
}
/**
Delete a resource, given a resource type and and id.
*/
SqlStore.prototype.delete = function (request, finishedCallback) {
var self = this
self._dealWithTransaction(finishedCallback, function (t, finishTransaction) {
self.baseModel.findAll({
where: { id: request.params.id },
include: Object.keys(self.relations).map((relationName) => self.relations[relationName])
}).asCallback(function (findErr, results) {
if (findErr) {
// We get a 22P02 error if the UUID format is wrong
if (findErr.original.code === '22P02') {
return finishTransaction({
status: '404',
code: 'ENOTFOUND',
title: 'Requested resource does not exist',
detail: 'The' + request.params.id + ' for ' + request.params.type + ' is of wrong format. (Make sure it is UUID)'
})
}
return finishTransaction(findErr)
}
var theResource = results[0]
// If the resource doesn't exist, error
if (!theResource) {
return finishTransaction({
status: '404',
code: 'ENOTFOUND',
title: 'Requested resource does not exist',
detail: 'There is no ' + request.params.type + ' with id ' + request.params.id
})
}
theResource.destroy(t).asCallback(function (deleteErr) {
return finishTransaction(deleteErr)
})
})
})
}
/**
Update a resource, given a resource type and id, along with a partialResource.
partialResource contains a subset of changes that need to be merged over the original.
*/
SqlStore.prototype.update = function (request, partialResource, finishedCallback) {
var self = this
self._dealWithTransaction(finishedCallback, function (t, finishTransaction) {
self.baseModel.findOne({
where: { id: request.params.id },
include: Object.keys(self.relations).map((relationName) => self.relations[relationName]),
transaction: t.transaction
}).asCallback(function (err2, theResource) {
if (err2) {
// We get a 22P02 error if the UUID format is wrong
if (err2.original.code === '22P02') {
return finishTransaction({
status: '404',
code: 'ENOTFOUND',
title: 'Requested resource does not exist',
detail: 'The' + request.params.id + ' for ' + request.params.type + ' is of wrong format. (Make sure it is UUID)'
})
}
return finishTransaction(err2)
}
// If the resource doesn't exist, error
if (!theResource) {
return finishTransaction({
status: '404',
code: 'ENOTFOUND',
title: 'Requested resource does not exist',
detail: 'There is no ' + request.params.type + ' with id ' + request.params.id
})
}
self._clearAndSetRelationTables(theResource, partialResource, t, function (err) {
if (err) return finishTransaction(err)
theResource.update(partialResource, t).asCallback(function (err3) {
if (err3) return finishTransaction(err3)
return finishTransaction(null, partialResource)
})
})
})
})
}