Generic sql extension

Overview

Make eKuiper supports sql syntax based database access from/to(source & sink). Provide this ability as native plugin. provide a list of databases support and switch them by build tag. default support mysql.

Rationality

Go native package sql provides a generic interface around SQL (or SQL-like) databases. The sql package must be used in conjunction with a database driver. Once driver imported, user can use APIs provided by sql package to interact with Target Database:

• Open
• Close
• Exec Sql statement more info

There is a package which wrapper the above operation to support a number of databases more easily. users just need to provide the connection info to the target database, then he can get a sql.DB with which he can run the SQL statement

SQL Source

Sql source pull the database periodically to get the data and send the data rows one by one as stream.

Sql Source Name: sql

the configuration for Source include

• ConnectionUrl: the target database address info. Example
• SqlCommand: sql statement against the target database
• Interval: sql statement running interval

sqlCommand

should only accept select clause

SELECT statement... [WHERE condition | GROUP BY `field_name(s)` HAVING condition] ORDER BY `field_name(s)` [ASC | DESC];

Deduplication

Sql source need maintain offset for the query. in the sql context, it should use some sql field's offset. So user need config two parameters

• offsetField: the sql filed that can be used as offset
• offsetValue: initial offset value

After user set the deduplicate field, will generate a condition offsetField > offsetValue (offsetValue will update by on the latest query result's largest offsetField value), and append this condition to sql's condition part automaticlly

SELECT statement... [WHERE condition | GROUP BY `field_name(s)` HAVING condition] ORDER BY `field_name(s)` [ASC | DESC];

SQL Sink

Sql sink write the result data to the target database.

Sql Source Name: sql

• ConnectionUrl: the target database address info. Example
• SqlCommand: sql statement against the target database
• Table: table name for the target
• Fields: Table field collection