Adding basic support for POINT

[dresende]

Does this make sense? I'm overwriting the function locally but perhaps this could land upstream. It's not the most elegant but enables one to insert or update a record with a point, just the way mysql retrieved it.

sqlstring.objectToValues = (obj, tz) => {
    let sql = "";

    for (let key in obj) {
        let val = obj[key];

        if (typeof val === "function") continue;

        if (typeof val == "object" && Object.keys(val).length == 2 && typeof val.x == "number" && typeof val.y == "number") {
            sql += (sql.length === 0 ? "" : ", ") + sqlstring.escapeId(key) + " = " + "GeomFromText('POINT(" + val.x + " " + val.y + ")')";
        } else {
            sql += (sql.length === 0 ? "" : ", ") + sqlstring.escapeId(key) + " = " + sqlstring.escape(val, true, tz);
        }
    }

    return sql;
};

Of course, it only works with 2D points, but for me it's 90% of geometries I use.

[dougwilson]

I'd have to look though the cases, as maybe it would produce some false-positives? What is wrong with using SqlString.raw() or making a Point object with a .toSqlString method on it to pass around?

[dresende]

There are of course false positives. And I just noticed val can be null/undefined and is not checked, but that's easy. What I thought was, the false positive occurrences is extremely tiny (or doesn't make sense at all.

The use case is something like this:

db.query("UPDATE devices SET ? WHERE id = ?", [{
    name     : "John Doe",
    location : { x: -8, y: 40 },
}, device_id, next);

[dougwilson]

Right, I get what you're trying to do :) That's why I'm wondering why should there be any risk of false-positives when there is support for doing this today with no false positives? What, specifically, is wrong with using SqlString.raw() or making a Point object with a .toSqlString method on it to pass around?

[dresende]

It was an easier path, since, when querying the database, the module does not return a Point object. But ok, since I use a small abstraction on top of the module, perhaps I can incorporate a Point there.

[dougwilson]

I'm going to close this as there is a solution with recent versions:

class Point {
  constructor(x, y) {
    this.x = x;
    this.y = y;
  }

  toSqlString() {
    return `GeomFromText('POINT(${SqlString.escape(this.x)} ${SqlString.escape(this.y})')`
  }
}

Eventually the mysql module itself will return objects that have a toSqlString attached to provide built-in round trip support, but that is something that would be a change in that module, rather than here. The pieces are in place in this module these days 👍