Wednesday, August 28, 2013

SQL : CASE statements

Using conditions in SQL statements is really useful. Specially when we need to compare 1 value based on conditions with multiple columns.

For example I have 1 table called tasks. Where I need to check the given date parameter should be greater than or equal to multiple columns based on the current tasks category.

I can easily do that by code. Can be ROR or PHP. But if we can get the data directly from database it gets really fast for any language.

In this situation CASE statements are really useful.

In this example I need to compare parameter date to start_date column if category is 'appointment' and to end_date if category is 'todo'.
SELECT * FROM tasks 
WHERE '2013-09-02' >=
CASE 
  WHEN tasks.category='appointment' THEN (date(start_date))
  WHEN tasks.category='todo' THEN (date(end_date))
END

Monday, August 26, 2013

Rails/ PHP : Ubuntu : Add autostart on system boot

There is never a thought of starting an application if working in PHP.
But for one of the project we were using node.js and if the PC gets restarted we have to manually start the node.js.

I have done this before for my local rails application previously(http://stefanwienert.net) and applied the same for this issue.

First added .conf file
sudo gedit /etc/init/run_nodejs.conf
This will open default editor with new file called run_nodejs.conf
Now add following text to the file
description "Run Node JS"

start on (net-device-up and local-filesystems)
stop on runlevel [016]
chdir /path_to_nodejs

setuid user_name  # set to the user, which should run the app
exec sudo -u user_name  "/path_to_nodejs/nodejs.sh"

respawn
## give up if I respawn 3 times in 60 seconds...
respawn limit 3 60
Now create 1 .sh file named nodejs.sh at location /path_to_nodejs.

and in nodejs.sh add following commands, to start node js services.

Also give permissions to the file as chmod +x nodejs.sh to make is executable.
and then just check with (.conf file name for following command)
sudo start run_nodejs
You can also restart and stop the service.

As I mentioned I have done this for rails application.
But had 1 issue. By default I have 1.9.3 ruby version for other projects and for only local project I am using 2.0.0.

In demoapp.sh I have added following code
#!/bin/bash
PATH=$PATH:/home/supriya/.rvm/bin
source "/home/supriya/.rvm/scripts/rvm"
source ".rvmrc" 

rvm use 2.0.0
rails s # -p port_u_want -e env_u_want
So with these commands after starting my pc I get my local demoapp project always running with 2.0.0 and no worries about the default gemset.
And while working on project I can run log with if any error checking
sudo tail -f /var/log/upstart/demoapp.log