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