db.js

'use strict';

var env = process.env;
var fs = require('fs');
var path = require('path');
var models = require('./models.js');
var Sequelize = require('sequelize');
var parse = require('csv-parse');
var log = console.log.bind(console);

var fileIsNew = function (p) {
	return new Promise(function (resolve, reject) {
		models.File.findAll({where: {originalName: path.basename(p)}}).then(function (res) {
			if (res && res.length == 0) {
				resolve(true);
			} else {
				reject(false);
			}
		});
	});
};

var createFile = function (p) {
	var fileName = path.basename(p);
	return models.File.create({
		name: fileName,
		originalName: fileName,
		status: 'Queued',
		byteSize: fs.statSync(p).size,
		sourceDirectory: path.dirname(p)
	});
};

var augment = function (file, flie) {
	return models.Layout.create({
		encoding: flie.encoding,
		fieldDelimiter: flie.fieldDelimiter,
		format: flie.format,
		header: flie.header,
		trailer: flie.trailer,
		notes: flie.notes,
		recordDelimiter: flie.recordDelimiter
	}).then(function (layout) {
		file.columnCount = flie.fields.length;
		file.layout = layout;
		file.status = 'Loading';
		file.save();
		flie.fields.forEach(function (field) {
			field.layout = layout;
			models.Field.create(field);
		});
		return file;
	});
};

var sequelize = new Sequelize(env.FILE_DB_NAME, env.FILE_DB_USER, env.FILE_DB_PASS, {
	host: env.FILE_DB_HOST,
	port: env.FILE_DB_PORT,
	dialect: 'mssql',
	pool: {
		max: 5,
		min: 0,
		idle: 10000
	}
});

var testConnection = function () {
	return new Promise(function (resolve, reject) {
		sequelize
			.authenticate()
			.then(function () {
				log('Files DB Connection has been established successfully.');
				return resolve(true);
			})
			.catch(function (err) {
				log('Unable to connect to the Files database:', err);
				return reject(err);
			});
	});
};

var upload = function (p, file, flie) {
	return new Promise(function (resolve, reject) {
		var Table, cols;
		cols = {};

		// Using SequelizeJS here isn't the most computationally
		// efficient, but it lets us use a single API, and the DB will
		// most likely still be the bottleneck.

		flie.fields.forEach(function (field) {
			// Potential FLIE types
			// 	'alpha', 'digit', 'punct', 'empty', 'alphanum', 'string', 'number'
			// "number" # could have '-' and '.' in it

			// FLIE doesn't provide enough detail to tell numeric types apart,
			// so to avoid data loss, just keep it as a string.
			//
			// Also it appears that we don't need anything more than strings,
			// so avoid any extra processing
			cols[field.header] = Sequelize.STRING(parseInt(field.maxLength) + 10);
		});

		Table = sequelize.define('file_' + file.id, cols);

		Table.sync().then(function () {
			var parser, input;

			parser = parse({delimiter: flie.fieldDelimiter});

			var makeFormatter = function(cols) {
				var f = function(cols, data) {
					var d = {};
					cols.forEach(function (header, index) {
						d[header] = data[index];
					});
					return d;
				};
				return f.bind(undefined, cols);
			};

			var makeReader = function (Table) {
				var firstRun, f;
				firstRun = true;
				return function () {
					var record = false;
					if (firstRun == true) {
						firstRun = false;
						f = makeFormatter(parser.read());
					}
					while ((record = parser.read())) {
						Table.create(f(record));
					}
				};
			};

			parser.on('readable', makeReader(Table));

			parser.on('error', function(err) {
				log(err.message);
				reject(err);
			});

			parser.on('finish', function() {
				log('Finished uploading table file_' + file.id);
			});

			input = fs.createReadStream(p);
			input.pipe(parser);

			resolve(Table);
		});
	});

};

var uploadComplete = function (file, Table) {
	return new Promise(function(resolve, reject) {
		Table.findAllAndCountAll().then(function (count) {
			file.status = 'Loaded';
			file.recordCount = count;
			resolve(file.save());
		});
	});
};

exports.testConnection = testConnection;
exports.fileIsNew = fileIsNew;
exports.createFile = createFile;
exports.augment = augment;
exports.upload = upload;
exports.uploadComplete = uploadComplete;