Supabase REST API client for NX

This is a Supabase client (API client service) library for OMRON's NX controllers. This library provides functionality for Supabase REST API calls, including Database, Edge functions, and GraphQL. While it's not production-level quality for third parties, it's suitable for internal use. There's some additional information in the Japanese article, "NXからSupabaseを使う" (Using Supabase from NX), available at https://zenn.dev/kitam/articles/f33c107c63ae4c.

Users can call the Supabase REST API with code similar to this, which you might infer from the terms "fetch," "resolved," and "rejected":

100:
    // Generate the query.
    SupabaseQuery_init(iQuery);
    SPQ_FROM('production_monitor', iQuery);
    SPQ_BULK_INSERT(iRows, 0, iRowSize, 'text/csv', iQuery);
    
    // Generate the Fetch.
    SupabaseFetch_new(
        Context:=iFetchContext,
        EndpointName:=SUPABASE_ENDPOINT_NAME,
        Query:=iQuery);
    
    Inc(iState);
101:
    CASE Supabase_fetch(iFetchContext) OF
        ATS_RESOLVED:
            SupabaseFetch_getStatusCode(
                Context:=iFetchContext,
                StatusCode=>iStatusCode);
                        
            iState := iReturnState;
        ATS_REJECTED:
            SupabaseFetch_getStatusCode(
                Context:=iFetchContext,
                StatusCode=>iStatusCode);
            SupabaseFetch_getResponseBodyAsStr(
                Context:=iFetchContext,
                Body=>iRespBody);
            SupabaseFetch_getError(
                Context:=iFetchContext,
                Error=>iError,
                ErrorID=>iErrorID,
                ErrorIDEx=>iErrorIDEx);

            iState := iReturnState;
    END_CASE;

The code above only initiates an API call request. You must separately execute a POU that handles the API call processing in advance. Run a program that includes a POU for API call processing, as shown below. If you place secret information in the POU, you should password-protect it separately.

CASE iState OF
    // STATE_INIT
    0:
        // Initialize settings.
        InitSupabaseClientServiceSettings(
            // Exclusive control key.
            LockKey:=17);
        
        // Register Supabase endpoint.
        RegisterSupabaseEndpoint(
            // Name to identify the endpoint.
            Name:='MachineInfo',
            // Supabase domain.
            Domain:='YOUR_PROJECT_ID.supabase.co',
            // anon key.
            ApiKey:='YOUR_ANON_KEY');
        
        // Register unrestricted TLS sessions.
        RegisterUnrestrictedTlsSession(
            TlsSessionName:='TLSSession0');
        RegisterUnrestrictedTlsSession(
            TlsSessionName:='TLSSession1');

        Inc(iState);
    1:
        IF iCtrlReload THEN
            ReloadSupabaseClientService();
            iCtrlReload := FALSE;
        ELSE
            EnableSupabaseClientService();
            iService.Enable := TRUE;
        END_IF;

        iState := STATE_ACTIVE;
    
    // STATE_ACTIVE
    10:
        // For manual online operation.
        IF iCtrlEnable THEN
            EnableSupabaseClientService();
            iCtrlEnable := FALSE;
        ELSIF iCtrlDisable THEN
            DisableSupabaseClientService();
            iCtrlDisable := FALSE;
        ELSIF iCtrlReload THEN
            iState := STATE_INIT;
        END_IF;
END_CASE;

iService();

If you edit the example project and run it on the controller, it will create records in your Supabase table as shown below. This table stores the production status of the device.

The API client service has the following limitations:

It cannot be used in an HTTP proxy environment.

The API client service can use the following Supabase REST APIs:

Database
Edge functions
GraphQL

Operating Environment

To use this project, you need the following environment:


Controller	NX1 or NX5
Sysmac Studio	Latest version recommended.
Network	Internet connection must be possible.

Environment Used for Building

This project was built in the following environment:


Controller	NX102-9000 Ver 1.64
Sysmac Studio	Ver.1.62

Example Project Usage Procedure

To run the example project, you'll need to perform tasks for Supabase, the example project itself, and the controller. First, create a Supabase account, then set up an Organization and a Project.

The example project is a backend-only solution for Database operations, with no frontend. It operates on the following tables. Row Level Security (RLS) is enabled to allow access restrictions using a simple service key.

production_monitor: Stores production information.
production_task: Stores production tasks.

Ultimately, you will need the following information to use the example project:

Supabase domain
Supabase anon key
Model of the controller being used
Controller settings for internet connection
Controller secure socket session numbers

Perform the following steps:

Set up Supabase.
Confirm your anon key.
Adapt the example project to your environment.
Modify the API client service settings in the example project.
Register TLS sessions in the controller's secure socket settings.
Transfer the example project to the controller.

1. Supabase Setup

Execute the repository's setup SQL (setup_demo.sql) in Supabase's SQL Editor. After copying the setup SQL to the SQL Editor, change the initial_service_key in the DECLARE statement to an appropriate value. By setting the pair of http_header_key and initial_service_key values as an HTTP request header, you'll be able to perform CRUD operations on the table. This is an access restriction enforced by a service key.

DECLARE
    http_header_key          TEXT := 'x-service-key';
    initial_service_key      TEXT := 'mykey';
    service_keys_table       TEXT := 'service_keys';
    production_monitor_table TEXT := 'production_monitor';
    production_task_table    TEXT := 'production_task';

With the above, setting x-service-key: mykey in the HTTP header will allow operations. The setup SQL performs clean-up of related elements, so you can re-execute it to revert to the initial state if any issues arise. Please refer to the setup SQL for details.

2. Confirming the Anon Key

Check and note down your anon key in Supabase's Settings/API Keys.

3. Adapting the Example Project to Your Environment

Adapt the example project to your operating environment. The following changes are required:

Controller Model:
Change it to the model of the controller you are using.
Controller Network Settings:
Configure it for internet connectivity in your environment. Unless there's a special reason, use a public DNS like "1.1.1.1".

4. Modifying the API Client Service Settings in the Example Project

Edit POU/Program/SupabaseClientServiceRunner. Adjust the arguments of each RegisterSupabaseEndpoint to match your Supabase environment. Change the Supabase domain and API key (anon key). Replace YOUR_SUPABASE_DOMAIN with the subdomain prompted during login, and YOUR_ANON_KEY with the anon key you noted in step 2.

// Register Supabase endpoint
RegisterSupabaseEndpoint(
    // Name to identify the endpoint.
    Name:='MachineInfo',
    // Supabase domain.
    Domain:='YOUR_SUPABASE_DOMAIN',
    // Supabase anon key.
    ApiKey:='YOUR_ANON_KEY');

Unless there's a reason to change them, keep the TLS sessions as follows:

RegisterUnrestrictedTlsSession(
    TlsSessionName:='TLSSession0');
RegisterUnrestrictedTlsSession(
    TlsSessionName:='TLSSession1');

Next, edit POU/Function/SetServiceCredentialToSupabaseQuery. Set the service key to the value you configured in step 1. If you haven't changed it, leave it as follows:

SupabaseQuery_setHeader(
    Context:=Query,
    Key:='x-service-key',
    Value:='mykey');

This POU isn't included in the API client service, but it's used in each program as an additional access restriction.

5. Registering TLS Sessions in the Controller's Secure Socket Settings

Connect to the controller, switch to program mode, and register sessions with the same numbers specified in step 4 in the secure socket settings. If you haven't changed them, create sessions with IDs 0 and 1. Perform the operation as shown below.

6. Transferring the Example Project to the Controller

Transfer the example project to the controller and switch it to run mode. Check for any network errors. If network errors occur, resolve their causes.

If errors occur, the following possibilities exist:

Mismatch between TLS session ID and TLS session name
Confirm that the TLS session number specified in step 4 matches the TLS session ID specified in step 5.
Domain mismatch
Confirm that the registered domain specified in step 4 matches your Supabase domain.
API key mismatch
Confirm that the API key specified in step 4 matches your Supabase API key.
Unable to connect to the internet or resolve names
Check the route to the Supabase domain using tracert.
Supabase is experiencing an outage
Check Supabase Status.

About Sysmac Projects

Here's an overview of each Sysmac project and library:

`SupabaseClientServceLib.smc2`

This is the Sysmac project for API client service development. You can review the implementation of the API client service here.

`SupabaseClientServiceLib.slr`

This is the API client service library. It does not include dependent libraries. If you use this library, you must also reference the dependent libraries located in the lib\ directory.

Usage Procedure

Here's how to use the API client service in an existing project. Since it involves library operations, always create a copy of your existing project first.

Reference the API client service library and its dependent libraries in your project.
The lib/ directory in the repository contains the dependent libraries.
Register service variables as global variables.
Define gSupabaseClientServiceSingleton : SupabaseClientServiceSingletonContext.
Create a service runner (a program that executes the SupabaseClientSingletonService FB).
Refer to the example project for guidance.
Add the service runner to a task.
Since the service runner performs API call processing, register it to a task with an appropriate task time. The primary task is unsuitable.
Build and confirm there are no errors.
Errors will appear if there are missing libraries or incorrect global variable definitions.
Check memory usage.
Confirm that changes are reflected in memory usage.

Once your environment is set up, create a program that uses the API client service and a Supabase project, then write the necessary information in the service runner. Performing these operations on a large project can be time-consuming. If possible, consider developing the required functions in a small project, completing the operational tests, and then integrating them into your main project.

`SupabaseClientServiceLibExample.smc2`

This is an example project for the API client service. You can use it to confirm the operation of the API client service. All necessary libraries are bundled within it.

Fetch POU

The Fetch POU is a set of helper POUs designed to simplify calling the Supabase REST API from your API client service. Each operation is performed through a SupabaseRestApiFetchContext structure, which aggregates elements related to a "Fetch"—a single API call unit.

The process for making an API call using Fetch involves two main steps:

Generate a Fetch.
Execute and monitor the Fetch.

Here's an outline of this procedure:

NEW_FETCH:
    GenerateSupabaseQuery(
        Query:=iQuery,
        ...);
    Supabase_newFetch(
        Context:=iFetchContext,
        EndpointName:=SUPABASE_ENDPOINT_NAME,
        Query:=iQuery,
        ...);
    
    iState := DO_FETCH;
DO_FETCH:
    CASE Supabase_fetch(iFetchContext) OF
        ATS_RESOLVED:
            iState := DONE;
        ATS_REJECTED:
            SupabaseFetch_getError(
                Context:=iFetchContext,
                Error=>iError,
                ErrorID=>iErrorID,
                ErrorIDEx=>iErrorIDEx);
            
            iState := DONE;
    END_CASE;

In the NEW_FETCH section, you generate the API call query and then create a Fetch instance, passing the query as an argument. Once the Fetch is generated, the process moves to the DO_FETCH section, where the Fetch is monitored each cycle until a success or failure status is returned, at which point the operation completes.

Generating a Fetch doesn't consume resources from the API client service. This allows you to accumulate Fetcnes during a fault and execute them once the system recovers. However, please note that if the API client service is reloaded, any Fetcnes generated before the reload will fail if executed.

Executing and Monitoring a Fetch

The generated Fetch is passed to a specific POU for execution and monitoring. These execution and monitoring POUs include:

Supabase_fetch:
This POU returns the Fetch's status as an enumerated value: ATS_PENDING (In progress), ATS_RESOLVED (Success), or ATS_REJECTED (Failure). It's typically used with a CASE statement.
Supabase_fetchSignals:
This POU outputs the Fetch's status as a boolean value: Pending (In progress), Resolved (Success), or Rejected (Failure). It's designed for use in Ladder Diagram (LD) logic.

You must execute these execution and control POUs until they return a success or failure status. Since API call processing occurs within the API client service, the execution and control POUs should run once per cycle, or once per cycle where a response can be processed. Executing them multiple times in a single cycle is permissible but won't shorten the processing time.

When an execution/monitoring POU returns a success or failure status, it internally duplicates the response. Therefore, if other processes have tight task time constraints, or if multiple Fetcnes are used concurrently and complete simultaneously, a task timeout might occur. It's not necessary to monitor a Fetch every cycle; executing it during cycles where the response can be processed is sufficient.

Once an execution/monitoring POU has processed a Fetch, you must confirm its success or failure. The execution/monitoring POU releases API client service tasks when it returns a success or failure. If you don't confirm the status, the task resources will remain occupied. Exercise caution when using Fetch in arbitrarily executed POUs. Also, a Fetch becomes re-executable once the execution/monitoring POU returns a success or failure. If you don't intend to re-execute it, avoid running that Fetch with the execution/monitoring POU afterward.

Retrieving Fetch Results

Once a Fetch's status is confirmed, you can retrieve its contents for further processing. The following POUs are used to extract necessary values from a Fetch:

SupabaseFetch_getError: Retrieves error information from the Fetch.
SupabaseFetch_getStatusCode: Retrieves the Fetch's response status code.
SupabaseFetch_getResponseHeader: Retrieves the Fetch's response headers.
SupabaseFetch_getResponseBody: Retrieves the Fetch's response body as a byte array.
SupabaseFetch_getResponseBodyAsStr: Retrieves the Fetch's response body as a string.

Fetch State Transition

Finally, here's the state transition for a Fetch according to the procedure:

stateDiagram-v2
    [*] --> UNDEFINED : Supabase_newFetch()
    UNDEFINED --> run : Supabase_fetch()

    state run {
        state settled <<choice>>
        [*] --> PENDING : The task created in the service
        PENDING --> settled : The task done
        settled --> RESOLVED : success
        settled --> REJECTED : failure
        RESOLVED --> [*]
        REJECTED --> [*]
    }

Query POU

A Query is a structure (SupabaseQuery) that aggregates the target table and operations required for generating a Fetch. The following Query POUs are used to construct a query:

POU	Function
`SPQ_SCHEMA`	Specifies the schema.
`SPQ_FROM`	Specifies the table.
`SPQ_SELECT`	Specifies record retrieval.
`SPQ_INSERT`	Specifies single record creation.
`SPQ_BULK_INSERT`	Specifies multiple record creation.
`SPQ_UPDATE`	Specifies record updating.
`SPQ_UPSERT`	Specifies record updating via Upsert.
`SPQ_SINGLE_UPSERT`	Specifies single record updating via Upsert.
`SPQ_DELETE`	Specifies record deletion.
`SPQ_FILTER`	Specifies a filter.
`SPQ_PARAM`	Specifies an arbitrary URL query parameter.
`SPQ_RPC`	Specifies a PostgreSQL function call.
`SPQ_FUNCTION`	Specifies an Edge function call.
`SPQ_GRAPHQL`	Specifies a GraphQL.
`SupabaseQuery_addPreference`	Adds an arbitrary preference.
`SupabaseQuery_setHeader`	Sets an arbitrary HTTP header.

SPQ_SCHEMA: Specifying the Schema

The schema is specified using SPQ_SCHEMA. Use it as follows:

SupabaseQuery_init(iQuery);
SPQ_SCHEMA('myschema', iQuery);

SPQ_FROM: Specifying the Table (From)

The table is specified using SPQ_FROM. Use it as follows:

SupabaseQuery_init(iQuery);
SPQ_FROM('mytable', iQuery);

SPQ_SELECT: Retrieving Records (Select)

SPQ_SELECT is used after specifying the database. Use it as follows:

SupabaseQuery_init(iQuery);
SPQ_FROM('client_test', iQuery);
SPQ_SELECT('*', iQuery);
SPQ_FILTER('limit', '3', iQuery);

SPQ_INSERT: Creating a Single Record (Insert)

SPQ_INSERT is used after specifying the database. Use it as follows:

SupabaseQuery_init(iQuery);
SPQ_FROM('client_test', iQuery);
SPQ_INSERT(iPayload, 0, iPayloadSize, iQuery);

// If you want to retrieve the record as a response
SupabaseQuery_addPreference(
    Context:=iQuery,
    Preference:='return=representation');

The payload is assumed to be in JSON format, but you can also pass a CSV payload and then use SupabaseQuery_setHeader to replace the Content-Type and set it to CSV format.

SPQ_BULK_INSERT: Creating Multiple Records (Bulk Insert)

SPQ_BULK_INSERT is used after specifying the database. Use it as follows:

SupabaseQuery_init(iQuery);
SPQ_FROM('client_bulk_insert_test', iQuery);
SPQ_BULK_INSERT(iPayload, 0, iPayloadSize, 'application/json', iQuery);

// If you want to retrieve the records as a response
SupabaseQuery_addPreference(
    Context:=iQuery,
    Preference:='return=representation');

If the payload is in CSV format, use it as follows:

SupabaseQuery_init(iQuery);
SPQ_FROM('client_bulk_insert_test', iQuery);
SPQ_BULK_INSERT(iPayload, 0, iPayloadSize, 'text/csv', iQuery);

// If you want to retrieve the records as a response
SupabaseQuery_addPreference(
    Context:=iQuery,
    Preference:='return=representation');

SPQ_UPDATE: Updating Records (Update)

SPQ_UPDATE is used after specifying the database. Use it as follows:

SupabaseQuery_init(iQuery);
SPQ_FROM('client_test', iQuery);
SPQ_UPDATE(iPayload, 0, iPayloadSize, iQuery);
SPQ_FILTER('id', 'lt.3', iQuery);

SPQ_UPSERT: Updating Records (Upsert)

SPQ_UPSERT is used after specifying the database. Use it as follows:

SupabaseQuery_init(iQuery);
SPQ_FROM('client_upsert_test', iQuery);
SPQ_UPSERT(iPayload, 0, iPayloadSize, 'application/json', iQuery);

If the payload is in CSV format, use it as follows:

SupabaseQuery_init(iQuery);
SPQ_FROM('client_upsert_test', iQuery);
SPQ_UPSERT(iPayload, 0, iPayloadSize, 'text/csv', iQuery);

SPQ_SINGLE_UPSERT: Updating a Single Record (Upsert)

SPQ_SINGLE_UPSERT is used after specifying the database for a single record Upsert. Use it as follows:

SupabaseQuery_init(iQuery);
SPQ_FROM('client_upsert_test', iQuery);
SPQ_SINGLE_UPSERT(iPayload, 0, iPayloadSize, 'sku', 'MS0', iQuery);

The payload is assumed to be in JSON format, but you can also pass a CSV payload and then use SupabaseQuery_setHeader to replace the Content-Type and set it to CSV format. Single record Upsert corresponds to PostgREST's PUT-based Upsert.

SPQ_RPC: Calling PostgreSQL Functions (RPC)

PostgreSQL functions are specified using SPQ_RPC. Use it as follows:

SupabaseQuery_init(iQuery);
SPQ_RPC('helloworld', iPayload, 0, 0, iQuery);

SPQ_FUNCTION: Calling Edge Functions

Edge functions are specified using SPQ_FUNCTION. Use it as follows:

SupabaseQuery_init(iQuery);
SPQ_FUNCTION('hello-world', iPayload, 0, iPayloadSize, iQuery);

SPQ_GRAPHQL: GraphQL

GraphQL are specified using SPQ_GRAPHQL. Use it as follows:

SupabaseQuery_init(iQuery);
SPQ_GRAPHQL(iPayload, 0, iPayloadSize, iQuery);

SPQ_FILTER: Specifying a Filter

A filter is specified using SPQ_FILTER. Use it as follows:

SPQ_FILTER('id', 'gt.3', iQuery);

No validation checks (like operator validation) or sanitization (like SQL escaping of operands) are performed.

SQP_PARAM: Specifying URL Query Parameters

URL query parameters are specified using SPQ_PARAM. Use it as follows:

SPQ_PARAM('key', 'value', iQuery);

SupabaseQuery_addPreference: Setting Preferences

In PostgREST, you might need to control the response using Prefer headers to get the desired outcome. You can set preferences like this:

SupabaseQuery_addPreference(
    Context:=iQuery,
    Preference:='timezone=Aisa/Tokyo');

You can set multiple preferences, but you can only add them; replacement is not supported.

SupabaseQuery_setHeader: Setting HTTP Headers

For various reasons, you might need custom HTTP headers. You can set HTTP headers like this:

SupabaseQuery_setHeader(
    Context:=Query,
    Key:='x-service-key',
    Value:='mykey');

HTTP headers can only be added. Values for the same key can be replaced.

Name		Name	Last commit message	Last commit date
Latest commit History 3 Commits
images		images
lib		lib
libdev		libdev
.gitattributes		.gitattributes
.gitignore		.gitignore
LICENSE		LICENSE
Monitor-Print.ps1		Monitor-Print.ps1
README.md		README.md
README_ja.md		README_ja.md
SupabaseClientServiceLib.slr		SupabaseClientServiceLib.slr
SupabaseClientServiceLib.smc2		SupabaseClientServiceLib.smc2
SupabaseClientServiceLibExample.smc2		SupabaseClientServiceLibExample.smc2
monitor-local-stunit.sh		monitor-local-stunit.sh
setup_demo.sql		setup_demo.sql

License

kmu2030/SupabaseClientServiceLib

Folders and files

Latest commit

History

Repository files navigation