Basecamp Connection Details

Introduction

Connector Version

This documentation is based on version 23.0.8803 of the connector.

Get Started

Basecamp Version Support

The connector models Basecamp 2 and Basecamp 3 accounts as bidirectional tables.

Establish a Connection

Connect to Basecamp

In addition to authenticating, set the following connection properties to access Basecamp tables.

• Schema: Set this to "V2" or "V3" to connect to a Basecamp 2 or Basecamp 3 account.

• ProjectId: This is a required parameter for most table operations. You can query the Projects table to obtain this value.

  If you do not specify the ProjectId connection property, the connector will use the first project ID returned from the Projects table in your first authentication to Basecamp.

• AccountId: You can find the AccountId in the URL after you log in to Basecamp in a web browser:

  http://basecamp.com/<AccountId>
  

Authenticate to Basecamp

Basecamp 2 uses basic or OAuth 2.0 authentication. You can use basic authentication to connect to your own account, or you can use OAuth to enable other users to log into their own accounts.

Basecamp 3 requires OAuth 2.0.

Basic

In basic authentication, you use your login credentials to connect. Set the following properties:

• User: This is the username you use to log in to Basecamp.
• Password: This is the password you use to log in to Basecamp.
• AuthScheme: Set this to Basic.

OAuth

AuthScheme must be set to OAuth in all user account flows.

Desktop Applications

An embedded OAuth application is provided that simplifies OAuth desktop Authentication. Alternatively, you can create a custom application. See Creating a Custom OAuth App for information on creating custom applications and reasons for doing so.

For authentication, the only difference between the two methods is that you must set two additional connection properties when using custom OAuth applications.

After setting the following connection properties, you are ready to connect:

• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• OAuthClientId: (custom applications only) Set to the Client ID in your application settings.
• OAuthClientSecret: (custom applications only) Set to the Client Secret in your application settings.

When you connect, the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process as follows:

• Extracts the access token from the callback URL.
• Obtains a new access token when the old one expires.
• Saves OAuth values in OAuthSettingsLocation so that they persist across connections.

Create a Custom OAuth App

When to Create a Custom OAuth App

embeds OAuth Application Credentials with branding that can be used when connecting via either a Desktop Application or from a Headless Machine. Creating a custom OAuth application is, however, required when using a web application. You may choose to create your own OAuth Application Credentials when you want to

• control branding of the Authentication Dialog
• control the redirect URI that the application redirects the user to after the user authenticates
• customize the permissions that you are requesting from the user

Follow the steps below to create a custom OAuth app and obtain the connection properties in a specific OAuth authentication flow.

Create a Custom OAuth App

Register your Basecamp app on integrate.37signals.com to obtain the following connection properties:

• OAuthClientId
• OAuthClientSecret
• CallbackURL (desktop only)

The only difference between desktop applications, web applications, and headless machines is what you set the Redirect URI to:

• Desktop Applications:

  Set the Redirect URI to http://localhost:portnumber and set CallbackURL to match. You can specify any port available.

• Web Applications: Set the Redirect URI to a page on your website. This page is what users return to after granting permissions to the application.

• Headless Machines: Set the Redirect URI to http://localhost:portnumber. You can specify any port available.

Important Notes

Configuration Files and Their Paths

• All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features

This section details a selection of advanced features of the Basecamp connector.

User Defined Views

The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Proxy

To configure the connector using private agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Basecamp and then processes the rest of the query in memory (client-side).

User Defined Views

The Basecamp connector allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Define Views Using a Configuration File

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Projects WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Work with User Defined Views

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration

Customize the SSL Configuration

By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Data Model

The Basecamp connector models the Basecamp 2 and Basecamp APIs as relational tables, views, and stored procedures. These are defined in schema files, which are simple, text-based configuration files.

The available entities, as well as any API limitations and requirements for querying these entities, are documented in Basecamp 2 Data Model and Basecamp 3 Data Model.

Overview

The Data Models illustrate an example of what your Basecamp environment might look like. The actual data model will be obtained dynamically based on your Basecamp account.

Key Features

• Tables and Views are dynamically defined to model calendars, documents, and projects on Basecamp.
• Stored procedures allow you to execute operations to Basecamp, including downloading and uploading objects.
• Live connectivity to these objects means any changes to your Basecamp account are immediately reflected when using the connector.

Basecamp 2 Data Model

Basecamp 2 Data Model describes the schemas available to connect to Basecamp 2 accounts. You can use tables to work with live Basecamp data. You can use stored procedures provided by Basecamp connector to automate working with Basecamp data.

Basecamp 3 Data Model

Basecamp 3 Data Model describes the schemas available to connect to Basecamp 3 accounts. You can use tables to work with live Basecamp data. You can use stored procedures provided by Basecamp connector to automate working with Basecamp data.

Basecamp 2 Data Model

This section documents the Tables, Views, and Stored Procedures available to connect to Basecamp 2 accounts.

Tables

The connector models the data in Basecamp as a list of tables in a relational database that can be queried using standard SQL statements.

Basecamp Connector Tables

Accesses

Retrieve, grant, and deny access permissions to Projects and Calendars on Basecamp.

Table Specific Information

Select

You need to specify a ProjectId or CalendarId to retrieve Accesses. These columns are also the only columns supported by Basecamp as filter criteria; they can be used with the '=' operator.

SELECT * FROM Accesses WHERE ProjectId = '11111111'
SELECT * FROM Accesses WHERE CalendarId = '11111111'

Insert

You can give someone access to a Project or Calendar using their Email Addresses or Ids (if they are part of the organization already).

INSERT INTO Accesses (ProjectId, EmailAddresses) VALUES ('11111111', 'someone@example.com, someoneelse@example.com')

INSERT INTO Accesses (CalendarId, Ids) VALUES ('11111111', '123456789, 987654321')

Update

UPDATE is not a supported operation for the table Accesses.

Delete

To revoke access to someone from a Project or Calendar, specify the ProjectId or CalendarId and the Person's Id.

DELETE FROM Accesses WHERE ProjectId = '11111111' AND Id = '123456789'
DELETE FROM Accesses WHERE CalendarId = '11111111' AND ID = '123456789'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CalendarEvents

Retrieve, create, update, and delete Calendar Events on Basecamp.

Table Specific Information

Select

Basecamp supports only a limited subset of columns to be used as filter criteria in the WHERE clause. All Boolean columns for this table can be used with the '=' operator. ProjectId, CalendarId, StartsAt, and EndsAt can also be used with the "=" operator. Additionally, the StartsAt column can be used with the '>=' operator.

The following query filters on a particular time range.

SELECT * FROM CalendarEvents WHERE StartsAt = '2016-01-01' AND EndsAt = '2016-02-01'

The following queries get CalendarEvents for a certain Project or Calendar.

SELECT * FROM CalendarEvents WHERE ProjectId = '123456768'
SELECT * FROM CalendarEvents WHERE CalendarId = '123456768'

Insert

Specify the ProjectId or CalendarId and StartsAt when creating a new CalendarEvent.

INSERT INTO CalendarEvents (ProjectId, Summary, Description, AllDay, StartsAt) VALUES ('12345678', 'My single, all-day Project event', 'Details to follow', 'true', 2016-02-01)

Update and Delete

Specify the ProjectId or CalendarId and the CalendarEvent ID when updating or deleting CalendarEvents.

UPDATE CalendarEvents SET Summary = 'Updated, two-day Project event', Description = 'Details to follow', AllDay = 'true', StartsAt = '2016-01-01', EndsAt = '2016-01-03' WHERE ProjectId = '12345678' AND Id = '12345678'

DELETE FROM CalendarEvents WHERE ProjectId = '12345678' AND ID = '12345678'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Calendars

Retrieve, create, update, and delete Calendars on Basecamp.

Table Specific Information

Select

Select all from Calendars or specify an ID to retrieve more detail on a certain Calendar (Id is the only column supported as search criteria by Basecamp).

SELECT * FROM Calendars WHERE ID = '123456789'

Insert

Specify (at least) the Name property to create a new Calendar under the organization.

INSERT INTO Calendars (Name) VALUES ('New Test Calendar')

Update and Delete

Specify a Calendar ID to update or delete Calendars. Set the Name property again if you are updating.

UPDATE Calendars SET Name = 'This is a new name for the Test Calendar.' WHERE Id = '123456789'

DELETE FROM Calendars WHERE ID = '123456789'

Columns

Documents

Retrieve, create, update, and delete Documents on Basecamp.

Table Specific Information

Select

You can retrieve all Documents (for all Projects), select Documents belonging to a certain Project only, or specify a Project and a certain Document at the same time (ProjectId and ID are the only columns supported as search criteria by Basecamp).

You can also sort Documents by UpdatedAt, CreatedAt, or Title.

SELECT * FROM Documents WHERE ProjectId = '11111111' AND Id = '1234567689'

SELECT * FROM Documents ORDER BY UpdatedAt

SELECT * FROM Documents ORDER BY Title DESC

Insert

To add a new Document to a major Project, specify the ProjectId and provide the Document's title or some Content.

INSERT INTO Documents (ProjectId, Title, Content) VALUES ('11111111', 'Memo', 'This is our document content')

Update and Delete

Specify the ProjectId and the Document ID to update or delete a Document.

UPDATE Documents SET Title = 'Updated Memo3', Content = 'Updated Content of Memo.' WHERE ProjectId = '11111111' AND Id = '1234567689'

DELETE FROM Documents WHERE ProjectId = '11111111' AND ID = '12345678'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

People

Retrieve and delete People on Basecamp.

Table Specific Information

Select

Retrieve all People from the organization, specify an ID to get more info, or see who has been deleted (admin access is required). ID and Trashed are the columns supported by Basecamp as search criteria.

SELECT * FROM People WHERE Id = '123456789'

SELECT * FROM People WHERE Trashed = true

INSERT

New People can be invited directly to Projects via the Accesses table.

Update

UPDATE is not a supported operation for the table People.

Delete

Specify the Person's ID to delete the record from the organization.

DELETE FROM People WHERE ID = '123456789'

Columns

Projects

Retrieve, create, update, and delete Projects on Basecamp.

Table Specific Information

Select

You can retrieve all active Projects from the organization, filter records by Drafts and Archived Projects, or simply set a Project ID for more information. Drafts, Archived, and ID are the columns supported as search criteria by Basecamp.

SELECT * FROM Projects WHERE Drafts = True
SELECT * FROM Projects WHERE Advanced = True
SELECT * FROM Projects WHERE ID = '11111111'

Insert

Specify the Name and Description properties at a minimum to create a new Project.

INSERT INTO Projects (Name, Description) VALUES ('Test Proj', 'This is a new test project.')

Update and Delete

Simply set the Project ID to update or delete a project you have access to.

UPDATE Projects SET Name = 'Updated Proj', Description = 'This is an updated test project.' WHERE Id = '11111111'

DELETE FROM Projects WHERE ID = '11111111'

Columns

Views

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Basecamp Connector Views

Attachments

Retrieve Attachments on Basecamp.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Comments

Retrieve, create, update, and delete Calendars on Basecamp.

Table Specific Information

Select

You can retrieve all active Comments on the platform, and any field of the table can filter the result.

SELECT * FROM Comments WHERE ProjectId = '1111111' AND ItemType = 'Message'

Columns

Events

Retrieve activity Events on Basecamp.

Table Specific Information

Select

You can retrieve all activity Events or filter data by the created date. The columns and operators supported by Basecamp as search criteria are shown below.

For example, the following query retrieves activity occurring after the specified date for the specified person:

SELECT * FROM Events WHERE CreatedAt >= '2016-01-01' AND PersonId = '1111111'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Forwards

Retrieve Forwards on Basecamp.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ToDoLists

Retrieve ToDoLists on Basecamp.

Table Specific Information

Select

The following columns are supported by Basecamp as search criteria:

• ProjectId
• Id
• PersonId
• Completed
• Trashed
• CreatedAt

CreatedAt can be used with the '>' or '>=' operators.

The following query retrieves only completed ToDoLists belonging to a certain Project since a certain date.

SELECT * FROM ToDoLists WHERE ProjectId = '11111111' AND Completed = True AND CreatedAt > '2016-01-01'M

The following query retrieves a single ToDoList from a certain Project.

SELECT * FROM ToDoLists WHERE ProjectId = '11111111' AND ID = '123456789'

The following query retrieves ToDoLists that are assigned to a certain Person.

SELECT * FROM ToDoLists WHERE PersonId = '11111111'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ToDos

Retrieve ToDos on Basecamp.

Table Specific Information

Select

The following are columns supported as search criteria by Basecamp:

• ProjectId
• ToDoListId
• Id
• Completed
• Remaining
• Trashed
• DueAt
• DueOn

DueAt and DueOn can be used with the '>' or'>=' operators.

The following query retrieves only completed ToDos belonging to a certain Project.

SELECT * FROM ToDos WHERE ProjectId = '11111111' AND Completed = True

The following query filters ToDos that are due since a certain date.

SELECT * FROM ToDos WHERE ProjectId = '11111111' AND DueAt > '2016-01-01'

The following query lists the remaining ToDos that belong to a ToDo list.

SELECT * FROM ToDos WHERE ProjectId = '11111111' AND ToDoListId = '123456789' AND Remaining = true

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Topics

Retrieve Topics on Basecamp.

Table Specific Information

Select

You can retrieve all active Topics, sort Topics that belong to a Project, or only list archived Topics (ProjectId and Archived are the only columns supported as search criteria by Basecamp).

SELECT * FROM Topics WHERE ProjectId = '1111111' ORDER BY UpdatedAt ASC

SELECT * FROM Topics WHERE ProjectId = '1111111' AND Archived = True

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Stored Procedures

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Basecamp.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Basecamp, along with an indication of whether the procedure succeeded or failed.

Basecamp Connector Stored Procedures

GetOAuthAccessToken

Gets the OAuth access token from Basecamp.

Input

Result Set Columns

GetOAuthAuthorizationURL

Gets the Basecamp authorization URL. Access the URL returned in the output in a Web browser. This requests the access token that can be used as part of the connection string to Basecamp.

Input

Result Set Columns

PublishProject

Will publish (activate) a project created from a template (draft project).

Input

Result Set Columns

RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with various Basecamp services.

Input

Result Set Columns

StarProject

Will star a specified project.

Input

Result Set Columns

UnstarProject

Will unstar a specified project.

Input

Result Set Columns

Basecamp 3 Data Model

This section documents the Tables, Views, and Stored Procedures available to connect to Basecamp 3 accounts.

Tables

The connector models the data in Basecamp as a list of tables in a relational database that can be queried using standard SQL statements.

Basecamp Connector Tables

CalendarEvents

Retrieve calendar events on Basecamp 3.

Table Specific Information

You can query Calendar Events in the specified project.

Select

The following columns are supported as search criteria by Basecamp:

• ProjectId
• CalendarId
• Status (by default only active events are retrieved)

If ProjectId is not specified in the query, the default ProjectId will be used.

SELECT * FROM CalendarEvents WHERE ProjectId = '11111111' AND CalendarId = '22222222' AND Status = 'active'

You can also retrieve a single calendar event by specifying the Id.

SELECT * FROM CalendarEvents WHERE  ProjectId = '11111111' AND ID = '33333333'

Insert

You can insert a new Calendar Event by providing the ProjectId, CalendarId, StartsAt, EndsAt, and Summary as required parameters.

INSERT INTO CalendarEvents (ProjectId, CalendarId, Summary, StartsAt, EndsAt, Description, Notify, ParticipantsIds ) VALUES ( '1111111', '2222222', 'summary test', '2017-11-11 09:00Z', '2017-11-11 11:00Z', 'description', 'false', '3333333, 4444444')

Update

You can also change the Calendar Event by providing the ProjectId and event Id.

UPDATE CalendarEvents SET Summary = 'Updated summary',  StartsAt =  '2017-11-12', EndsAt = '2017-11-12', AllDay = 'true' WHERE ProjectId = '11111111' AND ID = '1234567689'

Delete

Calendar Events cannot be deleted.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Comments

Retrieve comments on a Basecamp 3 recording.

Table Specific Information

Select

The columns in Comments are filtered client side. You can either get all comments for a recording by specifying the RecordingId or you can get a certain comment by specifying its Id.

If ProjectId is not specified in the query, the default ProjectId will be used.

SELECT * FROM Comments WHERE ProjectId = '11111111' AND RecordingId = '222222222' AND Content = 'Exempli gratia' AND CreatedAt < '2017-04-18T15:53:38.040-06:00'

You can also retrieve a single comment by specifying the ID (given that you've specified the ProjectId in the connection)

SELECT * FROM Comments WHERE ID = '1234567'

Insert

You can insert a new comment, by providing the required ProjectId, RecordingId, and Content.

INSERT INTO Comments (Content, ProjectId, RecordingId) VALUES ('Very informative document. Thanks!', 114857506, 392946856)

Update

You can change the columns that are not read-only by providing the Id, ProjectId, and new values.

UPDATE Comments SET Content = 'I like this better now' WHERE ProjectId = '18061798' AND ID = '1308746423'

Delete

Comments cannot be deleted (unsupported by the API).

Columns

Documents

Retrieve, create, update, and delete Documents on Basecamp 3.

Table Specific Information

Select

You can retrieve all documents belonging to a certain Project and Vault only, or specify a Project and a certain Document at the same time (ProjectId, VaultId and ID are the only columns supported as search criteria by Basecamp).

If ProjectId is not specified in the query, the default ProjectId will be used.

SELECT * FROM Documents WHERE ProjectId = '11111111' AND VaultId = '222222222'

SELECT * FROM Documents WHERE ProjectId = '11111111' AND ID = '1234567689'

Insert

To add a new document to a major project, you need to specify the ProjectId, Vaultid, Title, Content, and Status (optional).

INSERT INTO Documents (ProjectId, Vaultid, Title, Content, Status) VALUES ('11111111', '222222222', 'Memo', 'This is our document content', 'active')

Update

Specify the ProjectId and the document ID to update a document.

UPDATE Documents SET Title = 'Updated Memo3', Content = 'Updated Content of Memo.' WHERE ProjectId = '11111111' AND ID = '1234567689'

Delete

DELETE is not supported for this table.

Columns

Messages

Retrieve messages on Basecamp 3.

Table Specific Information

You can query messages posted on the message board of the specified project.

Select

The columns supported as search criteria by Basecamp are ProjectId, MessageBoardId.

If ProjectId is not specified in the query, the default ProjectId will be used.

SELECT * FROM Messages WHERE ProjectId = '11111111' AND MessageBoardId = '22222222'

You can also retrieve a single message by specifying the Id.

SELECT * FROM Messages WHERE  ProjectId = '11111111' AND ID = '33333333'

Insert

You can insert a new message, by providing the required ProjectId, MessageBoardId, and Subject in addition to the message related parameters.

INSERT INTO Messages (ProjectId, MessageBoardId, Content, Subject, Status ) VALUES ('1111111', '2222222', 'Content test', 'Subject test',  'active')

Update

You can also update messages by providing the ProjectId and message Id.

UPDATE Messages SET subject = 'Updated subject', content = 'Updated content'  WHERE ProjectId = '11111111' AND ID = '1234567689'

Delete

Messages cannot be deleted.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Projects

Retrieve, create, update, and delete Projects on Basecamp 3.

Table Specific Information

Select

You can retrieve all active Projects from the organization, or set a project ID to retrieve a single project. ID is the only column supported as search criteria by Basecamp.

SELECT * FROM Projects WHERE ID = '11111111'

Insert

Specify at least the Name and optionally Description properties to create a new Project.

INSERT INTO Projects (Name, Description) VALUES ('Test Proj', 'This is a new test project.')

Update and Delete

Set the Project ID to update or delete a project you have access to.

UPDATE Projects SET Name = 'Updated Project', Description = 'This is an updated test project.' WHERE Id = '11111111'

DELETE FROM Projects WHERE ID = '11111111'

Columns

ToDoLists

Retrieve ToDoLists on Basecamp 3.

Table Specific Information

Select

The following columns are supported by Basecamp as search criteria:

• ProjectId
• ToDoSetId and Status
• ProjectId and Id

ToDoSetId can be retrieved in the Projects table.

The following query retrieves only completed to-do lists belonging to a certain Project and a certain Status. If ProjectId is not specified in the query, the default ProjectId will be used.

SELECT * FROM ToDoLists WHERE ProjectId = '11111111' AND ToDoSetId = '2222222' AND Status = 'active'

The following query retrieves a single ToDoList from a certain Project.

SELECT * FROM ToDoLists WHERE ProjectId = '11111111' AND ID = '123456789'

Insert

Specify the Name, ProjectId, Description (optional), and the ToDoSetId to insert a to-do list.

INSERT INTO ToDoLists (Name, Description, ProjectId, ToDoSetId ) VALUES ('Name', 'desc', '11111111', '2222222')

Update

Basecamp allows changing the name and description of the to-do list, provided that the ProjectId and to-do list ID are included in the query:

UPDATE ToDoLists SET Name = 'Updated Name', Description = 'Updated desc' WHERE ProjectId = '11111111' AND ID = '1234567689'

Delete

To-do lists cannot be deleted.

Columns

ToDos

Retrieve ToDos on Basecamp 3.

Table Specific Information

Select

The following columns are supported as search criteria by Basecamp:

• ProjectId
• ToDoListId
• Id
• Completed
• Status

By default, only active to-dos will be retrieved.

If ProjectId is not specified in the query, the default ProjectId will be used.

SELECT * FROM ToDos WHERE ProjectId = '11111111' AND ToDoListId = '222222222' AND Status = 'active' AND Completed = True

You can also retrieve a single to-do by specifying the Id.

SELECT * FROM ToDos WHERE ProjectId = '11111111' AND ID = '33333333'

Insert

You can insert a new to-do by providing the required ProjectId, ToDoListId, and Content in addition to the to-do related parameters.

INSERT INTO ToDos (Content, Description, DueOn, StartsOn, AssigneeIds, ProjectId, ToDoListId ) VALUES ('What to do', 'description', '2017-11-11', '2017-11-12', '333333333', '1111111111', '22222222')

Update

You can change the columns that are not read-only by providing the ProjectId, to-do Id, and new values.

UPDATE ToDos SET Content = 'Updated content' WHERE ProjectId = '11111111' AND ID = '1234567689'

You can change the Completed state of the to-do by providing the ProjectId and to-do Id. The Completed column can only be updated standalone, and other columns cannot be updated in the same query.

UPDATE ToDos SET Completed = True WHERE ProjectId = '11111111' AND ID = '1234567689'

Delete

To-dos cannot be deleted.

Columns

Vaults

Retrieve Vaults(folders) on Basecamp 3 folders.

Table Specific Information

All projects have a primary Vault (folder). Additional Vaults may be nested under the primary Vault or any child Vault.

Select

The columns supported as search criteria by Basecamp are ProjectId, VaultId, and Id. If ProjectId is not specified in the query, the default ProjectId will be used.

SELECT * FROM Vaults WHERE  ProjectId = '11111111' AND VaultId = '222222222'

You can also retrieve a single To-do by specifying the Id.

SELECT * FROM Vaults WHERE  ProjectId = '11111111' ID = '333333333'

INSERT

You can insert a new Vault by providing the ProjectId, VaultId, and Title, which are required parameters.

INSERT INTO Vaults (Title, ProjectId, Vaultid) VALUES ('InsertedVault', '11111111', '222222222')

Update

You can update the Vault title by providing the ProjectId, VaultId, and the new title.

UPDATE Vaults SET Title = 'Updated title' WHERE ProjectId = '11111111' AND ID = '333333333'

Delete

Vaults cannot be deleted.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Views

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Basecamp Connector Views

Assignments

Retrieve Assignments on Basecamp 3.

Columns

Calendars

Retrieve calendars on Basecamp 3.

Table Specific Information

There is only one Calendar (Schedule) in each Basecamp project.

Select

Retrieve more detail on a specific Calendar (ProjectId and ID are the only columns supported as search criteria by Basecamp). If ProjectId is not specified in the query, the default ProjectId will be used.

SELECT * FROM Calendars WHERE ProjectId = '111111111' AND ID = '123456789'

Columns

Events

Retrieve activity Events on Basecamp 3.

View Specific Information

An event is created any time a recording changes.

Select

You can retrieve all activity Events in the specified project and recording. The columns supported as search criteria by Basecamp are ProjectId and RecordingId.

If ProjectId is not specified in the query, the default ProjectId will be used.

SELECT * FROM Events WHERE ProjectId = '1111111' AND RecordingId = '22222222'

Columns

People

Retrieve People on Basecamp 3.

View Specific Information

Select

Retrieve all People visible to the current user, list people by Project, or specify an ID to get more information. ID and ProjectId are the columns supported by Basecamp as search criteria.

SELECT * FROM People WHERE Id = '123456789'

SELECT * FROM People WHERE ProjectId = '5168618'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ProjectTools

Retrieve project tools on Basecamp 3.

Table Specific Information

Select

No filters are supported server side for this table. All criteria will be handled client side within the connector.

For example, the following queries are processed server side:

SELECT * FROM ProjectTools

Columns

Recordings

Retrieve recordings on Basecamp 3 folders.

View Specific Information

Retrieve most of the data structures in the Basecamp 3, which are represented as "Recordings."

Select

The columns supported as search criteria by Basecamp are Type (required parameter), Bucket, and Status.

SELECT * FROM Recordings WHERE type = 'Upload' AND Bucket = '1111111, 22222222' AND Status = 'active'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Uploads

Retrieve Uploads on Basecamp 3.

Columns

Stored Procedures

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Basecamp.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Basecamp, along with an indication of whether the procedure succeeded or failed.

Basecamp Connector Stored Procedures

GetOAuthAccessToken

Gets the OAuth access token from Basecamp.

Input

Result Set Columns

GetOAuthAuthorizationURL

Gets the Basecamp authorization URL. Access the URL returned in the output in a Web browser. This requests the access token that can be used as part of the connection string to Basecamp.

Input

Result Set Columns

RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with various Basecamp services.

Input

Result Set Columns

System Tables

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for Basecamp:

• sys_catalogs: Lists the available databases.
• sys_schemas: Lists the available schemas.
• sys_tables: Lists the available tables and views.
• sys_tablecolumns: Describes the columns of the available tables and views.
• sys_procedures: Describes the available stored procedures.
• sys_procedureparameters: Describes stored procedure parameters.
• sys_keycolumns: Describes the primary and foreign keys.
• sys_indexes: Describes the available indexes.

Data Source Tables

The following tables return information about how to connect to and query the data source:

• sys_connection_props: Returns information on the available connection properties.
• sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries:

• sys_identity: Returns information about batch operations or single updates.

sys_catalogs

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns

sys_schemas

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas

Columns

sys_tables

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables

Columns

sys_tablecolumns

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Projects table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Projects'

Columns

sys_procedures

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures

Columns

sys_procedureparameters

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the RefreshOAuthAccessToken stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='RefreshOAuthAccessToken' AND Direction=1 OR Direction=2

Columns

sys_keycolumns

Describes the primary and foreign keys.

The following query retrieves the primary key for the Projects table:

SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Projects'

Columns

sys_foreignkeys

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'

Columns

sys_primarykeys

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

SELECT * FROM sys_primarykeys

Columns

sys_indexes

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

SELECT * FROM sys_indexes WHERE IsPrimary='false'

Columns

sys_connection_props

Returns information on the available connection properties and those set in the connection string.

When querying this table, the config connection string should be used:

jdbc:cdata:basecamp:config:

This connection string enables you to query this table without a valid connection.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns

sys_sqlinfo

Describes the SELECT query processing that the connector can offload to the data source.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.