Metadata Mashup – Exporting MySQL Schemas to Force.com

Community-contribution.jpg

Editors Note: This article is an award-winning article for a Developer Content Challenge. Congratulations Cory! Learn why and how you can contribute too.

The Force.com Metadata API is a powerful tool to manipulate your configuration in Force.com programmatically. Using the API, you can create powerful tools to assist with creating data schemas and move data from legacy systems to Force.com.

Overview

Problem: I needed to move data from a legacy, on-site database Into Force.com. There were about a dozen tables in the schema, and each table had anywhere from 40 to 200 fields. To manually create every one of those tables with their columns as objects in Force.com would take a very long time. Longer than I had time for. Plus, it's mind numbing!

Solution: Force.com Metadata API to the rescue! The Force.com Metadata API can create custom objects and custom fields in Force.com.

To solve this problem, I created a simple Java Swing application that leveraged the Force.com Metadata API and JDBC drivers. I used the MySQL JDBC Driver to read the local databases table and load them into memory. After I read the MySQL tables into memory, I then used the Force.com Metadata API to create those tables as custom objects in Force.com. After the custom objects are created, I then make a second call to create the custom fields for all the objects. Before you dive into the application code, take a look at the simple UI shown below.

Metadata-mashup-1.png

Simple UI

A user simply enters his/her MySQL credentials on the left side of the screen and his/her Salesforce credentials on the right. The user clicks Read Local Schema to read the legacy database schema. Once the local database schema is loaded, the user can click the Upload Local Schema to Force.com. Once that button is clicked, the Force.com Metadata API will be invoked to log in, and then convert the loaded local schema objects into Force.com Metadata request objects. It will finish by sending the request to Force.com to create the objects and fields.

To see the application in action, check out this video.



Also, the full source code to the application is available on GitHub, so feel free to pull down the source as you follow along with the subsequent code dive.

The Code

Now that you have an idea on the general use case and application flow, it's time to dive into the code. For the purposes of this article, you are going to focus on the Force.com Metatadata API classes. The application code encapsulates all of the logic related to the Metadata API in the class ForceMetadataAPI. This class holds instance variables to maintain the session state so a user only has to login once. First, take a look at your instance variables.

public class ForceMetadataAPI 
{
	private ConnectorConfig connFig; //Session Object holds Logged In Info
	private EnterpriseConnection entConn;
	private MetadataConnection metaConn;
	private boolean loggedIn = false;
…

The first step in the invoking the Metadata API is to log in to Salesforce. To do this, you need pass along the UserID and Password to Salesforce, and save the session in memory. Luckily, the API makes this easy to do. You can use the ConnectorConfig class along with the MetadataConnection and EnterpriseConnection classes from the Force.com Web Service Connector (WSC) library. The public login method takes the user's credentials, performs the login, and stores the resulting connection objects in the ForceMetadataAPI class instance variables. Now take a look at the login steps.

	/*
	 * Method: login
	 * Purpose: This method will take in the Salesforce Login Credentials. If the user is currently logged in, you will not re-login, just return true.
	 *          If the user is not logged in, then you setup your Connections. The Connections objects are created by the Force.com Web Service Kit.
	 *          The WSC Jars can be found on Google Code at http://code.google.com/p/sfdc-wsc/
	*/
	public boolean login(String sfUser, String sfPass) throws Exception
	{
		try
		{
			if(!loggedIn) //If you arent logged in, then login
			{
				connFig = new ConnectorConfig();
				connFig.setUsername(sfUser);
				connFig.setPassword(sfPass);
				connFig.setAuthEndpoint("https://login.salesforce.com/services/Soap/c/22.0"); // Set the Login URL
		
				entConn = new EnterpriseConnection(connFig);
				LoginResult lr = entConn.login(sfUser,sfPass);
				
				connFig.setServiceEndpoint("https://na3-api.salesforce.com/services/Soap/m/23.0"); //Set the Service Endpoint. Endpoints need to be changed between Metadata API and Enterprise API
				metaConn = new MetadataConnection(connFig);
				loggedIn = true;
			}
		}catch(Exception e)
		{
			throw e;
		}
		return true;
	}

As you can see from the source, if the user is not currently logged in, you perform the login. The order of these method calls is important. You may wonder why you have an EnterpriseConnection if you are only dealing with the Metadata API? The login method is only available in the EnterpriseConnection class, so to actually login you have to use the Enterprise API. Once you are logged in, then you switch your service endpoint on the ConnectorConfig to point to the Metadata API. This is important because the Enterprise and Metadata API's have different endpoint URLs!

After invoking the login method, you have a connection to the Metadata API ready to go. You can then make CRUD calls to the API to create objects in Force.com. To perform the creation of the objects and fields, use the public uploadSchema method in the ForceMetedataAPI class. This method takes a username, password, and an ArrayList of Table objects from MySQL. The method calls the login method again because it's possible the user did not log in when clicking the button on the UI yet. If the user is already logged in, the login method will not login again, as you may remember from the previous discussion. After calling the login method, you then perform transformations on the local schema tables into MetadataRequest objects.

An important thing to note is that you can batch Metadata API requests and create multiple objects in one efficient call, but you cannot create different object types in the same call. For example, you cannot create custom objects and custom fields in the same API call. Instead, you need to make two calls — one call to create custom objects and a second call to create fields. Take a look.

	public ArrayList<TableSchema> uploadSchema(String sfUser, String sfPass, ArrayList<TableSchema> localTableSchema) throws Exception
	{
		login(sfUser, sfPass);
		connFig.setServiceEndpoint("https://na3-api.salesforce.com/services/Soap/m/23.0"); //Reset the Endpoint!
		System.out.println("Start Upload");
		
		ArrayList<Metadata> metadataRequest = new ArrayList();
		ArrayList<Metadata> metadataRequest2 = new ArrayList();
		for(TableSchema localTable : localTableSchema)
		{
			System.out.println("Table Name == " + localTable.getTableName());
			CustomObject cus = buildCustomObject(localTable);
			metadataRequest.add(cus);
			
			ArrayList<FieldDefinition> fields = localTable.getFields();
			for(FieldDefinition field : fields)
			{
				CustomField customField = buildCustomField(field,cus);
				metadataRequest2.add(customField);
			}
		}
		
		if(metadataRequest.size() > 0) //If you have Objects, make that request
		{
			sendMetadataRequest(metadataRequest);
		}
		if(metadataRequest2.size() > 0)// If you have Fields, make that request
		{
			sendMetadataRequest(metadataRequest2);
		}
		return localTableSchema;
	}

Notice above that you iterate over the list of TableSchema objects and create the Metadata API objects out of them. Once you have two lists of request objects, you make a call to a method named sendMetadataRequest that invokes the Metadata API.

Now take a look at how to create the MetdataRequest objects. First, you'll see how to create custom objects inside the buildCustomObject method. Then, you'll explore custom fields that the buildCustomField method creates. Finally, you will look at the sendMetadataRequest method, which sends the requests to Force.com for processing.

	/*
	 * Name: buildCustomObject
	 * Purpose: Create the CUstom Object based on the Schema information from MySQL
	 */
	private CustomObject buildCustomObject(TableSchema localTable)
	{
		CustomObject cus = new CustomObject();
		cus.setFullName(localTable.getTableName() + "__c");
		cus.setLabel(localTable.getTableName());
		cus.setPluralLabel(localTable.getTableName() + "s");
		
		//Each field in Force.com Needs a Name field. By Default, you are going to create AutoNumbers
		CustomField cf = new CustomField();
		cf.setType(FieldType.AutoNumber);
		cf.setLabel("AutoID");
		cus.setNameField(cf);
		cus.setDeploymentStatus(DeploymentStatus.Deployed);
		cus.setSharingModel(SharingModel.ReadWrite);
		return cus;
	}

Notice that the method above is very straightforward. You simply make sure to set all the required fields on the CustomObject and return it. One important thing to note is that each custom object in Salesforce has to have a Name field, which has to be present at the time you create the object. To create other fields, a separate call is necessary. Also you need to ensure that you create the full name with the “__c” literal at the end.

For each object, you also need to create the corresponding fields. To do this, you process the fields in the buildCustomField method below.

	/*
	 * Name: buildCustomField
	 * Purpose: Create the metadata customfield based on the data type from MySQL
	 */
	private CustomField buildCustomField(FieldDefinition field, CustomObject cus)
	{
		CustomField customField = new CustomField();
		customField.setFullName(cus.getFullName() + "." + field.getName() + "__c");
		customField.setLabel(field.getName());
		if(field.getType().contains("VARCHAR"))
		{
			customField.setType(FieldType.Text);
			customField.setLength(field.getLength());
		}else if(field.getType().contains("INT"))
		{
			customField.setType(FieldType.Number);
			customField.setScale(0);
			customField.setPrecision(field.getLength());
		}else if(field.getType().contains("DECIMAL") || field.getType().contains("FLOAT"))
		{
			customField.setType(FieldType.Number);
			customField.setScale(field.getPrecision());
			customField.setPrecision(field.getLength());
		}else if(field.getType().contains("DATETIME"))
		{
			customField.setType(FieldType.DateTime);
		}else if(field.getType().contains("DATE"))
		{
			customField.setType(FieldType.Date);
		}else
		{
			customField.setType(FieldType.Text);
			customField.setLength(field.getLength());
		}
		return customField;
	}

There are a few “gotchas” here that you should be aware of. First, to link a field to a custom object, you do not use an ID, but instead use a qualified name. So when you set the full name of the object, you give it the full name of “ObjectName__c.FieldName__c” that will link it to your custom object. Also, certain field types have requirements. Number fields, for example, need to have a scale and precision set, whereas text fields need to have a length. You can reference the Metadata API Documentation for more details.

Once you create the objects and fields, you can invoke the Metadata API to actually create the objects in your Force.com org. To do this, use the sendMetadataRequest method that calls the Metadata API.

	/*
	 * Name: sendMetadataRequest
	 * Purpose: This method will fire the Asynchronous Request to the Metadata API. It will retry to check on the request status every 3 seconds.
	 */
	private void sendMetadataRequest(ArrayList<Metadata> metadataRequest) throws Exception
	{
		Metadata[] reqList = new Metadata[metadataRequest.size()];
		reqList = metadataRequest.toArray(reqList);
		AsyncResult[] results = metaConn.create(reqList); // Send the request to Force.com to create the metadata
		String[] ids = new String[results.length];
		int y = 0;
		for(AsyncResult result: results)
		{
			System.out.println(result.toString());
			ids[y] = result.getId();
			System.out.println("AsyncID == " + result.getId());
			y++;
		}
		boolean done = false;
                    long waitTimeMilliSecs = 3000; //Set the time to wait to query for the Metadata API Status
        	AsyncResult[] arsStatus = null;
        	while (!done) //Loop until the response is completed either successfully or as a failure
        	{
        		Thread.sleep(waitTimeMilliSecs); //Sleep and then retry
        	
        		arsStatus = metaConn.checkStatus(ids); // Make a call to Force.com to check the status of the requests
        		if(arsStatus == null)
        		{
        			done = true;
        		}
        		if(arsStatus != null)
        		{
	        		for(AsyncResult arsResult : arsStatus)
	        		{
	        			System.out.println(arsResult.toString());
	        			if(arsResult.isDone())
	        			{
	        				done = true;
	        			}
	        		}
        		}
        	}		
     }

The Metadata API, unlike the Enterprise API, is asynchronous. When you make an Metadata API call, you are given a set of request ID's that allow you to monitor the status of the request. The method takes the list of ID's returned from the call, waits 3 seconds, and then calls the checkStatus method to find out the status of the request. If the status comes back with values, then it's apparent that the request has finished and the method can exit the loop. If the request fails and returns a null value, the method also exits the loop.

Summary

Those are the basics of this simple application. As you can see, it is fairly easy to create a Force.com Metadata API application to move schema information between Force.com and other databases. This small tool is only scratching the surface of what the API offers. Theoretically, you can create tools that archive metadata versions for compliance reasons, or create applications to assist in the sometimes painful process of changing field types by auto-removing field references in Apex/Visualforce code. The Metadata API provides the foundation to make our lives as developers and administrators easier. So go forth and code!

About the Author

Cory Cowgill is a Senior Consultant at West Monroe Partners, a Salesforce.com Select Consulting Partner. As an active member of the Force.com community he can be found answering questions on the developer boards, at developer meetups, or twitter (@corycowgill). He also maintains a Force.com blog where he discusses his thoughts on the platform. He is a firm advocate for leveraging the Force.com platform to rapidly develop enterprise applications.