SQL만으로 Open API 생성하기

HandStack에 기본 제공되는 openapi 모듈을 이용하여 SQL을 작성하여 DB에 저장된 데이터를 Open API 형태로 서비스를 할 수 있습니다.

openapi 모듈로 별도의 개발 없이 다양한 포맷(JSON, XML, SOAP, RSS 2.0, ATOM 1.0)들을 지원하는 Endpoint를 관리 할 수 있습니다.

제공 가치

별도의 개발 없이 5개의 테이블의 정보를 구성하여 간단하게 Open API 서비스를 구성 가능합니다.

• Open API 서비스 개발 인력 없이 단기간에 구축이 가능합니다.
• SqlServer, Oracle, MySQL, PostgreSQL, SQLite DBMS의 SQL을 사용하여 도메인 데이터를 외부에 노출합니다.
• Open API 서비스 설정 변동 시 무중단으로 실시간으로 반영됩니다.
• OWASP 10대 취약점에 대응하여 안전하게 운영이 가능합니다.

Open API 서비스를 관리하기 위해 테이블의 데이터를 추가하거나 변경해야 하는데 웹 기반의 UI는 향후 forbes로 제공됩니다. 데이터 엔티티 정보와 초기 데이터 SQL을 참고하여 자신만의 Open API를 관리 가능합니다.

openapi 모듈은 SqlServer, SQLite 데이터베이스에서 테스트 했으며 Oracle, MySQL, PostgreSQL에서 운영 동작은 확인이 필요 할 수 있습니다.

주요 기능

• API 서비스 관리
• 회원 정보 관리
• 서비스 키 발급
• 접근 권한 제어
• API 메타 정보 캐시 관리
• 로깅 및 거래 횟수 업데이트
• 거래 제한 조건 적용
• Json, Xml, Soap, Rss/Atom 출력 포맷 지원
• 필수 매개변수 및 기본 매개변수 적용 확인
• 공개, 비밀 키, IP 주소 접근 제어
• 기간 내 호출 수 제한 및 제한 조건 일별/월별 자동 갱신
• 응답 결과 캐싱
• 시간 단위 API 사용 통계 수집
• SqlServer, Oracle, MySQL, PostgreSQL, SQLite 데이터베이스 지원

appsettings.json LoadModules 설정에 openapi 추가

openapi 모듈을 활성화하기 위해 ack 서버 프로그램이 있는 디렉토리에 있는 appsettings.json 파일을 열고 LoadModules 설정에 openapi 추가합니다.

openapi 모듈의 기본 데이터베이스는 SQLite 이며 다른 데이터베이스를 실행할 경우 module.json의 DataSource.DataProvider, DataSource.ConnectionString 설정을 변경해야 합니다.

{
    "AppSettings": {
        ......
        "LoadModules": [
            "wwwroot",
            "transact",
            "dbclient",
            "function",
            "repository",
            "logger",
            "checkup",
            "openapi" <-- ack 서버 프로그램 실행시 로드할 모듈 추가
        ],
        ......
    }
}

데이터 엔티티 정보

다음의 데이터 엔티티 정보를 기반으로 SqlServer, Oracle, MySQL, PostgreSQL, SQLite 데이터베이스 테이블 스키마와 SQL이 개발되었습니다. 다음 링크의 "ZD01" 기능 ID의 DDL 쿼리를 참고하세요.

• SqlServer: SQS010.xml
• Oracle: ORA010.xml
• PostgreSQL: PGS010.xml
• MySQL: MYS010.xml
• SQLite: SLT010.xml

DataSource 데이터 소스

필드ID	필드명	필드타입	PK	IX	UI	NN	AI	길이	기본값	설명
DataSourceID	데이터소스ID	String	Y					10
DataProvider	데이터제공자	String						10
ConnectionString	연결문자열	String						2000
IsEncryption	연결문자열 암호화 여부	String						1
Comment	설명	String						1000
CreatedAt	입력일시	DateTime						50

APIService API 서비스

필드ID	필드명	필드타입	PK	IX	UI	NN	AI	길이	기본값	설명
APIServiceID	API 서비스 ID	String	Y					36		GUID
InterfaceID	인터페이스 ID	String			Y			50
InterfaceName	인터페이스명	String						100
DataSourceID	데이터 소스 ID	String						10
CommandText	명령 구문	String						65536
Descriptions	설명	String						2000		HTML 내용으로 API 서비스 설명
DefaultFormat	기본 데이터 포맷	String						10
FormatJsonYN	Json 지원 유무	String						1		Format을 지정하지 않으면 기본값 (Y)으로 적용
FormatXmlYN	Xml 지원 유무	String						1
FormatSoapYN	Soap 지원 유무	String						1
FormatRssYN	Rss 지원 유무	String						1
FormatAtomYN	Atom 지원 유무	String						1
LimitPeriod	제한 기간	String						10		Day, Month, Infinite
LimitCallCount	제한 요청 횟수	Int64						0
LimitIPAddressYN	제한 IP 주소 유무	String						1
AccessControl	접근 정책	String						10		Public, SecretKey
CumulativeCallCount	요청 횟수 합계	Int64						0
CacheDuration	캐시 지속 간격 (분)	Int32						0
UseYN	사용 유무	String						1
DeleteYN	삭제 유무	String						1
CreatedAt	입력 일시	DateTime						0

APIParameter API 매개변수

필드ID	필드명	필드타입	PK	IX	UI	NN	AI	길이	기본값	설명
APIServiceID	API 서비스 ID	String	Y					36
ParameterID	매개변수 ID	String	Y					50
ParameterType	매개변수 데이터 타입	String						30
Length	길이	Int32						50
DefaultValue	기본값	String						1000
RequiredYN	필수 유무	String						1
SortingNo	정렬순서	Int32						0
Comment	매개변수 설명	String						2000

Member 회원

필드ID	필드명	필드타입	PK	IX	UI	NN	AI	길이	기본값	설명
MemberNo	회원NO	String	Y					36
EmailID	이메일ID	String		Y		Y		256
EmailVerifyAt	이메일확인일시	DateTime						8
Celluar	핸드폰번호	String						20
CelluarVerifyAt	핸드폰확인일시	DateTime						8
MemberName	회원명	String						100
PositionName	직위명	String						100
DepartmentName	부서명	String						100
CompanyName	회사명	String						100
Roles	역할	String						200
BirthDate	생년월일	String						10
JoinAt	가입일시	DateTime						8
RetireAt	탈퇴일시	DateTime						8
Address	주소	String						510
AddressDetail	상세주소	String						100
Gender	성별	String						1
DeleteYN	삭제여부	String						1
Comment	설명	String						2000
TermsOfServiceConsentYN	서비스 이용약관	String						1
PersonalInformationUseConsentYN	개인정보 이용동의	String						1
ThirdPartyProvisionConsentYN	제3자 제공동의	String						1
CreatedUserNo	생성사용자NO	String						36
CreatedAt	생성일시	DateTime						8
ModifiedMemberNo	수정회원NO	String						36
ModifiedAt	수정일시	DateTime						8

AccessMemberAPI API 사용신청

필드ID	필드명	필드타입	PK	IX	UI	NN	AI	길이	기본값	설명
AccessID	API 접근 ID	String	Y					36
APIServiceID	API 서비스 ID	String		Y				36
MemberID	회원 ID	String		Y				36
SecretKey	접근 비밀키	String						36
IPAddress	요청 IP 주소	String						1000		LimitIPAddressYN == Y 인 경우 적용
LimitPeriod	제한 기간	String						10		Day, Month, Infinite
LimitCallCount	제한 횟수	Int64						0
RequestCallCount	요청 횟수	Int64						0	0
CumulativeCallCount	전체 요청 횟수	Int64						0
UseYN	사용 유무	String						1
DeleteYN	삭제 유무	String						1
CreatedAt	입력 일시	DateTime						0

UsageAPIAggregate API 사용통계

필드ID	필드명	필드타입	PK	IX	UI	NN	AI	길이	기본값	설명
RequestYear	요청 년도	Int32	Y					0
RequestMonth	요청 월	Int32	Y					0
RequestDay	요청 일	Int32	Y					0
RequestHour	요청 시간	Int32	Y					0
APIServiceID	API 서비스 ID	String	Y					36
AccessID	API 접근 ID	String	Y					36
Format	응답 데이터 형식	String						10		APIService Format중 Json, Xml, Soap, Rss, Atom 하나
CumulativeCallCount	전체 요청 횟수	Int64						0
ModifiedAt	최근 요청 일시	DateTime						0

초기 데이터 입력 (Seed 데이터 입력)

openapi 모듈을 로드하여 ack 프로그램을 실행하면 handstack/sqlite/HDS/openapi/managed.db 경로에 있는 SQLite 데이터베이스를 오픈하여 다음의 초기 데이터 입력을 참고하여 도메인 환경에 맞도록 데이터베이스 연결문자열과 설정 정보를 넣어줍니다.

데이터베이스 클라이언트 도구는 DB Browser for SQLite 또는 DBeaver 를 권장합니다.

INSERT INTO DataSource (DataSourceID,DataProvider,ConnectionString,IsEncryption,Comment,CreatedAt) VALUES
     ('DB01','SqlServer','Data Source=localhost;Initial Catalog=handstack;User ID=qcn;Password=Strong@Passw0rd;Pooling=true;Min Pool Size=60;Max Pool Size=180;','','Handsup','2024-03-25 00:00:00.000'),
     ('DB02','SqlServer','Data Source=localhost;Initial Catalog=fashion;User ID=qcn;Password=Strong@Passw0rd;Pooling=true;Min Pool Size=60;Max Pool Size=180;','','FashionSeoul','2024-03-25 00:00:00.000');

INSERT INTO APIService (APIServiceID,InterfaceID,InterfaceName,DataSourceID,CommandText,Descriptions,DefaultFormat,FormatJsonYN,FormatXmlYN,FormatSoapYN,FormatRssYN,FormatAtomYN,LimitPeriod,LimitCallCount,LimitIPAddressYN,AccessControl,CumulativeCallCount,CacheDuration,UseYN,DeleteYN,CreatedAt) VALUES
     ('061897e5cdb6488a9e8ca9e1d447bb1b','codes','기초코드 목록','DB01','SELECT BC.GroupCode
    , BC.CodeID
    , BC.CodeValue
    , BC.CategoryID
    , BC.Value1
    , BC.Value2
    , BC.Value3
    , BC.Value4
    , BC.Value5
    , BC.Comment
    , BC.SortingNo
    , BC.CreatedMemberNo
    , BC.CreatedAt
FROM 
    BaseCode BC
WHERE CASE WHEN @GroupCode = '''' THEN @GroupCode ELSE BC.GroupCode END = @GroupCode
    AND CASE WHEN @CategoryID = '''' THEN @CategoryID ELSE BC.CategoryID END = @CategoryID;','GroupCode, CategoryID 필터 조건','json','Y','Y','Y','Y','Y','Day',3000,'N','SecretKey',0,60,'Y','N','2024-03-25 00:00:00.000'),
     ('56051520bcbe404b93b88b5728699dfe','kaggle','패션 캐글 데이터 셋','DB02','SELECT id
    , gender
    , masterCategory
    , subCategory
    , articleType
    , baseColour
    , season
    , year
    , usage
    , productDisplayName
FROM KaggleDataSet KD
WHERE CASE WHEN @year = '''' THEN @year ELSE KD.year END = @year
    AND CASE WHEN @gender = '''' THEN @gender ELSE KD.gender END = @gender
    AND CASE WHEN @usage = '''' THEN @usage ELSE KD.usage END = @usage
ORDER BY KD.id
OFFSET (@rows * @offset) ROWS
FETCH NEXT @rows ROWS ONLY;','페이징 적용 조회','json','Y','Y','Y','Y','Y','Day',3000,'N','SecretKey',0,60,'Y','N','2024-03-25 00:00:00.000'),
     ('824a1b8629734b32b0fb101c984eed66','rss','Rss Feed 2.0','DB01','SELECT ''Title'' AS Title
    , ''Descriptio'' AS Description
    , ''Copyright'' AS Copyright
    , ''Generator'' AS Generator
    , ''ImageUrl'' AS ImageUrl
    , CONVERT(VARCHAR(19), GETDATE(), 121) AS LastUpdatedTime;

SELECT BC.GroupCode
    , BC.CodeID
    , CONCAT(BC.CodeID, '': '', BC.CodeValue) AS Title
    , ''https://handstack.kr,https://github.com/handstack77,'' AS Links
    , BC.Comment AS Sumamry
    , ''junchul@qcn.co.kr'' AS AuthorEmail
    , ''HandStack'' AS AuthorName
    , CONVERT(VARCHAR(19), BC.CreatedAt, 121) AS PublishDate
FROM 
    BasicCode BC;','Rss Feed 2.0','json','Y','Y','Y','Y','Y','Day',3000,'Y','SecretKey',0,60,'Y','N','2024-03-25 00:00:00.000');

INSERT INTO APIParameter (APIServiceID,ParameterID,ParameterType,"Length",DefaultValue,RequiredYN,SortingNo,Comment) VALUES
     ('061897e5cdb6488a9e8ca9e1d447bb1b','@CategoryID','String',30,'','',NULL,NULL),
     ('061897e5cdb6488a9e8ca9e1d447bb1b','@GroupCode','String',10,'SYS000','',NULL,NULL),
     ('56051520bcbe404b93b88b5728699dfe','@gender','String',10,'','',NULL,NULL),
     ('56051520bcbe404b93b88b5728699dfe','@offset','String',1000,'0','',NULL,NULL),
     ('56051520bcbe404b93b88b5728699dfe','@rows','String',1000,'300','Y',NULL,NULL),
     ('56051520bcbe404b93b88b5728699dfe','@usage','String',20,'','',NULL,NULL),
     ('56051520bcbe404b93b88b5728699dfe','@year','String',4,'','',NULL,NULL),
     ('635dfe49bd844cda9246f31f4cb84019','@GroupCode','String',10,'','',NULL,NULL);

INSERT INTO Member (MemberNo,EmailID,EmailVerifyAt,Celluar,CelluarVerifyAt,MemberName,PositionName,DepartmentName,CompanyName,Roles,BirthDate,JoinAt,RetireAt,Address,AddressDetail,Gender,DeleteYN,Comment,TermsOfServiceConsentYN,PersonalInformationUseConsentYN,ThirdPartyProvisionConsentYN,CreatedUserNo,CreatedAt,ModifiedMemberNo,ModifiedAt) VALUES
     ('08dc4c8f01e1efcbca91a89e5404cbb8','blabla@handstack.kr','2024-03-25 00:00:00.000','01000000000','2024-03-25 00:00:00.000','Blabla','부장','개발팀','HandStack','관리자','20240325','2024-03-25 00:00:00.000',NULL,'블라블라','라블라블','남','','블라블라','Y','Y','Y',NULL,'2024-03-25 00:00:00.000',NULL,NULL);

INSERT INTO AccessMemberAPI (AccessID,APIServiceID,MemberNo,SecretKey,IPAddress,LimitPeriod,LimitCallCount,RequestCallCount,CumulativeCallCount,UseYN,DeleteYN,CreatedAt) VALUES
     ('169910d971bb4c00bba04a71cfeaa870','56051520bcbe404b93b88b5728699dfe','08dc4c8f01e1efcbca91a89e5404cbb8','handstackblabla','1.1.14.10,127.0.0.1','Day',3000,0,0,'Y','N','2024-03-25 00:00:00.000'),
     ('c48972d403cf4c3485d2625a892d135d','061897e5cdb6488a9e8ca9e1d447bb1b','08dc4c8f01e1efcbca91a89e5404cbb8','handstackblabla','1.1.14.2','Day',3000,0,0,'Y','N','2024-03-25 00:00:00.000'),
     ('ca175d8158304ba99c9c20ec0845245d','824a1b8629734b32b0fb101c984eed66','08dc4c8f01e1efcbca91a89e5404cbb8','handstackblabla','1.1.14.3','Day',3000,0,0,'Y','N','2024-03-25 00:00:00.000');

데이터 설정 적용

데이터베이스의 테이블의 데이터를 추가 및 변경하여 Open API 서비스 설정 변동 시 적용하는 방법은 ack 서버 프로그램이 시작하면서 데이터베이스에 필요한 데이터를 조회 하기 때문에 간단하게 프로그램을 재시작 해도 됩니다.

하지만 운영중에 무중단으로 실시간으로 반영하기 위해 다음의 관리 목적의 URL을 호출하여 캐시된 설정을 업데이트 할 수 있습니다.

관리 목적의 URL을 호출할 때 AuthorizationKey 키 값은 필수 이며 module.json 의 AuthorizationKey 키 값을 추가하거나 빈 값으로 둘 경우 자동으로 appsettings.json 의 다음의 항목으로 조합하여 구성됩니다.

AuthorizationKey: HANDSTACKDHOSTNAME

AuthorizationKey == AppSettings.SystemID + AppSettings.RunningEnvironment + AppSettings.HostName

# 기초코드 목록 (061897e5cdb6488a9e8ca9e1d447bb1b) API 서비스의 캐시된 설정을 삭제
http://localhost:8000/openapi/api/managed/delete-api-service?apiServiceID=061897e5cdb6488a9e8ca9e1d447bb1b&AuthorizationKey=HANDSTACKDHOSTNAME

# 데이터 소스의 캐시된 설정을 삭제
http://localhost:8000/openapi/api/managed/delete-api-data-source?dataSourceID=DB01&AuthorizationKey=HANDSTACKDHOSTNAME

# 응답 결과의 캐시된 정보를 삭제
http://localhost:8000/openapi/api/managed/cache-clear?AuthorizationKey=HANDSTACKDHOSTNAME

Open API 사용

클라이언트에서는 APIService 테이블에 등록된 InterfaceID를 호출하여 데이터를 소비할 수 있습니다.

http://localhost:8000/openapi/{interfaceID}

• http://localhost:8000/openapi/codes?AccessID=c48972d403cf4c3485d2625a892d135d&SecretKey=handstackblabla&GroupCode=SYS000&CategoryID=&Format=json
• http://localhost:8000/openapi/codes?AccessID=c48972d403cf4c3485d2625a892d135d&SecretKey=handstackblabla&GroupCode=SYS000&CategoryID=&Format=xml
• http://localhost:8000/openapi/codes?AccessID=c48972d403cf4c3485d2625a892d135d&SecretKey=handstackblabla&GroupCode=SYS000&CategoryID=&Format=soap
• http://localhost:8000/openapi/codes?AccessID=c48972d403cf4c3485d2625a892d135d&SecretKey=handstackblabla&GroupCode=SYS000&CategoryID=&Format=rss
• http://localhost:8000/openapi/codes?AccessID=c48972d403cf4c3485d2625a892d135d&SecretKey=handstackblabla&GroupCode=SYS000&CategoryID=&Format=atom

응답 정보

API 응답의 Http Status 정보는 정상: 200, 요청 오류: 400, 응답 오류: 500 으로 구분되며, 오류가 발생할 경우 다음과 같이 메시지를 출력합니다.

• E10: APPLICATION_ERROR, 어플리케이션 오류
• E11: CONFIGURATION_ERROR, 설정 오류
• E12: SERVICE_EXECUTE_ERROR, 서비스 실행 오류
• E99: UNKNOWN_ERROR, 기타 오류
• I20: NO_OPENAPI_SERVICE_ERROR, 해당 오픈 API 서비스가 없거나 폐기됨
• I21: SERVICE_ACCESS_DENIED_ERROR, 서비스 접근 거부
• I22: LIMITED_NUMBER_OF_SERVICE_REQUESTS_EXCEEDS_ERROR, 서비스 요청 제한 횟수 초과
• I23: REQUEST_REQUIRED_ERROR, 서비스 요청 정보 확인 필요
• I24: SERVICE_DATASOURCE_ERROR, 서비스 데이터 소스 확인 필요
• I25: SERVICE_SETTING_ERROR, 서비스 설정 정보 확인 필요
• I31: DEADLINE_HAS_EXPIRED_ERROR, 활용기간 만료
• I40: HTTP_ERROR, HTTP 에러
• I41: UNREGISTERED_SECRET_KEY_ERROR, 등록되지 않은 서비스 키
• I42: UNREGISTERED_IP_ERROR, 등록되지 않은 IP