WooCommerce SQL connector

Ideas (en)
,
1

WooCommerce is one of the biggest e-commerce platform providers in the world.

API Doc:
https://woocommerce.github.io/woocommerce-rest-api-docs/

WooCommerce (WC) 2.6+ is fully integrated with the WordPress REST API. This allows WC data to be created, read, updated, and deleted using requests in JSON format and using WordPress REST API Authentication methods and standard HTTP verbs which are understood by most HTTP clients.

Create API connection:

2

The WooCommerce API has a simple authentication model using pre-generated secrets: Consumer Key (user) and Consumer Secret (password) on WooCommerce API calls.

There is currently no planned driver, but the data can easily be accessed using Invantive SQL as described for the Twinfield proxy of BI-ON-FOCUS on Replicate Twinfield through a Proxy :

select json.*
from   httpdownload@datadictionary
       ( url => 'https://example.com/wp-json/wc/v3/orders?per_page=1000'
       , contentType => 'application/json'
       , headers => 'Authorization=Basic%20' 
                    || urlencode(base64_encode(ascii_to_blob('CONSUMERKEY' || ':' || 'CONSUMERSECRET')))
       ) htp
join  jsontable
      ( '[*]'
        passing htp.content_clob
        columns id    int path 'id'
      )

The code is untested since there is no access to an environment, but serve as a starting point.

The data can be replicated with Data Replicator using for instance:

create or replace table data_from_woo_commerce@InMemoryStorage
as
select json.*
...

select /*+ ods(true, interval '0 seconds') */ 
       count(*) 
from   data_from_woo_commerce@InMemoryStorage

This simple variant does not include paging. That would require some Invantive PSQL to feed back the data into the next call to httpdownload.

It is not clear in what way errors on WooCommerce are transported by the API to the client.

3

Thanks for the tip. We will see if we can make this work!