Get your $100 Google Ads Coupon

This may sound spammy or affiliate in nature, but it's not. In fact I am only writing this because I feel generous. Google Adwords are giving away a free $100 coupon if you spend $25 in ads. Just click here.

Exporting Salesforce Data

A quick tip for myself (as I might need it in the future) and for others searching how to export Salesforce data on a daily basis (example to feed a data mart)...just read on.

The best way is to use SOQL, a SQL like language adopted for Salesforce to query its objects. An even better way is to use a wrapper which combines the power of a scripting language like Python with SOQL.

In fact I use SQLForceForJython, an opensource tool which you can easily integrate with a Jython script. The process is very simple:

1. Download the most recent sqlforce.zip distribution.
2. Add sqlforce.jar to your java CLASSPATH
3. Import the SQLForce module

The following is a script I use to export the Contact table (to be precise, we refer to it as Object in Salesforce):

import SQLForce

session = SQLForce.Session()
session.connect( "PRODUCTION", "james@ttard.info", "password", "security-token" )

for rec in session.selectRecords("SELECT LastName, FirstName, MailingCountry FROM Contact"):
   print rec.LastName, rec.FirstName, rec.MailingCountry

You can filter by the date column and query by a timestamp as you would normally do in SQL to simulate a daily export...

Generate Google API Refresh and Access Tokens in PHP

I don't want to sound like Captain Obvious but here's the thing: Google API is great and flexible but the lack of documentation from Google makes me think that these guys assume that everyone out there is as smart as them. This is what happened the first time I put my hands on trying out to use OAuth2 for Google API to generate a refresh token for offline access. Luckily for you, humble reader of my humble blog, I am sharing my wisdom with you.

But first, a bit of theory. You are able to access private user information through Google API by means of an access token which expires after 3600s. So what happens when you want to retrieve information offline like what most web applications do? Here comes the refresh token. This guy does not expire and you are able to generate a new access token using REST.

All you need to make this happening is to download my script and put it in your PHP project root. If you are using a laptop on localhost, just point your browser to http://localhost/get_google_tokens.php. Authorize the app to access your personal data, and a token will be generated for you and printed on the screen. But before doing all that, read the section below as you need to modify the script to enter your own credentials.

How does the script work? First of all you need to go to Google API Console and create a new project. Let's say that you are interested in accessing the Google Drive API - from the services menu just enable Drive. Last but not least, go to API Access and create a client ID (Web application or desktop application, does not really matter). Copy the client ID and secret and paste them in the script. Some notes for the geek who want to understand more:

$access_type = "offline";

This line tells Google API that we want it to generate a refresh token.

$approval_prompt = "force";

This line asks Google to prompt us again for approval even if we have previously approved it. I use this in testing scenarios when I have run the script a number of times and end up with a blank refresh token.

$scope = "https://www.googleapis.com/auth/drive";

This is an important line of code. This defines the access scope of the application. So if our application is only interested in accessing the Drive API, put that in the scope. If you want to access more APIs, place the URIs separated by a space. Check this site for a full list of API scopes.

Finally note that the response we get from Google is JSON encoded and therefore needs to be decoded to extract the refresh and access tokens.

View Oracle sessions by CPU usage and status

As a database guy one of the most common questions I get asked is to display a list of Oracle processes sorted by CPU usage. This might not be entirely accurate but proves to be a bit useful when you do not have visibility of the operating system such as Amazon Oracle RDS. So whether looking which sessions consume the most CPU, or to get a list of all Oracle processes sorted by CPU usage, displaying machine they are coming from, their waiting time and whether they are active or not, run the following query:

SELECT se.username, ss.sid, ROUND (value/100) "CPU Usage", seconds_in_wait, machine, status
FROM v$session se, v$sesstat ss, v$statname st 
WHERE ss.statistic# = st.statistic# 
   AND name LIKE  '%CPU used by this session%' 
   AND se.sid = ss.SID  
   AND se.username IS NOT NULL 
  ORDER BY value DESC; 

And the result would be something like this:

Username SID  CPU Wait(s) Machine      Status
james    2841 81  937     AMAZONA-ABC2 INACTIVE
james    1436 68  78      machine1     INACTIVE
james    49   61  100     machine2     INACTIVE
james    47   45  3297    machine1     INACTIVE
james    48   36  4669    machine3     INACTIVE

Now you would ask me what sort of CPU gives you more than 100% total CPU Usage? A multicore one of course..

Sanitize Ruby strings for safe use in shells

This is one of those awkward titled articles, unless you purposely searched it on Google. As many of Ruby developers know, you can use any of the following methods to call shell scripts from Ruby:

1. system()
2. backticks `` or %x shortcut
3. Kernel.exec()

The problem comes when you are aware of information security abuses, such as shell injection which might exploit unsanitized input. Let me portray this with an example:

1.9.3-p392 :001 > system ("ls")
file1 file2 file3
 => true 

Let's try shell injection:

1.9.3-p392 :002 > system ("ls; rm *")
file1 file2 file3
 => true 
1.9.3-p392 :003 > system ("ls")
 => true 

Ouch! Imagine what would have happened with user input. This is dangerous indeed and wise Rubyists often don't recommend you using system() commands for user input facing situations. If you still can't live without it, Ruby has a library called Shellwords, and you can use it as follows:

1.9.3-p392 :004 > require 'shellwords'
 => true 
1.9.3-p392 :005 > a1 = %w{ls -l}
 => ["ls", "-l"] 
1.9.3-p392 :006 > system(a1.shelljoin)
total 0
-rw-r--r--  1 jamesattard  wheel  0 15 Apr 14:47 file1
-rw-r--r--  1 jamesattard  wheel  0 15 Apr 14:47 file2
-rw-r--r--  1 jamesattard  wheel  0 15 Apr 14:47 file3
 => true  

Now let's try to drop a bomb:

1.9.3-p392 :007 > a2 = %w{ls -l; rm *}
 => ["ls", "-l;", "rm", "*"] 
1.9.3-p392 :008 > system(a2.shelljoin)
ls: illegal option -- ;
usage: ls [-ABCFGHLOPRSTUWabcdefghiklmnopqrstuwx1] [file ...]
 => false 
1.9.3-p392 :009 > system("ls")
file1 file2 file3
 => true 

As you can see, Shellwords sanitized the string to make it safe for use in a shell.

How to block Facebook app invites from certain friends

Sometimes you feel tempted to block that particular friend who continuously spams your notification box with childish app invites! You don't need to go that road (possibly risking a friendship in real world). Just follow these simple steps to simply ignore any app invites from that friend:

1. Click on the Account Settings from your dashboard (upper right corner):

2. Select "Blocking" from the left hand column.

3. Next to the Block App Invites, type in the email address or name of your Facebook friend whom invites you want to block.

Note that you can use the same technique to block those annoying event invites as well.

Youtube bass covers

Posting some recent bass covers I did at the start of this year. Hopefully will resume later on this year...