Wednesday, June 28, 2017

Introduction to Postgrest

   Postgrest is third party REST API interface for quering postgres SQL server,which let us access data from postgres tables,views & function.Lets explore it further.

   First we will download postgrest(https://github.com/begriffs/postgrest/releases/tag/v0.3.1.0). For my debian machine I am using ubuntu version  of postgrest        (postgrest-0.3.1.0-ubuntu.tar.xz),Extract it and rename it to just "postgrest",You can preferably move it to "/opt/".

We can run it as follows from Linux Terminal:
    cd /opt
    ./postgrest 'postgres://mypgusername:mypassword@localhost:5432/mypgdb' -a mypgusername -p 2020

    Note: Here first argument is jdbc url to our postgres server."mypgusername" is database role  & 2020 is port on which Postgrest will run.
              role can be "anonymous".

We need some tables to check Postgrest functionality.Lets create below table

        create table t_review
        (
            review_id uuid,
            user_id uuid,
            product_id uuid,
            review_headline varchar,
            verbose_response text,
            review_date timestamp without time zone,
            status record_status
        )

        create table t_review_response
        (
            response_id uuid,
            review_id uuid,
            user_id uuid,
            vote smallint,
            vote_date timestamp without time zone,
            status record_status
        )


    Add some dummy data in both tables please keep in mind "review_id" in "t_review_response" should belong to "t_review"

    You may need to give access right to this table to user through which postgrest is running

        GRANT SELECT ON TABLE t_review TO mypgusername;
        GRANT SELECT,INSERT,UPDATE ON  playground.public.t_review_response  to mypgusername;

    As some columns are declared of type uuid,to generate new uuid in postgres we might need to add corresponding extension if not already installed.

        CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Now we can read data from any table using GET as follows

        API End Point :http://localhost:2020/t_review_response
        API Response:
            [
                {
                "response_id": "cc19414f-449d-447b-a1ae-d741f2f4e05d",
                "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
                "user_id": "9b7f5b2d-b35a-4a53-bd50-6b0a69784dad",
                "vote": 1,
                "vote_date": "2017-06-28T14:36:36.206623",
                "status": "active"
                },
                {
                "response_id": "e18905d4-5f34-4060-b7fc-fd919849e6ed",
                "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
                "user_id": "9dd246a6-cf01-4df5-b259-68ca153bdda9",
                "vote": -1,
                "vote_date": "2017-06-28T14:36:36.206623",
                "status": "active"
                }
              ]
Where Clause:
    a) Equality:

    API End Point :http://localhost:2020/t_review_response?vote=eq.-1&review_id=eq.8e03e047-7eea-4725-8e5d-5c3dded7c0b9&response_id=eq.75320cf9-e5ca-4844-a2ae-ae6a44466fa9
    API Response:
        [
           {
            "response_id": "75320cf9-e5ca-4844-a2ae-ae6a44466fa9",
            "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
            "user_id": "c7de1c3d-0172-44f2-ad92-88bb9eed1918",
            "vote": -1,
            "vote_date": "2017-06-28T14:36:36.206623",
            "status": "active"
           }
        ]
    b) In clause:
    API End Point :http://localhost:2020/t_review_response?response_id=in.cc19414f-449d-447b-a1ae-d741f2f4e05d,e18905d4-5f34-4060-b7fc-fd919849e6ed
    API Response:
        [
            {
            "response_id": "cc19414f-449d-447b-a1ae-d741f2f4e05d",
            "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
            "user_id": "9b7f5b2d-b35a-4a53-bd50-6b0a69784dad",
            "vote": 1,
            "vote_date": "2017-06-28T14:36:36.206623",
            "status": "active"
            },
            {
            "response_id": "e18905d4-5f34-4060-b7fc-fd919849e6ed",
            "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
            "user_id": "9dd246a6-cf01-4df5-b259-68ca153bdda9",
            "vote": -1,
            "vote_date": "2017-06-28T14:36:36.206623",
            "status": "active"
            }
        ]
     c) like Clause:
    API End Point :http://localhost:2020/t_review?review_headline=like.*battery*
    API Response:
        [
            {
            "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
            "user_id": "4ace0309-b355-403f-9777-f046c157a9b0",
            "product_id": "be4b2c18-3afb-41ec-9a6b-157be393eb03",
            "review_headline": "LG V20 battery backup",
            "verbose_response": "Here is my review",
            "review_date": "2017-06-28T14:53:46.974368",
            "status": "active"
            }
        ]

    Postgrest uses * instead of % in like queries unlike sql queries.

     d) not like  clause:
    API End Point : http://localhost:2020/t_review?review_headline=not.like.*battery*
    API Response:
        [
            {
            "review_id": "016b0891-5411-4f40-b0f9-c6a036f309f9",
            "user_id": "7b326e04-3f12-45a6-9ec2-1015491fccce",
            "product_id": "50514530-e1a7-4904-b4f4-4244d3579658",
            "review_headline": "LG V20 Speaker Sound Quality",
            "verbose_response": "Here is my review",
            "review_date": "2017-06-28T14:54:27.937735",
            "status": "active"
            }
        ]
     e) Not In clause:
    API End Point : http://localhost:2020/t_review_response?response_id=not.in.cc19414f-449d-447b-a1ae-d741f2f4e05d,e18905d4-5f34-4060-b7fc-fd919849e6ed
    API Response:
        [
            {
            "response_id": "d3f5841d-6d38-47a4-a60f-a9cf17653574",
            "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
            "user_id": "fb8cfa0d-896a-44aa-9530-7517b9e5259f",
            "vote": -1,
            "vote_date": "2017-06-28T14:36:36.206623",
            "status": "active"
            },
            {
            "response_id": "ab7d479b-36ae-4b03-b23a-f883939e11ec",
            "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
            "user_id": "40baf601-ab30-4f07-88f2-9b9f51d30c76",
            "vote": -1,
            "vote_date": "2017-06-28T14:36:36.206623",
            "status": "active"
            }
        ]


        Note :    documentation of postgrest list way to use other common operators like  "less than equality","greator than" etc.

Using Views in Postgrest:
         Postgrest can also RESTIFY views.Lets create a sample view like below
   

    CREATE VIEW vw_review_response AS
    SELECT
        t_review.review_headline,
        t_review_response.review_id,
        count(*) totalResponses,
        sum(t_review_response.vote) as votes ,
        sum(case when t_review_response.vote > 0 then 1 else 0 end) as totalUpVotes,
        sum(case when t_review_response.vote < 0 then 1 else 0 end) as totalDownVotes
    FROM
        t_review
        LEFT OUTER JOIN t_review_response on t_review.review_id = t_review_response.review_id   
    GROUP BY
        t_review_response.review_id,
        t_review.review_headline;

        GRANT SELECT ON TABLE vw_review_response TO mypgusername;

    API End Point:http://localhost:2020/vw_review_response
    API Response:
        [
            {
            "review_headline": "LG V20 battery backup",
            "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
            "totalresponses": 128,
            "votes": 2,
            "totalupvotes": 65,
            "totaldownvotes": 63
            },
            {
            "review_headline": "LG V20 Speaker Sound Quality",
            "review_id": "016b0891-5411-4f40-b0f9-c6a036f309f9",
            "totalresponses": 64,
            "votes": 128,
            "totalupvotes": 64,
            "totaldownvotes": 0
            }
        ]

Select Specific Columns:
    We can also select only specific columns from table.In below request we will read 3 columns only, namely review_headline,totalupvotes,totaldownvotes.

    API End Point:http://localhost:2020/vw_review_response?select=review_headline,totalupvotes,totaldownvotes
    API Response:
        [
            {
            "review_headline": "LG V20 battery backup",
            "totalupvotes": 65,
            "totaldownvotes": 63
            },
            {
            "review_headline": "LG V20 Speaker Sound Quality",
            "totalupvotes": 64,
            "totaldownvotes": 0
            }
        ]

Ordering Rows:
        we can order the result set using order param,Here our sort order is  totalupvotes ascending & totaldownvotes descending.
    API End Point:http://localhost:2020/vw_review_response?order=totalupvotes.asc,totaldownvotes.desc
    API Response:
        [
            {
            "review_headline": "LG V20 Speaker Sound Quality",
            "review_id": "016b0891-5411-4f40-b0f9-c6a036f309f9",
            "totalresponses": 64,
            "votes": 128,
            "totalupvotes": 64,
            "totaldownvotes": 0
            },
            {
            "review_headline": "LG V20 battery backup",
            "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
            "totalresponses": 128,
            "votes": 2,
            "totalupvotes": 65,
            "totaldownvotes": 63
            }
        ]

Limiting Numbers of rows in Result set:
    In Postgrest you can add header in http request to limit number of rows as follows

    API End point:http://localhost:2020/t_review_response
        Request Header:
             {
                Range : 0-4
             }
       API Response:
            [
                {
                "response_id": "cc19414f-449d-447b-a1ae-d741f2f4e05d",
                "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
                "user_id": "9b7f5b2d-b35a-4a53-bd50-6b0a69784dad",
                "vote": 1,
                "vote_date": "2017-06-28T14:36:36.206623",
                "status": "active"
                },
                {
                "response_id": "e18905d4-5f34-4060-b7fc-fd919849e6ed",
                "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
                "user_id": "9dd246a6-cf01-4df5-b259-68ca153bdda9",
                "vote": -1,
                "vote_date": "2017-06-28T14:36:36.206623",
                "status": "active"
                },
                {
                "response_id": "d3f5841d-6d38-47a4-a60f-a9cf17653574",
                "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
                "user_id": "fb8cfa0d-896a-44aa-9530-7517b9e5259f",
                "vote": -1,
                "vote_date": "2017-06-28T14:36:36.206623",
                "status": "active"
                },
                {
                "response_id": "ab7d479b-36ae-4b03-b23a-f883939e11ec",
                "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
                "user_id": "40baf601-ab30-4f07-88f2-9b9f51d30c76",
                "vote": -1,
                "vote_date": "2017-06-28T14:36:36.206623",
                "status": "active"
                },
                {
                "response_id": "b772deb7-5b5b-4db8-966a-d7ec8b14aceb",
                "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
                "user_id": "1900cbb5-6b1f-4cf4-b7d8-ecbc9bce3e42",
                "vote": -1,
                "vote_date": "2017-06-28T14:36:36.206623",
                "status": "active"
                }
            ]
   
    Note:The Range:0-4 gives five rows the counting begins at zero.

    To know total number of record along with effect of server side pagination. we need to have additional header
        {
            Prefer:count=exact
        }

    we are using CURL tool on linux terminal to test the functionality as below.

    Request: curl -i -H "Range:0-4" -H "Prefer:count=exact" http://localhost:2020/t_review_response
    Response:
        HTTP/1.1 206 Partial Content
        Transfer-Encoding: chunked
        Date: Wed, 28 Jun 2017 13:40:21 GMT
        Server: postgrest/0.3.1.0
        Content-Type: application/json
        Content-Range: 0-4/192
        Content-Location: /t_review_response

        [
           {
              "response_id":"cc19414f-449d-447b-a1ae-d741f2f4e05d",
              "review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
              "user_id":"9b7f5b2d-b35a-4a53-bd50-6b0a69784dad",
              "vote":1,
              "vote_date":"2017-06-28T14:36:36.206623",
              "status":"active"
           },
           {
              "response_id":"e18905d4-5f34-4060-b7fc-fd919849e6ed",
              "review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
              "user_id":"9dd246a6-cf01-4df5-b259-68ca153bdda9",
              "vote":-1,
              "vote_date":"2017-06-28T14:36:36.206623",
              "status":"active"
           },
           {
              "response_id":"d3f5841d-6d38-47a4-a60f-a9cf17653574",
              "review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
              "user_id":"fb8cfa0d-896a-44aa-9530-7517b9e5259f",
              "vote":-1,
              "vote_date":"2017-06-28T14:36:36.206623",
              "status":"active"
           },
           {
              "response_id":"ab7d479b-36ae-4b03-b23a-f883939e11ec",
              "review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
              "user_id":"40baf601-ab30-4f07-88f2-9b9f51d30c76",
              "vote":-1,
              "vote_date":"2017-06-28T14:36:36.206623",
              "status":"active"
           },
           {
              "response_id":"b772deb7-5b5b-4db8-966a-d7ec8b14aceb",
              "review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
              "user_id":"1900cbb5-6b1f-4cf4-b7d8-ecbc9bce3e42",
              "vote":-1,
              "vote_date":"2017-06-28T14:36:36.206623",
              "status":"active"
           }
        ]

     In response header we got "Content-Range" as "0-4/192" that says that there are in all 192 record out of which we are selecting 5 records from 0th till 4th.

    samething can be done using postman to see response headers beside body & cookie tabs.
   

Calling Stored Procedure:
To demonstrate how we can call stored procedure through postgrest we will create a sample Stored procedure as follows

    CREATE OR REPLACE FUNCTION fn_get_review_response(p_review_id uuid)
    RETURNS  TABLE (headline varchar, review_id uuid,totalresponses bigint,votes bigint,totalupvotes bigint,totaldownvotes bigint) 
    AS $$
    BEGIN
        RETURN QUERY
        SELECT
            t_review.review_headline,
            t_review_response.review_id,
            count(*) totalResponses,
            sum(t_review_response.vote) as votes ,
            sum(case when t_review_response.vote > 0 then 1 else 0 end) as totalUpVotes,
            sum(case when t_review_response.vote < 0 then 1 else 0 end) as totalDownVotes
        FROM
            t_review
            LEFT OUTER JOIN t_review_response on t_review.review_id = t_review_response.review_id   
        WHERE
           t_review_response.review_id = p_review_id
        GROUP BY
            t_review_response.review_id,
            t_review.review_headline;
    END
    $$ LANGUAGE plpgsql;

may need to grant access to user through which postgrest is being run

    GRANT EXECUTE ON  FUNCTION fn_get_review_response(uuid) TO mypgusername

Now we can call our stored procedure,stored procedures are exposed in postgrest under "/rpc".
You can use "Postman" extension to test POST API.

    API End Point  :
        http://localhost:2020/rpc/fn_get_review_response

    Header:
        Content-Type : application/json

    Payload:
        {
            "p_review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9"
        }

        Note: Here I observed that JSON key ,need to be put into quotes else end up in error.
       
    API Response:
        [
            {
            "totalupvotes": 65,
            "votes": 2,
            "headline": "LG V20 battery backup",
            "totalresponses": 128,
            "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
            "totaldownvotes": 63
            }
        ]

We need to explore built in security aspect of Postgrest further.




Saturday, June 3, 2017

How to Reset Mysql root password

Forgot mysql root password ? No worries here is how I reset mine on by debian 9 Box.Usually it boils down to starting service
in safe mode & updating password & then restrating it normally.

Here are the steps


1) First Stop mysql service

       sudo service mysql stop

2) Start mysql in safe mode

        sudo mysqld_safe –skip-grant-tables

3) In Another Terminal login as root mysql user no password needed

         mysql -u root

4) Update your root password using SQL Query for that first switch to mysql database which stores login and password.

          use mysql;

 5) Update password

         update user set password=PASSWORD(‘your_password_goes_here’) where where User='root';

6) Ask Mysql to reload previlages to make changes take effect

        flush privileges;

7) Now time to test if change:

we made are working fine first exit from mysql command prompt by issuing  'exit'.also close the mysql running in safe mode by going to corresponding terminal  & pressing  "control + c" until it stops.

8) Lets start mysql in normal way using service dameon

          sudo service mysql start

Once service is up try to login through command line use password you just assigned.
                  mysql -u root -p

    it works !