1. Introduction
In my current project, I use a SQL domain to implement the formatting and precedence rules for Semantic Versioning. I started with a simple implementation covering only the most basic rules. Getting the sorting right is key in my project. It allows me to identify the latest compatible version of an artefact. After adding some tests I started to evolve the functionality to a point where I can say that the implementation covers everything needed for my use case.
Domains make data models easier to understand and reduce the logic regarding data consistency and visualisation. In this blog post, I demonstrate this by evolving a domain for Semantic Versioning. This should give you an impression of whether keeping domains and table columns in sync is more complicated than traditional approaches.
2. What Are Domains?
A data use case domain (aka SQL domain, aka domain) is a new feature in the Oracle Database 23ai based on the SQL standard. Domains abstract column properties so that they can be used across multiple tables. They come in different flavours:
- Single-column domain (constraints for a single column, enums, display and order expressions)
- Multi-column domain (constraints across multiple columns, display and order expressions)
- Flexible domain (abstract domain, delegating functionality to concrete domains via discriminator columns)
It’s important to notice, that a table column can have only one domain. It is therefore not possible to combine single-column domains with multi-column or flexible domains.
3. Starting Model
3.1 Domains
First, let’s create three domains.
create domain if not exists app_identifier
as raw(16) strict
display lower(substr(rawtohex(app_identifier), 1, 8)
|| '-' || substr(rawtohex(app_identifier), 9, 4)
|| '-' || substr(rawtohex(app_identifier), 13, 4)
|| '-' || substr(rawtohex(app_identifier), 17, 4)
|| '-' || substr(rawtohex(app_identifier), 21, 12));
create domain if not exists app_file_name
as varchar2(128 char) strict;
create domain if not exists app_semantic_version
as varchar2(20 byte) strict
-- naïve, incomplete implementation of semantic versioning
constraint app_semantic_version_has_major_minor_patch_ck
check (regexp_like(app_semantic_version, '\d{1,6}\.\d{1,6}\.\d{1,6}'))
order to_char(to_number(regexp_substr(app_semantic_version, '\d+', 1, 1)), 'FM000000')
|| '.' || to_char(to_number(regexp_substr(app_semantic_version, '\d+', 1, 2)), 'FM000000')
|| '.' || to_char(to_number(regexp_substr(app_semantic_version, '\d+', 1, 3)), 'FM000000');Domain APP_IDENTIFIER created.
Domain APP_FILE_NAME created.
Domain APP_SEMANTIC_VERSION created.The domain app_identifier defines the data type for a GUID-based identifier. It also defines the display format.
The domain app_file_name defines only the data type used for file names.
The domain app_semantic_version is an incomplete implementation of Semantic Versioning 2.0.0 (we will fix that later). It defines the data type, a check constraint app_semantic_version_has_major_minor_patch_ck and an order expression.
3.2 Table
Let’s create a table using all these domains and insert a few rows.
create table if not exists app_files (
file_id app_identifier default sys_guid() not null,
file_name app_file_name not null,
file_version app_semantic_version not null,
constraint app_files_pk primary key (file_id),
constraint app_files_uk1 unique (file_name, file_version)
);
desc app_files
insert into app_files
(file_name, file_version)
values
('file1.txt', '1.9.0'),
('file1.txt', '1.10.0'),
('file1.txt', '1.11.0'),
('file1.txt', '2.0.0'),
('file2.txt', '0.0.7'),
('file2.txt', '0.0.42');Table APP_FILES created.
Name Null? Type
------------ -------- ----------------------------------------
FILE_ID NOT NULL RAW(16 BYTE) DOMAIN APP_IDENTIFIER
FILE_NAME NOT NULL VARCHAR2(128 CHAR) DOMAIN APP_FILE_NAME
FILE_VERSION NOT NULL VARCHAR2(20) DOMAIN APP_SEMANTIC_VERSION
6 rows inserted.Look at the result of the desc app_files command. Each column has a concrete data type inherited from the domain, as well as the domain associated with it.
3.3 Demo
Now, let’s query the data to demonstrate the domain functionality.
column file_name format a9
select domain_display(file_id) as file_id,
file_name,
file_version,
domain_order(file_version) as file_version_order
from app_files
order by file_name, file_version_order desc;FILE_ID FILE_NAME FILE_VERSION FILE_VERSION_ORDER
------------------------------------ --------- -------------------- -----------------------
29ed8fd9-57c7-4b4d-e063-03e0a8c091f8 file1.txt 2.0.0 000002.000000.000000
29ed8fd9-57c6-4b4d-e063-03e0a8c091f8 file1.txt 1.11.0 000001.000011.000000
29ed8fd9-57c5-4b4d-e063-03e0a8c091f8 file1.txt 1.10.0 000001.000010.000000
29ed8fd9-57c4-4b4d-e063-03e0a8c091f8 file1.txt 1.9.0 000001.000009.000000
29ed8fd9-57c9-4b4d-e063-03e0a8c091f8 file2.txt 0.0.42 000000.000000.000042
29ed8fd9-57c8-4b4d-e063-03e0a8c091f8 file2.txt 0.0.7 000000.000000.000007
6 rows selected. You see the file version is sorted according to the specification in 11.2:
“Precedence is determined by the first difference when comparing each of these identifiers from left to right as follows: Major, minor, and patch versions are always compared numerically.”
4. Test
The semantic versioning specification covers a lot of ground that our simple implementation can barely cover. So let’s test it, to find out what is missing. We use utPLSQL, of course.
4.1 Define Test
Let’s create a utPLSQL test PL/SQL package.
create or replace package test_app_domains is
--%suite
--%test
procedure test_app_semantic_version;
end test_app_domains;
/
create or replace package body test_app_domains is
procedure test_app_semantic_version is
c_actual sys_refcursor;
c_expected sys_refcursor;
begin
open c_actual for
select input_col,
to_char(domain_check(app_semantic_version, input_col)) as test_check,
case when domain_check(app_semantic_version, input_col) then domain_order(cast(input_col as app_semantic_version)) end as test_order
from (values
-- https://semver.org/#spec-item-2
('1.9.0'),
('1.10.0'),
('1.11.0'),
-- https://semver.org/#spec-item-9
('1.0.0-alpha'),
('1.0.0-alpha.1'),
('1.0.0-0.3.7'),
('1.0.0-x.7.z.92'),
('1.0.0-x-y-z.--'),
-- https://semver.org/#spec-item-10
('1.0.0-alpha+001'),
('1.0.0+20130313144700'),
('1.0.0-beta+exp.sha.5114f85'),
('1.0.0+21AF26D3----117B344092BD'),
-- https://semver.org/#spec-item-11
('1.0.0'),
('2.0.0'),
('2.1.0'),
('2.1.1'),
('1.0.0-alpha'),
('1.0.0-alpha.1'),
('1.0.0-alpha.beta'),
('1.0.0-beta'),
('1.0.0-beta.2'),
('1.0.0-beta.11'),
('1.0.0-rc.1'),
-- invalid
('01.0.0'),
('1.00.0'),
('1.0.00'),
('1.0.x')
) as t(input_col);
open c_expected for
select *
from (values
-- https://semver.org/#spec-item-2
('1.9.0', 'TRUE', '000001.000009.000000(1)'),
('1.10.0', 'TRUE', '000001.000010.000000(1)'),
('1.11.0', 'TRUE', '000001.000011.000000(1)'),
-- https://semver.org/#spec-item-9
('1.0.0-alpha', 'TRUE', '000001.000000.000000(0)-alpha'),
('1.0.0-alpha.1', 'TRUE', '000001.000000.000000(0)-alpha.000001'),
('1.0.0-0.3.7', 'TRUE', '000001.000000.000000(0)-000000.000003.000007'),
('1.0.0-x.7.z.92', 'TRUE', '000001.000000.000000(0)-x.000007.z.000092'),
('1.0.0-x-y-z.--', 'TRUE', '000001.000000.000000(0)-x-y-z.--'),
-- https://semver.org/#spec-item-10
('1.0.0-alpha+001', 'TRUE', '000001.000000.000000(0)-alpha'),
('1.0.0+20130313144700', 'TRUE', '000001.000000.000000(1)'),
('1.0.0-beta+exp.sha.5114f85', 'TRUE', '000001.000000.000000(0)-beta'),
('1.0.0+21AF26D3----117B344092BD', 'TRUE', '000001.000000.000000(1)'),
-- https://semver.org/#spec-item-11
('1.0.0', 'TRUE', '000001.000000.000000(1)'),
('2.0.0', 'TRUE', '000002.000000.000000(1)'),
('2.1.0', 'TRUE', '000002.000001.000000(1)'),
('2.1.1', 'TRUE', '000002.000001.000001(1)'),
('1.0.0-alpha', 'TRUE', '000001.000000.000000(0)-alpha'),
('1.0.0-alpha.1', 'TRUE', '000001.000000.000000(0)-alpha.000001'),
('1.0.0-alpha.beta', 'TRUE', '000001.000000.000000(0)-alpha.beta'),
('1.0.0-beta', 'TRUE', '000001.000000.000000(0)-beta'),
('1.0.0-beta.2', 'TRUE', '000001.000000.000000(0)-beta.000002'),
('1.0.0-beta.11', 'TRUE', '000001.000000.000000(0)-beta.000011'),
('1.0.0-rc.1', 'TRUE', '000001.000000.000000(0)-rc.000001'),
--invalid
('01.0.0', 'FALSE', null),
('1.00.0', 'FALSE', null),
('1.0.00', 'FALSE', null),
('1.0.x', 'FALSE', null)
) as t(input_col, test_check, test_order);
ut.expect(c_actual).to_equal(c_expected).join_by('INPUT_COL');
end test_app_semantic_version;
end test_app_domains;
/Package TEST_APP_DOMAINS compiled
Package Body TEST_APP_DOMAINS compiled The test compares the cursors for the actual and expected results.
You will get a compile error for the package body if you do not have the utPLSQL framework installed on your database instance. See the utPLSQL installation guide for information on how to install utPLSQL. It’s quite simple and works on an Oracle Database 23ai.
4.2 Run Test
Now let’s run the test.
set serveroutput on size unlimited;
exec ut.run;test_app_domains
test_app_semantic_version [.087 sec] (FAILED - 1)
Failures:
1) test_app_semantic_version
Actual: refcursor [ count = 27 ] was expected to equal: refcursor [ count = 27 ]
Diff:
Rows: [ 30 differences, showing first 20 ]
PK <INPUT_COL>1.9.0</INPUT_COL> - Actual: <TEST_ORDER>000001.000009.000000</TEST_ORDER>
PK <INPUT_COL>1.9.0</INPUT_COL> - Expected: <TEST_ORDER>000001.000009.000000(1)</TEST_ORDER>
PK <INPUT_COL>1.10.0</INPUT_COL> - Actual: <TEST_ORDER>000001.000010.000000</TEST_ORDER>
PK <INPUT_COL>1.10.0</INPUT_COL> - Expected: <TEST_ORDER>000001.000010.000000(1)</TEST_ORDER>
PK <INPUT_COL>1.11.0</INPUT_COL> - Actual: <TEST_ORDER>000001.000011.000000</TEST_ORDER>
PK <INPUT_COL>1.11.0</INPUT_COL> - Expected: <TEST_ORDER>000001.000011.000000(1)</TEST_ORDER>
PK <INPUT_COL>1.0.0-alpha</INPUT_COL> - Actual: <TEST_ORDER>000001.000000.000000</TEST_ORDER>
PK <INPUT_COL>1.0.0-alpha</INPUT_COL> - Expected: <TEST_ORDER>000001.000000.000000(0)-alpha</TEST_ORDER>
PK <INPUT_COL>1.0.0-alpha</INPUT_COL> - Actual: <TEST_ORDER>000001.000000.000000</TEST_ORDER>
PK <INPUT_COL>1.0.0-alpha</INPUT_COL> - Expected: <TEST_ORDER>000001.000000.000000(0)-alpha</TEST_ORDER>
PK <INPUT_COL>1.0.0-alpha.1</INPUT_COL> - Actual: <TEST_ORDER>000001.000000.000000</TEST_ORDER>
PK <INPUT_COL>1.0.0-alpha.1</INPUT_COL> - Expected: <TEST_ORDER>000001.000000.000000(0)-alpha.000001</TEST_ORDER>
PK <INPUT_COL>1.0.0-alpha.1</INPUT_COL> - Actual: <TEST_ORDER>000001.000000.000000</TEST_ORDER>
PK <INPUT_COL>1.0.0-alpha.1</INPUT_COL> - Expected: <TEST_ORDER>000001.000000.000000(0)-alpha.000001</TEST_ORDER>
PK <INPUT_COL>1.0.0-0.3.7</INPUT_COL> - Actual: <TEST_ORDER>000001.000000.000000</TEST_ORDER>
PK <INPUT_COL>1.0.0-0.3.7</INPUT_COL> - Expected: <TEST_ORDER>000001.000000.000000(0)-000000.000003.000007</TEST_ORDER>
PK <INPUT_COL>1.0.0-x.7.z.92</INPUT_COL> - Actual: <TEST_ORDER>000001.000000.000000</TEST_ORDER>
PK <INPUT_COL>1.0.0-x.7.z.92</INPUT_COL> - Expected: <TEST_ORDER>000001.000000.000000(0)-x.000007.z.000092</TEST_ORDER>
PK <INPUT_COL>1.0.0-x-y-z.--</INPUT_COL> - Actual: <TEST_ORDER>000001.000000.000000</TEST_ORDER>
PK <INPUT_COL>1.0.0-x-y-z.--</INPUT_COL> - Expected: <TEST_ORDER>000001.000000.000000(0)-x-y-z.--</TEST_ORDER>
PK <INPUT_COL>1.0.0-alpha+001</INPUT_COL> - Actual: <TEST_ORDER>000001.000000.000000</TEST_ORDER>
PK <INPUT_COL>1.0.0-alpha+001</INPUT_COL> - Expected: <TEST_ORDER>000001.000000.000000(0)-alpha</TEST_ORDER>
PK <INPUT_COL>1.0.0+20130313144700</INPUT_COL> - Actual: <TEST_ORDER>000001.000000.000000</TEST_ORDER>
PK <INPUT_COL>1.0.0+20130313144700</INPUT_COL> - Expected: <TEST_ORDER>000001.000000.000000(1)</TEST_ORDER>
PK <INPUT_COL>1.0.0-beta+exp.sha.5114f85</INPUT_COL> - Actual: <TEST_CHECK>FALSE</TEST_CHECK><TEST_ORDER/>
PK <INPUT_COL>1.0.0-beta+exp.sha.5114f85</INPUT_COL> - Expected: <TEST_CHECK>TRUE</TEST_CHECK><TEST_ORDER>000001.000000.000000(0)-beta</TEST_ORDER>
PK <INPUT_COL>1.0.0+21AF26D3----117B344092BD</INPUT_COL> - Actual: <TEST_CHECK>FALSE</TEST_CHECK><TEST_ORDER/>
PK <INPUT_COL>1.0.0+21AF26D3----117B344092BD</INPUT_COL> - Expected: <TEST_CHECK>TRUE</TEST_CHECK><TEST_ORDER>000001.000000.000000(1)</TEST_ORDER>
PK <INPUT_COL>1.0.0</INPUT_COL> - Actual: <TEST_ORDER>000001.000000.000000</TEST_ORDER>
PK <INPUT_COL>1.0.0</INPUT_COL> - Expected: <TEST_ORDER>000001.000000.000000(1)</TEST_ORDER>
PK <INPUT_COL>2.0.0</INPUT_COL> - Actual: <TEST_ORDER>000002.000000.000000</TEST_ORDER>
PK <INPUT_COL>2.0.0</INPUT_COL> - Expected: <TEST_ORDER>000002.000000.000000(1)</TEST_ORDER>
PK <INPUT_COL>2.1.0</INPUT_COL> - Actual: <TEST_ORDER>000002.000001.000000</TEST_ORDER>
PK <INPUT_COL>2.1.0</INPUT_COL> - Expected: <TEST_ORDER>000002.000001.000000(1)</TEST_ORDER>
PK <INPUT_COL>2.1.1</INPUT_COL> - Actual: <TEST_ORDER>000002.000001.000001</TEST_ORDER>
PK <INPUT_COL>2.1.1</INPUT_COL> - Expected: <TEST_ORDER>000002.000001.000001(1)</TEST_ORDER>
PK <INPUT_COL>1.0.0-alpha</INPUT_COL> - Actual: <TEST_ORDER>000001.000000.000000</TEST_ORDER>
PK <INPUT_COL>1.0.0-alpha</INPUT_COL> - Expected: <TEST_ORDER>000001.000000.000000(0[...]
at "DEMO42.TEST_APP_DOMAINS.TEST_APP_SEMANTIC_VERSION", line 80 ut.expect(c_actual).to_equal(c_expected).join_by('INPUT_COL');
Finished in .091311 seconds
1 tests, 1 failed, 0 errored, 0 disabled, 0 warning(s)
PL/SQL procedure successfully completed.The highlighted lines for 1.0.0-alpha+001 explain the problems with our current implementation, which are:
- Missing support for pre-release labels (
-alpha) - Missing support for build metadata (
+001, leading zeros are allowed and preserved) - Missing precedence handling between releases and pre-releases (releases
(1)are newer than pre-releases(0)) - Missing precedence handling for pre-releases (numeric qualifiers to be compared numerically)
- Missing precedence handling for build metadata (to be ignored)
5. Fix
5.1 Drop domain
Before deploying a new version of the domain, we have to drop the existing one. alter domain is not applicable because we need to change the length of the data type and a create or replace syntax variant does not exist for domains.
drop domain if exists app_semantic_version;Error starting at line : 1 in command -
drop domain if exists app_semantic_version
Error report -
ORA-11502: The domain APP_SEMANTIC_VERSION to be dropped has dependent objects.
https://docs.oracle.com/error-help/db/ora-11502/11502. 0000 - "The domain %s to be dropped has dependent objects."
*Cause: An attempt is made to drop a domain with dependent objects.
*Action: Drop the domain using the FORCE modeOkay, that didn’t work, but the error message is good. It lets us know what to do.
So let’s try again with the force option.
drop domain if exists app_semantic_version force;Domain APP_SEMANTIC_VERSION dropped.5.2 Recreate domain
Now we can deploy the fixed variant of the domain.
create domain if not exists app_semantic_version
as varchar2(60 byte) strict
-- valid examples: '0.13.0', '23.5.0', '123456.789012.345678'
-- valid pre-release examples: '1.0.0-alpha', '1.0.0-alpha.1', '1.0.0-0.3.7', '1.0.0-x.7.z.92'
-- valid build metadata examples: '1.0.0+20130313144700', '1.0.0-beta+exp.sha.5114f85'
-- use suggested regex from https://semver.org/spec/v2.0.0.html#is-there-a-suggested-regular-expression-regex-to-check-a-semver-string without non-capturing groups (?:)
constraint app_semantic_version_has_major_minor_patch_ck
check (regexp_like(app_semantic_version, '^(0|[1-9]\d*)\.(0|[1-9]\d*)\.(0|[1-9]\d*)(-((0|[1-9]\d*|\d*[a-zA-Z-][0-9a-zA-Z-]*)(\.(0|[1-9]\d*|\d*[a-zA-Z-][0-9a-zA-Z-]*))*))?(\+([0-9a-zA-Z-]+(\.[0-9a-zA-Z-]+)*))?$'))
-- add leading zeroes to major, minor, patch and numeric qualifiers in pre-release for sorting (normalized semantic version)
-- supports up to 6 digits for major, minor, patch and numeric qualifiers in pre-release
order to_char(to_number(regexp_substr(app_semantic_version, '\d+', 1, 1)), 'FM000000')
|| '.' || to_char(to_number(regexp_substr(app_semantic_version, '\d+', 1, 2)), 'FM000000')
|| '.' || to_char(to_number(regexp_substr(app_semantic_version, '\d+', 1, 3)), 'FM000000')
-- sort pre-release versions (0) before final versions (1)
-- build metadata is ignored for sorting, they have the same precedence according to the specification
|| case
when instr(app_semantic_version, '-') > 0
and (instr(app_semantic_version, '+') = 0 or instr(app_semantic_version, '-') < instr(app_semantic_version, '+'))
then
-- sort pre-release according the qualifiers after the hyphen, ignoring build metadata, add leading zeroes to qualifiers starting with a number
'(0)' || regexp_replace( -- workaround part 2: remove superfluous leading zeroes
regexp_replace( -- workaround part 1: add 6 leading zeroes to numeric qualifiers
regexp_replace( -- remove build metadata
substr(app_semantic_version, instr(app_semantic_version, '-')),
'\+.+$',
null
),
'(\.|\-|^)(\d{1,6})',
'\1@000000\2@' -- workaround since \2 in lpad, to_char is not evaluated before calling the function
),
'@[0]+(\d{6})@',
'\1'
)
else
'(1)'
end;Domain APP_SEMANTIC_VERSION created.The details of the fix are not so important, besides the fact that the data type changed from varchar2(20 bytes) to varchar2(60 bytes).
This variant should fully support Semantic Versioning 2.0 as long as numeric qualifiers do not require more than 6 digits. This is good enough for my use case.
5.3 Re-test
It’s now time to re-run the previously failed utPLSQL test.
set serveroutput on size unlimited;
exec ut.run;test_app_domains
test_app_semantic_version [.02 sec]
Finished in .021919 seconds
1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)
PL/SQL procedure successfully completed.Looks good.
5.4 Check Table
Now let’s look at the structure of the app_files table.
desc app_filesName Null? Type
------------ -------- ---------------------------------------
FILE_ID NOT NULL RAW(16 BYTE) DOMAIN APP_IDENTIFIER
FILE_NAME NOT NULL VARCHAR2(128 CHAR) DOMAIN APP_FILE_NAME
FILE_VERSION NOT NULL VARCHAR2(20) The file_version column has a length of 20 instead of 60 bytes, and the association with the app_semantic_version domain is missing.
5.5 Modify Table
We can fix the column length and associate the column with a domain in one go.
alter table app_files
modify (file_version varchar2(60 byte) domain app_semantic_version);
desc app_filesTable APP_FILES altered.
Name Null? Type
------------ -------- ----------------------------------------
FILE_ID NOT NULL RAW(16 BYTE) DOMAIN APP_IDENTIFIER
FILE_NAME NOT NULL VARCHAR2(128 CHAR) DOMAIN APP_FILE_NAME
FILE_VERSION NOT NULL VARCHAR2(60) DOMAIN APP_SEMANTIC_VERSION This looks good and was quite easy.
Let’s query the data in this table using the new domain variant.
column file_name format a9
column file_version format a12
column file_version_order format a25
select domain_display(file_id) as file_id,
file_name,
file_version,
domain_order(file_version) as file_version_order
from app_files
order by file_name, file_version_order desc;FILE_ID FILE_NAME FILE_VERSION FILE_VERSION_ORDER
------------------------------------ --------- ------------ -------------------------
29ed8fd9-57c7-4b4d-e063-03e0a8c091f8 file1.txt 2.0.0 000002.000000.000000(1)
29ed8fd9-57c6-4b4d-e063-03e0a8c091f8 file1.txt 1.11.0 000001.000011.000000(1)
29ed8fd9-57c5-4b4d-e063-03e0a8c091f8 file1.txt 1.10.0 000001.000010.000000(1)
29ed8fd9-57c4-4b4d-e063-03e0a8c091f8 file1.txt 1.9.0 000001.000009.000000(1)
29ed8fd9-57c9-4b4d-e063-03e0a8c091f8 file2.txt 0.0.42 000000.000000.000042(1)
29ed8fd9-57c8-4b4d-e063-03e0a8c091f8 file2.txt 0.0.7 000000.000000.000007(1)
6 rows selected. The only visible change is the release version indicator –(1) – at the end of the file_version_order column. We need it when we have pre-release versions to determine the correct order.
Let’s add some pre-release versions and versions with build metadata and query the table again.
insert into app_files
(file_name, file_version)
values
('file3.txt', '1.0.0-beta'),
('file3.txt', '1.0.0-beta.2'),
('file3.txt', '1.0.0-beta.11.2.3.4'),
('file3.txt', '1.0.0-beta+exp.sha.5114f85'),
('file3.txt', '1.0.0+21AF26D3----117B344092BD');
column file_name format a9
column file_version format a30
column file_version_order format a57
select domain_display(file_id) as file_id,
file_name,
file_version,
domain_order(file_version) as file_version_order
from app_files
order by file_name, file_version_order desc;5 rows inserted.
FILE_ID FILE_NAME FILE_VERSION FILE_VERSION_ORDER
------------------------------------ --------- ------------------------------ ---------------------------------------------------------
29ed8fd9-57c7-4b4d-e063-03e0a8c091f8 file1.txt 2.0.0 000002.000000.000000(1)
29ed8fd9-57c6-4b4d-e063-03e0a8c091f8 file1.txt 1.11.0 000001.000011.000000(1)
29ed8fd9-57c5-4b4d-e063-03e0a8c091f8 file1.txt 1.10.0 000001.000010.000000(1)
29ed8fd9-57c4-4b4d-e063-03e0a8c091f8 file1.txt 1.9.0 000001.000009.000000(1)
29ed8fd9-57c9-4b4d-e063-03e0a8c091f8 file2.txt 0.0.42 000000.000000.000042(1)
29ed8fd9-57c8-4b4d-e063-03e0a8c091f8 file2.txt 0.0.7 000000.000000.000007(1)
29fb5aeb-44d7-39e2-e063-03e0a8c01f4e file3.txt 1.0.0+21AF26D3----117B344092BD 000001.000000.000000(1)
29fb5aeb-44d5-39e2-e063-03e0a8c01f4e file3.txt 1.0.0-beta.11.2.3.4 000001.000000.000000(0)-beta.000011.000002.000003.000004
29fb5aeb-44d4-39e2-e063-03e0a8c01f4e file3.txt 1.0.0-beta.2 000001.000000.000000(0)-beta.000002
29fb5aeb-44d3-39e2-e063-03e0a8c01f4e file3.txt 1.0.0-beta 000001.000000.000000(0)-beta
29fb5aeb-44d6-39e2-e063-03e0a8c01f4e file3.txt 1.0.0-beta+exp.sha.5114f85 000001.000000.000000(0)-beta
11 rows selected. Look at the five highlighted rows. The pre-release labels are considered in the file_version_order column but the build metadata is ignored.
Now we can easily determine the latest version of a file.
select file_Name, file_version as latest_file_version
from (
select file_name,
file_version,
domain_order(file_version) as current_version,
max(domain_order(file_version)) over (partition by file_name) as max_version
from app_files
)
where current_version = max_version
order by file_name;FILE_NAME LATEST_FILE_VERSION
--------- ------------------------------------------------------------
file1.txt 2.0.0
file2.txt 0.0.42
file3.txt 1.0.0+21AF26D3----117B344092BD The domain nicely hides the implementation details of the semantic versioning precedence rules.
6. Alternatives to Domains
The following table shows the domain features and their alternatives within the Oracle Database.
| Domain Feature | Alternative |
|---|---|
| ❌ Data type of a domain column | ❌ Data type of a table column |
| ❌ Enums | 🤔 Lookup table |
| ❌ Constraint on single-column domain | ❌ Table constraint |
| ❌ Constraint on multi-column domain | ❌ Table constraint |
| ❌ Flexible domain | ❌ Table constraint |
| ❌ Validate JSON column against a schema | ❌ Table constraint |
| ❌ Collation | ❌ Table column collation |
| ✅ Annotation | ✅ Table column annotation |
| ✅ Display expression | ✅ Function (standalone, package, type) |
| ✅ Order expression | ✅ Function (standalone, package, type) |
The emojis have the following meanings:
- ✅ A change is possible without impacting the underlying table/data (e.g.
alter domainis applicable) - 🤔 A change might have an impact on the underlying table/data (e.g. removing/adding enum item)
- ❌ A change will have an impact on the underlying table/data (e.g.
alter tableor data migration)
The main difference between the domain features and their alternatives is that domains provide an abstraction in a standardised way. This should make the models easier to understand and therefore easier to maintain.
7. Conclusion
A tough part of evolving data structures is changing data types. Domains neither simplify nor complicate this. You only use an alternative series of statements for a change.
However, reassociating domains with columns can become tedious for domains used in many columns. It’s a good idea to save the usage before dropping a domain. This is certainly a useful area for annotations and helper scripts. Perhaps future versions of the Oracle Database will allow us to disable domain associations instead of dropping them. Similar to constraints. This would be very helpful in preventing the loss of important information and would relieve us of the burden of managing additional metadata.
Furthermore, the lack of support for domains in PL/SQL or virtual columns limits its usefulness. Hopefully, future versions will address these shortcomings.
Nevertheless, I like the idea of domains and will try to apply the following principles in new projects:
- Use strict single-column domains instead of raw data types in tables whenever possible. This ensures the consistent use of data types (e.g., for identifiers, names, descriptions, etc.).
- Do not use multi-column domains, as columns can only be associated with one domain. Also, do not use flexible domains (IMO we should avoid designs with discriminator columns).
- Favour traditional lookup tables over enums (and provide the data as part of the application). Enums become appealing for small and static reference data once domains are supported in PL/SQL.
- Define a
display_expressionfor raw and string columns, if the data should not be presented “as is” (e.g. GUID). - Define an
order_expressionfor columns that have a non-default precedence (e.g. semantic version).
Ask me in two or three years whether this was a good idea.