return new Promise(function(resolve, reject) {
pg.connect("postgres://localhost/liveandgov_test", function(err, client, done) {
if (err) {
return console.error('error fetching client from pool', err);
}
// if (typeof preparedStatements[statement] === 'function') {
// statement = preparedStatements[statement](values);
// } else {
// statement = _.merge(preparedStatements[statement], {values: values});
// }
statement = (typeof preparedStatements[statement] === 'function') ?
(preparedStatements[statement](values)) : _.merge(preparedStatements[statement], {values: values});
console.log(statement);
client.query(statement, function(err, result) {
console.log(statement);
if (err) {
return console.error('error running query', err);
}
console.log(result);
resolve(result);
done();
});
});
// pg.end();
});
// read the objects satisfying a given condition
function readObjectsFor(table, column, id, objectid, read, callback) {
pg.connect(connectionString, // try to connect to the database
function (error, database, done) {
if (error) return callback(error); // if there was an error, return it
var querystring = selectFrom( // generate the read query string
table, [column], map(id, objectid)
);
query(database, done, querystring, // query the database
function (error, result) {
if (error) return callback(error); // if there was an error, return it
if (!result.length) return callback(SUCCESS, []); // if no matches were found, return an error
var ids = result;
var objects = []; // create an empty array to store objects
readObjects(column, objects, ids, read, // read the requested objects into arrays
function (error, result) {
if (error) return callback(error); // if there was an error, return it
return callback(SUCCESS, result); // otherwise, return the result of the query
}
);
}
);
}
);
}
var server = http.createServer(function(req, res) {
var client = new pg.Client({
user: "******",
password: "******",
database: "d9vled6ah1g453",
port: 5432,
host: "ec2-107-21-223-147.compute-1.amazonaws.com",
ssl: true
});
client.connect();
try{
client.query(queryObject,callback);
}catch(err){
//errCallback(err);
console.log(err);
}
// get a pg client from the connection pool
pg.connect(conString, function(err, client, done) {
console.log(Object.keys(pg.pools.all)); //["{}"]
console.log(pg.pools.getOrCreate(conString));
var handleError = function(err) {
console.log(err);
// no error occurred, continue with the request
if(!err) return false;
// An error occurred, remove the client from the connection pool.
// A truthy value passed to done will remove the connection from the pool
// instead of simply returning it to be reused.
// In this case, if we have successfully received a client (truthy)
// then it will be removed from the pool.
done(client);
res.writeHead(500, {'content-type': 'text/plain'});
res.end('An error occurred');
return true;
};
// record the visit
client.query('INSERT INTO visit (date) VALUES ($1)', [new Date()], function(err, result) {
// handle an error from the query
if(handleError(err)) return;
// get the total number of visits today (including the current visit)
client.query('SELECT COUNT(date) AS count FROM visit', function(err, result) {
// handle an error from the query
if(handleError(err)) return;
// return the client to the connection pool for other requests to reuse
done();
res.writeHead(200, {'content-type': 'text/plain'});
res.end('You are visitor number ' + result.rows[0].count);
});
});
});
})
Jobs.create = function(jobData, processIn, done) {
pg.connect(dbConnString, function(err, db, releaseClient) {
var doneAndRelease = function(err) {
releaseClient();
done(err);
};
if (err) return doneAndRelease(err);
jobsModel.write(db, null, processIn, jobData, doneAndRelease);
});
};
function(req,res){
pg.connect(conString, function(err, client, done){
//Return if an error occurs
if(err) {
//TODO respond with error code
console.error('error fetching client from pool', err);
}
//querying database
var sql = 'SELECT country.id, country.code_iso_alfa2, country.code_iso_alfa3, country.code_iso_num, country.name_iso, country.common_name, country.comment, country.citizenship, country.entity, country.entity_code_iso_alfa2 FROM geo_object.country country LEFT JOIN geo_object.continent continent ON country.continent_id = continent.id WHERE country.erased=false AND continent.code ilike ';
sql += "'" + req.params.code + "'";
sql += " ORDER BY common_name";
var responseArray = [];
client.query(sql, function(err, result) {
//Return if an error occurs
if(err) {
console.error('error fetching client from pool', err);
}
// Storing result in an array
result.rows.forEach(
function(data) {
var dto = {
id: data.id,
code_iso_alfa2: data.code_iso_alfa2,
code_iso_alfa3: data.code_iso_alfa3,
code_iso_num: data.code_iso_num,
name_iso: data.name_iso,
common_name: data.common_name,
comment: data.comment,
citizenship: data.citizenship,
entity: data.entity,
entity_code_iso_alfa2: data.entity_code_iso_alfa2,
name: data.name,
_links: {
continent: {
href: 'http://'+config.host+':' + config.port + "/continents/" + req.params.code,
type: 'application/json'
},
country: {
href: 'http://'+config.host+':' + config.port + "/countries/" + data.code_iso_alfa3,
type: 'application/json'
}
}
};
responseArray.push(dto);
}
);
done(); //release the pg client back to the pool
var model = {
"org.geoobject.model.Country": responseArray
};
res.json(model);
});
});
});
Jobs.process = function(userJobIterator) {
shouldStillProcess = true;
pg.connect(dbConnString, connected);
function connected(err, db, releaseClient) {
if (err) throw(err);
async.whilst(
// test
function() {return shouldStillProcess;},
// iterator func
iterator,
// called when test fails and execution ceases
function() {
releaseClient();
Jobs.eventEmitter.emit('stopProcess');
});
function iterator(cb) {
setTimeout(function() {
//console.log('getting jobs');
var tx = new Transaction(db);
tx.on('error', cb);
tx.begin();
Jobs.eventEmitter.emit('maybeServiceJob');
jobsModel.nextToProcess(tx, gotResult);
function gotResult(err, job) {
if (err) return cb(err);
if (!job) {
return tx.commit(function() {
Jobs.eventEmitter.emit('drain');
cb();
});
}
return serviceJob(tx, job, userJobIterator, serviceJobHandler);
}
function serviceJobHandler(err) {
if (err) {
tx.rollback();
} else {
tx.commit();
Jobs.eventEmitter.emit('processCommitted');
}
cb(err);
}
}, pollInterval);
}
}
};
module.exports = express.Router().get('/', function(request, response) {
pg.connect(connection, function(error, client, release) {
if (error) throw error;
else {
client.query(select, function(error, result) {
release();
if (error) throw error;
else response.send(result.rows);
});
}
});
});
Support.Seed = function(tableName, cb) {
pg.connect(Support.Config, function(err, client, done) {
createRecord(tableName, client, function(err) {
if(err) {
done();
return cb(err);
}
done();
cb();
});
});
};
function getConnection() {
function tryCatch(fn) {
try { fn(); }
catch (e) {}
}
var close;
return pg.connectAsync(connectionString).spread(function(client, done) {
close = done;
return client;
}).disposer(function() {
if (typeof close === 'function') tryCatch(close);
});
}
/*
* object CRUD functions
* */
// create a new object in the database
function createObject(table, fields, returnField, callback) {
pg.connect(connectionString, // try to connect to the database
function (error, database, done) {
if (error) return callback(error); // if there was an error, return it
var querystring = insertInto(table, fields, returnField); // generate the isnert query
query(database, done, querystring, // query the database
function (error, result) {
if (error) return callback(error); // if there was an error, return it
return callback(SUCCESS, result[0][ID]); // otherwise, return the id of the object created
}
);
}
);
}
// destroy an object in the database
function destroyObject(table, id, objectid, callback) {
pg.connect(connectionString, // try to connect to the database
function (error, database, done) {
if (error) return callback(error); // if there was an error, return it
var querystring = deleteFrom(table, map(id, objectid)); // generate the delete query string
query(database, done, querystring, // query the database
function (error, result) {
if (error) return callback(error); // if there was an error, return it
return callback(SUCCESS, objectid); // otherwise, return the id of the destroyed object
}
);
}
);
}
function(req,res){
pg.connect(conString, function(err, client, done){
//Return if an error occurs
if(err) {
//TODO respond with error code
console.error('error fetching client from pool', err);
}
//querying database
var sql = 'SELECT id, code_iso_alfa2, code_iso_alfa3, code_iso_num, name_iso, common_name, comment, citizenship, entity, entity_code_iso_alfa2 FROM geo_object.country WHERE erased=false AND code_iso_alfa3 ilike ';
sql += "'" + req.params.code_iso_alfa3 + "'";
sql += " ORDER BY common_name";
console.log(sql);
client.query(sql, function(err, result) {
//Return if an error occurs
if(err) {
console.error('error fetching client from pool', err);
}
if(!result.rows[0])
res.send(404);
else{
var dto = {
id: result.rows[0].id,
code_iso_alfa2: result.rows[0].code_iso_alfa2,
code_iso_alfa3: result.rows[0].code_iso_alfa3,
code_iso_num: result.rows[0].code_iso_num,
name_iso: result.rows[0].name_iso,
common_name: result.rows[0].common_name,
comment: result.rows[0].comment,
citizenship: result.rows[0].citizenship,
entity: result.rows[0].entity,
entity_code_iso_alfa2: result.rows[0].entity_code_iso_alfa2,
_links: {
country: {
rel : 'self',
href: 'http://'+config.host+':' + config.port + "/countries/" + result.rows[0].code_iso_alfa3,
type: 'application/json'
}
}
};
var model = {
"org.geoobject.model.Country" : dto
}
res.json(model);
}
done(); //release the pg client back to the pool
});
});
});
Support.Teardown = function(tableName, cb) {
pg.connect(Support.Config, function(err, client, done) {
dropTable(tableName, client, function(err) {
if(err) {
done();
return cb(err);
}
adapter.teardown('test', function(err) {
done();
cb();
});
});
});
};
// unjoins two rows in the database
function unjoinObject(table, primaryid, primaryobjectid, secondaryid, secondaryobjectid, callback) {
pg.connect(connectionString,
function (error, database, done) {
if (error) return callback(error);
var condition = map(primaryid, primaryobjectid) + ' AND ' +
map(secondaryid, secondaryobjectid);
var querystring = deleteFrom(table, condition);
query(database, done, querystring,
function (error, result) {
if (error) return callback(error);
return callback(SUCCESS, secondaryobjectid);
}
);
}
);
}
// update a single field in the database
function updateField(table, column, field, id, objectid, callback) {
pg.connect(connectionString, // try to connect to the database
function (error, database, done) {
if (error) return callback(error); // if there was an error, return it
var querystring = updateWhere( // generate the update query string
table, [column], [field], map(id, objectid)
);
query(database, done, querystring, // query the database
function (error, result) {
if (error) return callback(error); // if there was an error, return it
return callback(SUCCESS, result); // otherwise, return the object
}
);
}
);
}
// read an object from the database
function readObject(table, column, field, callback) {
pg.connect(connectionString, // try to connect to the database
function (error, database, done) {
if (error) return callback(error); // if there was an error, return it
var querystring = selectFrom( // generate the select query
table, ALL_COLUMNS, map(column, field)
);
query(database, done, querystring, // query the database
function (error, result) {
if (error) return callback(error); // if there was an error, return it
if (!result.length) return callback(DOES_NOT_EXIST); // if there were no matches, return the error
return callback(SUCCESS, result[0]); // otherwise, return the result of the query
}
);
}
);
}
// authenticate a user
function authenticate(table, id, idcolumn, idfield, passcolumn, passfield, callback) {
pg.connect(connectionString, // try to connect to the database
function (error, database, done) {
if (error) return callback(error); // if there was an error, return it
var condition = map(idcolumn, idfield) + ' AND ' +
map(passcolumn, passfield);
var querystring = selectFrom(table, [id], condition); // generate the select query string
query(database, done, querystring, // query the database
function (error, result) {
if (error) return callback(error); // if there was an error, return it
if (!result.length) return callback(SUCCESS, undefined); // if no matches were found, return an error
return callback(SUCCESS, result[0][id]); // otherwise, return a success message
}
);
}
);
}
function(req,res){
pg.connect(conString, function(err, client, done){
//Return if an error occurs
if(err) {
//TODO respond with error code
console.error('error fetching client from pool', err);
}
//querying database
var sql = 'SELECT id, code, name, description, comment FROM geo_object.continent WHERE erased=false AND code ilike ';
sql += "'" + req.params.code + "'";
sql += " ORDER BY name";
console.log(sql);
client.query(sql, function(err, result) {
//Return if an error occurs
if(err) {
console.error('error fetching client from pool', err);
}
if(!result.rows[0])
res.send(404);
else{
var dto = {
id: result.rows[0].id,
code: result.rows[0].code,
name: result.rows[0].name,
description: result.rows[0].description,
comment: result.rows[0].comment,
_links: {
continent: {
rel : 'self',
href: 'http://'+config.host+':' + config.port + "/continents/" + result.rows[0].code,
type: 'application/json'
}
}
};
var model = {
"org.geoobject.model.Continent" : dto
}
res.json(model);
}
done(); //release the pg client back to the pool
});
});
});
Jobs.processNow = function(jobId, callback, done) {
pg.connect(dbConnString, connected);
var db, doneWithRelease, tx;
function connected(err, _db, releaseClient) {
if(err) return done(err);
db = _db;
doneWithRelease = function(err) {
releaseClient();
done(err);
};
tx = new Transaction(db);
tx.on('error', doneWithRelease);
tx.begin();
jobsModel.obtainLock(tx, jobId, gotResult);
Jobs.eventEmitter.emit('lockSought');
}
function gotResult(err, jobSnap) {
if (err) return doneWithRelease(err);
if (!jobSnap) {
return doneWithRelease(new Error('Could not locate job with ID: ' + jobId));
}
Jobs.eventEmitter.emit('lockObtained');
callback(null, jobSnap.data, processingComplete);
function processingComplete(err, newJobData, processIn) {
if (err !== null) {
tx.rollback();
return doneWithRelease();
} else {
updateJob(tx, jobSnap, newJobData, processIn, commitTransaction);
}
}
}
function commitTransaction() {
tx.commit();
Jobs.eventEmitter.emit('processNowCommitted');
doneWithRelease();
}
};
return Mosaic.P().then(function() {
var deferred = Mosaic.P.defer();
PG.connect(that.options.url, function(err, client, done) {
if (err) {
deferred.reject(err);
return;
}
return Mosaic.P().then(function() {
return action(client);
}).then(function(result) {
done();
return result;
}).then(function(result) {
deferred.resolve(result);
}, function(err) {
deferred.reject(err);
});
})
return deferred.promise;
})
// associate two objects in the database with each other
function joinObjects(firstTable, secondTable, firstid, secondid, callback) {
pg.connect(connectionString, // try to connect to the database
function (error, database, done) {
if (error) return callback(error); // if there was an error, return it
var fields = []; // create an empty array for storing ids
fields[fields.length] = firstid; // store the first id
fields[fields.length] = secondid; // store the second id
var querystring = insertInto( // generate the insert query
joinTables(firstTable, secondTable), fields
);
query(database, done, querystring, // query the database
function (error, result) {
if (error) return callback(error); // if there was an error, return it
return callback(SUCCESS, result); // otherwise, return the result of the query
}
);
}
);
}
// Wrap a function in the logic necessary to provision a connection
// (grab from the pool or create a client)
function spawnConnection(connectionName, logic, cb) {
var connectionObject = connections[connectionName];
if(!connectionObject) return cb(Errors.InvalidConnection);
// If the connection details were supplied as a URL use that. Otherwise,
// connect using the configuration object as is.
var connectionConfig = connectionObject.config;
if(_.has(connectionConfig, 'url')) {
connectionUrl = url.parse(connectionConfig.url);
connectionUrl.query = _.omit(connectionConfig, 'url');
connectionConfig = url.format(connectionUrl);
}
// Grab a client instance from the client pool
pg.connect(connectionConfig, function(err, client, done) {
after(err, client, done);
});
// Run logic using connection, then release/close it
function after(err, client, done) {
if(err) {
console.error("Error creating a connection to Postgresql: " + err);
// be sure to release connection
done();
return cb(err);
}
logic(client, function(err, result) {
// release client connection
done();
return cb(err, result);
});
}
}
// update an object in the database
function updateObject(table, id, object, callback) {
pg.connect(connectionString, // try to connect to the database
function (error, database, done) {
if (error) return callback(error); // if there was an error, return it
var columns = []; // create an empty array to store the columns that will be updated
var fields = []; // create an empty array to store the fields that the columns will take
for (var property in object) { // for each property of an object
columns[columns.length] = property; // save the column name
fields[fields.length] = object[property]; // save the actual field value
}
var querystring = updateWhere( // generate the update query string
table, columns, fields, map(id, object[id])
);
query(database, done, querystring, // query the database
function (error, result) {
if (error) return callback(error); // if there was an error, return it
return callback(SUCCESS, object); // otherwise, return the object
}
);
}
);
}
var pg = require('pg.js');
//var conString = "postgres://*****:*****@hostname:5432/dbname";
//it's a url, so don't forget to encode password for URL
//in production put connection string into a seperate .pgpass file
//var conString = "postgres://*****:*****@localhost:5432/nodetest";
pg.connect(conString, function(err, client, done) {
if(err) {
return console.error('error fetching client from pool', err);
}
client.query("CREATE TEMP TABLE reviews(id SERIAL, author VARCHAR(50), content TEXT)");
client.query("INSERT INTO reviews(author, content) VALUES($1, $2)",
["mad_reviewer", "I'd buy this any day of the week!!11"]);
client.query("INSERT INTO reviews(author, content) VALUES($1, $2)",
["calm_reviewer", "Yes, that was a pretty good product."]);
client.query("SELECT * FROM reviews", function(err, result) {
console.log("Row count: %d",result.rows.length); // 1
for (var i = 0; i < result.rows.length; i++) {
var row = result.rows[i];
console.log("id: " + row.id);
console.log("author: " + row.author);
console.log("content: " + row.content);
}
});
//call `done()` to release the client back to the pool
done();
});
function findOptions(query, entity, paramsArray, callback) {
pg.connect(db_conString, function(errConnect, client, done) {
if (errConnect) {
logger.logError('Error while connecting to Database.');
logger.logError('Error : ' + errConnect);
callback(errConnect);
return;
}
logger.logDebug('SELECT query : ' + query);
client.query(query, objectToArray(paramsArray), function(errQuery, entityResult) {
// err treatment.
if (errQuery) {
logger.logError('SELECT : An error occurred while executing query : ' + query);
logger.logError('Error : ' + errQuery);
done();
callback(errQuery, null);
return;
}
if (entityResult.rows.length === 0) {
done();
callback(null, entityResult.rows);
return;
}
logger.logInfo('SELECT o2ms query');
performOneToManySelect(client, entityResult.rows, entity, function(errO2M, o2mResult) {
if (errO2M) {
logger.logError('SELECT : An error occurred while Selecting o2ms rows.');
logger.logError('Error : ' + errO2M);
done();
callback(errO2M);
return;
}
logger.logInfo('SELECT m2ms query');
performManyToManySelect(client, o2mResult, entity, function(errM2M, m2mResult) {
if (errM2M) {
logger.logError('Select : An error occurred while Selecting m2ms rows.');
logger.logError('Error : ' + errM2M);
done();
callback(errM2M);
return;
}
// release connection whatever happened.
done();
// normal case.
callback(null, m2mResult);
});
});
});
});
}
+ db_config.host + '/' + db_config.database;
var utils = require(__root + 'utils/utils');
// pool connections
var pg = require('pg.js');
var Logger = require(__root + 'utils/logger').Logger;
// LOGGER
var logger = new Logger();
/**
* 'connection' event listener
*/
pg.on('connection', function(connection) {
// Do something
});
// TODO centraliser les connexions
/**
* exports attributes
*/
exports.persist = persist;
exports.find = find;
exports.findQuery = findQuery;
exports.findOptions = findOptions;
exports.findAll = findAll;
/**
* persist query.
Support.Client = function(cb) {
pg.connect(Support.Config, cb);
};
/**
* Update statement
*
* @param model
* The model to persist as an object (attribute must have columns names)
* @param callback
* The callback to execute
*/
function update(model, callback) {
logger.logInfo('Updating row in table : ' + model.tableName);
// build query
var query = buildUpdateStatement(model.tableName, model.fields, model.id);
pg.connect(db_conString, function(errConnect, client, done) {
if (errConnect) {
logger.logError('Error while connecting to Database.');
logger.logError('Error : ' + errConnect);
callback(errConnect);
return;
}
// BEGIN Statement
logger.logInfo('BEGIN Transaction');
client.query('BEGIN', function(errBegin) {
if (errBegin) {
logger.logError('BEGIN : An error occurred while beginning transaction');
logger.logError('Error : ' + errBegin);
performRollback(client, done);
callback(errBegin);
return;
}
// This function is a sort of insurance that the passed function will be executed on the next VM internal loop,
// which means it will be the very first executed lines of code.
// @see http://nodejs.org/api/all.html#all_process_nexttick_callback for more
process.nextTick(function() {
// First Insert Statement
logger.logDebug('UPDATE query : ' + query);
client.query(query, objectToArray(model.fields), function(errQuery, insertedResult) {
// err treatment.
if (errQuery) {
logger.logError('UPDATE : An error occurred while updating row : ' + query);
logger.logError('Error : ' + errQuery);
performRollback(client, done);
callback(errQuery);
return;
}
logger.logInfo('UPDATE o2ms query');
performOneToManyUpdate(client, insertedResult.rows[0].id, model, function(errO2M) {
if (errO2M) {
logger.logError('UPDATE : An error occurred while inserting o2ms rows.');
logger.logError('Error : ' + errO2M);
performRollback(client, done);
callback(errO2M);
return;
}
logger.logInfo('UPDATE m2ms query');
performManyToManyUpdate(client, insertedResult.rows[0].id, model, function(errM2M) {
if (errM2M) {
logger.logError('UPDATE : An error occurred while inserting m2ms rows.');
logger.logError('Error : ' + errM2M);
performRollback(client, done);
callback(errM2M);
return;
}
// COMMIT the transaction
logger.logInfo('COMMIT Transaction');
client.query('COMMIT', function() {
done();
// Get the inserted Element with all its objects
find(model, { id : insertedResult.rows[0].id }, function(errSelect, element) {
if (errSelect) {
logger.logError('SELECT : An error occurred while Re finding the Updated rows.');
logger.logError('Error : ' + errSelect);
}
callback(errSelect, element[0]);
});
});
});
});
});
});
});
});
}
function(req,res){
pg.connect(conString, function(err, client, done){
//Return if an error occurs
if(err) {
//TODO respond with error code
console.error('error fetching client from pool', err);
}
var sql_locale='SELECT DISTINCT locale FROM geo_object.continent ';
var responseLocaleArray = [];
client.query(sql_locale, function(err, result){
//Return if an error occurs
if(err) {
console.error('error fetching client from pool', err);
}
result.rows.forEach(
function(data) {
responseLocaleArray.push(data.locale);
}
);
// Header
var locale = req.header('Accept-Language', 'es-AR'); //"es-AR" es el valor default en caso de ser null
var languagesArray = locale.match(/[a-zA-z\-]{2,10}/g) || [];
var resultDB = "";
languagesArray.every(
function(dataLang){
responseLocaleArray.every(
function(dataDB){
if(dataLang.toUpperCase() == dataDB.toUpperCase()){
resultDB = dataDB;
return false;
}
return true;
}
);
if(resultDB == "")
return false;
else return true;
}
);
console.log(resultDB);
console.log(locale);
if(resultDB == "") resultDB = "es-AR";
var sql = 'SELECT id, code, name, description, comment FROM geo_object.continent WHERE erased=false AND locale ilike ';
sql += "'" + resultDB + "'";
sql += " ORDER BY name";
var responseArray = [];
client.query(sql, function(err, result) {
//Return if an error occurs
if(err) {
console.error('error fetching client from pool', err);
}
// Storing result in an array
result.rows.forEach(
function(data) {
var dto = {
id: data.id,
code: data.code,
name: data.name,
description: data.description,
comment: data.comment,
_links: {
continent: {
href: 'http://'+config.host+':'+ config.port + "/continents/" + data.code,
type: 'application/json'
}
}
};
responseArray.push(dto);
}
);
done(); //release the pg client back to the pool
var model = {
"org.geoobject.model.Continent": responseArray
};
res.json(model);
});
});
});
});