cratedb 集成graphjin 尝试（木有成功）
graphjin 是一个很不错的graphql engine（library），因为cratedb 兼容pg协议，所以以下是一个简单的
集成尝试
环境准备
- cratedb 环境
version: "3"
services:
db:
image: dalongrong/cratedb:4.4.0
ports:
- "4200:4200"
- "4300:4300"
- "5432:5432"
- golang 集成
main.go
package main
import (
"context"
"database/sql"
"fmt"
"log"
"github.com/dosco/graphjin/core"
_ "github.com/jackc/pgx/v4/stdlib"
)
func main() {
db, err := sql.Open("pgx", "postgres://crate:@localhost:5432/doc")
if err != nil {
log.Fatal(err)
}
sg, err := core.NewGraphJin(nil, db)
if err != nil {
log.Fatal(err)
}
query := `
query {
demoapps{
id
full_name
email
}
}`
ctx := context.Background()
ctx = context.WithValue(ctx, core.UserIDKey, 1)
res, err := sg.GraphQL(ctx, query, nil)
if err != nil {
log.Fatal(err)
}
fmt.Println(string(res.Data))
}
环境初始化
- 创建table&&写入数据(使用admin)
docker-compose up -d
create table demoapp(id int ,name string, age int);
insert into demoapp(id,name,age) values(3,'dalong',3444);
- 运行(没有通过)
go run main.go
- 几个问题
INF attribute based access control disabled: roles_query not set
2021/01/10 18:52:17 error fetching columns: ERROR: line 3:20: no viable alternative at input 'SELECT \n\tcol.table_name as table' (SQLSTATE XX000)
通过代码调试返现以下问题:
core/core.go 对于处理列有问题，尝试的修改（sql,core/internal/sdata/sql.go）
主要是数据类型处理，以及引号问题
SELECT
col.table_name as "table",
col.column_name as "name",
col.data_type as "type",
(CASE
WHEN col.is_nullable = TRUE THEN TRUE
ELSE FALSE
END) AS notnull,
(CASE
WHEN col.data_type = 'ARRAY' THEN TRUE
ELSE FALSE
END) AS isarray,
(CASE
WHEN tc.constraint_type = 'PRIMARY KEY' THEN TRUE
ELSE FALSE
END) AS primarykey,
(CASE
WHEN tc.constraint_type = 'UNIQUE' THEN TRUE
ELSE FALSE
END) AS uniquekey,
(CASE
WHEN tc.constraint_type = 'FOREIGN KEY' THEN ccu.table_schema
ELSE ''
END) AS foreignkey_schema,
(CASE
WHEN tc.constraint_type = 'FOREIGN KEY' THEN ccu.table_name
ELSE ''
END) AS foreignkey_table,
(CASE
WHEN tc.constraint_type = 'FOREIGN KEY' THEN ccu.column_name
ELSE ''
END) AS foreignkey_column
FROM
information_schema.columns col
LEFT JOIN
information_schema.key_column_usage kcu ON col.table_schema = kcu.table_schema
AND col.column_name = kcu.column_name
LEFT JOIN
information_schema.table_constraints tc ON kcu.table_schema = tc.table_schema
AND kcu.constraint_name = tc.constraint_name
LEFT JOIN
information_schema.key_column_usage ccu ON tc.constraint_schema = ccu.constraint_schema
AND ccu.constraint_name = tc.constraint_name
WHERE
col.table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY
col.ordinal_position
INF attribute based access control disabled: roles_query not set
2021/01/10 15:18:09 Error fetching functions: ERROR: Relation 'information_schema.parameters' unknown (SQLSTATE 42P01)
cratedb 就不存在information_schema.parameters 内存表（graphjin 主要是进行函数的处理）
解决方法：
注释关于function 的处理 core/internal/sdata/tables.go
cratedb-graphjin go run main.go
INF attribute based access control disabled: roles_query not set
2021/01/10 15:24:17 ERROR: line 1:119: no viable alternative at input 'SELECT jsonb_build_object('demoapps', __sj_0.json) AS __root FROM (VALUES(true)) AS __root_x LEFT OUTER JOIN LATERAL (SELECT' (SQLSTATE XX000)
此问题主要是cratedb对于jsonb 的支持问题，当然解决方法也是有的，我们可以通过自定义函数解决
目前从sql 编译的来看，对于jsonb 的支持需要好几个函数，后边研究好了，尝试提供提供相关的自定义函数
说明
cratedb client 兼容性，但是因为graphjin使用了json的处理，但是cratedb 没有json 的直接支持，所有就有问题了，从套路上
graphjin 的处理特别类似hasura（基于了jsonb 的处理能力） 
参考资料
https://crate.io/docs/crate/clients-tools/en/latest/
https://github.com/jackc/pgx
https://github.com/dosco/graphjin/blob/master/core/core.go#L70
https://crate.io/docs/crate/reference/en/4.3/general/information-schema.html#information-schema
https://crate.io/docs/crate/reference/en/4.3/admin/system-information.html#system-information
