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 !

Friday, June 2, 2017

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










Sunday, April 23, 2017

Centos 7 Booting automatically into Graphical mode

Centos 7  installation end up into terminal mode start up ,if no INTERNET connectivity within VM instance.

running "dhclient" command resolved this problem for session.For permanent solution we need to check our Instance settings in Virtual Box.

In settings --> Network modify as follows

1)  Enable Network Adapter
2)  Select "Bridged Adapter" from "Attached to" dropdown
3)  select interface (eth0) from "Name" dropdown
4) Check "Cable Connected"  checkbox from Advanced Section
5) Promiscuous Mode to Deny (Default)

On host Linux machine run ifconfig and check active interface usually its "eth0".Save Changes & and restrat VM instance.
   Now from Command Prompt in Guest OS run

   ping google.co.in

to check if internet is accessible or not.

Now we have to install Gnome as follows

        yum groups install "GNOME Desktop"

we will check current target

    systemctl get-default

Output expected is "graphical.target", if not then it will first boot into terminal then we have to run manually "startx" command for graphical mode.

Lets set system to automatically boot into graphical mode so we have to set default display manager with gnome installed we are going for "gdm" (other choices are kdn,xdm).

    sudo systemctl enable gdm.service
    sudo systemctl set-default graphical.target

We can revert back to terminal mode using following

    sudo systemctl disable gdm.service
    sudo systemctl set-default multi-user.target


In older system usually there was setting of runlevel called "initd" ,which is currently replaced by "systemd".

Both this system used for initialization of system during boot process . also used for starting & stopping services usually refered as daemon.

The key file in this regard was  "/etc/inittab" ,which need to be edited suitablly.

    For logging into terminal initdefault need to set as 3    

        id:3:initdefault:

    For logging into graphical mode initdefault need to set as 5

        id:5:initdefault:

    Initd Equivalent for "systemctl get-default" command is
   
        cat /etc/inittab | grep initdefault

    Relating Systemd & Initd

           Init RunLevel    Systemd Target         Run Level Description

        0     poweroff.target            The halt runlevel - this is the runlevel at which the system shuts down. For obvious reasons it is
                           unlikely you would want this as your default runlevel.

        1     rescue.target           Single runlevel. This causes the system to start up in a single user mode under which only the root user can log in. In this
                                                   mode the system does not start any networking or X windowing, X or multi-user services. This run level is ideal for system
                                                   administrators to perform system maintenance or repair activities.

        2     multi-user.target       Boots the system into a multi-user mode with text based console login capability. This runlevel does not, however, start the
                                                   network.

        3     multi-user.target       Similar to runlevel 2 except that networking services are started. This is the most common runlevel for server based systems
                                                   that do not require any kind of graphical desktop environment.

        4     multi-user.target       Undefined runlevel. This runlevel can be configured to provide a custom boot state.

        5     graphical.target       Boots the system into a networked, multi-user state with X Window System capability. By default the graphical desktop
                                                   environment will start at the end of the boot process. This is the most common run level for desktop or workstation use.

        6     reboot.target               Reboots the system. Another runlevel that you are unlikely to want as your default.

        Please refer http://www.linuxtopia.org/HowToGuides/runlevel.html for more information on initd



    References :
          http://landoflinux.com/linux_runlevels_systemd.html
          http://www.linuxtopia.org/HowToGuides/runlevel.html
          http://www.linfo.org/runlevel_def.html
          https://www.cyberciti.biz/faq/howto-linux-renew-dhcp-client-ip-address/
       

Sunday, April 9, 2017

REDIS on Debian 9 Installation Notes


To install redis on Debian 9 we need to download source code then we need to compile it by unzipping then running make,make install & finally from util dir run "install_server.sh".

Please refer below link for  detail instructions.

https://www.digitalocean.com/community/tutorials/how-to-install-and-use-redis

Here are notes of installation.

Installation Details
Default Port:6379
Configuration:/etc/redis/6379.conf
Log:/var/log/redis_6379.log
Data Directory:/var/lib/redis/6379
EXEC PATH:/usr/local/bin/redis-server

redis service start /stop/status/restart:

sudo service redis_6379 start
sudo service redis_6379 stop
sudo service redis_6379 restart
sudo service redis_6379 status

How to start command line interface for redis:
redis-cli

Adding redis to auto start:
sudo update-rc.d redis_6379 defaults

Examples of same articles (https://www.digitalocean.com/community/tutorials/how-to-install-and-use-redis) are are elaborated further below in case of cryptic information while others are summarized.

Checking if Redis is Up:

SET users:GeorgeWashington "job: President, born:1732, dislikes: cherry trees"
GET users:GeorgeWashington

Expiry of key:
SET classified:information "Secret Stuff"
EXPIRE classified:information 45

this will key expire in 45 second while below command will give time remaining in second for expiry for given key (here classified)

TTL classified:information

Increment/Decrement of Key (numeric)
SET population 6
INCRBY population 10
INCR population
GET population

on same line 'of "INCR" ,"DECR" will decrement value by 1

DECR population

and "DECRBY" is similar counterpart of "INCRBY"

DECRBY population 5

Running commnds in a transaction(all or nothing):

following command will create transaction of commands and will execute them in a order.

MULTI
SET population 6
INCRBY population 10
INCR population
EXEC

Removed Blocked transaction:
   if redis interrupted while processing transaction then it goes into blocking stage to get out of this mess below command is used

edis-check-aof

-------------------------------------------------------------
                    # SET
-------------------------------------------------------------
SET (Multiple values against single key):
   SET is type of collection which is order agnostic,SADD adds element to set

SADD colors red
SADD colors orange
SADD colors yellow
SADD colors orange

   as orange is already added set will not take duplicate value,below command will give list of all members of set "colors"

SMEMBERS colors

   while below command will give random number from our set "colors"

SRANDMEMBER colors

Intersection of two sets:
   creating one more set with some common elements

SADD colors_new orange
SADD colors_new green
SADD colors_new yellow

   below command will give intersection of our two sets colors & colors_new

SINTER colors colors_new

Checking if a set has a given member:
   Below command will check if green is member of colors_new set

SISMEMBER colors_new green

-------------------------------------------------------------------
                  # SORTED SET
--------------------------------------------------------------------
we are adding key along with a number association that will be used for ordering.

zadd countries 9 Tuvalu
zadd countries 10 India
zadd countries 11 Japan
zadd countries 7 USA
zadd countries 50 China
zadd countries 34 Tibet

   inside collection they get sorted based on number associated (low to high)

Low to high sorting:
     "withscores" differ from penultimate command it gives both value & associated number unlike without it.
zrange countries 0 -1
zrange countries 0 -1 withscores

High to low sorting:
ZREVRANGE countries 0 -1

Range:  
   below command will display only display element in array with index 0,1 & 2 when sorted from low to high

zrange countries 0  2 withscores

negative index can also be used with convention that -1 is the last element of the sorted set,-2 the penultimate element.
e.g. wrt collection below

 7 -->"USA"
 9 --> "Tuvalu"
10 --> "India"
11 --> "Japan"
34 --> "Tibet"
50 --> "China"

 china is of index -1,tibet is of -2 ,japan -3 so on

Removing a element from sorted set:
below command will remove "USA" from sorted set

ZREM countries  "USA"

this can be confirmed by seeing members of sorted set

zrange countries 0 -1

--------------------------------------------------------------
                   # LIST
--------------------------------------------------------------
"RPUSH" command add a value to the end of a list

rpush lunch.provider alice
rpush lunch.provider bob
rpush lunch.provider carol
rpush lunch.provider don
rpush lunch.provider emily

but "lpush" adds a value to start of list

lpush lunch.provider zoe

to view all elements in list below command is used

lrange lunch.provider 0 -1

to remove last element of list RPOP used

RPOP lunch.provider

to remove first element of list LPOP used

LPOP lunch.provider

Trim list to specific size:
Based on counting 1st element of index as 0,till index become 2 all element will be kept while all others will be deleted

LTRIM lunch.provider  0  2

this list structure can be used to emulate both stack as well as queue.


--------------------------------------------------------------------
                               # HASH KEY
--------------------------------------------------------------------

Hash is a key value pair based collection it is efficient in storing large amount of data

hmset user:1 username jsmith password 4bAc0s email jsmith@gmail.com

this will store multiple key values namely

username -->jsmith
password --> 4bAc0s
        email  --->jsmith@gmail.com

against single collection name "user:1"

can be viewed as

hgetall user:1

to retreive only value of single key against collection name say email in above scenario we can issue

hget user:1  email

if we want retrive values of multiple keys against collection name below command is used ,it will  retrive value for contact & email field

HMGET user:1   email contact


to add one more key value in this collection name

HSET user:1 contact 02334556

verify now by looking at whole collection as below

hgetall user:1


Sunday, January 29, 2017

postgresql Cluster

List All Cluster:

    root@debian:~# pg_lsclusters
    Ver Cluster Port Status Owner    Data directory               Log file
    9.6 main    5432 online postgres /var/lib/postgresql/9.6/main     /var/log/postgresql/postgresql-9.6-main.log

Find Status of given instance:


    root@debian:~# pg_ctlcluster 9.6 main  status
    pg_ctl: server is running (PID: 6062)
    /usr/lib/postgresql/9.6/bin/postgres "-D" "/var/lib/postgresql/9.6/main" "-c" "config_file=/etc/postgresql/9.6/main/postgresql.conf"

Kill Process:

    sudo kill -SIGHUP  6062

Change Status of instance – start/stop:

    pg_ctlcluster 9.6 main  stop
    pg_ctlcluster 9.6 main  start
    pg_ctlcluster 9.6 main  restart

View Cluster Configuration:

    root@debian:~# pg_ctlcluster 9.6 main  status
    pg_ctl: server is running (PID: 6062)
    /usr/lib/postgresql/9.6/bin/postgres "-D" "/var/lib/postgresql/9.6/main"     "-c" "config_file=/etc/postgresql/9.6/main/postgresql.conf"
    root@debian:~# ^C
    root@debian:~# pg_conftool 9.6 main show all
    cluster_name = '9.6/main'
    data_directory = '/var/lib/postgresql/9.6/main'
    datestyle = 'iso, mdy'
    default_text_search_config = pg_catalog.english
    dynamic_shared_memory_type = posix
    external_pid_file = '/var/run/postgresql/9.6-main.pid'
    hba_file = '/etc/postgresql/9.6/main/pg_hba.conf'
    ident_file = '/etc/postgresql/9.6/main/pg_ident.conf'
    lc_messages = en_IN
    lc_monetary = en_IN
    lc_numeric = en_IN
    lc_time = en_IN
    log_line_prefix = '%t [%p-%l] %q%u@%d '
    log_timezone = localtime
    max_connections = 100
    port = 5432
    shared_buffers = 128MB
    ssl = true
    ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
    ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
    stats_temp_directory = '/var/run/postgresql/9.6-main.pg_stat_tmp'
    timezone = localtime
    unix_socket_directories = '/var/run/postgresql'

Create New Cluster:
     pg_createcluster 9.6 new_cluster

DROP Cluster:
     pg_dropcluster --stop 9.6 main

Update Cluster:
     pg_upgradecluster 9.5 main

    it upgrades to next version

Reload Configuration:
      pg_ctlcluster 9.6 main reload

Restart Postgres Service:
    root@debian:~# systemctl reload postgresql
CHECK if Postgres is Listensing :
    root@debian:~# pg_isready
    /var/run/postgresql:5432 - accepting connections

Sunday, January 15, 2017

Listing all files recursively using bluebird promise

var Promise = require("bluebird");
var fs = Promise.promisifyAll(require('fs'));
var Path = require('path');
var dir = '/home/test_user/pictures/sample';


var ext = ['.txt', '.jpg'];
let files = [];

function walk(dir, ext) {
    return fs.readdirAsync(dir).map(function (fileName) {
        fileName = Path.join(dir, fileName);
        var extname = Path.extname(fileName);

        if ((ext.indexOf(extname) > -1) || (extname == '')) {
            return fs.statAsync(fileName)
                .then(function (stats) {
                    return stats.isDirectory() ? walk(fileName, ext) : fileName;
                })
        } else {
            return false
        }
    }).reduce(function (a, b) {
        if (b == false) {
            return a;
        } else {
            return a.concat(b);
        }
    }, []);
}

var res = walk(dir, ext).then(function (v) {
    console.log(JSON.stringify(v));
});

Saturday, January 14, 2017

Listing files & directories in given folder using node.js synchroniously

Here is my code for getting list of directories & sub-directories & files within each of it for given path.

var fs = require("fs");
var path = require('path');
var op = new Object();

var basePath = '/home/test_user/pictures/samples/';

function myCb(opt){
    if(op[opt.path] == null){
        op[opt.path] ={
            files:null,
            directories:null
        }
        op[opt.path].files = opt.files
        op[opt.path].directories = opt.dirs
    }

    for(var j=0;j< opt.dirs.length;j++){
        walkSync(opt.dirs[j],myCb);
    }
}

function walkSync(searchPath,cb) {
    var pathList = fs.readdirSync(searchPath);

    var fileList=[];
    var dirList=[];

    for (var i = 0; i < pathList.length; i++) {
        var walkPath = path.join(searchPath, pathList[i]);
        var stat = fs.statSync(walkPath);
        if (stat.isFile()) {
            fileList.push(walkPath);
        }
        else if (stat.isDirectory()) {
            dirList.push(walkPath);
        }
    }

    var output ={
        "path":searchPath,
        "files":fileList,
        "dirs":dirList
    }

    cb(output);
}

walkSync(basePath,myCb);
console.log(JSON.stringify(op));

output is an array of object of type

"source_folder_path" : 


{
    "/home/test_user/pictures/samples/": {
        "files": [],
        "directories": [
            "/home/test_user/pictures/samples/1",
            "/home/test_user/pictures/samples/2"
        ]
    },
    "/home/test_user/pictures/samples/1": {
        "files": [
            "/home/test_user/pictures/samples/1/IMG_1942067a4_235124.jpg",
            "/home/test_user/pictures/samples/1/IMG_1942067a4_235138.jpg",
            "/home/test_user/pictures/samples/1/IMG_1942067a7_213012.jpg",
            "/home/test_user/pictures/samples/1/IMG_1942067a7_213020.jpg",
            "/home/test_user/pictures/samples/1/IMG_1942067a7_213022.jpg",
            "/home/test_user/pictures/samples/1/IMG_1942067a7_213034.jpg"
                ],
        "directories": []
    },
    "/home/test_user/pictures/samples/2": {
        "files": [
                      "/home/test_user/pictures/samples/2/IMG_20151218_232553.jpg"
        ],
        "directories": [
            "/home/test_user/pictures/samples/2/4"
        ]
    },
    "/home/test_user/pictures/samples/2/4": {
        "files": [
            "/home/test_user/pictures/samples/2/4/IMG_1942067a8_000623.jpg",
            "/home/test_user/pictures/samples/2/4/IMG_1942067a8_000626.jpg",
            "/home/test_user/pictures/samples/2/4/IMG_19420123_200014.jpg",
            "/home/test_user/pictures/samples/2/4/IMG_19420123_200018.jpg",
            "/home/test_user/pictures/samples/2/4/IMG_19420206_267a022.jpg",
            "/home/test_user/pictures/samples/2/4/IMG_19420214_215517.jpg",
            "/home/test_user/pictures/samples/2/4/IMG_19420214_215650.jpg"
        ],
        "directories": []
    }
}