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¶

provides an embedded OAuth application 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 Harmony 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.