Query Error: Error: ER_NOT_SUPPORTED_YET: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Does not work on MySQL, works on Postgres

select
    t.player, 
    sum(case when t.eventid = 1 then t.points end) as event1,
    sum(case when t.eventid = 2 then t.points end) as event2,
    sum(case when t.eventid = 3 then t.points end) as event3,
    sum(case when t.eventid = 4 then t.points end) as event4,
    
    sum(
        case when t.points >= any(
            select best3.points 
            from tbl best3 
            where best3.player = t.player
            order by best3.points desc 
            limit 3
        ) then 
            t.points
        end
    )             
from tbl t
group by t.player

Output on MySQL 8.0:

Query Error: Error: ER_NOT_SUPPORTED_YET: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Output on Postgres:

| player | event1 | event2 | event3 | event4 | sum |
| ------ | ------ | ------ | ------ | ------ | --- |
| 1      | 25     | 15     | 20     | 20     | 65  |
| 2      | 20     | 13     | 12     | 10     | 45  |

You can even made the code neater on Postgres by using FILTER:

Live test: https://www.db-fiddle.com/f/haMmw4S4f7XMqcBD8CDV7H/1

select
    t.player, 
    sum(t.points) filter(where t.eventid = 1) as event1,
    sum(t.points) filter(where t.eventid = 2) as event2,
    sum(t.points) filter(where t.eventid = 3) as event3,
    sum(t.points) filter(where t.eventid = 4) as event4,
    
    sum(t.points) filter(where
        t.points >= any(
            select best3.points 
            from tbl best3 
            where best3.player = t.player
            order by best3.points desc 
            limit 3
        )
    ) as best3          
from tbl t
group by t.player          

Solution on MySQL 8.0, works on Postgres too:

Live test: https://www.db-fiddle.com/f/4ufuFAXKf7mi5yefNQqoXM/2

with ranking as
(
    select 
        player,       
        rank() over(partition by player order by points desc) as xrank,
        points
    from tbl
)
,pick3 as
(
    select 
        player, 
        sum(points) as best3
    from ranking 
    where xrank <= 3
    group by player
)
select
    t.player, 
    sum(if(t.eventid = 1, t.points,0)) as event1,
    sum(if(t.eventid = 2, t.points,0)) as event2,
    sum(if(t.eventid = 3, t.points,0)) as event3,  
    sum(if(t.eventid = 4, t.points,0)) as event4,
    p.best3            
from tbl t
join pick3 p on t.player = p.player
group by t.player

MySQL 5.7 solution:

Live test: https://www.db-fiddle.com/f/4ufuFAXKf7mi5yefNQqoXM/15

select
    t.player, 
    sum(case when t.eventid = 1 then t.points end) as event1,
    sum(case when t.eventid = 2 then t.points end) as event2,
    sum(case when t.eventid = 3 then t.points end) as event3,
    sum(case when t.eventid = 4 then t.points end) as event4,
    
    sum(
        case when t.points >= (
            select best3.points 
            from tbl best3 
            where best3.player = t.player
            order by best3.points desc 
            limit 1 offset 2
        ) then 
            t.points
        end
    ) as best3            
from tbl t
group by t.player;

Output:

| player | event1 | event2 | event3 | event4 | best3 |
| ------ | ------ | ------ | ------ | ------ | ----- |
| 1      | 25     | 15     | 20     | 20     | 65    |
| 2      | 20     | 13     | 12     | 10     | 45    |

Dynamic unpivoting: SQL Server said, Postgres said

SQL Server version:

declare @tx table(
    id int identity(1, 1) not null,
    data varchar(100),
    column1 int,
    column2 int,
    column3 int
);

insert into
    @tx(data, column1, column2, column3)
values
    ('data1', 1, 2, 3),
    ('data2', 4, 5, 6),
    ('data3', 7, 8, null);

select
    a.id,
    a.data,
    c.item,
    c.value 
from
    @tx a
    cross apply (
        values
            (
                cast(
                    (
                        select
                            a.* for xml raw
                    ) as xml
                )
            )
    ) as b(xmldata)
    cross apply (
        select
            item  = xattr.value('local-name(.)', 'varchar(100)'),
            value = xattr.value('.', 'int')
        from
            b.xmldata.nodes('//@*') as xnode(xattr)
        where
            xnode.xattr.value('local-name(.)', 'varchar(100)') not in 
                ('id', 'data', 'other-columns', 'to-exclude')
    ) c

SQL Server can't include null values though.

Postgres version:

Note: Should run this first before being able to use hstore functionality: create extension hstore

create temporary table tx(
    id int generated by default as identity primary key,
    data text,
    column1 int,
    column2 int,
    column3 int
) on commit drop;

insert into
    tx(data, column1, column2, column3)
values
    ('data1', 1, 2, 3),
    ('data2', 4, 5, 6),
    ('data3', 7, 8, null);
    
with a as (
    select
        id,
        data,
        each(hstore(tx.*) - 'id'::text - 'data'::text) as h
    from
        tx
)
select
    id,
    data,
    (h).key as item,
    (h).value::int as value
from
    a 
-- this would work too:
-- where (h).key not in ('id', 'data', 'other-columns', 'to-exclude')

No problem with Postgres, it include nulls


If the number of columns to unpivot is not so dynamic, can do this in Postgres:

select tx.id, tx.data, x.*
from tx
join lateral (
 values
     ('column1', column1), 
     ('column2', column2), 
     ('column3', column3)
) as x(item, value) on true

Equivalent to SQL Server:

select tx.id, tx.data, x.*
from @tx tx
cross apply (  
    values
     ('column1', column1), 
     ('column2', column2), 
     ('column3', column3)    
) as x(item, value);

Both Postgres and SQL Server include nulls in result

https://stackoverflow.com/questions/55731155/combined-semi-transpose-of-a-data/55731461

pgAdmin Internal Server Error

Internal Server Error

The server encountered an internal error and was unable to complete your request. Either the server is overloaded or there is an error in the application.

If you encountered this error on *nix-based system, just delete the .pgAdmin directory from your directory, i.e.,

$ rm -rf ~/.pgadmin

Note that you will need to re-enter your user postgres password when pgAdmin is launched

Not every RDBMS has every

Live test: https://www.db-fiddle.com/f/j1y7WCH2tRcWoE6QMbfRxC/6

Postgres supports the SQL Standard's every.

The query below shows the movie(s) that their genre is exactly Fantasy + Mystery. It won't show the movie if it is Fantasy + Mystery + Drama for example.

Here are the various ways every can be emulated in other RDBMSes.

select m.id, m.title, m.year
from movie m
join movie_genre mg on m.id = mg.movie_id
group by m.id
having 
    count(mg.genre) = 2 -- making sure that the movie has exactly two genres

    -- SQL Standard. Available in Postgres
    and every(mg.genre in ('Fantasy','Mystery'))
    -- Specific to Postgres
    and bool_and(mg.genre in ('Fantasy','Mystery'))

    
    -- MySQL can simulate every/bool_and by using BIT_AND. 
    -- Since MySQL's boolean behaves same as C's int, there's no need to explicitly cast the boolean expression to int. 
    -- Hence MySQL don't need to compare bit_and's result to 1 too.
    -- Following is how it is done in MySQL:
    
    -- and bit_and(mg.genre in ('Fantasy','Mystery'))

    
    
    --  Postgres has bit_and, but it's solely for bit manipulation. 
    --  No Postgres users would use bit_and for every/bool_and functionality, Postgres already has very English-like every/bool_and.
    --  If a user is intent to use bit_and instead of every/bool_and, the user has to cast the boolean expression to integer using ::int. 
    --  And also, bit_and need to be compared with a result:    

    and bit_and((mg.genre in ('Fantasy','Mystery'))::int) = 1


    -- Other RDBMSes
    and count(case when mg.genre in ('Fantasy','Mystery') then mg.genre end) = count(mg.genre)
    and min(case when mg.genre in ('Fantasy','Mystery') then 1 else 0 end) = 1
    and sum(case when mg.genre in ('Fantasy','Mystery') then 0 else 1 end) = 0

Output:

| id  | title                                    | year |
| --- | ---------------------------------------- | ---- |
| 125 | Harry Potter and the Prisoner of Azkaban | 2004 |

DDL:

CREATE TABLE movie (
  id INTEGER primary key,
  title VARCHAR(42),
  year INTEGER
);

INSERT INTO movie
  (id, title, year)
VALUES
  ('308', 'Coraline', '2009'),
  ('125', 'Harry Potter and the Prisoner of Azkaban', '2004'),
  ('204', 'Hugo', '2011'),
  ('42', 'Hitchiker''s guide to galaxy', '2011'),
  ('168', 'Lucky', '2011'),
  ('88', 'Nice', 1969);
  
  

CREATE TABLE movie_genre (
  movie_id INTEGER,
  genre VARCHAR(8)
);

INSERT INTO movie_genre
  (movie_id, genre)
VALUES
  ('308', 'Fantasy'),
  ('308', 'Thriller'),
  ('125', 'Fantasy'),
  ('125', 'Mystery'),
  ('204', 'Fantasy'),
  ('204', 'Mystery'),
  ('204', 'Drama'),
  ('308', 'Fantasy'),
  ('168', 'Fantasy'),
  ('88', 'Mystery'),
  ('88', 'Tour');  

Postgres-specific

Live test: https://www.db-fiddle.com/f/j1y7WCH2tRcWoE6QMbfRxC/2

select m.id, m.title, m.year
from movie m
join movie_genre mg on m.id = mg.movie_id
group by m.id
having 
    count(mg.genre) = 2 -- making sure that the movie has exactly two genres
    and every(mg.genre in ('Fantasy','Mystery'))    

Output:

| id  | title                                    | year |
| --- | ---------------------------------------- | ---- |
| 125 | Harry Potter and the Prisoner of Azkaban | 2004 |

To avoid explicit count (Postgres-specific)

Live test: https://www.db-fiddle.com/f/j1y7WCH2tRcWoE6QMbfRxC/7

select m.id, m.title, m.year
from movie m
join movie_genre mg on m.id = mg.movie_id
group by m.id
having array_agg(mg.genre order by mg.genre) = array['Fantasy', 'Mystery']

Output:

| id  | title                                    | year |
| --- | ---------------------------------------- | ---- |
| 125 | Harry Potter and the Prisoner of Azkaban | 2004 |

Note that we need to use order by mg.genre as array comparison is order-dependent. Without order by mg.genre, Harry Potter will not be shown.

Another good read on every: https://blog.jooq.org/2014/12/18/a-true-sql-gem-you-didnt-know-yet-the-every-aggregate-function/

timestamptz is easier to adjust than timestamp

create table z(id int primary key, _timestamp timestamp, _timestamptz timestamptz);
insert into z(id, _timestamp, _timestamptz) values (1, '2018-05-04T17:37:00Z', '2018-05-04T17:37:00Z');
set timezone to 'Asia/Manila';
select 
    _timestamp as "indeterminate timezone", -- timestamp is an indeterminate timezone. for the purpose of this demo, we are storing UTC-based data
    _timestamptz as "follows set timezone to XXX", 
    _timestamp at time zone 'UTC' at time zone 'Asia/Tokyo' as "local: Japan", 
    _timestamptz at time zone 'Asia/Tokyo' as "local: Japan",
    _timestamp at time zone 'UTC' at time zone 'Asia/Manila' as "local: Philippines", 
    _timestamptz at time zone 'Asia/Manila' as "local: Philippines"
from z;

-[ RECORD 1 ]---------------+-----------------------
indeterminate timezone      | 2018-05-04 17:37:00
follows set timezone to XXX | 2018-05-05 01:37:00+08
local: Japan                | 2018-05-05 02:37:00
local: Japan                | 2018-05-05 02:37:00
local: Philippines          | 2018-05-05 01:37:00
local: Philippines          | 2018-05-05 01:37:00

set timezone to 'Asia/Tokyo';
select 
    _timestamp as "indeterminate timezone", -- timestamp is an indeterminate timezone. for the purpose of this demo, we are storing UTC-based data
    _timestamptz as "follows set timezone to XXX", 
    _timestamp at time zone 'UTC' at time zone 'Asia/Tokyo' as "local: Japan", 
    _timestamptz at time zone 'Asia/Tokyo' as "local: Japan",
    _timestamp at time zone 'UTC' at time zone 'Asia/Manila' as "local: Philippines", 
    _timestamptz at time zone 'Asia/Manila' as "local: Philippines"
from z;

-[ RECORD 1 ]---------------+-----------------------
indeterminate timezone      | 2018-05-04 17:37:00
follows set timezone to XXX | 2018-05-05 02:37:00+09
local: Japan                | 2018-05-05 02:37:00
local: Japan                | 2018-05-05 02:37:00
local: Philippines          | 2018-05-05 01:37:00
local: Philippines          | 2018-05-05 01:37:00

If we use timestamp field type instead of timestamptz, everytime we need to see the local time in another time zone, we have to convert it to UTC first before we can convert it to a local timezone, e.g.,

_timestamp at time zone 'UTC' at time zone 'Asia/Tokyo' as "local: Japan", 
_timestamp at time zone 'UTC' at time zone 'Asia/Manila' as "local: Philippines", 

Whereas if we use timestamptz upfront, we don't need to convert the data to UTC first before we can adjust it to local, we can just adjust it straight to local, e.g.,

_timestamptz at time zone 'Asia/Tokyo' as "local: Japan",
_timestamptz at time zone 'Asia/Manila' as "local: Philippines"

Another benefit of using timestamptz field type and saving UTC-based data to it, is we can set a local timezone per database session, and Postgres will present the UTC data in local timezone, e.g.,

set timezone to 'Asia/Manila';

select 
    _timestamptz as "follows set timezone to XXX"
from z;

-[ RECORD 1 ]---------------+-----------------------
follows set timezone to XXX | 2018-05-05 01:37:00+08

select 
    _timestamptz + interval '1 day' as "follows set timezone to XXX"
from z;

-[ RECORD 1 ]---------------+-----------------------
follows set timezone to XXX | 2018-05-06 01:37:00+08



set timezone to 'Asia/Tokyo';

select 
    _timestamptz as "follows set timezone to XXX"
from z;

-[ RECORD 1 ]---------------+-----------------------
follows set timezone to XXX | 2018-05-05 02:37:00+09

select 
    _timestamptz + interval '1 day' as "follows set timezone to XXX"
from z;

-[ RECORD 1 ]---------------+-----------------------
follows set timezone to XXX | 2018-05-06 02:37:00+09



set timezone to 'UTC';

select 
    _timestamptz as "follows set timezone to XXX"
from z;

-[ RECORD 1 ]---------------+-----------------------
follows set timezone to XXX | 2018-05-04 17:37:00+00

select 
    _timestamptz + interval '1 day' as "follows set timezone to XXX"
from z;

-[ RECORD 1 ]---------------+-----------------------
follows set timezone to XXX | 2018-05-05 17:37:00+00

Whereas if we used timestamp field type instead of timestamptz, Postgres won't apply set timezone to XXX to the timestamp field type, timestamp is always presented as-is. And usage of timestamp field type should be documented, since timestamp is an indeterminate timezone, no one knows whether the developer designed it to be stored of local timezone, or was it designed to be stored of UTC data. Whereas with timestamptz, it is implied that we should only store UTC-based data to it, doing otherwise is a fool's errand.

Postgres timestamp

Happy Coding!

Postgres UTC data type

There's really no type of timestamp with time zone with Postgres. Time zone is not stored, everything is stored as UTC. The time zone is only used for presentation purposes.











Happy Coding!

Using array instead of creating a junction table with foreign keys

Joining is slow on junction table:

explain analyze
select coupons.coupon_id 
from coupons join coupons_products using (coupon_id) 
where coupons.user_id is null and product_id = 1000 group by coupon_id;

Planning time: 0.931 ms
Execution time: 676.687 ms

Try to query against another query:

explain analyze
select coupons.coupon_id 
from coupons 
where coupons.user_id is null 
    and coupons.coupon_id in (select cp.coupon_id from coupons_products cp where cp.product_id = 100);

Planning time: 0.769 ms
Execution time: 0.075 ms

The above has same performance with array approach:

explain analyze
select coupons.coupon_id 
from coupons 
where coupons.user_id is null and product_ids @> array[1000];

Planning time: 0.211 ms
Execution time: 0.075 ms

Array query and junction table query(no-join) has same performance, 0.075 ms. The advantage of array query is its planning takes less time. 0.211 ms vs 0.769 ms.


This is an observation made on denormalizing junction table to array


Happy Coding!

Add column with default value and nullity

create table something
(
p int not null
);

insert into something(p) values(1);


alter table something
add q int default 6 not null;


alter table something
add r int default 8 null;


select * from something;

Postgresql output:

p q r
1 6 8

Sql Server output:

p q r
1 6 null

Custom Basic Authentication and Authorization with AngularJS, Node Express, TypeScript, Massive DAL and PostgreSQL

Design:
    • On successful login, the server shall send back the authentication header that will be saved to angular's $http pipeline
    • Authorization is roles-based
    • Member's role(s) are stored as text array in the database

Prerequisites:
    • Data Access Layer node module
        ○ npm install massive
    • Basic Authentication node module
        ○ npm install basic-auth



I'll go owl on this and just do a code dump and briefly explain each part and their relation to other parts.


What will be created:
    
    • Login screen:
        1. /public/app-dir/Login/Template.html
        2. /public/app-dir/Login/Controller.ts
        
    • Login and Response DTOs shared by client-side and server-side
    
        3. /shared/dto/LoginDto.ts
        4. /shared/dto/LoginResponseDto.ts
        
    • Authentication service for Angular
        5. /public/inhouse-lib/AuthService.ts
    
    • Authentication node service
        6. /api/member.ts
        7. /db/loginVerify.sql

    • Authorization for node REST APIs
        8. /server/CanBe.ts
        9. /db/verifyRoleAccess.sql

    • Member table
        10. server/ddl.sql

    
What need to be changed:
        11. Nodejs's app.ts
            i. Add initialization of massive DAL
            ii. Add authentication url to node
            iii. Authorize a REST API




1. public/app-dir/Login/Template.html

<style>

    .block label { display: inline-block; width: 140px; text-align: left; }

</style>

<div class="col-md-2 col-md-offset-5">
    <form ng-submit="c.login()">
        <div class="block">
            <label>Username</label>
            <input type="text" ng-model="c.username"/>
        </div>

        <div class="block">
            <label>Password</label>
            <input type="password" ng-model="c.password"/>
        </div>

        <div class="block">
            <label></label>
            <input type="submit" value="Login"/>
        </div>

    </form>
</div>

2. public/app-dir/Login/Controller.ts

///<reference path="../../lib-inhouse/doDefine.ts"/>
///<reference path="../../../typings/angularjs/angular.d.ts"/>
///<reference path="../../lib-inhouse/AuthService.ts"/>
///<reference path="../../../typings/sweetalert/sweetalert.d.ts"/>
///<reference path="../../shared-state/AppWide.ts"/>

module App.Login {

    export class Controller {


        username:string;
        password:string;


        // Dependency-injected sweet alert, so it can be easily mocked

        constructor(public authService:InhouseLib.AuthService, public $http:ng.IHttpService, public $state:any,
                    public TheSweetAlert:SweetAlert.SweetAlertStatic,
                    public appWide:SharedState.AppWide) {
        }


        login():void {


            this.authService.verify(this.username, this.password)
                .then(success => {
                    this.appWide.isUploadVisible = true;
                    this.$state.go('root.app.home');
                }, error => {
                    this.appWide.isUploadVisible = false;
                    this.TheSweetAlert(error.status.toString(), "Not authorized", "error");
                });


        }

    }
}


doDefine(require => {

    var mod:angular.IModule = require('eat');


    require('/shared/dto/LoginDto.js');


    mod["registerController"]('LoginController', ['AuthService', '$http', '$state', 'TheSweetAlert', 'AppWide',
        App.Login.Controller]);

});

authService.verify line 27, is where the setting and clearing of Basic Authentication information to Angular's $http pipeline happens, authService.verify returns a promise if the authentication is successful, if the user is not authenticated it goes to the promise's error callback parameter.


public/lib-inhouse/doDefine.ts

///<reference path="../../typings/requirejs/require.d.ts"/>

// the doDefine will be disabled during unit test

function doDefine(func: (cb) => any) {

    define(func);

}

3. shared/dto/LoginDto.ts

///<reference path="../../typings/node/node.d.ts"/>


module Dto {

    export class LoginDto {

        username: string;
        password: string;

    }

}

4. shared/dto/LoginResponseDto.ts

///<reference path="../../typings/node/node.d.ts"/>

module Dto {

    export class LoginResponseDto {

        isValidUser: boolean;

        theBasicAuthHeaderToUse: string;

    }

}

The theBasicAuthHeaderToUse is the header that will be received by Angular and shall be assigned to Angular's $http pipeline. Its format is:

Basic base64encodeOf(username + ': ' + password)

Sample format:
Basic VGhvbSBZb3JrZTpjcmVlcA==


5. public/lib-inhouse/AuthService.ts

///<reference path="../../typings/angularjs/angular.d.ts"/>
///<reference path="../../shared/dto/LoginDto.ts"/>
///<reference path="../../shared/dto/LoginResponseDto.ts"/>

module InhouseLib {

    export class AuthService {


        constructor(public $http:ng.IHttpService, public $q:ng.IQService) {
        }

        verify(username:string, password:string):ng.IPromise<ng.IHttpPromiseCallbackArg<Dto.LoginResponseDto>> {

            var deferred = this.$q.defer();

            var u = new Dto.LoginDto();
            u.username = username;
            u.password = password;



            this.$http.post<Dto.LoginResponseDto>('/api/member/verify', u)
                .then(success => {


                    this.$http.defaults.headers["common"]["Authorization"] = success.data.theBasicAuthHeaderToUse;

                    // This goes to then's success callback parameter
                    deferred.resolve(success);

                }, error => {

                    this.logout();

                    // this goes to then's error callback parameter
                    deferred.reject(error);

                });

            return deferred.promise;

        }

        logout(): void {
            delete this.$http.defaults.headers["common"]["Authorization"];
        }

    }

}

6. Authentication module. Do the base64 encoding of Basic username:passwordHere at the server.

api/member.ts

/// <reference path="../typings/express/express.d.ts"/>
/// <reference path="../typings/extend/extend.d.ts"/>
/// <reference path="../shared/dto/LoginDto.ts"/>
///<reference path="../shared/dto/LoginResponseDto.ts"/>


import express = require("express");

import extend = require('extend');


export function verify(req:express.Request, res:express.Response, next:Function):any {


    var app:express.Application = req["app"];
    var db = app.get('db');


    var loginDto = <Dto.LoginDto> req.body;
    console.log(loginDto.username);

    var loginResponseDto = <Dto.LoginResponseDto>{};

    db.loginVerify([loginDto.username, loginDto.password], (err, result) => {


        if (result.length == 0) {

            loginResponseDto.isValidUser = false;
            loginResponseDto.theBasicAuthHeaderToUse = "Ha! Are you expecting a returned value?!";
            res.status(401).json(loginResponseDto);

        }
        else {

            var member = result[0];
            var salted_password = member.salted_password;

            loginResponseDto.isValidUser = true;
            loginResponseDto.theBasicAuthHeaderToUse = 
                "Basic " + new Buffer(loginDto.username + ":" + loginDto.password).toString("base64");
            res.json(loginResponseDto);
        }

    });

}

7. db/loginVerify.sql

This is called by the Authentication module.

For a primer of bcrypt mechanism: http://www.ienablemuch.com/2014/10/bcrypt-primer.html

select member_name, salted_password
from member
where member_name = $1 and salted_password = crypt($2, salted_password);

This is where the authorization happens.


8. Authorization module

server/CanBe.ts

/// <reference path="../typings/express/express.d.ts"/>

import express = require('express');

var basicAuth = require('basic-auth');


export function accessedBy(roles:string[]):express.RequestHandler {
    return accessedByBind.bind(undefined, roles);
}


function accessedByBind(roles:string[], req:express.Request, res:express.Response, next:Function): any {

    function unauthorized(): any {
        res.set('WWW-Authenticate', 'Basic realm=Authorization Required');
        return res.sendStatus(401);
    }


    // === accessedByBind starts here ===

    var app:express.Application = req["app"];
    var db = app.get('db');


    var user = basicAuth(req);

    if (!user || !user.name || !user.pass) {
        return unauthorized();
    }

    var username = user.name;
    var password = user.pass;

    db.verifyRoleAccess([username, password, roles], (err, result) => {

        if (err == null) {
            var isAllowed = result[0].is_allowed;

            if (isAllowed)
                return next();
            else {
                return unauthorized();
            }
        }
        else {
            return res.status(500).json({customError: 'Unknown Error'});
        }

    });


}

9. db/verifyRoleAccess.sql

This is called by the CanBe Authorization module.

select exists(

select null
from member
where
member_name = $1 and salted_password = crypt($2, salted_password)
and roles && $3 -- check if $3 (arrayOfRoles) passed from canBe.accessedBy(arrayOfRolesHere) is in member.roles.

) as is_allowed;

10. server/ddl.sql

create table member
(
    member_id serial primary key,
    member_name citext not null unique,
    salted_password text not null, -- using bcrypt
    roles text[]
);

insert into member(member_name, salted_password, roles) values
('Thom Yorke', crypt('creep', gen_salt('bf')), '{"rockstar"}')

11. app.ts

var massive = require('massive'); // DAL
import memberApi = require('./api/member'); // authentication 
import canBe = require('./server/CanBe'); // authorization


var massiveInstance = massive.connectSync({connectionString: 'postgres://postgres:yourPasswordHere@localhost/commerce'});
app.set('db', massiveInstance);

app.post('/api/member/verify', memberApi.verify);

// unsecured API
app.get('/api/item', itemApi.search);

// just insert canBe.accessedBy before the itemApi.search RequestHandler REST API to secure the REST API
app.get('/api/item-secured', canBe.accessedBy(['rockstar']), itemApi.search); 

UPDATE:

Some code above has Cargo Culting in it, it uses $q.defer unnecessarily. Haven't properly learned the promise's fundamentals before :) Read: https://www.codelord.net/2015/09/24/%24q-dot-defer-youre-doing-it-wrong/

Fast text search in PostgreSQL

This works in Postgres:

select * from location where location_id = any(array[1,6,8])

However, if you tried to match it against an array on another query, it won't work, even though the another query returns exactly one row only.

select * from location where location_id = any(select location_ids from province p where p.province_name = 'Metro Manila' limit 1)

Output:

ERROR:  operator does not exist: integer = integer[]
LINE 1: select * from location where location_id = any
                                                 ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

To correct it, unnest the array:

select * from location where location_id = any(select unnest(location_ids) from province p where p.province_name = 'Metro Manila')

Might as well use IN:

select * from location where location_id in (select unnest(location_ids) from province p where p.province_name = 'Metro Manila')

There's a drawback on the queries above, it uses equal operator. In Postgres, indexers are bound to operator, and GIN, the indexing technology for indexing array, doesn’t support equal operator. See the detail here: http://stackoverflow.com/questions/4058731/can-postgresql-index-array-columns/29245753#29245753


So to optimize the query, use either of the following:

-- This query works same as the query at the bottom. The query at the bottom is more readable though.
-- The advantage of this query is if the province matches many rows, it would still work, say the province_name uses ILIKE operator.

select  *
from    location l
where   exists
        (
            select  p.location_ids
            from    province p
            where   p.province_name = 'Metro Manila'
                    and array[l.location_id] && p.location_ids
        );

The query uses overlap operator, &&. See the use of supported operators on arrays: http://www.postgresql.org/docs/9.1/static/functions-array.html

-- If province returns exactly one row, this is more readable than above
select   *
from     location l
where    array[l.location_id] && ( select location_ids p from province p where p.province_name = 'Metro Manila' );

Live code: http://sqlfiddle.com/#!15/6ac38/5


Happy Coding!

Use citext on PostgreSQL

Case-insensitive text type is not installed by defaut on Postgres, to use, run this in pgAdmin:

CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;

PostgreSQL (mis)feature

Martin Smith pointed out a gotcha on Postgres feature I recommended


GROUP BY on alias doesn't work properly when the alias has a same name from the table's columns

select 'foo' as Table_name
from information_schema.tables 
group by Table_name

Live code: http://sqlfiddle.com/#!15/d41d8/1920

When DEFAULT doesn't default

I almost gave a wrong code review to a colleague:

It's better to use NOT NULL:

ALTER TABLE dbo.blah
ADD IsTransferred BIT NULL DEFAULT (0);

Is there a business or technical reason why IsTransferred should be nullable?

On the above DDL for IsTransferred, nullable or not nullable, IsTransferred defaults to 0, hence this is a superfluous code:

-- initialize new column, i.e., NULL columns, to "not transferred"
UPDATE dbo.blah
SET IsTransferred = 0
WHERE IsTransferred IS NULL ;


Further read: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/06/null-ndash-the-database-rsquo-s-black-hole.aspx

The code could be shortened and optimized but I'm not inclined to advise it; this query is just a one-time thing, no need to micro-optimize. The code is good enough

Spotted the wrong thing on the above advice? The above advice is correct if we are using PostgreSQL. However we are using SQL Server, the newly-added IsTransferred field will default to null if the field is nullable regardless of the above DDL indicating a default value of false(0).


So the UPDATE above is not a superfluous code, at least on SQL Server, the UPDATE is still needed despite the DEFAULT 0 clause. PostgreSQL (and other RDBMSes for that matter) is intuitive in this regard, it honors the DEFAULT clause regardless of the field is nullable or not.


Further read: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/06/null-ndash-the-database-rsquo-s-black-hole.aspx


PostgreSQL-related news: https://twitter.com/spolsky/status/433323487961178113



Happy Coding! ツ

Use IMMUTABLE on PostgreSQL function to avoid optimization fence

I thought this is the PostgreSQL equivalent of SQL Server's efficient inline table-valued function:

create or replace function get_orgs() returns table
(
ID int,
Favorite int
)
as
$$ 
    select p.person_id, eorg.ounits
    from person p
    join lateral
    (
        select case when p.person_id = 4 then p.person_id / 0 else 7 end
    ) eorg(ounits) on true
$$ language sql;

Using that function on this expression..

select p.*, o.* 
from person p
join get_orgs() o on p.person_id = o.ID
where p.person_id < 4

..produces a divide-by-zero error on PostgreSQL, while SQL Server does not materializes the rows that are greater than or equal to 4, hence this doesn't produce an error on SQL Server:

ERROR:  division by zero
CONTEXT:  SQL function "get_orgs" statement 1

********** Error **********

ERROR: division by zero
SQL state: 22012
Context: SQL function "get_orgs" statement 1

To achieve the same optimized execution plan of SQL Server on PostgreSQL, must add IMMUTABLE on the function:

create or replace function get_orgs() returns table
(
ID int,
Favorite int
)
as
$$ 
    select person_id, eorg.ounits
    from person p
    join lateral
    (
        select case when p.person_id = 4 then p.person_id / 0 else 7 end
    ) eorg(ounits) on true
$$ language sql IMMUTABLE;

When that function is used on the the query above, it doesn't yield divide-by-zero anymore, it's now efficient, it doesn't eager load the function's result, it's like the function is inlined on the query itself, the query that joins to the IMMUTABLE function behaves like a table-deriving query, very efficient, no divide-by-zero will occur, i.e., the RDBMS just expand the function's query to other query, divide-by-zero error won't happen, to wit:

select p.*, o.* 
from person p
join
(
    select p.person_id as ID, eorg.ounits
    from person p
    join lateral
    (
        select case when p.person_id = 4 then p.person_id / 0 else 7 end
    ) eorg(ounits) on true
)
o on p.person_id = o.ID
where p.person_id < 4;

Produces this output, has no divide by zero error:

 person_id | lastname  |  firstname   | nickname | favorite_number | id | favorite 
-----------+-----------+--------------+----------+-----------------+----+----------
         2 | mccartney | james paul   | paul     |                 |  2 |        7
         3 | harrison  | george       |          |                 |  3 |        7
         1 | lemon     | john winston | john     |                 |  1 |        7
(3 rows)

However, PostgreSQL, unlike SQL Server, has an optimization fence on its CTE, i.e., PostgreSQL eagerly loads the result of the CTE. SQL Server's CTE is more efficient than PostgreSQL's CTE on this regard:

with o as
(
    select p.person_id as ID, eorg.ounits
    from person p
    join lateral
    (
        select case when p.person_id = 4 then p.person_id / 0 else 7 end
    ) eorg(ounits) on true
)
select p.*, o.* 
from person p
join o on p.person_id = o.ID
where p.person_id < 4;

That CTE yields the following error on Postgresql, a proof that Postgresql eagerly load the result of the CTE; while on SQL Server it has no error, SQL Server doesn't eagerly load the result of CTE, a proof that SQL Server sees CTE as subject to execution plan rather than an eager-loading mechanism to fetch the results. Eagerly-loaded results has no chance to be optimized, as it looks like a blackbox from the caller, the caller can't optimize what's inside

ERROR:  division by zero

********** Error **********

ERROR: division by zero
SQL state: 22012

If we will follow C language principle design, I wishes PostgreSQL to use an existing keyword to indicate we don't want an optimization fence on a CTE:

with o immutable as
(
    select p.person_id as ID, eorg.ounits
    from person p
    join lateral
    (
        select case when p.person_id = 4 then p.person_id / 0 else 7 end
    ) eorg(ounits) on true
)
select p.*, o.* 
from person p
join o on p.person_id = o.ID
where p.person_id < 4;

If a CTE's query can be re-used, the query is better be made as view instead, a query inside a view don't have optimization fence too, i.e., it also behaves as table-deriving query, view's query just get expanded to other query, this won't produce divide-by-zero error:

create or replace view vw_get_orgs as
    select person_id as ID, eorg.ounits
    from person p
    join lateral
    (
        select case when p.person_id = 4 then person_id / 0 else 7 end
    ) eorg(ounits) on true;


select p.*, o.* 
from person p
join vw_get_orgs o on p.person_id = o.ID
where p.person_id < 4;

Happy Coding!

Very efficient JSON in PostgreSQL

From this old approach:

  select array_to_json(array_agg(row_to_json(t))) 
  from (select firstname, lastname, favorite_number from person) t;

To this new one:

  select json_agg(t)
  from (select firstname, lastname, favorite_number from person) t;

Bye bye C#, PHP, Java, Ruby. You are so long, and thanks for all the fish*

I told my friend(whose career is built around PHP) about AngularJS, like everybody else who discovered the joys of using AngularJS, he's very excited learning something revolutionary. He shared that one time he's learning AngularJS up to 3 AM.




What he likes in AngularJS is the PHP's role in a web application development is taking a backseat, and the realization that he doesn't even need PHP at all, any server-side languages that can serve JSON would do, be it from C#, Java, Ruby and whathaveyou. He also don't like it that PHP and HTML are being interwoven too much. I don't know if Mark Zuckerberg shares the same sentiment.


One thing is for sure though, given that interweaving PHP and HTML is unavoidable, the integration should feel natural and make the integration cross-site-scripting-attack-proof. Facebook took a page from ASP.NET MVC Razor capabilities on this matter, Facebok created XHP. With XHP, they made interweaving PHP and HTML very seamless, PHP's output is now automatically HTML-escaped too, cross-site-scripting attacks can be avoided. Of course, I'm just massaging the ego of ASP.NET MVC programmers here, open-source folks doesn't need our innovations :-) Seamless integration between programming language and markup language is something everyone are pining for ever since everyone moved to web for rapid application development. I digress.




It's not surprising that AngularJS is the easiest to use JavaScript MVC framework, given that Misko Hevery created AngularJS as an easy-to-use framework for his JSON service business, which is priced by the megabyte. JSON service is a bad kind of business though, if they really wanted to earn well they should serve XML instead of JSON :D



While everyone is abuzz and getting nerdgasmic with the release of iPhone 5S last September 10, I'm eagerly anticipating and excited on the release of PostgreSQL 9.3 last September 9. JSON in PostgreSQL is becoming more and more a first-class language construct, API-wise and syntax-wise.



As for the reason why the excitement and how it relates to AngularJS, let's get to the core principle of AngularJS:

Every web-application is really just once complex marshaling problem. We marshal data from the DB, through the HTTP into the heap and to the DOM for the user to read, and then we do it all again in reverse order. At first this is fun, but after you build few applications, you very quickly get tired of marshaling your data through these layers, and I wanted to do something about it. -- Misko Hevery

While my friend is happy with lesser role of PHP in a web application development using AngularJS, any server-side languages for that matter, I'm stoked at the idea that we don't even need server-side languages at all. If only RDBMSes can expose RESTful services the way ServiceStack, ASP.NET Web API, WCF, JAX-RS and Recess Framework can do, the days of server-side languages would be numbered. Bye bye C#, PHP, Java, Ruby, you are so long, and thanks for all the fish.



RDBMS RESTful? I'm fancying that. But before we get there, let's see how easy it is to make web applications with AngularJS and the supercharged PostgreSQL JSON functionalities.



Here's how easy it is to serve JSON with PostgreSQL:

northwind=# with company_summary_info as 
(
    select c."CustomerID", c."CompanyName", c."ContactName"
    from customers c where c."ContactName" like 'A%'
)
select json_agg(i.*)
from company_summary_info i;
                                                 json_agg                                                  
-----------------------------------------------------------------------------------------------------------
 [{"CustomerID":"ANATR","CompanyName":"Ana Trujillo Emparedados y helados","ContactName":"Ana Trujillo"}, +
  {"CustomerID":"ANTON","CompanyName":"Antonio Moreno Taquería","ContactName":"Antonio Moreno"},          +
  {"CustomerID":"EASTC","CompanyName":"Eastern Connection","ContactName":"Ann Devon"},                    +
  {"CustomerID":"FAMIA","CompanyName":"Familia Arquibaldo","ContactName":"Aria Cruz"},                    +
  {"CustomerID":"GOURL","CompanyName":"Gourmet Lanchonetes","ContactName":"André Fonseca"},               +
  {"CustomerID":"LAMAI","CompanyName":"La maison d'Asie","ContactName":"Annette Roulet"},                 +
  {"CustomerID":"MORGK","CompanyName":"Morgenstern Gesundkost","ContactName":"Alexander Feuer"},          +
  {"CustomerID":"ROMEY","CompanyName":"Romero y tomillo","ContactName":"Alejandra Camino"},               +
  {"CustomerID":"SPLIR","CompanyName":"Split Rail Beer & Ale","ContactName":"Art Braunschweiger"},        +
  {"CustomerID":"TRADH","CompanyName":"Tradição Hipermercados","ContactName":"Anabela Domingues"}]
(1 row)

Very easy.


The plus signs doesn't turn up in actual JSON result, it's just presented that way by PostgreSQL's commandline tool. The type returned is not a string, PostgreSQL has a JSON datatype. Being a first-class data type that it is, you can manipulate it further with PostgreSQL's superb JSON API and operators.




Returning a JSON paged result(along with total records count) with the latest PostgreSQL:

create or replace function get_customers(p_pageNumber int, p_pageSize int) returns json
as
$$
begin

return (
    with src as
    (
        select c."CustomerID", c."CompanyName", c."ContactTitle", c."ContactName", c."City"
        from customers c
        order by c."CompanyName"
    ),
    paged as
    (
        select * 
        from src
        offset ((p_pageNumber - 1) * p_pageSize)        
        limit p_pageSize
    ),
    returnedJson as
    (
        select  
            (select json_agg(p.*) from paged p) as list,
            (select count(*) from src) as count
    )
    select row_to_json(r.*) from returnedJson r
);  
    
end;
$$ language 'plpgsql';    
 
 

Sample result:

northwind=# select get_customers(1,10);
                                                                            get_customers                                                                            
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"list":[{"CustomerID":"ALFKI","CompanyName":"Alfreds Futterkiste","ContactTitle":"Sales Representative","ContactName":"Maria Anders","City":"Berlin"},            +
  {"CustomerID":"ANATR","CompanyName":"Ana Trujillo Emparedados y helados","ContactTitle":"Owner","ContactName":"Ana Trujillo","City":"México D.F."},               +
  {"CustomerID":"ANTON","CompanyName":"Antonio Moreno Taquería","ContactTitle":"Owner","ContactName":"Antonio Moreno","City":"México D.F."},                        +
  {"CustomerID":"AROUT","CompanyName":"Around the Horn","ContactTitle":"Sales Representative","ContactName":"Thomas Hardy","City":"London"},                        +
  {"CustomerID":"BSBEV","CompanyName":"B's Beverages","ContactTitle":"Sales Representative","ContactName":"Victoria Ashworth","City":"London"},                     +
  {"CustomerID":"BERGS","CompanyName":"Berglunds snabbköp","ContactTitle":"Order Administrator","ContactName":"Christina Berglund","City":"Luleå"},                 +
  {"CustomerID":"BLAUS","CompanyName":"Blauer See Delikatessen","ContactTitle":"Sales Representative","ContactName":"Hanna Moos","City":"Mannheim"},                +
  {"CustomerID":"BLONP","CompanyName":"Blondesddsl père et fils","ContactTitle":"Marketing Manager","ContactName":"Frédérique Citeaux","City":"Strasbourg"},        +
  {"CustomerID":"BONAP","CompanyName":"Bon app'","ContactTitle":"Owner","ContactName":"Laurence Lebihan","City":"Marseille"},                                       +
  {"CustomerID":"BOTTM","CompanyName":"Bottom-Dollar Markets","ContactTitle":"Accounting Manager","ContactName":"Elizabeth Lincoln","City":"Tsawassen"}],"count":91}
(1 row)

Here's how to extract the values from JSON:

create or replace function save_customer(p_customer json) returns json
as
$$
begin
    
    if not exists(
        select * from customers
        where "CustomerID" = p_customer->>'CustomerID') then

        insert into customers
        ("CustomerID","CompanyName","ContactName","ContactTitle","City",
        "Region", "PostalCode", "Country", "Phone", "Fax"
        )   
        select
            p_customer->>'CustomerID',
            p_customer->>'CompanyName',
            p_customer->>'ContactName',
            p_customer->>'ContactTitle',
            p_customer->>'City',
            '', -- Region
            '', -- PostalCode
            '', -- Country
            '', -- Phone
            ''; -- Fax
                        
    else

        update Customers set    
            "CompanyName" = p_customer->>'CompanyName',
            "ContactName" = p_customer->>'ContactName',
            "ContactTitle" = p_customer->>'ContactTitle',
            "City" = p_customer->>'City'            
        where "CustomerID" = p_customer->>'CustomerID';
        
    end if;

    return (with src as
    (
        select c."CustomerID", c."CompanyName", c."ContactTitle", c."ContactName", c."City"
        from customers c
        where c."CustomerID" = p_customer->>'CustomerID'
        order by c."CompanyName"
    )
    select row_to_json(c.*) from src c);

end;
$$ language 'plpgsql';

Now, how about our PHP web developers? What is now the use of their decades-long experience on PHP? Following code is the thinnest and fastest marshaller of data to your web app, courtesy of PostgreSQL's superb JSON functionality


Serving paged results:

<?php 
    header('Access-Control-Allow-Headers: Content-Type, application/json, X-Requested-With');
    header("Access-Control-Allow-Origin: *"); 

    $pageNumber = $_GET["PageNumber"];
    $pageSize = $_GET["PageSize"];
    
    $con = pg_connect("host=localhost port=5433 dbname=northwind user=postgres password=opensesame93");

    $res = pg_query_params($con, "select get_customers($1,$2)", array($pageNumber, $pageSize));
    
    if ($res) {
        $row = pg_fetch_row($res);
        echo $row[0];
    }
    else {
        echo "Error";       
    }
?>

Serving persistence requests:

<?php 

    header('Access-Control-Allow-Headers: Content-Type, application/json, X-Requested-With');
    header("Access-Control-Allow-Origin: *"); 
    
    $con = pg_connect("host=localhost port=5433 dbname=northwind user=postgres password=opensesame93");

    
    $data = file_get_contents("php://input");   
    
    /*  
    $getData = json_decode($data);  
    $CompanyName = $getData->CompanyName;
    */
        
    $res = pg_query_params($con, "select save_customer($1)", array($data));
        
    if ($res) {
        $row = pg_fetch_row($res);
        echo $row[0];
    }
    else {
        echo "Error";       
    }
  
?>

For our web designer bretheren, they would be delighted that there's no more server-side languages that gets in the way between them and their beloved HTML and CSS, they could change the tags and css to their heart's content as long they keep in mind some basic HTML principles like ng-repeat and moustache tags. With AngularJS, web designer could now work in parallel and independently from web developers.


Customers.html

<!DOCTYPE html>
<html ng-app="sampleApp">
<head>
    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <link rel="stylesheet" href="angularjs-twitter-bootstrap/assets/bootstrap.css"/>

    <script src="angularjs/angular.min.js"></script>

    <script src="angularjs-twitter-bootstrap/ui-bootstrap-0.6.0.min.js"></script>

    <script src="app.js"></script>

    <title></title>
</head>
<body ng-controller="CustomersCtrl">

<p>PostgreSQL loved by DBAs and Application Developers alike</p>


<div class="form-inline">
    <div class="btn-group">
    <button type="button" class="btn btn-default btn-sm" ng-click="prevPage()" ng-disabled="!(pageNumber > 1)">Previous</button>
        <button type="button" class="btn btn-default btn-sm"  ng-click="nextPage()" ng-disabled="isLastPage()">Next</button>
    </div>

    <input type="number" ng-model="pageNumber" ng-change="getCustomers()" style="width:50px">
    <select ng-model="pageSize" ng-options="opt for opt in pageSizeOptions" ng-change="getCustomers()"></select>
</div>


<p></p>


<div class="panel panel-default">
    <div class="panel-heading">Customers: {{customers.count}}</div>
    <table class="table">
        <thead>
            <th>
                <i class="icon-large icon-plus-sign" style="width: 20px"></i>
            </th>
            <th>Company</th>
            <th>Contact Title</th>
            <th>Contact</th>
            <th>City</th>
        </thead>

        <tbody>
            <tr ng-repeat="c in customers.list">
                <td ng-click="showForm(c)"
                    style="cursor: pointer;">
                    <i  class="icon-large icon-pencil"
                        style="width: 20px">
                     </i>
                </td>
                <td>{{c.CompanyName}}</td>
                <td>{{c.ContactTitle}}</td>
                <td>{{c.ContactName}}</td>
                <td>{{c.City}}</td>
            </tr>
        </tbody>

    </table>
</div>


</body>

</html>

CustomersForm.html

<div class="modal-header">
    <h3>{{Customer.CustomerID}}</h3>
</div>
<div class="modal-body">

    <div class="form-inline">
    Company: <input type="text" ng-model="Customer.CompanyName">
    </div>


</div>
<div class="modal-footer">
    <button class="btn btn-primary" ng-click="ok()">OK</button>
    <button class="btn btn-warning" ng-click="cancel()">Cancel</button>
</div>

Here's the AngularJS code, the glue framework. This is now where the battle-scarred web application developers need to turn their discipline to:

var theApp = angular.module('sampleApp', ['ui.bootstrap']);


theApp
    .controller('CustomersCtrl',['$scope','$http', '$modal', function ($scope, $http, $modal) {

        $scope.customers = { count: 0 };
        $scope.pageNumber = 1;
        $scope.pageSize = 10;
        $scope.pageSizeOptions = [5, 10, 15, 20, 25];


        $scope.pageNumber = 1;



        $scope.getCustomers = function() {
            var pageNumber = $scope.pageNumber;
            var pageSize = $scope.pageSize;

            $http({
                url: 'http://192.168.254.100/getCustomers.php',
                method: 'GET',
                params: { PageNumber: pageNumber, PageSize : pageSize }
            }).success(function (data, status, headers, config) {
                    $scope.customers = data;
            }).error(function(data, status, headers, config) {
                    $scope.message = "error";
            });

        }



        $scope.showForm = function(customer) {
            var modalInstance = $modal.open({
                templateUrl: 'CustomersForm.html',
                controller: 'CustomersFormCtrl',
                resolve: {
                    Customer: function () {
                        return angular.copy(customer);
                    },
                    refresher : function() {
                        return $scope.refresh;
                    }
                }
            });
        };

        $scope.refresh = function() {
            $scope.getCustomers();
        };


        $scope.nextPage = function() {
            ++$scope.pageNumber;
            $scope.getCustomers();
        };

        $scope.prevPage = function() {
            if ($scope.pageNumber == 1) return;
            --$scope.pageNumber;
            $scope.getCustomers();
        };

        $scope.isLastPage = function() {
            return Math.ceil($scope.customers.count / $scope.pageSize) == $scope.pageNumber;
        };



        $scope.getCustomers();


    }])

    .controller('CustomersFormCtrl', ['$scope','$http', '$modalInstance', 'Customer', 'refresher',
        function ($scope, $http, $modalInstance, Customer, refresher) {

        $scope.Customer = Customer;


        $scope.ok = function () {

            $http({
                url: 'http://192.168.254.100/saveCustomer.php',
                method: 'POST',
                data: $scope.Customer,
                headers: {'Content-Type': "application/json"}
            }).success(function (data, status, headers, config) {

                $modalInstance.dismiss('ok');
                refresher();

            }).error(function(data, status, headers, config) {

                // called asynchronously if an error occurs
                // or server returns response with an error status.

                $scope.message = "error";
            });


        };

        $scope.cancel = function () {
            $modalInstance.dismiss('cancel');
        };

    }]);

* The humor of the phrase is not lost on me. The phrase was alluded to what the dolphin said on Hitchhiker's Guide To The Galaxy. Dolphin, being a MySQL mascot, could have titled this blog post as "So long, and thanks for all the dolphins" lol



Happy Coding! ツ

SQL Server Said, PostgreSQL Said. APPLY and LATERAL

SQL Server CROSS APPLY:

select m.*, elder.*
from Member m
cross apply
(
    select top 1 ElderBirthDate = x.BirthDate
    from Member x 
    where x.BirthDate < m.BirthDate 
    order by x.BirthDate desc
) as elder
order by m.BirthDate

Equivalent on PostgreSQL:

select m.*, elder.*
from Member m
join lateral 
(
    select x.BirthDate as ElderBirthDate, x.FirstName as ElderFirstName
    from Member x 
    where x.BirthDate < m.BirthDate 
    order by x.BirthDate desc
 limit 1
) as elder on true -- Though PostgreSQL's LATERAL offers more flexibility than SQL Server's APPLY, I haven't yet found a use case to use a condition on LATERAL, hence the hardcoded true
order by m.BirthDate

Note, a JOIN LATERAL(explicitly INNER JOIN LATERAL) with a condition of always true, is essentially a cross join. We can rewrite the PostgreSQL code above as follows:

select m.*, elder.*
from Member m
cross join lateral 
(
    select x.BirthDate as ElderBirthDate, x.FirstName as ElderFirstName
    from Member x 
    where x.BirthDate < m.BirthDate 
    order by x.BirthDate desc
    limit 1
) as elder -- we don't need to put any condition here anymore since this is a cross join
order by m.BirthDate

SQL Server OUTER APPLY:

select m.*, elder.*
from Member m
outer apply
(
    select top 1 ElderBirthDate = x.BirthDate, ElderFirstname = x.Firstname
    from Member x 
    where x.BirthDate < m.BirthDate 
    order by x.BirthDate desc
) as elder
order by m.BirthDate

Equivalent on PostgreSQL:

select m.*, elder.*
from Member m
left join lateral 
(
    select x.BirthDate as ElderBirthDate, x.FirstName as ElderFirstName
    from Member x 
    where x.BirthDate < m.BirthDate 
    order by x.BirthDate desc
    limit 1
) as elder on true -- Though PostgreSQL's LATERAL offers more flexibility than SQL Server's APPLY, I haven't yet found a use case to use a condition on LATERAL, hence the hardcoded true
order by m.BirthDate

Related: http://www.ienablemuch.com/2012/04/outer-apply-walkthrough.html

Example: http://www.sqlfiddle.com/#!17/50ee1/8

Happy Coding! ツ

Seamless dynamic SQL using PostgreSQL

Have you seen seamless dynamic query on a SQL Server function?

Neither do I, so I will show to you a PostgreSQL approach.

To cut to the chase, here's the query:

create or replace function DynamicSql(p_fields varchar)
    returns table
    (    
        "YouKnowMyName" varchar,
        "LookupTheNumber" int
    ) as    
$$
declare
    sql varchar;    
begin

    sql = 'select ' || p_fields || ' from employees';
    
    return query execute sql;
end;
$$ language 'plpgsql';

select * from DynamicSql(' "LastName", "EmployeeID" ' );
select * from DynamicSql(' "FirstName", "EmployeeID" ' );

Look Ma! No Drama!



Contrast that on how it is done on other database *cough* Sql Server Table-Valued Function via CLR *cough*:

http://www.ienablemuch.com/2013/07/streaming-table-valued-function-via-clr.html

Accomplishing dynamic SQL on a Table-Valued Function via CLR is too ceremonial and has too much drama, y'know you gotta need to accomplish all of these: FillRowMethodName, Streaming Table-Valued Functions via yield return, assembly signing, sp_configure 'clr enabled', create asymmetric key, grant external access, blah blah and all that shiznit.


Microsoft should be included on the list of featured PostgreSQL users given that Microsoft bought Skype. I Love Technology!


"You already trust PostgreSQL, you just may not know it yet..." -- http://www.2ndquadrant.com/en/who-uses-postgresql/

Happy Coding! ツ

Transposing columns to rows.

You have already probably done some crosstab query from your favorite RDBMS, i.e. the rows are transposed to columns.

But how about a reverse crosstab(a.k.a. reverse pivot)? You got columns that you wanted to transpose to rows.

That is, you need to display this...

testdb-# 

with p as
(
    select *, firstname || ' ' || lastname as fullname 
    from person 
)
select * from p;


 person_id | lastname  |  firstname   | nickname | favorite_number |       fullname       
-----------+-----------+--------------+----------+-----------------+----------------------
         1 | lennon    | john winston | john     |                 | john winston lennon
         2 | mccartney | james paul   | paul     |                 | james paul mccartney
         3 | harrison  | george       |          |                 | george harrison
         4 | starr     | richard      | ringo    |              10 | richard starr
(4 rows)

...as this:

person_id  |      field      |        value         
-----------+-----------------+----------------------
         1 | favorite_number | 
         1 | firstname       | john winston
         1 | fullname        | john winston lennon
         1 | lastname        | lennon
         1 | nickname        | john
         1 | person_id       | 1
         2 | favorite_number | 
         2 | firstname       | james paul
         2 | fullname        | james paul mccartney
         2 | lastname        | mccartney
         2 | nickname        | paul
         2 | person_id       | 2
         3 | favorite_number | 
         3 | firstname       | george
         3 | fullname        | george harrison
         3 | lastname        | harrison
         3 | nickname        | 
         3 | person_id       | 3
         4 | favorite_number | 10
         4 | firstname       | richard
         4 | fullname        | richard starr
         4 | lastname        | starr
         4 | nickname        | ringo
         4 | person_id       | 4
(24 rows)

On other RDBMS, you got to do this query:

with p as
(
    select *, firstname || ' ' || lastname as fullname 
    from person 
)
select 
    person_id,
    'firstname' as field,
    firstname as value
from p
union all
select 
    person_id,
    'firstname' as field,
    firstname as value
from p
union all
select 
    person_id,
    'lastname' as field,
    firstname as value
from p
union all
select 
    person_id,
    'nickname' as field,
    nickname as value
from p
union all
select 
    person_id,
    'fullname' as field,
    fullname as value
from p
union all
select 
    person_id,
    'favorite_number' as field,
    favorite_number::text as value
from p
order by person_id, field

That's very tedious.


If you are using Postgres, luckily there is a better way:

with p as
(
    select *, firstname || ' ' || lastname as fullname 
    from person 
)
select
    person_id,
    skeys(hstore(p)) as field, svals(hstore(p)) as value 
from p
order by person_id, field

That's more maintainable, even if you add another column on the main query, your key value pair query need not be modified.



There's another approach (darn, PostgreSQL is too flexible! :D)

with p as
(
    select *, firstname || ' ' || lastname as fullname 
    from person 
)
select 
    p.person_id,
    unnest(array['person_id', 'favorite_number', 'firstname','fullname','lastname', 'nickname']) as field_label,
    unnest(array[cast(p.person_id as text), cast(p.favorite_number as text), p.firstname, p.fullname, p.lastname, p.nickname])  as field_value
from p
order by p.person_id, field_label

That's it folks. Happy Computing! ツ


Data Source:

create table person
(
 person_id serial not null primary key,
 lastname text not null,
 firstname text not null,
 nickname text null,
 favorite_number int null
);
 
 
insert into person(lastname,firstname,nickname, favorite_number) values
('lennon','john winston','john',default),
('mccartney','james paul','paul',default),
('harrison','george',default,default),
('starr','richard','ringo', 10);