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.