var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'me',
  password : 'secret',
  database : 'my_db'
});

var wrapper = require('node-mysql-wrapper');
var db = wrapper.wrap(connection);
//or (without need of require mysql module) ->
//var db = wrapper.wrap("mysql://user:[email protected]/databaseName?debug=false&charset=utf8");

db.ready(function(){
	//your code goes here	
	//users -> an example table inside the database, just call it like property:

	db.table("users").findById(8,function(user){
	    console.dir(user);

		//to destroy the whole connection, its events and its tables use: 
		db.destroy();
	
    }); //or using promises: findById(8).then(function(rowResults){...});
    

});

var wrapper =  require('node-mysql-wrapper');
var db = wrapper.wrap("mysql://user:[email protected]/databaseName?debug=false&charset=utf8");

db.ready(function(){ 

});

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'me',
  password : 'secret',
  database : 'my_db'
});

var wrapper =  require('node-mysql-wrapper');
var db = wrapper.wrap(connection);

db.ready(function(){

});

db.end(function(err) {
  // The connection, table events and queries are terminated now
}); 
//Surely you can have a direct access to mysql connection from db.connection object, if you ever need it.

db.destroy();

 db.useOnly('users','comments',['or_an_array_of_tables','comment_likes']);
 //this goes before db.ready function.

//all code you will see bellow goes inside db.ready(function () { //code here });
var usersTable = db.table("users"); //yes, just this :)
console.log('available columns: '+ usersTable.columns);
console.log('primary key column name: '+ usersTable.primaryKey);
console.log('find, findById, findAll, save and remove methods can be called from this table');

usersTable.find({mail:'[email protected]'},function(results){

});

db.table("users").findById(18,function(user){
	console.log("SELECT * FROM users WHERE user_id = 18");
	console.dir(user);
});

db.table("users").findSingle({mail:'[email protected]'},function(user){
	console.log("SELECT * FROM users WHERE mail = '[email protected]' LIMIT 1");
	console.dir(user);
});

db.table("users").find({yearsOld:22},function(users){
	console.log("SELECT * FROM users WHERE yearsOld = 22. results: ";
	console.dir(users);
});

db.table("users").findAll({limit :42}).then(function(users){ 
	console.log("SELECT * FROM users LIMIT 42. results: ");
	console.dir(users);
});

var dbUsers = db.table("users");

var criteria = dbUsers.criteria
.where("yearsOld",22)
.exclude("password","createdDate") //or .except(...columns). Removes that column(s) from the select query. 
.joinAs("info","userInfos","userId").at("info").limit(1) 
//with.at('tableOrPropertyName') we are going and passing criterias inside the info property
//this will pass the the result as object not as array, because of limit(1)
.parent() // or .original() here will be redirect to parent object, ( user(s) table) to continue our query...
//original() goes to the first-original-primary table, parent() goes to the parent table, you can have unlimited .at('joinedTableOrProperty') functions.
.join("comments","userId")
.at("comments")
.orderBy("commentId",true) //true if you want desceding ( ORDER BY COLUMN_KEY DESC )
.joinAs("likes","commentLikes","commentId")
.at("likes")
.joinAs("likers","users","userId").build(); 
//In order to go to parent table use : parent(),
//to go to primary-first-iriginal table use .original(),
//.build() builds all in correct order so you don't need to call parent() in this case. 

// .build() makes that : 
/*
var criteria= {
	yearsOld:22, //where
	info : {  //joined table
		userId : '=' , //'=' means: put the parent object's property's value.
		tableRules: {
			table:'userInfos',
			limit:1
		}
	},
	comments: { //another joined table with it's own joined tables also
		userId: '=',
		
		tableRules:{
		    orderByDesc: "commentId" //ORDER BY comment_id DESC
		},
		
		likes: {
		    commentId: '=', //foreign key is comment_id. Where it's value is from comment object's primary key's value.
			
			tableRules{ //NEW 
				table: "commentLikes" //use commentLikes tables as 'likes' property
			}
		
			users: {
				userId : '=' 
			}
		} 
	}
};
*/

dbUsers.find(criteria,function(results){
	console.log('A lot of queries executed here, you can guess them :)');
	console.dir(results);
});

var newUser = { username: 'a new user', yearsOld: 23, mail: '[email protected]' };

db.table("users").save(newUser).then(function(result){ //if you want use a promise
	console.log('New user just created, new userId:'
	+ result.userId+ ' which is the same as newUser.userId now:' +newUser.userId);
		
	result.username = 'an updated new username';	
	
	db.table("users").save(result,function(){
		console.log('User was just updated, because we have already a primary key setted at this object');
		
	});
		
});

//remove/delete all rows from users table where years_old = 22
db.table("users").remove({yearsOld:22},function(resultAnswer){
	console.log(resultAnswer.affectedRows+ ' rows removed from '+resultAnswer.table);
});

//remove/delete a single row by its primary key
db.table("users").remove(4,function(results){
	console.log('we were just deleted a user with userId(user_id) = 4');
});

var findAUser = db.table("users").findById(16);
var findMoreUsers = db.table("users").find({username: 'a username'});
var findSomeComments = db.table("comments").find({userId:16});

//you can pass an array of promises too.
db.when(findAUser,findMoreUsers,findSomeComments).then(function(results) {
	/*
results -> results[0] -> findAUser (one object) , results[1]->findMoreUsers (array) results  , results[2] ->findSomeComments (array) results.
	*/

});

db.query('SELECT * FROM `users` WHERE `user_id` = 18', function (error, results) {
  // error will be an Error if one occurred during the query
  // results will contain the results of the query
});

//users -> an example table on a database, call it like a normal function
var usersInsertWatcher = function(parsedInsertedRows){
	console.log('Somewhere an insert query executed on USERS table : ');
	console.dir(parsedInsertedRows);
};

var usersRemoveWatcher = function(deleted){
	console.log('Somewhere a remove/delete query executed on USERS table : ');
	console.log('Affected rows number: '+ deleted.affectedRows);
};

db.table("users").on('insert',usersInsertWatcher);
db.table("users").on('remove',usersRemoveWatcher); //and so on...

db.table("users").off('insert',usersInsertWatcher);
db.table("users").off('remove',usersRemoveWatcher);

if(db.table("users").has('mailExists') === false) //not necessary
{
	db.table("users").extend('mailExists',function(mail,callback){
	//this = the users table, so this.name = 'users'
	var q= "SELECT COUNT(*) FROM " + this.name + " WHERE mail = " + 
	db.connection.escape(mail);
		
		db.query(q, function (err, results) {
            if (!err && results.length > 0 && results[0]["COUNT(*)"] > 0) {
                 callback(true);
                } else {
                  callback(false);
            }
       });
	});

}

	db.table("users").mailExists('[email protected]',function(exists){
		if(exists) console.log('this mail already exists!');
		else console.log('this mail doesnt exists.');
	});