Bridge and Subscriber

Bridge

Register a bridge

register sqlite connection

bridge add -t sqlite sqlitedb file:/data/sqlite.db;

List registered bridges

bridge list
┌──────────┬────────┬────────────────────────┐
│ NAME     │ TYPE   │ CONNECTION             │
├──────────┼────────┼────────────────────────┤
│ sqlitedb │ sqlite │ file:/data/sqlite.db   │
└──────────┴────────┴────────────────────────┘

Execute commands on the bridge

bridge exec sqlitedb CREATE TABLE IF NOT EXISTS example(id INTEGER NOT NULL PRIMARY KEY, name TEXT, age TEXT, address TEXT, UNIQUE(name));

Query command on the bridge

bridge query command is only works with “SQL” type bridges

bridge query sqlitedb select * from example;

┌────┬────────┬─────┬───────────────┐
│ ID │ NAME   │ AGE │ ADDRESS       │
├────┼────────┼─────┼───────────────┤
│  1 │ hong_1 │ 20  │ address for 1 │
│  2 │ hong_2 │ 20  │ address for 2 │
│  3 │ hong_3 │ 20  │ address for 3 │
└────┴────────┴─────┴───────────────┘

Utilize a bridge in tql with SQL()

SQL() takes bridge() option with “SQL” type bridge and execute the given SQL statement.

SQL(bridge("sqlitedb"), `select * from example`)
CSV()

Utilize a bridge in tql SCRIPT()

You can access database-type bridges from SCRIPT() using $.db({bridge:"name"}), as shown in the example below.

Support for accessing bridged databases in JavaScript using $.db({bridge:"name"}) has been available since version 8.0.27.

SCRIPT({
    err = $.db({bridge:"mem"})
     .query("select company, employee, created_on from mem_example")
     .forEach( function(fields){
        $.yield(fields[0], fields[1], fields[2]);
     })
    if (err !== undefined) {
        console.error("result", ret);
    }
})
CSV()

Copy data to other database

This example demonstrates how to copy data from Machbase to an SQLite bridge.

Bridge

Define a sqlite bridge with the following details:

• Type: SQLite
• Connection string: file:///tmp/sqlite.db

SQL

Create the example table in the SQLite database located at “/tmp/sqlite.db”.

--env: bridge=sqlite
CREATE TABLE IF NOT EXISTS example (
    NAME TEXT,
    TIME DATETIME,
    VALUE REAL
);
-- env: reset

TQL

The TQL script below executes a SELECT statement using the SQL() function to retrieve the required data, and then writes the data into the SQLite database using the bridge("sqlite") as the first argument.

SQL(`select name, time, value from example where name = 'my-car'`)
SQL(bridge('sqlite'), `insert into example values(?,?,?)`, value(0), value(1), value(2))

Subscriber

The purpose of a subscriber is connecting to an external message broker system, receiving streaming messages, ingesting messages by tql script.

Currently machbase-neo supports connecting to the external MQTT brokers, and it will support also NATS and Kafka with the future releases.

A simple use case is that make a bridge to the external MQTT broker, and define a subscriber with

1. the bridge, 2) a topic of the MQTT broker and 3) tql script path. Then machbase-neo works as MQTT client and whenever it receives messages, it passes them to the specified tql script.

    flowchart RL
    external-system --PUBLISH--> machbase-neo
    machbase-neo --SUBSCRIBE--> external-system
    subgraph machbase-neo
        direction RL
        bridge --> subscriber
        subscriber["Subscriber
                    TQL"] --Write--> machbase
        machbase[("machbase
                    engine")]
    end
    subgraph external-system
        direction RL
        client["Client"] --PUBLISH--> mqtt[["MQTT
                                            Broker"]]
    end
  

Register a subscriber

Register subscribers.

Syntax: subscriber add [options] <name> <bridge> <topic> <tql-path>

• options

  • --autostart makes the subscriber will start automatically when machbase-neo starts. If the subscriber is not autostart mode, you can make it start and stop manually by subscriber start <name> and subscriber stop <name> commands.
  • --qos <int> if the bridge is MQTT type, it specifies the QoS level of the subscription to the topic. It supports 0, 1 and the default is 0 if it is not specified.
  • --queue <string> if the bridge is NATS type, it specifies the Queue Group.

• <name> subscriber’s name

• <bridge> specify pre-defined bridge, it should be a type of the broker

• <topic> topic to subscribe

• <tql-path> the tql script that handles the received message

Subscriber Status

Syntax: subscriber list

• STOP
• RUNNING

Subscriber Start/Stop

Syntax: subscriber [start | stop] <name>

Remove subscriber

Syntax: subscriber del <name>