Mysql-live-client NPM

mysql-live-client

mysql-live for server side

Click here to read the online book

These documents are not fully updated with the new features, please be pattient one or two days in order to complete writing, I put great effort into.

Install

mysql-live-client package is designed to work with the most modern tools such as webpack and browserify.

(Recommended method) Using Node's NPM:

$ npm install --save mysql-live-client

Alternatively, you can serve the file mysql-live-client.js found at the 'compat' directory of this repository. This is the concated and minified version, which includes the compiled/*, lodash.min.js and socket.io-client.js files.

<script src="https://raw.githubusercontent.com/nodets/node-mysql-live-client/master/compat/mysql-live-client.js"> </script>

Sometimes I may also ask you to install the latest version from Github to check if a bugfix is working. In this case, please do:

$ npm install --save nodets/node-mysql-live-client

Principles of Mysql Live

Database Everywhere. You can use the same methods to access your database from the client or the server.
Latency Compensation. On the client, Mysql Live prefetches data and simulates models to make it look like server method calls return instantly. In the near future
In Mysql Live, realtime is the default. All layers, from database collection to methods, update themselves automatically when necessary.
Simplicity Equals Productivity. The best way to make something seem simple is to have it actually be simple. Mysql Live's main functionality has clean, classically beautiful APIs

Introduction

This is the client side package of the mysql-live package.

Here is an example on how to use it:

//server-side 

var Server = require("http").Server;
var express = require("express");

//important stuff
var live = require("mysql-live").live;

//or ES6 syndax: import live from "mysql-live";

var app = express();
var http = Server(app);

var mysqlConnectionString="mysql://kataras:pass@127.0.0.1/taglub?debug=false&charset=utf8";

//you can have multiple mysql server in the same project, just assign the variable.
var Mysql = live(mysqlConnectionString,http);
//

//Collection initialization
var usersCollection = Mysql.Collection("usersCollection", "users"); 
//

Mysql.publish("allUsers", usersCollection);

//and you can do something like that too, both .publish can exists.
Mysql.publish("userById", function(userid){
	var criteria = usersCollection.selector()
				   .where("userId").eq(userid)
				   .build();
				   
	return usersCollection.find(criteria);
});

var server = http.listen(8080, function(){
    console.log("=============================");
    console.log("Server started at port 8080");
    console.log("=============================");
});

//client-side

import Mysql from "mysql-live-client";

//you can totally skip Mysql.connect if your mysql-live server's side is in the same domain
Mysql.connect(); //or if your mysql-live server's side is not at the same domain http://otherdomain.com:8080


var usersCol = Mysql.Collection("usersCollection"); 
//the name of the collection you want to register for.

usersCol.onChange(function(event,next){
	//event.newItem, event.selector, event.items
	//event.name = 'receive' | 'insert' | 'update' | 'remove'
	//your collection is synchronized with the actual mysql database.
	console.log(event.name + 'new items: ', usersCol.items);
	next();
});

Mysql.subscribe("allUsers");

setTimeout(function(){
	console.log('I changed my mind and I want only the user with id 16 in usersCol');
	Mysql.subscribe("userById",16);
},3000);

Enable Binary Logs

First of all you have to enable binary logs in your MySQL Server, In most of the cases this is enabled by default, but if not I will explain you how to enable it.

IF MYSQL SERVER VERSION IS GREATER THAN OR EQUAL TO 5.7 follow this:

  IF OS === Windows 
	IF (MYSQL INSIDE xampp,wamp) 
	    1. Open: C:/ xampp/ OR wamp/ mysql/YOUR_MYSQL_VERSION/my.ini 
	ELSE  
		1. Open explorer and write : %PROGRAMDATA%/MySQL/MySQL Server 5.7/my.ini
		
    2. Go to the lines(119-120) which you can see these contents: 
	  # Binary Logging.
	  # log-bin
    3. Just uncomment the # log-bin, finall result must look like that:
	  # Binary Logging.
	  log-bin
	  								
    4. Restart the mysql server service and you are ready.

 ELSE IF OS === (L)Unix
	IF (MYSQL INSIDE lampp) 
	    1. Open explorer and open opt/lampp/etc/my.cnf
	ELSE  
		1. Find where is my.cnf using these one of these shell commands: 
			locate my.cnf
			whereis my.cnf
			find . -name my.cnf
			
		   and open the my.cnf file.
		
   2. Go to the line(119-120) which you can see these contents: 
	  # Binary Logging.
	  # log-bin
   3. Just uncomment the # log-bin, finall result must look like that:
	  # Binary Logging.
	  log-bin
	  								
   4. Restart the mysql server service and you are ready.

ELSE IF MYSQL SERVER VERSION IS LESS THAN OR EQUAL TO 5.6
    Watch on youtube this video: https://www.youtube.com/watch?v=xrTBFZyn-Bk

Collections

Mysql Live hanles database rows in collections. JavaScript objects stored in client collections are called documents (like your table's stores rows into your mysql database). To get started, declare a collection with Mysql.Collection.

Server-side
Mysql.Collection(name, tablename) Register a managed (synchronized) database-servercollection.
name String The name of the collection.
tablename String The name of the actual mysql's database table which data exists.
Client-side
Mysql.Collection(name) Register a managed (synchronized) client collection.
name String The name of the collection.

Calling the Mysql.Collection function creates a collection object which acts just like a Mysql table but are javascript objects/documents. You have to pass a name when you create the collection, then you have to create a client collection with the same name.
To allow both client code and server code to access the same collection using the same API, you have to create a client collection with the same name.

Here's an example of declaring two named, synchronized collections, one by the server and one lives in client (which the data are stored when a client subscribes to a record set, we will speak for this later):

//server-side
var users = Mysql.Collection("usersCollection","users");

//client-side
var users = Mysql.Collection("usersCollection");

//"usersCollection" is the name

The server side collection just stores the information in order to publish documents from database . No rows or data are stored to your server-side.

When data arrives they are stored only on the client side collections.

Use findSingle or find to retrieve documents from a collection.

If called by a client collection then fetches from it's local stored items. If called by a server collection then returns a cursor which publication use this in order to retrieve the database rows when a client subscribes, with the selector you passed in the find method.

Both sides
collection.findSingle(selector) Finds the first document that matches the selector, as ordered.
selector Javascript based Selector, Object ID, or String A query describing the documents to find, like lodash and simple javascript arrays for client side collections.
.findSingle({ authorId:16 });
For server side collection you can use CriteriaBuilder (which you will find on node-mysql-wrapper package) or simple javascript object, the only difference is that on server side we need more coblex selectors, with conditionals...
.findSingle({authorId: '= 16'});

This method lets you retrieve a specific document from your collection. The findSingle method is most commonly called with a specific row/document_id:

var story = storiesCollection.findSingle(42);

The mysql-live is smart enough, to check the primary key columns of your database, don't worry about this.

However, you can also call findSingle with a more coblex selector, which is an object that specifies a required set of attributes of the desired document. For example, this selector

//client-side selector structure.
var story = storiesCollection.findSingle({authorId:16, title: 'The right way is programming with Mysql-live!'});

At server side we need more advanced way to select our data, take a look on these:

//server-side selector, with CriteriaBuilder's structure.
var criteria = storiesCollection.selector()
				.where("authorId").eq(16)
				.where("title").eq("The right way ...")
				.orderByDesc("storyId")
				.limit(10);
var story = storiesCollection.findSingle(criteria);

//server-side selector, with object structure.
var story = storiesCollection.findSingle({authorId:'= '+16, title: '= The right way is programming with Mysql-live!'});

Both sides
collection.find(selector) Find the documents in a collection that match the selector.
selector Javascript based selector A query describing the documents to find, like lodash and simple javascript arrays.

The find method is similar to findSingle, but instead of returning a single object it returns an array objects.

The server's collection find method is similar to client's collection find, but instead of returning object/s it returns a Live Cursor. A cursor is a special object that represents a list of objects/documents that might be returned from a query. Cursors are passed automatically when a client subscribes to a record set that owns to a collection.

Server-side
collection.fetch/fetchSingle(selector, callback) Fetch a document from the database
selector Javascript(object) based selector or ID A query describing the documents to fetch from the database
callback Function Optional. If present, called with rows were found as its argument.
returns Promise, if you don't pass a callback then the server-side collections return Promises.

Collections have some extra functions that helps you manipulate your database rows directly from both sides without any code required, these are insert, update, save, remove functions. We will speak about security later.

Both sides
collection.insert(obj, callback) Insert a document/object in the collection. Returns its final result after mysql inserts it, which means you have access to the ID too.
obj Object The document to insert.
callback Function Optional. If present, called with the final result object as the first argument.

Here's how you insert a document into a collection:

storiesCollection.insert({
  createdBy: Mysql.socket.id,
  createdAt: new Date(),
  title: "My first story!",
  content: "Today is a good day, I am learning how to use mysql-live!"
});

Both sides
collection.update(objectWithId, callback) Modify one document in the collection. Returns the final updated object, propably the same as passed.
objectWithId Object The document to update. The difference from .insert is that object has it's table's ID. You can use .save(obj,callback) also. I will not describe it because it checks if id is presents inside the obj, if yes then .update else .insert.
callback Function Optional. If present, called with the final result object as the first argument.

Here's how you update a document into a collection:

storiesCollection.update({
  storyId:42,
  title: "My super story!"
});

Both sides
collection.remove(id, callback) Remove a document from the collection
id Object's ID Specifies which document to remove by it's id value.
callback Function Optional. If present, called with affected rows number as its argument (if remove success will be 1, else 0).

Use remove carefully — there's no way to get that data back.

As with update, client code can only remove documents by _id, whereas server code and methods can remove documents using any selector.

Server-side
collection.allow(options) Allow users to write directly to this collection from client code, subject to limitations you define.
options subscribe, insert, update, remove Function Functions that look at a proposed modification to the database and return true if it should be allowed.
subscribe option if you want to control which client can/can't subscribe a specific collection

In newly created apps, Mysql-live allows almost any calls to insert, update, and remove from any client or server code. Obviously, if any user could change the database whenever they wanted it would be bad for security, so it is important to remove the insecure option and specify some permissions rules, in your code, before .allow/.deny options, write:

Mysql.insecure(false);

Once you have removed the insecure option, use the allow and deny methods to control who can perform which operations on the database. By default (with .insecure(false)) , all operations on the client are denied, so we need to add some allow rules. Keep in mind that server code and code inside methods are not affected by allow and deny — these rules only apply when insert, update, and remove are called from untrusted client code.

For example, we might say that users can only create new posts if the createdBy field matches the ID of the current user, so that users can't impersonate each other

//server-side
// In a file loaded on the server (ignored on the client)
postCollection.allow({
  insert: function (socket, post) {
    // can only create posts where you are the author
    var userId = Mysql.getPassport(socket).userId;
    return post.createdBy === userId;
  },
  remove: function (socket, post) {
    // can only delete your own posts
    var userId = Mysql.getPassport(socket).userId;
    return post.createdBy === userId;
  }
  // since there is no update field, all updates
  // are automatically denied
});

The allow method accepts three possible callbacks: subscribe, insert, remove, and update. The first argument to all three callbacks is the socket of the logged in user, and the remaining arguments are as follows:

subscribe(socket,subscriptionName)
subscriptionName is the subscription's name that the client try to subscribe, Return true if you allow this client to subscribe to the collection, false otherwise.
insert(socket, document)
document is the document that is about to be inserted into the database. Return true if the insert should be allowed, false otherwise.
update(socket, document)
document is the document that is about to be modified. Return true if the update should be allowed, false otherwise.
remove(socket, primaryKeyValue)
primaryKeyValue is the document's ID that is about to be removed from the database. Return true if the document should be removed, false otherwise.

Server-side
collection.deny(options) Override allow rules.
options subscribe, insert, update, remove Function Functions that look at a proposed modification to the database and return true if it should be denied, even if an allow rule says otherwise.
subscribe option if you want to control which client can/can't subscribe a specific collection

Methods

Methods are server functions that can be called from the client and server. They are useful in situations where you want to do something more complicated than insert, update or remove, or when you need to do data validation that is difficult to achieve with just allow and deny.

Methods can call a client callback which could return values or throw errors, this helps you to write async/callback code style inside these methods.

Server-side
Mysql.methods(methods) Defines functions that can be invoked over the network by clients.
methods Object Dictionary whose keys are method names and values are functions.

Calling Mysql.methods on the server defines functions that can be called remotely by clients.

Here's an example of a method that checks its arguments and throws an error:

//Server-side

Mysql.methods({
  commentOnPost: function (comment, postId,send) { //send(err,result) example on client side below
    // Your custom login to check the arguments...
    // .... checkChars(comment); ...
    
    if (! this.passport.userId) {
       send("Must be logged in to post a comment.", undefined);
        return;
    }

    // ... do some async stuff ...

    send(undefined,"something to the client");
  },

  otherMethod: function () {
    // ... do other stuff ...
  }
});

Inside your method definition, *this keyword* is bound to a method invocation object, which has several useful properties, including this.socket, which identifies the currently logged-in user, this.passport which identifies the authenticated currently logged-in user/socket, this.isServer if true then the method has been called from server and this.isClient if true the method has been called from the client side.

You don't have to put all your method definitions into a single Mysql.methods call; you may call it multiple times, as long as each method has a unique name.

Both sides

Mysql.call(name, arg1, arg2..., asyncCallback) Invokes a method passing any number of arguments.
name String Name of method to invoke
arg1, arg2... EJSON-able Object Optional method arguments
asyncCallback Function Optional callback, which is called asynchronously with the error or result after the method is complete. If not provided, the method runs, but client doesn't know when the action on the server will complete, this is ok if action is doing database manipulation because mysql-live will update the necessary client collections automatically :)

This is how you call a method.

On the client

Methods called on the client run asynchronously, so you need to pass a callback in order to observe the result of the call. The callback will be called with any arguments YOU choose, arguments can be error and result.

Here's an example of calling the commentOnPost method with arguments comment and postId:

// Asynchronous call with a callback on the client
Mysql.call('commentOnPost', comment, postId, function (err, result) {
  if (err) {
    // handle error
  } else {
    // examine result
  }
});

Mysql Live tracks the database updates performed as part of a method call, and waits to invoke the client-side callback until all of those updates have been sent to the client.

On the server

On the server, you don't have to pass a callback if its body is synchronous — the method call will simply block until the method is complete, returning a result or throwing an exception, just as if you called the function directly:

// Synchronous method register

Mysql.methods({
	commentOnPostSync: function(userId,comment,postId){

    if (!userId) {
       return "user id does not exists";
    }

    // ... do some syc stuff ...

    return "something to the server";

	}
});

// Synchronous call on the server with no callback
var result = Mysql.call('commentOnPostSync', comment, postId);

//Of course, you can use the same method for client and server too, you can check and return values if this.isServer, or call a callback if it is called from the client side.

Publish and subscribe

Server can publish sets of rows with Mysql.publish, and clients can subscribe to those publications with Mysql.subscribe. Any rows the client subscribes to will be available through the find method or the .items property of client collections.

You can use Mysql.publish and Mysql.subscribe to control what rows flow from the server to its clients.

Server-side
Mysql.publish(name, cursor) Publish a record set.
name String Name of the record set.
cursor Function returns Cursor or Cursors | Cursor | Cursor[] | Collection | Collection[]
func Function returns Cursor Function called on the server each time a client handler object, described below. If the client passed same arguments.
Cursor or Cursor[] Cursor or Array of Cursors Cursor is just a collection.find(criteriaselector) or Mysql.procedure(...)
Collection or Collection[] As the second parameter you can pass also only the collection or the collections you want to publish to the clients, to be noticed: with this method all rows will be published.

To publish data to clients, call Mysql.publish on the server with two arguments: the name of the record set (no the collection name, but you can do that), and a publish function that will be called each time a client subscribes to this record set.

Publish functions typically return the result of calling collection.find(criteria) on some collection with a criteria that narrows down the set of rows to publish from that collection:

Publish stories by client criterias/selector:

// Publish the stories by authorId
Mysql.publish("storiesByAuthor", function(author_id){
	return storiesCollection.find(authorId: '= '+author_id);
});

You can publish rows from the current client:

Mysql.publish("myStories", function(){
	var passport = Mysql.getPassport(this);
	var criteria = storiesCollection.selector()
				   .where("authorId").eq(passport.userId)
				   .build();
				   
	return storiesCollection.find(criteria);
});

All rows from a table:

Mysql.publish("allStories", storiesCollection);

Publish more than one collection at one publication:

Mysql.publish("storiesAndUsers", storiesCollection, usersCollection);

Publish with server only selector :

Mysql.publish("adultUsers", usersCollection.find({yearsOld: '>= 18'});

Publish a stored procedure , called only on client subscribe:

/* Example of A valid Stored Procedure: 

DELIMITER //

CREATE PROCEDURE getUsersByAge(age INT)
 BEGIN
		SELECT user_id,username,firstname,lastname,avatar FROM users WHERE users.years_old >= age;
 END //

DELIMITER ;


*/
//If you want a procedure to work with mysql-live, for every select result  you have to return the ID also (eg. user_id) , this is the way I figure out to find what table's client collections should be updated. 
 Mysql.publish("usersByAge", function(age) {
	return Mysql.procedure("getUsersByAge", [age], function(newItem){
	//first argument is the procedure name you have stored to your database.
	//second argument is optional, an array of  the parameters passing to the stored procedure.
	//third parameter is optional, with the third argument of Mysql.procedure
	//you can configure if a new user should be inserted to this collection or no
			  return (newItem.yearsOld >= age);
			  //here we are telling to the collection if a new user created to this table, check if it's yearsOld column is gt or eq to 'age' and if yes, insert this object to the collection and emit this change to the client.
//we use this method because: at the normal publish actions package knows your criteria and makes it automatically, but on procedure call it is impossible to check the criteria on insert action with a 100% corrent way.
//You don't have to do something for update and remove actions, package will take care of these actions automatically.
          });
 });

Inside the publish function, this is the current subscribed socket, Passport is a the way to authendicate your users with mysql-live actions, we will discuss this later but this can be useful for filtering collections so that certain rows are visible only to certain users. If the logged-in user changes for a particular client, the publish function will be automatically rerun with the new passport object, so the new user will not have access to any rows that were meant only for the previous user.

Client-side
Mysql.subscribe(name, arg1, arg2..., callback)
Subscribe to a record set. Returns void.
name String Name of the subscription. Matches the name of the server's publish() call.
arg1, arg2... Any Optional arguments passed to publisher function on server.
callback Function Optional. If a function is passed as the last argument, it is interpreted when .publish() finish and subscription is ready.

Clients call Mysql.subscribe to express interest in rows collections published by the server. Clients can further filter these collections of documents by calling collection.find(selector). Whenever any data that was accessed by a publish function changes on the server, the publish function is automatically update and send .onChange event to the document collections are pushed to the subscribed client.

The callback is called with no arguments when the server has sent all of the initial data for the subscription.

Collection events

We saw how collection is declared and what functions do, we spoke about Publish & Subscribe, it's time to learn how the client knows when a collection receives the data and where stores these (data).

Client-side
collection.onChange(function(evt, next, forEach) { })
Register an event, it's callback called everytime a collection has been changed. Collection changes when receives new data, or any of the documents/items has been updated, removed or new document has been inserted.
evt CollectionChangedEvent An object which contains the information about this change.
name String the kind of the event which changed the collection, can be 'receive', 'insert', 'update' or 'remove'.
.items | .newItem | .newItem, .selector | selector These arguments are depent from the event's name, when evt.name is:
'receive' evt.items Array The items received and stored to collection.items property, by the server's publication.
'insert' evt.newItem Object The new document which has been inserted to the collection.
'update' evt.newItem Object The new document that has been updated. evt.selector Javascript Object selector This object describes which document has been updated, typically and by default this has the form of {id:value}, where id is the primary key column's name and the value, is the primary key's value. 4. 'remove' evt.selector Javascript Object selector This object keeps the primary key and it's value of the object that has been removed from the collection.items, like update's selector.
next Function Optional. You call next() when the next onChange callback (of this collection) depends on these changes have been maden from this .onChange.
forEach Function(item) Optional. forEach iterating over items inside the collection, like array.forEach.
collection.on('change', function(evt, next, forEach) { })
Acts like the collection.onChange, fires on any event.
collection.on('insert', function(evt, next, forEach) { })
Acts like the collection.onChange but fires only when evt.name === 'insert'.
evt.newItem Object The new document which has been inserted to the collection.
collection.on('update', function(evt, next, forEach) { })
Acts like the collection.onChange but fires only when evt.name === 'update'.
evt.newItem Object The new document that has been updated.
evt.selector Javascript Object selector This object describes which document has been updated, typically and by default this has the form of {id:value}, where id is the primary key column's name and the value, is the primary key's value.
collection.on('remove', function(evt, next, forEach) { })
Acts like the collection.onChange but fires only when evt.name === 'remove'.
evt.selector Javascript Object selector This object keeps the primary key and it's value of the object that has been removed from the collection.items, like update's selector.

The receive event exists only one time per collection and is the first event called when a collection has received it's initial data from the server's publication, after the client's subscription.

When you need the receive kind of event, it's recommended that you register an event, in the collection.onChange, before the subscription, Mysql.subscribe, because the initial data may come back before any event registered.

You can have unlimited number of registed onChange events per collection. It's collection has it's registed events with their callbacks.

Client side collections have some properties also, the most valuable are:

Client-side
collection.items Array This is the property which all a collection's documents are stored. After the 'receive' event you have acess to this property anywhere in your application.
collection.primaryKeyColumn String This is the name of the primary key of this collection's database table, you don't have to remember your primary key column names, just call collection.primaryKeyColumn to get it's name. Also there is a helper function named collection.getId(object) which gives you the value of the id of the passed object.

Events usage example:

//server-side

var Stories = Mysql.Collection("storiesCollection","stories");

Mysql.publish("allStories",Stories); 
/*Yes, you can simply pass just a collection here too, or an array of collections instead of 
function () { 
	return Stories.find();
}
*/

//client-side

var Stories = Mysql.Collection("storiesCollection");

//Lets register an event change here with .onChange syndax
Stories.onChange(function(evt,next,forEach){
	
	console.log('The collection was changed, from ', evt.name);
	
	switch(evt.name){
	
		case 'receive':
			console.log('The collection is ready and its initial data: ', evt.items);
		break;
		
		case 'insert':
			console.log('A new item inserted ', evt.newItem);
			console.log('Its ID is: ', Stories.getId(evt.newItem));
			console.log('New Stories count: '+ Stories.items.length);
		break;
		
		case 'update':
			console.log('This object with ID of ' + evt.selector[Stories.primaryKeyColumn] +' has been updated to: ', evt.newItem);
		break;
		
		case 'remove':
			console.log('An item with ID ' + evt.selector[Stories.primaryKeyColumn] +' has been removed from the  items/documents'); 
			console.log('New Stories count: ' +Stories.items.length);
		break;

	}
	
	next();
	
});

//Or you can do this for receive, insert, update, remove events or any of these events but with .on syndax

Stories.on('change',(function(evt,next,forEach){
	console.log('The collection was changed, from ', evt.name);
	next();
});

Stories.on('receive',(function(evt,next,forEach){
	console.log('The collection is ready and its initial data: ', evt.items);
	next();
});

Stories.on('insert',(function(evt,next,forEach){
	console.log('A new item inserted ', evt.newItem);
	console.log('Its ID is: ', Stories.getId(evt.newItem));
	console.log('New Stories count: '+ Stories.items.length);
	next();
});

Stories.on('update',(function(evt,next,forEach){
	console.log('This object with ID of ' + evt.selector[Stories.primaryKeyColumn] +' has been updated to: ', evt.newItem);
	next();
});

Stories.on('remove',(function(evt,next,forEach){
	console.log('An item with ID ' + evt.selector[Stories.primaryKeyColumn] +' has been removed from the  items/documents'); 
	console.log('New Stories count: ' +Stories.items.length);
	next();
});


//and finally we do subsribe, if you don't  really care about the 'receive' event you can register events after the .subscribe also.

Mysql.subscribe('allStories');

Collection joiners

I won't lie to you, this was one of my hardest feature to build and I did it because it's necessary in order to build successfully real time big mysql applications.

I named this feature 'Collection joiners' because it gives you the abillity to join collections together.

The syndax is very simple, anyone should use this feature for related data tables and collections.

It's a client side feature because server side must be light, the hard work should be maden by the client, this is the idea behind all features in the Mysql Live package/framework.

Client-side
collection.join(anotherCollection)
anotherCollection Collection The collection will be joined inside.
returns .as(property) Function
.as(property)
property String The property that should this joined collection stored inside it's parent collection.
returns .thisKey(columnPropertyName) Function
.thisKey(columnName)
columnName String The column/property name of the collection which identifies the relationship between the joined collection.
returns .foreignKey(columnName) Function
.foreignKey(columnName)
columnName String The column/property name of the joined collection which identifies the relationship between the parent collection.
returns .asList() OR .asObject() Function
.asList() Call this function if the property which passed on .as Function expecting an Array, One/Many-To-Many relation.
returns the collection back Collection
.asObject() Call this function if the property which passed on .as Function expecting a single Object, One-To-One relation.
returns the collection back Collection

Ok, markdown doesnt support more than 4 joined paragraphs, I hope you get the idea, you join a collection like that: collection.join(collection2).as('something').thisKey('collectionproperty').foreignKey('collection2property').asList() or .asObject();

The Joiners must be provided before any subscription, if you want the right and complete data results for the collection, let's see an example on how to use it...

We want to join a user with it's profile row with property 'profile' ,
We want to join a story favorite with the user who favorite a story,
We want to join the author property with the user who writes the story,
We want to map the story_favorites with the correct stories.
Of course for all that, we want the real time database changes event, means that
1. If a user changed his/her property, a username for example, I want to update the story's username if this is the author
2. If a story_favorite inserted or updated I want the nessecary story to know that and update itself
3. ... you got the idea, full real time updates on all of these collections, with fast and light way, you're lucky. You DON'T need to do something special for this, the Mysql Live by default take cares of the relationship between tables and collections :)

Let's do that fast!

//server-side, just publishing the needed collections.
var Profiles =
    Mysql.Collection("profilesCollection","user_profiles"),
Users = 
    Mysql.Collection("usersCollection","users"),
StoryFavorites = 
    Mysql.Collection("favsCollection","story_favorites"),
Stories =
    Mysql.Collection("storiesCollection","stories");

Mysql.publish("fullStories",[Users,Profiles,StoryFavorites,Stories]);
/*yes you can pass them like an array of collections instead of
function() { 
	return collection.find(...);
}x4 */

//client-side 
var Profiles =
    Mysql.Collection("profilesCollection"),
Users = 
    Mysql.Collection("usersCollection"),
StoryFavorites = 
    Mysql.Collection("favsCollection"),
Stories =
    Mysql.Collection("storiesCollection");

this.Users.join(this.Profiles)
						.as("profile")
						.thisKey("userId")
						.foreignKey("userId")
						.asObject();
						
this.StoryFavorites.join(this.Users)
						.as("liker")
					    .thisKey("likerId")
						.foreignKey("userId")
						.asObject();

this.Stories
			.join(this.Users)
						.as("author")
						.thisKey("authorId")
						.foreignKey("userId")
						.asObject()
			.join(this.StoryFavorites)
						.as("likes")
						.thisKey("storyId")
						.foreignKey("storyLikedId")
						.asList();
						
									


var aReactJsComponent = this;
this.Stories.onChange(function(evt,next,forEach){
		/*you can have .onChange events on all the collections ofcourse, but for this example we care about the stories only*/

//1. If a joined collection updated or removed you get it's event here
//for example with Users collection, on insert or update event you get this evt.newItem: evt.newItem.author
//if a favorite has inserted or removed updated also, you get a evt.newItem.favs[theFavWhich inserted or updated].... you get it.

//the collection updating itself anytime a database change happen which and only which modifies/changes the parts of this collection has been made.

//For example in ReactJS, all you need to do is:

	aReactJsComponent.setState({stories:Stories.items}); 

//and your stories will be always in the last database rows with the correct joined collections inside the properties you declared on the .as() functions.

//Myself I place the .onChange events inside a ReactJS component or inside the Store if you use Flux architecture, you can register events anywhere you want ofcourse.

//lets loop to see the results...
	forEach(function(story){
		console.log('Title: '+story.title +
		' author: '+story.author.username +
		' likes count : '+story.favs.length +
		' the first user which liked this story is: '+
		story.favs[0].liker.username);
		
	});
	
	next();
	/*where next() is missing, the callbacks runs at the same time, simultaneously. with next() you can control the flow of your events and they are executing with the order you registed them.*/
	
});

Mysql.subscribe("fullStories");

Passport

I told you about the security, this section is one step further, to be explained...

Passport is just an object which you have to create it, at the client side with Mysql.setPassport(yourCustomObject,callback optional);

The Passport object is being auto encrypted before send to the server. The server side decrypts it and stores it with the socket.id as a key/id of this object.

Safe Collections - Beyond Basics

In the section above you had access to only the socket object and the object which is pre-processed before action is taken, with Passport you can create a user object for example and use it for your authentication logic.

This is the only section which I provide client side code in the server's side docs. Simple but strong solution which gives you freedom.

Example:

//client-side

var usersCollection = Mysql.Collection("usersCollection");
var myUser = {username:'kataras', pass: 'mypassword'};

/********** there it is **********/
Mysql.setPassport(myUser);
/*********************************/
usersCollection.onChange( function(event,next [optional]){
		//event = event.name = 'receive', 'insert', 'update', 'remove'
		//if name === receive => event.items array of received objects (from subscribe) 
		//if name === insert => event.newItem object
		//if name === update => event.newItem and event.selector
		//if name === remove => event.selector
		//react js example
	this.setState({users:usersCollection.items}); // .items are always there and synchronized with the database at any changes may come.
});

Mysql.subscribe("adultUsers");

//you can call Mysql.setPassport anywhere you want, it will take care all of your subscription when it is ready.

/********************************************/

//server-side

var usersCollection = Mysql.Collection('usersCollection','users'); 

Mysql.publish("adultUsers",function (){
	return usersCollection.find({yearsOld: '>= 18'});
});

//this will allow subscriptions only from client(s) has/ve a passport with username === 'kataras' and pass === 'mypassword'

Mysql.insecure(false);

usersCollection.allow({
	subscribe: function(socket,subscriptionName){
		/********** there it is **********/
		var passport = Mysql.getPassport(socket);
		/*********************************/
		if(subscriptionName === "adultUsers"){
			return passport.username === 'kataras' && passport.pass === 'mypassword';
		}
		return false;
		
	}
});

You can also use Passports inside a Router or any http method, passing the request as parameter.

//server-side

import * as  express from 'express';
var router = express.Router();
/// ...

router.get("/", (req, res)=> {
    res.render("index.html");
});


router.get("/api/stories/", (req, res)=> {
	var passport = Mysql.getPassport(req);
	//... your logic ....
	
});

So passport is just an object which is created by the client (or server if you want) which travel encrypted to the server, and you use it inside the .allow and .deny option functions.

You can set a passport for a specific socket from the server-side too:

var socket = Mysql.getSocket('a socket id');
//Learn more about Mysql.criteriaFor()/collection.selector(), at 'node-mysql-wrapper' docs.
	
var criteria = usersCollection.selector().where("userId").eq("24").build();
//column_name alwaysreturns as columnName at node-mysql-wrapper and mysql-live packages*
	
usersCollection.fetchSingle(criteria).then(function(user){
	/********** there it is **********/
	Mysql.setPassport(socket,user);
	/*********************************/
});