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