Designing a log of database queries for MongoDB

The database query log could be a crucial part of a database restoring process required after data corruption caused by either an illegal update operation or a damage of database files. The main task here is to find the right format for storing the queries.

The format should be targeted at programmatic data retrieving, and be minimalistic to keep the size of the audit collection, that holds the query log, to a minimum. Also, it must contain additional information such as the time of modification, or the collection name. You could imagine the following set of properties:

collection – the collection name
operation – the update operation name
timestamp – the time of modification
custom fields – to keep the data

Custom fields would depend on the update operation. For instance, for the ‘insert’ operation, only one custom field must be stored – the ‘object’ field which contains an object that is being inserted into a collection,
this way the operation:

db.users.insert({ "username": "john" })Code language: JavaScript (javascript)

will result in the following log record:

{ "collection": "users", "operation": "insert", "object": { "username": "john" },
"timestamp": /*...*/ }Code language: JSON / JSON with Comments (json)

Let’s have a look at the custom fields required to log all the available Java driver operations:

Operation	Object	‘Criteria’ object	‘Sort’ object	‘Update’ object	‘Insert’ flag	‘Multi’ flag
Add	+
Remove		+
Update		+		+	+	+
FetchAndRemove		+	+
FindAndModify		+	+	+	+

The number of optional fields is very limited and a lot of operations use the same objects and flags, so a simple plain old object can be used to represent a log record in your application:

public class LogRecord {
    private String collection;
    private String action;
    private DBObject object;
    private DBObject criteria;
    private DBObject sort;
    private DBObject query;
    private boolean insert;
    private boolean multiple;
    private long time;

    // ...
}Code language: PHP (php)

Of course, to persist log data to a MongoDB audit collection, instead of a POJO, you should use the wrapper for the BasicDBObject document:

public class LogRecord {

    /** Database document. */
    protected final BasicDBObject doc = new BasicDBObject();

    /**
     * Field names.
     */
    public enum Fields {

        /** Object id. */
        ID("_id"),

        /** Collection name. */
        COLLECTION("collection"),

        /** Database action. */
        ACTION("action"),

        /** Object to store. */
        OBJECT("object"),

        /** Search criteria. */
        CRITERIA("criteria"),

        /** 'Sort' object. */
        SORT("sort"),

        /** 'Update' object. */
        UPDATE("update"),

        /** 'Insert' flag. */
        INSERT("insert"),

        /** 'Multiple' flag. */
        MULTIPLE("multiple"),

        /** The time of modification. */
        TIME("time");

        private final String name;

        private Fields(final String name) {
            this.name = name;
        }

        @Override
        public String toString() {
            return name;
        }
    }

    // Action           Object  Criteria   Sort    Update  Insert  Multiple

    // Add              --
    // Remove                    --
    // Update                    --                 --      --      --
    // FetchAndRemove           --         --
    // FindAndModify            --         --      --      --

    /**
     * Creates a log record.
     *
     * @param collection collection name
     * @param action     database action
     * @param object     object to store
     * @param criteria   search criteria
     * @param sort       'sort' object
     * @param update     'update' object
     * @param insert     'insert' flag
     * @param multiple   'multiple' flag
     * @param time       the time of modification
     */
    public LogRecord(String collection,
                     Actions action,
                     DBObject object,
                     DBObject criteria,
                     DBObject sort,
                     DBObject update,
                     boolean insert,
                     boolean multiple,
                     long time) {
        doc.append(Fields.COLLECTION.toString(), collection);
        doc.append(Fields.ACTION.toString(), action);
        doc.append(Fields.OBJECT.toString(), object);
        doc.append(Fields.CRITERIA.toString(), criteria);
        doc.append(Fields.SORT.toString(), sort);
        doc.append(Fields.UPDATE.toString(), update);
        doc.append(Fields.INSERT.toString(), insert);
        doc.append(Fields.MULTIPLE.toString(), multiple);
        doc.append(Fields.TIME.toString(), time);
    }

    /**
     * Returns the field value.
     *
     * @return the field value, or null if the value is not defined
     */
    public Object getValue(Fields field) {
        doc.get(field.toString());
    }

    /**
     * Returns the database log document.
     *
     * @return the database log document
     */
    public DBObject getDBObject() {
        return (DBObject) doc.clone();
    }
}Code language: PHP (php)

Records stored in this way can be queried and read by humans, which gives sufficient debugging capabilities and, also, can be converted to update queries during an automatic restoring process.

This post is written for the MongoDB Java driver version 2.4

Thank you!