Monday, August 22, 2016

MS SQL: using BCP + Powershell + Bulk Copy to sync your table to another server

The usage scenario is: you have a huge log table (or some tables like that) and you want to sync it from your production server to another server (slaver server) for analyzing daily. This will help to reduce workload of production server in analyzing as also as backup your data on slaver server. You even can delete old data (not used more) on your production server to minimize the size of production data.

Below steps to setup this sync job:

1. Use BCP to export & import data
On production server, run bcp command to export data, for example:
#bcp "SELECT * FROM log_table" queryout "C:\log_table.bcp" -N -S "PRODUCTION_SERVER_NAME" -d "DB_NAME" -U "USER_NAME" -P "PASSWORD"

After that, upload this log_table.bcp file to your slaver server, create a log_table having same structure as on production server, then run a command like example below to import data to your log_table:
#bcp "log_table" IN "E:\log_table.bcp" -N -S "SERVER_NAME" -d "DB_NAME" -U "USER_NAME" -P "PASSWORD"

2. Make Powershell script to bulk copy new data from production server to slaver server
Below is my sample script:

#Set-ExecutionPolicy Unrestricted
#2 params needed to provide for the script
Param (
[parameter(Mandatory = $true)]
[string] $LogTable,
[parameter(Mandatory = $true)]
[int] $Syncrows
)


$SrcServer = "PRODUCTION_SERVER"
$SrcDatabase = "PRODUCTION_DB"
$SrcUser = "USER_NAME"
$SrcPwd = "PASSWORD"
$SrcTable = $LogTable


$DestServer = "SLAVER_SERVER"
$DestDatabase = "SLAVER_DB"
$DestUser = "USER_NAME"
$DestPwd = "PASSWORD"
$DestTable = $LogTable


#create connection string
Function ConnectionString ([string] $ServerName, [string] $DbName, [string] $User, [string] $Pwd)
{
    "Server=$ServerName;uid=$User;pwd=$Pwd;Database=$DbName;Integrated Security=False;"
}


# executes a query and populates the $datatable with the data
function ExecuteSqlQuery ($ConnectionString, $SQLQuery) {
    $Connection = New-Object System.Data.SQLClient.SQLConnection
    $Connection.ConnectionString = $ConnectionString
    $Connection.Open()
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $Command.CommandText = $SQLQuery
    $Reader = $Command.ExecuteReader()
    $Datatable = New-Object System.Data.DataTable
    $Datatable.Load($Reader)
    $Connection.Close()
    $Connection.Dispose()
    return ,$Datatable #work arround to get right datatable after unrolling arrays/collections
}


#get max id of last synced rows
$DestConnStr = ConnectionString $DestServer $DestDatabase $DestUser $DestPwd
$Query = "SELECT MAX(Pid) FROM " + $DestTable
$resultsDataTable = ExecuteSqlQuery $DestConnStr $Query
$LastRow = $resultsDataTable.Rows[0][0]


#bulk copy
$SrcConnStr = ConnectionString $SrcServer $SrcDatabase $SrcUser $SrcPwd
$SrcConn  = New-Object System.Data.SqlClient.SQLConnection($SrcConnStr)
if($Syncrows -eq 0) {
    $CmdText = "SELECT * FROM $SrcTable WHERE row_id>$LastRow"
}
else {
    $CmdText = "SELECT TOP ($Syncrows) * FROM $SrcTable WHERE row_id>$LastRow"
}
#Write-Host "$CmdText"
$SqlCommand = New-Object system.Data.SqlClient.SqlCommand($CmdText, $SrcConn)
$SrcConn.Open()
[System.Data.SqlClient.SqlDataReader] $SqlReader = $SqlCommand.ExecuteReader()
Try
{
   $logmsg = "$LogTable : Bulk copy completed!"
   $bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($DestConnStr, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
   $bulkCopy.DestinationTableName = $DestTable
   $bulkCopy.BatchSize = 100 #rows
   $bulkCopy.BulkCopyTimeout = 1800 #seconds
   $bulkCopy.WriteToServer($SqlReader)
}
Catch [System.Exception]
{
   $ex = $_.Exception
   $logmsg = $ex.Message
}
Finally
{
   $currenttime = Get-Date -format s
   Add-Content D:\logstatus.txt "`n$currenttime $logmsg"
   $SqlReader.Close()
   $SrcConn.Close()
   $SrcConn.Dispose()
   $bulkCopy.Close()
}


The above script will use BulkCopy to copy rows having id > id synced. You can limit number of rows for syncing via $Syncrows param. If $Syncrows=0, it will get all new rows to copy.

3. Create task schedule for copying new data
Use Task Scheduler to create a new task for running the script file, you can select your schedule as your demand. Remember to add below line in Action setting for passing Execution Policy:

-ExecutionPolicy Bypass D:\synctbl.ps1 -LogTable log_table -Syncrows 10000


That's all. Happy coding! Any questions?


Friday, August 12, 2016

CodeIgniter: Build JWT authentication REST server

1. About CodeIgniter

CodeIgniter (CI) was created by EllisLab, and is now a project of the British Columbia Institute of Technology. its website is https://www.codeigniter.com.

It is a light weight PHP framework but very flexible and fast. You can read more info about CI and why it is good for you at here. Beside that, from my experience, I see CI is good for:
  • Building REST / RESTFull APIs
  • Building concept apps quickly
  • Creating beautiful and fast web apps with AngularJS or React easily
2. Installation

You can read its installation instruction on its website. Here I just high light some important notes:

2.1 Make friendly URL without index.php:
+ Apache: create .htaccess file in root folder with below content:
RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ index.php/$1 [L]


+ IIS: create web.config file in root folder with below content:
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
 <system.webServer>
  <rewrite>
    <rules>
   <rule name="CI Rule" stopProcessing="true">
     <match url="^(.*)$" ignoreCase="false" />
     <conditions>
    <add input="{REQUEST_FILENAME}" matchType="IsFile" ignoreCase="false" negate="true" />
    <add input="{REQUEST_FILENAME}" matchType="IsDirectory" ignoreCase="false" negate="true" />
    <add input="{URL}" pattern="^/favicon.ico$" ignoreCase="false" negate="true" />
     </conditions>
     <action type="Rewrite" url="index.php/{R:1}" appendQueryString="true" />
   </rule>
    </rules>
  </rewrite>
 </system.webServer>
</configuration>


2.2 Set up multiple domains using HTTP or HTTPS
Read here: https://www.codeigniter.com/userguide3/installation/upgrade_303.html

2.3 Handling errors and show customized 404 page
Read here: https://www.codeigniter.com/userguide3/general/errors.html?highlight=#show_404

3. Folder structure, app flow and URI
CI has top folder structure as the following:
Almost your code will be under application folder, which has below folders:
The controller folder contains classes which can be invoked via URIs. To understand roles of folders under application, let see the flow of CI application:
Read here for more info about its app flow.

Its URI segment is based on this rule: website.com/class/function/var, in which:
  • class: a controller class, it is in a file of application/controllers folder
  • function: an invoked function in above class
  • var: a variable passed to above function, it is optional in the URI
Read more at: https://www.codeigniter.com/userguide3/general/urls.html

4. PHP JWT classes for CI
JSON Web Tokens is new industry standard for securely transmitting information between parties (e.g. client and server). It is becoming popular in authenticating, especially in mobile apps world.

To provide this feature for CI, we need to create some classes for implementing JWT standard. I don't want to reinvent the wheel, so let copy JWT classes from php-jwt (these classes are in src folder) into application/libraries folder of your CI.

5. REST server, users and authentication:
Next step, we need to build a REST server which will use php-jwt to authenticate and create access token after user logins successfully.

Luckily, there is a good REST server built by chriskacerguis. Please see the installation guide on its Github to install into your project. In the case you want to allow CORS, let open application/config/rest.php then set:

$config['check_cors'] = TRUE;
...
$config['allow_any_cors_domain'] = TRUE;

Now we have 2 libraries: php-jwt and REST server ready for using. Then we need a model to manage our users database. Let create your users table firstly:

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 Users_model in application/models (remember config your DB in application\config\database.php)

class Users_model extends CI_Model {
    public function __construct() {
        $this->load->database();
    }

    public function login($username, $password) {
        $this->db->select('*');
  $this->db->from('users');
  $this->db->where('username', $username);
  $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $result = $query->result();
            return $result[0]->id;
        }
        return false;
    }
}


Make an API for checking user login (User.php) in application/controllers/api:

require_once APPPATH . '/libraries/REST_Controller.php';
require_once APPPATH . '/libraries/JWT.php';

use \Firebase\JWT\JWT;
class User extends REST_Controller {
    public function __construct() {
        parent::__construct();
        $this->load->model('Users_model');
    }

    public function login_post() {
        $username = $this->post('username');
        $password = $this->post('password');
        $invalidLogin = ['invalid' => $username];
        if(!$username || !$password) $this->response($invalidLogin, REST_Controller::HTTP_NOT_FOUND);
        $id = $this->Users_model->login($username,$password);
        if($id) {
            $token['id'] = $id;
            $token['username'] = $username;
            $date = new DateTime();
            $token['iat'] = $date->getTimestamp();
            $token['exp'] = $date->getTimestamp() + 60*60*5;
            $output['id_token'] = JWT::encode($token, "my Secret key!");
            $this->set_response($output, REST_Controller::HTTP_OK);
        }
        else {
            $this->set_response($invalidLogin, REST_Controller::HTTP_NOT_FOUND);
        }
    }
}


Yeah! We can login and get the token now!!!. Let check it on Postman tool:

That's all for today!

In next article, I'll present an Ionic 2 app which uses this REST server for authenticating with JWT.

Happy coding!




Thursday, August 4, 2016

Genymotion - VirtualBox: Fix error "VT-x/AMD-V hardware acceleration is not available on your system"

Genymotion (run on top VirtualBox) is one of the best emulators for Android. It is preferred by Android's developers because it is light and has high speed for testing apps in Android.

In the case of you get error: "VT-x/AMD-V hardware acceleration is not available on your system" when running a 64 bit virtual device, the following steps can help you solve this problem:

1. Your CPU (Intel/AMD) must support 64 bit with virtualization technologies. You must enable it in the BIOS setting. To know where, let dig on searching your BIOS manual. For example in the BIOS of my PC, it is in Configuration menu:

2. If you are using Windows 7/8/10, let turn off Hyper-V feature:
3. Restart your PC, open Genymotion and download your favorite 64 bit virtual device for using.

Happy coding!




Subscribe to RSS Feed Follow me on Twitter!