How to use value from row derived column from a table in another query?

SELECT...INTO can be used to store the single row column result value from a query to be used in another query depending on that value.

"accounts" table

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| account_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| account_name | varchar(50) | NO   |     | NULL    |                |
| balance      | float       | NO   |     | NULL    |                |
| state        | varchar(50) | NO   |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

Sample data

+------------+--------------+---------+-------+
| account_id | account_name | balance | state |
+------------+--------------+---------+-------+
|          1 | Rico         |     500 | IL    |
|          2 | Mark         |     100 | NY    |
|          3 | Rita         |      50 | NY    |
+------------+--------------+---------+-------+

Lets say we want to get average balance of all accounts in the state of NY from the above table and then use that value in another query.

mysql> SELECT AVG(balance) INTO @avg_bal
    -> FROM accounts
    -> WHERE state = 'NY'
    -> GROUP BY state;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT CONCAT('Average balance in NY is ', @avg_bal) AS result;
+-----------------------------+
| result                      |
+-----------------------------+
| Average balance in NY is 75 |
+-----------------------------+
1 row in set (0.00 sec)

The above use case arises when we have 2 or more queries depending on each others' output inside a stored procedure.

Below is how the stored procedure would look:

DELIMITER $$

DROP PROCEDURE IF EXISTS get_ny_avg_bal$$

CREATE PROCEDURE get_ny_avg_bal()
BEGIN
    DECLARE avg_bal FLOAT;

    SELECT AVG(balance) INTO avg_bal
    FROM accounts
    WHERE state = 'NY'
    GROUP BY state;

    SELECT CONCAT('Average balance in NY is ', avg_bal) AS result;

END$$

DELIMITER ;

mysql> CALL get_ny_avg_bal();
+-----------------------------+
| result                      |
+-----------------------------+
| Average balance in NY is 75 |
+-----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Use of loops in MySQL stored procedures

Loops -> Repetitive tasks

3 types of loops in MySQL stored program:
- Simple loop using LOOP and END LOOP clauses
- WHILE and END WHILE clauses that runs while a condition is true
- REPEAT  and UNTIL clauses that runs until a condition is true

Termination of loop -> LEAVE statement.

I will demonstrate the use of loops the simple loop way.

Simple counter from 1 to 10:

Store below stored procedure in a file named my_loop.sql

DELIMITER $$

DROP PROCEDURE IF EXISTS my_loop$$

CREATE PROCEDURE my_loop()
BEGIN
    DECLARE counter INT DEFAULT 0;

    my_loop: LOOP
        SET counter = counter + 1;

        IF counter = 10 THEN
            LEAVE my_loop;
        END IF;
    END LOOP my_loop;

    SELECT CONCAT('Done counting up to ', counter) AS result;

END$$

DELIMITER ;

Execute the stored procedure:

mysql> SOURCE my_loop.sql

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL my_loop();

+------------------------+

| result                 |

+------------------------+

| Done counting up to 10 |

+------------------------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Explaining simply, we are defining a counter that starts with 0 and a LOOP labelled my_loop that increments from 0 onward and a condition inside to check for counter value of 10, when the loop terminates through the use of LEAVE clause. You can give a try to the other loop clauses :)

Using conditional expressions inside MySQL Stored Procedure

Sometimes there is need to use conditional expression inside stored procedure to control the flow of execution.
We can use IF or CASE statements for this.

Below is a stored procedure to check the performance of a student based on its score.
Store the below stored procedure in a file called get_performance.sql

DELIMITER $$

DROP PROCEDURE IF EXISTS get_performance$$

CREATE PROCEDURE get_performance
    (score NUMERIC(8, 2),
    OUT result VARCHAR(11))

BEGIN
    IF (score >= 90) THEN
        SET result = 'OUTSTANDING';
    ELSEIF (score >= 70 AND score < 90) THEN
        SET result = 'AWESOME';
    ELSEIF (score >= 60 AND score < 70) THEN
        SET result = 'GOOD';
    ELSEIF (score >= 40 AND score < 60) THEN
    SET result = 'OK';
    ELSE
    SET result = 'FAIL';
    END IF;

END$$

DELIMITER ;

Execute the procedure:

mysql> SOURCE get_performance.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Execute the call:

mysql> CALL get_performance(67, @result);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @result;
+---------+
| @result |
+---------+
| GOOD    |
+---------+
1 row in set (0.00 sec)

Basically IF, ELSEIF check for conditions, ELSE matches any condition which has not been matched by any preceding condition and finally THEN executes the set of statements for the satisfied condition and breaks out of the conditional expression. Similarly, we can use CASE for switching to statement sections based on equality comparison with a set of unique values.

How to use and get output arguments in stored procedure using OUT parameter mode?

It is sometimes required to get the result of a stored procedure in a variable which can be used later or to output the result.

To do so, we can use the "OUT" parameter mode while defining stored procedures.

In the below section, we will be writing a stored procedure to get the square root of a number returned in an output variable provided by us.

Stored Procedure Definition:

Store the below stored procedure in a file named my_sqrt.sql and save it.

DELIMITER $$

DROP PROCEDURE IF EXISTS my_sqrt$$

CREATE PROCEDURE my_sqrt(inp_number INT, OUT op_number FLOAT)
BEGIN
    SET op_number=SQRT(inp_number);
END$$

DELIMITER ;

Execute Stored Procedure:

Open your mysql client in terminal and run the above procedure using the below command:

mysql> SOURCE my_sqrt.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Then call the stored procedure to get square root of 100 in an output variable called @sqrt:

Get square root of 100:

mysql> CALL my_sqrt(100, @sqrt);
Query OK, 0 rows affected (0.00 sec)

Now the output of the sqrt operation is stored in @sqrt variable which can be used by us as we desire.

mysql> SELECT @sqrt;
+--------+
| @sqrt  |
+--------+
|   10   |
+--------+
1 row in set (0.00 sec)

That's all it takes :)

A few tips while you start or work your node project

There are few good practices one can follow while starting a node app / project.

1. One of them, I learned today, is that it's a good practice to have a package.json file which keeps info related to app and dependencies. You can do so using the below command:

$ npm init

It will ask for a couple of questions. You can click enter to skip them and use the default values instead.

After you are done, you can see a "package.json" file created in your current directory. You might want to edit package.json file and add "private" property to the json list as below to prevent the json being published to npm.

"private" : false

2. The above step will basically help us and others, who are added to work on the project, setup the project initially to get a brief idea about the app and its dependencies and also they can install the related dependencies listed in it using the below command:

$ npm install

It helps in installing the version used in the app instead of the latest. That's important since there might be api changes to the package and it might not be working with the current app setup.

3. Another practice one can follow is to save the version to the package.json when you install any package. You can do so by using the "--save" option with "npm install" command. For example:

$ npm install express --save

The above command will save "express" as a dependency with the installed version mentioned.

This has helped me a lot and is quite interesting :)

Review Board - The Perfect Review System For You

If you have a big team, and manually asking or attending to peer code reviews in your team is a hassle, then "Review Board" is there for you.

Site

"Review Board makes code review easy
Saving time, money, and sanity"


"Yes, Review Board is absolutely free for commercial use."

Faqs

It's a python utility and easy to install in ubuntu and any linux system through easy_install
http://www.reviewboard.org/docs/manual/dev/admin/installation/linux/

You can setup it in your local and play around with your team in a local setup :)

Here's a demo

Some features:
1. Admin panel to manage reviews, users, groups and other customization
2. Review groups to whom review request can be sent.
3. User authorization preventing outside access.
4. Sending review requests to different members and group users easily.
5. Easy issue and tracking of comments on diffs by reviewers and reviewees.
6. Every diff review, comment sent through mail to required users and admin making it trackable and easy to follow.
7. And many others features.

Excerpts from site:
- Easily track your team's review requests
- Not your typical diff viewer
- Upload and review all kinds of files
- Comment on anything
- Keep track of everything that needs fixing
- All your history, in one place
- Birds-eye view of the server
- Helpful command-line tools
- Extensible

Get rocking :)

How to structure and design a relational database to support you data storage needs?

Well, every now and then, when we began to start a new project or app, which has some data storage requirement, we have a deep intriguing thought as to how best represent the data structure so as to support a variety of needs including but not limited to (ACID rules):

1. Normalization
2. Reliability
3. Consistency
4. And many others

Below, I provide a set of steps which you can follow to arrive at a data model that correctly suites your requirements.

Steps:

1. Identify the project or app requirements / specifications and business rules which tell you what your app will be able to do when it is ready.
2. From these business rules, identify possible objects for each business rule and mark them in a paper using rectangular sections like authors, posts etc.
3. Once you have recognized the entities and attributes, it's time to normalize the data structure and define the tables and columns.
4. After this, we now have to recognize the relationships between tables that is whether it is a one to one, one to many or many to many relationship like authors and posts have a one to many relationship.
5. Finally, we have to re-access the data model for any possible inconsistencies and refine data that is not yet normalized like any relationship with many to many relationship being moved to different tables with the help of a junction table.

So, at the end of the fifth step, we have a data model with which we can go ahead and build our database.

Now,
1. It satisfies the normalization rules
2. It represents relationships correctly between various entities taking into account the business rules of the application
3. It prevents redundancy
4. And satisfies the ACID rules

Maybe a simple theoretical explanation is not enough for we practical nerds. In a probable future post, I will explain the steps with a practical example. Stay tuned in. :)

My experience with node and mongodb course "M101JS: MongoDB for Node.js Developers" (Third Week)

Well, currently I am into the third week of mongodb node course "M101JS: MongoDB for Node.js Developers" and I am pretty enjoying it.

Lots of personal learning into node and mongodb.

The third week subject of "Patterns, Case Studies & Tradeoffs" is really interesting.

Here is a list of topics, I learned about:
- Mongodb rich documents concept.
- Mongodb schema use cases.
- Mongodb one:one, one:many, many:many use cases.
- How to select schema based on the usage like whether you want max performance
  or it may be a tradeoff.

One important point, I learned during the course is:
"While relational databases usually go for the normalised 3rd form so that data usage is agnostic to application, but mongodb schema arrangement is very closely related to application usage and varies accordingly."

Use of DECLARE and SET in MySQL stored program

DELIMITER $$                    -- Change the delimiter

DROP PROCEDURE IF EXISTS demo$$ -- Remove if previously exists
CREATE PROCEDURE demo()         -- Create Stored Procedure
BEGIN                           -- Start of definition
    DECLARE my_int INT;         -- Declare variables
    DECLARE my_big_int BIGINT;
    DECLARE my_text TEXT;

    DECLARE my_name VARCHAR(30) -- Declare variables with 
        DEFAULT 'Rookie Dev';   -- default value set

    SET my_int = 20;            -- Set variables to values
    SET my_big_int = POWER(my_int, 50);

END$$                           -- End of definition
DELIMITER ;                     -- Reset the delimiter

The self-executing anonymous function

There are three parts to it:
1. It's anonymous and does not have a name.
2. It self executes.
3. It's a function.

Well, a self-executing anonymous function has it's own uses.

First case, where you need to execute some functionality without it effecting other surrounding functionality nor the surrounding environment effecting it. It basically creates a local private execution area bereft of the outside environment.

Example:

var name  = 'The Master Dev';

(function () {
    var name = 'The Rookie Dev';
    console.log(name); // Outputs "The Rookie Dev"
}) ();

console.log(name); // Outputs "The Master Dev"

Second case, where lets say you have a lot of code inside a <script> block and you want to test breakpoints and exit and not execute the remaining code, then in this case surrounding the entire code in a anonymous function and using return as an exit point helps.

Example:

<script>

var line = 'Hello World';

// some code
// here

// Need to debug here and exit

// and here

</script>

With self-executing anonymous function:

<script>

(function () {
    var line = 'Hello World';

    // some code
    // here

    return;

    // and here
});

</script>

PHP NG

Well, there has been a lot of awesome things going around PHP recently with work going on in a separate branch related to PHP NG.

It's all about making php ready for JIT Compilation capabilities after which there will be work going on to make the integration.

Hopefully, it will be making the web world more faster for us. Thanks to the people out there for their passionate and hard work.

You can see the work in progress at phpng.

The original article can be seen at phpng-archive.

An intro to Offline.js

Ever wondered how many times we have been browsing a site and we do not see any activity. After many wonders and one refresh, we find that our internet connection is down.

Well, wouldn't it be nice if someone notifies us. Do not worry, because Offline.js comes to your rescue to provide your users with that unique experience.

References:

Site: Offline.js
Docs: Offline.js doc
Github page: Offline.js Git
Demo page: Offline.js Demo

Some of the things it manages to do are:

1. It basically notifies the users that they have lost internet connectivity.
2. It captures ajax requests made during the offline state and remakes them when the connection is back.
3. It requires zero configuration.
4. It provides many themes to improve the user experience and to blend with your site.
5. It's minified and comes at 3kb size.

How to execute mysql query from a file in your mysql client terminal?

Being a terminal fan myself, I usually find myself running queries in the mysql client instead of a UI interface as it is much faster. You get to see the results instantaneously.

One thing which is pretty tedious is editing a big query again after once running it as the whole multi-line formatted query now appears on a single line, thus reducing its readability.

But no problems, you can edit your query from a file and run the file from your mysql client terminal as many times as you want with as many edits.

To do so, follow the below steps:

1. Open your terminal and cd into the folder you want to store our sample mysql file. Then save your query in a sample file called my_query.sql

$ cd /path/to/folder
$ vim my_query.sql

Save a sample query like:

SELECT * FROM employees
WHERE type LIKE 'hard_working'
OR type LIKE 'cool'
OR type LIKE 'crazy';

2. Then open mysql client from terminal and execute the query file using SOURCE command.

$ mysql -u username -p

> SOURCE my_query.sql;
We get the results here.

3. Edit your source file as many times and execute the SOURCE command again from the terminal. Be sure you are able to help SOURCE command in locating the source file location. Since we have opened the mysql client in the same folder which contains the source file, so there was no need for any path. If path varies, please do provide it.

> SOURCE /path/to/my_query_file.sql

How to install Phalcon PHP framework in Ubuntu linux?

Well, we have all heard about the fastest php framework out there. But how do we install it in a Ubuntu Linux machine.

Default process for any linux setup.

Steps:

1. First, we need  a few packages previously installed. To install them, issue the distro specific command in your linux terminal.

For Ubuntu:

sudo apt-get install php5-dev php5-mysql gcc libpcre3-dev

For Fedora:

sudo yum install php-devel php-mysqlnd gcc libtool

For RHEL:

sudo yum install php-devel php-mysql gcc libtool

For Suse:

yast2 -i php5-pear php5-devel php5-mysql gcc

Basically, here we are installing the dev tools we require to compile and setup the Phalcon extension.

2. Get the Phalcon build using git

git clone --depth=1 git://github.com/phalcon/cphalcon.git

3. CD into the folder and issue the install command.

cd cphalcon/build
sudo ./install

4. Now, lets add the extension to our php.ini file. At the end of the file, add the below line

extension=phalcon.so

5. Restart your apache server.

sudo service apache restart

6. Check your phpinfo and you will find the Phalcon extension installed. We are good to go now.

Note: You can also install Phalcon as a debian repo which has been offered by FortRabbit at http://debrepo.frbit.com/

How to install Composer ?

Open your terminal, go to the preferred project directory / folder and type the following command to install Composer.

# curl -sS https://getcomposer.org/installer | php

A new file called "composer.phar" will be downloaded in the directory.

Then type

# php composer.phar

to check if Composer has been installed, which lists all available commands

Composer Site
Docs