Salesforce PHP MySQL Database Replication Tool

mike · #1 08-09-2007, 10:44 AM

Here is the MySQL version of the Database replication Tool. This new script takes parameters from a web form and will create your MySQL tables/objects via the API. All you have to supply is the following:

* You're Salesforce Username
* You're Salesforce Password
* Table Prefix (e.g. sforce_ ) not required
* MySQL Database Name
* MySQL User Name
* MySQL Password

David Clairborne recently released a tool that will create the schema that a user could copy and paste from a web browser for MySQL and I added the process of creating the tables in MySQL . This new script will not only create that same file, but it will login to your MySQL database and create the tables for you.

This script was developed against a new developers account and it works without any hiccups. It has also been tested against a real live org, a sandbox, and a modified developers account. I do not see why it would not work against anyone's or any company's live instance.

If you plan on using this script to test it out, there are a few lines of code that need to be checked so they match your paths of the following scripts from the PHP toolkit:

Check line number 26 & 27 and make sure you correct the path to the following:

PHP Code:


			
require_once ('./includes/soapclient/SforcePartnerClient.php');
require_once ('./includes/soapclient/SforceHeaderOptions.php');

and line number > 53

PHP Code:


			
$wsdl = './includes/soapclient/partner.wsdl.xml';

If you forget to modify these lines, the script will fail. This takes into account that the table names and field names in MySQL has some reserved words that conflict with the Object names in Salesforce, so you will need to check your table names once the script is complete. These two words are the Objects Group and Case. There are field names which are in salesforce that have this same exact name. Here is what I did to check this and change the name to Group__C and Case__C.

Note: This change to the tables names on happens if you decide not to use a prefix (e.g. sforce_) on the form

If you wish to obtain the SQL file that is also created to this, it will be located in the same folder as this script runs in and is called:

Code:

salesforce_mysql.sql

This script uses ADOdb to connect to MySQL; you MUST be using this database class in order for this script to work, if you do not, THE SCRIPT WILL FAIL.

You can download the ADOdb package from the main menu on my home page. This package was developed to work with:

Apache
MySQL 10g
PHP 5.*

If you have any questions please feel free to use the contact form on this site and I will do my best to get back to you ASAP!

Thanks,
Mike

mike · #2 08-10-2007, 08:25 AM

I am adding one more feature to this script that i am testing still.

This will only be added to the MySQL version. It will try and create the database that you input into the form, if it is not already setup. If it is then it will still create the tables

I will probably release it prior to the Salesforce.com Dreamforce '07 conference, I still need to test it.

Well it is off to Hawaii, be back soon!!!!

~Mike

aaron · #3 05-22-2008, 03:05 PM

I got this script working perfectly with my developer account. All of my table schema is populated in mysql. Now the question is how do I fill it with data? I've been looking for a generic replication script to run next and am coming up blank. Is there any way to do a generic rep script in conjunction with this schema download?

Clarification: My goal here is to
1. create a copy of all table schema including custom fields in mysql (done, thanks to the script above).
2. copy everything out of salesforce into the new mysql database
3. create a script that will update the mysql database with any changes/additions/deletions done in sforce (this can be run in cron or on demand). From what I have read, this may only be possible with an output soap connection from sforce whenever data changes...is that right?

This will always be one way from sforce to mysql. The point of this is so that I can do fun things with the data in mysql that I cannot do on sforce.

I have found scripts on this site for replicating some of the tables, but I really just want one super script that will update my backup mysql copy with the current sforce data. Seems simple enough, right?

mike · #4 05-22-2008, 04:28 PM

Aaron

Thanks for posting on this site, I appreciate it

So it seems to me that you are looking for a super script that would download all the data from each object in Salesforce and place it into the schema that you have built

// I am glad that you have been able to get it to work

Could a script like that be built, I think it could, but it would be a waste of time in my opinion

Not all the objects (and this is my opinion) need to be downloaded, only ones that you deem necessary and I guess you could say strategic.

Example: We replicate our Accounts, Users, Opportunities, Parts, Pricebook, etc....

Each object has it's own script that replicates and runs on CRON

But there is a function that could work to your advantage and I have not used them, the function is > getUpdated:

PHP Code:


			
public function getUpdated($type, $startDate, $endDate) {
        $this->setHeaders("getUpdated");
        $arg = new stdClass;
        $arg->sObjectType = new SoapVar($type, XSD_STRING, 'string', 'http://www.w3.org/2001/XMLSchema');
        $arg->startDate = $startDate;
        $arg->endDate = $endDate;
        return $this->sforce->getUpdated($arg)->result;
    }

I have a meeting, I will reply more later

~Mike

aaron · #5 05-22-2008, 05:11 PM

I just wanted to do everything to make sure I was never missing anything. I suppose I don't need every table, I just thought it would be easier that way.

So my question is why is it necessary to have a separate script for each table? It looks like a generic data copy script could be written using the same methods as the schema copy script. Have you come across some reason why each object must have it's own explicit script in which all fields are hardcoded?

mike · #6 05-22-2008, 05:49 PM

Quote:

Originally Posted by aaron

I just wanted to do everything to make sure I was never missing anything. I suppose I don't need every table, I just thought it would be easier that way.

So my question is why is it necessary to have a separate script for each table? It looks like a generic data copy script could be written using the same methods as the schema copy script. Have you come across some reason why each object must have it's own explicit script in which all fields are hardcoded?

It is not necessary if you want to build such a script, I just find it more manageable to do it this way.

BUT let's say you do it your way and create one script to download all your fields from an array of 10 tables and during the process something fails in the script, like a lost connection to Saleforce (which has happened) or you have a large dataset in salesforce (like 150,000 records). Let's say it happens in the third object during your script run, well the other 7 will not replicate

Anyway, You can go about it anyway you wish man, I am just trying to give you advice that works for me and has never failed. Your way may work just fine and if it does, I hope that you share your results with all the rest of the PHP developers in the Salesforce community

Anyway I hope that gives you some guidance in your search for a replication system

All the best

~Mike

aaron · #7 06-05-2008, 12:26 PM

On the contrary, I like the suggestion of not needing to replicate all data. That is also a good point of encountering errors during a large process.

I am thinking of making a generic function that when given a table/object name, would be flexable enough to pull all the data from that table and put it into mysql. This function would be passed an array or looped through one so that each part would be seperate and could log errors or repeat a single section if errors were encountered. I want to keep it modular, I just don't want to write a different replication script for each table. I will work on this and let you know how it goes.

mike · #8 06-05-2008, 01:17 PM

That would be interesting and I would love to see what you come up with. Keep me in the loop if you do not mind Aaron