RestQ

在 REST API 中表达数据操作的简单方法。

通过结合合适的 HTTP 方法、URL 和 CSV 数据格式来实现。

示例：带有过滤、分组和分页的简单 person 表的查询。

GET /person?age=lt.42&(student=eq.true|gender=eq.'M')&group_by=sum(age),grade,gender&having=min(age)=gt.42&order_by=age.desc,height.asc&page=20&page_size=100

这可以转换成 SQL 查询。

SELECT * FROM person
WHERE age < 42
    AND (student = true OR gender = 'M')
GROUP BY sum(age), grade, gender
HAVING min(age) > 42
ORDER BY age DESC, height ASC
LIMIT 100 OFFSET 1900 ROWS

响应体将包含查询结果的 csv 格式数据。

RestQ 语法/语法

create  = ["CREATE" | "PUT"], "/",  table, column_def_list, "\n", csv

select = "GET", "/", table, [join_table], column_list, [ "?", condition]

delete = "DELETE", table, [ "?", condition ]

update = ["UPDATE | "PATCH"] table, set_expr_list, [ "?", condition]

drop = ["DROP" | "DELETE"] "-", table

alter = ["ALTER" | "PATCH"] table, { drop_column | add_column | alter_column }

drop_column = "-", column

add_column = "+", column_def

alter_column = column, "=", column_def


column_def_list =  "{", { column_def }, "}"
        | "(", { column_def }, ")"

column_def = [ { column_attributes } ], column, [ "(" foreign ")" ], ":", data_type, [ "(" default_value ")" ]

column_attributes = primary | index | unique

primary = "*"

index = "@"

unique = "&"

data_type = "bool" | "s8" | "s16" | "s32" | "s64" | "u8" | "u16", etc

default_value  = value

value = number | string | bool ,..etc

column = string, ".", string
        | string

table = string

foreign = table

insert = table, column_list ,"\n", csv

column_list = "{", { column }, "}"


join_table = table, join_type, table

join_type = right_join | left_join | inner_join | full_join

right_join = "->"

left_join = "<-"

inner_join = "-><-"

full_join = "<-->"

condition = expr

expr =  column | value | binary_operation

binary_operation = expr, operator, expr

operator = "and" | "or" | "eq" | "gte" | "lte" ,..etc

数据类型

• bool : 布尔值
• s8 : 自动增长的 u8
• s16 : 自动增长的 u16
• s32 : 自动增长的 u32，序列号
• s64 : 自动增长的 u64，大序列号
• f32 : 4 字节浮点数
• f64 : 8 字节浮点数
• i8、i16、i32、i64 : 有符号整数
• u8、u16、u32、u64 : 无符号整数
• text : utf8 字符串
• uuid : 纯 uuid，当为 null 时随机生成
• uuid_rand : 随机生成的 uuid
• uuid_slug : 创建新的 uuid 并从它生成 URL 友好的 base64 字符串
• utc : UTC 时区的时间戳
• url : URL 类型
• json : json
• bytes : 二进制数据

在单个请求中创建表并插入记录。

PUT /+product{*product_id:s32,name:text,created_by(users):u32,created:utc,is_active:bool}
Content-Type: text/csv; charset=UTF-8

1,go pro,1,2019-10-31 11:59:59.872,,true
2,shovel,1,2019-11-01 07:30:00.462,,false

• HTTP 方法是 PUT
• URL 是 restq 语法。
• 正文是 csv

等效的 SQL

CREATE TABLE product (
 product_id serial NOT NULL PRIMARY,
 name character varying NOT NULL,
 created_by integer NOT NULL REFERENCES users(user_id),
 created timestamp with time zone NOT NULL DEFAULT now(),
 is_active boolean NOT NULL

INSERT INTO product(product_id, name, created_by, is_active)
VALUES(
    (1,'go pro',1,2019-10-31 11:59:59.872,DEFAULT,true)
    (2,'shovel',1,2019-11-01 07:30:00.462,DEFAULT,false)
);

显示表定义

HEAD /product

显示所有表

HEAD /

查询记录

GET /product{product_id,name}?is_active=eq.true&order_by=created.desc

SELECT product_id,name FROM product WHERE is_active = true ORDER BY created DESC

插入记录

POST /product{product_id,name,created_by,created,is_active}
1,go pro,1,2019-10-31 11:59:59.872,,true
2,shovel,1,2019-11-01 07:30:00.462,,false

INSERT INTO product(product_id, name, created_by, is_active)
VALUES(
    (1,'go pro',1,2019-10-31 11:59:59.872,true)
    (2,'shovel',1,2019-11-01 07:30:00.462,false)
);

通过查询插入

POST /user{user_id,name,person_id(GET/person{id}?person.name=name)}
1,TOM JONES,,

INSERT INTO user(user_id, name, person_id)
VALUES(1, 'TOM JONES', (SELECT person.id FROM person WHERE person.name='TOM JONES'));

更新记录

PATCH /product{description="I'm the new description now"}?product_id=1

UPDATE product SET description = 'I\'m the new description now' WHERE product_id = 1;

批量更新记录

传递了同一记录的两个版本，第一个是原始版本，下一个是更新后的版本

PATCH /product{*product_id,name}
1,go pro,1,go pro hero4
2,shovel,2,slightly used shovel

UPDATE product SET name = 'go pro hero4' WHERE id = 1;
UPDATE product SET name = 'slightly used shovel' WHERE id = 2'

删除

DELETE /product?product_id=1

DELETE FROM product WHERE product_id = '1'

删除多个

DELETE /product{product_id}
1
2
3

DELETE FROM product WHERE product_id IN ('1','2','3')

按名称删除多个（无主键）

DELETE /product{name,is_active}
Go Pro,true
Shovel,true
Chair,true

DELETE FROM product WHERE name = 'Go Pro' AND is_active = 'true';
DELETE FROM product WHERE name = 'Shovel' AND is_active = 'true';
DELETE FROM product WHERE name  = 'Chair' AND is_active = 'true';

删除表中所有记录

DELETE /product

TRUNCATE product;

复杂选择（带有连接）

GET /product<-users{product.*,users.user_name}?product_id=1&is_active=true&created=gt.2019-11-05T08:45:03.432

SELECT product.*, users.user_name
    FROM product
        LEFT JOIN users ON product.created_by = users.user_id
    WHERE product_id = 1
        AND is_active = true
        AND created > '2019-11-05T08:45:03.432'

连接表

支持的连接类型

• INNER JOIN table1-><-table2
• OUTER JOIN
  • LEFT JOIN table1<-table2
  • RIGHT JOIN table1->table2
  • FULL JOIN table1<-->table2

先前构建和灵感来源

• inquerest，在移植到调用此库的过程中。
• postgrest，restq 的语法与 postgrest 不同，侧重于直观的过滤子句

请支持此项目