ruscomap/server/dbHandler.js

const config = require("config");
const mysql = require("mysql");

const pool = mysql.createPool({
  host: config.get("db.host"),
  user: config.get("db.user"),
  password: config.get("db.password"),
  database: config.get("db.database"),
});

function addMarker(marker) {
  const insertQuery = "INSERT INTO markers VALUES (?,?,?,POINT(?,?),?)";
  const date = new Date();

  const query = mysql.format(insertQuery, [
    marker.name,
    marker.description,
    marker.filename,
    marker.long,
    marker.lat,
    date,
  ]);

  pool.query(query, (err, response) => {
    if (err) {
      console.error(err);
      return;
    }
    // rows added
    console.log(response);
  });
}

function getAllMarkers(callback) {
  pool.getConnection((err, connection) => {
    if (err) throw err;
    connection.query("SELECT * from markers", (err, rows) => {
      connection.release(); // return the connection to pool
      if (err) throw err;
      callback(rows);
    });
  });
}

function getUpdatedMarkers(fromDate, callback) {
  pool.getConnection((err, connection) => {
    if (err) throw err;
    let selectQuery = "SELECT * FROM markers mrk WHERE mrk.ts >= ?";
    let query = mysql.format(selectQuery, fromDate);
    connection.query(query, (err, rows) => {
      connection.release(); // return the connection to pool
      if (err) throw err;
      callback(rows);
    });
  });
}

module.exports = {
  addMarker,
  getAllMarkers,
  getUpdatedMarkers,
};