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 theAccountId
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 toGETANDREFRESH
. You can useInitiateOAuth
to avoid repeating the OAuth exchange and manually setting theOAuthAccessToken
.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 setCallbackURL
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¶
Name | Description |
---|---|
Accesses | Retrieve, grant, and deny access permissions to Projects and Calendars on Basecamp. |
CalendarEvents | Retrieve, create, update, and delete Calendar Events on Basecamp. |
Calendars | Retrieve, create, update, and delete Calendars on Basecamp. |
Documents | Retrieve, create, update, and delete Documents on Basecamp. |
People | Retrieve and delete People on Basecamp. |
Projects | Retrieve, create, update, and delete Projects on Basecamp. |
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¶
Name | Type | ReadOnly | References | Description |
---|---|---|---|---|
Id [KEY] | String | True | The unique identifier of the person with access to the project or calendar. | |
Admin | Boolean | True | Boolean value to show if the current person is an admin or not. | |
AppUrl | String | True | The app URL. | |
AvatarUrl | String | True | The avatar URL of the person. | |
CanCreateProjects | Boolean | True | Whether the person can create projects or not. | |
CreatedAt | Datetime | True | The date and time when the record was created. | |
EmailAddress | String | True | The email address of the person with access to the project or calendar. | |
FullsizeAvatarUrl | String | True | The full-size avatar URL of the person. | |
IdentityId | String | True | The identity ID of the person with access to the project or calendar. | |
IsClient | Boolean | False | Is the returned user a client or not. | |
Name | String | True | The name of the person with access to the project or calendar. | |
Trashed | Boolean | False | Boolean value for deleted people. | |
UpdatedAt | Datetime | True | The date and time when the record was last updated. | |
URL | String | True | The URL of the person. | |
Ids | String | False | A comma separated list of user Ids to be granted access to a project or calendar. | |
EmailAddresses | String | False | A comma separated list of user email addresses to be granted access to a project or calendar. |
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.
Name | Type | Description |
---|---|---|
ProjectId | String | The Project ID associated with the access. |
CalendarId | String | The Calendar ID associated with the access. |
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¶
Name | Type | ReadOnly | References | Description |
---|---|---|---|---|
Id [KEY] | String | True | The unique identifier for the calendar event. | |
AllDay | Boolean | False | Whether the calendar event is an all-day event. | |
AppUrl | String | True | The app URL. | |
BucketAppUrl | String | True | The calendar event bucket app URL. | |
BucketColor | String | True | The calendar event bucket color. | |
BucketId | String | True | The calendar event bucket Id. | |
BucketName | String | True | The calendar event bucket name. | |
BucketType | String | True | The calendar event bucket type. | |
BucketUrl | String | True | The calendar event bucket URL. | |
CommentsCount | Integer | True | The number of comments on this calendar event. | |
CreatedAt | Datetime | True | The date and time when the calendar event was created. | |
CreatorAvatarUrl | String | False | Creator Avatar URL. | |
CreatorFullsizeAvatarUrl | String | False | Creator full-size avatar URL. | |
CreatorId | String | False | The ID of the creator of the calendar event. | |
CreatorName | String | False | The name of the creator of the calendar event. | |
Description | String | False | The description for the calendar event. | |
EndsAt | Datetime | False | The end date and time for the calendar event. | |
Past | Boolean | False | Boolean value for past calendar events. | |
Private | Boolean | False | Whether this calendar event is private. | |
RemindAt | Datetime | False | The date and time for the calendar event reminder. | |
StartsAt | Datetime | False | The start date and time for the calendar event. | |
SubscribersId | String | False | The ID of the subscriber to the calendar event. | |
SubscribersName | String | False | The name of the subscriber to the calendar event. | |
Summary | String | False | The summary for the calendar event. | |
Trashed | Boolean | False | Boolean value for deleted calendar events. | |
UpdatedAt | Datetime | True | The date and time when the calendar event was updated. | |
Url | String | True | The URL of the calendar event. |
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.
Name | Type | Description |
---|---|---|
ProjectId | String | The ID of the Project associated with the calendar event. |
CalendarId | String | The calendar ID associated with the calendar event. |
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¶
Name | Type | ReadOnly | References | Description |
---|---|---|---|---|
Id [KEY] | String | True | The unique identifier for the calendar. | |
AccessesAppUrl | String | True | The accesses app URL. | |
AccessesCount | Integer | True | The accesses count. | |
AccessesUpdatedAt | Datetime | True | The time when accesses were updated. | |
AccessesUrl | String | True | The accesses URL. | |
AppUrl | String | True | The app URL. | |
CalendarEventsCount | Integer | True | Number of calendar events. | |
CalendarEventsUpdatedAt | Datetime | True | The date and time when the calendar event was last updated. | |
CalendarEventsUrlsPast | String | True | URL of the past calendar event. | |
CalendarEventsUrlsUpcoming | String | True | The date and time when the calendar was last updated. | |
Color | String | True | The color of the calendar. | |
CreatedAt | Datetime | True | The date and time when the document was created. | |
CreatorAvatarUrl | String | False | Creator Avatar URL. | |
CreatorFullsizeAvatarUrl | String | False | Creator full-size avatar Url. | |
CreatorId | String | False | The ID of the creator of the calendar. | |
CreatorName | String | False | The name of the creator of the calendar. | |
Name | String | False | The name of the calendar. | |
UpdatedAt | Datetime | True | The date and time when the calendar was last updated. | |
Url | String | True | The URL of the calendar. |
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¶
Name | Type | ReadOnly | References | Description |
---|---|---|---|---|
Id [KEY] | String | True | The unique identifier for the document. | |
AppUrl | String | True | The app URL. | |
BucketAppUrl | String | True | The topic bucket app URL. | |
BucketColor | String | True | The to-do bucket color. | |
BucketId | String | True | The to-do bucket Id. | |
BucketName | String | True | The to-do bucket name. | |
BucketType | String | True | The to-do bucket type. | |
BucketUrl | String | True | The to-do bucket URL. | |
Content | String | False | The content of the document. | |
CreatedAt | Datetime | True | The date and time when the document was created. | |
CreatorAvatarUrl | String | False | Creator Avatar URL. | |
CreatorFullsizeAvatarUrl | String | False | Creator full-size avatar URL. | |
CreatorId | String | False | The ID of the creator of the document. | |
CreatorName | String | False | The name of the creator of the document. | |
LastUpdaterId | String | False | The ID of the person who last updated the document. | |
LastUpdaterName | String | False | The name of the person who last updated the document. | |
Private | Boolean | False | Whether this to-do is private. | |
SubscribersId | String | False | The ID of the subscriber to the document. | |
SubscribersName | String | False | The name of the subscriber to the document. | |
Title | String | False | The title of the document. | |
Trashed | Boolean | False | Boolean value for deleted to-dos. | |
UpdatedAt | Datetime | True | The date and time when the document was last updated. | |
URL | String | True | The URL of the document. |
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.
Name | Type | Description |
---|---|---|
ProjectId | String | The ID of the Project associated with the document. |
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¶
Name | Type | ReadOnly | References | Description |
---|---|---|---|---|
Id [KEY] | String | True | The unique identifier for the person. | |
AccountOwner | Boolean | True | Boolean value to show if the current person is the account owner. | |
Admin | Boolean | True | Boolean value to show if the current person is an admin or not. | |
AssignedTodosCount | Integer | True | Number of assigned to-dos. | |
AssignedTodosUpdatedAt | Datetime | True | The date and time when the record was updated. | |
AssignedTodosUrl | String | True | The URL of the assigned to-do. | |
AppUrl | String | True | The app URL. | |
AvatarUrl | String | True | The avatar URL of the person. | |
CalendarEventsTodayCount | Integer | True | Number of events for today. | |
CanCreateProjects | Boolean | True | Whether the person can create projects or not. | |
CreatedAt | Datetime | True | The date and time when the record was created. | |
EmailAddress | String | True | The email address of the person. | |
EventsAppUrl | String | True | The events app URL. | |
EventsCount | Integer | True | Number of events. | |
EventsUpdatedAt | Datetime | True | The date and time when the event was updated. | |
EventsUrl | String | True | The URL of the assigned to-do. | |
FullsizeAvatarUrl | String | True | The full-size avatar URL of the person. | |
HasBasecampSiblings | Boolean | False | Boolean value for Basecamp siblings. | |
IdentityId | String | True | The identity ID of the person. | |
Name | String | True | The name of the person. | |
OutstandingTodosCount | Integer | True | Number of outstanding to-dos. | |
Trashed | Boolean | False | Boolean value for deleted people. | |
UpdatedAt | Datetime | True | The date and time when the record was last updated. | |
Url | String | True | The URL of the person. |
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¶
Name | Type | ReadOnly | References | Description |
---|---|---|---|---|
Id [KEY] | String | True | The unique identifier of the project. | |
AccessesAppUrl | String | True | The accesses app URL. | |
AccessesCount | Integer | True | The accesses count. | |
AccessesUpdatedAt | Datetime | True | Time when accesses were updated. | |
AccessesUrl | String | True | The accesses URL. | |
AppUrl | String | True | The app URL. | |
Archived | Boolean | False | When user archives a project, this value will be set to true. | |
AttachmentsAppUrl | String | True | The attachments app URL. | |
AttachmentsCount | Integer | True | The attachments count. | |
AttachmentsUpdatedAt | Datetime | True | Time when attachments were updated. | |
AttachmentsUrl | String | True | The attachments URL. | |
CalendarEventsAppUrl | String | True | The calendar events app URL. | |
CalendarEventsCount | Integer | True | The calendar events count. | |
CalendarEventsUpdatedAt | Datetime | True | Time when calendar events were updated. | |
CalendarEventsUrl | String | True | The calendar events URL. | |
Color | String | True | Color. | |
CreatedAt | Datetime | True | The creation time of the project. | |
CreatorAvatarUrl | String | False | Creator Avatar URL. | |
CreatorFullsizeAvatarUrl | String | False | Creator full-size Avatar URL. | |
CreatorId | String | False | The ID of the creator. | |
CreatorName | String | False | The name of the creator. | |
Description | String | False | The description of the project. | |
DocumentsAppUrl | String | True | The documents app URL. | |
DocumentsCount | Integer | True | The documents count. | |
DocumentsUpdatedAt | Datetime | True | Time when documents were updated. | |
DocumentsUrl | String | True | The documents URL. | |
Drafts | Boolean | False | Boolean value for a draft project. | |
ForwardsAppUrl | String | True | The forwards app URL. | |
ForwardsCount | Integer | True | The forwards count. | |
ForwardsUpdatedAt | Datetime | True | Time when forwards were updated. | |
ForwardsUrl | String | True | The forwards URL. | |
IsClientProject | Boolean | False | Whether the project is related to clients or not. | |
LastEventAt | Datetime | False | Time when the project was last modified. | |
Name | String | False | The name of the project. | |
Starred | Boolean | False | Boolean value for a starred project. | |
Template | Boolean | False | Boolean value for a template project. | |
TodolistsAppUrl | String | True | The todolists app URL. | |
TodolistsCompletedCount | Integer | True | The todolists completed count. | |
TodolistsRemainingCount | Integer | True | The todolists remaining count. | |
TodolistsUpdatedAt | Datetime | True | Time when todolists were updated. | |
TodolistsUrl | String | True | The todolists URL. | |
TopicsAppUrl | String | True | The topics app URL. | |
TopicsCount | Integer | True | The topics count. | |
TopicsUpdatedAt | Datetime | True | Time when topics were updated. | |
TopicsUrl | String | True | The topics URL. | |
Trashed | Boolean | False | Boolean value for a trashed project. | |
UpdatedAt | Datetime | True | Time when the project was updated. | |
URL | String | True | The URL of the project. |
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¶
Name | Description |
---|---|
Attachments | Retrieve Attachments on Basecamp. |
Comments | Retrieve, create, update, and delete Calendars on Basecamp. |
Events | Retrieve activity Events on Basecamp. |
Forwards | Retrieve Forwards on Basecamp. |
ToDoLists | Retrieve ToDoLists on Basecamp. |
ToDos | Retrieve ToDos on Basecamp. |
Topics | Retrieve Topics on Basecamp. |
Attachments¶
Retrieve Attachments on Basecamp.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the attachment. | |
Key | String | The key of the attachment. | |
Name | String | The name of the attachment. | |
ByteSize | Int | The size of the attachment in bytes. | |
CreatedAt | Datetime | The date when the attachment was created. | |
UpdatedAt | Datetime | The date when the attachment was last updated. | |
Url | String | The Url of the attachment. | |
AppUrl | String | The AppUrl of the attachment. | |
ThumbnailUrl | String | The Thumbnail Url of the attachment. | |
Private | Boolean | Indicates if the attachemtn is private. | |
Trashed | Boolean | Indicates if the attachment is trashed. | |
Tags | String | The tags of the attachment. | |
CreatorId | String | The ID of the attachment creator. | |
CreatorName | String | The name of the attachment creator. | |
CreatorAvatarUrl | String | The avatar URL of the attachment creator. | |
CreatorFullsizeAvatarUrl | String | The URL of the full sized avatar of the attachment creator. | |
AttachableId | String | The ID of the attachable. | |
AttachableType | String | The type of the attachable. | |
AttachableUrl | String | The Url of the attachable. | |
AttachableAppUrl | String | The AppUrl of the attachable. |
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.
Name | Type | Description |
---|---|---|
ProjectId | String |
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¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The unique identifier for the comment. | |
ProjectId [KEY] | String | The unique identifier for the project where the comment is contained. | |
ItemId | String | The unique identifier for the topic containing the comment. | |
ItemType | String | Type of the object containing the comment. Valid values are: Message, Document, Forward, CalendarEvent, Upload, Todolist, Todo. | |
AttachmentsAggregate | String | The attachments related to the comment. | |
Content | String | The content of the comment. | |
Private | Boolean | Privacy of the comment | |
Trashed | Boolean | If the comment was trashed or not. | |
CreatedAt | Datetime | The date and time when the document was created. | |
CreatorAvatarUrl | String | Creator Avatar URL. | |
CreatorFullsizeAvatarUrl | String | Creator full-size avatar Url. | |
CreatorId | String | The ID of the creator of the comment. | |
CreatorName | String | The name of the creator of the comment. | |
UpdatedAt | Datetime | The date and time when the comment was last updated. |
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.
Column | Operators |
---|---|
ProjectId | = |
PersonId | = |
CreatedAt | >, >= |
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¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The unique identifier of the event. | |
Action | String | The action description for the event. | |
BucketAppUrl | String | The calendar events app URL. | |
BucketColor | String | The bucket event color. | |
BucketId | String | The bucket event Id. | |
BucketName | String | The bucket event name. | |
BucketType | String | The bucket event type. | |
BucketUrl | String | The bucket event URL. | |
CreatedAt | Datetime | The date and time when the event was created. | |
CreatorAvatarUrl | String | Creator Avatar URL. | |
CreatorFullsizeAvatarUrl | String | Creator full-size avatar URL. | |
CreatorId | String | The ID of the event creator. | |
CreatorName | String | The name of the event creator. | |
EventableAppUrl | String | Eventable App URL. | |
EventableId | String | Eventable Id. | |
EventableType | String | Eventable type. | |
EventableUrl | String | Eventable URL. | |
Excerpt | String | The type of excerpt for this event. | |
HtmlUrl | String | The HTML URL of the event. | |
Private | Boolean | Whether the event is private. | |
RawExcerpt | String | The raw excerpt type for this event. | |
Summary | String | The summary for the event. | |
Target | String | The target project of this event. | |
UpdatedAt | Datetime | The date and time when the event was last updated. | |
URL | String | The URL of the event. |
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.
Name | Type | Description |
---|---|---|
ProjectId | String | |
PersonId | String |
Forwards¶
Retrieve Forwards on Basecamp.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the forward. | |
Subject | String | The subject of the forward. | |
From | String | ||
CreatedAt | Datetime | The date when the forward was created. | |
UpdatedAt | Datetime | The date when the forward was last updated. | |
Url | String | The Url of the forward. | |
AppUrl | String | The AppUrl of the forward. | |
Private | Boolean | Indicates if the forward is private. | |
Trashed | Boolean | Indicates if the forward is trashed. | |
BucketId | String | The bucket ID of the forward. | |
BucketName | String | The bucket name of the forward. | |
BucketType | String | The bucket type of the forward. | |
BucketUrl | String | The bucket Url of the forward. | |
BucketAppUrl | String | The bucket app URL of the forward. |
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.
Name | Type | Description |
---|---|---|
ProjectId | String |
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¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The unique identifier of the topic. | |
AppUrl | String | The app URL. | |
AssignedTodos | String | Assigned to-dos. | |
BucketAppUrl | String | The topic bucket app URL. | |
BucketColor | String | The to-do bucket color. | |
BucketId | String | The to-do bucket Id. | |
BucketName | String | The to-do bucket name. | |
BucketType | String | The to-do bucket type. | |
BucketUrl | String | The to-do bucket URL. | |
Completed | Boolean | Whether the to-do list is completed. | |
CompletedCount | Integer | Number of completed to-dos. | |
CreatedAt | Datetime | The date and time when the record was created. | |
CreatorAvatarUrl | String | Creator Avatar URL. | |
CreatorFullsizeAvatarUrl | String | Creator full-size avatar URL. | |
CreatorId | String | The ID of the creator. | |
CreatorName | String | The name of the creator. | |
Description | String | The to-do list description. | |
Name | String | The name of the to-do list. | |
Position | Integer | Position number of the current to-do list. | |
Private | Boolean | Whether the to-do list is private. | |
RemainingCount | Integer | Number of unfinished to-dos. | |
SubscribersId | String | The ID of the subscriber. | |
SubscribersName | String | The name of the subscriber. | |
ToDos | String | To-dos list. | |
Trashed | Boolean | Boolean value for deleted to-do lists. | |
UpdatedAt | Datetime | The date and time when the record was last updated. | |
URL | String | The URL of the to-do list. |
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.
Name | Type | Description |
---|---|---|
ProjectId | String | |
PersonId | String |
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¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The unique identifier of the topic. | |
AppUrl | String | The app URL. | |
AssigneeId | String | Id of the person assigned to. | |
AssigneeName | String | Name of the person assigned to. | |
AssigneeType | String | Type of assignee. | |
CommentsCount | Integer | Number of comments on this to-do. | |
Completed | Boolean | Whether the to-do is completed. | |
Content | String | Description of the to-do. | |
CreatedAt | Datetime | The date and time when the record was created. | |
CreatorAvatarUrl | String | Creator Avatar URL. | |
CreatorFullsizeAvatarUrl | String | Creator full-size avatar URL. | |
CreatorId | String | The ID of the creator. | |
CreatorName | String | The name of the creator. | |
DueAt | Datetime | The date and time when the to-do is set due. | |
DueOn | Datetime | The date and time when the to-do is set due. | |
Position | Integer | Position number of the current to-do on the list. | |
Private | Boolean | Whether this to-do is private. | |
Remaining | Boolean | Boolean value for remaining to-dos. | |
SubscribersId | String | The ID of the subscriber. | |
SubscribersName | String | The name of the subscriber. | |
ToDoList | String | To-do list. | |
ToDoListId | String | The to-do list Id. | |
Trashed | Boolean | Boolean value for deleted to-dos. | |
UpdatedAt | Datetime | The date and time when the record was last updated. | |
URL | String | The URL of the to-do. |
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.
Name | Type | Description |
---|---|---|
ProjectId | String |
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¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The unique identifier of the topic. | |
Archived | Boolean | Boolean value for archived topics. | |
Attachments | Integer | The number of attachments to the topic. | |
BucketAppUrl | String | The topic bucket app URL. | |
BucketColor | String | The bucket topic color. | |
BucketId | String | The bucket topic Id. | |
BucketName | String | The bucket topic name. | |
BucketType | String | The bucket topic type. | |
BucketUrl | String | The bucket topic URL. | |
CreatedAt | Datetime | The date and time when the record was created. | |
Excerpt | String | The excerpt from the latest comment on the topic. | |
LastUpdaterId | String | The ID of the person who made the last comment. | |
LastUpdaterName | String | The name of the person who last updated the topic. | |
Private | Boolean | Whether the topic is private. | |
Title | String | The title of the topic. | |
TopicableAppUrl | String | The URL of the topicable Basecamp entity. | |
TopicableId | String | The ID of the topicable Basecamp entity. | |
TopicableType | String | The type of the topicable Basecamp entity. | |
TopicableUrl | String | The URL of the topicable Basecamp entity. | |
Trashed | Boolean | Boolean value for deleted topics. | |
UpdatedAt | Datetime | The date and time when the record was last updated. |
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.
Name | Type | Description |
---|---|---|
ProjectId | String |
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¶
Name | Description |
---|---|
GetOAuthAccessToken | Gets the OAuth access token from Basecamp. |
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. |
PublishProject | Will publish (activate) a project created from a template (draft project). |
RefreshOAuthAccessToken | Refreshes the OAuth access token used for authentication with various Basecamp services. |
StarProject | Will star a specified project. |
UnstarProject | Will unstar a specified project. |
GetOAuthAccessToken¶
Gets the OAuth access token from Basecamp.
Input¶
Name | Type | Required | Description |
---|---|---|---|
AuthMode | String | True | WEB or APP. The Basecamp authentication mode. Default is APP. The default value is APP. |
CallbackUrl | String | False | The URL the user will be redirected to after authorizing your application. |
Verifier | String | False | The verifier token returned by Basecamp. Required for only the Web AuthMode. |
State | String | False | This field indicates any state that may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to Basecamp authorization server and back. Uses include redirecting the user to the correct resource in your site, using nonces, and mitigating cross-site request forgery. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
OAuthAccessToken | String | The OAuth token. |
OAuthRefreshToken | String | The OAuth refresh token. |
ExpiresIn | String | The remaining lifetime for the access token in seconds. |
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¶
Name | Type | Required | Description |
---|---|---|---|
CallbackUrl | String | False | The URL that Basecamp will return to after the user has authorized your app. |
State | String | False | This field indicates any state that may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to Basecamp authorization server and back. Uses include redirecting the user to the correct resource in your site, using nonces, and mitigating cross-site request forgery. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
URL | String | The URL to be entered into a Web browser to obtain the verifier token and authorize the data provider with. |
PublishProject¶
Will publish (activate) a project created from a template (draft project).
Input¶
Name | Type | Required | Description |
---|---|---|---|
ProjectId | String | True | ID of the project. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Project creation result. |
RefreshOAuthAccessToken¶
Refreshes the OAuth access token used for authentication with various Basecamp services.
Input¶
Name | Type | Required | Description |
---|---|---|---|
OAuthRefreshToken | String | True | The refresh token returned with the previous access token. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
OAuthAccessToken | String | The authentication token returned from Basecamp. This can be used in subsequent calls to other operations for this particular service. |
OAuthRefreshToken | String | A token that may be used to obtain a new access token. |
ExpiresIn | String | The remaining lifetime on the access token. |
StarProject¶
Will star a specified project.
Input¶
Name | Type | Required | Description |
---|---|---|---|
ProjectId | String | True | ID of the project. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Project star result. |
UnstarProject¶
Will unstar a specified project.
Input¶
Name | Type | Required | Description |
---|---|---|---|
ProjectId | String | True | ID of the project. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Project unstar result. |
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¶
Name | Description |
---|---|
CalendarEvents | Retrieve calendar events on Basecamp 3. |
Comments | Retrieve comments on a Basecamp 3 recording. |
Documents | Retrieve, create, update, and delete Documents on Basecamp 3. |
Messages | Retrieve messages on Basecamp 3. |
Projects | Retrieve, create, update, and delete Projects on Basecamp 3. |
ToDoLists | Retrieve ToDoLists on Basecamp 3. |
ToDos | Retrieve ToDos on Basecamp 3. |
Vaults | Retrieve Vaults(folders) on Basecamp 3 folders. |
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¶
Name | Type | ReadOnly | References | Description |
---|---|---|---|---|
Id [KEY] | Long | True | The unique identifier of the calendar event. | |
ProjectId | Long | True | Projects.Id | The Project ID associated with the schedule. |
CalendarId | Long | True | Projects.CalendarId | The Calendar ID that the event belongs to. |
Status | String | True | The status of the calendar event. | |
Type | String | True | The type of the returned record. | |
CreatedAt | Datetime | True | The date and time when the calendar event was created. | |
UpdatedAt | Datetime | True | The date and time when the calendar event was last updated. | |
Description | String | False | Provided description for the calendar event. | |
AllDay | Boolean | False | Indicates that the event is an all day event. | |
StartsAt | Datetime | False | The date and time when the calendar event starts. | |
EndsAt | Datetime | False | The date and time when the calendar event ends. | |
Summary | String | False | Provided summary for the calendar event. | |
ParticipantsIds | String | False | A list of comma separated participant ids. | |
ParticipantsNames | String | True | A list of comma separated participant names. | |
ParentTitle | String | True | Title of the parent. | |
ParentType | String | True | Type of the parent. | |
BucketName | String | True | Name of the containing bucket. | |
BucketType | String | True | Type of the containing bucket. | |
CreatorId | Long | True | People.Id | The ID of the creator. |
CreatorName | String | True | The name of the creator. | |
CommentsCount | Integer | True | Number of comments the document has. |
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.
Name | Type | Description |
---|---|---|
Notify | String | When set to true, will notify the participants about the event. |
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¶
Name | Type | ReadOnly | References | Description |
---|---|---|---|---|
Id [KEY] | String | True | The ID of the comment. | |
Content | String | False | The conent of the comment. | |
Title | String | True | The title of the comment. | |
Status | String | True | The status of the comment. | |
CreatedAt | Datetime | True | The time the comment was created. | |
UpdatedAt | Datetime | True | The time the comment was last updated. | |
InheritsStatus | Boolean | True | Whether this comment inherits its status or not. | |
URL | String | True | The URL of the comment. | |
VisibleToClients | Boolean | True | Whether this comment is visible to clients or not. | |
ProjectId | Long | False | Projects.Id | The ID of the project this comment is a child of. A project contains recordings. |
ProjectName | String | True | The name of the project this comment is a child of. | |
RecordingId | Long | False | Recordings.Id | The ID of the recording the comment is written on. A recording can be a comment, document, message, question:answer, schedule:entry, todo, todolist or upload. |
CommenterId | String | True | The ID of the commenter. | |
CommenterName | String | True | The name of the commenter. | |
CommenterIsAdmin | Boolean | True | Whether the commenter is an admin or not. | |
CommenterIsOwner | Boolean | True | Whether the commenter is the owner of the recording or not. | |
CommenterTitle | String | True | The commenter's title. | |
CommenterEmail | String | True | The email address of the commenter. | |
CommenterAvatarURL | String | True | The URL of the avatar of the commenter. | |
CommenterCompanyId | String | True | The company ID of the commenter. | |
CommenterCompanyName | String | True | The name of the company of the commenter. | |
AppURL | String | True | The URL of the app this comment belongs to. | |
BookmarkURL | String | True | The URL of the bookmark. | |
CommenterAttachableSGID | String | True | Te attachable SGID of the commenter. |
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¶
Name | Type | ReadOnly | References | Description |
---|---|---|---|---|
Id [KEY] | Long | True | The unique identifier for the document. | |
ProjectId | Long | True | Projects.Id | The ID of the Project associated with the document. |
VaultId | Long | True | Vaults.Id | The Vault ID associated with the document. |
Status | String | False | The status of the vault. | |
Title | String | False | The title of the document. | |
Content | String | False | The content of the Document | |
Type | String | True | The type of the returned record. | |
CreatedAt | Datetime | True | The date and time when the document was created. | |
UpdatedAt | Datetime | True | The date and time when the document was last updated. | |
ParentTitle | String | True | Title of the parent. | |
ParentType | String | True | Type of the parent. | |
BucketName | String | True | Name of the containing bucket. | |
BucketType | String | True | Type of the containing bucket. | |
CreatorId | Long | True | People.Id | The ID of the creator. |
CreatorName | String | True | The name of the creator. | |
CommentsCount | Integer | True | Number of comments the document has. |
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¶
Name | Type | ReadOnly | References | Description |
---|---|---|---|---|
Id [KEY] | Long | True | The unique identifier of the message. | |
ProjectId | Long | True | Projects.Id | The Project ID associated with the schedule. |
MessageBoardId | Long | True | Projects.MessageBoardId | The Message Board ID that the message belongs to. |
Status | String | False | The status of the message | |
Type | String | True | The type of the returned record. | |
CreatedAt | Datetime | True | The date and time when the message was created. | |
UpdatedAt | Datetime | True | The date and time when the message was last updated. | |
Content | String | False | Provided content for the message | |
Subject | String | False | Subject of the message | |
ParentTitle | String | True | Title of the parent. | |
ParentType | String | True | Type of the parent. | |
BucketName | String | True | Name of the containing bucket. | |
BucketType | String | True | Type of the containing bucket. | |
CreatorId | Long | True | People.Id | The ID of the creator. |
CreatorName | String | True | The name of the creator. | |
CategoryId | Long | False | The ID of the category. | |
CategoryName | String | True | The name of the category. | |
CommentsCount | Integer | True | Number of comments the document has. |
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.
Name | Type | Description |
---|---|---|
IncludeReplies | Boolean | Set this to true to retrieve replies for each message. |
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¶
Name | Type | ReadOnly | References | Description |
---|---|---|---|---|
Id [KEY] | Long | True | The unique identifier of the project. | |
Status | String | True | The status of the project. | |
Purpose | String | True | The purpose of the project. | |
CreatedAt | Datetime | True | The creation time of the project. | |
UpdatedAt | Datetime | True | Time when the project was updated. | |
Description | String | False | The description of the project. | |
Name | String | False | The name of the project. | |
Bookmarked | Boolean | False | Boolean value for a bookmarked project. | |
CalendarId | Long | True | Id of the project's Calendar. | |
ChatId | Long | True | Id of the project's Chat. | |
ToDoSetId | Long | True | Id of the project's ToDo Set. | |
MessageBoardId | Long | True | Id of the project's Message Board. | |
QuestionnaireId | Long | True | Id of the project's Questionnaire. | |
VaultId | Long | True | Id of the project's Vault. | |
InboxId | Long | True | Id of the project's Inbox. | |
KanbanBoardId | Long | True | Id of the project's Kanban Board. |
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¶
Name | Type | ReadOnly | References | Description |
---|---|---|---|---|
Id [KEY] | Long | True | The unique identifier of the topic. | |
ProjectId | Long | True | Projects.Id | The ID of the Project associated with the to-do list. |
ToDoSetId | Long | True | Projects.ToDoSetId | The ID of the To Do Set the to-do list belongs to. |
Status | String | True | The to-do status. | |
Type | String | True | The record type. | |
Name | String | False | The to-do name. | |
Completed | Boolean | True | The to-do state. | |
CompletedRatio | String | True | The to-do state in ratio. | |
Description | String | False | The to-do list description. | |
CreatedAt | Datetime | True | The date and time when the event was created. | |
UpdatedAt | Datetime | True | The date and time when the record was last updated. | |
BucketName | String | True | The to-do list bucket name. | |
BucketType | String | True | The to-do list bucket type. | |
ParentTitle | String | True | Title of the parent. | |
ParentType | String | True | Type of the parent. | |
CreatorId | Long | True | People.Id | Event creator Id. |
CreatorAttachableSgid | String | True | Event creator Attachable SGID. | |
CreatorName | String | True | Event creator Name. | |
CreatorEmailAddress | String | True | Event creator Email Address. | |
CreatorTitle | String | True | Event creator Title. | |
CreatorBio | String | True | Event creator Bio. | |
CreatorCreatedAt | Datetime | True | Event creator creation date and time. | |
CreatorUpdatedAt | Datetime | True | Event creator update date and time. | |
CreatorAdmin | Boolean | True | Boolean indicationg if the event creator is an admin. | |
CreatorOwner | Boolean | True | Boolean indicationg if the event creator is an owner. | |
CreatorTimeZone | String | True | Event creator's timezone. | |
CreatorCompanyId | Long | True | Event creator's company Id. | |
CreatorCompanyName | String | True | Event creator's company name. | |
CommentsCount | Integer | True | Number of coments the document has. |
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¶
Name | Type | ReadOnly | References | Description |
---|---|---|---|---|
Id [KEY] | Long | True | The unique identifier of the todo. | |
ProjectId | Long | True | Projects.Id | The ID of the Project associated with the to-do list. |
ToDoListId | Long | True | ToDoLists.Id | The ID of the To Do list the to-do belongs to. |
Status | String | True | The state the todo. | |
CreatedAt | Datetime | True | The date and time when the event was created. | |
UpdatedAt | Datetime | True | The date and time when the record was last updated. | |
StartsOn | Date | False | The date and time when the todo should start. | |
DueOn | Date | False | The date and time when the todo is due. | |
CommentsCount | Integer | True | Number of coments the document has. | |
Description | String | False | The to-do list description. | |
Content | String | False | What to do. | |
AssigneesIds | String | False | List of assigned user Ids. | |
Completed | Boolean | False | The to-do state. | |
CompletedAt | Datetime | True | The tdate and time when the todo was completed. | |
CompletionCreatorId | String | True | The ID of the user who completed the todo. | |
CompletionCreatorName | String | True | The name of the user who completed the todo. | |
BucketName | String | True | The to-do list bucket name. | |
BucketType | String | True | The to-do list bucket type. | |
ParentTitle | String | True | Title of the parent. | |
ParentType | String | True | Type of the parent. | |
CreatorId | Long | True | People.Id | Event creator Id. |
CreatorAttachableSgid | String | True | Event creator Attachable SGID. | |
CreatorName | String | True | Event creator Name. | |
CreatorEmailAddress | String | True | Event creator Email Address. | |
CreatorTitle | String | True | Event creator Title. | |
CreatorBio | String | True | Event creator Bio. | |
CreatorCreatedAt | Datetime | True | Event creator creation date and time. | |
CreatorUpdatedAt | Datetime | True | Event creator update date and time. | |
CreatorAdmin | Boolean | True | Boolean indicationg if the event creator is an admin. | |
CreatorOwner | Boolean | True | Boolean indicationg if the event creator is an owner. | |
CreatorTimeZone | String | True | Event creator's timezone. | |
CreatorCompanyId | String | True | Event creator's company Id. | |
CreatorCompanyName | String | True | Event creator's company name. |
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¶
Name | Type | ReadOnly | References | Description |
---|---|---|---|---|
Id [KEY] | Long | True | The unique identifier of the topic. | |
ProjectId | Long | True | Projects.Id | The Project ID associated with the vault. |
Status | String | True | The status of the vault. | |
Title | String | False | The title of the returned record. | |
Type | String | True | The type of the returned record. | |
CreatedAt | Datetime | True | The date and time when the record was created. | |
UpdatedAt | Datetime | True | The date and time when the record was last updated. | |
ParentId | Long | True | Id of the parent. | |
ParentTitle | String | True | Title of the parent. | |
ParentType | String | True | Type of the parent. | |
BucketId | Long | True | Id of the containing bucket. | |
BucketName | String | True | Name of the containing bucket. | |
BucketType | String | True | Type of the containing bucket. | |
CreatorId | Long | True | People.Id | The ID of the creator. |
CreatorName | String | True | The name of the creator. | |
DocumentsCount | Integer | True | Number of documents the vault contains. | |
UploadsCount | Integer | True | Number of uploads the vault contains. | |
VaultsCount | Integer | True | Number of vaults the vault contains. |
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.
Name | Type | Description |
---|---|---|
VaultId | Long | The Vault ID that the record belongs to. |
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¶
Name | Description |
---|---|
Assignments | Retrieve Assignments on Basecamp 3. |
Calendars | Retrieve calendars on Basecamp 3. |
Events | Retrieve activity Events on Basecamp 3. |
People | Retrieve People on Basecamp 3. |
ProjectTools | Retrieve project tools on Basecamp 3. |
Recordings | Retrieve recordings on Basecamp 3 folders. |
Uploads | Retrieve Uploads on Basecamp 3. |
Assignments¶
Retrieve Assignments on Basecamp 3.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | Long | The unique identifier for the person. | |
TodoListId | Long | ToDoLists.Id | The unique identifier for the todolist. |
ProjectId | Long | Projects.Id | Use filter to retrieve active people on the project with the given ID. |
Name | String | The name of the person. | |
Owner | Boolean | Boolean value to show if the current person is the account owner. | |
Admin | Boolean | Boolean value to show if the current person is an admin or not. | |
Title | String | The title of the person. | |
Bio | String | Bio description of the person. | |
CreatedAt | Datetime | The date and time when the record was created. | |
UpdatedAt | Datetime | The date and time when the record was last updated. | |
EmailAddress | String | The email address of the person. | |
AttachableSgid | String | The attachable Set Group ID. | |
PersonableType | String | Type of the person. | |
TimeZone | String | Human readable timezone for the user. | |
CompanyId | Long | Id of users company. | |
CompanyName | String | Name of users company. |
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¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | Long | Projects.CalendarId | The unique identifier of the calendar. |
ProjectId | Long | Projects.Id | The Project ID associated with the calendar. |
Status | String | The status of the calendar. | |
Type | String | The type of the returned record. | |
CreatedAt | Datetime | The date and time when the calendar was created. | |
UpdatedAt | Datetime | The date and time when the calendar was last updated. | |
Title | String | Title of the calendar. | |
EntriesCount | Integer | Number of calendar events. | |
BucketId | Long | Id of the containing bucket. | |
BucketName | String | Name of the containing bucket. | |
BucketType | String | Type of the containing bucket. | |
CreatorId | Long | People.Id | The ID of the creator. |
CreatorName | String | The name of the creator. |
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¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | Long | The unique identifier of the event. | |
ProjectId | Long | Projects.Id | The ID of the Project associated with the document. |
RecordingId | Long | Recordings.Id | The ID of the Recording associated with this event. |
Action | String | The action description for the event. | |
Details | String | Details on the event. | |
CreatedAt | Datetime | The date and time when the event was created. | |
CreatorId | Long | People.Id | Event creator Id. |
CreatorAttachableSgid | String | Event creator Attachable SGID. | |
CreatorName | String | Event creator Name. | |
CreatorEmailAddress | String | Event creator Email Address. | |
CreatorTitle | String | Event creator Title. | |
CreatorBio | String | Event creator Bio. | |
CreatorCreatedAt | Datetime | Event creator creation date and time. | |
CreatorUpdatedAt | Datetime | Event creator update date and time. | |
CreatorAdmin | Boolean | Boolean indicationg if the event creator is an admin. | |
CreatorOwner | Boolean | Boolean indicationg if the event creator is an owner. | |
CreatorTimeZone | String | Event creator's timezone. | |
CreatorCompanyId | Long | Event creator's company Id. | |
CreatorCompanyName | String | Event creator's company name. |
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¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | Long | The unique identifier for the person. | |
Name | String | The name of the person. | |
Owner | Boolean | Boolean value to show if the current person is the account owner. | |
Admin | Boolean | Boolean value to show if the current person is an admin or not. | |
Title | String | The title of the person. | |
Bio | String | Bio description of the person. | |
CreatedAt | Datetime | The date and time when the record was created. | |
UpdatedAt | Datetime | The date and time when the record was last updated. | |
EmailAddress | String | The email address of the person. | |
AttachableSgid | String | The attachable Set Group ID. | |
PersonableType | String | Type of the person. | |
TimeZone | String | Human readable timezone for the user. | |
CompanyId | Long | Id of users company. | |
CompanyName | String | Name of users company. |
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.
Name | Type | Description |
---|---|---|
ProjectId | Long |
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¶
Name | Type | References | Description |
---|---|---|---|
Id | Long | The ID of the tool associated with the project. | |
Name | String | The name of the tool associated with the project. | |
AppUrl | String | The app URL of the tool associated with this project. | |
Enabled | Boolean | Boolean value for a tool of the project. | |
Position | Int | The postion of the tool associated with this project. | |
Title | String | The title of the tool associated with this project. | |
Url | String | The URL of the tool associated with this project. | |
ProjectId | Long | The unique identifier of the project. |
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¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | Long | The unique identifier of the topic. | |
Status | String | The status of the vault. The allowed values are active, archived, trashed. | |
Type | String | The type of the vault. The allowed values are Comment, Document, Message, Question:Answer, Schedule:Entry, Todo, Todolist, Upload. | |
CreatedAt | Datetime | The date and time when the event was created. | |
UpdatedAt | Datetime | The date and time when the event was last modified. | |
CreatorId | Long | People.Id | Event Creator. |
CreatorName | String | Event Creator. |
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.
Name | Type | Description |
---|---|---|
Bucket | String |
Uploads¶
Retrieve Uploads on Basecamp 3.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | Long | The unique identifier for the file uploaded. | |
ProjectId | Long | Projects.Id | The ID of the Project associated with the file uploaded. |
VaultId | Long | Vaults.Id | The Vault ID associated with the file uploaded. |
Status | String | The status of the vault. | |
Title | String | The title of the file uploaded. | |
ContentType | String | The content type of the file uploaded | |
Type | String | The type of the returned record. | |
CreatedAt | Datetime | The date and time when the file uploaded was created. | |
UpdatedAt | Datetime | The date and time when the file uploaded was last updated. | |
ParentTitle | String | Title of the parent. | |
ParentType | String | Type of the parent. | |
BucketName | String | Name of the containing bucket. | |
BucketType | String | Type of the containing bucket. | |
CreatorId | Long | People.Id | The ID of the creator. |
CreatorName | String | The name of the creator. | |
CommentsCount | Integer | Number of comments the file uploaded has. | |
DownloadURL | String | Number of comments the file uploaded has. | |
ByteSize | Integer | Number of comments the file uploaded has. |
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¶
Name | Description |
---|---|
GetOAuthAccessToken | Gets the OAuth access token from Basecamp. |
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. |
RefreshOAuthAccessToken | Refreshes the OAuth access token used for authentication with various Basecamp services. |
GetOAuthAccessToken¶
Gets the OAuth access token from Basecamp.
Input¶
Name | Type | Required | Description |
---|---|---|---|
AuthMode | String | True | WEB or APP. The Basecamp authentication mode. Default is APP. The default value is APP. |
CallbackUrl | String | False | The URL the user will be redirected to after authorizing your application. |
Verifier | String | False | The verifier token returned by Basecamp. Required for only the Web AuthMode. |
State | String | False | This field indicates any state that may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to Basecamp authorization server and back. Uses include redirecting the user to the correct resource in your site, using nonces, and mitigating cross-site request forgery. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
OAuthAccessToken | String | The OAuth token. |
OAuthRefreshToken | String | The OAuth refresh token. |
ExpiresIn | String | The remaining lifetime for the access token in seconds. |
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¶
Name | Type | Required | Description |
---|---|---|---|
CallbackUrl | String | False | The URL that Basecamp will return to after the user has authorized your app. |
State | String | False | This field indicates any state that may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to Basecamp authorization server and back. Uses include redirecting the user to the correct resource in your site, using nonces, and mitigating cross-site request forgery. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
URL | String | The URL to be entered into a Web browser to obtain the verifier token and authorize the data provider with. |
RefreshOAuthAccessToken¶
Refreshes the OAuth access token used for authentication with various Basecamp services.
Input¶
Name | Type | Required | Description |
---|---|---|---|
OAuthRefreshToken | String | True | The refresh token returned with the previous access token. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
OAuthAccessToken | String | The authentication token returned from Basecamp. This can be used in subsequent calls to other operations for this particular service. |
OAuthRefreshToken | String | A token that may be used to obtain a new access token. |
ExpiresIn | String | The remaining lifetime on the access token. |
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¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database name. |
sys_schemas¶
Lists the available schemas.
The following query retrieves all available schemas:
SELECT * FROM sys_schemas
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database name. |
SchemaName | String | The schema name. |
sys_tables¶
Lists the available tables.
The following query retrieves the available tables and views:
SELECT * FROM sys_tables
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view. |
TableType | String | The table type (table or view). |
Description | String | A description of the table or view. |
IsUpdateable | Boolean | Whether the table can be updated. |
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¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view containing the column. |
ColumnName | String | The column name. |
DataTypeName | String | The data type name. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The storage size of the column. |
DisplaySize | Int32 | The designated column's normal maximum width in characters. |
NumericPrecision | Int32 | The maximum number of digits in numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The column scale or number of digits to the right of the decimal point. |
IsNullable | Boolean | Whether the column can contain null. |
Description | String | A brief description of the column. |
Ordinal | Int32 | The sequence number of the column. |
IsAutoIncrement | String | Whether the column value is assigned in fixed increments. |
IsGeneratedColumn | String | Whether the column is generated. |
IsHidden | Boolean | Whether the column is hidden. |
IsArray | Boolean | Whether the column is an array. |
IsReadOnly | Boolean | Whether the column is read-only. |
IsKey | Boolean | Indicates whether a field returned from sys_tablecolumns is the primary key of the table. |
sys_procedures¶
Lists the available stored procedures.
The following query retrieves the available stored procedures:
SELECT * FROM sys_procedures
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database containing the stored procedure. |
SchemaName | String | The schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure. |
Description | String | A description of the stored procedure. |
ProcedureType | String | The type of the procedure, such as PROCEDURE or FUNCTION. |
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¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the stored procedure. |
SchemaName | String | The name of the schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure containing the parameter. |
ColumnName | String | The name of the stored procedure parameter. |
Direction | Int32 | An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters. |
DataTypeName | String | The name of the data type. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The number of characters allowed for character data. The number of digits allowed for numeric data. |
NumericPrecision | Int32 | The maximum precision for numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The number of digits to the right of the decimal point in numeric data. |
IsNullable | Boolean | Whether the parameter can contain null. |
IsRequired | Boolean | Whether the parameter is required for execution of the procedure. |
IsArray | Boolean | Whether the parameter is an array. |
Description | String | The description of the parameter. |
Ordinal | Int32 | The index of the parameter. |
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¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
IsKey | Boolean | Whether the column is a primary key in the table referenced in the TableName field. |
IsForeignKey | Boolean | Whether the column is a foreign key referenced in the TableName field. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
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¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
ForeignKeyType | String | Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key. |
sys_primarykeys¶
Describes the primary keys.
The following query retrieves the primary keys from all tables and views:
SELECT * FROM sys_primarykeys
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
KeySeq | String | The sequence number of the primary key. |
KeyName | String | The name of the primary key. |
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¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the index. |
SchemaName | String | The name of the schema containing the index. |
TableName | String | The name of the table containing the index. |
IndexName | String | The index name. |
ColumnName | String | The name of the column associated with the index. |
IsUnique | Boolean | True if the index is unique. False otherwise. |
IsPrimary | Boolean | True if the index is a primary key. False otherwise. |
Type | Int16 | An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3). |
SortOrder | String | The sort order: A for ascending or D for descending. |
OrdinalPosition | Int16 | The sequence number of the column in the index. |
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¶
Name | Type | Description |
---|---|---|
Name | String | The name of the connection property. |
ShortDescription | String | A brief description. |
Type | String | The data type of the connection property. |
Default | String | The default value if one is not explicitly set. |
Values | String | A comma-separated list of possible values. A validation error is thrown if another value is specified. |
Value | String | The value you set or a preconfigured default. |
Required | Boolean | Whether the property is required to connect. |
Category | String | The category of the connection property. |
IsSessionProperty | String | Whether the property is a session property, used to save information about the current connection. |
Sensitivity | String | The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms. |
PropertyName | String | A camel-cased truncated form of the connection property name. |
Ordinal | Int32 | The index of the parameter. |
CatOrdinal | Int32 | The index of the parameter category. |
Hierarchy | String | Shows dependent properties associated that need to be set alongside this one. |
Visible | Boolean | Informs whether the property is visible in the connection UI. |
ETC | String | Various miscellaneous information about the property. |
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.
Name | Description | Possible Values |
---|---|---|
AGGREGATE_FUNCTIONS | Supported aggregation functions. | AVG , COUNT , MAX , MIN , SUM , DISTINCT |
COUNT | Whether COUNT function is supported. | YES , NO |
IDENTIFIER_QUOTE_OPEN_CHAR | The opening character used to escape an identifier. | [ |
IDENTIFIER_QUOTE_CLOSE_CHAR | The closing character used to escape an identifier. | ] |
SUPPORTED_OPERATORS | A list of supported SQL operators. | = , > , < , >= , <= , <> , != , LIKE , NOT LIKE , IN , NOT IN , IS NULL , IS NOT NULL , AND , OR |
GROUP_BY | Whether GROUP BY is supported, and, if so, the degree of support. | NO , NO_RELATION , EQUALS_SELECT , SQL_GB_COLLATE |
STRING_FUNCTIONS | Supported string functions. | LENGTH , CHAR , LOCATE , REPLACE , SUBSTRING , RTRIM , LTRIM , RIGHT , LEFT , UCASE , SPACE , SOUNDEX , LCASE , CONCAT , ASCII , REPEAT , OCTET , BIT , POSITION , INSERT , TRIM , UPPER , REGEXP , LOWER , DIFFERENCE , CHARACTER , SUBSTR , STR , REVERSE , PLAN , UUIDTOSTR , TRANSLATE , TRAILING , TO , STUFF , STRTOUUID , STRING , SPLIT , SORTKEY , SIMILAR , REPLICATE , PATINDEX , LPAD , LEN , LEADING , KEY , INSTR , INSERTSTR , HTML , GRAPHICAL , CONVERT , COLLATION , CHARINDEX , BYTE |
NUMERIC_FUNCTIONS | Supported numeric functions. | ABS , ACOS , ASIN , ATAN , ATAN2 , CEILING , COS , COT , EXP , FLOOR , LOG , MOD , SIGN , SIN , SQRT , TAN , PI , RAND , DEGREES , LOG10 , POWER , RADIANS , ROUND , TRUNCATE |
TIMEDATE_FUNCTIONS | Supported date/time functions. | NOW , CURDATE , DAYOFMONTH , DAYOFWEEK , DAYOFYEAR , MONTH , QUARTER , WEEK , YEAR , CURTIME , HOUR , MINUTE , SECOND , TIMESTAMPADD , TIMESTAMPDIFF , DAYNAME , MONTHNAME , CURRENT_DATE , CURRENT_TIME , CURRENT_TIMESTAMP , EXTRACT |
REPLICATION_SKIP_TABLES | Indicates tables skipped during replication. | |
REPLICATION_TIMECHECK_COLUMNS | A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication. | |
IDENTIFIER_PATTERN | String value indicating what string is valid for an identifier. | |
SUPPORT_TRANSACTION | Indicates if the provider supports transactions such as commit and rollback. | YES , NO |
DIALECT | Indicates the SQL dialect to use. | |
KEY_PROPERTIES | Indicates the properties which identify the uniform database. | |
SUPPORTS_MULTIPLE_SCHEMAS | Indicates if multiple schemas may exist for the provider. | YES , NO |
SUPPORTS_MULTIPLE_CATALOGS | Indicates if multiple catalogs may exist for the provider. | YES , NO |
DATASYNCVERSION | The Data Sync version needed to access this driver. | Standard , Starter , Professional , Enterprise |
DATASYNCCATEGORY | The Data Sync category of this driver. | Source , Destination , Cloud Destination |
SUPPORTSENHANCEDSQL | Whether enhanced SQL functionality beyond what is offered by the API is supported. | TRUE , FALSE |
SUPPORTS_BATCH_OPERATIONS | Whether batch operations are supported. | YES , NO |
SQL_CAP | All supported SQL capabilities for this driver. | SELECT , INSERT , DELETE , UPDATE , TRANSACTIONS , ORDERBY , OAUTH , ASSIGNEDID , LIMIT , LIKE , BULKINSERT , COUNT , BULKDELETE , BULKUPDATE , GROUPBY , HAVING , AGGS , OFFSET , REPLICATE , COUNTDISTINCT , JOINS , DROP , CREATE , DISTINCT , INNERJOINS , SUBQUERIES , ALTER , MULTIPLESCHEMAS , GROUPBYNORELATION , OUTERJOINS , UNIONALL , UNION , UPSERT , GETDELETED , CROSSJOINS , GROUPBYCOLLATE , MULTIPLECATS , FULLOUTERJOIN , MERGE , JSONEXTRACT , BULKUPSERT , SUM , SUBQUERIESFULL , MIN , MAX , JOINSFULL , XMLEXTRACT , AVG , MULTISTATEMENTS , FOREIGNKEYS , CASE , LEFTJOINS , COMMAJOINS , WITH , LITERALS , RENAME , NESTEDTABLES , EXECUTE , BATCH , BASIC , INDEX |
PREFERRED_CACHE_OPTIONS | A string value specifies the preferred cacheOptions. | |
ENABLE_EF_ADVANCED_QUERY | Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. | YES , NO |
PSEUDO_COLUMNS | A string array indicating the available pseudo columns. | |
MERGE_ALWAYS | If the value is true, The Merge Mode is forcibly executed in Data Sync. | TRUE , FALSE |
REPLICATION_MIN_DATE_QUERY | A select query to return the replicate start datetime. | |
REPLICATION_MIN_FUNCTION | Allows a provider to specify the formula name to use for executing a server side min. | |
REPLICATION_START_DATE | Allows a provider to specify a replicate startdate. | |
REPLICATION_MAX_DATE_QUERY | A select query to return the replicate end datetime. | |
REPLICATION_MAX_FUNCTION | Allows a provider to specify the formula name to use for executing a server side max. | |
IGNORE_INTERVALS_ON_INITIAL_REPLICATE | A list of tables which will skip dividing the replicate into chunks on the initial replicate. | |
CHECKCACHE_USE_PARENTID | Indicates whether the CheckCache statement should be done against the parent key column. | TRUE , FALSE |
CREATE_SCHEMA_PROCEDURES | Indicates stored procedures that can be used for generating schema files. |
The following query retrieves the operators that can be used in the WHERE clause:
SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'
Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.
Columns¶
Name | Type | Description |
---|---|---|
NAME | String | A component of SQL syntax, or a capability that can be processed on the server. |
VALUE | String | Detail on the supported SQL or SQL syntax. |
sys_identity¶
Returns information about attempted modifications.
The following query retrieves the Ids of the modified rows in a batch operation:
SELECT * FROM sys_identity
Columns¶
Name | Type | Description |
---|---|---|
Id | String | The database-generated ID returned from a data modification operation. |
Batch | String | An identifier for the batch. 1 for a single operation. |
Operation | String | The result of the operation in the batch: INSERTED, UPDATED, or DELETED. |
Message | String | SUCCESS or an error message if the update in the batch failed. |
Advanced Configurations Properties¶
The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.
Property | Description |
---|---|
AuthScheme | The type of authentication to use when connecting to Basecamp. |
Schema | Specify the Basecamp version to connect with. |
User | Username of the currently authenticated user. |
Password | Password of the currently authenticated user. |
AccountId | Account ID of the currently authenticated user. |
ProjectId | The Project ID of the currently authenticated user. |
Property | Description |
---|---|
InitiateOAuth | Set this property to initiate the process to obtain or refresh the OAuth access token when you connect. |
OAuthClientId | The client ID assigned when you register your application with an OAuth authorization server. |
OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
OAuthAccessToken | The access token for connecting using OAuth. |
OAuthSettingsLocation | The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://' . |
CallbackURL | The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. |
OAuthAuthorizationURL | The authorization URL for the OAuth service. |
OAuthAccessTokenURL | The URL to retrieve the OAuth access token from. |
OAuthVerifier | The verifier code returned from the OAuth authorization URL. |
OAuthRefreshToken | The OAuth refresh token for the corresponding OAuth access token. |
OAuthExpiresIn | The lifetime in seconds of the OAuth AccessToken. |
OAuthTokenTimestamp | The Unix epoch timestamp in milliseconds when the current Access Token was created. |
Property | Description |
---|---|
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
Property | Description |
---|---|
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC. |
Property | Description |
---|---|
MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
Other | These hidden properties are used only in specific use cases. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
Authentication¶
This section provides a complete list of authentication properties you can configure.
Property | Description |
---|---|
AuthScheme | The type of authentication to use when connecting to Basecamp. |
Schema | Specify the Basecamp version to connect with. |
User | Username of the currently authenticated user. |
Password | Password of the currently authenticated user. |
AccountId | Account ID of the currently authenticated user. |
ProjectId | The Project ID of the currently authenticated user. |
AuthScheme¶
The type of authentication to use when connecting to Basecamp.
Possible Values¶
Auto
, OAuth
, Basic
Data Type¶
string
Default Value¶
Auto
Remarks¶
- Auto: Lets the driver decide automatically based on the other connection properties you have set.
- Basic: Set this to use BASIC user / password authentication.
- OAuth: Set to this to perform OAuth authentication.
Schema¶
Specify the Basecamp version to connect with.
Possible Values¶
V2
, V3
Data Type¶
string
Default Value¶
V2
Remarks¶
The schemas available are V2 (to use the Basecamp 2 API) andV3 (to use the Basecamp 3 API).
User¶
Username of the currently authenticated user.
Data Type¶
string
Default Value¶
""
Remarks¶
Username of the currently authenticated user. This property is required to execute commands.
Password¶
Password of the currently authenticated user.
Data Type¶
string
Default Value¶
""
Remarks¶
Password of the currently authenticated user. This property is required to execute commands.
AccountId¶
Account ID of the currently authenticated user.
Data Type¶
string
Default Value¶
""
Remarks¶
Account ID of the currently authenticated user. This property is required to execute commands.
ProjectId¶
The Project ID of the currently authenticated user.
Data Type¶
string
Default Value¶
""
Remarks¶
The Project ID of the currently authenticated user. By default it will be set to the first Project ID of your projects list.
OAuth¶
This section provides a complete list of OAuth properties you can configure.
Property | Description |
---|---|
InitiateOAuth | Set this property to initiate the process to obtain or refresh the OAuth access token when you connect. |
OAuthClientId | The client ID assigned when you register your application with an OAuth authorization server. |
OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
OAuthAccessToken | The access token for connecting using OAuth. |
OAuthSettingsLocation | The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://' . |
CallbackURL | The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. |
OAuthAuthorizationURL | The authorization URL for the OAuth service. |
OAuthAccessTokenURL | The URL to retrieve the OAuth access token from. |
OAuthVerifier | The verifier code returned from the OAuth authorization URL. |
OAuthRefreshToken | The OAuth refresh token for the corresponding OAuth access token. |
OAuthExpiresIn | The lifetime in seconds of the OAuth AccessToken. |
OAuthTokenTimestamp | The Unix epoch timestamp in milliseconds when the current Access Token was created. |
InitiateOAuth¶
Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
Possible Values¶
OFF
, GETANDREFRESH
, REFRESH
Data Type¶
string
Default Value¶
OFF
Remarks¶
The following options are available:
OFF
: Indicates that the OAuth flow will be handled entirely by the user. An OAuthAccessToken will be required to authenticate.GETANDREFRESH
: Indicates that the entire OAuth Flow will be handled by the connector. If no token currently exists, it will be obtained by prompting the user via the browser. If a token exists, it will be refreshed when applicable.REFRESH
: Indicates that the connector will only handle refreshing the OAuthAccessToken. The user will never be prompted by the connector to authenticate via the browser. The user must handle obtaining the OAuthAccessToken and OAuthRefreshToken initially.
OAuthClientId¶
The client ID assigned when you register your application with an OAuth authorization server.
Data Type¶
string
Default Value¶
""
Remarks¶
As part of registering an OAuth application, you will receive the OAuthClientId
value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.
OAuthClientSecret¶
The client secret assigned when you register your application with an OAuth authorization server.
Data Type¶
string
Default Value¶
""
Remarks¶
As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret
property.
OAuthAccessToken¶
The access token for connecting using OAuth.
Data Type¶
string
Default Value¶
""
Remarks¶
The OAuthAccessToken
property is used to connect using OAuth. The OAuthAccessToken
is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.
The access token is used in place of your user name and password. The access token protects your credentials by keeping them on the server.
OAuthSettingsLocation¶
The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH. Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'
.
Data Type¶
string
Default Value¶
%APPDATA%\CData\Acumatica Data Provider\OAuthSettings.txt
Remarks¶
When InitiateOAuth is set to GETANDREFRESH
or REFRESH
, the driver saves OAuth values to avoid requiring the user to manually enter OAuth connection properties and to allow the credentials to be shared across connections or processes.
Instead of specifying a file path, you can use memory storage. Memory locations are specified by using a value starting with 'memory://'
followed by a unique identifier for that set of credentials (for example, memory://user1). The identifier can be anything you choose but should be unique to the user. Unlike file-based storage, where credentials persist across connections, memory storage loads the credentials into static memory, and the credentials are shared between connections using the same identifier for the life of the process. To persist credentials outside the current process, you must manually store the credentials prior to closing the connection. This enables you to set them in the connection when the process is started again. You can retrieve OAuth property values with a query to the sys_connection_props
system table. If there are multiple connections using the same credentials, the properties are read from the previously closed connection.
The default location is "%APPDATA%\CData\Acumatica Data Provider\OAuthSettings.txt" with %APPDATA%
set to the user's configuration directory. The default values are
- Windows: "
register://%DSN
" - Unix: "%AppData%..."
where DSN is the name of the current DSN used in the open connection.
The following table lists the value of %APPDATA%
by OS:
Platform | %APPDATA% |
---|---|
Windows | The value of the APPDATA environment variable |
Linux | ~/.config |
CallbackURL¶
The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
Data Type¶
string
Default Value¶
""
Remarks¶
During the authentication process, the OAuth authorization server redirects the user to this URL. This value must match the callback URL you specify in your app settings.
OAuthAuthorizationURL¶
The authorization URL for the OAuth service.
Data Type¶
string
Default Value¶
""
Remarks¶
The authorization URL for the OAuth service. At this URL, the user logs into the server and grants permissions to the application. In OAuth 1.0, if permissions are granted, the request token is authorized.
OAuthAccessTokenURL¶
The URL to retrieve the OAuth access token from.
Data Type¶
string
Default Value¶
""
Remarks¶
The URL to retrieve the OAuth access token from. In OAuth 1.0, the authorized request token is exchanged for the access token at this URL.
OAuthVerifier¶
The verifier code returned from the OAuth authorization URL.
Data Type¶
string
Default Value¶
""
Remarks¶
The verifier code returned from the OAuth authorization URL. This can be used on systems where a browser cannot be launched such as headless systems.
Authentication on Headless Machines¶
See to obtain the OAuthVerifier
value.
Set OAuthSettingsLocation along with OAuthVerifier
. When you connect, the connector exchanges the OAuthVerifier
for the OAuth authentication tokens and saves them, encrypted, to the specified location. Set InitiateOAuth to GETANDREFRESH to automate the exchange.
Once the OAuth settings file has been generated, you can remove OAuthVerifier
from the connection properties and connect with OAuthSettingsLocation set.
To automatically refresh the OAuth token values, set OAuthSettingsLocation and additionally set InitiateOAuth to REFRESH.
OAuthRefreshToken¶
The OAuth refresh token for the corresponding OAuth access token.
Data Type¶
string
Default Value¶
""
Remarks¶
The OAuthRefreshToken
property is used to refresh the OAuthAccessToken when using OAuth authentication.
OAuthExpiresIn¶
The lifetime in seconds of the OAuth AccessToken.
Data Type¶
string
Default Value¶
""
Remarks¶
Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.
OAuthTokenTimestamp¶
The Unix epoch timestamp in milliseconds when the current Access Token was created.
Data Type¶
string
Default Value¶
""
Remarks¶
Pair with OAuthExpiresIn to determine when the AccessToken will expire.
SSL¶
This section provides a complete list of SSL properties you can configure.
Property | Description |
---|---|
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
SSLServerCert¶
The certificate to be accepted from the server when connecting using TLS/SSL.
Data Type¶
string
Default Value¶
""
Remarks¶
If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.
This property can take the following forms:
Description | Example |
---|---|
A full PEM Certificate (example shortened for brevity) | -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE----- |
A path to a local file containing the certificate | C:\\cert.cer |
The public key (example shortened for brevity) | -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY----- |
The MD5 Thumbprint (hex values can also be either space or colon separated) | ecadbdda5a1529c58a1e9e09828d70e4 |
The SHA1 Thumbprint (hex values can also be either space or colon separated) | 34a929226ae0819f2ec14b4a3d904f801cbb150d |
If not specified, any certificate trusted by the machine is accepted.
Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).
Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.
Schema¶
This section provides a complete list of schema properties you can configure.
Property | Description |
---|---|
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC. |
Location¶
A path to the directory that contains the schema files defining tables, views, and stored procedures.
Data Type¶
string
Default Value¶
%APPDATA%\Basecamp Data Provider\Schema
Remarks¶
The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location
property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.
Note
Given that this connector supports multiple schemas, the structure for Basecamp custom schema files is as follows:
- Each schema is given a folder corresponding to that schema name.
- These schema folders are contained in a parent folder.
- The
parent folder
should be set as theLocation
, not an individual schema's folder.
If left unspecified, the default location is "%APPDATA%\Basecamp Data Provider\Schema" with %APPDATA%
being set to the user's configuration directory:
Platform | %APPDATA% |
---|---|
Windows | The value of the APPDATA environment variable |
Mac | ~/Library/Application Support |
Linux | ~/.config |
BrowsableSchemas¶
This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.
Tables¶
This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Views¶
Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Miscellaneous¶
This section provides a complete list of miscellaneous properties you can configure.
Property | Description |
---|---|
MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
Other | These hidden properties are used only in specific use cases. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
MaxRows¶
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Data Type¶
int
Default Value¶
-1
Remarks¶
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other¶
These hidden properties are used only in specific use cases.
Data Type¶
string
Default Value¶
""
Remarks¶
The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.
Specify multiple properties in a semicolon-separated list.
Integration and Formatting¶
Property | Description |
---|---|
DefaultColumnSize | Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000. |
ConvertDateTimeToGMT | Determines whether to convert date-time values to GMT, instead of the local time of the machine. |
RecordToFile=filename | Records the underlying socket data transfer to the specified file. |
PseudoColumns¶
This property indicates whether or not to include pseudo columns as columns to the table.
Data Type¶
string
Default Value¶
""
Remarks¶
This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".
Timeout¶
The value in seconds until the timeout error is thrown, canceling the operation.
Data Type¶
int
Default Value¶
60
Remarks¶
If Timeout
= 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.
If Timeout
expires and the operation is not yet complete, the connector throws an exception.
UserDefinedViews¶
A filepath pointing to the JSON configuration file containing your custom views.
Data Type¶
string
Default Value¶
""
Remarks¶
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
Note that the specified path is not embedded in quotation marks.