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...

My 5 minute Python data generator

I needed to do some database benchmarks on a 10GB dataset. The problem: I did not have the dataset in hand, and I needed to give an estimate to a customer before signing off a contract. No problemo: enter python and in 5 minutes I had flexible script to create me a dataset with table structure very similar to what the client had:

import csv
import random
import string

class SomeEntity( list ):
    titles = ( 'attr1', 'attr2' ) # ... for all columns
    def _init_( self ):
        self.append( random.randrange( 100, 10000 ) )
        self.append( ''.join(random.choice(string.ascii_lowercase + string.digits) for x in range (1000)) )
        # ... for all columns

myData = [ SomeEntity() for i in range(10000) ]
aFile= open( 'tmp.csv', 'wb' )
dest= csv.writer( aFile )
dest.writerow( SomeEntity.titles )   
dest.writerows( myData )

Getting started with Twitter Bootstrap for Ruby on Rails and Heroku

Woah..one hell of a title for a humble blog.

Recently I decided to give a try to using twitter bootstrap on my rails projects which I normally push to Heroku. It was not an easy feit - unfortunately due to bugs in bootstrap gems. In this article I will let you know what worked for me without breaking a sweat. (Special thanks to http://railsapps.github.io/twitter-bootstrap-rails.html).

Let's start by creating a new rails project:

$ rails new bootstrap-demo -T -d mysql

Add the bootstrap-sass gem in your Gemfile - make sure it is not in an assets group, but in the parent:

gem 'bootstrap-sass'

Install the bundle:

$ bundle install

Include bootstrap javascript file by adding the following in your app/assets/javascripts/application.js:

//= require jquery
//= require jquery_ujs
//= require bootstrap
//= require_tree .

Rename the file app/assets/stylesheets/application.css file to app/assets/stylesheets/application.css.scss for best practice. Import twitter bootstrap css by adding the following code inside app/assets/stylesheets/bootstrap_and_overrides.css.scss:

@import "bootstrap";
body { padding-top: 60px; }
@import "bootstrap-responsive";

You are now ready to use twitter bootstrap css framework! Paste the following code inside app/assets/stylesheets/application.css.scss to make a cool gray background for all your pages:

.content {
  background-color: #eee;
  padding: 20px;
  margin: 0 -20px; /* negative indent the amount of the padding to maintain the grid system */
  -webkit-border-radius: 0 0 6px 6px;
  -moz-border-radius: 0 0 6px 6px;
  border-radius: 0 0 6px 6px;
  -webkit-box-shadow: 0 1px 2px rgba(0,0,0,.15);
  -moz-box-shadow: 0 1px 2px rgba(0,0,0,.15);
  box-shadow: 0 1px 2px rgba(0,0,0,.15);

Ready for more action? Let's add a navigation bar, responsive layout and beautiful links. Replace the contents of the file app/views/layouts/application.html.erb with this:

<!doctype html>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title><%= content_for?(:title) ? yield(:title) : "Myapp" %></title>
    <meta name="description" content="">
    <meta name="author" content="">
    <%= stylesheet_link_tag "application", :media => "all" %>
    <%= javascript_include_tag "application" %>
    <%= csrf_meta_tags %>
    <%= yield(:head) %>
    <header class="navbar navbar-fixed-top">
      <nav class="navbar-inner">
        <div class="container">
          <%= render 'layouts/navigation' %>
    <div id="main" role="main">
      <div class="container">
        <div class="content">
           <div class="row">
            <div class="span12">
              <%= render 'layouts/messages' %>
              <%= yield %>
      </div> <!--! end of .container -->
    </div> <!--! end of #main -->

Great! Now run webrick or your favorite rails server and enjoy the eyecandy. This is just the tip of the iceberg. You can do much more in twitter bootstrap. Now that you're happy, you're ready to deploy to heroku:

git init
git add .
git commit -m "First commit"
heroku create
git push heroku master

Heroku supports this framework if installed and configured the way I explained. I did not have much luck with other gems such as twitter-bootstrap-rails.

Tethering speeds with Three on Android

Just purchased myself the £25 monthly contract plan with Three which apart from 2000 minutes in calls and 5000 text messages, it allows me to perform tethering on my android device. To be fair with you guys I was in a quick need of an Internet connection so this sounded like great value for money... I was told that BT sells broadband internet for roughly the same price (but of course no calls/texts/etc) and would be bound by an 18 month contract - a royal ripoff if you may ask.

Would love to ask any geek who may be reading this whether he has any experience with trying to tether on non-allowed (ONE) plans? And I am really asking for the sake of an educational research and not to be a digital cowboy.

I have also noticed that tethering speeds really depend on the location of the mobile device - in my case, placing the Android near the window managed to triple the connection speed as can be seen in the screenshot above. I am quite happy with the results especially since this is an all you can eat data plan!

Update: I noticed that the default apn three.co.uk provided erratic results and a couple of minutes after publishing this article I was getting disconnected from the internet, or suffering from slow connections or even getting blocked on certain websites! I changed the APN to 3internet instead of three.co.uk and now I have better speed, more reliable connection and most of all, uncensored browsing:

Also I am pretty sure that once I charge the battery I get better results! Additionally if you set data connection to WCDMA (rather than auto which allows android to flip between GSM and UMTS) results in better ping and lower latencies (evident from resolving a website address):

Remember that for 3G, the maximum theoretical speed is 5Mb/s so I am getting pretty close! If you still have problems, before contacting your mobile phone operator try to remove wi-fi encryption and set it as an open network:

Additionally I am trying to look for an app for my Android ICS (4.0) which hides the SSID when I configure the mobile as a personal hotspot... Let me know if you are aware of one!

Github primer - pushing code remotely

Someone has recently asked me to give him a no-brainer procedure to push a git repository onto github. So here it is for everyone's convenience...

Assuming you have created a repo on your Github account, let's say you want to commit all your Ruby projects:

james@ebony ~/dev/ruby $ git init
Initialized empty Git repository in /home/james/dev/ruby/.git/
james@ebony ~/dev/ruby $ touch README
james@ebony ~/dev/ruby $ git add README
james@ebony ~/dev/ruby $ git add TaxCalculator/TaxCalculator.rb
james@ebony ~/dev/ruby $ git commit -m 'first commit'
[master (root-commit) a97909f] first commit
 1 file changed, 103 insertions(+)
 create mode 100644 README
 create mode 100755 TaxCalculator/TaxCalculator.rb
james@ebony ~/dev/ruby $ git remote add origin https://github.com/jamesattard/ruby.git
james@ebony ~/dev/ruby $ git push origin master
Username for 'https://github.com': jamesattard
Password for 'https://jamesattard@github.com': 
To https://github.com/jamesattard/ruby.git
 ! [rejected]        master -> master (non-fast-forward)
error: failed to push some refs to 'https://github.com/jamesattard/ruby.git'
To prevent you from losing history, non-fast-forward updates were rejected
Merge the remote changes (e.g. 'git pull') before pushing again.  See the
'Note about fast-forwards' section of 'git push --help' for details.
james@ebony ~/dev/ruby $ git pull origin master
warning: no common commits
remote: Counting objects: 3, done.
remote: Total 3 (delta 0), reused 0 (delta 0)
Unpacking objects: 100% (3/3), done.
From https://github.com/jamesattard/ruby
 * branch            master     -> FETCH_HEAD
Merge made by the 'recursive' strategy.
 README.md |    2 ++
 1 file changed, 2 insertions(+)
 create mode 100644 README.md
james@ebony ~/dev/ruby $ git push origin master
Username for 'https://github.com': jamesattard
Password for 'https://jamesattard@github.com': 
To https://github.com/jamesattard/ruby.git
   2de0c70..c96770d  master -> master
james@ebony ~/dev/ruby $ ls
README  README.md  TaxCalculator
james@ebony ~/dev/ruby $ git rm README.md
rm 'README.md'
james@ebony ~/dev/ruby $ ls
README  TaxCalculator
james@ebony ~/dev/ruby $ git commit -m 'remove old README.md'
[master 1cf431a] remove old README.md
 1 file changed, 2 deletions(-)
 delete mode 100644 README.md
james@ebony ~/dev/ruby $ git push origin master
Username for 'https://github.com': jamesattard
Password for 'https://jamesattard@github.com': 
To https://github.com/jamesattard/ruby.git
   c96770d..1cf431a  master -> master

Follow the above procedure, note some common errors and also what you need to do if you want to remove a file from the repo the correct way.

OOP in Ruby - UK Income Tax Calculator

Today I wanted to learn more about OOP and do some actual coding. I started with a procedural paradigm and then recoded to OOP and learn from it. I wrote a very simple program in Ruby which calculates income tax in UK (very original!! hehe). I decided to use three objects....but can you come up with something different?

#!/usr/bin/env ruby

class Employee

 attr_reader :salary, :type, :taxtotal

 def initialize( salary, type )
  @salary = salary
  @type = type

 def computetaxbands
  case type  # current tax rates are the same for single and married persons
  when 1
   bandA = 2790
   bandB = 32010
   bandC = 150000
   rateA = 0.1
   rateB = 0.2
   rateC = 0.4
   rateD = 0.5
  case salary
  when 0..bandA
   calctax( IncomeTaxBand.new( salary, rateA ) )
  when bandA+1..bandB
   calctax( IncomeTaxBand.new( salary, rateB ) )
  when bandB+1..bandC
   calctax( IncomeTaxBand.new( bandB, rateB ), IncomeTaxBand.new( salary - bandB, rateC ) )
   calctax( IncomeTaxBand.new( bandB, rateB ), IncomeTaxBand.new( bandC, rateC ), IncomeTaxBand.new( salary - bandC, rateD )  )

 def calctax( *taxbands ) #taxbands is an array of IncomeTaxBand objects
  @taxtotal = 0
  taxband_inst = taxbands
  taxbands.each { | taxband_inst | @taxtotal += taxband_inst.tax }

class IncomeTaxBand
 attr_reader :tax
 def initialize( amount, pct )
  @tax = amount * pct


class TaxViewer

 def inputvalid?(salary)
  if (salary.respond_to?(:match) && salary.match(/\d/))
 def annual(salary, tax)
  netannual = (salary - tax).round  
 def monthly(salary, tax)
  netmonthly = ((salary - tax) / 12).round
 def weekly(salary, tax)
  netweekly = ((salary - tax) / 52).round


puts "UK Income tax calculator (2013-2014):\n\n"
loop do

 viewer = TaxViewer.new
 print "Please enter your gross annual Salary: £"
 salary = gets.chomp

 if viewer.inputvalid? salary

  emp = Employee.new(salary.to_i, 1)

  printf "Total Tax: £%d\n", emp.taxtotal
  printf "Net Salary: £%d\n", viewer.annual(salary.to_i, emp.taxtotal)
  printf "Net Month: £%d\n", viewer.monthly(salary.to_i, emp.taxtotal)
  printf "Net Week: £%d\n", viewer.weekly(salary.to_i, emp.taxtotal) 
  print "\nPlease enter a valid number!\n\n"


You can pull this code from Github for easier readability - please note that I am using 3 Classes -
1. Employee - reflecting a real world object
2. IncomeTaxBand - another real world object
3. TaxViewer - since Ruby encourages MVC, any viewer methods can be placed as an object too

This post is by no means any guide or tutorial - but if you need to ask me any queries please do. What I wanted to illustrate is an example of how a simple program which I personally used to undertake in a procedural approach in Bash or Perl in the past, can be made as OOP. The advantages of this design are too many to mention although some people might think it looks overbloated or complex.

Malta song for Europe and the art of plagiarizing

If we Maltese are not good for anything, we are so good in plagiarizing music that we fool entire nations. Take a tried and tested song from the past, change the lyrics (unless you're Klinsmann and don't even bother!), and drop the key by a few tonal intervals to adjust the melody to your not well developed vocal range. Take a look at the following clips of the Maltese winners of the past two editions (2012, 2013) alongside with the 'what I think where plagiarized from' and judge by yourselves:

2012 Edition: 

Alexandra Stan - Get Back ASAP (Original): 

Kurt Calleja - This is the night (Maltese winner): 

2013 Edition: 

Train - Soul Sisters (Original): 

Gianluca Bezzina - Tomorrow (Maltese winner): 

And for the simpletons who say that 'they just share the same style'...there are 7 basic notes and 120 pitch sounds in music with an infinite number of intervallic possibilities to choose from.

Amazon wishlist

In case you haven't noticed, I recently added an Amazon book wishlist, hint: right sidebar! Living abroad does give you more time to invest in books, expand your knowledge and be a better person. So I would not mind some help from you folks reading my blog to cross them from the list ;)

What have I been upto?

I took a long vacation in Malta! Now I am back in Aberdeen for a while until I go back to work in Edinburgh.

Lately I did some self development exercises namely practicing more more and more music (checkout my new videos on my youtube channel), skiing in the Scottish mountains of Lecth, reading philosophy and religion, and studying Ruby.

With regards to the latter, consider me a new Ruby hacker and will be posting and sharing lots of new Ruby tricks and experiences with you :)

At the moment I am looking for a decent place to stay in Edinburgh city center. If you are reading this give me a shout on my email addy.