Oracle REST Data Services 23.3より、検索のみですがGraphQLがサポートされるようになったので、Oracle Database 23c Freeのコンテナ・イメージを使ってGraphQLを実行できる環境を作ってみました。
Apple MシリーズのColimaで実行します。Colimaは、Oracle Database 23c Freeのコンテナが実行できるようにセットアップ済みとします。
作成したコンテナを起動するディレクトリを決めて、その下にディレクトリsetupおよびstartupを作成します。
mkdir setup startup
chmod 755 setup startup
Oracle Database 23c Freeのコンテナ・イメージからコンテナgraphql-dbを作成します。
docker run -d --name graphql-db \
-e ORACLE_PWD=[SYSのパスワード] -p 1521:1521 -p 8181:8181 \
-v ${PWD}/setup:/opt/oracle/scripts/setup -v ${PWD}/startup:/opt/oracle/scripts/startup \
container-registry.oracle.com/database/free:latest% docker run -d --name graphql-db \
-e ORACLE_PWD=******** -p 1521:1521 -p 8181:8181 \
-v ${PWD}/setup:/opt/oracle/scripts/setup -v ${PWD}/startup:/opt/oracle/scripts/startup \
container-registry.oracle.com/database/free:latest
82db7cfaae91b44656bd1be0e92b1efe5275fb375ec2da38a185fbc018a7668c
%
% docker logs -f graphql-db
Starting Oracle Net Listener.
Oracle Net Listener started.
Starting Oracle Database instance FREE.
Oracle Database instance FREE started.
The Oracle base remains unchanged with value /opt/oracle
SQL*Plus: Release 23.0.0.0.0 - Production on Thu Apr 4 09:03:29 2024
Version 23.3.0.23.09
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL>
User altered.
SQL>
User altered.
SQL>
Session altered.
SQL>
User altered.
SQL> Disconnected from Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
The Oracle base remains unchanged with value /opt/oracle
#########################
DATABASE IS READY TO USE!
#########################
The following output is now a tail of the alert.log:
===========================================================
No patches have been applied
===========================================================
2024-04-04T09:03:21.932957+00:00
FREEPDB1(3):Opening pdb with Resource Manager plan: DEFAULT_PLAN
2024-04-04T09:03:25.289080+00:00
Completed: Pluggable database FREEPDB1 opened read write
Completed: ALTER DATABASE OPEN
2024-04-04T09:03:41.030041+00:00
FREEPDB1(3):TABLE AUDSYS.AUD$UNIFIED: ADDED INTERVAL PARTITION SYS_P342 (3566) VALUES LESS THAN (TIMESTAMP' 2024-04-05 00:00:00')
Ctrl+C
docker exec -it graphql-db /opt/oracle/scripts/setup/01_setup_graphql.sh
% docker exec -it graphql-db /opt/oracle/scripts/setup/01_setup_graphql.sh
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 173M 100 173M 0 0 8532k 0 0:00:20 0:00:20 --:--:-- 8663k
Oracle Linux 8 BaseOS Latest (x86_64) 7.6 kB/s | 3.6 kB 00:00
Oracle Linux 8 BaseOS Latest (x86_64) 6.5 MB/s | 72 MB 00:11
Oracle Linux 8 Application Stream (x86_64) 17 kB/s | 3.9 kB 00:00
Oracle Linux 8 Application Stream (x86_64) 7.4 MB/s | 55 MB 00:07
Oracle Linux 8 Development Packages (x86_64) 14 kB/s | 3.3 kB 00:00
Oracle Linux 8 Development Packages (x86_64) 7.4 MB/s | 128 MB 00:17
Last metadata expiration check: 0:01:17 ago on Thu Apr 4 09:21:37 2024.
Dependencies resolved.
================================================================================
Package Architecture Version Repository Size
================================================================================
Installing:
jdk-17 x86_64 2000:17.0.10-11 @commandline 174 M
Transaction Summary
================================================================================
Install 1 Package
Total size: 174 M
Installed size: 303 M
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : jdk-17-2000:17.0.10-11.x86_64 1/1
Running scriptlet: jdk-17-2000:17.0.10-11.x86_64 1/1
jexec setup failed
Verifying : jdk-17-2000:17.0.10-11.x86_64 1/1
Installed:
jdk-17-2000:17.0.10-11.x86_64
Complete!
Added ol8_oracle_software repo from http://yum.oracle.com/repo/OracleLinux/OL8/oracle/software/x86_64
ol8_oracle_software 159 kB/s | 109 kB 00:00
Last metadata expiration check: 0:00:02 ago on Thu Apr 4 09:26:35 2024.
Dependencies resolved.
================================================================================
Package Architecture Version Repository Size
================================================================================
Installing:
ords noarch 23.4.0-8.el8 ol8_oracle_software 108 M
Installing dependencies:
lsof x86_64 4.93.2-1.el8 ol8_baseos_latest 253 k
Transaction Summary
================================================================================
Install 2 Packages
Total download size: 108 M
Installed size: 113 M
Downloading Packages:
(1/2): lsof-4.93.2-1.el8.x86_64.rpm 857 kB/s | 253 kB 00:00
(2/2): ords-23.4.0-8.el8.noarch.rpm 7.5 MB/s | 108 MB 00:14
--------------------------------------------------------------------------------
Total 7.5 MB/s | 108 MB 00:14
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : lsof-4.93.2-1.el8.x86_64 1/2
Running scriptlet: ords-23.4.0-8.el8.noarch 2/2
Installing : ords-23.4.0-8.el8.noarch 2/2
Running scriptlet: ords-23.4.0-8.el8.noarch 2/2
INFO: Before starting ORDS service, run the below command as user oracle:
ords --config /etc/ords/config install
Verifying : lsof-4.93.2-1.el8.x86_64 1/2
Verifying : ords-23.4.0-8.el8.noarch 2/2
Installed:
lsof-4.93.2-1.el8.x86_64 ords-23.4.0-8.el8.noarch
Complete!
ORDS: Release 23.4 Production on Thu Apr 04 09:27:39 2024
Copyright (c) 2010, 2024, Oracle.
Configuration:
/etc/ords/config/
Created folder /tmp/logs
Oracle REST Data Services - Non-Interactive Install
Retrieving information.
The setting named: db.connectionType was set to: basic in configuration: default
The setting named: db.hostname was set to: localhost in configuration: default
The setting named: db.port was set to: 1521 in configuration: default
The setting named: db.servicename was set to: freepdb1 in configuration: default
The setting named: db.username was set to: ORDS_PUBLIC_USER in configuration: default
The setting named: db.password was set to: ****** in configuration: default
The setting named: feature.sdw was set to: true in configuration: default
The global setting named: database.api.enabled was set to: true
The setting named: restEnabledSql.active was set to: true in configuration: default
The setting named: security.requestValidationFunction was set to: ords_util.authorize_plsql_gateway in configuration: default
2024-04-04T09:27:55.486Z INFO Installing Oracle REST Data Services version 23.4.0.r3461619 in FREEPDB1
2024-04-04T09:28:04.039Z INFO ... Verified database prerequisites
2024-04-04T09:28:09.668Z INFO ... Created Oracle REST Data Services proxy user
2024-04-04T09:28:15.371Z INFO ... Created Oracle REST Data Services schema
2024-04-04T09:28:23.460Z INFO ... Granted privileges to Oracle REST Data Services
2024-04-04T09:28:52.539Z INFO ... Created Oracle REST Data Services database objects
2024-04-04T09:30:47.669Z INFO Completed installation for Oracle REST Data Services version 23.4.0.r3461619. Elapsed time: 00:02:51.881
2024-04-04T09:30:47.700Z INFO Log file written to /tmp/logs/ords_install_2024-04-04_092753_81448.log
2024-04-04T09:30:47.861Z INFO To run in standalone mode, use the ords serve command:
2024-04-04T09:30:47.876Z INFO ords --config /etc/ords/config serve
2024-04-04T09:30:47.880Z INFO Visit the ORDS Documentation to access tutorials, developer guides and more to help you get started with the new ORDS Command Line Interface (http://oracle.com/rest).
ORDS: Release 23.4 Production on Thu Apr 04 09:31:00 2024
Copyright (c) 2010, 2024, Oracle.
Configuration:
/etc/ords/config/
The global setting named: db.invalidPoolTimeout was set to: 5s
ORDS: Release 23.4 Production on Thu Apr 04 09:31:12 2024
Copyright (c) 2010, 2024, Oracle.
Configuration:
/etc/ords/config/
The global setting named: debug.printDebugToScreen was set to: true
ORDS: Release 23.4 Production on Thu Apr 04 09:31:24 2024
Copyright (c) 2010, 2024, Oracle.
Configuration:
/etc/ords/config/
No updates required to setting named restEnabledSql.active in default configuration because the values are the same.
ORDS: Release 23.4 Production on Thu Apr 04 09:31:39 2024
Copyright (c) 2010, 2024, Oracle.
Configuration:
/etc/ords/config/
No updates required to setting named feature.sdw in default configuration because the values are the same.
ORDS: Release 23.4 Production on Thu Apr 04 09:31:54 2024
Copyright (c) 2010, 2024, Oracle.
Configuration:
/etc/ords/config/
The setting named: jdbc.MaxLimit was set to: 30 in configuration: default
ORDS: Release 23.4 Production on Thu Apr 04 09:32:09 2024
Copyright (c) 2010, 2024, Oracle.
Configuration:
/etc/ords/config/
The setting named: jdbc.InitialLimit was set to: 10 in configuration: default
ORDS: Release 23.4 Production on Thu Apr 04 09:32:25 2024
Copyright (c) 2010, 2024, Oracle.
Configuration:
/etc/ords/config/
The global setting named: standalone.http.port was set to: 8181
Dependencies resolved.
================================================================================
Package Architecture Version Repository Size
================================================================================
Removing:
jdk-17 x86_64 2000:17.0.10-11 @@commandline 303 M
Transaction Summary
================================================================================
Remove 1 Package
Freed space: 303 M
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Running scriptlet: jdk-17-2000:17.0.10-11.x86_64 1/1
Erasing : jdk-17-2000:17.0.10-11.x86_64 1/1
Running scriptlet: jdk-17-2000:17.0.10-11.x86_64 1/1
Verifying : jdk-17-2000:17.0.10-11.x86_64 1/1
Removed:
jdk-17-2000:17.0.10-11.x86_64
Complete!
Added ol8_graalvm repo from https://yum.oracle.com/repo/OracleLinux/OL8/graalvm/community/x86_64
ol8_graalvm 3.2 MB/s | 3.6 MB 00:01
Last metadata expiration check: 0:00:07 ago on Thu Apr 4 09:32:44 2024.
Dependencies resolved.
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
graalvm22-ce-17-javascript x86_64 22.3.3-1.el8 ol8_graalvm 47 M
graalvm22-ce-17-jdk x86_64 22.3.3-1.el8 ol8_graalvm 224 M
Transaction Summary
================================================================================
Install 2 Packages
Total download size: 271 M
Installed size: 663 M
Downloading Packages:
(1/2): graalvm22-ce-17-javascript-22.3.3-1.el8. 5.3 MB/s | 47 MB 00:08
(2/2): graalvm22-ce-17-jdk-22.3.3-1.el8.x86_64. 7.0 MB/s | 224 MB 00:32
--------------------------------------------------------------------------------
Total 8.4 MB/s | 271 MB 00:32
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : graalvm22-ce-17-jdk-22.3.3-1.el8.x86_64 1/2
Running scriptlet: graalvm22-ce-17-jdk-22.3.3-1.el8.x86_64 1/2
Installing : graalvm22-ce-17-javascript-22.3.3-1.el8.x86_64 2/2
Running scriptlet: graalvm22-ce-17-javascript-22.3.3-1.el8.x86_64 2/2
Verifying : graalvm22-ce-17-javascript-22.3.3-1.el8.x86_64 1/2
Verifying : graalvm22-ce-17-jdk-22.3.3-1.el8.x86_64 2/2
Installed:
graalvm22-ce-17-javascript-22.3.3-1.el8.x86_64
graalvm22-ce-17-jdk-22.3.3-1.el8.x86_64
Complete!
%
% docker restart graphql-db
graphql-db
%
curl -OL https://github.com/oracle-samples/db-sample-schemas/archive/refs/tags/v23.3.zip
unzip v23.3.zip
% curl -OL https://github.com/oracle-samples/db-sample-schemas/archive/refs/tags/v23.3.zip
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
0 0 0 0 0 0 0 0 --:--:-- --:--:-- --:--:-- 0
100 11.6M 0 11.6M 0 0 3352k 0 --:--:-- 0:00:03 --:--:-- 3743k
% unzip v23.3.zip
Archive: v23.3.zip
e3325a83e56c516815844025418a96ecaf219751
creating: db-sample-schemas-23.3/
extracting: db-sample-schemas-23.3/.gitignore
inflating: db-sample-schemas-23.3/LICENSE.txt
inflating: db-sample-schemas-23.3/README.md
inflating: db-sample-schemas-23.3/README.txt
inflating: db-sample-schemas-23.3/SECURITY.md
creating: db-sample-schemas-23.3/customer_orders/
inflating: db-sample-schemas-23.3/customer_orders/README.md
inflating: db-sample-schemas-23.3/customer_orders/README.txt
[中略]
inflating: db-sample-schemas-23.3/sales_history/sh_install.sql
inflating: db-sample-schemas-23.3/sales_history/sh_populate.sql
inflating: db-sample-schemas-23.3/sales_history/sh_uninstall.sql
inflating: db-sample-schemas-23.3/sales_history/supplementary_demographics.csv
inflating: db-sample-schemas-23.3/sales_history/times.csv
%
サンプルのhuman_resourcesをインストールします。SQLclを使用します。
cd db-sample-schemas-23.3/human_resources
sql sys/<パスワード>@localhost/freepdb1 as sysdba
% cd db-sample-schemas-23.3/human_resources
% sql sys/********@localhost/freepdb1 as sysdba
SQLcl: 水 4月 03 16:54:29 2024のリリース23.4 Production
Copyright (c) 1982, 2024, Oracle. All rights reserved.
接続先:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL>
hr_install.sqlを実行します。すぐ後にユーザーHRで接続して作業するため、ユーザHRに与えるパスワードは覚えておきます。
SQL> @hr_install
Thank you for installing the Oracle Human Resources Sample Schema.
This installation script will automatically exit your database session
at the end of the installation or if any error is encountered.
The entire installation will be logged into the 'hr_install.log' log file.
Enter a password for the user HR: ******
Enter a tablespace for HR [USERS]:
Do you want to overwrite the schema, if it already exists? [YES|no]:
****** Creating REGIONS table ....
Table REGIONSは作成されました。
INDEX REG_ID_PKは作成されました。
Table REGIONSが変更されました。
****** Creating COUNTRIES table ....
Table COUNTRIESは作成されました。
[中略]
コミットが完了しました。
Installationverification
___________________________
Verification:
Table provided actual
______________ ___________ _________
regions 5 5
countries 25 25
departments 27 27
locations 23 23
employees 107 107
jobs 19 19
job_history 10 10
Thankyou!
___________________________________________________________
The installation of the sample schema is now finished.
Please check the installation verification output above.
You will now be disconnected from the database.
Thank you for using Oracle Database!
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09から切断されました
%
SQL> begin
ords_admin.enable_schema(
p_enabled => true
,p_schema => 'HR'
);
end;
/
PL/SQLプロシージャが正常に完了しました。
SQL> exit
ユーザーHRで接続し直し、以下のスクリプトを実行して、スキーマHRにある表とビューをすべてAutoRESTを有効にします。
% sql hr/********@localhost/freepdb1
SQLcl: 水 4月 03 17:58:16 2024のリリース23.4 Production
Copyright (c) 1982, 2024, Oracle. All rights reserved.
接続先:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL> set serveroutput on
SQL> begin
for r in (
select object_type, object_name from user_objects where object_type in ('TABLE','VIEW')
)
loop
begin
ords.enable_object(
p_enabled => true
,p_object => r.object_name
,p_object_type => r.object_type
,p_object_alias => lower(r.object_name)
,p_auto_rest_auth => true
);
dbms_output.put_line(r.object_name || ' is rest enabled.');
commit;
end;
end loop;
end;
/
REGIONS is rest enabled.
COUNTRIES is rest enabled.
LOCATIONS is rest enabled.
DEPARTMENTS is rest enabled.
JOBS is rest enabled.
EMPLOYEES is rest enabled.
JOB_HISTORY is rest enabled.
EMP_DETAILS_VIEW is rest enabled.
PL/SQLプロシージャが正常に完了しました。
SQL> exit
% docker exec -it graphql-db bash
bash-4.4$ /usr/local/bin/ords --config /etc/ords/config serve
ORDS: Release 23.4 Production on Thu Apr 04 12:55:22 2024
Copyright (c) 2010, 2024, Oracle.
Configuration:
/etc/ords/config/
2024-04-04T12:55:24.382Z INFO HTTP and HTTP/2 cleartext listening on host: 0.0.0.0 port: 8181
2024-04-04T12:55:24.529Z INFO Disabling document root because the specified folder does not exist: /etc/ords/config/global/doc_root
2024-04-04T12:55:24.534Z INFO Default forwarding from / to contextRoot configured.
2024-04-04T12:56:01.227Z INFO Configuration properties for: |default|lo|
db.servicename=freepdb1
db.invalidPoolTimeout=5s
java.specification.version=17
conf.use.wallet=true
sun.jnu.encoding=ANSI_X3.4-1968
java.class.path=/opt/oracle/ords/ords.war
java.vm.vendor=GraalVM Community
sun.arch.data.model=64
nashorn.args=--no-deprecation-warning
java.vendor.url=https://www.graalvm.org/
resource.templates.enabled=false
user.timezone=UTC
db.port=1521
debug.printDebugToScreen=true
java.vm.specification.version=17
os.name=Linux
sun.java.launcher=SUN_STANDARD
user.country=US
sun.boot.library.path=/usr/lib64/graalvm/graalvm22-ce-java17/lib
sun.java.command=/opt/oracle/ords/ords.war --config /etc/ords/config serve
jdk.debug=release
sun.cpu.endian=little
user.home=/home/oracle
oracle.dbtools.launcher.executable.jar.path=/opt/oracle/ords/ords.war
user.language=en
sun.stderr.encoding=ANSI_X3.4-1968
java.specification.vendor=Oracle Corporation
java.version.date=2023-07-18
database.api.enabled=true
java.home=/usr/lib64/graalvm/graalvm22-ce-java17
db.username=ORDS_PUBLIC_USER
file.separator=/
java.vm.compressedOopsMode=Zero based
jdk.internal.vm.ci.enabled=true
line.separator=
sun.stdout.encoding=ANSI_X3.4-1968
restEnabledSql.active=true
java.specification.name=Java Platform API Specification
java.vm.specification.vendor=Oracle Corporation
feature.sdw=true
java.awt.headless=true
db.hostname=localhost
db.password=******
sun.management.compiler=HotSpot 64-Bit Tiered Compilers
security.requestValidationFunction=ords_util.authorize_plsql_gateway
java.runtime.version=17.0.8+7-jvmci-22.3-b22
user.name=oracle
path.separator=:
standalone.http.port=8181
os.version=6.5.0-15-generic
java.runtime.name=OpenJDK Runtime Environment
file.encoding=ANSI_X3.4-1968
java.vm.name=OpenJDK 64-Bit Server VM
java.vendor.version=GraalVM CE 22.3.3
java.vendor.url.bug=https://github.com/oracle/graal/issues
java.io.tmpdir=/tmp
oracle.dbtools.cmdline.ShellCommand=ords
java.version=17.0.8
user.dir=/home/oracle
os.arch=amd64
java.vm.specification.name=Java Virtual Machine Specification
jdbc.MaxLimit=30
oracle.dbtools.cmdline.home=/opt/oracle/ords
native.encoding=ANSI_X3.4-1968
java.library.path=/usr/java/packages/lib:/usr/lib64:/lib64:/lib:/usr/lib
java.vendor=GraalVM Community
java.vm.info=mixed mode, sharing
java.vm.version=17.0.8+7-jvmci-22.3-b22
sun.io.unicode.encoding=UnicodeLittle
jdbc.InitialLimit=10
db.connectionType=basic
java.class.version=61.0
2024-04-04T12:56:58.238Z INFO
Mapped local pools from /etc/ords/config/databases:
/ords/ => default => VALID
2024-04-04T12:56:58.970Z INFO Oracle REST Data Services initialized
Oracle REST Data Services version : 23.4.0.r3461619
Oracle REST Data Services server info: jetty/10.0.18
Oracle REST Data Services java info: OpenJDK 64-Bit Server VM 17.0.8+7-jvmci-22.3-b22
