Generating Salesforce Entity Relationship Diagrams

---

Update – theses changes have been accepted as patches in the main Force-metadata-driver project

Please disregard the attached jars, below, unless you need separate dependencies. The force-metadata-driver is now implemented as a “jumbo” jar, with driver code and dependencies bundled in a single jar. Please refer to:

force-metadata-jdbc-driver

---

Usually you use a tool such as the popular commercial tool, ERWin to generate ERDs. Examples of Open Source ERD modeling tools are Open ModelSphere and SchemaSpy – these tools normally connect to the database via a JDBC driver.

Although Salesforce is built on database technology, Salesforce objects (entities) do not have a direct correspondence to physical tables in the underlying database. Rather all standard and custom objects (as well as other entity types) are represented by metadata in a so-called “Universal Data Dictionary”. Some of this metadata is exposed via their MetaData API (via SOAP web services) Luckily, there is a special JDBC driver out on Google Code, called Force MetaData JDBC Driver, which translates Salesforce MetaData Web Service API calls to JDBC metadata; the project owner is Keith Clarke.

Force MetaData JDBC Driver

In this post, I will cover using the Force MetaData JDBC Driver with the SchemaSpy ERD generator tool, which is command-line driven. I actually found the documentation a bit ambiguous, which I hope to not make worse here… ;)

The first issue I found was that on this page: Usage – it claims that release 1.3 and higher uses the “generated-sforce-partner-NN.jar” and release 1.2 and lower uses “a generated Axis Enterprise web services API jar”. I downloaded this force-metadata-jdbc-driver-1.4.jar, which I discovered still had dependencies on the Axis jars.

As this point, I wanted to see what was happening in the debugger, so I checked the code out of source-control, per this page: force-metadata-jdbc-driver-read-only This was Revision 61.

I made a number of changes:

• removed all Axis and javax.xml.rpc related code and references
• changed the web service implementation to use sfdc-wsc
• Added missing JDBC-3 API methods as stubbed-out dummy implementations (only the metadata is provided by this driver)
• enhanced the JDBC URL format to accept the user/password as URL properties, per section 9.4.1 of the JDBC spec (i.e. Standard Data Source Properties) e.g. jdbc:claimvantage:force;user=someuser;password=somepw
• Added static initialization to register the driver with the DriverManager, per section 9.3 of the JDBC spec. (to solve “no suitable driver found” error)

I didn’t make these changes because the driver didn’t work – it obviously works great, however, I wanted to reduce the dependencies required (get rid of Axis and it’s dependencies) and possibly make it more performant (WSC uses pull-parsing, which may perform better). Also, I wanted to make the driver more JDBC compliant so that it might work in greater variety of tools (other then just SchemaSpy) – for example, it did not work with Tigris ERmodeller because that app relies on the driver to register itself with the DriverManager (as specified in section 9.3 of the JDBC spec)

As a result of these changes, you will now NOT need these jars: axis.jar, commons-discovery.jar, commons-logging.jar, jaxrpc.jar, saaj.jar and wsdl4j.jar. Instead, use partner-20.jar and wsc-20.jar, which are provided here. (and, of course, force-metadata-jdbc-driver-1.4.cw.jar) In fact, the classes in partner.jar were generated by the WSC jar, via the command java -cp wsc.jar com.sforce.ws.tools.wsdlc partner.wsdl partner.jar – so actually the only real dependency is the wsc.jar file.

SchemaSpy Setup and Usage

Download schemaSpy_5.0.0.jar

I think some of the usage ambiguity with the force-metadata-driver has to do with it’s usage with SchemaSpy and how the user/password are passed in. There are a number of mechanisms to pass in the user/password:

1. Via template place-holders. SchemaSpy utilizes a per-database properties file to specify database-specific configuration, e.g. driver, connectionSpec, etc. You can have “place holder” tokens that appear in the properties file, in a property value string, embedded between “<” and “>”. For example, the original force.properties file from http://code.google.com/p/force-metadata-jdbc-driver/ has a templatized connectionSpec=jdbc:claimvantage:force:<un>:<pw> These tokens are then used as command-line arguments of SchemaSpy, which passes them through to the driver, e.g. “-un someuser” “-pw somepwd_”
2. Command-line arguments, “-u” and “-p”
3. JDBC DataSource properties. These properties can be appended as a semicolon-delimited list of name-value pairs. e.g. jdbc:claimvantage:force;user=someuser;password=somepasswd

My preference would be to pass the user/password via the SchmemaSpy “-u” and “-p” args, because if you pass the user/password via any other mechanism, you must “lie” to SchemaSpy and specify the “-sso” argument, indicating “Single Sign-On” is in-effect, which it is not. Also, using the “-u” and “-p” args will keep things more uniform across database vendor setups.

In this case, your force.properties file would look like:

description=Force.com
driver=com.claimvantage.force.jdbc.ForceMetaDataDriver
connectionSpec=jdbc:claimvantage:force

…and your invocation of SchemaSpy would look like this shell script:

#!/bin/sh
JARDIR=/opt/jlib

CP=$JARDIR/partner-20.jar
CP=$CP:$JARDIR/wsc-20.jar
CP=$CP:$JARDIR/schemaSpy_5.0.0.jar
CP=$CP:$JARDIR/force-metadata-jdbc-driver-1.4.cw.jar
java -cp $CP net.sourceforge.schemaspy.Main \
  -t force -hq -norows -charset utf8 -u yourSFuser -p yourSFpwSectoken \
  -o out -connprops "custom\\=true\;standard\\=true"

The script works as follows. JARDIR is a directory on your system where you keep all your jar files, assuming they’re all together in one directory. The “-t force” means “database type ‘force’” (Salesforce) and will cause SchemaSpy to look for a file called “force.properties” (see above). “-u” passes your Salesforce username and the “-p” passes your Salesforce password (with security token appended, if required). “-o” specifies the output directory of the generated diagrams and HTML. “-connprops” specifies vendor-specific JDBC connection properties – here I am overriding the default ‘standard=false’ to ‘standard=true’ so I can see all the entities (objects) not just custom objects. Don’t forget that you need Graphviz installed on your system and the “dot” command on your system PATH.

This script was tested on MacOSX-10.6 and is known to work, it will most likely work on Linux as well. If you are on Windows, you can try this script, which I have yet to actually test:

setlocal
set JARDIR=c:\opt\jlib

set CP=%JARDIR%\partner-20.jar
set CP=%CP;%JARDIR%\wsc-20.jar
set CP=%CP;%JARDIR%\schemaSpy_5.0.0.jar
set CP=%CP;%JARDIR%\force-metadata-jdbc-driver-1.4.cw.jar
java -cp %CP% net.sourceforge.schemaspy.Main ^
  -t force -hq  -norows -charset utf8 -u yourSFuser -p yourSFpwSectoken ^
  -o out -connprops "custom\=true;standard\=true"
endlocal

Using Ant

To use Ant, you can try this version of the script, which is parameterized by the properties file, “user.properties”, which you create and configure for your specific environment. A sample follows:

ss.jardir=schemaspy
sf.username=yourSFusername
sf.password=yourSFpassword
sf.sectoken=yourSFsecurityToken
sf.docdir=docs

Here is the build.xml:

<project default="gendocs" basedir=".">
  <property file="user.properties"/>
  <target name="gendocs">
    <echo message="Generating SchemaSpy documentation (requires Graphviz to be installed to produce diagrams)"/>
    <delete dir="${sf.docdir}" failonerror="false"/>
    <java classname="net.sourceforge.schemaspy.Main" 
               fork="true" failonerror="true">
      <arg line="-t force -hq -norows -charset utf8"/>
      <arg line="-u ${sf.username} -p ${sf.password}${sf.sectoken} " />
      <arg line="-connprops custom\=true;standard\=true" />
      <arg line="-o ${sf.docdir}" />
      <arg line="-font Arial"/>
      <arg line="-fontsize 8"/>
      <arg line='-desc "Salesforce Org Schema"'/>
      <classpath>
        <fileset dir="${ss.jardir}" includes="*.jar"/>
      </classpath>
    </java>
  </target>
</project>

To run this, put user.properties, build.xml in the same directory. Next, create the jar file directory and copy the following jars there: schemaSpy_5.0.0.jar, wsc-20.jar, partner-20.jar, force-metadata-jdbc-driver-1.4.cw.jar and copy the force.properties file into this directory also. Edit the user.properties file and set the property, ss.jardir to the directory where you copied (or linked) the jar files and force.properties. Also in the user.properties file, configure your username, password, security token and output directory.

Update – theses changes have been accepted as patches in the main Force-metadata-driver project

Please disregard the attached jars, below, unless you need separate dependencies. The force-metadata-driver is now implemented as a “jumbo” jar, with driver code and dependencies bundled in a single jar. Please refer to:

http://code.google.com/p/force-metadata-jdbc-driver/wiki/UsageForV2

JAR Files

N.B. – the generated partner.jar here is NOT the same as other partner.jar files found elsewhere, which may have been generated with Axis rather then WSC, and they are NOT compatible.

If you want to generate your own partner.jar, follow these steps:

1. Setup => Develop => API => Generate Partner WSDL
2. java -cp wsc.jar com.sforce.ws.tools.wsdlc partner.wsdl partner.jar

Description	classes	source
Force.com Web Service Connector (WSC) 1.5 class compat.	wsc-20-1.5.jar	wsc-20-1.5-sources.jar
Force.com Generated Partner API 1.5 class compat.	partner-20-1.5.jar	partner-20-1.5-sources.jar
Force MetaData JDBC Driver - with numerous changes and enhancements so it can work with other tools. 1.5 class compat.	force-metadata-jdbc-driver-1.4.cw.jar	force-metadata-jdbc-driver-1.4.cw-sources.jar

Update – the JARS have been updated, again.

I made numerous changes to the Force.com MetaData Driver, including:

• Add missing metadata columns to getTables, getColumns, getIndexes, etc.
• Implement column index getters on ResultSet
• Re-sequence column order of the above metadata queries so column index getters on ResultSet return correct values
• Implement getTypes, with correct Salesforce field type -to- java.sql.Types mapping

As a result of fixing and extending the driver, I can now use Open ModelSphere to reverse-engineer custom and standard object schema of Salesforce: