Replicate Multiple Bitbucket Accounts via the CData Sync CLI

CData Sync for Bitbucket is a stand-alone application that provides solutions for a variety of replication scenarios such as replicating sandbox and production instances into your database. Both Sync for Windows and Sync for Java include a command-line interface (CLI) that makes it easy to manage multiple Bitbucket connections. In this article we show how to use the CLI to replicate multiple Bitbucket accounts.

Configure Bitbucket Connections

You can save connection and email notification settings in an XML configuration file. To replicate multiple Bitbucket accounts, use multiple configuration files. Below is an example configuration to replicate Bitbucket to SQLite:

Windows

<?xml version="1.0" encoding="UTF-8" ?>
<CDataSync>
  <DatabaseType>SQLite</DatabaseType>
  <DatabaseProvider>System.Data.SQLite</DatabaseProvider>
  <ConnectionString>Workspace=myworkspaceslug;Schema=Information;InitiateOAuth=GETANDREFRESH;</ConnectionString>
  <ReplicateAll>False</ReplicateAll>
  <NotificationUserName></NotificationUserName>
  <DatabaseConnectionString>Data Source=C:\my.db</DatabaseConnectionString>
  <TaskSchedulerStartTime>09:51</TaskSchedulerStartTime>
  <TaskSchedulerInterval>Never</TaskSchedulerInterval>
</CDataSync>

Java

<?xml version="1.0" encoding="UTF-8" ?>
<CDataSync>
<DatabaseType>SQLite</DatabaseType>
  <DatabaseProvider>org.sqlite.JDBC</DatabaseProvider>
  <ConnectionString>Workspace=myworkspaceslug;Schema=Information;InitiateOAuth=GETANDREFRESH;</ConnectionString>
  <ReplicateAll>False</ReplicateAll>
  <NotificationUserName></NotificationUserName>
  <DatabaseConnectionString>Data Source=C:\my.db</DatabaseConnectionString>
</CDataSync>

For most queries, you must set the Workspace. The only exception to this is the Workspaces table, which does not require this property to be set, as querying it provides a list of workspace slugs that can be used to set Workspace. To query this table, you must set Schema to 'Information' and execute the query SELECT * FROM Workspaces>.

Setting Schema to 'Information' displays general information. To connect to Bitbucket, set these parameters:

• Schema: To show general information about a workspace, such as its users, repositories, and projects, set this to Information. Otherwise, set this to the schema of the repository or project you are querying. To get a full set of available schemas, query the sys_schemas table.
• Workspace: Required if you are not querying the Workspaces table. This property is not required for querying the Workspaces table, as that query only returns a list of workspace slugs that can be used to set Workspace.

Authenticating to Bitbucket

Bitbucket supports OAuth authentication only. To enable this authentication from all OAuth flows, you must create a custom OAuth application, and set AuthScheme to OAuth.

Be sure to review the Help documentation for the required connection properties for you specific authentication needs (desktop applications, web applications, and headless machines).

Creating a custom OAuth application

From your Bitbucket account:

1. Go to Settings (the gear icon) and select Workspace Settings.
2. In the Apps and Features section, select OAuth Consumers.
3. Click Add Consumer.
4. Enter a name and description for your custom application.
5. Set the callback URL:
   • For desktop applications and headless machines, use http://localhost:33333 or another port number of your choice. The URI you set here becomes the CallbackURL property.
   • For web applications, set the callback URL to a trusted redirect URL. This URL is the web location the user returns to with the token that verifies that your application has been granted access.
6. If you plan to use client credentials to authenticate, you must select This is a private consumer. In the driver, you must set AuthScheme to client.
7. Select which permissions to give your OAuth application. These determine what data you can read and write with it.
8. To save the new custom application, click Save.
9. After the application has been saved, you can select it to view its settings. The application's Key and Secret are displayed. Record these for future use. You will use the Key to set the OAuthClientId and the Secret to set the OAuthClientSecret.

Configure Queries for Each Bitbucket Instance

Sync enables you to control replication with standard SQL. The REPLICATE statement is a high-level command that caches and maintains a table in your database. You can define any SELECT query supported by the Bitbucket API. The statement below caches and incrementally updates a table of Bitbucket data:

REPLICATE Issues;

You can specify a file containing the replication queries you want to use to update a particular database. Separate replication statements with semicolons. The following options are useful if you are replicating multiple Bitbucket accounts into the same database:

You can use a different table prefix in the REPLICATE SELECT statement:

REPLICATE PROD_Issues SELECT * FROM Issues 

Alternatively, you can use a different schema:

REPLICATE PROD.Issues SELECT * FROM Issues

Run Sync

After you have configured the connection strings and replication queries, you can run Sync with the following command-line options:

Windows

BitbucketSync.exe -g MyProductionBitbucketConfig.xml -f MyProductionBitbucketSync.sql

Java

java -Xbootclasspath/p:c:\sqlitejdbc.jar -jar BitbucketSync.jar -g MyProductionBitbucketConfig.xml -f MyProductionBitbucketSync.sql