Adding a Connection to WooCommerce

To add a connection to WooCommerce:

1. In the application console, navigate to the Connections page.
2. At the Add Connections panel, select the icon for the connection you want to add.
3. If the WooCommerce icon is not available, click the Add More icon to download and install the WooCommerce connector from the CData site.

For required properties, see the Settings tab.

For connection properties that are not typically required, see the Advanced tab.

Connecting to WooCommerce

In order to connect to WooCommerce, you also must specify the service instance's URL:

• Url: The base URL of the WooCommerce instance to connect to. For example, http://localhost/woocommerce/.

Authenticating to WooCommerce

WooCommerce supports Basic authentication, "one-legged" OAuth1.0 authentication, and standard OAuth2.0 authentication.

Basic Authentication

Certain plugins or servers can interfere with the Authorization header if not configured properly. In those cases, users can employ Basic authentication to validate their credentials. In Basic authentication, the user provides the WooCommerce consumer_key as the user, and the WooCommerce consumer_secret as the password. These credentials are then sent as query parameters with each request.

To authenticate using Basic authentication, specify these properties:

• AuthScheme: Basic.
• ConsumerKey: The WooCommerce consumer key(User).
• ConsumerSecret: The WooCommerce consumer secret(Password).

"One-Legged" Authentication

One-legged (OAuth 1.0) authentication uses credentials generated from the WooCommerce setttings page. These are not the same as the credentials generated using the WooCommerce OAuth 2.0 plugin.

To authenticate using one-legged authentication, specify these properties:

• AuthScheme: OAuthOneLegged.
• ConsumerKey
• ConsumerSecret

OAuth 2.0 Authentication

To enable OAuth 2.0 authentication from all OAuth flows, you must install the WooCommerce OAuth2 Plugin, create a custom OAuth application, and set AuthScheme to OAuth.

The following subsections describe how to authenticate to WooCommerce from three common authentication flows. For information about how to create a custom OAuth application, see Creating a Custom OAuth Application. For a complete list of connection string properties available in WooCommerce, see Connection.

Automatic refresh of the OAuth access token:

To have the Sync App automatically refresh the OAuth access token:

1. Before connecting to data for the first time, set these connection properties:
   • InitiateOAuth: REFRESH.
   • OAuthClientId: The client Id in your application settings.
   • OAuthClientSecret: The client secret in your application settings.
   • OAuthAccessToken: The access token returned by GetOAuthAccessToken.
   • OAuthSettingsLocation: The path where you want the Sync App to save the OAuth values, which persist across connections.
2. On subsequent data connections, set:
   • InitiateOAuth
   • OAuthSettingsLocation

Manual refresh of the OAuth access token:

The only value needed to manually refresh the OAuth access token is the OAuth refresh token.

1. To manually refresh the OAuthAccessToken after the ExpiresIn period (returned by GetOAuthAccessToken) has elapsed, call the RefreshOAuthAccessToken stored procedure.
2. Set these connection properties:
   • OAuthClientId: The Client Id in your application settings.
   • OAuthClientSecret: The Client Secret in your application settings.

3. Call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken.
4. After the new tokens have been retrieved, set the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken. This opens a new connection.

Store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

This section details a selection of advanced features of the WooCommerce Sync App.

User Defined Views

The Sync App 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 Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

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

See Query Processing for more information.

Logging

See Logging for an overview of configuration settings that can be used to refine CData logging. For basic logging, you only need to set two connection properties, but there are numerous features that support more refined logging, where you can select subsets of information to be logged using the LogModules connection property.

Customizing the SSL Configuration

By default, the Sync App 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.

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

Overview

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

Key Features

• The Sync App models WooCommerce entities like orders, customers, and products as relational tables and views, allowing you to write SQL to query WooCommerce data.
• Stored procedures allow you to execute operations to WooCommerce, including running system tools and updating the available locations for a given shipping zone.
• Live connectivity to these objects means any changes to your WooCommerce account are immediately reflected when using the Sync App.

Views

Pre-defined Tables and Views are available for read or write access to data from WooCommerce.

Stored Procedures

The Sync App allows you to run WooCommerce tools and update locations for a given shipping zone via Stored Procedures.

System Tables

System Tables contains information about the objects and resources belonging to your database.

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

WooCommerce Connector for CData Sync Tables

Name	Description
Attributes	Retrieve and modify product attributes.
Categories	Retrieve product categories.
Coupons	Retrieve and modify coupons.
Customers	Retrieve and modify customers.
OrderLineItems	Add, update and view the line items for an order.
OrderNotes	Retrieve and modify the notes for an order.
OrderRefunds	Retrieve and modify order refunds.
Orders	Retrieve and modify orders.
PaymentGateways	Retrieve and modify payment gateways.
ProductAttributeTerms	Retrieve the terms for an attribute.
ProductReviews	Retrieve and modify products reviews.
Products	Retrieve and modify products.
ProductShippingClasses	Retrieve and modify product shipping classes.
ProductTags	Retrieve and modify product tags.
ProductVariations	Retrieve and modify product variations.
SettingOptions	View and manage settings options.
ShippingZoneMethods	Retrieve and modify shipping zone methods.
ShippingZones	Retrieve and modify shipping zones.
TaxClasses	Retrieve and modify tax classes.
TaxRates	Retrieve and modify tax rates.
Webhooks	Retrieve and modify webhooks.

Retrieve and modify product attributes.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: Id. All other columns are processed client side.

SELECT * FROM Attributes WHERE Id = 4

Insert

The following attribute is required when performing an insert: Name.

INSERT INTO Attributes (Name) VALUES ('attribute_name')

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	False	Attribute ID.
Name	String	False	Attribute name.
Slug	String	False	An alphanumeric identifier for the resource unique to its type.
Type	String	False	Type of attribute. By default only select is supported.
OrderBy	String	False	Default sort order. Options: menu_order, name, name_num and id. Defaults to menu_order.
HasArchives	Boolean	False	Enable/Disable attribute archives. Defaults to false.

Retrieve product categories.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, Slug, Parent, Product. All other columns are processed client side.

SELECT * FROM Categories WHERE Id = 4
SELECT * FROM Categories WHERE Slug = 'criteria'
SELECT * FROM Categories WHERE Parent = 2

Insert

The following attribute is required when performing an insert: Name.

INSERT INTO Categories (Name) VALUES ('category_name')

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
Name	String	False	Category name.
Slug	String	False	An alphanumeric identifier for the resource unique to its type.
Parent	Long	False	The ID for the parent of the resource.
Description	String	False	HTML description of the resource.
Display	String	False	Category archive display type. Options: default, products, subcategories and both.
MenuOrder	Integer	False	Menu order, used to custom sort the resource.
Count	Integer	True	Number of published products for the resource.
ImageId	Long	False	Image ID.
ImageDateCreated	Datetime	True	The date the image was created, in the site's timezone.
ImageDateModified	Datetime	True	The date the image was last modified, in the site's timezone.
ImageSrc	String	False	Image URL.
ImageName	String	False	Image name.
ImageAlt	String	False	Image alternative text.

Retrieve and modify coupons.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, Code. All other columns are processed client side.

SELECT * FROM Coupons WHERE Id = 4
SELECT * FROM Coupons WHERE Code = 'criteria'

Update

To perform an update or insert using any of the aggregate columns which contain simple primitive arrays, we can simply pass a JSON array string as the value:

UPDATE Coupons SET ProductIdsAggregate = '[14, 16, 29]' WHERE ID = 42

To introduce new metadata fields which are not present in the schema, the 'metadata' pseudocolumn can be used. The update below will create two new metadata fields with keys 'key1' and 'key2' and set their respective values. If any of the keys specified already exists, its value will be updated.

UPDATE Coupons SET metadata = 'key1:val1, key2:val2' WHERE ID = 58

Insert

The following attribute is required when performing an insert: Code.

INSERT INTO Coupons (Code) VALUES ('coupon_code')

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the object.
Code	String	False	Coupon code.
Amount	String	False	The amount of discount. Should always be numeric, even if setting a percentage.
DateCreated	Datetime	True	The date the coupon was created, in the site's timezone.
DateModified	Datetime	True	The date the coupon was last modified, in the site's timezone.
DiscountType	String	False	Determines the type of discount that will be applied. Options: percent, fixed_cart and fixed_product. Defaults to fixed_cart.
Description	String	False	Coupon description.
DateExpires	Datetime	False	The date the coupon expires, in the site's timezone.
UsageCount	Integer	True	Number of times the coupon has been used already.
IndividualUse	Boolean	False	If true, the coupon can only be used individually. Other applied coupons will be removed from the cart. Defaults to false.
ProductIdsAggregate	String	False	List of product IDs the coupon can be used on.
ExcludedProductIdsAggregate	String	False	List of product IDs the coupon cannot be used on.
UsageLimit	Integer	False	How many times the coupon can be used in total.
UsageLimitPerUser	Integer	False	How many times the coupon can be used per customer.
LimitUsageToXItems	Integer	False	Max number of items in the cart the coupon can be applied to.
FreeShipping	Boolean	False	If true and if the free shipping method requires a coupon, this coupon will enable free shipping. Defaults to false.
ProductCategoriesAggregate	String	False	List of category IDs the coupon applies to.
ExcludedProductCategoriesAggregate	String	False	List of category IDs the coupon does not apply to.
ExcludeSaleItems	Boolean	False	If true, this coupon will not be applied to items that have sale prices. Defaults to false.
MinimumAmount	String	False	Minimum order amount that needs to be in the cart before coupon applies.
MaximumAmount	String	False	Maximum order amount allowed when using the coupon.
EmailRestrictionsAggregate	String	False	List of email addresses that can use this coupon.
UsedByAggregate	String	True	List of user IDs (or guest email addresses) that have used the coupon.

Retrieve and modify customers.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, Email, Role. All other columns are processed client side.

SELECT * FROM Customers WHERE Id = 4
SELECT * FROM Customers WHERE Email = '[email protected]'
SELECT * FROM Customers WHERE Username = 'user' AND IsPayingCustomer = true

Update

To introduce new metadata fields which are not present in the schema, the 'metadata' pseudocolumn can be used. The update below will create two new metadata fields with keys 'key1' and 'key2' and set their respective values. If any of the keys specified already exists, its value will be updated.

UPDATE Customers SET metadata = 'key1:val1, key2:val2' WHERE ID = 58

Insert

The following attribute is required when performing an insert: Email.

INSERT INTO Customers (Email) VALUES ('[email protected]')

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
DateCreated	Datetime	True	The date the customer was created, in the site's timezone.
DateModified	Datetime	True	The date the customer was last modified, in the site's timezone.
Email	String	False	The email address for the customer.
FirstName	String	False	Customer first name.
LastName	String	False	Customer last name.
Role	String	True	Customer role.
Username	String	False	Customer login name.
Password	String	False	Customer password. write-only
IsPayingCustomer	Bool	True	Is the customer a paying customer? read-only
AvatarUrl	String	True	Avatar URL.
MetaData	String	False	Meta data. See Customer - Meta data properties
BillingFirstName	String	False	First name.
BillingLastName	String	False	Last name.
BillingCompany	String	False	Company name.
BillingAddress1	String	False	Address line 1
BillingAddress2	String	False	Address line 2
BillingCity	String	False	City name.
BillingState	String	False	ISO code or name of the state, province or district.
BillingPostcode	String	False	Postal code.
BillingCountry	String	False	ISO code of the country.
BillingEmail	String	False	Email address.
BillingPhone	String	False	Phone number.
ShippingFirstName	String	False	First name.
ShippingLastName	String	False	Last name.
ShippingCompany	String	False	Company name.
ShippingAddress1	String	False	Address line 1
ShippingAddress2	String	False	Address line 2
ShippingCity	String	False	City name.
ShippingState	String	False	ISO code or name of the state, province or district.
ShippingPostcode	String	False	Postal code.
ShippingCountry	String	False	ISO code of the country.

Add, update and view the line items for an order.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: TotalTax, OrderId. All other columns are processed client side. If the 'OrderId' filter is not specified, the Sync App will fetch a list of all the orders and perform a request for each order in the list.

SELECT * FROM OrderLineItems WHERE OrderId = 13
SELECT * FROM OrderLineItems WHERE TotalTax = 14.21

Insert

The following attribute is required when performing an insert: OrderId.

INSERT INTO OrderLineItems (Productid, Quantity, OrderId) VALUES (28, 9, 35)

Update

The following attribute is required when performing an update: OrderId.

UPDATE OrderLineItems SET Quantity = 12 WHERE Id = 4 AND OrderId = 35

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	False	Item ID.
Name	String	False	Product name.
ProductId	Long	False	Product ID.
VariationId	Long	False	Variation ID, if applicable.
Quantity	Integer	False	Quantity ordered.
TaxClass	String	False	Tax class of product.
Subtotal	String	False	Line subtotal (before discounts).
SubtotalTax	String	True	Line subtotal tax (before discounts).
Total	String	False	Line total (after discounts).
TotalTax	String	True	Line total tax (after discounts).
Taxes	String	True	Line taxes. See Order - Taxes properties read-only
MetaData	String	False	Meta data. See Order - Meta data properties
Sku	String	True	Product SKU.
Price	String	True	Product price.
OrderId	String	True	Id of the order.

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
ReferenceNumber	String	This column will be used in Bulk operations to get specific values from the Temp tables.

Retrieve and modify the notes for an order.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: OrderId. All other columns are processed client side. If the 'OrderId' filter is not specified, the Sync App will fetch a list of all the orders and perform a request for each order in the list.

SELECT * FROM OrderNotes WHERE OrderId = 21

Insert

The following attributes are required when performing an insert: Note, OrderId.

INSERT INTO OrderNotes (Note, OrderId) VALUES ("note_text", 144)

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	False	Unique identifier for the resource.
Author	String	True	Order note author.
DateCreated	Datetime	True	The date the order note was created, in the site's timezone.
Note	String	False	Order note content.
CustomerNote	Boolean	False	If true, the note will be shown to customers and they will be notified. If false, the note will be for admin reference only. Defaults to false.
AddedByUser	Boolean	False	If true, this note will be attributed to the current user. If false, the note will be attributed to the system. Defaults to false.
OrderId	String	True	Id of the order.

Retrieve and modify order refunds.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: OrderId. All other columns are processed client side. If the 'OrderId' filter is not specified, the Sync App will fetch a list of all the orders and perform a request for each order in the list.

SELECT * FROM OrderRefunds WHERE OrderId = 21

Insert

The following attribute is required when performing an insert: OrderId.

INSERT INTO OrderRefunds (OrderId) VALUES (33)

Columns

Name	Type	ReadOnly	Description
Id	Long	False	Unique identifier for the resource.
DateCreated	Datetime	True	The date the order refund was created, in the site's timezone.
Amount	String	False	Refund amount.
Reason	String	False	Reason for refund.
RefundedBy	Long	False	User ID of user who created the refund.
RefundedPayment	Boolean	True	If the payment was refunded via the API. See api_refund.
LineItems	String	False	Line items data. See Order refund - Line items properties
ApiRefund	Boolean	False	When true, the payment gateway API is used to generate the refund. Defaults to true. write-only
OrderId	String	True	Id of the order.

Retrieve and modify orders.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, ParentId, Status, DateModified, ProductId. All other columns are processed client side.

SELECT * FROM Orders WHERE Id = 4
SELECT * FROM Orders WHERE ParentId = 4
SELECT * FROM Orders WHERE Status = 'pending' AND CustomerId = 501

Update

To perform an update or insert using any of the aggregate columns which contain object arrays, we can use a temporary table, or pass the value as a JSON string:

INSERT INTO OrderCouponLines#TEMP (Code) VALUES ('CouponCode')
UPDATE Orders SET CouponLinesAggregate = 'OrderCouponLines#TEMP' WHERE ID = 14

To introduce new metadata fields which are not present in the schema, the 'metadata' pseudocolumn can be used. The update below will create two new metadata fields with keys 'key1' and 'key2' and set their respective values. If any of the keys specified already exists, its value will be updated.

UPDATE Orders SET metadata = 'key1:val1, key2:val2' WHERE ID = 58

Insert

This table does not have any required attributes when performing an insert.

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
ParentId	Long	False	Parent order ID.
Number	String	True	Order number.
OrderKey	String	True	Order key.
CreatedVia	String	True	Shows where the order was created.
Version	String	True	Version of WooCommerce which last updated the order.
Status	String	False	Order status. Options: pending, processing, on-hold, completed, cancelled, refunded, failed and trash. Defaults to pending.
Currency	String	False	Currency the order was created with, in ISO format. Options: AED, AFN, ALL, AMD, ANG, AOA, ARS, AUD, AWG, AZN, BAM, BBD, BDT, BGN, BHD, BIF, BMD, BND, BOB, BRL, BSD, BTC, BTN, BWP, BYR, BZD, CAD, CDF, CHF, CLP, CNY, COP, CRC, CUC, CUP, CVE, CZK, DJF, DKK, DOP, DZD, EGP, ERN, ETB, EUR, FJD, FKP, GBP, GEL, GGP, GHS, GIP, GMD, GNF, GTQ, GYD, HKD, HNL, HRK, HTG, HUF, IDR, ILS, IMP, INR, IQD, IRR, IRT, ISK, JEP, JMD, JOD, JPY, KES, KGS, KHR, KMF, KPW, KRW, KWD, KYD, KZT, LAK, LBP, LKR, LRD, LSL, LYD, MAD, MDL, MGA, MKD, MMK, MNT, MOP, MRO, MUR, MVR, MWK, MXN, MYR, MZN, NAD, NGN, NIO, NOK, NPR, NZD, OMR, PAB, PEN, PGK, PHP, PKR, PLN, PRB, PYG, QAR, RON, RSD, RUB, RWF, SAR, SBD, SCR, SDG, SEK, SGD, SHP, SLL, SOS, SRD, SSP, STD, SYP, SZL, THB, TJS, TMT, TND, TOP, TRY, TTD, TWD, TZS, UAH, UGX, USD, UYU, UZS, VEF, VND, VUV, WST, XAF, XCD, XOF, XPF, YER, ZAR and ZMW. Defaults to USD.
DateCreated	Datetime	True	The date the order was created, in the site's timezone.
DateModified	Datetime	True	The date the order was last modified, in the site's timezone.
DiscountTotal	String	True	Total discount amount for the order.
DiscountTax	String	True	Total discount tax amount for the order.
ShippingTotal	String	True	Total shipping amount for the order.
ShippingTax	String	True	Total shipping tax amount for the order.
CartTax	String	True	Sum of line item taxes only.
Total	String	True	Grand total.
TotalTax	String	True	Sum of all taxes.
PricesIncludeTax	Boolean	True	True the prices included tax during checkout.
CustomerId	Long	False	User ID who owns the order. 0 for guests. Defaults to 0.
CustomerIpAddress	String	True	Customer's IP address.
CustomerUserAgent	String	True	User agent of the customer.
CustomerNote	String	False	Note left by customer during checkout.
PaymentMethod	String	False	Payment method ID.
PaymentMethodTitle	String	False	Payment method title.
TransactionId	String	False	Unique transaction ID.
DatePaid	Datetime	True	The date the order was paid, in the site's timezone.
DateCompleted	Datetime	True	The date the order was completed, in the site's timezone.
CartHash	String	True	MD5 hash of cart items to ensure orders are not modified.
LineItemsAggregate	String	False	Line items data. See Order - Line items properties
TaxLinesAggregate	String	True	Tax lines data. See Order - Tax lines properties read-only
ShippingLinesAggregate	String	False	Shipping lines data. See Order - Shipping lines properties
FeeLinesAggregate	String	False	Fee lines data. See Order - Fee lines properties
CouponLinesAggregate	String	False	Coupons line data. See Order - Coupon lines properties
RefundsAggregate	String	True	List of refunds. See Order - Refunds properties read-only
SetPaid	Boolean	False	Define if the order is paid. It will set the status to processing and reduce stock items. Defaults to false. write-only
BillingFirstName	String	False	First name.
BillingLastName	String	False	Last name.
BillingCompany	String	False	Company name.
BillingAddress1	String	False	Address line 1
BillingAddress2	String	False	Address line 2
BillingCity	String	False	City name.
BillingState	String	False	ISO code or name of the state, province or district.
BillingPostcode	String	False	Postal code.
BillingCountry	String	False	Country code in ISO 3166-1 alpha-2 format.
BillingEmail	String	False	Email address.
BillingPhone	String	False	Phone number.
ShippingFirstName	String	False	First name.
ShippingLastName	String	False	Last name.
ShippingCompany	String	False	Company name.
ShippingAddress1	String	False	Address line 1
ShippingAddress2	String	False	Address line 2
ShippingCity	String	False	City name.
ShippingState	String	False	ISO code or name of the state, province or district.
ShippingPostcode	String	False	Postal code.
ShippingCountry	String	False	Country code in ISO 3166-1 alpha-2 format.
ProductId	Long	False	Limit result set to resources assigned to a specific product.

Retrieve and modify payment gateways.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: Id. All other columns are processed client side.

SELECT * FROM PaymentGateways WHERE Id = 'id'

Update

The following attribute is required when performing an update: Id.

UPDATE PaymentGateways SET Title='Credit card / debit card', Description='Enter your card details' WHERE Id = 'woocommerce_payments'

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	True	Payment gateway ID.
Title	String	False	Payment gateway title on checkout.
Description	String	False	Payment gateway description on checkout.
Order	Integer	False	Payment gateway sort order.
Enabled	Boolean	False	Payment gateway enabled status.
MethodTitle	String	True	Payment gateway method title.
MethodDescription	String	True	Payment gateway method description.
MethodSupportsAggregate	String	True	Supported features for this payment gateway.
SettingsTitleId	String	True	A unique identifier for the setting.
SettingsTitleDefault	String	True	Default value for the setting.
SettingsTitleDescription	String	True	A human readable description for the setting used in interfaces.
SettingsTitleLabel	String	True	A human readable label for the setting used in interfaces.
SettingsTitlePlaceholder	String	True	Placeholder text to be displayed in text inputs.
SettingsTitleTip	String	True	Additional help text shown to the user about the setting.
SettingsTitleType	String	True	Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsTitleValue	String	True	Setting value.
SettingsInstructionsDefault	String	True	Default value for the setting.
SettingsInstructionsDescription	String	True	A human readable description for the setting used in interfaces.
SettingsInstructionsLabel	String	True	A human readable label for the setting used in interfaces.
SettingsInstructionsPlaceholder	String	True	Placeholder text to be displayed in text inputs.
SettingsInstructionsTip	String	True	Additional help text shown to the user about the setting.
SettingsInstructionsType	String	True	Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsInstructionsValue	String	True	Setting value.

Retrieve the terms for an attribute.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, Slug, AttributeId, ProductId. All other columns are processed client side. If the 'AttributeId' filter is not specified, the Sync App will fetch a list of all the attributes and perform a request for each attribute in the list.

SELECT * FROM ProductAttributeTerms WHERE AttributeId = 4
SELECT * FROM ProductAttributeTerms WHERE ProductId = 41 AND Slug = 'criteria'

ProductAttributeTerms.rsd

Insert

The following attributes are required when performing an insert: Name, AttributeId.

INSERT INTO ProductAttributeTerms (Name, AttributeId) VALUES ('term_name', 2)

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
Name	String	False	Term name.
Slug	String	False	An alphanumeric identifier for the resource unique to its type.
Description	String	False	HTML description of the resource.
MenuOrder	Integer	False	Menu order, used to custom sort the resource.
Count	Integer	True	Number of published products for the resource.
AttributeId	Long	True	Id of the attribute.

Retrieve and modify products reviews.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, Status, Reviewer, ReviewerEmail, ProductId. All other columns are processed client side.

SELECT * FROM ProductReviews WHERE Id = 4
SELECT * FROM ProductReviews WHERE ReviewerEmail = '[email protected]'

Insert

The following attributes are required when performing an insert: ProductId, Reviewer, Review.

INSERT INTO ProductReviews (ProductId, Reviewer, ReviewerEmail, Review) VALUES (167, 'reviewer', '[email protected]', 'review_content')

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
DateCreated	Datetime	True	The date the review was created, in the site's timezone.
ProductId	Long	False	Unique identifier for the product that the review belongs to.
Status	String	False	Status of the review. Options: approved, hold, spam, unspam, trash and untrash. Defauls to approved.
Reviewer	String	False	Reviewer name.
ReviewerEmail	String	False	Reviewer email.
Review	String	False	The content of the review.
Rating	Integer	False	Review rating (0 to 5).
Verified	Boolean	False	Shows if the reviewer bought the product or not.

Retrieve and modify products.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, Slug, Type, Status, Sku, Price, OnSale, TaxClass, StockStatus, ShippingClass, ParentId. The price filter can be used with the <, > conditions and may be used twice to specify a range. All other columns are processed client side.

SELECT * FROM Products WHERE Id = 4
SELECT * FROM Products WHERE Slug = 'criteria' AND OnSale = true
SELECT * FROM Products WHERE Price < 14.99 AND Price > 11.99

Update

To perform an update or insert using any of the aggregate columns which contain simple primitive arrays, we can simply pass a JSON array string as the value:

UPDATE Products SET Name = 'ProductName', RelatedIdsAggregate = '[14, 16, 29]' WHERE ID = 42

To perform an update or insert using any of the aggregate columns which contain object arrays, we can use a temporary table, or pass the value as a JSON string:

INSERT INTO ProductCategories#TEMP (Name) VALUES ('Category')
INSERT INTO ProductCategories#TEMP (Name) VALUES ('Category2')
UPDATE Products SET CategoriesAggregate = 'ProductCategories#TEMP' WHERE ID = 167

To introduce new metadata fields which are not present in the schema, the 'metadata' pseudocolumn can be used. The update below will create two new metadata fields with keys 'key1' and 'key2' and set their respective values. If any of the keys specified already exists, its value will be updated.

UPDATE Products SET metadata = 'key1:val1, key2:val2' WHERE ID = 58

Insert

This table does not have any required attributes when performing an insert.

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
Name	String	False	Product name.
Slug	String	False	Product slug.
Permalink	String	True	Product URL.
DateCreated	Datetime	True	The date the product was created, in the site's timezone.
DateModified	Datetime	True	The date the product was last modified, in the site's timezone.
Type	String	False	Product type. Options: simple, grouped, external and variable. Defaults to simple.
Status	String	False	Product status (post status). Options: draft, pending, private and publish. Defaults to publish.
Featured	Boolean	False	Featured product. Defaults to false.
CatalogVisibility	String	False	Catalog visibility. Options: visible, catalog, search and hidden. Defaults to visible.
Description	String	False	Product description.
ShortDescription	String	False	Product short description.
Sku	String	False	Unique identifier.
Price	String	True	Current product price.
RegularPrice	String	False	Product regular price.
SalePrice	String	False	Product sale price.
DateOnSaleFrom	Datetime	False	Start date of sale price, in the site's timezone.
DateOnSaleTo	Datetime	False	End date of sale price, in the site's timezone.
PriceHtml	String	True	Price formatted in HTML.
OnSale	Boolean	True	Shows if the product is on sale.
Purchasable	Boolean	True	Shows if the product can be bought.
TotalSales	Integer	True	Amount of sales.
Virtual	Boolean	False	If the product is virtual. Defaults to false.
Downloadable	Boolean	False	If the product is downloadable. Defaults to false.
Downloads	String	False	List of downloadable files. See Product - Downloads properties
DownloadLimit	Integer	False	Number of times downloadable files can be downloaded after purchase. Defaults to -1.
DownloadExpiry	Integer	False	Number of days until access to downloadable files expires. Defaults to -1.
ExternalUrl	String	False	Product external URL. Only for external products.
ButtonText	String	False	Product external button text. Only for external products.
TaxStatus	String	False	Tax status. Options: taxable, shipping and none. Defaults to taxable.
TaxClass	String	False	Tax class.
ManageStock	Boolean	False	Stock management at product level. Defaults to false.
StockQuantity	Integer	False	Stock quantity.
StockStatus	String	False	Controls the stock status of the product. Options: instock, outofstock, onbackorder. Defaults to instock.
Backorders	String	False	If managing stock, this controls if backorders are allowed. Options: no, notify and yes. Defaults to no.
BackordersAllowed	Boolean	True	Shows if backorders are allowed.
Backordered	Boolean	True	Shows if the product is on backordered.
SoldIndividually	Boolean	False	Allow one item to be bought in a single order. Defaults to false.
Weight	String	False	Product weight.
ShippingRequired	Boolean	True	Shows if the product need to be shipped.
ShippingTaxable	Boolean	True	Shows whether or not the product shipping is taxable.
ShippingClass	String	False	Shipping class slug.
ShippingClassId	Long	True	Shipping class ID.
ReviewsAllowed	Boolean	False	Allow reviews. Defaults to true.
AverageRating	String	True	Reviews average rating.
RatingCount	Integer	True	Amount of reviews that the product have.
RelatedIdsAggregate	String	True	List of related products IDs.
UpsellIdsAggregate	String	False	List of up-sell products IDs.
CrossSellIdsAggregate	String	False	List of cross-sell products IDs.
ParentId	Long	False	Product parent ID.
PurchaseNoteAggregate	String	False	Optional note to send the customer after purchase.
CategoriesAggregate	String	False	List of categories. See Product - Categories properties
TagsAggregate	String	False	List of tags. See Product - Tags properties
ImagesAggregate	String	False	List of images. See Product - Images properties
AttributesAggregate	String	False	List of attributes. See Product - Attributes properties
DefaultAttributesAggregate	String	False	Defaults variation attributes. See Product - Default attributes properties
VariationsAggregate	String	True	List of variations IDs.
GroupedProductsAggregate	String	False	List of grouped products ID.
MenuOrder	Integer	False	Menu order, used to custom sort products.
MetaData	String	False	Meta data. See Product - Meta data properties
DimensionsLength	String	False	Product length.
DimensionsWidth	String	False	Product width.
DimensionsHeight	String	False	Product height.

Retrieve and modify product shipping classes.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, Slug, ProductId. All other columns are processed client side.

SELECT * FROM ProductShippingClasses WHERE Id = 4
SELECT * FROM ProductShippingClasses WHERE ProductId = 16

Insert

The following attribute is required when performing an insert: Name.

INSERT INTO ProductShippingClasses (Name) VALUES ('class_name')

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
Name	String	False	Shipping class name.
Slug	String	False	An alphanumeric identifier for the resource unique to its type.
Description	String	False	HTML description of the resource.
Count	Integer	True	Number of published products for the resource.
ProductId	Long	False	Limit result set to resources assigned to a specific product.

Retrieve and modify product tags.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, Slug, Product, HideEmpty. All other columns are processed client side.

SELECT * FROM ProductTags WHERE Id = 4
SELECT * FROM ProductTags WHERE Slug = 'criteria'
SELECT * FROM ProductTags WHERE ProductId = 16

Insert

The following attribute is required when performing an insert: Name.

INSERT INTO ProductTags (Name) VALUES ('tag_name')

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
Name	String	False	Tag name.
Slug	String	False	An alphanumeric identifier for the resource unique to its type.
Description	String	False	HTML description of the resource.
Count	Integer	True	Number of published products for the resource.
ProductId	Long	False	Limit result set to resources assigned to a specific product.

Retrieve and modify product variations.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: Id, Sku, Price, OnSale, Status, TaxClass, StockStatus, ProductId, ParentId. The price filter can be used with the <, > conditions and may be used twice to specify a range. All other columns are processed client side.

SELECT * FROM ProductVariations WHERE Id = 4
SELECT * FROM ProductVariations WHERE Price < 14.99 AND Price > 11.99 AND OnSale = true

Update

To introduce new metadata fields which are not present in the schema, the 'metadata' pseudocolumn can be used. The update below will create two new metadata fields with keys 'key1' and 'key2' and set their respective values. If any of the keys specified already exists, its value will be updated.

UPDATE ProductVariations SET metadata = 'key1:val1, key2:val2' WHERE ID = 58 AND ProductID = 80

To update Stock information, you must set ManageStock to true. For example:

UPDATE ProductVariations SET StockQuantity = 6, ManageStock = true WHERE ID = 58 AND ProductID = 80

Insert

The following attribute is required when performing an insert: ProductId.

INSERT INTO ProductVariations (ProductId) VALUES (167)

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
DateCreated	Datetime	True	The date the variation was created, in the site's timezone.
DateModified	Datetime	True	The date the variation was last modified, in the site's timezone.
Description	String	False	Variation description.
Permalink	String	True	Variation URL.
Sku	String	False	Unique identifier.
Price	String	True	Current variation price.
RegularPrice	String	False	Variation regular price.
SalePrice	String	False	Variation sale price.
DateOnSaleFrom	Datetime	False	Start date of sale price, in the site's timezone.
DateOnSaleTo	Datetime	False	End date of sale price, in the site's timezone.
OnSale	Boolean	True	Shows if the variation is on sale.
Status	String	False	Variation status. Options: draft, pending, private and publish. Defaults to publish.
Purchasable	Boolean	True	Shows if the variation can be bought.
Virtual	Boolean	False	If the variation is virtual. Defaults to false.
Downloadable	Boolean	False	If the variation is downloadable. Defaults to false.
DownloadsAggregate	String	False	List of downloadable files. See Product variation - Downloads properties
DownloadLimit	Integer	False	Number of times downloadable files can be downloaded after purchase. Defaults to -1.
DownloadExpiry	Integer	False	Number of days until access to downloadable files expires. Defaults to -1.
TaxStatus	String	False	Tax status. Options: taxable, shipping and none. Defaults to taxable.
TaxClass	String	False	Tax class.
ManageStock	String	False	Stock management at variation level. Defaults to false. If the stock is managed by product and not variaation then the value is parent.
StockQuantity	Integer	False	Stock quantity.
StockStatus	String	False	Controls the stock status of the product. Options: instock, outofstock, onbackorder. Defaults to instock.
Backorders	String	False	If managing stock, this controls if backorders are allowed. Options: no, notify and yes. Defaults to no.
BackordersAllowed	Boolean	True	Shows if backorders are allowed.
Backordered	Boolean	True	Shows if the variation is on backordered.
Weight	String	False	Variation weight.
ShippingClass	String	False	Shipping class slug.
ShippingClassId	String	True	Shipping class ID.
AttributesAggregate	String	False	List of attributes. See Product variation - Attributes properties
MenuOrder	Integer	False	Menu order, used to custom sort products.
MetaData	String	False	Meta data. See Product variation - Meta data properties
DimensionsLength	String	False	Variation length.
DimensionsWidth	String	False	Variation width.
DimensionsHeight	String	False	Variation height.
ImageId	Long	False	Image ID.
ImageDateCreated	Datetime	True	The date the image was created, in the site's timezone.
ImageDateModified	Datetime	True	The date the image was last modified, in the site's timezone.
ImageSrc	String	False	Image URL.
ImageName	String	False	Image name.
ImageAlt	String	False	Image alternative text.
ProductId	Long	True	Id of the product.
ParentId	Long	False	The id of the parent, filtered server side.

View and manage settings options.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, SettingId. All other columns are processed client side. If the 'SettingId' filter is not specified, the Sync App will fetch a list of all the settings and perform a request for each setting in the list.

SELECT * FROM SettingOptions WHERE Id = 'woocommerce_excluded_report_order_statuses'

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	True	A unique identifier for the setting.
Label	String	True	A human readable label for the setting used in interfaces.
Description	String	True	A human readable description for the setting used in interfaces.
Value	String	False	Setting value.
Default	String	True	Default value for the setting.
Tip	String	True	Additional help text shown to the user about the setting.
Placeholder	String	True	Placeholder text to be displayed in text inputs.
Type	String	True	Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
GroupId	String	True	An identifier for the group this setting belongs to.
SettingId	String	True	Id of the setting group.

Retrieve and modify shipping zone methods.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: InstanceId, ZoneId. All other columns are processed client side. If the 'ZoneId' filter is not specified, the Sync App will fetch a list of all the zones and perform a request for each zone in the list.

SELECT * FROM ShippingZoneMethods WHERE InstanceId = 4 AND ZoneId = 11

Insert

The following attributes are required when performing an insert: MethodId, ZoneId.

INSERT INTO ShippingZoneMethods (MethodId, ZoneId) VALUES ('flat_rate', 1)

Columns

Name	Type	ReadOnly	Description
InstanceId [KEY]	Long	False	Shipping method instance ID.
Enabled	Boolean	False	Shipping method enabled status.
MethodDescription	String	False	hipping method description.
MethodId	String	False	Shipping method ID.
MethodTitle	String	False	Shipping method title.
Order	Integer	False	Shipping method sort order.
Title	String	False	Shipping method customer facing title.
SettingsTitleId	String	True	A unique identifier for the setting.
SettingsTitleLabel	String	True	A human readable label for the setting used in interfaces.
SettingsTitleDescription	String	True	A human readable description for the setting used in interfaces.
SettingsTitleType	String	True	Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsTitleValue	String	False	Setting value.
SettingsTitleDefault	String	True	Default value for the setting.
SettingsTitleTip	String	True	Additional help text shown to the user about the setting.
SettingsTitlePlaceholder	String	True	Placeholder text to be displayed in text inputs.
SettingsTaxStatusId	String	True	A unique identifier for the setting.
SettingsTaxStatusLabel	String	True	A human readable label for the setting used in interfaces.
SettingsTaxStatusDescription	String	True	A human readable description for the setting used in interfaces.
SettingsTaxStatusType	String	True	Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsTaxStatusValue	String	False	Setting value.
SettingsTaxStatusDefault	String	True	Default value for the setting.
SettingsTaxStatusTip	String	True	Additional help text shown to the user about the setting.
SettingsTaxStatusPlaceholder	String	True	Placeholder text to be displayed in text inputs.
SettingsCostId	String	True	A unique identifier for the setting.
SettingsCostLabel	String	True	A human readable label for the setting used in interfaces.
SettingsCostDescription	String	True	A human readable description for the setting used in interfaces.
SettingsCostType	String	True	Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsCostValue	String	False	Setting value.
SettingsCostDefault	String	True	Default value for the setting.
SettingsCostTip	String	True	Additional help text shown to the user about the setting.
SettingsCostPlaceholder	String	True	Placeholder text to be displayed in text inputs.
SettingsTypeId	String	True	A unique identifier for the setting.
SettingsTypeLabel	String	True	A human readable label for the setting used in interfaces.
SettingsTypeDescription	String	True	A human readable description for the setting used in interfaces.
SettingsTypeType	String	True	Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsTypeValue	String	False	Setting value.
SettingsTypeDefault	String	True	Default value for the setting.
SettingsTypeTip	String	True	Additional help text shown to the user about the setting.
SettingsTypePlaceholder	String	True	Placeholder text to be displayed in text inputs.
ZoneId	Long	True	Id of the shipping zone.

Retrieve and modify shipping zones.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: Id. All other columns are processed client side.

SELECT * FROM ShippingZones WHERE Id = 13

Insert

The following attribute is required when performing an insert: Name.

INSERT INTO ShippingZones (Name) VALUES ('zone_name')

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
Name	String	False	Shipping zone name.
Order	Integer	False	Shipping zone order.

Retrieve and modify tax classes.

Table Specific Information

Select

The Sync App will process all filters client side.

Insert

The following attribute is required when performing an insert: Name.

INSERT INTO TaxClasses (Name) VALUES ('tax_class_name')

Columns

Name	Type	ReadOnly	Description
Slug [KEY]	String	True	Unique identifier for the resource.
Name	String	False	Tax class name. required

Retrieve and modify tax rates.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, Class. All other columns are processed client side.

SELECT * FROM TaxRates WHERE Id = 4

Insert

This table does not have any required attributes when performing an insert.

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
Country	String	False	Country ISO 3166 code. See ISO 3166 Codes (Countries) for more details
State	String	False	State code.
Postcode	String	False	Postcode/ZIP.
City	String	False	City name.
Rate	String	False	Tax rate.
Name	String	False	Tax rate name.
Priority	Integer	False	Tax priority. Only 1 matching rate per priority will be used. To define multiple tax rates for a single area you need to specify a different priority per rate. Defaults to 1.
Compound	Boolean	False	Whether or not this is a compound rate. Compound tax rates are applied on top of other tax rates. Defaults to false.
Shipping	Boolean	False	Whether or not this tax rate also gets applied to shipping. Defaults to true.
Order	Integer	False	Indicates the order that will appear in queries.
Class	String	False	Tax class. Defaults to standard.
Postcodes	String	False	Postcodes/ZIP.
Cities	String	False	City names.

Retrieve and modify webhooks.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, Status. All other columns are processed client side.

SELECT * FROM Webhooks WHERE Id = 4

Insert

The following attributes are required when performing an insert: Topic, DeliveryUrl.

INSERT INTO Webhooks (Topic, DeliveryUrl) VALUES ('order.updated', 'http://url.com')

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
Name	String	False	A friendly name for the webhook.
Status	String	False	Webhook status. Options: active, paused and disabled. Defaults to active.
Topic	String	False	Webhook topic.
Resource	String	True	Webhook resource.
Event	String	True	Webhook event.
HooksAggregate	String	True	WooCommerce action names associated with the webhook.
DeliveryUrl	String	True	The URL where the webhook payload is delivered.
Secret	String	False	Secret key used to generate a hash of the delivered webhook and provided in the request headers. This will Defaults to a MD5 hash from the current user's ID
DateCreated	Datetime	True	The date the webhook was created, in the site's timezone.
DateModified	Datetime	True	The date the webhook was last modified, in the site's timezone.

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.

WooCommerce Connector for CData Sync Views

Name	Description
Continents	Retrieve continents.
Countries	Retrieve countries.
Currencies	Retrieve available currencies.
OrderCouponLines	Retrieve the coupon lines for an order.
OrderFeeLines	Retrieve the fee lines for an order.
OrderRefundLineItems	Retrieve the line items for a refund.
OrderShippingLines	Retrieve the shipping lines for an order.
OrderTaxLines	Retrieve the tax lines for an order.
ProductAttributes	Retrieve product attributes.
ProductCategories	Retrieve product categories.
ProductImages	Retrieve images for a product.
SalesReport	Executes a Sales report.
SettingGroups	Retrieve settings groups.
ShippingLocations	Retrieve shipping zone locations.
ShippingMethods	Retrieve shipping methods.
States	Retrieve shipping states.
SystemStatus	Retrieve system status items.
SystemStatusPages	Retrieve and system status items.
SystemStatusTools	Retrieve system status tools.
TopSellersReports	Execute a top sellers report.
TotalsReport	Execute a totals report.

Retrieve continents.

Table Specific Information

Select

The Sync App will process all filters client side.

Columns

Name	Type	Description
Code	String	2 character continent code.
Name	String	Full name of continent.
CountriesAggregate	String	List of countries on this continent. See Continents - Countries properties read-only

Retrieve countries.

Table Specific Information

Select

The Sync App will process all filters client side.

Columns

Name	Type	Description
Code	String	ISO3166 alpha-2 country code.
Name	String	Full name of country.
StatesAggregate	String	List of states in this country. See Countries - States properties read-only

Retrieve available currencies.

Table Specific Information

Select

The Sync App will process all filters client side.

Columns

Name	Type	Description
Code	String	ISO4217 currency code.
Name	String	Full name of currency.
Symbol	String	Currency symbol.

Retrieve the coupon lines for an order.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: OrderId. All other columns are processed client side.

SELECT * FROM OrderCouponLines WHERE OrderId = 144

Columns

Name	Type	Description
Id [KEY]	Long	Item ID.
Code	String	Coupon code.
Discount	String	Discount total.
DiscountTax	String	Discount total tax.
MetaData	String	Meta data. See Order - Meta data properties
OrderId	String	Id of the order.

Retrieve the fee lines for an order.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: OrderId. All other columns are processed client side. If the 'OrderId' filter is not specified, the Sync App will fetch a list of all the orders and perform a request for each order in the list.

SELECT * FROM OrderFeeLines WHERE OrderId = 21

Columns

Name	Type	Description
Id [KEY]	Long	Item ID.
Name	String	Fee name.
TaxClass	String	Tax class of fee.
TaxStatus	String	Tax status of fee. Options: taxable and none.
Total	String	Line total (after discounts).
TotalTax	String	Line total tax (after discounts).
Taxes	String	Line taxes. See Order - Taxes properties read-only
MetaData	String	Meta data. See Order - Meta data properties
OrderId	String	Id of the order.

Retrieve the line items for a refund.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: OrderId, RefundId. All other columns are processed client side. If the 'OrderId' filter is not specified, the Sync App will fetch a list of all the orders and perform a request for each order in the list. The above behaviour will happen apply also for refunds, if 'RefundId' is not specified.

SELECT * FROM OrderRefundLineItems WHERE OrderId = 16

Columns

Name	Type	Description
Id	Long	Item ID.
Name	String	Product name.
ProductId	Long	Product ID.
VariationId	Long	Variation ID, if applicable.
Quantity	Integer	Quantity ordered.
TaxClass	Integer	Tax class of product.
Subtotal	String	Line subtotal (before discounts).
SubtotalTax	String	Line subtotal tax (before discounts).
Total	String	Line total (after discounts).
TotalTax	String	Line total tax (after discounts).
TaxesAggregate	String	Line taxes. See Order refund line item - Taxes properties read-only
MetaDataAggregate	String	Meta data. See Order refund - Meta data properties
Sku	String	Product SKU.
Price	String	Product price.
OrderId	String	Id of the order.
RefundId	String	Id of the refund.

Retrieve the shipping lines for an order.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: OrderId. All other columns are processed client side. If the 'OrderId' filter is not specified, the Sync App will fetch a list of all the orders and perform a request for each order in the list.

SELECT * FROM OrderShippingLines WHERE OrderId = 21

Columns

Name	Type	Description
Id [KEY]	Long	Item ID.
MethodTitle	String	Shipping method name.
MethodId	String	Shipping method ID.
Total	String	Line total (after discounts).
TotalTax	String	Line total tax (after discounts).
Taxes	String	Line taxes. See Order - Taxes properties read-only
MetaData	String	Meta data. See Order - Meta data properties
OrderId	Long	Id of the order.

Retrieve the tax lines for an order.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: OrderId. All other columns are processed client side. If the 'OrderId' filter is not specified, the Sync App will fetch a list of all the orders and perform a request for each order in the list.

SELECT * FROM OrderTaxLines WHERE OrderId = 12

Columns

Name	Type	Description
Id [KEY]	Long	Item ID.
RateCode	String	Tax rate code.
RateId	String	Tax rate ID.
Label	String	Tax rate label.
Compound	Boolean	Show if is a compound tax rate.
TaxTotal	String	Tax total (not including shipping taxes).
ShippingTaxTotal	String	Shipping tax total.
MetaData	String	Meta data. See Order - Meta data properties
OrderId	Long	Id of the order.

Retrieve product attributes.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: ProductId. All other columns are processed client side. If the 'ProductId' filter is not specified, the Sync App will fetch a list of all the products and perform a request for each product in the list.

SELECT * FROM ProductAttributes WHERE ProductId = 4

Columns

Name	Type	Description
Id [KEY]	Long	Attribute ID.
Name	String	Attribute name.
Slug	String	An alphanumeric identifier for the resource unique to its type.
Type	String	Type of attribute. By default only select is supported.
OrderBy	String	Default sort order. Options: menu_order, name, name_num and id. Defaults to menu_order.
HasArchives	Boolean	Enable/Disable attribute archives. Defaults to false.
ProductId	Long	Id of the product.

Retrieve product categories.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, Slug, ProductId. All other columns are processed client side. If the 'ProductId' filter is not specified, the Sync App will fetch a list of all the products and perform a request for each product in the list.

SELECT * FROM ProductCategories WHERE Id = 4
SELECT * FROM ProductCategories WHERE Slug = 'criteria'

Columns

Name	Type	Description
Id [KEY]	Long	Unique identifier for the resource.
Name	String	Category name.
Slug	String	An alphanumeric identifier for the resource unique to its type.
ProductId	Long	Id of the product.

Retrieve images for a product.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, DateCreated, DateModified, Src, Name, Alt, ProductId. All other columns are processed client side. If the 'ProductId' filter is not specified, the Sync App will fetch a list of all the products and perform a request for each product in the list.

SELECT * FROM ProductImages WHERE Id = 4
SELECT * FROM ProductImages WHERE ProductId = 6

Columns

Name	Type	Description
Id [KEY]	Long	Image ID.
DateCreated	Datetime	The date the image was created, in the site's timezone.
DateModified	Datetime	The date the image was last modified, in the site's timezone.
Src	String	Image URL.
Name	String	Image name.
Alt	String	Image alternative text.
ProductId	Long	Id of the product.

Executes a Sales report.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Period, StartDate, EndDate. All other columns are processed client side.

SELECT * FROM SalesReport WHERE Period = 'week' AND StartDate = '01/01/2019'

Columns

Name	Type	Description
TotalSales	String	Gross sales in the period.
NetSales	String	Net sales in the period.
AverageSales	String	Average net daily sales.
TotalOrders	Integer	Total of orders placed.
TotalItems	Integer	Total of items purchased.
TotalTax	String	Total charged for taxes.
TotalShipping	String	Total charged for shipping.
TotalRefunds	Integer	Total of refunded orders.
TotalDiscount	Integer	Total of coupons used.
TotalsGroupedBy	String	Group type.
TotalsAggregate	String	Totals.
Period	String	Report period. Defaults to week. Options: week, month, last_month and year.
StartDate	Date	Return sales for a specific start date, the date need to be in the YYYY-MM-DD format.
EndDate	Date	Return sales for a specific end date, the date need to be in the YYYY-MM-DD format.

Retrieve settings groups.

Table Specific Information

Select

The Sync App will process all filters client side.

Columns

Name	Type	Description
Id [KEY]	String	A unique identifier that can be used to link settings together.
Label	String	A human readable label for the setting used in interfaces.
Description	String	A human readable description for the setting used in interfaces.
ParentId	String	ID of parent grouping.

Retrieve shipping zone locations.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: ZoneId. All other columns are processed client side. If the 'ZoneId' filter is not specified, the Sync App will fetch a list of all the zones and perform a request for each zone in the list.

SELECT * FROM ShippingLocations WHERE ZoneId = 13

Columns

Name	Type	Description
Code	String	Shipping zone location code.
Type	String	Shipping zone location type. Options: postcode, state, country and continent. Defaults to country.
ZoneId	Long	Id of the shipping zone.

Retrieve shipping methods.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: Id. All other columns are processed client side.

SELECT * FROM ShippingMethods WHERE Id = 'flat_rate'

Columns

Name	Type	Description
Id [KEY]	String	Method ID.
Title	String	Shipping method title.
Description	String	Shipping method description.

Retrieve shipping states.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: Country. All other columns are processed client side. If the 'Country' filter is not specified, the Sync App will fetch a list of all the countries and perform a request for each country in the list.

SELECT * FROM States WHERE Country = 'Germany'

Columns

Name	Type	Description
Country	String	State code.
Code	String	State code.
Name	String	Full name of the state.

Retrieve system status items.

Table Specific Information

Select

This table represents a singleton entity.

Columns

Name	Type	Description
ActivePluginsAggregate	String	Active plugins.
PagesAggregate	String	WooCommerce pages.
EnvironmentHomeUrl	String	Home URL.
EnvironmentSiteUrl	String	Site URL.
EnvironmentWcVersion	String	WooCommerce version.
EnvironmentLogDirectory	String	Log directory.
EnvironmentLogDirectoryWritable	Boolean	Is log directory writable? read-only
EnvironmentWpVersion	String	WordPress version.
EnvironmentWpMultisite	Boolean	Is WordPress multisite? read-only
EnvironmentWpMemoryLimit	Integer	WordPress memory limit.
EnvironmentWpDebugMode	Boolean	Is WordPress debug mode active? read-only
EnvironmentWpCron	Boolean	Are WordPress cron jobs enabled? read-only
EnvironmentLanguage	String	WordPress language.
EnvironmentServerInfo	String	Server info.
EnvironmentPhpVersion	String	PHP version.
EnvironmentPhpPostMaxSize	Integer	PHP post max size.
EnvironmentPhpMaxExecutionTime	Integer	PHP max execution time.
EnvironmentPhpMaxInputVars	Integer	PHP max input vars.
EnvironmentCurlVersion	String	cURL version.
EnvironmentSuhosinInstalled	Boolean	Is SUHOSIN installed? read-only
EnvironmentMaxUploadSize	Integer	Max upload size.
EnvironmentMysqlVersion	String	MySQL version.
EnvironmentDefaultTimezone	String	Default timezone.
EnvironmentFsockopenOrCurlEnabled	Boolean	Is fsockopen/cURL enabled? read-only
EnvironmentSoapclientEnabled	Boolean	Is SoapClient class enabled? read-only
EnvironmentDomdocumentEnabled	Boolean	Is DomDocument class enabled? read-only
EnvironmentGzipEnabled	Boolean	Is GZip enabled? read-only
EnvironmentMbstringEnabled	Boolean	Is mbstring enabled? read-only
EnvironmentRemotePostSuccessful	Boolean	Remote POST successful? read-only
EnvironmentRemotePostResponse	String	Remote POST response.
EnvironmentRemoteGetSuccessful	Boolean	Remote GET successful? read-only
EnvironmentRemoteGetResponse	String	Remote GET response.
DatabaseWcDatabaseVersion	String	WC database version.
DatabasePrefix	String	Database prefix.
DatabaseMaxmindGeoipDatabase	String	MaxMind GeoIP database.
DatabaseTablesAggregate	String	Database tables.
ThemeName	String	Theme name.
ThemeVersion	String	Theme version.
ThemeVersionLatest	String	Latest version of theme.
ThemeAuthorUrl	String	Theme author URL.
ThemeIsChildTheme	Boolean	Is this theme a child theme? read-only
ThemeHasWoocommerceSupport	Boolean	Does the theme declare WooCommerce support? read-only
ThemeHasWoocommerceFile	Boolean	Does the theme have a woocommerce.php file? read-only
ThemeHasOutdatedTemplates	Boolean	Does this theme have outdated templates? read-only
ThemeOverridesAggregate	String	Template overrides.
ThemeParentName	String	Parent theme name.
ThemeParentVersion	String	Parent theme version.
ThemeParentAuthorUrl	String	Parent theme author URL.
SettingsApiEnabled	Boolean	REST API enabled? read-only
SettingsForceSsl	Boolean	SSL forced? read-only
SettingsCurrency	String	Currency.
SettingsCurrencySymbol	String	Currency symbol.
SettingsCurrencyPosition	String	Currency position.
SettingsThousandSeparator	String	Thousand separator.
SettingsDecimalSeparator	String	Decimal separator.
SettingsNumberOfDecimals	Integer	Number of decimals.
SettingsGeolocationEnabled	Boolean	Geolocation enabled? read-only
SettingsTaxonomiesAggregate	String	Taxonomy terms for product/order statuses.
SecuritySecureConnection	Boolean	Is the connection to your store secure? read-only
SecurityHideErrors	Boolean	Hide errors from visitors? read-only

Retrieve and system status items.

Table Specific Information

Select

The Sync App will process all filters client side.

Columns

Name	Type	Description
PageName	String	Name of the page.
PageId	String	Id of the page.
PageSet	String	Whether the page is set.
PageExists	String	Whether the page exists.
PageVisible	String	Whether the page is visible.
Shortcode	String	Page short code.
ShortcodeRequired	String	Whether shortcode is required.
ShortcodePresent	String	Whether shortcode is present.

Retrieve system status tools.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: Id. All other columns are processed client side.

SELECT * FROM SystemStatusTools WHERE Id = 'install_pages'

Columns

Name	Type	Description
Id [KEY]	String	A unique identifier for the tool.
Name	String	Tool name.
Action	String	What running the tool will do.
Description	String	Tool description.
Success	Boolean	Did the tool run successfully?
Message	String	Tool return message.
Confirm	Boolean	Confirm execution of the tool. Defaults to false. write-only

Execute a top sellers report.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Title, ProductId, Quantity, StartDate, EnDDate. All other columns are processed client side.

SELECT * FROM TopSellersReports WHERE Title = 'criteria'
SELECT * FROM TopSellersReports WHERE ProductId = 4

Columns

Name	Type	Description
Title	String	Product title.
ProductId	Long	Product ID.
Quantity	Integer	Total number of purchases.
StartDate	Date	Return the report for a specific start date.
EndDate	Date	Return the report for a specific end date.

Execute a totals report.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: ReportType, StartDate, EndDate. All other columns are processed client side.

SELECT * FROM TotalsReport WHERE ReportType = 'reviews' AND StartDate = '01/01/2019'

Columns

Name	Type	Description
Slug	String	An alphanumeric identifier for the resource.
Name	String	Review type name.
Total	String	Amount of reviews.
ReportType	String	Type of the report. Must be one of: reviews, products, orders, customers, coupons.
StartDate	Date	Return the report for a specific start date.
EndDate	Date	Return the report for a specific end date.

The connection string 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 in the connection string for this provider. Click the links for further details.

For more information on establishing a connection, see Establishing a Connection.

Authentication

---

Property	Description
AuthScheme	Whether to use Basic Authentication, the one-legged OAuth 1.0 Authentication or the OAuth 2.0 Authentication when connecting to WooCommerce.
URL	The URL of the WooCommerce instance.

OAuth

---

Property	Description
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.
ConsumerKey	The consumer key assigned by WooCommerce when creating the authentication credentials. If this property is set, and InitiateOAuth is OFF, the provider will authenticate using one-legged OAuth1.0.
ConsumerSecret	The consumer secret assigned by WooCommerce when creating the authentication credentials. If this and the ConsumerKey properties are set, and InitiateOAuth is OFF, the provider will authenticate using one-legged OAuth1.0.

SSL

---

Property	Description
SSLServerCert	The certificate to be accepted from the server when connecting using TLS/SSL.

Firewall

---

Property	Description
FirewallType	The protocol used by a proxy-based firewall.
FirewallServer	The name or IP address of a proxy-based firewall.
FirewallPort	The TCP port for a proxy-based firewall.
FirewallUser	The user name to use to authenticate with a proxy-based firewall.
FirewallPassword	A password used to authenticate to a proxy-based firewall.

Proxy

---

Property	Description
ProxyAutoDetect	This indicates whether to use the system proxy settings or not.
ProxyServer	The hostname or IP address of a proxy to route HTTP traffic through.
ProxyPort	The TCP port the ProxyServer proxy is running on.
ProxyAuthScheme	The authentication type to use to authenticate to the ProxyServer proxy.
ProxyUser	A user name to be used to authenticate to the ProxyServer proxy.
ProxyPassword	A password to be used to authenticate to the ProxyServer proxy.
ProxySSLType	The SSL type to use when connecting to the ProxyServer proxy.
ProxyExceptions	A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

Logging

---

Property	Description
LogModules	Core modules to be included in the log file.

Schema

---

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.

Miscellaneous

---

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.
Pagesize	The maximum number of results to return per page from WooCommerce.
PseudoColumns	This property indicates whether or not to include pseudo columns as columns to the table.
RowScanDepth	The maximum number of rows to scan to look for the columns available in a 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.

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

---

Property	Description
AuthScheme	Whether to use Basic Authentication, the one-legged OAuth 1.0 Authentication or the OAuth 2.0 Authentication when connecting to WooCommerce.
URL	The URL of the WooCommerce instance.

Whether to use Basic Authentication, the one-legged OAuth 1.0 Authentication or the OAuth 2.0 Authentication when connecting to WooCommerce.

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. Note that Basic authentication is only supported over HTTPS. If using Basic authentication, the credentials will be sent as query parameters with every request.
• OAuthOneLegged: Set this to use the one-legged OAuth 1.0 Authentication.
• OAuth: Set this to perform OAuth 2.0 authentication.

The URL of the WooCommerce instance.

Remarks

The URL of the WooCommerce instance.

This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.

---

Property	Description
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.
ConsumerKey	The consumer key assigned by WooCommerce when creating the authentication credentials. If this property is set, and InitiateOAuth is OFF, the provider will authenticate using one-legged OAuth1.0.
ConsumerSecret	The consumer secret assigned by WooCommerce when creating the authentication credentials. If this and the ConsumerKey properties are set, and InitiateOAuth is OFF, the provider will authenticate using one-legged OAuth1.0.

The client Id assigned when you register your application with an OAuth authorization server.

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.

The client secret assigned when you register your application with an OAuth authorization server.

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.

The consumer key assigned by WooCommerce when creating the authentication credentials. If this property is set, and InitiateOAuth is OFF, the provider will authenticate using one-legged OAuth1.0.

Remarks

As part of creating a new set of credentials, you will receive the ConsumerKey and ConsumerSecret values.

The consumer secret assigned by WooCommerce when creating the authentication credentials. If this and the ConsumerKey properties are set, and InitiateOAuth is OFF, the provider will authenticate using one-legged OAuth1.0.

Remarks

As part of creating a new set of credentials, you will receive the ConsumerKey and ConsumerSecret values.

This section provides a complete list of the SSL properties you can configure in the connection string for this provider.

---

Property	Description
SSLServerCert	The certificate to be accepted from the server when connecting using TLS/SSL.

The certificate to be accepted from the server when connecting using TLS/SSL.

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.

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

This section provides a complete list of the Firewall properties you can configure in the connection string for this provider.

---

Property	Description
FirewallType	The protocol used by a proxy-based firewall.
FirewallServer	The name or IP address of a proxy-based firewall.
FirewallPort	The TCP port for a proxy-based firewall.
FirewallUser	The user name to use to authenticate with a proxy-based firewall.
FirewallPassword	A password used to authenticate to a proxy-based firewall.

The protocol used by a proxy-based firewall.

Remarks

This property specifies the protocol that the Sync App will use to tunnel traffic through the FirewallServer proxy. Note that by default, the Sync App connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.

Type	Default Port	Description
TUNNEL	80	When this is set, the Sync App opens a connection to WooCommerce and traffic flows back and forth through the proxy.
SOCKS4	1080	When this is set, the Sync App sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted.
SOCKS5	1080	When this is set, the Sync App sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes.

To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.

The name or IP address of a proxy-based firewall.

Remarks

This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.

Note that the Sync App uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.

The TCP port for a proxy-based firewall.

Remarks

This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.

The user name to use to authenticate with a proxy-based firewall.

Remarks

The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.

A password used to authenticate to a proxy-based firewall.

Remarks

This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.

This section provides a complete list of the Proxy properties you can configure in the connection string for this provider.

---

Property	Description
ProxyAutoDetect	This indicates whether to use the system proxy settings or not.
ProxyServer	The hostname or IP address of a proxy to route HTTP traffic through.
ProxyPort	The TCP port the ProxyServer proxy is running on.
ProxyAuthScheme	The authentication type to use to authenticate to the ProxyServer proxy.
ProxyUser	A user name to be used to authenticate to the ProxyServer proxy.
ProxyPassword	A password to be used to authenticate to the ProxyServer proxy.
ProxySSLType	The SSL type to use when connecting to the ProxyServer proxy.
ProxyExceptions	A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

This indicates whether to use the system proxy settings or not.

Remarks

This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.

The hostname or IP address of a proxy to route HTTP traffic through.

Remarks

The hostname or IP address of a proxy to route HTTP traffic through. The Sync App can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.

If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.

By default, the Sync App uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.

The TCP port the ProxyServer proxy is running on.

Remarks

The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.

The authentication type to use to authenticate to the ProxyServer proxy.

Remarks

This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.

Note that the Sync App will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

The authentication type can be one of the following:

• BASIC: The Sync App performs HTTP BASIC authentication.
• DIGEST: The Sync App performs HTTP DIGEST authentication.
• NEGOTIATE: The Sync App retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
• PROPRIETARY: The Sync App does not generate an NTLM or Kerberos token. You must supply this token in the Authorization header of the HTTP request.

If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.

A user name to be used to authenticate to the ProxyServer proxy.

Remarks

The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.

You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:

user@domain
domain\user

A password to be used to authenticate to the ProxyServer proxy.

Remarks

This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.

If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.

If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.

For SOCKS 5 authentication or tunneling, see FirewallType.

By default, the Sync App uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.

The SSL type to use when connecting to the ProxyServer proxy.

Remarks

This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:

AUTO	Default setting. If the URL is an HTTPS URL, the Sync App will use the TUNNEL option. If the URL is an HTTP URL, the component will use the NEVER option.
ALWAYS	The connection is always SSL enabled.
NEVER	The connection is not SSL enabled.
TUNNEL	The connection is through a tunneling proxy. The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy.

A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

Remarks

The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.

Note that the Sync App uses the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you need to set ProxyAutoDetect = false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

This section provides a complete list of the Logging properties you can configure in the connection string for this provider.

---

Property	Description
LogModules	Core modules to be included in the log file.

Core modules to be included in the log file.

Remarks

Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.

See the Logging page for an overview.

This section provides a complete list of the Schema properties you can configure in the connection string for this provider.

---

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.

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Remarks

The path to a directory which contains the schema files for the Sync App (.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.

If left unspecified, the default location is "%APPDATA%\\CData\\WooCommerce Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:

Platform	%APPDATA%
Windows	The value of the APPDATA environment variable
Linux	~/.config

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the Sync App.

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.

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the Sync App.

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.

This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.

---

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.
Pagesize	The maximum number of results to return per page from WooCommerce.
PseudoColumns	This property indicates whether or not to include pseudo columns as columns to the table.
RowScanDepth	The maximum number of rows to scan to look for the columns available in a 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.

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Remarks

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

These hidden properties are used only in specific use cases.

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

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.

The maximum number of results to return per page from WooCommerce.

Remarks

The Pagesize property affects the maximum number of results to return per page from WooCommerce. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.

This property indicates whether or not to include pseudo columns as columns to the table.

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, "*=*".

The maximum number of rows to scan to look for the columns available in a table.

Remarks

The columns in a table must be determined by scanning table rows. This value determines the maximum number of rows that will be scanned.

Setting a high value may decrease performance. Setting a low value may prevent the data type from being determined properly, especially when there is null data.

The value in seconds until the timeout error is thrown, canceling the operation.

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 Sync App throws an exception.

A filepath pointing to the JSON configuration file containing your custom views.

Remarks

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The Sync App 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 Sync App.

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 Orders 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.