Oracle Databaseに作成したプロパティ・グラフはPGQL（プロパティ・グラフ問合せ言語）を使って操作しますが、２層モードで扱う範囲であればPGQLはSQLに変換されて、Oracle Databaseで実行されます。
そこで、SQLclを使ってPGQLから変換されたSQLを取り出し、Oracle APEXのレポートのソースにしてみます。
PGQLやプロパティ・グラフについて勉強をするための準備作業なので、それらについては特に触れません。@ryotayamanakaさんのこちらの記事などを手掛かりにして、勉強を始めるのも良いでしょう。
この記事では、SQLを取り出す準備作業とレポートの生成のために実施した作業を記述します。作業にはAlways FreeのAutonomous Databaseのインスタンスを使用します。
今回の作業を行う上で参考にした記事は以下です。
Executing Property Graph (PGQL) queries in SQLcl
https://blogs.oracle.com/oraclespatial/post/executing-property-graph-pgql-queries-in-sqlcl
それでは、SQLclでPGQLを実行するための準備から始めます。
Oracle APEXのワークスペースとしてAPEXDEVが作成済みとします。
この作業を行うAutonomous Databaseのデータベース・アクションに接続します。
管理のデータベース・ユーザーを開きます。
データベース・ユーザーの設定は以上で完了です。
この中のPGQL Plugin for SQLclを開きます。
unzip -d <SQLclを展開したディレクトリ>/sqlcl/lib/ext oracle-graph-sqlcl-plugin-22.3.0.zip
create table sqlcl_bank_accts (
acct_id number not null constraint sqlcl_bank_accts_acct_id_pk primary key,
name varchar2(64) not null
)
;
create table sqlcl_bank_txns (
txn_id number not null constraint sqlcl_bank_txns_txn_id_pk primary key,
from_acct_id number not null
constraint sqlcl_bank_txns_from_acct_i_fk
references sqlcl_bank_accts on delete cascade,
to_acct_id number not null
constraint sqlcl_bank_txns_to_acct_id_fk
references sqlcl_bank_accts on delete cascade,
description varchar2(128) not null,
amount number(38,2) not null
)
;
create index sqlcl_bank_txns_i1 on sqlcl_bank_txns (from_acct_id);
create index sqlcl_bank_txns_i52 on sqlcl_bank_txns (to_acct_id);SQL> create table sqlcl_bank_accts (
2 acct_id number not null constraint sqlcl_bank_accts_acct_id_pk primary key,
3 name varchar2(64) not null
4 )
5* ;
Table SQLCL_BANK_ACCTSは作成されました。
SQL> create table sqlcl_bank_txns (
2 txn_id number not null constraint sqlcl_bank_txns_txn_id_pk primary key,
3 from_acct_id number not null
4 constraint sqlcl_bank_txns_from_acct_i_fk
5 references sqlcl_bank_accts on delete cascade,
6 to_acct_id number not null
7 constraint sqlcl_bank_txns_to_acct_id_fk
8 references sqlcl_bank_accts on delete cascade,
9 description varchar2(128) not null,
10 amount number(38,2) not null
11 )
12* ;
Table SQLCL_BANK_TXNSは作成されました。
SQL> create index sqlcl_bank_txns_i1 on sqlcl_bank_txns (from_acct_id);
Index SQLCL_BANK_TXNS_I1は作成されました。
SQL> create index sqlcl_bank_txns_i52 on sqlcl_bank_txns (to_acct_id);
Index SQLCL_BANK_TXNS_I52は作成されました。
SQL>
SQLワークショップのユーティリティより、データ・ジェネレータを起動します。
SQL> select count(*) from sqlcl_bank_accts;
COUNT(*)
___________
1000
SQL>
SQL> select count(*) from sqlcl_bank_txns;
COUNT(*)
___________
9979
SQL>
SQL> delete from sqlcl_bank_txns where from_acct_id = to_acct_id;
8行削除されました。
SQL> commit;
コミットが完了しました。
SQL>
pgql auto on;
create property graph sqlcl_bank_graph
vertex tables (
SQLCL_BANK_ACCTS
Key (ACCT_ID)
Label ACCOUNT
Properties (ACCT_ID)
)
edge tables (
SQLCL_BANK_TXNS
key(TXN_ID)
Source key (FROM_ACCT_ID) references SQLCL_BANK_ACCTS
Destination key (TO_ACCT_ID) references SQLCL_BANK_ACCTS
Label TRANSFER
Properties (FROM_ACCT_ID, TO_ACCT_ID, AMOUNT)
);
commit;SQL> pgql auto on
PGQL Auto enabled for schema=[null], graph=[null], execute=[true], translate=[false]
PGQL> create property graph sqlcl_bank_graph
2 vertex tables (
3 SQLCL_BANK_ACCTS
4 Key (ACCT_ID)
5 Label ACCOUNT
6 Properties (ACCT_ID)
7 )
8 edge tables (
9 SQLCL_BANK_TXNS
10 key(TXN_ID)
11 Source key (FROM_ACCT_ID) references SQLCL_BANK_ACCTS
12 Destination key (TO_ACCT_ID) references SQLCL_BANK_ACCTS
13 Label TRANSFER
14 Properties (FROM_ACCT_ID, TO_ACCT_ID, AMOUNT)
15* );
Graph created
PGQL> commit;
コミットが完了しました。
PGQL>
select v.ACCT_ID, count(*) as NumDeposits match (m)-[e]->(v) group by v.ACCT_ID order by NumDeposits desc limit 5;
select s.ACCT_ID, e.AMOUNT, d.ACCT_ID MATCH (s)-[e]->(d) WHERE s.ACCT_ID=1002 or s.ACCT_ID=1001;
PGQL> pgql auto on graph sqlcl_bank_graph translate
PGQL Auto enabled for schema=[null], graph=[SQLCL_BANK_GRAPH], execute=[true], translate=[true]
PGQL>
PGQL> select v.ACCT_ID, count(*) as NumDeposits match (m)-[e]->(v) group by v.ACCT_ID order by NumDeposits desc limit 5;
SELECT * FROM(SELECT T0$1.T AS "v.ACCT_ID$T",
T0$1.V AS "v.ACCT_ID$V",
T0$1.VN AS "v.ACCT_ID$VN",
T0$1.VT AS "v.ACCT_ID$VT",
7 AS "NumDeposits$T",
to_nchar(COUNT(*),'TM9','NLS_Numeric_Characters=''.,''') AS "NumDeposits$V",
COUNT(*) AS "NumDeposits$VN",
to_timestamp_tz(null) AS "NumDeposits$VT"
FROM (SELECT DISTINCT EID, SVID, DVID,EL FROM "WKSP_APEXDEV".SQLCL_BANK_GRAPHGE$) T0$0,
"WKSP_APEXDEV".SQLCL_BANK_GRAPHVT$ T0$1
WHERE T0$1.K=n'ACCT_ID' AND
T0$0.DVID=T0$1.VID
GROUP BY T0$1.T,
T0$1.V,
T0$1.VN,
T0$1.VT
ORDER BY COUNT(*) DESC NULLS FIRST)
WHERE ROWNUM <= 5;
v.ACCT_ID NumDeposits
____________ ______________
156 20
649 20
430 20
644 19
227 18
PGQL>
PGQL> select s.ACCT_ID, e.AMOUNT, d.ACCT_ID MATCH (s)-[e]->(d) WHERE s.ACCT_ID=1002 or s.ACCT_ID=1001;
SELECT T0$1.T AS "s.ACCT_ID$T",
T0$1.V AS "s.ACCT_ID$V",
T0$1.VN AS "s.ACCT_ID$VN",
T0$1.VT AS "s.ACCT_ID$VT",
T0$0.T AS "e.AMOUNT$T",
T0$0.V AS "e.AMOUNT$V",
T0$0.VN AS "e.AMOUNT$VN",
T0$0.VT AS "e.AMOUNT$VT",
T0$2.T AS "d.ACCT_ID$T",
T0$2.V AS "d.ACCT_ID$V",
T0$2.VN AS "d.ACCT_ID$VN",
T0$2.VT AS "d.ACCT_ID$VT"
FROM "WKSP_APEXDEV".SQLCL_BANK_GRAPHGE$ T0$0,
"WKSP_APEXDEV".SQLCL_BANK_GRAPHVT$ T0$1,
"WKSP_APEXDEV".SQLCL_BANK_GRAPHVT$ T0$2
WHERE T0$0.K=n'AMOUNT' AND
T0$1.K=n'ACCT_ID' AND
T0$2.K=n'ACCT_ID' AND
T0$0.SVID=T0$1.VID AND
T0$0.DVID=T0$2.VID AND
(T0$1.VN = 1002 OR T0$1.VN = 1001);
行が選択されていません
PGQL>
select v.ACCT_ID, count(*) as NumDeposits match (m)-[e]->(v) group by v.ACCT_ID order by NumDeposits desc limit 5;
以下のSELECT文と同じです。
select to_acct_id, count(*) from sqlcl_bank_txns group by to_acct_id order by 2 desc fetch first 5 rows only;
以下のPGQLのSELECT文は
select s.ACCT_ID, e.AMOUNT, d.ACCT_ID MATCH (s)-[e]->(d) WHERE s.ACCT_ID=1002 or s.ACCT_ID=1001;
select * from sqlcl_bank_txns where from_acct_id in (1001,1002);
