CCDBSync

Project Description:

The purpose of this project is to sync a SQL database with data from an ODK-X Sync-Endpoint.

Setting Up the Development Environment:

1. Install git
2. Install maven
3. Install openjdk-8
4. Clone this repository and cd into it
5. Set up your config directory (See instructions below)
6. To install the local ODK-X dependencies:

mvn validate

7. To build the executable jar:

mvn clean package

8. To execute the jar:

java -jar target/ccdbsync-*-SNAPSHOT-jar-with-dependencies.jar

Config Values

The following files must be in the config directory.

1. blacklisted_tables.txt
2. single_sync_tables.txt
3. config.txt
4. tables_to_convert.csv

blacklisted_tables.txt

The blacklisted_tables.txt file can be used to ignore tables on the ODK-X Sync-Endpoint server to sync to the SQL database. If none of the tables should be ignored, this file should be empty. To ignore a table, put the ODK-X table name on one line of the file. For example, to ignore tables refrigerators and refrigerator_temperature_data the content of blacklisted_tables.txt should be:

refrigerators
refrigerator_temperature_data

single_sync_tablestxt

The single_sync_tables.txt file can be used declare which tables should only sync data from the first ODK-X Sync-Endpoint defined in config.txt. If a table should only be synced once with, put the ODK-X table name on one line fo the file. For example, to sync geographic_regions and refrigerator_types only once the content of single_sync_tables.txt should be:

geographic_regions
refrigerator_types

config.txt

The config.txt file contains the credentials for the SQL database and ODK-X Sync-Endpoint as well as other configuration values. The order of values is below:

jdbc:postgresql://host:port/database
databaseUsername
databasePassword
logsAndDataDir
defaultTimeZone
logTimeZone
1
syncEndpointURL
syncEndpointAppId
syncEndpointUsername
syncEndpointPassword

Example configuration values could be:

SQL Server Name: db.windows.net 
SQL Server Port: 5432
SQL Database Name: testdb
SQL Username: testuser
SQL Password: testpass
Directory to Store Logs and Data: logsAndData
Default Time Zone: America/Los_Angeles
Log Time Zone: America/Los_Angeles
Number of ODK-X Sync-Endpoints: 2
ODK-X Sync-Endpoint URL: https://testserver.com/odktables
ODK-X Sync-Endpoint Username: testODKUser
ODK-X Sync-Endpoint Password: testODKPass
ODK-X Sync-Endpoint AppID: default
ODK-X Sync-Endpoint 2 URL: https://testserver2.com/odktables
ODK-X Sync-Endpoint 2 Username: testODKUser
ODK-X Sync-Endpoint 2 Password: testODKPass
ODK-X Sync-Endpoint 2 AppID: default

The corresponding config.txt would be:

jdbc:postgresql://db.windows.net:5432/testdb
testuser
testpass
logsAndData
America/Los_Angeles
America/Los_Angeles
2
https://testserver.com/odktables
default
testODKUser
testODKPass
https://testserver2.com/odktables
default
testODKUser
testODKPass

NOTE: odktables is required at the end of the ODK-X Sync-Endpoint URL.

tables_to_convert.csv

tables_to_convert.csv is a CSV file that maps the ODK-X Sync-Endpoint fields to the SQL database fields. The header for the CSV file should be:

source_table	destination_table	source_field	target_field	field_type

To sync field `test_data` from `test_odkx_table` ODK-X table to `sql_test_data` in SQL table `sql_test_table`, see the example `tables_to_convert.csv` file below:

source_table	destination_table	source_field	target_field	field_type
test_odkx_table	sql_test_table	test_data	sql_test_data

The `field_type` can be left blank if the type in the database is VARCHAR. Other valid field types are INT, DOUBLE, DATETIME, and TIME. Every field that should be synced from an ODK-X table to a SQL table should be defined in `tables_to_convert.csv`.

Execute the Code Via a Cron Job in a Docker Container

Install docker

To build the docker container

docker build -t data-sync .

To run the container

docker run -p5432:5432 data-sync

The entrypoint.sh script has the details of the cron job.

Integration Tests

In order for the tests to run correctly, a config directory must exist under the test directory with a valid ODK-X Sync-Endpoint and SQL database.

To run the tests:

mvn test -DskipTests=false