db.js

// -------------------- Describe the tables --------------------

plugin.db.table("department", {
    name: { type:"text" },
    roomNumber: { type:"int", nullable:true }
}, {
    // Define a method on the row objects
    fancyStuff: function(ping) { return ping + " pong"; }
});

plugin.db.table("employee", {
    name: { type:"text" },
    startDate: { type:"date", nullable:true },
    salary: { type:"int", nullable:true, indexed:true },
    ref: { type:"ref", nullable:true, indexed:true, uniqueIndex:true },
    department: { type:"link", nullable:true, indexedWith:["lastDepartment","salary"] },
    lastDepartment: { type:"link", nullable:true, linkedTable:"department" }, // table name doesn't match the field name
});

plugin.db.table("numbers", {
    name: { type:"text" },
    small: { type:"smallint", indexed:true },
    medium: { type:"int" },
    big: { type:"bigint", nullable:true },
    floaty: { type:"float", nullable:true }
})

// -------------------- Use the database --------------------

// Create an object
var engineering = db.department.create({name:"Engineering'"});  // add ' to make sure SQL is escaped
engineering.roomNumber = 42;
engineering.save();

var x = engineering.fancyStuff("ping"));

// Load it back
var engineering2 = db.department.load(engineering.id);

// Another department
var hr = db.department.create();
hr.name = "Human resources";
hr.roomNumber = 101;
hr.save();

// Select all the objects
var alldepartments = db.department.select().stableOrder();

// Select one object in two different ways
var onedepartment = db.department.select().where("name", "=", "Human resources");
var deptbynum = db.department.select().where("roomNumber", "<", 100);

// Create an object in another table, referencing the first
var fred = db.employee.create();
fred.name = "Fred Bloggs";
fred.salary = 90000;
fred.ref = O.ref(56,1000);
var fredStartDate = new Date(2010, 4, 23, 12, 30);
fred.startDate = fredStartDate;
fred.department = engineering;
fred.save();

// Select by a linked field
_.each([engineering, engineering.id], function(linkto) {
    var s = db.employee.select().where("department", "=", linkto);
    TEST.assert_equal(1, s.length);
    TEST.assert_equal("Fred Bloggs", s[0].name);
});

// Check the unique index on the ref field works
TEST.assert_exceptions(function() {
    var ping = db.employee.create({name:"Ping",ref:O.ref(56,1000)}); // duplicates ref on fred
    ping.save();
});
var ping2 = db.employee.create({name:"Ping2",ref:O.ref(56,1001)});  // another ref, but different
ping2.save();

// Load it back
var fred2 = db.employee.load(fred.id);
fred2.save();   // do a null save and check everything still works