Simple starter kit for building realistic app with Ionic 2

At the point I'm writing this article, Ionic 2 released its RC1 and is going to official release. This article is a continue of my series: Learn Ionic 2 with Authentication by JWT, Ionic 2: FormBuilder and Validator, Build JWT authentication server with Node.js, Express and MySQL. It put all these articles into a Todos System with 2 parts:

• Server: an enhancement of previous server in Build JWT authentication server with Node.js, Express and MySQL by adding new todos API allowing to get, create new and delete todos based on user role authentication. You can clone / download the source code of this server on my GitHub: https://github.com/vnheros/nodejs-mysql-jwt-auth
• App: a todo app which allows user login or sign up new account. After login successfully, it will displays a list of public todos or todos of user. User can add new todo or delete an existing todo. You can clone / download the source code of this server on my GitHub (tested with Ionic 2 RC1): https://github.com/vnheros/ionic2-todos-example

I think this is a simple starter kit for you starting your real apps with Ionic 2. Below are important things in this starter kit which I didn't mention in my GitHub.

1. My SQL DB
There are 2 tables: users and todos having script as below:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `password` varchar(20) NOT NULL,
  `email` varchar(50) NOT NULL,
  `role` varchar(20) DEFAULT 'Regitered',
  PRIMARY KEY (`id`,`username`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

CREATE TABLE `todos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task` varchar(150) NOT NULL,
  `deadline` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `public` tinyint(1) NOT NULL DEFAULT '0',
  `completed` tinyint(1) DEFAULT '0',
  `username` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

2. Design of todos app
Below are screens of this app:

The Welcome page containing Ionic login will appear first, then it will check if user is logged in or not by checking id_token stored in local storage. If user logged in, it will move to Todos List page which presents public todos and todos of the user. If user is not logged in yet, it will navigate to Todos Login page for signing in or signing up. Todos List has 2 buttons, one on top for logging out and one in bottom for adding new todo.

3. Step to generate source code for the app
For Ionic 2 RC1, you should install its app-script to avoid some issues when running the app (read this post for more info).

Install @ionic/app-scripts and update your ionic 2:
#npm install @ionic/app-scripts@latest --save-dev
#npm install -g ionic

Generate providers then replace by the source code from my GitHub:
#ionic g provider Auth
#ionic g provider Todos

Generate pages then replace by the source code from my GitHub:
#ionic g page Welcome
#ionic g page Login
#ionic g page Signup
#ionic g page NewTodo

Add folder helpers then add file jwt-helper.ts (used to parse JWT in the app), add folder validators and add file password.ts (used to validate a password input when signing up). You can copy them from my GitHub.

From Ionic RCx, you will need to add pages and providers into app/app.module.ts to use them in your app. See my file on GitHub for example.

There are some another things in the source code which you can discover easily in the source code.
That's for to day and wish you have fun Halloween. Remember turn on the light.
Any comment is welcome.

Build JWT authentication server with Node.js, Express and MySQL

Node.js is based on JavaScript and V8 JavaScript Engine (an open source JavaScript engine developed by The Chromium Project for the Google Chrome web browser). It supports non-blocking I/O because it is running in single process and dedicated CPU. You can spawn many Node.js processes corresponding with number of CPU cores in your machine. It is unfair to say that Node.js is run faster vs. other web servers because it is very depended on complex of your server application. However in many test cases, they shows that Node.js is very fast especially in running web service (although its startup time is a little bit slow) and how it is easy in coding.

This article is inspired from my previous article using the code from Auth0 for JWT authentication server which users are in memory array. Now I want to rewrite by using MySQL to store users and another purpose is helping beginner of Node.js can get start with Node.js + Express + MySQL.

If you don't know how to start a Node.js application, I recommend you read the popular article "Node.js Applications with VS Code" in VS Code. Assuming that you already read that article, we will start Node.js application with Express Generator as the following steps:

#express mysql-jwt-auth
#cd mysql-jwt-auth
#npm install
#code .

The last command will open mysql-jwt-auth application in VS Code for editing. You will have generated codes like below picture:

OK, now we'll add some necessary modules into this app:
#npm i cors --save
#npm i lodash --save
#npm i jsonwebtoken --save
#npm i express-jwt --save
#npm i mysql --save

After that, open app.js file and add below lines:
var cors = require('cors'); //after the line var bodyParser = require('body-parser');
app.use(cors()); //after the line app.use(logger('dev'));

Change the line:

app.use('/users', users);  ==> app.use(users);

Open your MySQL database and create table users and table quotes as the following:
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `password` varchar(20) NOT NULL,
  `email` varchar(50) NOT NULL,
  PRIMARY KEY (`id`,`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `users`(`id`,`username`,`password`,`email`) VALUES (1,'gonto','gonto','gonto@me.com');

CREATE TABLE `quotes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(150) NOT NULL,
  `private` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Add db.js file into root folder (same level with app.js):
var mysql = require('mysql');
var pool  = null;
exports.connect = function() {
  pool = mysql.createPool({
    host     : 'myhost',
    user     : 'myuser',
    password : 'mypassword',
    database : 'mydb'
  });
}
exports.get = function() {
  return pool;
}

Add below lines to bin/www file (after line: var http = require('http')):
var db = require('../db');
db.connect();

Now we have the db ready for using. Add new file config.json into root folder with content below:
{
  "secretKey": "don't share this key"
}

The above file will keep a secret key for encoding user token after logged in. Open routes/user.js file and replace by new code below:
var express = require('express'),
    _       = require('lodash'),
    config  = require('../config'),
    jwt     = require('jsonwebtoken')
    db      = require('../db');
var app = module.exports = express.Router();
var secretKey = "don't share this key";
function createToken(user) {
  return jwt.sign(_.omit(user, 'password'), config.secretKey, { expiresIn: 60*60*5 });
}
function getUserDB(username, done) {
  db.get().query('SELECT * FROM users WHERE username = ? LIMIT 1', [username], function(err, rows, fields) {
    if (err) throw err;
    done(rows[0]);
  });
}
app.post('/user/create', function(req, res) { 
  if (!req.body.username || !req.body.password) {
    return res.status(400).send("You must send the username and the password");
  }
  getUserDB(req.body.username, function(user){
    if(!user) {
      user = {
        username: req.body.username,
        password: req.body.password,
        email: req.body.email
      };
      db.get().query('INSERT INTO users SET ?', [user], function(err, result){
        if (err) throw err;
        newUser = {
          id: result.insertId,
          username: user.username,
          password: user.password,
          email: user.email
        };
        res.status(201).send({
          id_token: createToken(newUser)
        });
      });
    }
    else res.status(400).send("A user with that username already exists");
  });
});
app.post('/user/login', function(req, res) {
  if (!req.body.username || !req.body.password) {
    return res.status(400).send("You must send the username and the password");
  }
  getUserDB(req.body.username, function(user){
    if (!user) {
      return res.status(401).send("The username is not existing");
    }
    if (user.password !== req.body.password) {
      return res.status(401).send("The username or password don't match");
    }
    res.status(201).send({
      id_token: createToken(user)
    });
  });
});
app.get('/user/check/:username', function(req, res) {
  if (!req.params.username) {
    return res.status(400).send("You must send a username");
  }
  getUserDB(req.params.username, function(user){
    if(!user) res.status(201).send({username: "OK"});
    else res.status(400).send("A user with that username already exists");
  });
});

At this time, we can use above code for authenticating user. Key the command npm start and use Postman tool to check what the server response. Here is my example:

That's fine, so we are going to add routes/quotes.js file to illustrate for anonymous request and authenticated request in reading quotes. This file has simple content as below:
var express = require('express'),
    jwt     = require('express-jwt'),
    config  = require('../config'),
    db      = require('../db');
var app = module.exports = express.Router();
var jwtCheck = jwt({
  secret: config.secretKey
});
function getPublicQuotesDB(done){
    db.get().query('SELECT * FROM quotes WHERE private=0', function(err, rows) {
        if (err) throw err;
        done(rows);
    });
}
function getPrivateQuotesDB(done){
    db.get().query('SELECT * FROM quotes WHERE private=1', function(err, rows) {
        if (err) throw err;
        done(rows);
    });
}
app.get('/api/public/quote', function(req, res) {
  getPublicQuotesDB(function(result) {
      res.status(200).send(result);
  });
});
app.use('/api/private', jwtCheck);
app.get('/api/private/quote', function(req, res) {
  getPrivateQuotesDB(function(result) {
      res.status(200).send(result);
  });
});

Don't forget to add below lines to app.js file to include above quotes.js:
var quotes = require('./routes/quotes');
app.use(quotes);

OK, let use Postman tool to check if the api gets quotes working as expected. Here are my results:

As you can see, request /api/public/quote works fine. But you will get a complain "No authorization token was found" if you request /api/private/quote. When you add Authorization header with your token for it, it runs ok soon (last picture).

That's all for tonight. Bye!
You can find source code on my GitHub.

MySQL: swap valúes of 2 columns

If you want to swap values of 2 columns (e.g. col1 and col2) in a table (e.g. a_table), you can use below SQL:

UPDATE a_table SET col1=(@temp:=col1), col1 = col2, col2 = @temp;

Do you have another ways? Pls post comment here. Thanks!

MySQL: manage (disable, purge/clean) binary log files (mysql-bin.xxx)

MySQL binary log files contain all statements that update data or potentially could have updated it.
These files have 2 important purposes:

• Data Recovery : after a backup file has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.
• High availability / replication : used on master replication servers as a record of the statements to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master.

The problem is these files can cause your hard disk full. So you need to manage them to save your hard disk space and match your replication and backup policy. Please note that you shouldn't delete them manually. Instead of, you should configure MySQL for them.

I suggest 2 ways for handling them.

1. Disable these binary logs if you don't use replication function.
Open my.ini (Windows) or my.cnf (Linux), then find the line (starting with log-bin or log_bin) and comment to disable it:
#log-bin = mysql-bin
You should create a backup (dump data) daily if disable this.

2. Purge / clean them frequently if you use replication function.
Login to mysql then use the following command:

mysql> PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;

It will clean all binary logs before midnight 3 days ago.

You may also need to set expire_logs_days value:

mysql> SET GLOBAL expire_logs_days = 3;

And add or modify this variable in my.ini (Windows) or my.cnf (Linux).

[mysqld]
expire-logs-days=3

To avoid to interrupt your replication, run SHOW SLAVE STATUS\G to check, you will see 2 binary logs from the Master: Master_Log_File and Relay_Master_Log_File. Then run this command:
PURGE BINARY LOGS TO 'Whatever Relay_Master_Log_File Is';

Finally you should restart your MySQL. That's all.

MySQL: rounding numbers

1. ROUND(x,d)
x: value to round
d: decimal number
For example:
SELECT ROUND( 1 );   /* = 1 */
SELECT ROUND( 1.4 ); /* = 1 */
SELECT ROUND( 1.5 ); /* = 2 */
SELECT ROUND( -1.4 ); /* = -1 */
SELECT ROUND( -1.5 ); /* = -2 */
SELECT ROUND( 1.4212, 1 ); /* = 1.4 */
SELECT ROUND( 1.4512, 1 ); /* = 1.5 */

2. CEILING(x)

CEILING = ROUND x up to the nearest integer.

For example:

SELECT CEILING( 1 );   /* = 1 */

SELECT CEILING( 1.4 ); /* = 2 */

SELECT CEILING( 1.6 ); /* = 2 */

SELECT CEILING( -1.4 ); /* = -1 */

SELECT CEILING( -1.6 ); /* = -1 */

3. FLOOR(x)

FLOOR =  ROUND x down to the nearest integer.

For example:

SELECT FLOOR( 1 );   /* = 1 */

SELECT FLOOR( 1.4 ); /* = 1 */

SELECT FLOOR( 1.6 ); /* = 1 */

SELECT FLOOR( -1.4 ); /* = -2 */

SELECT FLOOR( -1.6 ); /* = -2 */

4. TRUNCATE(x,y)

x: value to truncate

y: decimal number after truncated. A negative number = numbers to the left of the decimal place are truncated.

For example:

SELECT TRUNCATE( 1, 0 );       /* = 1    */

SELECT TRUNCATE( 1.5555, 0 );  /* = 1    */

SELECT TRUNCATE( 1.5555, 1 );  /* = 1.5  */

SELECT TRUNCATE( -1.5555, 0 ); /* = -1   */

SELECT TRUNCATE( -1.5555, 1 ); /* = -1.5 */

SELECT TRUNCATE( 12345, -1 );  /* = 12340 */

SELECT TRUNCATE( 12345, -2 );  /* = 12300 */

SELECT TRUNCATE( 12345, -3 );  /* = 12000 */

Solving error "MySQL server has gone away"

If you have time, you can read the article "MySQL server has gone away". OR just do 3 steps as the following:
1. Find my.ini file if you are using Windows (it can locate in C:\ProgramData\MySQL\MySQL Server 5.5\). OR my.cnf file if you are using Linux (it can locate in /etc/mysql/my.cnf)

2. Increase max_allowed_packet value below [mysqld] section (by default max_allowed_packet =1M or not defined), for example you change it to 32M:
[mysqld]
max_allowed_packet=32M

3. Restart mysql service

MySQL: [1406]Data too long for column

If you get this 1406 error while you run SQL command to insert data into a TEXT column.

1. Check if max_allowed_packet size is enough for your SQL command size. If not, change it in the my.ini file. Just nnclude the single line under [mysqld] in your file, for example:
max_allowed_packet=16M
Then restart mysql.

2. If max_allowed_packet is OK, try to change the column to LONGTEXT.

Hope it can help some bodies -:)

Auto task ftp backup mysql

This guide helps you backup mysql db via FTP with a schedule.
1. Requirements
-WinRAR
-FTP client (command lines)

2. Create batch file (for example mysql_backup.bat) with following code

@echo off
setlocal
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c%%a%%b)
"<your mysql folder>\bin\mysqldump.exe" -u <mysqluser> --password=<mysqlpassword> yourdb > "<your mysql backup folder>\yourdb%mydate%.sql"
"<your WinRAR folder>\WinRAR.exe" a -r -inul<your mysql backup folder>\yourdb%mydate%.rar <your mysql backup folder>\yourdb%mydate%.sql
del <your mysql backup folder>\yourdb%mydate%.sql
copy <your mysql backup folder>\yourdb%mydate%.rar <your folder to upload>
ftp -v -i -s:ftpscript.txt
del <your folder to upload>\tmcrm%mydate%.rar

3. Create ftp script file (ftpscript.txt)

open <your server>
<username>
<password>
lcd  <your folder to upload>
cd <your server folder to hold backup>
binary
mput "*.*"
disconnect
bye

4. Create Windows' task schedule for this mysql_backup.bat

End ./.

MySQL: Check logs of queries

Apply for MySQL >= 5.1.12:
1. Open MySQL console
2. Execute SET GLOBAL log_output = 'TABLE';
3. Execute SET GLOBAL general_log = 'ON';
4. Check table mysql.general_log (should ORDER BY event_time DESC)

To turn off:
5. Execute SET GLOBAL general_log = 'OFF';

If you want to put logs to file:
SET GLOBAL log_output = "FILE";
SET GLOBAL general_log_file = "your_folder/your_file.log"

MySQL Change root Password

How do I change MySQL root password under Linux, FreeBSD, OpenBSD and UNIX like operating system over ssh / telnet session?

Setting up mysql password is one of the essential tasks. By default root user is MySQL admin account. Please note that the Linux / UNIX login root account for your operating system and MySQL root are different. They are separate and nothing to do with each other (indeed some admin removes root account and setup admin as mysql super user).

mysqladmin command to change root password

If you have never set a root password for MySQL, the server does not require a password at all for connecting as root. To setup root password for first time, use mysqladmin command at shell prompt as follows:

$ mysqladmin -u root password NEWPASSWORD

However, if you want to change (or update) a root password, then you need to use following command

$ mysqladmin -u root -p'oldpassword' password newpass

For example, If old password is abc, and set new password to 123456, enter:

$ mysqladmin -u root -p'abc' password '123456'

Change MySQL password for other user

To change a normal user password you need to type (let us assume you would like to change password for vivek):

$ mysqladmin -u vivek -p oldpassword password newpass

Changing MySQL root user password using MySQL sql command

This is another method. MySQL stores username and passwords in user table inside MySQL database. You can directly update password using the following method to update or change password for user vivek:
1) Login to mysql server, type following command at shell prompt:

$ mysql -u root -p

2) Use mysql database (type command at mysql> prompt):

mysql> use mysql;

3) Change password for user vivek:

mysql> update user set password=PASSWORD("NEWPASSWORD") where User='vivek';

4) Reload privileges:

mysql> flush privileges;
mysql> quit

This method you need to use while using PHP or Perl scripting.

Move mysql service of wamp

1. Copy all content of old mysql folder to new mysql folder.
2. In new mysql folder, open file my.ini and replace all old folder to new folder (except log-error variable)
3. To change executable path of wampmysqld service, run regedit in START >> RUN, then go to:

HKEY_LOCAL_MACHINE >> SYSTEM >> CurrentControlSet >> Services >> wampmysqld >> ImagePath
Change it to new folder.
4. Open wampmanager.ini and replace all old mysql folder to new folder.