基於postgresQL的API設計

標籤：

基於postgresQL的API設計

本文檔設計思想主要是用於簡化API一些重複驗證工作，同時固定API入口, 在入口中做請求分發， 以便在入口做一些統計工作以及請求次數限制.-- functionapi_interface(in jsonb, out jsonb);  --入口函數(預存程序)api_interface_error(code);  -- 統一錯誤處理函數api_interface_lines(user_id, params, team_id); --線路列表例子--tableapi_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;--使用者IDDECLARE _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, jsonb, 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, jsonb, uuid)  OWNER TO postgres;COMMENT ON FUNCTION api_interface_lines(uuid, jsonb, 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);

基於postgresQL的API設計