Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
AdamMAdamM 

Creating local SQL schema from 'describeSObject' or other means

We're trying to create a local copy of our Salesforce data.  We did a quick-and-dirty CSV import to get started, but everything is a varchar and we don't have any referential integrity.

How are people doing this on their local RDBMS systems?  I think I could build some "CREATE TABLE ..." strings in Java using describeSObject, but is there an easier way?  Or has someone already invented this wheel?

I'm already having problems using the 'field.getReferenceTo()' method.  It tells me that Contact.AccountId is a reference to the "Account" object, but I don't know to which field it is referring.  Of course, Contact.AccountId is a reference to Account.Id, but I don't see how to make that connection using the API.

How are you folks creating your local schema?  Is there a tool to read the enterprise.wsdl file and create ANSI SQL schema or something?

Thanks,

Adam

 

SuperfellSuperfell
foreign key references reported by the referenceTo array are always to the Id fields on the foreign object.
benjasikbenjasik
If data replication is what you want to do, there's a partner solution.

See the data replication engine:

http://www.salesforce.com/partners/solutions.jsp?id=Integration%20(EAI/ETL)

Our professional services has also built a solution.

Also, as you mention, all the data you need is in the describeSobject call.
HaroldHHaroldH
Another quick and dirty approach which seems reasonably thorough -- please correct me if I'm wrong -- is to use the "Save Schema" function in the sforce explorer application. We took the XML file that is produced by this function, and transformed it with XSL to create a SQL table definition.

We're not implementing referential integrity at this time. This information isn't provided by the XML schema, although you could safely assume that fields named "ObjectId" reference the Id field on an object called "Object".

Message Edited by HaroldH on 01-25-2005 11:19 AM
AdamMAdamM
I've had some luck iterating through all the objects returned by binding.describeGlobal().getTypes() and just creating SQL from that. At least I can get all the foreign key constraints that way with field.getReferenceTo().

It's taken a while to get to this point, but I can see the light at the end of the tunnel!!

AdamM