OCIクラウド移行ガイドとは
オンプレミスやAWSなど、複数のプラットフォームからOracle Cloud Infrastructureへの移行プロジェクトに取り組んでいるクラウドエンジニア（@araidon,@kazunishi,@yama6,@tktk2712,@ritokuna,@nomu_kyou）による、OCI移行手順をまとめたシリーズ記事です。
各回、サンプルワークロードから対象サービスを取り上げ、移行手順をガイドいたします。
まとめ記事は以下になります。
移行するサービス：RDS for PostgreSQL
今回、移行対象とするのはAmazon RDS for PostgreSQLです。
Autonomous Databaseで利用可能なDBMS_CLOUD_MIGRATIONパッケージを用いて、Amazon RDS for PostgreSQLをAutonomous Databaseに移行する手順を解説します。
DBMS_CLOUD_MIGRATIONパッケージを使用すると、PostgreSQLからOracle SQLへのSQLコードの変換が容易になります。
このパッケージからはいくつかのプロシージャが提供されています。今回は、その中でもDBMS_CLOUD_MIGRATION.MIGRATE_FILEプロシージャを使用します。DBMS_CLOUD_MIGRATION.MIGRATE_FILEプロシージャは、オブジェクト・ストレージのPostgreSQLファイル内のSQL文を変換し、Oracle SQLを含む新しいファイルを生成します。
一言で言うと、RDS for PostgreSQLでDumpしたPostgreSQLの.sqlファイルをOracleの.sqlファイルに変換してくれるという代物です。
DBMS_CLOUD_MIGRATION.MIGRATE_FILEプロシージャ
このプロシージャにはDBエンジン差異による一部制約があるため、生成されたファイルに記載されたSQL文を実行し、DBMS_CLOUD_MIGRATION.MIGRATE_FILEプロシージャで変換されたSQL文の整合性を検証します。
Oracle以外のSQLからOracle SQLへの移行および変換の制限
検証データ
移行対象とするサンプルデータとして、PostgreSQL Tutorialが提供するDVDレンタルデータベースを使用します。
このデータベースには下記オブジェクトが含まれています。
| No | オブジェクト | 数量 |
|---|---|---|
| 1 | sequences | 13 |
| 2 | tables | 15 |
| 3 | trigger | 15 |
| 4 | views | 7 |
| 5 | functions | 9 |
| 6 | domain | 1 |
| - | SUM | 60 |
検証 Summary
検証手順が長くなったため、先に検証結果を記載します。
| No | オブジェクト | 全数量 | 改修数量 | 結果 | コメント |
|---|---|---|---|---|---|
| 1 | sequences | 13 | 0 | 🙆♂️ | --- |
| 2 | tables | 15 | 2 | ⚠️ | address.phone,film.fulltext 一部改修 |
| 3 | trigger | 15 | 1 | ⚠️ | film_fulltext_trigger 書き直し |
| 4 | views | 7 | 2 | ⚠️ | customer_list,nicer_but_slower_film_list 一部改修 |
| 5 | functions | 9 | 9 | ❌ | GET_CUSTOMER_BALANCE,REWARDS_REPORT 未対応 |
| 6 | domain | 1 | 1 | ❌ | domainに直接対応するオブジェクトがないため、OracleDBのtypeで実現 |
全60オブジェクトのうち、75%にあたる45オブジェクトについては変換されたSQL文のまま実行することができましたが、残り25%のオブジェクトについては改修もしくは1から書き直しが必要となりました。
functionsについては9本中、9本が書き直しとなりました。
うち、の2つについてはサンプルデータ起因でPostgreSQL側でもうまく動作確認ができなかったため改修検証の対象外としました。
前提条件
DBバージョン
RDS for PostgreSQL：14.11
Autonomous Database：19c
SQLの実行について
実行方法
検証目的のため、Oracle SQLに変換された.sqlファイルから、該当部分のSQL文を逐一抜き出して実行しています。本番の移行方法で採択されなさそうな回りくどい方法となっている点、SQLの実行順がDumpfileの記載順となっていない点をご容赦ください。
オブジェクトの作成順
オブジェクト作成の実行順は下記の通りです。
SEQUENCE → TYPE(domain) → TABLE → INDEX → TRIGGER → VIEW → FUNCTION
スキーマ名の変更 "public"→"postgres"
サンプルデータのスキーマ名となっている"public"がプロシージャで変換されません。"public"はOracleの予約語のため、そのまま実行するとエラーが発生します。そのため、Oracle Databaseで"postgres"というユーザーを作成した上で、Dumpfileでpublicスキーマを指定している部分はpostgresに置換して実行しています。
移行手順
- EC2作成
- RDS for PostgreSQL作成
- サンプルデータのインポート
- サンプルデータのエクスポート
- Object Storageの作成
- Autonomous Databaseの作成
- 資格証明の作成
- DBMS_CLOUD_MIGRATION.MIGRATE_FILEプロシージャの実行
- 変換されたSQL文の実行
- SEQUENCE
- TABLE
- INDEX
- TRIGGER
- VIEW
- TYPE
- FUNCTION
- データロード
- 動作検証
1. EC2作成
RDS操作用のインスタンスとして、Amazon Linux2023でEC2を作成します。
作成方法は下記の記事等を参考にしてください。
EC2へはCloudShellから接続し、postgresql15をインストールしておきます。
$ sudo dnf install postgresql15
2. RDS for PostgreSQL作成
下記手順に概ね沿い、RDS for PostgreSQLを作成します。
EC2との接続オプションを選ぶと、自動的にセキュリティグループを設定してくれて便利ですね。
下記コマンドでEC2からRDSへ接続できれば成功です。
$ psql --host=test-postgres.xxxxxx.ap-northeast-1.rds.amazonaws.com --port=5432 --dbname=postgres --username=postgres
3. サンプルデータのインポート
下記サイトよりサンプルデータをダウンロードします。
今回の検証では、DVD レンタル データベースを使用します。
https://neon.tech/postgresql/postgresql-getting-started/postgresql-sample-database
一度ローカルPCにデータをダウンロードし、cloudShellにアップロードします。
scpコマンドを使用し、CloudShellからEC2へファイルを転送します。
$ scp -i posgre-test.pem dvdrental.zip ec2-user@XXX.XX.XXX.XXX:/home/ec2-user/
dvdrental.zip 100% 538KB 66.7MB/s 00:00
RDSへ接続し、データのインポート前にdvdrentalという名前のDatabaseを作ります。
$ psql --host=test-postgres.xxxxxx.ap-northeast-1.rds.amazonaws.com --port=5432 --dbname=postgres --username=postgres
postgres=>create database dvdrental;
exitコマンドでEC2に戻り、pg_restoreコマンドでzipファイルからDBを作成します。
$ pg_restore -U postgres -h test-postgres.xxxxxx.ap-northeast-1.rds.amazonaws.com -d dvdrental -Ft dvdrental.tar -v
再度RDSへ接続し、インポートデータを確認します。
$ psql --host=test-postgres.xxxxxx.ap-northeast-1.rds.amazonaws.com --port=5432 --dbname=postgres --username=postgres
postgres=> \c dvdrental
dvdrental=> dvdrental=#
dvdrental-> \dt
List of relations
Schema | Name | Type | Owner
--------+---------------+-------+----------
public | actor | table | postgres
public | address | table | postgres
public | category | table | postgres
public | city | table | postgres
public | country | table | postgres
public | customer | table | postgres
public | film | table | postgres
public | film_actor | table | postgres
public | film_category | table | postgres
public | inventory | table | postgres
public | language | table | postgres
public | payment | table | postgres
public | rental | table | postgres
public | staff | table | postgres
public | store | table | postgres
(15 rows)
4. サンプルデータのエクスポート
EC2上からpg_dumpコマンドで、メタデータをエクスポートします。
$ pg_dump -U postgres -h test-postgres.xxxxxx.ap-northeast-1.rds.amazonaws.com -s -E 'UTF8' -d dvdrental -f dvdrental_metadata.sql
データ本体は、のちにSQL Loaderでインポートします。
RDSに接続し、テーブル単位でcsvファイルに出力します。
dvdrental-> \copy ACTOR to ACTOR.csv with csv delimiter ',' force quote * null as '' header;
COPY 200
dvdrental-> \copy ADDRESS to ADDRESS.csv with csv delimiter ',' force quote * null as '' header;
COPY 603
dvdrental-> \copy CATEGORY to CATEGORY.csv with csv delimiter ',' force quote * null as '' header;
COPY 16
dvdrental-> \copy CITY to CITY.csv with csv delimiter ',' force quote * null as '' header;
COPY 600
dvdrental-> \copy COUNTRY to COUNTRY.csv with csv delimiter ',' force quote * null as '' header;
COPY 109
dvdrental-> \copy CUSTOMER to CUSTOMER.csv with csv delimiter ',' force quote * null as '' header;
COPY 599
dvdrental-> \copy FILM to FILM.csv with csv delimiter ',' force quote * null as '' header;
COPY 1000
dvdrental-> \copy FILM_ACTOR to FILM_ACTOR.csv with csv delimiter ',' force quote * null as '' header;
COPY 5462
dvdrental-> \copy FILM_CATEGORY to FILM_CATEGORY.csv with csv delimiter ',' force quote * null as '' header;
COPY 1000
dvdrental-> \copy INVENTORY to INVENTORY.csv with csv delimiter ',' force quote * null as '' header;
COPY 4581
dvdrental-> \copy LANGUAGE to LANGUAGE.csv with csv delimiter ',' force quote * null as '' header;
COPY 6
dvdrental-> \copy PAYMENT to PAYMENT.csv with csv delimiter ',' force quote * null as '' header;
COPY 14596
dvdrental-> \copy RENTAL to RENTAL.csv with csv delimiter ',' force quote * null as '' header;
COPY 16044
dvdrental-> \copy STAFF to STAFF.csv with csv delimiter ',' force quote * null as '' header;
COPY 2
dvdrental-> \copy STORE to STORE.csv with csv delimiter ',' force quote * null as '' header;
COPY 2
上記実行すると、EC2上にファイルが生成されます。
先ほどご紹介したscpコマンド等を使い、何らかの手段でこれらのエクスポートファイルをローカルPCに保存します。
5. Object Storageの作成
下記手順に沿い、Object Storageを作成します。
4でエクスポートしたデータ群をアップロードします。
アップロード後、オブジェクト一覧右のボタンを押下し、「オブジェクト詳細」の表示を押下してこれをコピーします。
のちのプロシージャ実行にて、オブジェクトのURIが必要となります。
6. Autonomous Databaseの作成
下記手順に従ってAutonomous Data Warehouseを作成します。
ワークロード・タイプには、トランザクション・ワークロードを対象とするAutonomous Transaction Processingを選択します。
データベース・バージョンは19cを選択しました。
ネットワーク・アクセスは、「すべての場所からのセキュア・アクセス」を選択ました。
Autonomous Databaseの作成 ボタンを押下し、プロビジョニングの完了を確認します。
Autonomous Databaseの作成作業は以上です。
Autonomous Databaseへの接続
Autonomous Databaseの詳細 画面から「データベース接続」を選択し、クライアント資格証明(ウォレット)をダウンロードします。
ウォレット保存先ディレクトリとして"/network/admin"を作成した上で、これをcloudshellにアップロードして展開します。
$ mkdir -p ~/network/admin
TNS_ADMIN 環境変数にウォレット保存先のパスを設定します。
$ export TNS_ADMIN=~/network/admin
viコマンドなどを使用し、sqlnet.oraにこの環境変数を設定します。
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY=$TNS_ADMIN)))
環境変数”ORACLE_HOME”を設定します。
$ export ORACLE_HOME=~
sqlplusから接続します。
$ sqlplus admin/<password>@adb4psql_tp
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 27 12:58:04 2024
Version 19.10.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Last Successful login time: Fri Dec 27 2024 07:28:03 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.1.0
SQL>
7. 資格証明の作成
5で作成したObject Storageへ、6で作成したAutonomous Databaseから操作を行うために権限を付与します。今回は、Object Storageへの操作権限を持つユーザーの認証トークンを使用して権限を付与しました。
リソース・プリンシパル等でも設定が可能です。詳しくは下記をご参照ください。
認証トークンの取得
OCIコンソール画面右上のユーザーアイコンを押下し、「ユーザー設定」を押下します。
画面遷移後、画面左下「リソース」のナビゲーションペインから「認証トークン」を選択します。
トークンの作成ボタンを押下し、説明を記述後、「トークンの作成」ボタンを押下します。
遷移後の画面で表示される認証トークンの文字列をコピーします。
認証トークンの設定
ユーザーの認証トークンをAutonomous Databaseに設定します。
権限付与には、DBMS_CLOUDパッケージのCREATE_CREDENTIALプロシージャを使用します。
SQL> BEGIN DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'OCI_NATIVE_CRED',
username => '<ユーザー名>',
password => '<認証トークンの文字列>'
);
END;
/
PL/SQL procedure successfully completed.
これでAutonomous DatabaseにObject Storageへの操作権限が付与されました。
6で作成したバケットのURIを指定して、下記SQL文を実行した結果、アップロードしたファイル名が表示されれば権限付与は成功です。
SQL> SELECT * FROM DBMS_CLOUD.LIST_OBJECTS(
NULL,
'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/<NameSpace名>/b/<Bucket名>/o/'
);
/
オブジェクトストレージへの DBMS_CLOUD アクセスのトラブルシューティングには下記の記事をご参照ください。
8. DBMS_CLOUD_MIGRATION.MIGRATE_FILEプロシージャの実行
本題である、PostgreSQLからOracleへSQLを変換するMIGRATE_FILEプロシージャを実行します。
実行前のObject Storageは、先ほどアップロードした"dvdrental_metadata.sql"のみ格納されている状態です。
6で取得したURIをlocation_uriに指定し、下記のようにプロシージャを実行します。
SQL> BEGIN DBMS_CLOUD_MIGRATION.MIGRATE_FILE (
credential_name => 'OCI_NATIVE_CRED',
location_uri => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/<NameSpace名>/b/<Bucket名>/o/dvdrental_metadata.sql',
source_db => 'POSTGRES'
);
END;
/
PL/SQL procedure successfully completed.
実行後、"dvdrental_metadata_oracle.sql"というファイルが追加されていることがわかります。
これがMIGRATE_FILEプロシージャによりOracle SQLへ変換された.sqlファイルとなります。
このファイルに記載されたSQL文を実行し、Autonomous Database上でデータベースを構築します。
9. 変換されたSQL文の実行
出力されたSQL文から、下記順でオブジェクトを作成していきます。
SEQUENCE → TYPE(domain) → TABLE → INDEX → TRIGGER → VIEW → FUNCTION
冒頭のサマリ通り、一部オブジェクトについては改修または書き直しが必要となったため、該当オブジェクトについては注釈を示します。ここまでの手順はdvdrentalのデータベースを使用すれば共通のアウトプットとなるので、そちらと見比べながら実行してください。
9-1. Database Actionsの開始
以降のSQL文はDatabase Actionsを利用してブラウザから実行していきます。
Autonomous Databaseの詳細画面から、「データベース・アクション」を押下してください。
DBにログインされていれば、別タブで「Oracle Database Actions」が立ち上がります。
ログインされていない場合は、Autonomous Database作成時に設定したパスワードを利用し、ユーザー名：adminで認証します。
SQLの画面からSQL文を実行していきます。
9-2. ユーザ作成
CREATE USER postgres IDENTIFIED BY <パスワード>;
ALTER USER postgres quota unlimited on DATA;
postgresというユーザを作成します。
また、postgresに対するリソースの制限を解除します。
9-3. シーケンスの作成
シーケンスを作成します。
"dvdrental_metadata_oracle.sql"におけるユーザ名を"public"に置換し、実行します。
CREATE SEQUENCE postgres.customer_customer_id_seq
START WITH 1
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
CACHE 2;
残り12シーケンスについても同様に実行します。
シーケンス作成
CREATE SEQUENCE postgres.actor_actor_id_seq
START WITH 1
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
CACHE 2;
CREATE SEQUENCE postgres.category_category_id_seq
START WITH 1
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
CACHE 2;
CREATE SEQUENCE postgres.film_film_id_seq
START WITH 1
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
CACHE 2;
CREATE SEQUENCE postgres.address_address_id_seq
START WITH 1
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
CACHE 2;
CREATE SEQUENCE postgres.city_city_id_seq
START WITH 1
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
CACHE 2;
CREATE SEQUENCE postgres.country_country_id_seq
START WITH 1
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
CACHE 2;
CREATE SEQUENCE postgres.inventory_inventory_id_seq
START WITH 1
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
CACHE 2;
CREATE SEQUENCE postgres.language_language_id_seq
START WITH 1
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
CACHE 2;
CREATE SEQUENCE postgres.payment_payment_id_seq
START WITH 1
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
CACHE 2;
CREATE SEQUENCE postgres.rental_rental_id_seq
START WITH 1
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
CACHE 2;
CREATE SEQUENCE postgres.staff_staff_id_seq
START WITH 1
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
CACHE 2;
CREATE SEQUENCE postgres.store_store_id_seq
START WITH 1
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
CACHE 2;
実行後、下記SQL文を実行し、シーケンスが13個確認できれば成功です。
select object_name,object_type,status from dba_objects where owner='POSTGRES' and object_type='SEQUENCE' order by 1;
9-4. タイプの作成
タイプ（PostgreSQLで言うところのドメイン）を作成します。
CREATE or REPLACE TYPE postgres."year" AS OBJECT (year_type NUMBER(10));
9-5. テーブルの作成
テーブルを作成します。
"dvdrental_metadata_oracle.sql"におけるユーザ名を"public"に置換し、実行します。
下記テーブルについては、のちに実データをロードする際にエラーが発生します。
これは実データでNULL値が入っているためです。ALTER文で部分的にNULL値を許可します。
CREATE TABLE postgres.address (
address_id NUMBER(10) DEFAULT postgres.address_address_id_seq.nextval NOT NULL,
address VARCHAR2(50) NOT NULL,
address2 VARCHAR2(50),
district VARCHAR2(20) NOT NULL,
city_id NUMBER(5) NOT NULL,
postal_code VARCHAR2(10),
phone VARCHAR2(20) NOT NULL,
last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL
);
alter table postgres.address modify phone NULL;
CREATE TABLE postgres.film (
film_id NUMBER(10) DEFAULT postgres.film_film_id_seq.nextval NOT NULL,
title VARCHAR2(255) NOT NULL,
description VARCHAR2(4000),
release_year postgres."year",
language_id NUMBER(5) NOT NULL,
rental_duration NUMBER(5) DEFAULT 3 NOT NULL,
rental_rate NUMBER(4,2) DEFAULT 4.99 NOT NULL,
length NUMBER(5),
replacement_cost NUMBER(5,2) DEFAULT 19.99 NOT NULL,
rating VARCHAR2(4000) CHECK (rating IN ('G','PG','PG-13','R','NC-17')) ,
last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL,
special_features CLOB,
fulltext CLOB NOT NULL
);
alter table postgres.film modify fulltext NULL;
残りの13テーブルについても同様にCRETAE文を実行します。
テーブル作成
CREATE TABLE postgres.customer (
customer_id NUMBER(10) DEFAULT postgres.customer_customer_id_seq.nextval NOT NULL,
store_id NUMBER(5) NOT NULL,
first_name VARCHAR2(45) NOT NULL,
last_name VARCHAR2(45) NOT NULL,
email VARCHAR2(50),
address_id NUMBER(5) NOT NULL,
activebool CHAR(1) DEFAULT 1 NOT NULL,
create_date date DEFAULT CAST((CAST(SYSDATE AS date)) AS date) NOT NULL,
last_update timestamp DEFAULT SYSTIMESTAMP,
active NUMBER(10)
);
CREATE TABLE postgres.actor (
actor_id NUMBER(10) DEFAULT postgres.actor_actor_id_seq.nextval NOT NULL,
first_name VARCHAR2(45) NOT NULL,
last_name VARCHAR2(45) NOT NULL,
last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL
);
CREATE TABLE postgres.category (
category_id NUMBER(10) DEFAULT postgres.category_category_id_seq.nextval NOT NULL,
name VARCHAR2(25) NOT NULL,
last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL
);
CREATE TABLE postgres.film_actor (
actor_id NUMBER(5) NOT NULL,
film_id NUMBER(5) NOT NULL,
last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL
);
CREATE TABLE postgres.film_category (
film_id NUMBER(5) NOT NULL,
category_id NUMBER(5) NOT NULL,
last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL
);
CREATE TABLE postgres.city (
city_id NUMBER(10) DEFAULT postgres.city_city_id_seq.nextval NOT NULL,
city VARCHAR2(50) NOT NULL,
country_id NUMBER(5) NOT NULL,
last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL
);
CREATE TABLE postgres.country (
country_id NUMBER(10) DEFAULT postgres.country_country_id_seq.nextval NOT NULL,
country VARCHAR2(50) NOT NULL,
last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL
);
CREATE TABLE postgres.inventory (
inventory_id NUMBER(10) DEFAULT postgres.inventory_inventory_id_seq.nextval NOT NULL,
film_id NUMBER(5) NOT NULL,
store_id NUMBER(5) NOT NULL,
last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL
);
CREATE TABLE postgres.language (
language_id NUMBER(10) DEFAULT postgres.language_language_id_seq.nextval NOT NULL,
name character(20) NOT NULL,
last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL
);
CREATE TABLE postgres.payment (
payment_id NUMBER(10) DEFAULT postgres.payment_payment_id_seq.nextval NOT NULL,
customer_id NUMBER(5) NOT NULL,
staff_id NUMBER(5) NOT NULL,
rental_id NUMBER(10) NOT NULL,
amount NUMBER(5,2) NOT NULL,
payment_date timestamp NOT NULL
);
CREATE TABLE postgres.rental (
rental_id NUMBER(10) DEFAULT postgres.rental_rental_id_seq.nextval NOT NULL,
rental_date timestamp NOT NULL,
inventory_id NUMBER(10) NOT NULL,
customer_id NUMBER(5) NOT NULL,
return_date timestamp,
staff_id NUMBER(5) NOT NULL,
last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL
);
CREATE TABLE postgres.store (
store_id NUMBER(10) DEFAULT postgres.store_store_id_seq.nextval NOT NULL,
manager_staff_id NUMBER(5) NOT NULL,
address_id NUMBER(5) NOT NULL,
last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL
);
CREATE TABLE postgres.staff (
staff_id NUMBER(10) DEFAULT postgres.staff_staff_id_seq.nextval NOT NULL,
first_name VARCHAR2(45) NOT NULL,
last_name VARCHAR2(45) NOT NULL,
address_id NUMBER(5) NOT NULL,
email VARCHAR2(50),
store_id NUMBER(5) NOT NULL,
active CHAR(1) DEFAULT 1 NOT NULL,
username VARCHAR2(16) NOT NULL,
password VARCHAR2(40),
last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL,
picture BLOB
);
実行後、下記SQL文を実行し、テーブルが15個確認できれば成功です。
select object_name,object_type,status from dba_objects where owner='POSTGRES' and object_type='TABLE' order by 1;
9-6. キーの作成
主キー、外部キーを作成します。
主キー作成
ALTER TABLE postgres.actor ADD CONSTRAINT actor_pkey PRIMARY KEY (actor_id);ALTER TABLE postgres.address
ADD CONSTRAINT address_pkey PRIMARY KEY (address_id);
ALTER TABLE postgres.category
ADD CONSTRAINT category_pkey PRIMARY KEY (category_id);
ALTER TABLE postgres.city
ADD CONSTRAINT city_pkey PRIMARY KEY (city_id);
ALTER TABLE postgres.country
ADD CONSTRAINT country_pkey PRIMARY KEY (country_id);
ALTER TABLE postgres.customer
ADD CONSTRAINT customer_pkey PRIMARY KEY (customer_id);
ALTER TABLE postgres.film_actor
ADD CONSTRAINT film_actor_pkey PRIMARY KEY (actor_id, film_id);
ALTER TABLE postgres.film_category
ADD CONSTRAINT film_category_pkey PRIMARY KEY (film_id, category_id);
ALTER TABLE postgres.film
ADD CONSTRAINT film_pkey PRIMARY KEY (film_id);
ALTER TABLE postgres.inventory
ADD CONSTRAINT inventory_pkey PRIMARY KEY (inventory_id);
ALTER TABLE postgres.language
ADD CONSTRAINT language_pkey PRIMARY KEY (language_id);
ALTER TABLE postgres.payment
ADD CONSTRAINT payment_pkey PRIMARY KEY (payment_id);
ALTER TABLE postgres.rental
ADD CONSTRAINT rental_pkey PRIMARY KEY (rental_id);
ALTER TABLE postgres.staff
ADD CONSTRAINT staff_pkey PRIMARY KEY (staff_id);
ALTER TABLE postgres.store
ADD CONSTRAINT store_pkey PRIMARY KEY (store_id);
実行後、15個の主キーが確認できれば成功です。
select constraint_name,constraint_type,table_name,status from dba_constraints where owner='POSTGRES' and constraint_type='P' order by 1;
外部キーの作成
ALTER TABLE postgres.customer
ADD CONSTRAINT customer_address_id_fkey FOREIGN KEY (address_id) REFERENCES postgres.address(address_id) ON DELETE CASCADE;
ALTER TABLE postgres.film_actor
ADD CONSTRAINT film_actor_actor_id_fkey FOREIGN KEY (actor_id) REFERENCES postgres.actor(actor_id) ON DELETE CASCADE;
ALTER TABLE postgres.film_actor
ADD CONSTRAINT film_actor_film_id_fkey FOREIGN KEY (film_id) REFERENCES postgres.film(film_id) ON DELETE CASCADE;
ALTER TABLE postgres.film_category
ADD CONSTRAINT film_category_category_id_fkey FOREIGN KEY (category_id) REFERENCES postgres.category(category_id) ON DELETE CASCADE;
ALTER TABLE postgres.film_category
ADD CONSTRAINT film_category_film_id_fkey FOREIGN KEY (film_id) REFERENCES postgres.film(film_id) ON DELETE CASCADE;
ALTER TABLE postgres.film
ADD CONSTRAINT film_language_id_fkey FOREIGN KEY (language_id) REFERENCES postgres.language(language_id) ON DELETE CASCADE;
ALTER TABLE postgres.address
ADD CONSTRAINT fk_address_city FOREIGN KEY (city_id) REFERENCES postgres.city(city_id);
ALTER TABLE postgres.city
ADD CONSTRAINT fk_city FOREIGN KEY (country_id) REFERENCES postgres.country(country_id);
ALTER TABLE postgres.inventory
ADD CONSTRAINT inventory_film_id_fkey FOREIGN KEY (film_id) REFERENCES postgres.film(film_id) ON DELETE CASCADE;
ALTER TABLE postgres.payment
ADD CONSTRAINT payment_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES postgres.customer(customer_id) ON DELETE CASCADE;
ALTER TABLE postgres.payment
ADD CONSTRAINT payment_rental_id_fkey FOREIGN KEY (rental_id) REFERENCES postgres.rental(rental_id) ON DELETE SET NULL;
ALTER TABLE postgres.payment
ADD CONSTRAINT payment_staff_id_fkey FOREIGN KEY (staff_id) REFERENCES postgres.staff(staff_id) ON DELETE CASCADE;
ALTER TABLE postgres.rental
ADD CONSTRAINT rental_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES postgres.customer(customer_id) ON DELETE CASCADE;
ALTER TABLE postgres.rental
ADD CONSTRAINT rental_inventory_id_fkey FOREIGN KEY (inventory_id) REFERENCES postgres.inventory(inventory_id) ON DELETE CASCADE;
ALTER TABLE postgres.rental
ADD CONSTRAINT rental_staff_id_key FOREIGN KEY (staff_id) REFERENCES postgres.staff(staff_id);
ALTER TABLE postgres.staff
ADD CONSTRAINT staff_address_id_fkey FOREIGN KEY (address_id) REFERENCES postgres.address(address_id) ON DELETE CASCADE;
ALTER TABLE postgres.store
ADD CONSTRAINT store_address_id_fkey FOREIGN KEY (address_id) REFERENCES postgres.address(address_id) ON DELETE CASCADE;
ALTER TABLE postgres.store
ADD CONSTRAINT store_manager_staff_id_fkey FOREIGN KEY (manager_staff_id) REFERENCES postgres.staff(staff_id) ON DELETE CASCADE;
実行後、18個の外部キーが確認できれば成功です。
select constraint_name,constraint_type,table_name,status from dba_constraints where owner='POSTGRES' and constraint_type='R' ORDER by 1;
9-7. インデックスの作成
インデックスを作成します。
インデックス作成
CREATE INDEX postgres.idx_actor_last_name ON postgres.actor (last_name);
CREATE INDEX postgres.idx_fk_address_id ON postgres.customer (address_id);
CREATE INDEX postgres.idx_fk_city_id ON postgres.address (city_id);
CREATE INDEX postgres.idx_fk_country_id ON postgres.city (country_id);
CREATE INDEX postgres.idx_fk_customer_id ON postgres.payment (customer_id);
CREATE INDEX postgres.idx_fk_film_id ON postgres.film_actor (film_id);
CREATE INDEX postgres.idx_fk_inventory_id ON postgres.rental (inventory_id);
CREATE INDEX postgres.idx_fk_language_id ON postgres.film (language_id);
CREATE INDEX postgres.idx_fk_rental_id ON postgres.payment (rental_id);
CREATE INDEX postgres.idx_fk_staff_id ON postgres.payment (staff_id);
CREATE INDEX postgres.idx_fk_store_id ON postgres.customer (store_id);
CREATE INDEX postgres.idx_last_name ON postgres.customer (last_name);
CREATE INDEX postgres.idx_store_id_film_id ON postgres.inventory (store_id, film_id);
CREATE INDEX postgres.idx_title ON postgres.film (title);
CREATE UNIQUE INDEX postgres.idx_unq_manager_staff_id ON postgres.store (manager_staff_id);
CREATE UNIQUE INDEX postgres.idx_unq_rental_rental_date_inventory_id_customer_id ON postgres.rental (rental_date, inventory_id, customer_id);
実行後、16個のインデックスが確認できれば成功です。
select index_name,index_type,table_name,status from dba_indexes where owner='POSTGRES' and index_name like 'IDX_%' order by 1;
9-8. トリガーの作成
トリガーを作成します。
トリガー作成
CREATE OR REPLACE TRIGGER postgres.last_updated BEFORE UPDATE ON postgres.actor FOR EACH ROW
BEGIN
:NEW.last_update := SYSTIMESTAMP;
END;
CREATE OR REPLACE TRIGGER postgres.last_updated_1 BEFORE UPDATE ON postgres.address FOR EACH ROW
BEGIN
:NEW.last_update := SYSTIMESTAMP;
END;
CREATE OR REPLACE TRIGGER postgres.last_updated_2 BEFORE UPDATE ON postgres.category FOR EACH ROW
BEGIN
:NEW.last_update := SYSTIMESTAMP;
END;
CREATE OR REPLACE TRIGGER postgres.last_updated_3 BEFORE UPDATE ON postgres.city FOR EACH ROW
BEGIN
:NEW.last_update := SYSTIMESTAMP;
END;
CREATE OR REPLACE TRIGGER postgres.last_updated_4 BEFORE UPDATE ON postgres.country FOR EACH ROW
BEGIN
:NEW.last_update := SYSTIMESTAMP;
END;
CREATE OR REPLACE TRIGGER postgres.last_updated_5 BEFORE UPDATE ON postgres.customer FOR EACH ROW
BEGIN
:NEW.last_update := SYSTIMESTAMP;
END;
CREATE OR REPLACE TRIGGER postgres.last_updated_6 BEFORE UPDATE ON postgres.film FOR EACH ROW
BEGIN
:NEW.last_update := SYSTIMESTAMP;
END;
CREATE OR REPLACE TRIGGER postgres.last_updated_7 BEFORE UPDATE ON postgres.film_actor FOR EACH ROW
BEGIN
:NEW.last_update := SYSTIMESTAMP;
END;
CREATE OR REPLACE TRIGGER postgres.last_updated_8 BEFORE UPDATE ON postgres.film_category FOR EACH ROW
BEGIN
:NEW.last_update := SYSTIMESTAMP;
END;
CREATE OR REPLACE TRIGGER postgres.last_updated_9 BEFORE UPDATE ON postgres.inventory FOR EACH ROW
BEGIN
:NEW.last_update := SYSTIMESTAMP;
END;
CREATE OR REPLACE TRIGGER postgres.last_updated_10 BEFORE UPDATE ON postgres.language FOR EACH ROW
BEGIN
:NEW.last_update := SYSTIMESTAMP;
END;
CREATE OR REPLACE TRIGGER postgres.last_updated_11 BEFORE UPDATE ON postgres.rental FOR EACH ROW
BEGIN
:NEW.last_update := SYSTIMESTAMP;
END;
CREATE OR REPLACE TRIGGER postgres.last_updated_12 BEFORE UPDATE ON postgres.staff FOR EACH ROW
BEGIN
:NEW.last_update := SYSTIMESTAMP;
END;
CREATE OR REPLACE TRIGGER postgres.last_updated_13 BEFORE UPDATE ON postgres.store FOR EACH ROW
BEGIN
:NEW.last_update := SYSTIMESTAMP;
END;
CREATE OR REPLACE TRIGGER postgres.film_fulltext_trigger
BEFORE INSERT OR UPDATE ON postgres.film
FOR EACH ROW
BEGIN
:NEW.fulltext := :NEW.title || ' ' || :NEW.description;
END;
実行後、15個のトリガーが確認できれば成功です。
select object_name,object_type,status from dba_objects where owner='POSTGRES' and object_type='TRIGGER' order by 1;
9-9. ビューの作成
ビューを作成します。
ビュー作成
CREATE OR REPLACE TYPE postgres.group_concat_aggr AS OBJECT (
aggregated_text CLOB,
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT group_concat_aggr) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT group_concat_aggr, value IN CLOB) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT group_concat_aggr, ctx2 IN group_concat_aggr) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(self IN group_concat_aggr, returnValue OUT CLOB, flags IN NUMBER) RETURN NUMBER
);
CREATE OR REPLACE FUNCTION postgres.group_concat (input CLOB) RETURN CLOB AGGREGATE USING group_concat_aggr;
CREATE OR REPLACE TYPE BODY postgres.group_concat_aggr AS
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT group_concat_aggr) RETURN NUMBER IS
BEGIN
sctx := group_concat_aggr(NULL);
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT group_concat_aggr, value IN CLOB) RETURN NUMBER IS
BEGIN
IF self.aggregated_text IS NULL THEN
self.aggregated_text := value;
ELSE
self.aggregated_text := self.aggregated_text || ', ' || value;
END IF;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT group_concat_aggr, ctx2 IN group_concat_aggr) RETURN NUMBER IS
BEGIN
IF ctx2.aggregated_text IS NOT NULL THEN
IF self.aggregated_text IS NULL THEN
self.aggregated_text := ctx2.aggregated_text;
ELSE
self.aggregated_text := self.aggregated_text || ', ' || ctx2.aggregated_text;
END IF;
END IF;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate(self IN group_concat_aggr, returnValue OUT CLOB, flags IN NUMBER) RETURN NUMBER IS
BEGIN
returnValue := self.aggregated_text;
RETURN ODCIConst.Success;
END;
END;
CREATE VIEW postgres.actor_info AS
SELECT a.actor_id,
a.first_name,
a.last_name,
postgres.group_concat(DISTINCT ((CAST((c.name) AS VARCHAR2(4000)) || CAST(': ' AS VARCHAR2(4000))) || ( SELECT postgres.group_concat(CAST((f.title) AS VARCHAR2(4000))) AS group_concat
FROM ((postgres.film f
JOIN postgres.film_category fc_1 ON ((f.film_id = fc_1.film_id)))
JOIN postgres.film_actor fa_1 ON ((f.film_id = fa_1.film_id)))
WHERE ((fc_1.category_id = c.category_id) AND (fa_1.actor_id = a.actor_id))
GROUP BY fa_1.actor_id))) AS film_info
FROM (((postgres.actor a
LEFT JOIN postgres.film_actor fa ON ((a.actor_id = fa.actor_id)))
LEFT JOIN postgres.film_category fc ON ((fa.film_id = fc.film_id)))
LEFT JOIN postgres.category c ON ((fc.category_id = c.category_id)))
GROUP BY a.actor_id, a.first_name, a.last_name;
CREATE VIEW postgres.customer_list AS
SELECT cu.customer_id AS id,
((CAST((cu.first_name) AS VARCHAR2(4000)) || CAST(' ' AS VARCHAR2(4000))) || CAST((cu.last_name) AS VARCHAR2(4000))) AS name,
a.address,
a.postal_code AS "zip code",
a.phone,
city.city,
country.country,
CASE
WHEN cu.activebool= 't' THEN CAST('active' AS VARCHAR2(4000))
ELSE CAST('' AS VARCHAR2(4000))
END AS notes,
cu.store_id AS sid
FROM (((postgres.customer cu
JOIN postgres.address a ON ((cu.address_id = a.address_id)))
JOIN postgres.city ON ((a.city_id = city.city_id)))
JOIN postgres.country ON ((city.country_id = country.country_id)));
CREATE VIEW postgres.film_list AS
SELECT film.film_id AS fid,
film.title,
film.description,
category.name AS category,
film.rental_rate AS price,
film.length,
film.rating,
postgres.group_concat(((CAST((actor.first_name) AS VARCHAR2(4000)) || CAST(' ' AS VARCHAR2(4000))) || CAST((actor.last_name) AS VARCHAR2(4000)))) AS actors
FROM ((((postgres.category
LEFT JOIN postgres.film_category ON ((category.category_id = film_category.category_id)))
LEFT JOIN postgres.film ON ((film_category.film_id = film.film_id)))
JOIN postgres.film_actor ON ((film.film_id = film_actor.film_id)))
JOIN postgres.actor ON ((film_actor.actor_id = actor.actor_id)))
GROUP BY film.film_id, film.title, film.description, category.name, film.rental_rate, film.length, film.rating;
CREATE VIEW postgres.nicer_but_slower_film_list AS
SELECT film.film_id AS fid,
film.title,
film.description,
category.name AS category,
film.rental_rate AS price,
film.length,
film.rating,
postgres.group_concat((((upper(SUBSTR(CAST((actor.first_name) AS VARCHAR2(4000)), 1, 1)) || lower(SUBSTR(CAST((actor.first_name) AS VARCHAR2(4000)), 2))) || upper(SUBSTR(CAST((actor.last_name) AS VARCHAR2(4000)), 1, 1))) || lower(SUBSTR(CAST((actor.last_name) AS VARCHAR2(4000)), 2)))) AS actors
FROM ((((postgres.category
LEFT JOIN postgres.film_category ON ((category.category_id = film_category.category_id)))
LEFT JOIN postgres.film ON ((film_category.film_id = film.film_id)))
JOIN postgres.film_actor ON ((film.film_id = film_actor.film_id)))
JOIN postgres.actor ON ((film_actor.actor_id = actor.actor_id)))
GROUP BY film.film_id, film.title, film.description, category.name, film.rental_rate, film.length, film.rating;
CREATE VIEW postgres.sales_by_film_category AS
SELECT c.name AS category,
sum(p.amount) AS total_sales
FROM (((((postgres.payment p
JOIN postgres.rental r ON ((p.rental_id = r.rental_id)))
JOIN postgres.inventory i ON ((r.inventory_id = i.inventory_id)))
JOIN postgres.film f ON ((i.film_id = f.film_id)))
JOIN postgres.film_category fc ON ((f.film_id = fc.film_id)))
JOIN postgres.category c ON ((fc.category_id = c.category_id)))
GROUP BY c.name
ORDER BY (sum(p.amount)) DESC;
CREATE VIEW postgres.sales_by_store AS
SELECT ((CAST((c.city) AS VARCHAR2(4000)) || CAST(',' AS VARCHAR2(4000))) || CAST((cy.country) AS VARCHAR2(4000))) AS store,
((CAST((m.first_name) AS VARCHAR2(4000)) || CAST(' ' AS VARCHAR2(4000))) || CAST((m.last_name) AS VARCHAR2(4000))) AS manager,
sum(p.amount) AS total_sales
FROM (((((((postgres.payment p
JOIN postgres.rental r ON ((p.rental_id = r.rental_id)))
JOIN postgres.inventory i ON ((r.inventory_id = i.inventory_id)))
JOIN postgres.store s ON ((i.store_id = s.store_id)))
JOIN postgres.address a ON ((s.address_id = a.address_id)))
JOIN postgres.city c ON ((a.city_id = c.city_id)))
JOIN postgres.country cy ON ((c.country_id = cy.country_id)))
JOIN postgres.staff m ON ((s.manager_staff_id = m.staff_id)))
GROUP BY cy.country, c.city, s.store_id, m.first_name, m.last_name
ORDER BY cy.country, c.city;
CREATE VIEW postgres.staff_list AS
SELECT s.staff_id AS id,
((CAST((s.first_name) AS VARCHAR2(4000)) || CAST(' ' AS VARCHAR2(4000))) || CAST((s.last_name) AS VARCHAR2(4000))) AS name,
a.address,
a.postal_code AS "zip code",
a.phone,
city.city,
country.country,
s.store_id AS sid
FROM (((postgres.staff s
JOIN postgres.address a ON ((s.address_id = a.address_id)))
JOIN postgres.city ON ((a.city_id = city.city_id)))
JOIN postgres.country ON ((city.country_id = country.country_id)));
実行後、7個のビューが確認できれば成功です。
select object_name,object_type,status from dba_objects where owner='POSTGRES' and object_type='VIEW' order by 1 ;
9-10. ファンクションの作成
ファンクションを作成します。
ファンクション作成
CREATE OR REPLACE TYPE postgres.group_concat_aggr AS OBJECT (
aggregated_text CLOB,
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT group_concat_aggr) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT group_concat_aggr, value IN CLOB) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT group_concat_aggr, ctx2 IN group_concat_aggr) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(self IN group_concat_aggr, returnValue OUT CLOB, flags IN NUMBER) RETURN NUMBER
);
CREATE OR REPLACE TYPE BODY postgres.group_concat_aggr AS
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT group_concat_aggr) RETURN NUMBER IS
BEGIN
sctx := group_concat_aggr(NULL);
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT group_concat_aggr, value IN CLOB) RETURN NUMBER IS
BEGIN
IF self.aggregated_text IS NULL THEN
self.aggregated_text := value;
ELSE
self.aggregated_text := self.aggregated_text || ', ' || value;
END IF;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT group_concat_aggr, ctx2 IN group_concat_aggr) RETURN NUMBER IS
BEGIN
IF ctx2.aggregated_text IS NOT NULL THEN
IF self.aggregated_text IS NULL THEN
self.aggregated_text := ctx2.aggregated_text;
ELSE
self.aggregated_text := self.aggregated_text || ', ' || ctx2.aggregated_text;
END IF;
END IF;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate(self IN group_concat_aggr, returnValue OUT CLOB, flags IN NUMBER) RETURN NUMBER IS
BEGIN
returnValue := self.aggregated_text;
RETURN ODCIConst.Success;
END;
END;
CREATE OR REPLACE FUNCTION postgres.group_concat (input CLOB) RETURN CLOB AGGREGATE USING group_concat_aggr;
CREATE OR REPLACE FUNCTION postgres.inventory_in_stock (p_inventory_id NUMBER) RETURN CHAR IS
v_rentals NUMBER(10) := 0;
v_out NUMBER(10) := 0;
BEGIN
SELECT COUNT(*) INTO v_rentals
FROM postgres.rental
WHERE inventory_id = p_inventory_id;
IF v_rentals = 0 THEN
RETURN '1';
END IF;
SELECT COUNT(rental.rental_id) INTO v_out
FROM postgres.inventory inventory
LEFT JOIN postgres.rental rental ON inventory.inventory_id = rental.inventory_id
WHERE inventory.inventory_id = p_inventory_id
AND rental.return_date IS NULL;
IF v_out > 0 THEN
RETURN '0';
ELSE
RETURN '1';
END IF;
END;
CREATE OR REPLACE FUNCTION postgres.film_in_stock(p_film_id NUMBER, p_store_id NUMBER, p_film_count OUT NUMBER) return sys_refcursor is
l_ret_var sys_refcursor;
BEGIN
OPEN l_ret_var FOR SELECT inventory_id
FROM inventory
WHERE film_id = p_film_id
AND store_id = p_store_id
AND inventory_in_stock(inventory_id) = 1;
dbms_sql.return_result(l_ret_var);
END;
CREATE OR REPLACE FUNCTION postgres.film_not_in_stock(p_film_id NUMBER, p_store_id NUMBER, p_film_count OUT NUMBER) return sys_refcursor is
l_ret_var sys_refcursor;
BEGIN
OPEN l_ret_var FOR SELECT inventory_id
FROM inventory
WHERE film_id = p_film_id
AND store_id = p_store_id
AND NOT inventory_in_stock(inventory_id) = 0;
dbms_sql.return_result(l_ret_var);
END;
CREATE OR REPLACE FUNCTION postgres.inventory_held_by_customer(p_inventory_id NUMBER) RETURN NUMBER IS
v_customer_id NUMBER(10);
BEGIN
SELECT customer_id
INTO v_customer_id
FROM postgres.rental
WHERE return_date IS NULL
AND inventory_id = p_inventory_id;
RETURN v_customer_id;
EXCEPTION
-- Handle the case where no rows are found (e.g., inventory is not held by any customer)
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
CREATE OR REPLACE FUNCTION postgres.last_day(f_param1 TIMESTAMP) RETURN DATE IS
l_ret_var DATE;
BEGIN
l_ret_var := LAST_DAY(f_param1);
RETURN l_ret_var;
END;
CREATE OR REPLACE FUNCTION postgres.rewards_report (
min_monthly_purchases NUMBER,
min_dollar_amount_purchased NUMBER
) RETURN SYS_REFCURSOR IS
l_ret_var SYS_REFCURSOR;
last_month_start DATE;
last_month_end DATE;
BEGIN
IF min_monthly_purchases <= 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Minimum monthly purchases parameter must be greater than 0');
END IF;
IF min_dollar_amount_purchased <= 0 THEN
RAISE_APPLICATION_ERROR(-20002, 'Minimum monthly dollar amount purchased parameter must be greater than $0.00');
END IF;
last_month_start := TRUNC(ADD_MONTHS(SYSDATE, -3), 'MM');
last_month_end := LAST_DAY(last_month_start);
OPEN l_ret_var FOR
SELECT c.*
FROM customer c
WHERE c.customer_id IN (
SELECT p.customer_id
FROM payment p
WHERE p.payment_date BETWEEN last_month_start AND last_month_end
GROUP BY p.customer_id
HAVING SUM(p.amount) > min_dollar_amount_purchased
AND COUNT(p.customer_id) > min_monthly_purchases
);
RETURN l_ret_var;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20003, 'No customers found matching the criteria.');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20004, 'Unexpected error occurred: ' || SQLERRM);
END rewards_report;
CREATE OR REPLACE FUNCTION postgres.get_customer_balance (p_customer_id NUMBER, p_effective_date DATE) RETURN NUMBER IS
v_rentfees NUMBER(5, 2);
v_overfees NUMBER(10);
v_payments NUMBER(5, 2);
v_replacement_cost NUMBER(5, 2);
v_rental_duration_interval INTERVAL DAY TO SECOND;
BEGIN
SELECT NVL(SUM(film.rental_rate), 0) INTO v_rentfees
FROM film
JOIN inventory ON film.film_id = inventory.film_id
JOIN rental ON inventory.inventory_id = rental.inventory_id
WHERE rental.rental_date <= p_effective_date
AND rental.customer_id = p_customer_id;
SELECT NVL(SUM(CASE
WHEN (rental.return_date - rental.rental_date) > (film.rental_duration * INTERVAL '1' DAY) THEN
EXTRACT(DAY FROM (rental.return_date - rental.rental_date) - (film.rental_duration * INTERVAL '1' DAY))
ELSE 0 END), 0) INTO v_overfees
FROM rental
JOIN inventory ON rental.inventory_id = inventory.inventory_id
JOIN film ON inventory.film_id = film.film_id
WHERE rental.rental_date <= p_effective_date
AND rental.customer_id = p_customer_id;
SELECT NVL(SUM(CASE
WHEN (rental.return_date - rental.rental_date) > (film.rental_duration * 2 * INTERVAL '1' DAY) THEN
film.replacement_cost
ELSE 0 END), 0) INTO v_replacement_cost
FROM rental
JOIN inventory ON rental.inventory_id = inventory.inventory_id
JOIN film ON inventory.film_id = film.film_id
WHERE rental.rental_date <= p_effective_date
AND rental.customer_id = p_customer_id;
SELECT NVL(SUM(payment.amount), 0) INTO v_payments
FROM payment
WHERE payment.payment_date <= p_effective_date
AND payment.customer_id = p_customer_id;
RETURN v_rentfees + v_overfees + v_replacement_cost - v_payments;
END;
実行後、8個のファンクションが確認できれば成功です。
select object_name,object_type,status from dba_objects where owner='POSTGRES' and object_type='FUNCTION' order by 1;
9-11. 主な変更点
| No | オブジェクト | オブジェクト名 | 変更点 |
|---|---|---|---|
| 1 | TYPE | postgres."year" | 「year 」 →「"year" 」へ変更 |
| 2 | TABLE | address.phone | 実データにNULLあるので、NULL許可。 |
| 3 | TABLE | film.fulltext | 同上 |
| 4 | VIEW | customer_list | 「cu.activebool=1」→「cu.activebool= 't‘」へ変更 |
| 5 | VIEW | nicer_but_slower_film_list | 「“substring”」 →「SUBSTR」へ変更 |
| 6 | TRIGGER | CREATE TRIGGER film_fulltext_trigger | tsvector_update_triggerが変換されていないため、書き直し |
| 7 | FUNCTION | inventory_in_stock | 「CREATE TABLE」→「SELECT」へ変更 |
| 8 | FUNCTION | inventory_held_by_customer | 「CREATE TABLE」→「SELECT」へ変更 |
| 9 | FUNCTION | get_customer_balance | 「CAST(‘1 day’ AS interval DAY(5) TO SECOND) 」→「INTERVAL ‘1’ DAY」へ変更 |
| 10 | FUNCTION | postgres.film_in_stock | 「inventory_in_stock(inventory_id) 」→「inventory_in_stock(inventory_id) = 1’へ変更 |
| 11 | FUNCTION | postgres.film_not_in_stock | 「inventory_in_stock(inventory_id) 」→「inventory_in_stock(inventory_id) = 0’へ変更 |
| 12 | FUNCTION | postgres.group_concat | 書き直し |
10. データロード
SQL*Loaderを使用してデータをロードします。
10-1. SQL*Loaderの設定
SQL*Loaderの設定
$ wget https://download.oracle.com/otn_software/linux/instantclient/214000/instantclient-tools-linux.x64-21.4.0.0.0dbru.zip
$ wget https://download.oracle.com/otn_software/linux/instantclient/2111000/instantclient-basic-linux.x64-21.11.0.0.0dbru.zip
$ unzip instantclient-tools-linux.x64-21.4.0.0.0dbru.zip
$ unzip instantclient-basic-linux.x64-21.11.0.0.0dbru.zip
$ cd /home/<OCIユーザ名>/network/admin
$ mv Wallet_adb4psql.zip ../instantclient_21_11/network/admin
$ cd ../instantclient_21_11/network/admin
$ unzip Wallet_adb4psql.zip
viで編集
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/home/<OCIユーザ名>/network/admin/instantclient_21_11/network/admin")))
SSL_SERVER_DN_MATCH=yes
パスの設定
$ export TNS_ADMIN=/home/<OCIユーザ名>/network/admin/instantclient_21_11/network/admin
$ export PATH=$PATH:/home/<OCIユーザ名>/network/admin/instantclient_21_4/
10-2. ロードファイルの準備
viコマンド等を使用し、ロード用のファイルを作成します。
actor.ctl
LOAD DATA
INFILE 'ACTOR.csv'
INTO TABLE postgres.ACTOR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
ACTOR_ID "TO_NUMBER(:ACTOR_ID, '99999')",
FIRST_NAME,
LAST_NAME,
LAST_UPDATE TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF6"
)
10-3. データロードの実行
ロード実行前に、外部制約キーが設定されているテーブルはエラーが出るため、一度外部制約を無効化します。
ALTER TABLE POSTGRES.ADDRESS DISABLE CONSTRAINT FK_ADDRESS_CITY;
ALTER TABLE POSTGRES.CITY DISABLE CONSTRAINT FK_CITY;
ALTER TABLE POSTGRES.CUSTOMER DISABLE CONSTRAINT CUSTOMER_ADDRESS_ID_FKEY;
ALTER TABLE POSTGRES.FILM_CATEGORY DISABLE CONSTRAINT FILM_CATEGORY_FILM_ID_FKEY;
ALTER TABLE POSTGRES.FILM_ACTOR DISABLE CONSTRAINT FILM_ACTOR_FILM_ID_FKEY;
ALTER TABLE POSTGRES.FILM DISABLE CONSTRAINT FILM_LANGUAGE_ID_FKEY;
ALTER TABLE POSTGRES.INVENTORY DISABLE CONSTRAINT INVENTORY_FILM_ID_FKEY;
ALTER TABLE POSTGRES.PAYMENT DISABLE CONSTRAINT PAYMENT_STAFF_ID_FKEY;
ALTER TABLE POSTGRES.PAYMENT DISABLE CONSTRAINT PAYMENT_RENTAL_ID_FKEY;
ALTER TABLE POSTGRES.RENTAL DISABLE CONSTRAINT RENTAL_STAFF_ID_KEY;
ALTER TABLE POSTGRES.STAFF DISABLE CONSTRAINT STAFF_ADDRESS_ID_FKEY;
ALTER TABLE POSTGRES.STORE DISABLE CONSTRAINT STORE_MANAGER_STAFF_ID_FKEY;
順次、SQL*Loaderでデータをロードします。
actor.ctlを実行すると下記のような実行結果になります。
$ sqlldr admin/<パスワード>@adb4psql_high control=actor.ctl
SQL*Loader: Release 19.0.0.0.0 - Production on Sun Dec 29 05:22:37 2024
Version 19.10.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 201
Table POSTGRES.ACTOR:
200 Rows successfully loaded.
Check the log file:
actor.log
for more information about the load.
他のロード文は下記の通りとなります。
$ sqlldr admin/<パスワード>@adb4psql_high control=address.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=category.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=city.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=country.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=customer.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=film_category.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=film_actor.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=film.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=inventory.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=language.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=payment.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=rental.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=staff.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=store.ctl
ロードが成功したら、外部制約キーを有効化します。
ALTER TABLE POSTGRES.ADDRESS ENABLE CONSTRAINT FK_ADDRESS_CITY;
ALTER TABLE POSTGRES.CITY ENABLE CONSTRAINT FK_CITY;
ALTER TABLE POSTGRES.CUSTOMER ENABLE CONSTRAINT CUSTOMER_ADDRESS_ID_FKEY;
ALTER TABLE POSTGRES.FILM_CATEGORY ENABLE CONSTRAINT FILM_CATEGORY_FILM_ID_FKEY;
ALTER TABLE POSTGRES.FILM_ACTOR ENABLE CONSTRAINT FILM_ACTOR_FILM_ID_FKEY;
ALTER TABLE POSTGRES.FILM ENABLE CONSTRAINT FILM_LANGUAGE_ID_FKEY;
ALTER TABLE POSTGRES.INVENTORY ENABLE CONSTRAINT INVENTORY_FILM_ID_FKEY;
ALTER TABLE POSTGRES.PAYMENT ENABLE CONSTRAINT PAYMENT_STAFF_ID_FKEY;
ALTER TABLE POSTGRES.PAYMENT ENABLE CONSTRAINT PAYMENT_RENTAL_ID_FKEY;
ALTER TABLE POSTGRES.RENTAL ENABLE CONSTRAINT RENTAL_STAFF_ID_KEY;
ALTER TABLE POSTGRES.STAFF ENABLE CONSTRAINT STAFF_ADDRESS_ID_FKEY;
ALTER TABLE POSTGRES.STORE ENABLE CONSTRAINT STORE_MANAGER_STAFF_ID_FKEY;
11. 動作検証
行数をカウントするSQL文を実行し、RDS for PostgreSQLとAutonomous Databaseで行数がマッチするか検証します。ACTOR TABLEの例を下記に示します。
Oracle
SQL> select count(*) from postgres.ACTOR;
COUNT(*)
----------
200
PostgreSQL
dvdrental=> select count(*) from public.ACTOR;
count
-------
200
(1 row)
まとめ
RDS for PostgreSQLからAutonomous Databaseへの移行検証は以上になります。
DBエンジンを変換する移行方法を試してみました。
DBMS_CLOUD_MIGRATION.MIGRATE_FILEプロシージャを使用することで、DumpファイルからOracle用のSQL文を自動変換してくれることは便利です。ただ、100%変換した上で自動実行してくれる代物ではないため、現時点では一部手動で改修する前提が必要です。
変換までしきれずとも、自動試行まで実施した上で、変換のエラー部分を特定してくれるとさらに便利だと思いました。（よりよい方法をご存じのOraclemanがいらっしゃれば教えてください・・・）
とはいえ、このような変換ファイルなく、1からSQL文を書いて実行するのは大変工数がかかる作業ですので、その一助にはなりえる移行ツールであることは間違いなさそうです。