Thursday, April 18, 2019

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

Saturday, April 13, 2019

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

Friday, April 12, 2019

SQL Intersect

create table test (
  id integer,
  pid integer,
  name varchar(2),
  val integer
);

insert into test
  (id, pid, name, val)
values
  ('1', '1', 'aa', '10'),
  ('2', '1', 'bb', '20'),
  ('3', '1', 'cc', '30'),
  ('4', '2', 'aa', '10'),
  ('5', '2', 'bb', '20'),
  ('6', '2', 'cc', '30'),
  ('7', '3', 'aa', '10'),
  ('8', '3', 'bb', '20'),
  ('9', '3', 'cc', '999');

select distinct pid from test 
where
pid in (select pid from test where (name,val) = ('aa',10))     
and pid in (select pid from test where (name,val) = ('bb',20))
and pid in (select pid from test where (name,val) = ('cc',30));



-- works on all RDBMS
select pid from test where (name,val) = ('aa',10)
and pid in (
    select pid from test where (name,val) = ('bb',20)
    and pid in (
        select pid from test where (name,val) = ('cc',30)
    )
);


-- works on most RDBMS, MySQL has no INTERSECT
select pid from test where (name,val) = ('aa',10)
intersect
select pid from test where (name,val) = ('bb',20)
intersect
select pid from test where (name,val) = ('cc',30);


-- works on all RDBMS
select a.pid
from   test a, test b, test c
where  (a.name,a.val) = ('aa',10) 
and    (b.name,b.val) = ('bb',20) 
and    (c.name,c.val) = ('cc',30) 
and    (a.pid = b.pid and b.pid = c.pid);

-- same as above. for JOIN purists
select a.pid
from   test a 
cross join test b
cross join test c
where  (a.name,a.val) = ('aa',10) 
and    (b.name,b.val) = ('bb',20) 
and    (c.name,c.val) = ('cc',30) 
and    (a.pid = b.pid and b.pid = c.pid);


Output:
| pid |
| --- |
| 2   |
| 1   |


| pid |
| --- |
| 1   |
| 2   |


| pid |
| --- |
| 1   |
| 2   |


| pid |
| --- |
| 1   |
| 2   |


| pid |
| --- |
| 1   |
| 2   |

All have same result, the first query's result is not ordered though.

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

Execution plan: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=78628f85cfb53bad6793491f8ab2bef1

Thursday, April 11, 2019

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/

Wednesday, March 27, 2019

For interface-free action props, use typeof

Instead of creating an interface for the actions that will be used by the component, just use typeof on actionCreators object. See line 54 and 63



A sample action creator (e.g., setColorTheme):

export const setColorTheme = (colorTheme: string): LoggedUserAction => ({
    type: LoggedUserActionType.LOGGED_USER__SET_COLOR_THEME,
    colorTheme
});

Tuesday, March 26, 2019

Without immer, with immer

Without immer, constructing immutables is error-prone:

increaseOrderQuantity(item) {
    const itemIndex = this.state.data.indexOf(item);

    this.setState(({data}) => ({
        data: [
            ...data.slice(0, itemIndex),
            {
                ...item,
                quantity: item.quantity + 1
            },
            ...data.slice(itemIndex + 1) // have made a mistake of forgetting to include + 1
        ]
    }));
}


With immer, less error-prone:

import produce from 'immer';

.
.
.


increaseOrderQuantity(item) {
    const itemIndex = this.state.data.indexOf(item);
    
    this.setState(produce(draft => {
        draft.data[itemIndex].quantity++;
    }));
}

Saturday, March 23, 2019

Don't use siloed mapDispatchToProps, use redux-thunk

This makes getLoggedUser functionality not accessible on all components but App component

export const incrementCounter = (n?: number): CounterAction => ({
    type: CounterActionType.COUNTER__INCREMENT,
    n
});

const mapDispatchToProps = (dispatch: Dispatch) => ({
    incrementCounter: (n?: number) => dispatch(incrementCounter(n)),
    getLoggedUser: async () => {
        const userRequest = await fetch('https://jsonplaceholder.typicode.com/users/1');

        const {status} = userRequest;

        if (!(status === 200 || status === 301)) {
            throw new Error('HTTP Status: ' + status);
        }

        const {username} = await userRequest.json() as IUserDto;

        await dispatch(setLoggedUser(username));

        await dispatch(setColorTheme('blue'));

    }
});

export default connect(mapStateToProps, mapDispatchToProps)(hot(App));


To make getLoggedUser functionality accessible from other components, instead of defining it in mapDispatchToProps, define getLoggedUser outside and make it return a thunk that accepts dispatch parameter. A function returned from a function is called a thunk. Include the thunk creator on action creators.


export const incrementCounter = (n?: number): CounterAction => ({
    type: CounterActionType.COUNTER__INCREMENT,
    n
});

export const getLoggedUser = () => async (dispatch: Dispatch): Promise<void> =>
{
    const userRequest = await fetch('https://jsonplaceholder.typicode.com/users/1');

    const {status} = userRequest;

    if (!(status === 200 || status === 301)) {
        throw new Error('HTTP Status: ' + status);
    }

    const {username} = await userRequest.json() as IUserDto;

    await dispatch(setLoggedUser(username));

    await dispatch(setColorTheme('blue'));

};


const actionCreators = {
    incrementCounter, 
    getLoggedUser
};

export default connect(mapStateToProps, actionCreators)(hot(App));


You'll receive the error below if you forgot to import and configure redux-thunk to your project:

Uncaught Error: Actions must be plain objects. Use custom middleware for async actions.


Here's an example configuration for redux-thunk:

import { applyMiddleware, compose, createStore, Store } from 'redux';

import { reducersRoot } from './reducers-root';

import { IAllState } from './all-state';

import ReduxThunk from 'redux-thunk';

export function configureStore(): Store<IAllState>
{
    const middlewares = applyMiddleware(ReduxThunk);

    const composeEnhancers = (window as any)['__REDUX_DEVTOOLS_EXTENSION_COMPOSE__'] || compose;

    const composed = composeEnhancers(middlewares);

    return createStore(reducersRoot(), composed);
}

Another benefit of not using mapDispatchToProps is you can just pass the action creator directly to the action creators object, no need for the code to call the dispatch by itself. Making the code simple.

const actionCreators = {
    incrementCounter, 
    getLoggedUser
};

If needed be, it can be customized how an action creator is called:

const actionCreators = {
    incrementCounter: (n?: number) => incrementCounter(n! * 42),
    getLoggedUser
};