基于postgresql的API设计

本文档设计思想主要是用于简化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 --记录请求日志

入口设置

入口主要是读取一些通用信息，如access_token(凭证),interface(请求接口名称),version(接口版本)等信息,同时记录请求日志,以及后续的接口请求次数限制等

-- 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接口';

接口例子（如线路列表）

-- 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 '线路列表';

辅助函数和表

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

统一错误处理

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接口调用错误';

记录请求日志

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版本';

统计错误信息

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 直接调用存储过程

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();

客户端请求:

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);