Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to create a copy of MongoDB on the basis of PostgreSQL". In daily operation, I believe many people have doubts about how to create a copy of MongoDB on the basis of PostgreSQL. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "how to create a copy of MongoDB on the basis of PostgreSQL". Next, please follow the editor to study!

What you need to prepare before you start:

Postgres 9.2 + (as of this blog entry, 9.2 is 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. Collections can be represented by tables:

CREATE TABLE some_collection (some_collection_id SERIAL NOT NULL PRIMARY KEY, data JSON)

Character-based JSON is stored in the Postgres table, which is simple and easy (as it now seems).

The following implementation automatically creates the collection. Save in the collection table:

CREATE TABLE collection (collection_id SERIAL NOT NULL PRIMARY KEY, name VARCHAR);-- make sure the name is unique CREATE UNIQUE INDEX idx_collection_constraint ON collection (name)

Once the table is built, the collection can be created automatically through the stored procedure. The method is to create the table first, and then insert the table-building sequence.

CREATE OR REPLACE FUNCTION create_collection (collection varchar) RETURNS boolean AS $$var plan1 = plv8.prepare ('INSERT INTO collection (name) VALUES ($1)', ['varchar']); var plan2 = plv8.prepare (' CREATE TABLE col_' + collection +'(col_' + collection +'_ id INT NOT NULL PRIMARY KEY, data 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')

To solve the problem of collection storage, let's take a look at MongoDB data parsing. MongoDB completes this action through dot annotation operation:

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 will either be the value, or undefined return obj; $$LANGUAGE plv8 STRICT

The above function returns VARCHAR, which is not applicable in all cases, but is useful for string comparison:

SELECT data FROM col_my_collection WHERE find_in_obj (data, 'some.element') =' something cool'

In addition to string comparisons, MongoDB provides comparisons of numeric types and the keyword exists. Here 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

Then there is the data query. The find () method is implemented through existing materials.

Saving data to the collection is simple. First, we need to examine the JSON object and look for a _ id value. This part of the code is a native assumption, and if _ id already exists, it means an update, otherwise it means an insert. Note that we haven't created an objectID yet, just a sequence waiting for it to occur:

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 an 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 ($1, $2)", ['int',' json']); insert.execute ([id, JSON.stringify (obj)]); insert.free () } else {var update = plv8.prepare ("UPDATE col_" + collection + "SET data = $1 WHERE col_" + collection + "_ id = $2", ['json',' int']); update.execute ([data, id]);} return true; $$LANGUAGE plv8 IMMUTABLE STRICT

Based on this point of view, we can build some simple inserted documents:

{"name": "Jane Doe", "address": {"street": "123Fake Street", "city": "Portland", "state": "OR"}, "age": 33} {"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": 23}

Let's create a set to merge and 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 objects by checking the contents of the "col_data" table.

Other translated versions (1)

Now that we have some data, let's look it up again. Suppose we want to find everyone over the age of 30 living in Oregon or Washington State, using a MongoDB-style find ():

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

Since we created some deep package inspection last time, it is now easy to create a query and return Jane Doe:

SELECT data FROM col_data WHERE find_in_obj_int (data, 'age') > 30 AND (find_in_obj (data,' address.state') = 'OR' OR find_in_obj (data,' address.state') = 'WA')

I used the method of writing a recursive calling function to create the WHERE clause. It's a little long, so instead of posting it here, I put it on GitHub. Once the find () stored procedure is created, we can use it in the query. We should be able to see the Jane Doe returned:

Work=# SELECT find ('data',' {"$or": [{"address.state": "OR"}, {"address.state": "WA"}], "age": {"$gt": 30}}'). At this point, the study on "how to create a copy of MongoDB on the basis of PostgreSQL" is over. I hope I can solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.