日日是Oracle APEX: GoogleのAlloyDB Omniを使ってOracle APEXの仕組みを理解する

最近更新されたGoogleのAlloyDB Omniを使って、Oracle APEXの仕組みに似せたテンプレート・エンジンをPL/pgSQLで書いてみました。Oracle Databaseを知らない方にOracle APEXに興味を持っていただけることを期待しています。また、すでにOracle APEXを使っている方も、基本的な仕組みを理解する助けになるように期待しています。

MシリーズのmacOS上で、AlloyDB OmniとNode.jsを使ってアプリケーションを開発します。作成するアプリケーションは以下のように動作します。

http://localhost:3000/data?app=1&page=1&session=0

AlloyDB Omniの準備から始めます。Googleの以下のドキュメントに説明されているpodmanを使った方法を採用します。Macの場合、IntelとMシリーズの両方がサポートされています。

https://cloud.google.com/alloydb/omni/docs/quickstart

AlloyDB Omniのコンテナを削除した後もデータベースのデータを維持するために、podmanでボリュームを作成しておきます。ボリューム名はomnidataとしています。

podman volume create omnidata

% podman volume create omnidata

omnidata

AlloyDB Omniが動作するコンテナを作成します。

コンテナ・イメージがダウンロードされていないと、コンテナの初回作成時にダウンロードが実行されます。pullを実行していると、あらかじめコンテナ・イメージをダウンロードできます。

podman pull google/alloydbomni

% podman pull google/alloydbomni

Trying to pull docker.io/google/alloydbomni:latest...

Getting image source signatures

Copying blob sha256:9a05f2493d03e0bbb1066e3bb909096d9703c5418d053689f8f7ec5c1d7f2b60

Copying blob sha256:205213b8936196dbb62c16b3a96e0b7e220299b3a9ca113bccfd126dadb699e5

Copying blob sha256:c1e0ef7b956a07c7b090256aa16cbb0550a34d0625d1d23c5b1a76e92a58d01e

Copying blob sha256:2fdd8bd0abec7d6bf9c9413fa4822358118e9e09fbd29407074e295e7480643f

Copying blob sha256:0185c6859b00ddb146dc31ca9b6beaed53efb3407563057f05fb266b3f434287

Copying blob sha256:f1c87667e7842cd5137ab5ab073fd291aab49798d524866faf5e29baf799b2ef

Copying blob sha256:f5852518415488bf422f9b7f2b17ffd7d4d7185e7c8ab8bf2147deeba4634f9b

Copying blob sha256:f4e912a38a04c24a3b2c9357881eb1d2c4a79966b599994cf923a3b861deb717

Copying blob sha256:149990bdbe96c90c9632f7494d32f308f2f410a17dc862496798163b371df491

Copying blob sha256:867c56a9d8dc8e0f8bf8540326bb5638c709e265ad3375249cc036c62b16338b

Copying blob sha256:7b43d926df6c0192fef4a132f46301cb25c933a70475037b5817c9ee977cf7f9

Copying blob sha256:4f4fb700ef54461cfa02571ae0db9a0dc1e0cdb5577484a6d75e68dc38e8acc1

Copying blob sha256:5fe5be224054609d6669eb71c74f8414c654ed0495c9298a32aec1bd29ac4ef3

Copying blob sha256:b5897c548647a8d823411b5f365fccd16994b35efcd75164b5fc82d77c396ebe

Copying blob sha256:8714a15a9f79d2f01947ddb11b2760a05d897325efc39ff988dc4e073cf7a93c

Copying blob sha256:2cc17a1ffe78834f475a35e67e612c3af76ac44d459e0ab42339f0fb2347feb7

Copying blob sha256:94947c0ccc56de03ab622a35e70c99ab4636dae5dae427dfba5df79b2a84177f

Copying blob sha256:215fbeb3c59b120bf53d3081b76272a835b13ae7a77dd072d59e70cfcda3bb07

Copying blob sha256:5e6b93867b6c54a5b48c6497faf8bf50214ea208d0be670cea6db9d9f23482fd

Copying blob sha256:92a15b9c822ec96fc09ed2ceacfec6f0a20058f9dc2c601c490985ec306826f7

Copying blob sha256:a503bc9e96a17982d6a2ae33314b3127dc7bf9acb4db2ac1ebbb899b32147aeb

Copying blob sha256:6bf938e955c48891853605a1636950a912b1c81b9749205ba17c26241e005702

Copying blob sha256:4c80b84ecf5976c85858871790f90a50816f48ceefccab5c843d2c0bde955036

Copying blob sha256:e01e5606c56fe7fc61177d8118a6274a5fbb146c881f00de6a8bc4780412c6df

Copying blob sha256:09e9c6b61a697d1af8b69f1aa5f2d048adfbbfd705ca5752943cd49772d884e3

Copying blob sha256:0bf7b01fb1e47ec1361fadec05df0a950da5a4c46b49779eb34d0e091e165a5e

Copying blob sha256:d220fb7d6aeb40b0998b710f1a4b61c2ce10876ed6e8dd5c8cc0009b5eb07289

Copying blob sha256:5ed9845c51464bbab10c8deb0bd80b0bc484e2064fa4076176cb5a4421169557

Copying config sha256:fe0702e3d2389cf3cdf28f1f6c4342b3da53981820f5c9fba1249a7efc351eac

Writing manifest to image destination

fe0702e3d2389cf3cdf28f1f6c4342b3da53981820f5c9fba1249a7efc351eac

以下のコマンドを実行します。コンテナ名はmy-omniとしています。コンテナ・イメージとしてはgoogle/alloydbomniを指定します。

podman run --name my-omni -e POSTGRES_PASSWORD=<パスワード> -v omnidata:/var/lib/postgresql/data -p 5432:5432 -d google/alloydbomni

% podman run --name my-omni -e POSTGRES_PASSWORD=******** -v omnidata:/var/lib/postgresql/data -p 5432:5432 -d google/alloydbomni

833de6f70e64eedde17644361833fb0a226f8958c50920ffba3e5b674d73805c

% podman ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

833de6f70e64 docker.io/google/alloydbomni:latest postgres 11 seconds ago Up 11 seconds 0.0.0.0:5432->5432/tcp my-omni

以上でAlloyDB Omniのデータベースが作成されました。

作成されたデータベースに接続してみます。PostgreSQLを扱えるツールは持っていないので、コンテナに含まれているpsqlを実行します。

podman exec -it my-omni psql -U postgres

接続した後、接続情報を表示します。

\conninfo

% podman exec -it my-omni psql -U postgres

psql (15.7)

Type "help" for help.

postgres=# \conninfo

You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".

postgres=#

ユーザーはpostgres、ポート番号5432、パスワードはコンテナ作成時に環境変数POSTGRES_PASSWORDとして設定した文字列で外部から接続できる状態になっています。

Oracle Databaseでよく使われるEMP/DEPTのサンプル・データセットを、このデータベースにインストールします。

上記のプロンプトより、以下のスクリプトを実行します。

postgres=# -- EMP

postgres=# CREATE TABLE EMP (

postgres(# EMPNO NUMERIC(4,0) PRIMARY KEY,

postgres(# ENAME VARCHAR(10),

postgres(# JOB VARCHAR(9),

postgres(# MGR NUMERIC(4,0),

postgres(# HIREDATE DATE,

postgres(# SAL NUMERIC(7,2),

postgres(# COMM NUMERIC(7,2),

postgres(# DEPTNO NUMERIC(2,0)

postgres(# );

CREATE TABLE

postgres=#

postgres=# -- Prep EMP

postgres=# INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES

postgres-# (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10),

postgres-# (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20),

postgres-# (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30),

postgres-# (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10),

postgres-# (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000.00, NULL, 20),

postgres-# (7902, 'FORD', 'ANALYST', 7566, '1981-12-09', 3000.00, NULL, 20),

postgres-# (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30),

postgres-# (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30),

postgres-# (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30),

postgres-# (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30),

postgres-# (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30),

postgres-# (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 10),

postgres-# (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100.00, NULL, 20),

postgres-# (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20);

INSERT 0 14

postgres=#

postgres=# -- DEPT

postgres=# CREATE TABLE DEPT (

postgres(# DEPTNO NUMERIC(4,0) PRIMARY KEY,

postgres(# DNAME VARCHAR(50),

postgres(# LOC VARCHAR(50)

postgres(# );

CREATE TABLE

postgres=#

postgres=# -- Prep DEPT.

postgres=# INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES

postgres-# (10, 'ACCOUNTING', 'NEW YORK'),

postgres-# (20, 'RESEARCH', 'DALLAS'),

postgres-# (30, 'SALES', 'CHICAGO'),

postgres-# (40, 'OPERATIONS', 'BOSTON');

INSERT 0 4

postgres=#

作成された表EMPを全件検索します。

select * from emp;

実行例の記載は省略しますが、表DEPTについても確認しておきます。

postgres=# select * from emp;

-------+--------+-----------+------+------------+---------+---------+--------

7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10

7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | | 20

7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | | 30

7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10

7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000.00 | | 20

7902 | FORD | ANALYST | 7566 | 1981-12-09 | 3000.00 | | 20

7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30

7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30

7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30

7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30

7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30

7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10

7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | | 20

7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20

(14 rows)

postgres=#

テンプレート処理の実装に使う表として、APPLICATIONS、PAGES、REGIONS、SESSIONSを作成します。

初回実行時はDROP文は不要です。CREATE TABLE文だけを実行します。

postgres=# -- APPLICATIONS

postgres=# create table applications(

postgres(# app_id int primary key,

postgres(# name text not null

postgres(# );

CREATE TABLE

postgres=#

postgres=# -- PAGES in APPLICATIONS

postgres=# create table pages(

postgres(# page_id int,

postgres(# app_id int,

postgres(# template text,

postgres(# constraint pk_pages primary key(page_id, app_id),

postgres(# constraint fk_apps foreign key(app_id) references applications(app_id)

postgres(# );

CREATE TABLE

postgres=#

postgres=# -- REGIONS in PAGES

postgres=# create table regions(

postgres(# region_id serial primary key,

postgres(# page_id int,

postgres(# app_id int,

postgres(# region_type int,

postgres(# source text,

postgres(# constraint fk_r_pages foreign key(page_id, app_id) references pages(page_id, app_id)

postgres(# );

CREATE TABLE

postgres=# -- SESSIONS

postgres=# create table sessions(

postgres(# session_id serial primary key,

postgres(# start_timestamp timestamp default current_timestamp not null,

postgres(# session_state jsonb

postgres(# );

CREATE TABLE

postgres=#

これらの表に保存されたデータよりHTMLを生成するファンクションgenerate_htmlを作成します。generate_htmlの引数はp_app_id（アプリケーションID）、p_page_id（ページ番号）、p_session_idです。これらは、ブラウザのリクエストに含まれる引数app、page、sessionの値が渡されます。

表示対象となるページを指定するアプリケーションIDとページ番号、それとセッションIDを受け取ってHTMLを出力する処理は、すべてPL/pgSQLで記述されたファンクションが行います。

postgres=# create or replace function generate_html(

postgres(# p_app_id int,

postgres(# p_page_id int,

postgres(# p_session_id int

postgres(# )

postgres-# returns text as $$

postgres$# declare

postgres$# l_html_output text;

postgres$# l_page_output text;

postgres$# r_region_record record;

postgres$# l_region_output text;

postgres$# r_header_record record;

postgres$# a_reg_columns text[];

postgres$# l_query_text text;

postgres$# r_rec record;

postgres$# l_col text;

[中略]

postgres$# -- セッション・ステートを保存。

postgres$# update sessions set session_state = l_session_state where session_id = l_session_id;

postgres$#

postgres$# -- ページ・テンプレートを穴埋め

postgres$# l_html_output := replace(l_html_output,'#BODY#',l_page_output);

postgres$#

postgres$# -- HTMLページを返す。

postgres$# return l_html_output;

postgres$# end;

postgres$# $$ language plpgsql;

CREATE FUNCTION

postgres=#

どのようなページが表示されるかを決めるデータを、表APPLICATIONS、PAGES、REGIONSに投入します。

postgres=# /*

postgres*# * アプリケーション名: My First App, アプリケーションIDが1のアプリケーションを作成する。

postgres*# */

postgres-# insert into applications(app_id,name) values(1, 'My First App');

INSERT 0 1

postgres=# /*

postgres*# * 作成したアプリケーションにページ番号が1と2の２つのページを作成する。

postgres*# */

postgres-# insert into pages(page_id,app_id,template) values(1,1,'<html><body>#BODY#</body></html>');

INSERT 0 1

postgres=# insert into pages(page_id,app_id,template) values(2,1,'<html><body>#BODY#</body></html>');

INSERT 0 1

postgres=# /*

postgres*# * 作成したページにリージョンを作成する。

postgres*# */

postgres-# -- ページ番号1に、静的コンテンツのリージョン(リージョン・タイプが1）を作成する。sourceはHTMLとして扱い置換文字列をセッション・ステートの値で置き換える。

postgres-# insert into regions(page_id,app_id,region_type,source) values(1,1,1,'<span style="font-weight: bold;">Table :EMP</span> Open <a href="/data?app=1&page=2&session=#SESSION#">DEPT</a>, Visit: #COUNT#');

INSERT 0 1

postgres=# -- ページ番号1に、レポート（リージョン・タイプが2）を作成する。sourceを表の名前として扱う。つまり表EMPがソース。

postgres=# insert into regions(page_id,app_id,region_type,source) values(1,1,2,'emp');

INSERT 0 1

postgres=# -- ページ番号2に、静的コンテンツのリージョンを作成する。

postgres=# insert into regions(page_id,app_id,region_type,source) values(2,1,1,'<span style="font-weight: bold;">Table :DEPT</span> Open <a href="/data?app=1&page=1&session=#SESSION#">EMP</a> Visit: #COUNT#');

INSERT 0 1

postgres=# -- ページ番号２に、レポートのリージョンを作成する。ソースは表DEPT。

postgres=# insert into regions(page_id,app_id,region_type,source) values(2,1,2,'dept');

INSERT 0 1

postgres=#

以上でテンプレート処理のために必要なデータ（Oracle APEXではこのようなデータをアプリケーションのメタデータと呼んでいます）が準備できました。

HTTPを受け付けてテンプレート処理を呼び出すNode.jsのアプリケーションを作成します。

Node.jsのアプリケーションを保持するディレクトリとしてmy-omni-appを作成し、初期化します。

mkdir my-omni-app

cd my-omni-app

npm init -y

% mkdir my-omni-app

% cd my-omni-app

my-omni-app % npm init -y

(node:55964) ExperimentalWarning: CommonJS module /opt/homebrew/lib/node_modules/npm/node_modules/debug/src/node.js is loading ES Module /opt/homebrew/lib/node_modules/npm/node_modules/supports-color/index.js using require().

Support for loading ES Module in require() is an experimental feature and might change at any time

(Use `node --trace-warnings ...` to show where the warning was created)

Wrote to /Users/**********/Documents/my-omni-app/package.json:

{

"name": "my-omni-app",

"version": "1.0.0",

"main": "index.js",

"scripts": {

"test": "echo \"Error: no test specified\" && exit 1"

"keywords": [],

"author": "",

"license": "ISC",

"description": ""

}

my-omni-app %

モジュールexpress（HTTPリクエストのルータ）とpg（PostgreSQLの接続ドライバ）をインストールします。

npm install express pg

my-omni-app % npm install express pg

(node:55972) ExperimentalWarning: CommonJS module /opt/homebrew/lib/node_modules/npm/node_modules/debug/src/node.js is loading ES Module /opt/homebrew/lib/node_modules/npm/node_modules/supports-color/index.js using require().

Support for loading ES Module in require() is an experimental feature and might change at any time

(Use `node --trace-warnings ...` to show where the warning was created)

added 79 packages, and audited 80 packages in 2s

13 packages are looking for funding

run `npm fund` for details

found 0 vulnerabilities

my-omni-app %

ファイルapp.jsを作成します。内容は以下になります。パスワードの部分はコンテナ作成時に環境変数POSTGRES_PASSWORDに与えた値に置き換えます。

HTTPリクエストに含まれる引数を渡してストアド・プロシージャgenerate_htmlを呼び出しています。HTMLはデータベース側で生成し、セッションの管理もデータベース側で行なっているため、Node.jsのアプリケーションは生成されたHTMLをクライアントに返却するだけです。

Node.jsのアプリapp.jsを実行します。

node app.js

my-omni-app % node app.js

Server is running on http://localhost:3000

以上で作業は完了です。ブラウザより記事の先頭にあるURLにアクセスすると、GIF動画のようなページが表示されます。

今回の記事は以上になります。

Oracle APEXのアプリケーション作成の参考になれば幸いです。

完

2024年11月21日木曜日

GoogleのAlloyDB Omniを使ってOracle APEXの仕組みを理解する