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.

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 !

Quick Notes OrientDb installation on Debian 9

1) Download OrientDb Community Edition for linux from official sites
           orientdb-community-2.2.21.tar.gz
2) Extract it
   tar -zxvf orientdb-community-2.2.21.tar.gz
3) Move to /opt dir
    mv ~/orientdb-community-2.2.21 /opt/orientdb
4) installation
    Inside  /opt/orientdb issue below command
       sudo bin/server.sh
   follow the installation instruction & note password set for administrative    
   user account “root”.
 4) create user & group for running  orientdb as service dameon
       sudo useradd -r orientdb -s /sbin/nologin.
5) give access rights to new user
     sudo chown -R orientdb:orientdb /opt/orientdb
6) configuration
      Inside /opt/orientdb/bin/server.sh 
      a) ORIENTDB memory options change below line
                       ORIENTDB_OPTS_MEMORY="-Xms2G -Xmx2G"
                       with ORIENTDB_OPTS_MEMORY="-Xms128m -Xmx256m"
7)   Inside /opt/orientdb/bin/orientdb.sh
    b) OrientDB installation directory set installation path & user
                       ORIENTDB_DIR="/opt/orientdb"
                   ORIENTDB_USER="orientdb"

8)  Orientdb comes with web based gui like phpmyadmin assign sufficient right to         
    its config file
    sudo chmod 640 /opt/orientdb/config/orientdb-server-config.xml
9)  Package come with service but you have to move it to respective os directory
     sudo cp /opt/orientdb/bin/orientdb.service /etc/systemd/system   
    Inside this newly copied service file new need to set our user & entrypoint for server
         Open /opt/orientdb/bin/orientdb.service and replace default values with  
    orientdb user & group & change entry point with location of server.sh as
    below
          [Service]
      User=orientdb
      Group=orientdb
      ExecStart=/opt/orientdb/bin/server.sh
10) Setup Dameon
            sudo systemctl daemon-reload
        sudo systemctl start orientdb
        sudo systemctl enable orientdb
        sudo systemctl status orientdb
11) Hit http://localhost:2480 to see oriendb studio a web based interface like phpmyadmin.
12) Orientdb  server uses ports 2424 for binary connections & 2480 for HTTP
    connections.you might need to open the 2480 port in firewall.
13) Commandline interface
            sudo /opt/orientdb/bin/console.sh
            connect remote:127.0.0.1 root yourpasword