Musing, tips, tricks, on programming and farming. I am a technologist (CIO/CTO) at a pharmaceutical research company who also loves to grow fruits/nuts/berries/grapes/etc. Cyberspace plus dirt -- a great combination!
Tuesday, September 7, 2010
Two weeks with the bride.
I have a two part problem:
* I want to take snapshots of my Salesforce on demand. My primary need is to backup the data.
* I do not want another database/application infrastructure to maintain.
Using the libraries from SQLForce, I have taken a stab at a the problem (called CopyForce) that introspects any SalesForce instance and creates a local database that matches the Salesforce schema/data. So far the only database back-end I have used is H2 (a good alternative to Apache/Derby).
Any takers for beta testing? The source is open at http://code.google.com/p/sqlforce/
Back to my inbox...only 89 message to go;-)
Tuesday, May 11, 2010
Moving Salesforce Attachments Around
It would be nice is Salesforce supported something like:
- UPDATE Attachment SET parentId='new' WHERE parentId='old'
Fortunately, the jython SQLForce module made this problem easy to solve. Here is the complete script:
import SQLForce
def getAccountId( session, name ):
records = session.selectRecords( "SELECT id FROM account where name='" + name + "'")
if 1 == len(records):
return records[0].id
return None
if __name__ == '__main__':
session = SQLForce.Session("sandbox")
srcAccountId = getAccountId( session, "Biota Scientific Management Pty Ltd")
destAccountId = getAccountId( session, "Biota Structural Biology Laboratory")
print "Src Account", srcAccountId
print "Dest Account", destAccountId
for att in session.selectRecords( "SELECT id, body, contentType, isPrivate, name FROM attachment WHERE parentId='" + srcAccountId + "'"):
session.insert( "attachment", ["body", "contentType", "isPrivate", "name", "parentId"],
[[att.body, att.contentType, att.isPrivate, att.name, destAccountId]])
session.delete( "attachment", [att.id] )
Yeah, I should have cached both the updates and deletes into a python array and made two calls to Salesforce instead of two per attachment. What can I say -- I was lazy.
Tuesday, March 16, 2010
Select Records Instead of Arrays
session = SQLForce.Session("sandbox")
for row in session.select( "SELECT id, FirstName, LastName FROM Contact LIMIT 3"):
print "First Name: " + row[1]
print "Last Name: " + row[2]
This works OK as long as each row is referenced in close proximity to the session.select() and not many columns are being selected.When selecting a lot of columns, I found this technique to be error prone...so I introduced a form of select, session.selectRecords() that returns jython classes where the class attributes are set to the name of each column. Example:
session = SQLForce.Session("sandbox")
for row in session.selectRecords( "SELECT id, name, account.name FROM Contact LIMIT 3"):
print "Name: " + row.name
print "Account: " + row.account.name
The only trick to to use the same column name referenced in the SELECT statement. Naturally, a session.selectRecords2(sql, callback) form is also supplied.That's it..a small change that makes code more readable.
Saturday, February 20, 2010
Multi-line Value Handling with the SQLForce Jython Module
His code was something like:
import SQLForce
session = SQLForce.Session("Sandbox")
for row in session.select( "SELECT id, MailingStreet FROM Contact WHERE MailingStreet!=null"):
if isStreetInvalid( row[1] ):
reportTheError( row[0], row[1] )
Looks simple enough -- but it failed because the jython code was assuming NO EMBEDDED NEW LINES in any of the returned columns. Multi-line addresses were processed as additional rows.
If run against SQLForce 1.0.3 (or above) Caleb's loop runs like he expected originally. Multi-line addresses are stored in row[1] as multi-line strings.
Underneath the Hood
Underneath the hood, the Jython Session.select() was changed to always replace the SELECT that occurs at the beginning of the SOQL with a SELECTX. SELECTX works just like the SELECT command except that the results are returned as simple XML. Jython parses the XML using SAX to build each row. Simple!
Thanks for the bug Caleb.
Tuesday, February 16, 2010
Is There Junk in My Salesforce Pricebooks?
Finding the products was easy. I wanted all products where:
- No Opportunity referenced the product.
- No Asset referenced the product
Here is the script:
import SQLForce
session = SQLForce.Session("sandbox")
for product in session.select("SELECT id,name, division__c FROM Product2 ORDER BY name asc"):
nOps = session.select("SELECT COUNT() FROM OpportunityLineItem WHERE PriceBookEntry.product2Id ='" + product[0]+ "'")[0][0]
nAssets = session.select("SELECT COUNT() FROM Asset WHERE product2Id='" + product[0]+ "'")[0][0]
pricebooks = []
for pb in session.select("SELECT DISTINCT Pricebook2Id FROM PricebookEntry WHERE Product2Id='" + product[0]+ "'"):
pricebooks.append(pb[0])
if "0"==nOps and "0"==nAssets:
notUsed = [product[0],product[2],product[1], nOps, nAssets, "\t".join(pricebooks) ]
print "\t".join(notUsed)
I put the results of the script into an Excel worksheet so marketing can look for mistakes. Later I will modify the script to delete the records I found.
Thursday, February 11, 2010
Jython and Salesforce
We just started using Salesforce forecasting subsystem and needed to initialize Opportunity.ForecastCategory based on two Opportunity custom fields. Hundreds of records had to be updated -- doing it by hand was not a good option.
The solution...the following short SQL Force script:
import SQLForce
session = SQLForce.Session("sandbox")
nUpdated = 0
##
## Case where ForecastCategory should be Best Case
##
for record in session.select("""SELECT id, forecastCategory FROM Opportunity
WHERE (forecastCategory='Commit' OR forecastCategory='Pipeline')
AND isClosed=false
AND include_in_estimate__c=false
AND include_in_upside__c=true
AND license_type__c!=null
AND closeDate > LAST_YEAR
"""):
nUpdated += 1
print nUpdated
session.runCommands("UPDATE Opportunity SET forecastCategoryName='Best Case' WHERE id='" + record[0] + "'" )
There were a couple of addition loops in the code for setting other Opportunity.ForecastCategory values. Looks useful? See SQLForce Project on code.google.
Friday, August 28, 2009
SalesForce Java Migration Tools for Lots of Records
- To save us a lot of time, we wrote a code generator (template based) for creating strongly typed access classes to any SObject.
- Connection pooling.
- Since I am a unit test coverage fanatic (I love Lemma (the ECLemma variant)) the code is we exercised.
- Optional Eclipse framework exposes the capabilities. We use this for one-off tasks because we can easily push solutions to end-users.
- In our migration effort, we transferred the contents of a 30GB SFDC into a 2nd SFDC database during a long Friday evening.