基于postgresql的API设计

@H_502_3@本文档设计思想主要是用于简化API一些重复验证工作，同时固定API入口,在入口中做请求分发，以便在入口做一些统计工作以及请求次数限制. -- function api_interface(in jsonb,out jsonb); --入口函数(存储过程) api_interface_error(code); -- 统一错误处理函数 api_interface_lines(user_id,params,team_id); --线路列表例子 --table api_errors -- 统一平台上错误信息 api_request_log --记录请求日志

入口设置

@H_502_3@入口主要是读取一些通用信息，如access_token(凭证),interface(请求接口名称),version(接口版本)等信息,同时记录请求日志,以及后续的接口请求次数限制等 @H_502_3@-- Function: api_interface(jsonb) -- DROP FUNCTION api_interface(jsonb); CREATE OR REPLACE FUNCTION api_interface( IN _input jsonb,OUT _result jsonb) RETURNS jsonb AS $BODY$ --访问凭证 DECLARE _access_token TEXT; --接口版本 DECLARE _version text; --调用函数 DECLARE _interface TEXT; --参数 DECLARE _params JSONB; --错误码 DECLARE code INTEGER; --错误消息 DECLARE message TEXT; --用户ID DECLARE _user_id UUID; DECLARE v_sql TEXT; BEGIN _access_token = _input::jsonb->>'access_token'; _version = _input::jsonb->>'version'; _interface = _input::jsonb->>'interface'; _params = _input::jsonb->'params'; IF (_access_token IS NULL ) THEN code = 1001; SELECT api_interface_error(code) INTO _result; RETURN; END IF; IF (_interface IS NULL) THEN code = 1002; SELECT api_interface_error(code) INTO _result; RETURN; END IF; IF (_version IS NULL) THEN code = 1003; SELECT api_interface_error(code) INTO _result; RETURN; END IF; IF ( _params IS NULL) THEN code = 1004; SELECT api_interface_error(code) INTO _result; RETURN; END IF; --查看access_token是否存在 SELECT id FROM public.users WHERE account_id = (SELECT account_id FROM public.account_tokens WHERE token = _access_token LIMIT 1) LIMIT 1 INTO _user_id; IF (_user_id IS NULL) THEN code = 1005; SELECT api_interface_error(code) INTO _result; RETURN; END IF; --插入到请求日志中 INSERT INTO public.api_request_log(user_id,interface,version,params) VALUES( _user_id,_interface,_version,_params); --动态拼接sql v_sql = concat('SELECT api_interface_',_interface); v_sql = concat(v_sql,'($1::uuid,$2::jsonb,$3::uuid)'); EXECUTE v_sql USING _user_id,_params::jsonb,'00000000-0000-0000-0000-000000000000' INTO _result; RETURN; END$BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION api_interface(jsonb) OWNER TO postgres; COMMENT ON FUNCTION api_interface(jsonb) IS 'API接口';

接口例子（如线路列表）

@H_502_3@-- Function: api_interface_lines(uuid,jsonb,uuid) -- DROP FUNCTION api_interface_lines(uuid,uuid); CREATE OR REPLACE FUNCTION api_interface_lines( IN _user_id uuid,IN _input jsonb,IN _team_id uuid,OUT _result jsonb) RETURNS jsonb AS $BODY$ DECLARE code INTEGER; DECLARE message TEXT; BEGIN code = 0; message = ''; SELECT row_to_json(t) FROM (SELECT code,message,(SELECT json_agg (x) FROM (SELECT * from lines limit 2) x) lines) t INTO _result; RETURN; END;$BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION api_interface_lines(uuid,uuid) OWNER TO postgres; COMMENT ON FUNCTION api_interface_lines(uuid,uuid) IS '线路列表';

辅助函数和表

@H_502_3@记录请求日志统一处理错误 nodejs中的使用

统一错误处理

@H_502_3@CREATE OR REPLACE FUNCTION api_interface_error( IN _code integer,OUT result jsonb) RETURNS jsonb AS $BODY$ DECLARE _message text; BEGIN SELECT message FROM api_errors AS AE WHERE AE.code = _code INTO _message; IF (_message IS NULL) THEN _message = ''; END IF; SELECT row_to_json(t) FROM (SELECT _code,_message) t INTO result; RETURN; END$BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION api_interface_error(integer) OWNER TO postgres; COMMENT ON FUNCTION api_interface_error(integer) IS 'API接口调用错误';

记录请求日志

@H_502_3@CREATE TABLE api_request_log ( id uuid NOT NULL DEFAULT uuid_generate_v4(),user_id uuid,create_at timestamp without time zone DEFAULT now(),-- 创建时间 interface character varying(50),-- 请求接口 params jsonb,-- 请求参数 version character varying(50),-- API版本 CONSTRAINT api_request_log_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); ALTER TABLE api_request_log OWNER TO postgres; COMMENT ON TABLE api_request_log IS 'API请求日志'; COMMENT ON COLUMN api_request_log.create_at IS '创建时间'; COMMENT ON COLUMN api_request_log.interface IS '请求接口'; COMMENT ON COLUMN api_request_log.params IS '请求参数'; COMMENT ON COLUMN api_request_log.version IS 'API版本';

统计 错误信息

@H_502_3@CREATE TABLE api_errors ( id uuid NOT NULL DEFAULT uuid_generate_v4(),code integer,message text,-- 错误信息 CONSTRAINT pk_api_errors_id PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); ALTER TABLE api_errors OWNER TO postgres; COMMENT ON TABLE api_errors IS 'api错误信息'; COMMENT ON COLUMN api_errors.message IS '错误信息';

nodejs 直接调用存储过程

@H_502_3@var _input_json = req.body.input; //对_input_json进行一些参数判断，如token是否合法等 //省略... // var sql = "SELECT public.api_interface($1::jsonb) AS result"; db.execQuery(sql,[_input_json]) .then(function(result){ return result.rows[0]['result']; }) .then(function(result){ //返回的已经是标准格式的json res.json(result); }) .catch(next).done();

客户端请求:

@H_502_3@android or any: String response = ''; request .send({"access_token":"TOKEN","interface":"lines","version":"1.0","params":{"param1":1,"params2":2}}) .end(response); System.out.println(response); 原文链接：https://www.f2er.com/postgresql/195324.html

基于postgresQL的API设计