nor-nopg


nor-nopg
========

This is a Node.js library which implements NoSQL features over a PostgreSQL v9.3 database.

Usage
-----

``javascript
var nopg = require('nor-nopg');
`

Before using the database you should initialize it by calling: nor-nopg --pg=psql://localhost:5432/test init

See also
--------

* nor-app -- provides a HTTP interface in to a nor-nopg database
* nopg -- Shell scripting CLI for nor-nopg

Summary
-------

$3

| Name | Description | Example |
| ------ | ------------------------ | --------------------------------------------------------------- |
| NoPg | NoPg module | var NoPg = require('nor-nopg'); |
| db | NoPg instance | NoPg.start(...).then(function(db) { ... }); |
| doc | NoPg.Document instance | db.create()(...).shift().then(function(doc) { ... }); |
| type | NoPg.Type instance | db.declareType("name")().shift().then(function(type) { ... }); |

$3

| Short usage | Description | Tested at |
| ----------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------- |
| NoPg.start(...) | Get connection and start transaction | L42 |
| db.init() | Initialize database | L15 |
| db.create()({"hello":"world"}) | Create document without type | L41 |
| db.create("MyType")({"hello":"world"}) | Create document with type as string | L57 |
| db.create(type)({"hello":"world"}) | Create document with type as object | L306 |
| db.search()({"$id": "b58e402e-6b39-11e3-99c7-0800279ca880"}) | Search documents by id | L156 |
| db.search()({"hello": "world"}) | Search documents by values | L130 |
| db.search()(function(doc) { return doc.hello === 'world'; }) | Search documents by custom function | |
| db.search("Foobar")() | Search documents by type string | L185 |
| db.search("Foobar")({"name":"hello"}) | Search documents by type string with values | L219 |
| db.search(type)() | Search documents by type | |
| db.search(type)({"name":"hello"}) | Search documents by type as string with values | L254 |
| db.update(doc) | Edit document by instance of NoPg.Document | L93 |
| db.update(doc, {"hello": "world"}) | Edit document by plain document | L74 |
| n/a | Edit documents by type | |
| db.del(doc) | Delete document by instance of NoPg.Document | L113 |
| n/a | Delete documents by instance of NoPg.Type | |
| db.del(type) | Delete type by instance of NoPg.Type | L400 |
| db.del(attachment) | Delete attachment | |
| db.declareType("Product")({"$schema":{"type":"object"}}) | Create or replace type with name as string | |
| db.createType("Product")({"$schema":{"type":"object"}}) | Create type with name as string | |
| db.createType()({"$schema":{"type":"object"}}) | Create type without name | |
| db.update(type) | Edit type by instance of NoPg.Type | Yes |
| db.update(type, {$schema:{...}}) | Edit type by plain object | Yes |
| db.searchTypes({"$id": "b58e402e-6b39-11e3-99c7-0800279ca880"}) | Search types | |
| doc.createAttachment(data, {"content-type": "image/png"}) | Create attachments | |
| doc.searchAttachments() | Search attachments | |
| doc.getAttachment("b58e402e-6b39-11e3-99c7-0800279ca880") | Search attachments | |
| db.import('/path/to/tv4.js', {'$name': 'tv4'}) | Import or upgrade module in database | |

$3

| PostgreSQL | JavaScript | Description |
| --------------------------------------- | ---------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| id | obj.$id | Property with leading $ is mapped to the actual database table column |
| content->>'name' or content->'name' | obj.name | Property without leading $ is mapped to the property of the primary JSON data variable. It's content for NoPg.Documents and meta for other objects. The string or number operator is detected automatically from the type of the value. |

Connections and transactions
----------------------------

`javascript
nopg.start('postgres://user:pass@localhost/dbname').then(function(db) {
/ ... /
return db.commit();
});
`

You must call

* db.commit() to actually save any changes to the database; or
* db.rollback() to cancel the transaction

Initialize database
-------------------

The required table structures and initial settings and data can be created or upgraded
by calling db.init():

`javascript
nopg.start(PGCONFIG).init().then(function(db) {
...
});
`

Events
------

NoPG (from v1.1.0) has integrated support for events using PostgreSQL's tcn
extension.

Please note: You need to use .connect() instead of .start(...), since
listening in a transaction will block other threads!

$3

* Listener gets arguments:
* id - UUID of the document which has been created
* eventName - Name of the event
* type - Type name

`javascript
nopg.connect(PGCONFIG).then(function(db) {
return db.on('User#create', function db_on_create(id) {
// User with UUID as id was created
});
});
`

$3

* Listener gets arguments:
* id - UUID of the document which has been created
* eventName - Name of the event
* type - Type name

`javascript
nopg.connect(PGCONFIG).then(function(db) {
return db.on('User#create', function db_on_create(id) {
// User with UUID as id was created
db.on('User#'+id+'@', function db_on_create(id, eventName) {
// User with UUID as id was updated or deleted
}).fail(...).done();
});
});
`

$3

* Listener gets arguments:
* id - UUID of the document which has been changed
* eventName - Name of the event
* type - Type name

$3

* Listener gets arguments:
* id - UUID of the type which has been deleted
* eventName - Name of the event
* type - Type name

$3

* Listener gets arguments:
* id - UUID of the document which has been created
* eventName - Name of the event

`javascript
nopg.connect(PGCONFIG).then(function(db) {
return db.on('create', function db_on_create(id) {
// Document with UUID as id was created
});
});
`

$3

* Listener gets arguments:
* id - UUID of the document which has been changed
* eventName - Name of the event

$3

* Listener gets arguments:
* id - UUID of the document which has been deleted
* eventName - Name of the event

$3

* Listener gets arguments:
* id - UUID of the type which has been created
* eventName - Name of the event

$3

* Listener gets arguments:
* id - UUID of the type which has been changed
* eventName - Name of the event

$3

* Listener gets arguments:
* id - UUID of the type which has been deleted
* eventName - Name of the event

$3

* Listener gets arguments:
* id - UUID of the attachment which has been created
* eventName - Name of the event

$3

* Listener gets arguments:
* id - UUID of the attachment which has been changed
* eventName - Name of the event

$3

* Listener gets arguments:
* id - UUID of the attachment which has been deleted
* eventName - Name of the event

$3

* Listener gets arguments:
* id - UUID of the lib which has been created
* eventName - Name of the event

$3

* Listener gets arguments:
* id - UUID of the lib which has been changed
* eventName - Name of the event

$3

* Listener gets arguments:
* id - UUID of the lib which has been deleted
* eventName - Name of the event

$3

We also implement few events which are not through PostgreSQL. These will
happen only on the local instance.

#### Event: disconnect

When connection will be disconnected.

#### Event: commit

When transaction has been commited.

#### Event: rollback

When transaction has been rollback'd.

$3

If you want your application to use custom LISTEN or NOTIFY, we recommend
directly using
nor-pg. It has a
nice familiar interface for it.

There is no reason to implement this feature in NoPG since implementing it
wouldn't invent anything new; it would be direct wrapper for nor-pg.

Our promise implementation
--------------------------

We use an extended promise implementation which allows chaining of
multiple methods together.

Under the hood we are using q promises
which are extended using nor-extend.

However these extended features are not required. You may use our promises
just like any other q-promises.

$3

`javascript
NoPg.start(...).create("Group")({"name":"Bar"}).create("User")({"name":"Foo"}).then(function(db) {
var group = db.fetch();
var user = db.fetch();

// ... do your magic at this point

return db.commit();
}).fail(function(err) {
console.error(err);
}).done();
`

About the PostgreSQL ORM Mapping
--------------------------------

The module has simple ORM mappings for all of our PostgreSQL tables.

| JavaScript constructor | PostgreSQL table | Default JSON column |
| ---------------------- | ---------------- | ------------------- |
| NoPg.Document | documents | content |
| NoPg.Type | types | meta |
| NoPg.Attachment | attachments | meta |
| NoPg.Lib | libs | meta |
| NoPg.Method | methods | meta |
| NoPg.DBVersion | dbversions | n/a |

$3

These constructors will take an object and convert it to JavaScript instance
of that PostgreSQL table row.

Example object:

`javascript
{
"name": "Hello",
"foo": "bar",
"age": 10
"$id": "8a567836-72be-11e3-be5d-0800279ca880",
"$created": "",
"$updated": ""
}
`

The special $ in the name makes it possible to point directly to a column
in PostgreSQL row.

Any other property points to the column in default JSON column.

For example a obj.$meta.foo in NoPg.Type instance has the same value as
obj.foo unless the ORM instance has been changed by the user.

Documents
---------

$3

#### Create document without type

`javascript
db.create()({"hello":"world"}).then(function(db) {
var doc = db.fetch();
console.log("Successfully created new document: " + util.inspect(doc) );
});
`

#### Create document with type as string

`javascript
db.create("MyType")({"hello":"world"}).then(function(db) {
var doc = db.fetch();
console.log("Successfully created new document: " + util.inspect(doc) );
});
`

Tested at test-nopg.js:57.

#### Create document with type as object

`javascript
db.create(type)({"hello":"world"}).then(function(db) {
var doc = db.fetch();
console.log("Successfully created new document: " + util.inspect(doc) );
});
`

$3

#### Search documents by id

`javascript
db.search()({"$id": "b58e402e-6b39-11e3-99c7-0800279ca880"}).then(function(db) {
var list = db.fetch();
console.log("Found documents: " + util.inspect(list) );
});
`

#### Search documents by values

`javascript
db.search()({"hello": "world"}).then(function(db) {
var list = db.fetch();
console.log("Found documents: " + util.inspect(list) );
});
`

#### Search documents by custom function

`javascript
db.search()(function(doc) {
return doc.hello === 'world';
}).then(function(db) {
var list = db.fetch();
console.log("Found documents: " + util.inspect(list) );
});
`

#### Search documents by type string

`javascript
db.search("Foobar")().then(function(db) {
var list = db.fetch();
console.log("Found documents: " + util.inspect(list) );
});
`

#### Search documents by type string with values

`javascript
db.search("Foobar")({"name":"hello"}).then(function(db) {
var list = db.fetch();
console.log("Found documents: " + util.inspect(list) );
});
`

#### Search documents by type

`javascript
db.search(type)().then(function(db) {
var list = db.fetch();
console.log("Found documents: " + util.inspect(list) );
});
`

$3

#### Edit document by instance of NoPg.Document

`javascript
doc.hello = "world";

db.update(doc).then(function(db) {
console.log("Successfully edited document: " + util.inspect(doc) );
});
`

Tested at test-nopg.js:93.

#### Edit document by plain document

`javascript
db.update(doc, {"hello": "world"}).then(function(db) {
console.log("Successfully edited document: " + util.inspect(doc) );
});
`

Tested at test-nopg.js:74.

#### Edit documents by type

`javascript
/ n/a /
`

$3

#### Delete document by instance of NoPg.Document

`javascript
db.del(doc).then(function(db) {
console.log("Document deleted succesfully.");
});
`

Tested at test-nopg.js:113.

#### Delete documents by instance of NoPg.Type

`javascript
// n/a
`

#### Delete type by instance of NoPg.Type

`javascript
db.del(type).then(function(db) {
console.log("Type deleted succesfully.");
});
`

#### Delete attachment

`javascript
db.del(attachment).then(function(db) {
console.log("Attachment deleted succesfully.");
});
`

Types
-----

$3

#### Create or replace type with name as string

`javascript
db.declareType("Product")({"schema":{"type":"object"}}).then(function(db) {
var type = db.fetch();
console.log("Successfully fetched a type: " + util.inspect(type) );
});
`

#### Create a new type with name as string

`javascript
db.createType("Product")({"schema":{"type":"object"}}).then(function(db) {
var type = db.fetch();
console.log("Successfully created new type: " + util.inspect(type) );
});
`

#### Create type without name

`javascript
db.createType()({"schema":{"type":"object"}}).then(function(db) {
var product_type = db.fetch();
console.log("Successfully created new type: " + util.inspect(product_type) );
});
`

$3

#### Edit type by instance of NoPg.Type

`javascript
type.schema = {..};
db.update(type).then(function(db) {
console.log("Successfully edited type: " + util.inspect(type) );
});
`

#### Edit type by plain object

`javascript
db.update(type, {schema:{...}}).then(function(db) {
console.log("Successfully edited type: " + util.inspect(type) );
});
`

$3

`javascript
db.searchTypes({"$id": "b58e402e-6b39-11e3-99c7-0800279ca880"}).then(function(db) {
var list = db.fetch();
console.log("Found types: " + util.inspect(list) );
});
`

Attachments
-----------

$3

`javascript
nopg.createAttachment(doc)(file, {"content-type": "image/png"}).then(function(db) {
var file = db.fetch();
console.log("Successfully created new attachment: " + util.inspect(file) );
});
`

* If doc is undefined then document is looked from previous value in the buffer which must by nopg.Attachment or nopg.Document.

$3

#### Search all attachments

`javascript
doc.searchAttachments(doc)(opts).then(function(db) {
var list = db.fetch();
console.log("Found attachments: " + util.inspect(list) );
});
`

* If you omit doc, the last element in the queue will be used.
* If you omit opts, then all attachments are listed otherwise only matching.

#### Get attachment by ID

`javascript
doc.getAttachment("b58e402e-6b39-11e3-99c7-0800279ca880").then(function(db) {
var attachment = db.fetch();
console.log("Found attachment: " + util.inspect(attachment) );
});
`

Libs
----

$3

`javascript
db.import('/path/to/tv4.js', {'name': 'tv4'}).then(function(db) {
console.log("Library imported succesfully.");
});
`

Run tests
---------

Database configurations can be set using PGCONFIG:

`
export PGCONFIG='pg://user:password@localhost/db'
`

The actual test can be run: npm test

You must delete the data if you need to run the test suite again for the same database:

`
psql -q db < scripts/cleanup.sql
`

Please note: psql does not follow PGCONFIG environment variable!

Run lint test
-------------

npm run lint

Commercial Support
------------------

You can buy commercial support from Sendanor.

TODO
----

We use Trello board to organize development and keep track on things to do.

Internal Database Schema
------------------------

$3

| Name | Type |
| ---------- | ------------- |
| version | integer |
| modified | timestamptz |

$3

| Name | Type |
| ----------- | ------------- |
| id | uuid |
| name | text |
| schema | json |
| validator | text |
| meta | json |
| created | timestamptz |
| modified | timestamptz |

$3

| Name | Type |
| ----------- | ------------- |
| id | uuid |
| types_id | uuid |
| type | text |
| name | text |
| body | text |
| meta | json |
| active | boolean |
| created | timestamptz |
| modified | timestamptz |

$3

| Name | Type |
| ---------- | ------------- |
| id | uuid |
| content | json |
| types_id | uuid |
| created | timestamptz |
| modified | timestamptz |
| type | text |

$3

| Name | Type |
| -------------- | ------------- |
| id | uuid |
| documents_id | uuid |
| content | bytea |
| meta | json |
| created | timestamptz |
| modified | timestamptz |

$3

| Name | Type |
| -------------- | ------------- |
| id | uuid |
| name | text |
| content | text |
| meta | json |
| created | timestamptz |
| modified | timestamptz` |

$3

This database structure has not been updated.

!ERS