※ 本記事は、Martin Bachによる”Using faker-js/Faker to generate test data respecting referential integrity in Oracle Database 23c“を翻訳したものです。
2024年2月7日
Oracle Databaseには、一連のサンプル・スキーマが付属しています。これを使って、きわめて手軽に現実的なデータモデルを作る方法があります。この記事では、一から作るのではなく、人事管理の表(HRスキーマ)に基づいて作成します。
仮にHR.EMPLOYEES表のサンプル・データを提供するタスクがあなたに割り当てられたとします。EMPLOYEES表は、次のように定義されています:
SQL> info+ employees
TABLE: EMPLOYEES
LAST ANALYZED:
ROWS :
SAMPLE SIZE :
INMEMORY :DISABLED
COMMENTS :employees table. References with departments,
jobs, job_history tables. Contains a self reference.
Columns
NAME DATA TYPE NULL DEFAULT LOW_VALUE HIGH_VALUE NUM_DISTINCT HISTOGRAM
*EMPLOYEE_ID NUMBER(6,0) No
FIRST_NAME VARCHAR2(20 BYTE) Yes
LAST_NAME VARCHAR2(25 BYTE) No
EMAIL VARCHAR2(25 BYTE) No
PHONE_NUMBER VARCHAR2(20 BYTE) Yes
HIRE_DATE DATE No ..... .....
JOB_ID VARCHAR2(10 BYTE) No
SALARY NUMBER(8,2) Yes
COMMISSION_PCT NUMBER(2,2) Yes
MANAGER_ID NUMBER(6,0) Yes
DEPARTMENT_ID NUMBER(4,0) Yes
Indexes
INDEX_NAME UNIQUENESS STATUS FUNCIDX_STATUS COLUMNS
_______________________ _____________ _________ _________________ ________________________
HR.EMP_JOB_IX NONUNIQUE VALID JOB_ID
HR.EMP_NAME_IX NONUNIQUE VALID LAST_NAME, FIRST_NAME
HR.EMP_EMAIL_UK UNIQUE VALID EMAIL
HR.EMP_EMP_ID_PK UNIQUE VALID EMPLOYEE_ID
HR.EMP_MANAGER_IX NONUNIQUE VALID MANAGER_ID
HR.EMP_DEPARTMENT_IX NONUNIQUE VALID DEPARTMENT_ID
References
TABLE_NAME CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE VALIDATED GENERATED
______________ __________________ ______________ __________ _________________ ____________ ____________
DEPARTMENTS DEPT_MGR_FK NO ACTION ENABLED NOT DEFERRABLE VALIDATED USER NAME
EMPLOYEES EMP_MANAGER_FK NO ACTION ENABLED NOT DEFERRABLE VALIDATED USER NAME
JOB_HISTORY JHIST_EMP_FK NO ACTION ENABLED NOT DEFERRABLE VALIDATED USER NAME
(info+はsqlclで使用可能なコマンドです)
EMPLOYEES表は、このようなブログ投稿には最適です。(他の表に対する多くの制約や外部キーがあり、複雑さがちょうど良いです。)
FakerJSにご挨拶
FakerJSは、よく知られたデータ・ジェネレータで、前の記事で紹介されています。あらためて取り上げて、テストデータの作成に使用してみましょう。
ライセンスおよび使用に関する詳細は、プロジェクトのGitHubプロジェクト・サイトを参照してください。この記事では、貴社の法務およびITセキュリティ部門(および他の関係者)が、コードでこのモジュールを使用することが安全であり、ライセンスに準拠していることに同意したことほ前提としています。通常、アプリケーションでサード・パーティ・コードを使用するには、特定のコンプライアンス手続きを完了する必要がありますが、その段取りはこの記事の範囲外です。
この記事では、@faker-js/faker 8.3.1をOracle Database 23cにMultilingual Engine (MLE)モジュールとしてデプロイしたことを前提としています。この方法が不明な場合は、前の記事を参照してください。対応するMLE環境FAKER_ENVを構築したものとして話を進めます。
テスト・データ生成
次のJavaScriptモジュールは、いくつかのカスタム・ルーチンで@faker-js/fakerを拡張し、要求された従業員レコードを生成します。次に示す関数の一部は、以前の記事で紹介したものですが、コードをそのままコピー/ペーストすることで実行時にトラブルが起きないようにします。
randomEmployee()によって生成された従業員レコードは、JSONとして返されます。randomEmployee()の利用者は、データをそのまま列またはコレクションに格納できます。あるいは、json_table()を使用して、JSONをリレーショナル・モデルに変換することもできます。後者の例については、この記事の後半で説明します。
create or replace mle module mle_faker language javascript as
import { simpleFaker, faker } from "fakerjs";
/**
* Generate a random integer in the interval [minNumber, maxNumber]
* @param {number} minNumber the lower bound of the interval
* @param {number} maxNumber the upper bound of the interval
* @returns number
*/
export function randomInt(minNumber, maxNumber) {
const options = {
max: maxNumber,
min: minNumber
}
return simpleFaker.number.int(options);
}
/**
* Generate a random, alpha-numeric string of a certain case (defaults
* to mixed case), and a specific length
* @param {string} casing the string's case, one of upper, lower, mixed
* @param {*} minLength the string's minimum length
* @param {*} maxLength the string's maximum length
* @returns string
*/
export function randomString(casing, minLength, maxLength) {
const options = {
length: {
max: maxLength,
min: minLength
}
};
switch (casing) {
case 'upper':
options.casing = 'upper';
break;
case 'lower':
options.casing = 'lower';
break;
case 'mixed':
options.casing = 'mixed';
break;
default:
options.casing = 'mixed';
break;
}
return simpleFaker.string.alpha(options);
}
/**
* Generate a random date between in the interval [startDate, stopDate]
* @param {Date} startDate the earliest possible date
* @param {Date} stopDate the latest possible date
* @returns Date
*/
export function randomDate(startDate, stopDate) {
const options = {
from: startDate,
to: stopDate
};
return simpleFaker.date.between(options);
}
/**
* Create a new employee record for insertion into HR.EMPLOYEES. The main
* goal is to ensure referential integrity is preserved. Helper functions
* select the relevant details from the schema. Consumers of this function
* need to either store the record in JSON format, or use json_table() to
* convert JSON into the relational format.
* @param {string} gender the employee's gender
* @returns {JSON} a JSON representation of the new employee
*/
export function randomEmployee(gender) {
let employee = {};
const extension = randomInt(1, 9999);
// employee_id doesn't need to be created here
employee.firstName = faker.person.firstName(gender);
employee.lastName = faker.person.lastName();
employee.email = employee.firstName.substr(0,1) + employee.lastName.toUpperCase();
employee.phoneNumber = '1.650.555.' + faker.string.numeric(4);
employee.hireDate = randomDate('2020-01-01T00:00:00.000Z', '2023-12-01T00:00:00.000Z');
employee.jobId = randomJob();
employee.salary = faker.number.int(randomSalary(employee.jobId));
employee.commissionPct = null;
employee.departmentId = randomDepartment();
employee.managerId = randomManager(employee.departmentId);
return employee;
}
/**
* Get a random job from the HR.JOBS table
* @returns a random JOB_ID
*/
function randomJob() {
const result = session.execute(
`SELECT
job_id
FROM
jobs
ORDER BY
dbms_random.random
FETCH FIRST 1 ROWS ONLY`
);
if (result.rows === undefined) {
throw new Error(`database query went wrong: ${err}`);
} else {
return result.rows[0].JOB_ID;
}
}
/**
* Get a random department ID from HR.DEPARTMENTS. Makes sure the
* department has a manager. The department's manager will later
* be used as the new employee's manager as well.
* @returns a random DEPARTMENT_ID
*/
function randomDepartment() {
const result = session.execute(
`SELECT
department_id
FROM
departments
WHERE
manager_id is not null
ORDER BY
dbms_random.random
FETCH FIRST 1 ROWS ONLY`
);
if (result.rows === undefined) {
throw new Error(`database query went wrong: ${err}`);
} else {
return result.rows[0].DEPARTMENT_ID;
}
}
/**
* Get a specific department's manager from HR.DEPARTMENTS
* @param {number} departmentId the department ID for which a manager must be found
* @returns the corresponding MANAGER_ID
*/
function randomManager(departmentId) {
const result = session.execute(
`SELECT
manager_id
FROM
departments
WHERE
department_id = :1`,
[ departmentId ]
);
if (result.rows === undefined) {
throw new Error(`database query went wrong: ${err}`);
} else {
return result.rows[0].MANAGER_ID;
}
}
/**
* Get a random salary in the range defined by HR.JOBS, depending
* on the input parameter.
* @param {string} jobId matching HR.JOBS.JOB_ID
* @returns {JSON} a JSON object to be used in faker.number.int to calculate the appropriate salary
*/
function randomSalary(jobId) {
const result = session.execute(
`SELECT
min_salary as min,
max_salary as max
FROM
jobs
WHERE
job_id = :1`,
[ jobId ]
);
if (result.rows === undefined) {
throw new Error(`database query went wrong: ${err}`);
} else {
return {
min: result.rows[0].MIN,
max: result.rows[0].MAX
}
}
}
コール仕様
コール仕様により、JavaScriptコードがSQLおよびPL/SQLで使用可能になります。次のように作成できます:
create or replace package mle_faker_api as
function random_number(
p_min_number number,
p_max_number number
) return number
as mle module mle_faker
env faker_env
signature 'randomInt';
function random_string(
p_casing varchar2,
p_min_length number,
p_max_length number
) return varchar2
as mle module mle_faker
env faker_env
signature 'randomString';
function random_date(
p_start_date date,
p_stop_date date
) return date
as mle module mle_faker
env faker_env
signature 'randomDate';
function random_employee(
p_gender varchar2
) return JSON
as mle module mle_faker
env faker_env
signature 'randomEmployee';
end mle_faker_api;
/
テスト
JavaScriptコードが整い、コール仕様が利用可能になったら、いよいよテストです。
単一のレコードの作成
1人の従業員を作成するとどうなるかを見てみましょう:
SQL> select
2 mle_faker_api.random_employee('female') employee;
問合せによって次の出力が生成されます:
EMPLOYEE
---------------------------------------------------
{
"firstName" : "Beatrice",
"lastName" : "Lynch",
"email" : "BLYNCH",
"phoneNumber" : "1.650.555.2607",
"hireDate" : "2023-01-11T05:42:26.380000+01:00",
"jobId" : "IT_PROG",
"salary" : 6458,
"commissionPct" : null,
"departmentId" : 90,
"managerId" : 100
}
このJSONでは注目すべき点がいくつかあります:
- 真にランダムなデータ・ジェネレータとは異なり、
EMAIL列は表の行と一致します。Eメール・アドレスは、名の頭文字+姓で、すべて大文字で構成されます - 電話番号(phoneNumber)は既存の番号と同じ範囲内に表示されます
- 6458単位の給与(salary)は、
HR.JOBSで定義されている範囲にあります。プログラマの給与は4000から10000の通貨単位です - 部門90のマネージャが、この従業員のマネージャも務めます
HRサンプル・スキーマのコンテキストで、すべての値が理にかなっているので、良さそうです。
さらにデータを
複数の従業員レコードを作成する必要がある場合はどうなりますか? 大丈夫です! 次の例は、json_table()を使用してリレーショナル・フォーマットに変換された新しい10の従業員レコードを作成する方法を示しています:
SQL> with lots_of_employees as (
2 select
3 case when mod(rownum, 2) = 0 then
4 mle_faker_api.random_employee('female')
5 else
6 mle_faker_api.random_employee('male')
7 end employee
8 from
9 dual
10 connect by level
11 <= 10
12 )
13 select
14 jt.*
15 from
16 lots_of_employees e,
17 json_table(
18 e.employee,
19 '$'
20 columns (
21 first_name varchar2 path '$.firstName',
22 last_name varchar2 path '$.lastName',
23 email varchar2 path '$.email',
24 phone_number varchar2 path '$.phoneNumber',
25 hire_date date path '$.hireDate',
26 job_id varchar2 path '$.jobId',
27 salary number path '$.salary',
28 commission_pct number path '$.commissionPct',
29 manager_id number path '$.managerId',
30 department_id number path '$.departmentId'
31 )
32* ) jt;
FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
_____________ __________________ ___________________ _________________ ____________ __________ _________ _________________ _____________ ________________
Marc Mills MMILLS 1.650.555.4471 18-JUN-23 PU_MAN 14241 100 90
Marcia Welch MWELCH 1.650.555.6313 18-MAR-21 PU_MAN 13593 100 90
Mack Quitzon MQUITZON 1.650.555.7048 20-DEC-22 PR_REP 5533 200 10
Leticia Murphy LMURPHY 1.650.555.6040 29-AUG-22 AD_ASST 5066 103 60
Laurence Schuppe LSCHUPPE 1.650.555.8321 02-SEP-20 SA_MAN 19420 103 60
Phyllis Grant PGRANT 1.650.555.2625 14-MAY-22 AC_MGR 9475 100 90
Lowell Kilback LKILBACK 1.650.555.2167 20-DEC-21 PR_REP 4751 121 50
Mindy Upton MUPTON 1.650.555.4995 02-APR-23 ST_MAN 8356 200 10
Eddie Mitchell EMITCHELL 1.650.555.6041 26-MAR-23 SA_REP 11012 100 90
Gertrude O'Connell-Hoppe GO'CONNELL-HOPPE 1.650.555.8125 23-JAN-21 AC_MGR 9139 100 90
この方法で、HR.EMPLOYEES表に新しいレコードを追加するのは非常に簡単です。幸いなことに、EMPLOYEE_IDを心配する必要はありません。シーケンス(EMPLOYEES_SEQ)で、この列へ追加できます:
SQL> insert into employees
2 with lots_of_employees as (
3 select
4 case when mod(rownum, 2) = 0 then
5 mle_faker_api.random_employee('female')
6 else
7 mle_faker_api.random_employee('male')
8 end employee
9 from
10 dual
11 connect by level
12 <= 10
13 )
14 select
15 employees_seq.nextval,
16 jt.*
17 from
18 lots_of_employees e,
19 json_table(
20 e.employee,
21 '$'
22 columns (
23 first_name varchar2 path '$.firstName',
24 last_name varchar2 path '$.lastName',
25 email varchar2 path '$.email',
26 phone_number varchar2 path '$.phoneNumber',
27 hire_date date path '$.hireDate',
28 job_id varchar2 path '$.jobId',
29 salary number path '$.salary',
30 commission_pct number path '$.commissionPct',
31 manager_id number path '$.managerId',
32 department_id number path '$.departmentId'
33 )
34* ) jt;
10 rows inserted.
まとめ
ランダム・ダミー・データの作成はそれほど難しくありません。データが意味を持ち、主キーと外部キーの定義を考慮する必要がある場合、難易度が多少高くなりますが、前回の記事では、これらの要件を満たすことがそれほど難しくないことを実証できたと思います。