sqler 在10前发布了，2.2 添加了定时任务以及触发器（webhook），都是比较方便的功能，
同时我也修改了dockerfile， 做了构建，添加了功能支持，同时push 到了dockerhub 仓库

• docker-compose 文件
  集成了一个测试webhook benthos

version: "3"

services:

 sqler:

   image: dalongrong/sqler:2.2

   volumes:

   - "./config/config-2-2-example.hcl:/app/config.example.hcl"

   environment:

   - "DSN=root:dalongrong@tcp(mysqldb:3306)/test?multiStatements=true"

   ports:

   - "3678:3678"

   - "8025:8025"

 benthos:

   image: jeffail/benthos

   volumes:

   - "./configs/webhook.yaml:/benthos.yaml"

   ports:

   - "4195:4195"

 mysqldb:

   image: mysql:5.7.16

   ports:

     - 3306:3306

   command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

   environment:

     MYSQL_ROOT_PASSWORD: dalongrong

     MYSQL_DATABASE: test

     MYSQL_USER: test

     MYSQL_PASSWORD: test

     TZ: Asia/Shanghai

• benthos webhook 配置

input:

 type: broker

 broker:

   inputs:

     - type: http_server

       http_server:

         path: /post/customer1

       processors:

         - type: text

           text:

             operator: prepend

             value: "Customer 1 received: "

     - type: http_server

       http_server:

         path: /post/customer2

       processors:

         - type: text

           text:

             operator: prepend

             value: "Customer 2 received: "

     - type: http_server

       http_server:

         path: /post/customer3

       processors:

         - type: text

           text:

             operator: prepend

             value: "Customer 3 received: "

output:

 type: stdout

• 参考配置文件

// create a macro/endpoint called "_boot",

// this macro is private "used within other macros"

// because it starts with "_".

_boot {

   // the query we want to execute

   exec = <<SQL

       CREATE TABLE IF NOT EXISTS datax (

           ID INT PRIMARY KEY,

           data TEXT DEFAULT NULL

      );

   SQL

}

​

addpost {

   include = ["_boot"]

   methods = ["POST"]

​

   // validators {

   // title_is_empty = "$input.title && $input.title.trim().length > 0"

   // content_is_empty = "$input.content"

   // }

​

   bind {

       data = <<JS

           JSON.stringify({

               "title": $input.title,

               "content": $input.content

          })

       JS

  }

​

   exec = <<SQL

       INSERT INTO datax(ID, data) VALUES(default, :data) RETURNING id, data;

   SQL

}

​

// adduser macro/endpoint, just hit `/adduser` with

// a `?user_name=&user_email=` or json `POST` request

// with the same fields.

adduser {

   validators {

       user_name_is_empty = "$input.user_name && $input.user_name.trim().length > 0"

       user_email_is_empty = "$input.user_email && $input.user_email.trim().length > 0"

       user_password_is_not_ok = "$input.user_password && $input.user_password.trim().length > 5"

  }

​

   bind {

       name = "$input.user_name"

       email = "$input.user_email"

       password = "$input.user_password"

  }

​

   methods = ["POST"]

​

   authorizer = <<JS

      (function(){

           log("use this for debugging")

           token = $input.http_authorization

           response = fetch("http://requestbin.fullcontact.com/zxpjigzx", {

               headers: {

                   "Authorization": token

              }

          })

           if ( response.statusCode != 200 ) {

               return false

          }

           return true

      })()

   JS

​

   // include some macros we declared before

   include = ["_boot"]

​

   exec = <<SQL

       INSERT INTO users(name, email, password, time) VALUES(:name, :email, :password, UNIX_TIMESTAMP());

       SELECT * FROM users WHERE id = LAST_INSERT_ID();

   SQL

}

​

// list all databases, and run a transformer function

databases {

   // include = ["_boot"]

   exec = "SHOW DATABASES"

   transformer = <<JS

      (function(){

           // $result

           $new = [];

           for ( i in $result ) {

               $new.push($result[i].Database)

          }

           return $new

      })()

   JS

}

​

// list all tables from all databases

tables {

   exec = "SELECT `table_name` as `table`, `table_schema` as `database` FROM INFORMATION_SCHEMA.tables"

   transformer = <<SQL

      (function(){

           $ret = []

           for ( i in $result ){

               $ret.push({

                   table: $result[i].table,

                   database: $result[i].database,

              })

          }

           return $ret

      })()

   SQL

}

​

data {

   bind {

       limit = 2

       field = "'id'"

  }

​

   exec = "SELECT id FROM data limit 5"

}

​

// a macro that aggregates `databases` macro and `tables` macro into one macro

databases_tables {

   aggregate = ["databases", "tables"]

}

​

_sqlite_tables {

   exec = <<SQL

   SELECT

       name

   FROM

       userinfos;

   SQL

   cron = "* * * * *"

   trigger {

       webhook = "http://benthos:4195/post/customer1"

  }

}

• 启动

docker-compose up -d

• 准备测试数据表
  参考如下脚本添加（主要测试定时任务以及webhook）

CREATE TABLE `userinfos` (

 `id` bigint(20) DEFAULT NULL,

 `name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

​

INSERT INTO test.userinfos

(id, name)

VALUES(1, 'dalong'); 

• 效果
  查看benthos 容器日志

docker-compose logs -f benthos

效果

sqler 日志

https://github.com/alash3al/sqler
https://github.com/rongfengliang/sqler-docker-compose
https://github.com/Jeffail/benthos