When I traveled down the path of replicating data from Salesforce into a local database, it was a long and winding road. I started by using the command line interface (CLI) and setting up CRON jobs to run scripts that would login via the API and get the records and insert them into Oracle. This had tons of problems with field data and field types. It was not something that one could rely on.

The next step that I took was to write PHP scripts to login via the API and perform a SOQL query, basically a Select * from Table, and then insert this data into a database. I have been fine tuning these scripts over and over again to increase speed and performance. They work perfectly, but each time they run it is a full load. The size of the objects in Salesforce increase, some of them with over 200,000 records. When you are running 8 or 9 different scripts to process the objects that are being replicated, it can take around 2 hours for the whole process to run.

Let's take an example using the Opportunity object in Salesforce. If there is around 100,000 records and each record has over 60 fields, which is a ton of data to replicate each time. Even with the most fine tuned script, it takes over 45 min for that one script to run and insert all the data into Oracle, and that is with binding the data and using stored procedures to help speed the process up.

GetUpdated and GetDeleted are methods that are used and have been in the PHPToolKit for sometime, I just never had time to develop any of the scripts to use these methods. Two weeks ago I finally started and have been working on a proof of concept so I could speed up the process of keeping our local replication of data fresh.

Theoretically you could use these methods like this:

1. Run your script and login to Salesforce via the API
2. Set two variables for a time period to check the logs to see what has been updated
3. Get an array of those ID's using the GetUpdated method
4. Loop through those ID's and delete them from your local database
5. Insert the fresh record with updated data, no need to compare individual fields, just insert a whole new record
6. Once done, repeat the process to see if there are any ID's that have been removed using the GetDeleted method.
7. Delete them from your local database to keep the two tables in sync.

This process sounds like a lot, but should only take about 3-5 minutes to run.

Once I have a working copy, I will post it here, but this could really give your company a more real time data update and not having to worry about granting access to others to your Salesforce data via the API

~Mike

I am doing my best to get a full working example of the getupdated and get deleted for MySQL up and running within the next week. I have been so hammered at work and DreamForce in the past few weeks, I just have not had the time

~Mike

I am so sorry for anyone that was looking for this tutorial or example

I will have the getUpdated example up tomorrow! This one will only work with Oracle at the current time, but I will get a MySQL example up soon

It is just so hard these days to find time with work, school, and my family

So with getUpdated you can really improve keeping your records in your local database up to date.

This simple code gives you an array of the ID's that have changed in a time period. In my example, I will use the last 36 hours:

PHP Code:

<?php

ini_set("soap.wsdl_cache_enabled", "0");
require_once ('./soapclient_new/SforcePartnerClient.php');

$wsdl = './soapclient_new/partner.wsdl.xml';
$userName = "me@email.com";
$password = "password";


//setup connection
$client = new SforcePartnerClient();
$client->createConnection($wsdl);
$loginResult = $client->login($userName, $password);


//Set current time to get records from Salesforce
$currentTime = mktime();
// assume that update occured within the last 24 hrs.
$startTime = $currentTime - (60 * 60 * 36); //(seconds * minutes * hours)
$endTime = $currentTime;

echo "***** Get Updated Contacts from the last 36 hours *****<br />";
$getUpdateddResponse = $client->getUpdated('Contact', $startTime, $endTime);

echo '<pre>' . print_r($getUpdateddResponse,true) . '</pre>';


?>

and this produces this array of ID's:

Code:

***** Get Updated Contacts from the last 36 hours *****
stdClass Object
(
    [ids] => Array
        (
            [0] => 0034000000NZ91IAAT
            [1] => 0034000000SE5sdAAD
            [2] => 0034000000LQOGmAAP
            [3] => 0034000000MHkGRAA1
            [4] => 0034000000NF3ECAA1
            [5] => 0034000000TvTDJAA3
            [6] => 0034000000QXNqJAAX
            [7] => 0034000000LQWmfAAH
            [8] => 0034000000P5LwtAAF
            [9] => 0034000000OBx71AAD
            [10] => 0034000000NtNK5AAN
            [11] => 0034000000LQUT4AAP
            [12] => 0034000000TvTLLAA3
            [13] => 0034000000MJG3CAAX
            [14] => 0034000000LQR9rAAH
            [15] => 0034000000MbqmsAAB
            [16] => 0034000000MIHV7AAP
            [17] => 0034000000LQRAQAA5
            [18] => 0034000000SFHFJAA5
            [19] => 0034000000NY0zKAAT
            [20] => 0034000000LQR9sAAH
            [21] => 0034000000RWDukAAH
            [22] => 0034000000MGTYdAAP
            [23] => 0034000000PwxDjAAJ
            [24] => 0034000000SolzIAAR
            [25] => 0034000000Tw0mfAAB
            [26] => 0034000000SFtNzAAL
            [27] => 0034000000PUk4IAAT
            [28] => 0034000000PUk7jAAD
            [29] => 0034000000RfAZWAA3
            [30] => 0034000000RfAZYAA3
            [31] => 0034000000RfAZaAAN
            [32] => 0034000000MxfFGAAZ
            [33] => 0034000000MxfNPAAZ
            [34] => 0034000000NvSUlAAN
            [35] => 0034000000NvSV0AAN
            [36] => 0034000000OAYjcAAH
            [37] => 0034000000OAYkSAAX
            [38] => 0034000000OAYlGAAX
            [39] => 0034000000PUjpeAAD
            [40] => 0034000000PUkGwAAL
            [41] => 0034000000PuR8SAAV
            [42] => 0034000000PuRYqAAN
            [43] => 0034000000SEFp6AAH
            [44] => 0034000000SEFw2AAH
            [45] => 0034000000SEFw6AAH
            [46] => 0034000000SEG3aAAH
            [47] => 0034000000SFtBaAAL
            [48] => 0034000000SFtCTAA1
            [49] => 0034000000SFtJZAA1
            [50] => 0034000000SFtMIAA1
            [51] => 0034000000SGMF3AAP
            [52] => 0034000000SGMGTAA5
            [53] => 0034000000SGMOkAAP
            [54] => 0034000000SGMR2AAP
            [55] => 0034000000SGMSeAAP
            [56] => 0034000000SGMUCAA5
            [57] => 0034000000SaYU2AAN
            [58] => 0034000000SaYqjAAF
            [59] => 0034000000SaZ1oAAF
            [60] => 0034000000TYFaEAAX
            [61] => 0034000000Tw2QPAAZ
            [62] => 0034000000LQPUKAA5
            [63] => 0034000000LQPV1AAP
            [64] => 0034000000LQOk3AAH
            [65] => 0034000000Nv7IWAAZ
            [66] => 0034000000LQRLmAAP
            [67] => 0034000000LQQQQAA5
            [68] => 0034000000LQRJfAAP
            [69] => 0034000000Tw4XWAAZ
            [70] => 0034000000TUl5yAAD
            [71] => 0034000000SCNDDAA5
            [72] => 0034000000LQQItAAP
            [73] => 0034000000LQQHKAA5
            [74] => 0034000000LQRIeAAP
            [75] => 0034000000LlBbKAAV
            [76] => 0034000000LQRWVAA5
            [77] => 0034000000LQRvgAAH
            [78] => 0034000000LQRZsAAP
            [79] => 0034000000LQRUJAA5
            [80] => 0034000000LQWk3AAH
            [81] => 0034000000TikuBAAR
            [82] => 0034000000TwXATAA3
            [83] => 0034000000SAE4xAAH
            [84] => 0034000000LQSHwAAP
            [85] => 0034000000QMyjCAAT
            [86] => 0034000000LQSIGAA5
            [87] => 0034000000LQUBcAAP
            [88] => 0034000000MJlSiAAL
            [89] => 0034000000RUUaHAAX
            [90] => 0034000000LlA6tAAF
            [91] => 0034000000LQX4yAAH
            [92] => 0034000000Tk4Q6AAJ
            [93] => 0034000000SGExpAAH
            [94] => 0034000000QncmkAAB
            [95] => 0034000000MyV3kAAF
            [96] => 0034000000Nv8XKAAZ
            [97] => 0034000000RB9LMAA1
            [98] => 0034000000PQV7CAAX
            [99] => 0034000000LQX42AAH
            [100] => 0034000000MvnTbAAJ
            [101] => 0034000000MbnwVAAR
            [102] => 0034000000MyU4fAAF
            [103] => 0034000000TY2ACAA1
            [104] => 0034000000TY2GuAAL
            [105] => 0034000000TwXTeAAN
            [106] => 0034000000TwXTyAAN
            [107] => 0034000000PUXk2AAH
            [108] => 0034000000MGcmQAAT
            [109] => 0034000000SF0TjAAL
            [110] => 0034000000TwXXHAA3
            [111] => 0034000000Tw7utAAB
            [112] => 0034000000LQWPKAA5
            [113] => 0034000000LQV3FAAX
            [114] => 0034000000MIMW2AAP
            [115] => 0034000000LQUboAAH
            [116] => 0034000000LQV3LAAX
            [117] => 0034000000LQUETAA5
            [118] => 0034000000LQWRGAA5
            [119] => 0034000000MwiJHAAZ
            [120] => 0034000000MIMVdAAP
            [121] => 0034000000LQUc7AAH
        )

    [latestDateCovered] => 2008-11-26T14:03:00.000Z
)

From here you can code a Salesforce SOQL statement to get the individual record from Salesforce and then re-insert it into your MySQL database.

So it would work like this:

1. Get the array of ID's
2. Take each ID and check to see if it is in your local database or just delete it from your database without checking (saves on processing time)
3. Take each ID and get all the data from the individual record
4. Then re-insert it into your database.

It just makes more sense to do it this way because you do not have to truncate your entire table each time you run and the record count goes way down. Instead of inserting 50,000 records each time, you only have to insert the ones that have changed in a past time frame. In my case it is only about 250 records a day.

I will try and get a complete working script up, but this should give you a good start


~Mike