I have a MySQL database in which I keep address information that a device goes through. In this table I have the fields "latitude", "longitude" and "direction". And the main idea is to pass those records from MySQL to Mongo, but I want to avoid duplicate addresses so the new structure for Mongo includes an arrangement of positions in which I intend to save latitudes and longitudes that have the same address. To do this I created the following code to perform this migration:
var mysql = require('mysql');
// var async = require('async');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "root",
port: "8889",
database: "gts"
});
var MongoClient = require('mongodb').MongoClient, assert = require('assert');
// Connection URL
var url = 'mongodb://localhost:27017/geolocations';
var resultMongo;
var resultLatitudes;
var findLocation = function (db, callback, param) {
// Find all the latitudes
var collection = db.collection('geopoints');
collection.find(param).toArray(function (err, docs) {
// console.log("\n Location found");
// console.log(docs);
callback(docs);
});
}
var findAddress = function (db, callback, param) {
var collection = db.collection('geopoints');
collection.find(param).toArray(function (err, docs) {
if (err) {
callback(err);
} else {
callback(null, docs);
console.log("\n Address found");
// console.log(docs);
callback(docs)
}
});
}
var insertDocument = function (db, callback, param) {
var collection = db.collection('geopoints');
collection.insertOne(param, function (err, r) {
assert.equal(null, err);
assert.equal(1, r.insertedCount);
console.log("New document inserted");
// console.log(r);
callback(r);
});
}
var addLocation = function (db, callback, id, locations) {
var collection = db.collection('geopoints');
collection.findOneAndUpdate(id,{
$push: {locations}
});
}
// Use connect method to connect to the server
MongoClient.connect(url, function(err, db) {
assert.equal(null, err);
//console.log("Connected successfully to server");
// Query the SQL table
con.connect(function(err) {
if (err)
throw err;
con.query("SELECT DISTINCT 'latitude', 'longitude', 'address' FROM 'eventdata' ORDER BY 'eventdata'.'address' ASC LIMIT 4", function (err, result, fields) {
if (err)
throw err;
console.log("\n SQL found");
for (let index = 0; index < result.length; index++) {
const elementSQL = result[index];
const addressSQL = elementSQL.address.trim();
const latitudeSQL = elementSQL.latitude.toFixed(6);
const longitudeSQL = elementSQL.longitude.toFixed(6);
console.log(addressSQL);
console.log(latitudeSQL);
console.log(longitudeSQL);
// Find the SQL address in Mongo
findAddress(db, function(err, docs){
// console.log("Log de address");
// console.log(docs.length);
if (err) {
console.log(err);
return;
}
if (docs.length > 0){
console.log("Encuentra direcciones iguales: " + addressSQL + " -- " + docs[0].address );
// Check if the positions exist
let currentID = docs[0]._id.toString();
console.log(currentID);
findLocation(db, function (docs) {
if(docs.length > 0){
console.log("Locaciones existentes");
} else {
console.log("Agregar locaciones");
addLocation(db, function (docs) {
console.log('Acaba de agregar locaciones.');
}, {
_id: currentID
}, {
latitude: latitudeSQL,
longitude: longitudeSQL
});
}
}, {
locations: {
latitude: latitudeSQL,
longitude: longitudeSQL
}
});
} else {
console.log("Las direcciones no coinciden");
// Inserta nueva dirección
insertDocument(db, function(r){
console.log("Insertar");
//console.log(r);
}, {
address : addressSQL,
locations : [
{
latitude : latitudeSQL,
longitude : longitudeSQL
}
]
})
}
}, {
address: addressSQL
});
}
});
});
});The problem that I have is that once the for cycle is going through the results of the query to MySQL the code of the queries to mongo is executed until the cycle ends, so I do not know how it can be done so that the function does not let the cycle continue without first doing all the operations with the database. I was reading that it could be with async and await, but I tried to implement it without success.
Example of a section using the async library
async.waterfall([
function(callback) {
// Query the SQL database
con.query("SELECT DISTINCT 'latitude', 'longitude', 'address' FROM 'eventdata' ORDER BY 'eventdata'.'address' ASC LIMIT 2", function (err, result, fields) {
if (err) {
callback('Error al consultar MySQL', null);
} else {
console.log("\n SQL found");
console.log(result);
callback(null, result);
}
});
},
function(result, callback) {
async.each(result, function (row, callback) {
console.log('Muestra una única fila');
console.log(row.address);
findAddress(db, function (err, docs) {
if (err) {
console.log(err);
} else {
console.log('Las direcciones coinciden');
}
}, {
address: row.address
});
}, function (err) {
if(err){
console.log('Problemas al consultar');
callback('Problemas al consultar')
} else {
callback();
}
});
// Find and compare directions in Mongo and MySQL
console.log('Wachea callback');
console.log(result);
callback(null, 'Todo correcto');
// do some more stuff ...
}
],
// optional callback
function(err, results) {
if( err ){
throw "Error al consultar Mongo";
} else {
console.log(results);
console.log('\n');
}
// results is now equal to ['one', 'two']
});Could you help me tell me what I'm doing wrong?