SomeMyExp

Order by sequence number in ascending, positioning NULL value at last (order by id ascending)

SELECT * FROM picrure ORDER BY -sequence DESC, id ASC;

Given example: picture table

id	sequence	name
1	NULL	picture_one
2	1	picture_two
3	2	picture_three
4	NULL	picture_four
5	3	picture_five

In the query above we use (-) minus sign to sequence field that will negate/inverse sequence number, positive value will be negative, a negative value will be positive, NULL values will remain.

Item picture_two with id=2 order sequence will be -1 from 1 and so on. This query will order picture in ascending sequence if not null, otherwise order in ascending id.

id	sequence	name
2	1	picture_two
3	2	picture_three
5	3	picture_five
1	NULL	picture_one
4	NULL	picture_four

This two query will also give same result:

SELECT * FROM fortest.picture ORDER BY sequence IS NULL ASC, sequence ASC, id ASC;
SELECT * FROM fortest.picture ORDER BY ISNULL(sequence) ASC, sequence ASC, id ASC;

This section describe how to implement authentication in React.

First, we need to create React app using this command:

npx create-react-app my-app

We also need to install React Router DOM using command:

npm install --save react-router-dom

Create file src/AppComponent/Authentication.js
This class will handle authentication.
isAuthentication() function will return boolean value whether user has been authenticated.
checkCredentials() function will check whether given credential is valid. For this example, checkCredentials() function use static user data. We need to change this function to use programmatic user data using user service.
setUserDetails() function is used to store user details. In this example, user details will saved to local storage.

class Authentication {
    static isAuthenticated = () => {
        return Authentication.getUser() !== null;
    }
    
    static authenticate = (credentials) => {
        if(credentials
            && credentials.username
            && credentials.password) {
                const isValidCredentials = Authentication.checkCredentials(credentials);
                if(isValidCredentials) {
                    Authentication.setUserDetails(credentials);
                }
                return isValidCredentials;
        }
        return false;
    }
    
    static checkCredentials = (credentials) => {
        return credentials.username == "admin" && credentials.password == "123";
    }
    
    static getUsername = (userDetails) => {
        return Authentication.getUser().username;
    }
    
    static setUserDetails = (userDetails) => {
        localStorage.setItem("user", JSON.stringify(userDetails));
    }

    static getUser = () => {
        return JSON.parse(localStorage.getItem("user"));
    }

    static logOut = () => {
        localStorage.removeItem("user");
    }
}

export default Authentication;

Create file src/AppComponent/AnonymousRoute.js
Use this class on pages that can only be visited by users who have not been authenticated, like login and reset password. If someone has been authenticated try to enter login page, this component will redirect user to root page. So, authenticated user will not face that page.

import React, { Component } from 'react';
import { Route, Redirect } from 'react-router-dom';
import Authentication from './Authentication';

class AnonymousRoute extends Component {
    render() {
        const {component: Component, ...others} = this.props;
        
        const renderRoute = props => {
            if (Authentication.isAuthenticated()) {
                return (
                    <Redirect to={
                        {
                            pathname: '/'
                        }
                    } />
                );
            }
            return ( <Component {...props} /> );
        }

        return (
            <Route {...others} render={renderRoute}/>
        );
    }
}

export default AnonymousRoute;

Create file src/AppComponent/ProtectedRoute.js
Use this class on pages that can only be visited by users who have been authenticated, like profile page etc. If someone has not been authenticated try to enter protected resource/page, this component will redirect user to login page. This class will hold return url (actual user requested url before redirect to login) on state and will redirect user to requested url if authentication is success.

import React, { Component } from 'react';
import { Route, Redirect } from 'react-router-dom';
import Authentication from './Authentication';

class ProtectedRoute extends Component {
    render() {
        const {component: Component, ...others} = this.props;
        
        const renderRoute = props => {
            if (Authentication.isAuthenticated()) {
                return ( <Component {...props} /> );
            }

            return (
                <Redirect to={
                    {
                        pathname: '/login', 
                        state: { returnUrl: props.location }
                    }
                } />
            );
        }

        return (
            <Route {...others} render={renderRoute}/>
        );
    }
}

export default ProtectedRoute;

Create file src/views/Home.js
This class will render Home Page that contain navigation to profile page and logout button if user has been authenticated, login page if user has not been authenticated.

import React, { Component } from 'react';
import Authentication from '../AppComponent/Authentication';
import { Link } from 'react-router-dom';

class Home extends Component {
    constructor(props) {
        super(props);
    }
    
    logout =(e) => {
        Authentication.logOut();
        this.props.history.push('/login');
    }

    render() {
        const navigation = Authentication.isAuthenticated()
            ? (<div><Link to="/profile">Profile Page</Link><br /><button onClick={() => this.logout() }>Logout</button></div>)
            : <Link to="/login">Login Page</Link>;
        
            return (
            <React.Fragment>
                <div>This is Home Page</div>
                { navigation }
            </React.Fragment>
        );
    }
}

export default Home;

views/Login.js
This class will render login page.

import React, { Component } from 'react';
import Authentication from '../AppComponent/Authentication';

class Login extends Component {
    constructor(props) {
        super(props);
        this.loginForm = React.createRef();
        this.state = {
            isLoginSuccess: true
        }
    }

    doLogin = () => {
        const form = this.loginForm.current;
        const credentials = {
            username: form.username.value,
            password: form.password.value
        }
        const isLoginSuccess = Authentication.authenticate(credentials);
        this.setState({ isLoginSuccess });
        if(!isLoginSuccess){ return; }

        let successUrl = this.props.location.state ? this.props.location.state.returnUrl : '/';
        this.props.history.push(successUrl);
    }

    render() {
        return (
            <React.Fragment>
                <div>
                    This is Login Page
                </div>
                <div>
                    <form ref={this.loginForm} onSubmit={(e)=> { e.preventDefault(); this.doLogin() }} >
                        <input type="text" name="username" placeholder="username" /><br />
                        <input type="password" name="password" placeholder="password" /><br />
                        <input type="submit" value="Submit" />
                    </form>
                    <div>{this.state.isLoginSuccess ? '' : 'Login fail.'}</div>
                </div>
            </React.Fragment>
        );
    }
}

export default Login;

Create file sec/views/Profile.js
This class will render profil page, and displays the name of the user who has logged in.

import React, { Component } from 'react';
import Authentication from '../AppComponent/Authentication';

class Profile extends Component {
    constructor(props) {
        super(props);
    }

    render() {
        return (
            <div>
                This is Profile Page.<br />
                Login as { Authentication.getUsername() }
            </div>
        );
    }
}

export default Profile;

Create file src/App.js
In this class, we use AnonymousRoute and ProtectedRoute components. Login page use AnonymousRoute, can only be seen by users who have not been authenticated. Profile page use ProtectedRoute, can only be seen by users who have been authenticated.

import React, { Component } from 'react';

import {
  Route,
  BrowserRouter, 
  Switch 
} from 'react-router-dom';

import Login from './views/Login';
import Profile from './views/Profile';
import Home from './views/Home';

import ProtectedRoute from './AppComponent/ProtectedRoute';
import AnonymousRoute from './AppComponent/AnonymousRoute';

class App extends Component {
  render() {
    return (
      <BrowserRouter>
        <Switch>
          <AnonymousRoute exact path="/login" name="Login" component={Login} />
          <ProtectedRoute path="/profile" name="Profile" component={Profile} />
          <Route exact path="/" name="Home" component={Home} />
        </Switch>
      </BrowserRouter>
    );
  }
}

export default App;

To run app, we can execute command:

npm start

Source code can be found here: GitHub
React Version : 16.7.0

Lambda expression is an expression that allow to apply one or more expression to a data. Lambda expression use fat arrow (=>) token in an expression. Lambda expression will work on posts, comments, or labels.
Lambda expression pattern:
[variable name] => [expression]
Example:

label => label.name == "Technology"

In order to use lambda expression, we need to use available aggregate operator that can take lambda expression as an operand (lambda operator). Lambda operators is set of aggregate operations that allow to pass lambda expression as an operand. Lambda operator take two operand. Left operand is a set of data that can be posts, comments, or labels. Right operand is lambda expression.
Lambda operation pattern:
[set of data] [lambda operator] ([lambda expression])
Example:

data:post.labels any (label => label.name == "Blogger")

data:post.labels is a set of data,
any is lambda operator,
(label => label.name == "Blogger") is lambda expression.

Below is list of available aggregate operators that can be used to operates lambda expression:

Operator	Description
any	Returns true if any item matches the rules on lambda expression (boolean)
all	Returns true if all of the items in the set matches the rules on lambda expression (boolean)
none	Returns true if none of the items in the set matches lambda expression (boolean)
count	Returns the count of the items in the set which matches rules on lambda expression (number)
first	Returns the first item which match lambda expression (data)
filter, where	Returns all of items that matches rules on lambda expression (set of data)
map, select	Returns a set containing each of the results in a new form applied by lambda expression (new set of data)

Arithmetic Operators
Blogger provides arithmetic operator to perform addition (+), subtraction (-), multiplication (*), division (/) and modulo/remainder (%). Additive operator (+) also used for string concatenation.

Operator	Description
+	Adds Left-Operand and Right-Operand, also use for concatenate strings
-	Subtracts Right-Operand from Left-Operand
*	Multiplies values on either side of the operator
/	Divides Left-Operand by Right-Operand
%	Computes the remainder of dividing Left-Operand by Right-Operand

Example:

<b:with var='totalComments' value='9'>
    data:totalComments = <b:eval expr='data:totalComments' /><br/>
    data:totalComments + 2 = <b:eval expr='data:totalComments + 2' /><br/>
    data:totalComments - 2 = <b:eval expr='data:totalComments - 2' /><br/>
    data:totalComments * 2 = <b:eval expr='data:totalComments * 2' /><br/>
    data:totalComments / 2 = <b:eval expr='data:totalComments / 2' /><br/>
    data:totalComments % 2 = <b:eval expr='data:totalComments % 2' /><br/>
    "Hello" + " World" = <b:eval expr='"Hello" + " World"' /><br/>
</b:with>

Result:

data:totalComments = 9
data:totalComments + 2 = 11
data:totalComments - 2 = 7
data:totalComments * 2 = 18
data:totalComments / 2 = 4.5
data:totalComments % 2 = 1
"Hello" + " World" = Hello World

Relational Operators
The relational operator determines the relation between two operand. Relational operator compare two operand and determine if one operand is equal, not equal, greater than, less than, greater than or equal, less than or equal to another operand.

Operator	Description
==	Equal to. True if two operands are equal
!=	Not equal to. True if two operands are not equal
>	Greater than. True if Left-Operand is greater than Right-Operand
<	Less than. True if Left-Operand is less than Right-Operand
>=	Greater than or equal to. True if Left-Operand is greater than or equal to Right-Operand
<=	Less than or equal to. True if Left-Operand is less than or equal to Right-Operand

Example:

5 == 7 = <b:eval expr='5 == 7' />
5 != 7 = <b:eval expr='5 != 7' />
5 > 7 = <b:eval expr='5 > 7' />
5 < 7 = <b:eval expr='5 < 7' />
5 >= 7 = <b:eval expr='5 >= 7' />
5 <= 7 = <b:eval expr='5 <= 7' />

Result:

5 == 7 = false
5 != 7 = true
5 > 7 = false
5 < 7 = true
5 >= 7 = false
5 <= 7 = true

Conditional or Logical Operator
The logical operators || (OR) and && (AND) operates two boolean expression and return boolean value.
There is logical NOT/complement operator, which take one operand, unary operator category. This operator used to inverses boolean value of its operand.
There are another conditional operator called Ternary Operator. Called ternary operator because it take 3 (Three) operand, and only this operator that take 3 operand. Ternary operator is like simplified b:if, b:else statement, but with return value. Format: operand1 ? operand2 : operand3
Operand1 must be boolean expression, operand2 and operand3 can be any data or expression that will be return value. Ternary operator will return operand2 value if operand1 is true, otherwise return value of operand3.

Operator	Description
&&, AND	conditional-AND; true if all operand are true.
||, OR	conditional-OR; true if either of the operand is true
!	logical NOT, return negation of its boolean operand
?:	Ternary Operator, return operand2 value if operand1 is true, otherwise return value of operand3.

Example:

9 == 7 AND 9 > 7 returns <b:eval expr='9 == 7 AND 9 > 7' /><br/>
9 == 7 OR 9 > 7 returns <b:eval expr='9 == 7 OR 9 > 7' /><br/>
!(9 == 7) returns <b:eval expr='(9 == 9)' />
9 == 9 ? "Equal" returns "Not equal" : <b:eval expr='9 == 9 ? "Equal" : "Not equal"' /><br/>

Result:

9 == 7 AND 9 > 7 returns false
9 == 7 OR 9 > 7 returns true
!(9 == 7) returns true
9 == 9 ? "Equal" : "Not equal" returns Equal

This section describes the control flow in blogger. Control flow is used to alter the flow of statement execution with decision making and looping that will enabling execute particular blocks of code/show content conditionally.

Decision Making

b:if, b:elseif, & b:else Statement
We can use the b:if, b:elseif and b:else tags to execute particular statement in different cases.
Format:

<b:if cond='condition 1'>
  [SHOW CONTENT IF MEET `condition 1`]
<b:elseif cond='condition 2'/>
  [SHOW CONTENT IF MEET `condition 2` and NOT MEET `condition 1`]
<b:else/>
  [SHOW CONTENT IF `condition 1` and `condition 2` evaluates to false]
</b:if>

The b:elseif and b:else tags are optional. So we can use only just b:if, or b:if with b:elseif, or b:if with b:else as needed. For `condition`, we can put anything that will be evaluates to boolean value (true or false).
Example:

<b:if cond='data:post.allowComments'>
<b:if cond='data:blog.pageType != "static_page"'>
<b:if cond='data:blog.pageType == "static_page" AND data:blog.pageName == "About Us"'>
<b:if cond='data:post.labels any (l => l.name == "Tutorial")'>
<b:if cond='data:blog.pageType not in {"static_page", "index"}'>
<b:if cond='data:post.labels any (l => l.name in {"Technology", "Sport"})'>

b:switch statement
The use of b:switch statement is best in case when we need to show content based on same variable.
Format:

<b:switch var='[data]'>
  <b:case value="[value 1]" />
    [SHOW CONTENT IF data value is `value 1]

  <b:case value="[value 2]" />
   [SHOW CONTENT IF data value is `value 1]
  <b:default />
   [SHOW CONTENT IF data value is not equal to any case value above]
</b:switch>

Example:

<b:switch var='data:blog.pageType'>
  <b:case value="static_page" />
    <title>Static Page</title>
  <b:case value="item" />
    <title>Blog Post</title>
  <b:default />
    <h2>Other Page Type</h2>
</b:switch>

LOOPING

b:loop
The b:loop will execute statements or block code for each data of items individually.
Format:

<b:loop var='identifier' index='index' values='set-of-data'>
  [PUT CONTENT LOOP HERE]
</b:loop>

identifier will hold value of each item in loop. index attribut is optional, which is zero-based index. This index will save state current iteration number and is incremented.
Example:

<b:loop index='i' values='data:posts' var='post'>
  <b:if cond='data:i == 3'> 
    <div>Show ads when index number is 3</div>
  </b:if> 
  <div><data:post.title/></div>
</b:loop>

b:loop Allow us to loop between two number, in case we need loop with no set of data relation. Two number that define start and end can be increment (lower to higher) or decrement (higher to lower). example we loop from 1 to 3 that show div element with different class:

<b:loop var='number' values='1 to 3'>
  <div expr:id='"layoutId_" + data:number'></div>
</b:loop>

This section show example how to calling API using HttpClient in C#.

ApiCallerHelper.cs

using System.Collections.Generic;
using System.Linq;
using System.Net.Http;
using System.Net.Http.Headers;

namespace WebApplicationMvcApi.Helper
{
    public class ApiCallerHelper
    {
        private static string endpointName = "endpoint";
        public static HttpResponseMessage getResult(HttpRequestMessage request)
        {

            var queryStringNvp = request.GetQueryNameValuePairs();
            var endpoint = getEndpoint(queryStringNvp);
            var requestParam = getRequestParams(queryStringNvp);
            var actualEndpointWithParam = concatUrlWithParam(endpoint, requestParam);
            
            var headers = request.Headers;

            var newRequest = new HttpRequestMessage(request.Method, actualEndpointWithParam);
            if(request.Method != HttpMethod.Get && request.Method != HttpMethod.Delete)
            {
                newRequest.Content = request.Content;
            }

            var ignoreClientHeaders = new List<string> { "Accept", "Accept-Charset", "Accept-Encoding", "Accept-Language", "Host", "Connection", "Cache-Control", "Origin" };
            
            var headersEnumerable = (IEnumerable<KeyValuePair<string, IEnumerable<string>>>)headers;
            foreach (var header in headersEnumerable)
            {
                if(!ignoreClientHeaders.Exists(item => item.ToLower().Equals(header.Key.ToLower())))
                    newRequest.Headers.Add(header.Key, header.Value);
            }

            return doRequest(newRequest);
        }
        
        private static string concatUrlWithParam(string url, string queryString)
        {
            return string.IsNullOrEmpty(queryString) ? url : string.Format("{0}?{1}", url, queryString);
        }

        private static string getRequestParams(IEnumerable<KeyValuePair<string, string>> nameValuePairs)
        {
            var requestParams = nameValuePairs.Where(item => !item.Key.ToLower().Equals(endpointName.ToLower()))
                .Select(item => string.Format("{0}={1}", item.Key, item.Value));
            return string.Join("&", requestParams); ;
        }

        private static string getEndpoint(IEnumerable<KeyValuePair<string, string>> nameValuePairs)
        {
            var endpointKvp = nameValuePairs.FirstOrDefault(item => item.Key.ToLower().Equals(endpointName.ToLower()));
            if(!endpointKvp.Equals(default(KeyValuePair<string, string>)))
            {
                return endpointKvp.Value;
            }
            return null;
        }

        public static HttpResponseMessage doRequest(HttpRequestMessage request)
        {
            using (HttpClient client = new HttpClient())
            {
                client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
                return client.SendAsync(request).Result;
            }
        }
    }
        
}

API Caller Usage

[RoutePrefix("api/caller")]
public class CallerController : ApiController
{
    [Route("do")]
    [HttpGet, HttpPost, HttpPut, HttpPatch, HttpDelete]
    public HttpResponseMessage Do()
    {
        return ApiCallerHelper.getResult(Request);
    }
}

Example: POST Request

POST /api/caller/do?endpoint=https://reqres.in/api/register HTTP/1.1
Host: localhost:57596
Content-Type: application/json
Cache-Control: no-cache
Postman-Token: 58b8c84f-89a5-ec5d-5196-17f2814cabee

{
    "email": "alex@son.com",
    "password": "Lex_One"
}

POST Response

{
    "token": "QpwL5tke4Pnpja7X"
}

Environment
Windows 10 Home 64bit
Visual Studio 2017 (Version 15.8.4)
Target .Net Framework 4.5.2

This post shows how to break simple captcha using Tesseract in C#. Challenges in the captcha solving is how to train or make a good algorithm so that the computer more better in recognizing the text, such as a human can read the captcha.