このページでは、CData Cloud でのAsana への接続の確立 のガイド、利用可能なリソースに関する情報、および使用可能な接続プロパティのリファレンスについて説明します。

Asana への接続

接続の確立 は、CData Cloud にデータベースを作成するためのAsana への認証方法と必要な接続プロパティの設定方法について示します。

CData Cloud サービスからデータにアクセス

利用可能な標準サービスを経由してAsana からデータにアクセスする方法と、CData Cloud の管理については、CData Cloud ドキュメント で詳しく説明します。

Database タブで対応するアイコンを選択して、Asana に接続します。必須プロパティはSettings にリストされています。Advanced タブには、通常は必要ない接続プロパティが表示されます。

Connecting to Asana

You can optionally set the following to refine the data returned from Asana.

• WorkspaceId: The globally unique identifier (gid) associated with your Asana Workspace to only return projects from the specified Workspace. To get your Workspace Id, navigate to https://app.asana.com/api/1.0/workspaces while logged into Asana. This displays a JSON object containing your Workspace name and Id.
• ProjectId: The globally unique identifier (gid) associated with your Asana Project to only return data mapped under the specified Project. Project Ids can be found in the URL of your project's Overview page. This will be the numbers directly after /0/.

Authenticating to Asana

Asana uses the OAuth authentication standard.

User Accounts (OAuth)

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

Service Accounts

To authenticate with a Server Account (Enterprise only), use a Personal Access Token by setting these connection properties:

• InitiateOAuth: OFF.
• OAuthAccessToken: Your access token value.

For more more information, see the Asana documentation for personal access tokens and service accounts.

To obtain an OAuthClientId, OAuthClientSecret, and CallbackURL, you first need to create an app linked to your Asana account.

To create an app linked to your Asana account:

1. Log in to your Asana account.
2. Navigate to My profile Settings > Apps > Manage Developer Apps or https://app.asana.com/0/developer-console.
3. Under My apps, select New app. Specify the app name, then select Create app.
4. Once your app created, set Redirect URL to http://localhost:33333 (or a different available port of your choice), then select add.

Once you are done with creating a new app, it will be displayed on your screen. From there, you can click View Client ID to reveal your newly created app's OAuthClientId and OAuthClientSecret.

SSL 設定のカスタマイズ

デフォルトでは、Cloud はサーバーの証明書をシステムの信頼できる証明書ストアと照合してSSL / TLS のネゴシエーションを試みます。

別の証明書を指定するには、利用可能なフォーマットについてSSLServerCert プロパティを参照してください。

Firewall またはProxy 経由の接続

HTTP プロキシ

Windows のシステムプロキシ経由の接続では、接続プロパティを追加で設定する必要はありません。他のプロキシに接続するには、ProxyAutoDetect をfalse に設定します。

さらにHTTP プロキシへの認証には、ProxyServer とProxyPort に加えてProxyAuthScheme、ProxyUser、およびProxyPassword を設定します。

その他のプロキシ

次のプロパティを設定します。

• プロキシベースのファイヤーウォールを使用するには、FirewallType、FirewallServer、およびFirewallPort を設定します。
• 接続をトンネルするには、FirewallType をTUNNEL に設定します。
• 認証するには、FirewallUser とFirewallPassword を設定します。
• SOCKS プロキシへの認証には、さらにFirewallType をSOCKS5 に設定します。

Overview

This section shows the available API objects and provides more information on executing SQL to Asana APIs.

Key Features

• The Cloud models Asana entities as relational views, allowing you to write SQL to query Asana data.
• Stored procedures allow you to execute operations to Asana, including retrieving the access token and keeping it refreshed in OAuth 2.0.
• Live connectivity to these objects means any changes to your Asana account are immediately reflected when using the Cloud.
• IncludeCustomFields connection property allows you to retrieve custom fields for Tasks view. Set this property to True, to enable this feature.

Views

ビュー describes the available views. Views are statically defined to model Projects, Users, Workspaces, and more.

Stored Procedures

ストアドプロシージャ are function-like interfaces to Asana. Stored procedures allow you to execute operations to Asana, including downloading documents and moving envelopes.

Cloud はAsana のデータを、標準のSQL ステートメントを使用してクエリできるリレーショナルデータベースのテーブルのリストとしてモデル化します。

CData Cloud - Asana テーブル

Name	Description
Attachments	To Delete and Query from the Attachments in the tasks.
GoalMembership	To Create, Delete and Query memberships in the Goal.
Goals	To Create, Update, Delete and Query the Goals table.
PortfolioItems	To Create, delete, and Query Items in the Portfolio.
PortfolioMembership	To Create, Delete and Query memberships in the Portfolio.
Portfolios	To Create, update, delete, and query from Portfolios table.
ProjectMembership	To Create, Delete and Query memberships in the Project.
Projects	To Create, Update, Delete and Query the Projects table.
ProjectStatuses	To Create, Delete and Query from ProjectStatuses table. A project status is an update on the progress of a particular project, and is sent out to all project followers when created.
Sections	To Create, Update, Delete, and Query from Sections table. A section is a subdivision of a project that groups tasks together.
Stories	To Create, Update, Delete and Query from stories table. A story represents an activity associated with an object in the Asana system. Stories are generated by the system whenever users take actions such as creating or assigning tasks, or moving tasks between projects.
SubTasks	To Create, update, delete, and query from SubTasks table.
Tags	To Create, Update and Query from Tags table. A tag is a label that can be attached to any task in Asana. It exists in a single workspace.
Tasks	To Create, Update, Delete and Query from Tasks table.
TeamMembership	To represents a users connection to a team.
Teams	A team is used to group related projects and people together within an organization. Each project in an organization is associated with a team.
WorkspaceMembership	To Create, Delete and Query memberships in the Workspace.
Workspaces	To Update and Query from Workspaces table. A workspace is the highest-level organizational unit in Asana. All projects and tasks have an associated workspace.

To Delete and Query from the Attachments in the tasks.

Table Specific Information

Select

The Cloud will use the Asana API to process WHERE clause conditions built with the following column and operator. The TaskId or Id is required to make a request and the rest of the filter is executed client side within the Cloud.

• Id supports the '=' comparison.
• TaskId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Attachments WHERE Id = '1134618934491707'

SELECT * FROM Attachments WHERE TaskId = '1128092964672854'

Delete

Following is an example of how to delete from Attachments table:

DELETE FROM Attachments WHERE Id = '1134618934491707'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the attachment.
Name	String	True		The name of the file.
Host	String	True		The service hosting the attachment. Valid values are asana, dropbox, gdrive and box.
CreatedAt	Datetime	True		The time at which this attachment was uploaded.
ViewURL	String	True		The URL where the attachment can be viewed, which may be friendlier to users in a browser than just directing them to a raw file.
DownloadURL	String	True		May be null if the attachment is hosted by box. If present, this URL may only be valid for 1 hour from the time of retrieval. You should avoid persisting this URL somewhere and just refresh it on demand to ensure you do not keep stale URLs.
TaskId	String	True	Tasks.Id	The task this attachment is attached to.
ResourceType	String	True		The resource type of this resource.

To Create, Delete and Query memberships in the Goal.

Table Specific Information

Select

The Cloud will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports the '=' comparison.
• GoalId supports the '=' comparison.
• MemberId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM GoalMembership

SELECT * FROM GoalMembership WHERE Id = '1126938837961834'

SELECT * FROM GoalMembership WHERE MemberId = '1126938837961830'

SELECT * FROM GoalMembership WHERE GoalId = '1126938691750986'

Insert

GoalId and MemberId is a mandatory column for inserting into GoalMembership table. For example:

INSERT INTO GoalMembership (GoalId, MemberId) VALUES ('1161963899354167', '1126938837961830')

Delete

Following is an example of how to delete from GoalMembership table:

DELETE FROM GoalMembership WHERE Id = '1161963899354167'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique Id of the goal membership.
GoalId	String	False	Goals.Id	Globally unique identifier for goal.
GoalName	String	True		The name of the goal.
GoalOwnerGid	String	True		Globally unique identifier of the goal owner.
GoalOwnerName	String	True		The name of the goal owner.
Role	String	True		Describes if the member is a commenter or editor in goal.
MemberId	String	False		Globally unique identifier of the member of the goal.
MemberName	String	True		The name of the member.
MemberResourceType	String	True		The type of the member (team or user).
ResourceSubtype	String	True		The type of membership.
ResourceType	String	True		The base type of this resource.

To Create, Update, Delete and Query the Goals table.

Table Specific Information

Select

The Cloud will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports the '=' comparison.
• IsWorkspaceLevel supports the '=' comparison.
• TeamId supports the '=' comparison.
• TimeId supports the '=' comparison.
• WorkspaceId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Goals

SELECT * FROM Goals WHERE Id = '1126938837961834'

SELECT * FROM Goals WHERE IsWorkspaceLevel = 'false'

SELECT * FROM Goals WHERE TeamId = '122334345323466' AND WorkspaceId = '1126938837961830'

SELECT * FROM Goals WHERE TimeId = '1243436322145547'

SELECT * FROM Goals WHERE WorkspaceId = '1126938837961830'

Insert

UserId and Goals is a mandatory column for inserting into Goals table. For example:

INSERT INTO Goals (Name, DueOn, StartOn, IsWorkspaceLevel, liked, WorkspaceId, OwnerId, TimePeriodId) VALUES ('Sample Goal', '2023-10-31', '2023-09-15', 'false', 'false', '1203124527069362', '1203124437606434', '1203124434984659' )

Update

Following is an example of how to update a Goals table:

UPDATE Goals SET DueOn ='2023-10-20' WHERE Id = '1128092864672888'

Delete

Following is an example of how to delete from Goals table:

DELETE FROM Goals WHERE Id = '1161963899354167'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		Globally unique Id of the goal.
ResourceType	String	True		The base type of this resource.
Name	String	False		The name of the goal.
OwnerId	String	False		Globally unique Id of the owner.
OwnerName	String	True		The name of the owner.
OwnerResourceType	String	True		The base type of this resource.
DueOn	Date	False		The localized day on which this goal is due.
StartOn	Date	False		The day on which work for this goal begins.
CurrentStatusUpdate	String	True		The latest status_update posted to this goal.
Followers	String	False		Array of users who are members of this goal.
IsWorkspaceLevel	Boolean	False		Whether the goal belongs to the workspace.
Liked	Boolean	False		True if the goal is liked by the authorized user, false if not.
Likes	String	True		Array of likes for users who have liked this goal.
MetricId	String	True		Globally unique identifier of the metric.
MetricCurrencyCode	String	True		ISO 4217 currency code to format this custom field. This will be null if the unit is not currency.
MetricCurrentDisplayValue	String	True		This string is the current value of a goal metric of type string.
MetricCurrentNumberValue	Integer	True		This number is the current value of a goal metric of type number.
MetricInitialNumberValue	Integer	True		This number is the start value of a goal metric of type number.
MetricPrecision	Integer	True		Only relevant for goal metrics of type 'Number'. This field dictates the number of places after the decimal to round to, i.e. 0 is integer values, 1 rounds to the nearest tenth, and so on. Must be between 0 and 6, inclusive.
MetricProgressSource	String	True		This field defines how the progress value of a goal metric is being calculated.
MetricResourceSubtype	String	True		The subtype of this resource.
MetricResourceType	String	True		The base type of this resource.
MetricTargetNumberValue	Integer	True		This number is the end value of a goal metric of type number. This number cannot equal initial_number_value.
MetricUnit	String	True		A supported unit of measure for the goal metric, or none. 使用できる値は次のとおりです。none, currency, percentage
Notes	String	False		Free-form textual information associated with the goal.
HtmlNotes	String	False		The notes of the goal with formatting as HTML.
NumLikes	Integer	True		The number of users who have liked this goal.
Status	String	False		The current status of this goal. When the goal is open, its status can be green, yellow, and red to reflect 'On Track', 'At Risk', and 'Off Track', respectively. When the goal is closed, the value can be missed, achieved, partial, or dropped.
TeamId	String	False	Teams.Id	Globally unique identifier of the team.
TeamName	String	True		The name of the team.
TimePeriodId	String	False		Globally unique identifier of the time period.
TimePeriodDisplayName	String	True		A string representing the cadence code and the fiscal year.
TimePeriodStartOn	Date	True		The localized end date of the time period.
TimePeriodEndOn	Date	True		The localized end date of the time period.
TimePeriodPeriod	String	True		The cadence and index of the time period. The value is one of: FY, H1, H2, Q1, Q2, Q3, or Q4. 使用できる値は次のとおりです。FY, H1, H2, Q1, Q2, Q3, Q4
WorkspaceId	String	False	Workspaces.Id	Globally unique identifier of the workspace.
WorkspaceName	String	True		The name of the workspace.

To Create, delete, and Query Items in the Portfolio.

Table Specific Information

Select

The Cloud will use the Asana API to process WHERE clause conditions built with the following column and operator. The PortfolioId is required to make a request and rest of the filter is executed client side within the Cloud.

• PortfolioId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM PortfolioItems WHERE PortfolioId = '1173026173301164'

Insert

ProjectId and PortfolioId is a mandatory column for inserting into PortfolioItems table. For example:

INSERT INTO PortfolioItems (ProjectId, PortfolioId) VALUES ('1173023668354420', '1173026173301164')

Delete

Following is an example of how to delete from PortfolioItems table:

DELETE FROM PortfolioItems WHERE ProjectId = '1173023668354420' AND PortfolioId = '1173026173301164'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the Portfolio Item.
Name	String	True		Name of the Item.
PortfolioId [KEY]	String	True	Portfolios.Id	Globally unique ID of the portfolio.
OwnerId	String	True		Globally unique ID of the Owner.
StartOn	Date	True		The day on which work for this project begins, or null if the project has no start date.
DueOn	Date	True		The day on which this project is due. This takes a date with format YYYY-MM-DD.
ProjectId	String	True	Projects.Id	Globally unique ID of the Project. This column is a Pseudocolmn and its used for INSERT and UPDATE.

To Create, Delete and Query memberships in the Portfolio.

Table Specific Information

Select

The Cloud will use the Asana API to process WHERE clause conditions built with the following column and operator. The [WorkspaceId AND UserId] OR PortfolioId OR Id is required to make a request and rest of the filter is executed client side within the Cloud.

• Id supports the '=' comparison.
• WorkspaceId supports the '=' comparison.
• UserId supports the '=' comparison.
• PortfolioId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM PortfolioMembership WHERE WorkspaceId = '1172686716695308' AND UserId = '1172686741877880'

SELECT * FROM PortfolioMembership WHERE PortfolioId = '1172684730399265'

SELECT * FROM PortfolioMembership WHERE Id = '1172684730399266'

Insert

PortfolioId and UserId is a mandatory column for inserting into PortfolioMembership table. For example:

INSERT INTO PortfolioMembership (PortfolioId, UserId) VALUES ('1172684730399265', '1172686741877880')

Delete

Following is an example of how to delete from PortfolioMembership table:

DELETE FROM PortfolioMembership WHERE PortfolioId = '1172684730399265' AND UserId = '1172686741877880'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the portfoolio membership.
PortfolioId [KEY]	String	True	Portfolios.Id	The portfolio the user is a member of.
PortfolioName	String	True		The name of the portfolio.
UserId [KEY]	String	True	Users.Id	Globally unique ID of the user.
UserName	String	True		The users name.
ResourceType	String	True		The resource type of this resource.
WorkspaceId	String	True	Workspaces.Id	Globally unique ID of the workspace. This column is a Pseudocolmn and its used in SELECT Query.

To Create, update, delete, and query from Portfolios table.

Table Specific Information

Select

The Cloud will use the Asana API to process WHERE clause conditions built with the following column and operator. The [WorkspaceId AND OwnerId] OR Id is required to make a request and rest of the filter is executed client side within the Cloud.

• Id supports the '=' comparison.
• WorkspaceId supports the '=' comparison.
• OwnerId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Portfolios WHERE WorkspaceId = '1172686716695308' AND OwnerId = '1172686741877880'

SELECT * FROM Portfolios WHERE Id = '1172684730399265'

Insert

WorkspaceId is a mandatory column for inserting into Portfolios table. For example:

INSERT INTO Portfolios (Name, Color, WorkspaceId)  VALUES ('Test', 'dark-pink', '1172686716695308'

Update

Following is an example of how to update a Portfolios table:

"UPDATE Portfolios SET Name = 'IT-Test' WHERE Id = '1173026173301164'

Delete

Following is an example of how to delete from Portfolios table:

DELETE FROM Portfolios WHERE Id = '1172684730399265'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the portfolio.
Name	String	False		Name of the portfolio.
Color	String	False		Color of the portfolio. 使用できる値は次のとおりです。dark-pink, dark-green, dark-blue, dark-red, dark-teal, dark-brown, dark-orange, dark-purple, dark-warm-gray, light-pink, light-green, light-blue, light-red, light-teal, light-brown, light-orange, light-purple, light-warm-gray
CreatedAt	Datetime	True		The time at which this portfolio was created.
CreatorId	String	True		Globally unique ID of the user created this portfolio.
CreatorName	String	True		The users name.
Members	String	False		Array of users who are members of this portfolio.
OwnerId	String	False	Users.Id	Globally unique ID of the Owner.
OwnerName	String	True		The Owners Name.
ResourceType	String	True		The base type of this resource.
WorkspaceId	String	False	Workspaces.Id	The workspace or organization that the portfolio belongs to.
WorkspaceName	String	True		Name of the workspace.

To Create, Delete and Query memberships in the Project.

Table Specific Information

Select

The Cloud will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports the '=' comparison.
• ProjectId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM ProjectMembership WHERE Id = '1127092449876445'

SELECT * FROM ProjectMembership WHERE ProjectId = '1127092449876444'

Insert

UserId and ProjectId is a mandatory column for inserting into ProjectMembership table. For example:

INSERT INTO ProjectMembership (UserId, ProjectId) VALUES ('1161963899354167', '1128092964672848')

Delete

Following is an example of how to delete from ProjectMembership table:

DELETE FROM ProjectMembership WHERE UserId = '1161963899354167' AND ProjectId = '1128092964672848'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the project membership.
ProjectId [KEY]	String	False	Projects.Id	The project the user is a member of.
WriteAccess	String	True		Whether the user has full access to the project or has comment-only access.
UserId [KEY]	String	False	Users.Id	The user in the membership.
ResourceType	String	True		The resource type of this resource.

To Create, Update, Delete and Query the Projects table.

Table Specific Information

Select

The Cloud will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports the '=' comparison.
• workspaceId supports the '=' comparison.
• TaskId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM projects WHERE workspaceId = '1126938837961830'

SELECT * FROM projects WHERE Id = '1128092864672888'

SELECT * FROM projects WHERE TaskId = '1130092964672468'

Insert

WorkspaceId or TeamId is a mandatory column for inserting into Projects table. For example:

INSERT INTO Projects (Name, Color, StartOn, WorkspaceId, DueOn, public, UserId) VALUES ('ProjectName', 'dark-pink', '2020-03-01', '1126938837961830', '2020-04-30', false, '1130062547672468')

Update

Following is an example of how to update a Projects table:

UPDATE Projects SET TeamId = '1129514033997890' WHERE Id = '1128092864672888'

Delete

Following is an example of how to delete from Projects table:

DELETE FROM Projects WHERE Id = '1128092864672888'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the project.
Name	String	False		Name of the project. This is generally a short sentence fragment that fits on a line in the UI for maximum readability. However, it can be longer.
Color	String	False		Color of the project. Must be either null or one of: dark-pink, dark-green, dark-blue, dark-red, dark-teal, dark-brown, dark-orange, dark-purple, dark-warm-gray, light-pink, light-green, light-blue, light-red, light-teal, light-yellow, light-orange, light-purple, light-warm-gray. 使用できる値は次のとおりです。dark-pink, dark-green, dark-blue, dark-red, dark-teal, dark-brown, dark-orange, dark-purple, dark-warm-gray, light-pink, light-green, light-blue, light-red, light-teal, light-brown, light-orange, light-purple, light-warm-gray, green, yellow, red
CreatedAt	Datetime	True		The time at which this project was created.
ModifiedAt	Datetime	True		The time at which this project was last modified.
StartOn	Date	False		The day on which work for this project begins, or null if the project has no start date. This takes a date with YYYY-MM-DD format. Note: due_on must be present in the request when setting or unsetting the start_on parameter.
DueOn	Date	False		The day on which this project is due. This takes a date with format YYYY-MM-DD.
DefaultView	String	False		The default view of a project. 使用できる値は次のとおりです。list, board, calendar, timeline
Followers	String	False		Followers are a subset of members who receive all notifications for a project.
Archived	Boolean	False		True if the project is archived, false if not. Archived projects do not show in the UI by default and may be treated differently for queries.
UserId	String	False	Users.Id	The current owner of the project.
TeamId	String	False	Teams.Id	The team that this project is shared with. This field only exists for projects in organizations.
WorkspaceId	String	True	Workspaces.Id	The workspace or organization this project is associated with. Once created, projects cannot be moved to a different workspace. This attribute can only be specified at creation time.
Public	Boolean	False		True if the project is public to the organization. If false, do not share this project with other users in this organization without explicitly checking to see if they have access.
Notes	String	False		More detailed, free-form textual information associated with the project.
HTMLNotes	String	False		The notes of the project with formatting as HTML.
IsTemplate	Boolean	False		Determines if the project is a template.
Layout	String	True		The layout (board or list view) of a project.
Members	String	True		Array of users who are members of this project.
SectionMigrationStatus	String	True		The section migration status of this project.
TaskId	String	True		Globally unique ID of the Task.

To Create, Delete and Query from ProjectStatuses table. A project status is an update on the progress of a particular project, and is sent out to all project followers when created.

Table Specific Information

Select

The Cloud will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports the '=' comparison.
• ProjectId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM ProjectStatuses WHERE Id = '1127092449876445'

SELECT * FROM ProjectStatuses WHERE ProjectId = '1127092449876444'

Insert

ProjectId is a mandatory column for inserting into ProjectStatuses table. For example:

INSERT INTO projectstatuses (Color, Title, Text, ProjectId) VALUES ('red', 'Status1', 'StatusNotes', '1127092449876444')

Delete

Following is an example of how to delete from ProjectStatuses table:

DELETE FROM ProjectStatuses WHERE Id = '1127092449876445'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the project status update.
Color	String	True		The color associated with the status update. 使用できる値は次のとおりです。green, yellow, red
Title	String	True		The title of the project status update.
Text	String	True		The text content of the status update.
CreatedAt	Datetime	True		The time at which the status update was created.
CreatedBy	String	True		The creator of the status update.
ProjectId	String	True	Projects.Id	Globally unique ID of the project.
ResourceType	String	True		The resource type of this resource. The value for this resource is always project_status.
HTMLText	String	True		The notes of the project with formatting as HTML.

To Create, Update, Delete, and Query from Sections table. A section is a subdivision of a project that groups tasks together.

Table Specific Information

Select

The Cloud will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports the '=' comparison.
• ProjectId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Sections WHERE Id = '1127092449876457'

SELECT * FROM Sections WHERE ProjectId = '1128092964672848'

Insert

ProjectId is a mandatory column for inserting into Sections table. For example:

INSERT INTO Sections (Name, ProjectId) VALUES ('SectionName', '1128092964672848')

Update

Following is an example of how to update a Sections table:

UPDATE Sections SET Name = 'IT-Pending' WHERE Id = '1127092449876457'

Delete

Following is an example of how to delete from Sections table:

DELETE FROM Sections WHERE Id = '1127092449876457'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the section.
Name	String	False		The name of the section.
CreatedAt	Datetime	True		The time at which the section was created.
ResourceType	String	True		The resource type of this resource.
ProjectId	String	False	Projects.Id	The section under the project.

To Create, Update, Delete and Query from stories table. A story represents an activity associated with an object in the Asana system. Stories are generated by the system whenever users take actions such as creating or assigning tasks, or moving tasks between projects.

Table Specific Information

Select

The Cloud will use the Asana API to process WHERE clause conditions built with the following column and operator. The TaskId or Id is required to make a request and rest of the filter is executed client side within the Cloud.

• Id supports the '=' comparison.
• TaskId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Stories WHERE Id = '1126938691750986'

SELECT * FROM Stories WHERE TaskId = '1129514033997892'

Insert

TaskId is a mandatory column for inserting into Stories table. For example:

INSERT INTO Stories (Text, TaskId, IsPinned) VALUES ('InProgress', '1129514033997892', false)

Update

Following is an example of how to update a Stories table:

UPDATE Stories SET Text = 'story line' WHERE Id = '1126938691750986'

Delete

Following is an example of how to delete from Stories table:

DELETE FROM Stories WHERE Id = '1126938691750986'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the story.
Text	String	False		Formatted text for the story or comment.
CreatedAt	Datetime	True		The time at which this story was created.
creatorId	String	True		The user who created the story.
creatorName	String	False	Users.Name	The user who created the story.
Source	String	True		The component of the Asana product the user used to create the story.
TaskId	String	False	Tasks.Id	The story under the task.
Liked	Boolean	True		True if the story is liked by the authorized user, false if not.
IsPinned	Boolean	False		This field is only present on comment and attachment stories.
IsEdited	Boolean	True		Whether the text of the story has been edited after creation.
ResourceType	String	True		The resource type of this resource.
ResourceSubType	String	True		The type of story. This provides fine-grained information about what triggered the story creation.
HtmlText	String	True		HTML formatted text for a comment.
DuplicateTaskOf	String	True		The task is the basic object around which many operations in Asana are centered.
DependencyTaskId	String	True		The task is the basic object around which many operations in Asana are centered.
NumberofLikes	Integer	True		The number of users who have liked this story.
Likes	String	True		Array of likes for users who have liked this story.

To Create, update, delete, and query from SubTasks table.

Table Specific Information

Select

The Cloud will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• ParentTaskId supports the '=' comparison.

For example, the following query is processed on server side and it is a required filter:

SELECT * FROM SubTasks WHERE ParentTaskId = '1127092449876457'

A simple SELECT query will only return the first level of subtasks.

SELECT * FROM SubTasks

There may be up to five levels of subtasks below a task. (Note, Asana does not recommend using sub-subtasks). If additional levels of subtasks are needed, a subquery can be used:

SELECT * FROM SubTasks WHERE ParentTaskId IN (SELECT Id FROM SubTasks)

When executing a query without a ParentTaskId filter, due to the Asana API design, subtasks are only retrieved on a per task basis. The Cloud will first query the workspace to retrieve a list of projects. A separate request will then be made for each project to retrieve all the applicable tasks. Another request will be made for each task to retrieve any subtasks associated with the task. In theory, this is similar to executing the following query:

SELECT * FROM SubTasks WHERE ParentTaskId IN (SELECT Id FROM Tasks WHERE ProjectId IN (SELECT Id FROM Projects))

Due to the number of required requests, performance may be impacted.

Insert

ParentTaskId is a mandatory column for inserting into Projects table. For example:

INSERT INTO SubTasks (ParentTaskId, Name, Assignee, AssigneeStatus, startOn, DueOn, Completed, ProjectId, Notes, ApprovalStatus, Followers) VALUES ('1167713816822372', 'subtask1', '1167715005778471', 'later', '2020-03-25', '2020-04-01', false, 1167713816822364, 'SubTask Notes', 'changes_requested', '1167715005778471')

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the Subtask.
ParentTaskId	String	True		Globally unique ID of the task.
Name	String	False		Name of the Subtask.
Assignee	String	False		User to which this subtask is assigned.
AssigneeStatus	String	False		Scheduling status of this subtask for the user it is assigned to. 使用できる値は次のとおりです。today, upcoming, later, new, inbox
CreatedAt	Datetime	True		The time at which this subtask was created.
ModifiedAt	Datetime	True		The time at which this subtask was last modified.
startOn	Date	False		The day on which work begins for the task , or null if the task has no start date. This takes a date with YYYY-MM-DD format.
DueAt	Datetime	False		Date and time on which this subtask is due, or null if the subtask has no due time.
DueOn	Date	False		Date on which this task is due, or null if the task has no due date. This takes a date with YYYY-MM-DD format.
Completed	Boolean	False		True if the subtask is currently marked complete, false if not.
CompletedAt	Datetime	True		The time at which this subtask was completed, or null if the subtask is incomplete.
completed_by	String	True		The user who completed this task.
ProjectId	String	False	Projects.Id	Globally unique ID of the Project.
WorkspaceId	String	False	Workspaces.Id	Globally unique ID of the Workspace.
Notes	String	False		More detailed, free-form textual information associated with the subtask.
ApprovalStatus	String	False		Reflects the approval status of this task. 使用できる値は次のとおりです。pending, approved, rejected, changes_requested
ResourceSubType	String	False		The subtype of this resource. Different subtypes retain many of the same fields and behavior, but may render differently in Asana or represent resources with different semantic meaning. 使用できる値は次のとおりです。default_task, milestone, section, approval
HtmlNotes	String	False		The notes of the text with formatting as HTML.
Dependencies	String	True		Array of resources referencing tasks that this task depends on.
Memberships	String	True		Array of projects this task is associated with and the section it is in.
Dependents	String	True		Array of resources referencing tasks that depend on this task.
Liked	Boolean	False		True if the task is liked by the authorized user, false if not.
NumberofLikes	Integer	True		The number of users who have liked this Task.
LikedBy	String	True		Array of likes for users who have liked this task.
ResourceType	String	True		The base type of this resource.
IsRenderedAsSeparator	Boolean	True		In some contexts tasks can be rendered as a visual separator.
Followers	String	False		Array of users following this task.
Tags	String	False		Array of tags associated with this task.

To Create, Update and Query from Tags table. A tag is a label that can be attached to any task in Asana. It exists in a single workspace.

Table Specific Information

Select

The Cloud will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports the '=' comparison.
• WorkspaceId supports the '=' comparison.
• TaskId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Tags WHERE Id = '1129517083601713'

SELECT * FROM Tags WHERE WorkspaceId = '1126938837961830'

SELECT * FROM Tags WHERE TaskId = '1128092964672854'

Insert

Workspaceid is a mandatory column for inserting into Tags table. For example:

INSERT INTO Tags (Name, Color, WorkspaceId, Notes) VALUES ('TestTag', 'dark-blue', '1126938837961830', 'sample notes')

Update

Following is an example of how to update a Tags table:

UPDATE Tags SET Name = 'IT-1' WHERE Id = '1158271967831255'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the tag.
Name	String	True		Name of the tag.
CreatedAt	Datetime	True		The time at which this tag was created.
Color	String	True		Color of the tag. 使用できる値は次のとおりです。dark-pink, dark-green, dark-blue, dark-red, dark-teal, dark-brown, dark-orange, dark-purple, dark-warm-gray, light-pink, light-green, light-blue, light-red, light-teal, light-brown, light-orange, light-purple, light-warm-gray
Notes	String	False		Notes of the tag.
Followers	String	True		Followers for the tag.
WorkspaceId	String	True	Workspaces.Id	The workspace or organization this tag is associated with.
TaskId	String	True	Tasks.Id	The task this tag is mapped with.

To Create, Update, Delete and Query from Tasks table.

Table Specific Information

Select

The Cloud will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports the '=' comparison.
• ProjectId supports the '=' comparison.
• Assignee supports the '=' comparison.
• TagId supports the '=' comparison.
• SectionId supports the '=' comparison.
• UserTaskListId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Tasks WHERE Id = '1127092449876457'

SELECT * FROM Tasks WHERE ProjectId = '1128092964672848'

SELECT * FROM Tasks WHERE Assignee = '1126938691750986' AND WorkspaceId = '1126938837961830'

SELECT * FROM Tasks WHERE Assignee = '1126938691750986'

SELECT * FROM tasks WHERE TagId = '1129517083601713'

SELECT * FROM tasks WHERE SectionId = '1127092449876457'

SELECT * FROM tasks WHERE UserTaskListId = '1126938837961837'

When executing a query without one of the above filters, due to the Asana API design, tasks must be retrieved per project. The Cloud will first query the workspace to retrieve a list of projects. A separate request will then be made for each project to retrieve all the applicable tasks. In theory, this is similar to executing the following query:

SELECT * FROM Tasks WHERE ProjectId IN (SELECT Id FROM Projects)

Due to the number of required requests, performance may be impacted. Note, tasks may also be orphaned and not belong to a project. The Cloud will not be able to retrieve orphaned tasks.

Insert

ProjectId is a mandatory column for inserting into Tasks table. For example:

INSERT INTO Tasks (Name, Assignee, AssigneeStatus, startOn, DueOn, Completed, ProjectId, Notes, ApprovalStatus, Followers) VALUES ('Task1', '1167715005778471', 'later', '2020-03-25', '2020-04-01', false, 1167713816822364, 'TaskNotes', 'changes_requested', '1167715005778471')

Update

Following is an example of how to update a Tasks table:

UPDATE Tasks SET Browser = 'ie' WHERE Id = '1167713816822371'

Delete

Following is an example of how to delete from Tasks table:

DELETE FROM Tasks WHERE Id = '1167713816822371'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the task.
Name	String	False		Name of the task.
Assignee	String	False		User to which this task is assigned.
AssigneeStatus	String	False		Scheduling status of this task for the user it is assigned to. 使用できる値は次のとおりです。today, upcoming, later, new, inbox
CreatedAt	Datetime	True		The time at which this task was created.
ModifiedAt	Datetime	True		The time at which this task was last modified.
startOn	Date	False		Date and time on which this task is due, or null if the task has no due time.
DueAt	Datetime	False		Date and time on which this task is due, or null if the task has no due time.
DueOn	Date	False		Date on which this task is due, or null if the task has no due date. This takes a date with YYYY-MM-DD format.
Completed	Boolean	False		True if the task is currently marked complete, false if not.
CompletedAt	Datetime	True		The time at which this task was completed, or null if the task is incomplete.
CompletedBy	String	True		The user who completed this task.
ProjectId	String	False	Projects.Id	Projectid of this task is associated with.
Projects	String	False		Projects of this task is associated with.
WorkspaceId	String	False	Workspaces.Id	Globally unique ID of the Workspace.
Notes	String	False		More detailed, free-form textual information associated with the task.
NumberofSubTasks	Integer	True		The number of subtasks on this task.
ApprovalStatus	String	False		Reflects the approval status of this task. 使用できる値は次のとおりです。pending, approved, rejected, changes_requested
ResourceSubType	String	False		The subtype of this resource. Different subtypes retain many of the same fields and behavior, but may render differently in Asana or represent resources with different semantic meaning. 使用できる値は次のとおりです。default_task, milestone, section, approval
HtmlNotes	String	False		The notes of the text with formatting as HTML.
Memberships	String	True		Array of projects this task is associated with and the section it is in.
Dependencies	String	True		Array of resources referencing tasks that this task depends on.
Dependents	String	True		Array of resources referencing tasks that depend on this task.
Liked	Boolean	True		True if the task is liked by the authorized user, false if not.
NumberofLikes	Integer	True		The number of users who have liked this Task.
LikedBy	String	True		Array of likes for users who have liked this task.
ResourceType	String	True		The base type of this resource.
IsRenderedAsSeparator	Boolean	True		In some contexts tasks can be rendered as a visual separator.
Followers	String	True		Array of users following this task.
Tags	String	False		Array of tags associated with this task.
SectionId	String	True	Sections.Id	PseudoColumn: Globally unique ID of the section.
UserTaskListId	String	True	WorkspaceMembership.UserTaskListId	PseudoColumn: A user task list represents the tasks assigned to a particular user.
TagId	String	True	Tags.Id	PseudoColumn: Globally unique ID of the tag.

To represents a users connection to a team.

Table Specific Information

Select

The Cloud will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports the '=' comparison.
• UserId supports the '=' comparison.
• WorkspaceId supports the '=' comparison.
• TeamId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM TeamMembership WHERE UserId = '1126938691750986' AND WorkspaceId = '1126938837961830'

SELECT * FROM TeamMembership WHERE TeamId = '1126938837961832'

SELECT * FROM TeamMembership WHERE Id = '1126938837961832'

Insert

UserId and TeamId is a mandatory column for inserting into TeamMembership table. For example:

INSERT INTO TeamMembership (UserId, TeamId) VALUES ('1161963899354167', '1126938837961832')

Delete

Following is an example of how to delete from TeamMembership table:

DELETE FROM TeamMembership WHERE UserId = '1161963899354167' AND TeamId = '1126938837961832'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique identifier of the resource, as a string.
userId	String	True		Globally unique ID of the user.
userName	String	True		The users name.
TeamId	String	True		Globally unique ID of the team.
TeamName	String	True		The name of the team.
IsGuest	Boolean	True		Describes if the user is a guest in the team.
ResourceType	String	True		The base type of this resource.
WorkspaceId	String	True		Globally unique ID of the workspace.

A team is used to group related projects and people together within an organization. Each project in an organization is associated with a team.

Table Specific Information

Select

The Cloud will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports the '=' comparison.
• WorkspaceId supports the '=' comparison.
• UserId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Teams WHERE Id = '1126938837961832'

SELECT * FROM Teams WHERE WorkspaceId = '1126938837961830'

SELECT * FROM Teams WHERE UserId = '1126938691750986' AND workspaceid = '1126938837961830'

Insert

WorkspaceId is a mandatory column for inserting into Teams table.In the WorkspaceId only the organisation ID can be used to create teams. Teams can not be created for workspaces which are not part of a organisation. For example:

INSERT INTO Teams (Name, Description, HTMLDescrption, WorkspaceId) VALUES ('Salessssk', 'sales developers should be members of this team.', '<body> <em>All</em> developers should be members of this team.</body>', '1126938837961830')

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the team.
Name	String	True		The name of the team.
Description	String	True		The description of the team.
WorkspaceId	String	True	Workspaces.Id	The workspace or organization this team is associated with.
HTMLDescrption	String	True		The description of the team with formatting as HTML.
ResourceType	String	True		The resource type of this resource.
UserId	String	True	Users.Id	Globally unique ID of the user.

To Create, Delete and Query memberships in the Workspace.

Table Specific Information

Select

The Cloud will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports the '=' comparison.
• UserId supports the '=' comparison.
• WorkspaceId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM WorkspaceMembership WHERE WorkspaceId = '1126938837961830'

SELECT * FROM WorkspaceMembership WHERE UserId = '1126938691750986'

SELECT * FROM WorkspaceMembership WHERE Id = '1126938837961834'

Insert

UserId and WorkspaceId is a mandatory column for inserting into WorkspaceMembership table. For example:

INSERT INTO WorkspaceMembership (UserId, WorkspaceId) VALUES ('1161963899354167', '1126938837961830')

Delete

Following is an example of how to delete from WorkspaceMembership table:

DELETE FROM WorkspaceMembership WHERE UserId = '1161963899354167' AND WorkspaceId = '1126938837961830'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the membership.
userId [KEY]	String	True	Users.Id	Globally unique ID of the user.
userName	String	True		The user`s name.
WorkspaceId [KEY]	String	True	Workspaces.Id	Globally unique ID of the workspace.
WorkspaceName	String	True		The Workspace name.
IsActive	Boolean	True		Reflects if this user still a member of the workspace.
IsAdmin	Boolean	True		Reflects if this user is an admin of the workspace.
IsGuest	Boolean	True		Reflects if this user is a guest of the workspace.
UserTaskListId	String	True		A user task list represents the tasks assigned to a particular user.
ResourceType	String	True		The base type of this resource.
VacationStartDate	String	True		The day on which the user's vacation in this workspace starts.
VacationEndDate	String	True		The day on which the user's vacation in this workspace ends.

To Update and Query from Workspaces table. A workspace is the highest-level organizational unit in Asana. All projects and tasks have an associated workspace.

Table Specific Information

Select

The Cloud will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports the '=' comparison.
• UserId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Workspaces WHERE Id = '1126938837961835'

SELECT * FROM Workspaces WHERE UserId = '1126938691750986'

Update

Following is an example of how to update a Workspaces table:

UPDATE Workspaces SET Name = 'IT-Eng' WHERE Id = '1126938837961835'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the workspace.
Name	String	False		The name of the workspace.
ResourceType	String	True		The resource type of this resource.
IsOrganization	Boolean	True		Whether the workspace is an organization.
EmailDomains	String	True		Whether the workspace is an organization.
UserId	String	True	Users.Id	Globally unique ID of the user.

ビューは、データを示すという点でテーブルに似ていますが、ビューは読み取り専用です。

クエリは、ビューに対して通常のテーブルと同様に実行することができます。

CData Cloud - Asana ビュー

Name	Description
CustomFields	To view the Custom Fields in the project.
Events	To view the events in projects and tasks.
ProjectFollowers	To view the Project follower details.
ProjectTemplates	TO get ProjectTemplates in the Team or workspace.
TaskStatuses	Get task count of a project.
Users	To view the users details.
UserTaskList	Generated schema file.

To view the Custom Fields in the project.

Table Specific Information

Select

The Cloud uses the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side in the Cloud.

• ProjectId supports the '=' comparison.

For example, the following query is processed server side:

SELECT * FROM CustomFields WHERE ProjectId = '1126938691750986'

Columns

Name	Type	References	Description
Id	String		Globally unique identifier of the resource.
Name	String		The name of the custom field.
Description	String		The description of the custom field.
Type	String		The type of the custom field. It must be one of the given values.
ProjectId	String		Globally unique Id of the project.
ProjectName	String		Name of the project.
IsImportant	Boolean		Name of the project.
Format	String		The format of the custom field.
TextValue	String		The value of a text custom field.
NumberValue	Double		The value of a number custom field.
Precision	Integer		Only relevant for custom fields of type 'Number'. This field specifies the number of digits after the decimal to round to.
CurrencyCode	String		The currency code for this custom field. The value is null if the format is not currency.
EnumOptions	String		Array of projects this task is associated with and the section it is in.
CreatedBy	String		Globally unique user identifier.
CreatedByName	String		The user's name.
IsGlobaltoWorkspace	Boolean		A flag that specifies whether this custom field is available to every container in the workspace.
HasNotificationsEnabled	Boolean		A flag that specifies whether a follower of a task with this field should receive inbox notifications about changes in this field.

To view the events in projects and tasks.

Table Specific Information

Select

The Cloud will use the Asana API to process WHERE clause conditions built with the following column and operator. The resource id will be either Project or Task Id. The rest of the filter is executed client side within the Cloud.

• ResourceId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Events WHERE ResourceId = '1128092964672854'

Columns

Name	Type	References	Description
ResourceId [KEY]	String		The resource GID for the events were requested for.
ResourceType	String		The resource type for the events were requested for.
ResourceSubType	String		The resource sub type for the events were requested for.
ResourceName	String		The resource name for the events were requested for.
Parent	String		For added/removed events, the parent that resource was added to or removed from. null for other event types.
CreatedAt	Datetime		The timestamp when the event occurred.
UserId	String		The event may be triggered by a different user GID than the subscriber.
UserFullname	String		The event may be triggered by a different user name than the subscriber.
Action	String		The type of action taken that triggered the event.

To view the Project follower details.

Columns

Name	Type	References	Description
ProjectId	String	Projects.Id	Globally unique ID of the Project.
UserId	String		Globally unique ID of the user.
UserName	String		The user`s name.
ResourceType	String		The resource type of this resource.
WorkspaceId	String	Workspaces.Id	The workspace this Project is associated with.

TO get ProjectTemplates in the Team or workspace.

Columns

Name	Type	References	Description
Id [KEY]	String		Globally unique identifier of the resource, as a string.
Color	String		Color of the project template.
Description	String		Free-form textual information associated with the project template.
Html_description	String		The description of the project template with formatting as HTML.
Name	String		Name of the project template.
OwnerId	String		A user object. Globally unique identifier of the resource, as a string.
OwnerName	String		A user object. The base type of this resource.
OwnerResourceType	String		A user object. Read-only except when same user as requester. The name of the User.
IsPublic	Boolean		True if the project template is public to its team.
RequestedDates	String		Array of date variables in this project template. Calendar dates must be provided for these variables when instantiating a project.
ResourceType	String		The base type of this resource.
TeamId	String	Teams.Id	A team is used to group related projects and people together within an organization. Globally unique identifier of the resource, as a string.
TeamName	String		A team is used to group related projects and people together within an organization. The name of the team.
TeamResourceType	String		A team is used to group related projects and people together within an organization. The base type of this resource.

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
WorkspaceId	String		The workspace Id to filter results on.

Get task count of a project.

Table Specific Information

Select

The Cloud will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• ProjectId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM TaskStatuses WHERE ProjectId = '1136437449978157'

Columns

Name	Type	References	Description
ProjectId	String	Projects.Id	Globally unique ID of the project.
NumberofTasks	Integer		The number of tasks in a project.
CompletedTasks	Integer		The number of completed tasks in a project.
IncompleteTasks	Integer		The number of incomplete tasks in a project.
NumberofMilestones	Integer		The number of milestones in a project.
CompletedMilestones	Integer		The number of completed milestones in a project.
InCompleteMilestones	Integer		The number of incomplete milestones in a project.

To view the users details.

Table Specific Information

Select

The Cloud will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports the '=' comparison.
• WorkspaceId supports the '=' comparison.
• TeamId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Users WHERE Id = '1126938691750986'

SELECT * FROM Users WHERE WorkspaceId = '1126938837961835'

SELECT * FROM Users WHERE TeamId = '1129514033997892'

Columns

Name	Type	References	Description
Id [KEY]	String		Globally unique ID of the user.
Name	String		The user`s name.
Email	String		The user`s email address.
ResourceType	String		The resource type of this resource.
WorkspaceId	String	Workspaces.Id	The User access in workspaces.
TeamId	String	Teams.Id	Globally unique ID of the team.

Generated schema file.

Table Specific Information

Select

The Cloud will use the Asana API to process WHERE clause conditions built with the following column and operator. The [UserId and workspaceId] or Id is required to make a request and the rest of the filter is executed client side within the Cloud.

• Id supports the '=' comparison.
• UserId supports the '=' comparison.
• workspaceId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM UserTaskList WHERE Id = '1126938837961837'

SELECT * FROM UserTaskList WHERE UserId = '1126938691750986' AND workspaceId = '1126938837961830'

Columns

Name	Type	References	Description
Id [KEY]	String	WorkspaceMembership.UserTaskListId	Globally unique ID of the user task list.
Name	String		The name of the user task list.
UserId	String	Users.Id	The owner of the user task list.
workspaceId	String	Workspaces.Id	The workspace in which the user task list is located.
ResourceType	String		The resource type of this resource.

ストアドプロシージャはファンクションライクなインターフェースで、Asana の単純なSELECT/INSERT/UPDATE/DELETE 処理にとどまらずCloud の機能を拡張します。

ストアドプロシージャは、パラメータのリストを受け取り、目的の機能を実行し、プロシージャが成功したか失敗したかを示すとともにAsana から関連するレスポンスデータを返します。

CData Cloud - Asana ストアドプロシージャ

Name	Description
AddDependenciesToTask	To set of tasks as dependencies of this task, if they are not already dependencies. A task can have at most 15 dependencies.
AddDependentsToTask	To Set or Unlink dependents to the task.
AddFollowers	To add followers to the tasks or projects.
AddProjectToTask	Add a project to a task.
AddTagsToTask	To Set or Unlink dependents to the task.
AddTaskToSection	Add a task to a specific, existing section. This will remove the task from other sections of the project.
DuplicateProject	To create a duplicate copy of the project.
DuplicateTask	To Set or Unlink dependencies to the task.
MoveSectionsinProject	To reorder the section in the project.
RemoveDependenciesFromTask	To Set or Unlink dependencies to the task.
RemoveDependentsFromTask	To Set or Unlink dependents to the task.
RemoveFollowers	To remove followers from the tasks or projects.
RemoveProjectsFromTask	Remove a project from a task.
RemoveTagsFromTask	To Set or Unlink dependents to the task.
SetParentofTask	To Set the parent of a task.

To set of tasks as dependencies of this task, if they are not already dependencies. A task can have at most 15 dependencies.

Input

Name	Type	Required	Description
TaskId	String	True	The Tasks Id to operate on.
Dependencies	String	True	Task ids to add dependencies. For more than One task Id please separate with comma(,).

Result Set Columns

Name	Type	Description
Status	String	The status of the operation.

To Set or Unlink dependents to the task.

Input

Name	Type	Required	Description
TaskId	String	True	The Tasks Id to operate on.
DependentTaskId	String	True	Task ids to add or remove as dependents. For more than One task Id please separate with comma(,).

Result Set Columns

Name	Type	Description
Status	String	The status of the operation.

To add followers to the tasks or projects.

Input

Name	Type	Required	Description
Id	String	True	The Tasks Id or Project Id to operate on.
User	String	True	Users.Id OR Users.EMail to Add.
TableName	String	True	To add the followers in. 使用できる値は次のとおりです。projects, tasks

Result Set Columns

Name	Type	Description
Status	String	The status of the operation.

Add a project to a task.

Input

Name	Type	Required	Description
TaskId	String	True	The Tasks Id to operate on.
ProjectId	String	True	The project Id to add the task.
SectionId	String	False	A section Id in the project to insert the task into. The task will be inserted at the bottom of the section.
AddAfter	String	False	A task Id in the project to insert the task after, or null to insert at the beginning of the list.
AddBefore	String	False	A task Id in the project to insert the task before, or null to insert at the end of the list.

Result Set Columns

Name	Type	Description
Status	String	The status of the operation.

To Set or Unlink dependents to the task.

Input

Name	Type	Required	Description
TaskId	String	True	The Tasks Id to operate on.
TagId	String	True	Tags Id to add in the task.

Result Set Columns

Name	Type	Description
Status	String	The status of the operation.

Add a task to a specific, existing section. This will remove the task from other sections of the project.

Input

Name	Type	Required	Description
SectionId	String	True	A section Id in the project to move the task into.
TaskId	String	True	The task Id to add to this section.
AddAfter	String	False	An existing task id within this section before which the added task should be inserted. Cannot be provided together with insert_after.
Status	String	False	The status of the operation.

To create a duplicate copy of the project.

Input

Name	Type	Required	Description
ProjectId	String	True	Globally unique identifier for the project.
NewProjectName	String	True	The name of the new project.
Include	String	True	The fields that will be duplicated to the new task. The accepted values are members, notes, task_notes, task_assignee, task_subtasks, task_attachments, task_dates, task_dependencies, task_followers, task_tags, task_projects. For more than One values, please separate with comma(,).
TeamId	String	False	Sets the team of the new project. If team is not defined, the new project will be in the same team as the the original project.
DueOn	Date	False	Sets the last due date in the duplicated project to the given date. The rest of the due dates will be offset by the same amount as the due dates in the original project. This takes a date with format YYYY-MM-DD.
StartOn	Date	False	Sets the first start date in the duplicated project to the given date. The rest of the start dates will be offset by the same amount as the start dates in the original project. This takes a date with format YYYY-MM-DD.
SkipWeekends	Boolean	False	Determines if the auto-shifted dates should skip weekends.

Result Set Columns

Name	Type	Description
Status	String	The status of the operation.

To Set or Unlink dependencies to the task.

Input

Name	Type	Required	Description
TaskId	String	True	The Task Id to operate on.
NewTaskName	String	True	The name of the new task.
Include	String	True	The fields that will be duplicated to the new task. The accepted values are notes,assignee,subtasks,attachments,tags,followers,projects,dates,dependencies,parent. For more than One values please separate with comma(,).

Result Set Columns

Name	Type	Description
Status	String	The status of the operation.

To reorder the section in the project.

Input

Name	Type	Required	Description
ProjectId	String	True	The project in which to reorder the given section.
SectionId	String	True	The section to reorder.
BeforeSection	String	False	Insert the given section immediately before the section specified by this parameter.
AfterSection	String	False	Insert the given section immediately after the section specified by this parameter.

Result Set Columns

Name	Type	Description
Status	String	The status of the operation.

To Set or Unlink dependencies to the task.

Input

Name	Type	Required	Description
TaskId	String	True	The Tasks Id to operate on.
DependentTaskId	String	True	Task ids to add or remove as dependents. For more than One task Id please separate with comma(,).

Result Set Columns

Name	Type	Description
Status	String	The status of the operation.

To Set or Unlink dependents to the task.

Input

Name	Type	Required	Description
TaskId	String	True	The Tasks Id to operate on.
DependentTaskId	String	True	Task ids to add or remove as dependents. For more than One task Id please separate with comma(,).

Result Set Columns

Name	Type	Description
Status	String	The status of the operation.

To remove followers from the tasks or projects.

Input

Name	Type	Required	Description
Id	String	True	The Tasks Id or Project Id to operate on.
User	String	True	Users.Id OR Users.EMail to remove.
TableName	String	True	To remove the followers in. 使用できる値は次のとおりです。projects, tasks

Result Set Columns

Name	Type	Description
Status	String	The status of the operation.

Remove a project from a task.

Input

Name	Type	Required	Description
TaskId	String	True	The Tasks Id to operate on.
ProjectId	String	True	The project Id to add or remove the task to.

Result Set Columns

Name	Type	Description
Status	String	The status of the operation.

To Set or Unlink dependents to the task.

Input

Name	Type	Required	Description
TaskId	String	True	The Tasks Id to operate on.
TagId	String	True	Tags Id to remove from the task.

Result Set Columns

Name	Type	Description
Status	String	The status of the operation.

To Set the parent of a task.

Input

Name	Type	Required	Description
TaskId	String	True	The TaskId to update.
ParentTaskId	String	True	The new parent of the task, or null for no parent..
AddBefore	String	False	A subtask of the parent to insert the task after, or null to insert at the beginning of the list.
AddAfter	String	False	A subtask of the parent to insert the task before, or null to insert at the end of the list.

Result Set Columns

Name	Type	Description
Status	String	The status of the operation.

このセクションで説明されているシステムテーブルをクエリして、スキーマ情報、データソース機能に関する情報、およびバッチ操作の統計にアクセスできます。

スキーマテーブル

以下のテーブルは、Asana のデータベースメタデータを返します。

• sys_catalogs：利用可能なデータベースをリスト。
• sys_schemas：利用可能なスキーマをリスト。
• sys_tables：利用可能なテーブルおよびビューをリスト。
• sys_tablecolumns：利用可能なテーブルおよびビューのカラムについて説明。
• sys_procedures：利用可能なストアドプロシージャについて説明。
• sys_procedureparameters：ストアドプロシージャパラメータについて説明。
• sys_keycolumns：主キーおよび外部キーについて説明。
• sys_indexes：利用可能なインデックスについて説明。

データソーステーブル

以下のテーブルは、データソースへの接続方法およびクエリ方法についての情報を返します。

• sys_connection_props：利用可能な接続プロパティについての情報を返す。
• sys_sqlinfo：Cloud がデータソースにオフロードできるSELECT クエリについて説明。

クエリ情報テーブル

次のテーブルは、データ変更クエリ（バッチ処理を含む）のクエリ統計を返します。

• sys_identity：バッチ処理または単一の更新に関する情報を返す。

利用可能なデータベースをリストします。

次のクエリは、接続文字列で決定されるすべてのデータベースを取得します。

SELECT * FROM sys_catalogs

Columns

Name	Type	Description
CatalogName	String	データベース名。

利用可能なスキーマをリストします。

次のクエリは、すべての利用可能なスキーマを取得します。

          SELECT * FROM sys_schemas
          

Columns

Name	Type	Description
CatalogName	String	データベース名。
SchemaName	String	スキーマ名。

利用可能なテーブルをリストします。

次のクエリは、利用可能なテーブルおよびビューを取得します。

          SELECT * FROM sys_tables
          

Columns

Name	Type	Description
CatalogName	String	テーブルまたはビューを含むデータベース。
SchemaName	String	テーブルまたはビューを含むスキーマ。
TableName	String	テーブル名またはビュー名。
TableType	String	テーブルの種類（テーブルまたはビュー）。
Description	String	テーブルまたはビューの説明。
IsUpdateable	Boolean	テーブルが更新可能かどうか。

利用可能なテーブルおよびビューのカラムについて説明します。

次のクエリは、projects テーブルのカラムとデータ型を返します。

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

Columns

Name	Type	Description
CatalogName	String	テーブルまたはビューを含むデータベースの名前。
SchemaName	String	テーブルまたはビューを含むスキーマ。
TableName	String	カラムを含むテーブルまたはビューの名前。
ColumnName	String	カラム名。
DataTypeName	String	データ型の名前。
DataType	Int32	データ型を示す整数値。この値は、実行時に環境に基づいて決定されます。
Length	Int32	カラムのストレージサイズ。
DisplaySize	Int32	指定されたカラムの通常の最大幅（文字数）。
NumericPrecision	Int32	数値データの最大桁数。文字データおよび日時データの場合は、カラムの長さ（文字数）。
NumericScale	Int32	カラムのスケール（小数点以下の桁数）。
IsNullable	Boolean	カラムがNull を含められるかどうか。
Description	String	カラムの簡単な説明。
Ordinal	Int32	カラムのシーケンスナンバー。
IsAutoIncrement	String	カラムに固定増分値が割り当てられるかどうか。
IsGeneratedColumn	String	生成されたカラムであるかどうか。
IsHidden	Boolean	カラムが非表示かどうか。
IsArray	Boolean	カラムが配列かどうか。
IsReadOnly	Boolean	カラムが読み取り専用かどうか。
IsKey	Boolean	sys_tablecolumns から返されたフィールドがテーブルの主キーであるかどうか。

利用可能なストアドプロシージャをリストします。

次のクエリは、利用可能なストアドプロシージャを取得します。

          SELECT * FROM sys_procedures
          

Columns

Name	Type	Description
CatalogName	String	ストアドプロシージャを含むデータベース。
SchemaName	String	ストアドプロシージャを含むスキーマ。
ProcedureName	String	ストアドプロシージャの名前。
Description	String	ストアドプロシージャの説明。
ProcedureType	String	PROCEDURE やFUNCTION などのプロシージャのタイプ。

ストアドプロシージャパラメータについて説明します。

次のクエリは、UploadAttachment ストアドプロシージャのすべての入力パラメータについての情報を返します。

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

Columns

Name	Type	Description
CatalogName	String	ストアドプロシージャを含むデータベースの名前。
SchemaName	String	ストアドプロシージャを含むスキーマの名前。
ProcedureName	String	パラメータを含むストアドプロシージャの名前。
ColumnName	String	ストアドプロシージャパラメータの名前。
Direction	Int32	パラメータのタイプに対応する整数値：input (1)。input/output (2)、またはoutput(4)。input/output タイプパラメータは、入力パラメータと出力パラメータの両方になれます。
DataTypeName	String	データ型の名前。
DataType	Int32	データ型を示す整数値。この値は、実行時に環境に基づいて決定されます。
Length	Int32	文字データの場合は、許可される文字数。数値データの場合は、許可される桁数。
NumericPrecision	Int32	数値データの場合は最大精度。文字データおよび日時データの場合は、カラムの長さ（文字数）。
NumericScale	Int32	数値データの小数点以下の桁数。
IsNullable	Boolean	パラメータがNull を含められるかどうか。
IsRequired	Boolean	プロシージャの実行にパラメータが必要かどうか。
IsArray	Boolean	パラメータが配列かどうか。
Description	String	パラメータの説明。
Ordinal	Int32	パラメータのインデックス。

主キーおよび外部キーについて説明します。

次のクエリは、projects テーブルの主キーを取得します。

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

Columns

Name	Type	Description
CatalogName	String	キーを含むデータベースの名前。
SchemaName	String	キーを含むスキーマの名前。
TableName	String	キーを含むテーブルの名前。
ColumnName	String	キーカラムの名前
IsKey	Boolean	カラムがTableName フィールドで参照されるテーブル内の主キーかどうか。
IsForeignKey	Boolean	カラムがTableName フィールドで参照される外部キーかどうか。
PrimaryKeyName	String	主キーの名前。
ForeignKeyName	String	外部キーの名前。
ReferencedCatalogName	String	主キーを含むデータベース。
ReferencedSchemaName	String	主キーを含むスキーマ。
ReferencedTableName	String	主キーを含むテーブル。
ReferencedColumnName	String	主キーのカラム名。

外部キーについて説明します。

次のクエリは、他のテーブルを参照するすべての外部キーを取得します。

         SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
          

カラム

名前	タイプ	説明
CatalogName	String	キーを含むデータベースの名前。
SchemaName	String	キーを含むスキーマの名前。
TableName	String	キーを含むテーブルの名前。
ColumnName	String	キーカラムの名前
PrimaryKeyName	String	主キーの名前。
ForeignKeyName	String	外部キーの名前。
ReferencedCatalogName	String	主キーを含むデータベース。
ReferencedSchemaName	String	主キーを含むスキーマ。
ReferencedTableName	String	主キーを含むテーブル。
ReferencedColumnName	String	主キーのカラム名。
ForeignKeyType	String	外部キーがインポート（他のテーブルを指す）キーかエクスポート（他のテーブルから参照される）キーかを指定します。

主キーについて説明します。

次のクエリは、すべてのテーブルとビューから主キーを取得します。

         SELECT * FROM sys_primarykeys
          

Columns

Name	Type	Description
CatalogName	String	キーを含むデータベースの名前。
SchemaName	String	キーを含むスキーマの名前。
TableName	String	キーを含むテーブルの名前。
ColumnName	String	キーカラムの名前。
KeySeq	String	主キーのシーケンス番号。
KeyName	String	主キーの名前。

利用可能なインデックスについて説明します。インデックスをフィルタリングすることで、より高速なクエリ応答時間でセレクティブクエリを記述できます。

次のクエリは、主キーでないすべてのインデックスを取得します。

          SELECT * FROM sys_indexes WHERE IsPrimary='false'
          

Columns

Name	Type	Description
CatalogName	String	インデックスを含むデータベースの名前。
SchemaName	String	インデックスを含むスキーマの名前。
TableName	String	インデックスを含むテーブルの名前。
IndexName	String	インデックス名。
ColumnName	String	インデックスに関連付けられたカラムの名前。
IsUnique	Boolean	インデックスが固有の場合はTrue。そうでない場合はFalse。
IsPrimary	Boolean	インデックスが主キーの場合はTrue。そうでない場合はFalse。
Type	Int16	インデックスタイプに対応する整数値：statistic (0)、clustered (1)、hashed (2)、またはother (3)。
SortOrder	String	並べ替え順序：A が昇順、D が降順。
OrdinalPosition	Int16	インデックスのカラムのシーケンスナンバー。

利用可能な接続プロパティと、接続文字列に設定されている接続プロパティに関する情報を返します。

このテーブルをクエリする際は、config 接続文字列を使用する必要があります。

jdbc:cdata:asana:config:

この接続文字列を使用すると、有効な接続がなくてもこのテーブルをクエリできます。

次のクエリは、接続文字列に設定されている、あるいはデフォルト値で設定されているすべての接続プロパティを取得します。

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns

Name	Type	Description
Name	String	接続プロパティ名。
ShortDescription	String	簡単な説明。
Type	String	接続プロパティのデータ型。
Default	String	明示的に設定されていない場合のデフォルト値。
Values	String	可能な値のカンマ区切りリスト。別な値が指定されていると、検証エラーがスローされます。
Value	String	設定した値またはあらかじめ設定されたデフォルト。
Required	Boolean	プロパティが接続に必要かどうか。
Category	String	接続プロパティのカテゴリ。
IsSessionProperty	String	プロパティが、現在の接続に関する情報を保存するために使用されるセッションプロパティかどうか。
Sensitivity	String	プロパティの機密度。これは、プロパティがロギングおよび認証フォームで難読化されているかどうかを通知します。
PropertyName	String	キャメルケースの短縮形の接続プロパティ名。
Ordinal	Int32	パラメータのインデックス。
CatOrdinal	Int32	パラメータカテゴリのインデックス。
Hierarchy	String	このプロパティと一緒に設定する必要がある、関連のある依存プロパティを表示します。
Visible	Boolean	プロパティが接続UI に表示されるかどうかを通知します。
ETC	String	プロパティに関するその他のさまざまな情報。

Cloud がデータソースにオフロードできるSELECT クエリ処理について説明します。

SQL 構文の詳細については、SQL 準拠 を参照してください。

データソースのSELECT 機能

以下はSQL 機能のサンプルデータセットです。 SELECT 機能のいくつかの側面がサポートされている場合には、カンマ区切りのリストで返されます。サポートされていない場合、カラムにはNO が入ります。

名前	説明	有効な値
AGGREGATE_FUNCTIONS	サポートされている集計関数。	AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT	COUNT 関数がサポートされているかどうか。	YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR	識別子をエスケープするための開始文字。	[
IDENTIFIER_QUOTE_CLOSE_CHAR	識別子をエスケープするための終了文字。	]
SUPPORTED_OPERATORS	サポートされているSQL 演算子。	=, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY	GROUP BY がサポートされているかどうか。サポートされている場合、どのレベルでサポートされているか。	NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
OJ_CAPABILITIES	サポートされている外部結合の種類。	NO, LEFT, RIGHT, FULL, INNER, NOT_ORDERED, ALL_COMPARISON_OPS
OUTER_JOINS	外部結合がサポートされているかどうか。	YES, NO
SUBQUERIES	サブクエリがサポートされているかどうか。サポートされていれば、どのレベルでサポートされているか。	NO, COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED
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	サポートされている数値関数。	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	サポートされている日付および時刻関数。	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	レプリケーション中にスキップされたテーブルを示します。
REPLICATION_TIMECHECK_COLUMNS	レプリケーション中に更新判断のカラムとして使用するかどうかを、（指定された順に）チェックするカラムのリストを含む文字列の配列。
IDENTIFIER_PATTERN	識別子としてどの文字列が有効かを示す文字列値。
SUPPORT_TRANSACTION	プロバイダーが、コミットやロールバックなどのトランザクションをサポートしているかどうかを示します。	YES, NO
DIALECT	使用するSQL ダイアレクトを示します。
KEY_PROPERTIES	Uniform データベースを特定するプロパティを示します。
SUPPORTS_MULTIPLE_SCHEMAS	プロバイダー用に複数のスキームが存在するかどうかを示します。	YES, NO
SUPPORTS_MULTIPLE_CATALOGS	プロバイダー用に複数のカタログが存在するかどうかを示します。	YES, NO
DATASYNCVERSION	このドライバーにアクセスするために必要な、CData Sync のバージョン。	Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY	このドライバーのCData Sync カテゴリ。	Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL	API で提供されている以上の、追加SQL 機能がサポートされているかどうか。	TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS	バッチ操作がサポートされているかどうか。	YES, NO
SQL_CAP	このドライバーでサポートされているすべてのSQL 機能。	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	使用したいcacheOptions を指定する文字列値。
ENABLE_EF_ADVANCED_QUERY	ドライバーがEntity Framework の高度なクエリをサポートしているかどうかを示します。サポートしていなければ、クエリはクライアントサイドで処理されます。	YES, NO
PSEUDO_COLUMNS	利用可能な疑似カラムを示す文字列の配列。
MERGE_ALWAYS	値がtrue であれば、CData Sync 内でMerge Model が強制的に実行されます。	TRUE, FALSE
REPLICATION_MIN_DATE_QUERY	レプリケート開始日時を返すSELECT クエリ。
REPLICATION_MIN_FUNCTION	サーバーサイドでmin を実行するために使用する式名を、プロバイダーが指定できるようになります。
REPLICATION_START_DATE	レプリケート開始日を、プロバイダーが指定できるようになります。
REPLICATION_MAX_DATE_QUERY	レプリケート終了日時を返すSELECT クエリ。
REPLICATION_MAX_FUNCTION	サーバーサイドでmax を実行するために使用する式名を、プロバイダーが指定できるようになります。
IGNORE_INTERVALS_ON_INITIAL_REPLICATE	初回のレプリケートで、レプリケートをチャンクに分割しないテーブルのリスト。
CHECKCACHE_USE_PARENTID	CheckCache 構文を親キーカラムに対して実行するかどうかを示します。	TRUE, FALSE
CREATE_SCHEMA_PROCEDURES	スキーマファイルの生成に使用できる、ストアドプロシージャを示します。

次のクエリは、WHERE 句で使用できる演算子を取得します。

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

WHERE 句では、個々のテーブルの制限や要件が異なる場合がありますので注意してください。詳しくは、データモデル セクションを参照してください。

Columns

Name	Type	Description
NAME	String	SQL 構文のコンポーネント、またはサーバー上で処理できる機能。
VALUE	String	サポートされるSQL またはSQL 構文の詳細。

試行された変更に関する情報を返します。

次のクエリは、バッチ処理で変更された行のId を取得します。

         SELECT * FROM sys_identity
          

Columns

Name	Type	Description
Id	String	データ変更処理から返された、データベース生成Id。
Batch	String	バッチの識別子。1 は単一処理。
Operation	String	バッチ内の処理の結果：INSERTED、UPDATED、またはDELETED。
Message	String	SUCCESS、またはバッチ内の更新が失敗した場合のエラーメッセージ。

接続文字列プロパティは、接続を確立するために使用できるさまざまなオプションです。このセクションでは、本プロバイダーの接続文字列で設定できるオプションの一覧を示します。詳細については各リンクをクリックしてください。

Connection

---

プロパティ	説明
ProjectId	The globally unique identifier (gid) associated with your Asana Project.
WorkspaceId	The globally unique identifier (gid) associated with your Asana Workspace.
IncludeCustomFields	Custom fields may be added to the Tasks, SubTasks, and PortfolioItems tables.

OAuth

---

プロパティ	説明
OAuthClientId	OAuth 認証サーバーを使用してアプリケーションを登録する場合に割り当てられたクライアントId。
OAuthClientSecret	OAuth 認証サーバーにアプリケーションを登録する場合に割り当てられたクライアントシークレット。

SSL

---

プロパティ	説明
SSLServerCert	TLS/SSL を使用して接続するときに、サーバーが受け入れ可能な証明書。

Logging

---

プロパティ	説明
Verbosity	ログファイルの記述をどの程度の詳細さで記載するかを決定するverbosity レベル。

Schema

---

プロパティ	説明
BrowsableSchemas	このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。

Miscellaneous

---

プロパティ	説明
MaxRows	クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。
PseudoColumns	このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。
Timeout	タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。

このセクションでは、本プロバイダーの接続文字列で設定可能なConnection プロパティの全リストを提供します。

---

プロパティ	説明
ProjectId	The globally unique identifier (gid) associated with your Asana Project.
WorkspaceId	The globally unique identifier (gid) associated with your Asana Workspace.
IncludeCustomFields	Custom fields may be added to the Tasks, SubTasks, and PortfolioItems tables.

The globally unique identifier (gid) associated with your Asana Project.

データ型

string

デフォルト値

""

解説

Requests will return the data mapped under this project.

The globally unique identifier (gid) associated with your Asana Workspace.

データ型

string

デフォルト値

""

解説

Requests will return the projects mapped under this WorkspaceId.

Custom fields may be added to the Tasks, SubTasks, and PortfolioItems tables.

データ型

bool

デフォルト値

false

解説

If set to TRUE, custom fields may be added to the Tasks, SubTasks, and PortfolioItems tables. The WorkspaceId should also be specified. Custom fields are related to a specific Workspace. If no WorkspaceId is specified, the first available WorkspaceId will be used.

このセクションでは、本プロバイダーの接続文字列で設定可能なOAuth プロパティの全リストを提供します。

---

プロパティ	説明
OAuthClientId	OAuth 認証サーバーを使用してアプリケーションを登録する場合に割り当てられたクライアントId。
OAuthClientSecret	OAuth 認証サーバーにアプリケーションを登録する場合に割り当てられたクライアントシークレット。

OAuth 認証サーバーを使用してアプリケーションを登録する場合に割り当てられたクライアントId。

データ型

string

デフォルト値

""

解説

OAuth アプリケーションの登録の一環として、コンシューマキーとも呼ばれるOAuthClientId 値、およびクライアントシークレットOAuthClientSecret が提供されます。

OAuth 認証サーバーにアプリケーションを登録する場合に割り当てられたクライアントシークレット。

データ型

string

デフォルト値

""

解説

OAuth アプリケーションの登録の一環として、コンシューマキーとも呼ばれるOAuthClientId が提供されます。また、コンシューマーシークレットと呼ばれるクライアントシークレットも提供されます。クライアントシークレットをOAuthClientSecret プロパティに設定します。

このセクションでは、本プロバイダーの接続文字列で設定可能なSSL プロパティの全リストを提供します。

---

プロパティ	説明
SSLServerCert	TLS/SSL を使用して接続するときに、サーバーが受け入れ可能な証明書。

TLS/SSL を使用して接続するときに、サーバーが受け入れ可能な証明書。

データ型

string

デフォルト値

""

解説

TLS/SSL 接続を使用する場合は、このプロパティを使用して、サーバーが受け入れるTLS/SSL 証明書を指定できます。コンピュータによって信頼されていない他の証明書はすべて拒否されます。

このプロパティは、次のフォームを取ります：

説明	例
フルPEM 証明書（例では省略されています）	-----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
証明書を保有するローカルファイルへのパス。	C:\cert.cer
公開鍵（例では省略されています）	-----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
MD5 Thumbprint （hex 値はスペースおよびコロン区切り）	ecadbdda5a1529c58a1e9e09828d70e4
SHA1 Thumbprint （hex 値はスペースおよびコロン区切り）	34a929226ae0819f2ec14b4a3d904f801cbb150d

これを指定しない場合は、マシンが信用するすべての証明書が受け入れられます。

すべての証明書の受け入れを示すには、'*'を使用します。セキュリティ上の理由から、これはお勧めできません。

このセクションでは、本プロバイダーの接続文字列で設定可能なLogging プロパティの全リストを提供します。

---

プロパティ	説明
Verbosity	ログファイルの記述をどの程度の詳細さで記載するかを決定するverbosity レベル。

ログファイルの記述をどの程度の詳細さで記載するかを決定するverbosity レベル。

データ型

string

デフォルト値

"1"

解説

Verbosity レベルは、Cloud がLogfile にどの程度の詳細さで記載するかを決定します。1 から5 までのVerbosity レベルがサポートされています。より詳しい説明は、ログ ページに記載されています。

このセクションでは、本プロバイダーの接続文字列で設定可能なSchema プロパティの全リストを提供します。

---

プロパティ	説明
BrowsableSchemas	このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。

このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。

データ型

string

デフォルト値

""

解説

スキーマをデータベースからリストすると、負荷がかかる可能性があります。接続文字列でスキーマのリストを提供すると、 パフォーマンスが向上します。

このセクションでは、本プロバイダーの接続文字列で設定可能なMiscellaneous プロパティの全リストを提供します。

---

プロパティ	説明
MaxRows	クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。
PseudoColumns	このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。
Timeout	タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。

クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。

データ型

int

デフォルト値

-1

解説

クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。

このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。

データ型

string

デフォルト値

""

解説

Entity Framework ではテーブルカラムでない疑似カラムに値を設定できないため、この設定はEntity Framework で特に便利です。この接続設定の値は、"Table1=Column1, Table1=Column2, Table2=Column3" の形式です。"*=*" のように"*" 文字を使用して、すべてのテーブルとすべてのカラムを含めることができます。

タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。

データ型

int

デフォルト値

60

解説

Timeout が0に設定されている場合は、操作がタイムアウトしません。処理が正常に完了するか、エラー状態になるまで実行されます。

Timeout の有効期限が切れても処理が完了していない場合は、Cloud は例外をスローします。