Skip to content

Home

Jump to bottom Edit New page
paynejd edited this page Mar 21, 2013 · 5 revisions

Summary

This PHP command-line script is used to automate synchronizing form data between Dimagi's CommCare HQ and a local relational database (i.e. MySql).

NOTE: Dimagi is in the process fo developing their own export functionality that should replace this script for most use cases. (Expected to be ready by May 2013)

HQSync Setup Instructions

  1. Download code from: https://github.com/paynejd/hqsync
  2. Unzip code into an executable directory
  3. Create the hqsync database in mysql
  4. Run the hqsync_init.sql script in the newly created hqsync database
  5. Update hqsync.hqsync to sync the desired reports (see https://wiki.commcarehq.org/display/commcarepublic/Export+API for documentation)
  6. Create databases for each domain (e.g. pathfinder) - this is where the data for each domain will be stored; tables are created automatically
  7. Update sync.php to use

How to upgrade the sync.php:

  1. Backup existing data (use your'e own username, you will be prompted for password). From the command line, enter: mysql -u -p pathfinder > pathfinder_backup.sql mysql -u -p hqsync > hqsync_backup.sql

  2. Drop all pathfinder tables that were created by hqsync - these should all be in the pathfinder database; you can see the list of tables to drop in hqsync.hqsync_table. The sql command is: drop table table_name_goes_here;

  3. Purge the hqsync_table and hqsync_log tables truncate hqsync.hqsync_table truncate hqsync.hqsync_log

  4. Replace sync.php with new version - Browse to location of the sync.php scripts (I think this is under the root user's home directory) - Rename existing sync.php (mv sync.php sync.php.backup) - Copy in the attached updated sync script

  5. Execute the script (note that this might take 30+ min to finish since it is downloading all of the data) ./sync.sh

  6. Confirm that it works - check the logs to confirm that the import was successful select * from hqsync.hqsync_log;

How to add a domain to hqsync:

  1. Add your sync user to the domain inside CommCare HQ (take a look at the hqsync table to see which username I was using).

  2. Go to the Reports -> Export Submissions to Excel section of HQ for the domain you would like to add.

  3. Create a database in Mysql named after the domain to store the synced data

  4. Add a record to the hqsync table for each form you want to sync. Records should follow this format:

a. domain - should match the domain name in HQ exactly

b. dbname - should match the database name that you created in step #3 exactly

c. url - should follow the format below (let's wait for Cory's response to finalize): https://www.commcarehq.org/a/pih-cholera/reports/export/?export_tag=%22http://openrosa.org/formdesigner/1679BE38-C8DD-4564-B34D-F706E43B0807%22&filename=Done%20Kolera&format=csv d. form_name - should match the name of the form in HQ exactly e. uid - this is the username used to login to HQ (NOTE: Don't use your personal account. Its better to use a separate sync account, such as the one I created to sync Pathfinder) f. pwd - this is the password used to login to HQ g. active - 0 to inactivate; 1 to activate

h. use_token - this should be 1 for tables that store data sequentially and you don't want to download the entire dataset each time. basically, set this to 1 for all forms; set it to 0 for the user/referrals/case export

i. purge_before_import - this empties the table out before every import; generally only set this to 1 for the user/referrals/case export

Add a custom footer
Add a custom sidebar
Clone this wiki locally