Tying Shoelaces And What is Valid JSON

Up until this week, I thought I knew enough about tying shoelaces and JSON. Turns out that for both, I fell short.
First to the tying of shoelaces. You would have thought that I should have been a pro by now since I have done it from childhood and you may be laughing at me but before you do that, check out this very informative video from a talk from TED 2005 Terry Moore: How to tie your shoes.

So to the techy stuff.
I got introduced to JSON at the beginning of last summer as I worked on creating a JSON RPC adapter for NexJ Express.
At the moment I am also involved in building a JSON Integration Adapter, and as part of the design phase we had to figure out what vaild JSON was.
To my astonishment, I found out that Javascript “primitives”, that is String, Number and Boolean are not valid JSON. My misunderstanding of this was from missing reading the railway diagrams on json.org, I just thought that every thing that was a valid value, was also valid JSON.
So how did I know I was wrong, my colleague Brian, showed me the RFC for JSON, and right there it says
A JSON text is a serialized object or array.
JSON-text = object / array

I also asked another colleague, Dave, who works a lot with Javascript and he started off by saying basically the same thing, JSON is “Object Notation”.
So what does this mean for me? Well, I have authored some web services which serve what I thought was “JSON”, so I may have to go back and fix them.

The lesson learned, read RFCs’

PostgreSQL: Create a Large Object (lo) from binary data (bytea) on server side

Again, I found something that NexJ Express uses that PostgreSQL does not have support for, that is creating large objects when given bytea data.
Like with Comparing bytea data to a Large Object on server side, I created a CAST (bytea AS oid) and a procedure to solve this problem.
The algorithm is pretty simple, get the binary data, if it is null, return null. Else create a large object and in the lowrite function, pass it the binary value, instead of a path to a file.

The code for the procedure is below. Note that the lo_manage package should be installed for this to work.

create or replace function blob_write(lbytea bytea)
   returns oid
   volatile
   language plpgsql as
$f$
   declare
      loid oid;
      lfd integer;
      lsize integer;
begin
   if(lbytea is null) then
      return null;
   end if;

   loid := lo_create(0);
   lfd := lo_open(loid,131072);
   lsize := lowrite(lfd,lbytea);
   perform lo_close(lfd);
   return loid;
end;
$f$;
CREATE CAST (bytea AS oid) WITH FUNCTION blob_write(bytea) AS ASSIGNMENT;

So now the following code works:

CREATE TABLE bytea_to_lo (
   lo largeObj
);

INSERT INTO bytea_to_lo VALUES ( DECODE('00AB','hex'));

PostgreSQL Compare bytea data to a Large Object on server side

While working on the PostgreSQL Adapter for NexJ Express, I found out that I needed to compare large objects to bytea data on the server side.
for example:
select * from Table_With_LOs where lo_value = bytea_value;

A little background:
PostgreSQL stores large object data in a separate table (pg_largeobject), and in the data column for the table it uses an oid (an integer value) as a “pointer” to this data.
So, doing "where lo_value = bytea_value", actually translates to
"where integer_value = bytea_value"

Solution:
Based on this blog post, Reading XML Files into Database ,by rhodiumtoad (he is very active in the postgresql irc channel), I created a cast function to go from oid to bytea data.

create or replace function blob_read(loid oid)
  returns bytea
  volatile
  language plpgsql as
$f$
  declare
    content bytea;
    lfd integer;
    lsize integer;
  begin
  if(loid is null) then
   return null;
  end if;
    lfd := lo_open(loid,262144);
    lsize := lo_lseek(lfd,0,2);
    perform lo_lseek(lfd,0,0);
    content := loread(lfd,lsize);
    perform lo_close(lfd);
    return content;
  end;
$f$;
CREATE CAST (oid AS bytea) WITH FUNCTION blob_read(oid) AS IMPLICIT;

So now I can use large_objects (lo/oid) and bytea in the “WHERE” clause.