Auto Increment with MongoDB

• THU, 29 JUL 2010 AT 19:52:18 GMT
 
• 13 COMMENTS
 
• TWEETS

We are currently working on an app that uses a number of technologies, including PHP, Python, and MongoDB. Recently, a need arose to use sequential identifiers for users, similar to an auto_increment column in MySQL.

First, create a sequence collection that you can use to determine the next identifier in the sequence. The following creates a collection called seq that has a single sequence in it (for users), but you can add as many as you need:

TOGGLE CODE VIEW

1. db.seq.insert({"_id":"users", "seq":new NumberLong(1)});

Before adding a new user, you need to increment the sequence by one and fetch the next identifier. Fortunately, the findandmodify() command provides an atomic way to do this. Using the MongoDB shell, the command would look something like this:

TOGGLE CODE VIEW

1. db.seq.findAndModify({
2. query: {"_id":"users"},
3. update: {$inc: {"seq":1}},
4. new: true
5. });

Because I'm using Lithium, I added a method for fetching the next identifier to myUser model:

TOGGLE CODE VIEW

1. <?php
2.  
3. namespace app\models;
4.  
5. class User extends \lithium\data\Model {
6.  
7. static public function seq() {
8. $seq = static::_connection()->connection->command(
9. array('findandmodify' => 'seq',
10. 'query' => array('_id' => 'users'),
11. 'update' => array('$inc' => array('seq' => 1)),
12. 'new' => TRUE
13. )
14. );
15.  
16. return $seq['value']['seq'];
17. }
18.  
19. }
20.  
21. ?>

If you're not using Lithium, you can use the MongoDB class to execute a command().

With this in place, adding a new user is a simple process. I create an array called$data with everything I want to store for a user, and then do the following:

TOGGLE CODE VIEW

1. <?php
2.  
3. $user = User::create($data);
4. $user->_id = User::seq();
5. $success = $user->save();
6.  
7. ?>

This example should be easy to adapt to any environment. Once you have the next identifier in the sequence, you simply store it as you would any other data.

I hope to blog more about both MongoDB and Lithium. As these technologies are still pretty new to me, please feel free to point out any improvements. I'll update the post accordingly.

package com.Mongo.Test;

import com.mongodb.*;

import org.junit.Before;

import org.junit.Test;

import static org.junit.Assert.*;

public class MongoDBUnitTestII {

    private DBCollection customers;

    @Before

    public void setUp() throws Exception {

        Mongo mongo = new Mongo();

        DB db = mongo.getDB("webshop");

        customers = db.getCollection("customer");

        customers.remove(new BasicDBObject());

    }

    @Test

    public void shouldWorkWithMongoDB() {

        DBObject customerMaxMustermann = new BasicDBObject()

                .append("id", "4711")

                .append("name","Max Mustermann")

                .append("city","Cologne")

                .append("numberOfOrders", 3);

        customers.insert(customerMaxMustermann);

        DBObject savedCustomer = customers.findOne();

        assertNotNull(savedCustomer);

        assertEquals("4711", savedCustomer.get("id"));

    }

   /* {"id" : 1234, "name": "Otto Normal", city: "Berlin",#

      numberOfOrders: 4, bankData: {accountNumber : "9876543210", bankCode : "30020011", accountHolder : "Otto Normal"}*/

    private void insertCustomerOttoNormal() {

        Customer customer = new Customer();

        customer.setId("1234");

        customer.setName("Otto Normal");

        customer.setCity("Berlin");

        customer.setNumberOfOrders(4);

        BankData bankData = new BankData();

        bankData.setAccountNumber("9876543210");

        bankData.setBankCode("30020011");

        bankData.setAccountHolder("Otto Normal");

        customer.setBankData(bankData);

        customers.setObjectClass(Customer.class);

        customers.insert(customer);

    }

    @Test

    public void shouldSaveAndFindCustomers() {

        insertCustomerOttoNormal();

        // attention: all field names here begin with a capital letter

        DBCursor result = customers.find(new BasicDBObject("City", "Berlin"));

        assertTrue(result.hasNext());

        Customer savedCustomer = (Customer) result.next();

        assertEquals("1234", savedCustomer.getId());

        assertEquals("30020011", savedCustomer.getBankData().getBankCode());

    }

    @Test

    public void shouldIncrementTheNumberOfOrdersIfTheCustomerPlaceAnOrder() {

        insertCustomerOttoNormal();

        customers.update(new BasicDBObject("Id", "1234"),

                new BasicDBObject("$inc",

                        new BasicDBObject("NumberOfOrders", 1)));

        Customer savedCustomer = (Customer) customers

                .findOne(new BasicDBObject("NumberOfOrders", new BasicDBObject("$gt", 4)));

        assertNotNull(savedCustomer);

        assertEquals("Otto Normal", savedCustomer.getName());

        assertEquals(5, savedCustomer.getNumberOfOrders());

    }

}

import java.net.UnknownHostException;

import java.util.Calendar;

import java.util.GregorianCalendar;

import java.util.Iterator;

import java.util.List;

import java.util.regex.Pattern;

import org.junit.Test;

import java.sql.*;

import com.mongodb.BasicDBObject;

import com.mongodb.DB;

import com.mongodb.DBCollection;

import com.mongodb.DBCursor;

import com.mongodb.DBObject;

import com.mongodb.Mongo;

import com.mongodb.MongoException;

Public Class MongoDB {

       @Test

       // MSSQL 연결

       public void testMSSQLConnection() throws Exception {

             // TODO Auto-generated method stub

             Connection conSQL = null;

             Statement stmt = null;

             ResultSet rs = null;

             // MSSQL

             String strJDBCDrv = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

             String strJDBCUrl = "jdbc:sqlserver://localhost:1433;DatabaseName=SQLDB";

             String strQuery = "select testID, testName from T_Test order by testID desc";

             // MongoDB

             Mongo connection = new Mongo("MongoDBIP", 27017);

             DB mongoJUnit = connection.getDB("testDB");

             DBCollection dept = mongoJUnit.getCollection("T_Test");

             /*

             try{

                    Class.forName(strJDBCDrv);

                    System.err.println("드라이버 검색 성공");

             }

             catch(ClassNotFoundException ee) {

                    ee.printStackTrace();

                    System.err.println("드라이버 검색 실패");

             }

             */

             try {

                    Class.forName(strJDBCDrv).newInstance();

                    conSQL = DriverManager.getConnection(strJDBCUrl, "sa", "1231231");

                    stmt = conSQL.createStatement();

                    rs = stmt.executeQuery(strQuery);

                    BasicDBObject putDB = new BasicDBObject();

                    //GregorianCalendar cal = new GregorianCalendar();

                    while(rs.next()){

                           // System.out.println(rs.getString(1));

                           putDB.put("testID", rs.getInt(1));

                           putDB.put("testName", rs.getString(2));

                           dept.insert(putDB);

                           putDB.clear();

                    }

             }

             catch (Exception e){

                    e.printStackTrace();

             }

             finally {

                    conSQL.close();

                    stmt.close();

                    rs.close();

             }

       }

}

[root@searchapp mongodb]# mongodump -d newDB -o /usr/mongodb/backup

connected to: 127.0.0.1

DATABASE: newDB  to     /usr/mongodb/backup/newDB

        newDB.test to /usr/mongodb/backup/newDB/test.bson

                 1 objects

        newDB.system.indexes to /usr/mongodb/backup/newDB/system.indexes.bson

                 1 objects

> db.dropDatabase()

{ "dropped" : "newDB", "ok" : 1 }

> show dbs

admin

fileSize

local

test

> exit

bye

-- 콘솔에서 MongoRestore 를 사용해 Restore 를 합니다.

[root@searchapp bin]# mongorestore -d newDB --drop /usr/mongodb/backup/newDB/

connected to: 127.0.0.1

Tue Jan 25 20:51:24 /usr/mongodb/backup/newDB/test.bson

Tue Jan 25 20:51:24      going into namespace [newDB.test]

Tue Jan 25 20:51:24      dropping

Tue Jan 25 20:51:24      1 objects found

Tue Jan 25 20:51:24 /usr/mongodb/backup/newDB/system.indexes.bson

Tue Jan 25 20:51:24      going into namespace [newDB.system.indexes]

Tue Jan 25 20:51:24      dropping

Tue Jan 25 20:51:25      1 objects found

-- 접속해서 보면 복원되어 있습니다. 

[root@searchapp bin]# mongo

MongoDB shell version: 1.6.5

connecting to: test

> show dbs

admin

fileSize

local

newDB

test

3. 특정시점의 데이터 백업

--- Fsync 를 통해서 insert 를 하지 않도록 하고 

> use admin

switched to db admin

> db.runCommand({"fsync":1, "lock":1})

{

        "info" : "now locked against writes, use db.$cmd.sys.unlock.findOne() to unlock",

        "ok" : 1

}

> use newDB

switched to db newDB

> show collections

system.indexes

test

> db.test.insert({"name":"Koon", age:37})

 --------------- Lock 이 걸려 있습니다. -------------------------------

> 

-- 외부에서 콘솔을 열고... 

몽고로 들어가서 "db.$cmd.sys.unlock.findOne()" 를 가볍게 실행해주면... admin에서

Lock 이 풀립니다.

Fsync 는 MSSQL의 Checkpoint 와 유사한 개념입니다. (기능)