jsrel

JSRel
=========

description
------------
JavaScript synchronous RDB (Relational database) without SQL

Available in modern browsers, Node.js and Titanium(NEW!).

This ISN'T ORM, but SQL-less RDB implemented in JavaScript!

Get it!
----------
``bash
$ npm install jsrel
`

or

`bash
$ curl https://raw.github.com/shinout/jsrel/master/install-jsrel.sh | sh
`


API at a glance
----------------
First, define the schema

`js
var JSRel = require("jsrel");
var db = JSRel.create("dbname", {schema:
{ user: { name : true, is_activated: "on", $uniques: "name"},
book: { title: true, price: 1, author: "user", $indexes: "title" },
}});
`

Second, insert data

`js
if (!db.loaded) { // if loaded from saved data, omits this section
var u1 = db.ins('user', {name: 'shinout'});
var u2 = db.ins('user', {name: 'xxxxx', is_activated: false});
var b1 = db.ins('book', {title: 'how to jsrel', price: 10, author: u1});
var b2 = db.ins('book', {title: 'JSRel API doc', price: 20, author_id: u1.id});
}
`

Find them!

`js
var users = db.find('user', {is_activated: true});
`

Get one!

`js
var shinout = db.one('user', {name: "shinout"});
`

Greater Than, Less Equal!

`js
var booksGreaterThan5 = db.find('book', { price: {gt: 5} } );
var booksLessEqual15 = db.find('book', { price: {le: 15} } );
`

Like xxx%

`js
var booksLikeJS = db.find('book', { title: {like$: "JS"} } );
`

Join!

`js
var usersJoinBooks = db.find('user', {is_activated: true}, {join: "book"});
`

OrderBy! Offset! Limit!

`js
var users = db.find('user', null, {order: "name", limit : 10, offset : 3} );
`


Perpetuation

`js
db.save();
`

Export / Import

`js
var str = db.export();
var newDB = JSRel.import("newID", str);
`

dump as SQL!

`js
var sql = db.toSQL();
`

suitable applications
---------------------

- rich client applications
- tiny serverside applications
- client caching
- mock DB

NOT suitable for applications which require scalability.


motivation
-------------
Thinking about the separation of the Model layer.

If we connect to DB asynchronously, we must handle lots of callbacks in a model method.

`js
model.getUserBooks = function(name, callback) {
db.find("user", {name: name}, function(err, users) {
db.find("book", {user_id: users[0].id}, callback);
});
};
`

If we access to DB synchoronously, we can easily write human-readable model APIs.

`js
model.getUserBooks = function(name) {
var user = db.find("user", {name: "xxyy"})[0];
return db.find("book", {user_id: user.id});
};
`

Also, synchoronous codes have an advantage of error handling.

###for those who dislike Blocking APIs###

Why not making it standalone using WebWorker (browsers) or child_process.fork() (Node.js)?
Then the whole calculation process doesn't affect the main event loop and we can get the result asynchronously.

I prepared another JavaScript library for this purpose.

standalone.

Then, we can access model methods like

`js
model.getUserBooks("user01", function(err, result) {
})
`

by defining

`js
model.getUserBooks = function(name) {
var user = db.find("user", {name: "xxyy"})[0];
if (!user) return [];
return db.find("book", {user_id: user.id});
};
`

That is, try/catch and asynchronous APIs are automatically created via standalone.

See make it standalone for detailed usage.


installation
-------------

`bash
$ npm install jsrel
`

for development in Titanium or web browsers,

`bash
$ curl https://raw.github.com/shinout/jsrel/master/install-jsrel.sh | sh
`

in browsers,

`html


`

in Node.js or Titanium,

`js
var JSRel = require('jsrel');
`

is the way to load the library.

In browsers, the variable "JSRel" is set to global.

In Web Worker,

`js
importScripts('/pathto/SortedList.js', '/pathto/jsrel.js');
`

See also make it standalone.

dependencies
-------------
JSRel internally uses SortedList
When installed with npm, it is automatically packed to node_modules/sortedlist
Otherwise, it is recommended to run the following command to prepare jsrel and sortedlist.

`bash
$ curl https://raw.github.com/shinout/jsrel/master/install-jsrel.sh | sh
`

In Titanium, you have to set jsrel.js and SortedList.js at the top of Resources directory.


JSRel API documentation
-------------------------

JSRel

- JSRel.use(uniqId, options)
- JSRel.create(uniqId, options)
- JSRel.createIfNotExists(uniqId, options)
- JSRel.import(uniqId, data_str, options)
- JSRel.$import(uniqId, data_str, options)
- JSRel.uniqIds
- JSRel.isNode
- JSRel.isBrowser
- JSRel.isTitanium

instance of JSRel (jsrel)

- jsrel.table(tableName)
- jsrel.save()
- jsrel.export(noCompress)
- jsrel.$export(noCompress)
- jsrel.on(eventName, func, options)
- jsrel.off(eventName, func)
- jsrel.toSQL(options)
- jsrel.origin()
- jsrel.drop()
- jsrel.id
- jsrel.name
- jsrel.tables
- jsrel.schema
- jsrel.loaded
- jsrel.created


instance of JSRel Table (table)

- table.columns
- table.ins(obj)
- table.upd(obj, options)
- table.find(query, options)
- table.one(id)
- table.one(query, options)
- table.del(id)
- table.del(query)


shortcut

- jsrel.ins(tableName, ...)
- jsrel.upd(tableName, ...)
- jsrel.find(tableName, ...)
- jsrel.one(tableName, ...)
- jsrel.del(tableName, ...)

$3

`js
{
tableName1: tableDescription,
tableName2: {
columnName1 : columnDescription,
columnName2 : columnDescription
}
}
`

table description

column description


$3

`js
var savedata = jsrel.origin();
var newdb = JSRel.import("new_db", savedata);
`

$3


`js
[table1, table2, ...]
`

$3

`js
var schema = db.schema; // created dynamically
var schema2 = db.schema; // created dynamically
schema === schema2 // false, but deeply equal

var db2 = JSRel.use("db2", {schema: schema}); // the same structure as db
`

$3

`js
db = JSRel.use("/path/to/file", {schema: {tbl1: {name:true}, tbl2: {n: 1}}});

// if not loaded, then inputs initialized data
if (!db.loaded) {
db.ins("tbl1", {name: "shinout"});
db.ins("tbl1", {name: "nishiko"});
db.ins("tbl2", {n: 37});
db.ins("tbl2", {n: -21});
}
`


$3

`js
db = JSRel.use("/path/to/file", {schema: {tbl1: {name:true}, tbl2: {n: 1}}});

// if created, then inputs initialized data
if (db.created) {
db.ins("tbl1", {name: "shinout"});
db.ins("tbl1", {name: "nishiko"});
db.ins("tbl2", {n: 37});
db.ins("tbl2", {n: -21});
}
`

instanceof JSRel.Table (shown as table)
------


$3

We can specify id in insertion.

`js
table.ins({id: 11, name: "iPhone"});
`

When the table already has the same id, an exception is thrown.


#### relation handling in insertion ####
OK, let's think upon the following schema.

`js
var schema = { user: {
nickName : true,
fitstName: false,
lastName : false
},
card: {
title : true,
body : true
},
user_card {
user: "user",
card: "card",
owner: {type : "user", required: false}
$uniques: { user_card: ["user", "card"] }
}
}
`

First, inserts users and cards.

`js
var jsrel = JSRel.use('sample', {schema: schema});

var uTable = jsrel.table('user');
var shinout = uTable.ins({nickName: "shinout"});
var nishiko = uTable.ins({nickName: "nishiko"});
var cTable = jsrel.table('card');
var rabbit = uTable.ins({title: "rabbit", body: "It jumps!"});
var pot = uTable.ins({title: "pot", body: "a tiny yellow magic pot"});
`


Then, inserts these relations.

`js
var ucTable = jsrel.table('user_card');
ucTable.ins({ user: shinout, card: rabbit });
`


We can also insert these relation like

`js
ucTable.ins({ user_id: nishiko.id, card_id: pot.id });
ucTable.ins({ user_id: 1, card_id: 2 }); // 1: shinout, 2: pot
`

Remember that user_id and card_id are automatically generated and it represent the id column of each instance.
When we pass an invalid id to these columns, an exception is thrown.

`js
ucTable.ins({ user_id: 1, card_id: 5 }); // 1: shinout, 5: undefined!
`

When a relation column is not required, we can pass null.

`js
ucTable.ins({ user: nishiko, card_id: 1, owner_id: null });
`

When duplicated, xxxx_id priors to xxxx (where xxxx is the name of the original column).

`js
ucTable.ins({ user: nishiko, user_id: 1, card_id: 1 }); // user_id => 1
`

#### inserting relations ####

`js
obj.rel_table = [relObj1, relObj2, ...];
table.ins(obj);
`
relObj1, relObj2 are also inserted to table "rel_table" containing the new id as the external key.

If the main table is related to the rel_table multiply,
you must specify the column like

`js
obj["rel_table.relcolumn"] = [relObj1, relObj2, ...];
table.ins(obj);
`

$3


updating related tables
`js
obj.rel = {id: 3 }
obj.rel_id = 1
// in this case, relObj is prior to rel_id
table.upd(obj, {append: append});

var rel_id = table.one(obj, {id: obj.id}, {select: "rel_id"}); // 3. not 1
`


`js
obj.rel_table = [relObj1, relObj2, ...];
table.upd(obj, {append: append});
`

if relObj contains "id" column, updating the object.
Otherwise, inserting the object.
If options.append is false or not given, already existing related objects are deleted.

If the main table is related to the rel_table multiply,
you must specify the column like

`js
obj["rel_table.relcolumn"] = [relObj1, relObj2, ...];
table.upd(obj, {append: append});
`

$3

`js
[ {id: 1, name: "shinout"}, {id: 2, name: "nishiko"}, ...]
`

#### join description ####
sample data

group

`js
var result = db.table('user').find({name: "shinout"}, {join: JOIN_VALUE});
`


Fetching 1:N related objects

var result = db.table('group').find({name: "mindia"}, {join: JOIN_VALUE});

Fetching N:M related objects

var result = db.table('user').find({name: "shinout"}, {join: JOIN_VALUE});

$3

`js
var jsrel = JSRel.use('sample', {schema: schema});

var uTable = jsrel.table('user');
var cTable = jsrel.table('card');
var ucTable = jsrel.table('user_card');

var shinout = uTable.ins({nickName: "shinout"});
var nishiko = uTable.ins({nickName: "nishiko"});

var rabbit = uTable.ins({title: "rabbit", body: "It jumps!"});
var pot = uTable.ins({title: "pot", body: "a tiny yellow magic pot"});

ucTable.ins({ user: shinout, card: rabbit });
ucTable.ins({ user: nishiko, card: rabbit });
ucTable.ins({ user: shinout, card: pot });
`


Next, delete shinout.

`js
uTable.del(shinout);
`

Then, the dependent records ( shinout-rabbit, shinout-pot ) are also removed.

`js
ucTable.find().length; // 1 (nishiko-rabbit)
`


shortcut
--------

- jsrel.ins(tableName, ...)
- jsrel.upd(tableName, ...)
- jsrel.find(tableName, ...)
- jsrel.one(tableName, ...)
- jsrel.del(tableName, ...)

are, select table via jsrel.table(tableName) in the first place.
Then run the operation using the remaining arguments.

for example,

`js
jsre.ins('user', {nickName: "shinout"});
`

is completely equivalent to

`js
jsrel.table('user').ins({nickName: "shinout"});
`


make it standalone
--------------------
standalone is a library to make a worker process / thread which can communicate with master.

Here are the basic concept.

master.js

`js
standalone("worker.js", function(model) {

model.getSongsByArtist("the Beatles", function(err, songs) {
console.log(songs);
});

});
`


worker.js

`js
var db = JSRel.use("xxx", {schema: {
artist: {name: true},
song : {title: true, artist: "artist"}
}});
var btls = db.ins("artist", {name: "the Beatles"});
db.ins("song", {title: "Help!", artist: btls});
db.ins("song", {title: "In My Life", artist: btls});

var model = {
getSongsByArtist: function(name) {
return db.find("artist", {name : name}, {join: "song", select : "song"});
}
};
standalone(model);
`

In master.js, we can use "getSongsByArtist" asynchronously, catching possible errors in err.

In Node.js, standalone spawns a child process.

In browsers, standalone creates a WebWorker instance.

In Titanium, standalone is not supported.

$3

Because Node.js and WebWorker has a different requiring system,
We must be careful of loading scripts.


in Node.js (worker.js)

`js
var JSRel = require('jsrel');
var standalone = require('standalone');
`

This is enough.

in browsers (worker.js)

`js
importScripts('/pathto/SortedList.js', '/pathto/jsrel.js', '/pathto/standalone.js');
``

Don't forget to import SortedList (which JSRel depends on).

LICENSE
-------
(The MIT License)

Copyright (c) 2012 SHIN Suzuki

Permission is hereby granted, free of charge, to any person obtaining
a copy of this software and associated documentation files (the
'Software'), to deal in the Software without restriction, including
without limitation the rights to use, copy, modify, merge, publish,
distribute, sublicense, and/or sell copies of the Software, and to
permit persons to whom the Software is furnished to do so, subject to
the following conditions:

The above copyright notice and this permission notice shall be
included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED 'AS IS', WITHOUT WARRANTY OF ANY KIND,
EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY
CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT,
TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE
SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.