본문으로 건너뛰기

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 쿼리를 참고하세요.

DataSource 데이터 소스

필드ID필드명필드타입PKIXUINNAI길이기본값설명
DataSourceID데이터소스IDStringY10
DataProvider데이터제공자String10
ConnectionString연결문자열String2000
IsEncryption연결문자열 암호화 여부String1
Comment설명String1000
CreatedAt입력일시DateTime50

APIService API 서비스

필드ID필드명필드타입PKIXUINNAI길이기본값설명
APIServiceIDAPI 서비스 IDStringY36GUID
InterfaceID인터페이스 IDStringY50
InterfaceName인터페이스명String100
DataSourceID데이터 소스 IDString10
CommandText명령 구문String65536
Descriptions설명String2000HTML 내용으로 API 서비스 설명
DefaultFormat기본 데이터 포맷String10
FormatJsonYNJson 지원 유무String1Format을 지정하지 않으면 기본값 (Y)으로 적용
FormatXmlYNXml 지원 유무String1
FormatSoapYNSoap 지원 유무String1
FormatRssYNRss 지원 유무String1
FormatAtomYNAtom 지원 유무String1
LimitPeriod제한 기간String10Day, Month, Infinite
LimitCallCount제한 요청 횟수Int640
LimitIPAddressYN제한 IP 주소 유무String1
AccessControl접근 정책String10Public, SecretKey
CumulativeCallCount요청 횟수 합계Int640
CacheDuration캐시 지속 간격 (분)Int320
UseYN사용 유무String1
DeleteYN삭제 유무String1
CreatedAt입력 일시DateTime0

APIParameter API 매개변수

필드ID필드명필드타입PKIXUINNAI길이기본값설명
APIServiceIDAPI 서비스 IDStringY36
ParameterID매개변수 IDStringY50
ParameterType매개변수 데이터 타입String30
Length길이Int3250
DefaultValue기본값String1000
RequiredYN필수 유무String1
SortingNo정렬순서Int320
Comment매개변수 설명String2000

Member 회원

필드ID필드명필드타입PKIXUINNAI길이기본값설명
MemberNo회원NOStringY36
EmailID이메일IDStringYY256
EmailVerifyAt이메일확인일시DateTime8
Celluar핸드폰번호String20
CelluarVerifyAt핸드폰확인일시DateTime8
MemberName회원명String100
PositionName직위명String100
DepartmentName부서명String100
CompanyName회사명String100
Roles역할String200
BirthDate생년월일String10
JoinAt가입일시DateTime8
RetireAt탈퇴일시DateTime8
Address주소String510
AddressDetail상세주소String100
Gender성별String1
DeleteYN삭제여부String1
Comment설명String2000
TermsOfServiceConsentYN서비스 이용약관String1
PersonalInformationUseConsentYN개인정보 이용동의String1
ThirdPartyProvisionConsentYN제3자 제공동의String1
CreatedUserNo생성사용자NOString36
CreatedAt생성일시DateTime8
ModifiedMemberNo수정회원NOString36
ModifiedAt수정일시DateTime8

AccessMemberAPI API 사용신청

필드ID필드명필드타입PKIXUINNAI길이기본값설명
AccessIDAPI 접근 IDStringY36
APIServiceIDAPI 서비스 IDStringY36
MemberID회원 IDStringY36
SecretKey접근 비밀키String36
IPAddress요청 IP 주소String1000LimitIPAddressYN == Y 인 경우 적용
LimitPeriod제한 기간String10Day, Month, Infinite
LimitCallCount제한 횟수Int640
RequestCallCount요청 횟수Int6400
CumulativeCallCount전체 요청 횟수Int640
UseYN사용 유무String1
DeleteYN삭제 유무String1
CreatedAt입력 일시DateTime0

UsageAPIAggregate API 사용통계

필드ID필드명필드타입PKIXUINNAI길이기본값설명
RequestYear요청 년도Int32Y0
RequestMonth요청 월Int32Y0
RequestDay요청 일Int32Y0
RequestHour요청 시간Int32Y0
APIServiceIDAPI 서비스 IDStringY36
AccessIDAPI 접근 IDStringY36
Format응답 데이터 형식String10APIService Format중 Json, Xml, Soap, Rss, Atom 하나
CumulativeCallCount전체 요청 횟수Int640
ModifiedAt최근 요청 일시DateTime0

초기 데이터 입력 (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}

응답 정보

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