sqler sql 转rest api 2.0 试用

sqler 的迭代还是很快的，已经2.0 了，2.0 有好多新功能的添加，同时也有好多不兼容的修改
说明： 测试使用docker-compose，同时我已经push 了docker 镜像 dalongrong/sqler

发布说明

  • 添加 aggregate
  • 移除 authorizers hooks
  • 添加 authorizer script
  • 移除 rules
  • 添加 validators as array of scripts
  • 添加 Go text/template
  • 添加 include
  • 添加 bind

环境准备

  • docker-compose 文件
 
version: "3"
services:
  sqler:
    image: dalongrong/sqler:2.0
    volumes:
    - "./config/config-2-0-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
 
 
  • 配置说明
    使用了2.0 的参考配置，为了方便，我注释了授权的处理
 
// 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 `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
}
​
// 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 {
    exec = "SHOW DATABASES"
}
​
// list all tables from all databases
tables {
    exec = "SELECT `table_schema` as `database`, `table_name` as `table` FROM INFORMATION_SCHEMA.tables"
}
​
// a macro that aggregates `databases` macro and `tables` macro into one macro
databases_tables {
    aggregate = ["databases", "tables"]
}
 
 

运行&&测试

  • 启动
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"
}'
 
 

返回结果

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":2,"email":"1141591465@qq.com","name":"dalong","password":"dalongdemo","time":1547433588}],"success":true}%
 
 
  • 聚合功能试用
curl http://localhost:8025/databases_tables | jq
 

返回结果
数据比较多，截取部分

 
{
  "data": {
    "databases": [
      {
        "Database": "information_schema"
      },
      {
        "Database": "mysql"
      },
      {
        "Database": "performance_schema"
      },
      {
        "Database": "sys"
      },
      {
        "Database": "test"
      }
    ],
。。。。。。
 
 
  • redis 集成
    使用redis_cli
 
redis-cli -p 3678
 

列出宏列表

list
1) "tables"
2) "databases_tables"
3) "_boot"
4) "adduser"
5) "databases"
 
 

调用宏
databases_tables
数据较多，返回部分

 
databases_tables
1) (integer) 1
2) "{\"databases\":[{\"Database\":\"information_schema\"},{\"Database\":\"mysql\"},{\"Database\":\"performance_schema\"},{\"Database\":\"sys\"},{\"Database\":\"test\"}],\"tables\":[{\"database\":\"information_schema\",\"table\":\"CHARACTER_SETS\"},{\"database\":\"information_schema\",\"table\":\"COLLATIONS\"},{\"database\":\"information_s

说明

2.0 的功能是越来越方便了

参考资料

https://github.com/alash3al/sqler
https://github.com/rongfengliang/sqler-docker-compose
https://cloud.docker.com/repository/docker/dalongrong/sqler/

posted on 2019-01-14 10:47  荣锋亮  阅读(711)  评论(0)  编辑  收藏  举报

导航