Beware the Salesforce QueryOptions parameter

When I learned that CopyForce was using up almost 1GB of memory copying some SalesForce instances, I had to understand why.  Three hours later.....the default batch size return from a SalesForce API query is 1000 rows.  Since CopyForce grabs all columns in a table, the amount of memory required can get huge. The solution:

• Set the "QueryOptions" parameter in the binding header.

The next revision of CopyForce will choose values for this parameter that cause memory usage to stay below 200mb (app and data). Naturally, CopyForce will also include a new flag to control the number of rows in a batch at runtime.

It looks like CopyForceOracle will be ready to post on Monday. It is passing of the standard CopyForce unit tests and copied my large test database with flying colors.

Cranking with CopyForceOracle

I had a few hours free last weekend and took a stab a CopyForceOracle (copy all or part of a SalesForce instance to Oracle). The status is pretty good:

• 1.0 code has been checked into SVN
• No problems seen when copying a 4GB Salesforce instance (all tables) to Oracle 10g/XE. The Salesforce instance has ~9000 account, 45000 contacts, 70000 assets, etc.
• Application seems to need about 500mb to run (e.g. using . -Xm500m). This is a mystery I will look into. It is more memory usage than I would expect. I am flushing to Oracle every 2MB from Salesforce.

Volunteers are need to try this out on their own production SalesForce instances.

Time for a workout at the YMCA.

CopyForceOracle or Something else?

I have been thinking about creating a version of CopyForce for Oracle (CopyForceOracle) using one of Oracle's type 4 drivers. Since I do not have a near term need, I am looking for 4-5 sites to help me test it. If a few people step up, I will put a version together. I have a few years of Oracle/JDBC practice and think creating a version that passes the CopyForce unit tests should take no longer than a week (part time).

Volunteers willing to lend a testing hand please drop me a note at gsmthfarmer@gmail.com.

Downloads of the various sqlforce tools are holding steady at 10-20 per week. Feedback is fairly sparse (not surprising since I rarely post comments on a tool that works). If you are using one of the tools, drop me a note --  good or bad feedback is welcome.

I Just Saved a Lot of Time....

A number of Salesforce price updates were entered by hand (100s) and the "UseStandardPrice" flag was set to true. It should have been set to false. The data was fixed in a couple of minutes with SQLForce.

UPDATE PriceBookEntry SET useStandardPrice=false WHERE useStandardPrice=true AND pricebook2.isStandard=false

One line of SQLForce instead an afternoon of Apex batch...I love it.

Lovely Salesforce (for Non-Admin Users)

When is a SOQL SELECT not a SELECT? When it references a table whose selection syntax restrictions depend on who is selecting.  I was running a complete CopyForce on a development database this morning to validate an upgrade to Eclipse/Helios. This time I ran the CopyForce using a non-admin user. I discovered that two tables (NewsFeed and EntitySubscription) will not accept the same SELECT syntax when referenced by a non-admin. Mine is not to wonder why.  Anyway, problem fixed and new distributions have been posted.

The CopyForce feedback I am seeing indicates that most people are taking the default options and trying to copy entire Salesforce instances.  No problem...this will work. For production purposes we usually restrict what is copied because quite a few of the tables (like EntitySubscription) are not important for us to backup anyway.

Here is an interesting use case: We wanted Salesforce pricebooks duplicated in our accounting system. CopyForce can do this on one line:

• java -jar CopyForceSqlServer.jar -salesforce production -schema -include Pricebook2,Product2 -sqlserver "...."

This will copy just Pricebook2 and Product2 into the target SQL Server database. Support we wanted to also copy all of the tables referenced by Pricebook2 and Product2. CopyForce does not have a command line switch for this case -- you have to use a CopyForce configuration file (see the -config switch). More on this in a later post.

A Busy SQLForce week in Flyover Land

There was a lot of SQLForce activity this week. New distros were published that fix all reported problems.

The jython module saved me a quite a bit of work today. Ages ago someone create a free form string column on Account that was supposed to hold the beginning month of an accounts fiscal year. Naturally, people had entered the data in a variety of forms. I wanted to changed the type of a picklist and convert the existing data to consistent month names. How long did this take? About 10 minutes.

1. Find all unique ways that people had expressed the data.
   
   SELECT DISTINCT FiscalYearEnd__c FROM Account WHERE FiscalYearEnd!=null
   
2. Write a tiny bit of Jython that maps all of the "expressed" ways to normal ways.
   
   myMap = { "Jan":["1/1", "January", "janary", "01-01", ], "Feb":[...] }
   
3. Write a tiny bit of Jython that creates SQL statements that look like:
   
   UPDATE Account SET FiscalYearEnd__c="Jan" WHERE FiscalYearEnd__c="1/1"
   
4. Run each SQL command (in the Jython).
   
   session.runCommands( "UPDATE Account ..." )
   

Try doing steps 1 and 3 using base Apex tools. There is no way I could finish the job in 10 minutes.

Time to pick up some pizza. Have a great weekend.

Turkey for the next 5 Days -)

This week I made major upgrades to all SQLForce documentation and launched the a project for the core java bindings (JavaForce) used in SQLForce. Download has been very good for the last upgrade. My hope is that the upgraded documentation will make it much easier for people to get started. I was surprise at the quality of a lot of the documentation -- so much for an author proofing his own writing.

All of the kids (6) have been home for Thanksgiving. Large families are truly a blast -- even more so as the children get older. The early stage were fun but I would rather not go back to "two in diapers."

Enough "computing" for a break week. It's time to spend some quality time with Mary.

CopyForce for SQL Server and H2 posted

Finally....I posted the first release of CopyForce for SQL Server and H2. This tools copies a Salesforce instance to a relational database. Copy all or a subset of the tables/data. Perform incremental updates.

CopyForce came about because of several business problems.

1. I needed a way to backup salesforce (just in case a malicious person or a fat-fingered person destroyed and recycled our data).
2. Often I need to scan an analyze larger chunks of data than the Salesforce governs like me to do. Though my sqlforce (http://code.google.com/p/sqlforce/) project gets around the govern problems, I often need the speed of a local RDBMS.

The first CopyForce (http://code.google.com/p/copyforce/) comes in two flavors.

• Microsoft SQL Server
• H2 (http://h2database.org). H2 is a brilliant small footprint ANSI/SQL database.

Hopefully the salesforce network can handle the volume. CopyForce grabs data (lots of it) as fast as a line will allow.

Up late "Copying Salesforce" to my box.

For a few months I have been working occasionally on a utility that will create a local database (mySQL, JavaDB, or H2) that matches the schema and data in any Salesforce instance. Winter'11 finally seems to have given me the stability that I needed.

The tool, CopyForce, is based on the same libraries we use everyday (via jython) to perform routine DBA work on Salesforce (when Apex is just too hard). The base project, SQlForce, can be found at SQLForce

CopyForce is fairly simple to use:

• Enter a command line like java -jar CopyForceH2.jar -connect sfdcCredentials -h2connect myNewH2DatabaseName
  
• Wait while CopyForce creates the database, the schema, and populates the new database.
  

My motivation in creating CopyForce was a desire for an open-source was to create a snapshot of salesforce for backup purposes.

If it sounds interesting drop me a line.

Two weeks with the bride.

After two weeks of with my bride (for 26 years), it's time to get back to work.

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;-)

Moving Salesforce Attachments Around

I had an interesting problem (from Portugal) early this morning. A series of attachments had been attached to the wrong account. We wanted to move the attachments to a new account -- NOT BY HAND.
It would be nice is Salesforce supported something like:

• UPDATE Attachment SET parentId='new' WHERE parentId='old'
  

Unfortunately Salesforce does not support updating of the parentId field.

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.

Select Records Instead of Arrays

Earlier versions of the SQLForce Jython module support SELECT statements like:

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.

Multi-line Value Handling with the SQLForce Jython Module

An engineering student, Caleb Smith -- MST (the old Univ of MO, Rolla) found an interesting problem this week where the SQLForce jython module failed. He was searching for errors in the Salesforce Contact table where the complete address was entered in MailingStreet (rather than using the MailingCity, MailingState, etc. fields).

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.

Is There Junk in My Salesforce Pricebooks?

I had an interesting problem today: Find products in my Salesforce pricebooks that we have never sold.. In my case I want to delete these products from Salesforce (that's another post).

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.

Jython and Salesforce

A few weeks ago I launched a open source version of a Jython module (SQLForce) for working with Salesforce (the base functions are in a jar). Here's a problem solved earlier this week.

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.