Oracle APEXによるRadio オンエアリストの検索アプリ作成

#はじめに
Oracle Cloud Infrastructure(OCI)のFree Tierには、Autonomous Database、Oracle Application Express(APEX)も含まています。常時無償、無期限で最新のデータベースとアプリケーション作成環境が使えます。これは楽しい。早速、データを放り込んで遊んでみました。

#Power Rock Today オンエアリストのスクレイピング
毎週土曜日、BAYFMで放送しているPower Rock Todayという、伊藤政則氏がDJのハードロック/へヴィメタルの番組があります。この番組でオンエアされた曲を有志で掲載してくれているサイトがあります。

PRTオンエアリスト ver.2

これをpythonのBeautifulSoupを使ってスクレイピング、CSVファイルに出力しました。スクリプトは以下にあります。

2004年から80ページを超えるアーカイブがあり、時々タグの構成が変わるところがなんとも面倒なのですが、地道にデバッグしました。スクリプトには他のページと構成が異なるURLの一覧をコメントとして残しています。スクリプトを修正した際は、テストに使えます。

#    url = 'https://herrkf.com/prtonair/2020/01/13/3797/' # Maximum PRT
#    url = 'https://herrkf.com/prtonair/2020/04/26/3843/' # 冒頭にコメントあり
#    url = 'https://herrkf.com/prtonair/2019/02/17/3626/' # 時間の前にスペースあり
#    url = 'https://herrkf.com/prtonair/2013/12/23/2843/' # NHK FMのため除外
#    url = 'https://herrkf.com/prtonair/2012/09/09/2671/' # titleがプレイリスト
#    url = 'https://herrkf.com/prtonair/2011/02/27/2307/' # 冒頭に複数行のコメントあり
#    url = 'https://herrkf.com/prtonair/2011/01/10/2220/' # P=>Div
#    url = 'https://herrkf.com/prtonair/2011/01/09/2207/' # :=>Space
#    url = 'https://herrkf.com/prtonair/2010/09/12/696/'  # div id 3桁

python3の開発環境としてAnacondaをインストールしました。環境構築は以下を参考にさせて頂きました。

Anaconda/Jupyter Notebookは、実行結果を見ながらデバッグが進められるのでとても便利です。

実行すると、標準出力にCSVが吐き出されますので、ファイルに保存します。

% python getallPRT.py > onairlist.txt

なお、出力されたファイルをExcel等で読み込むと文字化けしますので、BOMありに変換します。

% nkf --overwrite --oc=UTF-8-BOM onairlist.txt

Oracle Autonomous Datawarehouse構築

以下を参考にDBを構築します。Autonomous Data Warehouse(ADW)でも　Autonomous Transaction Processing(ATP)でも、このレベルの利用ではどちらも変わりませんね。なんとなくADWにしました。

https://www.hitoriit.com/entry/2019/10/22/080000
https://www.s-style.co.jp/blog/2019/06/4567/

Autonomous Databaseの作成ページで、Always FreeのチェックをON(Always Freeの構成オプションのみを表示)にすると、選べる項目が限定されます。メニュー上、変更したのは以下3箇所のみです。あとはデフォルトのまま、 「Autonomous Databaseの作成」を押せば構築開始されます。

• Autonomous Databaseの基本情報の指定 「表示名」
• 管理者資格証明の作成にあるADMINの「パスワード」、「パスワードの確認」

作成中は、オレンジ色（プロビジョニング中...）で、完了すると緑色（使用可能）になります。

#SQL Developer Webにてユーザ登録
以下を参考にSQL Developer Web(SDW)にADMINでログインし、ユーザを登録します。

CREATE USER calbeez IDENTIFIED BY <password>;
GRANT CREATE SESSION TO calbeez;
ALTER USER calbeez QUOTA UNLIMITED ON DATA;

SDWへのスキーマアクセスを有効にします。

BEGIN
   ORDS_ADMIN.ENABLE_SCHEMA(
     p_enabled => TRUE,
     p_schema => 'CALBEEZ',
     p_url_mapping_type => 'BASE_PATH',
     p_url_mapping_pattern => 'calbeez',
     p_auto_rest_auth => TRUE
   );
   COMMIT;
END;

更にテーブル作成をするためにADMIN権限や、データロード権限を与えます。

GRANT DWROLE TO calbeez;
GRANT UNLIMITED TABLESPACE TO calbeez;

詳しくは以下を参照してください。

https://docs.oracle.com/en/cloud/paas/atp-cloud/atpug/sql-developer-web.html
https://docs.oracle.com/en/cloud/paas/atp-cloud/atpug/manage-users-privileges.html

これで、作成したユーザでSDWにログインできるようになります。が、ログイン時のURLを変更しなければいけません。「サービスコンソール」→「開発」→「SQL Developer Web」で開かれるログイン画面のURLのうち、adminの部分をp_url_mapping_patternで設定した値に変更し、そこからログインできます。毎回URLを変更する必要があるのは、ちょっと不便です。

Oracle APEXワークスペースの作成

以下を参考にAPEXワークスペースを作成します。

「サービスコンソール」→「開発」→「Oracle APEX」で開かれるログイン画面にてADMINでログインすると、ワークスペースの作成ページが開かれますので、「データベース。ユーザー」にSDWで登録したユーザを選択、「ワークスペース名」を入力して「ワークスペースの作成」を押します。

作成後、ADMINをログアウトし、再度ユーザでログインし直します。

プロファイルの編集に諸々入力、「変更の適用」を押して登録完了です。

#オンエアリストのテーブル作成
準備が整いました。以下を参考にオンエアリストのスキーマを作成します。

APEXログイン後、「SQLワークショップ」→「ユーティリティ」→「クイックSQL」で開かれるページの左側にテーブル定義を記載すると、右側にDDLが自動生成されます。

クイックSQLに記載するテーブル定義は以下の通りです。

onairlist
  maximum  num
  year  num
  month  num
  day  num
  hour  num
  minute  num
  title vc1000
  artist vc1000
  createdate ts /default systimestamp

と、残念ながらこのままだとエラーになります。記述が終わったら、「SQLスクリプトを保存」実行の後、「レビューおよび実行」を押し、SYSTIMESTAMPを囲むシングルクォーテーションを削除します。

createdate                     timestamp default on null SYSTIMESTAMP

「実行」を押すとスクリプト実行ページになります。「即時実行」を押すとテーブルが作成されます。

オンエアリストのデータインポート

作成したテーブルにデータをインポートしましょう。SDWにログインし、作成されたテーブルを選択、右クリックメニューより、「データのロード」→「データをアップロード」を選択します。

スクレイピングしたファイルを選択して読み込みます。デフォルトで列名に最初の行が選択されているので外します（「- 選択 -」を選ぶ）。

各カラムをテーブルのカラムとマッピングし、「次」→「終了」でデータのインポートが完了します。

#オンエアリストの検索アプリ作成
検索アプリをAPEXで作成しましょう。APEXにログイン後、「アプリケーション・ビルダー」→「作成」→「新規アプリケーション」を選択します。名前を適当に入れ、機能も面白いのですべてチェックし、「ページの追加」→「対話モード・レポート」を押し、ページ名を適当に入れ、表を選び、「ページの追加」を押します。


「アプリケーションの作成」を押すと、アプリケーションが生成されます。あとは、色々修正して好みに仕上げていくのみです。以下、私が設定した内容になります。

• ホーム、対話モード・レポート、フィードバック、情報、ヘルプの認証をパブリック・ページに設定
• 情報ページにテキスト設定
• OnairlistのID, MAXIMUM, CREATEDATEを非表示列に設定
• MONTH, DAY, HOUR, MINUTEの書式マスクを「00」に設定
• 「共通コンポーネント」→「ナビゲーション・バー・リスト」→「デスクトップ・ナビゲーション・バー」→「サインアウト」で「ターゲット・タイプ」を「このアプリケーションのページ」に、ページを「9999 ログインページ」に設定（サインアウトした時にログイン画面を出したいため）

アプリケーションの実行をしているときのURLから"home?session=xxxx"を取り除いたもので誰でもアクセスできます。

#最後に
以下で今回のアプリを公開しています。

こんな面白い検索ができますので、是非お試しあれ。フィードバックもお待ちしております。