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:8421/openapi/api/managed/delete-api-service?apiServiceID=061897e5cdb6488a9e8ca9e1d447bb1b&AuthorizationKey=HANDSTACKDHOSTNAME
# 데이터 소스의 캐시된 설정을 삭제
http://localhost:8421/openapi/api/managed/delete-api-data-source?dataSourceID=DB01&AuthorizationKey=HANDSTACKDHOSTNAME
# 응답 결과의 캐시된 정보를 삭제
http://localhost:8421/openapi/api/managed/cache-clear?AuthorizationKey=HANDSTACKDHOSTNAME
Open API 사용
클라이언트에서는 APIService 테이블에 등록된 InterfaceID를 호출하여 데이터를 소비할 수 있습니다.
- http://localhost:8421/openapi/codes?AccessID=c48972d403cf4c3485d2625a892d135d&SecretKey=handstackblabla&GroupCode=SYS000&CategoryID=&Format=json
- http://localhost:8421/openapi/codes?AccessID=c48972d403cf4c3485d2625a892d135d&SecretKey=handstackblabla&GroupCode=SYS000&CategoryID=&Format=xml
- http://localhost:8421/openapi/codes?AccessID=c48972d403cf4c3485d2625a892d135d&SecretKey=handstackblabla&GroupCode=SYS000&CategoryID=&Format=soap
- http://localhost:8421/openapi/codes?AccessID=c48972d403cf4c3485d2625a892d135d&SecretKey=handstackblabla&GroupCode=SYS000&CategoryID=&Format=rss
- http://localhost:8421/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