To create a mongodb copy of the tutorial _ database on the basis of PostgreSQL other

I have a lazy idea. How should this good idea begin? Well, this is an appropriate little madness: Why not build our own MongoDB version directly on the basis of postgres? It sounds far-fetched, but it's simple and real.

When the NoSQL movement was brilliant, the Postgres community did not sit around and fiddle with their thumbs. They continue to develop, throughout the Postgres ecosystem, and several prominent features have caught my eye: integrating JSON support and PLV8. PLV8 the V8 JavaScript engine into Postgres, he made JavaScript a first class language (first-class language). Having a JSON type makes it easier to process JSON (which works).

What you need to do before you start:

• Postgres 9.2+ (as of this blog entry, 9.2 are in beta)-http://www.postgresql.org/ftp/source/
• V8-https://github.com/v8/v8
• Plv8-http://code.google.com/p/plv8js/wiki/plv8

The lowest level of MongoDB is the collection. A collection can be represented by a table:

  CREATE TABLE some_collection (
   some_collection_id serial not NULL PRIMARY KEY,
   data JSON
  );

Character JSON is saved in the Postgres table, easy (see now).

The following implements the automatic creation of collections. Save in the collection table:

  CREATE TABLE Collection (
   collection_id serial not NULL PRIMARY KEY,
   name VARCHAR
  );
 
  --Make sure the ' name ' unique
  CREATE unique INDEX idx_collection_constraint on collection (name);

Once the table is built, the collection can be created automatically through stored procedures. The method is to first build the table and then insert the table sequence.

 CREATE OR REPLACE FUNCTION create_collection (collection varchar) RETURNS
  Boolean as $$
   var plan1 = Plv8.prepare (' INSERT into collection (name) VALUES ($) ', [' varchar ']);
   var plan2 = Plv8.prepare (' CREATE TABLE Col_ ' + collection +
    ' (Col_ ' + collection + ' _id INT not NULL PRIMARY KEY, dat a JSON);
   var plan3 = Plv8.prepare (' CREATE SEQUENCE Seq_col_ ' + collection);
   
   var ret;
   
   try {
    plv8.subtransaction (function () {
     plan1.execute ([collection]);
     Plan2.execute ([]);
     Plan3.execute ([]);
    
     ret = true;
    });
   catch (Err) {
    ret = false;
   }
   
   Plan1.free ();
   Plan2.free ();
   Plan3.free ();
   
   return ret;
  $$ LANGUAGE plv8 immutable STRICT;

With stored procedures, it is much more convenient:

 SELECT create_collection (' my_collection ');

Solve the problem of collection storage, see MongoDB data parsing below. MongoDB This action through the dotted annotation method:

  CREATE OR REPLACE FUNCTION find_in_obj (data json, key varchar) RETURNS
  varchar as $$
   var obj = json.parse (data); 
   var parts = Key.split ('. ');
   
   var part = Parts.shift ();
   while (part && (obj = Obj[part])!== undefined) {Part
    = Parts.shift ();
   }
   
   This would either be the value, or undefined return
   obj;
  $$ LANGUAGE plv8 STRICT;

The above functionality returns varchar and does not apply to all cases, but is useful for strings comparisons:

  SELECT data
   from Col_my_collection
   WHERE find_in_obj (data, ' some.element ') = ' Something cool '

In addition to string comparisons, MongoDB also provides a comparison of numeric types and provides keyword exists. The following are the different implementations of the Find_in_obj () method:

  CREATE OR REPLACE FUNCTION find_in_obj_int (data json, key varchar) RETURNS
  int as $$
   var obj = json.parse (data); 
   var parts = Key.split ('. ');
   
   var part = Parts.shift ();
   while (part && (obj = Obj[part])!== undefined) {Part
    = Parts.shift ();
   }
   
   return number (obj);
  $$ LANGUAGE plv8 STRICT;
   
  CREATE OR REPLACE FUNCTION find_in_obj_exists (data json, key varchar) RETURNS
  BOOLEAN as $$
   var obj = Json.parse (data);
   var parts = key.split ('. ');
   
   var part = Parts.shift ();
   while (part && (obj = Obj[part])!== undefined) {Part
    = Parts.shift ();
   }
   
   return (obj = = undefined? ' F ': ' t ');
  $$ LANGUAGE plv8 STRICT;

Next is the data query. The Find () method is implemented through existing materials.
Saving data to the collection is simple. First, we need to check the JSON object and look for a _id value. This part of the code is a native hypothesis, if _id already exists this means an update, otherwise it means an insert. Note that we have not yet created Objectid, and only one sequence is used to make it happen:

 CREATE OR REPLACE FUNCTION Save (collection varchar, data JSON) RETURNS BOOLEAN as $$
 
   var obj = json.parse (data);
 
   var id = obj._id; If there is no ID, naively assume a insert if (id = = = undefined) {//Get the next value from the sequence for
    The ID var seq = plv8.prepare ("Select Nextval (' Seq_col_" + Collection + ') as ID ");
    
    var rows = Seq.execute ([]);
    id = rows[0].id;
 
    obj._id = ID;
   
    Seq.free ();
      var insert = Plv8.prepare ("INSERT INTO Col_" + Collection + "(Col_" + collection + "_id, data) VALUES ($)",
 
    [' int ', ' json ']];
    Insert.execute ([ID, json.stringify (obj)]);
   Insert.free ();  else {var update = Plv8.prepare ("Update Col_ + collection +" SET data = $ WHERE Col_ "+ collection +" _id =
 
    $ ", [' json ', ' int ']];
   Update.execute ([data, id]);
  return true;
$$ LANGUAGE plv8 immutable STRICT; 

Based on this view, we can build some simple documents to insert:

  {
   ' name ': ' Jane Doe ',
   ' address ': {
    ' street ': ' 123 Fake Street ', ' City
    ': ' Portland ',
    ' state ': ' OR ' c6/>},
   ' age ':
   
  {
   ' name ': ' Sarah Smith ', ' address
   ': {Street
    ': ' 456 real Ave ',
    ' City ': "Seattle", "State
    ": "WA"
   }
   
  {
   "name": "James Jones",
   "address": {
    " Street ":" 789 Infinity Way "," City
    ":" Oakland ",
    " state ":" CA "
   },
   " Age ":
  }

Let's create a set merge to insert some data:

  work=# SELECT create_collection (' data ');
   Create_collection
  -------------------
   t
  (1 row)
   
  work=# SELECT Save (' data ', ' {our object} ');
   Save
  ------
   t
  (1 row)

You can view the object by checking the contents of the "col_data" table.

Other translated versions (1)

Now that we have some data, let's check it out again. Suppose we want to find everyone who lives in Oregon or Washington state older than 30, using a MongoDB style found ():

  {"
   $or": [
    {
     "address.state": "or"
    },
    {
     "address.state": "WA"
    }
   ],
   "age": {
    "$gt":}
  

Because last time we've created some depth of packet detection, it's easy to create a query and return to Jane Doe:

  SELECT data from
   col_data
   WHERE find_in_obj_int (data, ' age ') > and
    (
       find_in_obj (data, ' Address.state ') = ' or '
      or
       find_in_obj (data, ' address.state ') = ' WA '
      )

I used the method of writing a recursive call function to create a WHERE clause. It was a bit long, so I didn't stick it here but put it on the GitHub. Once the find () stored procedure is created, we can use it in the query. We should be able to see Jane Doe being returned:

  work=# SELECT Find (' data ', ' {"$or": [{"Address.state": "or"}, {"Address.state": "WA"}], "age": {"$GT": 30}} ');

This works: It's not elegant, but it works. This is a proof of concept, and there is hardly as good a possibility as it. I have been asked before why not use Hstore. Although you can store nested Hstore and array values, it is still not JSON and is not easy to operate through PLV8. This will require a serializer from hstore to JSON, which serializes the requested return at any time into a MongoDB-accepted data form, but it is still too easy to handle in JavaScript. This is the suboptimal choice, after all, we are going to create a mongodb copy on the basis of postgres.

Source can be found on the GitHub: fork and try it, remember feedback oh.