2021年1月22日金曜日

リモート・データへのアクセス (3) - RESTデータ・ソース

こちらの記事の継続になります。リモートのデータベースに存在する表EMPを扱うRESTデータ・ソースを登録し、それをソースとして使用する対話グリッドのページを作成します。

オブジェクトのRESTの有効化 - Oracle APEXによる作業

アクセス先の表EMPのREST APIによる操作を有効化します。SQL Developer Webから実施したいのですが、AutoRESTの設定はOracle REST Data Services 20.4.1から提供が開始される予定で、今はまだ無いため、Oracle APEXを使って行います。

ワークスペースにサインインし、SQLワークショップのオブジェクト・ブラウザを開きます。オブジェクト・ブラウザより表EMPを選択し、RESTのタブを開きます。

オブジェクトのRESTの有効化をはい、認可が必要もはいに設定します。オブジェクトの別名はempにしています。セキュリティ面を考慮して、表の名前と異なる文字列を指定する場合もあります。以上の設定を行い、適用をクリックします。

オブジェクトのREST APIによる操作が有効化されました。

RESTful URIが定義されています。このRESTful URIにアクセスするには、OAuthクライアントに、ここで作成された認可ロールを、ロールとして与える必要があります。RESTful URIがオブジェクトを操作するREST APIのエンドポイントです。Oracle APEXのアプリケーションにRESTデータ・ソースを登録する際に使用するので、どこかに記録しておきましょう。

続いて、SQL Developer Webにデータベース・ユーザーAPEXDEVにてサインインします。

RESTのページを開いてROLESのタイルをクリックし、登録された認可ロールを確認します。


Oracle APEX側でオブジェクトのREST操作を有効にすると、認可ロールの所有者がORDS_PUBLIC_USERになります。


そのためGUIでは認可ロールをOAuthクライアントに割り当てることができません。プロシージャOAUTH.GRANT_CLIENT_ROLEを実行することにより、認可ロールの割り当てを行います。ロール名oracle.dbtools.role.autorest.APEXDEV.EMPをクリップボードにコピーしておきましょう。

実行するプロシージャは以下になります。
begin
    oauth.grant_client_role(
        p_client_name => 'apexdev',
        p_role_name => 'oracle.dbtools.role.autorest.APEXDEV.EMP'
    );
end;


以上で表EMPを、REST API経由で操作できるようになりました。

オブジェクトのRESTの有効化 - SQL Developer Webによる作業

Oracle APEXではなくSQL Developer Webから作業を行う場合は、UIが提供されていないため、Oracle REST Data Servicesが提供しているORDS.ENABLE_OBJECTプロシージャを呼び出すことで、オブジェクトのREST APIによる操作を有効にします。

SQL Developer Webにデータベース・ユーザーAPEXDEVにてサインインし、以下のプロシージャを実行します。

begin
    ORDS.ENABLE_OBJECT(
        p_enabled => TRUE,
        p_schema => 'APEXDEV',
        p_object => 'EMP',
        p_object_type => 'TABLE',
        p_object_alias => 'emp',
        p_auto_rest_auth => TRUE
    );
end;

続いてRESTのページを開いて、CLIENTSのタイルをクリックします。

OAuthクライアントapexdevの編集を実行します。

ロールのタブを開き、認可ロールoracle.dbtools.role.autorest.APEXDEV.EMPをクライアントapexdevに割り当て、保存をクリックします。


以上で、APEXから設定したのと同様に、表EMPをREST API経由で操作できるようになりました。


RESTデータ・ソースの登録


これからRESTデータ・ソースの設定を行います。以前の呼び名はWebソース・モジュールでした。

共有コンポーネントのデータ・ソースに含まれるRESTデータ・ソースを開きます。


作成をクリックします。


RESTデータ・ソースの作成は、最初から行います。


RESTデータ・ソース・タイプとしてOracle REST Data Servicesを選択します。名前は任意です。ここではEMP in Ashburnとしています。URLエンドポイントは、REST対応ユーザーを有効にしたときに決められたエンドポイントURLにオブジェクト別名を加えたものです。

https://Autonomous Databaseのホスト.oraclecloudapps.com/ords/スキーマ別名/オブジェクト別名/

今回のオブジェクト別名はempとしているので、ホスト名を除いたパスは /ords/apexdev/emp/ になります。以上を設定して、次に進みます。


リモート・サーバー、ベースURL、サービスURLパスは、先に設定したURLエンドポイントから導出されます。確認だけで、そのまま次へ進みます。


認証が必要ですをONにします。資格証明はすでに作成済みの資格証明を設定します。今回の作業ではAPEXDEV at Ashburnになります。OAuthトークンURLは自動的に決まります。検出をクリックします。


取得したデータのプレビューが表示されます。プレビューが表示されれば、Web資格証明の設定やエンドポイントURLの構成に間違いはありません。内容を確認して、RESTデータソースの作成をクリックします。


作成したRESTデータ・ソースが一覧に表示されます。


以上で、RESTデータ・ソースが作成されました。

RESTデータ・ソースによる対話グリッドの作成


RESTデータ・ソースをソースとする対話グリッドのページを作成します。作成手順は一つ前のREST対応SQLをソースとする場合とほぼ同じですので、違う部分だけを記載します。

ページの名前は任意ですが、今回はRESTデータ・ソースとします。

ページ作成ウィザードを実行し、ページの名前やナビゲーション・メニューの指定を行います。最後にデータ・ソースを指定するページが開きます。

データ・ソースとしてRESTデータ・ソースを選択します。編集が有効はONとし、RESTデータ・ソースとして、作成済みのRESTデータ・ソース、今回の例ではEMP in Ashburnを選択します。主キー列が選択できるようになるので、EMPNO (Number)を選択し、作成をクリックします。


対話グリッドのページが作成されます。作成された対話グリッドのリージョンのソースを確認します。外部フィルタ、外部並替え基準、ローカル後処理の設定が追加されています。それぞれの機能については、オンライン・ヘルプを参照してください。

作成されたページを実行し、動作を確認します。


検索および行の挿入/更新/削除を行ってみましょう。操作感には大きな違いはありません。


RESTデータ・ソースの作成と、それを使用した対話グリッドの作成については以上で完了です。

次の記事が最後になります。RESTデータ・ソースの同期化を実装してみます。

2021年1月21日木曜日

リモート・データへのアクセス (2) - REST対応SQL

こちらの記事からの継続です。リモートのAutonomous Databaseに、REST対応SQLによってアクセスするOracle APEXのアプリケーションを作成します。

アプリケーション名をリモート・アクセスとした、空っぽのアプリケーションを作成しておきます。アプリケーション・ビルダーの作成から、新規アプリケーションの作成を実行し、名前だけを設定し、それ以外は設定を行わずにアプリケーションの作成を実行します。

この状態から作業の説明を始めます。

Web資格証明の登録


先の記事で作成したOAuthクライアントを、Web資格証明として登録します。作成したアプリケーションの共有コンポーネントを開きます。


セキュリティに含まれるWeb資格証明を開きます。


作成をクリックして、Web資格証明の登録を行います。


今回はアプリケーションの共有コンポーネントからWeb資格証明のページを開きました。実際には、Web資格証明はワークスペース・ユーティリティに含まれています。そのため、ここで作成したWeb資格証明は、同じワークスペースの他のアプリケーションからも利用することができます。

Web資格証明として、名前を設定し(デフォルトでは静的識別子が名前より導出されるため、英数字の利用に限定してください)、認証タイプとしてOAuth2クライアント資格証明フローを選択します。クライアントIDまたはユーザー名、クライアント・シークレットまたはパスワード(と確認)、URLに対して有効については、前回の記事で作成したOAuthクライアントの目のアイコンをクリックして表示される情報と、Autonomous DatabaseのURL(OAuthクライアントはスキーマAPEXDEVに登録されているので、URLの末尾はスキーマ別名まで含めます)を指定します。

それぞれ設定し、作成をクリックします。


Web資格証明が登録されたことを確認します。


REST対応SQLの登録


アプリケーションの共有コンポーネントに戻り、データ・ソースに含まれるREST対応SQLを開きます。


REST対応SQLの定義も実際にはワークスペース・ユーティリティに含まれているので、ここでの定義は同じワークスペースに作成されているアプリケーションから利用できます。

作成をクリックします。


名前とエンドポイントURLを指定します。名前は任意です。エンドポイントURLは今回はアクセス先のAutonomous DatabaseのURLです。URLの末尾は、スキーマAPEXDEVをREST対応ユーザーとして有効化する際に指定したスキーマ別名になります。以下の形式になります。

https://Autonomous Databaseのホスト.oraclecloudapps.com/ords/スキーマ別名/

Autonomous Database以外でも、末尾はスキーマ別名になります。

次に進みます。


資格証明として先ほど登録したWeb資格証明を選択します。今回の例ではAPEXDEV at AshburnとしてWeb資格証明を登録しているので、それを選択しています。その後、作成をクリックします。


REST対応SQLが作成され、接続テストも続いて行われます。エラーが発生せず、REST対応SQLサービスが作成されました。と表示されれば成功です。ウィンドウを閉じます。


REST対応SQLが新たに登録されていることを確認します。テストのボタンがありますが、これは上記のテストの再実行になるので、繰り返し行う必要はありません。



REST対応SQLによる対話グリッドの作成


アプリケーションに対話グリッドのページを作成します。アプリケーション・ビルダーを開いて、ページの作成をクリックし、ページ作成ウィザードを開始します。


コンポーネントのフォームをクリックします。


編集可能対話グリッドをクリックします。


ページ名をREST対応SQLとします。次に進みます。


ナビゲーションのプリファレンスとして、新規ナビゲーション・メニュー・エントリの作成を選択します。新規ナビゲーション・メニュー・エントリとして、ページ名であるREST対応SQLがデフォルトになります。そのまま変更せず、次へ進みます。


データ・ソースの設定を行います。REST対応SQLサービスを選択します。REST対応SQLサービスとして、登録済みのサービスを選択します。今回はAPEXDEV on Ashburnです。ソース・タイプとして表を選択し、表/ビューの名前としてEMPを選択します。主キー列にEMPNO (Number)を選択し、作成を実行します。

主キー列はウィンドウからはみ出ている場合があるので、ウィンドウを拡大するかスクロールするかして表示させてから、設定します。デフォルトはROWIDなので、レポートの表示列が変わってしまいます。


ページが作成されるとページ・デザイナが開きます。作成されたリージョンのソースの定義を確認します。REST対応SQLがソースのときは、リモート・キャッシュの機能が追加されます。リモート・キャッシュについては、オンライン・ヘルプを参照してください。

作成されたページを実行し、動作を確認します。


検索および行の挿入/更新/削除を行ってみましょう。


REST対応SQLを使ったリモート・データへのアクセスについては以上で完了です。

次の記事ではRESTデータ・ソースを定義し、データベース間でのデータ同期を実装してみます。

リモート・データへのアクセス (1) - アクセス先の準備

 AshburnにあるAutonomous Databaseのインスタンスに東京のAutonomous Databaseからアクセスする必要があったので、REST APIを使ってデータの同期をとるように構成してみました。その作業ログです。

両方ともAlways FreeのAutonomous Transaction Processingですが、AshburnのものはDBのバージョンが21c、東京は19cです。両方ともにAPEXのワークスペースとしてAPEXDEVを構成しています。

色々な方法はあるのですが、できるだけGUIを使うように試みています。

Ashburn側のスキーマAPEXDEVのREST API有効化


SQL Developer WebにADMINでサインインし、管理に含まれるデータベース・ユーザーを開きます。


データベース・ユーザーの一覧より、Oracle APEXのワークスペースに紐づいているスキーマ、今回はAPEXDEVですが、それのRESTの有効化を開きます。


承認が必要をONにし、REST対応ユーザーをクリックします。


これでスキーマAPEXDEVのREST操作が有効化されます。

URLマッピング・タイプはBASE_PATHがデフォルトで、通常は変更の必要はありません。詳しくはマニュアルのこちらに説明があります。スキーマ別名の指定よってURLに現れる文字列を変更することができます。安全性を考慮すると、実際のスキーマとは異なる名前にすることが推奨です。今回は検証なので、変更は小文字apexdevにしているだけです。

データベース・ユーザーAPEXDEVに上記の変更を行うことで、APEXDEVにてSQL Developer Webにサインインできるようになります。逆に言うと、この変更を行わないと、どのようなデータベース・ユーザーでもSQL Developer Webにサインインできません。

OAuthクライアントの作成


ADMINからサインアウトし、APEXDEVでSQL Developer Webにサインインし直します。トップ・ページよりRESTを開きます。


概要のページより、ROLESのタイルをクリックします。本来必要ないのですが、SQL Developer WebからOAuthクライアントを作成する際に必ずロールのアサインが必要なので、ダミーのロールを作成します。


ロールの作成をクリックします。


ロール名をとりあえずDummyとして、ロールを作成します。


Dummyという名前のロールが作成されていることを確認します。左上のREST、もしくはメニューから概要をクリックし、概要のページに戻ります。


CLIENTSのタイルをクリックします。


OAuthクライアントの作成をクリックします。


クライアント定義として、名前を設定します。ここではapexdevとしています。それ以外の説明、サポートURI、サポート電子メールは必須項目になっていますが、OAuthの動作に影響がある設定ではないので、適当と思われれる値を設定します。(クライアントを作成するAPIのOAUTH.CREATE_CLIENTでは、これらの値の指定は任意です)。


ロールのタブをクリックして開き、先ほど作成したDummyを選択します。その後、作成をクリックします。


OAuthクライアントが作成されたことを確認します。作成されたOAuthクライアントapexdevのクライアントID、および、クライアント・シークレットは目のアイコンをクリックすることで表示できます。



SQL Developerロールの割り当て


REST対応SQLを利用可能にするため、SQL DeveloperロールをOAuthクライアントapexdevに割り当てます。この作業は今のところ、GUIから行うことができません。

サイド・メニューを開き、SQLを実行するページを開きます。


以下のコードを実行し、OAuthクライアントapexdevにロールSQL Developerを割り当てます。
begin
    oauth.grant_client_role(
        p_client_name => 'apexdev',
        p_role_name => 'SQL Developer'
    );
end;

スクリプトの実行アイコンをクリックし、上記のコードを実行します。


スクリプト出力にPL/SQL procedure successfully completedと表示され、プロシージャの実行が成功していることを確認します。

以上でREST対応SQLが利用できるようになりました。

動作確認


REST対応SQLを実行し、動作の確認を行います。OAuthクライアントのページを開き、OAuthクライアントapexdevのハンバーガー・メニューを開き、Bearerトークンの取得を実行します。


新規トークンの取得をクリックし、現在のトークンを更新します。現在のトークンが有効期限内であり、権限などに変更がなければ、そのまま利用することもできます。現在のトークンをクリップボードなどにコピーしておきます。


または、端末のトークンを取得するには、次のコードを使用します。として記載されているcurlコマンドを実行することで、コマンドラインよりトークンを取得することも可能です。どちらかの情報をコピーし、OKをクリックしてダイアログを閉じます。

Autonomous DatabaseへアクセスするURLを確認し、以下のcurlコマンドを実行します。
curl -i -X POST -H"Authorization: Bearer 現在のトークンの値" --data-binary "select * from emp" -H"Content-Type: application/sql" -k https://ADBのURL.oraclecloudapps.com/ords/apexdev/_/sql

select * from empの結果が正しく返ってきたら、確認完了です。スキーマAPEXDEVに表EMPを用意していないときは、select sysdate from dualを代わりに実行してもよいでしょう。その場合の実行結果は以下のようになります。

HTTP/1.1 200 OK

Date: Thu, 21 Jan 2021 06:13:44 GMT

Content-Type: application/json

Transfer-Encoding: chunked

Connection: keep-alive

X-Frame-Options: SAMEORIGIN


{"env":{"defaultTimeZone":"UTC"},"items":[{"statementId":1,"statementType":"query","statementPos":{"startLine":1,"endLine":2},"statementText":"select sysdate from dual","resultSet":{"metadata":[{"columnName":"SYSDATE","jsonColumnName":"sysdate","columnTypeName":"DATE","precision":0,"scale":0,"isNullable":1}],"items":[{"sysdate":"2021-01-21T06:13:44Z"}],"hasMore":false,"limit":10000,"offset":0,"count":1},"response":[],"result":0}]}

ソース側のデータベースの設定ができたので、次からは、それにアクセスするOracle APEXのアプリケーションを定義します。

続く