hstore parser

[troyk]

I googled and a found a few for ruby but none for js. Is anyone working on this? If not then probably time the gauge my regex skills.

[brianc]

Pull request would make the best birthday present ever. just sayin.

[distracteddev]

This might be a good place to start:

https://github.com/mikl/node-hstore/blob/master/hstore.js

[troyk]

@distracteddev yeah, there is another one here: https://github.com/bjpirt/backbone-postgresql/blob/master/backbone-postgresql.js#L134-162 I'm still messing with pg9.2 row_to_json and array_to_json stuff and I'm not sure if I'll be needing much parsing in node-postgres itself, besides JSON.parse ;)

[distracteddev]

Awesome :) Thanks for the link. Still struggling with wether I should use PSQL to store my JSON or just stick to Mongo/Couch.

[booo]

Little bit of code to discuss:

var parseHstore = function parseHstore(val) {

  var object = {};
  var values = [];

  var i = 1;
  var start = 1;

  while(i < val.length) {
    c = val.charAt(i);
    if(c == '\\') {
      i = i + 2;
    } else if(c == '"') {
      values.push(val.substring(start, i));
      i = i + 4;
      start = i;
    } else {
      i++;
    }
  }

  for(i = 0; i < values.length; i = i + 2) {
    object[values[i]] = values[i+1];
  }

  return object;

};

Maybe someone can write some hyper tricky test cases so we can check our implementations?

[booo]

I pushed my initial idea to https://github.com/booo/node-postgres/tree/hstore

[williamkapke]

Until there is a static OID for hstore it seems it will need to remain as an addon. Fortunately, it isn't too hard to modify your code as an add on. (types was exposed since your comment 5 months ago and made it easier)

var pgtypes = require('pg').types;

//hstore oid is different for every db. To find out yours, run:
//SELECT oid FROM pg_type WHERE typname='hstore'
var oid = <your_hstore_oid>

pgtypes.setTypeParser(oid, function parseHstore(val) {
    val = String(val);//convert it from Binary
    var object = {};
    var name;

    var i = 1;
    var start = 1;

    while(i < val.length) {
        c = val.charAt(i);
        if(c == '\\') {
            i = i + 2;
        } else if(c == '"') {
            if(name){
                object[name] = val.substring(start, i);
                name = null;
            }
            else {
                name = val.substring(start, i);
            }
            i = i + 4;//skip over ", "
            start = i;
        } else {
            i++;
        }
    }

    return object;
});

The serialization code gets a bit ugly since prepareValue is not exposed nicely. The comments indicate that Brian isn't too happy with the code which is probably why. Here we go...

 var pgutils = require(require.resolve('pg').replace("index","utils"));
var pv = pgutils.prepareValue;

function lameObjectTest(obj){
    if(!obj || obj && obj.toString()!=="[object Object]") return false;

    var k;
    for(k in obj){}

    return obj.hasOwnProperty(k);
}
function hstore(val){
    var out = [];
    var js = JSON.stringify;
    for(var k in val){
        out.push(js(k)+'=>'+js(val[k].toString()));
    }
    return out.join(', ');
}
pgutils.prepareValue = function(val){
    //You might want to do something else. If you're using the postgres
    //JSON extension this will really hose everything.
    if(lameObjectTest(val)){
        return hstore(val);
    }
    return pv(val);
};

Try it out...

var pg = require("pg");
var conString = "tcp://postgres:1234@localhost/postgres";

pg.connect(conString, function(err, client) {
    var query = "SELECT $1::hstore || $2 your_hstore";
    client.query(query, [{a:123,b:456,c:789}, '"x"=>"000"'], function(err, qry){
        console.log(qry.rows[0]);
        //output: { your_hstore: { a: '123', b: '456', c: '789', x: '000' } }
        process.exit();
    })
});

...feedback is appreciated!

[zenhob]

I have been trying to figure out whether there is a cleaner way to do this yet, but that doesn't seem to be the case.

While it's easy to register an hstore type parser for reading (provided you bring your own OID), writing remains tricky. There does not appear to be any type information available to the client for prepared statement input, preventing us from being able to tell whether we should be converting hashes to JSON or hstore. Is my understanding of the situation correct? Barring some mechanism for querying the psql types of prepared statement input, is this even possible to do without trampling JSON support?

[williamkapke]

You are understanding correctly. And since javascript objects do not have meta data (perhaps like you'd find with .NET attributes)- it needs to be converted manually.

Now that JSON & plV8 support is a reality (for me)- my solution is to just switch all my hstores columns to JSON columns.

[whitelynx]

There does not appear to be any type information available to the client for prepared statement input, preventing us
from being able to tell whether we should be converting hashes to JSON or hstore.

Since it looks like hstore will have to be an add-on anyway, my suggestion would be to make a simple transformation function that converts to the hstore format, and leave it up to the user to call that when appropriate. (since the JSON data type is now built in, it should probably be the default)

var hstore = require('pg').hstore;
client.query({
    text: "SELECT keyValuePairs -> 'some' AS someValue FROM (VALUES ($1::hstore)) AS sub (keyValuePairs)",
    values: [hstore({some: 'properties', "go here": 33})]
});

I have a simple example up at https://gist.github.com/whitelynx/5832817 .

The big disadvantage I see there is that it's still going to get labelled as TEXT or VARCHAR on the wire, not as the hstore type, so that cast to hstore is probably required. It'd be nifty if there were a way to pass an instance of some object (say, pg.TypedValue or something similar) that would allow us to declare type information about our parameters. (actually, I could probably put together a PR if that sounds like a good idea to everyone) Then, the hstore() function could just return an instance of that with the correct type set.

[brianc]

This looks like something that might be better served by an addon. Closing due to inactivity.