【OCI】APEXのREST API機能を使ってAutonomous Databaseにデータを登録してみる

# はじめに

今回はOracle Cloud Infrastructure（OCI）のAutonomous Databaseの
APEX機能のRESTful Servicesを使って、テーブルにAPIでデータを簡単に
登録する方法をご紹介したいと思います。

■参考
https://docs.oracle.com/ja-jp/iaas/autonomous-database/doc/create-applications-ords-autonomous-database.html#GUID-E2E921FF-2D80-4E32-9660-28506D10BADB

作成したADB（Autonomous Database）にデフォルトで付属している
APEXの機能を使ってREST APIを公開し、ローカル環境のPostmanから
json形式のデータをPostしてテーブルにデータを登録します。

■構成
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/66511b5f-ced5-73a7-7e07-62b381049f71.png)

■事前準備
・アカウント
ADBが使用できるOCIアカウントを準備

・ADBの作成
今回作成するADBは外部からアクセスできるようするために
「すべての場所からのセキュア・アクセス」で作成してください。

因みにADBのワークロードタイプはトランザクション系の処理が得意な
ATP（Autonomous Transaction Processing）を使用します。
※もちろん、ADW（Autonomous Data Warehouse）でも構いません。

ADBの作成方法についてはこちらを参考にします。
https://oracle-japan.github.io/ocitutorials/database/adb101-provisioning/

◆目次
1.事前データ登録
2.APEXワークスペースの作成
3.RESTful APIの設定
4.APIのアクセステスト

## 1.事前データ登録
APEXでAPIを公開する前に予めユーザ作成やデータの登録をしておきます。

作業の流れは以下の通りです。
①ユーザ登録・権限付与
②テーブルの作成
③データ登録

左上メニュー[Ξ]からOracle Database → Autonomous Transaction Processing
を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/ddda4f4d-716b-d9f9-c2c9-1fca4d0475a7.png)

予め作成しておいたATPインスタンス名を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/46a90fde-48b1-0c00-96c7-2a9342f7bca1.png)

データベース・アクションボタンの中の
「すべてのデータベース・アクションを表示」を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/0bc619ce-9a30-51ee-45c9-43c598c91d7b.png)

次のようなメニューが表示されるので「SQL」を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/6675bce3-4d60-bcea-bcc1-0b1999f1af7e.png)

画面が遷移するとワークシートが表示されるので以下の処理を進めていきます。
①ユーザ登録・権限付与

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/d11af15d-7323-c431-3653-bf44d16400f3.png)

[ユーザ作成・権限付与SQL]
ユーザ名: testuser001
パスワード: 任意の文字列
```
CREATE USER testuser001 IDENTIFIED BY "<パスワード>";
GRANT CREATE SESSION TO testuser001;
GRANT CREATE TABLE TO testuser001;
GRANT UNLIMITED TABLESPACE TO testuser001;
GRANT EXECUTE ON DBMS_CRYPTO to testuser001;
```
※DBMS_CRYPTOはデータをハッシュ値にするためのパッケージで
後続の処理で使うため権限付与しておきます。

SQLの入力が終わったら全てのSQL文を範囲選択し、
実行ボタン（緑の矢印）をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/39cbe99b-d951-3788-7398-d0b1cf0286a4.png)

データが正常に登録できたら次は以下の処理を進めます。
②テーブルの作成

API登録用テーブルと認証用テーブルを作成するため、
２つ同時にCreate文を入力します。
入力が終わったら範囲選択をし、実行ボタンをクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/5b5b11ce-9b62-8b0d-8390-d55082b3c37b.png)

[テーブル作成SQL]
```
--API登録用テーブル
CREATE TABLE testuser001.api_data (
    id NUMBER(10) primary key,
    name VARCHAR2(30)
);

--認証用テーブル
CREATE TABLE testuser001.api_users (
    username VARCHAR2(100) primary key,
    token VARCHAR2(4000)
);
```

最後に③データ登録としてAPI認証で使うための
認証データを登録します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/1489feab-3261-0219-d54d-790d159ef008.png)

[データ登録SQL]
```
DECLARE
    l_token_raw VARCHAR2(4000) := 'ABCDE123';
    l_token_hash RAW(2000);
BEGIN
    l_token_hash := DBMS_CRYPTO.Hash(UTL_I18N.STRING_TO_RAW(l_token_raw, 'AL32UTF8'), DBMS_CRYPTO.HASH_SH256);
    INSERT INTO testuser001.api_users (username, token) VALUES ('u00001', l_token_hash);
    commit;
END;
```
今までと同様に上記SQLを範囲選択して、実行ボタンをクリックします。

＜ポイント＞
APIからADBにデータを登録する際に認証をかけるのですが、
パスワードを平文のままテーブルに格納するのはセキュリティ上よろしくないので
ここではハッシュ化したデータとして登録しています。
※前段で権限付与したDBMS_CRYPTOパッケージを使ってハッシュ化しています。

username = u00001
token = ABCDE123
というレコードを登録しておき、認証として使います。

以上で事前準備は終了です。

## 2.APEXワークスペースの作成

ここでは前段の処理で作成したtestuser001用のワークスペースを作成します。

データベース・アクションボタンの中の
「すべてのデータベース・アクションを表示」を選択し、
画面遷移後に「APEX」を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/40873442-4362-c7b7-bf77-f4c80f4dea5b.png)

APEXの認証画面が表示されるので、ATP作成時に設定した
adminのパスワードを入力してサインインします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/a1cc3d03-c413-7534-27ab-23f946cf32b0.png)

サインイン後に次のような画面となるので、
「Create Workspace」をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/a3762277-faf7-161e-cd76-8eed4082f98f.png)
※上部のManage WorkspacesからでもCreate Workspaceを実行できます。

この画面では「Existing Schema」を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/a1523b6f-bc0b-7b3e-b091-087b6a486a04.png)

次の画面ではユーザ等を入力する画面となっているため
任意の文字列を入力します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/1fe92f25-c0ab-98a2-5330-62eb38e3ef17.png)

ここでは以下のように入力しています。
Database User: TESTUSER001
Workspace Name: TESTUSER001
Workspace UserName: TESTUSER001
Workspace Password: <任意の文字列>

次のような画面になれば正常に処理は終了です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/c0f83385-8b41-b8ba-8aae-eac04946ceb3.png)

以上でAPEXワークスペースの作成は終了です。

## 3.RESTful APIの設定

続いてRESTful APIの設定に進みますので、再度APEXに入り直します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/9fb46e2c-ee43-8ba7-eb8d-59de4d4e1687.png)

APEXを起動するとサインイン画面が前回とは少し変わっていますので、
そのままワークスペースにサインインします。
（ワークスペースができるとデフォルトがワークスペース用サインインとなります）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/7e8528ea-18dc-16bd-2c61-a52ac60ca749.png)
※adminでサインインする場合は最下部のAdministration Servicesを選択します。

サインインは前段で登録した以下の情報です。
Workspace: TESTUSER001
Database UserName: TESTUSER001
Workspace Password: <任意の文字列>

サインインすると次の画面となるため、「SQL Workshop」を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/4951f3eb-9e69-2507-282e-ce184a45c7c3.png)

次に「RESTful Services」を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/f5ee03eb-c028-ec56-77ff-88c4d1ede308.png)

続いてORDSの有効化をします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/b371a09b-8e0f-6ab7-e0ca-4740cefec9f9.png)

デフォルトのまま「Save Schema Attributes」を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/e16c950e-e7ac-e481-da72-2b8574008665.png)

これで初期設定ができました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/f9f7f5be-f74d-1d23-61b7-6a5633102767.png)

次は「Modules」→「Create Module」を選択します。 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/46a252b1-093f-e7b8-fc75-45b48b33aedf.png)

「Module Name」と「Base Path」を入力して「Create Module」を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/feaf7d3d-39cb-31e1-bd8d-c68b0041e133.png)

ここでは以下のようにしています。
Module Name = md_api_insert
Base Path = md_api_insert

Moduleが作成されると次のようになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/f13743c3-7622-d862-cc07-7f12a47482e7.png)

そのまま下にスクロールすると「Create Template」が表示されているので
それを選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/e2c8a2ee-f805-30a7-be82-e5dfb0b4f19a.png)

「URI Template」を入力して、「Create Template」を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/23ff9de8-9401-68ee-49ec-f32ae307ba49.png)

ここでは以下を設定しています。
URI Template = api_data

Templateが作成されると次のようになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/2d4a6be6-f483-1dbb-c374-c5ed5599525f.png)

続いて下にスクロールすると「Create Handler」が表示されるので
それを選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/88d69288-0ea1-7558-fea4-600f1730deb7.png)

HandlerではMethodでPOSTを選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/068cd83c-541d-f60b-2867-b59a19ccc1a0.png)

下にスクロールするとSource入力欄が表示されるので
ここにコード・SQLを入力します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/925e65cc-084f-72ac-1540-d283035456e3.png)

```
DECLARE
    l_authorization VARCHAR2(4000);
    l_token_raw VARCHAR2(4000);
    l_token_hash RAW(2000);
    l_user_exists NUMBER;
BEGIN
    -- HTTPヘッダーから認証情報を取得
    l_authorization := owa_util.get_cgi_env('AUTHORIZATION');

    -- Bearer認証情報を取得（形式：Bearer <Token>）
    IF l_authorization IS NOT NULL AND l_authorization LIKE 'Bearer %' THEN
        l_token_raw := substr(l_authorization, 8);

        -- トークンをハッシュ化
        l_token_hash := DBMS_CRYPTO.Hash(UTL_I18N.STRING_TO_RAW(l_token_raw, 'AL32UTF8'), DBMS_CRYPTO.HASH_SH256);

        -- データベースでハッシュ値を検証
        SELECT COUNT(*) INTO l_user_exists FROM api_users WHERE token = l_token_hash;
        IF l_user_exists = 0 THEN
            -- 認証失敗
            htp.print('{"status": "failure", "message": "Unauthorized"}');
            RETURN;
        END IF;
    ELSE
        -- 認証情報がない場合はエラー
        htp.print('{"status": "failure", "message": "No Authorization header"}');
        RETURN;
    END IF;

    -- データの挿入を試みる
    BEGIN
        INSERT INTO api_data (id, name) VALUES (:id, :name);
        -- 挿入成功
        htp.print('{"status": "success", "message": "Data inserted successfully"}');
    EXCEPTION
        WHEN OTHERS THEN
            -- 挿入失敗
            htp.print('{"status": "failure", "message": "Data insertion failed"}');
    END;
END;
```

入力が終わったら上部の「Create Handler」を実行します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/bfd53be9-de0f-bdbc-4df7-0efbcabc15c6.png)

正常に登録が完了すると次のような画面となります。
Full URLはAPIのエンドポイントとなるため保存しておきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/c7989ce5-512f-4dce-f7be-94f70fb26ce7.png)

以上でRESTful APIの設定は終了です。

## 4.APIのアクセステスト
最後にAPEX RESTful APIのアクセステストを実施してみます。

Postmanを起動したら以下情報を入力します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/cff086f4-06ec-1e9a-36db-841056e32a5c.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/a84b230a-a5f5-a561-ec11-cee486db0616.png)


[Method] POST
[Endpoint] APEX Restful APIで設定したFull URL
[Header]
  Content-Type: application/json
  Authorization: Bearer ABCDE123
[Body]
{
　　"id": 1,
　　"name": "APIテスト"
}

データ登録が正常に処理されると以下のような
メッセージが返ってきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/7f85536b-4e52-4ac6-abd9-17ab98c1517f.png)

SQLでも登録されていることが確認できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/9bd341c1-277f-6553-f14b-ae3c84ed84b3.png)

因みにわざと認証を間違えるとUnauthorizedとなるようになっています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3493138/f41b40c8-7685-eeae-c91c-15d986304958.png)

## おわり
以上、ADBに付属しているAPEXのRESTful APIの機能を使って
簡単にデータを登録することができました。