sqler 支持redis 协议，我们可以用过redis client 连接sqler，他会将宏住转换为redis command
实现上看源码我们发现是基于一个开源的redis 协议的golang 实现，同时sqler 内置了一些方便
的command ： list、 info、 echo、select、ping。
目前关于redis 的使用文档基本没有，但是我们通过阅读源码可以看出，就是解析参数，第一个为
command， 第二个为数据（json 序列化之后的，下边会有使用的说明
环境准备
docker 镜像，可以参考我的文章，里面有镜像的制作同时我也上传dockerhub 了
- docker-compose 文件
version: "3"
services:
sqler:
image: dalongrong/sqler:1.6
volumes:
- "./config/config.example.hcl:/app/config.example.hcl"
environment:
- "DSN=root:dalongrong@tcp(mysqldb:3306)/test?multiStatements=true"
ports:
- "3678:3678"
- "8025:8025"
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
- 运行的配置文件
_boot {
exec = <<SQL
CREATE TABLE IF NOT EXISTS `users` (
`ID` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(30) DEFAULT "@anonymous",
`email` VARCHAR(30) DEFAULT "@anonymous",
`password` VARCHAR(200) DEFAULT "",
`time` INT UNSIGNED
);
SQL
}
allusers {
methods = ["GET"]
exec = <<SQL
SELECT * FROM users;
SQL
}
adduser {
methods = ["POST"]
rules {
user_name = ["required"]
user_email = ["required", "email"]
user_password = ["required", "stringlength: 5,50"]
}
exec = <<SQL
{{ template "_boot" }}
/* let's bind a vars to be used within our internal prepared statment */
{{ .BindVar "name" .Input.user_name }}
{{ .BindVar "email" .Input.user_email }}
{{ .BindVar "emailx" .Input.user_email }}
INSERT INTO users(name, email, password, time) VALUES(
/* we added it above */
:name,
/* we added it above */
:email,
/* it will be secured anyway because it is encoded */
'{{ .Input.user_password | .Hash "bcrypt" }}',
/* generate a unix timestamp "seconds" */
{{ .UnixTime }}
);
SELECT * FROM users WHERE id = LAST_INSERT_ID();
SQL
}
databases {
exec = "SHOW DATABASES"
transformer = <<JS
// there is a global variable called `$result`,
// `$result` holds the result of the sql execution.
(function(){
newResult = []
for ( i in $result ) {
newResult.push($result[i].Database)
}
return newResult
})()
JS
}
运行&&基本试用
- 启动
docker-compose up -d
- 添加数据
curl -X POST \
http://localhost:8025/adduser \
-H 'Content-Type: application/json' \
-H 'Postman-Token: a7784ea1-9f50-46ee-92ac-1d850334f3f1' \
-H 'cache-control: no-cache' \
-d '{
"user_name":"dalong",
"user_email":"1141591465@qq.com",
"user_password":"dalongdemo"
}'
返回结果
{"data":[{"ID":1,"email":"1141591465@qq.com","name":"dalong","password":"$2a$10$9gB.kOC8sn5f3jGPo4qzl.tULFkDRXj0i3EMyAr7VvkvBxBijbqKu","time":1547169926}],"success":true}%
- 查询数据
curl -i http://localhost:8025/allusers
返回结果
HTTP/1.1 200 OK
Access-Control-Allow-Origin: *
Content-Type: application/json; charset=UTF-8
Vary: Origin
Vary: Accept-Encoding
Date: Fri, 11 Jan 2019 01:26:20 GMT
Content-Length: 170
{"data":[{"ID":1,"email":"1141591465@qq.com","name":"dalong","password":"$2a$10$9gB.kOC8sn5f3jGPo4qzl.tULFkDRXj0i3EMyAr7VvkvBxBijbqKu","time":1547169926}],"success":true}%
redis 集成使用
为了测试，我使用的是cli，没有编写代码
- 连接
默认端口是3678，可以通过环境变量修改
redis-cli -p 3678
- 查看宏信息
list
输出如下：
1) "adduser"
2) "databases"
3) "_boot"
4) "allusers"
- 通过redis-cli 添加数据
调用adduser 宏
adduser "{\"user_name\":\"dalong\",\"user_email\":\"1141591465@qq.com\",\"user_password\":\"dalongdemo\"}"
效果
实际上从这个也可以看出，会有一些bug，具体待确定
adduser "{\"user_name\":\"dalong\",\"user_email\":\"1141591465@qq.com\",\"user_password\":\"dalongdemo\"}"
(error) not found
127.0.0.1:3678> adduser "{\"user_name\":\"dalong\",\"user_email\":\"1141591465@qq.com\",\"user_password\":\"dalongdemo\"}"
、1) (integer) 1
2) "[{\"ID\":2,\"email\":\"1141591465@qq.com\",\"name\":\"dalong\",\"password\":\"$2a$10$995GTjNXCNAElXHafDoxkOsum5juPBjpu5dlIt0GJi.TsJfQ3uRIK\",\"time\":1547170172}]"
- 通过redis-cli 查询数据
调用allusers 宏
allusers
1) (integer) 1
2) "[{\"ID\":1,\"email\":\"1141591465@qq.com\",\"name\":\"dalong\",\"password\":\"$2a$10$9gB.kOC8sn5f3jGPo4qzl.tULFkDRXj0i3EMyAr7VvkvBxBijbqKu\",\"time\":1547169926},{\"ID\":2,\"email\":\"1141591465@qq.com\",\"name\":\"dalong\",\"password\":\"$2a$10$995GTjNXCNAElXHafDoxkOsum5juPBjpu5dlIt0GJi.TsJfQ3uRIK\",\"time\":1547170172}]"
- 列出数据
调用databases 宏
databases
1) (integer) 1
2) "[\"information_schema\",\"mysql\",\"performance_schema\",\"sys\",\"test\"]"
说明
不太确定是sqler redis 协议的兼容问题还是，目前使用mac 的cli，会有数据时有时无的问题，待确定原因，同时对于添加了安全
认证与redis 怎么通信还有待研究。
参考资料
https://github.com/alash3al/sqler/blob/master/server_resp.go
https://github.com/rongfengliang/sqler-docker-compose
https://github.com/alash3al/sqler